MySQL多表查询一网打尽

select * from student; +------+--------+-------+-------+ | s_id | s_name | s_age | s_sex | +------+--------+-------+-------+ | 1 | 鲁班 | 12 | 男 | | 2 | 貂蝉 | 20 | 女 | | 3 | 刘备 | 35 | 男 | | 4 | 关羽 | 34 | 男 | | 5 | 张飞 | 33 | 女 | +------+--------+-------+-------+ 5 rows in set (0.00 sec) student

mysql> select * from teacher;
+------+-----------+
| t_id | t_name    |
+------+-----------+
|    1 | 张雪峰    |
|    2 | 老子      |
|    3 | 墨子      |
+------+-----------+
3 rows in set (0.00 sec)

teacher

mysql> select * from course;
+------+--------+------+
| c_id | c_name | t_id |
+------+--------+------+
|    1 | python |    1 |
|    2 | java   |    1 |
|    3 | linux  |    3 |
|    4 | web    |    2 |
+------+--------+------+
4 rows in set (0.00 sec)

course

mysql> select * from score;
+-------+------+------+---------+
| sc_id | s_id | c_id | s_score |
+-------+------+------+---------+
|     1 |    1 |    1 |      79 |
|     2 |    1 |    2 |      78 |
|     3 |    1 |    3 |      35 |
|     4 |    2 |    2 |      32 |
|     5 |    3 |    1 |      66 |
|     6 |    4 |    2 |      77 |
|     7 |    4 |    1 |      68 |
|     8 |    5 |    1 |      66 |
|     9 |    2 |    1 |      69 |
|    10 |    4 |    4 |      75 |
|    11 |    5 |    4 |      75 |
+-------+------+------+---------+
11 rows in set (0.00 sec)

score

有以下需求：

1、查询课程编号“001”比课程编号“002” 成绩高的所有学生的学号

#1.先查询001课程和"002"课程的学生成绩,临时表
#2.让两个临时表进行比较
select a.s_id from 
        (select * from score where c_id =‘1‘) a, 
        (select * from score where c_id =‘2‘) b 
where a.s_id = b.s_id and a.s_score > b.s_score; 

View Code

2、查询平均成绩大于60分的同学的学号和平均成绩;

#1.先查询学生的学号和平均成绩
#2.再进行条件过滤
select s_id, avg(s_score) as sc from score GROUP BY s_id having sc>60;

View Code

3、查询所有同学的学号、姓名、选课数、总成绩；

#1.先查学生表中的字段
#2.然后再连表查询成绩表中的字段
select s.s_id,s.s_name,COUNT(sc.c_id)AS‘选课数‘,sum(sc.s_score) 
from student s 
　　LEFT JOIN score sc 
on s.s_id = sc.s_id GROUP BY s.s_id

View Code

4、查询含有"子"的老师的个数；

select count(t_id) from teacher where t_name like‘%子%‘

View Code

5、查询没学过“老子”老师课的同学的学号、姓名；

#1.先查询"老子"老师教什么课程
#2.再查询学过该老师课程的学生有哪些
#3.排除学过该老师课的学生,剩下的就是没有学过的学生

select s_id,s_name from student where s_id not in(
select s_id FROM score where c_id =
    (select c_id from teacher,course where teacher.t_id = course.t_id and t_name =‘老子‘) 
)

View Code

6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；

#1.注意:是即学过001也学过002课程的学生
#2.思路:先查询有哪些学生学了‘001‘或者‘002‘课程
#3.然后进行分组,学科数 = 2 表示学了两门学科

select student.s_id,student.s_name FROM

(select s_id from score se where se.c_id=‘1‘ or se.c_id =‘2‘  GROUP BY se.s_id  HAVING  count(c_id)>1) as B

LEFT JOIN student on student.s_id = B.s_id;

View Code

7、查询学过“老子”老师所教的所有课的同学的学号、姓名；

#1.先查询"老子"老师教哪些课程
#2.再查询哪些学生学习了这些课程
#3.再根据学生编号分组,如果分组后的个数 ="老子"老师所教授课程的个数,则表示学过该老师所有课程.
　select s_id,s_name from student where s_id in(
　　select s_id FROM score where c_id in(
　　　　　　select c_id from teacher,course where teacher.t_id = course.t_id and t_name =‘老子‘
　　) group by s_id having count(s_id) =(    select count(c_id) from teacher,course where teacher.t_id = course.t_id and t_name =‘老子‘)
　)

View Code

8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；

#同第一题
select a.s_id from 
        (select * from score where c_id =‘1‘) a, 
        (select * from score where c_id =‘2‘) b 
where a.s_id = b.s_id and a.s_score < b.s_score; 

View Code

9、查询有课程成绩小于60分的同学的学号、姓名； 

#1.查询所有成绩分数小于60分的同学
#2.关联学生表,去重复

select DISTINCT student.s_id,student.s_name from score,student where score.s_id=student.s_id and s_score < 60

View Code

10、查询没有学全所有课的同学；

#1.分数表分组得到学生选课数量
#2.选课数量 = 课程表总课程
select student.* from score LEFT JOIN student
    on score.s_id = student.s_id 
GROUP BY score.s_id HAVING count(score.s_id) = (select count(c_id) from course);

View Code

11、查询至少有一门课与学号为“002”的同学所学相同的同学的学号和姓名；

#1 002学生学了什么课程
#2.其他学过002学生其中一门课程的学生
#3.关联学生表

select student.s_id,student.s_name from score LEFT JOIN student
on score.s_id = student.s_id
where score.c_id in(select c_id from score where s_id = ‘2‘) and score.s_id !=‘2‘ GROUP BY score.s_id

View Code

12、查询学过 学号为“002”同学全部课程 的其他同学的学号和姓名；

# 1先找到学过002同学课程的人
# 2.课程个数 = 002学生课程个数
# 3.关联学生表,如果不显示自身就去掉

select student.s_id,student.s_name from score LEFT JOIN student on 
score.s_id = student.s_id
　　where score.c_id in(select c_id from score where score.s_id =‘2‘)
and score.s_id !=‘2‘
GROUP BY score.s_id having count(score.s_id) =(select count(c_id) from score where score.s_id =‘2‘)

View Code

13、查询和“002”号的同学学习的课程完全相同的,其他同学学号和姓名；

#1.找出与002学生学习课程数相同的学生(你学两门,我也学两门)
#2.然后再找出学过‘002‘学生课程的学生,剩下的一定是至少学过一门002课程的学生
#3.再根据学生ID进行分组,剩下学生数count(1) = 002学生所学课程数
SELECT * FROM score where score.s_id in(

    select score.s_id from score GROUP BY s_id 
    
    HAVING count(1) =(select count(1) from score where score.s_id = ‘2‘)
) 
and score.c_id in (select c_id from score where score.s_id = ‘2‘) and score.s_id!=‘2‘

GROUP BY score.s_id HAVING count(1) = (select count(1) from score where score.s_id = ‘2‘)

View Code

14、把“score”表中“老子”老师教的课的成绩都更改为此课程的平均成绩；

#1.获得"老子"老师所教的课程号
-- select c_id from course LEFT JOIN teacher on teacher.t_id = course.t_id and teacher.t_name =‘老子‘;

#2. 获得"老子"老师课程的平均成绩
-- select AVG(score.s_score) s_score from score where score.c_id  
--     in(select c_id from course,teacher where teacher.t_id = course.t_id and teacher.t_name =‘老子‘)
-- 
#3.注意:如果直接把上面的查询结果作为更新字段,则会报错(不能先select出同一表中的某些值，再update这个表(在同一语句中))
#所以 需要将查询结果集包装(加一层查询)变为临时表.则可以作为更新字段

update score SET s_score = (
    select bb.s_score from (
        select AVG(s_score) s_score from score where score.c_id  
            in(select c_id from course,teacher where teacher.t_id = course.t_id and teacher.t_name =‘老子‘)
)as bb)
where score.c_id in (select c_id from course,teacher where teacher.t_id = course.t_id and teacher.t_name =‘老子‘)

View Code

15、删除学习“墨子”老师课的score表记录；

#1.找到墨子老师教的课程
#2.根据课程号直接删除

DELETE from score where c_id in(select c_id from course INNER JOIN teacher  on teacher.t_id = course.t_id and teacher.t_name = ‘墨子‘)

View Code

16、按平均成绩从高到低显示所有学生的“python”、“java”、“linux”三门的课程成绩，按如下形式显示： 学生ID,python,java,linux,有效课程数,有效平均分

#1.学生python课程的平均成绩是多少?
select sc1.s_score from course c,score sc1 where c.c_id = sc1.c_id and c.c_name = ‘python‘ and sc1.s_id = 1 ORDER BY sc1.s_score desc;
select sc.s_score from course c,score sc where c.c_id = sc.c_id and c.c_name = ‘java‘ and sc.s_id = 1 ORDER BY sc.s_score desc;
select sc.s_score from course c,score sc where c.c_id = sc.c_id and c.c_name = ‘linux‘ and sc.s_id = 1 ORDER BY sc.s_score desc
#2.学生id,有效课程数,有效平均分如何查询?
select sc.s_id,
count(*),
AVG(sc.s_score)
from score sc,course c where sc.c_id = c.c_id GROUP BY sc.s_id;
#3.组合SQL:按平均分排序
select sc.s_id,
(select sc1.s_score from course c,score sc1 where c.c_id = sc1.c_id and c.c_name = ‘python‘ and sc1.s_id = sc.s_id ORDER BY sc1.s_score desc)as ‘python‘,
(select sc1.s_score from course c,score sc1 where c.c_id = sc1.c_id and c.c_name = ‘java‘ and sc1.s_id = sc.s_id ORDER BY sc1.s_score desc)as ‘java‘,
(select sc1.s_score from course c,score sc1 where c.c_id = sc1.c_id and c.c_name = ‘linux‘ and sc1.s_id = sc.s_id ORDER BY sc1.s_score desc)as ‘linux‘,
count(*) as ‘课程数‘,
AVG(sc.s_score) as ‘平均分‘
from score sc,course c where sc.c_id = c.c_id GROUP BY sc.s_id order by AVG(sc.s_score) desc;

View Code

17、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

select c_id,MAX(s_score),MIN(s_score) from score GROUP BY c_id

View Code

18、按各科平均成绩从低到高和及格率的百分数从高到低顺序

#1. case when .... then ...else ... end 
#2.先获得学生ID,各科平均成绩
#3.计算及格率. 规则:及格课数/总科数 *100　
select sc.c_id as ‘学生号‘, 
　　avg(sc.s_score) as ‘平均成绩‘, 
　　sum(case when sc.s_score >=60 then 1 ELSE 0 end)/count(1) * 100 as ‘及格率‘
from score sc GROUP BY sc.c_id order by avg(sc.s_score) asc ,
　　sum(case when sc.s_score >=60 then 1 ELSE 0 end)/count(1) * 100 desc;

View Code

19、查询老师所教课程平均分从高到低显示,并显示老师的名称及课程名称

select teacher.t_name,avg(score.s_score),course.c_name from teacher
　　LEFT JOIN course on course.t_id = teacher.t_id
　　LEFT join score on score.c_id = course.c_id
GROUP BY score.c_id

View Code

20、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60] 

#1.先统计出课程ID和课程名称,可以利用分组
#2.通过判断语句计算和的方式获得分数段人数
select score.c_id, course.c_name,
    sum(case when score.s_score between 85 and 100 THEN 1 ELSE 0 END) as ‘[100-85]‘,
    sum(case when score.s_score between 70 and 85 THEN 1 ELSE 0 END) as ‘[85-70]‘,
  sum(case when score.s_score between 60 and 70 THEN 1 ELSE 0 END) as ‘[70-60]‘,
    sum(case when score.s_score < 60 THEN 1 ELSE 0 END) as ‘[<60]‘
from score,course 
where score.c_id = course.c_id group by score.c_id

View Code

21、查询每门课程被选修的学生数.

select c_id,count(s_id) from score GROUP BY c_id

View Code

22、查询出只选修了一门课程的学生的学号和姓名

select student.s_id,student.s_name from score
    LEFT JOIN student on score.s_id = student.s_id

  group by s_id HAVING count(1)=‘1‘; 

View Code

23、查询学生表中男生、女生人数

select 
    sum(case when s_sex =‘男‘ then 1 ELSE 0 end )as ‘男‘,
    sum(case when s_sex =‘女‘ then 1 ELSE 0 end )as ‘女‘
 from student

View Code

24、查询姓“张”的学生名单

select * from student where student.s_name like ‘张%‘

View Code

25、查询同名学生名单，并统计同名人数

select s_name,count(
                        

                            

                                
< 上一篇
Fedora 27 安装 MongoDB
                            
                            

                                 
下一篇 >
MYSQL数据库设计规范