Task03：复杂一点的查询（二）

教程地址

https://github.com/datawhalechina/wonderful-sql
https://gitee.com/datawhalechina/wonderful-sql

3. 各种各样的函数

mysql> show tables;
+-------------------+
| Tables_in_shop    |
+-------------------+
| ViewPractice5_1   |
| ViewPractice5_2   |
| chars             |
| empskills         |
| inventoryproduct  |
| product           |
| product2          |
| productcopy       |
| productins        |
| productsum        |
| producttype       |
| samplelike        |
| samplemath        |
| samplestr         |
| shop_product      |
| shopproduct       |
| skills            |
| user              |
| view_product      |
| view_shop_product |
+-------------------+
20 rows in set (0.01 sec)

mysql> select * from samplemath;
+----------+------+------+
| m        | n    | p    |
+----------+------+------+
|  500.000 |    0 | NULL |
| -180.000 |    0 | NULL |
|     NULL | NULL | NULL |
|     NULL |    7 |    3 |
|     NULL |    5 |    2 |
|     NULL |    4 | NULL |
|    8.000 | NULL |    3 |
|    2.270 |    1 | NULL |
|    5.555 |    2 | NULL |
|     NULL |    1 | NULL |
|    8.760 | NULL | NULL |
+----------+------+------+
11 rows in set (0.00 sec)

先看一下表，已经有了，那就不用再建了

3.1 算术函数

3.1.1 绝对值

先来求个绝对值

mysql> select m, abs(m) as abs_col from samplemath;
+----------+---------+
| m        | abs_col |
+----------+---------+
|  500.000 | 500.000 |
| -180.000 | 180.000 |
|     NULL |    NULL |
|     NULL |    NULL |
|     NULL |    NULL |
|     NULL |    NULL |
|    8.000 |   8.000 |
|    2.270 |   2.270 |
|    5.555 |   5.555 |
|     NULL |    NULL |
|    8.760 |   8.760 |
+----------+---------+
11 rows in set (0.02 sec)

null 的绝对值还是 null

3.1.2 求余

再来做一下求余

mysql> select n, p, mod(n,p) as mod_col from samplemath;
+------+------+---------+
| n    | p    | mod_col |
+------+------+---------+
|    0 | NULL |    NULL |
|    0 | NULL |    NULL |
| NULL | NULL |    NULL |
|    7 |    3 |       1 |
|    5 |    2 |       1 |
|    4 | NULL |    NULL |
| NULL |    3 |    NULL |
|    1 | NULL |    NULL |
|    2 | NULL |    NULL |
|    1 | NULL |    NULL |
| NULL | NULL |    NULL |
+------+------+---------+
11 rows in set (0.00 sec)

小数不能进行求余数，该操作只对整数有效

需要我们注意的是，SQL Server 是使用 % 来进行求余计算的，不支持 mod 函数

3.1.3 四舍五入

接下来是四舍五入的操作，我们保留一位小数来看看

mysql> select m, round(m,1) as round_col from samplemath;
+----------+-----------+
| m        | round_col |
+----------+-----------+
|  500.000 |     500.0 |
| -180.000 |    -180.0 |
|     NULL |      NULL |
|     NULL |      NULL |
|     NULL |      NULL |
|     NULL |      NULL |
|    8.000 |       8.0 |
|    2.270 |       2.3 |
|    5.555 |       5.6 |
|     NULL |      NULL |
|    8.760 |       8.8 |
+----------+-----------+
11 rows in set (0.00 sec)

需要注意的是，我们最好直接使用数字来指定保留小数的位数

如果使用变量的话可能会有错误

3.2 字符串函数

之前已经建好表了

mysql> select * from samplestr;
+-----------+--------+--------+
| str1      | str2   | str3   |
+-----------+--------+--------+
| opx       | rt     | NULL   |
| abc       | def    | NULL   |
| 山田      | 太郎   | 是我   |
| aaa       | NULL   | NULL   |
| NULL      | xyz    | NULL   |
| @!#$%     | NULL   | NULL   |
| ABC       | NULL   | NULL   |
| aBC       | NULL   | NULL   |
| abc太郎   | abc    | ABC    |
| abcdefabc | abc    | ABC    |
| micmic    | i      | I      |
+-----------+--------+--------+
11 rows in set (0.00 sec)

3.2.1 拼接

先来看看 concat

mysql> select str1, str2, concat(str1,'-',str2) from samplestr;
+-----------+--------+-----------------------+
| str1      | str2   | concat(str1,'-',str2) |
+-----------+--------+-----------------------+
| opx       | rt     | opx-rt                |
| abc       | def    | abc-def               |
| 山田      | 太郎   | 山田-太郎             |
| aaa       | NULL   | NULL                  |
| NULL      | xyz    | NULL                  |
| @!#$%     | NULL   | NULL                  |
| ABC       | NULL   | NULL                  |
| aBC       | NULL   | NULL                  |
| abc太郎   | abc    | abc太郎-abc           |
| abcdefabc | abc    | abcdefabc-abc         |
| micmic    | i      | micmic-i              |
+-----------+--------+-----------------------+
11 rows in set (0.00 sec)

啊，对 null 无效啊，其他的都还好说，貌似最多只能连三列

3.2.2 长度

mysql> select str1, length(str1) as len_str1 from samplestr;
+-----------+----------+
| str1      | len_str1 |
+-----------+----------+
| opx       |        3 |
| abc       |        3 |
| 山田      |        6 |
| aaa       |        3 |
| NULL      |     NULL |
| @!#$%     |        5 |
| ABC       |        3 |
| aBC       |        3 |
| abc太郎   |        9 |
| abcdefabc |        9 |
| micmic    |        6 |
+-----------+----------+
11 rows in set (0.00 sec)

普普通通的一个测量长度，没什么好讲的

3.2.3 大小写转换

mysql> select str1, lower(str1) as lower_str1, upper(str1) as upper_str1 from samplestr;
+-----------+------------+------------+
| str1      | lower_str1 | upper_str1 |
+-----------+------------+------------+
| opx       | opx        | OPX        |
| abc       | abc        | ABC        |
| 山田      | 山田       | 山田       |
| aaa       | aaa        | AAA        |
| NULL      | NULL       | NULL       |
| @!#$%     | @!#$%      | @!#$%      |
| ABC       | abc        | ABC        |
| aBC       | abc        | ABC        |
| abc太郎   | abc太郎    | ABC太郎    |
| abcdefabc | abcdefabc  | ABCDEFABC  |
| micmic    | micmic     | MICMIC     |
+-----------+------------+------------+
11 rows in set (0.00 sec)

只对字母有效啊，而且有效范围是全部字母，包含被混杂在中文中的字母

3.2.4 替换

mysql> select str1, replace(str1,'a','Z') as replace_str1 from samplestr;
+-----------+--------------+
| str1      | replace_str1 |
+-----------+--------------+
| opx       | opx          |
| abc       | Zbc          |
| 山田      | 山田         |
| aaa       | ZZZ          |
| NULL      | NULL         |
| @!#$%     | @!#$%        |
| ABC       | ABC          |
| aBC       | ZBC          |
| abc太郎   | Zbc太郎      |
| abcdefabc | ZbcdefZbc    |
| micmic    | micmic       |
+-----------+--------------+
11 rows in set (0.00 sec)

我们把小写的 a 全部替换成大写的 Z，范围覆盖全部字母

3.2.5 截取

mysql> select str1, substring(str1,0,1), substring(str1,1,1) from samplestr;
+-----------+---------------------+---------------------+
| str1      | substring(str1,0,1) | substring(str1,1,1) |
+-----------+---------------------+---------------------+
| opx       |                     | o                   |
| abc       |                     | a                   |
| 山田      |                     | 山                  |
| aaa       |                     | a                   |
| NULL      | NULL                | NULL                |
| @!#$%     |                     | @                   |
| ABC       |                     | A                   |
| aBC       |                     | a                   |
| abc太郎   |                     | a                   |
| abcdefabc |                     | a                   |
| micmic    |                     | m                   |
+-----------+---------------------+---------------------+
11 rows in set (0.00 sec)

我们可以看到这个字符串的下标是从 1 开始的

第一个参数是字符串列，第二个参数是起点，第三个参数是长度

3.2.6 索引截取

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
+------------------------------------------+
| SUBSTRING_INDEX('www.mysql.com', '.', 2) |
+------------------------------------------+
| www.mysql                                |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> 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)

这个函数也挺神奇的，大概意思应该是先按照分隔符分割

然后分割结果的下标是从 1 开始的，然后根据长度获取分割结果

3.2.7 重复

mysql> select str1, repeat(str1,3) as repeat_str1 from samplestr;
+-----------+-----------------------------+
| str1      | repeat_str1                 |
+-----------+-----------------------------+
| opx       | opxopxopx                   |
| abc       | abcabcabc                   |
| 山田      | 山田山田山田                |
| aaa       | aaaaaaaaa                   |
| NULL      | NULL                        |
| @!#$%     | @!#$%@!#$%@!#$%             |
| ABC       | ABCABCABC                   |
| aBC       | aBCaBCaBC                   |
| abc太郎   | abc太郎abc太郎abc太郎       |
| abcdefabc | abcdefabcabcdefabcabcdefabc |
| micmic    | micmicmicmicmicmic          |
+-----------+-----------------------------+
11 rows in set (0.00 sec)

简单传个参数，规定好重复次数就可以了

3.3 日期函数

mysql> SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP;
+--------------+--------------+---------------------+
| CURRENT_DATE | CURRENT_TIME | CURRENT_TIMESTAMP   |
+--------------+--------------+---------------------+
| 2022-05-25   | 16:42:35     | 2022-05-25 16:42:35 |
+--------------+--------------+---------------------+
1 row in set (0.00 sec)

mysql> 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 |
+---------------------+------+-------+------+------+--------+--------+
| 2022-05-25 16:42:48 | 2022 |     5 |   25 |   16 |     42 |     48 |
+---------------------+------+-------+------+------+--------+--------+
1 row in set (0.01 sec)

mysql> 

current 前缀的相关函数可以获得当前的日期、时间以及时间戳

extract 函数是可以从时间戳当中分离出年份、月份、分钟、秒

3.4 转换函数

先来看一看类型转换

mysql> SELECT CAST('0001' AS SIGNED INTEGER) AS int_col,
    ->  CAST('2009-12-14' AS DATE) AS date_col;
+---------+------------+
| int_col | date_col   |
+---------+------------+
|       1 | 2009-12-14 |
+---------+------------+
1 row in set (0.00 sec)

这是直接用 cast 函数，标明类型就好

然后还有一个，转换 null 值的，我们之前也提到过，coalesce

mysql> select str3, coalesce(str3,'Zeta') from samplestr;
+--------+-----------------------+
| str3   | coalesce(str3,'Zeta') |
+--------+-----------------------+
| NULL   | Zeta                  |
| NULL   | Zeta                  |
| 是我   | 是我                  |
| NULL   | Zeta                  |
| NULL   | Zeta                  |
| NULL   | Zeta                  |
| NULL   | Zeta                  |
| NULL   | Zeta                  |
| ABC    | ABC                   |
| ABC    | ABC                   |
| I      | I                     |
+--------+-----------------------+
11 rows in set (0.00 sec)

4. 谓词

先来看看表，我们之前建好的

mysql> select * from samplelike;
+--------+
| strcol |
+--------+
| abcdd  |
| abcddd |
| abddc  |
| abdddc |
| ddabc  |
| dddabc |
+--------+
6 rows in set (0.00 sec)

mysql> 

谓词函数的返回值一般都是布尔型，例如 true、false、unknown

4.1 like

这个函数主要用于字符串的查询匹配

我们在这里使用 %，它代表零个或者多个任意字符

mysql> SELECT *
    -> FROM samplelike
    -> WHERE strcol LIKE 'd%';
+--------+
| strcol |
+--------+
| ddabc  |
| dddabc |
+--------+
2 rows in set (0.00 sec)

例如这里选取的是以字符 d 开头的行

我们还可以使用下划线 _ 来代表任意一个字符

mysql> SELECT *
    -> FROM samplelike
    -> WHERE strcol LIKE 'abdd_';
+--------+
| strcol |
+--------+
| abddc  |
+--------+
1 row in set (0.01 sec)

mysql> SELECT *
    -> FROM samplelike
    -> WHERE strcol LIKE 'abdd%';
+--------+
| strcol |
+--------+
| abddc  |
| abdddc |
+--------+
2 rows in set (0.01 sec)

4.2 between

那这个就是很显然的范围查询，包含临界值

mysql> -- 选取销售单价为100～ 1000元的商品
mysql> SELECT product_name, sale_price
    -> FROM product
    -> WHERE sale_price BETWEEN 100 AND 1000;
+--------------+------------+
| product_name | sale_price |
+--------------+------------+
| T恤          |       1000 |
| 打孔器       |        500 |
| 叉子         |        500 |
| 擦菜板       |        880 |
+--------------+------------+
4 rows in set (0.00 sec)

4.3 null 判断

那这个就是 is null 和 is not null

然后 isnull() 好像还是个函数来着

可以直接在查询语句当中包裹列的那种

我记得 null 值是可以和其他值比较的，但是默认是最小的

然后不能用 == 来判断 null 值，所以要用这个 is null

mysql> 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)

4.4 in

如果我们要查询在指定范围的数据，可能第一反应是between，或者用大于、小于去调整

但是这个都是连续的范围，如果是离散的，那可能是多个的 == 用 or 连接

好在我们这里还有 in，我感觉非常像 python，判定值在不在列表中

mysql> 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 还可以用在子查询当中

假设我们需要取出大阪在售商品的销售单价

mysql> 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)

这个看起来就更像是联表查询了

我们得先去 shopproduct 表，根据 shop_id = ‘000C’，取出大阪所售商品的 id

然后再去 product 表当中，把这些 id 的记录挑选出来，再展示指定的列

直觉上来看好像关联子查询的样子，但是仔细想想并不是

先说这里吧，这里的感觉是类似于标量子查询，只不过返回的是一个列表的感觉

但是关联子查询当中，那个返回结果是带有一定的关系在里面的

之前关联子查询部分举的例子是，查询售价比本类均价高的商品

那简单来说，子查询拿到了每个类别的均价，如果只是这个的话，好像没法比较

因为我们每读到一个商品，希望的比较的都是该商品与其所属类别均价的大小

所以外查询的 where 语句，要找到那个类别对应的均价去比较，也就是对应关系

那么在关联子查询的返回结果当中，我们是包含了这个关系的

我们在子查询当中嵌入了 p1.product_type = p2.product_type 的语句

也就是返回的结果当中是包含了这么一个对应关系，供外查询的 where 调用的

4.5 exist

这个东西的作用就是 “判断是否存在满足某种条件的记录”

要求和上面一样，都是选出来大阪售卖货物的价格

mysql> 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)

哦吼吼，说什么来什么，那这不就是关联子查询嘛

我们可以看到在子查询当中，从 shopproduct 表选出大阪商店

然后 and 指定 sp.product_id = p.product_id，选出对应的 id 映射关系

然后把这个关系传给外查询，外查询用 exist 来处理映射关系

而且，exist 只关心记录是否存在，所以可能不太注意选出的列

于是，我们在子查询 select 的列可以不用那么严谨

但是，一般来说，我们还是选 * 比较稳妥

5. case表达式

就是类似于 switch 一样的条件分支

首先来看看最常规的，根据某一字段的不同值，对不同记录进行不同处理

mysql> 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)

如上，我们根据商品种类的不同，对商品类别进行了不同的处理

然后，还可以实现类似行转列的效果，或者说是在列上展示聚合结果

mysql> -- 对按照商品种类计算出的销售单价合计值进行行列转换
mysql> 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 |             5500 |
+-------------------+-------------------+------------------+
1 row in set (0.01 sec)

第一列只选衣服，第二列选厨具，在不同的列展示对不同类别的记录做处理

我感觉这个很像是子查询，每个 sum 里面用子查询过滤一个类别出来

我们也可以对上述操作进行进一步延展，就是相对泛化的行转列操作

B.练习题

B.1

运算中含有 NULL 时，运算结果是否必然会变为NULL ？

字符串、数值类含有 null 的运算都是 null

B.2

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

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

这应该是选售价非500、2800、5000的物品名称和售价

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

这个在上一步的基础上还加了个 null

但是好像不行吧，null 只能用 is null 这种的

这里的感觉是类似于 == 这种的

mysql> SELECT product_name, purchase_price
    ->   FROM product
    ->  WHERE purchase_price NOT IN (500, 2800, 5000);
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| 打孔器       |            320 |
| 擦菜板       |            790 |
+--------------+----------------+
2 rows in set (0.00 sec)

mysql> SELECT product_name, purchase_price
    ->   FROM product
    ->  WHERE purchase_price NOT IN (500, 2800, 5000, NULL);
Empty set (0.00 sec)

果然

B.3

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

低档商品：销售单价在1000日元以下（T恤衫、办公用品、叉子、擦菜板、 圆珠笔）
中档商品：销售单价在1001日元以上3000日元以下（菜刀）
高档商品：销售单价在3001日元以上（运动T恤、高压锅）
请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句，结果如下所示。

执行结果
low_price | mid_price | high_price
----------±----------±-----------
5 | 1 | 2

mysql> SELECT SUM(CASE WHEN purchase_price < 1000 THEN 1 ELSE 0 END) AS low_price,
    ->        SUM(CASE WHEN purchase_price < 1000 OR ISNULL(purchase_price) THEN 1 ELSE 0 END) AS low_price2,
    ->        SUM(CASE WHEN purchase_price > 1000 AND purchase_price < 3000 THEN 1 ELSE 0 END) AS mid_price,
    ->        SUM(CASE WHEN purchase_price > 3000 THEN 1 ELSE 0 END) AS high_price
    ->   FROM product;
+-----------+------------+-----------+------------+
| low_price | low_price2 | mid_price | high_price |
+-----------+------------+-----------+------------+
|         3 |          5 |         2 |          1 |
+-----------+------------+-----------+------------+
1 row in set (0.00 sec)

mysql> 

可能是我的数据库改了？反正看结果应该是这个意思，但是和题中对不上