安装不同平台

1.mac m1
2.centos7 arm
3.windows10
再有些不常用，需要直接百度

连接mysql

用mysql workbench就行
记得填主机名（ip地址），用户，密码

初识数据库

数据库定义

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

DBMS的种类

DBMS 主要通过数据的保存格式（数据库的种类）来进行分类，现阶段主要有以下 5 种类型:

1.层次数据库（Hierarchical Database，HDB）
2.关系数据库（Relational Database，RDB）
Oracle Database：甲骨文公司的RDBMS
SQL Server：微软公司的RDBMS
DB2：IBM公司的RDBMS
PostgreSQL：开源的RDBMS
MySQL：开源的RDBMS
如上是5种具有代表性的RDBMS，其特点是由行和列组成的二维表来管理数据，这种类型的 DBMS 称为关系数据库管理系统（Relational Database Management System，RDBMS）。
3.面向对象数据库（Object Oriented Database，OODB）
4.XML数据库（XML Database，XMLDB）
5.键值存储系统（Key-Value Store，KVS），举例：MongoDB

RDBMS的常见系统结构

示例图

初识SQL

基本概念

数据库中存储的表结构类似于excel中的行和列，在数据库中，行称为记录，它相当于一条记录，列称为字段，它代表了表中存储的数据项目。
行和列交汇的地方称为单元格，一个单元格中只能输入一条记录。

SQL语句分类

根据对 RDBMS 赋予的指令种类的不同，SQL 语句可以分为以下三类：
1.DDL ：DDL（Data Definition Language，数据定义语言） 用来创建或者删除存储数据用的数据库以及数据库中的表等对象。DDL 包含以下几种指令。

CREATE ： 创建数据库和表等对象
DROP ： 删除数据库和表等对象
ALTER ： 修改数据库和表等对象的结构

2.DML ：DML（Data Manipulation Language，数据操纵语言） 用来查询或者变更表中的记录。DML 包含以下几种指令。

SELECT ：查询表中的数据
INSERT ：向表中插入新数据
UPDATE ：更新表中的数据
DELETE ：删除表中的数据

3.DCL ：DCL（Data Control Language，数据控制语言） 用来确认或者取消对数据库中的数据进行的变更。除此之外，还可以对 RDBMS 的用户是否有权限操作数据库中的对象（数据库表等）进行设定。DCL 包含以下几种指令。

COMMIT ： 确认对数据库中的数据进行的变更
ROLLBACK ： 取消对数据库中的数据进行的变更
GRANT ： 赋予用户操作权限
REVOKE ： 取消用户的操作权限

实际使用的 SQL 语句当中有 90% 属于 DML

SQL的基本书写规则

1.SQL语句要以分号（ ; ）结尾
2.SQL 不区分关键字的大小写，但是插入到表中的数据是区分大小写的
3.win 系统默认不区分表名及字段名的大小写
linux / mac 默认严格区分表名及字段名的大小写 * 本教程已统一调整表名及字段名的为小写，以方便初学者学习使用。
4.常数的书写方式是固定的
'abc', 1234, '26 Jan 2010', '10/01/26', '2010-01-26'......

5.单词需要用半角空格或者换行来分隔
SQL 语句的单词之间需使用半角空格或换行符来进行分隔，且不能使用全角空格作为单词的分隔符，否则会发生错误，出现无法预期的结果。

数据库的创建

CREATE DATABASE < 数据库名称 > ;

表的创建

CREATE TABLE < 表名 >
( < 列名 1> < 数据类型 > < 该列所需约束 > ,
  < 列名 2> < 数据类型 > < 该列所需约束 > ,
  .
  .
  .
  < 该表的约束 1> , < 该表的约束 2> ,……);

命名规则

只能使用半角英文字母、数字、下划线（_）作为数据库、表和列的名称
名称必须以半角英文字母开头（）

数据类型的指定

常见的：INTEGER 型，CHAR 型，VARCHAR 型，DATE 型

约束的设置

约束是除了数据类型之外，对列中存储的数据进行限制或者追加条件的功能。
NOT NULL是非空约束，即该列必须输入数据。
PRIMARY KEY是主键约束，代表该列是唯一值，可以通过该列取出特定的行的数据。

表的删除

1.DROP TABLE < 表名 > ;
2.ALTER TABLE < 表名 > ADD COLUMN < 列的定义 >;
例子：ALTER TABLE product ADD COLUMN product_name_pinyin VARCHAR(100);
3.ALTER TABLE < 表名 > DROP COLUMN < 列名 >;
4.删除表中特定的行（语法）
DELETE FROM product WHERE COLUMN_NAME='XXX';
5.清空表内容

表的更新

基本语法：
UPDATE <表名>
SET <列名> = <表达式> [, <列名2>=<表达式2>...]  
WHERE <条件>  -- 可选，非常重要
ORDER BY 子句 --可选
LIMIT 子句; --可选

向表中插入数据

1.INSERT INTO <表名> (列1, 列2, 列3, ……) VALUES (值1, 值2, 值3, ……);  
2.查询结果导入表
例：将商品表中的数据复制到商品复制表中
INSERT INTO productcopy (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
SELECT product_id, product_name, product_type, sale_price, purchase_price, regist_date
FROM Product;  

索引

相关概念

1.作用：索引可以大大提高MySQL的检索速度。
2.索引创建了一种有序的数据结构，采用二分法搜索数据时，其复杂度为 1 ，1000多万的数据只要搜索23次，其效率是非常高效的。
3.语法：
第一种：CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);
第二种：CREATE INDEX indexName ON table_name (column_name)
第三种：ALTER table tableName ADD INDEX indexName(columnName)

索引分类

文字说明

作业

第一题

第一题参考答案
create table addressbook (
regist_no int not null,
name varchar(128) not null,
address varchar(256) not null,
mail_address char(20),
primary key (regist_no)
);

第二题

ALTER TABLE product ADD COLUMN postal_code CHAR(8) not null;

复杂一点的查询

视图

为什么有视图的存在呢？视图到底是什么？视图与表有什么不同呢？

什么是视图

视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的，所以操作视图时会根据创建视图的SELECT语句生成一张虚拟表，然后在这张虚拟表上做SQL操作。

视图与表有什么区别

1.凝练概括视图与表的区别---“是否保存了实际的数据”。所以视图并不是数据库真实存储的数据表，它可以看作是一个窗口，通过这个窗口我们可以看到数据库表中真实存在的数据。所以我们要区别视图和数据表的本质，即视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上。
2.顺口溜方便记忆视图与表的关系：“视图不是表，视图是虚表，视图依赖于表”。

为什么会存在视图

主要有以下几点原因：

1. 通过定义视图可以将频繁使用的SELECT语句保存以提高效率。
2. 通过定义视图可以使用户看到的数据更加清晰。
3. 通过定义视图可以不对外公开数据表全部字段，增强数据的保密性。
4. 通过定义视图可以降低数据的冗余。

如何创建视图

语句：CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS <SELECT语句>
需要注意的是视图名在数据库中需要是唯一的，不能与其他视图和表重名。

在 MySQL中视图的定义是允许使用 ORDER BY 语句的，但是若从特定视图进行选择，而该视图使用了自己的 ORDER BY 语句，则视图定义中的 ORDER BY 将被忽略。

各种各样的函数

sql 自带了各种各样的函数，极大提高了 sql 语言的便利性。
所谓函数，类似一个黑盒子，你给它一个输入值，它便按照预设的程序定义给出返回值，输入值称为参数。
函数大致分为如下几类：

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

算术函数

1. ABS -- 绝对值
   语法：ABS( 数值 )
2. MOD -- 求余数
   语法：MOD( 被除数，除数 )
3. ROUND -- 四舍五入
   语法：ROUND( 对象数值，保留小数的位数 )

字符串函数

4. CONCAT -- 拼接
   语法：CONCAT(str1, str2, str3)

5. LENGTH -- 字符串长度
   语法：LENGTH( 字符串 )

6. LOWER -- 小写转换
   LOWER 函数只能针对英文字母使用，它会将参数中的字符串全都转换为小写。该函数不适用于英文字母以外的场合，不影响原本就是小写的字符。类似的， UPPER 函数用于大写转换。

7. REPLACE -- 字符串的替换
   语法：REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )

8. SUBSTRING -- 字符串的截取
   语法：SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）
   注意：使用 SUBSTRING 函数 可以截取出字符串中的一部分字符串。截取的起始位置从字符串最左侧开始计算，索引值起始为1。

9.SUBSTRING_INDEX -- 字符串按索引截取
语法：SUBSTRING_INDEX (原始字符串， 分隔符，n)
该函数用来获取原始字符串按照分隔符分割后，第 n 个分隔符之前（或之后）的子字符串，支持正向和反向索引，索引起始值分别为 1 和 -1。
10.（扩展内容）REPEAT -- 字符串按需重复多次
语法：REPEAT(string, number)
该函数用来对特定字符实现按需重复。

日期函数

1.CURRENT_DATE -- 获取当前日期
语法：SELECT CURRENT_DATE;
2.CURRENT_TIME -- 当前时间
语法：SELECT CURRENT_TIME;
3.CURRENT_TIMESTAMP -- 当前日期和时间
语法：SELECT CURRENT_TIMESTAMP;
4.EXTRACT -- 截取日期元素
语法例子：SELECT CURRENT_TIMESTAMP as now,
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；另一层意思是值的转换。

1.CAST -- 类型转换
语法：CAST（转换前的值 AS 想要转换的数据类型）
例子：SELECT CAST('2009-12-14' AS DATE) AS date_col;
注意：需要特别注意的是，当要转换为整型时，需要指定为 SIGNED（有符号） 或者 UNSIGNED（无符号）
2.COALESCE -- 将NULL转换为其他值
语法：COALESCE(数据1，数据2，数据3……)
注意：COALESCE 是 SQL 特有的函数。该函数会返回可变参数 A 中左侧开始第 1个不是NULL的值。参数个数是可变的，因此可以根据需要无限增加。
在 SQL 语句中将 NULL 转换为其他值时就会用到转换函数。
例子：SELECT COALESCE(NULL, 11) AS col_1,
COALESCE(NULL, 'hello world', NULL) AS col_2;

谓词

什么是谓词

谓词就是返回值为真值的函数，包括TRUE / FALSE / UNKNOWN。
谓词主要有以下几个：
LIKE
BETWEEN
IS NULL、IS NOT NULL
IN
EXISTS

LIKE谓词 -- 用于字符串的部分一致查询

当需要进行字符串的部分一致查询时需要使用该谓词。
部分一致大体可以分为前方一致、中间一致和后方一致三种类型。
1.%是代表“零个或多个任意字符串”的特殊符号，如'%ddd%'。
2.下划线匹配任意 1 个字符，如'abc_'（两个）

BETWEEN谓词 -- 用于范围查询

使用 BETWEEN 可以进行范围查询。该谓词与其他谓词或者函数的不同之处在于它使用了 3 个参数。
如：WHERE sale_price BETWEEN 100 AND 1000

IS NULL、 IS NOT NULL -- 用于判断是否为NULL

1.为了选取出某些值为 NULL 的列的数据，不能使用 =，而只能使用特定的谓词IS NULL。
例子：WHERE purchase_price IS NULL
2.与此相反，想要选取 NULL 以外的数据时，需要使用IS NOT NULL
例子：WHERE purchase_price IS NOT NULL

IN谓词 -- OR的简便用法

1.多个查询条件取并集时可以选择使用or语句。
例子：WHERE purchase_price = 320
OR purchase_price = 500
OR purchase_price = 5000
虽然上述方法没有问题，但还是存在一点不足之处，那就是随着希望选取的对象越来越多， SQL 语句也会越来越长，阅读起来也会越来越困难。这时， 我们就可以使用IN 谓词 `IN(值1, 值2, 值3, ......)来替换上述 SQL 语句。
例子WHERE purchase_price IN (320, 500, 5000)
2.可以使用否定形式NOT IN
例子：WHERE purchase_price NOT IN (320, 500, 5000)
3.注意：需要注意的是，在使用IN 和 NOT IN 时是无法选取出NULL数据的，NULL 只能使用 IS NULL 和 IS NOT NULL 来进行判断。

使用子查询作为IN谓词的参数

1.IN 谓词（NOT IN 谓词）具有其他谓词所没有的用法，那就是可以使用子查询作为其参数。
子查询就是 SQL内部生成的表，因此也可以说“能够将表作为 IN 的参数”。同理，我们还可以说“能够将视图作为 IN 的参数”。
例子：SELECT product_name, sale_price
FROM product
WHERE product_id IN (SELECT product_id
FROM shopproduct WHERE shop_id = '000C');
子查询是从最内层开始执行的（由内而外）
2.NOT IN 同样支持子查询作为参数，用法和 in 完全一样。

EXIST 谓词

1.EXIST谓词的使用方法
之前我们学过的谓词，基本上都是像“列 LIKE 字符串”或者“ 列 BETWEEN 值 1 AND 值 2”这样需要指定 2 个以上的参数，而 EXIST 的左侧并没有任何参数。因为 EXIST 是只有 1 个参数的谓词。 所以，EXIST 只需要在右侧书写 1 个参数，该参数通常都会是一个子查询。
例子：SELECT product_name, sale_price
FROM product AS p
WHERE EXISTS (SELECT 1 -- 这里可以书写适当的常数
FROM shopproduct AS sp
WHERE sp.shop_id = '000C'
AND sp.product_id = p.product_id);

Case表达式

什么是 CASE 表达式？

CASE 表达式是函数的一种。是 SQL 中数一数二的重要功能，有必要好好学习一下。
CASE 表达式是在区分情况时使用的，这种情况的区分在编程中通常称为（条件）分支。
CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种。由于搜索CASE表达式包含简单CASE表达式的全部功能。
语法：CASE WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
.
ELSE <表达式>
END
上述语句执行时，依次判断 when 表达式是否为真值，是则执行 THEN 后的语句，如果所有的 when 表达式均为假，则执行 ELSE 后的语句。 无论多么庞大的 CASE 表达式，最后也只会返回一个值。

CASE表达式的使用方法

1.ELSE 子句也可以省略不写，这时会被默认为 ELSE NULL。但为了防止有人漏读，还是希望大家能够显式地写出 ELSE 子句。 此外， CASE 表达式最后的“END”是不能省略的，请大家特别注意不要遗漏。忘记书写 END 会发生语法错误，这也是初学时最容易犯的错误。
2.聚合函数 + CASE WHEN 表达式即可实现列转行，行转列，套路类似，确定变前变后的样子。
3.总结：
当待转换列为数字时，可以使用SUM AVG MAX MIN等聚合函数；
当待转换列为文本时，可以使用MAX MIN等聚合函数

练习第一部分

第一题

create view ViewPractice5_1(product_name, sale_price, regist_date)
as select product_name, sale_price, regist_date from product
where sale_price >= 1000 and regist_date = 2009-09-20；

第二题

造成该问题的原因是：当向视图中插入数据时，同时也会向原表插入数据插入数据 ，而原表中存在多个字段不允许为空，所以无法插入 ，将这些不允许为空的字段修改为允许为空即可。

第三题

select product_id, product_name, product_type, sale_price, round((select avg(sale_price) from product), 4) as sale_price_average from product;

练习题第二部分

第一题

四则运算中含有 NULL 时（不进行特殊处理的情况下），运算结果是否必然会变为NULL ？
会

第二题

基础查询与排序

SELECT语句基础

1.从表中选取数据

SELECT语句
SELECT <列名>,
FROM <表名>;

2.从表中选取符合条件的数据

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

3.相关法则

1.星号（）代表全部列的意思。
2.SQL中可以随意使用换行符，不影响语句执行（但不可插入空行）。
3.设定汉语别名时需要使用双引号（"）括起来。
4.在SELECT语句中使用DISTINCT可以删除重复行。
5.注释是SQL语句中用来标识说明或者注意事项的部分。分为1行注释"-- "和多行注释两种"/ */"。

算术运算符和比较运算符

算术运算符

含义	运算符
加法	+
减法	-
乘法	*
除法	/

比较运算符

格式问题有的省掉了

运算符	含义
=	和 ~ 相等
<>	和 ~ 不相等

常用法则

1.SELECT子句中可以使用常数或者表达式。
2.使用比较运算符时一定要注意不等号和等号的位置。
3.字符串类型的数据原则上按照字典顺序进行排序，不能与数字的大小顺序混淆。
4.希望选取NULL记录时，需要在条件表达式中使用IS NULL运算符。希望选取不是NULL的记录时，需要在条件表达式中使用IS NOT NULL运算符。

逻辑运算符

NOT运算符

1.想要表示 不是…… 时，除了前文的<>运算符外，还存在另外一个表示否定、使用范围更广的运算符：NOT。
NOT不能单独使用，必须和其他查询条件组合起来使用，例：
SELECT product_name, product_type, sale_price
FROM product
WHERE NOT sale_price >= 1000;
2.值得注意的是，虽然通过 NOT 运算符否定一个条件可以得到相反查询条件的结果，但是其可读性明显不如显式指定查询条件，因此，不可滥用该运算符。

AND运算符和OR运算符

1.当希望同时使用多个查询条件时，可以使用AND或者OR运算符。
AND 相当于“并且”，类似数学中的取交集；
OR 相当于“或者”，类似数学中的取并集。
2.AND 运算符优先于 OR 运算符 ，想要优先执行OR运算，可以使用 括号
3.优先级表

真值表

1.复杂运算时该怎样理解？
当碰到条件较复杂的语句时，理解语句含义并不容易，这时可以采用真值表来梳理逻辑关系。
2.什么是真值？
本节介绍的三个运算符 NOT、AND 和 OR 称为逻辑运算符。这里所说的逻辑就是对真值进行操作的意思。真值就是值为真（TRUE）或假 （FALSE）其中之一的值。
例如，对于 sale_price >= 3000 这个查询条件来说，由于 product_name 列为 '运动 T 恤' 的记录的 sale_price 列的值是 2800，因此会返回假（FALSE），而 product_name 列为 '高压锅' 的记录的sale_price 列的值是 5000，所以返回真（TRUE）
3.AND 运算符两侧的真值都为真时返回真，除此之外都返回假。
OR 运算符两侧的真值只要有一个不为假就返回真，只有当其两侧的真值都为假时才返回假。
NOT运算符只是单纯的将真转换为假，将假转换为真。
4.含有NULL时的真值：NULL的真值结果既不为真，也不为假，因为并不知道这样一个值。
那该如何表示呢？（三值运算）
这时真值是除真假之外的第三种值——不确定（UNKNOWN）。一般的逻辑运算并不存在这第三种值。SQL 之外的语言也基本上只使用真和假这两种真值。与通常的逻辑运算被称为二值逻辑相对，只有 SQL 中的逻辑运算被称为三值逻辑。

练习题第一部分

练习题题目

第一题

select  product_name,  regist_date from product where regist_date > 2009-04-28;

第三题

select  product_name,  sale_price,  purchase_price where  sale_price -  purchase_price > 500;

对表进行聚合查询

聚合函数

SQL中用于汇总的函数叫做聚合函数。以下五个是最常用的聚合函数：
SUM：计算表中某数值列中的合计值
AVG：计算表中某数值列中的平均值
MAX：计算表中任意列中数据的最大值，包括文本类型和数字类型
MIN：计算表中任意列中数据的最小值，包括文本类型和数字类型
COUNT：计算表中的记录条数（行数）

聚合函数应用法则

1.COUNT 聚合函数运算结果与参数有关，COUNT() / COUNT(1) 得到包含 NULL 值的所有行，COUNT(<列名>) 得到不包含 NULL 值的所有行。
2.聚合函数不处理包含 NULL 值的行，但是 COUNT() 除外。
3.MAX / MIN 函数适用于文本类型和数字类型的列，而 SUM / AVG 函数仅适用于数字类型的列。
4.在聚合函数的参数中使用 DISTINCT 关键字，可以得到删除重复值的聚合结果。

对表进行分组

GROUP BY语句

SELECT <列名1>,<列名2>, <列名3>, ……
FROM <表名>
GROUP BY <列名1>, <列名2>, <列名3>, ……;
GROUP BY 子句就像切蛋糕那样将表进行了分组。在 GROUP BY 子句中指定的列称为聚合键或者分组列。

GROUP BY书写位置

GROUP BY的子句书写顺序有严格要求，不按要求会导致SQL无法正常执行，目前出现过的子句顺序为：
SELECT ➡️ 2. FROM ➡️ 3. WHERE ➡️ 4. GROUP BY

常见错误

1. 在聚合函数的SELECT子句中写了聚合键以外的列使用COUNT等聚合函数时，SELECT子句中如果出现列名，只能是GROUP BY子句中指定的列名（也就是聚合键）。
2. 在GROUP BY子句中使用列的别名SELECT子句中可以通过AS来指定别名，但在GROUP BY中不能使用别名。因为在DBMS中 ,SELECT子句在GROUP BY子句后执行。
3. 在WHERE中使用聚合函数原因是聚合函数的使用前提是结果集已经确定，而WHERE还处于确定结果集的过程中，所以相互矛盾会引发错误。 如果想指定条件，可以在SELECT，HAVING（下面马上会讲）以及ORDER BY子句中使用聚合函数。

为聚合结果指定条件

用 HAVING 得到特定分组

1.如何得到分组聚合结果的部分结果呢？这里 WHERE 不可行，因为，WHERE子句只能指定记录（行）的条件，而不能用来指定组的条件（例如，“数据行数为 2 行”或者“平均值为 500”等）。
2.可以在 GROUP BY 后使用 HAVING 子句。HAVING 的用法类似 WHERE。

3.值得注意的是：HAVING 子句必须与 GROUP BY 子句配合使用，且限定的是分组聚合结果，WHERE 子句是限定数据行（包括分组列），二者各司其职，不要混淆。

HAVING特点

HAVING子句用于对分组进行过滤，可以使用常数、聚合函数和GROUP BY中指定的列名（聚合键）。

对查询结果进行排序

介绍ORDER BY

1.代码示例SELECT <列名1>, <列名2>, <列名3>, ……
FROM <表名>
ORDER BY <排序基准列1> [ASC, DESC], <排序基准列2> [ASC, DESC], ……

2.如果有多列排序需求，只需在 ORDER BY 子句中依次书写排序列 + 排序参数即可
3.需要特别说明的是：由于 NULL 无法使用比较运算符进行比较，也就是说，无法与文本类型，数字类型，日期类型等进行比较，当排序列存在 NULL 值时，NULL 结果会展示在查询结果的开头或者末尾。

ORDER BY 子句中使用别名

SQL 在使用 HAVING 子句时 SELECT 语句的执行顺序为：
FROM → WHERE → GROUP BY → SELECT → HAVING → ORDER BY
当在 ORDER BY 子句中使用别名时，已经知道了 SELECT 子句设置的别名，但是在 GROUP BY 子句执行时还不知道别名的存在，所以在 ORDER BY 子句中可以使用别名，但是在GROUP BY中不能使用别名。

ORDER BY 遇上 NULL

在MySQL中，NULL 值被认为比任何 非NULL 值低，因此，当顺序为 ASC（升序）时，NULL 值出现在第一位，而当顺序为 DESC（降序）时，则排序在最后。
如果想指定存在 NULL 的行出现在首行或者末行，需要特殊处理。
一般有如下两种需求：
1.将 NULL 值排在末行，同时将所有 非NULL 值按升序排列。
对于数字或者日期类型，可以在排序字段前添加一个负号（minus）来得到反向排序。（-1、-2、-3....-∞）

对于字符型或者字符型数字，此方法不一定能得到期望的排序结果，可以使用 IS NULL 比较运算符。另外 ISNULL( ) 函数等同于使用 IS NULL 比较运算符。

2.将 NULL 值排在首行，同时将所有 非NULL 值按倒序排列。
对于数字或者日期类型，可以在排序字段前添加一个负号（minus）来实现。

作业第二部分

第一题

where改having

第二题

select product_type, sum(sale_price), sum(purchase_price) from product
group by product_type
having sum(sale_price) > 1.5 * sum(purchase_price);

集合运算

表的加减法

什么是集合运算

集合在数学领域表示“各种各样的事物的总和”, 在数据库领域表示记录的集合. 具体来说,表、视图和查询的执行结果都是记录的集合, 其中的元素为表或者查询结果中的每一行。
在标准 SQL 中, 分别对检索结果使用 UNION, INTERSECT, EXCEPT 来将检索结果进行并,交和差运算, 像UNION,INTERSECT, EXCEPT这种用来进行集合运算的运算符称为集合运算符。

表的加法--UNION

UNION入门

UNION 等集合运算符通常都会除去重复的记录。
上述查询是对不同的两张表进行求并集运算. 对于同一张表, 实际上也是可以进行求并集的。

UNION与OR

倘若要将两个不同的表中的结果合并在一起, 就不得不使用 UNION 了.。

包含重复行的集合运算 UNION ALL

SQL 语句的 UNION 会对两个查询的结果集进行合并和去重, 这种去重不仅会去掉两个结果集相互重复的, 还会去掉一个结果集中的重复行. 但在实践中有时候需要需要不去重的并集, 在 UNION 的结果中保留重复行的语法其实非常简单,只需要在 UNION 后面添加 ALL 关键字就可以了。

隐式数据类型转换

通常来说, 我们会把类型完全一致, 并且代表相同属性的列使用 UNION 合并到一起显示, 但有时候, 即使数据类型不完全相同, 也会通过隐式类型转换来将两个类型不同的列放在一列里显示, 例如字符串和数值类型

MySQL 8.0 不支持交运算INTERSECT

需要用 inner join 来求得交集

差集,补集与表的减法

求集合差集的减法运算和实数的减法运算有些不同, 当使用一个集合A减去另一个集合B的时候,对于只存在于集合B而不存在于集合A的元素, 采取直接忽略的策略,因此集合A和B做减法只是将集合A中也同时属于集合B的元素减掉。

MySQL 8.0 还不支持 EXCEPT 运算

MySQL 8.0 还不支持 表的减法运算符 EXCEPT. 不过, 借助NOT IN 谓词, 我们同样可以实现表的减法。

EXCEPT 与 NOT 谓词

使用 NOT IN 谓词, 基本上可以实现和SQL标准语法中的EXCEPT运算相同的效果。

INTERSECT 与 AND 谓词

对于同一个表的两个查询结果而言, 他们的交INTERSECT实际上可以等价地将两个查询的检索条件用AND谓词连接来实现。

对称差

两个集合A,B的对称差是指那些仅属于A或仅属于B的元素构成的集合。对称差也是个非常基础的运算，例如, 两个集合的交就可以看作是两个集合的并去掉两个集合的对称差。上述方法在其他数据库里也可以用来简单地实现表或查询结果的对称差运算: 首先使用UNION求两个表的并集，然后使用INTERSECT求两个表的交集，然后用并集减去交集，就得到了对称差。
但由于在MySQL 8.0 里, 由于两个表或查询结果的并不能直接求出来, 因此并不适合使用上述思路来求对称差. 好在还有差集运算可以使用. 从直观上就能看出来, 两个集合的对称差等于 A-B并上B-A, 因此实践中可以用这个思路来求对称差。

借助并集和差集迂回实现交集运算 INTERSECT

通过观察集合运算的文氏图, 我们发现, 两个集合的交可以看作是两个集合的并去掉两个集合的对称差。

连结(JOIN)

1.前一节我们学习了 UNION和INTERSECT 等集合运算, 这些集合运算的特征就是以行方向为单位进行操作. 通俗地说, 就是进行这些集合运算时, 会导致记录行数的增减. 使用 UNION 会增加记录行数,而使用 INTERSECT 或者 EXCEPT 会减少记录行数。
但这些运算不能改变列的变化, 虽然使用函数或者 CASE表达式等列运算, 可以增加列的数量, 但仍然只能从一张表中提供的基础信息列中获得一些"引申列", 本质上并不能提供更多的信息. 如果想要从多个表获取信息, 例如, 如果我们想要找出某个商店里的衣服类商品的名称,数量及价格等信息, 则必须分别从 shopproduct 表和 product 表获取信息。
2.连结图示
3.连结(JOIN)就是使用某种关联条件(一般是使用相等判断谓词"="), 将其他表中的列添加过来, 进行“添加列”的集合运算. 可以说,连结是 SQL 查询的核心操作, 掌握了连结, 能够从两张甚至多张表中获取列, 能够将过去使用关联子查询等过于复杂的查询简化为更加易读的形式, 以及进行一些更加复杂的查询。

内连结(INNER JOIN)

内连结的语法格式是:FROM <tb_1> INNER JOIN <tb_2> ON <condition(s)>

使用内连结从两个表获取信息

1.我们先来分别观察所涉及的表, product 表保存了商品编号,商品名称,商品种类等信息,这个表可以提供关于衣服种类的衣服的详细信息, 但是不能提供商店信息。
2.所以问题的关键是, 找出一个类似于"轴"或者"桥梁"的公共列, 将两张表用这个列连结起来。这就是连结运算所要作的事情。
例子：我们来对比一下上述两张表, 可以发现, 商品编号列是一个公共列, 因此很自然的事情就是用这个商品编号列来作为连接的“桥梁”，将product和shopproduct这两张表连接起来。
3.关于内连结,需要注意以下三点:
要点一: 进行连结时需要在 FROM子句中使用多张表。
要点二:必须使用 ON 子句来指定连结条件。
ON 子句是专门用来指定连结条件的, 我们在上述查询的 ON 之后指定两张表连结所使用的列以及比较条件, 基本上, 它能起到与 WHERE 相同的筛选作用
要点三: SELECT 子句中的列最好按照 表名.列名 的格式来使用。
当两张表的列除了用于关联的列之外, 没有名称相同的列的时候, 也可以不写表名, 但表名使得我们能够在今后的任何时间阅读查询代码的时候, 都能马上看出每一列来自于哪张表, 能够节省我们很多时间。

结合 WHERE 子句使用内连结

代码示例：SELECT SP.shop_id
,SP.shop_name
,SP.product_id
  ,P.product_name
  FROM shopproduct AS SP
 INNER JOIN product AS P
  ON SP.product_id = P.product_id
 WHERE SP.shop_name = '东京'
  AND P.product_type = '衣服';
我们首先给出上述查询的执行顺序:
FROM 子句->WHERE 子句->SELECT 子句
也就是说, 两张表是先按照连结列进行了连结, 得到了一张新表, 然后 WHERE 子句对这张新表的行按照两个条件进行了筛选, 最后, SELECT 子句选出了那些我们需要的列。

结合 GROUP BY 子句使用内连结

结合 GROUP BY 子句使用内连结, 需要根据分组列位于哪个表区别对待。
最简单的情形, 是在内连结之前就使用 GROUP BY 子句.
但是如果分组列和被聚合的列不在同一张表, 且二者都未被用于连结两张表, 则只能先连结, 再聚合。

内连结与关联子查询

仅仅从代码量上来看, 上述方法似乎比关联子查询更加复杂, 但这并不意味着这些代码更难理解. 通过上述分析, 很容易发现上述代码的逻辑实际上更符合我们的思路, 因此尽管看起来复杂, 但思路实际上更加清晰。

自然连结(NATURAL JOIN)

自然连结并不是区别于内连结和外连结的第三种连结, 它其实是内连结的一种特例--当两个表进行自然连结时, 会按照两个表中都包含的列名来进行等值内连结, 此时无需使用 ON 来指定连接条件。、
例子：SELECT * FROM shopproduct NATURAL JOIN product

使用连结求交集

我们在上一节表的加减法里知道, MySQL 8.0 里没有交集运算, 我们当时是通过并集和差集来实现求交集的. 现在学了连结, 让我们试试使用连结来实现求交集的运算。
练习题: 使用内连结求 product 表和 product2 表的交集。
SELECT P1.*
FROM product AS P1
INNER JOIN product2 AS P2
ON (P1.product_id = P2.product_id
AND P1.product_name = P2.product_name
AND P1.product_type = P2.product_type
AND P1.sale_price = P2.sale_price
AND P1.regist_date = P2.regist_date);

外连结(OUTER JOIN)

1.内连结会丢弃两张表中不满足 ON 条件的行,和内连结相对的就是外连结. 外连结会根据外连结的种类有选择地保留无法匹配到的行。
按照保留的行位于哪张表,外连结有三种形式: 左连结, 右连结和全外连结。
左连结会保存左表中无法按照 ON 子句匹配到的行, 此时对应右表的行均为缺失值; 右连结则会保存右表中无法按照 ON 子句匹配到的行, 此时对应左表的行均为缺失值; 而全外连结则会同时保存两个表中无法按照 ON子句匹配到的行, 相应的另一张表中的行用缺失值填充。
2.语法：-- 左连结
FROM <tb_1> LEFT OUTER JOIN <tb_2> ON <condition(s)>
-- 右连结
FROM <tb_1> RIGHT OUTER JOIN <tb_2> ON <condition(s)>
-- 全外连结
FROM <tb_1> FULL OUTER JOIN <tb_2> ON <condition(s)>

使用左连结从两个表获取信息

外连结要点 1：选取出单张表中全部的信息。
外连结要点 2：使用 LEFT、RIGHT 来指定主表。使用 LEFT 时 FROM 子句中写在左侧的表是主表，使用 RIGHT 时右侧的表是主表。

结合 WHERE 子句使用左连结

在结合WHERE子句使用外连结时，由于外连结的结果很可能与内连结的结果不一样，会包含那些主表中无法匹配到的行，并用缺失值填写另一表中的列，由于这些行的存在，因此在外连结时使用WHERE子句，情况会有些不一样。
练习题：代码第一块
SELECT P.product_id
,P.product_name
,P.sale_price
  ,SP.shop_id
,SP.shop_name
,SP.quantity
  FROM product AS P
  LEFT OUTER JOIN shopproduct AS SP
  ON SP.product_id = P.product_id
 WHERE quantity< 50;

代码第二块：
SELECT P.product_id
,P.product_name
,P.sale_price
,SP.shop_id
,SP.shop_name
,SP.quantity
FROM product AS P
LEFT OUTER JOIN-- 先筛选quantity<50的商品
(SELECT *
FROM shopproduct
WHERE quantity < 50 ) AS SP
ON SP.product_id = P.product_id;

第二块代码有把null选进来
代码结果图

多表连结

通常连结只涉及 2 张表，但有时也会出现必须同时连结 3 张以上的表的情况，原则上连结表的数量并没有限制。
例子：使用内连接找出每个商店都有那些商品，每种商品的库存总量分别是多少。一张是哪些商店，一张补上哪些商品，一张补上哪些库存总量。
由于 product 表和 shopproduct 表已经进行了连结，因此就无需再对 product 表和 Inventoryproduct 表进行连结了。

多表进行外连结

正如之前所学发现的，外连结一般能比内连结有更多的行，从而能够比内连结给出更多关于主表的信息，多表连结的时候使用外连结也有同样的作用。
语法类似。

ON 子句进阶--非等值连结

在刚开始介绍连结的时候，书上提到过，除了使用相等判断的等值连结，也可以使用比较运算符来进行连接。实际上，包括比较运算符(<、<=、>、>=、BETWEEN)和谓词运算(LIKE、IN、NOT 等等)在内的所有的逻辑运算都可以放在 ON 子句内作为连结条件。

非等值自左连结(SELF JOIN)

使用非等值自左连结实现排名。
SELECT product_id
,product_name
,sale_price
,COUNT(p2_id) AS my_rank
FROM (--使用自左连结对每种商品找出价格不低于它的商品
  SELECT P1.product_id
  ,P1.product_name
  ,P1.sale_price
  ,P2.product_id AS P2_id
  ,P2.product_name AS P2_name
  ,P2.sale_price AS P2_price 
  FROM product AS P1
   LEFT OUTER JOIN product AS P2 
 ON P1.sale_price <= P2.sale_price 
) AS X
GROUP BY product_id, product_name, sale_price
ORDER BY my_rank; 
本代码重点：P1.sale_price <= P2.sale_price 
注 1：COUNT 函数的参数是列名时，会忽略该列中的缺失值，参数为 * 时则不忽略缺失值。
注 2：上述排名方案存在一些问题--如果两个商品的价格相等，则会导致两个商品的排名错误。
注 3：实际上，进行排名有专门的函数，这是 MySQL 8.0 新增加的窗口函数中的一种(窗口函数将在下一章学习)，但在较低版本的 MySQL 中只能使用上述自左连结的思路。

交叉连结—— CROSS JOIN(笛卡尔积)

在连结去掉 ON 子句，就是所谓的交叉连结(CROSS JOIN)，交叉连结又叫笛卡尔积，后者是一个数学术语。两个集合做笛卡尔积，就是使用集合 A 中的每一个元素与集合 B 中的每一个元素组成一个有序的组合。

两个集合做笛卡尔积，就是使用集合 A 中的每一个元素与集合 B 中的每一个元素组成一个有序的组合。数据库表(或者子查询)的并，交和差都是在纵向上对表进行扩张或筛选限制等运算的， 这要求表的列数及对应位置的列的数据类型"相容"，因此这些运算并不会增加新的列，而交叉连接(笛卡尔积)则是在横向上对表进行扩张，即增加新的列。

交叉连结的语法有如下几种形式：
-- 1.使用关键字 CROSS JOIN 显式地进行交叉连结
SELECT SP.shop_id
,SP.shop_name
,SP.product_id
,P.product_name
,P.sale_price
FROM shopproduct AS SP
CROSS JOIN product AS P;
--2.使用逗号分隔两个表,并省略 ON 子句
SELECT SP.shop_id
,SP.shop_name
,SP.product_id
,P.product_name
,P.sale_price
FROM shopproduct AS SP , product AS P;
本例中，因为 shopproduct 表存在 13 条记录，product 表存在 8 条记录，所以结果中就包含了 13 × 8 = 104 条记录。
交叉连结没有应用到实际业务之中的原因有两个。一是其结果没有实用价值，二是由于其结果行数太多，需要花费大量的运算时间和高性能设备的支持。

代码练习

Section A

所有的建表语句
全部的建表语句类似，仅保留练习一的
-- 练习一：各部门工资最高的员工
DROP TABLE if EXISTS Employee;
CREATE TABLE Employee
(id INT,
name VARCHAR(20),
salary INT,
departmentid INT,
PRIMARY KEY (id));

INSERT INTO Employee VALUES(1,'Joe',70000,1);
INSERT INTO Employee VALUES(2,'Henry',80000,2);
INSERT INTO Employee VALUES(3,'Sam',60000,2);
INSERT INTO Employee VALUES(4,'Max',90000,1);

DROP TABLE if EXISTS Department;
CREATE TABLE Department
(id INT,
name VARCHAR(20),
PRIMARY KEY (id));

INSERT INTO Department VALUES(1,'IT');
INSERT INTO Department VALUES(2,'Sales');

练习一: 各部门工资最高的员工（难度：中等）

select Department, Employee, salary from (
select d.name Department, e.name employee, e.salary,
rank() over(partition by e.DepartmentId order by e.salary desc) rk
from employee e left join Department d
on e.DepartmentId = d.id
)temp
where temp.rk = 1

练习二: 换座位（难度：中等）

select id,
(case when id % 2 = 0 then f
when id % 2 = 1 && b is not null then b else student end
)student
from (
select id, student, lag(student, 1) over(order by id) f, lead(student, 1) over(order by id) b from seat
)temp

练习三: 分数排名（难度：中等）

select class, score_avg,
rank() over(order by score_avg desc) rank1,
dense_rank() over(order by score_avg desc) rank2,
row_number() over(order by score_avg desc) rank3
from score
order by class;

练习四：连续出现的数字（难度：中等）

select num ConsecutiveNums from (
select num, lag(num, 1) over(order by id) num2, lead(num, 1) over(order by id) num3
from logs
)temp
where num = temp.num2 = temp.num3

练习五：树节点 （难度：中等）

select id,
(case when p_id is null then "Root"
when id not in (select ifnull(p_id, 0) from tree) then "leaf" --不作为父节点
else "Inner" end) Type
from tree

练习六：至少有五名直接下属的经理 （难度：中等）

select name from employee2
where id in (
select managerid from employee2 group by ManagerId having count(*) >= 5
)

练习七：查询回答率最高的问题 （难度：中等）

select question_id survey_log
from (
select
question_id,
sum(if(action = 'answer', 1, 0)) as AnswerCnt,
sum(if(action = 'show', 1, 0)) as ShowCnt
from
survey_log
group by question_id
) as tbl
order by (AnswerCnt / ShowCnt) desc
limit 1

练习八：各部门前3高工资的员工（难度：中等）

SELECT T.request_at AS Day,
ROUND(
SUM(
IF(T.STATUS = 'completed',0,1)
)
/
COUNT(T.STATUS),
2
) AS Cancellation Rate
FROM trips AS T
WHERE
T.Client_Id NOT IN (
SELECT users_id
FROM users
WHERE banned = 'Yes'
)
AND
T.Driver_Id NOT IN (
SELECT users_id
FROM users
WHERE banned = 'Yes'
)
AND T.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY T.request_at

Section B

行转列练习

select name,
sum(case when subject = 'chinese' then score else null end) chinese
sum(case when subject = 'math' then score else null end) math
sum(case when subject = 'english' then score else null end) english
from score
group by name

列转行练习

select name, 'chinese' as subject, chinese as score from score2
union all
select name, 'math' as subject, math as score from score2
union all
select name, 'english' as subject, english as score from score2

谁是明星带货主播?

SELECT COUNT(DISTINCT anchor_name)
FROM (
SELECT * FROM (
SELECT t1.anchor_name, t1.date, t1.sales / t2.sum_sales as ratio
FROM anchor_sales t1
JOIN (SELECT date, sum(sales) AS sum_sales FROM anchor_sales GROUP BY date) t2
ON t1.date = t2.date ) t3
WHERE t3.ratio >= 0.9) t4;

行转列比赛数据练习

select cdate as '比赛日期'
sum(case when result = '胜' then 1 else 0 end) as '胜'
sum(case when result = '负' then 1 else 0 end) as '负'
from row_col
group by c_date

连续登陆

SQL高级处理

窗口函数

窗口函数概念及基本的使用方法

窗口函数也称为OLAP函数。OLAP 是 OnLine AnalyticalProcessing 的简称，意思是对数据库数据进行实时分析处理。
为了便于理解，称之为 窗口函数。常规的SELECT语句都是对整张表进行查询，而窗口函数可以让我们有选择的去某一部分数据进行汇总、计算和排序。

窗口函数的通用形式：
<窗口函数> OVER ([ PARTITION BY <列名> ] [ ORDER BY <排序用列名> ])  
[ ]中的内容可以省略。
窗口函数最关键的是搞明白关键字 PARTITON BY 和 ORDER BY 的作用。

PARTITON BY 子句 可选参数，指示如何将查询行划分为组，类似于 GROUP BY 子句的分组功能，但是 PARTITION BY 子句并不具备 GROUP BY 子句的汇总功能，并不会改变原始表中记录的行数。(暂时取出来)
ORDER BY 子句 可选参数，指示如何对每个分区中的行进行排序，即决定窗口内，是按那种规则(字段)来排序的。注意在窗口内。
注意：虽然 PARTITON BY 子句 和 ORDER BY 子句 都是可选参数，但是两个参数不能同时没有（最少二选一）。不然， <窗口函数> OVER( ) 这种用法没用实际意义（窗口由所有查询行组成，窗口函数使用所有行计算结果）。

例子：select product_name, product_type, sale_price, rank() over (partition by product_type order by sale_price) as ranking
from product;
PARTITION BY 能够设定窗口对象范围。本例中，为了按照商品种类进行排序，我们指定了product_type。即一个商品种类就是一个小的"窗口"。
ORDER BY 能够指定按照哪一列、何种顺序进行排序。为了按照销售单价的升序进行排列，我们指定了sale_price。此外，窗口函数中的ORDER BY与SELECT语句末尾的ORDER BY一样，可以通过关键字ASC/DESC来指定升序/降序。省略该关键字时会默认按照ASC，也就是升序进行排序。

窗口函数种类

大致来说，窗口函数可以分为两类。
一是 将SUM、MAX、MIN等聚合函数用在窗口函数中
二是 RANK、DENSE_RANK等排序用的专用窗口函数

（排名）专用窗口函数

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

代码合体示例：
SELECT product_name
,product_type
,sale_price
,RANK() OVER (ORDER BY sale_price) AS ranking
,DENSE_RANK() OVER (ORDER BY sale_price) AS dense_ranking
,ROW_NUMBER() OVER (ORDER BY sale_price) AS row_num
FROM product;

聚合函数在窗口函数上的使用

聚合函数在窗口函数中的使用方法和之前的专用窗口函数一样，只是出来的结果是一个累计的聚合函数值。
代码示例：
SELECT product_id
,product_name
,sale_price
,SUM(sale_price) OVER (ORDER BY product_id) AS current_sum
,AVG(sale_price) OVER (ORDER BY product_id) AS current_avg  
FROM product;  

窗口函数的的应用 - 计算移动平均

在上面提到，聚合函数在窗口函数使用时，计算的是累积到当前行的所有的数据的聚合。 实际上，还可以指定更加详细的汇总范围。该汇总范围称为框架 (frame)。

<窗口函数> OVER (ORDER BY <排序用列名>
ROWS n PRECEDING )  

<窗口函数> OVER (ORDER BY <排序用列名>
ROWS BETWEEN n PRECEDING AND n FOLLOWING)

PRECEDING（“之前”）， 将框架指定为 “截止到之前 n 行”，加上自身行
FOLLOWING（“之后”）， 将框架指定为 “截止到之后 n 行”，加上自身行
执行案例

窗口函数适用范围和注意事项

1.原则上，窗口函数只能在SELECT子句中使用。
2.窗口函数OVER 中的ORDER BY 子句并不会影响最终结果的排序。其只是用来决定窗口函数按何种顺序计算。

GROUPING运算符

ROLLUP - 计算合计及小计

常规的GROUP BY 只能得到每个分类的小计，有时候还需要计算分类的合计，可以用 ROLLUP关键字。
例题代码

存储过程和函数

基本介绍

基本语法：
[delimiter //]($$，可以是其他特殊字符）
CREATE
[DEFINER = user]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...]
[BEGIN]
routine_body
[END//]($$，可以是其他特殊字符）

参数介绍

存储过程和函数的参数有三类，分别是：IN，OUT，INOUT
应用示例

预处理声明 PREPARE Statement

当MySQL使用不同的 productCode 值执行此查询时，它不必完全解析查询。因此，这有助于MySQL更快地执行查询，特别是当MySQL多次执行相同的查询时。productcode
由于预准备语句使用占位符 （），这有助于避免 SQL 注入的许多变体，从而使应用程序更安全。

基本语法：PREPARE stmt_name FROM preparable_stmt

使用步骤

MySQL PREPARE Statement 使用步骤如下：
1.PREPARE – 准备需要执行的语句预处理声明。
2.EXECUTE – 执行预处理声明。
3.DEALLOCATE PREPARE – 释放预处理声明。
预处理声明的使用过程图

使用示例

这里使用 shop 中的 product 表进行演示。