读SQL学习指南（第3版）笔记07_分组和子查询

十六夜雨

1. 数据通常以数据库用户所需的最低层级的粒度存储

2. 分组

2.1. 隐式分组

2.1.1. mysql

1. -＞ SELECT MAX(amount) max_amt,
2.     -＞   MIN(amount) min_amt,
3.     -＞   AVG(amount) avg_amt,
4.     -＞   SUM(amount) tot_amt,
5.     -＞   COUNT(*) num_payments
6.     -＞ FROM payment;

复制代码

2.1.1.1. 查询返回的每个值都是由聚合函数生成的

2.1.1.2. 没有使用group by子句

2.1.1.3. 只有一个隐式分组

2.1.1.3.1. payment数据表中的所有行

2.2. 显式分组

2.2.1. mysql

1. -＞ SELECT customer_id,
2.    -＞   MAX(amount) max_amt,
3.    -＞   MIN(amount) min_amt,
4.    -＞   AVG(amount) avg_amt,
5.    -＞   SUM(amount) tot_amt,
6.    -＞   COUNT(*) num_payments
7.    -＞ FROM payment
8.    -＞ GROUP BY customer_id;

复制代码

2.2.1.1. 添加一个group by子句来指定聚合函数应该应用于哪个分组

2.3. 单列分组

2.3.1. 最简单，也是最常用的分组类型

2.3.2. mysql

1. -＞ SELECT actor_id, count(*)
2.     -＞ FROM film_actor
3.     -＞ GROUP BY actor_id;

复制代码

2.4. 多列分组

2.4.1. 需要跨越多列生成分组

2.4.2. mysql

1. -＞ SELECT fa.actor_id, f.rating, count(*)
2.     -＞ FROM film_actor fa
3.     -＞   INNER JOIN film f
4.     -＞   ON fa.film_id = f.film_id
5.     -＞ GROUP BY fa.actor_id, f.rating
6.     -＞ ORDER BY 1,2;

复制代码

2.5. 通过表达式分组

2.5.1. 根据表达式产生的值构建分组

2.5.2. mysql

1. -＞ SELECT extract(YEAR FROM rental_date) year,
2.     -＞   COUNT(*) how_many
3.     -＞ FROM rental
4.     -＞ GROUP BY extract(YEAR FROM rental_date);

复制代码

2.6. 分组过滤条件

2.6.1. 由于group by子句是在where子句被评估之后运行的，因此无法为此对where子句增加过滤条件

2.6.2. 无法在where子句中引用聚合函数count(*)

2.6.2.1. 因为在评估where子句时，分组尚未生成，因而必须将分组过滤条件放入having子句

2.6.3. 向包含group by子句的查询中添加过滤条件时，仔细考虑是过滤原始数据（将过滤条件放入where子句），还是过滤分组后的数据（将过滤条件放入having子句）

3. 聚合函数

3.1. 对分组中的所有行执行特定的操作

3.2. max()

3.2.1. 返回集合中的最大值

3.3. min()

3.3.1. 返回集合中的最小值

3.4. avg()

3.4.1. 返回集合中的平均值

3.5. sum()

3.5.1. 返回集合中所有值之和

3.6. count()

3.6.1. 返回集合中所有值的个数

4. 统计不同的值

4.1. mysql

1. -＞ SELECT COUNT(customer_id) num_rows,
2.     -＞   COUNT(DISTINCT customer_id) num_customers
3.     -＞ FROM payment;

复制代码

4.2. 通过指定distinct，count()函数检查分组中每个成员的列值，以便查找和删除重复项，而不是简单地计算分组中值的数量

4.3. mysql

1. -＞ SELECT COUNT(*) num_rows,
2.     -＞   COUNT(val) num_vals,
3.     -＞   SUM(val) total,
4.     -＞   MAX(val) max_val,
5.     -＞   AVG(val) avg_val
6.     -＞ FROM number_tbl;

复制代码

4.4. count(*)统计行数

4.5. count(val)统计val列包含多少个值并且忽略所有遇到的null值

5. 使用表达式

5.1. mysql

1. -＞ SELECT MAX(datediff(return_date,rental_date))
2.     -＞ FROM rental;

复制代码

5.2. 除了使用列作为聚合函数的参数，也可以使用表达式

6. 生成汇总

6.1. 假设在计算每位演员/评级组合的总计数的同时，还想知道不同演员参演的电影总数，这时可以运行一个额外的查询并合并结果

6.2. with rollup选项来让数据库服务器完成这些工作

6.3. mysql

1. -＞ SELECT fa.actor_id, f.rating, count(*)
2.     -＞ FROM film_actor fa
3.     -＞   INNER JOIN film f
4.     -＞   ON fa.film_id = f.film_id
5.     -＞ GROUP BY fa.actor_id, f.rating WITH ROLLUP
6.     -＞ ORDER BY 1,2;

复制代码

6.4. Oracle Database

6.4.1. GROUP BY ROLLUP(fa.actor_id, f.rating)

6.4.2. 可以在group_by子句中对部分列汇总

6.4.3. 如果按照列a、b、c进行分组，可以指示服务器通过下列语句仅对列b和c执行汇总

6.4.3.1. GROUP BY a, ROLLUP(b, c)

6.5. with cube选项

6.5.1. 为分组列的所有可能的组合生成汇总行

6.5.2. MySQL 8.0版并未提供

6.5.3. SQL Server和Oracle Database中可以使

7. 子查询

7.1. 子查询总是被包围在括号中，通常先于包含语句执行

7.2. 子查询像是一个具有语句作用域的临时数据表（这意味着服务器在执行SQL语句后会清空分配给子查询结果的内存）

7.3. 如果不清楚子查询究竟做了什么，可以单独运行子查询（不加括号）并查看返回结果

7.4. 返回的结果集

7.4.1. 单行单列

7.4.2. 多行单列

7.4.2.1. in和not in运算符

7.4.2.1.1. 虽然不能把单个值与一组值进行相等比较，但是可以检查这个值能否包含在一组值中

7.4.2.1.2. mysql

1. -＞ SELECT country_id
2.     -＞ FROM country
3.     -＞ WHERE country IN ('Canada','Mexico');

复制代码

7.4.2.1.3. mysql

1. -＞ SELECT city_id, city
2.     -＞ FROM city
3.     -＞ WHERE country_id IN
4.     -＞  (SELECT country_id
5.     -＞   FROM country
6.     -＞   WHERE country IN ('Canada','Mexico'));

复制代码

7.4.2.1.4. sql

1. SELECT first_name, last_name
2. FROM customer
3. WHERE customer_id NOT IN
4. (SELECT customer_id
5.   FROM payment
6.   WHERE amount = 0)

复制代码

7.4.2.1.4.1. not in的版本更易于理解

7.4.2.2. all运算符

7.4.2.2.1. 将某个值与集合中的所有值进行比较

7.4.2.2.2. mysql

1. -＞ SELECT first_name, last_name
2.     -＞ FROM customer
3.     -＞ WHERE customer_id ＜＞ ALL
4.     -＞  (SELECT customer_id
5.     -＞   FROM payment
6.     -＞   WHERE amount = 0);

复制代码

7.4.2.3. any运算符

7.4.2.3.1. 允许将单个值与一组值中的各个值进行比较

7.4.2.3.2. 只要有一次比较成立，使用any运算符的条件即为真

7.4.2.3.3. any与in等效

7.4.2.4. 使用not in或＜＞运算符比较一个值和一组值时，必须确保这组值中不包含null值，这是因为服务器会将表达式左侧的值与组中的各个值进行比较，任何值与null作相等比较时都会产生unknown

7.4.3. 多行多列

7.4.3.1. mysql

1. -＞ SELECT fa.actor_id, fa.film_id
2.     -＞ FROM film_actor fa
3.     -＞ WHERE fa.actor_id IN
4.     -＞  (SELECT actor_id FROM actor WHERE last_name = 'MONROE')
5.     -＞   AND fa.film_id IN
6.     -＞  (SELECT film_id FROM film WHERE rating = 'PG');

复制代码

7.4.3.2. 可以将两个单列子查询合并成一个多列子查询

7.4.3.3. mysql

1. -＞ SELECT actor_id, film_id
2.     -＞ FROM film_actor
3.     -＞ WHERE (actor_id, film_id) IN
4.     -＞  (SELECT a.actor_id, f.film_id
5.     -＞   FROM actor a
6.     -＞      CROSS JOIN film f
7.     -＞   WHERE a.last_name = 'MONROE'
8.     -＞   AND f.rating = 'PG');

复制代码

7.4.3.4. 过滤条件必须将film_actor数据表的两列放入括号内

7.4.3.5. 与子查询返回的顺序一致

7.4.3.6. 子查询使用了交叉连接

7.5. 子查询类型

7.5.1. 非关联子查询

7.5.1.1. 子查询完全独立

7.5.1.2. 单独执行，不会引用包含语句中的任何内容

7.5.2. 关联子查询

7.5.2.1. 子查询会引用包含语句中的列

7.5.2.2. 并不是先于包含语句一次性执行完毕，而是为每一个候选行（可能会包含在最终结果中）执行一次

7.5.2.2.1. 如果包含查询返回很多行，将会引发性能问题

7.5.2.3. 在MySQL的delete语句中使用关联子查询时，无论如何都不能使用数据表别名，这就是在子查询中使用数据表全名的原因

7.6. exists运算符

7.6.1. 子查询可能会返回0行、1行或者多行结果，然而条件只是简单地检查子查询是否返回至少1行

7.6.2. 惯例是指定select 1或select

7.6.3. 也可以使用not exists来检查没有返回行的子查询

8. 何时使用子查询

8.1. 子查询作为数据源

8.1.1. 数据加工

8.1.1.1. 如果使用子查询，你就能够遵守仅在有明确的存储新数据的业务需求时才向数据库添加新数据表的原则

8.1.2. 面向任务的子查询

8.1.3. 公用表表达式

8.1.3.1. Common table expression，CTE

8.1.3.2. MySQL 8.0版新引入的特性

8.1.3.3. CTE是一个具名子查询，出现在with子句内查询的顶部，该子句可以包含多个以逗号分隔的CTE

8.1.3.4. 使用临时数据表来存储后续查询要用到的查询结果的替代方案

8.2. 子查询作为表达式生成器


来源:https://www.cnblogs.com/lying7/p/17664818.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！