多表查询
多表连接查询
1.交叉连接:笛卡尔积
SELECT * FROM employee,department;
2.内连接:只显示两个表的交集
SELECT employee.name as employee,department.name as department FROM employee INNER JOIN department ON employee.dep_id = department.id;
3.左连接:显示交集加上左边有的,右边没有的(相当于差集 左边集合-右边集合)
SELECT employee.name as employee,department.name as department FROM employee LEFT JOIN department ON employee.dep_id = department.id;
4.右连接:显示交集加上右边有的,左边没有的(相当于差集 右边集合-左边集合)
SELECT employee.name as employee,department.name as department FROM employee RIGHT JOIN department ON employee.dep_id = department.id;
5.外连接:显示并集(左边集合+右边集合)
SELECT employee.name as employee,department.name as department FROM employee LEFT JOIN department ON employee.dep_id = department.id;
UNION
SELECT employee.name as employee,department.name as department FROM employee RIGHT JOIN department ON employee.dep_id = department.id;
UNION
和UNION ALL
区别:UNION
会去除重复记录
练习:
1.以内连接的方式查询employee和department表,并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
SELECT employee.name as name,department.name as department FROM employee INNER JOIN department ON employee.dep_id = department.id WHERE employee.age > 25;
2.以内连接的方式查询employee和department表,并且以age字段的升序方式显示
SELECT employee.name as name,department.name as department,employee.age FROM employee INNER JOIN department ON employee.dep_id = department.id WHERE employee.age > 25 ORDER BY age ASC;
子查询
一个查询语句嵌套在另一个查询语句中,一般将查询语句放在条件中。
- 可以使用比较运算符>、<、=、!=等。
- 关键字:IN,NOT IN,ANY,ALL,EXISTS,NOT EXISTS等
IN
查询平均年龄在25岁以上的部门名
只能查询到部门名
SELECT department.name FROM employee INNER JOIN department ON employee.dep_id=department.id GROUP BY department.name HAVING AVG(age) > 25;
可以查询到部门名,也可以查询到部门id
SELECT id,name FROM department WHERE id IN (SELECT dep_id FROM employee GROUP BY dep_id HAVING AVG(age) > 25);
查看技术部员工姓名
SELECT name FROM employee WHERE dep_id = (SELECT id FROM department WHERE name = "技术");
查看不足1人的部门名
SELECT name FROM department WHERE id IN (SELECT dep_id FROM employee GROUP BY dep_id HAVING COUNT(id) <= 1);
使用内连接也可以
SELECT department.name FROM department INNER JOIN employee ON department.id = employee.dep_id GROUP BY department.name HAVING COUNT(employee.id) < 2;
比较运算符
1.查询大于所有人平均年龄的员工名与年龄
SELECT name,age FROM employee WHERE age > (SELECT AVG(age) FROM employee);
2.查询大于部门内平均年龄的员工名、年龄*
SELECT t1.name,t1.age FROM employee AS t1
INNER JOIN
(SELECT dep_id,AVG(age) AS age_avg FROM employee GROUP BY dep_id) t2
ON t1.dep_id = t2.dep_id
WHERE age > age_avg;
注意:两个相同的表在一个条语句中,用别名
EXISTS
当子表查询有记录,则返回主表查询结果;反之不返回结果
1.查询department表中存在dep_id=203,返回员工表结果
SELECT * FROM employee WHERE EXISTS (SELECT * FROM department WHERE id = 203);
#存在
2.查询department表中存在dep_id=205,返回员工表结果
SELECT * FROM employee WHERE EXISTS (SELECT * FROM department WHERE id = 205);
#不存在
练习
查询每个部门最新入职的员工
1.找到每个部门最新入职的时间
SELECT post,MAX(hire_date) FROM employee GROUP BY post;
2.将查找这个记录属于该部门哪个员工
SELECT * FROM employee t1
INNER JOIN
(SELECT post,MIN(hire_date) max_date FROM employee GROUP BY post) t2
ON t1.post=t2.post AND t1.hire_date=max_date;
亦可以
SELECT * FROM employee t1
INNER JOIN
(SELECT post,MIN(hire_date) max_date FROM employee GROUP BY post) t2
ON t1.post=t2.post WHERE t1.hire_date=max_date;
导入数据
source 数据地址
source c:/init.sql
1、查询所有的课程的名称以及对应的任课老师姓名
SELECT course.cname,teacher.tname FROM course
INNER JOIN teacher ON course.teacher_id = teacher.tid;
2、查询学生表中男女生各有多少人
SELECT gender,COUNT(1) FROM student GROUP BY gender;
3、查询物理成绩等于100的学生的姓名
SELECT sname FROM student WHERE sid IN
(SELECT DISTINCT student_id FROM score WHERE num = 100
AND course_id =
(SELECT DISTINCT cid FROM course WHERE cname = "物理")
);
#也可以用内连接,内连接三个表
SELECT student.sname FROM score
INNER JOIN student ON student.sid = score.student_id
INNER JOIN course ON score.course_id = course.cid
WHERE course.cname = "物理" AND score.num = 100;
4、查询平均成绩大于八十分的同学的姓名和平均成绩
SELECT student.sname,AVG(score.num) FROM student
INNER JOIN score ON student.sid = score.student_id
GROUP BY student.sname HAVING AVG(score.num) > 80;
5、查询所有学生的学号,姓名,选课数,总成绩
SELECT id,t1.sname AS name,COUNT_SCORE,SUM_SCORE FROM student t1
INNER JOIN(
SELECT student_id AS id,COUNT(1) AS COUNT_SCORE,SUM(num) AS SUM_SCORE FROM score GROUP BY student_id
) t2 ON t1.sid=t2.id;
6、 查询姓李老师的个数
SELECT COUNT(1) FROM teacher WHERE tname like "李%";
**
7、 查询没有报李平老师课的学生姓名
#1)找到报名李平老师课程的学生id
#2)利用学生表求出没报其课程的学生姓名
SELECT sname FROM student WHERE sid NOT IN
(
SELECT student_id FROM score WHERE course_id in
(
SELECT cid FROM course
INNER JOIN teacher ON teacher.tid = course.teacher_id
WHERE tname = "李平老师"
)
);
**
8、 查询物理课程比生物课程高的学生的学号
#1.查询物理课程的成绩 2.查询生物课程的成绩
#合表,比较成绩大小
SELECT course_w.student_id,course_w.num,course_s.num FROM(
SELECT student_id,num FROM score
INNER JOIN course ON score.course_id = course.cid
WHERE cname = "物理"
) course_w INNER JOIN (
SELECT student_id,num FROM score
INNER JOIN course ON score.course_id = course.cid
WHERE cname = "生物"
) course_s ON course_w.student_id = course_s.student_id
WHERE course_w.num > course_s.num;
9、 查询没有同时选修物理课程和体育课程的学生姓名
#1.查到选修物理的学生id 2.查到选修生物的学生id 合表 内连接
#2.不在这个表内的学生,属于没有选修两个课程
#方式一:
SELECT sname FROM student WHERE sid NOT IN
(
SELECT score_w.student_id FROM (
SELECT student_id FROM score
INNER JOIN course ON score.course_id = course.cid
WHERE cname = "物理"
) score_w INNER JOIN(
SELECT student_id FROM score
INNER JOIN course ON score.course_id = course.cid
WHERE cname = "生物"
) score_s ON score_w.student_id = score_s.student_id
);
#改进
#找到同时满足两个条件的学生id
SELECT sname FROM student WHERE sid NOT IN
(
SELECT student_id FROM score
INNER JOIN course ON score.course_id = course.cid
WHERE cname IN ("物理","生物")
GROUP BY student_id
HAVING COUNT(1) = 2
) ;
10、查询挂科超过两门(包括两门)的学生姓名和班级
SELECT student.sname,class.caption FROM student
INNER JOIN class ON student.class_id = class.cid
WHERE student.sid IN
(
SELECT student_id FROM score
WHERE score.num < 60
GROUP BY student_id
HAVING COUNT(1) >= 2
);
11、查询选修了所有课程的学生姓名
SELECT sname FROM student
WHERE sid IN
(
SELECT student_id FROM score
GROUP BY student_id
HAVING COUNT(course_id) =
(SELECT COUNT(1) FROM course)
);
12、查询李平老师教的课程的所有成绩记录
SELECT * FROM score
WHERE course_id IN
(
SELECT course.cid FROM course
INNER JOIN teacher ON course.teacher_id = teacher.tid
WHERE teacher.tname = "李平老师"
);
13、查询全部学生都选修了的课程号和课程名
SELECT * FROM course
WHERE cid IN
(
SELECT course_id FROM score
GROUP BY course_id
HAVING COUNT(student_id) =
(SELECT COUNT(sid) FROM student)
);
14、查询每门课程被选修的次数
SELECT course.cname,t2.count_course FROM course
INNER JOIN (
SELECT course_id,COUNT(1) AS count_course FROM score
GROUP BY course_id) t2 ON course.cid=t2.course_id;
15、查询只选修了一门课程的学生姓名和学号
SELECT sid,sname FROM student
WHERE sid IN
(
SELECT student_id FROM score
GROUP BY student_id
HAVING COUNT(course_id) = 1
);
16、查询所有学生考出的成绩并按从高到低排序(成绩去重)
SELECT DISTINCT student.sname name,score.num score FROM score
INNER JOIN student ON score.student_id=student.sid
ORDER BY score.num DESC;
17、查询平均成绩大于85的学生姓名和平均成绩
SELECT student.sname AS name,AVG(score.num) AS score_avg FROM score
INNER JOIN student ON score.student_id = student.sid
GROUP BY student.sname;
18、查询生物成绩不及格的学生姓名和对应生物分数
SELECT student.sname,score.num FROM score
INNER JOIN course ON score.course_id = course.cid
INNER JOIN student ON score.student_id = student.sid
WHERE course.cname = "生物" AND score.num < 60;
19、查询在所有选修了李平老师课程的学生中,这些课程(李平老师的课程,不是所有课程)平均成绩最高的学生姓名
SELECT student.sname,t3.score_avg FROM student
INNER JOIN (
SELECT score.student_id sid,AVG(num) score_avg FROM score
INNER JOIN (
SELECT cid,cname FROM course
INNER JOIN teacher ON course.teacher_id = teacher.tid
WHERE teacher.tname = "李平老师"
) t2 ON score.course_id = t2.cid
GROUP BY score.student_id
ORDER BY AVG(num) DESC
LIMIT 1)t3 ON t3.sid=student.sid
;
**
20、查询每门课程成绩最好的前两名学生姓名
#先找到第一和第二的成绩及课程名
#再将其学生号找到
#最后连表找到学生名
SELECT student.sname,t_stu.course_id,t_stu.num,t_stu.first_num,t_stu.second_num
FROM student
INNER JOIN(
SELECT t4.student_id,t4.course_id,t4.num,t3.first_num,t3.second_num FROM score t4
INNER JOIN(
SELECT
sid,
(SELECT num FROM score t2 WHERE t1.course_id = t2.course_id ORDER BY t2.num DESC LIMIT 0,1 ) first_num,
(SELECT num FROM score t2 WHERE t1.course_id = t2.course_id ORDER BY t2.num DESC LIMIT 1,1 ) second_num
FROM score t1
) t3 ON t4.sid = t3.sid
WHERE t4.num = first_num OR t4.num = second_num ) t_stu ON student.sid = t_stu.student_id;
21、查询不同课程但成绩相同的学号,课程号,成绩
#连接两张表
SELECT DISTINCT s1.student_id,s1.course_id,s1.num FROM score s1,score s2
WHERE s1.num = s2.num AND s1.course_id != s2.course_id ;
22、查询没学过“李平”老师课程的学生姓名以及选修的课程名称;
SELECT student.sname,course.cname FROM score
INNER JOIN student ON score.student_id = student.sid
INNER JOIN course ON course.cid = score.course_id
WHERE student.sid NOT IN(
SELECT DISTINCT score.student_id FROM score
INNER JOIN (
SELECT cid FROM course
INNER JOIN teacher ON course.teacher_id = teacher.tid
WHERE tname = "李平老师"
) t2 ON score.course_id = t2.cid );
23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名;
SELECT DISTINCT student.sid,student.sname FROM score
INNER JOIN student ON score.student_id = student.sid
WHERE score.course_id in (
SELECT course_id FROM score
WHERE student_id = 1
);
24、任课最多的老师中学生单科成绩最高的学生姓名
SELECT DISTINCT student.sname,score.num FROM score
INNER JOIN course ON score.course_id = course.cid
INNER JOIN student ON score.student_id=student.sid
WHERE course.teacher_id = (
SELECT teacher_id FROM score
INNER JOIN course ON course.cid = score.course_id
GROUP BY teacher_id
ORDER BY COUNT(score.course_id) DESC
LIMIT 1
) ORDER BY score.num DESC
LIMIT 1;