本文最后更新于：2024年3月18日凌晨

SQL 表

基本表的定义与删除

定义基本表

CREATE TABLE<表名>(
    <列名><数据类型>[列级完整性约束条件]
    [,<列名><数据类型>[列级完整性约束条件]]
    ...
    [,<表级完整性约束条件>]
) [表参数];

建表的同时通常还可以定义与该表有关的完整性约束条件，这些完整性约束条件被存入系统的数据字典中，当用户操作表中数据时由关系数据库管理系统自动检查该操作是否违背这些完整性约束条件。如果完整性约束条件涉及该表的多个属性列，则必须定义在表级上，否则既可以定义在列级，也可以定义在表级。
AUTO_INCREMENT：自增字段。
COMMENT：字段注释或表注释。
DEFAULT：字段默认值。
DEFAULT CHARSET：默认字符集。
ENGINE：存储引擎。

[例3.5]：建立一个"学生"表Student

CREATE TABLE Student
	(Sno CHAR(9) PRIMARY KEY AUTO_INCREMENT,
	 Sname CHAR(20) UNIQUE,
	 Ssex CHAR(2) COMMENT '性别' NOT NULL,
	 Sage SMALLINT DEFAULT 18,
	 Sdept CHAR(20) DEAULT
	)ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='学生表';

系统执行该CREATE TABLE语句后，就在数据库中建立一个新的空"学生"表Student，并将有关"学生"表的定义及有关约束条件存放在数据字典中。

[例3.6]：建立一个"课程"表Course

CREATE TABLE Course
	(Cno CHAR(4) PRIMARY KEY,/*列级完整性约束，Cno是主码*/
	 Cname CHAR(40) NOT NULL,/*列级完整性约束条件，Cname不能取空值*/
	 Cpno CHAR(4),/Cpno的含义是先修课*/
	 Ccredit SMALLINT,
	 FOREIGN KEY (Cpno) REFERENCES Course (Cno)
	 /*表级完整性约束条件，Cpno是外码，被参照表是Course，被参照列是Cno*/
	);

本例说明参照表和参照表可以是同一个表。

[例3.7]：建立学生选课表SC

CREATE TABLE SC
	(Sno CHAR(9),
	 Cno CHAR(4),
	 Grade SMALLINT,
	 PRIMARY KEY (Sno,Cno),/*主码由两个属性构成，必须作为表级完整性进行定义*/
	 FOREIGN KEY (Sno) REFERENCES(Sno),
	 /*表级完整性约束条件，Sno是外码，被参照表是Student*/
	 FOREIGN KEY (Cno) REFERENCES(Cno)
	 /*表级完整性约束条件，Cno是外码，被参照表是Course*/
	);

数据类型

数据类型	含义
CHAR(n),CHARACYER(n)	长度为n的定长字符串
VARCHAR(n),CHARACTERVARYING(n)	最大长度为n的变长字符串
CLOB	字符串大对象
BLOB	二进制大对象
INT,INTEGER	长整数（4字节）
SMALLINT	短整数（2字节）
BIGINT	大整数（8字节）
NUMERIC(p,d)	定点数，由p为数字（不包括符号，小数点）组成，小数点后面有d位数字
DECIMAL(p,d),DEC(p,d)	同NUMERIC
REAL	取决于机器精度的单精度浮点数
DOUBLE PRECISION	取决于机器精度的双精度浮点数
FLOAT(n)	可选精度的浮点数，精度至少为n位数字
BOOLEAN	逻辑布尔量
DATE	日期，包括年，月，日，格式为yyyy-MM-dd
TIME	时间，包含一日的时，分，秒，格式为hh:mm:ss
DATETIME	日期和时间，包含年，月，日，时，分，秒，格式为yyyy-MM-dd hh:mm:ss
TIMESTAMP	时间戳类型
INTERVAL	时间间隔类型

注意
- VARCHAR(255）以下使用额外一个字节保存长度。
- VARCHAR(255）以上使用额外两个字节保存长度。
- VARCHAR变更长度时会出现锁表。
- DATETIME与时区无关，精确到毫秒，TIMESTAMP与时区有关精确到秒。

模式与表

每一个基本表都属于某一个模式，一个模式包含多个基本表，当定义基本表时一般可以有三种方法定义它所属的模式：

在表名中明显地给出模式名。

1
2
3

CREATE TABLE "S-T".Student(...);/*Student所属的模式是"S-T"*/
CREATE TABLE "S-T".SC(...);/*SC所属的模式是"S-T"*/
CREATE TABLE "S-T".Course(...);/*Course所属的模式是"S-T"*/

在创建模式语句中同时创建表。
设置所属的模式，这样在创建表时表名中不必给出模式名。
当用户创建基本表（其他数据库对象也一样）时若没有指定模式，系统根据搜索路径（search path）来确定该对象所属的模式，搜索路径包含一组模式列表，关系数据库管理系统会使用模式列表中第一个存在的模式作为数据库对象的模式名，若搜索路径中的模式名都不存在，系统将给出错误。
使用下面的语句可以显示当前的搜索路径：

1	`SHOW search_path;`

搜索路径的当前默认值是$user,PUBLIC.其含义是首先搜索与用户名相同的模式名，如果该模式名不存在，则使用PUBLIC模式。
数据库管理员也可以设置搜索路径，例如：

1	`SET search_path TO "S-T",PUBLIC`

然后定义基本表：

1	`CREATE TABLE Student(...)`

实际结果是建立了S-T.Student基本表。因为关系数据库管理系统发现搜索路径中第一个模式名S-T存在，就把该模式作为基本表Student所属的模式。

清除基本表数据

TRUNCATE 命令用于删除现有数据表中的所有数据。

1	`TRUNCATE TABLE table_name;`

实例

考虑 CUSTOMERS 表，表中的记录如下所示：

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

下面的示例展示了 TRUNCATE 命令的用法：

1	`TRUNCATE TABLE CUSTOMERS;`

现在，CUSTOMERS 表已经被清空了，SELECT 语句的输出应当如下所示：

1 2	`SQL> SELECT * FROM CUSTOMERS; Empty set (0.00 sec)`

删除基本表

1	`DROP TABLE<表名> [RESTRICT\|CASCADE];`

注意

若选择RESTRICT，则该表的删除是有限制条件的。欲删除的基本表不能被其他表的约束所引用（如CHECK,FOREIGN KEY等约束），不能有视图，不能有触发器（trigger)，不能有存储过程或函数等，如果存在这些依赖该表的对象，则此表不能被删除。
若选择CASCADE，则该表的删除没有限制条件，在删除基本表的同时，相关的依赖对象，例如视图，都将一起删除。
默认情况是RESTRICT

[例3.11]：删除Student表。

1	`DROP TABLE Student CASCADE;`

基本表定义一旦被删除，不仅表中的数据和此表的定义将被删除，而且此表上建立的索引，触发器等对象一般也都将被删除，有的关系数据库管理系统还会同时删除在此表上的索引，触发器等对象一般也都将被删除，有的关系数据库管理系统还会同时删除在此表上建立的视图，如果欲删除的基本表被其他基本表所引用，则这些表也可能被删除，SC也被级联删除，因此执行删除基本表的操作一定要格外小心。

[例3.12]：若表上建有视图，选择RESTRICT时表不能删除，选择CASCADE时可以删除表，视图也自动被删除。

CREATE VIEW IS_Student /*Student表上建立视图*/
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept='IS';

DROP TABLE Student RESTRICT;/*删除Student表*/
--ERROR:cannot drop table Student because other objects depend on it
/*系统返回错误信息，存在依赖该表的对象，此表不能被删除*/

DROP TABLE Student CASCADE;/*删除Student表*/
--NOTICE:drop cascades to view IS_Student
/*系统返回提示，此表上的视图也被删除*/

SELECT * FROM IS_Student;
--ERROR:relation "IS_Student"does not exist

注意：不同的数据库产品在遵循SQL标准的基础上具体实现细节和处理策略会与标准有差别。

修改基本表

ALTER TABLE<表名>
[ADD[COLUMN]<新列名><数据类型>[完整性约束]]
[ADD<表级完整性约束>]
[DROP[COLUMN]<列名>[CASCADE|RESTRICT]]
[DROP CONSTRAINT<完整性约束名>[RESTRICT|CASCADE]]
[ALTER COLUMN<列名><数据类型>];

<表名>是要修改的就基本表。
ADD子句用于增加新列，新的列级完整性约束条件和新的表级完整性约束条件。
DROP COLUMN子句用于删除表中的列如果指定了CASCADE短语，则自动删除引用了该列的其他对象，比如视图;如果指定了RESTRICT短语，则如果该列被其他对象引用，RDBMS将拒绝删除该列。
DROP CONSTRAINT子句用于删除指定的完整性约束条件。
ALTER COLUMN子句用于修改原有的列定义，包括修改列名和数据类型。

添加字段

向Student表增加"入学时间"列，其数据类型为日期型。

1	`ALTER TABLE Student ADD S_entrance DATE;`

不论基本表中原来是否已有数据，新增加的列一律为空值。

删除字段

1	`ALTER TABLE Student DROP COLUMN S_entrance;`

修改字段数据类型

将年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数。

1	`ALTER TABLE Student ALTER COLUMN Sage INT;`

添加字段约束

增加课程名称必须取唯一值的约束条件。

1	`ALTER TABLE Course ADD UNIQUE(Cname);`

删除字段约束

1	`ALTER TABLE Course DROP CONSTRAINT Cnam`

修改表名

1	`RENAME 原表名 TO 新表名。`

修改字段

1	`ALTER TABLE 表名 RENAME COLUMN 列名 TO 新列名。`

修改自增起始值

1	`ALTER TABLE Persons AUTO_INCREMENT=100`

Software BackEnd Database 关系数据库 DML

本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议，转载请注明出处！

SQL 视图上一篇

SQL 实体完整性下一篇