一、学习目标
本节课是本次实训的第十六节课，本次课的重点依然是表管理相关的知识。
表是存放数据的载体，目前所有的关系数据库基本上都是用表来存储数据，日常对数据库的维护其实更多还维护表及表中的数据，所以要想学好数据库，就要深入学习表相关知识。
看一下本节课的内容，主要是基于这几个知识点：创建和删除表、为表新增和修改字段、查看表结构、表约束的创建和删除及查看表约束。
对于日常运维openGauss，要熟练使用如下语句：
1) 查看数据库中包含的表
例如，在PG_TABLES系统表中查看public schema中包含的所有表
SELECT distinct(tablename) FROM pg_tables WHERE SCHEMANAME = 'public';
2) 查看数据库用户
通过PG_USER可以查看数据库中所有用户的列表，还可以查看用户ID（USESYSID）和用户权限
SELECT * FROM pg_user;
3)
查看正在运行的查询语句。以查看正在运行的查询语句所连接的数据库名、执行查询的用户、查询状态及查询对应的PID
SELECT datname, usename, state,pid FROM pg_stat_activity;
4) 查看非空闲查询语句
SELECT datname, usename, state FROM pg_stat_activity WHERE state != 'idle';
二、测试练习
2.1 创建表和添加添加字段
2.1.1 创建表
[omm@opengauss-node1 ~]$ gsql -d postgres -p 26000 -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.
-- 使用SELECT datname FROM pg_database语句查询当前有哪些数据库
openGauss=# SELECT datname FROM pg_database;
datname
-----------
template1
presdb
musicdb10
template0
musicdb2
postgres
(6 rows)
-- 切换到presdb数据库
openGauss=# \c presdb
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "presdb" as user "omm".
-- 创建customer_tb1表，表中含有自增类型字段、默认值字段、主键
presdb=# CREATE TABLE customer_tb1
presdb-# (
presdb(# c_customer_sk integer,
presdb(# c_customer_id serial not null,
presdb(# c_first_name char(20),
presdb(# c_last_name char(20),
presdb(# c_gender char(10) default 'male',
presdb(# primary key(c_customer_sk)
presdb(# );
NOTICE: CREATE TABLE will create implicit sequence "customer_tb1_c_customer_id_seq" for serial column "customer_tb1.c_customer_id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "customer_tb1_pkey" for table "customer_tb1"
CREATE TABLE
presdb=# INSERT INTO customer_tb1 (c_customer_sk, c_first_name,c_last_name) VALUES (2369, 'shang','lei');
INSERT 0 1
presdb=# INSERT INTO customer_tb1 (c_customer_sk, c_first_name,c_last_name) VALUES (7981, 'zhang','san');
INSERT 0 1
presdb=# select * from customer_tb1;
c_customer_sk | c_customer_id | c_first_name | c_last_name | c_gender
---------------+---------------+----------------------+----------------------+------------
2369 | 1 | shang | lei | male
7981 | 2 | zhang | san | male
(2 rows)
-- 查看customer_tb1表结构
presdb=# \d customer_tb1
Table "public.customer_tb1"
Column | Type | Modifiers
---------------+---------------+----------------------------------------------------------------------
c_customer_sk | integer | not null
c_customer_id | integer | not null default nextval('customer_tb1_c_customer_id_seq'::regclass)
c_first_name | character(20) |
c_last_name | character(20) |
c_gender | character(10) | default 'male'::bpchar
Indexes:
"customer_tb1_pkey" PRIMARY KEY, btree (c_customer_sk) TABLESPACE pg_default
presdb=# CREATE INDEX idx_cus_tb1_c_gender ON customer_tb1 (c_gender) LOCAL;
ERROR: non-partitioned table does not support local partitioned indexes
-- 为表字段c_gender创建索引
presdb=# CREATE INDEX idx_cus_tb1_c_gender ON customer_tb1 (c_gender);
CREATE INDEX
-- 查询索引结构
presdb=# \di idx_cus_tb1_c_gender
List of relations
Schema | Name | Type | Owner | Table | Storage
--------+----------------------+-------+-------+--------------+---------
public | idx_cus_tb1_c_gender | index | omm | customer_tb1 |
(1 row)
2.1.2 添加字段
-- 为表customer_tb1 新增两个字段 age 和 address
presdb=# alter table customer_tb1 add column age integer;
ALTER TABLE
presdb=# alter table customer_tb1 add column age integer;
ERROR: column "age" of relation "customer_tb1" already exists
presdb=# alter table customer_tb1 add column address char(100);
ALTER TABLE
-- 查看表结构
presdb=# \d customer_tb1
Table "public.customer_tb1"
Column | Type | Modifiers
---------------+----------------+----------------------------------------------------------------------
c_customer_sk | integer | not null
c_customer_id | integer | not null default nextval('customer_tb1_c_customer_id_seq'::regclass)
c_first_name | character(20) |
c_last_name | character(20) |
c_gender | character(10) | default 'male'::bpchar
age | integer |
address | character(100) |
Indexes:
"customer_tb1_pkey" PRIMARY KEY, btree (c_customer_sk) TABLESPACE pg_default
"idx_cus_tb1_c_gender" btree (c_gender) TABLESPACE pg_default
presdb=# select * from customer_tb1;
c_customer_sk | c_customer_id | c_first_name | c_last_name | c_gender | age | address
---------------+---------------+----------------------+----------------------+------------+-----+---------
2369 | 1 | shang | lei | male | |
7981 | 2 | zhang | san | male | |
(2 rows)
2.1.3 删除字段
-- 删除创建的address字段
presdb=# alter table customer_tb1 drop column address;
ALTER TABLE
presdb=# \d customer_tb1
Table "public.customer_tb1"
Column | Type | Modifiers
---------------+---------------+----------------------------------------------------------------------
c_customer_sk | integer | not null
c_customer_id | integer | not null default nextval('customer_tb1_c_customer_id_seq'::regclass)
c_first_name | character(20) |
c_last_name | character(20) |
c_gender | character(10) | default 'male'::bpchar
age | integer |
Indexes:
"customer_tb1_pkey" PRIMARY KEY, btree (c_customer_sk) TABLESPACE pg_default
"idx_cus_tb1_c_gender" btree (c_gender) TABLESPACE pg_default
presdb=# select * from customer_tb1;
c_customer_sk | c_customer_id | c_first_name | c_last_name | c_gender | age
---------------+---------------+----------------------+----------------------+------------+-----
2369 | 1 | shang | lei | male |
7981 | 2 | zhang | san | male |
(2 rows)
2.2 删除和添加表约束
2.2.1 删除约束
-- 查看当前表结构
presdb=# \d customer_tb1
Table "public.customer_tb1"
Column | Type | Modifiers
---------------+---------------+----------------------------------------------------------------------
c_customer_sk | integer | not null
c_customer_id | integer | not null default nextval('customer_tb1_c_customer_id_seq'::regclass)
c_first_name | character(20) |
c_last_name | character(20) |
c_gender | character(10) | default 'male'::bpchar
age | integer |
Indexes:
"customer_tb1_pkey" PRIMARY KEY, btree (c_customer_sk) TABLESPACE pg_default
"idx_cus_tb1_c_gender" btree (c_gender) TABLESPACE pg_default
-- 删除表中主键约束customer_tb1_pkey
presdb=# alter table customer_tb1 drop constraint customer_tb1_pkey;
ALTER TABLE
-- 再次查看被删除主键约束的表结构
presdb=# \d customer_tb1
Table "public.customer_tb1"
Column | Type | Modifiers
---------------+---------------+----------------------------------------------------------------------
c_customer_sk | integer | not null
c_customer_id | integer | not null default nextval('customer_tb1_c_customer_id_seq'::regclass)
c_first_name | character(20) |
c_last_name | character(20) |
c_gender | character(10) | default 'male'::bpchar
age | integer |
Indexes:
"idx_cus_tb1_c_gender" btree (c_gender) TABLESPACE pg_default
-- 使用SQL语句查询该约束是否被删除
presdb=# select * from pg_constraint where conname like 'customer_tb1_pkey';
conname | connamespace | contype | condeferrable | condeferred | convalidated | conrelid | contypid | conindid | confrelid | confupdtype | confdeltype | confmatchtype | conislocal | coninhcount | connoinherit | consoft | conopt | conkey | confkey | conpfeqop | conppeqop
| conffeqop | conexclop | conbin | consrc | conincluding
---------+--------------+---------+---------------+-------------+--------------+----------+----------+----------+-----------+-------------+-------------+---------------+------------+-------------+--------------+---------+--------+--------+---------+-----------+----------
-+-----------+-----------+--------+--------+--------------
(0 rows)
2.2.2 添加约束
-- 重新为表字段c_customer_sk添加主键约束
presdb=# alter table customer_tb1 add constraint customer_tb1_pkey primary key(c_customer_sk);
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "customer_tb1_pkey" for table "customer_tb1"
ALTER TABLE
-- 查看表结构
presdb=# \d customer_tb1
Table "public.customer_tb1"
Column | Type | Modifiers
---------------+---------------+----------------------------------------------------------------------
c_customer_sk | integer | not null
c_customer_id | integer | not null default nextval('customer_tb1_c_customer_id_seq'::regclass)
c_first_name | character(20) |
c_last_name | character(20) |
c_gender | character(10) | default 'male'::bpchar
age | integer |
Indexes:
"customer_tb1_pkey" PRIMARY KEY, btree (c_customer_sk) TABLESPACE pg_default
"idx_cus_tb1_c_gender" btree (c_gender) TABLESPACE pg_default
-- 使用SQL语句查看customer_tb1_pkey约束信息
presdb=# select * from pg_constraint where conname like 'customer_tb1_pkey';
conname | connamespace | contype | condeferrable | condeferred | convalidated | conrelid | contypid | conindid | confrelid | confupdtype | confdeltype | confmatchtype | conislocal | coninhcount | connoinherit | consoft | conopt | conkey | confkey | conpfeqop |
conppeqop | conffeqop | conexclop | conbin | consrc | conincluding
-------------------+--------------+---------+---------------+-------------+--------------+----------+----------+----------+-----------+-------------+-------------+---------------+------------+-------------+--------------+---------+--------+--------+---------+-----------+
-----------+-----------+-----------+--------+--------+--------------
customer_tb1_pkey | 2200 | p | f | f | t | 16722 | 0 | 16736 | 0 | | | | t | 0 | t | f | f | {1} | | |
| | | | |
(1 row)
2.3 修改表字段类型
-- 为表新增address字段，设置类型integer
presdb=# alter table customer_tb1 add column address integer;
ALTER TABLE
-- 查看表结构
presdb=# \d customer_tb1
Table "public.customer_tb1"
Column | Type | Modifiers
---------------+---------------+----------------------------------------------------------------------
c_customer_sk | integer | not null
c_customer_id | integer | not null default nextval('customer_tb1_c_customer_id_seq'::regclass)
c_first_name | character(20) |
c_last_name | character(20) |
c_gender | character(10) | default 'male'::bpchar
age | integer |
address | integer |
Indexes:
"customer_tb1_pkey" PRIMARY KEY, btree (c_customer_sk) TABLESPACE pg_default
"idx_cus_tb1_c_gender" btree (c_gender) TABLESPACE pg_default
presdb=# alter table customer_tb1 ALTER COLUMN age address char(100);
ERROR: syntax error at or near "address"
LINE 1: alter table customer_tb1 ALTER COLUMN age address char(100);
^
-- 修改字段address字段类型为char，字段长度为100
presdb=# alter table customer_tb1 ALTER COLUMN address TYPE char(100);
ALTER TABLE
-- 再次查看表结构
presdb=# \d customer_tb1
Table "public.customer_tb1"
Column | Type | Modifiers
---------------+----------------+----------------------------------------------------------------------
c_customer_sk | integer | not null
c_customer_id | integer | not null default nextval('customer_tb1_c_customer_id_seq'::regclass)
c_first_name | character(20) |
c_last_name | character(20) |
c_gender | character(10) | default 'male'::bpchar
age | integer |
address | character(100) |
Indexes:
"customer_tb1_pkey" PRIMARY KEY, btree (c_customer_sk) TABLESPACE pg_default
"idx_cus_tb1_c_gender" btree (c_gender) TABLESPACE pg_default
2.4 修改表字段默认值
-- 设置字段 age 默认值为 18
presdb=# alter table customer_tb1 alter column age set default 18;
ALTER TABLE
presdb=# \d customer_tb1
Table "public.customer_tb1"
Column | Type | Modifiers
---------------+----------------+----------------------------------------------------------------------
c_customer_sk | integer | not null
c_customer_id | integer | not null default nextval('customer_tb1_c_customer_id_seq'::regclass)
c_first_name | character(20) |
c_last_name | character(20) |
c_gender | character(10) | default 'male'::bpchar
age | integer | default 18
address | character(100) |
Indexes:
"customer_tb1_pkey" PRIMARY KEY, btree (c_customer_sk) TABLESPACE pg_default
"idx_cus_tb1_c_gender" btree (c_gender) TABLESPACE pg_default
presdb=# select * from customer_tb1;
c_customer_sk | c_customer_id | c_first_name | c_last_name | c_gender | age | address
---------------+---------------+----------------------+----------------------+------------+-----+---------
2369 | 1 | shang | lei | male | |
7981 | 2 | zhang | san | male | |
(2 rows)
presdb=# insert into customer_tb1 (c_customer_sk, c_first_name,c_last_name) VALUES (7981, 'zhang','san');
ERROR: duplicate key value violates unique constraint "customer_tb1_pkey"
DETAIL: Key (c_customer_sk)=(7981) already exists.
presdb=# INSERT INTO customer_tb1 (c_customer_sk, c_first_name,c_last_name,address) VALUES (7981, 'jacky','shang','nanjing');
ERROR: duplicate key value violates unique constraint "customer_tb1_pkey"
DETAIL: Key (c_customer_sk)=(7981) already exists.
presdb=# INSERT INTO customer_tb1 (c_customer_sk, c_first_name,c_last_name,address) VALUES (1001, 'jacky','shang','nanjing');
INSERT 0 1
presdb=# select * from customer_tb1;
c_customer_sk | c_customer_id | c_first_name | c_last_name | c_gender | age | address
---------------+---------------+----------------------+----------------------+------------+-----+------------------------------------------------------------------------------------------------------
2369 | 1 | shang | lei | male | |
7981 | 2 | zhang | san | male | |
1001 | 5 | jacky | shang | male | 18 | nanjing
(3 rows)
-- 可以看到 age的值都为默认值 18
2.5 修改表字段名称
-- 修改字段address 为 mail
presdb=# ALTER TABLE customer_tb1 RENAME COLUMN address TO mail;
ALTER TABLE
presdb=# \d customer_tb1
Table "public.customer_tb1"
Column | Type | Modifiers
---------------+----------------+----------------------------------------------------------------------
c_customer_sk | integer | not null
c_customer_id | integer | not null default nextval('customer_tb1_c_customer_id_seq'::regclass)
c_first_name | character(20) |
c_last_name | character(20) |
c_gender | character(10) | default 'male'::bpchar
age | integer | default 18
mail | character(100) |
Indexes:
"customer_tb1_pkey" PRIMARY KEY, btree (c_customer_sk) TABLESPACE pg_default
"idx_cus_tb1_c_gender" btree (c_gender) TABLESPACE pg_default
presdb=# INSERT INTO customer_tb1 (c_customer_sk, c_first_name,c_last_name,mail) VALUES (1002, 'Biker','xie','123@qq.com');
INSERT 0 1
presdb=# select * from customer_tb1;
c_customer_sk | c_customer_id | c_first_name | c_last_name | c_gender | age | mail
---------------+---------------+----------------------+----------------------+------------+-----+------------------------------------------------------------------------------------------------------
2369 | 1 | shang | lei | male | |
7981 | 2 | zhang | san | male | |
1001 | 5 | jacky | shang | male | 18 | nanjing
1002 | 6 | Biker | xie | male | 18 | 123@qq.com
2.6 修改表名称
presdb=# \d customer_tb1
Table "public.customer_tb1"
Column | Type | Modifiers
---------------+----------------+----------------------------------------------------------------------
c_customer_sk | integer | not null
c_customer_id | integer | not null default nextval('customer_tb1_c_customer_id_seq'::regclass)
c_first_name | character(20) |
c_last_name | character(20) |
c_gender | character(10) | default 'male'::bpchar
age | integer | default 18
mail | character(100) |
Indexes:
"customer_tb1_pkey" PRIMARY KEY, btree (c_customer_sk) TABLESPACE pg_default
"idx_cus_tb1_c_gender" btree (c_gender) TABLESPACE pg_default
-- 修改表名称customer_tb1 为 customer
presdb=# ALTER TABLE customer_tb1 RENAME TO customer;
ALTER TABLE
presdb=# \d customer
Table "public.customer"
Column | Type | Modifiers
---------------+----------------+----------------------------------------------------------------------
c_customer_sk | integer | not null
c_customer_id | integer | not null default nextval('customer_tb1_c_customer_id_seq'::regclass)
c_first_name | character(20) |
c_last_name | character(20) |
c_gender | character(10) | default 'male'::bpchar
age | integer | default 18
mail | character(100) |
Indexes:
"customer_tb1_pkey" PRIMARY KEY, btree (c_customer_sk) TABLESPACE pg_default
"idx_cus_tb1_c_gender" btree (c_gender) TABLESPACE pg_default
2.7 删除表
presdb=# drop table customer;
DROP TABLE
presdb=# \d customer
Did not find any relation named "customer".
presdb=#
三、学习心得
学完本节课后，再次回归了之前课程里openGauss 如何使用serial来进行递增。
但目前我还有几个疑问未得到解答：
- 如何查询索引的状态，目前我通过pg_index和pg_indexes未查找到指定索引的状态，该如何查询状态是否正常我不太清楚，目前还未找到资料。
- 修改表名后索引是否还继续生效。
这些点还要再进一步学习摸索。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
