【数据库】数据库查询（进阶命令详解）

目录

1.聚合查询

1.1聚合函数 

COUNT函数  

SUM函数 

AVG函数 

 MAX函数

MIN函数 

 1.2GROUP BY子句

1.3HAVING 

2.联合查询 

2.1内连接 

2.2外连接

 2.3自连接

2.4子查询

 3.合并查询

---

写在前面：

1. 文章截图均是每个代码显示的图。
2. 数据库对代码大小写不敏感，可以大写也可以小写，不要把大小写混在一起使用，最好只使用大写或者只使用小写。

1.聚合查询

1.1聚合函数 

常见的统计总数、计算平局值等操作，可以使用聚合函数来实现，常见的聚合函数有：

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的 数量
SUM([DISTINCT] expr)	返回查询到的数据的 总和，不是数字没有意义
AVG([DISTINCT] expr)	返回查询到的数据的 平均值，不是数字没有意义
MAX([DISTINCT] expr)	返回查询到的数据的 最大值，不是数字没有意义
MIN([DISTINCT] expr)	返回查询到的数据的 最小值，不是数字没有意义

COUNT函数  

select count(*) from student;

 我们将参数定位*时，得到就是这个表的所有行数。

我们还可以改变括号中的参数，例如改为qq_mail ：

select count(qq_mail) from student;

 这时得到的数据变为了4，为什么是4，我们查询你这个表看一下：

通过表可以明显看出，非空的数据有4个，空的数据有4个，这样我们就能得出，当数据为空时，COUNT()函数是不计入大小的。

SUM函数 

select sum(score) from score;

 SUM()函数就是将数据加和起来，当加和的列不是数字时，没有意义。

AVG函数 

select avg(score) from score;

 求得指定列平均值。

 MAX函数

select max(score) from score;

求得指定列最大值。 

MIN函数 

select min(score) from score;

 求得指定列最小值。 

 1.2GROUP BY子句

SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足：使用 GROUP BY 进行分组查询时，SELECT 指定的字段必须是“分组依据字段”，其他字段若想出现在SELECT 中则必须包含在聚合函数中。
 语法格式：

select column1, sum(column2), .. from table group by column1,column3;

示例：

 select role ,max(salary),min(salary),avg(salary) from emp group by role;
-- 显示结果
+----------+-------------+-------------+--------------+
| role     | max(salary) | min(salary) | avg(salary)  |
+----------+-------------+-------------+--------------+
| 服务员   |     1000.20 |     1000.20 |  1000.200000 |
| 游戏陪玩 |     2000.99 |     2000.99 |  2000.990000 |
| 游戏角色 |      999.11 |      333.50 |   677.646667 |
| 董事长   |    12000.66 |    12000.66 | 12000.660000 |
+----------+-------------+-------------+--------------+

根据role分组，分别求出每个组的最大值，最小值，平均值，在显示出角色名，最大值，最小值，平均值。

1.3HAVING 

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

select role ,max(salary),min(salary),avg(salary) from emp group by role having avg(salary)>1000;
-- 显示内容
+----------+-------------+-------------+--------------+
| role     | max(salary) | min(salary) | avg(salary)  |
+----------+-------------+-------------+--------------+
| 服务员   |     1000.20 |     1000.20 |  1000.200000 |
| 游戏陪玩 |     2000.99 |     2000.99 |  2000.990000 |
| 董事长   |    12000.66 |    12000.66 | 12000.660000 |
+----------+-------------+-------------+--------------+

根据role分组，分别求出每个组的最大值，最小值，平均值，根据having语句限制只显示大于1000的数据。

2.联合查询 

 笛卡尔积：将两个表同时进行查询，得到的汇总的表。如下方命令：

select * from classes,student;

这个命令就是将两个表中的所有数据全部显示出来，这样的好处是，我们可以同时操作两张表的数据。

2.1内连接 

语法格式：

select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

 示例1：查询许仙同学的成绩

 select student.name,score.score from student join score on student.id=score.student_id and student.name='许仙';

 示例2：查询所有同学的总成绩，及同学的个人信息并且以成绩中的id分组

 select student.sn,student.name,student.qq_mail,sum(score.score) from student join score on student.id=score.student_id group by score.student_id;

 示例3：查询所有同学的成绩，及同学的个人信息

 select student.id ,student.sn,student.name,student.qq_mail,score.score from student join score on student.id=score.student_id order by student.id ;

2.2外连接

外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。 

语法格式：

-- 左外连接，表1完全显示
select 字段名 from 表名1 left join 表名2 on 连接条件;
-- 右外连接，表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;

 示例1：查询所有同学的成绩，及同学的个人信息，如果该同学没有成绩也需要显示

-- 左外连接
select * from student stu left join score sco on stu.id=sco.student_id;

-- 右外连接
select * from score sco right join student stu on stu.id=sco.student_id;

 2.3自连接

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

 示例：显示所有“计算机原理”成绩比“Java”成绩高的成绩信息

-- 先查询“计算机原理”和“Java”课程的id
select id,name from course where name='Java' or name='计算机原理';
-- 再查询成绩表中，“计算机原理”成绩比“Java”成绩 好的信息
SELECT
    s1.*
FROM
    score s1,
    score s2
WHERE
    s1.student_id = s2.student_id
    AND s1.score < s2.score
    AND s1.course_id = 1
    AND s2.course_id = 3;

可以用join on语句实现：

-- 使用join on 语句来进行自连接查询
SELECT
    s1.*
FROM
    score s1
JOIN score s2 ON s1.student_id = s2.student_id
    AND s1.score < s2.score
    AND s1.course_id = 1
    AND s2.course_id = 3;

2.4子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。

示例1： 单行子查询

查询与“不想毕业” 同学的同班同学 ：

select * from student where classes_id=(select classes_id from student where
name='不想毕业');

 示例2：多行子查询

查询“语文”或“英文”课程的成绩信息

select * from score where course_id in (select id from course where
name='语文' or name='英文');

 3.合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用UNION和UNION ALL时，前后查询的结果集中，字段需要一致。

• union 

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。
示例：查询id小于3，或者名字为“英文”的课程
 

select * from course where id<3
union
select * from course where name='英文';

• union all 

该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。
案例：查询id小于3，或者名字为“Java”的课程

-- 可以看到结果集中出现重复数据Java
select * from course where id<3
union all
select * from course where name='英文';