一、Postgresql的基本操作

-----------------------------------------------------------------------------------------------------
--目录：
--1. 数据库
----1.1 创建数据库
----1.2 删除数据库
--2. 架构
----2.1 创建架构
----2.2 删除架构
--3. 表
----3.1 创建表
------3.1.1 多个字段的联合唯一性
------3.1.2 主键和外键
----3.2 删除表
----3.3 修改表
-----------------------------------------------------------------------------------------------------

--1. 数据库
----1.1 创建数据库
create database test;
----1.2 删除数据库
drop database test;
----1.3 修改数据库
alter database test rename to testdb; 

--2. 架构
----2.1 创建架构
create schema testschema;
----2.1 删除架构
drop schema testschema;

--3. 表
----3.1 创建表
create table test_table1(
    sid serial, --serial表示字段为自增字段
    stu_number integer not null, --not null表示非空约束
    name text unique, ----unique表示唯一性约束，指定的字段不能插入重复值
    math_score numeric default 59.99, ----defalut表示设置该字段的默认值
    english_score numeric check(english_score > 0), ----check表示该字段的值必须符合其内的表达式
    description text,
    UNIQUE(description) --唯一性约束也可以这样写
);

------3.1.1 多个字段的联合唯一性
create table example3(
    a integer,
    b integer,
    c integer,
    UNIQUE(a,c)
);
insert into example3 values(1,1,1);
insert into example3 values(1,1,2);--pased
insert into example3 values(1,2,1);--failed: duplicate key value violates unique constraint "example3_a_c_key"
------3.1.2 主键和外键
create table example4(
    a integer,
    b integer,
    c integer,
    primary key(b,c)-- 主键可以同时作用于多个字段，形成联合主键
);

create table example5(
    a integer primary key,
    b integer,
    c integer,
    foreign key(b,c) references example4(b,c)--该外键的字段数量和被引用表中的主键的数量必须保持一致
);
----Description----
--(1) 当多个表之间存在主外键参考性约束关系的时候，如果想删除主键的某行数据，由于该行的记录的主键字段值可能正在被其引用表中的某条记录所关联，将会导致删除操作的失败
insert into example4 values(1,1,1)；
insert into example4 values(1,2,2);
insert into example5 values(1,3,3);--failed: insert or update on table "example5" violates foreign key constraint "example5_b_fkey". Key (b, c)=(3, 3) is not present in table "example4".
insert into example5 values(2,1,1);
insert into example5 values(3,2,2);
select * from example4;
select * from example5;
delete from example4 where a = 1;--failed: update or delete on table "example4" violates foreign key constraint "example5_b_fkey" on table "example5".  Key (b, c)=(1, 1) is still referenced from table "example5".
--(2) Psql提供了限制和级联删除来解决(1)中的问题
create table a
(
    q integer primary key,
    w integer,
    e integer,
    t integer
);
insert into a values(1,1,1,1);
insert into a values(2,1,1,1);
create table b(
    a integer references a(q) on delete cascade,  --cascade删除主表中一个被引用的行，所有的引用它的行也会被自动删除
    b integer
);
insert into b values(1,1);
insert into b values(1,2);
insert into b values(2,1);
delete from a where q = 1;
select * from b ;
create table c(
    a integer references a(q) on delete restrict, --restrict 禁止删除被引用的行 
    --a integer references a(q) on update cascade
    b integer
);
insert into c values(2,1);
delete from a where q = 2; --ERROR:  update or delete on table "a" violates foreign key constraint "c_a_fkey" on table "c" DETAIL:  Key (q)=(2) is still referenced from table "c".
-------------------
----3.2 删除表
drop table tablename;
----3.3 修改表
alter table test_table1 add column add_column text not null; --增加一个字段
alter table test_table1 drop column add_column;
--如果该表是主表，该字段是被引用字段，那么该操作将会失败
--如果想要在删除的时候删除引用字段的所有关联数据，可以采用以下方式
alter table a drop column q cascade