前言

大家好，分享一些MySQL练习题，都是自己整理的考试例题附有答案，可用作巩固知识点，也可以用来考前复习。

一、创建数据库

1.创建一个叫做Mytest 的数据库，在该库中建立以下5张表，表之间的关系及字段如图所示：

在这里插入图片描述
创建数据库：

create database Mytest;

创建teacher表：

create table teacher(id smallint(4) not null primary key,name varchar(20));

创建class表：

create table class(id smallint(6) not null primary key,name varchar(50));

创建course表

create table course(id smallint(3) not null primary key,name varchar(100), teacher_id smallint(6) not null,foreign key(teacher_id) references teacher(id));

创建student表

create table student(id smallint(6) not null primary key,name varchar(20),gender char(2),class_id smallint(6) not null,foreign key(class_id) references class(id));

创建score表

create table score(id int(11),student_id smallint(6) not null,course_id smallint(3) not null,mark tinyint(4),foreign key(student_id) references student(id),foreign key(course_id) references course(id),primary key(student_id,course_id));

2.插入下图数据

在这里插入图片描述
有两种方式插入数据（演示两表）：

1.单行插入

例：插入class表数据

insert into class values(1,"软件工程1班");insert into class values(2,"计算机科学技术1班");insert into class values(3,"网络工程1班");

2,。批量插入

例：插入teacher表数据

insert into teacher values(1,"老虎"),(2,"小马"),(3,"大牛");

二、操作数据库

1.查询所有的课程的名称以及对应的任课老师姓名；

select course.name,teacher.namefrom course inner join teacheron course.teacher_id = teacher.id;

2.查询学习课程"数据结构"比课程"java语言"成绩低的学生的学号；

select shuju.student_id from(select score.student_id,course.name,score.mark from score inner join course on score.course_id=course.id where course.name="数据结构") as shujuinner JOIN(select score.student_id,course.name,score.mark from score inner join course on score.course_id=course.id where course.name="java语言") as javaon shuju.student_id=java.student_idwhere shuju .mark<java.mark;

3 查询平均成绩大于65分的同学的id和平均成绩(保留两位小数)；

select student_id,round(avg(mark),2) as grade from scoregroup by student_id having grade>65;

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

select student.name,round(avg(mark),2) as grade from scoreinner joinstudentonscore.student_id=student.idgroup by student_id having grade>65;

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

select student.name,count(score.course_id) as "选课数",sum(score.mark) as "总成绩" from score inner join student on score.student_id=student.idgroup by student_id;

6. 查询没学过"大牛"老师课的同学的姓名；

select name from student where id not in(select id from score where course_id in(3,3));

7. 查询学过"大牛"老师所教的全部课程的同学的姓名；

select student.name from score inner join student on score.student_id=student.id where score.course_id in(select course.id from course inner join teacheron course.teacher_id=teacher.id where teacher.name="大牛")group by score.student_id;

8. 查询有课程成绩小于60分的同学的姓名；

select name from student where id in(select student_id from score where mark<60 group by student_id);

9. 查询选修了全部课程的学生姓名；

select name from student where id in(select student_id from score group by student_id having count(1)=(select count(1) from course));

10. 查询至少有一门课程与"小草"同学所学课程相同的同学姓名；

select name as"姓名" from student where id in(select student_id from score where course_id in(select course_id from score inner join student on score.student_id=student.id where student.name="小草")group by student_id)and name!="小草";

11. 查询至少有一门课程和"小草"同学所学课程不相同的同学姓名；

select name as "姓名" from student where id in(select student_id from score where course_id in(select course_id from score inner join student on score.student_id!=student.id where student.name="小草")group by student_id)and name!="小草";

12. 查询各科成绩最高和最低的分：以如下形式显示：课程id，最高分，最低分；

select course_id as "课程id",max(mark) as "最高分",min(mark) as "最低分" from score group by course_id;

13. 查询只选修了一门课程的学生的学号和姓名；

select student_id as"学生学号",student.name as"学生姓名" from scoreinner join student on score.student_id=student.idgroup by student_id having count(course_id)=1;

14. 查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程id降序排列。

select course.name as"课程名称",avg(mark) as"平均成绩" from scoreinner join course on score.course_id=course.idgroup by course_idorder by avg(mark) asc,course_id desc;

15. 按平均成绩倒序显示所有学生的"数据库原理"、“java语言”、"C语言"三门的课程成绩，按如下形式显示：学生id、数据库原理、java语言、C语言、课程数、平均分；(高级应用较难)

先查询单一学生的数据库原理课程分数

select mark as"分数" from score left join course on score.course_id = course.idwhere course.name = "数据库原理" and score.student_id=1;

将上面查询的结果作为列字段使用，得到最终结果

select student_id as"学生学号",(select mark from score left join course on score.course_id = course.id where course.name = "数据库原理"and score.student_id=sc.student_id) as "数据库原理",(select mark from score left join course on score.course_id = course.id where course.name = "java语言" and score.student_id=sc.student_id) as "java语言",(select mark from score left join course on score.course_id = course.id where course.name = "C语言" and score.student_id=sc.student_id) as "C语言",count(course_id) as "课程数",avg(mark) as "平均分"from score as scgroup by student_id order by avg(mark) desc;