MySQL 练习题4 参考答案

1.表结构如下:

 

#课程表
CREATE TABLE `course` (
  `c_id` int(11) NOT NULL,
  `c_name` varchar(50) DEFAULT NULL,
  `t_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`c_id`),
  KEY `t_id` (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `course` VALUES ('1', 'python', '1');
INSERT INTO `course` VALUES ('2', 'java', '2');
INSERT INTO `course` VALUES ('3', 'linux', '3');
INSERT INTO `course` VALUES ('4', 'web', '2');

#成绩表
CREATE TABLE `score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `s_id` int(11) DEFAULT NULL,
  `c_id` int(11) DEFAULT NULL,
  `num` double DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

INSERT INTO `score` VALUES ('1', '1', '1', '79');
INSERT INTO `score` VALUES ('2', '1', '2', '78');
INSERT INTO `score` VALUES ('3', '1', '3', '35');
INSERT INTO `score` VALUES ('4', '2', '2', '32');
INSERT INTO `score` VALUES ('5', '3', '1', '66');
INSERT INTO `score` VALUES ('6', '4', '2', '77');
INSERT INTO `score` VALUES ('7', '4', '1', '68');
INSERT INTO `score` VALUES ('8', '5', '1', '66');
INSERT INTO `score` VALUES ('9', '2', '1', '69');
INSERT INTO `score` VALUES ('10', '4', '4', '75');
INSERT INTO `score` VALUES ('11', '5', '4', '66.7');

#学生表
CREATE TABLE `student` (
  `s_id` varchar(20) NOT NULL,
  `s_name` varchar(50) DEFAULT NULL,
  `s_age` int(10) DEFAULT NULL,
  `s_sex` char(1) DEFAULT NULL,
  PRIMARY KEY (`s_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `student` VALUES ('1', '鲁班', '12', '男');
INSERT INTO `student` VALUES ('2', '貂蝉', '20', '女');
INSERT INTO `student` VALUES ('3', '刘备', '35', '男');
INSERT INTO `student` VALUES ('4', '关羽', '34', '男');
INSERT INTO `student` VALUES ('5', '张飞', '33', '女');

#老师表
CREATE TABLE `teacher` (
  `t_id` int(10) NOT NULL,
  `t_name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `teacher` VALUES ('1', '大王');
INSERT INTO `teacher` VALUES ('2', 'alex');
INSERT INTO `teacher` VALUES ('3', 'egon');
INSERT INTO `teacher` VALUES ('4', 'peiqi');

数据脚本
数据脚本

2.查询:

  1. 查询学习课程"python"比课程 "java" 成绩高的学生的学号; 
    #先查询"python"课程和"java"课程的学生成绩,临时表
#让两个临时表进行比较
-- select * from course c,score s where c.c_id = s.c_id and c.c_name='python';
-- select * from course c,score s where c.c_id = s.c_id and c.c_name='java';

select A.s_id from 
(select s.s_id,s.num from course c,score s where c.c_id = s.c_id and c.c_name='python') AS A ,
 (select s.s_id,s.num from course c,score s where c.c_id = s.c_id and c.c_name='java') AS B
where A.s_id = B.s_id and A.num > B.num;

    　　

  2. 查询平均成绩大于65分的同学的姓名和平均成绩(保留两位小数);　 
    select round(avg(num),2) as num,student.s_name from score s 
LEFT  JOIN student ON s.s_id = student.s_id group by s.s_id having num > 65;

    　
  3. 查询所有同学的姓名、选课数、总成绩； 
    #先来分析需要哪些表:学生表/成绩表
#然后进行多表查询即可

select s_name,count(*) '选课数',sum(num)as '总成绩' from student st,score s where st.s_id = s.s_id GROUP BY s.s_id;

     

  4. 查询所有的课程的名称以及对应的任课老师姓名; 
    select c_name,t_name from course,teacher where course.t_id = teacher.t_id;

    　　

  5. 查询没学过“alex”老师课的同学的姓名; 
    #先看看alex教什么课程
#看看谁学了alex的课程
#最后把学了的人过滤掉就是没学过的学生
-- select c_id from teacher t, course c where t.t_id = c.t_id and t.t_name ='alex';

-- select s_id from score where c_id in(2,4);

select s_name from student  
where s_id not in(select s_id from score where c_id in(2,4));

    　　

  6. 查询学过'python'并且也学过编号'java'课程的同学的姓名; 
    -- select * from score where score.c_id='1' and score.c_id='2'
#查询python和java课程号
-- select c_id from course where course.c_name in('python','java');

SELECT st.s_name from score s ,student st
where s.s_id = st.s_id AND s.c_id in(1,2) GROUP BY s.s_id HAVING COUNT(*) = 2;

    　　

  7. 查询学过“alex”老师所教的全部课程的同学的姓名； 
    #先知道alex老师教什么课程
#然后来看看学了alex课程的学生有哪些人
#按学生分组,看看谁学的课程数 = alex老师教授的课程数

-- select c_id from teacher t, course c where t.t_id = c.t_id and t.t_name ='alex';

select student.s_name from score,student where score.s_id =student.s_id and score.c_id in
	(select c_id from teacher t, course c where t.t_id = c.t_id and t.t_name ='alex') GROUP BY score.s_id 

HAVING count(*) = (select count(*) from teacher t, course c where t.t_id = c.t_id and t.t_name ='alex');

    　　

  8. 查询挂科超过两门(包括两门)的学生姓名; 
    SELECT student.s_name from score,student 
where score.s_id = student.s_id and score.num <60 GROUP BY student.s_id HAVING count(*)>=2;

     

  9. 查询有课程成绩小于60分的同学的姓名; 
    SELECT DISTINCT student.s_name from score,student 
where score.s_id = student.s_id and score.num <60;

    　　

  10. 查询选修了全部课程的学生姓名; 
    -- select count(*) from course;

select student.s_name from score,student 
where score.s_id = student.s_id GROUP BY score.s_id HAVING count(*) = (select count(*) from course)

    　　

  11. 查询至少有一门课程与“貂蝉”同学所学课程相同的同学姓名； 
    -- SELECT c_id from score,student where score.s_id =student.s_id and student.s_name='貂蝉'
 
select student.s_name from score,student  where score.s_id = student.s_id and
　　score.c_id in(SELECT c_id from score,student where score.s_id =student.s_id and student.s_name='貂蝉')

and student.s_name <> '貂蝉' GROUP BY student.s_id;

    　　

  12. 查询学过'貂蝉'同学全部课程 的其他同学姓名； 
    -- SELECT c_id from score,student where score.s_id =student.s_id and student.s_name='貂蝉'

select student.s_name,count(*) from score,student  where score.s_id = student.s_id 
and  score.c_id in(SELECT c_id from score,student where score.s_id =student.s_id and student.s_name='貂蝉')
and student.s_name <> '貂蝉' GROUP BY student.s_id 
HAVING count(*) = (SELECT count(*) from score,student where score.s_id =student.s_id and student.s_name='貂蝉');

    　　

  13. 查询和'貂蝉'同学学习的课程完全相同的,其他同学姓名； 
    解题思路:
#1. 找出与'貂蝉'学习课程数 相同的学生s_id (你学两门,我也学两门)　
#2. 再找出学过'貂蝉'课程的学生,剩下的一定是至少学过一门'貂蝉'课程的学生
#3. 再根据学生ID进行分组,剩下学生数count(1) = 貂蝉学生所学课程数

#1.找出与'貂蝉'学习课程数 相同的学生s_id (你学两门,我也学两门)
select * FROM score where score.s_id in(
	select s_id from score GROUP BY score.s_id HAVING count(*) = (
		select count(*) from student,score where student.s_id = score.s_id and student.s_name= '貂蝉'
	) 
)

#2.然后再找出学过'貂蝉'课程的学生,剩下的一定是至少学过一门'貂蝉'课程的学生
select * FROM score where score.s_id in(
	select s_id from score GROUP BY score.s_id HAVING count(*) = (
		select count(*) from student,score where student.s_id = score.s_id and student.s_name= '貂蝉'
	) 
)
and score.c_id in(select c_id from student,score where student.s_id = score.s_id and student.s_name= '貂蝉')

#3.再根据学生ID进行分组,剩下学生数count(1) = 貂蝉学生所学课程数
select * FROM score where score.s_id in(
	select s_id from score GROUP BY score.s_id HAVING count(*) = (
		select count(*) from student,score where student.s_id = score.s_id and student.s_name= '貂蝉'
	) 
)
and score.c_id in(select c_id from student,score where student.s_id = score.s_id and student.s_name= '貂蝉')

GROUP BY score.s_id HAVING count(*) =(select count(*) from student,score where student.s_id = score.s_id and student.s_name= '貂蝉')
and score.s_id !=2;

    　　

  14. 按平均成绩倒序显示所有学生的“python”、“java”、“linux”三门的课程成绩，按如下形式显示： 学生ID,python,java,linux,课程数,平均分 
    #1.先查询单一学生的python课程分数
select num from score,course where score.c_id = course.c_id  AND course.c_name ='python' and score.s_id = 1;

#2.将上面查询的结果作为 列字段使用 
select s.s_id,
(select num from score,course where score.c_id = course.c_id AND course.c_name ='python' and score.s_id = s.s_id ) as 'python',
(select num from score,course where score.c_id = course.c_id AND course.c_name ='java' and score.s_id = s.s_id ) as 'java',
(select num from score,course where score.c_id = course.c_id AND course.c_name ='linux' and score.s_id = s.s_id ) as 'linux',
 count(c_id)as '课程数',
 avg(num) as '平均分'

from score s GROUP BY s.s_id;

    　　

  15. 统计各科各分数段人数.显示格式:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]  
    select score.c_id,course.c_name, 
　　sum(CASE WHEN num BETWEEN 85 and 100 THEN 1 ELSE 0 END) as '[100-85]',
　　sum(CASE WHEN num BETWEEN 70 and 85 THEN 1 ELSE 0 END) as '[85-70]',
　　sum(CASE WHEN num BETWEEN 60 and 70 THEN 1 ELSE 0 END) as '[70-60]',
　　sum(CASE WHEN num < 60 THEN 1 ELSE 0 END) as '[ <60]'
from score,course where score.c_id=course.c_id GROUP BY score.c_id;

    　　

  16. 查询每门课程被选修的次数 
    select c_name,count(*) from course,score where course.c_id = score.c_id GROUP BY score.c_id;

    　　

  17. 查询出只选修了一门课程的学生的学号和姓名 
    select student.s_id,student.s_name from student,score where 
student.s_id = score.s_id GROUP BY score.s_id HAVING count(*)=1

    　　

  18. 查询学生表中男生、女生各有多少人 
    注意:不用group by 分组
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

    　

  19. 查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列 
    select course.c_name,avg(num) as '平均成绩' from course,score
where course.c_id = score.c_id GROUP BY score.c_id ORDER BY avg(num),score.c_id desc;

    　　

  20. 查询课程名称为“python”，且分数低于60的学生姓名和分数 
    select student.s_name,score.num from score,course,student 
where score.c_id = course.c_id and student.s_id = score.s_id and course.c_name = 'python' and score.num < 67

    　　

posted @ 2017-12-04 18:05  A.隔壁老王  阅读(690)  评论(0)    收藏  举报