openGauss每日一练第14天 - 学习心得体会

原创尚雷 2022-12-07

1442

一、学习目标

本节课是本次实训的第14节课，本节课的重点依然是表相关的知识。重点是学习表的约束、表的默认值以及表的自增类型等知识。

1.1 表约束

约束，是对表中数据进行限定今儿保证数据的正确性、有效性和完整性，openGauss数据库常用的约束有以下几类：

主键约束：primary key --NOT NULL 和 UNIQUE 的结合，确保某列（两个或多个列的结合）有唯一标识

非空约束：not null --指示某列不能存储NULL值

唯一约束：unique --确保某列的值都是唯一的

外键约束：foreign key --保证一个表中的数据匹配另一个表中的值的参照完整性

CHECK：保证列中的值符合指定的条件。

表可以在使用create table初始建表时使用约束条件，也可以在表创建后使用alter table来添加或修改表约束。

约束有表约束和列约束。
列约束相关语法：

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression ) |
  DEFAULT default_expr |
  UNIQUE index_parameters |
  PRIMARY KEY index_parameters }
[ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

表约束相关语法：

[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) |
  UNIQUE ( column_name [, ... ] ) index_parameters |
  PRIMARY KEY ( column_name [, ... ] ) index_parameters |
  PARTIAL CLUSTER KEY ( column_name [, ... ] ) }
[ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

1.2 列默认值

默认值（Default）的完整称呼是"默认值约束（Default Constraint）"，用来指定某列的默认值。即在表中插入一条新记录时，如果没有为某个字段赋值，系统就会自动为这个字段插入默认值。

可以在创建表和修改表时来设置字段默认值。

创建表时可以使用 DEFAULT 关键字设置默认值约束，具体的语法格式如下：
<字段名> <数据类型> DEFAULT <默认值>;
其中，“默认值”为该字段设置的默认值，如果是字符类型的，要用单引号括起来。

修改表时添加默认值约束的语法格式如下：
ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> <数据类型> DEFAULT <默认值>;

1.3 自增类型

在设计数据库的表结构时，经常会使用一个自动增长的数字序列作为主键字段（代理主键）。除了作为主键使用之外，自增字段也可以用于记录各个操作发生的先后顺序,当插入一行数据时，数据库会为自增字段生成一个新的数值。

不同的数据库会选择采用不同的方式来执行字段值的自增。

– 对于openGauss自增有如下说明
postgresql(openGauss)数据库可以创建主键，但是没有像mysql那样直接指定主键自增的auto_increment关键字，在postgresql(openGauss)中是无法使用auto_increment关键字。postgresql(openGauss)通过序列来实现。序列可以实现自动增长，而且表字段可以指定默认值，因此可以通过指定默认值的方式来实现主键自增。

postgresql提供了三种serial类型：smallserial,serial,bigserial，但这三种不是真正的类型，而是在创建唯一标识符列的标志以方便使用。bigserial会创建一个bigint类型的自增，serial用以创建一个int类型的自增，依次类推。

二、测试练习

2.1 建表时定义列级约束

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=# \dt
omm=# No relations found.
-- 创建表con_tab01并为表中字段定义primary key和not null列约束
create table con_tab01(
  id bigint primary key, 
  name varchar(50) not null,
  age  int);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "con_tab01_pkey" for table "con_tab01"
CREATE TABLE
omm=# insert into con_tab01 values(1,'shanglei',18);
INSERT 0 1
omm=# select * from con_tab01;
 id |   name   | age 
----+----------+-----
  1 | shanglei |  18
(1 row)

-- 查看表结构
omm=# \d con_tab01
          Table "public.con_tab01"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | bigint                | not null
 name   | character varying(50) | not null
 age    | integer               | 
Indexes:
    "con_tab01_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default

2.2 建表时定义表级约束

-- 建表时指定表级和列级约束
omm=# create table con_tab02(
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 "con_tab02_pkey" for table "con_tab02"
CREATE TABLE
omm=# insert into con_tab02 values(1,'shanglei',18);
INSERT 0 1
omm=# insert into con_tab02 values(2,'shlei6067',18);
omm=# INSERT 0 1

omm=# select * from con_tab02;
 id |   name    | age 
----+-----------+-----
  1 | shanglei  |  18
  2 | shlei6067 |  18
(2 rows)

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

2.3 建表时指定默认值

2.3.1 建表时设置列默认值非空

-- 建表时为 age 列指定了默认值 20
omm=# create table con_tab03(
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 "con_tab03_pkey" for table "con_tab03"
CREATE TABLE
omm=# insert into con_tab03(id,name) values(1,'shanglei');
INSERT 0 1
omm=# insert into con_tab03(id,name) values(2,'shlei6067');
omm=# INSERT 0 1

-- 查询到的con_tab03表，age列都是20
omm=# select * from con_tab03;
 id |   name    | age 
----+-----------+-----
  1 | shanglei  |  20
  2 | shlei6067 |  20
(2 rows)

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

2.3.2 建表时设置列默认值为null

-- 建表时设置 age 列为null
omm=# create table con_tab04(
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 "con_tab04_pkey" for table "con_tab04"
CREATE TABLE
omm=# insert into con_tab04(id,name) values(1,'shanglei');
INSERT 0 1
omm=# insert into con_tab04(id,name) values(2,'shlei6067');
INSERT 0 1
-- 可以看到查询的数据age列都为null
omm=# select * from con_tab04;
 id |   name    | age 
----+-----------+-----
  1 | shanglei  |    
  2 | shlei6067 |    
(2 rows)

2.4 建表时设置自增类型

-- 使用serial关键字来进行自增
omm=# create table con_tab05(
omm(#    invoicenum serial NOT NULL,
omm(#    name varchar(20));
NOTICE:  CREATE TABLE will create implicit sequence "con_tab05_invoicenum_seq" for serial column "con_tab05.invoicenum"
CREATE TABLE
omm=# insert into con_tab05(name) values('A');
INSERT 0 1
omm=# insert into con_tab05(name) values('B');
INSERT 0 1
omm=# insert into con_tab05(name) values('C');
INSERT 0 1
omm=# select * from con_tab05;
 invoicenum | name 
------------+------
          1 | A
          2 | B
          3 | C
(3 rows)

omm=# \d con_tab05
 name       | character varying(20) | 

omm=#                                       Table "public.con_tab05"
   Column   |         Type          |                           Modifiers                            
------------+-----------------------+----------------------------------------------------------------
 invoicenum | integer               | not null default nextval('con_tab05_invoicenum_seq'::regclass)

2.5 使用现有的表创建新表

使用现有表来创建新表，通常有个称呼叫CTAS(create table as)。

omm=# create table con_tab_new as select * from con_tab05;
INSERT 0 3
omm=# \d con_tab_new
           Table "public.con_tab_new"
   Column   |         Type          | Modifiers 
------------+-----------------------+-----------
 invoicenum | integer               | 
 name       | character varying(20) | 

omm=# \d con_tab05 


omm=#                                       Table "public.con_tab05"
   Column   |         Type          |                           Modifiers                            
------------+-----------------------+----------------------------------------------------------------
 invoicenum | integer               | not null default nextval('con_tab05_invoicenum_seq'::regclass)
 name       | character varying(20) | omm=# 

omm=# select * from con_tab_new;
 invoicenum | name 
------------+------
          1 | A
          2 | B
          3 | C
(3 rows)

omm=# select * from con_tab05;
 invoicenum | name 
------------+------
          1 | A
          2 | B
          3 | C
(3 rows)

三、学习心得

今天的知识看似较少，其实深究扩展研究起来，知识点其实蛮多，特别是在表的约束方面，要深入学习，因为这影响到以后SQL的质量。

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

关注作者

openGauss每日一练第14天 - 学习心得体会

一、学习目标

1.1 表约束

1.2 列默认值

1.3 自增类型

二、测试练习

2.1 建表时定义列级约束

2.3 建表时指定默认值

2.3.1 建表时设置列默认值非空

2.3.2 建表时设置列默认值为null

2.4 建表时设置自增类型

2.5 使用现有的表创建新表

三、学习心得

评论