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,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
','Edogawaku 1-2-3' );
INSERT 0 1
newDatabase=# insert into t_address2 ( name,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,(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>
