本文共 9879 字，大约阅读时间需要 32 分钟。

聚合查询

聚合函数

COUNT

• 返回查询到的数量

mysql> select * from emp;+----+---------+-----------+---------+| id | name    | role      | salary  |+----+---------+-----------+---------+|  1 | 丫丫1   | 老师      |  200.00 ||  2 | 丫丫2   | 老师      |  300.00 ||  3 | 丫丫3   | 老师      |  400.00 ||  4 | 丫丫4   | 老师      |  500.00 ||  5 | 丫丫5   | 老师      |  600.00 ||  6 | 丫丫6   | 班主任    |  700.00 ||  7 | 丫丫7   | 班主任    |  800.00 ||  8 | 丫丫8   | 教导员    |  900.00 ||  9 | 丫丫9   | 教导员    | 1000.00 |+----+---------+-----------+---------+9 rows in set (0.01 sec)mysql> select count(id) from emp;+-----------+| count(id) |+-----------+|         9 |+-----------+1 row in set (0.01 sec)

SUM

• 返回查询到的数据的总和,不是数字没有意义

mysql> select sum(salary) from emp;+-------------+| sum(salary) |+-------------+|     5400.00 |+-------------+1 row in set (0.00 sec)

AVG

• 返回查询到的数据的平均值,不是数字没有意义

mysql> select avg(salary) from emp;+-------------+| avg(salary) |+-------------+|  600.000000 |+-------------+1 row in set (0.00 sec)

MAX

• 返回查询到的数据的最大值,不是数字没有意义

mysql> select max(salary) from emp;+-------------+| max(salary) |+-------------+|     1000.00 |+-------------+1 row in set (0.00 sec)

MIN

• 返回查询到的数据的最小追,不是数据没有意义

mysql> select min(salary) from emp;+-------------+| min(salary) |+-------------+|      200.00 |+-------------+1 row in set (0.00 sec)

GROUP BY 语句

• select中使用group by 语句 可以对指定列进行分组查询
• 注意: 使用group by进行分组查询时,SELECT 指定的字段必须是“分组依据字段”，其他字段若想出现在SELECT 中则必须包含在聚合函数中。

-- 查询每个角色的总工资mysql> select role, sum(salary) from emp group by role;+-----------+-------------+| role      | sum(salary) |+-----------+-------------+| 教导员    |     1900.00 || 班主任    |     1500.00 || 老师      |     2000.00 |+-----------+-------------+3 rows in set (0.00 sec)

HAVING 语句

• GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用
  HAVING

-- 查询每个角色总工资大于1500的mysql> select role, sum(salary) as totle from emp group by role having totle > 1500;+-----------+---------+| role      | totle   |+-----------+---------+| 教导员    | 1900.00 || 老师      | 2000.00 |+-----------+---------+2 rows in set (0.00 sec)

联合查询

笛卡尔积

• 实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积.
  
• 关联查询的时候可以对关联表使用别名

内连接

• 使用内连接的思路:

1. 观察要查询的数据涉及到几张表
2. 多张表连接时使用的是笛卡尔积运算,所以会产生许多无用的数据,此时我们就应该加上表的连接条件去除掉无用的数据
3. 再用选择条件和字段去控制得到的数据表为我们所需要的

-- 查询张飞的成绩,其涉及到学生表和成绩表mysql> select student.id, student.name, score from student, score where student.id = score.student_id and student.name = '张飞';+----+-----------+-------+| id | name      | score |+----+-----------+-------+|  3 | 张飞    |  33.0 ||  3 | 张飞    |  68.0 ||  3 | 张飞    |  99.0 |+----+-----------+-------+3 rows in set (0.00 sec)-- 还有这种写法mysql> select student.id, student.name, score from student join score on student.id = score.student_id and student.name = '张飞';+----+-----------+-------+| id | name      | score |+----+-----------+-------+|  3 | 张飞    |  33.0 ||  3 | 张飞    |  68.0 ||  3 | 张飞    |  99.0 |+----+-----------+-------+3 rows in set (0.00 sec)

– 结合分组查询

-- 查询所有同学的成绩mysql> select student.id, student.name, score from student, score where student.id = score.student_id;+----+-----------+-------+| id | name      | score |+----+-----------+-------+|  1 | 诸葛亮    |  70.5 ||  1 | 诸葛亮    |  98.5 ||  1 | 诸葛亮    |  33.0 ||  1 | 诸葛亮    |  98.0 ||  2 | 刘备      |  60.0 ||  2 | 刘备      |  59.5 ||  3 | 张飞      |  33.0 ||  3 | 张飞      |  68.0 ||  3 | 张飞      |  99.0 ||  4 | 关羽      |  67.0 ||  4 | 关羽      |  23.0 ||  4 | 关羽      |  56.0 ||  4 | 关羽      |  72.0 ||  5 | 孙权      |  81.0 ||  5 | 孙权      |  37.0 ||  6 | 吕布      |  56.0 ||  6 | 吕布      |  43.0 ||  6 | 吕布      |  79.0 ||  7 | 曹操      |  80.0 ||  7 | 曹操      |  92.0 |+----+-----------+-------+20 rows in set (0.00 sec)-- 查询所有同学的总成绩mysql> select student.id, student.name, sum(score) from student, score where student.id = score.student_id group by student.id;+----+-----------+------------+| id | name      | sum(score) |+----+-----------+------------+|  1 | 诸葛亮    |      300.0 ||  2 | 刘备      |      119.5 ||  3 | 张飞      |      200.0 ||  4 | 关羽      |      218.0 ||  5 | 孙权      |      118.0 ||  6 | 吕布      |      178.0 ||  7 | 曹操      |      172.0 |+----+-----------+------------+7 rows in set (0.00 sec)

外连接

左外连接

• 联合查询,左侧的表完全显示我们就说是左外连接

-- 查成绩,没有成绩也要体现mysql> select student.id, student.name, score from student left join score on student.id = score.student_id;

右外连接

• 联合查询,右侧的表完全显示我们就说是右外连接

mysql> select student.id, student.name, score from student right join score on student.id = score.student_id;

-- 三张表的关联查询mysql> select student.id, student.name, score from    -> student    -> left join score on student.id = score.student_id    -> left join course on score.course_id = course.id    -> where score < 60 or score <=> null    -> order by score desc;+----+-----------+-------+| id | name      | score |+----+-----------+-------+|  2 | 刘备      |  59.5 ||  4 | 关羽      |  56.0 ||  6 | 吕布      |  56.0 ||  6 | 吕布      |  43.0 ||  5 | 孙权      |  37.0 ||  1 | 诸葛亮    |  33.0 ||  3 | 张飞      |  33.0 ||  4 | 关羽      |  23.0 ||  8 | 赵云      |  NULL |+----+-----------+-------+9 rows in set (0.00 sec)

自连接

• 自连接是指在同一张表连接自身进行查询

-- 查 计算机原理 比 java高的成绩信息mysql> select s1.course_id, s1.score, s2.course_id, s2.score    -> from    -> score s1, score s2    -> where    ->  s1.student_id = s2.student_id    -> and s1.course_id = (select id from course where name = '计算机原理')    -> and s2.course_id = (select id from course where name = 'java')    -> and s1.score > s2.score;+-----------+-------+-----------+-------+| course_id | score | course_id | score |+-----------+-------+-----------+-------+|         3 |  98.5 |         1 |  70.5 ||         3 |  68.0 |         1 |  33.0 |+-----------+-------+-----------+-------+2 rows in set (0.00 sec)

子查询

• 在一个查询语句里套用查询语句称为子查询

单行子查询

• 子查询返回一条结果

-- 查询与刘备同班同学的总成绩mysql> select student.id, student.name, sum(score)    -> from    -> student, score    -> where    -> student.id = score.student_id    -> and student.classes_id = (select student.classes_id from student where name = '刘备')    -> group by student.id;+----+-----------+------------+| id | name      | sum(score) |+----+-----------+------------+|  1 | 诸葛亮    |      300.0 ||  2 | 刘备      |      119.5 ||  3 | 张飞      |      200.0 ||  4 | 关羽      |      218.0 ||  5 | 孙权      |      118.0 |+----+-----------+------------+5 rows in set (0.00 sec)

多行子查询

• 子查询返回多行结果

IN 处理多行

-- 查询语文和高阶数学的成绩mysql>  select score.course_id, score    -> from    -> score    -> where    ->  score.course_id    ->  in    -> (select id from course where name = '语文' or name = '高阶数学');+-----------+-------+| course_id | score |+-----------+-------+|         5 |  33.0 ||         5 |  59.5 ||         5 |  99.0 ||         5 |  56.0 ||         5 |  37.0 ||         4 |  43.0 |+-----------+-------+6 rows in set (0.00 sec)

• 还有not in

EXISTS 处理多行

• 括号不能省

mysql> select    -> score.course_id, score    -> from    -> score    -> where exists    -> (select score.course_id from course where  (name = '语文' or name = '高阶数 学')    -> and course.id = score.course_id);+-----------+-------+| course_id | score |+-----------+-------+|         5 |  33.0 ||         5 |  59.5 ||         5 |  99.0 ||         5 |  56.0 ||         5 |  37.0 ||         4 |  43.0 |+-----------+-------+6 rows in set (0.00 sec)

IN 与 EXISTS 的区别

1. in 的子查询是 先执行子查询,把结果保存到内存中,再进行主查询,最后结合子查询的结果进行筛选
2. exists 的子查询是 先执行主查询,再触发子查询,最后子查询的结果会对主表查询结果的每一条进行筛选.
3. 如果子查询的结果小,就用in
4. 如果子查询大,而主查询小,就用 exists

合并查询

• 合并多个select的查询结果,但必须保证合并表的字段需一致

UNION

• 将俩个结果集进行并集, 会去掉集中的重复行

mysql> select * from student where id > 3 or id < 7;+----+-------+-----------+------------------+------------+| id | sn    | name      | qq_mail          | classes_id |+----+-------+-----------+------------------+------------+|  1 | 09982 | 诸葛亮    | xuanfeng@qq.com  |          1 ||  2 | 00835 | 刘备      | NULL             |          1 ||  3 | 00391 | 张飞      | NULL             |          1 ||  4 | 00031 | 关羽      | xuxian@qq.com    |          1 ||  5 | 00054 | 孙权      | NULL             |          1 ||  6 | 51234 | 吕布      | say@qq.com       |          2 ||  7 | 83223 | 曹操      | NULL             |          2 ||  8 | 09527 | 赵云      | foreigner@qq.com |          2 |+----+-------+-----------+------------------+------------+8 rows in set (0.00 sec)mysql> select * from student where id > 3 union select * from student where id < 7;+----+-------+-----------+------------------+------------+| id | sn    | name      | qq_mail          | classes_id |+----+-------+-----------+------------------+------------+|  4 | 00031 | 关羽      | xuxian@qq.com    |          1 ||  5 | 00054 | 孙权      | NULL             |          1 ||  6 | 51234 | 吕布      | say@qq.com       |          2 ||  7 | 83223 | 曹操      | NULL             |          2 ||  8 | 09527 | 赵云      | foreigner@qq.com |          2 ||  1 | 09982 | 诸葛亮    | xuanfeng@qq.com  |          1 ||  2 | 00835 | 刘备      | NULL             |          1 ||  3 | 00391 | 张飞      | NULL             |          1 |+----+-------+-----------+------------------+------------+8 rows in set (0.00 sec)

UNION ALL

• 将俩个结果集进行并集, 不会去掉集中的重复行

mysql> select * from student where id > 3 union all select * from student where id < 7;+----+-------+-----------+------------------+------------+| id | sn    | name      | qq_mail          | classes_id |+----+-------+-----------+------------------+------------+|  4 | 00031 | 关羽      | xuxian@qq.com    |          1 ||  5 | 00054 | 孙权      | NULL             |          1 ||  6 | 51234 | 吕布      | say@qq.com       |          2 ||  7 | 83223 | 曹操      | NULL             |          2 ||  8 | 09527 | 赵云      | foreigner@qq.com |          2 ||  1 | 09982 | 诸葛亮    | xuanfeng@qq.com  |          1 ||  2 | 00835 | 刘备      | NULL             |          1 ||  3 | 00391 | 张飞      | NULL             |          1 ||  4 | 00031 | 关羽      | xuxian@qq.com    |          1 ||  5 | 00054 | 孙权      | NULL             |          1 ||  6 | 51234 | 吕布      | say@qq.com       |          2 |+----+-------+-----------+------------------+------------+11 rows in set (0.00 sec)