SQL那些事

前言

本博客为Datawhale第39期《SQL编程语言》打卡学习笔记。
开源教程地址：https://github.com/datawhalechina/wonderful-sql。

task01：初识数据库

1.1 编写一条 CREATE TABLE 语句，用来创建一个如下表所列项的 Addressbook （地址簿）表，并为 regist_no （注册编号）列设置主键约束

CREATE TABLE Addressbook 
(regist_no    INTEGER      NOT NULL,
name   VARCHAR(128) NOT NULL,	
address   VARCHAR(256)  NOT NULL,
tel_no     CHAR(10) ,
mail_address    CHAR(20) ,
PRIMARY KEY (regist_no)); 

知识点：
建表（CREATE TABLE）语法：

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

1.2 向 Addressbook 中加入 postal_code列

ALTER TABLE Addressbook ADD COLUMN postal_code CHAR(8) NOT NULL;

知识点：
表中添加列（ALTER TABLE）的语法：

ALTER TABLE < 表名 > ADD COLUMN < 列的定义 >;

1.2 向 Addressbook 中加入 postal_code列

ALTER TABLE Addressbook ADD COLUMN postal_code CHAR(8) NOT NULL;

知识点：
表中添加列（ALTER TABLE + ADD COLUMN）的语法：

ALTER TABLE < 表名 > ADD COLUMN < 列的定义 >;

表中删除列（ ALTER TABLE + DROP COLUMN）的语法：

ALTER TABLE < 表名 > DROP COLUMN < 列名 >;

1.3 删除 Addressbook 表

DROP table Addressbook;

知识点：
删表语法：

DROP TABLE < 表名 > ;

1.4 是否是否可以编写 SQL 语句来恢复删除掉的 Addressbook 表

答：不可以，所以备份很重要。

task02：基础查询与排序

用到的表 product ：

2.1 编写一条SQL语句，从 product(商品) 表中选取出“登记日期(regist_date)在2009年4月28日之后”的商品，查询结果要包含 product name 和 regist_date 两列

SELECT product_name,regist_date
FROM product
WHERE regist_date>'2009-04-28';

知识点：
查表语法（最最最最基础的知识了）：

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

2.2 如下3条SELECT语句时的返回结果

①

SELECT * FROM product
WHERE purchase_price = NULL;

②

 SELECT * FROM product
 WHERE purchase_price <> NULL;

③

 SELECT * FROM product
 WHERE purchase_price > NULL;

知识点：
(1) SELECT 如果接星号（*）代表选择全部列；
(2) SQL语句支持四则运算和比较运算，<> 代表“和 ~ 不相等”，类似于NOT。

2.3 从 product 表中取出“销售单价（sale_price）比进货单价（purchase_price）高出500日元以上”的商品。请写出两条可以得到相同结果的SELECT语句。执行结果如下所示：

--第一种：
SELECT product_name,sale_price,purchase_price 
FROM product
WHERE sale_price-purchase_price >= 500;
--第二种：
SELECT product_name,sale_price,purchase_price 
FROM product
WHERE sale_price >= purchase_price + 500;

2.4 请写出一条SELECT语句，从 product 表中选取出满足“销售单价打九折之后利润高于 100 日元的办公用品和厨房用具”条件的记录。查询结果要包括 product_name列、product_type 列以及销售单价打九折之后的利润（别名设定为 profit）。

SELECT product_name,product_type, sale_price*0.9-purchase_price as profit
FROM product
WHERE sale_price*0.9-purchase_price >= 100
AND (product_type = '办公用品' OR product_type = '厨房用具' );

知识点：
AND 运算符优先于 OR 运算符 ，想要优先执行OR运算，需要使用 括号 。

2.5 请指出下述SELECT语句中所有的语法错误。

SELECT product_id, SUM(product_name)
FROM product 
GROUP BY product_type 
WHERE regist_date > '2009-09-01';

错误 ①：字符型字段 product_name 不可以进行 SUM 聚合。
错误 ②：WHERE 语句应该书写在 GROUP BY 语句之前（ FROM 语句之后），或者使用HAVING。
错误 ③：GROUP BY 字段（product_type）与 SELECT 字段不同（product_id）。
知识点：
(1) MAX / MIN 函数适用于文本类型和数字类型的列，而 SUM / AVG 函数仅适用于数字类型的列；
(2) SQL语句的执行顺序：FROM → WHERE → GROUP BY → SELECT → HAVING → ORDER BY；
(3) 在聚合函数的SELECT子句中写了聚合键以外的列使用COUNT等聚合函数时，SELECT子句中如果出现列名，只能是GROUP BY子句中指定的列名（也就是聚合键）。

2.6 请编写一条SELECT语句，求出销售单价（ sale_price 列）合计值大于进货单价（ purchase_price 列）合计值1.5倍的商品种类。

SELECT product_type , SUM(sale_price) , SUM(purchase_price) 
FROM product
GROUP BY product_type
HAVING SUM(sale_price) > 1.5*SUM(purchase_price);

知识点：
（1）HAVING子句用于对分组进行过滤，可以使用常数、聚合函数和GROUP BY中指定的列名（聚合键）。
（2）HAVING 子句必须与 GROUP BY 子句配合使用，且限定的是分组聚合结果.

2.7 请编写一条SELECT语句，输出如下所示的表。

观察数据可知，该表格是先根据regist_date排序(降序)，再对同一regist_date中的purchase_price排序(升序)。

SELECT * FROM product
ORDER BY - regist_date ASC,purchase_price ASC;

知识点：
（1）ASC：升序；DESC：降序。
（2）在MySQL中，NULL 值被认为比任何 非NULL 值低，因此在降序的条件下想让它放到第一行，需要进行特殊处理，这里就是在排序字段前添加一个负号（minus）来得到反向排序（NULL位置不受影响）。
（3）由于SQL语句的执行顺序（2.5(2)），ORDER BY语句可以使用SELECT子句中定义的别名。
（4）现阶段所有关键词的书写顺序：SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY。

task03：复杂一(亿)点的查询

这节知识点比较重要，推荐把官方文档好好过一遍。

3.1 使用 product（商品）表作为参照表，创建出满足下述三个条件的视图（视图名称为 ViewPractice5_1）。

• 条件 1：销售单价大于等于 1000 日元。
• 条件 2：登记日期是 2009 年 9 月 20 日。
• 条件 3：包含商品名称、销售单价和登记日期三列。

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');

3.2 向习题一中创建的视图 ViewPractice5_1 中插入如下数据，会得到什么样的结果？

INSERT INTO ViewPractice5_1 VALUES (' 刀子 ', 300, '2009-11-02');

报错：Field of view ‘shop.viewpractice5_1’ underlying table doesn’t have a default value。
原因分析：视图归根结底还是从表派生出来的窗口，当向视图中插入数据时，同时也会向原表（product）插入数据插入数据 ，而原表存在多个字段不允许为空，所以无法插入。

3.3 请根据如下结果编写 SELECT 语句，其中 sale_price_avg 列为全部商品的平均销售单价。

SELECT product_id,product_name,product_type,sale_price,
		(SELECT  AVG(sale_price) FROM product) AS sale_price_avg
FROM product;

第一次做的时候，本能的把 AVG(sale_price) 同另外几列放到了一起，但是执行时候会报“In aggregated query without GROUP BY…”的错误，网上其实给了一个解决方案：修改下sql_mode。只是这种写法确实不太规范，这里还是用一下子查询，将两个结果合并起来即可解决。

3.4 请根据习题一中的条件编写一条 SQL 语句，创建一幅包含如下数据的视图（名称为AvgPriceByType）

顺着3.3的思路写下来的：

CREATE VIEW AvgPriceByType (product_id, product_name, product_type, sale_price, sale_price_avg_type)
AS
(
SELECT product_id, product_name, product_type, sale_price, 
	(SELECT AVG(sale_price) 
		FROM product p2
	WHERE p1.product_type = p2.product_type
	GROUP BY p1.product_type) AS sale_price_avg_type
FROM product p1
);

自己跟自己连接，然后再进行GROUP操作，我自己确实想不到，跪了。

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

是的。

3.6 对本章中使用的 product（商品）表执行如下 2 条 SELECT 语句，能够得到什么样的结果呢？

①

SELECT product_name, purchase_price
  FROM product
 WHERE purchase_price NOT IN (500, 2800, 5000);

②

SELECT product_name, purchase_price
  FROM product
 WHERE purchase_price NOT IN (500, 2800, 5000, NULL);

知识点：
(1) 谓词无法与 NULL 进行比较
(2) NOT IN 的参数中不能包含 NULL，否则，查询结果通常为空。

3.7 按照销售单价( sale_price )对 product（商品）表中的商品进行如下分类。

• 低档商品：销售单价在1000日元以下（T恤衫、办公用品、叉子、擦菜板、 圆珠笔）
• 中档商品：销售单价在1001日元以上3000日元以下（菜刀）
• 高档商品：销售单价在3001日元以上（运动T恤、高压锅）

请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句:

SELECT 
    SUM(CASE WHEN sale_price <= 1000 THEN 1 ELSE 0 END)               AS low_price,
    SUM(CASE WHEN sale_price BETWEEN 1001 AND 3000 THEN 1 ELSE 0 END) AS mid_price,
    SUM(CASE WHEN sale_price >= 3001 THEN 1 ELSE 0 END)               AS high_price
FROM product;

知识点： CASE 表达式

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