mysql多表查询

多表查询

多表连接查询

表2

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;

UNIONUNION 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;

子查询

一个查询语句嵌套在另一个查询语句中,一般将查询语句放在条件中。

  1. 可以使用比较运算符>、<、=、!=等。
  2. 关键字: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);#不存在

练习

图3
查询每个部门最新入职的员工

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;
导入数据

图4

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
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值