SQL数据定义

2022-04-09 18:44:59

1 基本数据类型
2 创建关系模式
3 删除关系模式
4 修改关系模式
5 定义视图

1 基本数据类型

1.1 数值型

类型	SQL语句	参数
整型	`INTEGER`/`INT`, `SMALLINT`, `TINYINT`, `MEDIUMINT`, `BIGINT`
定点型	`DECIMAL(p, s)`/`NUMERIC(p, s)`	有效数字共\(p\)位, 小数点后\(s\)位
浮点型	`FLOAT`, `DOUBLE`
二进制数型	`BIT(b)`	\(b\)位二进制数 (\(1 \leq b \leq 64\))

1.2 日期时间型

类型	SQL语句	显示和输入格式
年类型	`YEAR`	'YYYY'
日期型	`DATE`	'YYYY-MM-DD'
时间型	`TIME`	'HH:MM:SS' / 'HHH:MM:SS'
日期时间型 (与时区无关)	`DATETIME`	'YYYY-MM-DD HH:MM:SS'
时间戳型	`TIMESTAMP`	'YYYY-MM-DD HH:MM:SS'

1.3 字符串型

类型	SQL语句	参数
定长字符串型	`CHAR(n)`	最多储存\(n\)个字符 (\(n \leq 255\))
变长字符串型	`VARCHAR(n)`	最多储存\(n\)个字符 (\(n \leq 65535\))
文本型	`TINYTEXT`, `TEXT`, `MEDIUMTEXT`, `LONGTEXT`

1.4 二进制串

类型	SQL语句	参数
定长二进制串型	`BINARY(n)`	最多储存\(n\)个字符 (\(n \leq 255\))
变长二进制串型	`VARBINARY(n)`	最多储存\(n\)个字符 (\(n \leq 65535\))
二进制对象型	`TINYBLOB`, `BLOB`, `MEDIUMBLOB`, `LONGBLOB`

1.5 枚举型

ENUM(值列表)
枚举型的值只能取自值列表, 值列表中最多包含65535个不同的值.

例:

ENUM('Mercury', 'Venus', 'Earth', 'Mars')

1.6 集合型

SET(值列表)
集合型的值只能是值集合的子集, 如 'Mercury, Earth', 值列表中最多包含64个不同的值.

例:

SET('Mercury', 'Venus', 'Earth', 'Mars')

2 创建关系模式

2.1 创建关系模式

CREATE TABLE
定义关系模式, 包括关系名, 属性名, 属性类型, 主键, 外键, 完整性约束等.

例:
创建Student关系, 其模式如下:

属性名属性类型属性含义

Sno CHAR(6) 学号

Sname VARCHAR(10) 姓名

Ssex ENUM('M', 'F') 性别

Sage INT 年龄

Sdept VARCHAR(20) 所在系

SQL语句:
CREATE TABLE Student (
Sno CHAR(6),
Sname VARCHAR(10),
Ssex ENUM('M', 'F'),
Sage INT,
Sdept VARCHAR(20)
);

属性名	属性类型	属性含义
Sno	CHAR(6)	学号
Sname	VARCHAR(10)	姓名
Ssex	ENUM('M', 'F')	性别
Sage	INT	年龄
Sdept	VARCHAR(20)	所在系

2.2 声明主键

PRIMARY KEY
声明关系的主键 (一个关系仅有一个主键), 有两种声明方法.

例:
将Sno属性声明位Student关系的主键.

方法1:

CREATE TABLE Student (
Sno CHAR(6) PRIMARY KEY,
Sname VARCHAR(10),
Ssex ENUM('M', 'F'),
Sage INT,
Sdept VARCHAR(20)
);

方法2:

CREATE TABLE Student (
Sno CHAR(6),
Sname VARCHAR(10),
Ssex ENUM('M', 'F'),
Sage INT,
Sdept VARCHAR(20)
PRIMARY KEY (Sno)
);
声明多个属性构成的主键时只能用方法2.

2.3 声明外键

FOREIGN KEY
声明关系的外键 (一个关系可以有多个外键).

创建SC关系, 其模式如下:

属性名属性类型属性含义

Sno CHAR(6) 学号

Cno CHAR(4) 课号

Grade INT 成绩

{Sno, Cno}是SC的主键, Sno是SC的外键, 参照Student关系的主键Sno.
CREATE TABLE SC (
Sno CHAR(6),
Cno CHAR(4),
Grade INT,
PRIMARY KEY (Sno, Cno),
FOREIGN KEY (Sno) REFERENCES Student (Sno)
);

属性名	属性类型	属性含义
Sno	CHAR(6)	学号
Cno	CHAR(4)	课号
Grade	INT	成绩
{Sno, Cno}是SC的主键, Sno是SC的外键, 参照Student关系的主键Sno.

2.4 声明用户定义完整性约束

规定属性值非空: NOT NULL;
规定属性值不重复: UNIQUE;
规定数值型属性值自动递增: AUTO_INCREMENT;
定义属性的缺省值: DEFAULT 缺省值;
规定属性值必须满足表达式给出的条件: CHECK (表达式)^[1].

例:

CREATE TABLE Student (
Sno CHAR(6),
Sname VARCHAR(10) NOT NULL,
Ssex ENUM('M', 'F') NOT NULL CHECK (Ssex IN ('M', 'F')),
Sage INT DEFAULT 0 CHECK (Sage >= 0),
Sdept VARCHAR(20),
PRIMARY KEY (Sno)
);

3 删除关系模式

DROP TABLE 关系名1, 关系名2, ..., 关系名n;
删除关系时会连同关系中的数据一起删除.

4 修改关系模式

ALTER TABLE

增加, 修改, 删除属性;
增加, 删除约束.

例:
增加属性
在Student关系中增加属性Mno, 记录学生的班长的学号.
ALTER TABLE Student ADD Mno CHAR(6);
增加约束
将属性Mno声明为Student关系的外键, 参照Student的主键Sno.
ALTER TABLE Student
ADD CONSTRAINT fk_mno
FOREIGN KEY (Mno) REFERENCES Student(Sno);
删除属性
删除Student关系的Mno属性.
ALTER TABLE Student DROP Mno;
删除约束
删除Student关系中Mno属性上的外键约束fk_mno.
ALTER TABLE Student DROP CONSTRAINT fk_mno;
修改属性定义
将Student关系中Sname属性的类型修改为VARCHAR(20)且不重名.
ALTER TABLE Student ALTER Sname VARCHAR(20) UNIQUE;
MySQL使用
ALTER TABLE Student MODIFY Sname VARCHAR(20) UNIQUE;

5 定义视图

5.1 创建视图

CREATE VIEW 视图名 [(属性名列表)] AS 子查询;

例:
为选修了3006号课的计算机系(CS)的学生建立视图, 列出学号, 姓名和成绩.
CREATE VIEW CS_Student_on_DB AS
SELECT Sno, Sname, Grade
FROM Student NATURAL JOIN SC
WHERE Sdept = 'CS' AND Cno = '3006';

MySQL对CREATE VIEW中的子查询有很多限制条件, 参考https://dev.mysql.com/doc/refman/5.5/en/create-view.html.

5.2 修改视图定义

ALTER VIEW 视图名 [(属性名列表)] AS 子查询;

例:
修改视图CS_Student_on_DB, 增加性别属性.

ALTER VIEW CS_Student_on_DB AS
SELECT Sno, Sname, Ssex, Grade
FROM Student NATURAL JOIN SC
WHERE Sdept = 'CS' AND Cno = '3006';

5.3 删除视图

DROP VIEW 视图名;

5.4 视图查询

与SQL查询语法相同

例:
查询计算机系(CS)没有通过"Database Systems"课考试的学生.
SELECT Sno, Sname FROM CS_Student_on_DB WHERE Grade < 60;

MySQL只解析CHECK, 但储存引擎并不处理. ↩︎

码农公寓