PostgreSQL的Command Prompt(命令提示符)的基本操作

1.创建用户

C:/Program Files/PostgreSQL/8.3/bin>createuser -P -U postgres

Enter name of role to add: newUser [Enter]

Enter password for new role:xxxx [Enter]

Enter it again:xxxx [Enter]

Shall the new role be a superuser? (y/n) y [Enter]

Password:yyyy(用户postgres的密码) [Enter]

(CREATE ROLE)

 

2.创建数据库

C:/Program Files/PostgreSQL/8.3/bin>createdb -U newUser newDatabase

Password:

 

3.连接数据库

C:/Program Files/PostgreSQL/8.3/bin>psql newDatabase newUser

Password for user newUser:

Welcome to psql 8.3.4, the PostgreSQL interactive terminal.

 

Type:  /copyright for distribution terms

       /h for help with SQL commands

       /? for help with psql commands

       /g or terminate with semicolon to execute query

       /q to quit

 

4.创建表

newDatabase=# create table t_address ( id serial unique primary key not null,

newDatabase(#                          name text not null,

newDatabase(#                          pref integer not null,

newDatabase(#                          address text not null );

NOTICE:  CREATE TABLE will create implicit sequence "t_address_id_seq" for seria

l column "t_address.id"

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t_address_pkey"

for table "t_address"

CREATE TABLE

 

newDatabase=# create table t_pref ( id serial unique primary key not null,

newDatabase(#                       pref text not null );

NOTICE:  CREATE TABLE will create implicit sequence "t_pref_id_seq" for serial c

olumn "t_pref.id"

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t_pref_pkey" for

 table "t_pref"

CREATE TABLE

 

5.插入数据

newDatabase=# insert into t_pref ( pref ) values ( 'Tokyo' );

INSERT 0 1

newDatabase=# insert into t_pref ( pref ) values ( 'Chiba' );

INSERT 0 1

 

newDatabase=# insert into t_address ( name, pref, address ) values ('Okada', 1, 'Ed

ogawaku 1-2-3' );

INSERT 0 1

newDatabase=# insert into t_address ( name, pref, address ) values ('Takahashi', 2,

 'Ichikawa 4-5-6' );

INSERT 0 1

 

6.查询数据

newDatabase=# select * from t_pref;

 id | pref

----+-------

  1 | Tokyo

  2 | Chiba

(2 rows)

 

newDatabase=# select * from t_address;

 id |   name    | pref |     address

----+-----------+------+-----------------

  1 | Okada     |    1 | Edogawaku 1-2-3

  2 | Takahashi |    2 | Ichikawa 4-5-6

(2 rows)

 

7.制约测试

7.1创建表

newDatabase=# create table t_address2 ( id serial unique primary key not null,

newDatabase(#                           name text not null,

newDatabase(#                           pref_id_fk integer not null constraint pref

_id_constraint references t_pref(id),

newDatabase(#                           address text not null );

NOTICE:  CREATE TABLE will create implicit sequence "t_address2_id_seq" for seri

al column "t_address2.id"

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t_address2_pkey"

 for table "t_address2"

CREATE TABLE

7.2插入数据

newDatabase=# insert into t_address2 ( name, pref_id_fk, address ) values ( 'Okada

', 1, 'Edogawaku 1-2-3' );

INSERT 0 1

newDatabase=# insert into t_address2 ( name, pref_id_fk, address ) values ( 'Suzuki

', 200, 'Sapporoshi 7-8-9' );

7.3发生错误

ERROR:  insert or update on table "t_address2" violates foreign key constraint "

pref_id_constraint"

DETAIL:  Key (pref_id_fk)=(200) is not present in table "t_pref".

newDatabase=# insert into t_address2 ( name , pref_id_fk, address ) values ( 'Okada

', (select id from t_pref where pref like 'Tokyo'), 'Edogawaku 1-2-3' );

INSERT 0 1

7.4查询数据

newDatabase=# select * from t_address2;

 id | name  | pref_id_fk |     address

----+-------+------------+-----------------

  1 | Okada |          1 | Edogawaku 1-2-3

  3 | Okada |          1 | Edogawaku 1-2-3

(2 rows)

 

8.退出连接

newDatabase-# /q

 

C:/Program Files/PostgreSQL/8.3/bin>