DataWhale组队学习之SQL编程-9月-task2

一、SELECT语句

1.1 select语句

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

其中，SELECT子句中列举了希望从表中查询出的列的名称，而FROM子句则指定了选取出数据的表的名称。

1.2 where语句

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

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

1.3 一些规定

• 星号（*）代表全部列的意思。
• SQL中可以随意使用换行符，不影响语句执行（但不可插入空行）。
• 设定汉语别名时需要使用双引号（"）括起来。
• 在SELECT语句中使用DISTINCT可以删除重复行。
• 注释是SQL语句中用来标识说明或者注意事项的部分。分为1行注释"-- “和多行注释两种”/* */"。

-- 想要查询出全部列时，可以使用代表所有列的星号（*）。
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 算术运算符

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

2.2 比较运算符

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

2.3 一些规定

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

三、逻辑运算符

3.1 NOT、AND、OR运算符

NOT运算符

想要表示“不是……”时，除了前文的<>运算符外，还存在另外一个表示否定、使用范围更广的运算符：NOT。

AND运算符

AND 相当于“并且”，类似数学中的取交集。

AND运算符工作效果图

OR运算符

OR 相当于“或者”，类似数学中的取并集。

OR运算符工作效果图

通过括号优先处理

AND 运算符优先于 OR 运算符，想要优先执行OR运算，可以使用括号：

-- 通过使用括号让OR运算符先于AND运算符执行
SELECT product_name, product_type, regist_date
  FROM product
 WHERE product_type = '办公用品'
   AND ( regist_date = '2009-09-11'
        OR regist_date = '2009-09-20');

3.2 真值表

真值就是值为真（TRUE）或假 （FALSE）其中之一的值。

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

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

• NOT运算符只是单纯的将TRUE转换为FALSE，将FALSE转换为TRUE。

真值表

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

含有NULL时的真值

NULL的真值结果既不为真，也不为假，因为并不知道这样一个值。这时真值是除真假之外的第三种值——不确定（UNKNOWN）。

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

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JFkMPJf7-1631804335294)(C:\Users\83769\Desktop\组队学习\SQL\wonderful-sql-main\img\ch02\ch02.05true3.png)]

四、聚合查询

4.1 聚合函数

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

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

用聚合函数删除重复值

-- 计算去除重复数据后的数据行数
SELECT COUNT(DISTINCT product_type)
 FROM product;
-- 是否使用DISTINCT时的动作差异（SUM函数）
SELECT SUM(sale_price), SUM(DISTINCT sale_price)
 FROM product;

4.2 一些规定

• COUNT函数的结果根据参数的不同而不同。COUNT(*)会得到包含NULL的数据行数，而COUNT(<列名>)会得到NULL之外的数据行数。
• 聚合函数会将NULL排除在外。但COUNT(*)例外，并不会排除NULL。
• MAX/MIN函数几乎适用于所有数据类型的列。SUM/AVG函数只适用于数值类型的列。
• 想要计算值的种类时，可以在COUNT函数的参数中使用DISTINCT。
• 在聚合函数的参数中使用DISTINCT，可以删除重复数据。

五、对表分组GROUP BY

将进行分组汇总时（即：将现有的数据按照某列来汇总统计），可以使用GROUP BY来实现。在 GROUP BY 子句中指定的列称为聚合键或者分组列。

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

聚合键中包含NULL时，group by会将null作为一组特殊数据进行处理。

5.1 GROUP BY书写位置

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

1 SELECT → 2. FROM → 3. WHERE → 4. GROUP BY

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

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

5.3 为聚合结果指定条件

可以在GROUP BY后使用HAVING子句，来获得分组后的条件筛选。

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

六、对查询结果进行排序ORDER BY

SQL中的执行结果是随机排列的，当需要按照特定顺序排序时，可已使用ORDER BY子句。

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

默认为升序排列，降序排列为DESC

6.1 ORDER BY中列名可使用别名

GROUP BY 子句中不能使用SELECT 子句中定义的别名，但是在 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中不能使用别名。

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

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

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

一般有如下两种需求：

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

对于数字或者日期类型，可以在排序字段前添加一个负号（minus）来得到反向排序。

select * from table_name
order by - date_login desc

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

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

select * from table_name
order by - date_login

七、练习题

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

select product_name,regist_date
from product
where regist > '2009-4-28'

7.2 说出对product表执行如下三条SELECT语句返回的结果

①

SELECT *
  FROM product
 WHERE purchase_price = NULL;

②

SELECT *
  FROM product
 WHERE purchase_price <> NULL;

③

SELECT *
  FROM product
 WHERE product_name > NULL;

• 以上都无法输出，因为只能使用IS NULL 或者NOT NULL

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

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

-- 1.
select product_name,sale_price,purchase_price
from product
where (sale_price - purchase_price) > 500
-- 2.
select product_name,sale_price,purchase_price
from product
where (sale_price - purchase_price) <= 500

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

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

select product_name,product_type,(purchase_price - sale_price*0.9) as profit
from producet
where profit > 100

7.5 指出下面SELECT语句中所有的语法错误

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

group by的书写顺序错误，正确的书写顺序是select - from - where - group by

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

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

select product_type,sum(sale_prict) as sum_sale,sum(purchase_price) as sum_purchase
from product
where sum_sale > sum_purchase*1.5
group by product_type

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

select *
from product
order by - regist_date