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

学习目标

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

课程学习

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

复制

 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;

复制

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


复制

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;

复制

课程作业

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

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=# drop table if exists test;
NOTICE: table “test” does not exist, skipping
DROP TABLE
omm=# omm(# create table test(
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-# insert into test values(1,‘user1’,50);
omm-# select * from test;
ERROR: syntax error at or near “-”
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.创建表的时候定义表级约束

drop table if exists test001;
create table test001(
id bigint,
name varchar(50) not null,   
age int,
primary key(id) 
);
insert into test001 values(1,'user1',500);
select * from test001;
\d test001

复制

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
omm=# insert into test001 values(1,‘user1’,500);
INSERT 0 1
omm=# select * from test001;
id | name | age
----±------±----
1 | user1 | 500
(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, 
primary key(id)
);
-–下面的SQL insert语句，在向表test插入数据时，没有提供age列的值：
insert into test002(id,name) values(1,'user1');
insert into test002(id,name) values(2,'user2');


复制

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=# insert into test002(id,name) values(1,‘user1’);
INSERT 0 1
omm=# insert into test002(id,name) values(2,‘user2’);
INSERT 0 1
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=# id bigint,
omm(# create table test(
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’);
ERROR: type “‘” does not exist
LINE 1: insert into test(id,name) values(1,‘‘user1’);
^
omm=# insert into test(id,name) values(1,‘user1’);
INSERT 0 1
omm=# select * from test;
id | name | age
----±------±----
1 | user1 |
(1 row)

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

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

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

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