[数据库概念] SQL 基础

本章介绍有关于 SQL 语言的基本操作 —— 了解常用的数据类型，聚集，子查询，视图，约束，索引，事务等，它们保证我们可以将关系代数转换为 SQL 逻辑。其中，索引和事务会在后续的文章中专门给出详细的介绍。

数据类型

SQL 标准定义了多种数据类型，它们在各数据库中都是通用的。

基本数据类型

1. char(n)，固定 n 个长度的字符串。
2. varchar(n)，最长可拓展到 n 个长度的字符串。
3. int，整数。
4. smallint，小整数。
5. numeric(n,m)，由用户指定精度的浮点数。在这 n 位数字中，符号位占用 1 位，而小数点右边有 n 位中的 m 位数字。
6. real & double：浮点数和双精度浮点数。
7. float(n)，精度至少为 n 位的浮点数。

SQL 中的日期与时间

1. 日期 ( date )：记录年，月，日。
2. 时间 ( time )：记录一天当中的时分秒。
3. 时间戳 ( timestamp )：同时组合日期和时间。

在 MySQL 中，有三个函数对应如何获取它们：

-- 获取当前的日期
SELECT curdate();
-- 获取当前的时间
SELECT curtime();
-- 获取当前的时间戳
SELECT now();

除此之外，可以利用 extract(f from d) 函数从代表时间的值 d 中提取出相应的域。其中 f 可能包含了：year, month, day, hour, minute, second，具体取决于 d 是何种时间类型。

-- 从日期当中抽取年份
SELECT EXTRACT(year FROM CURDATE())

-- 从时间当中抽取小时数
SELECT EXTRACT(hour FROM CURTIME())

存储大型字节 / 字符数据

数据库自身是提供存储大型字符数据 ( 比如一篇文章 )，或是大型二进制数据 ( 比如图片，视频等 ) 的，它们统称为大对象数据类型 ( large-object date type )。这分为 clob 和 blob。

下面从 MySQL 数据库的角度来介绍这两种数据类型。

clob 用于存储大量的文本，它包含了：

类型	最大大小
TinyText	255 字节
Text	65535 字节 ( 约 65 K )
MediumText	16 777 215 字节 ( 约 16 M )
LongText	4 294 967 295 ( 约 4 G )

blob 用于存储二进制文件，它包含了：

类型	最大大小
TinyBlob	255 字节
Blob	65535 字节( 约 65 K )
MediumBlob	16 777 215 字节 (约 16 M )
LongBlob	4 294 967 295 ( 约 4 G )

建表和删除

SQL 建表的一个基本写法是：

CREATE TABLE <`table_name`> (
	<`attr1`> <`type1`> [`约束`],
	<`attr2`> <`tupe2`> [`约束`],
	<`主键约束`>，
	[`外键约束`],
	[`check子句`]
)；

create 子句必须要有一个主码约束。其中，诸如非空约束，唯一约束，和简短的 check 约束 ( 主要是出于观感的角度来考虑) 都可以直接放在属性声明的后面，不同行之间使用 , 相隔。额外的，在 MySQL 中，如果要让 DBMS 自动维护主键的值，可以为主键设置 AUTO_INCREMENT 另其自增，前提是主键是数字类型。这样，我们在后续插入新的元组时，就应当避免人为地存储主键。

反过来，如果要从数据库当中删除一个关系，使用的则是 drop 命令：

DROP TABLE <`table_name`>

另外，使用 alter add 子句可以为已有的关系中添加属性：

ALTER TABLE <`table_name`> add (
	<`attr_name`> <`type`>
)

约束

我们之前已经了解了主键约束 primary key(<key_name>)，外键约束 foreign key(<key_name)>) references <table_name>，非空约束 not null，它们都应用在 DDL SQL 当中。除此之外，SQL 还存在以下约束：

唯一性约束

和非空约束类似，在需要唯一约束的属性后面添加 unique 关键字。

CREATE TABLE <`table_name`> (
	<`attr1`> <`type1`> UNIQUE,
	...
    PRIMARY KEY <attrn>
)

如果这个属性没有被施加非空约束，那么该列唯一可以重复的值就是 null。

check 子句

check 子句提供一个灵活的，可用任意谓词修饰的条件约束。比如：约束性别一栏应该要么是 "female"，要么是 "male"。

-- gender 是一个属性。
CHECK(gender IN ('Female','Male'))

简单的 check 子句可以直接修饰属性，复杂的 check 子句也可以放到 create table 的后面。值得注意的是，部分数据库厂商可能 不提供包含子查询的谓词。

增删改操作

删除 ( delete ) 请求的表达方式和查询非常类似。

DELETE FROM r 
WHERE p

其中，r 代表了关系名 ( 表名 )，而 p 代表了条件谓词。如果省略了 where 子句，那么 DBMS 会将这个关系内的所有元组删除。和

插入 ( insert ) 数据使用 insert into 子句。比如向 instructor 关系中插入一条新的教师信息：

-- 简单的写法，这要求给定元组所有属性的值。
INSERT INTO course VALUES('CS-437','DataBase Systems','Comp.Sci',4);
-- 可以声明属性的排列顺序，那么给出的值也应该按这个顺序排列。
-- 在插入元组的部分属性时会用到这种写法。
INSERT INTO course(title,course_id,credits,dept_name) VALUES ('DataBase Systems','CS-437',4,'Comp.Sci')

更新 ( update ) 可以改变原有元组的一部分属性的值。

UPDATE `instructor` SET salary = 70000
WHERE name = 'Wu'

排序

关系本身就是一个无序的集合。因此，如果需要使查询结果按照一定的次序排列，可以选择使用 order by 子句。比如，将教职工按照工资的降序，名字的升序来排列显示结果：

SELECT * FROM `instructor`
ORDER BY salary DESC, name ASC

其中，desc 表示降序，asc 则表示升序。

聚集函数

聚集函数 ( aggregate function ) 是将一个集合压缩为一个值返回的规约函数。SQL 标准规定了 5 个基本规约函数：avg，min，max，sum，count。比如说：查询所有工资大于 50k 的教师人数。

SELECT COUNT(*) FROM instructor
WHERE salary > 50000

下面给出一个稍稍复杂的查询："查询各个系中工资大于 50k 的人数"，那么就需要先做一个分组聚集 group by 了，此时返回的是每一个分组的聚集函数计算结果的集合。

SELECT COUNT(*) FROM instructor
WHERE salary > 50000
GROUP BY dept_name

没有被 group by 直接选中的属性不应该再被 select 直接投影出来，只可以出现在聚集函数的参数当中。比如在上述的查询中，salary 不可以直接出现在 select 子句中，因为 group by 只根据 dept_name 进行聚集：

-- 错误的示范，这个 salary 没有任何意义。
SELECT COUNT(*),salary FROM instructor
WHERE salary > 50000
GROUP BY dept_name

-- 但是 salary 可以用于聚合函数中。比如这里求每个系中大于 50k 的教职工的平均薪资。
-- dept_name 可以直接出现在 SELECT 语句中.
SELECT AVG(salary),dept_name FROM instructor
WHERE salary > 50000
GROUP BY dept_name

一个更复杂的查询："查询 '工资大于 50k 的人数' 大于 1 个的系"。我们现在的问题是：需要对筛选之后的各个系进行筛选，如果人数小于等于 1，那么这个分组会被直接丢弃。因而需要引入一个新的关键字 —— 那就是 having。

SELECT COUNT(*) AS nums,dept_name FROM instructor
WHERE salary > 50000
GROUP BY dept_name
HAVING nums > 1

类似地，having 子句也不应该直接出现那些没有被 group by 选中的属性 ( 但是它们仍然可以以聚合函数的参数形式存在 )。另一个需要重要强调的知识点就是将它和 where 关键字区分开：where 条件用于筛选元组，而 having 则用于筛选聚集的分组。

子查询

子查询分为返回一个关系的子查询，或者是仅返回单个值的标量子查询。

对于第一种子查询，它可以出现在任意的 from，in，not in 子句中，前文已经演示过了。下面给出更简单一点的例子：

SELECT `name`
-- mysql 要求跟在 from 子句后的子查询必须要附带一个别称。
FROM (
	SELECT * FROM instructor 
	WHERE salary > 50000
) AS t1

额外的，in 和 not in 还可以用于枚举集合：

SELECT * FROM instructor
WHERE dept_name IN ("History","Finance")

如果确保一个子查询只返回一个值，那么它可以出现在 select ，where，having 等子句中。

with 子句

如果一个 from 子句后面的子查询过于冗长，可以利用 with 关键字将它提取到前面进行声明。形式为：WITH <临时表名> AS (子查询)。

-- 这两段是一个整体。
WITH more_than_50000 AS (
	SELECT * FROM instructor 
	WHERE salary > 50000
) 
SELECT `name`
FROM more_than_50000

如果有多个子查询需要被命名，那么仅需要一个 with 关键字，然后用逗号 , 相隔：

WITH history_instructors AS (
	SELECT * FROM instructor
	WHERE dept_name = "history"
), music_instructors AS (
	SELECT * FROM instructor
	WHERE dept_name = "music"
)
SELECT * FROM history_instructors 
UNION 
SELECT * FROM music_instructors

Some / All

这里介绍两个子句：some 和 all。前者表示 "至少"，后者表示 "所有"。在很久以前，SQL 使用 any 来表示 "至少"。但是 any 在英语中容易引发 "任何一个" 还是 "任何所有" 的歧义，因此后续又新引入了 some 。比如：查询 "至少比一位历史系教职工工资高的老师"：

SELECT * FROM instructor
WHERE salary > SOME (
	SELECT salary FROM instructor 
	WHERE dept_name = "history"
)

再比如，查询 "比所有历史系教职工工资都高的老师"：

SELECT * FROM instructor
WHERE salary > ALL (
	SELECT salary FROM instructor 
	WHERE dept_name = "history"
)

注意，这里查询结果只能有一列，因为我们仅需要一个值来要和外层的 salary 进行比较。另外，=some 等价于 in，但是 <>some 不等价于 not in；同样的，<>any 等价于 not in，但是 =all 不等价于 in。

Case 分支

偶尔，我们需要在查询中根据一个属性的取值来返回不同的结果。在 SQL 中，这个分支语句有两种写法：

SELECT 
ID,(
  -- 类似于 switch 
	CASE grade
		WHEN "A+" THEN "great"
		WHEN "A" THEN "good"
		ELSE "ok"
	END
) AS `rank`
FROM takes

第一种写法是 case 子句后接上一个属性，然后像 switch 子句那样检索它，每一个 when 子句后面只能跟上单个值。另外一种方式是：

SELECT 
ID,(
  -- 类似于 if 
	CASE 
		WHEN grade IN ("A+","A") THEN "great"
		WHEN grade IN ("B","C") THEN "good"
		ELSE "ok"
	END
) AS `rank`
FROM takes

如果 case 后面没有属性，那么 when 后面可以承接表达式。无论哪种写法，都必须以 end 关键字作为结尾。case 分支相当于是一个返回单个值的子查询，因此理论上它可以出现在 select，where，having 等子句中。

视图

对于一些常用的子查询，我们可以声明一个视图将它保存起来，而不是在每段查询前声明一大段 with。语法为：

create view <view_name>(<colum1>,<colum2>,...) as (select 子句)。比如，从 instructor 表中创建一个历史系教职工的视图：

CREATE VIEW history_instructor(name,salary) AS (
	SELECT `name`,salary FROM `instructor`
)

所有的视图都基于原有的数据库模式，且视图之间也可以相互引用。

不同的厂商对视图的实现方式有所不同。对于有些厂商而言，视图是 懒加载 的：DBMS 只会记录这个视图的查找过程，并且只有在真正调用的时候才会执行这个过程并返回视图的内容。但对于另外的一些厂商实现而言，视图是真实存在的，保存在数据库当中的数据，这种视图被称作 物化视图 ( materialized view )。但是，物化视图依赖的表发生变更时，物化视图就必须要及时做出更新。

保持物化视图一直在最新状态的过程称作是物化视图维护 ( materialized view maintenance )。最 "勤快" 的方式是每当检测到引用的表更新时，将视图一同更新。而 "懒惰" 的方法则是设立定时器周期性的重新计算数据。

如果能提前将经过大量复杂，费时的聚合结果保存为视图，那么可以极大提升下次查询的速度，避免读取大型的底层关系。但问题也在这里：用户是否能接受视图的数据可能是 "滞后" 的。出于思维定势，我们一般认为视图是只读的。能否通过对视图施加插入，更改，删除操作从而影响到底层的关系呢？

INSERT INTO history_instructor(name,salary) VALUES ('Li',50000)

假定我们选择在 history_instructor 视图插入一条记录，显然这条记录最终将插入到 instructor 表内。那么，原来表内需要的 ID 和 dept_name 项怎么办？因此，借助视图来影响底层关系表是一个很复杂的问题。SQL:1999 标准对 "视图何时可以被插入，更改以及删除的时机" 制定了非常严格且复杂的标准。

默认情况下，数据库不会阻止这类高危行为，比如 MySQL。但是由于缺失了 ID 和 dept_name 信息，这条语句最终仍会执行失败。

索引

当数据库内的数据量足够大时，遍历搜索的方式将变得十分低效。比如说在几十万条数据搜索 "物理系中薪资大于 50000 的老师"。如果我们能够对 instructor 表按照 dept_name 建立一个合适的索引，那么就能将搜索范围从十几万缩减到几千，从而提升查询效率。

索引是一个冗余结构，对于查询结果的正确性来说，它并不是必须的。创建索引的句式为：

CREATE INDEX <`index_name`> ON <`table`>(<`attribute`>)

因此，如果我们想要对 instructor 表的 dept_name 建立索引，SQL 语句是这样的：

CREATE INDEX dept_index ON instructor(dept_name)

对应的，如果要删除一个索引，我们使用以下 SQL 语句：

DROP INDEX <`index_name`>

索引有两种方式 —— B+ 树索引和哈希索引。

事务

简单来说，事务由一连串独立的查询 / 语句构成，这些语句 "要么都做，要么都不做"，以此来保证事务的 一致性。下面演示了如何在 MySQL 中使用一个简单的事务：

BEGIN;
	
	UPDATE bank SET balance = balance - 1000.00 WHERE `name` = 'Li Ping';
	UPDATE bank SET balance = balance + 1000.00 WHERE `name` = 'Wang Fang';

COMMIT;

如果在执行 Begin; ... Commit; 块的过程中没有错误，那么我们在事务块结束之后主动提交一次 Commit 表示将该事务做出的改动永久保存到数据库中。反之，如果得到了一些 "预料外的错误"，那么就需要在事务结束后提交一个 ROLLBACK 来让数据库回到 BEGIN; 开始之前的状态。

BEGIN;
	
	UPDATE bank SET balance = balance - 1000.00 WHERE `name` = 'Li Ping';
	UPDATE bank SET balance = balance + 1000.00 WHERE `name` = 'Wang Fang';
	
-- 以上更改全部会失效，我们称事务回滚了。
ROLLBACK;

回滚能够撤销的操作有：Delete，Update，Insert，而 Create 和 Drop 这类 DDL 操作无法回滚。额外需要注意的是，数据库本身不会因为事务块执行成功与否而自行判断该事务是 Commit 还是被 Rollback。比如说事务块内有一条语句 a "不幸" 包含了一个语法错误：这个事务会异常中断，语句 a 之前的执行结果被保留，而之后的查询则不会被执行，这显然破坏了事务的一致性要求。

除去上述这种低级错误之外，判别 "一个事务应当提交还是回滚" 需要我们自行设置一些控制流程来判断 —— 这些控制流程会在下一篇 SQL 专题中介绍。