openGauss每日一练第 14 天 | 表管理2

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

课程作业
1.创建表的时候定义列级约束

root@modb:~# su - omm
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=# create table t1(id int primary key,name varchar(10) not null);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
omm=# CREATE TABLE

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

omm=# create table t2(id int,name varchar(10) not null,primary key(id));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t2_pkey" for table "t2"
CREATE TABLE

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

omm=# create table t3(id int,name varchar(20) not null, age int default 20,primary key(id));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t3_pkey" for table "t3"
CREATE TABLE

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

omm=# create table t4(id int,aget int);
CREATE TABLE
omm=# insert into t4(id) values(10);
omm=# INSERT 0 1

omm=# select * from t4;
 id | aget 
----+------
 10 |     
(1 row)

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

omm=# create table t6(id serial not null,name varchar(10));
NOTICE:  CREATE TABLE will create implicit sequence "t6_id_seq" for serial column "t6.id"
CREATE TABLE
omm=# insert into t6(name) values('aa');
INSERT 0 1
omm=# insert into t6(name) values('bb');
INSERT 0 1
omm=# select * from t6;
 id | name 
----+------
  1 | aa
  2 | bb
(2 rows)

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

omm=# create table t7 as select * from t6;
INSERT 0 2
omm=# select * from t7;
(2 rows)

omm=#  id | name 
----+------
  1 | aa
  2 | bb

omm=# create table t8 as select * from t6 where 0=1;
INSERT 0 0
omm=# select * from t8;
 id | name 
----+------
(0 rows)