课程作业

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

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.
-- 创建表的时候为表定义列级约束：在列级定义了primary key约束（id列）和not null约束（name列）。
omm=# create table zouyang(id int primary key,name char(100) not null,age int);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "zouyang_pkey" for table "zouyang"
CREATE TABLE
--插入数据
omm=# insert into zouyang values(1,'zouyang',35);
INSERT 0 1
--查看数据
omm=# select * from zouyang;
id | name | age
----+------------------------------------------------------------------------------------------------------+-----
1 | zouyang | 35
(1 row)

--查看约束
omm=# \d zouyang
omm=# Table "public.zouyang"
Column | Type | Modifiers
--------+----------------+-----------
id | integer | not null
name | character(100) | not null
age | integer |
Indexes:
"zouyang_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default

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

--创建表级约束primary key
omm=# create table zouyang001(id int ,name char(100) not null,age int,primary key(id));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "zouyang001_pkey" for table "zouyang001"
CREATE TABLE
omm=# insert into zouyang001 values(1,'zouyang',35);
INSERT 0 1
omm=# insert into zouyang001 values(2,'zouyang2',35);
INSERT 0 1
omm=# select * from zouyang001;
id | name | age
----+------------------------------------------------------------------------------------------------------+-----
1 | zouyang | 35
2 | zouyang2 | 35
(2 rows)

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

--为age列定义默认值20
omm=# create table zouyang002(id int primary key,name char(100) not null,age int default 20 --默认值是20);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "zouyang002_pkey" for table "zouyang002"
CREATE TABLE
omm=# insert into zouyang002 values(1,'zouyang');
INSERT 0 1
omm=# insert into zouyang002 values(2,'zouyang1');
INSERT 0 1
omm=# select * from zouyang002;
id | name | age
----+------------------------------------------------------------------------------------------------------+-----
1 | zouyang | 20
2 | zouyang1 | 20
(2 rows)

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

--没有为age列定义默认值
omm=# create table zouyang003(id int primary key,name char(100) not null,age int);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "zouyang003_pkey" for table "zouyang003"
CREATE TABLE
omm=# insert into zouyang003 values(1,'zouyang');
omm=# INSERT 0 1

omm=# select * from zouyang003;
id | name | age
----+------------------------------------------------------------------------------------------------------+-----
1 | zouyang |
(1 row)

--结论：没有为列age定义默认值时，age的默认值是空值null

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

--serial:自增类型,顺序递增
omm=# create table zouyang004(id serial NOT NULL,name varchar(20));
NOTICE: CREATE TABLE will create implicit sequence "zouyang004_id_seq" for serial column "zouyang004.id"
CREATE TABLE
omm=# insert into zouyang004(name) values('zouyang1');
INSERT 0 1
omm=# insert into zouyang004(name) values('zouyang2');
INSERT 0 1
omm=# insert into zouyang004(name) values('zouyang3');
INSERT 0 1
omm=# select * from zouyang004;
id | name
----+----------
1 | zouyang1
2 | zouyang2
3 | zouyang3
(3 rows)
--结论：可以看到每插入一条记录到表zouyang004后，列id的值会自增1。

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

--执行下面的SQL语句，将创建新表，并且会将旧表的数据拷贝给新表：
omm=# create table newzouyang as select * from zouyang004;
INSERT 0 3
omm=# select * from newzouyang;
id | name
----+----------
1 | zouyang1
2 | zouyang2
3 | zouyang3
(3 rows)
--执行下面的SQL语句，创建和旧表的表结构相同的新表，但是不会将旧表的数据拷贝给新表：
omm=# create table newzouyang2 as select * from zouyang004 where 1=2;
INSERT 0 0
omm=# select * from newzouyang2;
id | name
----+------
(0 rows)