数据库（三）分组查询，having条件，语句执行顺序，内毗连，外毗连，子查询 ...

分组查询：
　　select  列名 from 表名 group by 列
　　在分组查询中使用聚合查询，会对每一个组别单独执行一次聚合操作
　　在执行聚合查询大概是分组查询中，只能查询被分组的列或聚合列
同时对多个列进行分组：
　　select 列名 from 表名 group by 列，列

having条件筛选:
　　select  列名 from 表名 group by 列 having 条件
　　having和where一样表示条件的筛选过滤，但是having只能和group连合使用
having和where条件的相同点和区别点:
　　1.都表示条件的过滤
　　2.having只能写在group后，where可以出现任何环境
　　3.先执行where再执行group by最后执行having
　　4.having中可以使用聚合函数，where不能使用
语句的执行顺序：
　　select 列 from 表 where条件 group by 列 having 条件 order by 列
　　　　1.from
　　　　2.where
　　　　3.group by
　　　　4.having条件
　　　　5.select
　　　　6.order by
例子数据

1. -- 创建学生表
2. create table student
3. (
4.     stuId int primary key auto_increment,
5.     stuName varchar(20) not null,
6.     email varchar(50)
7. );
9. -- 创建课程表
10. create table course
11. (
12.     courseId int primary key auto_increment,
13.     courseName varchar(20) not null
14. );
16. -- 创建成绩表
17. create table score
18. (
19.     stuId int not null,
20.     courseId int not null,
21.     score float
22. );
24. -- 插入学生数据
25. insert into student values(null,"tom","tom@geekhome.com");
26. insert into student values(null,"jack","jack@geekhome.com");
27. insert into student values(null,"tony","tony@geekhome.com");
28. insert into student values(null,"rose","rose@geekhome.com");
30. -- 添加课程数据
31. insert into course values(null,"Java");
32. insert into course values(null,"MySQL");
33. insert into course values(null,"JDBC");
35. -- 添加成绩信息
36. insert into score values(1,1,45);
37. insert into score values(1,1,58);
38. insert into score values(1,1,60);
39. insert into score values(1,2,78);
40. insert into score values(1,3,82);
41. insert into score values(2,1,90);
42. insert into score values(2,2,92);
43. insert into score values(2,3,78);
44. insert into score values(3,1,53);
45. insert into score values(3,2,45);
46. insert into score values(3,3,75);
47. -- 查看班级的平均分
51. select avg(score) as avg_score from score;
52. -- 对课程进行分组查询
54. -- 在分组查询，只能对被分组的列进行查看
56. select courseId from score group by courseId;
57. -- 在分组查询中使用聚合查询，会对每一个组别单独执行一次聚合操作
58. -- 在执行聚合查询或者是分组查询中，只能查询被分组的列或聚合列
60. -- 查看每一门课程的平均分
62. select courseId,avg(score) from score group by courseId;
63. -- 查询每个部门总薪资和平均薪资
65. select department_id,sum(salary) as sum_salary,avg(salary) as avg_salary from emp group by department_id;
66. -- 查询每个岗位的人数
68. select job_id,count(*) as empCount from emp group by job_id;
69. -- 查询每个部门中最高的薪资和最低的薪资
71. select department_id,max(salary) as maxSalary,min(salary) as minSalary from emp group by department_id;
72. -- 每个部门中入职时间是晚于2010年的员工总数
74. select department_id,count(*) as empCount from emp where hire_date>='2010-1-1' group by department_id;
75. -- 查询参加单科累计考试次数达到5次以上的所有科目的平均成绩
76. -- where条件中不允许进行聚合操作
77. -- having表示在group by分组后执行的条件筛选，可以使用聚合函数
79. select courseId,avg(score) from score group by courseId having count(*)>5
80. -- 查询参加过补考的同学编号
81. -- 思路：每个学生考的每门课程超过1次以上的，就是参加过补考的
83. -- 同时对多个列进行分组
85. -- 要查的是每个学生参加过的每一门考试超过一次
86. select stuId from score group by stuid,courseId having count(*)>1;
88. -- 以下两个都不是要查询的结果
89. --查询的是每个学生考试超过一次，即这个学生参加过的所有课程
90. select stuId from score group by stuid having count(*)>1;
91. -- 查询的是所有学生参加的课程考试超过一次的，即所有参加过这门考试的数据
92. select stuId from score group by courseId having count(*)>1;

复制代码

数据高级查询：
　　如果希望查询得到学生的姓名课程科目和成绩该怎么办？
　　姓名来自于学生表，课程来自于课程表，而成绩又来自于成绩表，所以必须从三张表中获取各个数据

表毗连查询：多表查询时，如果两张表中存在同名的字段，则必须要使用表名.字段名 加以区分（不同名不用加）
　　（多表查询时只查询单个表中的数据用子查询，需要查找多表中的数据需要用内毗连和外毗连）
　　1.inner   join：
　　　　内毗连查询  获取两表中共同部分的数据
　　　　语句：select 列 from 表A inner join 表B on 表A.列=表B.列 [where 条件]
　　2.left   join：
　　　　左外毗连  获取左表中的所有数据和右表中匹配的数据
　　　　左毗连以左表为基表去毗连右边的数据表，查询出左表的所有数据以及右边和左表关联的数据
　　3.right   join：
　　　　右外毗连   获取右表中的所有数据和左表中匹配的数据
　　（左毗连和右毗连只是基表不一样，表的排放顺序不一样，其余没有任何区别，会一种就行)

　　　　　　　　　　　　　　　inner join ··· on 的执行机制：查找学生的姓名和成绩，通过stuid进行毗连

   　　　　　　　left   join 左毗连的执行机制：找到没有到场测验的学生(最后只要查找右表不为null的列值为null的环境)

-- inner join 内联接查询多表的数据
-- 查询所有学生的姓名和课程编号和对应的成绩
方法一：inner join ··· on

1. -- 多表查询时，如果两张表中存在同名的字段，则必须要使用表名.字段名 加以区分
2. select stu.stuName,s.courseId,s.score from student as stu//表一 inner join score as s//表二 on stu.stuId=s.stuId//两张表的连接条件;

复制代码

方法二：from a表，b表 where 关联的条件

1. -- 使用from A表,B表也可以实现多表的关联查询（不写where的时候可以查看交叉的具体情况）
3. select stuName,courseId,score from student as stu,score as s where stu.stuId=s.stuId;
5. -- 查询所有及格的学生的编号、课程名称以及成绩
7.  -- 课程表和成绩表通过courseID进行连接
9. select stuId,courseName,score from course as c inner join score as s on c.courseId=s.courseId where score>=60;
10. -- 查询所有学生的姓名，课程名称、成绩
12. -- inner join 每次只能连接两个表，连接以后生成一个新的表，然后再去连接另外的表
14. --多表连接是有顺序的，要两个表键有直接联系的才能直接连接（学生表——成绩表——课程表）
16. select stuName,courseName,score from
17. student as stu inner join score as s on stu.stuId=s.stuId -- 学生表和成绩表连接
18. inner join course as c on s.courseId=c.courseId;-- 成绩表和成绩表连接

复制代码

课堂练习

1. -- 查询员工的姓名、薪资和所在部门的名称
2. select first_name,last_name,salary,department_name from emp inner join dep on emp.department_id=dep.department_id;
4. -- 查询部门编号是50的部门名称和该部门每个员工的姓名、入职时间
5. select first_name,last_name,hire_date from emp inner join dep on emp.department_id=dep.department_id where emp.department_id=50;
7. -- 查询发帖人的姓名、主贴的名称、回帖人姓名、回帖的内容
9. --inner join ··· on的方法
10. select u.uname,t.ttopic,r.rcontents,u2.uname from
11. bbsusers as u inner join bbstopic as t on u.userid=t.tuid-- 用户表和主贴表的关联inner join bbsreply as r on t.tid=r.rtid-- 回帖表和主贴表的关联inner join bbsusers as u2 on u2.userid=r.ruid; -- 用户表和回帖表的关联
13. -- from A表，B表  查询的方法
14. select u1.uname,t.ttopic,r.rcontents,u2.uname from
15. bbsusers as u1,bbsusers as u2,bbstopic as t,bbsreply as r -- from 的四张表where u1.userid=t.tuid and u2.userid=r.ruid and t.tid=r.rtid; -- 四张表的三个关联关系

复制代码

左毗连：
使用join left ··· on查询没有到场测验的学员姓名

1. -- 使用left join左连接实现
2. select * from student as stu left join score as s on stu.stuId=s.stuId where s.stuId is null;

复制代码

右毗连：
使用right join ··· on 查询没有到场测验的学生

1. -- 使用right join右联接
2. select * from score as s right join student as stu on stu.stuId=s.stuId where s.stuId is null;

复制代码

子查询：（多表查询时只查询单个表中的数据用子查询，需要查找多表中的数据需要用内毗连和外毗连）
　　如果想查询年事比TOM的年事大的学生信息怎么办？（要先查询tom的年事，在进行查询比他年事大的，要两次查询）
　　使用子查询解决！
　　子查询：即查询语句中使用嵌套的查询，在SQL语句中可以将查询的效果作为呈现数据大概将效果作为条件再次进行条件筛选
使用子查询的注意事项：
　select  列名 from  表名 where  列名=(select  列名 from  表名)　
　　1.当使用关系运算符对子查询效果进行处理惩罚时，子查询的效果必须为单行单列的值，只有单行单列的值才气用 >   <   =进行比较
　　2.如果是多行数据，则可以使用 in 进行比较 

1. -- 查询年龄比tom大的学生信息
3. -- 使用子查询，可以将查到的结果集继续进行查询处理
5. select * from student where age>(select age from student where stuname='tom');
6. -- 可以通过子查询作为结果集的虚拟表，继续完成查询，起别名为n
8. select stuName,age from (select * from student) as n;
9. -- 查询所有及格的学生姓名：groupby stuid是为了不重复
11. select  stuid,stuname from student where stuid in (select stuid from score where score<60 group by stuid);
12. -- 查询没有参加考试的学生姓名（对成绩表中的学生编号进行分组，然后查找学生表中学生学号不在之前的分组中的学生）
14. select stuname from student where stuid not in(select stuid from score group by stuid);
15. -- 查询java课程的平均分（查找课程表中的Java对应的courseid ,然后对成绩表中的courseid和Java对应的courseID相同的所有成绩，进行平均分计算）
17. select avg(score) from score where courseId=(select courseId from course where courseName='Java');
18. -- 查询在所在地(location)在美国的所有部门的员工姓名(emp)和部门名称(dep)

复制代码

方法一：用inner join 但是交织的数据量较大

1. select first_name,last_name,department_name from
2. emp inner join dep on emp.department_id=dep.department_id
3. inner join location as l on l.location_id=dep.location_id
4. where country_id='US';

复制代码

方法二：

1. -- 先查询出所在是美国的部门编号，
3. -- 然后根据部门编号查询这些部门的员工姓名和部门编号
5. -- 最后将查询的结果集inner join部门表
7. -- 先查询出所在是美国的部门编号，
8. select location_id from location where country_id='US'
9. -- 然后根据部门编号查询这些部门的部门编号
10. select department_id from dep where location_id in
11.     (select location_id from location where country_id='US')
12. --  然后根据部门编号查询这些部门的员工姓名
13. select first_name,last_name,department_id from emp where department_id in
14.     (select department_id from dep where location_id in
15.         (select location_id from location where country_id='US'))
16. -- 最后将查询的结果集inner join部门表
17. select first_name,last_name,department_name from
18.     (select first_name,last_name,department_id from emp where department_id in
19.     　　(select department_id from dep where location_id in
20.         　　(select location_id from location where country_id='US'))) n (-- 别名)
21. inner join dep on n.department_id=dep.department_id

复制代码

大概：

1. -- 先查询出所在是美国的部门编号
3. -- 然后根据部门编号查询这些部门的部门名称
5. --在通过部门编号和emp进行交叉查询
7. select first_name,last_name,department_name from
8. 　　(select department_id,department_name from dep where location_id in
9. 　　　　(select location_id from location where country_id='US')) n
10. inner join emp on n.department_id=emp.department_id

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。