Task02: 基础排序与查询

目录

2.1 排序与查询知识汇总

2.2 练习题（macOS）

2.2.1 第一部分

2.2.2 第二部分

---

2.1 排序与查询知识汇总

	用法	相关法则
SELECT	查询并选取出必要数据： SELECT <列名>,…… FROM <表名>;	星号（*）代表全部列 SQL中可以随意使用换行符，不影响语句执行（但不可插入空行） 设定汉语别名时需要使用双引号（"）括起来 在SELECT语句中使用DISTINCT可以删除重复行 注释是SQL语句中用来标识说明或者注意事项的部分。分为1行注释"-- "和多行注释两种"/* */"
WHERE	条件查询：  SELECT <列名>,…… FROM <表名> WHERE <条件表达式>;
算数运算符	含义 运算符 加法 + 减法 - 乘法 * 除法 /	SELECT子句中可以使用常数或者表达式 使用比较运算符时一定要注意不等号和等号的位置 字符串类型的数据原则上按照字典顺序进行排序，不能与数字的大小顺序混淆 希望选取NULL记录时，需要在条件表达式中使用IS NULL运算符 希望选取不是NULL的记录时，需要在条件表达式中使用IS NOT NULL运算符
比较运算符	运算符 含义 = 和~相等 <> , != 和~不相等 >= 大于等于~ > 大于~ <= 小于等于~ < 小于~
逻辑运算符	运算符 含义 NOT 不是,,,（不能单独使用） AND 并集 OR 交集	运算符优先级：NOT > AND > OR 可以使用括号确定优先级
真值表	在遇到条件较复杂的语句时，采用真值表来梳理逻辑关系（TRUE/FALSE/UNKNOW）	NOT的真值表： AND的真值表：FALSE>UNKNOW>TRUE OR的真值表：TRUE>UNKNOW>FALSE 更多详情参考：https://www.cnblogs.com/youzhibing/p/11337745.html
聚合函数	COUNT：计算表中的记录数（行数） SUM：计算表中数值列中数据的合计值 AVG：计算表中数值列中数据的平均值 MAX：求出表中任意列中数据的最大值 MIN：求出表中任意列中数据的最小值	COUNT(*)会得到包含NULL的数据行数，而COUNT(<列名>)会得到NULL之外的数据行数 除了COUNT(*)，其他聚合函数都会将NULL排除在外 MAX/MIN函数几乎适用于所有数据类型的列 SUM/AVG函数只适用于数值类型的列 想要计算值的种类时，可以在COUNT函数的参数中使用DISTINCT，COUNT(DISTINCT <列名>；但DISTINCT不能用于COUNT(*)，即COUNT(DISTINCT)会出错 在聚合函数的参数中使用DISTINCT，可以删除重复数据
表分组	分组汇总，即将现有的数据按照某列来汇总统计 SELECT <列名1>,<列名2>, <列名3>, …… FROM <表名> GROUP BY <列名1>, <列名2>, <列名3>, ……;	GOUPY BY书写位置：1. SELECT → 2. FROM → 3. WHERE → 4. GROUP BY (1,2,3-筛选数据，4-对筛选的数据进行分组) GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套， 为数据分组提供更细致的控制 如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上 进行汇总。换句话说，在建立分组时，指定的所有列都一起计算 (所以不能从个别的列取回数据) GROUP BY子句中列出的每个列都必须是检索列或有效的表达式 (但不能是聚集函数)。如果在SELECT中使用表达式，则必须在 GROUP BY子句中指定相同的表达式。不能使用别名（即SELECT中AS指定的别名） 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列 中有多行NULL值，它们将分为一组 如果想指定条件，可以在SELECT，HAVING以及ORDER BY子句中使用聚合函数 GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前
过滤分组	HAVING子句用于对分组进行过滤，可以使用数字、聚合函数和GROUP BY中指定的列名（聚合键）	HAVING支持所有WHERE操作符 唯一的差别是 WHERE过滤行，而HAVING过滤分组 WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤
对查询结果进行排序	默认为升序序列，降序序列关键字DESC SELECT <列名1>, <列名2>, <列名3>, …… FROM <表名> ORDER BY <排序基准列1>, <排序基准列2>, ……	ORDER BY中列名可使用别名，因为ORDER BY在SELECT之后 FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

2.2 练习题（macOS）

2.2.1 第一部分

准备工作，建表并插入数据：

-- 建表
CREATE TABLE product
(product_id CHAR(4) NOT NULL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INTEGER,
purchase_price INTEGER,
regist_date DATE);

-- 插入数据
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');

查看结果：

mysql> select * from product;
+------------+--------------+--------------+------------+----------------+-------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+------------+--------------+--------------+------------+----------------+-------------+
| 0001       | T恤衫        | 衣服         |       1000 |            500 | 2009-09-20  |
| 0002       | 打孔器       | 办公用品     |        500 |            320 | 2009-09-11  |
| 0003       | 运动T恤      | 衣服         |       4000 |           2800 | NULL        |
| 0004       | 菜刀         | 厨房用具     |       3000 |           2800 | 2009-09-20  |
| 0005       | 高压锅       | 厨房用具     |       6800 |           5000 | 2009-01-15  |
| 0006       | 叉子         | 厨房用具     |        500 |           NULL | 2009-09-20  |
| 0007       | 擦菜板       | 厨房用具     |        880 |            790 | 2008-04-28  |
| 0008       | 圆珠笔       | 办公用品     |        100 |           NULL | 2009-11-11  |
+------------+--------------+--------------+------------+----------------+-------------+
8 rows in set (0.00 sec)

 

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

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

查询结果：

+--------------+-------------+
| product_name | regist_date |
+--------------+-------------+
| T恤衫        | 2009-09-20  |
| 打孔器       | 2009-09-11  |
| 菜刀         | 2009-09-20  |
| 叉子         | 2009-09-20  |
| 圆珠笔       | 2009-11-11  |
+--------------+-------------+
5 rows in set (0.00 sec)

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

SELECT *
  FROM product
 WHERE purchase_price = NULL;

说明：返回空，= NULL会被判断为UNKNOWN，故返回为空；正确查询方式应为 IS NULL。

SELECT *
  FROM product
 WHERE purchase_price <> NULL;

说明：返回空，<> NULL会被判断为UNKNOWN，故返回为空；正确查询方式应为 IS NOT NULL。

SELECT *
  FROM product
 WHERE product_name > NULL;

说明：返回空，> NULL会被判断为UNKNOWN，故返回为空。

综上：

-- 以下的式子都会被判为UNKNOWN
= NULL
> NULL
< NULL
<> NULL
NULL = NULL

3. 示例代码清单中的SELECT语句能够从product表中取出“销售单价（saleprice）比进货单价（purchase price）高出500日元以上”的商品。请写出两条可以得到相同结果的SELECT语句。

示例代码和执行结果如下所示：

SELECT product_name，sale_price，purchase_price
  FROM product
 WHERE sale_price-purchase_price >= 500；

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

可以得到相同结果的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 - 500 >= purchase_price;

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

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

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

查询结果： 

+--------------+--------------+--------+
| product_name | product_type | profit |
+--------------+--------------+--------+
| 打孔器       | 办公用品     |  130.0 |
| 高压锅       | 厨房用具     | 1120.0 |
+--------------+--------------+--------+
2 rows in set (0.00 sec)

2.2.2 第二部分

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

SELECT product id，SUM（product name）
--本SELECT语句中存在错误。
  FROM product 
 GROUP BY product_type 
 WHERE regist_date > '2009-09-01';

说明：对分组数据进行筛选要用HAVING语句，而不是WHERE语句；WHERE语句在GROUPY BY之前，对行进行筛选

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

product_type | sum  | sum 
-------------+------+------
衣服         | 5000 | 3300
办公用品      |  600 | 320

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

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

+------------+--------------+--------------+------------+----------------+-------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+------------+--------------+--------------+------------+----------------+-------------+
| 0003       | 运动T恤      | 衣服         |       4000 |           2800 | NULL        |
| 0008       | 圆珠笔       | 办公用品     |        100 |           NULL | 2009-11-11  |
| 0006       | 叉子         | 厨房用具     |        500 |           NULL | 2009-09-20  |
| 0001       | T恤衫        | 衣服         |       1000 |            500 | 2009-09-20  |
| 0004       | 菜刀         | 厨房用具     |       3000 |           2800 | 2009-09-20  |
| 0002       | 打孔器       | 办公用品     |        500 |            320 | 2009-09-11  |
| 0005       | 高压锅       | 厨房用具     |       6800 |           5000 | 2009-01-15  |
| 0007       | 擦菜板       | 厨房用具     |        880 |            790 | 2008-04-28  |
+------------+--------------+--------------+------------+----------------+-------------+
8 rows in set (0.00 sec)

观察结果，发现regist_date是降序排列，尝试ORDER BY regist_date，代码和结果如下：

SELECT *
FROM product 
ORDER BY regist_date DESC;

结果发现有2个问题：

1)regist_date为NULL对应的行被排序到了最后

2)regist_date = ‘2009-09-20’对应的3条数据顺序不对

观察最终的要求结果，是在regist_data降序排列之后，再对sale_price升序排序；因此可以解决问题2)，调整后代码和结果如下：

SELECT *
FROM product 
ORDER BY regist_date DESC, sale_price;

最后需要解决问题1) regist_date为NULL的排序，这里借助ISNULL()函数判断regist_date得到中间数据。

当regist_date为NULL时，ISNULL(regist_date)返回为1；当regist_date不为NULL时，ISNULL(regist_date)返回为0。

再利用ODER BY对ISNULL(regist_date)进行降序排序，即可解决问题2，对应代码如下：

SELECT *
FROM product 
ORDER BY ISNULL(regist_date) DESC, regist_date DESC, sale_price;