SQL（Structured Query Language 结构化查询语言）是数据库操作的标准
概念
数据库是将大量数据保存起来，通过计算机加工而成的可以进行高效访问的数据集合。该数据集合称为数据库（Database，DB）。用来管理数据库的计算机系统称为数据库管理系统（Database Management System，DBMS）。
DBMS的种类
DBMS 主要通过数据的保存格式（数据库的种类）来进行分类，现阶段主要有以下 5 种类型.
- 层次数据库（Hierarchical Database，HDB）
- 关系数据库（Relational Database，RDB）
- Oracle Database：甲骨文公司的RDBMS
- SQL Server：微软公司的RDBMS
- DB2：IBM公司的RDBMS
- PostgreSQL：开源的RDBMS
- MySQL：开源的RDBMS
如上是5种具有代表性的RDBMS，其特点是由行和列组成的二维表来管理数据，这种类型的 DBMS 称为关系数据库管理系统（Relational Database Management System，RDBMS）。
- 面向对象数据库（Object Oriented Database，OODB）
- XML数据库（XML Database，XMLDB）
- 键值存储系统（Key-Value Store，KVS），举例：MongoDB
RDBMS的常见系统结构
最常见的系统结构就是客户端 / 服务器类型（C/S类型）
关系型数据库
数据库中存储的表结构类似于excel中的行和列，在数据库中，行称为记录，它相当于一条记录，列称为字段，它代表了表中存储的数据项目。
行和列交汇的地方称为单元格，一个单元格中只能输入一条记录。
SQL是为操作数据库而开发的语言。国际标准化组织（ISO）为 SQL 制定了相应的标准，以此为基准的SQL 称为标准 SQL。
完全基于标准 SQL 的 RDBMS 很少，通常需要根据不同的 RDBMS 来编写特定的 SQL 语句。
语句种类
根据对 RDBMS 赋予的指令种类的不同，SQL 语句可以分为以下三类.
DDL ：DDL（Data Definition Language，数据定义语言） 用来创建或者删除存储数据用的数据库以及数据库中的表等对象。DDL 包含以下几种指令。
- CREATE ： 创建数据库和表等对象
- DROP ： 删除数据库和表等对象
- ALTER ： 修改数据库和表等对象的结构
DML :DML（Data Manipulation Language，数据操纵语言） 用来查询或者变更表中的记录。DML 包含以下几种指令。
- SELECT ：查询表中的数据
- INSERT ：向表中插入新数据
- UPDATE ：更新表中的数据
- DELETE ：删除表中的数据
DCL ：DCL（Data Control Language，数据控制语言） 用来确认或者取消对数据库中的数据进行的变更。除此之外，还可以对 RDBMS 的用户是否有权限操作数据库中的对象（数据库表等）进行设定。DCL 包含以下几种指令。
- COMMIT ： 确认对数据库中的数据进行的变更
- ROLLBACK ： 取消对数据库中的数据进行的变更
- GRANT ： 赋予用户操作权限
- REVOKE ： 取消用户的操作权限
实际使用的 SQL 语句当中有 90% 属于 DML
书写规则
- SQL语句要以分号（ ; ）结尾
- SQL 不区分关键字的大小写，但是插入到表中的数据是区分大小写的
- win 系统默认不区分表名及字段名的大小写
- linux / mac 默认严格区分表名及字段名的大小写
- 常数的书写方式是固定的
‘abc’, 1234, ‘26 Jan 2010’, ‘10/01/26’, ‘2010-01-26’……
- 单词需要用半角空格或者换行来分隔
SQL 语句的单词之间需使用半角空格或换行符来进行分隔，且不能使用全角空格作为单词的分隔符，否则会发生错误，出现无法预期的结果。
数据库的创建（ CREATE DATABASE 语句）
CREATE DATABASE < 数据库名称 > ;
CREATE DATABASE shop;表的创建（ CREATE TABLE 语句）
CREATE TABLE < 表名 >
( < 列名 1> < 数据类型 > < 该列所需约束 > ,
< 列名 2> < 数据类型 > < 该列所需约束 > ,
< 列名 3> < 数据类型 > < 该列所需约束 > ,
< 列名 4> < 数据类型 > < 该列所需约束 > ,
.
.
.
< 该表的约束 1> , < 该表的约束 2> ,……);
CREATE TABLE product
(product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INTEGER ,
purchase_price INTEGER ,
regist_date DATE ,
PRIMARY KEY (product_id));一个表中，将不同记录区分开的字段称为主键
有时候由多个字段一起来区分记录，称为联合主键
通过另一个表的主键联合两张表的字段称为外键，所以外键一定是某个表的主键
数据类型的指定
数据库创建的表，所有的列都必须指定数据类型，每一列都不能存储与该列数据类型不符的数据。
四种最基本的数据类型
- INTEGER 型：用来指定存储整数的列的数据类型（数字型），不能存储小数。
- CHAR 型：用来存储定长字符串，当列中存储的字符串长度达不到最大长度的时候，使用半角空格进行补足，由于会浪费存储空间，所以一般不使用。
- VARCHAR 型：用来存储可变长度字符串，定长字符串在字符数未达到最大长度时会用半角空格补足，但可变长字符串不同，即使字符数未达到最大长度，也不会用半角空格补足。
- DATE 型：用来指定存储日期（年月日）的列的数据类型（日期型）。
约束的设置
- 约束是除了数据类型之外，对列中存储的数据进行限制或者追加条件的功能。
NOT NULL是非空约束，即该列必须输入数据。PRIMARY KEY是主键约束，代表该列是唯一值，可以通过该列取出特定的行的数据。
表的删除和更新
- 删除表的语法：
需要特别注意的是，删除的表是无法恢复的，只能重新插入，请执行删除操作时要特别谨慎。DROP TABLE < 表名 > ; DROP TABLE product;
- 添加列的 ALTER TABLE 语句
ALTER TABLE < 表名 > ADD COLUMN < 列的定义 >;- 添加一列可以存储100位的可变长字符串的 product_name_pinyin 列
ALTER TABLE product ADD COLUMN product_name_pinyin VARCHAR(100);- 删除列的 ALTER TABLE 语句
ALTER TABLE < 表名 > DROP COLUMN < 列名 >;- 删除 product_name_pinyin 列
ALTER TABLE product DROP COLUMN product_name_pinyin;- 删除表中特定的行（语法）
-- 一定注意添加 WHERE 条件，否则将会删除所有的数据
DELETE FROM product WHERE COLUMN_NAME='XXX';ALTER TABLE 语句和 DROP TABLE 语句一样，执行之后无法恢复。误添加的列可以通过 ALTER TABLE 语句删除，或者将表全部删除之后重新再创建。
- 清空表内容
TRUNCATE TABLE TABLE_NAME;优点：相比drop / delete，truncate用来清除数据时，速度最快。
- 数据的更新
基本语法：
UPDATE <表名>
SET <列名> = <表达式> [, <列名2>=<表达式2>...]
WHERE <条件> -- 可选，非常重要
ORDER BY 子句 --可选
LIMIT 子句; --可选使用 update 时要注意添加 where 条件，否则将会将所有的行按照语句修改
-- 修改所有的注册时间
UPDATE product
SET regist_date = '2009-10-10';
-- 仅修改部分商品的单价
UPDATE product
SET sale_price = sale_price * 10
WHERE product_type = '厨房用具'; 使用 UPDATE 也可以将列更新为 NULL（该更新俗称为NULL清空）。此时只需要将赋值表达式右边的值直接写为 NULL 即可。
-- 将商品编号为0008的数据（圆珠笔）的登记日期更新为NULL
UPDATE product
SET regist_date = NULL
WHERE product_id = '0008'; 和 INSERT 语句一样， UPDATE 语句也可以将 NULL 作为一个值来使用。
但是，只有未设置 NOT NULL 约束和主键约束的列才可以清空为NULL。如果将设置了上述约束的列更新为 NULL，就会出错，这点与INSERT 语句相同。
多列更新
UPDATE 语句的 SET 子句支持同时将多个列作为更新对象。
-- 基础写法，一条UPDATE语句只更新一列
UPDATE product
SET sale_price = sale_price * 10
WHERE product_type = '厨房用具';
UPDATE product
SET purchase_price = purchase_price / 2
WHERE product_type = '厨房用具'; 该写法可以得到正确结果，但是代码较为繁琐。可以采用合并的方法来简化代码。
-- 合并后的写法
UPDATE product
SET sale_price = sale_price * 10,
purchase_price = purchase_price / 2
WHERE product_type = '厨房用具'; 需要明确的是，SET 子句中的列不仅可以是两列，还可以是三列或者更多。
插入数据
INSERT INTO <表名> (列1, 列2, 列3, ……) VALUES (值1, 值2, 值3, ……); 对表进行全列 INSERT 时，可以省略表名后的列清单。这时 VALUES子句的值会默认按照从左到右的顺序赋给每一列。
CREATE TABLE productins
(product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INTEGER DEFAULT 0,
purchase_price INTEGER ,
regist_date DATE ,
PRIMARY KEY (product_id));
-- 包含列清单
INSERT INTO productins (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
-- 省略列清单
INSERT INTO productins VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15'); 原则上，执行一次 INSERT 语句会插入一行数据。插入多行时，通常需要循环执行相应次数的 INSERT 语句。其实很多 RDBMS 都支持一次插入多行数据
-- 通常的INSERT
INSERT INTO productins VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO productins VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO productins VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
-- 多行INSERT （ DB2、SQL、SQL Server、 PostgreSQL 和 MySQL多行插入）
INSERT INTO productins VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11'),
('0003', '运动T恤', '衣服', 4000, 2800, NULL),
('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
-- Oracle中的多行INSERT
INSERT ALL INTO productins VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11')
INTO productins VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL)
INTO productins VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20')
SELECT * FROM DUAL;
-- DUAL是Oracle特有（安装时的必选项）的一种临时表A。因此“SELECT *FROM DUAL” 部分也只是临时性的，并没有实际意义。 INSERT 语句中想给某一列赋予 NULL 值时，可以直接在 VALUES子句的值清单中写入 NULL。想要插入 NULL 的列一定不能设置 NOT NULL 约束。
INSERT INTO productins (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20'); 还可以向表中插入默认值（初始值）。可以通过在创建表的CREATE TABLE 语句中设置DEFAULT约束来设定默认值。
CREATE TABLE productins
(product_id CHAR(4) NOT NULL,
（略）
sale_price INTEGER
（略） DEFAULT 0, -- 销售单价的默认值设定为0;
PRIMARY KEY (product_id)); 可以使用INSERT … SELECT 语句从其他表复制数据。
-- 将商品表中的数据复制到商品复制表中
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; - DML ：插入数据
STARTTRANSACTION;
INSERT INTO product VALUES('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
INSERT INTO product VALUES('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO product VALUES('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO product VALUES('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO product VALUES('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO product VALUES('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO product VALUES('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO product VALUES('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');
COMMIT;索引
索引的作用
- MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
- 打个比方，如果合理的设计且使用索引的 MySQL 是一辆兰博基尼的话，那么没有设计和使用索引的 MySQL 就是一个人力三轮车。
- 拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。
- 索引创建了一种有序的数据结构，采用二分法搜索数据时，1000多万的数据只要搜索23次，其效率是非常高效的。
创建索引
创建表时可以直接创建索引，语法如下：
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
); 也可以使用如下语句创建：
-- 方法1
CREATE INDEX indexName ON table_name (column_name)
-- 方法2
ALTER table tableName ADD INDEX indexName(columnName)索引分类
- 主键索引
建立在主键上的索引被称为主键索引，一张数据表只能有一个主键索引，索引列值不允许有空值，通常在创建表时一起创建。
- 唯一索引
建立在UNIQUE字段上的索引被称为唯一索引，一张表可以有多个唯一索引，索引列值允许为空，列值中出现多个空值不会发生重复冲突。
- 普通索引
建立在普通字段上的索引被称为普通索引。
- 前缀索引
前缀索引是指对字符类型字段的前几个字符或对二进制类型字段的前几个bytes建立的索引，而不是在整个字段上建索引。前缀索引可以建立在类型为char、varchar、binary、varbinary的列上，可以大大减少索引占用的存储空间，也能提升索引的查询效率。
- 全文索引
利用“分词技术”实现在长文本中搜索关键字的一种索引。
语法：SELECT * FROM article WHERE MATCH (col1，col2，...) AGAINST (expr [ search _ modifier ])
1、MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
2、MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
3、只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
4、如果可能，请尽量先创建表并插入所有数据后再创建全文索引，而不要在创建表时就直接创建全文索引，因为前者比后者的全文索引效率要高。
- 单列索引
建立在单个列上的索引被称为单列索引。
- 联合索引（复合索引、多列索引）
建立在多个列上的索引被称为联合索引，又叫复合索引、组合索引。
基础查询与排序
选取数据
SELECT语句
从表中选取数据时需要使用SELECT语句，也就是只从表中选出（SELECT）必要数据的意思。通过SELECT语句查询并选取出必要数据的过程称为匹配查询或查询（query）。
基本SELECT语句包含了SELECT和FROM两个子句（clause）。示例如下：
SELECT <列名>,
FROM <表名>;其中，SELECT子句中列举了希望从表中查询出的列的名称，而FROM子句则指定了选取出数据的表的名称。
WHERE语句
当不需要取出全部数据，而是选取出满足“商品种类为衣服”“销售单价在1000日元以上”等某些条件的数据时，使用WHERE语句。
SELECT 语句通过WHERE子句来指定查询数据的条件。在WHERE 子句中可以指定“某一列的值和这个字符串相等”或者“某一列的值大于这个数字”等条件。执行含有这些条件的SELECT语句，就可以查询出只符合该条件的记录了。
SELECT <列名>, ……
FROM <表名>
WHERE <条件表达式>;比较下面两者输出结果的不同：
-- 用来选取product type列为衣服的记录的SELECT语句
SELECT product_name, product_type
FROM product
WHERE product_type = '衣服';
-- 也可以选取出不是查询条件的列（条件列与输出列不同）
SELECT product_name
FROM product
WHERE product_type = '衣服';相关法则
- 星号（*）代表全部列的意思。
- SQL中可以随意使用换行符，不影响语句执行（但不可插入空行）。
- 设定汉语别名时需要使用双引号（”）括起来。
- 在SELECT语句中使用DISTINCT可以删除重复行。
- 注释是SQL语句中用来标识说明或者注意事项的部分。分为1行注释”– “和多行注释两种”/* */“。
-- 想要查询出全部列时，可以使用代表所有列的星号（*）。 SELECT * FROM <表名>； -- SQL语句可以使用AS关键字为列设定别名（用中文时需要双引号（“”））。 SELECT product_id As id, product_name As name, purchase_price AS "进货单价" FROM product; -- 使用DISTINCT删除product_type列中重复的数据 SELECT DISTINCT product_type FROM product;
算术运算符和比较运算符
算术运算符
SQL语句中可以使用的四则运算的主要运算符如下：
| 含义 | 运算符 |
|---|---|
| 加法 | + |
| 减法 | - |
| 乘法 | * |
| 除法 | / |
比较运算符
-- 选取出sale_price列为500的记录
SELECT product_name, product_type
FROM product
WHERE sale_price = 500;SQL常见比较运算符如下：
| 运算符 | 含义 |
| :—– | :———- |
| = | 和 ~ 相等 |
| <> | 和 ~ 不相等 |
| >= | 大于等于 ~ |
| > | 大于 ~ |
| <= | 小于等于 ~ |
| < | 小于 ~ |
常用法则
- SELECT子句中可以使用常数或者表达式。
- 使用比较运算符时一定要注意不等号和等号的位置。
- 字符串类型的数据原则上按照字典顺序进行排序，不能与数字的大小顺序混淆。
- 希望选取NULL记录时，需要在条件表达式中使用IS NULL运算符。希望选取不是NULL的记录时，需要在条件表达式中使用IS NOT NULL运算符。
-- SQL语句中也可以使用运算表达式
SELECT product_name, sale_price, sale_price * 2 AS "sale_price x2"
FROM product;
-- WHERE子句的条件表达式中也可以使用计算表达式
SELECT product_name, sale_price, purchase_price
FROM product
WHERE sale_price - purchase_price >= 500;
/* 对字符串使用不等号
首先创建chars并插入数据
选取出大于‘2’的SELECT语句*/
-- DDL：创建表
CREATE TABLE chars
（chr CHAR（3）NOT NULL,
PRIMARY KEY（chr））;
-- 选取出大于'2'的数据的SELECT语句('2'为字符串)
SELECT chr
FROM chars
WHERE chr > '2';
-- 选取NULL的记录
SELECT product_name, purchase_price
FROM product
WHERE purchase_price IS NULL;
-- 选取不为NULL的记录
SELECT product_name, purchase_price
FROM product
WHERE purchase_price IS NOT NULL;逻辑运算符
NOT运算符
想要表示 不是…… 时，除了前文的<>运算符外，还存在另外一个表示否定、使用范围更广的运算符：NOT。
NOT不能单独使用，必须和其他查询条件组合起来使用。如下例：
选取出销售单价大于等于1000日元的记录
SELECT product_name, product_type, sale_price
FROM product
WHERE sale_price >= 1000;向上述 SELECT 语句的查询条件中添加NOT运算符
SELECT product_name, product_type, sale_price
FROM product
WHERE NOT sale_price >= 1000;可以看出，通过否定销售单价大于等于 1000 日元 （sale_price >= 1000） 这个查询条件，选取出了销售单价小于 1000 日元的商品。也就是说 NOT sale_price >= 1000 与 sale_price < 1000 是等价的。
值得注意的是，虽然通过 NOT 运算符否定一个条件可以得到相反查询条件的结果，但是其可读性明显不如显式指定查询条件，因此，不可滥用该运算符。
AND运算符和OR运算符
当希望同时使用多个查询条件时，可以使用AND或者OR运算符。
AND 相当于“并且”，类似数学中的取交集；
OR 相当于“或者”，类似数学中的取并集。
通过括号优先处理
如果要查找这样一个商品，该怎么处理？
“商品种类为办公用品”并且“登记日期是 2009 年 9 月 11 日或者 2009 年 9 月 20 日”
理想结果为“打孔器”，但当你输入以下信息时，会得到错误结果
-- 将查询条件原封不动地写入条件表达式，会得到错误结果
SELECT product_name, product_type, regist_date
FROM product
WHERE product_type = '办公用品'
AND regist_date = '2009-09-11'
OR regist_date = '2009-09-20';错误的原因是 AND 运算符优先于 OR 运算符 ，想要优先执行OR运算，可以使用 括号 ：
-- 通过使用括号让OR运算符先于AND运算符执行
SELECT product_name, product_type, regist_date
FROM product
WHERE product_type = '办公用品'
AND ( regist_date = '2009-09-11'
OR regist_date = '2009-09-20');含有NULL时的真值
- NULL的真值结果既不为真，也不为假，因为并不知道这样一个值。
- 这时真值是除真假之外的第三种值——不确定（UNKNOWN）。一般的逻辑运算并不存在这第三种值。SQL 之外的语言也基本上只使用真和假这两种真值。与通常的逻辑运算被称为二值逻辑相对，只有 SQL 中的逻辑运算被称为三值逻辑。
- AND和OR的左右只要有一个值为UNKNOWN，结果就为UNKNOWN。
聚合查询
聚合函数
SQL中用于汇总的函数叫做聚合函数。以下五个是最常用的聚合函数：
- SUM：计算表中某数值列中的合计值
- AVG：计算表中某数值列中的平均值
- MAX：计算表中任意列中数据的最大值，包括文本类型和数字类型
- MIN：计算表中任意列中数据的最小值，包括文本类型和数字类型
- COUNT：计算表中的记录条数（行数）
请使用 shop 数据库，执行以下 SQL 查询语句，理解并掌握聚合函数的常规用法：
-- 计算销售单价和进货单价的合计值
SELECT SUM(sale_price), SUM(purchase_price)
FROM product;
-- 计算销售单价和进货单价的平均值
SELECT AVG(sale_price), AVG(purchase_price)
FROM product;
-- 计算销售单价的最大值和最小值
SELECT MAX(sale_price), MIN(sale_price)
FROM product;
-- MAX和MIN也可用于非数值型数据
SELECT MAX(regist_date), MIN(regist_date)
FROM product;
-- 计算全部数据的行数（包含 NULL 所在行）
SELECT COUNT(*)
FROM product;
-- 计算 NULL 以外数据的行数
SELECT COUNT(purchase_price)
FROM product;DISTINCT
当对整表进行聚合运算时，表中可能存在多行相同的数据，比如商品类型（product_type 列）。
在某些场景下，就不能直接使用聚合函数进行聚合运算了，必须搭配 DISTINCT 函数使用。
比如：要计算总共有几种咖啡类型在售，该怎么计算呢？
如前所述，DISTINCT 函数用于删除重复数据，应用 COUNT 聚合函数之前，加上 DISTINCT 关键字就可以实现需求。
SELECT COUNT(DISTINCT product_type)
FROM product;聚合法则
- COUNT 聚合函数运算结果与参数有关，COUNT(*) / COUNT(1) 得到包含 NULL 值的所有行，COUNT(<列名>) 得到不包含 NULL 值的所有行。
- 聚合函数不处理包含 NULL 值的行，但是 COUNT(*) 除外。
- MAX / MIN 函数适用于文本类型和数字类型的列，而 SUM / AVG 函数仅适用于数字类型的列。
- 在聚合函数的参数中使用 DISTINCT 关键字，可以得到删除重复值的聚合结果。
GROUP BY
之前使用聚合函数都是会将整个表的数据进行处理，当你想将进行分组汇总时（即：将现有的数据按照某列来汇总统计），GROUP BY可以帮助你：
SELECT <列名1>,<列名2>, <列名3>, ……
FROM <表名>
GROUP BY <列名1>, <列名2>, <列名3>, ……;看一看是否使用GROUP BY语句的差异：
-- 按照商品种类统计数据行数
SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type;
-- 不含GROUP BY
SELECT product_type, COUNT(*)
FROM product这样，GROUP BY 子句就像切蛋糕那样将表进行了分组。在 GROUP BY 子句中指定的列称为聚合键或者分组列。
聚合键中包含NULL时
将进货单价（purchase_price）作为聚合键举例：
SELECT purchase_price, COUNT(*)
FROM product
GROUP BY purchase_price;此时会将NULL作为一组特殊数据进行聚合运算
书写位置
GROUP BY的子句书写顺序有严格要求，不按要求会导致SQL无法正常执行，目前出现过的子句顺序为：
- SELECT :arrow_right:
- FROM :arrow_right:
- WHERE :arrow_right:
- GROUP BY
其中前三项用于筛选数据，GROUP BY对筛选出的数据进行处理
SELECT purchase_price, COUNT(*)
FROM product
WHERE product_type = '衣服'
GROUP BY purchase_price;常见错误
在使用聚合函数及GROUP BY子句时，经常出现的错误有：
- 在聚合函数的SELECT子句中写了聚合键以外的列使用COUNT等聚合函数时，SELECT子句中如果出现列名，只能是GROUP BY子句中指定的列名（也就是聚合键）。
- 在GROUP BY子句中使用列的别名SELECT子句中可以通过AS来指定别名，但在GROUP BY中不能使用别名。因为在DBMS中 ,SELECT子句在GROUP BY子句后执行。
- 在WHERE中使用聚合函数原因是聚合函数的使用前提是结果集已经确定，而WHERE还处于确定结果集的过程中，所以相互矛盾会引发错误。 如果想指定条件，可以在SELECT，HAVING（下面马上会讲）以及ORDER BY子句中使用聚合函数。
HAVING
将表使用 GROUP BY 分组后，怎样才能只取出其中两组？
这里 WHERE 不可行，因为，WHERE子句只能指定记录（行）的条件，而不能用来指定组的条件（例如，“数据行数为 2 行”或者“平均值为 500”等）。
可以在 GROUP BY 后使用 HAVING 子句。
HAVING 的用法类似 WHERE。
值得注意的是：HAVING 子句必须与 GROUP BY 子句配合使用，且限定的是分组聚合结果，WHERE 子句是限定数据行（包括分组列），二者各司其职，不要混淆。
特点
HAVING子句用于对分组进行过滤，可以使用常数、聚合函数和GROUP BY中指定的列名（聚合键）。
-- 常数
SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type
HAVING COUNT(*) = 2;
-- 错误形式（因为product_name不包含在GROUP BY聚合键中）
SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type
HAVING product_name = '圆珠笔';ORDER BY
在某些场景下，需要得到一个排序之后的结果，比如运动员在奥运赛场的得分，组委会用得分倒序结果来判定金银铜牌到底花落谁家。而 SQL 语句执行结果默认随机排列，想要按照顺序排序，需使用 ORDER BY 子句。
SELECT <列名1>, <列名2>, <列名3>, ……
FROM <表名>
ORDER BY <排序基准列1> [ASC, DESC], <排序基准列2> [ASC, DESC], ……其中，参数 ASC 表示升序排列，DESC 表示降序排列，默认为升序，此时，参数 ASC 可以缺省。
如下代码将得到按照销售价格倒序排列的查询结果：
-- 降序排列
SELECT product_id, product_name, sale_price, purchase_price
FROM product
ORDER BY sale_price DESC;如果有多列排序需求，只需在 ORDER BY 子句中依次书写排序列 + 排序参数即可，详见如下代码：
-- 多个排序键
SELECT product_id, product_name, sale_price, purchase_price
FROM product
ORDER BY sale_price, product_id;需要特别说明的是：由于 NULL 无法使用比较运算符进行比较，也就是说，无法与文本类型，数字类型，日期类型等进行比较，当排序列存在 NULL 值时，NULL 结果会展示在查询结果的开头或者末尾。
-- 当用于排序的列名中含有NULL时，NULL会在开头或末尾进行汇总。
SELECT product_id, product_name, sale_price, purchase_price
FROM product
ORDER BY purchase_price;ORDER BY使用别名
前文讲GROUP BY中提到，GROUP BY 子句中不能使用SELECT 子句中定义的别名，但是在 ORDER BY 子句中却可以使用别名。为什么在GROUP BY中不可以而在ORDER BY中可以呢？
这是因为 SQL 在使用 HAVING 子句时 SELECT 语句的执行顺序为：
FROM → WHERE → GROUP BY → SELECT → HAVING → ORDER BY
其中 SELECT 的执行顺序在 GROUP BY 子句之后，ORDER BY 子句之前。
当在 ORDER BY 子句中使用别名时，已经知道了 SELECT 子句设置的别名，但是在 GROUP BY 子句执行时还不知道别名的存在，所以在 ORDER BY 子句中可以使用别名，但是在GROUP BY中不能使用别名。
ORDER BY NULL
在MySQL中，NULL 值被认为比任何 非NULL 值低，因此，当顺序为 ASC（升序）时，NULL 值出现在第一位，而当顺序为 DESC（降序）时，则排序在最后。
如果想指定存在 NULL 的行出现在首行或者末行，需要特殊处理。
使用如下代码构建示例表：
CREATE TABLE user (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(50),
date_login DATE,
PRIMARY KEY (id)
);
INSERT INTO user(name, date_login) VALUES
(NULL, '2017-03-12'),
('john', NULL),
('david', '2016-12-24'),
('zayne', '2017-03-02');既然排序时，NULL 的值比 非NULL 值低（可以理解为 0 或者 -∞），那么我们在排序时就要对这个默认情况进行特殊处理以达到想要的效果。
一般有如下两种需求：
- 将
NULL值排在末行，同时将所有非NULL值按升序排列。
对于数字或者日期类型，可以在排序字段前添加一个负号（minus）来得到反向排序。（-1、-2、-3....-∞）
SELECT * FROM user
ORDER BY -date_login DESC;对于字符型或者字符型数字，此方法不一定能得到期望的排序结果，可以使用 IS NULL 比较运算符。另外 ISNULL( ) 函数等同于使用 IS NULL 比较运算符。
-- IS NULL
SELECT * FROM user
ORDER BY name IS NULL ASC,name ASC;
-- ISNULL()
SELECT * FROM user
ORDER BY ISNULL(name) ASC,name ASC;上述语句先使用 ISNULL(name) 字段进行升序排列，而只有当 name 列值为 NULL 时，ISNULL(name) 才为真，所以其排到末行，而 name ASC 则实现了 非NULL 值升序排列。
还可以使用 COALESCE 函数实现需求
SELECT * FROM user
ORDER BY COALESCE(name, 'zzzzz') ASC;- 将
NULL值排在首行，同时将所有非NULL值按倒序排列。
对于数字或者日期类型，可以在排序字段前添加一个负号（minus）来实现。（-∞...-3、-2、-1）
SELECT * FROM user
ORDER BY -date_login ASC;对于字符型或者字符型数字，此方法不一定能得到期望的排序结果，可以使用 IS NOT NULL 比较运算符。另外 !ISNULL( ) 函数等同于使用 IS NOT NULL 比较运算符。
-- IS NOT NULL
SELECT * FROM user
ORDER BY name IS NOT NULL ASC,name DESC;
-- !ISNULL()
SELECT * FROM user
ORDER BY !ISNULL(name) ASC,name DESC;上述语句先使用 !ISNULL(name) 字段进行升序排列，而只有当 name 列值不为 NULL 时，!ISNULL(name) 才为真，所以其排到说行，而 name DESC 则实现了 非NULL 值降序排列。
还可以使用 COALESCE 函数实现需求
SELECT * FROM user
ORDER BY COALESCE(name, 'zzzzz') DESC;复杂查询
视图
SELECT product_name FROM view_product;单从表面上看起来这个语句是和正常的从数据表中查询数据是完全相同的，但其实操作的是一个视图。
从SQL的角度来说操作视图与操作表看起来是完全相同的
什么是视图
视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的（会在下面具体介绍），所以操作视图时会根据创建视图的SELECT语句生成一张虚拟表，然后在这张虚拟表上做SQL操作。
区别
视图与表的区别：是否保存了实际的数据。
视图并不是数据库真实存储的数据表，它可以看作是一个窗口，通过这个窗口我们可以看到数据库表中真实存在的数据。所以我们要区别视图和数据表的本质，即视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上。
视图不是表，视图是虚表，视图依赖于表
目的
那既然已经有数据表了，为什么还需要视图呢：
- 通过定义视图可以将频繁使用的SELECT语句保存以提高效率。
- 通过定义视图可以使用户看到的数据更加清晰。
- 通过定义视图可以不对外公开数据表全部字段，增强数据的保密性。
- 通过定义视图可以降低数据的冗余。
创建视图
说了这么多视图与表的区别，下面我们就一起来看一下如何创建视图吧。
创建视图的基本语法如下：
CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS <SELECT语句>其中SELECT 语句需要书写在 AS 关键字之后。 SELECT 语句中列的排列顺序和视图中列的排列顺序相同， SELECT 语句中的第 1 列就是视图中的第 1 列， SELECT 语句中的第 2 列就是视图中的第 2 列，以此类推。而且视图的列名是在视图名称之后的列表中定义的。
需要注意的是视图名在数据库中需要是唯一的，不能与其他视图和表重名。
视图不仅可以基于真实表，我们也可以在视图的基础上继续创建视图。
虽然在视图上继续创建视图的语法没有错误，但是我们还是应该尽量避免这种操作。这是因为对多数 DBMS 来说， 多重视图会降低SQL的性能。
- 注意事项：在一般的DBMS中定义视图时不能使用ORDER BY语句。
下面这样定义视图是错误的。
为什么不能使用ORDER BY子句呢？这是因为视图和表一样，数据行都是没有顺序的。CREATE VIEW productsum (product_type, cnt_product) AS SELECT product_type, COUNT(*) FROM product GROUP BY product_type ORDER BY product_type;
在 MySQL中视图的定义是允许使用 ORDER BY 语句的，但是若从特定视图进行选择，而该视图使用了自己的 ORDER BY 语句，则视图定义中的 ORDER BY 将被忽略。
基于单表的视图
我们在product表的基础上创建一个视图，如下：
CREATE VIEW productsum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type ;创建的视图如下图所示：
基于多表的视图
在product表和shop_product表的基础上创建视图。
CREATE VIEW view_shop_product(product_type, sale_price, shop_name)
AS
SELECT product_type, sale_price, shop_name
FROM product,
shop_product
WHERE product.product_id = shop_product.product_id;我们可以在这个视图的基础上进行查询
SELECT sale_price, shop_name
FROM view_shop_product
WHERE product_type = '衣服';查询结果为：
修改视图结构
修改视图结构的基本语法如下：
ALTER VIEW <视图名> AS <SELECT语句>
ALTER VIEW productsum
AS
SELECT product_type, sale_price
FROM Product
WHERE regist_date > '2009-09-11';其中视图名在数据库中需要是唯一的，不能与其他视图和表重名。
当然也可以通过将当前视图删除然后重新创建的方式达到修改的效果
更新视图
因为视图是一个虚拟表，所以对视图的操作就是对底层基础表的操作，所以在修改时只有满足底层基本表的定义才能成功修改。
对于一个视图来说，如果包含以下结构的任意一种都是不可以被更新的：
- 聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
- DISTINCT 关键字。
- GROUP BY 子句。
- HAVING 子句。
- UNION 或 UNION ALL 运算符。
- FROM 子句中包含多个表。
视图归根结底还是从表派生出来的，因此，如果原表可以更新，那么 视图中的数据也可以更新。反之亦然，如果视图发生了改变，而原表没有进行相应更新的话，就无法保证数据的一致性了。
- 更新视图
因为我们刚刚修改的productsum视图不包括以上的限制条件，我们来尝试更新一下视图
此时我们再查看 productsum 视图，可以发现数据已经更新了UPDATE productsum SET sale_price = '5000' WHERE product_type = '办公用品';
此时观察原表也可以发现数据也被更新了
刚才修改视图的时候是设置product_type=’办公用品’的商品的sale_price=5000，原表的数据只有一条做了修改，还是因为视图的定义，视图只是原表的一个窗口，所以它修改也只能修改透过窗口能看到的内容。 - 注意：这里虽然修改成功了，但是并不推荐这种使用方式。而且我们在创建视图时也尽量使用限制不允许通过视图来修改表*
删除视图
删除视图的基本语法如下：
DROP VIEW <视图名1> [ , <视图名2> …]
DROP VIEW productsum;如果我们继续操作这个视图的话就会提示当前操作的内容不存在。
子查询
SELECT stu_name
FROM (
SELECT stu_name, COUNT(*) AS stu_cnt
FROM students_info
GROUP BY stu_age
) AS studentSum;这个语句看起来很好理解，其中使用括号括起来的sql语句首先执行，执行成功后再执行外面的sql语句。但是我们上一节提到的视图也是根据SELECT语句创建视图然后在这个基础上再进行查询。那么什么是子查询呢？子查询和视图又有什么关系呢？
什么是子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从 MySQL 4.1 开始引入，在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。
子查询和视图的关系
子查询就是将用来定义视图的 SELECT 语句直接用于 FROM 子句当中。其中AS studentSum可以看作是子查询的名称，而且由于子查询是一次性的，所以子查询不会像视图那样保存在存储介质中，而是在 SELECT 语句执行之后就消失了。
嵌套子查询
与在视图上再定义视图类似，子查询也没有具体的限制，例如我们可以这样
SELECT product_type, cnt_product
FROM (
SELECT *
FROM (
SELECT product_type,
COUNT(*) AS cnt_product
FROM product
GROUP BY product_type
) AS productsum
WHERE cnt_product = 4
) AS productsum2;其中最内层的子查询我们将其命名为 productsum ，这条语句根据product_type分组并查询个数，第二层查询中将个数为4的商品查询出来，最外层查询product_type和cnt_product两列。
虽然嵌套子查询可以查询出结果，但是随着子查询嵌套的层数的叠加，SQL语句不仅会难以理解而且执行效率也会很差，所以要尽量避免这样的使用。
标量子查询
标量就是单一的意思，那么标量子查询也就是单一的子查询
所谓单一就是要求我们执行的SQL语句只能返回一个值，也就是要返回表中具体的某一行的某一列。例如我们有下面这样一张表
product_id | product_name | sale_price
------------+-------------+----------
0003 | 运动T恤 | 4000
0004 | 菜刀 | 3000
0005 | 高压锅 | 6800那么我们执行一次标量子查询后是要返回类似于，“0004”，“菜刀”这样的结果。
作用
- 查询出销售单价高于平均销售单价的商品
- 查询出注册日期最晚的那个商品
让我们看如何通过标量子查询语句查询出销售单价高于平均销售单价的商品。
SELECT product_id, product_name, sale_price
FROM product
WHERE sale_price > (SELECT AVG(sale_price) FROM product);上面的这条语句首先后半部分查询出product表中的平均售价，前面的sql语句在根据WHERE条件挑选出合适的商品。
由于标量子查询的特性，导致标量子查询不仅仅局限于 WHERE 子句中，通常任何可以使用单一值的位置都可以使用。也就是说， 能够使用常数或者列名的地方，无论是 SELECT 子句、GROUP BY 子句、HAVING 子句，还是 ORDER BY 子句，几乎所有的地方都可以使用。
我们还可以这样使用标量子查询：
SELECT product_id,
product_name,
sale_price,
(SELECT AVG(sale_price)
FROM product) AS avg_price
FROM product;关联子查询
- 什么是关联子查询
关联子查询既然包含关联两个字，那么一定意味着查询与子查询之间存在着联系。这种联系是如何建立起来的呢？
关联子查询就是通过一些标志将内外两层的查询连接起来起到过滤数据的目的。SELECT product_type, product_name, sale_price FROM product AS p1 WHERE sale_price > (SELECT AVG(sale_price) FROM product AS p2 WHERE p1.product_type = p2.product_type GROUP BY product_type); - 关联子查询与子查询的联系
在第二条SQL语句也就是关联子查询中我们将外面的product表标记为p1，将内部的product设置为p2，而且通过WHERE语句连接了两个查询。-- 查询出销售单价高于平均销售单价的商品 SELECT product_id, product_name, sale_price FROM product WHERE sale_price > (SELECT AVG(sale_price) FROM product); -- 选取出各商品种类中高于该商品种类的平均销售单价的商品 SELECT product_type, product_name, sale_price FROM product AS p1 WHERE sale_price > (SELECT AVG(sale_price) FROM product AS p2 WHERE p1.product_type =p2.product_type GROUP BY product_type);
关联查询的执行过程：
- 首先执行不带WHERE的主查询
- 根据主查询讯结果匹配product_type，获取子查询结果
- 将子查询结果再与主查询结合执行完整的SQL语句
在子查询中像标量子查询，嵌套子查询或者关联子查询可以看作是子查询的一种操作方式即可。
小结
视图和子查询是数据库操作中较为基础的内容，对于一些复杂的查询需要使用子查询加一些条件语句组合才能得到正确的结果。但是无论如何对于一个SQL语句来说都不应该设计的层数非常深且特别复杂，不仅可读性差而且执行效率也难以保证，所以尽量有简洁的语句来完成需要的功能。
函数
sql 自带了各种各样的函数，极大提高了 sql 语言的便利性。
给它一个输入值，它便按照预设的程序定义给出返回值，输入值称为参数。
函数大致分为如下几类：
- 算术函数 （用来进行数值计算的函数）
- 字符串函数 （用来进行字符串操作的函数）
- 日期函数 （用来进行日期操作的函数）
- 转换函数 （用来转换数据类型和值的函数）
- 聚合函数 （用来进行数据聚合的函数）
函数总个数超过200个，不需要完全记住，常用函数有 30~50 个，其他不常用的函数使用时查阅文档即可。
算数函数
- ABS – 绝对值
语法：ABS( 数值 )
ABS 函数用于计算一个数字的绝对值，表示一个数到原点的距离。
当 ABS 函数的参数为NULL时，返回值也是NULL。 - MOD – 求余数
语法：MOD( 被除数，除数 )
MOD 是计算除法余数（求余）的函数，是 modulo 的缩写。小数没有余数的概念，只能对整数列求余数。
注意：主流的 DBMS 都支持 MOD 函数，只有SQL Server 不支持该函数，其使用%符号来计算余数。 - ROUND – 四舍五入
语法：ROUND( 对象数值，保留小数的位数 )
ROUND 函数用来进行四舍五入操作。
注意：当参数 保留小数的位数 为变量时，可能会遇到错误，请谨慎使用变量。SELECT m, ABS(m) AS abs_col , n, p, MOD(n, p) AS mod_col, ROUND(m,1) AS round_col FROM samplemath; +----------+---------+------+------+---------+-----------+ | m | abs_col | n | p | mod_col | round_col | +----------+---------+------+------+---------+-----------+ | 500.000 | 500.000 | 0 | NULL | NULL | 500.0 | | -180.000 | 180.000 | 0 | NULL | NULL | -180.0 | | NULL | NULL | NULL | NULL | NULL | NULL | | NULL | NULL | 7 | 3 | 1 | NULL | | NULL | NULL | 5 | 2 | 1 | NULL | | NULL | NULL | 4 | NULL | NULL | NULL | | 8.000 | 8.000 | NULL | 3 | NULL | 8.0 | | 2.270 | 2.270 | 1 | NULL | NULL | 2.3 | | 5.555 | 5.555 | 2 | NULL | NULL | 5.6 | | NULL | NULL | 1 | NULL | NULL | NULL | | 8.760 | 8.760 | NULL | NULL | NULL | 8.8 | +----------+---------+------+------+---------+-----------+ 11 rows in set (0.08 sec)
字符串函数
- CONCAT – 拼接
语法：CONCAT(str1, str2, str3)
MySQL中使用 CONCAT 函数进行拼接。 - LENGTH – 字符串长度
语法：LENGTH( 字符串 ) - LOWER – 小写转换
LOWER 函数只能针对英文字母使用，它会将参数中的字符串全都转换为小写。该函数不适用于英文字母以外的场合，不影响原本就是小写的字符。
类似的， UPPER 函数用于大写转换。 - REPLACE – 字符串的替换
语法：REPLACE( 对象字符串，替换前的字符串，替换后的字符串 ) - SUBSTRING – 字符串的截取
语法：SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）
使用 SUBSTRING 函数 可以截取出字符串中的一部分字符串。截取的起始位置从字符串最左侧开始计算，索引值起始为1。 - （扩展内容）SUBSTRING_INDEX – 字符串按索引截取
语法：SUBSTRING_INDEX (原始字符串， 分隔符，n)
该函数用来获取原始字符串按照分隔符分割后，第 n 个分隔符之前（或之后）的子字符串，支持正向和反向索引，索引起始值分别为 1 和 -1。
获取第1个元素比较容易，获取第2个元素/第n个元素可以采用二次拆分的写法。SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2); +------------------------------------------+ | SUBSTRING_INDEX('www.mysql.com', '.', 2) | +------------------------------------------+ | www.mysql | +------------------------------------------+ 1 row in set (0.00 sec) SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2); +-------------------------------------------+ | SUBSTRING_INDEX('www.mysql.com', '.', -2) | +-------------------------------------------+ | mysql.com | +-------------------------------------------+ 1 row in set (0.00 sec)SELECT SUBSTRING_INDEX('www.mysql.com', '.', 1); +------------------------------------------+ | SUBSTRING_INDEX('www.mysql.com', '.', 1) | +------------------------------------------+ | www | +------------------------------------------+ 1 row in set (0.00 sec) SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('www.mysql.com', '.', 2), '.', -1); +--------------------------------------------------------------------+ | SUBSTRING_INDEX(SUBSTRING_INDEX('www.mysql.com', '.', 2), '.', -1) | +--------------------------------------------------------------------+ | mysql | +--------------------------------------------------------------------+ 1 row in set (0.00 sec) - （扩展内容）REPEAT – 字符串按需重复多次
语法：REPEAT(string, number)
该函数用来对特定字符实现按需重复。mysql> SELECT REPEAT('加油！',3); +-----------------------------+ | REPEAT('加油！',3) | +-----------------------------+ | 加油！加油！加油！ | +-----------------------------+ 1 row in set (0.00 sec)
日期函数
不同DBMS的日期函数语法各有不同，本课程介绍一些被标准 SQL 承认的可以应用于绝大多数 DBMS 的函数。特定DBMS的日期函数查阅文档即可。
- CURRENT_DATE – 获取当前日期
SELECT CURRENT_DATE; +--------------+ | CURRENT_DATE | +--------------+ | 2020-08-08 | +--------------+ 1 row in set (0.00 sec) - CURRENT_TIME – 当前时间
SELECT CURRENT_TIME; +--------------+ | CURRENT_TIME | +--------------+ | 17:26:09 | +--------------+ 1 row in set (0.00 sec) - CURRENT_TIMESTAMP – 当前日期和时间
SELECT CURRENT_TIMESTAMP; +---------------------+ | CURRENT_TIMESTAMP | +---------------------+ | 2020-08-08 17:27:07 | +---------------------+ 1 row in set (0.00 sec) - EXTRACT – 截取日期元素
语法：EXTRACT(日期元素 FROM 日期)
使用 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; +---------------------+------+-------+------+------+--------+--------+ | now | year | month | day | hour | MINute | second | +---------------------+------+-------+------+------+--------+--------+ | 2020-08-08 17:34:38 | 2020 | 8 | 8 | 17 | 34 | 38 | +---------------------+------+-------+------+------+--------+--------+ 1 row in set (0.00 sec)
转换函数
“转换”这个词的含义非常广泛，在 SQL 中主要有两层意思：一是数据类型的转换，简称为类型转换，在英语中称为cast；另一层意思是值的转换。
- CAST – 类型转换
语法：CAST（转换前的值 AS 想要转换的数据类型）
需要特别注意的是，当要转换为整型时，需要指定为 SIGNED（有符号） 或者 UNSIGNED（无符号）-- 将字符串类型转换为数值类型 SELECT CAST('0001' AS SIGNED INTEGER) AS int_col; +---------+ | int_col | +---------+ | 1 | +---------+ 1 row in set (0.00 sec) -- 将字符串类型转换为日期类型 SELECT CAST('2009-12-14' AS DATE) AS date_col; +------------+ | date_col | +------------+ | 2009-12-14 | +------------+ 1 row in set (0.00 sec) - 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, COALESCE(NULL, NULL, '2020-11-01') AS col_3; +-------+-------------+------------+ | col_1 | col_2 | col_3 | +-------+-------------+------------+ | 11 | hello world | 2020-11-01 | +-------+-------------+------------+ 1 row in set (0.00 sec)
谓词
什么是谓词
谓词就是返回值为真值的函数。包括TRUE / FALSE / UNKNOWN。
谓词主要有以下几个：
- LIKE
- BETWEEN
- IS NULL、IS NOT NULL
- IN
- EXISTS
LIKE
用于字符串的部分一致查询
当需要进行字符串的部分一致查询时需要使用该谓词。
部分一致大体可以分为前方一致、中间一致和后方一致三种类型。
首先我们来创建一张表
- 前方一致：选取出“dddabc”
前方一致即作为查询条件的字符串（这里是“ddd”）与查询对象字符串起始部分相同。
其中的SELECT * FROM samplelike WHERE strcol LIKE 'ddd%'; +--------+ | strcol | +--------+ | dddabc | +--------+ 1 row in set (0.00 sec)%是代表“零个或多个任意字符串”的特殊符号，本例中代表“以ddd开头的所有字符串”。 - 中间一致：选取出“abcddd”“dddabc”“abdddc”
中间一致即查询对象字符串中含有作为查询条件的字符串，无论该字符串出现在对象字
符串的最后还是中间都没有关系。SELECT * FROM samplelike WHERE strcol LIKE '%ddd%'; +--------+ | strcol | +--------+ | abcddd | | abdddc | | dddabc | +--------+ 3 rows in set (0.00 sec) - 后方一致：选取出“abcddd“
后方一致即作为查询条件的字符串（这里是“ddd”）与查询对象字符串的末尾部分相同。
综合如上三种类型的查询可以看出，查询条件最宽松，也就是能够取得最多记录的是SELECT * FROM samplelike WHERE strcol LIKE '%ddd'; +--------+ | strcol | +--------+ | abcddd | +--------+ 1 row in set (0.00 sec)中间一致。这是因为它同时包含前方一致和后方一致的查询结果。 _下划线匹配任意 1 个字符
使用 _（下划线）来代替 %，与 % 不同的是，它代表了“任意 1 个字符”。SELECT * FROM samplelike WHERE strcol LIKE 'abc__'; +--------+ | strcol | +--------+ | abcdd | +--------+ 1 row in set (0.00 sec)
BETWEEN
使用 BETWEEN 可以进行范围查询。该谓词与其他谓词或者函数的不同之处在于它使用了3个参数。
-- 选取销售单价为100～ 1000元的商品
SELECT product_name, sale_price
FROM product
WHERE sale_price BETWEEN 100 AND 1000;
+--------------+------------+
| product_name | sale_price |
+--------------+------------+
| T恤 | 1000 |
| 打孔器 | 500 |
| 叉子 | 500 |
| 擦菜板 | 880 |
| 圆珠笔 | 100 |
+--------------+------------+
5 rows in set (0.00 sec)BETWEEN 的特点就是结果中会包含 100 和 1000 这两个临界值，也就是闭区间。如果不想让结果中包含临界值，那就必须使用 < 和 >。
SELECT product_name, sale_price
FROM product
WHERE sale_price > 100
AND sale_price < 1000;
+--------------+------------+
| product_name | sale_price |
+--------------+------------+
| 打孔器 | 500 |
| 叉子 | 500 |
| 擦菜板 | 880 |
+--------------+------------+
3 rows in set (0.00 sec)IS NULL、 IS NOT NULL
为了选取出某些值为 NULL 的列的数据，不能使用 =，而只能使用特定的谓词IS NULL。
SELECT product_name, purchase_price
FROM product
WHERE purchase_price IS NULL;
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| 叉子 | NULL |
| 圆珠笔 | NULL |
+--------------+----------------+
2 rows in set (0.00 sec)与此相反，想要选取NULL以外的数据时，需要使用IS NOT NULL。
SELECT product_name, purchase_price
FROM product
WHERE purchase_price IS NOT NULL;
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| T恤 | 500 |
| 打孔器 | 320 |
| 运动T恤 | 2800 |
| 菜刀 | 2800 |
| 高压锅 | 5000 |
| 擦菜板 | 790 |
+--------------+----------------+
6 rows in set (0.00 sec)IN谓词
OR的简便用法
多个查询条件取并集时可以选择使用or语句。
-- 通过OR指定多个进货单价进行查询
SELECT product_name, purchase_price
FROM product
WHERE purchase_price = 320
OR purchase_price = 500
OR purchase_price = 5000;
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| T恤 | 500 |
| 打孔器 | 320 |
| 高压锅 | 5000 |
+--------------+----------------+
3 rows in set (0.00 sec)虽然上述方法没有问题，但还是存在一点不足之处，那就是随着希望选取的对象越来越多， SQL 语句也会越来越长，阅读起来也会越来越困难。这时， 我们就可以使用IN 谓词
`IN(值1, 值2, 值3, ……)来替换上述 SQL 语句。
SELECT product_name, purchase_price
FROM product
WHERE purchase_price IN (320, 500, 5000);
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| T恤 | 500 |
| 打孔器 | 320 |
| 高压锅 | 5000 |
+--------------+----------------+
3 rows in set (0.00 sec)上述语句简洁了很多，可读性大幅提高。
反之，希望选取出“进货单价不是 320 元、 500 元、 5000 元”的商品时，可以使用否定形式NOT IN来实现。
SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (320, 500, 5000);
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| 运动T恤 | 2800 |
| 菜刀 | 2800 |
| 擦菜板 | 790 |
+--------------+----------------+
3 rows in set (0.00 sec)需要注意的是，在使用IN 和 NOT IN 时是无法选取出NULL数据的。
实际结果也是如此，上述两组结果中都不包含进货单价为NULL的叉子和圆珠笔。 NULL只能使用 IS NULL 和 IS NOT NULL 来进行判断。
使用子查询作为IN谓词的参数
IN和子查询
IN 谓词（NOT IN 谓词）具有其他谓词所没有的用法，那就是可以使用子查询作为其参数。子查询就是 SQL内部生成的表，因此也可以说“能够将表作为 IN 的参数”。同理，我们还可以说“能够将视图作为 IN 的参数”。-- step2：取出大阪门店在售商品的销售单价 `sale_price` SELECT product_name, sale_price FROM product WHERE product_id IN (SELECT product_id FROM shopproduct WHERE shop_id = '000C'); +--------------+------------+ | product_name | sale_price | +--------------+------------+ | 运动T恤 | 4000 | | 菜刀 | 3000 | | 叉子 | 500 | | 擦菜板 | 880 | +--------------+------------+ 4 rows in set (0.00 sec)根据第5章学习的知识，子查询是从最内层开始执行的（由内而外），因此，上述语句的子查询执行之后，sql 展开成下面的语句
-- 子查询展开后的结果 SELECT product_name, sale_price FROM product WHERE product_id IN ('0003', '0004', '0006', '0007'); +--------------+------------+ | product_name | sale_price | +--------------+------------+ | 运动T恤 | 4000 | | 菜刀 | 3000 | | 叉子 | 500 | | 擦菜板 | 880 | +--------------+------------+ 4 rows in set (0.00 sec)既然 in 谓词也能实现，那为什么还要使用子查询呢：
①：实际生活中，某个门店的在售商品是不断变化的，使用 in 谓词就需要经常更新 sql 语句，降低了效率，提高了维护成本；
②：实际上，某个门店的在售商品可能有成百上千个，手工维护在售商品编号真是个大工程。
使用子查询即可保持 sql 语句不变，极大提高了程序的可维护性，这是系统开发中需要重点考虑的内容。NOT IN和子查询
NOT IN 同样支持子查询作为参数，用法和 in 完全一样。-- NOT IN 使用子查询作为参数，取出未在东京门店销售的商品的销售单价 SELECT product_name, sale_price FROM product WHERE product_id NOT IN (SELECT product_id FROM shopproduct WHERE shop_id = '000A'); +--------------+------------+ | product_name | sale_price | +--------------+------------+ | 菜刀 | 3000 | | 高压锅 | 6800 | | 叉子 | 500 | | 擦菜板 | 880 | | 圆珠笔 | 100 | +--------------+------------+ 5 rows in set (0.00 sec)
EXIST
EXIST 谓词的用法理解起来有些难度。
① EXIST 的使用方法与之前的都不相同
② 语法理解起来比较困难
③ 实际上即使不使用 EXIST，基本上也都可以使用 IN（或者 NOT IN）来代替
能够熟练使用 EXIST 谓词，就能体会到它极大的便利性。
- EXIST谓词的使用方法
谓词的作用就是 “判断是否存在满足某种条件的记录”。
如果存在这样的记录就返回真（TRUE），如果不存在就返回假（FALSE）。
EXIST（存在）谓词的主语是“记录”。
我们继续以 IN和子查询 中的示例，使用 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);
+--------------+------------+
| product_name | sale_price |
+--------------+------------+
| 运动T恤 | 4000 |
| 菜刀 | 3000 |
| 叉子 | 500 |
| 擦菜板 | 880 |
+--------------+------------+
4 rows in set (0.00 sec)- EXIST的参数
之前我们学过的谓词，基本上都是像“列 LIKE 字符串”或者“ 列 BETWEEN 值 1 AND 值 2”这样需要指定 2 个以上的参数，而 EXIST 的左侧并没有任何参数。因为 EXIST 是只有 1 个参数的谓词。 所以，EXIST 只需要在右侧书写 1 个参数，该参数通常都会是一个子查询。
上面这样的子查询就是唯一的参数。确切地说，由于通过条件“SP.product_id = P.product_id”将 product 表和 shopproduct表进行了联接，因此作为参数的是关联子查询。 EXIST 通常会使用关联子查询作为参数。(SELECT * FROM shopproduct AS sp WHERE sp.shop_id = '000C' AND sp.product_id = p.product_id) - 子查询中的SELECT *
由于 EXIST 只关心记录是否存在，因此返回哪些列都没有关系。 EXIST 只会判断是否存在满足子查询中 WHERE 子句指定的条件“商店编号（shop_id）为 ‘000C’，商品（product）表和商店
商品（shopproduct）表中商品编号（product_id）相同”的记录，只有存在这样的记录时才返回真（TRUE）。
因此，使用下面的查询语句，查询结果也不会发生变化。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); +--------------+------------+ | product_name | sale_price | +--------------+------------+ | 运动T恤 | 4000 | | 菜刀 | 3000 | | 叉子 | 500 | | 擦菜板 | 880 | +--------------+------------+ 4 rows in set (0.00 sec)可以把在 EXIST 的子查询中书写 SELECT * 当作 SQL 的一种习惯。
- 使用NOT EXIST替换NOT IN
就像 EXIST 可以用来替换 IN 一样， NOT IN 也可以用NOT EXIST来替换。
NOT EXIST 与 EXIST 相反，当“不存在”满足子查询中指定条件的记录时返回真（TRUE）。SELECT product_name, sale_price FROM product AS p WHERE NOT EXISTS (SELECT * FROM shopproduct AS sp WHERE sp.shop_id = '000A' AND sp.product_id = p.product_id); +--------------+------------+ | product_name | sale_price | +--------------+------------+ | 菜刀 | 3000 | | 高压锅 | 6800 | | 叉子 | 500 | | 擦菜板 | 880 | | 圆珠笔 | 100 | +--------------+------------+ 5 rows in set (0.00 sec)
CASE表达式
CASE 表达式是函数的一种。是 SQL 中数一数二的重要功能。
CASE 表达式是在区分情况时使用的，这种情况的区分在编程中通常称为（条件）分支。
CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种。搜索CASE表达式包含简单CASE表达式的全部功能
CASE WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
.
.
.
ELSE <表达式>
END 上述语句执行时，依次判断 when 表达式是否为真值，是则执行 THEN 后的语句，如果所有的 when 表达式均为假，则执行 ELSE 后的语句。
无论多么庞大的 CASE 表达式，最后也只会返回一个值。
应用场景1：根据不同分支得到不同列值
SELECT product_name, CASE WHEN product_type = '衣服' THEN CONCAT('A ： ',product_type) WHEN product_type = '办公用品' THEN CONCAT('B ： ',product_type) WHEN product_type = '厨房用具' THEN CONCAT('C ： ',product_type) ELSE NULL END AS abc_product_type FROM product; +--------------+------------------+ | product_name | abc_product_type | +--------------+------------------+ | T恤 | A ： 衣服 | | 打孔器 | B ： 办公用品 | | 运动T恤 | A ： 衣服 | | 菜刀 | C ： 厨房用具 | | 高压锅 | C ： 厨房用具 | | 叉子 | C ： 厨房用具 | | 擦菜板 | C ： 厨房用具 | | 圆珠笔 | B ： 办公用品 | +--------------+------------------+ 8 rows in set (0.00 sec)ELSE 子句也可以省略不写，这时会被默认为 ELSE NULL。但为了防止有人漏读，还是希望大家能够显式地写出 ELSE 子句。
此外， CASE 表达式最后的“END”是不能省略的，请大家特别注意不要遗漏。忘记书写 END 会发生语法错误，这也是初学时最容易犯的错误。应用场景2：实现列方向上的聚合
通常我们使用如下代码实现行的方向上不同种类的聚合（这里是 sum）SELECT product_type, SUM(sale_price) AS sum_price FROM product GROUP BY product_type; +--------------+-----------+ | product_type | sum_price | +--------------+-----------+ | 衣服 | 5000 | | 办公用品 | 600 | | 厨房用具 | 11180 | +--------------+-----------+ 3 rows in set (0.00 sec)假如要在列的方向上展示不同种类额聚合值，该如何写呢？
sum_price_clothes | sum_price_kitchen | sum_price_office ------------------+-------------------+----------------- 5000 | 11180 | 600聚合函数 + CASE WHEN 表达式即可实现该效果
-- 对按照商品种类计算出的销售单价合计值进行行列转换 SELECT SUM(CASE WHEN product_type = '衣服' THEN sale_price ELSE 0 END) AS sum_price_clothes, SUM(CASE WHEN product_type = '厨房用具' THEN sale_price ELSE 0 END) AS sum_price_kitchen, SUM(CASE WHEN product_type = '办公用品' THEN sale_price ELSE 0 END) AS sum_price_office FROM product; +-------------------+-------------------+------------------+ | sum_price_clothes | sum_price_kitchen | sum_price_office | +-------------------+-------------------+------------------+ | 5000 | 11180 | 600 | +-------------------+-------------------+------------------+ 1 row in set (0.00 sec)应用场景3：实现行转列
假设有如下图表的结构
计划得到如下的图表结构
聚合函数 + CASE WHEN 表达式即可实现该转换
-- CASE WHEN 实现数字列 score 行转列
SELECT name,
SUM(CASE WHEN subject = '语文' THEN score ELSE null END) as chinese,
SUM(CASE WHEN subject = '数学' THEN score ELSE null END) as math,
SUM(CASE WHEN subject = '外语' THEN score ELSE null END) as english
FROM score
GROUP BY name;
+------+---------+------+---------+
| name | chinese | math | english |
+------+---------+------+---------+
| 张三 | 93 | 88 | 91 |
| 李四 | 87 | 90 | 77 |
+------+---------+------+---------+上述代码实现了数字列 score 的行转列，也可以实现文本列 subject 的行转列
-- CASE WHEN 实现文本列 subject 行转列
SELECT name,
MAX(CASE WHEN subject = '语文' THEN subject ELSE null END) as chinese,
MAX(CASE WHEN subject = '数学' THEN subject ELSE null END) as math,
MIN(CASE WHEN subject = '外语' THEN subject ELSE null END) as english
FROM score
GROUP BY name;
+------+---------+------+---------+
| name | chinese | math | english |
+------+---------+------+---------+
| 张三 | 语文 | 数学 | 外语 |
| 李四 | 语文 | 数学 | 外语 |
+------+---------+------+---------+总结：
- 当待转换列为数字时，可以使用
SUM AVG MAX MIN等聚合函数； - 当待转换列为文本时，可以使用
MAX MIN等聚合函数
集合运算
集合在数学领域表示“各种各样的事物的总和”, 在数据库领域表示记录的集合. 具体来说,表、视图和查询的执行结果都是记录的集合, 其中的元素为表或者查询结果中的每一行。
在标准 SQL 中, 分别对检索结果使用 UNION, INTERSECT, EXCEPT 来将检索结果进行并,交和差运算, 像UNION,INTERSECT, EXCEPT这种用来进行集合运算的运算符称为集合运算符。
在数据库中, 所有的表–以及查询结果–都可以视为集合, 因此也可以把表视为集合进行上述集合运算, 在很多时候, 这种抽象非常有助于对复杂查询问题给出一个可行的思路。
加减法
UNION
SELECT product_id, product_name
FROM product
UNION
SELECT product_id, product_name
FROM product2;上述结果包含了两张表中的全部商品，这就是集合中的并集运算
UNION 等集合运算符通常都会除去重复的记录。
上述查询是对不同的两张表进行求并集运算. 对于同一张表, 实际上也是可以进行求并集的。
假设连锁店想要增加成本利润率超过 50%或者售价低于 800 的货物的存货量, 请使用 UNION 对分别满足上述两个条件的商品的查询结果求并集：
-- 参考答案:
SELECT product_id,product_name,product_type
,sale_price,purchase_price
FROM product
WHERE sale_price<800
UNION
SELECT product_id,product_name,product_type
,sale_price,purchase_price
FROM product
WHERE sale_price>1.5*purchase_price;UNION OR
思考: 如果不使用 UNION 该怎么写查询语句?
-- 参考答案:
SELECT product_id,product_name,product_type
,sale_price,purchase_price
FROM product
WHERE sale_price < 800
OR sale_price > 1.5 * purchase_price;对于上边的练习题, 如果你已经正确地写出来查询, 你会发现, 使用 UNION 对两个查询结果取并集, 和在一个查询中使用 WHERE 子句, 然后使用 OR 谓词连接两个查询条件, 能够得到相同的结果。
那么是不是就没必要引入 UNION 了呢? 当然不是这样的. 确实, 对于同一个表的两个不同的筛选结果集, 使用 UNION 对两个结果集取并集, 和把两个子查询的筛选条件用 OR 谓词连接, 会得到相同的结果, 但倘若要将两个不同的表中的结果合并在一起, 就不得不使用 UNION 了。
而且, 即便是对于同一张表, 有时也会出于查询效率方面的因素来使用 UNION。
分别使用 UNION 或者 OR 谓词,找出成本利润率不足 30%或成本利润率未知的商品。
-- 使用 OR 谓词
SELECT *
FROM product
WHERE sale_price / purchase_price < 1.3
OR sale_price / purchase_price IS NULL;-- 使用 UNION
SELECT *
FROM product
WHERE sale_price / purchase_price < 1.3
UNION
SELECT *
FROM product
WHERE sale_price / purchase_price IS NULL;UNION ALL
UNION 会对两个查询的结果集进行合并和去重, 这种去重不仅会去掉两个结果集相互重复的, 还会去掉一个结果集中的重复行. 但在实践中有时候需要需要不去重的并集, 在 UNION 的结果中保留重复行的语法其实非常简单,只需要在 UNION 后面添加 ALL 关键字就可以了。
例如, 想要知道 product 和 product2 中所包含的商品种类及每种商品的数量, 第一步,就需要将两个表的商品种类字段选出来, 然后使用 UNION ALL 进行不去重地合并. 接下来再对两个表的结果按 product_type 字段分组计数。
-- 保留重复行
SELECT product_type
FROM product
UNION ALL
SELECT product_type
FROM product2;隐式数据类型转换
通常来说, 我们会把类型完全一致, 并且代表相同属性的列使用 UNION 合并到一起显示, 但有时候, 即使数据类型不完全相同, 也会通过隐式类型转换来将两个类型不同的列放在一列里显示, 例如字符串和数值类型:
SELECT product_id, product_name, '1'
FROM product
UNION
SELECT product_id, product_name,sale_price
FROM product2;上述查询能够正确执行
需要注意的是 hive中进行join关联时，关联列要避免使用隐式数据类型转换，否则容易导致数据倾斜
交集
集合的交, 就是两个集合的公共部分, 由于集合元素的互异性, 集合的交只需通过文氏图就可以很直观地看到它的意义。
虽然集合的交运算在SQL标准中已经出现多年了, 然而很遗憾的是, 截止到 MySQL 8.0 版本, MySQL 仍然不支持 INTERSECT 操作。
SELECT product_id, product_name
FROM product
INTERSECT
SELECT product_id, product_name
FROM product2;错误代码：1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘SELECT product_id, product_name
FROM product2
此时需要用 inner join 来求得交集
SELECT p1.product_id, p1.product_name
FROM product p1
INNER JOIN product2 p2
ON p1.product_id=p2.product_id;INTERSECT AND
对于同一个表的两个查询结果而言, 他们的交INTERSECT实际上可以等价地将两个查询的检索条件用AND谓词连接来实现。
使用AND谓词查找product表中利润率高于50%,并且售价低于1500的商品：
SELECT *
FROM product
WHERE sale_price > 1.5 * purchase_price
AND sale_price < 1500;差集,补集与表的减法
求集合差集的减法运算和实数的减法运算有些不同, 当使用一个集合A减去另一个集合B的时候,对于只存在于集合B而不存在于集合A的元素, 采取直接忽略的策略,因此集合A和B做减法只是将集合A中也同时属于集合B的元素减掉。
EXCEPT
MySQL 8.0 还不支持 表的减法运算符 EXCEPT. 不过, 借助NOT IN谓词, 我们同样可以实现表的减法。
找出只存在于product表但不存在于product2表的商品：
-- 使用 NOT IN 子句的实现方法
SELECT *
FROM product
WHERE product_id NOT IN (SELECT product_id
FROM product2);EXCEPT与NOT
通过上述练习题的MySQL解法, 我们发现, 使用 NOT IN 谓词, 基本上可以实现和SQL标准语法中的EXCEPT运算相同的效果。
使用NOT谓词进行集合的减法运算, 求出 product 表中, 售价高于2000、成本利润率不低于 30% 的商品, 结果应该如下表所示：
SELECT *
FROM product
WHERE sale_price > 2000
AND product_id NOT IN (SELECT product_id
FROM product
WHERE sale_price < 1.3*purchase_price);对称差
两个集合A,B的对称差是指那些仅属于A或仅属于B的元素构成的集合. 对称差也是个非常基础的运算, 例如, 两个集合的交就可以看作是两个集合的并去掉两个集合的对称差.上述方法在其他数据库里也可以用来简单地实现表或查询结果的对称差运算: 首先使用UNION求两个表的并集, 然后使用INTERSECT求两个表的交集, 然后用并集减去交集, 就得到了对称差。
但由于在MySQL 8.0 里, 由于两个表或查询结果的并不能直接求出来, 因此并不适合使用上述思路来求对称差. 好在还有差集运算可以使用. 从直观上就能看出来, 两个集合的对称差等于 A-B并上B-A, 因此实践中可以用这个思路来求对称差。
使用product表和product2表的对称差来查询哪些商品只在其中一张表, 结果类似于:
-- 使用 NOT IN 实现两个表的差集
SELECT *
FROM product
WHERE product_id NOT IN (SELECT product_id FROM product2)
UNION
SELECT *
FROM product2
WHERE product_id NOT IN (SELECT product_id FROM product);JOIN
UNION和INTERSECT等集合运算的特征就是以行方向为单位进行操作. 通俗地说, 就是进行这些集合运算时, 会导致记录行数的增减. 使用 UNION 会增加记录行数,而使用 INTERSECT 或者 EXCEPT 会减少记录行数。
但这些运算不能改变列的变化, 虽然使用函数或者 CASE表达式等列运算, 可以增加列的数量, 但仍然只能从一张表中提供的基础信息列中获得一些”引申列”, 本质上并不能提供更多的信息. 如果想要从多个表获取信息, 例如, 如果我们想要找出某个商店里的衣服类商品的名称,数量及价格等信息, 则必须分别从 shopproduct 表和 product 表获取信息。
连结(JOIN)就是使用某种关联条件(一般是使用相等判断谓词”=”), 将其他表中的列添加过来, 进行“添加列”的集合运算. 可以说,连结是 SQL 查询的核心操作, 掌握了连结, 能够从两张甚至多张表中获取列, 能够将过去使用关联子查询等过于复杂的查询简化为更加易读的形式, 以及进行一些更加复杂的查询。
内连结 INNER JOIN
-- 内连结
FROM <tb_1> INNER JOIN <tb_2> ON <condition(s)>找出商店里的衣服类商品的商品名称,商品价格,商品种类,商品数量信息。
我们把上述问题进行分解:
首先, 找出每个商店的商店编号, 商店名称, 商品编号, 商品名称, 商品类别, 商品售价,商品数量信息。
按照内连结的语法, 在 FROM 子句中使用 INNER JOIN 将两张表连接起来, 并为 ON 子句指定连结条件为 shopproduct.product_id=product.product_id, 就得到了如下的查询语句:
SELECT SP.shop_id
,SP.shop_name
,SP.product_id
,P.product_name
,P.product_type
,P.sale_price
,SP.quantity
FROM shopproduct AS SP
INNER JOIN product AS P
ON SP.product_id = P.product_id;在上述查询中, 我们分别为两张表指定了简单的别名, 这种操作在使用连结时是非常常见的, 通过别名会让我们在编写查询时少打很多字, 并且更重要的是, 会让查询语句看起来更加简洁。
观察查询结果, 我们看到,这个结果里的列已经包含了所有我们需要的信息。
关于内连结,需要注意以下三点:
要点一: 进行连结时需要在 FROM 子句中使用多张表.
之前的 FROM 子句中只有一张表, 而这次我们同时使用了 shopproduct 和 product 两张表,使用关键字 INNER JOIN 就可以将两张表连结在一起了:
FROM shopproduct AS SP INNER JOIN product AS P要点二:必须使用 ON 子句来指定连结条件.
在进行内连结时 ON 子句是必不可少的(大家可以试试去掉上述查询的 ON 子句后会有什么结果)。
ON 子句是专门用来指定连结条件的, 我们在上述查询的 ON 之后指定两张表连结所使用的列以及比较条件, 基本上, 它能起到与 WHERE 相同的筛选作用, 我们会在本章的结尾部分进一步探讨这个话题。
要点三: SELECT 子句中的列最好按照 表名.列名 的格式来使用。
当两张表的列除了用于关联的列之外, 没有名称相同的列的时候, 也可以不写表名, 但表名使得我们能够在今后的任何时间阅读查询代码的时候, 都能马上看出每一列来自于哪张表, 能够节省我们很多时间。
但是, 如果两张表有其他名称相同的列, 则必须使用上述格式来选择列名, 否则查询语句会报错。
结合 WHERE
如果需要在使用内连结的时候同时使用 WHERE 子句对检索结果进行筛选, 则需要把 WHERE 子句写在 ON 子句的后边。
第一种增加 WEHRE 子句的方式, 就是把上述查询作为子查询, 用括号封装起来, 然后在外层查询增加筛选条件。
SELECT *
FROM (-- 第一步查询的结果
SELECT SP.shop_id
,SP.shop_name
,SP.product_id
,P.product_name
,P.product_type
,P.sale_price
,SP.quantity
FROM shopproduct AS SP
INNER JOIN product AS P
ON SP.product_id = P.product_id) AS STEP1
WHERE shop_name = '东京'
AND product_type = '衣服';还记得我们学习子查询时的认识吗? 子查询的结果其实也是一张表,只不过是一张虚拟的表, 它并不真实存在于数据库中, 只是数据库中其他表经过筛选,聚合等查询操作后得到的一个”视图”。
这种写法能很清晰地分辨出每一个操作步骤, 在我们还不十分熟悉 SQL 查询每一个子句的执行顺序的时候可以帮到我们。
但实际上, 如果我们熟知 WHERE 子句将在 FROM 子句之后执行, 也就是说, 在做完 INNER JOIN … ON 得到一个新表后, 才会执行 WHERE 子句, 那么就得到标准的写法:
SELECT SP.shop_id
,SP.shop_name
,SP.product_id
,P.product_name
,P.product_type
,P.sale_price
,SP.quantity
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 子句选出了那些我们需要的列。
此外, 一种不是很常见的做法是,还可以将 WHERE 子句中的条件直接添加在 ON 子句中, 这时候 ON 子句后最好用括号将连结条件和筛选条件括起来。
SELECT SP.shop_id
,SP.shop_name
,SP.product_id
,P.product_name
,P.product_type
,P.sale_price
,SP.quantity
FROM shopproduct AS SP
INNER JOIN product AS P
ON (SP.product_id = P.product_id
AND SP.shop_name = '东京'
AND P.product_type = '衣服');但上述这种把筛选条件和连结条件都放在 ON 子句的写法, 不是太容易阅读, 不建议大家使用。
另外, 先连结再筛选的标准写法的执行顺序是, 两张完整的表做了连结之后再做筛选,如果要连结多张表, 或者需要做的筛选比较复杂时, 在写 SQL 查询时会感觉比较吃力. 在结合 WHERE 子句使用内连结的时候, 我们也可以更改任务顺序, 并采用任务分解的方法,先分别在两个表使用 WHERE 进行筛选,然后把上述两个子查询连结起来。
SELECT SP.shop_id
,SP.shop_name
,SP.product_id
,P.product_name
,P.product_type
,P.sale_price
,SP.quantity
FROM (-- 子查询 1:从 shopproduct 表筛选出东京商店的信息
SELECT *
FROM shopproduct
WHERE shop_name = '东京' ) AS SP
INNER JOIN -- 子查询 2:从 product 表筛选出衣服类商品的信息
(SELECT *
FROM product
WHERE product_type = '衣服') AS P
ON SP.product_id = P.product_id;先分别在两张表里做筛选, 把复杂的筛选条件按表分拆, 然后把筛选结果(作为表)连接起来, 避免了写复杂的筛选条件, 因此这种看似复杂的写法, 实际上整体的逻辑反而非常清晰. 在写查询的过程中, 首先要按照最便于自己理解的方式来写, 先把问题解决了, 再思考优化的问题。
结合 GROUP BY
结合 GROUP BY 子句使用内连结, 需要根据分组列位于哪个表区别对待。
最简单的情形, 是在内连结之前就使用 GROUP BY 子句.
但是如果分组列和被聚合的列不在同一张表, 且二者都未被用于连结两张表, 则只能先连结, 再聚合。
每个商店中, 售价最高的商品的售价分别是多少?
-- 参考答案
SELECT SP.shop_id
,SP.shop_name
,MAX(P.sale_price) AS max_price
FROM shopproduct AS SP
INNER JOIN product AS P
ON SP.product_id = P.product_id
GROUP BY SP.shop_id,SP.shop_name;自连结(SELF JOIN)
之前的内连结, 连结的都是不一样的两个表。但实际上一张表也可以与自身作连结, 这种连接称之为自连结. 需要注意, 自连结并不是区分于内连结和外连结的第三种连结, 自连结可以是外连结也可以是内连结, 它是不同于内连结外连结的另一个连结的分类方法。
内连结与关联子查询
SELECT P1.product_id
,P1.product_name
,P1.product_type
,P1.sale_price
,P2.avg_price
FROM product AS P1
INNER JOIN
(SELECT product_type,AVG(sale_price) AS avg_price
FROM product
GROUP BY product_type) AS P2
ON P1.product_type = P2.product_type
WHERE P1.sale_price > P2.avg_price;仅仅从代码量上来看, 上述方法似乎比关联子查询更加复杂, 但这并不意味着这些代码更难理解. 通过上述分析, 很容易发现上述代码的逻辑实际上更符合我们的思路, 因此尽管看起来复杂, 但思路实际上更加清晰。
自然连结(NATURAL JOIN)
自然连结并不是区别于内连结和外连结的第三种连结, 它其实是内连结的一种特例–当两个表进行自然连结时, 会按照两个表中都包含的列名来进行等值内连结, 此时无需使用 ON 来指定连接条件。
SELECT * FROM shopproduct NATURAL JOIN product上述查询得到的结果, 会把两个表的公共列(这里是 product_id, 可以有多个公共列)放在第一列, 然后按照两个表的顺序和表中列的顺序, 将两个表中的其他列都罗列出来。
与上述自然连结等价的内连结：
-- 参考答案
SELECT SP.product_id,SP.shop_id,SP.shop_name,SP.quantity
,P.product_name,P.product_type,P.sale_price
,P.purchase_price,P.regist_date
FROM shopproduct AS SP
INNER JOIN product AS P
ON SP.product_id = P.product_id;使用自然连结还可以求出两张表或子查询的公共部分：
SELECT * FROM product NATURAL JOIN product2;使用连结求交集
使用内连结求 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);SELECT P1.*
FROM product AS P1
INNER JOIN product2 AS P2
ON P1.product_id = P2.product_id;外连结 OUTER JOIN
内连结会丢弃两张表中不满足 ON 条件的行,和内连结相对的就是外连结. 外连结会根据外连结的种类有选择地保留无法匹配到的行。
按照保留的行位于哪张表,外连结有三种形式: 左连结, 右连结和全外连结。
左连结会保存左表中无法按照 ON 子句匹配到的行, 此时对应右表的行均为缺失值; 右连结则会保存右表中无法按照 ON 子句匹配到的行, 此时对应左表的行均为缺失值; 而全外连结则会同时保存两个表中无法按照 ON子句匹配到的行, 相应的另一张表中的行用缺失值填充。
三种外连结的对应语法分别为:
-- 左连结
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)>左连结与右连结
由于连结时可以交换左表和右表的位置, 因此左连结和右连结并没有本质区别.接下来我们先以左连结为例进行学习. 所有的内容在调换两个表的前后位置, 并将左连结改为右连结之后, 都能得到相同的结果. 稍后再介绍全外连结的概念。
使用左连结从两个表获取信息
使用左连结的代码如下:
SELECT SP.shop_id
,SP.shop_name
,SP.product_id
,P.product_name
,P.sale_price
FROM product AS P
LEFT OUTER JOIN shopproduct AS SP
ON SP.product_id = P.product_id;● 外连结要点 1: 选取出单张表中全部的信息
与内连结的结果相比,不同点显而易见,那就是结果的行数不一样.内连结的结果中有 13 条记录,而外连结的结果中有 15 条记录,增加的 2 条记录到底是什么呢?这正是外连结的关键点. 多出的 2 条记录是高压锅和圆珠笔,这 2 条记录在 shopproduct 表中并不存在,也就是说,这 2 种商品在任何商店中都没有销售.由于内连结只能选取出同时存在于两张表中的数据,因此只在 product 表中存在的 2 种商品并没有出现在结果之中.相反,对于外连结来说,只要数据存在于某一张表当中,就能够读取出来.在实际的业务中,例如想要生成固定行数的单据时,就需要使用外连结.如果使用内连结的话,根据 SELECT 语句执行时商店库存状况的不同,结果的行数也会发生改变,生成的单据的版式也会受到影响,而使用外连结能够得到固定行数的结果.虽说如此,那些表中不存在的信息我们还是无法得到,结果中高压锅和圆珠笔的商店编号和商店名称都是 NULL （具体信息大家都不知道,真是无可奈何）.外连结名称的由来也跟 NULL 有关,即“结果中包含原表中不存在（在原表之外）的信息”.相反,只包含表内信息的连结也就被称为内连结了。
● 外连结要点 2：使用 LEFT、RIGHT 来指定主表.
外连结还有一点非常重要,那就是要把哪张表作为主表.最终的结果中会包含主表内所有的数据.指定主表的关键字是 LEFT 和 RIGHT.顾名思义,使用 LEFT 时 FROM 子句中写在左侧的表是主表,使用 RIGHT 时右侧的表是主表.代码清单 7-11 中使用了 RIGHT ,因此,右侧的表,也就是 product 表是主表.我们还可以像代码清单 7-12 这样进行改写,意思完全相同.这样你可能会困惑，到底应该使用 LEFT 还是 RIGHT？其实它们的功能没有任何区别,使用哪一个都可以.通常使用 LEFT 的情况会多一些,但也并没有非使用这个不可的理由,使用 RIGHT 也没有问题。
通过交换两个表的顺序, 同时将 LEFT 更换为 RIGHT(如果原先是 RIGHT,则更换为 LEFT), 两种方式会到完全相同的结果。
结合 WHERE 左连结
使用外连结从shopproduct表和product表中找出那些在某个商店库存少于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 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;全外连结
有了对左连结和右连结的了解, 就不难理解全外连结的含义了. 全外连结本质上就是对左表和右表的所有行都予以保留, 能用 ON 关联到的就把左表和右表的内容在一行内显示, 不能被关联到的就分别显示, 然后把多余的列用缺失值填充。
遗憾的是, MySQL8.0 目前还不支持全外连结, 不过我们可以对左连结和右连结的结果进行 UNION 来实现全外连结。
多表连结
通常连结只涉及 2 张表,但有时也会出现必须同时连结 3 张以上的表的情况, 原则上连结表的数量并没有限制。
多表进行内连结
接下来, 我们根据上表及 shopproduct 表和 product 表, 使用内连接找出每个商店都有那些商品, 每种商品的库存总量分别是多少。
SELECT SP.shop_id
,SP.shop_name
,SP.product_id
,P.product_name
,P.sale_price
,IP.inventory_quantity
FROM shopproduct AS SP
INNER JOIN product AS P
ON SP.product_id = P.product_id
INNER JOIN Inventoryproduct AS IP
ON SP.product_id = IP.product_id
WHERE IP.inventory_id = 'P001';多表进行外连结
SELECT P.product_id
,P.product_name
,P.sale_price
,SP.shop_id
,SP.shop_name
,IP.inventory_quantity
FROM product AS P
LEFT OUTER JOIN shopproduct AS SP
ON SP.product_id = P.product_id
LEFT OUTER JOIN Inventoryproduct AS IP
ON SP.product_id = IP.product_id;ON子句进阶–非等值连结
在刚开始介绍连结的时候, 书上提到过, 除了使用相等判断的等值连结, 也可以使用比较运算符来进行连接. 实际上, 包括比较运算符(<,<=,>,>=, BETWEEN)和谓词运算(LIKE, IN, NOT 等等)在内的所有的逻辑运算都可以放在 ON 子句内作为连结条件。
非等值自左连结 LEFT JOIN
希望对 product 表中的商品按照售价赋予排名. 一个从集合论出发,使用自左连结的思路是, 对每一种商品,找出售价不低于它的所有商品, 然后对售价不低于它的商品使用 COUNT 函数计数. 例如, 对于价格最高的商品,
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; 注 1: COUNT 函数的参数是列名时, 会忽略该列中的缺失值, 参数为 * 时则不忽略缺失值。
注 2: 上述排名方案存在一些问题–如果两个商品的价格相等, 则会导致两个商品的排名错误, 例如, 叉子和打孔器的排名应该都是第六, 但上述查询导致二者排名都是第七. 试修改上述查询使得二者的排名均为第六。
注 3: 实际上, 进行排名有专门的函数, 这是 MySQL 8.0 新增加的窗口函数中的一种(窗口函数将在下一章学习), 但在较低版本的 MySQL 中只能使用上述自左连结的思路。
交叉连结 CROSS JOIN(笛卡尔积)
之前的无论是外连结内连结, 一个共同的必备条件就是连结条件–ON 子句, 用来指定连结的条件. 如果你试过不使用这个连结条件的连结查询, 你可能已经发现, 结果会有很多行. 在连结去掉 ON 子句, 就是所谓的交叉连结(CROSS JOIN), 交叉连结又叫笛卡尔积, 后者是一个数学术语. 两个集合做笛卡尔积, 就是使用集合 A 中的每一个元素与集合 B 中的每一个元素组成一个有序的组合. 数据库表(或者子查询)的并,交和差都是在纵向上对表进行扩张或筛选限制等运算的, 这要求表的列数及对应位置的列的数据类型”相容”, 因此这些运算并不会增加新的列, 而交叉连接(笛卡尔积)则是在横向上对表进行扩张, 即增加新的列, 这一点和连结的功能是一致的. 但因为没有了ON子句的限制, 会对左表和右表的每一行进行组合, 这经常会导致很多无意义的行出现在检索结果中. 当然, 在某些查询需求中, 交叉连结也有一些用处。
交叉连结的语法有如下几种形式:
-- 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;结果中的记录数通常是两张表中行数的乘积
交叉连结没有应用到实际业务之中的原因有两个.一是其结果没有实用价值,二是由于其结果行数太多,需要花费大量的运算时间和高性能设备的支持。
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; 得到的结果是:
我们先忽略生成的新列 - [ranking]， 看下原始数据在PARTITION BY 和 ORDER BY 关键字的作用下发生了什么变化。
PARTITION BY 能够设定窗口对象范围。本例中，为了按照商品种类进行排序，我们指定了product_type。即一个商品种类就是一个小的”窗口”。
ORDER BY 能够指定按照哪一列、何种顺序进行排序。为了按照销售单价的升序进行排列，我们指定了sale_price。此外，窗口函数中的ORDER BY与SELECT语句末尾的ORDER BY一样，可以通过关键字ASC/DESC来指定升序/降序。省略该关键字时会默认按照ASC，也就是
升序进行排序中就省略了上述关键字。
窗口函数种类
大致来说，窗口函数可以分为两类。
一是 将SUM、MAX、MIN等聚合函数用在窗口函数中
二是 RANK、DENSE_RANK等排序用的专用窗口函数
专用窗口函数
- RANK函数
计算排序时，如果存在相同位次的记录，则会跳过之后的位次。
例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、4 位…… - DENSE_RANK函数
同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。
例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、2 位…… - 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; 
可以看出，聚合函数结果是，按我们指定的排序，这里是product_id，**当前所在行及之前所有的行**的合计或均值。即累计到当前行的聚合。
窗口函数的的应用
计算移动平均
在上面提到，聚合函数在窗口函数使用时，计算的是累积到当前行的所有的数据的聚合。 实际上，还可以指定更加详细的汇总范围。该汇总范围称为 框架 (frame)。
语法
<窗口函数> OVER (ORDER BY <排序用列名>
ROWS n PRECEDING )
<窗口函数> OVER (ORDER BY <排序用列名>
ROWS BETWEEN n PRECEDING AND n FOLLOWING)PRECEDING（“之前”）， 将框架指定为 “截止到之前 n 行”，加上自身行
FOLLOWING（“之后”）， 将框架指定为 “截止到之后 n 行”，加上自身行
SELECT product_id
,product_name
,sale_price
,AVG(sale_price) OVER (ORDER BY product_id
ROWS 2 PRECEDING) AS moving_avg
FROM product; 注意观察框架的范围。
BETWEEN 1 PRECEDING AND 1 FOLLOWING，将框架指定为 “之前1行” + “之后1行” + “自身”
SELECT product_id
,product_name
,sale_price
,AVG(sale_price) OVER (ORDER BY product_id
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg
FROM product; 
- 原则上，窗口函数只能在SELECT子句中使用。
- 窗口函数OVER 中的ORDER BY 子句并不会影响最终结果的排序。其只是用来决定窗口函数按何种顺序计算。
GROUPING运算符
ROLLUP 合计及小计
常规的GROUP BY 只能得到每个分类的小计，有时候还需要计算分类的合计，可以用 ROLLUP关键字。
SELECT product_type
,regist_date
,SUM(sale_price) AS sum_price
FROM product
GROUP BY product_type, regist_date WITH ROLLUP; 
这里ROLLUP 对product_type, regist_date两列进行合计汇总。结果实际上有三层聚合，如下图 模块3是常规的 GROUP BY 的结果，需要注意的是衣服 有个注册日期为空的，这是本来数据就存在日期为空的，不是对衣服类别的合计； 模块2和1是 ROLLUP 带来的合计，模块2是对产品种类的合计，模块1是对全部数据的总计。
