秋招 基础知识之——数据库 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、连接查询

前面的查询都是针对一个表进行的。若一个查询同时涉