MySQL的增删改查4（插入查询结果，联合查询，聚合查询）

1.插入查询结果

语法：INSERT INTO table_name [(column[,column…])] SELECT…;

把后面select出来的数据插入到前面指定的表中

insert into student1 (id,name)select id,name from student;

表必须要存在，写入的列名顺序与查询中的列名和顺序必须保持一致。

2.聚合查询

2.1聚合函数

count

统计班级共有多少同学

select count(*)from student;

 

 统计班级收集的 qq_mail 有多少个，qq_mail 为 NULL 的数据不会计入结果

select count(qq_mail)from student;

sum

统计数学成绩总分

select sum(math) from exam_result;

 

不及格 < 60 的总分，没有结果，返回 NULL

select sum(math) from exam_result where math<60;

 

 avg

统计平均总分

select avg(chinese+math+english)from exam_result;

max

英语最高分

select max(english) from exam_result;

 

min

语文最低分

select min(chinese) from exam_result;

2.2GROUP BY子句

语法：select column,sum(column2),…from table_name [where…] group by column1,column3;

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

select role,max(salary),min(salary),sum(salary) from emp group by role;

2.3HAVING

如果要对group by分组后的结果做过滤，必须要用having子句

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

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

3.联合查询

3.1内连接

语法：

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

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

步骤：

1.确定表

2.做笛卡尔积

3.确认连接条件

4.加入查询条件

5.精简查询结果

查询“许仙”同学的 成绩

select stu.name,s.score from student2 stu,score s 
where stu.sn=s.student2_id and name='许仙';

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

select stu.sn,stu.name,stu.qq_mail,stu.classes_id,sum(s.score) 
from student2 stu,score s where stu.sn=s.student2_id order by s.student2_id;

3.2外连接

语法：

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

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

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

select * from student2 stu left join score s on stu.sn=s.student2_id;

 3.3自连接

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

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

select s1.* from score s1 join score s2 on 
s1.student2_id=s2.student2_id 
and s1.score<s2.score 
and s1.course_id=1 
and s2.course_id=3;

 3.4子查询

一个查询的条件依赖于另一个查询的结果

单行子查询：返回一行记录的子查询

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

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

 多行子查询：返回多行记录的子查询

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

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

 3.5合并查询

union

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

查询id小于3，或者名字为“英文”的课程：

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

union all

该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。

查询id小于3，或者名字为“Java”的课程

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