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

学习目标

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

课程学习

演示：
1.创建表的时候定义列级约束
2.创建表的时候定义表级约束
3.为表的属性定义默认值
4.如果在创建表的时候，没有为某列定义默认值，缺省的默认值是空值null。
5.创建表时使用自增数据类型
6.使用现有的表创建新表

课程作业

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

/*
--创建表的时候为表定义列级约束：
--在列级定义了primary key约束（id列）和not null约束（name列）
drop table if exists test;
create table test(
id bigint primary key, 
name varchar(50) not null,
age  int
);
--插入数据
insert into test values(1,'user1',50);
--查看数据
select * from test;
--查看约束
\d test 
*/


omm=# --在列级定义了primary key约束（id列）和not null约束（name列）
omm=# drop table if exists test;
NOTICE:  table "test" does not exist, skipping
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

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

/*
--在创建表的时候为表定义表级约束：
#这里在表列级定义了primary key约束（id列），在列级定义了not null约束（name列）。
drop table if exists test001;
create table test001(
id bigint,
name varchar(50) not null,  -- 列级not null约束
age  int,
primary key(id)             -- 表级约束
);

insert into test001 values(1,'user1',50);
select * from test001;
\d test001
*/

omm=# drop table if exists test001;
WARNING:  Session unused timeout.
FATAL:  terminating connection due to administrator command
could not send data to server: Broken pipe
The connection to the server was lost. Attempting reset: Succeeded.
omm=# create table test001(
omm(# 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=# 
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

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

/*
--执行下面的语句，在创建表的时候为表的某个列定义默认值：
drop table if exists test002;
create table test002(
id bigint,
name varchar(28) not null,
age  int default 20,                  --为age列定义默认值为20
primary key(id)
);
select * from test002;
--向表test插入数据时，没有提供age列的值， 应该就会使用默认值：
insert into test002(id,name) values(1,'user1');
insert into test002(id,name) values(2,'user2');

select * from test002;
*/

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

omm=# omm=# --向表test插入数据时，没有提供age列的值， 应该就会使用默认值：
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
drop table if exists test003;
create table test003(
id bigint,
name varchar(50) not null,
age  int,                                
primary key(id)
);
insert into test003(id,name) values(1,'user1');
select * from test003;

insert into test003(id,name) values(2,'user2');
insert into test003(id,name,age) values(3,'user3',21);
insert into test003(id,name,age) values(4,'user4',28);
select * from test003 where age is NULL;
select * from test003;
*/


omm=# drop table if exists test003;
NOTICE:  table "test003" does not exist, skipping
DROP TABLE
omm=# create table test003(
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 "test003_pkey" for table "test003"
CREATE TABLE
omm=# insert into test003(id,name) values(1,'user1');
INSERT 0 1
omm=# select * from test003;
 id | name  | age 
----+-------+-----
  1 | user1 |    
(1 row)
omm=# insert into test003(id,name) values(2,'user2');
INSERT 0 1
omm=# insert into test003(id,name,age) values(3,'user3',21);
INSERT 0 1
omm=# insert into test003(id,name,age) values(4,'user4',28);
INSERT 0 1
omm=# select * from test003 where age is NULL;
 id | name  | age 
----+-------+-----
  1 | user1 |    
  2 | user2 |    
(2 rows)

omm=# select * from test003;
 id | name  | age 
----+-------+-----
  1 | user1 |    
  2 | user2 |    
  3 | user3 |  21
  4 | user4 |  28
(4 rows)

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

/*
--创建一个带有serial数据类型的测试表invoice：
drop table if exists invoice;
create table invoice(invoicenum serial NOT NULL,name varchar(20));
--为表invoice插入3条记录，并查看插入数据后的表的数据：
insert into invoice(name) values('user1');
insert into invoice(name) values('user2');
insert into invoice(name) values('user3');

--查看表invoice列invoicenum的值会自增1。
select * from 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
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=# 
omm=# --查看表invoice列invoicenum的值会自增1。
omm=# select * from invoice;
 invoicenum | name  
------------+-------
          1 | user1
          2 | user2
          3 | user3
(3 rows)

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

/*
--创建新表，并且会将旧表的数据拷贝给新表：
DROP TABLE if exists newtestwithdata;
CREATE TABLE newtestwithdata AS SELECT * FROM invoice;
SELECT * FROM newtestwithdata;

--创建和旧表的表结构相同的新表，但不传递旧表的数据给新表：
DROP TABLE if exists testnewwithoutdata;
CREATE TABLE testnewwithoutdata AS SELECT * FROM invoice WHERE 1=2;
SELECT * FROM testnewwithoutdata;
*/

omm=# omm=# --创建新表，并且会将旧表的数据拷贝给新表：
DROP TABLE if exists newtestwithdata;
NOTICE:  table "newtestwithdata" does not exist, skipping
omm=# DROP TABLE
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=# omm=# --创建和旧表的表结构相同的新表，但不传递旧表的数据给新表：
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)