聚合函数
常见的统计总数、计算平局值等操作,可以使用聚合函数来实现,常见的聚合函数有:
函数 | 说明 |
---|---|
count() | 统计数据总数 |
sum() | 求和 |
avg() | 求平均值 |
max() | 求最大值 |
min() | 求最小值 |
注意凡是涉及运算的,数据库会自动掉 NULL 值
注意NULL 是不参与比较 max 与 min 的
解析:
以此表为例
count()
count(),会统计数据总数
使用 count(*) 会查询一共有多少条数据行
使用 count(列名),会统计该列有多少行数据
如果列中有NULL 值,则不会被统计在内
建议使用 count(*) 来统计数据行,这是SQL 标准提出的。
sum()
如果运算中有NULL 值会自动过滤NULL,因为NULL 经过运算后为 NULL这个数据是没有意义的,所以数据库的开发者们进行了这样的运算设计。
如果运算遇到非数字型数据,则无法进行运算,会报警告:
注意可以使用表达式,但是如果想分别求每一列的总分还是要分开写的。
avg()
max() 与 min()
注意NULL 是不参与比较的
实践
1.统计班级共有多少同学
select count(name) from exam;
2.统计班级收集的 math 数学成绩数据 有多少个
select count(math) from exam;
3.统计数学成绩总分
select sum(math) from exam;
4.统计所有数学成绩不及格 (< 60) 的同学的数学总分
select sum(math) from exam where math < 60;
5.统计三科的平均总分
select avg(chinese + math + english) 三科平均分 from exam;
6.返回英语最高分
select max(english) from exam;
7.返回 > 70 分以上的数学最低分
select min(math) from exam where math > 70;
分组查询
group by
SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足:使用 GROUP BY 进行分组查询时,SELECT 指定的字段必须是“分组依据字段”,其他字段若想出现在SELECT 中则必须包含在聚合函数中。
select column1, sum(column2), .. from table group by column1,column3;
演示表:
计算每种职位的平均工资:
这里的执行顺序是先分组再计算。
拓展 round
可以使用 round(数值,小数点后的位数) 来指定数值的形式:
group by 后面可以跟 order by 子句
练习:
查询每个角色的最高工资、最低工资和平均工资
select role 职位, max(salary) 最高工资, min(salary) 最低工资 from emp group by 职位;
having
GROUP BY 子句进行分组以后,需要对分组结果再进行条件过滤时,不能使用 WHERE 语句,而需要用
HAVING
where 是对表中每一行的真实数据进行过滤的
having 是在 group by 之后,对计算结果进行过滤的。
所以两个执行顺序是不一样的,having 可以使用别名来过滤
演示:
显示平均工资低于1500的角色和它的平均工资
select role 职位, avg(salary) 平均工资 from emp group by 职位 having 平均工资 < 1500;
联合查询
实际开发中往往数据来自不同的表,所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积:
笛卡尔积实际上就是对数据进行全排列,举个例子,有两张表,其中一张表的一条数据要和另一张表的所有的数据进行组合:
我们也可以通过 SQL 代码来查看笛卡尔积:select * from table_name1, table_name2;
![在这
通过观察我们得知上面全排列的数据不全是正确的,那我们如果过滤掉这些无效的数据,从而获取正确的数据?
请看下面揭晓
内连接
语法格式:select 字段 from 表1 别名1, 表2 别名2 where 条件;
或者 select 字段 from 表1 别名1 [inner] join 表2 别名2 on 条件;
两个表之间存在主外键关系的话,只需要判断这两个表中主外键字段是否相同即可。
查询列表的字段 可以使用
表名.列名
我们可以通过给表名取字段的方式来减少我们的书写量。
演示:
select s.student_id, s.sn, s.name, s.mail, c.name from student s, class c where s.class_id = c.class_id;
select s.student_id, s.sn, s.name, s.mail, c.name from student s inner join class c on s.class_id = c.class_id;
当你给表取了别名之后,那就将表名的地方全部替换成别名,否则 where 子句会识别不出。
联合查询的步骤:
首先确定查询中涉及哪些表,然后对这些表取笛卡尔积,再确定连接条件与过滤条件,最后简化语句(使用别名)
实践:
查询白素贞的成绩:
首先确定需要哪些表:学生表和成绩表,取笛卡尔积:
然后确定连接条件:student_id 是相同的
确定过滤条件:姓名是白素贞
简化 sql 语句,将student 取 stu , score 取 sco
select stu.name, sco.score from student stu, score sco where name = '白素贞' and stu.student_id = sco.student_id;
select stu.name, sco.score from student stu join score sco on name = '白素贞' and stu.student_id = sco.student_id;
查询所有同学的总成绩,及同学的个人信息:
首先确定需要什么表:学生表,成绩表;然后取笛卡尔积:
然后确定连接条件与过滤条件:首先是由于需要的是总成绩,所以要使用聚合函数 sum(),那么就要使用到 分组查询 group by 子句,接着成绩表和学生表的连接是 student_id 要相同
这里要注意分组的依据,我们是对成绩表进行分组的,成绩表有学生的 id 和 成绩,那就应该是要按学生的 id 作为分组的依据。
最后简化 sql 语句 将student 取 stu , score 取 sco
select stu.name, stu.mail, sum(sco.score) from student stu, score sco where stu.student_id = sco.student_id group by sco.student_id;
查询所有同学的总成绩,及同学的个人信息 以及 学生所在的班级信息:
首先确定要几张表:学生表,班级表 以及 成绩表,然后取笛卡尔积:
然后确定连接条件与过滤条件:学生表和班级表的联系是 class_id 相同,学生表和成绩表的联系是 student_id 相同,总成绩就和上面的方式一样使用 sum() 通过 student_id 来进行分组。
然后简化 sql 语句:
select stu.sn 学号, stu.name 姓名, stu.mail 邮箱, sum(sco.score) 总成绩, c.name from student stu, score sco, class c where stu.student_id = sco.student_id and stu.class_id
= c.class_id group by sco.student_id;
外连接
外连接分为左外连接和右外连接。如果联合查询,左侧的表完全显示就是左外连接;右侧的表完全显示就是右外连接。
语法:左外连接: select 字段名 from 表名1 left join 表名2 on 连接条件;
与 右外连接: select 字段 from 表名1 right join 表名2 on 连接条件;
大家来看一下下面两张表,你会发现 3班 是没有学生的。
现在我们基于上述的式子,演示左外连接:select * from class c left join student s on c.class_id = s.class_id;
即使 3 班是没有同学的,但是3班这个字段还是会显示出来,只是对应的学生列表为空。
现在我们插入一个没有班级的学生数据:
然后我们来演示右外连接:select * from class c right join student s on c.class_id = s.class_id;
这里会将 student 表全部显示,即使有学生没有班级这个数据。
进行外连接如果遇到没有数据的时候,数据库会使用 NULL 填充。
自连接
自连接是指在同一张表连接自身进行查询。
语法:select * from 表名1 别名1, 表名1 别名2;
注意一定要起别名,不然MySQL 无法识别:
一般自连接会用在自己要和自己比较的时候
演示:
查询哪些学生的 Java 成绩 比 计算机原理要低:可以先查出Java 和 计算机原理的 course_id
select * from score s1, score s2 where s1.course_id = 1 and s2.course_id = 3 and s1.score < s2.score;
子查询
子查询是指嵌入在其他sql语句中的select语句,也叫嵌套查询
单行子查询:
基本语法:select * from table_name where 列名 = (select 列名 from table_name where 条件);
查询与“白素贞” 同学的同班同学:select * from student where class_id = (select class_id from student where name = '白素贞');
多行子查询:
[NOT] IN
in 之前提到过就是在不在 in 括号的字段范围内
语法:select * from table_name1 where 列名 [not] in (select * from table_name2);
举例:查询 Java 和 计算机的成绩
select * from score where course_id in (select course_id from course where name = 'Java' or name = '计算机原理');
[NOT] EXISTS
exists 表示存在,如果 exists 后面括号中的查询语句,如果返回的是空结果集,那就类似flase ,不会执行外层的查询,如果返回的是 true ,就会执行外层的查询。
语法:select * from table_name where [not] exists (select * from table_name);
注意如果集合是 select null;
集合不为空(empty),只是集合内容是 null
演示:
合并查询
合并查询可以将多个结果集合并。
使用UNION和UNION ALL时,前后查询的结果集中,字段需要一致。
union 【会去重】
该操作符用于取得两个结果集的并集。当使用该操作符时,会自动去掉结果集中的重复行。
union all 【不会去重】
该操作符用于取得两个结果集的并集。当使用该操作符时,不会去掉结果集中的重复行。
查询id小于3,或者名字为“英文”的课程:
在单表查询时,推荐使用 or, 多表查询时可以使用 union 或者 union all