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

课程学习

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

– 创建表的时候为表定义列级约束：在列级定义了primary key约束（id列）和not null约束（name列）。

omm=# drop table if exists test;
NOTICE:  table "test" does not exist, skipping
omm=# DROP TABLE
  create table test(
omm(#        id bigint primary key, 
omm(# omm(# age  int
 name varchar(50) not null,
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

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

–创建表时定义表级约束 执行下面的SQL语句，在创建表的时候为表定义表级约束：
#这里在表列级定义了primary key约束（id列），在列级定义了not null约束（name列）。

omm=# drop table if exists test001;
NOTICE:  table "test001" does not exist, skipping
DROP TABLE
omm=# omm(# create table test001(
      id bigint,
omm(#       name varchar(50) not null,  -- 创建列级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=# select * from test001;
omm=# 
 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

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

–执行下面的语句，在创建表的时候为表的某个列定义默认值：

omm=# drop table if exists test002;
NOTICE:  table "test002" does not exist, skipping
DROP TABLE
omm=# create table test002(
omm(#       id bigint,
omm(#       name varchar(28) not null,
omm(#       age  int default 20,  --  为该列定义默认值为20
omm(#       primary key(id)
omm(#      );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test002_pkey" for table "test002"
CREATE TABLE
omm=# 

–下面的SQL insert语句，在向表test插入数据时，没有提供age列的值：

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=# 
omm=#   select * from test002;
 id | name  | age 
----+-------+-----
  1 | user1 |  20
  2 | user2 |  20
(2 rows)

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

– 未定义age列的默认值，如果插入数据时未提供该列的值，则将默认插入空值null

omm=# drop table if exists test;
DROP TABLE
omm(# omm=#  create table test(
       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)

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

商品票的编号通常按顺序递增。这种情况可以使用serial数据类型。最简单方法直接使用serial数据类型。
-创建一个带有serial数据类型的测试表invoice：

omm=# drop table if exists invoice;
NOTICE:  table "invoice" does not exist, skipping
DROP TABLE
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

–为表invoice插入3条记录，并查看插入数据后的表的数据：

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

–可以看到每插入一条记录到表invoice后，列invoicenum的值会自增1。

omm=# select * from invoice;
 invoicenum | name  
------------+-------
          1 | user1
          2 | user2
          3 | user3
(3 rows)

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

–执行下面的SQL语句，将创建新表，并且会将旧表的数据拷贝给新表：

omm=#  DROP TABLE if exists newtestwithdata;
NOTICE:  table "newtestwithdata" does not exist, skipping
DROP TABLE
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)

–执行下面的SQL语句，创建和旧表的表结构相同的新表，但是不会将旧表的数据拷贝给新表：

omm=# DROP TABLE if exists testnewwithoutdata;
NOTICE:  table "testnewwithoutdata" does not exist, skipping
DROP TABLE
omm=#  CREATE TABLE testnewwithoutdata AS SELECT * FROM invoice WHERE 1=2;
INSERT 0 0
omm=#  SELECT * FROM testnewwithoutdata;
 invoicenum | name 
------------+------
(0 rows)

课程作业

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

omm=# create table zhxl1(id int,name char(30) not null);
CREATE TABLE
omm=# insert into zhxl1 values(1,'enmogdb');
INSERT 0 1

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

omm=# create table enm(id int,name char(30) not null,primary key (id));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "enm_pkey" for table "enm"
CREATE TABLE
omm=# insert into enm values (1,'test primary');
INSERT 0 1
omm=# select * from enm;
 id |              name              
----+--------------------------------
  1 | test primary                  
(1 row)

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

omm=# create table enm2(id int,col char(20),col2 char(2) default '01');
CREATE TABLE
omm=# insert into enm2 (id,col) values (1,'3434');
omm=# INSERT 0 1

omm=# select * from enm2;
 id |         col          | col2 
----+----------------------+------
  1 | 3434                 | 01
(1 row)

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

omm=# create table enm3(id int,col char(10),col2 char(10));
CREATE TABLE
omm=# insert into enm3(id,col) values(1,'00');
omm=# INSERT 0 1
select * from enm3;
 id |    col     | col2 
----+------------+------
  1 | 00         | 
(1 row)

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

omm=# create table enm4(id serial NOT NULL,name varchar(10));
NOTICE:  CREATE TABLE will create implicit sequence "enm4_id_seq" for serial column "enm4.id"
CREATE TABLE
omm=# insert into enm4 (name) values('t1'),('t2');
INSERT 0 2
omm=# select * from enm4;
 id | name 
----+------
  1 | t1
  2 | t2
(2 rows)

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

omm=# create table enm5 as select * from enm4;
INSERT 0 2
omm=# select * from enm5;
 id | name 
----+------
  1 | t1
  2 | t2
(2 rows)
omm=# create table enm6 as select * from enm4 where id=1;
INSERT 0 1
omm=# select * from enm6;
omm=#  id | name 
----+------
  1 | t1
(1 row)

omm=# create table enm7 as select id,name,0 as age from enm4;
omm=# INSERT 0 2

omm=# select * from enm7;
 id | name | age 
----+------+-----
  1 | t1   |   0
  2 | t2   |   0
(2 rows)

omm=# \d enm7
            Table "public.enm7"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | integer               | 
 name   | character varying(10) | 
 age    | integer