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

Cop4710 database management connect to postgresql sever via pgadmin
Download
1 / 20

  • 104 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).

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

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