【数据库】数据库入门（三）: SQL
SQL: 结构化查询语言（Structured Query Language）
SQL 是由 IBM 公司首先开发产生，它是关系型数据库最早出现的商用语言之一。1974年，IBM 公司 San Jose, California 研究实验室的 D.D.Chamberlin 和 R.F. Boyce 研制出一套规范语言 SEQUEL （Structured English Query Language），并在1976年11月的 IBM Journal of R&D 上公布新版本的 SQL （叫 SWQUEL/2）。
- 1980年改名为 SQL。
- 1986年，美国ANSI采用SQL作为关系数据库管理系统的标准语言（ANSI X3. 135-1986），后为国际标准化组织（ISO）采纳为国际标准。
- 1989年，美国ANSI采纳在ANSI X3.135-1989报告中定义的关系数据库管理系统的SQL标准语言，称为ANSI SQL 89，该标准替代ANSI X3.135-1986版本。
- 1999年，SQL 继续扩展并划分出核心功能并根据这些功能划分出不同的封装。
- 2003年，SQL 继续扩展，并可以允许 XML 支持。
- 2011年，SQL 继续扩展，改善了对临时数据库的支持。
跟关系型数据模型不同的是，关系型数据库主要处理对象的集合，即集合中不能出现有重复的对象；而 SQL 主要处理的是多集，即 SQL 允许一种数据关系中包含重复的对象。
SQL 为关系型数据库提供了许多接口，是一种高级的非过程化编程语言，其中包括：
- 数据定义语言 （DDL: Data Definition Language）
- 数据操纵语言 （DML: Data Manipulation Language）
- 数据控制语言 （DCL: Data Control Language）
- 事务控制语言 （TCL: transaction Control Language）
需要强调的是，在SQL语言的使用过程中，我们常将关键字用大写字母来表示，而其他变量名等，一般用小写字母表示或者按照其自身含义具体实现。
数据定义语言 （DDL: Data Definition Language）
1. 创建表 （CREATE TABLE）
使用 CREATE TABLE 可以创建一个新的关系模式，声明它的表名，属性，还可以指定每个属性的限制条件以及表自身的限制条件。基本格式如：
CREATE TABLE table_name
(attr_name data_type [attr constraints],
...,
attr_name data_type [attr constraints],
[table constraints]);
举几个例子：
| StudentID | Name | DoB |
|---|
CREATE TABLE Student
(StudentID INT,
Name VARCHAR(50),
DoB Date,
Email VARCHAR(100));
| No | Cname | Unit |
|---|
CREATE TABLE Course
(No VARCHAR(20),
Cname VARCHAR(50),
Unit SMALLINT);
| StudentID | CourseNo | Semester | Status |
|---|
CREATE TABLE Enrol
(StudentID INT,
CourseNo VARCHAR(20),
Semester VARCHAR(50),
Status VARCHAR(50));
2. 属性的数据类型
常用的属性数据类型有4中，分别是数值型（Numeric）、字符串型（String）、日期与时间（Date and time）以及布尔型（Boolean）。
- 数值型
- 整型：INT，SMALLINT
- 浮点型： FLOAT，REAL，DOUBLE PRECISION
- 指定显示位数：NUMERIC(i, j)，DECIMAL(i, j)。其中 i 为精度，表示总共显示的数字的位数；j 为范围，表示小数点后显示的数字的位数。
- 字符串型
- CHAR(n)：n 指定属性字符串的长度。
- VARCHAR(n)：n 指定不定长属性字符串允许的最大长度。
- BIT(n)：n 指定比特串的长度。
- BIT VARYING(n)：n 指定不定长比特串允许的最大长度。
- 日期与时间
- DATE：（年，月，日）/ （year, month, day）。
- TIME：（时，分，秒）/ （hour, minute, second）。
- TIMESTAMP：包含日期和时间，秒最少精确到6位，且可以限定所选的时区。
- INTERVAL：指定一个相对值，用于增加或者减少上述三种数据类型对应的值。
- 布尔值：包含 TRUE 和 FALSE 两个值。
在我们创建属性的时候，相应的会配置属性的数据类型。根据需求的不同，使用上述的数据类型，有时候我们也会自定义一些新的数据类型，使用 CREATE DOMAIN 语句，如：
CREATE DOMAIN ssn_type AS CHAR(9)；
3. 属性的约束条件
常用于 SQL 语句中的属性约束有以下几种：
- NOT NULL：声明属性的值不能为空。
- DEFAULT：为属性指定一个默认的值。
- CHECK （attr > 0）：限定当前属性数据的值在一个范围以内。
- UNIQUE：确保一个属性的值或者一个表内属性的独特性，即不能出现重复的值或者属性。
- PRIMARY KEY：当前属性作为主键，在表中唯一识别一个元组。
- FOREIGN KEY：在两个关系表之间执行实现引用完整性。
- INDEX：使用索引更快速访问表的特定行。
其中，PRIMARY KEY 和 UNIQUE 约束的属性自带 NOT NULL 的约束，也就是说，被约束为主键或者独特的属性，必然不能为空。
如果主键只包含一个属性，那么 PRIMARY KEY 可以定义在属性后面；如果主键包含多个属性，那么 PRIMARY KEY 要定义为表约束，如：
-- PRIMARY KEY 定义在属性后
CREATE TABLE Course
(No VARCHAR(20) PRIMARY KEY,
Cname VARCHAR(50) UNIQUE,
Unit SMALLINT not null Default 6);
-- PRIMARY KEY 定义在表约束中
CREATE TABLE Enrol
(StudentID INT not null CHECK (StudentID>0),
CourseNo VARCHAR(20) not null,
Semester VARCHAR(50) not null,
Status VARCHAR(50),
PRIMARY KEY(StudentID, CourseNo, Semester),
...);
下面是指定 FOREIGN KEY 的一个例子：
CREATE TABLE Enrol
(StudentID INT,
CourseNo VARCHAR(20),
Semester VARCHAR(50),
Status VARCHAR(50),
FOREIGN KEY(StudentID) REFERENCES Student(StudentID),
FOREIGN KEY(CourseNo) REFERENCES Course(No));
INDEX 的例子：
CREATE TABLE Customer
(CustomerID INT not null CHECK (CustomerID>0),
Name VARCHAR(50) not null,
DOB DATE not null,
Address VARCHAR(80),
Phone INT CHECK (Phone>0),
PRIMARY KEY(CustomerID));
CREATE INDEX index1 ON Customer (Name, DOB);
CREATE UNIQUE INDEX index2 ON Customer (Phone);
4. 更改与删除表
使用 ALTER TABLE 常用来更改已经存在的表的内容，可以作出的更改包括：
-
修改表名
-
添加或删除属性
-
修改属性的定义
-
添加或删除表的约束
ALTER TABLE Customer ALTER COLUMN Address SET NOT NULL;
ALTER TABLE Customer ADD UNIQUE(Phone);
ALTER TABLE Customer ADD CONSTRAINT positive_id CHECK (CustomerID > 0);
ALTER TABLE Enrol ADD FOREIGN KEY(StudentID) REFERENCES Student(StudentID);
ALTER TABLE Customer ADD Email VARCHAR(100);
使用 DROP TABLE 常用于移除一个已创建的表
DROP TABLE Enrol;
DROP TABLE IF EXISTS Customer;
数据操纵语言 （DML: Data Manipulation Language）
数据操纵语言主要包括四种命令：INSERT，UPDATE，DELETE，SELECT。
1. INSERT 插入命令
INSERT 命令常用于向一个表添加新的元组或者新的项，其主要格式为：
INSERT INTO table_name
[(attribute_name,...,attribute_name)]
VALUES (value,...,value),...,(value,...,value);
该系列的前一篇文章里曾经提到过关系型数据库完整性约束的内容：当我们执行 INSERT，UPDATE，DELETE 命令时，可能会导致违反完整性约束的情况。关于完整性约束的内容，可以去看这篇博客：【数据库】数据库入门（二）: 关系型数据库。
违反主键约束
主键作为在一个表中独立识别一个元组的标志，在不同的元组中，其主键对应的值不能相同，否则违反了主键约束。例如在下表中：
| <u>StudentID</u> | Name | DoB | |
|---|---|---|---|
| 456 | Tom | 25/01/1988 | tom@gmail.com |
| 458 | Peter | 20/02/1991 | peter@hotmail.com |
| ... | ... | ... | ... |
如果在这个表中，我们再执行以下插入命令：
INSERT INTO Student(StudentID, Name, DoB, Email)
VALUES (456, 'Smith', '27/08/1989', 'smith@gmail.com');
即插入一个主键同样为456，但其余值不同的一个新的元组。如果该元组能添加到表中，则存在两个元组的主键即 StudentID 为456，那么我们通过主键 StudentID 并不能识别出一个独立的元组，因此违反了主键约束，故上面这条插入命令不会被正确执行。DBMS 不允许两个主键值相同的元组存在于一个表中。
违反外键约束
违反外键约束常常出现在多个表相互引用的情况下，即在一个表中新添加的元组，其外键对应的值并没有出现在另一个表中时，就会导致违反外键约束。例如有以下两个表：
| <u>StudentID</u> | Name | DoB | |
|---|---|---|---|
| 456 | Tom | 25/01/1988 | tom@gmail.com |
| 458 | Peter | 20/02/1991 | peter@hotmail.com |
| ... | ... | ... | ... |
| <u>StudentID</u> | <u>CourseNo</u> | <u>Semester</u> | Status | EnrolDate |
|---|---|---|---|---|
| 456 | COMP1111 | 2017 S1 | active | 25/02/2017 |
| 458 | COMP2222 | 2017 S2 | active | 09/06/2017 |
然后我们执行以下插入命令：
INSERT INTO Enrol(StudentID, CourseNo, Semester, Status)
VALUES (460, 'COMP2400', '2016 S2', 'active');
可以想象这么一个现实情况：在某一个学期里数据库储存了一个学生在该学期参加某个课程的注册信息，然而该学生却并没有个人记录在学校的数据中，这种情况是非常不合理的。注册信息表必须依赖于学生信息表而存在，因此上面这条命令不能执行成功。DBMS 不会允许一个表中存在这么一个元组：其外键对应的值，在外键对应的引用表中却不存在。
2. UPDATE 命令
UPDATE 命令常用于修改一个或者多个元组中相应属性的值，其主要格式为：
UPDATE table_name
SET attribute_name = value,...,attribute_name = value
[WHERE selection_condition];
3. DELETE 命令
DELETE 命令常用于从表中移除存在的元组，其主要格式为：
DELETE FROM table_name
[WHERE selection_condition];
执行删除命令可以根据具体的条件进行删除操作，也可以删除表中所有的元组。
-- Delete the tuple whose studentID equals to 456.
DELETE FROM Student WHERE StudentID=456;
-- Delete all tuple in Student relation.
DELETE FROM Student;
这里需要注意区别 DELETE 和 DROP 作用是不一样的。DELETE FROM Student 会清空 Student 表内的所有元组，执行命令后该表依然存在，且为空表；而 DROP TABLE Student 不仅清空所有元组，还会删除 Student 这个表，执行命令后该表将不再存在。
引用表之间的行为
由于关系型数据库存在表间的相互引用关系，所以我们会希望在修改一个表中的内容时，其具有引用关系的表中对应内容也能够跟随着同步改变。所以 SQL 提供了以下的关键字：
- NO ACTION（default）：当修改或者删除一个存在引用关系的元组时，系统会抛出一个错误提示 error。
- CASCADE：删除或者更新操作会随着所有引用关系同步进行。
- SET NULL：执行删除或者更新操作后，存在引用关系的元组的对应属性会置为 NULL。
- SET DEFAULT：执行删除或者更新操作后，存在引用关系的元组的对应属性会置为一个指定的默认值。
以上的关键字常用于定义表中作为外键的属性，下面是一个例子：
CREATE TABLE Enrol
(StudentID INT,
CourseNo VARCHAR(20),
Semester VARCHAR(50),
Status VARCHAR(50),
FOREIGN KEY(StudentID) REFERENCES Student(StudentID)
ON DELETE NO ACTION ,
FOREIGN KEY(CourseNo) REFERENCES Course(No));
4. SELECT 命令
SELETE 命令常用于从数据库中检索数据，其主要格式如下：
SELECT attribute_list
FROM table_list
[WHERE condition]
[GROUP BY attribute_list [HAVING group_condition]]
[ORDER BY attribute_list [ASC | DESC]];
image.gif
SELETE 后面跟的是想要显示的属性，如果使用星号 “*”，代表要获取指定表的所有属性的值。
FROM 后面跟的是所要获取的表的名字。如果是多个表用逗号 “，” 隔开，则取的是两个表所有元组分别进行笛卡尔积后结合的结果。如果使用 JOIN 关键字，则会根据具体的 JOIN 的形式将多个表进行结合，并从中选出目标属性值。
WHERE 关键字后跟的是取值条件，条件通常为判断某个属性是否为某个指定值或者是否满足数值上的大小关系等。
GROUP BY 关键字会按照后面指定属性进行分组，即该属性值相同的元组将会归为一个组，后面可以使用 HAVING 关键字，其功能与 WHERE 类似，不过它指定的所划分的组中所有元组需要满足的条件。GROUP BY 关键字通常会跟以下的内置函数结合使用：
- COUNT：统计值不为 NULL 的条目的个数。
- AVG：求指定参数的平均值。
- MIN：求指定参数的最小值。
- MAX：求指定参数的最大值。
- SUM：求指定参数的总和。
ORDER BY 对检索的内容按照给定属性的值进行排序，默认按照升序排序，也可以使用关键字 ASC 指定升序或者 DESC 指定降序。
关于 SELETE 命令的深入内容，在下一篇文章中会进行详细介绍，并且提供一些例子的讲解。
【数据库】数据库入门（四）: SQL查询 - SELETE的进阶使用
参考资料
维基百科：https://zh.wikipedia.org/wiki/SQL
数据库基础：Fundamentals of Database Systems, 7th Edition (Global Edition), R. Elmasri and S. Navathe, 2017
