mysql增删改表查询统计数量_MySQL表的增删改查（进阶）下

4.查询

4.1聚合查询

4.1.1聚合函数

在MySQL中常见的统计可以使用聚合函数来实现，常见的聚合函数有：

函数

说明

count[dintinct] expr

返回查询到的数量(行数)

sum[dintinct] expr

返回查询到的数据的总和

avg[dintinct] expr

返回查询到的数据的平均值

max[dintinct] expr

返回查询到的数据的最大值

min[dintinct] expr

返回查询到的数据的最小值

案例：

在之前的exam_result表中返回 > 70 分以上的数学最低分。

SELECT MIN(math) FROM exam_result WHERE math > 70;

注意：

这里的最小值是指在math>70的前提下的最小值，意思是在执行聚合函数取最小值之前先进行where条件筛选，在筛选结果中取最小值。

4.1.2 group by 子句

在select 查询中使用group by子句可以对指定的列进行分组查询。

准备测试案例：

准备测试表及数据：职员表，有id(主键)、name(姓名)、role(角色)、salary(薪水)

create table emp(

id int primary key auto_increment,

name varchar(20) not null,

role varchar(20) not null,

salary numeric(11,2)

);

-- 插入数据

insert into emp(name, role, salary) values

('马云','服务员', 1000.20),

('马化腾','游戏陪玩', 2000.99),

('孙悟空','游戏角色', 999.11),

('猪无能','游戏角色', 333.5),

('沙和尚','游戏角色', 700.33),

('隔壁老王','董事长', 12000.66);

简单演示一下什么是分组，插入上面的数据之后执行下列语句：

查询每个角色的最高工资、最低工资和平均工资

select count(name),role,max(salary),min(salary),avg(salary) from emp group by role;

group by以role分为4组，在执行求和，平均值等一些聚合函数时是在求每个组里面的总和，平均值等。

注意：

如上面的查询语句，select指定的字段(要显示的列)必须是group by后面的分组依据字段(这里是role)若想要显示其他字段则必须包含在聚合函数中。

4.1.3 having语句

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

即where语句是在分组之前进行条件过滤(数据筛选)，而要想对分组之后的数据再次进行条件过滤则需要使用having语句。

例如：

显示平均工资低于1500的角色和它的平均工资。

select role,max(salary),min(salary),avg(salary) from emp group by role having avg(salary)<1500;

这里将分组之后平均工资低于1500的数据过滤掉了。

4.2.联合查询(重点)

实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积：

例如上图对 R表和S表取笛卡尔积得到表T，即对两张表进行排列组合得到的集合。

注意：

关联查询允许对表取别名。

测试数据准备：

insert into classes(name, `desc`) values

('计算机系2019级1班', 'C和Java语言、数据结构和算法'),

('中文系2019级3班','学习了中国传统文学'),

('自动化2019级5班','学习了机械自动化');

insert into student(sn, name, qq_mail, classes_id) values

(09982,'黑旋风李逵','xuanfeng@qq.com',1),

(00835,'菩提老祖',null,1),

(00391,'白素贞',null,1),

(00031,'许仙','xuxian@qq.com',1),

(00054,'不想毕业',null,1),

(51234,'好好说话','say@qq.com',2),

(83223,'tellme',null,2),

(09527,'老外学中文','foreigner@qq.com',2);

insert into course(name) values

('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');

insert into score(score, student_id, course_id) values

-- 黑旋风李逵

(70.5, 9, 1),(98.5, 9, 3),(33, 9, 2),

-- 菩提老祖

(60, 10, 1),(59.5,10, 3),

-- 白素贞

(33, 11, 1),(68, 11, 3),(99, 11, 2),

-- 许仙

(67, 12, 1),(23, 12, 3),(56, 12, 2),

-- 不想毕业

(81, 13, 1),(37, 13, 3),

-- 好好说话

(56, 14, 2),(43, 14, 3),(79, 14, 1),

-- tellme

(80, 15, 2),(92, 15, 3);

4.2.1 内连接

语法：

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

select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

案例：

查询“许仙”同学的 成绩

select

stu.name, -- 学生表中的name字段

sco.score -- 成绩表的成绩列

from

student stu -- 表1 学生表的别名stu

inner join score sco -- 表2成绩表 别名为sco

on stu.id=sco.student_id and stu.name='许仙';

连接条件：inner join成绩表中的id字段=成绩表中的student_id字段，and学生的名字为许仙

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

学生表、成绩表、课程表3张表关联查询

select

-- 这里因为是不同的表中的字段所以表示字段时需要用表名.字段

stu.id,

stu.sn,

stu.name,

stu.qq_mail,

sco.score,

sco.course_id,

cou.name

from

student stu

join score sco on stu.id = sco.student_id

join course cou on sco.course_id = cou.id

order by

stu.id;

4.2.2 外连接

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

语法：

左外连接，表1完全显示

select 字段名 from 表名1 left join 表名2 on 连接条件;

右外连接，表2完全显示

select 字段 from 表名1 right join 表名2 on 连接条件;

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

学生表、成绩表、课程表3张表关联查询

SELECT

stu.id,

stu.sn,

stu.NAME,

stu.qq_mail,

sco.score,

sco.course_id,

cou.NAME

FROM

student stu

LEFT JOIN score sco ON stu.id = sco.student_id

LEFT JOIN course cou ON sco.course_id = cou.id

ORDER BY

stu.id

4.2.3 自连接

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

案例：

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

SELECT

stu.*,

s1.score Java,

s2.score 计算机原理

FROM

score s1

JOIN score s2 ON s1.student_id = s2.student_id

JOIN student stu ON s1.student_id = stu.id

JOIN course c1 ON s1.course_id = c1.id

JOIN course c2 ON s2.course_id = c2.id

AND s1.score < s2.score

AND c1.NAME = 'Java'

AND c2.NAME = '计算机原理';

4.2.4 子查询

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

案例：

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

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

4.2.5 合并查询

在实际应用中，为了合并多个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”的课程。

select * from course where id<3

union all

select * from course where name='英文';