Cop4710 database management connect to postgresql sever via pgadmin
This presentation is the property of its rightful owner.
Sponsored Links
1 / 20

COP4710 Database Management Connect to PostgreSQL sever via pgAdmin PowerPoint PPT Presentation


  • 80 Views
  • Uploaded on
  • Presentation posted in: General

COP4710 Database Management Connect to PostgreSQL sever via pgAdmin. Prof: Dr. Shu-Ching Chen Ta: Hsin-Yu Ha. Procedure outline. Download pgAdmin from http://www.pgadmin.org/ Install pgAdmin Connect to PostgreSQL through pgAdmin Start creating your own database. Download pgAdmin (1).

Download Presentation

COP4710 Database Management Connect to PostgreSQL sever via pgAdmin

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Cop4710 database management connect to postgresql sever via pgadmin

COP4710 Database ManagementConnect to PostgreSQLsever viapgAdmin

Prof: Dr. Shu-Ching Chen

Ta: Hsin-Yu Ha


Procedure outline

Procedure outline

  • Download pgAdmin from http://www.pgadmin.org/

  • Install pgAdmin

  • Connect to PostgreSQL through pgAdmin

  • Start creating your own database.


Download pgadmin 1

Download pgAdmin (1)

Go to PgAdmin Website

Click download


Download pgadmin 2

Download pgAdmin(2)

Select the latest reliable version v1.12.3


Download pgadmin 3

Download pgAdmin(3)


Download pgadmin 4

Download pgAdmin(4)

  • Download pgadmin3-1.12.3.zip and extract.


Install pgadmin 1

Install pgAdmin (1)

  • After extracting the zip file “pgadmin3-1.12.3.zip” , execute the file pgadmin3.msi and start the installation process.


Install pgadmin 2

Install pgAdmin(2)


Install pgadmin 3

Install pgAdmin(3)


Install pgadmin 4

Install pgAdmin(4)

The installation is successfully finished


Connect to postgresql 1

Connect to PostgreSQL (1)

  • Open pgAdmin and add a connection to a server


Connect to postgresql 2

Connect to PostgreSQL(2)

Name for server connection

Hosted server: cop4710-postgresql.cs.fiu.edu

Database Name: spr13_”fiu_account”

Username: spr13 _”fiu_account”

Password: Panther ID


Create a database 1

Create a database (1)

  • Create table

  • Constraints:

  • NOT NULL

  • UNIQUE

  • CHECK Boolean expression

  • For instance CHECK (price>0)

  • (4) PRIMARY KEY

  • (5) FOREIGN KEY

CREATE TABLE products (

product_no integer PRIMARY KEY,

name text NOT NULL,

price numeric

);

  • Primary key and Foreign key

CREATE TABLE order_items (

product_no integer REFERENCES products,

order_id integer REFERENCES orders,

quantity integer,

PRIMARY KEY (product_no, order_id),

CONSTRAINT cq CHECK (quantity > 5)

);


Create a database 2

Create a database (2)

  • Foreign Key

CREATE TABLE other_table (

c1 integer PRIMARY KEY,

c2 integer

);

CREATE TABLE t1 (

a integer PRIMARY KEY,

b integer,

c integer,

FOREIGN KEY (b, c) REFERENCES other_table (c1, c2) );


Create a database 3

Create a database (3)

CREATE TABLE products (

product_no integer PRIMARY KEY,

name text,

price numeric

);

CREATE TABLE orders(

order_id integer PRIMARY KEY,

shipping_address text,

);

CREATE TABLE order_items (

product_no integer REFERENCES products ON DELETE RESTRICT,

order_id integer REFERENCES orders ON DELETE CASCADE,

quantity integer,

PRIMARY KEY (product_no, order_id)

);


Create a database 4

Create a database (4)

  • Insert Data

INSERT INTO products (product_no , name ,price) VALUES (1,'cheese',5);

INSERT INTO products VALUES

(1,'cheese',5),

(2,’cake’,10) ;


Create a database 5

Create a database (5)

  • Import Data

  • Export Data

COPY country FROM '/usr1/proj/bray/sql/country_data.csv'

WITH DELIMITER ',' ;

COPY country TO '/sql/country_data.csv' WITH DELIMITER '|';


Create a database 6

Create a database (6)

  • ALTER Table

    • Add columns

    • Remove columns

    • Add constraints

ALTER TABLE products ADD COLUMN description text;

ALTER TABLE products DROP COLUMN description;

ALTER TABLE products ADD CONSTRAINT namecheck CHECK (name <> '');

ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);

ALTER TABLE products ADD FOREIGN KEY (product_group_id)

REFERENCES product_groups;

ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;


Create a database 7

Create a database (7)

  • ALTER Table

    • Remove constraints

    • Change column data types

    • Rename columns

    • Rename tables

ALTER TABLE products DROP CONSTRAINT some_name;

ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;

ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);

ALTER TABLE products RENAME COLUMN product_no TO product_number;

ALTER TABLE products RENAME TO items;


Create a database 8

Create a database (8)

  • Delete Data

DELETE FROM products WHERE price = 10;

DELETE FROM products


  • Login