主键（PRIMARY KEY）的理解
DROP TABLE IF EXISTS shopproduct;
CREATE TABLE shopproduct
( shop_id CHAR(4) NOT NULL,
shop_name VARCHAR(200) NOT NULL,
product_id CHAR(4) NOT NULL,
quantity INTEGER NOT NULL,
PRIMARY KEY (shop_id, product_id) -- 指定主键
);
这种主键里有两个列时叫组合键，用来唯一确定每一行数据，意思是这两种变量的各个水平组合只能出现一次（因为单独使用商店编号（shop_id）或者商品编号（product_id）不能区分表中每一行数据）
视图
视图是一个虚拟的表，是依据SELECT语句来创建的
通过定义视图可以将频繁使用的SELECT语句保存以提高效率。
通过定义视图可以使用户看到的数据更加清晰。
通过定义视图可以不对外公开数据表全部字段，增强数据的保密性。
通过定义视图可以降低数据的冗余。
视图名在数据库中需要是唯一的，不能与其他视图和表重名
视图不仅可以基于真实表，我们也可以在视图的基础上继续创建视图。
视图和表一样，数据行都是没有顺序的。定义视图时不能使用ORDER BY语句
示例
--基于单表的视图
CREATE VIEW productsum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type ;
--基于多表的视图
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 stu_name
FROM (
SELECT stu_name, COUNT(*) AS stu_cnt
FROM students_info
GROUP BY stu_age) AS studentSum;
子查询指一个查询语句嵌套在另一个查询语句内部的查询
其中AS studentSum可以看作是子查询的名称
由于子查询是一次性的，所以子查询不会像视图那样保存在存储介质中， 而是在 SELECT 语句执行之后就消失了
标量子查询
标量子查询也就是单一的子查询，要返回表中具体的某一行的某一列
那么我们执行一次标量子查询后是要返回类似于，“0004”，“菜刀”这样的结果。
示例
SELECT product_id, product_name, sale_price
FROM product
WHERE sale_price > (SELECT AVG(sale_price) FROM product);
在这里，标量子查询指的是下面的这个语句,因为它返回的是某一行的某一列,可以把其得到的结果看成单一的值
SELECT AVG(sale_price) FROM product
由于标量子查询的特性，导致标量子查询不仅仅局限于 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);
理解关联子查询需要理解关联子查询执行独特的过程逻辑
因为关联子查询的执行逻辑完全不同于正常的SELECT语句
1.首先执行不带where的主查询
2.从主查询的product _type先取第一个值='衣服'，通过WHERE P1.product_type = P2.product_type传入子查询，子查询变成
SELECT AVG(sale_price)
FROM Product AS P2
WHERE P2.product_type = '衣服'
GROUP BY product_type;
从子查询得到的结果AVG(sale_price)=2500，返回主查询
SELECT product_type , product_name, sale_price
FROM Product AS P1
WHERE sale_price > 2500 AND product_type = '衣服'
第一次整个语句的结果：
然后，product _type取第二个值，得到整个语句的第二结果，依次类推，把product _type全取值一遍，就得到了整个语句的结果集。
这就是关联子查询的执行过程
总结：
（1）先从主查询的Product表中product _type列取出第一个值，进入子查询中，得到子查询结果，然后返回父查询，判断父查询的where子句条件，则返回整个语句的第1条结果。
（2）重复上述操作，直到所有主查询中的Product表中product _type列记录取完为止。得出整个语句的结果集，就是最后的答案。
（来自https://zhuanlan.zhihu.com/p/41844742）
各种各样的SQL函数的理解
留意常用的函数以及其功能
谓词
谓词就是返回值为真值的函数，包括TRUE / FALSE / UNKNOWN。
1.LIKE谓词 – 用于字符串的部分一致查询
示例
--前方一致：选取出“dddabc”
SELECT *
FROM samplelike
WHERE strcol LIKE 'ddd%';
2.BETWEEN谓词 – 用于范围查询
--这里是闭区间（包括100和1000）
SELECT product_name, sale_price
FROM product
WHERE sale_price BETWEEN 100 AND 1000;
--这里是开区间（只能使用>和<运算符）
SELECT product_name, sale_price
FROM product
WHERE sale_price > 100
AND sale_price < 1000;
3.IS NULL、 IS NOT NULL – 用于判断是否为NULL
4.IN谓词 – OR的简便用法
SELECT product_name, purchase_price
FROM product
WHERE purchase_price IN (320, 500, 5000);
SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (320, 500, 5000);
需要注意的是，在使用IN 和 NOT IN 时是无法选取出NULL数据的。
实际结果也是如此，上述两组结果中都不包含进货单价为 NULL 的叉子和圆珠笔。 NULL 只能使用 IS NULL 和 IS NOT NULL 来进行判断。
5.使用子查询作为IN谓词的参数
也可以说<能够将视图作为 IN 的参数>
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)
子查询展开后
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 谓词也能实现，那为什么还要使用子查询呢？
1.实际生活中，某个门店的在售商品是不断变化的，使用 in 谓词就需要经常更新 sql 语句，降低了效率，提高了维护成本；
2.实际上，某个门店的在售商品可能有成百上千个，手工输入IN后面的编号的工作量不敢想象。用子查询作为IN后面的参数是绝佳的选择
因此，使用子查询即可保持 sql 语句不变，极大提高了程序的可维护性，这是系统开发中需要重点考虑的内容。
6.EXIST谓词
EXIST谓词的作用是 “判断是否存在满足某种条件的记录”。
如果存在这样的记录就返回真（TRUE），如果不存在就返回假（FALSE）。
EXIST（存在）谓词的主语是记录。
EXIST 只需要在右侧书写 1 个参数，该参数通常都会是一个子查询。
EXIST 通常会使用关联子查询作为参数。
由于 EXIST 只关心记录是否存在，因此返回哪些列都没有关系。 EXIST 只会判断是否存在满足子查询中 WHERE 子句指定的条件“商店编号（shop_id）为 ‘000C’，商品（product）表和商店商品（shopproduct）表中商品编号（product_id）相同”的记录，只有存在这样的记录时才返回真（TRUE）。
所以可以在 EXIST 的子查询中书写 SELECT* 当作 写SQL 的一种习惯。
就像 EXIST 可以用来替换 IN 一样， NOT IN 也可以用NOT EXIST来替换。
NOT EXIST 与 EXIST 相反，当“不存在”满足子查询中指定条件的记录时返回真（TRUE）。
CASE表达式
CASE 表达式是在区分情况时使用的，这种情况的区分在编程中通常称为（条件）分支。
可以将其类比成python中的if-elif-else语句
示例
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;
ELSE 子句也可以省略不写，这时会被默认为 ELSE NULL。但是建议写出ELSE子句
CASE 表达式最后的"END"是不能省略的
示例2：在列的方向上展示不同种类的聚合值
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;
练习题
--1.
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";
--2.
报错：underlying table doesn't have a default value
--3.标量子查询
SELECT product_id,product_name,product_type,sale_price,
(SELECT AVG(sale_price) FROM product) AS sale_price_all
FROM product;
--4.关联子查询
CREATE VIEW AvgPriceByType
(product_id,product_name,product_type,sale_price,avg_sale_price)
AS
SELECT product_id,product_name,product_type,sale_price,
(SELECT AVG(sale_price) FROM product AS p2
WHERE p1.product_type=p2.product_type GROUP BY product_type)
AS avg_sale_price
FROM product AS p1;
--验证结果
SELECT * FROM AvgPriceByType;
--5.
错误
转换函数COALESCE会将NULL转换为其他值
该函数会返回可变参数中左侧开始第 1个不是NULL的值。参数个数是可变的，因此可以根据需要无限增加。
示例：COALESCE(NULL, 'hello world', NULL) 在表中会返回hello world
②需要注意：在使用IN 和 NOT IN 时是无法选取出NULL数据的。
NULL 只能使用 IS NULL 和 IS NOT NULL 来进行判断。
--7.
SELECT COUNT(CASE WHEN sale_price<=1000 THEN product_name END) AS low_price,
COUNT(CASE WHEN sale_price>=1001 AND sale_price<=3000 THEN product_name END) AS mid_price,
COUNT(CASE WHEN sale_price>=3001 THEN product_name END) AS high_price
FROM product;
