暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / openGauss每日一练 第14天 | openGauss逻辑结构：表管理2

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

原创 xiaocx 2022-12-07
418

学习目标

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

课程学习

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@opengauss ~]$ gsql -r
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 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
DROP TABLE
omm=# create table test( id bigint primary key, name varchar(50) not null,age  int);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
omm=#   insert into test values(1,'user1',50);
INSERT 0 1
omm=#  select * from test;
 id | name  | age 
----+-------+-----
  1 | user1 |  50
(1 row)

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列）。
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;
NOTICE:  table "test001" does not exist, skipping
DROP TABLE
omm=# create table test001(id bigint,name varchar(50) not null,age  int,primary key(id));
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.为表的属性定义默认值

--执行下面的语句，在创建表的时候为表的某个列定义默认值：
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;

执行过程如下：

omm=# drop table if exists test002;
NOTICE:  table "test002" does not exist, skipping
DROP TABLE
omm=# create table test002(id bigint,name varchar(28) not null,age  int default 20,primary key(id));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test002_pkey" for table "test002"
CREATE TABLE
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=# 
omm=# select * from test002;
 id | name  | age 
----+-------+-----
  1 | user1 |  20
  2 | user2 |  20
(2 rows)

omm=#


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;

执行过程如下：

omm=# drop table if exists test;
DROP TABLE
omm=# create table test(id bigint,name varchar(50) not null,age int,primary key(id));
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=#


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

    商品票据的编号通常按顺序递增。这种情况可以使用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;

执行过程如下：

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=# 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=# select * from invoice;
 invoicenum | name  
------------+-------
          1 | user1
          2 | user2
          3 | user3
(3 rows)

omm=#

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;


执行过程如下：

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

create table txiaocx( id bigint primary key, name varchar(50) not null,age int);
insert into txiaocx values(1,'user1',50);
select * from txiaocx;
\d txiaocx
执行过程如下：

omm=# create table txiaocx( id bigint primary key, name varchar(50) not null,age  int);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "txiaocx_pkey" for table "txiaocx"
CREATE TABLE
omm=# insert into txiaocx values(1,'user1',50);
INSERT 0 1
omm=# select * from txiaocx;
 id | name  | age 
----+-------+-----
  1 | user1 |  50
(1 row)

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

omm=#

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

create table txiaocx1(id bigint,name varchar(50) not null,age int,primary key(id));
insert into txiaocx1 values(1,'user1',50);
select * from txiaocx1;
\d txiaocx1
执行过程如下：

omm=# create table txiaocx1(id bigint,name varchar(50) not null,age  int,primary key(id));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "txiaocx1_pkey" for table "txiaocx1"
CREATE TABLE
omm=# insert into txiaocx1 values(1,'user1',50);
INSERT 0 1
omm=# select * from txiaocx1;
 id | name  | age 
----+-------+-----
  1 | user1 |  50
(1 row)

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

omm=#


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

create table txiaocx2(id bigint,name varchar(28) not null,age int default 20,primary key(id));
insert into txiaocx2(id,name) values(1,'user1');
insert into txiaocx2(id,name) values(2,'user2');
select * from txiaocx2;
执行过程如下：

omm=# create table txiaocx2(id bigint,name varchar(28) not null,age  int default 20,primary key(id));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "txiaocx2_pkey" for table "txiaocx2"
CREATE TABLE
omm=# insert into txiaocx2(id,name) values(1,'user1');
INSERT 0 1
omm=# insert into txiaocx2(id,name) values(2,'user2');
INSERT 0 1
omm=# select * from txiaocx2;
 id | name  | age 
----+-------+-----
  1 | user1 |  20
  2 | user2 |  20
(2 rows)

omm=#


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

drop table if exists txiaocx3;
create table txiaocx3(id bigint,name varchar(50) not null,age int,primary key(id));
insert into txiaocx3(id,name) values(1,'user1');
select * from txiaocx3;
执行过程如下：

omm=# create table txiaocx3(id bigint,name varchar(50) not null,age int,primary key(id));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "txiaocx3_pkey" for table "txiaocx3"
CREATE TABLE
omm=# insert into txiaocx3(id,name) values(1,'user1');
INSERT 0 1
omm=# select * from txiaocx3;
 id | name  | age 
----+-------+-----
  1 | user1 |    
(1 row)

omm=#


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

drop table if exists txiaocx4;
create table txiaocx4(invoicenum serial NOT NULL,name varchar(20));
insert into txiaocx4(name) values('user1');
insert into txiaocx4(name) values('user2');
insert into txiaocx4(name) values('user3');
select * from txiaocx4;
执行过程如下：

omm=# create table txiaocx4(invoicenum serial NOT NULL,name varchar(20));
NOTICE:  CREATE TABLE will create implicit sequence "txiaocx4_invoicenum_seq" for serial column "txiaocx4.invoicenum"
CREATE TABLE
omm=# insert into txiaocx4(name) values('user1');
INSERT 0 1
omm=# insert into txiaocx4(name) values('user2');
INSERT 0 1
omm=# insert into txiaocx4(name) values('user3');
INSERT 0 1
omm=# select * from txiaocx4;
 invoicenum | name  
------------+-------
          1 | user1
          2 | user2
          3 | user3
(3 rows)

omm=#


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

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

omm=#  CREATE TABLE txiaocx4_new AS SELECT * FROM txiaocx4;
INSERT 0 3
omm=#  SELECT * FROM txiaocx4_new;
 invoicenum | name  
------------+-------
          1 | user1
          2 | user2
          3 | user3
(3 rows)

--执行下面的SQL语句，创建和旧表的表结构相同的新表，但是不会将旧表的数据拷贝给新表：
omm=#  DROP TABLE if exists txiaocx4_new;
DROP TABLE
omm=#  CREATE TABLE txiaocx4_new AS SELECT * FROM txiaocx4 WHERE 1=2;
INSERT 0 0
omm=#  SELECT * FROM txiaocx4_new;
 invoicenum | name 
------------+------
(0 rows)

omm=#


墨力计划opengaussopengauss每日一练我和opengauss的故事opengauss训练营
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

openGauss每日一练（共22篇）
openGauss每日一练

评论