postgresql-查看表结构（命令和sql方式）、修改表结构、外键
声明：本PostgreSQl实用指南系列为刘兴(http://deepfuture.iyunv.com/)原创，未经笔者授权，任何人和机构不能转载SQL方式查看表结构(表名是student)
mydb=# SELECT a.attnum,a.attname AS field,t.typname AS type,a.attlen AS length,a
.atttypmod AS lengthvar,a.attnotnull AS notnull
mydb-# FROM pg_class c,pg_attribute a,pg_type t
mydb-# WHERE c.relname = 'student' and a.attnum > 0 and a.attrelid = c.oid and a
.atttypid = t.oid
mydb-# ORDER BY a.attnum;
attnum | field | type | length | lengthvar | notnull
--------+-------+---------+--------+-----------+---------
1 | name | varchar | -1 | 24 | f
2 | age | int4 | 4 | -1 | f
3 | city | int4 | 4 | -1 | f
(3 rows)
命令方式
mydb=# \d student
Table "public.student"
Column | Type | Modifiers
--------+-----------------------+-----------
name | character varying(20) |
age | integer |
city | integer |
修改表结构
mydb=# alter table student add sex int;
ALTER TABLE
mydb=# \d student;
Table "public.student"
Column | Type | Modifiers
--------+-----------------------+-----------
name | character varying(20) |
age | integer |
city | integer |
sex | integer |
mydb=# alter table student alter sex type varchar(5);
ALTER TABLE
mydb=# select * from citys
mydb-# ;
name | id
------+----
长沙 | 1
湛江 | 2
上海 | 3
(3 rows)
mydb=# select * from student;
name | age | city | sex
------------+-----+------+-----
deepfuture | 20 | 1 |
未来 | 20 | 2 |
张三 | 21 | 1 |
(3 rows)
增加主键
mydb=# alter table citys add primary key (id);
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "citys_pkey" f
or table "citys"
ALTER TABLE
增加外键
mydb=# alter table student add constraint cityfk foreign key (city) references c
itys(id);
ALTER TABLE
增加和修改表记录
mydb=# insert into student values ('王国',18,2);
INSERT 0 1
mydb=# insert into student values ('王国',18,3);
INSERT 0 1
mydb=# update student set name='干哈' where name='王国';
UPDATE
mydb=# update student set name='艾丝凡' where name='干哈' and city=3;
UPDATE 1
mydb=# select * from student;
name | age | city | sex
------------+-----+------+-----
deepfuture | 20 | 1 |
未来 | 20 | 2 |
张三 | 21 | 1 |
干哈 | 18 | 2 |
艾丝凡 | 18 | 3 |
(5 rows)
mydb=# select * from citys;
name | id
------+----
长沙 | 1
湛江 | 2
上海 | 3
(3 rows)
违反外键增加表记录
mydb=# insert into student values('萨芬',19,5);
ERROR: insert or update on table "student" violates foreign key constraint "cit
yfk"
DETAIL: Key (city)=(5) is not present in table "citys".
STATEMENT: insert into student values('萨芬',19,5);
ERROR: insert or update on table "student" violates foreign key constraint "cit
yfk"
DETAIL: Key (city)=(5) is not present in table "citys".
在外键允许下增加表记录
mydb=# insert into student values('萨芬',19,3);
INSERT 0 1
mydb=#
页:
[1]