一、多表查询——建立基础表

  1. ----------------------------------------------'建表'----------------------------------------------
  2. create table dep(
  3.     id int primary key auto_increment,
  4.     name varchar(20) 
  5. );
  7. create table emp(
  8.     id int primary key auto_increment,
  9.     name varchar(20),
  10.     sex enum('male','female') not null default 'male',
  11.     age int,
  12.     dep_id int
  13. );
  15. #插入数据
  16. insert into dep values
  17. (200,'技术'),
  18. (201,'人力资源'),
  19. (202,'销售'),
  20. (203,'运营'),
  21. (205,"安保部");
  23. insert into emp(name,sex,age,dep_id) values
  24. ('yly','male',18,200),
  25. ('zpx','female',48,201),
  26. ('aaa','male',18,201),
  27. ('bbb','male',28,202),
  28. ('ccc','male',18,203),
  29. ('ddd','female',18,204);
  31. ----------------------------------------------'表结构'----------------------------------------------
  32. mysql> select * from dep;
  33. +-----+--------------+
  34. | id  | name         |
  35. +-----+--------------+
  36. | 200 | 技术         |
  37. | 201 | 人力资源      |
  38. | 202 | 销售         |
  39. | 203 | 运营         |
  40. | 205 | 安保部       |
  41. +-----+--------------+
  43. mysql> select * from emp;
  44. +----+-------+--------+------+--------+
  45. | id | name  | sex    | age  | dep_id |
  46. +----+-------+--------+------+--------+
  47. |  1 | yly   | male   |   18 |    200 |
  48. |  2 | zpx   | female |   48 |    201 |
  49. |  3 | aaa   | male   |   18 |    201 |
  50. |  4 | bbb   | male   |   28 |    202 |
  51. |  5 | ccc   | male   |   18 |    203 |
  52. |  6 | ddd   | female |   18 |    204 |
  53. +----+-------+--------+------+--------+

二、多表查询——连表操作

1、前戏

  1. 要多表查询，可以合并两张表，从而查询对应的数据
  3. 案例一、查询每个人的姓名以及所处部门名称
  4. 方法一、"不合理"
  5.     # 笛卡尔积
  6.     select * from emp,dep;
  7.     mysql>  select * from emp,dep;
  8.     +----+------+--------+------+--------+-----+--------------+
  9.     | id | name | sex    | age  | dep_id | id  | name         |
  10.     +----+------+--------+------+--------+-----+--------------+
  11.     |  1 | yly  | male   |   18 |    200 | 200 | 技术         |
  12.     |  1 | yly  | male   |   18 |    200 | 201 | 人力资源     |
  13.     |  1 | yly  | male   |   18 |    200 | 202 | 销售         |
  14.     |  1 | yly  | male   |   18 |    200 | 203 | 运营         |
  15.     |  1 | yly  | male   |   18 |    200 | 205 | 安保部       |
  16.     |  2 | zpx  | female |   48 |    201 | 200 | 技术         |
  17.     |  2 | zpx  | female |   48 |    201 | 201 | 人力资源     |
  18.     |  2 | zpx  | female |   48 |    201 | 202 | 销售         |
  19.     |  2 | zpx  | female |   48 |    201 | 203 | 运营         |
  20.     |  2 | zpx  | female |   48 |    201 | 205 | 安保部       |
  21.     ...        # 只显示查询出来的部分表
  22.     # 合并成了，但是却查到了每个emp的id和部门的id重复一一对应，没有达到emp.dep_id和dep.id进行对应
  24. 方法二、"不合理，会报错"
  25.     mysql> select * from emp,dep where dep_id=id;
  26.     ERROR 1052 (23000): Column 'id' in where clause is ambiguous    
  27.     #报错：where子句中的列“id”不明确，
  28.     """
  29.     涉及到多表操作的时候 为了避免表字段重复 
  30.         需要在字段名的前面加上表名限制
  31.     """
  33. 方法三、"合并表之后，id来源于哪个表很明确，查询得出"
  34.     mysql> select emp.name,dep.name from emp,dep where emp.dep_id=dep.id;
  35.     +------+--------------+
  36.     | name | name         |
  37.     +------+--------------+
  38.     | yly  | 技术         |
  39.     | zpx  | 人力资源     |
  40.     | aaa  | 人力资源     |
  41.     | bbb  | 销售         |
  42.     | ccc  | 运营         |
  43.     +------+--------------+
  1. # 1、上述操作并不是合理的连表操作,效率低，太麻烦
  2. # 2、在mysql中，不用上述操作，而是使用mysql给我们几个关键字进行连表操作。

2、连表关键字

  1. 将两张表进行联合时，就可以使用下列四个关键字
  3. inner join    内连接:只连接两表中都存在(有对应关系)的数据
  4.     select * from emp inner join dep on emp.dep_id = dep.id;
  6. left join    左连接:以左表为基准展示左表所有的数据没有对应则NULL填充
  7.     select * from emp left join dep on emp.dep_id = dep.id;
  9. right join    右连接:以右表为基准展示右表所有的数据没有对应则NULL填充
  10.     select * from emp right join dep on emp.dep_id = dep.id;
  12. union        全连接:展示左右两表中所有的数据没有对应则NULL填充
  13.     select * from emp left join dep on emp.dep_id = dep.id
  14.     union
  15.     select * from emp right join dep on emp.dep_id = dep.id;
  18. """
  19. 注意左右连接的时候，以基准的时候，显示的顺序
  20. """

三、多表查询——子查询

  1. # 子查询：其实就是分布操作
  2.     将一张表的查询结果当做另外一条SQL语句的查询条件
  4. 案例一：查询 部门是技术或者人力资源的员工信息
  5.     1.先查询技术和人力资源的部门编号
  6.         select id from dep where name in ("技术","人力资源");
  7.     2.根据部门标号去员工表中筛选出对应的员工数据
  8.         select * from emp where dep_id in (200,201);
  9.     3.汇总上两步得出答案:
  10.         select * from emp where dep_id in (select id from dep where name in ("技术","人力资源"));

四、多表查询——练习题

1、练习的基础表

Navicat导入下面的文件

多表查询导入文件.sql

2、基于上表的练习题(必会)

  1. 1、 查询所有的课程的名称以及对应的任课老师姓名
  2. 2、 查询平均成绩大于八十分的同学的姓名和平均成绩
  3. 3、 查询没有报李平老师课的学生姓名
  4. 4、 查询没有同时选修物理课程和体育课程的学生姓名
  5. 5、 查询挂科超过两门(包括两门)的学生姓名和班级

解答过程：

  1. 1、 查询所有的课程的名称以及对应的任课老师姓名
  2.     select cname,tname from course INNER JOIN teacher on course.teacher_id=teacher.tid;
  4. -------------------------------------------------------------------------------------
  5. 2、 查询平均成绩大于八十分的同学的姓名和平均成绩
  6.     select student_id,AVG(num) from score group by student_id having AVG(num)>80;
  8. -------------------------------------------------------------------------------------
  9. 3、 查询没有报李平老师课的学生姓名
  10.     第一步：查李平老师不教哪些课的course.cid号
  11.     --     select course.cid from course INNER JOIN teacher on course.teacher_id=teacher.tid where tname not in ("李平老师");
  12.     第二步：将student_id分组，里面包含上面的course.cid，并去重
  13.     --     SELECT distinct student_id from score where course_id in (select course.cid from course INNER JOIN teacher on course.teacher_id=teacher.tid where tname not in ("李平老师"));
  14.     第三步：将student.sid和刚刚表的student_id对应上
  15.     SELECT
  16.         sname
  17.     FROM
  18.         student 
  19.     WHERE
  20.         student.sid IN (
  21.         SELECT DISTINCT
  22.             student_id 
  23.         FROM
  24.             score 
  25.         WHERE
  26.             course_id IN (
  27.             SELECT
  28.                 course.cid 
  29.             FROM
  30.                 course
  31.                 INNER JOIN teacher ON course.teacher_id = teacher.tid 
  32.         WHERE
  33.         tname NOT IN ( "李平老师" )));
  35. -------------------------------------------------------------------------------------
  36. 4、 查询没有同时选修物理课程和体育课程的学生姓名
  37.     第一步：score表和course表
  38.     --     select * from score INNER JOIN course on score.course_id=course.cid;
  41.     第二步：筛选同事选物理以及体育的
  42.     --     select student_id from (select * from score INNER JOIN course on score.course_id=course.cid) as t1 GROUP BY student_id HAVING n1 ;
  43.     select student_id,GROUP_CONCAT(cname) from score INNER JOIN course on score.course_id=course.cid GROUP BY student_id having GROUP_CONCAT(cname) like "%物理%体育%" or GROUP_CONCAT(cname) like "%体育%物理%";
  45.     第三步：not in
  46.     select * from student where student.sid not in (select student_id from score INNER JOIN course on score.course_id=course.cid GROUP BY student_id having GROUP_CONCAT(cname) like "%物理%体育%" or GROUP_CONCAT(cname) like "%体育%物理%");
  49. -------------------------------------------------------------------------------------
  50. 5、 查询挂科超过两门(包括两门)的学生姓名和班级
  51.     第一步：score.student_id分组，统计挂科大于等于2的
  52.     --     select score.student_id from score where num <60 group by score.student_id having count(num) >=2;
  54.     第二步：将class和student连表
  55.     select student.sname,class.caption from class INNER JOIN student on class.cid=student.class_id where sid in (select score.student_id from score where num <60 group by score.student_id having count(num) >=2);

3、基于上表拓展的练习题(不一定要做)

  1. 2、查询学生表中男女生各有多少人
  2.     select gender,count(gender) from student GROUP BY gender;
  3. 3、查询物理成绩等于100的学生的姓名
  4.     select sname from student where sid in (SELECT student_id from score INNER JOIN course on score.course_id=course.cid where num=100 and cname in ("物理"));
  5. 5、查询所有学生的学号，姓名，选课数，总成绩    # group by可以将一一对应关系的两个字段一起分组
  6.     select student.sid,student.sname,COUNT(course_id) as "选课数",sum(num) as "总成绩" from student INNER JOIN score on student.sid=score.student_id GROUP BY student.sid,student.sname;
  7. 6、 查询姓李老师的个数
  8.     select COUNT(tid) as "姓李的老师个数" from teacher where tname like "李%";
  9. 8、 查询物理课程比生物课程高的学生的学号    #分为物理表和生物表
  10.     SELECT t1.student_id from (select student_id,num as "生物成绩" from score INNER JOIN course on score.course_id=course.cid where cname in ("生物")) as t1 INNER JOIN (select student_id,num as "物理成绩" from score INNER JOIN course on score.course_id=course.cid where cname in ("物理")) as t2 on t1.student_id=t2.student_id where "生物成绩" < "物理成绩";
  11. -------------------------------------------------------------------------------------
  12. 11、查询选修了所有课程的学生姓名
  14. -- 1、找出有几门课程course
  15. -- select COUNT(cid) from course;
  16. -- 2、score表以student_id分组,计数course_id  in  上面查出来的表格4
  17. -- select student_id from score GROUP BY student_id having count(course_id) in (select COUNT(cid) from course);
  18. -- 3、找出student.sid in student_id
  19. select student.sname from student where student.sid in (select student_id from score GROUP BY student_id having count(course_id) in (select COUNT(cid) from course));
  20. -------------------------------------------------------------------------------------
  21. 12、查询李平老师教的课程的所有成绩记录
  23.  -------------------------------------------------------------------------------------
  24. 13、查询全部学生都选修了的课程号和课程名
  26. -------------------------------------------------------------------------------------
  27. 14、查询每门课程被选修的次数
  29. -------------------------------------------------------------------------------------
  30. 15、查询之选修了一门课程的学生姓名和学号
  32. -------------------------------------------------------------------------------------
  33. 16、查询所有学生考出的成绩并按从高到低排序（成绩去重）
  35. -------------------------------------------------------------------------------------
  36. 17、查询平均成绩大于85的学生姓名和平均成绩
  38. -------------------------------------------------------------------------------------
  39. 18、查询生物成绩不及格的学生姓名和对应生物分数
  41. -------------------------------------------------------------------------------------
  42. 19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
  44. -------------------------------------------------------------------------------------
  45. 20、查询每门课程成绩最好的前两名学生姓名
  47. -------------------------------------------------------------------------------------
  48. 21、查询不同课程但成绩相同的学号，课程号，成绩
  50. -------------------------------------------------------------------------------------
  51. 22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称；
  53. -------------------------------------------------------------------------------------
  54. 23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；
  56. -------------------------------------------------------------------------------------
  57. 24、任课最多的老师中学生单科成绩最高的学生姓名