Sql基础教程

第一章、数据库与sql

1.1 数据库是什么

将大量数据保存起来，通过计算机加工而成的可以进行高效访问的数据集合称为数据库（Database， DB）。
用来管理数据库的计算机系统称为数据库管理系统（Database Management System， DBMS）。

DBMS的种类

1. 层次数据库（Hierarchical Database， HDB）最古老的数据库之一，它把数据通过层次结构（树形结构）的方式表现出来。现在已经很少使用了。
2. 关系数据库（Relational Database， RDB）关系数据库是现在应用最广泛的数据库。它采用由行和列组成的二维表来管理数据，所以简单易懂。同时，它还使用专门的 SQL（Structured
   Query Language，结构化查询语言）对数据进行操作。
   代表性的关系型数据库有如下 5 种： Oracle Database：甲骨文公司的RDBMS，SQL Server：微软公司的RDBMS，DB2：IBM公司的RDBMS，PostgreSQL： 开源的RDBMS，MySQL：开源的RDBMS。
3. 面向对象数据库（Object Oriented Database， OODB）把数据以及对数据的操作集合起来以对象为单位进行管理，面向对象数据库就是用来保存这些对象的数据库。
4. XML数据库（XML Database， XMLDB）XML 数据库可以对 XML 形式的大量数据进行高速处理。
5. 键值存储系统（Key-Value Store， KVS）这是一种单纯用来保存查询所使用的主键（Key）和值（Value）的组合的数据库。

1.2 数据库的结构

RDBMS的常见系统结构使用 RDBMS 时，最常见的系统结构就是客户端 / 服务器类型（C/S
类型）。

表的结构

关系数据库通过由行和列组成的二维表来管理数据。用来管理数据的二维表在关系数据库中简称为表。

根据 SQL 语句的内容返回的数据同样必须是二维表的形式。

表的列（垂直方向）称为字段，它代表了保存在表中的数据项目。
表的行（水平方向）称为记录，它相当于一条数据。
关系数据库必须以行为单位进行数据读写。
行和列交汇的方格称为单元格。 一个单元格中只能输入一个数据。 （原子性）

1.3 SQL 概要

SQL 是用来操作关系数据库的语言。
国际标准化组织（ISO）为 SQL 制定了相应的标准，以此为基准的SQL 称为标准 SQL。
SQL 用关键字、表名、列名等组合而成的一条语句（SQL 语句）来描述操作的内容。

SQL语句及其种类

• DDL（Data Definition Language，数据定义语言）用来创建或者删除存储数据用的数据库以及数据库中的表等对象。DDL 包含以下几种指令。
  CREATE： 创建数据库和表等对象
  DROP： 删除数据库和表等对象
  ALTER： 修改数据库和表等对象的结构
• DML（Data Manipulation Language，数据操纵语言）用来查询或者变更表中的记录。
  DML 包含以下几种指令。
  SELECT：查询表中的数据
  INSERT：向表中插入新数据
  UPDATE：更新表中的数据
  DELETE：删除表中的数据
• DCL（Data Control Language，数据控制语言）用来确认或者取消对数据库中的数据进行的变更。除此之外，还可以对 RDBMS 的用户是否有权限操作数据库中的对象（数据库表等）进行设定。
  DCL 包含以下几种指令。
  COMMIT： 确认对数据库中的数据进行的变更
  ROLLBACK： 取消对数据库中的数据进行的变更
  GRANT： 赋予用户操作权限
  REVOKE： 取消用户的操作权限

SQL的基本书写规则

1. SQL语句要以分号（ ;）结尾
2. SQL语句不区分大小写,SQL不区分关键字的大小写。
3. 常数的书写方式是固定的，在 SQL 语句中直接书写的字符串、日期或者数字等称为常数。

• 使用单引号（'）将字符串括起来，用来标识这是一个字符串
• SQL 语句中含有日期的时候，同样需要使用单引号将其括起来。
• SQL 语句中书写数字的时候，不需要使用任何符号标识，直接写成 1000 这样的数字即可。

4.单词需要用半角空格或者换行来分隔

1.4 表的创建

在创建表之前，一定要先创建用来存储表的数据库。

数据库的创建

语法1-1　创建数据库的CREATE DATABASE语句
CREATE DATABASE <数据库名称>;

表的创建

CREATE TABLE <表名>
（ <列名1> <数据类型> <该列所需约束>，
<列名2> <数据类型> <该列所需约束>，
<列名3> <数据类型> <该列所需约束>，
<列名4> <数据类型> <该列所需约束>，
...
<该表的约束1>， <该表的约束2>，……）；

该语法清楚地描述了我们要创建一个包含 < 列名 1>、 < 列名 2>、 ……的名称为 < 表名 > 的表，非常容易理解。每一列的数据类型（后述）是必须要指定的，还要为需要的列设置约束（后述）。约束可以在定义列的时候进行设置，也可以在语句的末尾进行设置。

命名规则

数据库名称、表名和列名等可以使用以下三种字符。
● 半角英文字母　　● 半角数字　　● 下划线（_） ● 名称必须以半角英文字母开头。

在同一个数据库中不能创建两个相同名称的表，在同一个表中也不能创建两个名称相同的列。

数据类型

数据类型表示数据的种类，包括数字型、字符型和日期型等。每一列都不能存储与该列数据类型不符的数据。所有的列都必须指定数据类型。

常用的数据类型

• INTEGER型，用来指定存储整数的列的数据类型（数字型），不能存储小数
• CHAR型，是用来指定存储字符串的列的数据类型（字符型）。可以像 CHAR(10) 或 CHAR(20) 这样，在括号中指定该列可以存储的字符串的长度（最大长度）。字符串超出最大长度的部分是无法输入到该列中的。 字符串以定长字符串的形式存储在被指定为 CHAR 型的列中。所谓定长字符串，就是当列中存储的字符串长度达不到最大长度的时候，使用半角空格进行补足。
• VARCHAR型， VARCHAR 型也是用来指定存储字符串的列的数据类型（字符串类型），也可以通过括号内的数字来指定字符串的长度（最大长度）。但该类型的列是以可变长字符串的形式来保存字符串的 B。定长字符串在字符数未达到最大长度时会用半角空格补足，但可变长字符串不同，即使字符数未达到最大长度，也不会用半角空格补足。
• DATE型，用来指定存储日期（年月日）的列的数据类型（日期型）。

约束的设置

约束是除了数据类型之外，对列中存储的数据进行限制或者追加条件的功能。

• NOT NULL 约束。 字段不能存储空值
• 主键约束，PRIMARY KEY (col)，指定该字段为主键，无法重复。

1.5 表的删除和更新

表的删除和更新

删除表时使用的DROP TABLE语句

DROP TABLE <表名>；

删除了的表是无法恢复的。在执行DROP TABLE语句之前请务必仔细确认。

表定义的更新（ALTER TABLE语句），添加列的ALTER TABLE语句

ALTER TABLE <表名> ADD COLUMN <列的定义>；
-- 添加多个列
ALTER TABLE <表名> ADD （ <列名>， <列名>，……）；

删除列的ALTER TABLE语句

ALTER TABLE <表名> DROP COLUMN <列名>；
-- 删除多个列
ALTER TABLE <表名> DROP （ <列名>， <列名>，……）；

表定义变更之后无法恢复。在执行ALTER TABLE语句之前请务必仔细确认

表的数据的插入，更新和删除

INSERT语句的功能是向数据库的某个表中插入一个新的数据行

1、根据对应的字段插入相对应的值
insert into table_name(字段A, 字段B, 字段C) values(值A, 值B, 值C)

2、给表中的所有列添加值，不用指定列名称，但要保证值的顺序与列名在表中的数据一致
insert into table_name values(值A, 值B, 值C)

3、将A表中的数据插入到B表
insert into table_B(字段A, 字段B, 字段C) 
select 字段A, 字段B, 字段C from table_A [where condition]

UPDATE语句的功能是修改数据库某个表中的数据行,一般配合where使用，不限定条件时表中所有数据都会被修改。

修改某些字段的值
UPDATE table_name SET 字段A=值A, 字段B=值B, ...  WHERE [condition]

DELETE语句用来删除数据库中已有的数据，配合where可以删除特定的数据

1、删除表中指定定的数据
delete from table_name where [condition]

2、删除表中所有的数据
delete from table_name

第二章、查询基础

2.1 SELECT语句基础

列的查询

从表中选取数据时需要使用 SELECT 语句，通过 SELECT 语句查询并选取出必要数据
的过程称为查询（query）。

SELECT 语句语法
SELECT 子句中列举了希望从表中查询出的列的名称，而 FROM 子句则指定了选取出数据的表的名称，WHERE 子句指定了查询条件，GROUP BY 子句指定了分组列，HAVING 子句对分组添加条件，ORDER BY 排序，LIMIT 限制结果集。

SELECT <列名>，……
FROM <表名>；
WHERE <条件表达式>
GROUP BY <分组列>
HAVING <分组条件>
ORDER BY <排序列名>
LIMIT (n,size) 限制结果集 // n 起始位置，size 个数

SELECT 查询全部的列，星号（*）代表所有列。如果使用星号的话，就无法设定列的显示顺序了。这时就会按照 CREATE TABLE 语句的定义对列进行排序。

SELECT * FROM <表名>;

为列设定别名

SQL 语句可以使用 AS 关键字为列设定别名。设定汉语别名时需要使用双引号（ "）括起来。

SELECT <列名> AS <别名>,<列名> AS "别名"
FROM <表名>

常数的查询

SELECT 子句中不仅可以书写列名，还可以书写常数。

DISTINCT 从结果中删除重复行

在SELECT语句中使用DISTINCT可以删除重复行。在使用 DISTINCT 时， NULL 也被视为一类数据。 NULL 存在于多行中时， 也会被合并为一条NULL 数据。
DISTINCT 关键字只能用在第一个列名之前。

SELECT DISTINCT <列名>,<列名>,...
FROM <表名>

WHERE语句来选择记录

SELECT 语句通过 WHERE 子句来指定查询数据的条件。在 WHERE子句中可以指定“某一列的值和这个字符串相等”或者“某一列的值大于这个数字”等条件。执行含有这些条件的 SELECT 语句，就可以查询出只符合该条件的记录了。
WHERE语法

SELECT <列名>, ……
FROM <表名>
WHERE <条件表达式>;

首先通过WHERE 子句查询出符合指定条件的记录，然后再选取出 SELECT 语句指定的列。
SQL 中子句的书写顺序是固定的，不能随意更改。WHERE 子句必须紧跟在 FROM 子句之后。

注释的书写方法

1行注释书写在"--"之后，只能写在同一行。
多行注释书写在"/*"和 " */"之间，可以跨多行。

2.2 算术运算符和比较运算符

算术运算符

SQL 语句中可以使用计算表达式。

运算是以行为单位执行的。
SQL语句中可以使用的四则运算的主要运算符，四则运算所使用的运算符（ +、 -、 、 /） 称为算术运算符。
运算符就是使用其两边的值进行四则运算或者字符串拼接、数值大小比较等运算，并返回结果的符号。

需要注意NULL

所有包含 NULL 的计算，结果肯定是 NULL。

比较运算符

像符号 = 这样用来比较其两边的列或者值的符号称为比较运算符，符号 = 就是比较运算符。在 WHERE 子句中通过使用比较运算符可以组合出各种各样的条件表达式。

SQL 中主要的比较运算符

=    和~相等
<>   和~不相等
>=   大于等于~
>    大于~
<=   小于等于~
<    小于~

比较运算符可以对字符、数字和日期等几乎所有数据类型的列和值进行比较。

对字符串使用不等号时的注意事项

在对字符串类型的数据进行大小比较时，使用的规则就是按照字典顺序进行比较大小。
字符串类型的数据原则上按照字典顺序进行排序，不能与数字的大小顺序混淆。

IS NULL 运算符，IS NOT NULL 运算符

A IS NULL --如果A为NULL，则表达式为真，不为NULL，则表达式为假
A IS NOT NULL --如果A不是NULL，则表达式为真，是NULL，则表达式为假。

希望选取NULL记录时，需要在条件表达式中使用IS NULL运算符。希望选取不是NULL的记录时，需要在条件表达式中使用IS NOT NULL运算符。

2.3 逻辑运算符

NOT运算符用来否定某一条件。

NOT A -- 即找到不满足A表达式的条件

AND运算符和OR运算符
在 WHERE 子句中使用 AND 运算符或者 OR 运算符，可以对多个查询条件进行组合。

AND 运算符在其两侧的查询条件都成立时整个查询条件才成立，其意思相当于“并且”。
OR 运算符在其两侧的查询条件有一个成立时整个查询条件都成立，其意思相当于“或者”。
AND运算符的优先级高于OR运算符。想要优先执行OR运算符时可以使用括号()。
多个查询条件进行组合时，需要使用AND运算符或者OR运算符。

运算符 NOT、 AND 和 OR 称为逻辑运算符。逻辑运算符得到的结果只有真和假。
运算符对应的真值表

SELECT 语句的 WHERE 子句中，通过 AND 运算符将两个查询条件连接起来时，会查询出这两个查询条件都为真的记录。通过 OR 运算符将两个查询条件连接起来时，会查询出某一个查询条件为真或者两个查询条件都为真的记录。在条件表达式中使用 NOT 运算符时，会选取出查询条件为假的记录（反过来为真）。

含有NULL时的真值
NULL既不是真也不是假，是一个不确定的值。含有NULL的逻辑运算变成了三值逻辑。

第三章、聚合与排序

3.1 对表进行聚合查询

聚合函数

通过 SQL 对数据进行某种操作或计算时需要使用函数。用于汇总的函数称为聚合函数或者聚集函数，所谓聚合，就是将多行汇总为一行。实际上，所有的聚合函数都是这样，输入多行输出一行。
常见的聚合函数：

• COUNT： 计算表中的记录数（行数）
• SUM： 计算表中数值列中数据的合计值
• AVG： 计算表中数值列中数据的平均值
• MAX： 求出表中任意列中数据的最大值
• MIN： 求出表中任意列中数据的最小值

计算表中数据的行数，使用COUNT函数。

SELECT COUNT(*) FROM <表名>
SELECT COUNT(列名) FROM <表名>

当COUNT函数中查询某个列的个数时，返回的个数为当前列不为NULL的个数。
COUNT函数的结果根据参数的不同而不同。 COUNT(*)会得到包含NULL的数据行数，而COUNT(<列名>)会得到NULL之外的数据行数。

计算合计值，使用SUM函数，SUM函数会把当前列除了NULL之外的值进行求和

SELECT SUM(列名) FROM <表名>

聚合函数会将NULL排除在外。但COUNT(*)例外，并不会排除NULL。

计算平均值，使用AVG 函数，AVG函数的计算公式：( 值的合计 )/( 值的个数 ) 。

SELECT AVG(列名) FROM <表名>

计算最大值和最小值，使用 MAX和MIN函数。

SELECT MAX(列名),MIN(列名) FROM <表名>

MAX/MIN函数几乎适用于所有数据类型的列。 SUM/AVG函数只适用于数值类型的列。

使用聚合函数删除重复值（关键字DISTINCT）
DISTINCT 可以配合聚合函数使用，可以去除重复值。在聚合函数的参数中使用DISTINCT，可以删除重复数据。

SELECT COUNT(DISTINCT <列名>) FROM <表名>;
SELECT SUM(DISTINCT <列名>) FROM <表名>;
...

3.2 对表进行分组

GROUP BY子句

使用GROUP BY子句进行汇总，GROUP BY就像是切分表的一把刀，将表中的数据进行分组划分。

SELECT <列名1>, <列名2>, <列名3>, ……
FROM <表名>
GROUP BY <列名1>, <列名2>, <列名3>, ……;

GROUP BY 子句就像切蛋糕那样将表进行了分组。在 GROUP BY 子句中指定的列称为聚合键或者分组列。由于能够决定表的切分方式，所以是非常重要的列。当然， GROUP BY 子句也和 SELECT 子句一样，可以通过逗号分隔指定多列。

聚合键中包含NULL的情况
当聚合键中包含 NULL 时，也会将NULL 作为一组特定的数据，在结果中会以“不确定”行（空行）的形式表现出来。

使用WHERE子句时GROUP BY的执行结果

使用 WHERE 子句进行汇总处理时，会先根据 WHERE 子句指定的条件进行过滤，然后再进行汇总处理。
WHERE 和 GROUP BY 并用时 SELECT 语句的执行顺序 ：
FROM → WHERE → GROUP BY → SELEC

SELECT <列名1>, <列名2>, <列名3>, ……
FROM <表名>
WHERE
GROUP BY <列名1>, <列名2>, <列名3>, ……;

与聚合函数和GROUP BY子句有关的常见错误

常见错误① —— 在SELECT子句中书写了多余的列（mysql中可以）
使用聚合函数时， SELECT 子句中只能存在以下三种元素。
● 常数
● 聚合函数
● GROUP BY子句中指定的列名（也就是聚合键）

使用GROUP BY子句时， SELECT子句中不能出现聚合键之外的列名。

常见错误②——在GROUP BY子句中写了列的别名

SELECT 子句中的项目可以通过 AS 关键字来指定别名。但是，在 GROUP BY 子句中是不能使用别名的。
这是SQL 语句在 DBMS内部的执行顺序造成的—— SELECT 子句在 GROUP BY 子句之后执行。
在执行 GROUP BY 子句时， SELECT 子句中定义的别名， DBMS 还并不知道。

常见错误③——GROUP BY子句的结果能排序吗
通常 SELECT 语句的执行结果的显示顺序都是随机的，因此想要按照某种特定顺序进行排序的话，需要在 SELECT 语句中进行指定。
GROUP BY子句结果的显示是无序的。

常见错误④——在WHERE子句中使用聚合函数
只有SELECT子句和HAVING子句（以及ORDER BY子句）中能够使用聚合函数。

3.3 为聚合结果指定条件

HAVING 子句

HAVING子句是对分组之后的数据进行按条件过滤。
HAVING 子句的语法如下所示：

SELECT <列名1>, <列名2>, <列名3>, ……
FROM <表名>
WHERE
GROUP BY <列名1>, <列名2>, <列名3>, ……
HAVING <分组结果对应的条件

HAVING 子句必须写在 GROUP BY 子句之后，其在 DBMS 内部的执行顺序也排在 GROUP BY 子句之后。

HAVING 子句和包含 GROUP BY 子句时的 SELECT 子句一样，能够使用的要素有一定的限制，限制内容也是完全相同的。
HAVING子句的构成要素：
● 常数
● 聚合函数
● GROUP BY子句中指定的列名（即聚合键）

相对于HAVING子句，更适合写在WHERE子句中的条件

原表中作为聚合键的列也可以在 HAVING 子句中使用。聚合键所对应的条件可以写在WHERE子句中，也可以写在HAVING子句中。

但聚合键所对应的条件还是应该书写在 WHERE 子句之中。
根本原因是 WHERE 子句和 HAVING 子句的作用不同。HAVING 子句是用来指定“组”的条件的。因此，“行”所对应的条件还是应该写在 WHERE 子句当中。
WHERE 子句 = 指定行所对应的条件
HAVING 子句 = 指定组所对应的条件

WHERE子句和HAVING子句的执行速度
在 WHERE 子句和 HAVING 子句中都可以使用，通常情况下，为了得到相同的结果，将条件写在 WHERE 子句中要比写在 HAVING 子句中的处理速度更快，返回结果所需的时间更短。

3.4 对查询结果进行排序

ORDER BY子句

ORDER BY 子句，对查询的结果进行排序。

SELECT <列名1>, <列名2>, <列名3>, ……
FROM <表名>
WHERE 行条件过滤
GROUP BY <聚合键>,...
HAVING 分组排序规则
ORDER BY <排序基准列1>, <排序基准列2>, ……

ORDER BY子句中书写的列名称为排序键。
子句的书写顺序
SELECT 子句 → FROM 子句 → WHERE 子句 → GROUP BY 子句 →HAVING 子句 → ORDER BY 子句

指定升序或降序
未指定ORDER BY子句中排列顺序时会默认使用升序进行排列。降序排列时，在列名后面使用 DESC 关键字。
其实，使用升序进行排列时，正式的书写方式应该是使用关键字 ASC，但是省略该关键字时会默认使用升序进行排序。 ASC 和 DESC 是 ascendent（上升的）和descendent（下降的）这两个单词的缩写。由于 ASC 和 DESC 这两个关键字是以列为单位指定的，因此可以同时指定一个列为升序，指定其他列为降序。

指定多个排序键
ORDER BY 子句中同时指定多个排序键了。规则是优先使用左侧的键，如果该列存在相同值的话，再接着参考右侧的键。

NULL的顺序
使用含有 NULL 的列作为排序键时，NULL 会在结果的开头或末尾汇总显示。究竟是在开头显示还是在末尾显示，并没有特殊规定。某些 DBMS中可以指定 NULL 在开头或末尾显示。

在排序键中使用显示用的别名

在 GROUP BY 子句中不能使用SELECT 子句中定义的别名，但是在 ORDER BY 子句中却是允许使用别名的。
这是因为 SQL 语句在 DBMS 内部的执行顺序被掩盖起来了。 SELECT 语句按照子句为单位的执行顺序如下所示。
使用HAVING子句时，SELECT 语句的顺序
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

SELECT 子句的执行顺序在 GROUP BY 子句之后， ORDER BY 子句之前。
在执行 GROUP BY 子句时， SELECT 语句中定义的别名无法被识别。对于在SELECT 子句之后执行的 ORDER BY 子句就可以识别定义的别名。

ORDER BY子句中可以使用的列

在ORDER BY子句中可以使用SELECT子句中未使用的列和聚合函数。
在 ORDER BY 子句中，还可以使用在 SELECT 子句中出现的列所对应的编号。列编号是指 SELECT 子句中的列按照从左到右的顺序进行排列时所对应的编号（1, 2, 3, …）。

在ORDER BY子句中不要使用列编号。
原因：第一， 代码阅读起来比较难。第二，该排序功能将来会被删除。

第四章、数据更新

4.1 数据的插入（INSERT语句的使用方法）

什么是INSERT

INSERT 是向表中进行插入数据的语句。

INSERT语句的基本语法

INSERT INTO <表名> (列1, 列2, 列3, ……) VALUES (值1, 值2, 值3, ……);

将列名和值用逗号隔开，分别括在（） 内，这种形式称为清单。列清单和值清单。列清单和值清单的列数必须保持一致。
原则上，执行一次 INSERT 语句会插入一行数据 。因此，插入多行时，通常需要循环执行相应次数的 INSERT 语句。
列清单的省略，对表进行全列 INSERT 时，可以省略表名后的列清单。这时 VALUES子句的值会默认按照从左到右的顺序赋给每一列。

INSERT插入

插入NULL
INSERT 语句中想给某一列赋予 NULL 值时，可以直接在 VALUES子句的值清单中写入 NULL。但是，想要插入 NULL 的列一定不能设置 NOT NULL 约束。

插入默认值
向表中插入默认值（初始值）。可以通过在创建表的CREATE TABLE 语句中设置 DEFAULT 约束来设定默认值。
如果在创建表的同时设定了默认值，就可以在 INSERT 语句中自动为列赋值了。默认值的使用方法通常有显式和隐式两种。
通过显式方法插入默认值，在 VALUES 子句中指定 DEFAULT 关键字。

INSERT INTO <表名> (列1, 列2, 列3, ……) VALUES (值1, DEFAULT, 值3, ……);

通过隐式方法插入默认值，在列清单和值清单中省略设定了默认值的列。
省略INSERT语句中的列名，就会自动设定为该列的默认值（没有默认值时会设定为NULL）。

从其他表中复制数据

要插入数据，除了使用 VALUES 子句指定具体的数据之外，还可以从其他表中复制数据。

INSERT … SELECT语句
INSERT语句中的列与SELECT语句中的列要对应起来。

INSERT INTO <表名>(<列名>, <列名>, <列名>,...,<列名>, <列名>, <列名>)
SELECT <列名>, <列名>, <列名>, ..., <列名>,<列名>, <列名>
FROM <表名>;

多种多样的SELECT语句
INSERT语句的SELECT语句中，可以使用WHERE子句或者GROUP BY子句等任何SQL语法（但使用ORDER BY子句并不会产生任何效果）。

4.2 数据的删除（ DELETE语句的使用方法）

DROP TABLE语句和DELETE语句

• DROP TABLE 语句可以将表完全删除
• DELETE 语句会留下表（容器），而删除表中的全部数据

DROP TABLE 语句会完全删除整张表，因此删除之后再想插入数据，就必须使用 CREATE TABLE 语句重新创建一张表。
DELETE 语句在删除数据（行）的同时会保留数据表，因此可以通过 INSERT 语句再次向表中插入数据。

DELETE语句

DELETE语句的基本语法
保留数据表，仅删除全部数据行的DELETE语句

DELETE FROM <表名>;

DELETE语句的删除对象并不是表或者列，而是记录（行）。

指定删除对象的DELETE语句（搜索型DELETE）
想要删除部分数据行时，可以像 SELECT 语句那样使用 WHERE子句指定删除条件。 指定了删除对象的 DELETE 语句称为搜索型DELETEB。

DELETE FROM <表名> WHERE <条件>;

可以通过WHERE子句指定对象条件来删除部分数据。

删除和舍弃
标准 SQL 中用来从表中删除数据的只有 DELETE 语句。但是，很多数据库产品中还存在另外一种被称为 TRUNCATE 的语句。这些产品主要包括 Oracle、 SQLServer、PostgreSQL、MySQL 和 DB2。
TRUNCATE 是舍弃的意思，具体的使用方法如下：

TRUNCATE <表名>;

TRUNCATE 只能删除表中的全部数据，而不能通过WHERE 子句指定条件来删除部分数据。也正是因为它不能具体地控制删除对象，所以其处理速度比 DELETE 要快得多。

4.3 数据的更新（UPDATE语句的使用方法）

UPDATE语句的基本语法

和 INSERT 语句、 DELETE 语句一样， UPDATE 语句也属于 DML语句。通过执行该语句，可以改变表中的数据。
改变表中数据的UPDATE语句，将更新对象的列和更新后的值都记述在 SET 子句中。

UPDATE <表名> SET <列名> = <表达式>;

指定条件的UPDATE语句（搜索型UPDATE）

更新数据时也可以像DELETE 语句那样使用 WHERE 子句，这种指定更新对象的 UPDATE 语句称为搜索型 UPDATE 语句。

UPDATE <表名> SET <列名> = <表达式> WHERE <条件>;

使用NULL进行更新
使用 UPDATE 也可以将列更新为 NULL（该更新俗称为 NULL 清空）。此时只需要将赋值表达式右边的值直接写为 NULL 即可。使用UPDATE语句可以将值清空为NULL（但只限于未设置NOT NULL约束的列）。

多列更新
UPDATE 语句的 SET 子句支持同时将多个列作为更新对象。

UPDATE <表名> 
SET <列名> = <表达式>,...,<列名> = <表达式>

4.4 事务

什么是事务

事务是对表中数据进行更新的单位。简单来讲，事务就是需要在同一个处理单元中执行的一系列更新处理的集合。
对表进行更新需要使用 INSERT、 DELETE 或者UPDATE 三种语句。但通常情况下，更新处理并不是执行一次就结束了，而是需要执行一系列连续的操作。

需要在同一个处理单元中执行一系列更新操作的情况，一定要使用事务来进行处理。
一个事务中包含多少个更新处理或者包含哪些处理，在 DBMS 中并没有固定的标准，而是根据用户的要求决定的。

创建事务

事务的语法。

事务开始语句;
...
DML语句1;
DML语句2;
DML语句2;
...
事务结束语句（ COMMIT或者ROLLBACK） ;

使用事务开始语句和事务结束语句，将一系列 DML 语句（INSERT/UPDATE/DELETE 语句）括起来，就实现了一个事务处理。

事务的开始语句。在标准 SQL 中并没有定义事务的开始语句，而是由各个 DBMS 自己来定义的。比较有
代表性的语法如下所示。

● SQL Server、PostgreSQL
	BEGIN TRANSACTION
● MySQL
	START TRANSACTION
● Oracle、DB2
	无

事 务 结 束 语 句 只 有COMMIT和ROLLBACK两 种，在所有的 RDBMS 中都是通用的。

各个 DBMS 事务的开始语句都不尽相同，其中 Oracle 和DB2 并没有定义特定的开始语句。可能大家觉得这样的设计很巧妙，其实是因为标准 SQL 中规定了一种悄悄开始事务处理的方法。

事务的结束需要用户明确地给出指示。结束事务的指令有如下两种：
COMMIT——提交处理
COMMIT 是提交事务包含的全部更新处理的结束指令，相当于文件处理中的覆盖保存。一旦提交，就无法恢复到事务开始前的状态了。
万一由于误操作提交了包含错误更新的事务，就只能回到重新建表、重新插入数据这样繁琐的老路上了。

虽然我们可以不清楚事务开始的时间点，但是在事务结束时一定要仔细进行确认。

ROLLBACK——取消处理
ROLLBACK 是取消事务包含的全部更新处理的结束指令，相当于文件处理中的放弃保存。一旦回滚，数据库就会恢复到事务开始之前的状态。通常回滚并不会像提交那样造成大规模的数据损失。

事务处理何时开始

事务并没有标准的开始指令存在，而是根据 DBMS 的不同而不同。
实际上，几乎所有的数据库产品的事务都无需开始指令。这是因为大部分情况下，事务在数据库连接建立时就已经悄悄开始了，并不需要用户再明确发出开始指令。

像这样不使用指令而悄悄开始事务的情况下，通常会有如下两种情况。

• 每条SQL语句就是一个事务（ 自动提交模式）
• 直到用户执行COMMIT或者ROLLBACK为止算作一个事务

通常的 DBMS 都可以选择其中任意一种模式。默认使用自动提交模式的DBMS 有 SQL Server、 PostgreSQL 和 MySQL 等。该模式下的 DML 语句，每一条语句都括在事务的开始语句和结束语句之中。

4.5 ACID特性

DBMS 的事务都遵循四种特性，将这四种特性的首字母结合起来统称为 ACID 特性。这是所有 DBMS 都必须遵守的规则。

原子性（Atomicity）
原子性是指在事务结束时，其中所包含的更新处理要么全部执行（COMMIT），要么完全不执行（ROLLBACK），也就是要么占有一切要么一无所有。

一致性（Consistency）
一致性指的是事务中包含的处理要满足数据库提前设置的约束，如主键约束或者 NOT NULL 约束等。
一致性也称为完整性。

隔离性（Isolation）
隔离性指的是保证不同事务之间互不干扰的特性。该特性保证了事务之间不会互相嵌套。此外，在某个事务中进行的更改，在该事务结束之前，对其他事务而言是不可见的。因此，即使某个事务向表中添加了记录，在没有提交之前，其他事务也是看不到新添加的记录的。

持久性（Durability）
持久性也可以称为耐久性，指的是在事务（不论是提交还是回滚）结束后， DBMS 能够保证该时间点的数据状态会被保存的特性。即使由于系统故障导致数据丢失，数据库也一定能通过某种手段进行恢复。
保证持久性的方法根据实现的不同而不同，其中最常见的就是将事务的执行记录保存到硬盘等存储介质中（该执行记录称为日志）。当发生故障时，可以通过日志恢复到故障发生前的状态。

4.6 事务的隔离级别

为什么要设置隔离级别

事务并发可能会存在的问题：
更新丢失（Lost update）
如果多个线程操作，基于同一个查询结构对表中的记录进行修改，那么后修改的记录将会覆盖前面修改的记录，前面的修改就丢失掉了，这就叫做更新丢失。这是因为系统没有执行任何的锁操作，因此并发事务并没有被隔离开来。
解决方法：对行加锁，只允许并发一个更新事务。

脏读（Dirty Read）：事务A读取事务B尚未提交的数据并在此基础上操作，而事务B执行回滚，那么A读取到的数据就是脏数据。
解决办法：如果在第一个事务提交前，任何其他事务不可读取其修改过的值，则可以避免该问题。

不可重复读（Non-repeatable Reads）
一个事务对同一行数据重复读取两次，但是却得到了不同的结果。事务A读取某一数据后，事务B对其做了修改，当事务A再次读该数据时得到与前一次不同的值。
解决办法：如果只有在修改事务完全提交之后才可以读取数据，则可以避免该问题。

幻读
指两次执行同一条 select 语句会出现不同的结果。
解决办法：如果在操作事务完成数据处理之前，任何其他事务都不可以添加新数据，则可避免该问题。

数据库事务的隔离级别

数据库事务的隔离级别有4个，由低到高依次为Read uncommitted(未授权读取、读未提交)、Read committed（授权读取、读提交）、Repeatable read（可重复读取）、Serializable（序列化），这四个级别可以逐个解决脏读、不可重复读、幻象读这几类问题。

Read uncommitted(未授权读取、读未提交)：
如果一个事务已经开始写数据，则另外一个事务则不允许同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现。这样就避免了更新丢失，却可能出现脏读。也就是说事务B读取到了事务A未提交的数据。
Read committed（授权读取、读提交）：
读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。该隔离级别避免了脏读，但是却可能出现不可重复读。事务A事先读取了数据，事务B紧接了更新了数据，并提交了事务，而事务A再次读取该数据时，数据已经发生了改变。
Repeatable read（可重复读取）：
可重复读是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，即使第二个事务对数据进行修改，第一个事务两次读到的的数据是一样的。这样就发生了在一个事务内两次读到的数据是一样的，因此称为是可重复读。读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。这样避免了不可重复读取和脏读，但是有时可能出现幻象读。（读取数据的事务）这可以通过“共享读锁”和“排他写锁”实现。
Serializable（序列化）：
提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。序列化是最高的事务隔离级别，同时代价也花费最高，性能很低，一般很少使用，在该级别下，事务顺序执行，不仅可以避免脏读、不可重复读，还避免了幻像读。

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。

第五章、复杂查询

5.1 视图

视图是什么？

数据库中的数据会被保存到计算机的存储设备（通常是硬盘）中。通过 SELECT 语句查询数据时，实际上就是从存储设备（硬盘）中读取数据，进行各种计算之后，再将结果返回给用户这样一个过程。
使用视图时并不会将数据保存到存储设备之中，而且也不会将数据保存到其他任何地方。实际上视图保存的是 SELECT 语句。
从视图中读取数据时，视图会在内部执行该 SELECT 语句并创建出一张临时表。
表中存储的是实际数据，而视图中保存的是从表中取出数据所使用的SELECT语句。

视图的优点
视图无需保存数据，可以节省存储设备的容量。
可以将频繁使用的 SELECT 语句保存成视图，这样就不用每次都重新书写了。创建好视图之后，只需在 SELECT 语句中进行调用，就可以方便地得到想要的结果了。

应该将经常使用的SELECT语句做成视图。

创建视图的方法

创建视图的CREATE VIEW语句

CREATE VIEW 视图名称(<视图列名1>, <视图列名2>, ……)
AS <SELECT语句>

SELECT 语句需要书写在 AS 关键字之后。 SELECT 语句中列的排列顺序和视图中列的排列顺序相同， SELECT 语句中的第 1 列就是视图中的第 1 列， SELECT 语句中的第 2 列就是视图中的第 2 列，以此类推。视图的列名在视图名称之后的列表中定义。

定义视图时可以使用任何 SELECT 语句，既可以使用 WHERE、 GROUP BY、 HAVING，也可以通过 SELECT * 来指定全部列。

使用视图的查询

在 FROM 子句中使用视图的查询，通常有如下两个步骤：
① 首先执行定义视图的 SELECT 语句
② 根据得到的结果，再执行在 FROM 子句中使用视图的 SELECT 语句

视图还可以在已有视图的基础上创建视图。

虽然语法上没有错误，但是应该尽量避免在视图的基础上创建视图。这是因为对多数 DBMS 来说，多重视图会降低 SQL 的性能。

视图的限制

视图的限制① ——定义视图时不能使用ORDER BY子句
视图和表一样，数据行都是没有顺序的。
实际上，有些 DBMS 在定义视图的语句中是可以使用 ORDER BY 子句的，但是这并不是通用的语法。因此，在定义视图时请不要使用 ORDER BY 子句。
定义视图时不要使用ORDER BY子句。

视图的限制② ——对视图进行更新
在 SELECT 语句中视图可以和表一样使用。
在使用INSERT，UPDATA，DELETE进行更新视图时，如果视图中有汇总的信息，是无法进行更新的，如果视图中不存在汇总得到的信息，是可以进行更新的。

删除视图

删除视图需要使用 DROP VIEW 语句

DROP VIEW 视图名称(<视图列名1>, <视图列名2>, ……)

5.2 子查询

子查询和视图

子查询的特点概括起来就是一张一次性视图。

视图并不是用来保存数据的，而是通过保存读取数据的 SELECT 语句的方法来为用户提供便利。反之，子查询就是将用来定义视图的SELECT语句直接用于FROM子句当中。

该 SELECT 语句包含嵌套的结构，首先会执行 FROM 子句中的 SELECT 语句，然后才会执行外层的 SELECT 语句。
子查询作为内层查询会首先执行。

子查询的名称

原则上子查询必须设定名称，尽量从处理内容的角度出发为子查询设定恰当的名称。
为子查询设定名称时需要使用 AS 关键字，该关键字有时也可以省略（Oracle）

标量子查询

标量就是单一的意思。
标量子查询则有一个特殊的限制，那就是必须而且只能返回 1 行 1列的结果，也就是返回表中某一行的某一列的值。
由于返回的是单一的值，因此标量子查询的返回值可以用在 = 或者 <> 这样需要单一值的比较运算符之中。这也正是标量子查询的优势所在。

在WHERE子句中使用标量子查询

标量子查询的书写位置

标量子查询的书写位置并不仅仅局限于 WHERE 子句中，通常任何可以使用单一值的位置都可以使用。也就是说， 能够使用常数或者列名的地方，无论是 SELECT 子句、 GROUP BY 子句、 HAVING 子句，还是ORDER BY 子句，几乎所有的地方都可以使用。

使用标量子查询时的注意事项

使用标量子查询时的注意事项，该子查询绝对不能返回多行结果。如果子查询返回了多行结果，那么它就不再是标量子查询，而仅仅是一个普通的子查询了，因此不能被用在 = 或者 <> 等需要单一输入值的运算符当中，也不能用在 SELECT 等子句当中。

5.3 关联子查询

普通的子查询和关联子查询的区别

关联：“具有相互关系或联系，其中一件事影响或依赖于另一件事。”

**有一些方法可以将外部查询的值合并到子查询的子句中。**这些类型的查询称为关联子查询，因为子查询的结果以某种形式连接到外部查询中的值。它们有时称为同步查询。

关联子查询的典型用法是在内部查询的WHERE子句中使用外部查询的列之一。在许多情况下，您希望将内部查询限制为数据子集，这是常识。

例子

在本例中，子查询中的查询条件与外部查询的值进行了绑定，从而限制子查询的结果集。
由于作为比较对象的都是同一张 Product 表，因此为了进行区别，分别使用了 P1 和 P2 两个别名。在使用关联子查询时，需要在表所对应的列名之前加上表的别名，以“< 表名 >.< 列名 >”的形式记述。

结合条件一定要写在子查询中

在关联查询中，关联条件一定要写在内部的子查询中。

第六章、函数、谓词、CASE表达式

6.1 各种各样的函数

函数的种类

函数，就是输入某一值得到相应输出结果的功能，输入值称为参数（parameter），输出值称为返回值。
函数大致可以分为以下几种：

• 算术函数（用来进行数值计算的函数）
• 字符串函数（用来进行字符串操作的函数）
• 日期函数（用来进行日期操作的函数）
• 转换函数（用来转换数据类型和值的函数）
• 聚合函数（用来进行数据聚合的函数）

聚合函数基本上只包含 COUNT、 SUM、 AVG、 MAX、 MIN这 5 种，而其他种类的函数总数则超过 200 种。
函数虽然数量众多，但常用函数只有 30 ~ 50 个。

算术函数

ABS——绝对值
ABS 是计算绝对值的函数。 绝对值（absolute value）不考虑数值的符号，表示一个数到原点的距离。

ABS(数值)

ABS 函数的参数为 NULL 时，结果也是 NULL。并非只有 ABS 函数如此，其实绝大多数函数对于 NULL 都返回 NULL。

MOD——求余
MOD 是计算除法余数（求余）的函数，是 modulo 的缩写。
7 / 3 的余数是 1，MOD（ 7, 3）的结果也是1。小数计算中并没有余数的概念，只能对整数类型的列使用 MOD 函数。
主流的 DBMS 都支持 MOD 函数， 只有 SQL Server 不支持该函数，SQL Server使用特殊的运算符 “%” 来计算余数。

MOD(被除数，除数)
-- sql server
被除数%除数

ROUND——四舍五入
ROUND 函数用来进行四舍五入操作。四舍五入在英语中称为 round。如果指定四舍五入的位数为 1，那么就会对小数点第 2 位进行四舍五入处理。

ROUND(对象数值，保留小数的位数)

字符串函数

||——拼接
“abc” + “de” = “abcde” ，在 SQL 中，可以通过由两条并列的竖线变换而成的 “ || ” 函数来实现。
“abc” || “de” 的结果就是 “abcde” 。三个以上的字符串也可以进行拼接。
进行字符串拼接时，如果其中包含 NULL，那么得到的结果也是NULL。
|| 函数在 SQL Server 和 MySQL 中无法使用。
SQL Server使用“+”运算符（函数）来连接字符串。 MySQL使用CONCAT函数来完成字符串的拼接。

字符串1||字符串2
-- mysql
CONCAT(字符串1, 字符串2)
-- sql server
字符串1 + 字符串2

LENGTH——字符串长度
LENGTH 可以计算出字符串中包含多少个字符。LENGTH(字符串) 返回字符的长度。
该函数无法在 SQL Server 中使用。SQL Server使用LEN函数来计算字符串的长度。

LENGTH(字符串)
-- sql server
LEN(字符串)

LOWER——小写转换
LOWER 函数只能针对英文字母使用，它会将参数中的字符串全都转换为小写。

LOWER(字符串)

UPPER ——大写转换
UPPER 函数只能针对英文字母使用，它会将参数中的字符串全都转换为大写。

UPPER(字符串)

REPLACE——字符串的替换
使用 REPLACE 函数，可以将字符串的一部分替换为其他的字符串。

REPLACE(对象字符串，替换前的字符串，替换后的字符串)

SUBSTRING——字符串的截取
使用 SUBSTRING 函数可以截取出字符串中的一部分字符串。截取的起始位置从字符串最左侧开始计算。

-- PostgreSQL/MySQL专用语法
SUBSTRING（对象字符串 FROM 截取的起始位置 FOR 截取的字符数）
-- SQL Server专用语法
SUBSTRING(对象字符串，截取的起始位置，截取的字符数)
-- Oracle/DB2专用语法
SUBSTR(对象字符串，截取的起始位置，截取的字符数)

日期函数

CURRENT_DATE——当前日期
CURRENT_DATE 函数能够返回 SQL 执行的日期，也就是该函数执行时的日期。由于没有参数，因此无需使用括号。
该函数无法在 SQL Server 中执行。

-- PostgreSQL MySQL
CURRENT_DATE
-- DB2
CURRENT DATE

CURRENT_TIME——当前时间
CURRENT_TIME 函数能够取得 SQL 执行的时间，也就是该函数执行时的时间。由于该函数也没有参数，因此同样无需使用括号。
该函数同样无法在 SQL Server 中执行，在 Oracle 和 DB2 中的语法同样略有不同。

-- PostgreSQL MySQL
CURRENT_TIME
-- DB2
CURRENT TIME

CURRENT_TIMESTAMP——当前日期和时间
CURRENT_TIMESTAMP 函数具有 CURRENT_DATE + CURRENT_TIME 的功能。使用该函数可以同时得到当前的日期和时间，当然也可以从结果中截取日期或者时间。

-- SQL Server PostgreSQL MySQL
CURRENT_TIMESTAMP;
-- DB2
CURRENT TIMESTAMP

EXTRACT——截取日期元素
使用 EXTRACT 函数可以截取出日期数据中的一部分，例如“年”，“月”，或者“小时”“秒”等。该函数的返回值并不是日期类型而是数值类型。

EXTRACT(日期元素 FROM 日期)
-- 例子
EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS year,
EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS month,
EXTRACT(DAY FROM CURRENT_TIMESTAMP) AS day,
EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS hour,
EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS minute,
EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;

转换函数

“转换”这个词的含义非常广泛，在 SQL 中主要有两层意思：一是数据类型的转换，简称为类型转换，另一层意思是值的转换。

CAST——类型转换
进行类型转换需要使用 CAST 函数。之所以需要进行类型转换，是因为可能会插入与表中数据类型不匹配的数据，或者在进行运算时由于数据类型不一致发生了错误，又或者是进行自动类型转换会造成处理速度低下。

CAST（转换前的值 AS 想要转换的数据类型）

例子1

-- SQL Server PostgreSQL
SELECT CAST('0001' AS INTEGER) AS int_col;
-- MySQL
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;
-- Oracle
SELECT CAST('0001' AS INTEGER) AS int_col
FROM DUAL;
-- DB2
SELECT CAST('0001' AS INTEGER) AS int_col
FROM SYSIBM.SYSDUMMY1;
------------------------------------------------------
执行结果
int_col
---------
1

例子2

-- SQL Server PostgreSQL MySQL
SELECT CAST('2009-12-14' AS DATE) AS date_col;
-- Oracle
SELECT CAST('2009-12-14' AS DATE) AS date_col
FROM DUAL;
-- DB2
SELECT CAST('2009-12-14' AS DATE) AS date_col
FROM SYSIBM.SYSDUMMY1;
-----------------------------------------------
date_col
------------
2009-12-14

COALESCE——将NULL转换为其他值

COALESCE 是 SQL 特有的函数。该函数会返回可变参数中左侧开始第 1个不是 NULL 的值。参数个数是可变的，因此可以根据需要无限增加。

COALESCE(数据1，数据2，数据3……)

例子

-- SQL Server PostgreSQL MySQL
SELECT COALESCE(NULL, 1) AS col_1,
COALESCE(NULL, 'test', NULL) AS col_2,
COALESCE(NULL, NULL, '2009-11-01') AS col_3;
-- Oracle
SELECT COALESCE(NULL, 1) AS col_1,
COALESCE(NULL, 'test', NULL) AS col_2,
COALESCE(NULL, NULL, '2009-11-01') AS col_3
FROM DUAL;
-- DB2
SELECT COALESCE(NULL, 1) AS col_1,
COALESCE(NULL, 'test', NULL) AS col_2,
COALESCE(NULL, NULL, '2009-11-01') AS col_3
FROM SYSIBM.SYSDUMMY1;
---------------------------------------------------------------
col_1 | col_2 | col_3
-------+-------+-----------
1 | test | 2009-11-01

6.2 谓词

什么是谓词

谓词就是函数中的一种，是需要满足特定条件的函数，该条件就是返回值是真值。
对通常的函数来说，返回值有可能是数字、字符串或者日期等，但是谓词的返回值全都是真值（TRUE/FALSE/UNKNOWN）。这也是谓词和函数的最大区别。
=、 <、 >、 <> 等比较运算符，其正式的名称就是比较谓词。

常用的谓词

• LIKE
• BETWEEN
• IS NULL、 IS NOT NULL
• IN
• EXISTS

LIKE谓词——字符串的部分一致查询

LIKE 谓词用于字符串的模糊匹配。当需要进行字符串的部分一致查询时需要使用该谓词。
部分一致大体可以分为前方一致、中间一致和后方一致三种类型。、

-- 语法
? LIKE ?

前方一致查询，字符串的开头匹配
%可以匹配任何字符串。
_可以匹配任何一个字符。

-- 匹配的结果为真，%可以匹配任意字符串
"aabbcc" LIKE "aa%"  
-- 匹配的结果为真，"____"与"bbcc"匹配
"aabbcc" LIKE "aa____"

中间一致查询，字符串的中间部分匹配

-- 匹配结果为真。
"aaabbcc" LIKE "%bb%" 
-- 匹配结果为真。
"aaabbcc" LIKE "___bb__"

后方一致查询，字符串的结尾匹配

-- 匹配结果为真
"aabbcc" LIKE "%cc"
-- 匹配结果为真
"aabbcc" LIKE "____cc"

BETWEEN谓词——范围查询

使用 BETWEEN 可以进行范围查询。该谓词与其他谓词或者函数的不同之处在于它使用了 3 个参数。
BETWEEN 进行范围查询的时候，范围包含边界值,如果不想包含边界值，可以使用 <，>。

-- 语法
? BETWEEN ? AND ?
-- 例子,结果为真，范围边界值为[50,100]
100 BETWEEN 50 AND 150 

IS NULL、 IS NOT NULL——判断是否为NULL

为了选取出某些值为 NULL 的列的数据，不能使用 =，而只能使用特定的谓词 IS NULL
想要选取 NULL 以外的数据时，需要使用IS NOT NULL

-- 语法
? IS NULL
? IS NOT NULL
-- 如果a为NULL，结果为true，否则为false。
a IS NULL
-- 如果a不是NULL，结果为true，否则为false。
a IS NOT NULL

IN谓词——OR的简便用法

IN和OR有相同的作用，IN简化了OR的用法。

-- 语法，当参数与IN里面的任意值匹配结果就为真
? IN (?,?,?...)

NOT IN 与 IN，作用相反。

-- 语法，当参数不在NOT IN里面的参数列表中，结果为真
? NOT IN(?,?,?...)

注意的是，在使用IN 和 NOT IN 时是无法选取出NULL 数据的。

IN和子查询
IN 谓词（NOT IN 谓词）具有其他谓词所没有的用法，那就是可以使用子查询作为其参数。
子查询就是 SQL内部生成的表，能够将表作为 IN 的参数。同理，视图也可以作为 IN 的参数。

-- 语法
? IN(子查询)
? NOT IN(子查询)

EXIST谓词

一言以蔽之， EXIST谓词的作用就是“判断是否存在满足某种条件的记录”。如果存在这样的记录就返回真（TRUE），如果不存在就返回假（FALSE）。EXIST（存在）谓词的主语是“记录”。

EXIST的参数，EXIST只有一个参数，左侧没有参数，右侧有一个子查询作为参数。

-- 语法，如果子查询的结果不为空，则EXIST(子查询)的结果为真，否则结果为假。
EXIST(子查询)

EXIST 通常都会使用关联子查询作为参数。

-- 例子
SELECT product_name, sale_price
FROM Product AS P
WHERE 
EXISTS(
	SELECT * FROM ShopProduct AS SP 
	WHERE SP.shop_id = '000C' AND SP.product_id = P.product_id
);

子查询中的SELECT
由于 EXIST 只关心记录是否存在，因此返回哪些列都没有关系。写SELECT*只是一个语法习惯。

NOT EXIST

-- 语法，如果子查询的结果为空，则NOT EXIST(子查询)的结果为真，否则结果为假。
NOT EXIST(子查询)

6.3 CASE表达式

什么是CASE表达式

CASE 表达式是在区分情况时使用的，这种情况的区分在编程中通常称为（条件）分支。

CASE表达式的语法

CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种。
搜索CASE表达式

CASE 
	WHEN <求值表达式> THEN <表达式>
	WHEN <求值表达式> THEN <表达式>	
	WHEN <求值表达式> THEN <表达式>
	...
	ELSE <表达式>
END

CASE 表达式会从对最初的 WHEN 子句中的<求值表达式 >进行求值开始执行。如果结果为TRUE，那么就返回 THEN 子句中的表达式，CASE 表达式的执行到此为止。如果结果不为TRUE，那么就跳转到下一条 WHEN 子句的求值之中。如果直到最后的 WHEN 子句为止返回结果都不为真，那么就会返回 ELSE中的表达式，执行终止。
简单CASE表达式
简单 CASE 表达式比搜索 CASE 表达式简单，但是会受到条件的约束，因此通常情况下都会使用搜索 CASE 表达式。

简单CASE表达式
CASE <表达式>
	WHEN <表达式> THEN <表达式>
	WHEN <表达式> THEN <表达式>
	WHEN <表达式> THEN <表达式>
	...
	ELSE <表达式>
END

简单 CASE 表达式也是从最初的 WHEN 子句开始进行，逐一判断每个 WHEN 子句直到返回真值为止。此外，没有能够返回真值的WHEN 子句时，也会返回 ELSE 子句指定的表达式。两者的不同之处在于，简单CASE 表达式最初的“CASE< 表达式 >”也会作为求值的对象。

CASE表达式的使用方法

例子1：

ELSE NULL是“上述情况之外时返回 NULL” 的意思。 ELSE 子句指定处理不满足 WHEN 子句中的条件的记录， NULL 之外的其他值或者表达式也都可以写在ELSE 子句之中。
ELSE 子句也可以省略不写，这时会被默认为 ELSE NULL。

CASE 表达式最后的“END”是不能省略的。忘记书写END会发生语法错误。

CASE 表达式的便利之处就在于它是一个表达式。表达式可以书写在任意位置，

例子2：


搜索CASE和简单CASE的对比

像“CASE product_type”这样，简单 CASE 表达式在将想要求值的表达式（这里是列）书写过一次之后，就无需在之后的 WHEN 子句中重复书写 “product_type” 。虽然看上去简化了书写，但是想要在 WHEN 子句中指定不同列时，简单 CASE 表达式就无能为力了。

第七章、集合运算

7.1 表的加减法

什么是集合运算

集合在数学领域表示（各种各样的）事物的总和，在数据库领域表示记录的集合。具体来说，表、视图和查询的执行结果都是记录的集合。

所谓集合运算，就是对满足同一规则的记录进行的加减等四则运算。通过集合运算，可以得到两张表中记录的集合或者公共记录的集合，又或者其中某张表中的记录的集合。像这样用来进行集合运算的运算符称为集合运算符。

表的加法——UNION

UNION集合运算符是进行记录加法运算符（并集）。
例子：

上述结果包含了两张表中的全部商品。

商品编号为“0001”~“0003”的 3 条记录在两个表中都存在。UNION 等集合运算符通常都会除去重复的记录。
集合运算符会除去重复的记录。

集合运算的注意事项

注意事项1 —— 作为运算对象的记录的列数必须相同

-- 列数不一致时会发生错误
SELECT product_id, product_name
FROM Product
UNION
SELECT product_id, product_name, sale_price
FROM Product2;

注意事项2 —— 作为运算对象的记录中列的类型必须一致

-- 数据类型不一致时会发生错误
SELECT product_id, sale_price
FROM Product
UNION
SELECT product_id, regist_date
FROM Product2;

注意事项3 —— 可以使用任何SELECT语句，但ORDER BY子句只能在最后使用一次。

SELECT product_id, product_name
FROM Product
WHERE product_type = '厨房用具'
UNION
SELECT product_id, product_name
FROM Product2
WHERE product_type = '厨房用具'
ORDER BY product_id;

包含重复行的集合运算——ALL选项

在集合运算符中使用ALL选项，可以保留重复行。
在 UNION 的结果中保留重复行的语法。其实非常简单，只需要在 UNION 后面添加 ALL 关键字就可以了。这里的 ALL 选项，在 UNION 之外的集合运算符中同样可以使用。

选取表中公共部分——INTERSECT

INTERSECT集合运算符是选取两个记录集合中公共部分（交集）。
MySQL尚不支持INTERSECT，所以无法使用。

INTERSECT 应用于两张表，选取出它们当中的公共记录。希望保留重复行时需要使用 INTERSECT ALL。

记录的减法——EXCEPT

EXCEPT 集合运算符就是进行减法运算（差集）。
Oracle 不使用EXCEPT，而是使用其特有的MINUS运算符。使用Oracle中用MINUS代替EXCEPT。 MySQL还不支持EXCEPT，也无法使用。
例子：

7.2 联结（以列为单位对表进行联结）

什么是联结

UNION 和 INTERSECT 等集合运算，这些集合运算的特征就是以行方向为单位进行操作。进行这些集合运算时，会导致记录行数的增减。使用UNION会增加记录行数，使用INTERSECT 或者 EXCEPT 会减少记录行数。这些运算不会导致列数的改变。作为集合运算对象的表的前提就是列数要一致。因此，运算结果不会导致列的增减。
联结（JOIN）运算，就是将其他表中的列添加过来，进行“添加列”的运算。该操作通常用于无法从
一张表中获取期望数据（列）的情况。
使用联结就可以从多张表（3 张以上的表也可以）中选取数据了。

内联结——INNER JOIN

例子：
在Oracle的FROM子句中不能使用AS（会发生错误）。

内联结要点1 ——FROM子句
使用关键字 INNER JOIN 就可以将两张表联结在一起了。 SP 和 P 分别是这两张表的别名。
进行联结时需要在FROM子句中使用多张表。

FROM ShopProduct AS SP INNER JOIN Product AS P

内联结要点2 ——ON子句
注意的是 ON 后面的联结条件。
ON 是专门用来指定联结条件的，它能起到与 WHERE 相同的作用。需要指定多个键时，可以使用 AND、 OR。在进行内联结时ON子句是必不可少的，并且 ON 必须书写在 FROM 和 WHERE 之间。
进行内联结时必须使用ON子句，并且要书写在FROM和WHERE之间。

ON SP.product_id = P.product_id

联结条件可以使用 “=” 来记述。在语法上，还可以使用 <= 和 BETWEEN 等谓词。
ON 就像是连接河流两岸城镇的桥梁一样

内联结要点3 ——SELECT子句
需要注意SELECT 子句中指定的列。
使用联结时SELECT子句中的列需要按照“<表的别名>.<列名>”的格式进行书写。

SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price

内联结和WHERE子句结合使用

WHERE、GROUP BY、 HAVING、 ORDER BY 等工具都可以正常使用。
可以将联结之后的结果想象为新创建出来的一张表，对这张表使用WHERE 子句等工具，这张“表”只在 SELECT 语句执行期间存在， SELECT 语句执行之后就会消失。

外联结——OUTER JOIN

外联结也是通过 ON 子句的联结键将两张表进行联结，并从两张表中同时选取相应的列的。
例子：


外联结要点 ——选取出单张表中全部的信息
内联结只能选取出同时存在于两张表中的数据。对于外联结来说，只要数据存在于某一张表当中，就能够读取出来。

外联结要点 ——每张表都是主表吗？
外联结还有一点非常重要，那就是要把哪张表作为主表。最终的结果中会包含主表内所有的数据。指定主表的关键字是 LEFT 和 RIGHT。顾名思义， 使用 LEFT 时 FROM 子句中写在左侧的表是主表，
使用 RIGHT 时右侧的表是主表。
外联结中使用LEFT、 RIGHT来指定主表。使用二者所得到的结果完全相同。

3张以上的表的联结

交叉联结——CROSS JOIN

交叉联结是所有联结运算的基础。

对满足相同规则的表进行交叉联结的集合运算符是 CROSS JOIN（笛卡儿积）。进行交叉联结时无法使用内联结和外联结中所使用的 ON 子句，这是因为交叉联结是对两张表中的全部记录进行交叉组合，因此结果中的记录数通常是两张表中行数的乘积。

内联结是交叉联结的一部分，“内”也可以理解为“包含在交叉联结结果中的部分”。相反，外联结的“外”可以理解为“交叉联结结果之外的部分”。

联结的特定语法和过时语法

SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
FROM ShopProduct SP, Product P
WHERE SP.product_id = P.product_id AND SP.shop_id = '000A';

像上面的语法虽然可以得到正确的结果，但是将连接条件写在WHERE中存在一些问题。

• 第一，使用这样的语法无法马上判断出到底是内联结还是外联结（又或者是其他种类的联结）。
• 第二，由于联结条件都写在 WHERE 子句之中，因此无法在短时间内分辨出哪部分是联结条件，哪部分是用来选取记录的限制条件。
• 第三，我们不知道这样的语法到底还能使用多久。每个 DBMS 的开发者都会考虑放弃过时的语法，转而支持新的语法。

对于联结的过时语法和特定语法，虽然不建议使用，但还是希望大家能够读懂。

第八章、SQL高级处理

8.1 窗口函数

窗口函数也称为 OLAP 函数。OLAP 是 OnLine Analytical Processing 的简称，意思是对数据库数据
进行实时分析处理。
窗口函数就是为了实现 OLAP 而添加的标准 SQL 功能 。

窗口函数的语法

<窗口函数> OVER ([PARTITION BY <列清单>] ORDER BY <排序用列清单>)

[ ] 中的内容可以省略。

其中重要的关键字是 PARTITION BY 和 ORDER BY，理解这两个关键字的作用是帮助我们理解窗口函数的关键。

能够作为窗口函数使用的函数

• 能够作为窗口函数的聚合函数（ SUM、 AVG、 COUNT、 MAX、 MIN）
• RANK、DENSE_RANK、ROW_NUMBER 等专用窗口函数

将聚合函数书写在“< 窗口函数 >”中，就能够当作窗口函数来使用了。

语法的基本使用方法——使用RANK函数

RANK 是用来计算记录排序的函数。
例子


PARTITION BY 能够设定排序的对象范围。为了按照商品种类进行排序，指定了 product_type。
ORDER BY 能够指定按照哪一列、何种顺序进行排序。为了按照销售单价的升序进行排列，我们指定了 sale_price。可以通过关键字ASC/DESC 来指定升序和降序。省略该关键字时会默认按照 ASC。

PARTITION BY 在横向上对表进行分组，而 ORDER BY决定了纵向排序的规则。PARTITION BY 子句并不具备GROUP BY 子句的汇总功能。使用 RANK 函数并不会减少原表中记录的行数。

窗口函数兼具分组（PARTITION BY）和排序（ORDER BY）两种功能。

通过 PARTITION BY 分组后的记录集合称为窗口。此处的窗口并非“窗户”的意思，而是代表范围。此外，各个窗口在定义上绝对不会包含共通的部分。

无需指定PARTITION BY
PARTITION BY 并不是必需的，即使不指定也可以正常使用窗口函数。
这和没有使用 GROUP BY 的聚合函数时的效果一样，也就是将整个表作为一个大的窗口来使用。

专用窗口函数的种类

• RANK函数
  计算排序时，如果存在相同位次的记录，则会跳过之后的位次。
  例：有 3 条记录排在第 1 位时：1 位、1 位、1 位、4 位……
• DENSE_RANK函数
  同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。
  例：有 3 条记录排在第 1 位时：1 位、1 位、1 位、2 位……
• ROW_NUMBER函数
  赋予唯一的连续位次。
  例：有 3 条记录排在第 1 位时：1 位、2 位、3 位、4 位……

由于专用窗口函数无需参数，因此通常括号中都是空的。
使用 RANK 或 ROW_NUMBER 时无需任何参数，只需要像 RANK ()或者 ROW_NUMBER() 这样保持括号中为空就可以了。

窗口函数的适用范围

使用窗口函数的位置却有非常大的限制。更确切地说，窗口函数只能书写在一个特定的位置。
原则上窗口函数只能在SELECT子句中使用。
在 DBMS 内部，窗口函数是对 WHERE 子句或者 GROUP BY 子句处理后的“结果”进行的操作。所以窗口函数只能写在 SELECT 子句中，在WHERE子句或者GROUP BY处理后进行操作。

作为窗口函数使用的聚合函数

所有的聚合函数都能用作窗口函数，其语法和专用窗口函数完全相同。
例子：


计算该合计值的逻辑就像金字塔堆积那样，一行一行逐渐添加计算对象。在按照时间序列的顺序，计算各个时间的销售额总额等的时候，通常都会使用这种称为累计的统计方法。
使用其他聚合函数时的操作逻辑也和本例相同。

使用AVG函数作为窗口函数

计算移动平均

窗口函数就是将表以窗口为单位进行分割，并在其中进行排序的函数。其实其中还包含在窗口中指定更加详细的汇总范围的备选功能，该备选功能中的汇总范围称为框架。

指定框架范围

这里使用了 ROWS（“行”）和 PRECEDING（“之前”）两个关键字，将框架指定为“截止到之前 ~ 行”。

ROWS 2 PRECEDING 

就是将框架指定为“截止到之前 2 行”，也就是将作为汇总对象的记录限定为如下的“最靠近的 3 行”。

• 自身（当前记录）
• 之前1行的记录
• 之前2行的记录

框架是根据当前记录来确定的，因此和固定的窗口不同，其范围会随着当前记录的变化而变化。
这样的统计方法称为移动平均（moving average）。

使用关键字 FOLLOWING（“之后”）替换 PRECEDING，就可以指定“截止到之后 ~ 行”作为框架。

ROWS 2 FOLLOWING

• 自身（当前记录）
• 之后1行的记录
• 之后2行的记录

将当前记录的前后行作为汇总对象，同时使用 PRECEDING和FOLLOWING。

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

• 之前1行的记录
• 自身（当前记录）
• 之后1行的记录

两个ORDER BY

使用窗口函数时必须要在 OVER 子句中使用ORDER BY。OVER 子句中的 ORDER BY 只是用来决定
窗口函数按照什么样的顺序进行计算的，对结果的排列顺序并没有影响。

如果按照 ranking 列的升序进行排列，需要在SELECT语句后使用 ORDER BY子句进行对结果集排序。

将聚合函数作为窗口函数使用时，会以当前记录为基准来决定汇总对象的记录。

8.2 GROUPING运算符

同时得到合计行

使用GROUP BY 无法得到合计行。因为 GROUP BY 子句是用来指定聚合键的场所，所以只会根据这里指定的键分割数据，当然不会出现合计行。而合计行是不指定聚合键时得到的汇总结果。

如果想要得到合计行和汇总结果，通常的做法是分别计算出合计行和按照商品种类进行汇总的结果，然后通过 UNION ALLA 连接在一起。

UNION ALL和UNION的不同之处在于它不会对结果进行排序，因此比UNION的性能更好。

ROLLUP——同时得出合计和小计

为了满足用户的需求，标准 SQL 引入了 GROUPING 运算符。使用该运算符就能通过非常简单的 SQL 得到之前那样的汇总单位不同的汇总结果了。
目前 PostgreSQL 和 MySQL 并不支持GROUPING运算符（MySQL 仅支持ROLLUP）。
GROUPING 运算符包含以下 3 种。

• ROLLUP
• CUBE
• GROUPING SETS

ROLLUP的使用方法
使用 ROLLUP 就可以通过非常简单 的 SELECT 语句同时计算出合计行。
例子：

从语法上来说，就是将 GROUP BY 子句中的聚合键清单像 ROLLUP（ < 列 1>,< 列 2>,…） 这样使用。该运算符的作用，就是“一次计算出不同聚合键组合的结果”。
在本例中就是一次计算出了如下两种组合的汇总结果。

• GROUP BY ()
• GROUP BY (product_type)

GROUP BY() 表示没有聚合键，也就相当于没有 GROUP BY子句（这时会得到全部数据的合计行的记录），该合计行记录称为超级分组记录（super group row）。超级分组记录默认使用NULL作为聚合键。

将“登记日期”添加到聚合键当中


SELECT 语句的结果相当于使用 UNION 对如下 3 种模式的聚合级的不同结果进行连接。

ROLLUP 是“卷起”的意思，比如卷起百叶窗、窗帘卷，等等。其名称也形象地说明了该操作能够得到像从小计到合计这样，从最小的聚合级开始，聚合单位逐渐扩大的结果。
ROLLUP可以同时得出合计和小计，是非常方便的工具。

GROUPING函数——让NULL更加容易分辨

SQL 提供了一个用来判断超级分组记录的 NULL 的特定函数 —— GROUPING 函数。该函数在其参数列的值为超级分组记录所产生的 NULL 时返回 1，其他情况返回 0。

这样就能分辨超级分组记录中的 NULL 和原始数据本身的 NULL 了。
使用 GROUPING 函数还能在超级分组记录的键值中插入字符串。当 GROUPING 函数的返回值为 1 时，指定“合计”或者“小计”等字符串，其他情况返回通常的列的值。

使用GROUPING函数能够简单地分辨出原始数据中的NULL和超级分组记录中的NULL。

CUBE——用数据来搭积木

CUBE 的语法和 ROLLUP 相同，只需要将 ROLLUP 替换为 CUBE 就可以了。

与 ROLLUP 的结果相比， CUBE 的结果中多出了几行记录。多出来的记录就是只把 regist_date 作为聚合键所得到的汇总结果。

• GROUP BY ()
• GROUP BY (product_type)
• GROUP BY (regist_date) ←添加的组合
• GROUP BY (product_type, regist_date)

所谓 CUBE，就是将 GROUP BY 子句中聚合键的所有可能的组合的汇总结果集中到一个结果中。因此，组合的个数就是 2ⁿ（n 是聚合键的个数）。本例中聚合键有 2 个，所以 2² = 4。如果再添加 1 个变为 3 个聚合键的话，就是 2³ = 8。

可以把CUBE理解为将使用聚合键进行切割的模块堆积成一个立方体。

GROUPING SETS——取得期望的积木

GROUPING SETS运算符可以用于从 ROLLUP 或者 CUBE 的结果中取出部分记录。
CUBE 的结果就是根据聚合键的所有可能的组合计算而来的。如果希望从中选取出将“商品种类”和“登记日期”各自作为聚合键的结果，或者不想得到“合计记录和使用 2 个聚合键的记录”时，可以使用 GROUPING SETS。
例子：
与 ROLLUP 或者CUBE 能够得到规定的结果相对， GROUPING SETS 用于从中取出个别条件对应的不固定的结果。