mysql测试题蔡铜_MySQL：测试题

一，表关系的练习测试

请创建如下表关系，并建立相关约束

image.png

一，创建表结构数据：

创建的话肯定先创建没有关联的表，老师，课程(关联老师)，年级，班级(关联年级)，学生(关联班级)，

班级任职表 (关联老师，课堂)

create table teacher(

tid int primary key auto_increment,

tname varchar(16) not null

);

create table class_grade(

gid int primary key auto_increment,

gname varchar(16) not null unique

);

create table course(

cid int primary key auto_increment,

cname varchar(16) not null,

teacher_id int not null,

foreign key(teacher_id) references teacher(tid)

);

create table class(

cid int primary key auto_increment,

caption varchar(16) not null,

grade_id int not null,

foreign key(grade_id) references class_grade(gid)

on update cascade

on delete cascade

);

create table student(

sid int primary key auto_increment,

sname varchar(16) not null,

gender enum('女','男') not null default '男',

class_id int not null,

foreign key(class_id) references class(cid)

on update cascade

on delete cascade

);

create table score(

sid int not null unique auto_increment,

student_id int not null,

course_id int not null,

score int not null,

primary key(student_id,course_id),

foreign key(student_id) references student(sid)

on delete cascade

on update cascade,

foreign key(course_id) references course(cid)

on delete cascade

on update cascade

);

create table teach2cls(

tcid int not null unique auto_increment,

tid int not null,

cid int not null,

primary key(tid,cid),

foreign key(tid) references teacher(tid)

on delete cascade

on update cascade,

foreign key(cid) references class(cid)

on delete cascade

on update cascade

);

2，插入表数据

插入数据

老师的数据

年级的数据

班级的数据

课程的数据

学生的数据

成绩的数据

老师班级的数据

insert into teacher(tname) values

('张三'),

('李四'),

('王五');

insert into class_grade(gname) values

('一年级'),

('二年级'),

('三年级');

insert into class(caption,grade_id) values

('一年一班',1),

('一年二班',1),

('一年三班',1),

('二年一班',2),

('二年二班',2),

('二年三班',2),

('三年一班',3),

('三年二班',3),

('三年三班',3);

insert into course(cname,teacher_id) values

('生物',1),

('体育',1),

('物理',2),

('数学',2),

('马克思',3),

('外语',3),

('计算机',3);

insert into student(sname,gender,class_id) values

('乔丹','男',1),

('艾弗森','男',1),

('科比','男',2);

insert into score(student_id,course_id,score) values

(1,1,60),

(1,2,59),

(1,3,58),

(2,1,99),

(2,2,99),

(2,3,89),

(3,1,59),

(3,3,30);

insert into teach2cls(tid,cid) values

(1,1),

(1,2),

(1,3),

(1,5),

(2,4),

(2,6),

(2,8),

(2,9),

(2,1),

(2,5),

(3,7),

(3,1),

(3,3),

(3,5),

(3,9);

补充数据

insert into teacher(tname) values

('赵六'),

('苗七');

insert into class_grade(gname) values

('四年级');

insert into class(caption,grade_id) values

('四年一班',4),

('四年二班',4),

('四年三班',4),

('四年四班',4);

insert into course(cname,teacher_id) values

('线性代数',4);

insert into student(sname,gender,class_id) values

('张一','女',3),

('詹姆斯','男',3),

('荷花','女',3),

('杜兰特','男',3),

('哈登','男',4),

('尼克','男',4),

('青青','女',4),

('阿里扎','男',4);

insert into score(student_id,course_id,score) values

(3,4,60),

(4,1,59),

(4,2,100),

(4,3,90),

(4,4,80),

(5,1,59),

(5,2,33),

(5,3,12),

(5,4,88),

(6,1,100),

(6,2,60),

(6,3,59),

(6,4,100),

(7,1,20),

(7,2,36),

(7,3,57),

(7,4,60),

(8,1,61),

(8,2,59),

(8,3,62),

(8,4,59),

(9,1,60),

(9,2,61),

(9,3,21);

insert into teach2cls(tid,cid) values

(4,1),

(4,2),

(4,3),

(4,4),

(5,1),

(5,2),

(5,3),

(5,4);

二，操作表格内容

1、自行创建测试数据；

上面已经完成。

2、查询学生总人数；

select count(sid) from student;

3、查询“生物”课程和“物理”课程成绩都及格的学生id和姓名；

思路：获取所有生物课程的人(学号，成绩)--临时表

获取所有物理课程的人(学号，成绩)--临时表

根据学号连接两个临时表：学号，物理成绩，生物成绩

然后筛选及格的

select sid,sname

from student

where sid in(

select score.student_id from score inner join course on score.course_id=course.cid

where course.cname in('生物','物理') and score.score >=60

group by score.student_id having count(course_id) = 2);

4、查询每个年级的班级数，取出班级数最多的前三个年级；

思路：首先分析班级前三的情况，分为班级数相同的情况和班级数不同的情况

如果班级数相同，那么只需要考虑在班级里面统计班级数量即可，

然后在班级年级表中取出对应的年级数目

如果班级数不相同，那么首先班级里面统计班级数量，

然后在按照降序排列，取前三即可

#包含班级数不相同的排名前三年级

select class_grade.gname from class_grade inner join(

select grade_id,count(id) as count_cid from class group by grade_id order by count_cid desc limit 3)

as t1 on class_grade.gid = t1.grade_id;

#包含了班级数相同的排名前三年级

select gname from class_grade where gid in (

select grade_id from class group by grade_id having count(cid) in (

5、查询平均成绩最高和最低的学生的id和姓名以及平均成绩；

create view t1 as

select student_id avg(score) as avg_score from score group by student_id;

select sname,avg_score from t1 left join student on t1.student_id =student.sid

where t1.avg_score = ( select max(t1.avg_score) from t1) or t1.avg_score =

(select min(t1.avg_score) from t1);

6、查询每个年级的学生人数；

思路：先在学生表和班级表对应一下, 然后在对应班级表中查找学生人数

select t1.grade_id,count(t1.sid) as count_student from (

select student.sid ,class.grade_id from student,class

where student.class_id =class.cid) as t1 group by t1.grade_id;

7、查询每位学生的学号，姓名，选课数，平均成绩

思路：学生表中有学生学号，姓名，性别，班级 成绩表中对应成绩，所以

我们可以联立成绩表和学生表，并按学生id分类，直接查找即可。

select score.student_id,student.sname,sum(score.course_id),avg(score.score)

from score left join student on score.student_id = student.sid

group by score.student_id;

8、查询学生编号为“2”的学生的姓名、该学生成绩最高的课程名、成绩最低的课程名及分数；

思路：首先在成绩表中查找学生编号为2 的学生的最大最小成绩，学生id，课程id，

然后在课程表和学生表中找到对应的学生姓名和课程名称，

最后联立表格得出学生姓名，课程名称，分数

select student.sname,course.cname,t1.score from (

select student_id,course_id,score from score where student_id = 2 and score in((

select max(score) from score where student_id = 2),

(select min(score) from score where student_id = 2))) as t1

inner join student on t1.student_id = student.sid

inner join course on t1.course_id = course.cid;

9、查询姓“李”的老师的个数和所带班级数；

思路：首先在老师表中寻找姓李老师的id

然后在teach2cls中找到老师和班级的联系，并统计姓李老师所带的班级数

最后在老师表中查询老师id和姓名。

select teacher.tid as '姓李id', teacher.tname as '老师姓名' ,GROUP_CONCAT(teach2cls.cid) as '班级数'

from teacher left join teach2cls on teacher.tid = teach2cls.tid

where teacher.tname like '李%' group by teacher.tid;

10、查询班级数小于5的年级id和年级名

思路：首先查询班级表中