当前位置： 首页 > article >正文

8-表的定义

article 2024/11/23 8:29:55

表的定义

数据库中分很多种表，如普通表、簇表、分区表、外部表、临时表等

表可以类似的理解为excel表格，有行，有列，同时也可以进行查询

下面将会介绍普通表的创建、修改、删除，还有字段的数据类型等内容

表的基本概念

1、在KES中，表又被称为关系。与现实相关的一个关系对象，如员工表、部门表

2、表是由行和列组成的二维对象

3、表中的字段是用数据类型来决定的，必须为每个列指定数据类型

创建普通表

1、创建表的时候，需要指定表名、列名、列的数据类型、列数据精度、列的缺省值、表的约束等

语法：

CREATE [ UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
{ column_name data_type } [ COLLATE collation ] [ column_constraint [ ... ] ] ]
	| table_constraint
	| LIKE source_table [ like_option ]
	[, ...] ])
[ WITH ( storage_parameter [= value] ) [,...] | WITH OIDS | WITHOUT OIDS ]
[ TABLESAPCE tablespace_name ]
参数名称简介
TEMPORARY | TEMP表示该表是临时表
UNLOGGED指定该表在修改时不会在WAL日志中保存事务日志
INHERITS创建分区表时会使用，在子表和它的父表之间创建一种持久的关系
WITH创建表时指定参数，例如：FILLFACTOR、OIDS等
ON COMMIT指定临时表在一个事务结束的行为
TABLESPACE创建表时指定表所存放的表空间
CREATE TABLE <表名>
	(<列名> <数据类型>[ <列级完整性约束条件> ]
	[, <列名> <数据类型>[ <列级完整性约束条件> ] ]
	[,<表级完整级约束条件>] )

<表名>：索要定义的基本表的名字

<列名>：组成该表的各个属性(列)

<列级完整性约束>：涉及一个或多个属性列的完整性约束条件，用来唯一标识该行记录

<表级完整性约束>：涉及一个或多个属性列的完整性约束条件

修改表

1、使用ALTER TABLE命令可以修改表名、列名、列的精度、列的默认值，维护约束，更改表空间等

当表中存在记录的时候，修改时要符合现有数据的要求，否则可能修改失败或者导致数据被截断

语法：

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ] action [,...]
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ] RENAME [ COLUMN ] coulmn_name to new_column_name
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ] RENAME CONSTRAINT constraint_name TO new_constraint_name
ALTER TABLE [ IF EXISTS ] name RENAME TO new_name
ALTER TABLE [ IF EXISTS ] name SET SCHEMA new_schema
ALTER TABLE ALL IN TABLESAPCE name [ OWNED BY role_name [,...] ] SET TABLESPACE new_tablespace [NOTICE]
参数简介
ADD COLUMN新增一个列，新列将被默认值所填充，如果没有指定DEAFAULT子句，则会填充空值
DROP COLUMN删除一个列，列中的数据将会消失，涉及到该列的约束也会被移除
ADD CONSTRAINT新增一个约束，与表约束的语法相同
DROP CONSTRAINT删除一个约束，可以用"\d 表名"方式获取约束的名称
SET TABLESPACE移动表到其他的表空间
OWNER TO修改表的拥有者

删除表

1、DROP TABLE命令会将表的定义、表的依赖对象、表的数据同时从数据库中删除

2、只有表的拥有者、模式拥有者和超级用户能对表执行删除操作

3、RESTRICT关键字表示如果还有对象依赖于表，例如外键约束等，则不能删除表，这是默认选项

4、要删除一个被外键约束的表，需要指定CASCADE关键字(只级联删除外键约束，而不会删除原外键表中的数据行)

DROP TABLE [ IF EXISTS ] name [,...] [ CASCADE | RESTRICT ]
参数简介
RESTRICT当有外键约束时删除主表会失败
CASCADE当有外键约束时删除主表会连同子表中的数据一起删除

截断表

1、TRUNCATE TABLE命令会清空表中的数据，保留表的定义，立即回收表占用的磁盘空间

2、KES在执行TRUNCATE TABLE时会是一个能快速清空资料表内所有的资料的SQL语法。TRUNCATE语句的执行效率要高于delete语句

语法：

TRUNCATE [ TABLE ] [ ONLY ] neme [ * ] [,...] [ RESTART IDENTITY | CONTITY IDEBTITY ] [ CASCADE | RESTRICT ]
参数简介
RESTART IDENTITY自动重新开始截断表的序列
CONTINUE IDENTITY默认不该序列值
RESTRICT默认，表有外键引用则阻止记录被清空
CASCADE存在主外键关联时，清空父表时子表关联的行被级联清空

实验1：创建表

执行实验所需环境

test=# \i /install/EXAM_v0.11.sql

使用常规语法创建表

1、创建t01表

①表包含id、name、location三个列，类型分别为serial、text、text

②表存储在sys_default表空间(即默认表空间)

③使用"\d"命令查看t01表的定义信息

test=# create table public.t01(id serial,name text,location text);
CREATE TABLE
test=# \d t01
                            数据表 "public.t01"
   栏位   |  类型   | 校对规则 |  可空的  |              预设               
----------+---------+----------+----------+---------------------------------
 id       | integer |          | not null | nextval('t01_id_seq'::regclass)
 name     | text    |          |          | 
 location | text    |          |          |

2、创建t02表

①表包含code、title、did、date_prod、kind、len字段，类型分别为char(5)、varchar(40)、integer、date、varchar(10)、interval hour to minute，其中title和did字段不允许为空

②表存储在sys_default表空间(即默认表空间)

③使用"\d"命令查看t02表的定义信息

test=# create table t02(code char(5),title varchar(40) NOT NULL,did integer NOT NULL,date_prod date,kind varchar(10),len interval hour to minute);
CREATE TABLE
test=# \d t02
                         数据表 "public.t02"
   栏位    |            类型            | 校对规则 |  可空的  | 预设 
-----------+----------------------------+----------+----------+------
 code      | character(5 char)          |          |          | 
 title     | character varying(40 char) |          | not null | 
 did       | integer                    |          | not null | 
 date_prod | date                       |          |          | 
 kind      | character varying(10 char) |          |          | 
 len       | interval hour(2) to minute |          |          |

通过子查询来创建表

1、通过复制exam模式下的student表来创建student_m表，值复制原表中部分数据到新表中

注：使用子查询创建表时会复制表的字段名和表数据等信息到新表中

test=# create table student_m as select * from exam.student where gender='m';
SELECT 6
gender='m'表示复制性别为男的数据

image-20241112133458839

2、通过复制exam模式下course表来创建course01表，复制原表中全部数据到新表中

test=# create table course01 as select * from exam.course;
SELECT 3

image-20241112133812849

使用LIKE语法来创建表

1、使用LIKE创建表，非空约束会默认复制到新表中

①例如：根据t02表新建t03表，并查看t03表的信息

test=# create table t03 (like t02 including defaults including constraints including indexes);
CREATE TABLE

注：1、使用like复制的时候，并不会复制原表的数据到新表中

2、including defaults：表示复制t02表中默认值；3、including constraints：表示复制t02表中的约束(如主键、外键、唯一性约束等)

4、including indexes：表示复制t02表中的索引

实验2：修改表

重命名表名、列名、约束名

修改表名时，使用rename to；修改列名时，使用rename old_column_name to new_column_name

修改约束名时，使用rename constraint old_constraint_name to new_constraint_name

1、修改course01表的名字为courseOLD

test=# alter table course01 rename to courseOLD;
ALTER TABLE

image-20241112161711769

2、修改courseOLD表的cname列的名字为cno_name

test=# alter table courseold rename cname to cno_name;
ALTER TABLE

image-20241112161914106

3、修改exam.score表的外键约束cno_fk的名字为course_con_fk

test=# alter table exam.score rename CONSTRAINT cno_fk to course_con_fk;
ALTER TABLE

image-20241112163239231

修改表的模式(schema)

移动表时，会将表拥有的索引、约束和序列到；另一个模式下，必须由该表的拥有者或者超级用户执行，同时执行者必须拥有新模式上的CREATE特权

1、移动exam.score表到新建的sch01模式下

test=# alter table exam.score set schema sch01;
ALTER TABLE

image-20241112164127362

image-20241112164230632

移动表到其它空间

1、新建表空间tbs01和tbs02

①创建表空间所使用的目录并设置目录的属主、属组和文件系统权限

[root@node1 ~]# mkdir /tbs01
[root@node1 ~]# mkdir /tbs02
[root@node1 ~]# chown kingbase.kingbase /tbs01
[root@node1 ~]# chown kingbase.kingbase /tbs02

②创建两个表空间分别指向对应的目录

test=# create tablespace tbs01 location '/tbs01';
CREATE TABLESPACE
test=# create tablespace tbs02 location '/tbs02';
CREATE TABLESPACE

2、查看待移动表的原始表空间，然后迁移表到表空间tbs01中

test=# select table_name,tablespace_name from dba_tables where table_name in ('COURSE','STUDENT');
 table_name |       tablespace_name       
------------+-----------------------------
 COURSE     | database default tablespace
 STUDENT    | database default tablespace
(2 行记录)
test=# ALTER table exam.course set tablespace tbs01; 
ALTER TABLE
test=# alter table exam.student set tablespace tbs01;
ALTER TABLE
test=# select table_name,tablespace_name from dba_tables where table_name in ('COURSE','STUDENT');
 table_name | tablespace_name 
------------+-----------------
 COURSE     | tbs01
 STUDENT    | tbs01
(2 行记录)

3、将tbs01表空间中的所有表全部迁移到表空间tbs02

test=# alter table all in tablespace tbs01 set tablespace tbs02;
ALTER TABLE
test=# select table_name,tablespace_name from dba_tables where table_name in ('COURSE','STUDENT');
 table_name | tablespace_name 
------------+-----------------
 COURSE     | tbs02
 STUDENT    | tbs02
(2 行记录)

修改表的其它定义

增加、删除、修改表中的列(字段)

1、在表t02中增加列address，类型为varchar，精度为30

test=# alter table t02 add column address varchar(30);
ALTER TABLE

image-20241112171349930

2、删除表t02中的address字段

test=# alter table t02 drop column address;
ALTER TABLE

3、修改表t02的部分字段类型，将did的类型修改为number(10,2)，将kind的类型修改为varchar(20)

test=# alter table t02 alter COLUMN did type number(10,2);
ALTER TABLE
test=# alter table t02 alter COLUMN kind type varchar(20);
ALTER TABLE

image-20241112173156874

实验3：截断表

1、使用TRUNCATE命令清空courseold表中数据

test=# truncate courseold;
TRUNCATE TABLE

image-20241112173449160

实验4：临时表

1、概述：默认情况下，创建的临时表为会话级临时表，即只能在创建临时表的会话中使用，表结构在整个会话周期中一直保留。而在不同会话中可以创建同名的临时表

2、可以使用ON COMMIT PRESERVE ROWS(默认，事务结束时保留临时表记录)

或者ON COMMIT DELETE ROWS(事务结束时清除临时表记录)关键字指定事务结束后临时表数据是否被清除

3、在创建临时表的时候，系统会自动为其分配一个模式(SYS_TEMP_xx)，不能再创建临时表时指定模式

示例：先在当前会话创建临时表，接着在换一个会话查看是否还有在刚刚那个会话中创建的临时表

test=# create temporary table temp01(id int) on commit preserve rows;
CREATE TABLE
test=# create temporary table temp02(id int) on commit delete rows;
CREATE TABLE

image-20241112195132294

事务级临时表

1、使用ON COMMIT DROP 关键字创建事务级临时表，表的创建和使用需要放在同一个事务中，事务结束后表结构和表中数据都会被删除

就是说，事务结束后，表就消失的。表的存在是依托于事务的。但如果当前会话退出后，事务级临时表还是会被删除

示例：开启事务，创建事务级临时表后，结束事务，并查看刚刚创建的事务级临时表是否还存在

test=# begin ;
BEGIN
test=# create temporary table temp03 (id int) on commit drop ;
CREATE TABLE
test=# \dt temp03
               关联列表
 架构模式  |  名称  |  类型  | 拥有者 
-----------+--------+--------+--------
 pg_temp_4 | temp03 | 数据表 | system
(1 行记录)

test=# end;
COMMIT
test=# \dt temp03
没有找到任何名称为 "temp03" 的关联.

image-20241112195827111

数据类型补充说明

类型长度变长/定长取值范围
int4字节常用的整数-2147483648到+2147483647
serial4字节自增型1到+2147483647
numeric或number变长用户自定义精度无限制，与decimal等效
decimal变长用户自定义精度无限制，与numeric或number等效
char(n)自定义定长，不足不补空白用户定义固定长度的字符串。n为精度，单位为byte或char，默认为char
varchar(n)自定义变长，用户定义用户定义不定长度的字符串
test不固定变长，无长度限制用户定义未知长度的打文本
date4字节日期精度到天
time8字节时间精度到毫秒
timestamp8字节包括日期和时间精度毫秒，1纳秒/17位
interval12字节时间间隔精度毫秒
BYTEA4字节+二进制数据用于存储图片等二进制数据

numeric(4,0)：表示精度为4，标度为0，精度为数值可以拥有的总位数，标度为小数点后的位数，也就是说"numeric(4,0)"可以存储的数值最多可以有4位数字，如1001，1002…，标度位0，代表数值不能有小数部分，只能存储整数


http://www.kler.cn/a/403563.html

相关文章：

  • 计算机网络（12）介质访问控制
  • 【Xbim+C#】创建圆盘扫掠IfcSweptDiskSolid
  • Verilog HDL可综合与不可综合语句
  • js批量输入地址获取经纬度
  • 推荐一种编辑任意复杂JOSN数据的简单易方法
  • VIM的下载使用与基本指令【入门级别操作】
  • 如何在 Ubuntu 上设置 SSH X11 转发并访问远程图形界面
  • Quality minus junk论文阅读
  • PyTorch基础学习01_创建张量常见属性数据转换图像
  • vue+node+Express+xlsx+emements-plus实现导入excel,并且将数据保存到数据库
  • 002创建ASP.NET Core项目-数据库优先
  • C++算法练习-day41——700二叉搜索树中的搜索
  • RFdiffusion EuclideanDiffuser类解读
  • 缓存cache
  • Apache和HTTPS证书的生成与安装
  • 用遗传算法优化的网络学习改进算法
  • 斯坦福UC伯克利开源突破性视觉场景生成与编辑技术，精准描绘3D/4D世界!
  • MySQL：联合查询(2)
  • PH热榜 | 2024-11-19
  • 组件注册：局部(app.vue,import,components，组件标签）全局（main.js，import,vue.component,-组件标签)
  • CRM系统安全性排名：数据保护能力评估
  • 深入探索Golang的GMP调度机制：源码解析与实现原理
  • 【Linux】Namespace
  • Linux的权限
  • HarmonyOS Next 关于页面渲染的性能优化方案
  • C语言菜鸟入门·关键字·void的用法