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

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

openGauss逻辑结构：表管理2

学习目标

学习表的约束、表的默认值、自增类型等技术

课程学习

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

-- 创建表的时候为表定义列级约束：在列级定义了primary key约束（id列）和not null约束（name列）。
omm=# drop table if exists test;
DROP 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=# 

--插入数据
omm=# insert into test values(1,'user1',50);
INSERT 0 1
omm=# 

--查看数据
omm=# select * from test;
 id | name  | age 
----+-------+-----
  1 | user1 |  50
(1 row)

omm=# 

--查看约束
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.创建表的时候定义表级约束

--创建表时定义表级约束  执行下面的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(
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=# 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=#

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

omm=# 

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

-- 未定义age列的默认值，如果插入数据时未提供该列的值，则将默认插入空值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=# 

1. 创建表时使用自增数据类型
   商品发票的编号通常按顺序递增。这种情况可以使用serial数据类型。最简单方法直接使用serial数据类型。

--创建一个带有serial数据类型的测试表invoice：
omm=# drop table if exists invoice;
NOTICE:  table "invoice" does not exist, skipping
DROP TABLE
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=# 

--为表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
omm=# 

--可以看到每插入一条记录到表invoice后，列invoicenum的值会自增1。
omm=# select * from invoice;
 invoicenum | name  
------------+-------
          1 | user1
          2 | user2
          3 | user3
(3 rows)

omm=# 

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)

omm=# 

--执行下面的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)

omm=#

课程作业

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

omm=# drop table if exists lxs1;
NOTICE:  table "lxs1" does not exist, skipping
DROP TABLE
omm=# create table lxs1(
omm(#  id int not null,
omm(#  info text
omm(# );
CREATE TABLE
omm=# insert into lxs1 values(1,'Hello openGaussdb!');
INSERT 0 1
omm=# select * from lxs1;
 id |        info        
----+--------------------
  1 | Hello openGaussdb!
(1 row)

omm=# \d lxs1
      Table "public.lxs1"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | not null
 info   | text    | 

omm=# 

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

omm=# drop table if exists lxs2;
NOTICE:  table "lxs2" does not exist, skipping
DROP TABLE
omm=# create table lxs2(
omm(#  id int,
omm(#  info text,
omm(#  primary key(id)
omm(# );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "lxs2_pkey" for table "lxs2"
CREATE TABLE
omm=# insert into lxs2 values(2,'Hello openGaussdb!');
INSERT 0 1
omm=# select * from lxs2;
 id |        info        
----+--------------------
  2 | Hello openGaussdb!
(1 row)

omm=# \d lxs2
     Table "public.lxs2"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | not null
 info   | text    | 
Indexes:
    "lxs2_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default

omm=# 

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

omm=# drop table if exists lxs3;
NOTICE:  table "lxs3" does not exist, skipping
DROP TABLE
omm=# create table lxs3(
omm(#  id int,
omm(#  info text default 'Hello openGaussdb!'
omm(# );
CREATE TABLE
omm=# insert into lxs3(id) values(31);
INSERT 0 1
omm=# insert into lxs3 values(32,'Hello openGaussdb for id32!');
INSERT 0 1
omm=# select * from lxs3;
 id |            info             
----+-----------------------------
 31 | Hello openGaussdb!
 32 | Hello openGaussdb for id32!
(2 rows)

omm=# \d lxs3
                  Table "public.lxs3"
 Column |  Type   |             Modifiers              
--------+---------+------------------------------------
 id     | integer | 
 info   | text    | default 'Hello openGaussdb!'::text

omm=# 

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

omm=# drop table if exists lxs4;
NOTICE:  table "lxs4" does not exist, skipping
DROP TABLE
omm=# create table lxs4(
omm(#  id int,
omm(#  info text
omm(# );
CREATE TABLE
omm=# insert into lxs4(id) values(41);
INSERT 0 1
omm=# insert into lxs4 values(42,'Hello openGaussdb!');
INSERT 0 1
omm=# select * from lxs4;
 id |        info        
----+--------------------
 41 | 
 42 | Hello openGaussdb!
(2 rows)
    
omm=# \d lxs4
     Table "public.lxs4"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | 
 info   | text    | 

omm=# 

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

omm=# drop table if exists lxs5;
NOTICE:  table "lxs5" does not exist, skipping
DROP TABLE
omm=# create table lxs5(
omm(#  id serial ,
omm(#  info text
omm(# );
NOTICE:  CREATE TABLE will create implicit sequence "lxs5_id_seq" for serial column "lxs5.id"
CREATE TABLE
omm=# insert into lxs5(info) values('Hello openGaussdb!');
INSERT 0 1
omm=# insert into lxs5(info) values('Hello openGaussdb!');
INSERT 0 1
omm=# insert into lxs5(info) values('Hello openGaussdb!');
INSERT 0 1
omm=# select * from lxs5;
 id |        info        
----+--------------------
  1 | Hello openGaussdb!
  2 | Hello openGaussdb!
  3 | Hello openGaussdb!
(3 rows)

omm=# \d lxs5
                         Table "public.lxs5"
 Column |  Type   |                     Modifiers                     
--------+---------+---------------------------------------------------
 id     | integer | not null default nextval('lxs5_id_seq'::regclass)
 info   | text    | 

omm=# 

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

omm=# drop table if exists lxs6;
DROP TABLE
omm=# NOTICE:  table "lxs6" does not exist, skipping
create table lxs6 as select * from lxs5;
INSERT 0 3
omm=# select * from lxs6;
 id |        info        
----+--------------------
  1 | Hello openGaussdb!
  2 | Hello openGaussdb!
  3 | Hello openGaussdb!
(3 rows)

omm=# \d lxs6
     Table "public.lxs6"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | 
 info   | text    | 

omm=# 
omm=# drop table if exists lxs7;
NOTICE:  table "lxs7" does not exist, skipping
DROP TABLE
omm=# create table lxs7 as select * from lxs5 where 1=2;
INSERT 0 0
omm=# select * from lxs7;
 id | info 
----+------
(0 rows)

omm=# \d lxs7
     Table "public.lxs7"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | 
 info   | text    | 

omm=# 

–学如逆水行舟，不进则退。