MySQL练习题

 

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" 成绩高的学生的学号;
2. 查询平均成绩大于65分的同学的姓名和平均成绩(保留两位小数);　
3. 查询所有同学的姓名、选课数、总成绩；
4. 查询所有的课程的名称以及对应的任课老师姓名;
5. 查询没学过“alex”老师课的同学的姓名;
6. 查询学过'python'并且也学过编号'java'课程的同学的姓名;
7. 查询学过“alex”老师所教的全部课程的同学的姓名；
8. 查询挂科超过两门(包括两门)的学生姓名;
9. 查询有课程成绩小于60分的同学的姓名;
10. 查询至少有一门课程与“貂蝉”同学所学课程相同的同学姓名；
11. 查询和'貂蝉'同学学习的课程完全相同的,其他同学姓名；
12. 按平均成绩倒序显示所有学生的“python”、“java”、“linux”三门的课程成绩，按如下形式显示： 学生ID,python,java,linux,课程数,平均分
13. 统计各科各分数段人数.显示格式:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60] 
14. 查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

 

答案：

1、

select python.s_id,student.s_name from
	(select score.num,score.s_id from course,score where score.c_id = course.c_id and course.c_name = 'python') as python,
	(select score.num,score.s_id from course,score where score.c_id = course.c_id and course.c_name = 'java') as java,
	student
where python.s_id = java.s_id and python.num > java.num and python.s_id = student.s_id

2、

select student.s_name,round(avg(score.num),2) from student,score where student.s_id = score.s_id GROUP BY score.s_id having avg(num) > 65

3、

select student.s_name,count(score.s_id),sum(num) from student,score where student.s_id = score.s_id group by score.s_id

4、

select course.c_name,teacher.t_name from course,teacher where course.t_id = teacher.t_id

5、

select * from student where student.s_id not in (select score.s_id from score where score.c_id in (2,4))

6、

select student.s_name from course,score,student where
score.s_id = student.s_id and course.c_id = score.c_id and course.c_name in ('python','java') 
group by score.s_id having count(*)=2

7、

select student.s_name from score,student where score.s_id = student.s_id and score.c_id in
(select c_id from course,teacher where course.t_id = teacher.t_id and teacher.t_name = 'alex') 
group by score.s_id having count(*) = 
(select count(*) from course,teacher where course.t_id = teacher.t_id and teacher.t_name = 'alex')

8、

select student.s_name from score,student where score.s_id = student.s_id and score.num <= 70 group by score.s_id having count(*) >= 2

9、

select distinct student.s_name from score,student where score.s_id = student.s_id and score.num < 60

10、

select distinct student.s_name from student,score where student.s_id = score.id and score.c_id 
in (select score.c_id from student,score where student.s_id = score.s_id and student.s_name = '貂蝉')

11、

select student.s_name from score,student where score.s_id = student.s_id 
and score.s_id in (select score.s_id from score group by score.s_id having count(*) = 2)
and score.c_id in (1,2)
and student.s_name != '貂蝉'

12、

select s.s_id as '学生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(s.s_id) as '课程数',
	avg(s.num)
from score as s group by s.s_id

13、

select course.c_id as '课程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 course.c_id

14、

select avg(num), c_id from score group by c_id order by avg(num) asc, c_id desc