MySQL练习第三十九天 2018.11.30

MySQL面试题库练习

创建表

#课程表
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');

查询操作

1、查询学习课程"python"比课程 "java" 成绩高的学生的学号和姓名;

--select score.num from  course,score where score.c_id = course.c_id and course.c_name = 'python'
--select score.num from  course,score where score.c_id = course.c_id and course.c_name = 'java'
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、查询平均成绩大于65分的同学的姓名和平均成绩(保留两位小数);　

先让两张表关联起来   语句最后的;前有空格报错
每个学生平均成绩---->分组
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
round 函数保留n位小数点

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、查询没学过“alex”老师课的同学的姓名;

select course.c_id from teacher,course where teacher.t_id = course.t_id and teacher.t_id = 'alex';
select * from student where student.s_id not in (select score.s_id from score where score.c_id in (2,4))

6、查询学过'python'并且也学过编号'java'课程的同学的姓名;

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、查询学过“alex”老师所教的全部课程的同学的姓名；

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

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、查询有课程成绩小于60分的同学的姓名;

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

10、查询选修了全部课程的学生姓名;

select count(*) from course;
select 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 score.c_id from student,score where student.s_id = socre.s_id and student.s_name = '貂蝉'
select distinct student.s_name from student,score where
student.s_id = score.s_id and score.c_id in(1,2) and student.s_name !='貂蝉'

12、查询学过'貂蝉'同学全部课程的其他同学姓名；

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

13、查询和'貂蝉'同学学习的课程完全相同的,其他同学姓名；

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.c_id = student.c_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) group by score.s_id having count(*)=2 and student.s_name !='貂蝉'

14、按平均成绩倒序显示所有学生的“python”、“java”、“linux”三门的课程成绩，按如下形式显示：学生ID,python,java,linux,课程数,平均分

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 s group by s.s_sid
# as 可省略

15、统计各科各分数段人数.显示格式:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

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 score.c_id

16、查询每门课程被选修的次数

select course.c_name,count(*) from score,course where score.c_id = course.c_id group by score.c_id;

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

select student.s_id,student.s_name from score,student where score.s_id = student.s_id group by s_id having count(*)=1;

18、查询学生表中男生、女生各有多少人

select s_sex,count(*) from student group by s_sex;

19、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

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

20、查询课程名称为“python”，且分数低于60的学生姓名和分数

select student.s_name,score.num from course,score,student where course.c_id = score.c_id and score.s_id = student.s_id
and course.c_name = 'python' and score.num<70;

posted @ 2018-11-30 21:55 KK筑梦人阅读(145) 评论(0) 收藏举报

刷新页面返回顶部

MySQL练习 第三十九天 2018.11.30

MySQL面试题库练习

创建表

查询操作

1、查询学习课程"python"比课程 "java" 成绩高的学生的学号和姓名;

2、查询平均成绩大于65分的同学的姓名和平均成绩(保留两位小数);

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

4、查询所有的课程的名称以及对应的任课老师姓名;

5、查询没学过“alex”老师课的同学的姓名;

6、查询学过'python'并且也学过编号'java'课程的同学的姓名;

7、查询学过“alex”老师所教的全部课程的同学的姓名；

8、查询挂科超过两门(包括两门)的学生姓名;

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

10、查询选修了全部课程的学生姓名;

11、查询至少有一门课程与“貂蝉”同学所学课程相同的同学姓名；

12、查询学过'貂蝉'同学全部课程 的其他同学姓名；

13、查询和'貂蝉'同学学习的课程完全相同的,其他同学姓名；

14、按平均成绩倒序显示所有学生的“python”、“java”、“linux”三门的课程成绩，按如下形式显示： 学生ID,python,java,linux,课程数,平均分

15、统计各科各分数段人数.显示格式:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

16、查询每门课程被选修的次数

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

18、查询学生表中男生、女生各有多少人

19、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

20、查询课程名称为“python”，且分数低于60的学生姓名和分数

MySQL练习第三十九天 2018.11.30

2、查询平均成绩大于65分的同学的姓名和平均成绩(保留两位小数);　

12、查询学过'貂蝉'同学全部课程的其他同学姓名；

14、按平均成绩倒序显示所有学生的“python”、“java”、“linux”三门的课程成绩，按如下形式显示：学生ID,python,java,linux,课程数,平均分