表操作大全，主键，约束，外键，字段操作，重命名
1.单个字段主键
postgres=# create table test01(id int primary key, note varchar(20));
2.复合主键
postgres=# create table test02(id1 int,id2 int,note varchar(20), CONSTRAINT pk_test02 primary key(id1,id2));
3.唯一约束UNIQUE
postgres=# create table test03(id1 int,id2 int,note varchar(20),id3 int, CONSTRAINT pk_test03 primary key(id1,id2), CONSTRAINT uk_test03_id3 UNIQUE(id3));
4.check约束，定义某些字段的值必须满足某种要求
postgres=# create table child(name varchar(20), age int,note text, CONSTRAINT ck_child_age CHECK(age<18));
年龄字段要求不能大于18
create table books(book_no integer,name text,price numeric CHECK(price>0),discounted_price numeric CHECK(discounted_price),CHECK (price>discounted_price));
CHECK(price>0) CHECK(discounted_price) 为字段约束
CHECK (price>discounted_price)为表约束
create table books(book_no integer,name text,price numeric ,discounted_price numeric,CHECK(price>0),CHECK(discounted_price),CHECK (price>discounted_price));
或者
create table books(book_no integer,name text,price numeric ,discounted_price numeric,CHECK(price>0 and discounted_price and price>discounted_price));
5.非空约束
create table books(book_no integer not null, name text, price numeric);
6.外键约束
create table class(class_no int primary key, class_name varchar(40));
create table student(student_no int primary key,student_name varchar(40), age int, class_no int REFERENCES class(class_no));
7.增加字段
alter table class add column class_teacher varchar(40);
8.删除字段
alter table class drop column class_teacher varchar(40);
9.删除字段外键依赖CASCADE
alter table class drop column class_teacher varchar(40) CASCADE;
10.增加约束
alter table student ADD CHECK (age>16);
alter table class ADD CONSTRAINT unique_class_teacher UNIQUE(class_teacher);
11.增加非空约束
alter table student ALTER COLUMN student_name SET NOT NULL;
12.删除约束
alter table class DROP CONSTRAINT unique_class_teacher;
13.修改默认值
alter table student ALTER COLUMN age SET DEFAULT 15;
14.删除默认值
alter table student ALTER COLUMN age DROP DEFAULT;
15.修改字段数据类型
alter table student ALTER COLUMN student_name TYPE text;
只有在字段里现有的每个项都可以隐式地转换成新类型时，这个语句才能成功。
如果把varchar(40)改成varchar(5)，就可能失败
alter table student ALTER COLUMN student_name TYPE varchar(5);
16.重命名字段
alter table books RENAME COLUMN book_no TO book_id;
17.重命名表
alter table class RENAME to classes;