(一) 查询每个岗位的平均薪资(除掉张三的这条记录).先把张三这条记录删掉,再分组.where要写到group by的前面.
(二) 查询平均薪资 > 12000的岗位.先分组算好平均薪资之后再找到大于12000的岗位.having要写到group by后面.
(三) 求除了张三之外.每个岗位的平均薪资并且保留出平均薪资 >= 12000的岗位.
4.3 联合查询/多表查询:
- 要想了解多表查询先了解"笛卡尔积".
- 笛卡尔积就是
简单粗暴的排列组合把所有可能的情况都列出来,把俩张表的记录放在一起进行排列组合出所有可能的情况. - 笛卡尔积的
列数是俩个表的列数之和; 笛卡尔积的行数是俩个表的行数之积.笛卡尔积可以指定列(表达式/别名/去重).
- 在进行联合查询的过程就是在计算笛卡尔积的过程,当表比较大的时候,如果进行多表查询就会非常的低效,甚至称为"危险操作".
- 笛卡尔积的有些数据是有意义的,有一些是无意义的,
用来筛选笛卡尔积有效数据的条件称为"连接条件". 联合查询/多表查询 = 笛卡尔积 + 连接条件 + 其他条件(根据其他的需求)- SQL中的使用来获取到笛卡尔积.(使用 , 或者 使用 join on),有些特定的场景务必需要使用 join on.
4.3.1 内连接:
案例 :
(一) 查询"许仙"同学的成绩;
1.计算student和score的笛卡尔积
select * from student,score;
2.给笛卡尔积加上连接条件
select * from student,score where student.id = score.student_id;
3.根据许仙这个名字,再进行筛选
select * from student,score where student.id = score.student_id and student.name = “许仙”;
4.精简查询的结果,保留关注的,去掉不关注的信息
select name,score from student,score where student.id = score.student_id and student.name = “许仙”;
使用 join on 来完成
select name,score from student join score on student.id = score.student_id and student.name = “许仙”;
(二) 查询所有同学的总成绩(各科之和)
1.计算student和score的笛卡尔积
select * from student,score;
2.加上连接条件
select * from student,score where student.id = score.student_id;
3.分组
select * from student,score where student.id = score.student_id group by name;
4.精简查询的结果
select name,sum(score) from student,score where student.id = score.student_id group by name;
(三) 查询出所有同学的成绩,带课程名的那种
同学名字在学生表里/课程名字在课程表里/分数在分数表里
1.计算三张表的笛卡尔积
select * from student,score,course;
2.给笛卡尔积加上连接条件
select * from student,score,course
where student.id = score.student_id and classes.id = score.course_id;
3.不必加其他的条件了,直接精简即可
select student.name,course.name,score from student,course,score
where student.id = score.student_id and course.id = score.course_id;
使用 join on 来做,更能体现出"俩俩结合"的感觉
select student.name,course.name,score from student join score student.id = score.student_id join course on score.course_id = course.id;
4.3.2 外连接:
from 表1,表2 where …;
这就是内连接from 表1 join 表2 on …;
使用join/inner join可以作为内连接也可以作为外连接
- 写成
left join 左外连接/right join 右外连接. - 如果三个表外连接, 则是A和B先外连接得到一个临时表, 把临时表和C建立连接即可.
如果俩张表的数据都是一一对应的时候,其实内连接和外连接看不出来区别
如果表上的数据不在一一对应,内连接和外连接区别就非常明显了.下面的数据中,左侧表的王五同学在右侧表里没有成绩,右侧表中的4号同学在左侧表里没有同学信息.
- 左外还是右外,主要还是看表的先后顺序, 是在join的左侧还是右侧.
4.3.3 自连接:
- 自己和自己做笛卡尔积
- 条件查询中核心就是列和列之间的比较,而从来没有
行和行之间的比较.此处的自连接就是把行转成列. - 如果这个表很大,那么运行的开销就会很大,可读性也不高.
(一) 显示计算机原理比java成绩高的同学
1.需要的数据都在分数表里,
select * from score as s1,score as s2;
2.加上连接条件
select * from score as s1,score as s2 where s1.student_id = s2.student_id;
3.加上筛选条件
select * from score as s1,score as s2
where s1.student_id = s2.student_id and and s1.course_id = 3 and s2.course_id = 1
and s1.course_id > s2.course_id;
4.4 子查询:
- 本质上就是
把多个查询语句组合成一个查询语句, 套娃! - 用一个查询的结果的临时表,基于这个临时表再发起另外一组查询
- 如果子查询返回的结果是多条记录,就可以使用 in 来进行子查询.
(一) 查询出与不行毕业同学的同班同学
select name from student where name != “不想毕业” and
classes_id = (select classes_id from student where name = “不想毕业”);
(二) 查询语文或英文课程的成绩信息
select * from score where course_id in (select id from course where name = “语文” or name = “英文”);
4.5 合并查询:
- 使用 union 或者union all 来完成俩个查询的结果合并到一起.如果是一张表那么union的作用和 or 类似,使用or也能完成.如果是俩张表就不能使用or了,使用俩张表合并的话列需要列是匹配的.
- union 是把多个查询的结果集合并成一个,
如果有重复的数据,就会去重. - union all 合并的时候
如果有重复的数据就不会去重.
(一) 查询课程 id<3 或者名字为英文的课程
select * from course where id < 3 union select * from course where name = “英文”;
