秋招 基础知识之——数据库 SQL篇

     内容参考《数据库系统概论》（第五版），以及github笔记：

https://github.com/CyC2018/Interview-Notebook/blob/master/notes/SQL.md#%E5%8D%81%E5%85%AD%E7%BB%84%E5%90%88%E6%9F%A5%E8%AF%A2

      

      结构化查询语言（Structured Query Language，SQL）是关系数据库的标准语言，也是一个通用的、功能极强的关系数据库语言。其功能不仅仅是查询，而是包括数据库模式创建、数据库数据的插入与修改、数据库安全性完整性定义与控制等一系列功能。

一、基本概念

支持SQL的关系数据库管理系统同样支持关系数据库三级模式结构。如上图，其中外模式包括若干视图（view）和部分基本表，模式包括若干基本表，内模式包括若干存储文件（stored file）。

用户可以用SQL对基本表和视图进行查询或其他操作，基本表和视图一样，都是关系。

基本表是本身独立存在的表，在关系数据库管理系统中一个关系就对应一个基本表。一个或多个基本表对应一个存储文件，一个表可以带若干索引，索引也存放在存储文件中。

存储文件的逻辑结构组成了关系数据库的内模式，存储文件的物理结构对最终用户是隐蔽的。

视图是从一个或几个基本表导出的表。它本身不独立存储在数据库中，即数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。视图在概念上与基本表等同，用户可以在视图上再定义视图。

二、SQL的数据定义

一个关系数据库管理系统的实例中可以建立多个数据库，一个数据库中可以建立多个模式，一个模式下通常包括多个表、视图和索引等数据库对象。

1、模式的定义与删除

（1）定义模式

CREATE SCHEMA<模式名>AUTHORIZATION<用户名>;

例：CREATE SCHEMA TEST AUTHORIZATION WANG;

要创建模式，调用该命令的用户必须拥有数据库管理员权限，或者获得数据库管理员授予的CREATE SCHEMA的权限。

若没有指定<模式名>，则<模式名>隐含为<用户名>。

CREATE SCHEMA中可以接受CREATE TABLE,CREATE VIEW和GRANT子句。即用户可以在创建模式的同时在这个模式定义中进一步创建基本表、视图，定义授权。

CREATE SCHEMA<模式名>AUTHORIZATION<用户名>[<表定义子句>|<视图定义子句>|<授权定义子句>];

例：

CREATE SCHEMA TEST AUTHORIZATION ZHANG
CREATE TABLE TAB1(COL1 SMALLINT,
                  COL2 INT,
                  COL3 CHAR(20),
                  COL4 NUMERIC(10,3),
                  COL5 DECIMAL(5,2)
                  );

（2）删除模式

DROP SCHEMA<模式名><CASCADE|RESTRICT>;

其中CASCADE和RESTRICT两者必选其一。

CASCADE（级联），表示在删除模式的同时把该模式中所有的数据库对象全部删除；

RESTRICT（限制），表示如果该模式中已经定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行，只有当该模式中没有任何下属的对象时才能执行DROP SCHEMA语句。

2、基本表的定义、删除与修改

（1）定义基本表

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

例：

CREATE TABLE Student
(Sno CHAR(0) PRIMARY KEY,  /*列级完整性约束条件，Sno是主码*/
Sname CHAR(20) UNIQUE,  /*Sname取唯一值*/
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
);

（2）数据类型

（3）修改基本表

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

（4）删除基本表

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

默认RESTRICT。

3、索引的建立与删除

（1）建立索引

CREATE [UNIQUE][CLUSTER] INDEX <索引名> ON <表名>(<列名>[<次序>][,<列名>[<次序>]]...);

次序可选ASC（升序，默认）或DESC（降序）。

UNIQUE表明此索引的每一个索引值只对应唯一的数据记录。

CLUSTER表示要建立的索引是聚簇索引。

（2）修改索引

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

（3）删除索引

DROP INDEX<索引名>;

三、SQL的数据查询

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

1、单表查询

（1）选择表中的若干列

• 查询指定列

SELECT Sno,Sname
FROM Student;

• 查询全部列

SELECT *
FROM Student;

• 查询经过计算的值

SELECT Sname,2018-Sage   /*查询姓名和出生年份*/
FROM Student;

不仅可以是算术表达式，还可以是字符串常量（‘字符串内容’），函数等。

LOWER（Sdept）代表用小写字母表示Sdept的内容。

（2）选择表中的若干元组

• 消除取值重复的行

SELECT DISTINCT Sno
FROM Student;

• 查询满足条件的元组

查询满足指定条件的元组可以通过WHERE子句实现。

①比较大小

②确定范围

BETWEEN...AND...和NOT BETWEEN...AND...可以用来查找属性值在（或不在）指定范围内的元组，其中BETWEEN后面是范围的下限，AND后面是范围的上限。

③确定集合

谓词IN和NOT IN可以用来查找属性值属于（或不属于）指定集合的元组。

④字符匹配

谓词LIKE可以用来进行字符串的匹配：

[NOT] LIKE <'匹配串'> [ESCAPE'<换码字符>']

其含义是查找指定的属性列值与<匹配串>相匹配的元组。<匹配串>可以是一个完整的字符串，也可以含有通配符%和_。其中：

1）%（百分号）代表任意长度的字符串。

2）_（下横线）代表任意单个字符。

注意：数据库字符集为ASCII时一个汉字需要两个_；当字符集为GBK时只需要一个_。

如果用户要查询的字符串本身就含有通配符%和_，这时就要使用ESCAPE'<换码字符>'短语对通配符进行转义了。

其中，ESCAPE '\' 表示“\”为换码字符。这样匹配串中紧跟在“\”后面的字符“_”不再具有通配符的含义，转义为普通的“_”字符。

⑤涉及空值的查询

若要查询相应属性为空值的话，要用IS NULL，而不能用（=NULL）。

⑥多重条件查询

逻辑运算符AND和OR可用来连接多个查询条件。AND的优先级高于OR，但用户可以用括号改变优先级。

（3）ORDER BY子句

用户可以用ORDER BY子句对查询结果按照一个或多个属性列的升序（ASC）或降序（DESC）排列，默认值为升序。

（4）聚集函数

聚集函数只能用于SELECT子句和GROUP BY中的HAVING子句。不能在WHERE子句中作为条件表达式

如果使用DISTINCT短语，则表示在计算时要取消指定列中的重复值。如果不指定DISTINCT短语或指定ALL短语（ALL为默认值），则表示不取消重复值。

（5）GROUP BY子句

GROUP BY子句将查询结果按某一列或多列的值分组，值相等的为一组。

分组后聚集函数将作用于每一个组，即每一组都有一个函数值。

WHERE子句和HAVING短语的区别在于作用对象不同。

WHERE子句作用于基本表或视图，从中选择满足条件的元组。

HAVING短语作用于组，从中选择满足条件的组。

2、连接查询

前面的查询都是针对一个表进行的。若一个查询同时涉及两个以上的表，则称之为连接查询。连接查询是关系数据库中最主要的查询，包括等值连接查询、自然连接查询、非等值连接查询、自身连接查询、外连接查询和复合条件连接查询。

（1）等值与非等值连接查询

连接查询的WHERE子句中用来连接两个表的条件称为连接条件或连接谓词。

一般格式为：

[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>

其中比较运算符主要有=、>、<、>=、<=、!=(或<>)等。

此外连接谓词还可以使用下面形式：

[<表名1>.]<列名> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>

连接谓词中的列名称为连接字段。连接条件中的各连接字段类型必须是可比的，但名字不必相同。

在等值连接中把目标列中重复的属性列去掉则为自然连接。

（2）自身连接

一个表与其自己进行连接，称为表的自身连接。

（3）外链接

LEFT OUTER JOIN和RIGHT OUTER JOIN可以简写为LEFT JOIN和RIGHT JOIN。

（4）多表连接

两个以上的表进行连接称为多表连接。

3、嵌套查询

一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询（nested query）。

例如：

SELECT Sname             /*外层查询或父查询*/ 
FROM Student
WHERE Sno IN
         (SELECT Sno     /*内层查询或子查询*/ 
          FROM SC
          WHERE Cno='2');

上层的查询块称为外层查询或父查询，下层的查询块称为内层查询或子查询。

注意：子查询的SELECT语句中不能使用ORDER BY子句，ORDER BY子句只能对最终查询结果排序。

（1）带有IN谓词的子查询

子查询返回的是一个集合。

子查询的查询条件不依赖于父查询，则称为不相关子查询。

子查询的查询条件依赖于父查询，则称为相关子查询。

（2）带有比较运算符的子查询

确切知道内层查询返回的是单个值。

（3）带有ANY（SOME）或ALL谓词的子查询

子查询返回单值时可以用比较运算符，但返回多值时要用ANY（有的系统用SOME）或ALL谓词修饰符。

使用ANY或ALL谓词时必须同时使用比较运算符。

这种查询也可以用聚集函数来实现：

  等同于  

事实上，用聚集函数实现子查询通常比直接用ANY或ALL查询效率要高。

ANY、ALL与聚集函数的对应关系如表所示：

（4）带有EXISTS谓词的子查询

带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。若内层查询结果非空，则外层的WHERE子句返回真值，否则返回假值。

由EXISTS引出的子查询，其目标列表达式通常都用 * ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。

与EXISTS谓词相对应的是NOT EXISTS谓词，若内层查询结果为空，则外层的WHERE子句返回真值，否则返回假值。

4、集合查询

SELECT语句的查询结果是元组的集合，所以多个SELECT语句的结果可以进行集合操作。

集合操作主要包括：并操作UNION、交操作INTERSECT、差操作EXCEPT。

注意：参加集合操作的各查询结果的列数必须相同，对应项的数据类型也必须相同。

使用UNION将多个查询结果合并起来时，系统会自动去掉重复元组。若要保留重复元组，则用UNION ALL操作符。

5、基于派生表的查询

子查询不仅可以出现在WHERE子句中，还可以出现在FROM子句中，这时子查询生成的临时派生表（derived table）称为主查询的查询对象。

例如，找出每个学生超过他自己选修课程平均成绩的课程号：

这里FROM子句中的子查询将生成一个派生表Avg_sc，该表由avg_sno和avg_grade两个属性组成。

注意：通过FROM子句生成派生表时，AS关键字可以省略，但必须为派生关系指定一个别名。

6、SELECT语句一般格式总结

四、SQL的数据更新

1、插入数据

（1）插入元组

注意：INTO子句中没有出现的属性列，新元组在这些列上将取空值，但是表定义时说明了NOT NULL的属性列不能取空值。如果INTO子句中没有指明任何属性列名，则新插入的元组必须在每个属性列上均有值（空值就写NULL）。

p.s. 字符串常量要用单引号括起来。

（2）插入子查询结果

2、修改数据

修改操作又称为更新操作，其一般格式为：

（1）修改某一个元组的值

（2）修改多个元组的值

（3）带子查询的修改语句

3、删除数据

一般格式为：

DELETE语句的功能是从指定表中删除满足WHERE子句条件的所有元组。如果省略WHERE子句则表示删除表中全部元组，但表的定义仍在字典中。也就是说，DELETE语句删除的是表中的数据，而不是关于表的定义。

（1）修改某一个元组的值

（2）修改多个元组的值

（3）带子查询的修改语句

五、空值的处理

1、空值的产生

插入、修改、外连接、空值的关系运算都会产生空值。

2、空值的判断

判断一个属性的值是否为空值，用IS NULL或IS NOT NULL来表示。

3、空值的约束条件

属性定义（或域定义）中有NOT NULL约束条件的不能取空值，

加了UNIQUE限制的属性不能取空值，

码属性不能取空值。

4、空值的算术运算、比较运算和逻辑运算

在查询语句中，只有使WHERE和HAVING子句中的选择条件为TRUE的元组才被选出作为输出结果。

六、视图

视图是从一个或几个基本表（或视图）导出的表。是一个虚表。数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。

视图一经定义，就可以和基本表一样被查询、被删除。也可以在一个视图之上再定义新的视图，但对视图的更新（增、删、改）操作则有一定的限制。

1、定义视图

（1）建立视图

一般格式为：

关系数据库执行CREATE VIEW语句的结果只是把视图的定义存入数据字典，并不执行其中的SELECT语句。只是在对视图查询时，才按视图的定义从基本表中将数据查出。

（2）删除视图

2、查询视图

3、更新视图

更新视图是指通过视图来插入（INSERT）、删除（DELETE）和修改（UPDATE）数据。

对视图的更新最终要转换为对基本表的更新。

4、视图的作用

（1）简化用户的操作

（2）使用户能以多种角度看待同一数据

（3）对重构数据库提供了一定程度的逻辑独立性

（4）能够对机密数据提供安全保护

（5）适当利用视图可以更清晰地表达查询

七、存储过程

存储过程可以看成是对一系列 SQL 操作的批处理；

1、使用存储过程的好处

• 代码封装，保证了一定的安全性；
• 代码复用；
• 由于是预先编译，因此具有很高的性能。

2、创建存储过程

命令行中创建存储过程需要自定义分隔符，因为命令行是以 ; 为结束符，而存储过程中也包含了分号，因此会错误把这部分分号当成是结束符，造成语法错误。

包含 in、out 和 inout 三种参数。

给变量赋值都需要用 select into 语句。

每次只能给一个变量赋值，不支持集合的操作。

delimiter //

create procedure myprocedure( out ret int )
    begin
        declare y int;
        select sum(col1)
        from mytable
        into y;
        select y*y into ret;
    end //
delimiter ;

call myprocedure(@ret);
select @ret;

八、游标

在存储过程中使用游标可以对一个结果集进行移动遍历。

游标主要用于交互式应用，其中用户需要对数据集中的任意行进行浏览和修改。

使用游标的四个步骤：

1. 声明游标，这个过程没有实际检索出数据；
2. 打开游标；
3. 取出数据；
4. 关闭游标；

delimiter //
create procedure myprocedure(out ret int)
    begin
        declare done boolean default 0;

        declare mycursor cursor for
        select col1 from mytable;
        # 定义了一个continue handler，当 sqlstate '02000' 这个条件出现时，会执行 set done = 1
        declare continue handler for sqlstate '02000' set done = 1;

        open mycursor;

        repeat
            fetch mycursor into ret;
            select ret;
        until done end repeat;

        close mycursor;
    end //
 delimiter ;

九、触发器

触发器会在某个表执行以下语句时而自动执行：DELETE、INSERT、UPDATE

触发器必须指定在语句执行之前还是之后自动执行，之前执行使用 BEFORE 关键字，之后执行使用 AFTER 关键字。BEFORE 用于数据验证和净化。

INSERT 触发器包含一个名为 NEW 的虚拟表。

CREATE TRIGGER mytrigger AFTER INSERT ON mytable
FOR EACH ROW SELECT NEW.col into @result;

-- 获取结果
SELECT @result;

DELETE 触发器包含一个名为 OLD 的虚拟表，并且是只读的。

UPDATE 触发器包含一个名为 NEW 和一个名为 OLD 的虚拟表，其中 NEW 是可以被修改地，而 OLD 是只读的。

可以使用触发器来进行审计跟踪，把修改记录到另外一张表中。

MySQL 不允许在触发器中使用 CALL 语句 ，也就是不能调用存储过程。

十、事务处理

基本术语：

1. 事务（transaction）指一组 SQL 语句；
2. 回退（rollback）指撤销指定 SQL 语句的过程；
3. 提交（commit）指将未存储的 SQL 语句结果写入数据库表；
4. 保留点（savepoint）指事务处理中设置的临时占位符（placeholder），你可以对它发布回退（与回退整个事务处理不同）。

不能回退 SELECT 语句，回退 SELECT 语句也没意义；也不能回退 CREATE 和 DROP 语句。

MySQL 的事务提交默认是隐式提交，每执行一条语句就把这条语句当成一个事务然后进行提交。当出现 START TRANSACTION 语句时，会关闭隐式提交；当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复隐式提交。

通过设置 autocommit 为 0 可以取消自动提交，直到 autocommit 被设置为 1 才会提交；autocommit 标记是针对每个连接而不是针对服务器的。

如果没有设置保留点，ROLLBACK 会回退到 START TRANSACTION 语句处；如果设置了保留点，并且在 ROLLBACK 中指定该保留点，则会回退到该保留点。

START TRANSACTION
// ...
SAVEPOINT delete1
// ...
ROLLBACK TO delete1
// ...
COMMIT

十一、字符集

基本术语：

1. 字符集为字母和符号的集合；
2. 编码为某个字符集成员的内部表示；
3. 校对字符指定如何比较，主要用于排序和分组。

除了给表指定字符集和校对外，也可以给列指定：

CREATE TABLE mytable
(col VARCHAR(10) CHARACTER SET latin COLLATE latin1_general_ci )
DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;

可以在排序、分组时指定校对：

SELECT *
FROM mytable
ORDER BY col COLLATE latin1_general_ci;

十二、权限管理

MySQL 的账户信息保存在 mysql 这个数据库中。

USE mysql;
SELECT user FROM user;

1、创建账户

CREATE USER myuser IDENTIFIED BY 'mypassword';

新创建的账户没有任何权限。

2、修改账户名

RENAME myuser TO newuser;

3、删除账户

DROP USER myuser;

4、查看权限

SHOW GRANTS FOR myuser;

5、授予权限

GRANT SELECT, INSERT ON mydatabase.* TO myuser;

账户用 username@host 的形式定义，username@% 使用的是默认主机名。

6、删除权限

REVOKE SELECT, INSERT ON mydatabase.* FROM myuser;

GRANT 和 REVOKE 可在几个层次上控制访问权限：

• 整个服务器，使用 GRANT ALL 和 REVOKE ALL；
• 整个数据库，使用 ON database.*；
• 特定的表，使用 ON database.table；
• 特定的列；
• 特定的存储过程。

7、更改密码

必须使用 Password() 函数

SET PASSWROD FOR myuser = Password('newpassword');