概述

本文档记录openGauss 3.0.0数据库每日一练第14天课程作业，学习表的约束、表的默认值、自增类型等技术。

课程练习

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

omm@local:/opt/huawei/tmp [postgres]=#CREATE TABLESPACE day14 RELATIVE LOCATION 'tablespace/day14';
CREATE TABLESPACE
omm@local:/opt/huawei/tmp [postgres]=#CREATE DATABASE musicdb14 WITH TABLESPACE = day14;
CREATE DATABASE
omm@local:/opt/huawei/tmp [postgres]=#CREATE USER user14 IDENTIFIED BY 'zs@123456';
CREATE ROLE
omm@local:/opt/huawei/tmp [postgres]=#ALTER USER user14  SYSADMIN;
ALTER ROLE
omm@local:/opt/huawei/tmp [postgres]=#\c musicdb14 user14
Password for user user14: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb14" as user "user14".
user14@local:/opt/huawei/tmp [musicdb14]=>create table test1(id bigint primary key, name varchar(50) not null,age  int);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test1_pkey" for table "test1"
CREATE TABLE
user14@local:/opt/huawei/tmp [musicdb14]=>insert into test1 values(1,'user14',22);
INSERT 0 1
user14@local:/opt/huawei/tmp [musicdb14]=>select * from test1;
 id |  name  | age 
----+--------+-----
  1 | user14 |  22
(1 row)

user14@local:/opt/huawei/tmp [musicdb14]=>\d+ test1
                                Table "public.test1"
 Column |         Type          | Modifiers | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+--------------+-------------
 id     | bigint                | not null  | plain    |              | 
 name   | character varying(50) | not null  | extended |              | 
 age    | integer               |           | plain    |              | 
Indexes:
    "test1_pkey" PRIMARY KEY, btree (id) TABLESPACE day14
Has OIDs: no
Options: orientation=row, compression=no

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

user14@local:/opt/huawei/tmp [musicdb14]=>create table test2(
musicdb14(>       id bigint,
musicdb14(>       name varchar(50) not null,  -- 创建列级not null约束
musicdb14(>       age  int,
musicdb14(>       primary key(id) -- 创建表级约束
musicdb14(>      );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test2_pkey" for table "test2"
CREATE TABLE
user14@local:/opt/huawei/tmp [musicdb14]=>insert into test2 values(1,'user14',22);
INSERT 0 1
user14@local:/opt/huawei/tmp [musicdb14]=>select * from test2;
 id |  name  | age 
----+--------+-----
  1 | user14 |  22
(1 row)

user14@local:/opt/huawei/tmp [musicdb14]=> \d+ test2
                                Table "public.test2"
 Column |         Type          | Modifiers | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+--------------+-------------
 id     | bigint                | not null  | plain    |              | 
 name   | character varying(50) | not null  | extended |              | 
 age    | integer               |           | plain    |              | 
Indexes:
    "test2_pkey" PRIMARY KEY, btree (id) TABLESPACE day14
Has OIDs: no
Options: orientation=row, compression=no

为表的属性定义默认值

user14@local:/opt/huawei/tmp [musicdb14]=>create table test3(
musicdb14(>       id bigint,
musicdb14(>       name varchar(28) not null,
musicdb14(>       age  int default 20,  --  为该列定义默认值为20
musicdb14(>       primary key(id)
musicdb14(>      );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test3_pkey" for table "test3"
CREATE TABLE
user14@local:/opt/huawei/tmp [musicdb14]=>insert into test3(id,name) values(1,'user14');
INSERT 0 1
user14@local:/opt/huawei/tmp [musicdb14]=>insert into test3(id,name) values(2,'user144');
INSERT 0 1
user14@local:/opt/huawei/tmp [musicdb14]=>select * from test3;
 id |  name   | age 
----+---------+-----
  1 | user14  |  20
  2 | user144 |  20
(2 rows)

user14@local:/opt/huawei/tmp [musicdb14]=> \d+ test3
                                Table "public.test3"
 Column |         Type          | Modifiers  | Storage  | Stats target | Description 
--------+-----------------------+------------+----------+--------------+-------------
 id     | bigint                | not null   | plain    |              | 
 name   | character varying(28) | not null   | extended |              | 
 age    | integer               | default 20 | plain    |              | 
Indexes:
    "test3_pkey" PRIMARY KEY, btree (id) TABLESPACE day14
Has OIDs: no
Options: orientation=row, compression=no

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

user14@local:/opt/huawei/tmp [musicdb14]=> create table test4(
musicdb14(>        id bigint,
musicdb14(>        name varchar(50) not null,
musicdb14(>        age  int,                                
musicdb14(>        primary key(id)
musicdb14(>       );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test4_pkey" for table "test4"
CREATE TABLE
user14@local:/opt/huawei/tmp [musicdb14]=> insert into test4(id,name) values(1,'user1');
INSERT 0 1
user14@local:/opt/huawei/tmp [musicdb14]=> select * from test4;
 id | name  | age 
----+-------+-----
  1 | user1 |    
(1 row)

user14@local:/opt/huawei/tmp [musicdb14]=> \d+ test4
                                Table "public.test4"
 Column |         Type          | Modifiers | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+--------------+-------------
 id     | bigint                | not null  | plain    |              | 
 name   | character varying(50) | not null  | extended |              | 
 age    | integer               |           | plain    |              | 
Indexes:
    "test4_pkey" PRIMARY KEY, btree (id) TABLESPACE day14
Has OIDs: no
Options: orientation=row, compression=no

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

user14@local:/opt/huawei/tmp [musicdb14]=>create table test5(invoicenum serial NOT NULL,name varchar(20));
NOTICE:  CREATE TABLE will create implicit sequence "test5_invoicenum_seq" for serial column "test5.invoicenum"
CREATE TABLE
user14@local:/opt/huawei/tmp [musicdb14]=>insert into test5(name) values('user1');
INSERT 0 1
user14@local:/opt/huawei/tmp [musicdb14]=>insert into test5(name) values('user2');
INSERT 0 1
user14@local:/opt/huawei/tmp [musicdb14]=>insert into test5(name) values('user3');
INSERT 0 1
user14@local:/opt/huawei/tmp [musicdb14]=>select * from test5;
 invoicenum | name  
------------+-------
          1 | user1
          2 | user2
          3 | user3
(3 rows)

user14@local:/opt/huawei/tmp [musicdb14]=>\d+ test5
                                                          Table "public.test5"
   Column   |         Type          |                         Modifiers                          | Storage  | Stats target | D
escription 
------------+-----------------------+------------------------------------------------------------+----------+--------------+--
-----------
 invoicenum | integer               | not null default nextval('test5_invoicenum_seq'::regclass) | plain    |              | 
 name       | character varying(20) |                                                            | extended |              | 
Has OIDs: no
Options: orientation=row, compression=no

使用现有的表创建新表

user14@local:/opt/huawei/tmp [musicdb14]=> CREATE TABLE test61 AS SELECT * FROM test5;
INSERT 0 3
user14@local:/opt/huawei/tmp [musicdb14]=> SELECT * FROM test61;
 invoicenum | name  
------------+-------
          1 | user1
          2 | user2
          3 | user3
(3 rows)

user14@local:/opt/huawei/tmp [musicdb14]=>\d+ test61
                                 Table "public.test61"
   Column   |         Type          | Modifiers | Storage  | Stats target | Description 
------------+-----------------------+-----------+----------+--------------+-------------
 invoicenum | integer               |           | plain    |              | 
 name       | character varying(20) |           | extended |              | 
Has OIDs: no
Options: orientation=row, compression=no

user14@local:/opt/huawei/tmp [musicdb14]=>insert into test61(name) values('user4');
INSERT 0 1
user14@local:/opt/huawei/tmp [musicdb14]=>SELECT * FROM test61;
 invoicenum | name  
------------+-------
          1 | user1
          2 | user2
          3 | user3
            | user4    <=======================
(4 rows)

user14@local:/opt/huawei/tmp [musicdb14]=> CREATE TABLE test62 AS SELECT * FROM test5 WHERE 1=2;
INSERT 0 0
user14@local:/opt/huawei/tmp [musicdb14]=> SELECT * FROM test62;
 invoicenum | name 
------------+------
(0 rows)

user14@local:/opt/huawei/tmp [musicdb14]=>\d+ test62
                                 Table "public.test62"
   Column   |         Type          | Modifiers | Storage  | Stats target | Description 
------------+-----------------------+-----------+----------+--------------+-------------
 invoicenum | integer               |           | plain    |              | 
 name       | character varying(20) |           | extended |              | 
Has OIDs: no
Options: orientation=row, compression=no

user14@local:/opt/huawei/tmp [musicdb14]=> CREATE TABLE test63 AS SELECT * FROM test3 WHERE 1=2;
INSERT 0 0
user14@local:/opt/huawei/tmp [musicdb14]=>
user14@local:/opt/huawei/tmp [musicdb14]=>\d+ test3
                                Table "public.test3"
 Column |         Type          | Modifiers  | Storage  | Stats target | Description 
--------+-----------------------+------------+----------+--------------+-------------
 id     | bigint                | not null   | plain    |              | 
 name   | character varying(28) | not null   | extended |              | 
 age    | integer               | default 20 | plain    |              | 
Indexes:
    "test3_pkey" PRIMARY KEY, btree (id) TABLESPACE day14
Has OIDs: no
Options: orientation=row, compression=no

user14@local:/opt/huawei/tmp [musicdb14]=>\d+ test63
                               Table "public.test63"
 Column |         Type          | Modifiers | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+--------------+-------------
 id     | bigint                |           | plain    |              | 
 name   | character varying(28) |           | extended |              | 
 age    | integer               |           | plain    |              | 
Has OIDs: no
Options: orientation=row, compression=no

注：CTAS 建表，并未带原有表的特别约束。