openGauss每日一练第14天 | openGauss逻辑结构：表管理2

1、创建表的时候定义列级约束

-- 创建表的时候为表定义列级约束：在列级定义了primary key约束（id列）和not null约束（name列）。
omm=# create table t1(id bigint primary key,name varchar(50) not null,age int);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
CREATE TABLE

--查看表结构
omm-# \d t1
omm-#              Table "public.t1"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | bigint                | not null
 name   | character varying(50) | not null
 age    | integer               | 
Indexes:
    "t1_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default

--查看索引
omm-# \di
                 List of relations
 Schema |  Name   | Type  | Owner | Table | Storage 
--------+---------+-------+-------+-------+---------
 public | t1_pkey | index | omm   | t1    | 
(1 row)

2、创建表的时候定义表级约束

--新建t2表，这里在表级定义了primary key约束（id列）
create table t2(id bigint not null,name varchar(50) not null,age int,
               primary key (id));
               
omm=# omm=# 
create table t2(id bigint not null,name varchar(50) not null,age int,
                primary key (id));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t2_pkey" for table "t2"
CREATE TABLE

--查看t2表结构
omm=# \d t2
             Table "public.t2"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | bigint                | not null
 name   | character varying(50) | not null
 age    | integer               | 
Indexes:
    "t2_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default

3、为表的属性定义默认值

--创建t3表，同时给表字段age设置默认值18
create table t3(id bigint not null,name varchar(50) not null,age int default 18,
               primary key (id));
               
omm=# create table t3(id bigint not null,name varchar(50) not null,age int default 18,
                      primary key (id));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t3_pkey" for table "t3"
CREATE TABLE

--插入数据，主键不能为空
omm=# insert into t3(name) values('zhangsan'),('lisi');
ERROR:  null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, zhangsan, 18).

--插入2条数据，没有给age赋值
omm=# insert into t3(id,name) values(1,'zhangsan'),(2,'lisi');
INSERT 0 2

--没有给age赋值，age字段填充默认值18
omm=# select * from t3;
 id |   name   | age 
----+----------+-----
  1 | zhangsan |  18
  2 | lisi     |  18
(2 rows)

    
omm=# \d t3
              Table "public.t3"
 Column |         Type          | Modifiers  
--------+-----------------------+------------
 id     | bigint                | not null
 name   | character varying(50) | not null
 age    | integer               | default 18
Indexes:
    "t3_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default

omm=#           
               

4、如果在创建表的时候，没有为某列定义默认值，缺省的默认值是空值null

--创建t4表，descr字段没有设置默认值
create table t4(id bigint not null,name varchar(50) not null,age int default 18,descr varchar(200),primary key (id));


omm=# create table t4(id bigint not null,name varchar(50) not null,age int default 18,descr varchar(200),primary key (id));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t4_pkey" for table "t4"
CREATE TABLE

--插入一条数据，没给descr字段赋值，descr字段也没有默认值，缺省的默认值是空值null。
omm=# insert into t4(id,name) values(1,'zhangsan');
INSERT 0 1
omm=# select * from t4;
 id |   name   | age | descr 
----+----------+-----+-------
  1 | zhangsan |  18 | 
(1 row)

omm=# select * from t4 where descr is null;
 id |   name   | age | descr 
----+----------+-----+-------
  1 | zhangsan |  18 | 
(1 row)

5、创建表时使用自增数据类型

--表设置主键字段自增，可以用序列或者序列类型
--序列类型
create table t5(id bigserial not null,name varchar(50));

omm=# create table t5(id bigserial not null,name varchar(50));
NOTICE:  CREATE TABLE will create implicit sequence "t5_id_seq" for serial column "t5.id"
CREATE TABLE

--插入2条数据，可以看到主键id字段从1开始自增了
omm=# insert into t5(name) values('app1'),('app2');
INSERT 0 2
omm=# select * from t5;
 id | name 
----+------
  1 | app1
  2 | app2
(2 rows)


--查看t5表结构，可以知道id数据类型bigserial,其实内部是bigint类型基础上，多了一个序列
omm=# \d t5
                                Table "public.t5"
 Column |         Type          |                    Modifiers                    
--------+-----------------------+-------------------------------------------------
 id     | bigint                | not null default nextval('t5_id_seq'::regclass)
 name   | character varying(50) | 


--通过序列实现主键id自增，需要先创建序列，然后在给表插入数据的时候，显示使用序列赋值给主键字段
omm=# create sequence t6_id_seq start with 1 increment by 1;
CREATE SEQUENCE

--产生一个序列值
omm=# select t6_id_seq.nextval;
 nextval 
---------
       1
(1 row)

omm=# select t6_id_seq.nextval,t6_id_seq.currval;   
omm=#  nextval | currval 
---------+---------
       2 |       2
(1 row)

omm=# select nextval('t6_id_seq');
 nextval 
---------
       3
(1 row)


create table t6(id bigint not null,name varchar(50),primary key (id));
insert into t6 values(t6_id_seq.nextval,'app1'),(t6_id_seq.nextval,'app2')


omm=# create table t6(id bigint not null,name varchar(50),primary key (id));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t6_pkey" for table "t6"
CREATE TABLE

--序列当前值是3，我们这个时候使用序列给表t6插入两条数据，则主键从4开始，比如下面两条数据，主键id值分别是4和5
omm=# insert into t6 values(t6_id_seq.nextval,'app1'),(t6_id_seq.nextval,'app2');
INSERT 0 2

omm=# select * from t6;
 id | name 
----+------
  4 | app1
  5 | app2
(2 rows)


omm=# \d t6  
             Table "public.t6"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | bigint                | not null
 name   | character varying(50) | 
Indexes:
    "t6_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default

omm=# 

6、使用现有的表创建新表

--使用t5表创建t7表，下面这样会把t5表数据也复制过来
omm=# create table t7 as select * from t5;
INSERT 0 2

omm=# select * from t7;
omm=#  id | name 
----+------
  1 | app1
  2 | app2
(2 rows)

--使用t5表创建t8表，不复制t5表数据，只复制表结构
omm=# create table t8 as select * from t5 where 1=2;
INSERT 0 0
omm=# select * from t8;
 id | name 
----+------
(0 rows)


--下面可以看出，从t5表复制出来的t7、t8表只是表字段相同，表约束什么的都没有复制过来的
omm=# \d t5
omm=#                                 Table "public.t5"
 Column |         Type          |                    Modifiers                    
--------+-----------------------+-------------------------------------------------
 id     | bigint                | not null default nextval('t5_id_seq'::regclass)
 name   | character varying(50) | 

 
omm=# \d t7
             Table "public.t7"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | bigint                | 
 name   | character varying(50) | 

omm=# \d t8
             Table "public.t8"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | bigint                | 
 name   | character varying(50) | 



--下面可以看出，从t1表复制出来的t9表只是表字段相同，表约束什么的都没有复制过来的，索引也不会复制过来
omm=# \d t1
             Table "public.t1"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | bigint                | not null
 name   | character varying(50) | not null
 age    | integer               | 
Indexes:
omm=#     "t1_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default


omm=# \d t9
             Table "public.t9"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | bigint                | 
 name   | character varying(50) | 
 age    | integer               |