openGauss开源数据库实战十二

任务十二 openGauss逻辑结构:表管理

任务目标

本任务的目标是熟练掌握关系表的管理,包括创建表、在创建表时定义约束(列级约束和表级约束)、修改表(添加字段、删除字段、添加约束、删除约束、修改数据类型、修改字段的名字、修改字段的默认值)。

实施步骤

一、准备工作

使用Linux用户omm打开一个Linux终端窗口,执行如下的命令,创建表空间ustbs、数据库ustbdb、用户temp,并授予用户temp SYSADMIN权限:

gsql -d postgres -p 26000 -r
CREATE TABLESPACE ustb_ts RELATIVE LOCATION 'tablespace/ustb_ts1';
CREATE DATABASE ustbdb WITH TABLESPACE = ustb_ts;
CREATE USER temp IDENTIFIED BY 'temp@ustb2020';
ALTER USER temp  SYSADMIN;
\q

使用Linux用户omm,另外打开一个Linux终端窗口,以数据库用户temp的身份,连接到刚刚创建的数据库ustbdb:

gsql -d ustbdb -h 192.168.100.91 -U temp -p 26000 -W temp@ustb2020 -r

执行下面的SQL语句,创建模式jtjsj和jtxa:

create schema jtjsj;
create schema jtxa;

二、创建表

1.新建表默认保存在public模式中

新创建的表默认保存在数据库的public模式中。执行下面的命令,查看当前的模式搜索路径:

show SEARCH_PATH;

注意：需要确保新创建的表在数据库的public模式中。
否则需要手动修改，操作如下：

gsql -d postgres -p 26000 -r
ALTER USER temp SET SEARCH_PATH TO public;

执行下面的SQL语句,创建一个测试表testtable,并插入一条数据:

drop table if exists testtable;
create table testtable(col varchar(100));
insert into testtable values('Hello from testtable!');
select * from testtable;

执行下面的SQL语句,查看当前数据库ustbdb的public模式中有哪些表:

select table_catalog,table_schema,table_name,table_type
from information_schema.tables 
where table_schema = 'public';

实验结论:默认情况下,在某个数据库上创建的数据库对象(本例是表testtable),都位于该数据库的public模式中。

2.在一个数据库的不同模式下创建表

执行下面的命令,查看当前的模式搜索路径:

show SEARCH_PATH;

SET SEARCH_PATH TO jtjsj;

show SEARCH_PATH;

执行下面的SQL语句,在数据库ustbdb的模式jtjsj中创建表testtable、testtable1、testtable2:

create table testtable(col1  char(100));
create table testtable1(col1 char(100));
create table testtable2(col1 char(100));

可以看出,在不同的模式(public和jtjsj)中可以创建同名的表testtable(其定义可以不同,一个列的数据类型是varchar(100),另一个列的数据类型是char(100))。
执行下面的语句,为模式jtjsj下的表testtable插人一条测试数据(由于当前的模式搜索路径为模式jtjsj,因此不需要指定模式名,就可以为jtjsj.testtable插入新行):

insert into testtable values('Hello from testtable IN SCHEMA jtjsj!');
select * from testtable;

虽然当前模式搜索路径没有模式jxa,但是我们仍然可以在模式jxa中创建表,方法是使用SchemaName.TableName的方式来指定在哪个模式下创建表。下面的实验演示了这一点。
执行下面的SOL语句,将在数据库ustbdb的模式jtxa中创建表testtable、testtable1、testtable2,并向模式jtxa中新创建的表testtable插人一条数据:

create table jtxa.testtable(col1  char(100));
create table jtxa.testtable1(col1 char(100));
create table jtxa.testtable2(col1 char(100));
insert into jtxa.testtable values('Hello from testtable IN SCHEMA jtis!');
select * from jtxa.testtable;
\q

最后再次实验一下openGauss在某个用户连接到某个数据库时,可以访问该数据库中不同模式下的数据表。使用Linux用户omm,另外打开一个Linux终端窗口,执行如下命令,以数据库用户temp的身份连接到数据库ustbdb:

gsql -d ustbdb -h 192.168.100.91 -U temp -p 26000 -W temp@ustb2020 -r

--  查看当前会话的模式搜索顺序
show SEARCH_PATH;
--  查看不同模式下的表
select * from testtable;
select * from jtjsj.testtable;
select * from jtxa.testtable;
\q

3.创建表的时候定义约束

(1)创建表时定义列级约束执行下面的命令和SQL语句,体验一下在创建表的时候为表定义列级约束:

gsql -d ustbdb -h 192.168.100.91 -U temp -p 26000 -W temp@ustb2020 -r

drop table if exists test;
create table test(
      id bigint primary key,      -- 创建列级主键约束
      name varchar(50) not null,  -- 创建列级not null约束
      age  int
     );
insert into test values(1,'temp',50);
select * from test;

这个例子中,我们在列级定义了主键约束(id列)和NOTNULL约束(name列)
(2)创建表时定义表级约束执行下面的SQL语句,体验一下在创建表的时候为表定义表级约束:

drop table if exists test;
create table test(
      id bigint,
      name varchar(50) not null,  -- 创建列级not null约束
      age  int,
   -- 创建表级约束
      primary key(id)
     );
insert into test values(1,'temp',50);
select * from test;

这里在表级定义了主键约束(id列),在列级定义了NOTNULL约束(name列)。在定义单列约束时,表级约束和列级约束没有区别,但无法在表级定义作用在多列上的约束。
(3)为表的属性定义默认值 执行下面的语句,体验一下在创建表的时候为表的某个列定义默认值:

drop table if exists test;
create table test(
      id bigint,
      name varchar(50) not null,
      age  int default 20,  --  为该列定义默认值为20
                         --  如果插入数据时未提供该列的值，将默认插入20
      primary key(id)
     );

insert into test(id,name) values(1,'temp');
select * from test;

可以看到,插人数据时虽然没有提供age列的值,但结果显示为该行的age列提供了默认值20。如果在创建表的时候,没有为某列定义默认值,缺省的默
认值是空值null。执行下面的SQL语句可以验证这一点:

drop table if exists test;
create table test(
      id bigint,
      name varchar(50) not null,
      age  int,           --  未定义该列的默认值
                         --  如果插入数据时未提供该列的值，将默认插入空值null
      primary key(id)
     );
insert into test(id,name) values(1,'temp');
select * from test;

可以看到,插入数据时没有为age列提供具体的值,并且没有显式定义age列的默认值,结果显示新行的age列的值是空值 null。

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

发票的编号通常按顺序递增,这种情况可以使用serial数据类型。有两种方法可以完成编号顺序递增的任务。
第一种方法是直接使用serial数据类型。执行下面的SQL语句,创建一个带有serial数据类型的测试表invoice:

drop table if exists invoice;
create table invoice(invoicenum serial NOT NULL,name varchar(20));

执行下面的SQL语句,为表invoice插入3条记录,并查看插入数据后表的数据:

insert into invoice(name) values('temp');
insert into invoice(name) values('temp1');
insert into invoice(name) values('temp2');
select * from invoice;

可以看到,每插人一条记录到表invoice后,invoicenum列的值会自增1。

第二种方法是先创建一个系列,然后将表列的默认值设置为该系列的下一个值。执行下面的语句,首先创建一个系列:

DROP SEQUENCE if exists invoicenum_seq;
CREATE SEQUENCE invoicenum_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

创建表的时候,指定某个列的默认值为该系列下一个要取的值:

DROP TABLE if exists invoice;
create table invoice(
    invoicenum bigint DEFAULT nextval('invoicenum_seq'),
    name varchar(20)
   );

执行下面的命令,插人一些数据并进行查看:

insert into invoice(name) values('temp');
insert into invoice(name) values('temp1');
insert into invoice(name) values('temp2');
select * from invoice;

可以看到,每插入一条记录到表invoice后,invoicenum列的值会自增1。

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

执行下面的SQL语句将创建新表,并且会将旧表的数据拷贝给新表:

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

执行下面的SQL语句,将创建新表,并且不会将旧表的数据拷贝给新表:

DROP TABLE if exists testnewwithoutdata;
CREATE TABLE testnewwithoutdata AS SELECT * FROM test WHERE 1=2;
SELECT * FROM testnewwithoutdata;

注意:CREATETABLE语句中的WHERE子句,其谓词条件1=2在逻辑上永远为假,因此不会有表中的任何行数据满足谓词要求,最终结果自然是创建了一个空表。

三、查看表的信息

首先创建一个测试表:

drop table if exists test;
create table test(
      id bigint,
      name varchar(50) not null,
      age  int default 20,
      primary key(id)
     );

1.在gsql中查看表的定义

在gsql中,使用\dtableNmae命令可以查看表的信息。
执行下面的gsql元命令,查看表test的信息:

\d test

2.查看当前数据库中有哪些模式属于某个用户

执行下面的SQL语句,查看属于用户temp的模式有哪些:

SELECT catalog_name,schema_name,schema_owner 
FROM information_schema.schemata 
WHERE schema_owner='temp';

3.查看模式搜索路径下有哪些表

执行下面的gsql元命令,查看模式搜索路径下有哪些表:

\dt

4.查看某个模式下有哪些表

执行下面的SQL语句,查看模式jtsj下的所有表名:

SELECT table_name FROM information_schema.tables WHERE table_schema='jtjsj';

执行下面的SQL语句,查看模式jtxa下的所有表名:

SELECT table_name FROM information_schema.tables WHERE table_schema='jtxa';
\q

5.查看一个表下有哪些约束

使用gsql的元命令\d tableName可以很方便地查看一个表下有哪些约束。示例如下:

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r

\d instructor

也可以使用下面的SQL语句来查看表instructor下的约束:

select conname, connamespace, contype, conkey     
from pg_constraint 
where conrelid in (  select oid
                  from pg_class
                  where relname='instructor');

6.查看一个表属于哪个数据库的哪个模式

执行下面的SQL语句,查看表instructor属于哪个数据库的哪个模式:

\x on
SELECT * FROM information_schema.tables WHERE table_name='instructor';
\x off

可以看出,表instructor属于数据库studentdb的public模式。

四、修改表

首先创建一个测试表:

drop table if exists test;
create table test(
      id bigint,
      name varchar(50) not null,
      age  int default 20,
      primary key(id)
     );

1.为表添加字段

执行下面的gsql元命令,查看表test的信息:

\d test

执行下面的SOL语句,为表test新增一列,列名为sex,数据类型为boolean:

alter table test add column sex Boolean;

执行下面的gsql元命令,查看表test的信息:
```sql
\d test

2.删除表中的已有字段

执行下面的SOL语句,删除刚刚添加的sex列:

alter table test drop column sex ;

执行下面的gsql元命令,再次查看表test的信息:

\d test

可以看到,sex列已经从表test中消失了。

3.删除表的已有约束

执行下面的gsql元命令,查看表test的信息:

\d test

可以看到,表test下有一个名为testpkey的主键约束。执行下面的SQL语句,删除这个约束:

alter table test drop constraint test_pkey;

执行下面的gsql元命令,再次查看表test的信息:

\d test

我们发现表test下已经没有了testpkey这个主键约束。

4.为表添加约束

执行下面的SQL命令,为表test添加刚刚删除的主键约束:

alter table test add constraint test_pkey primary key(id);

执行下面的gsql元命令,再次查看表test的信息:

\d test

可以看到,已经为表test的id列重新添加了名为test_pkey的主键约束。

5.修改表中字段的默认值

执行下面的gsql元命令,查看表test的信息:

\d test

可以看到,age当前的默认值是20。
尝试插人一条记录,但不提供age的值,看看效果:

insert into test(id,name) values(10,'temp');
select * from test;

执行下面的SQL语句,将age的默认值变更为25:

alter table test alter column age set default 25;

执行下面的gsql元命令,再次查看表test的信息:

\d test

输出显示,agc的默认值已经变更为25了。
再次尝试插入一条记录,但是不提供age的值,进行测试:

insert into test(id,name) values(5,'temp');
select * from test;

输出表明,尽管插入数据时没有提供age的值,但在将记录插入数据库时,还是为该记录的age提供了默认值25。
我们也可以删除默认值。删除默认值,将导致默认值为NULL。执行下面的语句,删除age的默认值:

alter table test alter column age DROP default;

执行下面的gsql元命令,再次查看表test的信息:

\d test

可以看到,表test的age没有默认值了。执行下面的语句,插人一条新的记录,还是不提供age的值,进行测试:

insert into test(id,name) values(6,'temp');
select * from test;

可以看到,刚刚执行的语句由于没有提供age的值,因此将新行的age值存储为NULL

6.修改表中字段的数据类型

我们可以修改表中字段的数据类型。执行下面的语句,将age列的数据类型由int变更为bigint:

alter table test ALTER COLUMN age TYPE bigint;

修改数据类型时,如果一些行在该列上已经有值了,那么修改后的数据类型应能与现有的值相兼容,并且存储空间也必须能够容纳原有的值(修改后数据类型的存储长度需要足够大)。

7.修改表中字段的名字

我们可以修改表中字段的名字。执行下面的SOL语句,将age列的名字变更为stuage:

ALTER TABLE test RENAME COLUMN age TO stuage;

执行下面的gsql元命令,再次查看表test的信息:

\d test

我们看到,表中age列的名字已经被更名成stuage。

8.修改表的名字

我们可以修改表的名字。执行下面的SQL语句,将表test的名字变更为mytest:

\dt
ALTER TABLE test RENAME TO mytest;
\dt

可以看到,表test的名字已经被变更为mytest。

五、清除表中的数据

有时候我们需要保留一个表的定义,但是要把该表中的数据都删除。这可以通过执行数据操纵语言(DML)的DELETE语句来完成。但因为DELETE语句是DML语句,会生成很多操作日志,如果表的行数比较多,执行速度会比较慢。
使用TRUNCATE TABLE语句可以快速将一个有很多数据的表置成空表,因为TRUNCATE TABLE语句是数据定义语言(DDL)语句。
执行下面的SQL语句,将表test的内容清空(保留表test的结构):

select * from mytest;
truncate table mytest;
\dt mytest
select * from mytest;

六、删除表

使用DROPTABLE语句删除表的话,不但会删除表中的数据,而且会将表的定义删除。示例如下:

\dt
DROP TABLE mytest;
\dt
\q

七、任务的扫尾工作

在继续后面的任务之前,打开另外一个Linux终端窗口,执行下面的命令,做以下清理工作:

gsql -d postgres -p 26000 -r

drop database ustbdb;
drop user temp;
drop tablespace ustb_ts;
\q