使用聚合函数导致索引失效 聚合函数的执行顺序

基础

• 查询

• case

• 分组group by
• 排序ORDER BY

• 默认

• 聚合函数

• sum
• 聚合函数去重
• min(),max()
• group by
• limit

• 练习一

• 1
• 2product 执行返回结果
• 3
• 4

• 练习二

• 5
• 6
• 7

这是因为SQL在使用 HAVING 子句时 SELECT 语句的执行顺序为：

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

查询

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

case

分组group by

SELECT → 2. FROM → 3. WHERE → 4. GROUP BY
在使用聚合函数及GROUP BY子句时，经常出现的错误有：

• 在聚合函数的SELECT子句中写了聚合健以外的列 使用COUNT等聚合函数时，SELECT子句中如果出现列名，只能是GROUP BY子句中指定的列名（也就是聚合键）。
• 在GROUP BY子句中使用列的别名 SELECT子句中可以通过AS来指定别名，但在GROUP BY中不能使用别名。因为在DBMS中 ,SELECT子句在GROUP BY子句后执行。
• 在WHERE中使用聚合函数 原因是聚合函数的使用前提是结果集已经确定，而WHERE还处于确定结果集的过程中，所以相互矛盾会引发错误。 如果想指定条件，可以在SELECT，HAVING（下面马上会讲）以及ORDER BY子句中使用聚合函数。

排序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;

SELECT winner,yr,subject
FROM nobel
WHERE winner LIKE "Sir%"
ORDER BY yr DESC,winner ASC

并列加逗号

聚合函数

知识点
SQL聚合函数
有时候我们只是需要获取数据的汇总信息，比如说行数啊、平均值啊这种，并不需要吧所有数据都检索出来，为此，SQL提供了专门的函数，这也是SQL最强大功能之一。
聚合函数
SQL的聚合函数如下所示：
函数 说明
AVG() 返回某列的均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列的和
使用示例：
SELECT AVG(col_1) AS avg_col_1
FROM table_1;
⚠️聚合函数都会忽略列中的NULL值，但是COUNT(*)也就是统计全部数据的行数时，不会忽略NULL值。
聚合不同值
当添加DISTINCT参数时，就可以只对不同值（也就是某列中的唯一值）进行函数操作。
使用示例：
SELECT AVG(DISTINCT col_1) AS avg_dist_col_1
FROM table_1;

-- 计算全部数据的行数（包含NULL）
SELECT COUNT(*)
  FROM product;
-- 计算NULL以外数据的行数
SELECT COUNT(purchase_price)
  FROM product;
-- 计算销售单价和进货单价的合计值
SELECT SUM(sale_price), SUM(purchase_price) 
  FROM product;
-- 计算销售单价和进货单价的平均值
SELECT AVG(sale_price), AVG(purchase_price)
  FROM product;
-- MAX和MIN也可用于非数值型数据
SELECT MAX(regist_date), MIN(regist_date)
  FROM product;

sum

聚合函数去重

找出非洲(Africa)的GDP總和。

有多少個國家具有至少百萬(1000000)的面積。

(‘Estonia’, ‘Latvia’, ‘Lithuania’)总人口

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

COUNT函数的结果根据参数的不同而不同。COUNT(*)会得到包含NULL的数据行数，而COUNT(<列名>)会得到NULL之外的数据行数。

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

min(),max()

翻译/正确的题目描述
排除工资最高和最低的员工工资后，计算当前剩余员工的平均工资avg_salary
当前员工使用to_date = '9999-01-01’进行筛选

select avg(salary) as avg_salary from salaries
where to_date = '9999-01-01' 
    and salary not in (select max(salary) from salaries where to_date='9999-01-01')
    and salary not in (select min(salary) from salaries where to_date='9999-01-01')

group by

對於每一個洲份，顯示洲份和國家的數量。

對於每一個洲份，顯示洲份和至少有1000萬人(10,000,000)口國家的數目。

limit

55 limit限制查询的行数

https://www.nowcoder.com/practice/f24966e0cb8a49c192b5e65339bc8c03?tpId=82&&tqId=29823&rp=1&ru=/ta/sql&qru=/ta/sql/question-ranking 翻译/正确的题目描述

查询employees表中，第6-10行的数据

知识点

使用LIMIT语句可以限制返回的行数。

limit n 则返回前n行，如：limit 10 返回前10行

limit x,y 则从第x行后返回y行，如limit 10,2则会返回第11和12行

解题思路/代码逻辑

查询第几行到第几行的数据，要用limit x,y

查询第6-10行的数据，就是从第5行后返回5行.

符合代码规范的正确答案

select * from employees
    limit 5,5

练习一

1

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

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

实现

2product 执行返回结果

1、价格为空

2、价格不为空

三个语句都返回全字段为N/A的行

3

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

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

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  sale_price*0.9-purchase_price >100  AND product_type IN ('办公用品','厨房用具');

练习二

5

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

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

答;sum对应数值不是字符，where和group by顺序问题

6

SELECT product_type,SUM(sale_price) AS sum_sale_price,SUM(purchase_price) AS sum_purchase_price 
FROM product;
GROUP BY product_type
HAVING sum_sale_price > sum_purchase_price * 1.5
order by sale_sum desc;

7

select * from product
order by if(isnull(regist_date),1,0) desc,regist_date desc,sale_price asc;