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

原创代野（Tank） 2022-12-07

803

第 14 天学习打卡，来到了表管理的第 2 课，从后面没有更新的课程来看，表相关课程共有 4 节。本节主要学习约束、表的默认值、自增类型及使用现有的表创建新表。

建表详细的命令和介绍，参考 openGauss 官网：CREATE TABLE。

作业

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

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

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

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

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

omm=#

为表的属性定义默认值

omm=# create table test002(
omm(#   id bigint,
omm(#   name varchar(28) not null,
omm(#   age  int default 20,  
omm(#   primary key(id)
omm(# );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test002_pkey" for table "test002"
CREATE TABLE
omm=#  insert into test002(id,name) values(1,'user1');
INSERT 0 1
omm=# insert into test002(id,name) values(2,'user2');
INSERT 0 1
omm=#   select * from test002;
 id | name  | age 
----+-------+-----
  1 | user1 |  20
  2 | user2 |  20
(2 rows)

omm=#

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

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

omm=#

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

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
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
omm=# select * from invoice;
 invoicenum | name  
------------+-------
          1 | user1
          2 | user2
          3 | user3
(3 rows)

omm=#

使用现有的表创建新表

-- 带数据
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)

omm=# 

-- 不带数据
omm=#  CREATE TABLE testnewwithoutdata AS SELECT * FROM invoice WHERE 1=2;
INSERT 0 0
omm=#  SELECT * FROM testnewwithoutdata;
 invoicenum | name 
------------+------
(0 rows)

omm=#