瓜斯拉的逆袭
0%

SQL组队学习02：基础查询与排序

发表于 分类于
本文字数： 10k 阅读时长 ≈ 26 分钟

Task02：基础查询与排序

2.1 SELECT 语句基础

2.1.1 从表中选取数据

SELECT语句

从表中选取数据时需要使用SELECT语句，也就是只从表中选出（SELECT）必要数据的意思。通过SELECT语句查询并选取出必要数据的过程称为匹配查询或查询（query）。

基本SELECT语句包含了SELECT和FROM两个子句（clause）。示例如下：

1
2
SELECT <列名>, 
  FROM <表名>;

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

WHERE语句

当不需要取出全部数据，而是选取出满足“商品种类为衣服”“销售单价在1000日元以上”等某些条件的数据时，使用WHERE语句。

SELECT 语句通过WHERE子句来指定查询数据的条件。

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

比较下面两者输出结果的不同：

1
2
3
4
5
6
7
8
-- 用来选取product type列为衣服的记录的SELECT语句
SELECT product_name, product_type
  FROM product
 WHERE product_type = '衣服';
-- 也可以选取出不是查询条件的列（条件列与输出列不同）
SELECT product_name
  FROM product
 WHERE product_type = '衣服';

2.1.3 相关法则

  • 星号（*）代表全部列的意思。
  • SQL中可以随意使用换行符，不影响语句执行（但不可插入空行）。
  • 设定汉语别名时需要使用双引号（”）括起来。
  • 在SELECT语句中使用DISTINCT可以删除重复行。
  • 注释是SQL语句中用来标识说明或者注意事项的部分。分为1行注释”— “和多行注释两种”/ /“。
1
2
3
4
5
6
7
8
9
10
11
	-- 想要查询出全部列时，可以使用代表所有列的星号（*）。
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;

2.1.4 关于DISTINCT

DISTINCT 是跟在 SELECT 后面的，表示对查询出来的结果去重；而不是跟在某个字段之前。

1
2
3
select distinct product_type,
                product_id
from product;

image-20220714223600736

本例中product_type仍然有重复，因为去重的条件是两个SELECT的字段都相同，而本例中它们的product_id不同。

2.2 算术运算符和比较运算符

2.2.1 算术运算符

SQL语句中可以使用四则运算符：“+”，“-”，“*”，“/”

2.2.2 比较运算符

1
2
3
4
-- 选取出sale_price列为500的记录
SELECT product_name, product_type
  FROM product
 WHERE sale_price = 500;

SQL常见比较运算符如下：

运算符 含义
= 和 ~ 相等
<> / != 和 ~ 不相等
>= 大于等于 ~
> 大于 ~
<= 小于等于 ~
< 小于 ~

2.2.3 常用法则

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

相关代码如下：

1
2
3
-- 1、SQL语句中也可以使用运算表达式
SELECT product_name, sale_price, sale_price * 2 AS "sale_price x2"
  FROM product;

SELECT 了三列，第三列为算术运算结果 sale_price * 2

image-20220714230044491

1
2
3
4
-- 2、WHERE子句的条件表达式中也可以使用计算表达式
SELECT product_name, sale_price, purchase_price
  FROM product
 WHERE sale_price - purchase_price >= 500;

WHERE 相当于在查询的结果上再进行筛选，选出满足条件的

image-20220714230151001

1
2
3
4
-- 3、选取出大于'2'的数据的SELECT语句('2'为字符串)
SELECT chr
  FROM chars
 WHERE chr > '2';

字符串也可以直接比较大小

image-20220714230615130

1
2
3
4
5
6
7
8
-- 4、选取NULL的记录
SELECT product_name, purchase_price
  FROM product
 WHERE purchase_price IS NULL;
-- 5、选取不为NULL的记录
SELECT product_name, purchase_price
  FROM product
 WHERE purchase_price IS NOT NULL;

等于 NULL 的等于要用 IS，不等于要用 IS NOT

image-20220714230651887

image-20220714230815626

2.3 逻辑运算符

2.3.1 AND、OR、NOT

AND、OR、NOT，与或非，和逻辑里的概念一样。注意 AND 运算优先级高于 OR。

2.3.2 真值表

复杂运算时该怎样理解？

当碰到条件较复杂的语句时，理解语句含义并不容易，这时可以采用真值表来梳理逻辑关系。

什么是真值？

本节介绍的三个运算符 NOT、AND 和 OR 称为逻辑运算符。这里所说的逻辑就是对真值进行操作的意思。真值就是值为真（TRUE）或假 （FALSE）其中之一的值。

例如，对于 sale_price >= 3000 这个查询条件来说，由于 product_name 列为 ‘运动 T 恤’ 的记录的 sale_price 列的值是 2800，因此会返回假（FALSE），而 product_name 列为 ‘高压锅’ 的记录的sale_price 列的值是 5000，所以返回真（TRUE）。

AND 运算符两侧的真值都为真时返回真，除此之外都返回假。

OR 运算符两侧的真值只要有一个不为假就返回真，只有当其两侧的真值都为假时才返回假。

NOT运算符只是单纯的将真转换为假，将假转换为真。

真值表

图片

查询条件为P AND（Q OR R）的真值表

图片

含有NULL时的真值

NULL的真值结果既不为真，也不为假，因为并不知道这样一个值。

那该如何表示呢？

这时真值是除真假之外的第三种值——不确定（UNKNOWN）。一般的逻辑运算并不存在这第三种值。SQL 之外的语言也基本上只使用真和假这两种真值。与通常的逻辑运算被称为二值逻辑相对，只有 SQL 中的逻辑运算被称为三值逻辑。

三值逻辑下的AND和OR真值表为：

图片

练习题-第一部分（请给出代码、包含代码及代码执行结果的截图）

2.1

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

答：

1
2
3
4
select product_name,
       regist_date
from product
where regist_date>'2009-04-28';

image-20220714231802643

2.2

请说出对product 表执行如下3条SELECT语句时的返回结果。

①空

1
2
3
SELECT *
  FROM product
 WHERE purchase_price = NULL;

②空

1
2
3
SELECT *
  FROM product
 WHERE purchase_price <> NULL;

③空

1
2
3
SELECT *
  FROM product
 WHERE product_name > NULL;

判断某字段是否为空，必须用 IS/IS NOT NULL，而不能用比较运算符。

2.3

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

1
2
3
4
5
product_name | sale_price | purchase_price 
-------------+------------+------------
T恤衫        | 　 1000    | 500
运动T恤      |    4000    | 2800
高压锅       |    6800    | 5000

答：

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 1.
select product_name,
       sale_price,
       purchase_price
from product
where NOT sale_price-purchase_price<500;

-- 2.
select product_name,
       sale_price,
       purchase_price
from product
where sale_price-purchase_price>=500 is TRUE;

2.4

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

提示：销售单价打九折，可以通过 sale_price 列的值乘以0.9获得，利润可以通过该值减去 purchase_price 列的值获得。

答：

1
2
3
4
5
select  product_name,
        product_type,
        sale_price*0.9-purchase_price as profit
from product
where sale_price*0.9-purchase_price>100;

image-20220714233054704

2.4 对表进行聚合查询

2.4.1 聚合函数

SQL中用于汇总的函数叫做聚合函数。以下五个是最常用的聚合函数：

  • SUM：计算表中某数值列中的合计值
  • AVG：计算表中某数值列中的平均值
  • MAX：计算表中任意列中数据的最大值，包括文本类型和数字类型
  • MIN：计算表中任意列中数据的最小值，包括文本类型和数字类型
  • COUNT：计算表中的记录条数（行数）
1
2
3
-- 1、计算销售单价和进货单价的合计值
SELECT SUM(sale_price), SUM(purchase_price) 
  FROM product;

对所有sale_price和purchase_price求和，作为两列：

image-20220714233246149

1
2
3
-- 2、计算销售单价和进货单价的平均值
SELECT AVG(sale_price), AVG(purchase_price)
  FROM product;

对所有sale_price和purchase_price求均值，作为两列：

image-20220714233328811

1
2
3
-- 3、计算销售单价的最大值和最小值
SELECT MAX(sale_price), MIN(sale_price)
  FROM product;

求出所有sale_price的最大值和purchase_price的最小值，作为两列：

image-20220714233451884

1
2
3
-- 4、MAX和MIN也可用于非数值型数据
SELECT MAX(regist_date), MIN(regist_date)
  FROM product;

求出所有注册日期中的最大值和最小值，作为两列：

image-20220714233542435

1
2
3
-- 5、计算全部数据的行数（包含 NULL 所在行）
SELECT COUNT(*)
  FROM product;

计算product表中的记录行数：

image-20220714233643496

1
2
3
-- 计算 NULL 以外数据的行数
SELECT COUNT(purchase_price)
  FROM product;

NULL 不计数：

image-20220714233734002

DISTINCT 和聚合运算一起使用

当对整表进行聚合运算时，表中可能存在多行相同的数据，比如商品类型（product_type 列）。

在某些场景下，就不能直接使用聚合函数进行聚合运算了，必须搭配 DISTINCT 函数使用。

比如：要计算总共有几种咖啡类型在售，该怎么计算呢？

如前所述，DISTINCT 函数用于删除重复数据，应用 COUNT 聚合函数之前，加上 DISTINCT 关键字就可以实现需求。

1
2
SELECT COUNT(DISTINCT product_type)
  FROM product;

求不同的类别数：

image-20220714234318339

2.4.2 聚合函数应用法则

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

2.5 对表进行分组

2.5.1 GROUP BY语句

之前使用聚合函数都是会将整个表的数据进行处理，当你想将进行分组汇总时（即：将现有的数据按照某列来汇总统计），GROUP BY可以帮助你：

1
2
3
SELECT <列名1>,<列名2>, <列名3>, ……
  FROM <表名>
 GROUP BY <列名1>, <列名2>, <列名3>, ……;

看一看是否使用GROUP BY语句的差异：

1
2
3
4
5
6
7
-- 按照商品种类统计数据行数
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type;
 -- 不含GROUP BY
SELECT product_type, COUNT(*)
  FROM product

image-20220714234547139

报错原因：在不使用group by 子句的聚合查询中，Select列表中的第一个表达式包含了非聚合的列‘shop.product.product_type’；当sql_mode为only_full_group_by时，是不能出现这种情况的。

image-20220714234808432

按照商品种类对表进行切分

图片

这样，GROUP BY 子句就像切蛋糕那样将表进行了分组。在 GROUP BY 子句中指定的列称为聚合键或者分组列。

聚合键中包含NULL时

将进货单价（purchase_price）作为聚合键举例：

1
2
3
SELECT purchase_price, COUNT(*)
  FROM product
 GROUP BY purchase_price;

此时会将NULL作为一组特殊数据进行聚合运算

GROUP BY书写位置

GROUP BY的子句书写顺序有严格要求，不按要求会导致SQL无法正常执行，目前出现过的子句顺序为：

  1. SELECT ➡️ 2. FROM ➡️ 3. WHERE ➡️ 4. GROUP BY

GROUP BY 一定在 WHERE 后面！！

其中前三项用于筛选数据，GROUP BY对筛选出的数据进行处理

在WHERE子句中使用GROUP BY

1
2
3
4
SELECT purchase_price, COUNT(*)
  FROM product
 WHERE product_type = '衣服'
 GROUP BY purchase_price;

查询每种价格的衣服的销量

image-20220714235313436

2.5.2 常见错误

在使用聚合函数及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子句中使用聚合函数。

2.6 为聚合结果指定条件

2.6.1 用 HAVING 得到特定分组

前面学习了如何得到分组聚合结果，现在大家思考一下，如何得到分组聚合结果的部分结果呢？

将表使用 GROUP BY 分组后，怎样才能只取出其中两组？

image-20220714235742889

这里 WHERE 不可行，因为，WHERE子句只能指定记录（行）的条件，而不能用来指定组的条件（例如，“数据行数为 2 行”或者“平均值为 500”等）。

可以在 GROUP BY 后使用 HAVING 子句。

HAVING 的用法类似 WHERE。

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

WHERE子句 指定记录（行level）的条件

HAVING子句 制定分组聚合（组level）的结果

2.6.2 HAVING特点

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

1
2
3
4
5
-- 常数
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type
HAVING COUNT(*) = 2;

查询类别下个数为2的商品类别

image-20220715000314859

1
2
3
4
5
-- 错误形式（因为product_name不包含在GROUP BY聚合键中）
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type
HAVING product_name = '圆珠笔';

2.7 对查询结果进行排序

2.7.1 ORDER BY

SQL 语句执行结果默认随机排列，想要按照顺序排序，需使用 ORDER BY 子句。

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

其中，参数 ASC 表示升序排列，DESC 表示降序排列，默认为升序。

如下代码将得到按照销售价格倒序排列的查询结果：

1
2
3
4
-- 降序排列
SELECT product_id, product_name, sale_price, purchase_price
  FROM product
 ORDER BY sale_price DESC;

image-20220715000503572

如果有多列排序需求，只需在 ORDER BY 子句中依次书写排序列 + 排序参数即可，详见如下代码：

1
2
3
4
-- 多个排序键
SELECT product_id, product_name, sale_price, purchase_price
  FROM product
 ORDER BY sale_price, product_id;

先按照 sale_price 排序，如果 sale_price 相同，再按照 product_id 排序，都默认升序。

image-20220715000602322

需要特别说明的是：由于 NULL 无法使用比较运算符进行比较，也就是说，无法与文本类型，数字类型，日期类型等进行比较，当排序列存在 NULL 值时，NULL 结果会展示在查询结果的开头或者末尾。

1
2
3
4
-- 当用于排序的列名中含有NULL时，NULL会在开头或末尾进行汇总。
SELECT product_id, product_name, sale_price, purchase_price
  FROM product
 ORDER BY purchase_price;

在MySQL中，NULL 值被认为比任何 非NULL 值低，所以默认升序时排在开头。

image-20220715000730767

2.7.2 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中不能使用别名。

2.7.3 ORDER BY 排序列中存在 NULL 时，指定其出现在首行或者末行的方式

在MySQL中，NULL 值被认为比任何 非NULL 值低，因此，当顺序为 ASC（升序）时，NULL 值出现在第一位，而当顺序为 DESC（降序）时，则排序在最后。

如果想指定存在 NULL 的行出现在首行或者末行，需要特殊处理。

使用如下代码构建示例表：

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE user (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(5),
    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....-∞）

图片

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

图片

还可以使用 COALESCE 函数实现需求

图片

  • 将 NULL 值排在首行，同时将所有 非NULL 值按倒序排列。

对于数字或者日期类型，可以在排序字段前添加一个负号（minus）来实现。（-∞...-3、-2、-1）

图片

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

图片 图片

还可以使用 COALESCE 函数实现需求

图片

练习题-第二部分（请给出代码、包含代码及代码执行结果的截图）

2.5

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

1
2
3
4
5
SELECT product_id, SUM(product_name)
--本SELECT语句中存在错误。
  FROM product 
 GROUP BY product_type 
 WHERE regist_date > '2009-09-01';

答：1. GROUP BY 应该在 WHERE 后面；2. SELECT 只能选择 GROUP BY 的聚合键（这几个关键词的执行顺序是：FROM—WHERE—GROUP BY—SELECT）

2.6

请编写一条SELECT语句，求出销售单价（ sale_price 列）合计值大于进货单价（ purchase_price 列）合计值1.5倍的商品种类。执行结果如下所示。

1
2
3
4
product_type | sum  | sum 
-------------+------+------
衣服         | 5000 | 3300
办公用品      |  600 | 320

图片

答： 注意要用 having ，因为是对分组后的结果进行筛选。

1
2
3
4
5
6
select product_type,
       sum(sale_price) as sum1,
       sum(purchase_price) as sum2
from product
group by product_type
having sum1>sum2*1.5;

image-20220715002257639

2.7

此前我们曾经使用SELECT语句选取出了product（商品）表中的全部记录。当时我们使用了 ORDER BY 子句来指定排列顺序，但现在已经无法记起当时如何指定的了。请根据下列执行结果，思考 ORDER BY 子句的内容。

图片

答：观察可以看到是根据 regist_date, purchase_price 的优先级排列的。register_date降序排列，但是null排在了第一个；相同 register_date 按照 purchase_price 升序排列。如果直接对 register_date 降序排列，null会排在最后（null小于所有非null值，可以理解成负无穷）。所以用一些2.7.3的小trick。

1
2
3
4
select *
from product
order by coalesce(regist_date,'9999-12-31') desc,
         purchase_price;

image-20220715004704161

参考资料

https://github.com/datawhalechina/wonderful-sql/blob/main/ch02:%20%E5%9F%BA%E7%A1%80%E6%9F%A5%E8%AF%A2%E4%B8%8E%E6%8E%92%E5%BA%8F.md#22