学习目标

学习表的约束、表的默认值、自增类型等技术

课程作业

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

-- 创建表的时候为表定义列级约束：在列级定义了primary key约束（id列）和not null约束（name列）。
root@modb:~# 
root@modb:~# su - omm
omm@modb:~$ 
omm@modb:~$ gsql -r
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:00 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=#   
omm=# \dt
omm=# No relations found.
omm=# 

omm=# create table test( id bigint primary key, name varchar(50) not null,age int );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
omm=# insert into test values (1,'zhangsan',33);
INSERT 0 1
omm=# select * from test;
 id |   name   | age 
----+----------+-----
  1 | zhangsan |  33
(1 row)

omm=# \dt
                        List of relations
 Schema | Name | Type  | Owner |             Storage              
--------+------+-------+-------+----------------------------------
 public | test | table | omm   | {orientation=row,compression=no}
(1 row)

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

omm=# 

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

---创建表时定义表级约束  执行下面的SQL语句，在创建表的时候为表定义表级约束：
#这里在表列级定义了primary key约束（id列），在列级定义了not null约束（name列）
omm=# create table test001(id bigint,name varchar(50) not null,age int,primary key(id));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test001_pkey" for table "test001"
CREATE TABLE
omm=# insert into test001 values(1,'lisi',50);
INSERT 0 1
omm=# select * from test001;
 id | name | age 
----+------+-----
  1 | lisi |  50
(1 row)

omm=# \d test001
 age    | integer               | 
Indexes:
    "test001_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default

omm=#            Table "public.test001"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | bigint                | not null
 name   | character varying(50) | not null

omm=# 

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

omm=# create table test002(id bigint,name varchar(30) not null,age int default 20,primary key(id));
WARNING:  Session unused timeout.
FATAL:  terminating connection due to administrator command
could not send data to server: Broken pipe
The connection to the server was lost. Attempting reset: Succeeded.
omm=# 
omm=# 
omm=# 
omm=# create table test002(id bigint,name varchar(30) not null,age int default 20,primary key(id));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test002_pkey" for table "test002"
CREATE TABLE
omm=# insert into test002(id,name) values (1,'wangwu');
INSERT 0 1
omm=# insert into test002(id,name) values (2,'zhaoliu');
INSERT 0 1
omm=# select * from test002;
 id |  name   | age 
----+---------+-----
  1 | wangwu  |  20
  2 | zhaoliu |  20
(2 rows)

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

omm=# create table test003(id bigint,name varchar(50) not null,age int,primary key(id));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test003_pkey" for table "test003"
CREATE TABLE
omm=# insert into test003(id,name) values (1,'sunba');
INSERT 0 1
omm=# select * from test003;
 id | name  | age 
----+-------+-----
  1 | sunba |    
(1 row)

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

---商品发-票的编号通常按顺序递增。这种情况可以使用serial数据类型。最简单方法直接使用serial数据类型。
omm=# create table invoice(invoicenum serial NOT NULL,name varchar(20));
NOTICE:  CREATE TABLE will create implicit sequence "invoice_invoicenum_seq" for serial column "invoice.invoicenum"
CREATE TABLE
---为表invoice插入3条记录，并查看插入数据后的表的数据：
omm=# insert into invoice(name) values('user1');
INSERT 0 1
omm=# insert into invoice(name) values('user2');
INSERT 0 1
omm=# insert into invoice(name) values('user3');
INSERT 0 1
---可以看到每插入一条记录到表invoice后，列invoicenum的值会自增1。
omm=# select * from invoice;
 invoicenum | name  
------------+-------
          1 | user1
          2 | user2
          3 | user3
(3 rows)

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

--执行下面的SQL语句，将创建新表，并且会将旧表的数据拷贝给新表：
omm=# CREATE TABLE newtestwithdata AS SELECT * FROM invoice;
INSERT 0 3
omm=# SELECT * FROM newtestwithdata;
 invoicenum | name  
------------+-------
          1 | user1
          2 | user2
          3 | user3
(3 rows)
--执行下面的SQL语句，创建和旧表的表结构相同的新表，但是不会将旧表的数据拷贝给新表：
omm=# CREATE TABLE testnewwithoutdata AS SELECT * FROM invoice WHERE 1=2;
INSERT 0 0
omm=#  SELECT * FROM testnewwithoutdata;
 invoicenum | name 
------------+------
(0 rows)