mysql练习题40道

一、创建数据库，创建表，如下：

创建班级：班级id,班级
    create table class(
    cid int signed not null auto_increment primary key,
    caption varchar(32) not null
    )engine=innodb default charset=utf8;

    insert into class(cid,caption) values (1,'三年二班');
    insert into class(cid,caption) values (2,'一年三班');
    insert into class(cid,caption) values (3,'三年一班');

    insert into class(cid,caption) values (4,'三年三班');
    insert into class(cid,caption) values (5,'一年二班');
    insert into class(cid,caption) values (6,'三年一班');
    insert into class(cid,caption) values (7,'一年一班');
    insert into class(cid,caption) values (8,'二年一班');
    insert into class(cid,caption) values (9,'二年二班');
    insert into class(cid,caption) values (10,'二年三班');

    select * from class;


创建 学生：学生id,学生姓名，性别
    create table student(
    sid int signed not null auto_increment primary key,
    sname varchar(32) not null,
    gender varchar(32) not null,
    class_id int signed not null,
    constraint fk_cc foreign key(class_id) references class(cid)
    )engine=innodb default charset=utf8;


    insert into student(sid,sname,gender,class_id) values (1,'钢蛋','女','1');
    insert into student(sid,sname,gender,class_id) values (2,'铁锤','女','1');
    insert into student(sid,sname,gender,class_id) values (3,'山炮','男','2');


    insert into student(sname,gender,class_id) values ('学生李晓明','男','1');
    insert into student(sname,gender,class_id) values ('学生李颖','女','1');
    insert into student(sname,gender,class_id) values ('学生张三','男','4');
    insert into student(sname,gender,class_id) values ('学生李四','男','5');
    insert into student(sname,gender,class_id) values ('学生王五','男','6');
    insert into student(sname,gender,class_id) values ('学生吴永康','男','4');
    insert into student(sname,gender,class_id) values ('学生吴雪松','男','5');
    insert into student(sname,gender,class_id) values ('学生江阿生','男','2');
    insert into student(sname,gender,class_id) values ('学生司徒正妹','女','6');
    insert into student(sname,gender,class_id) values ('学生乔峰','男','7');
    insert into student(sname,gender,class_id) values ('学生郭靖','男','4');
    insert into student(sname,gender,class_id) values ('学生杨康','男','8');
    insert into student(sname,gender,class_id) values ('学生欧阳克','男','9');
    insert into student(sname,gender,class_id) values ('学生黄蓉','女','10');

    insert into student(sname,gender,class_id) values ('学生江阿生','男','6');
    insert into student(sname,gender,class_id) values ('学生黄蓉','女','4');
    insert into student(sname,gender,class_id) values ('学生吴雪松','男','2');

    select * from student;


创建老师：老师ID，老师姓名
    create table teacher(
    tid int signed not null auto_increment primary key,
    tname varchar(32) not null
    )engine=innodb default charset=utf8;

    insert into teacher(tid,tname) values (1,'波多');
    insert into teacher(tid,tname) values (2,'仓空');
    insert into teacher(tid,tname) values (3,'饭岛');

    insert into teacher(tname) values ('洪七公'), ('欧阳锋'),('金庸'),
    ('王重阳'),('周伯特'),('小龙女'),('张无忌'),('魏无忌'),('信陵君'),('春申君'),('孔夫子'),('魔帝');

    select * from teacher;
    insert into teacher(tname) values ('李莫愁'),("李沧海"),('李逍遥'),("巫行云");



创建课程：
    create table course(
    cid int signed not null auto_increment primary key,
    cname char(10) not null,
    teach_id int not null,
    constraint fk_tt foreign key(teach_id) references teacher(tid)
    )engine=innodb default charset=utf8;

    insert into course(cid,cname,teach_id) values (1,'生物','1');
    insert into course(cid,cname,teach_id) values (2,'体育','1');
    insert into course(cid,cname,teach_id) values (3,'物理','2');

    insert into course(cname,teach_id) values ('化学','2'),('政治','3'),('经济','4'),('历史','5'),('地理','6'),('语文','7')
    ,('英语','8'),('美术','9'),('音乐','10'),('哲学','11'),('希腊语','12'),('日语','13'),('降龙十八掌','14');

    insert into course(cname,teach_id) values ('数学','15');

    select * from course;



创建分数：
    create table score(
    sid int signed not null auto_increment primary key,
    student_id int signed not null,
    course_id int signed not null,
    number int signed not null,
    constraint uq1 unique(student_id,course_id),
    constraint fk_ss foreign key(student_id) references student(sid),
    constraint fk_xx foreign key(course_id) references course(cid)
    )engine=innodb default charset=utf8;


    insert into score(sid,student_id,course_id,number) values (1,1,1,60);
    insert into score(sid,student_id,course_id,number) values (2,1,2,59);
    insert into score(sid,student_id,course_id,number) values (3,2,2,100);

    insert into score(student_id,course_id,number) values (3,3,29),(3,4,59),(3,5,15),(3,6,80),(4,4,89),(4,5,99),(5,2,49)
    ,(5,6,79),(5,10,19),(5,14,34),(8,7,90),(8,2,69),(9,5,59),(5,12,50),(7,4,89),(7,8,59),(8,4,34),(8,3,89),(10,1,99);

    insert into score(student_id,course_id,number) values (5,15,78),(3,15,59);春申君

    insert into score(student_id,course_id,number) values (5,17,78),(3,17,59);
    insert into score(student_id,course_id,number) values (3,1,99),(8,1,60);



    insert into score(student_id,course_id,number) values (2,1,69),(5,1,70);
    insert into score(student_id,course_id,number) values (9,1,69),(9,2,70);


    select * from score;

 

 

二、练习题目

二、操作表

1、自行创建测试数据
按照上面所建立表，插入数据即可，可以随时增添。

2、查询“生物”课程比“物理”课程成绩高的所有学生的学号；
分别取出生物成绩表和物理成绩表，然后根据id分组，比较

生物成绩表，物理成绩表，都是同一个ID，成绩比较，注意是同行比较的思路

select A.student_id from
(select student_id,course.cname,score.number from score left join course on
score.course_id = course.cid where course.cname = "生物") as A
inner join
(select student_id,course.cname,score.number from score left join course on
 score.course_id = course.cid where course.cname = "物理") as B
on A.student_id = B.student_id
where A.number > B.number;

3、查询平均成绩大于60分的同学的学号和平均成绩；
select student_id,avg(number) from score group by student_id having avg(number) > 60;

查询平均成绩大于60分的同学的学号和姓名；
select student.sid,student.sname,b.c1 from (select student_id,avg(number) as c1 from score group by student_id
having avg(number) >60) as b left join student on b.student_id = student.sid;

4、查询所有同学的学号、姓名、选课数、总成绩；
select student.sid,student.sname,count(student_id),sum(number) from score left join student on
score.student_id = student.sid group by student_id;
选课数量＝学生组的个数

5、查询姓“李”的老师的个数；
select * from teacher where tname like "李%";
select count(tname) from teacher group by tname like "李%";

6、查询没学过“叶平”老师课的同学的学号、姓名；


有学过，根据学生ID分组，该学生学习的课程号 在某老师的课程组中等价于
查询学过“叶平”老师所教的课(一门，两门，或者所有课程)的同学的学号、姓名
    select student_id,student.sname from score
     left join course on course_id = course.cid
     left join student on student_id = student.sid
     where course_id in
     (select course.cid from teacher left join course on course.teach_id = teacher.tid where tname ="春申君")
     group by student_id;

没学过，根据学生ID分组，该学生学习的课程号 不在某老师的课程组中

     select student_id,student.sname from score
     left join course on course_id = course.cid
     left join student on student_id = student.sid
     where course_id not in
     (select course.cid from teacher left join course on course.teach_id = teacher.tid where tname ="春申君")
     group by student_id;

7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；

比学过的加了having条件，不但课程要在其中，而且数目要一致

select student_id,student.sname from score
 left join course on course_id = course.cid
 left join student on student_id = student.sid
 where course_id in
 (select cid from course left join teacher on course.teach_id = teacher.tid where tname ="春申君")
 group by student_id
 having count(score.course_id) = (select count(cid) from course left join teacher on course.teach_id = teacher.tid
 where tname ="春申君");


8、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；


学号，1,2课程名，1,2成绩暂时联合表
select * from
(select student_id,course_id,number from score where course_id = 1) as A
inner join
(select student_id,course_id,number from score where course_id = 2) as B
on A.student_id = B.student_id
group by A.student_id;

select student_id,student.sname from score
left join student on score.student_id = student.sid
where course_id =1 or course_id =2
group by student_id
having count(student_id)>1


select A.student_id,student.sname from
(select student_id,course_id from score where course_id = 1) as A
left join
(select student_id,course_id from score where course_id = 2) as B
on A.student_id = B.student_id
left join student on A.student_id = student.sid
group by A.student_id;



9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；

select A.student_id,student.sname from
(select student_id,course_id,number from score where course_id = 1) as A
inner join
(select student_id,course_id,number from score where course_id = 2) as B
on A.student_id = B.student_id
left join student on A.student_id = student.sid
where A.number > B.number
group by A.student_id;

10、查询有课程成绩小于60分的同学的学号、姓名；

select student_id from score where number < 60 group by student_id;

select student_id,student.sname from score left join student on score.student_id = student.sid where number <60
group by student_id;

11、查询没有学全所有课的同学的学号、姓名；

    注意把语意语言转变为逻辑语言，将逻辑语言转变为计算机高级语言，没学全所有课程等价于 < 所有课的数目
    统计每个学生几门课
    select student_id,count(student_id) from score group by student_id;
    总可课程数
    select count(course.cid) from course;

    正确的做法
    select student.sid,student.sname from student
    where (select count(student_id) From score where student.sid=score.student_id) < (select count(course.cid) from course);


    以下方式是错误的，因为是一行行比较的，group by student_id 的count 返回的不只是一行数据，因此要进行count比较必须用
    where student.sid=score.student_id这样子会一个个赋值比较，紧跟group by的是一个集合的值

    select student.sid,student.sname from student
    where (select count(student_id) From score group by student_id) < (select count(course.cid) from course);


12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；
除了001以外的同学并且任何一个课程在001的课程组中


 思路：
        获取 001 同学选择的所有课程
        获取课程在其中的所有人以及所有课程
        根据学生筛选，获取所有学生信息
        再与学生表连接，获取姓名

        select student_id,sname, count(course_id) from score
        left join student on score.student_id = student.sid
        where student_id != 1 and course_id in (select course_id from score where student_id = 1)
        group by student_id;

        001同学所学习的课程，
        select course_id,course.cname from score
        left join course on score score.course_id = course.cid
        where student_id = 1;


        select student_id,sname,count(1) from score
        left join student on score.student_id = student.sid
        where student_id != 1 and course_id in (select course_id from score where student_id = 1)
        group by student_id;


13、查询至少学过学号为“001”同学［所有课］的其他同学学号和姓名;

        意思是，至少学过1同学的三门课，其他同学学的比他还多
        select student_id,sname,count(1) from score
        left join student on score.student_id = student.sid
        where student_id != 1 and course_id in (select course_id from score where student_id = 1)
        group by student_id
        having count(course_id) >=  (select count(course_id) from score where student_id = 1);

14、查询和“001”号的同学学习的课程［完全相同］的其他同学学号和姓名；
        数目和科目相同，数等于数，in []=in []

        先拿到课程数相同，然后not i，
        这里是逐行查询

        002的所有课程
        select course_id from score where student_id = 2;

        个数相同
        002学过的也学过

        符合条件：
        学号不等于1，并且学号的选课个数＝002同学的选课数目，筛选出一部分非001的有同时几门课的学号，本来一般用where，但是第二个符合参数是聚合函数，用wgh结构
        这些学号的课程在，002的课程列表中，in ,如果都是3门课，其中一门不在，那么也不符合，所以是完全匹配

        select student_id,sname from score left join student on score.student_id = student.sid
        where student_id in (
                    select student_id from score where student_id != 1 group by student_id HAVING count(course_id) =
                    (select count(1) from score where student_id = 1)
                )
        and course_id in (select course_id from score where student_id = 1)
        group by student_id;


        会显示学生id,姓名,课程名
        select student_id,student.sname,course_id from score
        left join student on score.student_id = student.sid
        where student_id in (select student_id from score where student_id != 1 group by student_id having count(student_id) =
            (select count(student_id) from score where student_id = 1))
            and course_id in (select course_id from score where student_id = 1);

        会显示学生id,姓名去重（思考：什么时候出来的时候是重复的，需要去重）
        select student_id,student.sname from score
        left join student on score.student_id = student.sid
        where student_id in (select student_id from score where student_id != 1 group by student_id having count(student_id) =
            (select count(student_id) from score where student_id = 1))
            and course_id in (select course_id from score where student_id = 1)
        group by student_id;

15、删除学习“叶平”老师课的SC表记录；

select * from score
 left join course on score.course_id = course.cid
 left join teacher on course.teach_id = teacher.tid
 where teacher.tname = "春申君";

 delete from score
 left join course on score.course_id = course.cid
 left join teacher on course.teach_id = teacher.tid
 where teacher.tname = "春申君";

delete score
    from course ,teacher
    where course.cid=score.course_id and course.teach_id= teacher.tid and tname='春申君';

16、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩；
没有上过编号“002”课程的同学学号
select student_id from score where course_id !=2 group by student_id;

以下做法是错的，course_id !=2 不能准确选择出不学这个课程，必须通过course_id  =2  先挑选出这些学生，再not in
insert into score(student_id,course_id,number)
select student_id,2,(select avg(number) from score where course_id =2) from score where course_id !=2;

正确的做法
insert into score(student_id, course_id, number)
select student_id,2,(select avg(number) from score where course_id = 2) from student
where sid not in (select student_id from score where course_id = 2)；


17、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；


        select s1.student_id,
            (select number from score as s2 where s2.student_id=s1.student_id and course_id = 1) as 语文,
            (select number from score as s2 where s2.student_id=s1.student_id and course_id = 2) as 数学,
            (select number from score as s2 where s2.student_id=s1.student_id and course_id = 3) as 英语,
            count(s1.course_id),
            avg(s1.number) as a1
        from score as s1
        group by student_id
        order by avg(s1.number) asc;


18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
select course_id,max(number),min(number),avg(number) from score group by course_id;

19、按各科平均成绩从低到高和及格率的百分数从高到低顺序；
select course_id,avg(number),sum(case when number<60 then 0 else 1 end) as 及格数,sum(1) as 总数,
sum(case when number<60 then 0 else 1 end)/sum(1) as 及格率 from score
group by course_id order by avg(number) asc,及格率 desc;

20、课程平均分从高到低显示（显示任课老师）；
select course_id,course.cname,teacher.tname,avg(number) from score
left join course on score.course_id = course.cid
left join teacher on course.teach_id = teacher.tid
group by course_id
order by avg(number) desc;

21、查询各科成绩前三名的记录:(不考虑成绩并列情况)
    单科目前三名,每科就是变量
    select student_id,student.sname,course_id,number from score
    left join student on score.student_id = student.sid
    where course_id =1
    order by number desc limit 0,3;



    每科目前三名：取每一门科目第一名，第四名，凡大于第四名的即前三名
     select score.sid,score.course_id,score.number,T.first_number,T.second_number from score left join
        (
        select
            sid,
            (select number from score as s2 where s2.course_id = s1.course_id order by number desc limit 0,1) as first_number,
            (select number from score as s2 where s2.course_id = s1.course_id order by number desc limit 3,1) as second_number
        from
            score as s1
        ) as T
        on score.sid =T.sid
        where score.number <= T.first_number and score.number > T.second_number;


22、查询每门课程被选修的学生数；
select course_id,count(1) from score group by course_id;
查询热门课程被选修的学生数
select course_id,count(1) from score group by course_id having count(1)>5;

23、查询出只选修了一门课程的全部学生的学号和姓名；
select student_id,student.sname from score group by student_id having count(1)=1;

24、查询男生、女生的人数；
select count(gender) from student group by gender having gender="男"；
select count(gender) from student group by gender having gender="女"；

25、查询姓“张”的学生名单；
select sname from student where sname like "学生吴%";

26、查询同名同姓学生名单，并统计同名人数；
select sname,count(sname) as c1 from student group by sname having count(c1) >1;

27、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
select course_id,avg(number) from score group by course_id order by avg(number) asc,course_id desc;

28、查询平均成绩大于85的所有学生的学号、姓名和平均成绩；
select student_id,sname,avg(number) from score left join student on score.student_id =student.sid group by student_id
having avg(number)>85;

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

    先链表 再条件
    select student_id,number from score
    left join student on score.student_id = student.sid
    left join course on score.course_id = course.cid
    where course.cname = '数学' and number <60;


    先条件 再内链表
    select student_id,number from score
    where score.number < 60 and score.course_id=(select cid from course where cname = "数学");



30、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；
select student_id,student.sname from score
left join student on score.student_id =  student.sid
where score.course_id =3 and number >80;

31、求选了课程的学生人数
(score 表里插的都是至少选了一条记录的)
select student_id,count(student_id) from score group by student_id having count(student_id) >=1;
select count(c1) from (select count(student_id) as c1 from score group by student_id having count(course_id) >=1) as t1;

32、查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩；

由于题目并没有要求是xxx老师哪门科目成绩最高，只要是学生科目落在老师教的科目范围中，就是他的学生，然后直街倒序分数取第一条即可
select sname,number from score
left join student on score.student_id = student.sid
where score.course_id in (select course.cid from course left join teacher on course.teach_id = teacher.tid where tname='波多')
order by number desc
limit 1;

先做where形成子表，然后去重排序
select sname,course_id,number from score
left join student on score.student_id = student.sid
where score.course_id in (select cid from course left join teacher on course.teach_id = teacher.tid where tname='波多')
order by number desc
limit 1;


33、查询各个课程及相应的选修人数；
select course_id,count(course_id) from score group by course_id order by count(course_id) desc;

34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；

两表联合，记录相乘，笛卡尔积
一个人：课程号不同，成绩相同，比如数学99，英语99
另个人：课程号相同，成绩相同，比如数学99，英语99，不符合

思路：一表变成两个表，两表成绩相同，但是课程号码不同

注意：distinct效率底下，少用
select DISTINCT s1.course_id,s2.course_id,s2.number from score as s1, score as s2
where s1.number = s2.number and s1.course_id != s2.course_id;

根据分数从小到大排列
select s1.student_id,s1.course_id,s1.number from score as s1, score as s2
where s1.number = s2.number and s1.course_id != s2.course_id order by s1.number asc;

select s1.student_id,s1.course_id,s1.number from score as s1, score as s2
where s1.number = s2.number and s1.course_id != s2.course_id;


35、查询每门课程成绩最好的前两名；
一表变成两表，第一名，第二名是两列，

select score.sid,student_id,score.course_id,score.number,T.first_num,T.second_num from score
left join
    (
    select
        sid,
        course_id,
        (select number from score as s2 where s2.course_id = s1.course_id order by number desc limit 0,1) as first_num,
        (select number from score as s2 where s2.course_id = s1.course_id order by number desc limit 1,1) as second_num
    from
        score as s1
    ) as T
    on score.sid =T.sid
    where score.number <= T.first_num and score.number >= T.second_num;



    以下这种做法无法排除空值，second_num的NULL也出来了，不对
     select
        course_id,
        (select number from score as s2 where s2.course_id = s1.course_id order by number desc limit 0,1) as first_num,
        (select number from score as s2 where s2.course_id = s1.course_id order by number desc limit 1,1) as second_num
    from
        score as s1
    group by course_id;


36、检索至少选修两门课程的学生学号；
select student_id,count(1) from score group by student_id having count(1) >=2;


37、查询全部学生都选修的课程的课程号和课程名；
根据课程分组，统计每门课程的学生人数＝总学生人数
select course_id,count(1) from score group by course_id having count(1) = (select count(1) from student);

挑选最热门的课
select course_id,count(course_id) from score group by course_id order by count(course_id) desc limit 0,1;

38、查询没学过“叶平”老师讲授的任一门课程的学生姓名；
找学过老师课程的所有学生,然后所有学生not in

select student_id,student.sname from score
left join student on score.student_id = student.sid
where score.course_id not in (select cid from course left join teacher on course.teach_id = teacher.tid where tname = "春申君")
group by student_id;


39、查询两门以上不及格课程的同学的学号及其平均成绩；

select student_id,count(1),avg(number) from score where number < 60 group by student_id having count(1) > 2;

40、检索“004”课程分数小于60，按分数降序排列的同学学号；
select student_id from score where course_id =4 and number <60 order by number desc;

41、删除“002”同学的“001”课程的成绩；
delete from score where student_id = 2 and course_id = 1;

 

以上问题思路很关键，一张表变成两张表，先求什么再求什么。

明白inner join/left join,where,group by,having（select中有聚合函数sum(),max(),min(),avg()等必须使用）,order by的使用

方法必须牢记。