openGauss 每日一练第14天

学习目标

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

课程学习

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 

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

--创建表时定义表级约束  执行下面的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) -- 创建表级约束
     );
insert into test001 values(1,'user1',50);
select * from test001;
 \d test001

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

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

  select * from test002;

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

-- 未定义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;

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

-创建一个带有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;

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

--执行下面的SQL语句，将创建新表，并且会将旧表的数据拷贝给新表：
 DROP TABLE if exists newtestwithdata;
 CREATE TABLE newtestwithdata AS SELECT * FROM invoice;
 SELECT * FROM newtestwithdata;

--执行下面的SQL语句，创建和旧表的表结构相同的新表，但是不会将旧表的数据拷贝给新表：
 DROP TABLE if exists testnewwithoutdata;
 CREATE TABLE testnewwithoutdata AS SELECT * FROM invoice WHERE 1=2;
 SELECT * FROM testnewwithoutdata;

课程作业

准备

-- 进入数据库
su - omm
gsql -r

-- 创建表空间和数据库
omm=# create tablespace test_tbs relative location 'tablespaces/test_tbs';
CREATE TABLESPACE

omm=# create  database testdb with tablespace test_tbs ;
CREATE DATABASE
omm=# \l+
                                                          List of databases
   Name    | Owner | Encoding | Collate | Ctype | Access privileges | Size  | Tablespace |                Description                 
-----------+-------+----------+---------+-------+-------------------+-------+------------+--------------------------------------------
 omm       | omm   | UTF8     | C       | C     |                   | 12 MB | pg_default | 
 postgres  | omm   | UTF8     | C       | C     |                   | 12 MB | pg_default | default administrative connection database
 template0 | omm   | UTF8     | C       | C     | =c/omm           +| 12 MB | pg_default | default template for new databases
           |       |          |         |       | omm=CTc/omm       |       |            | 
 template1 | omm   | UTF8     | C       | C     | =c/omm           +| 12 MB | pg_default | unmodifiable empty database
           |       |          |         |       | omm=CTc/omm       |       |            | 
 testdb    | omm   | UTF8     | C       | C     |                   | 12 MB | test_tbs   | 
(5 rows)
-- 进入刚才创建的数据库 testdb
omm=# \c testdb
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "testdb" as user "omm".

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

-- 创建表 test01 定义name 列不能为空（not null）
testdb=# create table test01(id bigint, name varchar(50) not null,age int) ;
testdb=# CREATE TABLE
-- 查看表的详情
\d test01
           Table "public.test01"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | bigint                | 
 name   | character varying(50) | not null
 age    | integer               | 

-- 插入id 1  name 为空 age 为10 测试, 提示name 字段不能为null
testdb=# insert into test01 (id,age) values (1,10);
ERROR:  null value in column "name" violates not-null constraint
DETAIL:  Failing row contains (1, null, 10).

-- 插入正常数据  id 1 name kevin age 10 ，查询后可以看到，数据已被插入到表中
testdb=# insert into test01 values (1,'kevin',10);
INSERT 0 1
testdb=# select * from test01;
testdb=#  id | name  | age 
----+-------+-----
  1 | kevin |  10
(1 row)

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

-- 创建表 test02 ，account 为表级 唯一约束，name 为列不为空约束
testdb=# create table test02( id bigint, account char(30),name char(50) not null, age int,Constraint  un_account  unique(account));
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "un_account" for table "table03"
CREATE TABLE

-- 插入一条account 一样的数据，发现报key account已存在的错误
testdb=# insert into test02 values(2,'account01','user01',18);
INSERT 0 1
testdb=# insert into test02 values(3,'account01','user02',20);
ERROR:  duplicate key value violates unique constraint "un_account"
DETAIL:  Key (account)=(account01                     ) already exists.

-- 插入一条不一样的数据，显示正常。查询库中所有的数据
testdb=# insert into test02 values(4,'account02','user02',20);
INSERT 0 1
testdb=# select * from test02;
 id |            account             |                        name                        | age 
----+--------------------------------+----------------------------------------------------+-----
  2 | account01                      | user01                                             |  18
  4 | account02                      | user02                                             |  20
(2 rows)

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

-- 创建表 test03 ,age 默认值为21
testdb=#  create table test03( id bigint ,name char(50) not null, age int default 21);
CREATE TABLE

testdb=# \d test03
        Table "public.test03"
 Column |     Type      | Modifiers  
--------+---------------+------------
 id     | bigint        | 
 name   | character(50) | not null
 age    | integer       | default 21

-- 测试插入数据 id 1 ，name user01 ,age 不填写，查看数据发现age 自动填充了21

testdb=# insert into test03 (id,name) values (1,'user01');
INSERT 0 1
testdb=# select * from test03 ;
 id |                        name                        | age 
----+----------------------------------------------------+-----
  1 | user01                                             |  21
(1 

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

-- 创建表test04 ，列均未定义默认值
testdb=# create table test04(id int,name varchar(50) not null ,age int);
CREATE TABLE
-- 查看表结构
testdb=# \d test04
           Table "public.test04"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | integer               | 
 name   | character varying(50) | not null
 age    | integer               | 

--  插入一条只有name的 数据，查询表中数据，可以看到只有name 字段有值，其他为null
testdb=# insert into test04 (name) values ('user02');
INSERT 0 1
testdb=# select * from test04;
testdb=#  id |  name  | age 
----+--------+-----
    | user02 |    
(1 row)

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

-- 创建autoid字段为 序列号（SERIAL）类型
testdb=# create table test05(autoid serial not null,name varchar(50));
NOTICE:  CREATE TABLE will create implicit sequence "test05_autoid_seq" for serial column "test05.autoid"

-- 查看表的详细信息
testdb=# \d test05         
                                  Table "public.test05"
 Column |         Type          |                        Modifiers                        
--------+-----------------------+---------------------------------------------------------
 autoid | integer               | not null default nextval('test05_autoid_seq'::regclass)
 name   | character varying(50) | 

-- 插入两条数据，查看是否为递增，从查询结果可以看到，autoid 按顺序递增了
testdb=# insert into test05 (name) values ('user03');
INSERT 0 1

testdb=# insert into test05 (name) values ('user04');
INSERT 0 1
testdb=# select * from test05;
 autoid |  name  
--------+--------
      1 | user03
      2 | user04
(2 rows)

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

-- 从 test05 表 创建新表 test06 
testdb=# create table test06 as select * from test05;
INSERT 0 2

testdb=# \dt   
                         List of relations
 Schema |  Name  | Type  | Owner |             Storage              
--------+--------+-------+-------+----------------------------------
 public | test01 | table | omm   | {orientation=row,compression=no}
 public | test02 | table | omm   | {orientation=row,compression=no}
testdb=#  public | test03 | table | omm   | {orientation=row,compression=no}
 public | test04 | table | omm   | {orientation=row,compression=no}
 public | test05 | table | omm   | {orientation=row,compression=no}
 public | test06 | table | omm   | {orientation=row,compression=no}
(6 rows)

testdb=# 
-- 通过查询可以看到，在创建test06 表的时候将test05的数据也一同带入到了新表中
testdb=# select * from test06;
 autoid |  name  
--------+--------
      1 | user03
      2 | user04
(2 rows)


-- 创建新表 test07 ，其中只需要 name 是 user03 的数据

testdb=# create table test07 as select * from test05 where name='user03';
testdb=# INSERT 0 1

testdb=# \dt
                         List of relations
 Schema |  Name  | Type  | Owner |             Storage              
--------+--------+-------+-------+----------------------------------
 public | test01 | table | omm   | {orientation=row,compression=no}
 public | test02 | table | omm   | {orientation=row,compression=no}
 public | test03 | table | omm   | {orientation=row,compression=no}
 public | test04 | table | omm   | {orientation=row,compression=no}
 public | test05 | table | omm   | {orientation=row,compression=no}
 public | test06 | table | omm   | {orientation=row,compression=no}
 public | test07 | table | omm   | {orientation=row,compression=no}
(7 rows)

-- 从查询结果可以看出 name 是user03 的数据被带过来了，而 user02 的数据在test07 表中并没有
testdb=# select * from test07;
 autoid |  name  
--------+--------
      1 | user03
(1 row)