openGauss每日一练第 14 天 | openGauss 3.0.0数据库在线实训课程

原创 shunwah 2022-12-07

259

> 作者：马顺华

> 从事运维管理工作多年，目前就职于某科技有限公司，熟悉运维自动化、OceanBase部署运维、MySQL 运维以及各种云平台技术和产品。并已获得OceanBase认证OBCA、OBCP证书。

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

学习目标

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

课程学习

openGauss逻辑结构：表管理2

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

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
);

root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:00 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

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

2）-- 插入数据

insert into test values(1,‘user1’,50);

omm=# insert into test values(1,'user1',50);
INSERT 0 1
omm=#

3）-- 查看数据

select * from test;

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

omm=#

4）-- 查看约束

\d test

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

1）-- 创建表时定义表级约束执行下面的SQL语句，在创建表的时候为表定义表级约束：

#这里在表列级定义了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) – 创建表级约束
);

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(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

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

omm=# insert into test001 values(1,'user1',50);
omm=# INSERT 0 1
omm=# 

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.为表的属性定义默认值

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

drop table if exists test002;
create table test002(
id bigint,
name varchar(28) not null,
age int default 20,
primary key(id)
);

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=#

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

insert into test002(id,name) values(1,‘user1’);
insert into test002(id,name) values(2,‘user2’);

select * from test002;

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。

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

drop table if exists test;
create table test(
id bigint,
name varchar(50) not null,
age int,
primary key(id)
);
insert into test(id,name) values(1,‘user1’);
select * from test;

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=#

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

2）-- 创建一个带有serial数据类型的测试表invoice：

drop table if exists invoice;
create table invoice(invoicenum serial NOT NULL,name varchar(20));

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=#

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

insert into invoice(name) values(‘user1’);
insert into invoice(name) values(‘user2’);
insert into invoice(name) values(‘user3’);

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

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

select * from invoice;

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

omm=#

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

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

DROP TABLE if exists newtestwithdata;
CREATE TABLE newtestwithdata AS SELECT * FROM invoice;
SELECT * FROM newtestwithdata;

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=#

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

DROP TABLE if exists testnewwithoutdata;
CREATE TABLE testnewwithoutdata AS SELECT * FROM invoice WHERE 1=2;
SELECT * FROM testnewwithoutdata;

omm=# DROP TABLE if exists testnewwithoutdata;
NOTICE:  table "testnewwithoutdata" does not exist, skipping
omm=# 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.创建表的时候定义列级约束

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

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

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

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

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

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

root@modb:~# 
root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:00 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=# drop table if exists test_1;
NOTICE:  table "test_1" does not exist, skipping
DROP TABLE
omm=# create table test_1(
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_1_pkey" for table "test_1"
CREATE TABLE

omm=# insert into test_1 values(1,'user1',50);
INSERT 0 1
omm=# select * from test_1;
 id | name  | age 
----+-------+-----
  1 | user1 |  50
(1 row)

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

omm=#

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

omm=# create table test_2(
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_2_pkey" for table "test_2"
CREATE TABLE

omm=# 
omm=# insert into test_2 values(1,'user1',50);
omm=# INSERT 0 1
omm=# 

omm=# select * from test_2;
 id | name  | age 
----+-------+-----
  1 | user1 |  50
(1 row)

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

omm=#

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

omm=# drop table if exists test_3;
NOTICE:  table "test_3" does not exist, skipping
DROP TABLE
omm=# create table test_3(
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 "test_3_pkey" for table "test_3"
CREATE TABLE
omm=# insert into test_3(id,name) values (1,'user1');
INSERT 0 1
omm=# insert into test_3(id,name) values (2,'user2');
INSERT 0 1
omm=# select * from test_3;
 id | name  | age 
----+-------+-----
  1 | user1 |  20
  2 | user2 |  20
(2 rows)

omm=#

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

omm=# drop table if exists test_4;
NOTICE:  table "test_4" does not exist, skipping
DROP TABLE
omm=# create table test_4(
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_4_pkey" for table "test_4"
CREATE TABLE
omm=# insert into test_4(id,name) values (1,'user1');
INSERT 0 1
omm=# select * from test_4;
 id | name  | age 
----+-------+-----
  1 | user1 |    
(1 row)

omm=#

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

omm=# drop table if exists test_5;
NOTICE:  table "test_5" does not exist, skipping
DROP TABLE
omm=# create  table test_5(invoicenum serial not null,name varchar(20));
NOTICE:  CREATE TABLE will create implicit sequence "test_5_invoicenum_seq" for serial column "test_5.invoicenum"
CREATE TABLE
omm=# insert into test_5(name) values ('user1');
INSERT 0 1
omm=# insert into test_5(name) values ('user2');
INSERT 0 1
omm=# insert into test_5(name) values ('user3');
INSERT 0 1
omm=# select * from test_5;
 invoicenum | name  
------------+-------
          1 | user1
          2 | user2
          3 | user3
(3 rows)

omm=#

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

omm=# drop table if exists test_6;
NOTICE:  table "test_6" does not exist, skipping
DROP TABLE
omm=# create table test_6 as select * from test_5;
INSERT 0 3
omm=# select * from test_6;
 invoicenum | name  
------------+-------
          1 | user1
          2 | user2
          3 | user3
(3 rows)

omm=#

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

omm=#

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

openGauss每日一练第 14 天 | openGauss 3.0.0数据库在线实训课程

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

学习目标

课程学习

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

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

2）-- 插入数据

3）-- 查看数据

4）-- 查看约束

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

1）-- 创建表时定义表级约束 执行下面的SQL语句，在创建表的时候为表定义表级约束：

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

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

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

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

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

2）-- 创建一个带有serial数据类型的测试表invoice：

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

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

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

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

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

课程作业

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

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

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

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

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

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

评论

1）-- 创建表时定义表级约束执行下面的SQL语句，在创建表的时候为表定义表级约束：