openGauss每日一练第 14天 |表的约束、表的默认值、自增类型

openGauss每日一练第14天

今日目标：openGauss的逻辑结构：表的约束、表的默认值、自增类型等技术

create table

CREATE [ [ GLOBAL | LOCAL ] [ TEMPORARY | TEMP ] | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
({ column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ … ] ]
| table_constraint
| LIKE source_table [ like_option […] ] }
[, … ])
[ AUTO_INCREMENT [ = ] value ]
[ WITH ( {storage_parameter = value} [, … ] ) ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ COMPRESS | NOCOMPRESS ]
[ TABLESPACE tablespace_name ]
[ COMMENT {=| } ‘text’ ];

复制表结构及数据

复制表结构

create table as select * from where 1=2;

复制表结构和数据

create table as select * from ;

gsql常用命令

使用SchemaName.TableName的表标识方法访问表来指定在哪个模式下创建、插入、查询表。

查看库

\l

查看表空间

\db

查看用户

\du

查看模式

\dn+

查看表

\dt //查看当前库所有的表 list tables

\dt <表名> //查看指定表的信息

查看模式的搜索路径

show search_path;

查看约束

\d[S+] // list tables, views, and sequences
\d[S+] NAME // describe table, view, sequence, or index

查看sequences

\ds[S+] [PATTERN] //list sequences

设置模式的搜索路径

会话级模式搜索顺序的优先级最高，用户级模式搜索顺序的优先级第2，数据库级模式搜索顺序的优先级最低。

• 会话级别（已登录数据库）

  set search_path to <模式名>

• 数据库级别设置模式搜索顺序

  ALTER DATABASE <数据库> SET SEARCH_PATH TO <模式名>;

• 用户级别 设置模式搜索顺序

  ALTER USER <用户名> SET SEARCH_PATH TO <模式名>;

2.课后作业

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

2.1.1 查看表信息

\dt 查看表

或者使用如下方式

select table_catalog,table_schema,table_name from information_schema.tables;

2.1.2 查看约束

\d

– 查看表test的约束

\d <表名>

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


omm=# \dt test
                        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.2 创建表的时候定义表级约束

omm=# drop table if exists test001;
NOTICE:  table "test001" does not exist, skipping
DROP TABLE
omm=# create table test001(
omm(# id bigint,
omm(# name varchar(200) 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,'test001-user1',20)
;omm-# ;
INSERT 0 1
omm=# select * from test001 ;
omm=#  id |     name      | age 
----+---------------+-----
  1 | test001-user1 |  20
(1 row)


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

omm=# 

2.3 为表的属性定义默认值

omm=# create table test002(
omm(# id bigint,
omm(# name varchar(200) 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 values (1,'test002-user1');
INSERT 0 1
omm=# select * from test002;
 id |     name      | age 
----+---------------+-----
  1 | test002-user1 |  20
(1 row)

omm=# \d+ test002
                                Table "public.test002"
 Column |          Type          | Modifiers  | Storage  | Stats target | Description 
--------+------------------------+------------+----------+--------------+-------------
 id     | bigint                 | not null   | plain    |              | 
 name   | character varying(200) | not null   | extended |              | 
 age    | integer                | default 20 | plain    |              | 
Indexes:
    "test002_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

omm=# 

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

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

omm=# 

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

SERIAL // 四字节序列整型。-2,147,483,648 ~ +2,147,483,647

– 查看 sequences

\ds[S+] [PATTERN] list sequences

使用serial数据类型时，会默认创建一个sequence

omm=# create table invoice(invoicenum serial not null ,name varchar(200));
NOTICE:  CREATE TABLE will create implicit sequence "invoice_invoicenum_seq" for serial column "invoice.invoicenum"
CREATE TABLE
omm=# insert into invoice (name) values ('test1');
omm=# INSERT 0 1

omm=# insert into invoice (name) values ('test2');
omm=# INSERT 0 1
omm=# insert into invoice (name) values('test3');
INSERT 0 1
omm=# select * from invoice;
 invoicenum | name  
------------+-------
          1 | test1
          2 | test2
          3 | test3
(3 rows)

omm=# \d+ invoice
 invoicenum | integer                | not null default nextval('invoice_invoicenum_seq'::regclass) | plain    |              
| 
 name       | character varying(200) |                                                              | extended |              
| 
Has OIDs: no
Options: orientation=row, compression=no

                                                           Table "public.invoice"
   Column   |          Type          |                          Modifiers                           | Storage  | Stats target 
| Description 
------------+------------------------+--------------------------------------------------------------+----------+--------------
+-------------
omm=# 
omm=# \ds
                      List of relations
 Schema |          Name          |   Type   | Owner | Storage 
--------+------------------------+----------+-------+---------
 public | invoice_invoicenum_seq | sequence | omm   | 
(1 row)

omm=# \d+ invoice
                                                           Table "public.invoice"
   Column   |          Type          |                          Modifiers                           | Storage  | Stats target 
| Description 
------------+------------------------+--------------------------------------------------------------+----------+--------------
+-------------
 invoicenum | integer                | not null default nextval('invoice_invoicenum_seq'::regclass) | plain    |              
| 
 name       | character varying(200) |                                                              | extended |              
| 
Has OIDs: no
Options: orientation=row, compression=no

omm=# \ds+ invoice_invoicenum_seq 
                                    List of relations
 Schema |          Name          |   Type   | Owner |    Size    | Storage | Description 
--------+------------------------+----------+-------+------------+---------+-------------
 public | invoice_invoicenum_seq | sequence | omm   | 8192 bytes |         | 
(1 row)

omm=# 

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

复制表结构

create table as select * from where 1=2;

复制表结构和数据

create table as select * from ;

-- 创建新表，并且会将旧表的数据拷贝给新表：
omm=# select * from invoice;
 invoicenum | name  
------------+-------
          1 | test1
          2 | test2
          3 | test3
(3 rows)

omm=# create table new_invoice as select * from invoice;    
INSERT 0 3
omm=# \dt
                            List of relations
 Schema |    Name     | Type  | Owner |             Storage              
--------+-------------+-------+-------+----------------------------------
 public | test002     | table | omm   | {orientation=row,compression=no}
 public | test003     | table | omm   | {orientation=row,compression=no}
 public | test4       | table | omm   | {orientation=row,compression=no}
(7 rows)

omm=#  public | invoice     | table | omm   | {orientation=row,compression=no}
 public | new_invoice | table | omm   | {orientation=row,compression=no}
 public | test        | table | omm   | {orientation=row,compression=no}
 public | test001     | table | omm   | {orientation=row,compression=no}

omm=# select * from new_invoice ;
 invoicenum | name  
------------+-------
          1 | test1
          2 | test2
          3 | test3
(3 rows)

omm=# \d+ new_invoice  
                               Table "public.new_invoice"
   Column   |          Type          | Modifiers | Storage  | Stats target | Description 
omm=# ------------+------------------------+-----------+----------+--------------+-------------
 invoicenum | integer                |           | plain    |              | 
 name       | character varying(200) |           | extended |              | 
Has OIDs: no
Options: orientation=row, compression=no
-- 创建和旧表的表结构相同的新表，但是不会将旧表的数据拷贝给新表
omm=# 

mm=# create table new_invoice2 as select * from invoice where 1=2;
INSERT 0 0
omm=# select * from new_invoice2;
 invoicenum | name 
------------+------
(0 rows)

omm=# \d new_invoice2
           Table "public.new_invoice2"
   Column   |          Type          | Modifiers 
------------+------------------------+-----------
 invoicenum | integer                | 
 name       | character varying(200) | 

omm=#