数据库技术——第3章 关系型数据库标准语言SQL

第3章 关系型数据库标准语言SQL

基本概念

1. SQL语言其本身是一种用于关系型数据库的通用语言，有相关的国际标准来定义它。但是要注意的是，不同的DBMS对SQL语言的支持度都不同，有些还会添加自己的扩展功能

2. SQL中定义了关于数据查询、操纵、定义、控制的功能

3. SQL具有的特点

   • 综合统一：集多种功能于一体

   • 高度非过程化：只需要提出“做什么”，不需要指明“怎么做”

   • 面向集合的操作方式：针对一个个集合而不是记录进行操作

   • 以同一种语法结构提供多种使用方式：在不同应用中，语法基本固定

   • 语言简洁，易学易用：设计的简单，用户使用方便

4. 采用三级模式结构的概念，具有内模式、模式、外模式

5. 本系列笔记均以MySQL中的SQL语句为准

数据定义

一个DBMS中可以存在多个数据库，并且对他们进行管理。

在三级模式结构中可以定义模式、定义表、定义视图、定义索引。模式可以理解为一些表、视图、索引的集合，在MySQL中类似于一个数据库。

模式定义

创建模式

使用下面语句创建一个数据库，可以指定字符集名和排序规则名，字符集指明了数据库会以怎样的编码存储数据，排序规则则指明了在数据库中字符串比较的方式

CREATE DATABASE [IF NOT EXISTS] <数据库名称>
[CHARACTER SET <字符集名>] 
[COLLATE <排序规则名>];

删除模式

使用下面的语句可以删除一个数据库

DROP DATABASE [IF EXISTS] <数据库名>

查看数据库

查看某数据库的具体信息，即展示该数据库的定义语句

SHOW CREATE DATABASE <数据库名>

在操作某个数据库前，需要切换当前操作数据库到目标上，使用下面的语句进行切换

USE <数据库名>

修改模式

使用下面语句对某个数据库的属性进行修改，若不指定数据库名，默认操作当前使用的数据库，字符集修改或排序规则修改二者必须有一个

ALTER DATABASE [数据库名] 
[CHARACTER SET <字符集名>]
[COLLATE <排序规则名>]

基本表定义

MySQL中的数据类型

1. 数值类型

   • tinyint、smallint、mediumint、int、bigint

     均为整数，字节大小分别为1、2、3、4、8，在使用时都可以指定一个显示宽度，例如int(5)表示会显示成5位数字宽度的，但所占字节大小不会改变，也就是能表示数的范围不变

   • decimal(p,d)

     定点数，共由p个数字组成，小数点后d个，也就是整数部分有p-d个

   • float、double

     单精度和双精度浮点数，分别占4字节、8字节

2. 日期类型

   • date

     包括年月日的日期，格式为YYYY-MM-DD，占3字节

   • time

     包括时分秒的时间，格式为HH:MM:SS，占3字节

   • datetime

     前两种的结合，格式为YYYY-MM-DD HH:MM:SS，占8字节

   • timestamp

     时间戳类型，占4字节

3. 字符串类型

   • char、varchar

     分别为定长和变长字符串，都需要指定长度，对于varchar指定的是最多字符个数

   • tinyblob、blob、mediumblob、longblob

     二进制文本数据，可以指定能够存储的最大字节数

   • tinytext、text、mediumtext、longtext

     文本数据，可以指定能存储的最大字符数

创建表

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

定义各列的名称、数据类型，数据类型，在MySQL中，对于数值类型列级完整性约束条件可以使用unsigned、signed来指定其是否带符号

删除表

DROP TABLE [IF EXISTS] 表名1 [, 表名2,...] [RESTRICT | CASCADE]

可以指定多个表，同时删除一个数据库中的多个表。其中的restrict是默认选项，以此模式删除表时，目标表不能有被引用、有视图、有触发器等，有的话就会无法删除；以cascade模式删除表时，不会做上述检查，同时会把与将要删除的表的所有相关对象一起删除

查看表

在进入某个数据库后，查看该数据库中有哪些表

SHOW TABLES;

查看某个表的简述以及查看某个表的定义语句

DESC <表名>
SHOW CREATE TABLE <表名>

修改表

ALTER TABLE <表名>
[ADD <新列名> <数据类型> [完整性约束]]
[ADD <表级完整性约束>]
[CHANGE COLUMN <旧列名> <新列名> <新列类型>]    
[DROP <表级完整性约束名>]
[DROP COLUMN <列名>]
[MODIFY COLUMN <列名> <数据类型>]；
[RENAME TO <新表名>]
[CHARACTER SET <字符集名>]
[COLLATE <排序规则名>]

对于每个表，可以单独设置字符集和排序规则，若不设置，默认会继承所属数据库的这两个对应规则

索引定义

创建索引

CREATE [UNIQUE][CLUSTER] INDEX <索引名>
ON <表名>(<列名> [ASC|DESC] [,<列名> [ASC|DESC]]...); 

索引可以建立在一列或多列上，对于每列可以选择索引后的排序顺序，默认为ASC升序。另外，unique表示每个索引值对应唯一的数据记录，cluster表示建立聚簇索引

修改索引

出于内部实现与保证正确性的考虑，MySQL中没有真正意义上的修改索引，只有先删除之后在创建新的索引才可以达到修改的目的。

ALTER INDEX <旧索引名> RENAME TO <新索引名>

删除索引

DROP INDEX <索引名> ON <表名>

视图定义

创建视图

使用select语句可以创建视图，with check option选项表示对视图进行修改与插入操作时要满足子查询中的条件表达式。若不指定列名，则列名默认使用SELECT子查询中选择的列名

/* 一般格式 */
CREATE VIEW <视图名> [(<列名> [,<列名>]...)]
AS <SELECT语句>
[WITH CHECK OPTION];

# 例子
CREATE VIEW IS_Student
AS SELECT Sno, Sname, Sage FROM Student
    WHERE Sdept='IS';

删除视图

将视图定义从数据字典中删除

/* 一般格式 */
DROP VIEW <视图名>;

# 例子
DROP VIEW IS_Student;

修改视图

修改视图的定义

/* 一般格式 */
ALTER VIEW <视图名> AS <SELECT语句>;

数据查询

MySQL中数据查询的一般格式为

SELECT [ALL|DISTINCT] <目标列表达式> [,<目标列表达式>] …
FROM <表名或视图名>[,<表名或视图名>] … | (<SELECT语句>) [AS] <别名>
[WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC|DESC]];

其基本含义为：根据where子句中的条件，从from子句指定的基本表、视图或派生表中找出满足条件的元组，再按select子句中指定的目标列表达式计算元组中的属性值以形成结果表

另外group by用于分组，常常会与聚集函数一起使用；order by用于对结果表的排序

根据数据查询涉及的表，将数据查询大致分为单表查询、连接查询、嵌套查询、集合查询四种，以下均以例子叙述各类查询的用法

使用到的样例表

单表查询

目标列表达式

“目标列表达式”可以是属性列、算术表达式、字符串常量、函数等

/* 使用属性列 */
SELECT Sno,Sname FROM Student;
# 星号*表示选中所有属性列
SELECT * FROM Student;


/* 使用算术表达式、字符串常量、函数 */
SELECT Sname,'Year of Birth:',2014-Sage,LOWER(Sdept) 
FROM Student;

/* 通过指定别名来改变结果表的列标题 */
# 在目标列表达式后，加个空格接别名即可
SELECT Sname '姓名',LOWER(Sage)'专业' FROM Student;

select子句中，默认使用ALL形式，即展示结果表中的所有元组；若使用DISTINCT，结果表中相同的元组只会显示一次

条件表达式

where子句中的条件表达式，可以指定简单的查询条件，也可以使用更复杂的关键词组成条件

MySQL支持的算术运算符：+、-、*、/、%或MOD，即加减乘除和取余

MySQL支持的逻辑运算符：NOT或!、AND或&&、OR或||、XOR，即非与或和异或

MySQL支持的比较运算符：=、<=>、<>或!=、<=、>=、>、<、IS NULL或ISNULL、IS NOT NULL、BETWEEN a AND b，分别表示等于、安全的等于、不等于、小于等于、大于等于、小于、大于、为空、不为空、是否在[a,b]之间

此处需要注意的是，=不能用于NULL的比较，而<=>可以。NULL <=> NULL的值为真；两边只有一个为NULL时（例如0 <=> NULL），值为假

另外，MySQL还支持位运算符：|、&、^、~、<<、>>，分别表示按位或、按位与、按位异或、各位取反、位左移、位右移

IN、NOT IN可以用来判断元组某列的值是否在某集合中

LIKE用来匹配目标字符串，SQL中用%来表示0个或多个字符、用_表示一个字符，MySQL默认的转义符号为“\”

/* 查询年龄在[26,28]区间内学生的信息 */
SELECT * FROM Student
WHERE Sage BETWEEN 26 AND 28;

/* 查询某些专业学生的姓名学号 */
SELECT Sname '姓名', Sno '学号', Sdept '专业' FROM Student
WHERE Sdept IN ('CS', 'IS');

/* 字符串模糊查询LIKE */
SELECT * FROM Student
WHERE Sdept LIKE '_S';
# 查询专业名中包含%学生的信息
SELECT * FROM Student
WHERE Sdept LIKE '%\%%';
# 上面一条查询与下面等价，自定义转义符号为井号“#”
SELECT * FROM Student
WHERE Sdept LIKE '%#%%' ESCAPE '#';


/* 使用逻辑运算符实现多条件查询 */
# 查询出所有成年男学生
SELECT * FROM Student
WHERE Sage >= 18 AND Ssex = '男';

聚集函数

用于对数据进行聚集计算的函数

COUNT(*)统计元组个数、COUNT([DISTINCT | ALL] <列名>)统计某列中值的个数、SUM([DISTINCT | ALL] <列名>)对某一列求和、AVG([DISTINCT | ALL] <列名>)对某一列求平均值、MAX([DISTINCT | ALL] <列名>)求某列最大值、MIN ([DISTINCT | ALL] <列名>)求某列最小值

需要注意的是，除了COUNT(*)外，其他聚集函数都会跳过NULL不处理

# 求1号课程最高分
SELECT MAX(Grade) FROM SC
WHERE Cno='1';

GROUP BY与HAVING

将查询结果的各列进行分组，值相等的为一组。主要是为了将聚集函数只作用于某部分数据，即对于分组后的每组数据都会出一个计算结果

而having <条件表达式>跟在group by的后面，用于选择分组之后满足条件的组

# 列出每门课程的平均分
SELECT Cno, AVG(Grade) FROM SC
GROUP BY Cno;

# 查询选了两门课以上的学生学号
SELECT Sno FROM SC
GROUP BY Sno
HAVING COUNT(*)>2;

# 查询均分不小于88的学生学号及其均分
SELECT Sno '学号',AVG(Grade) '平均成绩' FROM SC
GROUP BY Sno
HAVING NOT AVG(Grade) < 90;

ORDER BY

使用ORDER BY <列名> [ASC | DESC]对结果按某列进行排序，默认为ASC升序

# 列出每门课程的平均分，按平均分从高到低排序
SELECT Cno '课程号', AVG(Grade) '平均分' FROM SC
GROUP BY Cno
ORDER BY '平均分' DESC;

空值判断

IS NULL与IS NOT NULL用于判断是否为空值

# 查询没分专业的学生
SELECT * FROM Student
WHERE Sdept IS NULL;

连接查询

就是将多张表连接后再进行查询，需要指出通过哪一列进行连接，即指定连接条件。从之前的关系代数运算中可以得知，连接包括了等值连接、自然连接、非等值连接、外连接等

外连接运算符：<表名1> LEFT OUTER JOIN <表名2>、<表名1> RIGHT OUTER JOIN <表名2>，左外连接会列出表1中的所有元组，右外连接会列出表2中的所有元组

需要注意的是，外连接应使用在from子句中，另外MySQL中并没有全外连接

/* 等值连接，即连接条件使用等号 */
# 求各学生的成绩单（连接Student和SC）
SELECT Student.*, SC.* FROM Student, SC
WHERE Student.Sno=SC.Sno;

/* 自然连接，即去除重复的列，需要自己完成列的选择 */
# 对于重名的列，使用表名.列名来指定
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Student, SC
WHERE Student.Sno = SC.Sno;

/* 在FROM子句中，表名后可以指定对该表的别名，使得同一个表可以用好几次 */
# 找到课程先修课的先修课
SELECT X.Cno, Y.Cpno FROM Course X, Course Y
WHERE X.Cpno=Y.Cno;

/* 可以同时连接多个表 */
# 查询每个学生的学号姓名、选修课程的名称及成绩
SELECT Student.Sno, Cname, Grade FROM Student, Course, SC
WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno;

/* 外连接 */
# 左外连接，Student表中的所有元组都会被展现
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Student LEFT OUTER JOIN SC
ON Student.Sno = SC.Sno;

嵌套查询

在某一个查询结果的基础上再进行查询，上层的查询块称为外层/父查询，下层查询块称为内层/子查询。若要使用order by对结果进行排序，只能对最外层查询结果进行排序

有些嵌套查询可以用连接完成，但有些是无法完成的。另外，对于有些查询，内层查询与外层查询之间没有依赖关系，称为不相关子查询；还有些内层查询会依赖于外层查询，称为相关子查询

显然，当子查询返回的结果只有一个值时，可以直接使用比较运算符；而子查询返回了多个值时，使用ANY、ALL进行处理

另外，对于关系代数中的除法，往往需要使用EXISTS进行处理，以表示“存在”的含义。外层查询的元组拿到exists中进行子查询，若查询结果为空，返回false，即外层查询中的该元组不选中

全称量词与蕴含关系的运算需要转换为exists进行，即转换为存在

$$\begin{gathered} (\forall x)P=\neg (\exists x(\neg P)) \\ (\forall y)p\to q=\neg \exists (p\wedge \neg q) \end{gathered}$$

例如，“选修了全部课程”转换为“不存在一门课程不选修”，“选修了学生A选修的全部课程”转换为“不存在一门课程，学生A选了，但目标学生没选”

/* 带有IN的子查询 */
# 查询与刘晨同一个专业的学生
SELECT Sno, Sname, Sdept FROM Student
WHERE Sdept IN (
    SELECT Sdept FROM Student
    WHERE Sname='刘晨'
);
# 也可以使用连接完成
SELECT Y.Sno, Y.Sname, Y.Sdept FROM Student X, Student Y
WHERE X.Sdept=Y.Sdept AND X.Sname='刘晨';


/* 带有比较运算符的子查询 */
# 这个是相关子查询，逐一取得外层的一个元组到内层查询中使用，得到结果后再返回外层查询
# 查询每个学生超过其所选课程平均成绩的课程
SELECT X.Sno, X.Cno FROM SC X
WHERE X.Grade >= (
    SELECT AVG(Y.Grade) FROM SC Y
    WHERE X.Sno = Y.Sno
);

/* 带有ANY或ALL的子查询 */
# 查询非计科专业中比所有计科专业学生年龄都小的学生
SELECT Sname, Sage FROM Student
WHERE Sage < ALL (
    SELECT Sage FROM Student
    WHERE Sdept='CS'
) AND Sdept != 'CS';


/* 带有EXISTS的子查询 */
# 查询所有选修了1号课程的学生姓名
SELECT Sname FROM Student
WHERE EXISTS (
    SELECT * FROM SC
    WHERE Student.Sno=SC.Sno AND SC.Cno='1'
);

# 查询选修了全部课程的学生姓名
# 即不存在一门课程，不存在于该学生选的课中
SELECT Sname FROM Student
WHERE NOT EXISTS (
    SELECT * FROM Course
    WHERE NOT EXISTS (
        SELECT * FROM SC
        WHERE SC.Sno=Student.Sno AND SC.Cno=Course.Cno
    )
);

# 查询至少选修了学生201215122选修的全部课程的学生号码
# 即不存在一门课程，201215122选了但不存在于x选的课中
SELECT DISTINCT Sno FROM SC X
WHERE NOT EXISTS (
    SELECT * FROM SC Y
    WHERE Y.Sno='201215122' AND NOT EXISTS (
        SELECT * FROM SC Z
        WHERE Z.Cno=Y.Cno AND Z.Cno=X.Cno
    )
)；

集合查询

对多个查询结果进行一些集合操作，包括并、交、差。参加集合操作的各查询结果的列数必须相同，且对应项的数据类型也必须相同

并交差分别为UNION、INTERSECT、EXCEPT，MySQL中只支持union，使用union合并多个查询结果，会自动去掉重复元组；union all会保留重复元组

/* 集合操作 */
# 选修1号课程与选修2号课程的学生的并集
SELECT Sno FROM SC
WHERE Cno='1'
UNION
SELECT Sno FROM SC
WHERE Cno='2';

基于派生表查询

上面的几种查询，子查询都位于where子句中，事实上可以将子查询放在from子句中形成临时的派生表，成为主查询的查询对象。必须为派生表指定别名

# 查询每个学生超过自己平均成绩的课程
SELECT Student.Sname, SC.Cno 
FROM Student, SC, (SELECT Sno, AVG(Grade) avg_grade FROM SC GROUP BY Sno) Avg_sc
WHERE Student.Sno=SC.Sno AND SC.Sno=Avg_sc.SNO AND Grade>Avg_sc.avg_grade;

数据更新

插入数据

可以一次插入单条元组、多条元组，还可以插入子查询结果

/* 直接插入元组 */
# 插入单条元组
INSERT INTO <表名> [(<属性列1> [,<属性列2>]...)]
VALUES (<常量1> [,<常量2>]...);

# 插入多条元组
INSERT INTO <表名> [(<属性列1> [,<属性列2>]...)]
VALUES (<常量1> [,<常量2>]...),
(<常量1> [,<常量2>]...),
...

# 例子
INSERT INTO Student
VALUES ('200215121', '李勇', '男', 20, 'CS'),
    ('200215122', '刘晨', '女', 19, 'CS');

INSERT INTO Student (Sno, Sname, Sage, Ssex)
VALUES ('200215121', '李勇', 20, '男');


/* 插入子查询结果 */
INSERT INTO <表名> [(<属性列1> [,<属性列2>]...)]
子查询;

# 求得存储各专业学生平均能力的表
CREATE TABLE Dept_age (
    Sdept VARCHAR(15),
    Avg_age INT
);
INSERT INTO Dept_age
SELECT Sdept, AVG(Sage) FROM Student
GROUP BY Sdept;

修改数据

可以根据条件对数据进行修改，可以使用一些表达式进行更新。其中where子句中的条件表达式可以带有子查询

/* 一般格式 */
UPDATE <表名> SET <列名>=<表达式> [,<列名>=<表达式>]
[WHERE <条件表达式>];

# 将所有学生的年龄增加1
UPDATE Student SET Sage=Sage+1;

删除数据

根据条件删除元组，同样可以删除多个元组，where子句中也可以带有子查询

/* 一般格式 */
DELETE FROM <表名>
[WHERE <条件>]

# 删除学号为201215128的学生
DELETE FROM Student
WHERE Sno='201215128'

# 删除所有计科专业学生的选课记录
DELETE FROM SC
WHERE Sno IN (
    SELECT Sno FROM Student
    WHERE Sdept='CS'
);

空值

空值可以用is null和is not null来判断，not null可以作为约束条件

空值与其他任何东西进行算术运算和比较运算得到的结果都是unknown，unknown可以进行逻辑运算

unknown参与逻辑运算得到的结果根据逻辑判断的熔断机制就可以得到（下面的公式中U表示unknown）

• T and U = U、T or U = T

• U and U = U、U or U = U

• not U = U

视图

视图可以建立在一个基本表、多个基本表、已有视图、已有视图和基本表上。DBMS只会把视图的定义存在数据字典中，并不会执行

在创建视图时，可以使用表达式得到原有表上没有的列，这样的列称为虚拟列

在创建视图指定列名的时候，要么全部指定，要么全部不指定使用select子句中的名字

在创建好视图之后，可以像基本表一样对其进行查询、删除、修改。在查询过程中，DBMS会结合视图的定义和用户的查询进行执行，这个结合的转换过程称为视图消解。在插入数据过程中，对视图的插入和更新会转换为相关基本表的插入和更新

• 行列子集视图：从单个基本表导出的，保留了主码，只是去掉了某些行某些列

• 分组视图：定义中带有聚集函数和group by子句

需要注意的是，有些视图由于无法转换为相关基本表的操作，无法进行更新操作，特别是对于虚拟列，显然是无法进行更新的