SQL编程task02作业-基础查询与排序

1 运算符

1.1 算术运算符

含义	运算符
加法	+
减法	-
乘法	*
除法	/

1.2 比较运算符

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

2 练习题第一部分-查询

2.1编写product表格

SQL代码如下：

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

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

结果如下：

2.2 查询语句

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

select product_name,regist_date
 from product
where 	not regist_date = '2009-01-15'
    and not regist_date = '2008-04-28';

查询结果：

2.3 NULL的几种不正确查询格式

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

SELECT *
  FROM product
 WHERE purchase_price = NULL;

SELECT *
  FROM product
 WHERE purchase_price <> NULL;

SELECT *
  FROM product
 WHERE product_name > NULL;

以上三种查询结果均取不出任何记录，结果如下：

原因：
选取NULL记录时，需要在条件表达式中使用IS NULL运算符。希望选取不是NULL的记录时，需要在条件表达式中使用IS NOT NULL运算符。

2.4 条件查询语句+运算符

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

SQL语句如下：

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;

SELECT product_name, sale_price, purchase_price
  FROM product
 WHERE sale_price >= purchase_price +500;

2.5 条件查询+查询结果另设定为一列

SQL代码：

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='厨房用具');

结果如下：

3 聚合&排序

3.1 聚合函数

COUNT： 计算表中的记录数（行数）
SUM： 计算表中数值列中数据的合计值
AVG： 计算表中数值列中数据的平均值
MAX： 求出表中任意列中数据的最大值
MIN： 求出表中任意列中数据的最小值

3.2 WHERE 条件下GROUP BY语句

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

GROUP BY的子句书写顺序有严格要求，不按要求会导致SQL无法正常执行，目前出现过的子句顺序为：
1 SELECT → 2. FROM → 3. WHERE → 4. GROUP BY
其中前三项用于筛选数据，GROUP BY对筛选出的数据进行处理

3.3 用HAVING得到特定分组

-- 数字
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type
HAVING COUNT(*) = 2;

3.4 ORDER BY排序

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

问题： GROUP BY 子句中不能使用SELECT 子句中定义的别名，但是在 ORDER BY 子句中却可以使用别名。为什么在GROUP BY中不可以而在ORDER BY中可以呢？
这是因为SQL在使用 HAVING 子句时 SELECT 语句的顺序为：
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
其中SELECT的执行顺序在 GROUP BY 子句之后，ORDER BY 子句之前。也就是说，当在ORDER
BY中使用别名时，已经知道了SELECT设置的别名存在，但是在GROUP BY中使用别名时还不知道别名的存在，所以不能在ORDER
BY中可以使用别名，但是在GROUP BY中不能使用别名

4 练习题第二部分-聚合&排序

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

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

解：

• SUM，计算表中数值列中数据的合计值，product_name列并非数值列数据。
• 标点符号如括号逗号分号用的是全角，应该使用半角。
• GROUP BY 与WHERE顺序颠倒，且此时WHERE语句后不需要分号。
• SELECT子句中如果出现列名，只能是GROUP BY子句中指定的列名（也就是聚合键）。

4.2 HAVING对聚合分组进行过滤

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

SQL代码：

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

4.3 ORDER BY排序

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

SQL代码：

SELECT *
  FROM product
 ORDER BY if(isnull(regist_date),0,1),regist_date DESC, sale_price;

参考：DataWhale SQL组队学习