第14天：openGauss逻辑结构：表管理2

1.创建表的时候定义列级约束
create table tab_newtest1(id int primary key,name varchar(200));

omm=# \l
                              List of databases
   Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges 
-----------+-------+----------+-------------+-------------+-------------------
 enmdb     | hc    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 musicdb1  | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 newdb1    | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 newdb2    | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 newdb3    | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 omm       | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/omm           +
           |       |          |             |             | omm=CTc/omm
 template1 | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/omm           +
           |       |          |             |             | omm=CTc/omm
(9 rows)

omm=# create table tab_newtest1(id int primary key,name varchar(200));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tab_newtest1_pkey" for table "tab_newtest1"
CREATE TABLE
omm=# \dt+
                                        List of relations
 Schema |     Name     | Type  | Owner |  Size   |             Storage              | Description 
--------+--------------+-------+-------+---------+----------------------------------+-------------
 public | tab_newtest1 | table | omm   | 0 bytes | {orientation=row,compression=no} | 
 public | tab_t1       | table | omm   | 0 bytes | {orientation=row,compression=no} | 
(2 rows)

2.创建表的时候定义表级约束
create table tab_newtest2(id int ,name varchar(200),primary key(id));

omm=# create table tab_newtest2(id int ,name varchar(200),primary key(id));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tab_newtest2_pkey" for table "tab_newtest2"
CREATE TABLE
omm=# \dt+
                                        List of relations
 Schema |     Name     | Type  | Owner |  Size   |             Storage              | Description 
--------+--------------+-------+-------+---------+----------------------------------+-------------
 public | tab_newtest1 | table | omm   | 0 bytes | {orientation=row,compression=no} | 
 public | tab_newtest2 | table | omm   | 0 bytes | {orientation=row,compression=no} | 
 public | tab_t1       | table | omm   | 0 bytes | {orientation=row,compression=no} | 
(3 rows)



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

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

create table tab_newtest3(id int ,name varchar(200) not null default '',primary key(id));

omm=# create table tab_newtest3(id int ,name varchar(200) not null default '',primary key(id));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tab_newtest3_pkey" for table "tab_newtest3"
CREATE TABLE
omm=# \d tab_newtest3
                       Table "public.tab_newtest3"
 Column |          Type          |               Modifiers                
--------+------------------------+----------------------------------------
 id     | integer                | not null
 name   | character varying(200) | not null default ''::character varying
Indexes:
    "tab_newtest3_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default

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

create table tab_newtest3(id int ,name varchar(200));

omm=# drop table if exists tab_newtest3;
DROP TABLE
omm=# create table tab_newtest3(id int ,name varchar(200));
CREATE TABLE
omm=# \d tab_newtest3
         Table "public.tab_newtest3"
 Column |          Type          | Modifiers 
--------+------------------------+-----------
 id     | integer                | 
 name   | character varying(200) | 

omm=# \d+ tab_newtest3
                             Table "public.tab_newtest3"
 Column |          Type          | Modifiers | Storage  | Stats target | Description 
--------+------------------------+-----------+----------+--------------+-------------
 id     | integer                |           | plain    |              | 
 name   | character varying(200) |           | extended |              | 
Has OIDs: no
Options: orientation=row, compression=no

omm=# insert into tab_newtest3(id) values(2);
INSERT 0 1
omm=# select * from tab_newtest3;
 id | name 
----+------
  2 | 
(1 row)

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

create table tab_newtest4(id int serial,name varchar(200));

omm=# create table tab_newtest4(id  serial not null,name varchar(200));
NOTICE:  CREATE TABLE will create implicit sequence "tab_newtest4_id_seq" for serial column "tab_newtest4.id"
CREATE TABLE
omm=# \d+ tab_newtest4
                                                     Table "public.tab_newtest4"
 Column |          Type          |                         Modifiers                         | Storage  | Stats target | Descri
ption 
--------+------------------------+-----------------------------------------------------------+----------+--------------+-------
------
 id     | integer                | not null default nextval('tab_newtest4_id_seq'::regclass) | plain    |              | 
 name   | character varying(200) |                                                           | extended |              | 
Has OIDs: no
Options: orientation=row, compression=no

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

create table tab_newtest41 as select * from pg_database;
create table tab_newtest42 as select * from pg_class where 1=2;

omm=# create table tab_newtest41 as select * from pg_database;
create table tab_newtest42 as select * from pg_class where 1=2;INSERT 0 9
omm=# create table tab_newtest42 as select * from pg_class where 1=2;
INSERT 0 0
omm=# \dt tab_newtest41
                             List of relations
 Schema |     Name      | Type  | Owner |             Storage              
--------+---------------+-------+-------+----------------------------------
 public | tab_newtest41 | table | omm   | {orientation=row,compression=no}
(1 row)

omm=# \d tab_newtest4 
                                 Table "public.tab_newtest4"
 Column |          Type          |                         Modifiers                         
--------+------------------------+-----------------------------------------------------------
 id     | integer                | not null default nextval('tab_newtest4_id_seq'::regclass)
 name   | character varying(200) | 

omm=# \d tab_newtest41
       Table "public.tab_newtest41"
      Column      |   Type    | Modifiers 
------------------+-----------+-----------
 datname          | name      | 
 datdba           | oid       | 
 encoding         | integer   | 
 datcollate       | name      | 
 datctype         | name      | 
omm=# select count(1) from tab_newtest41;
 count 
-------
     9
(1 row)

omm=# select count(1) from tab_newtest42;
 count 
-------
     0
(1 row)