mysql多表查询

最新推荐文章于 2023-01-09 17:21:15 发布

小青头

最新推荐文章于 2023-01-09 17:21:15 发布

阅读量1.3k

点赞数 2

分类专栏：数据库速查文章标签： mysql

本文链接：https://blog.csdn.net/xgy123xx/article/details/101479846

版权

数据库速查专栏收录该内容

4 篇文章 1 订阅

订阅专栏

多表查询

多表连接查询

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;

小青头

关注

2
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
mysql多表查询

多表查询多表连接查询1.交叉连接：笛卡尔积SELECT * FROM employee,department;2.内连接：只显示两个表的交集SELECT employee.name as employee,department.name as department FROM employee INNER JOIN department ON employee.dep_id = depa...
复制链接

扫一扫