50道练习题

create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));

insert into Student values('01' , '赵雷' , '1990-01-01' , '男');

insert into Student values('02' , '钱电' , '1990-12-21' , '男');

insert into Student values('03' , '孙风' , '1990-12-20' , '男');

insert into Student values('04' , '李云' , '1990-12-06' , '男');

insert into Student values('05' , '周梅' , '1991-12-01' , '女');

insert into Student values('06' , '吴兰' , '1992-01-01' , '女');

insert into Student values('07' , '郑竹' , '1989-01-01' , '女');

insert into Student values('09' , '张三' , '2017-12-20' , '女');

insert into Student values('10' , '李四' , '2017-12-25' , '女');

insert into Student values('11' , '李四' , '2012-06-06' , '女');

insert into Student values('12' , '赵六' , '2013-06-13' , '女');

insert into Student values('13' , '孙七' , '2014-06-01' , '女');

科目表 Course

create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));

insert into Course values('01' , '语文' , '02');

insert into Course values('02' , '数学' , '01');

insert into Course values('03' , '英语' , '03');

教师表 Teacher

create table Teacher(TId varchar(10),Tname varchar(10));

insert into Teacher values('01' , '张三');

insert into Teacher values('02' , '李四');

insert into Teacher values('03' , '王五');

成绩表 SC

create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));

insert into SC values('01' , '01' , 80);

insert into SC values('01' , '02' , 90);

insert into SC values('01' , '03' , 99);

insert into SC values('02' , '01' , 70);

insert into SC values('02' , '02' , 60);

insert into SC values('02' , '03' , 80);

insert into SC values('03' , '01' , 80);

insert into SC values('03' , '02' , 80);

insert into SC values('03' , '03' , 80);

insert into SC values('04' , '01' , 50);

insert into SC values('04' , '02' , 30);

insert into SC values('04' , '03' , 20);

insert into SC values('05' , '01' , 76);

insert into SC values('05' , '02' , 87);

insert into SC values('06' , '01' , 31);

insert into SC values('06' , '03' , 34);

insert into SC values('07' , '02' , 89);

insert into SC values('07' , '03' , 98);

 

select * from (select * from SC where CId=01) as t1, (select * from SC where CId=02) as t2 where t1.SId=t2.SId;

 

mysql> select * from
    -> (select * from SC where CId=01) as t1
    -> left join
    -> (select * from SC where CId=02) as t2
    -> on t1.SId=t2.SId;

 

 

mysql> select * from SC
    -> where SC.SId not in(
    -> select SId from SC
    -> where SC.CId=01)
    -> and SC.CId=02;

mysql> select Student.* from 
    -> Student,Teacher,Course,SC
    -> where 
    -> Student.SId=SC.SId
    -> and Course.CId=SC.CId
    -> and Course.TId=Teacher.TId
    -> and tname='张三';

 

 

 

mysql> select * from Student
    -> where Student.SId not in (
    ->   select SC.SId from SC
    ->   group by SC.SId
    ->   having count(SC.CId)= (select count(CId) from Course)
    -> );

mysql> select * from Student
    -> where Student.SId in (
    -> select SC.SId from SC
    -> where SC.SId in (
    -> select SC.CId from SC
    -> where SC.SId='01'));

 

mysql> select * from Student
    -> where Student.SId not in(
    -> select SC.SId from SC where SC.CId in(
    -> select Course.CId from Course where Course.TId in(
    -> select Teacher.TId from Teacher where Tname ='张三')));

mysql> select Student.SId, Student.Sname,b.avg
    -> from Student RIGHT JOIN
    -> (select SId, AVG(score) as avg from SC
    ->     where SId in (
    ->               select SId from SC 
    ->               where score<60 
    ->               GROUP BY SId 
    ->               HAVING count(score)>1)
    ->     GROUP BY SId) b on Student.SId=b.SId;

mysql> select Student.*, SC.score from Student, SC
    -> where Student.SId = SC.SId
    -> and SC.score < 60
    -> and CId = "01"
    -> ORDER BY SC.score DESC;

mysql> select *  from SC 
    -> left join (
    ->     select SId,avg(score) as avscore from SC 
    ->     group by SId
    ->     )r 
    -> on SC.SId = r.SId
    -> order by avscore desc;

mysql> select 
    -> SC.CId ,
    -> max(SC.score)as 最高分,
    -> min(SC.score)as 最低分,
    -> AVG(SC.score)as 平均分,
    -> count(*)as 选修人数,
    -> sum(case when SC.score>=60 then 1 else 0 end )/count(*)as 及格率,
    -> sum(case when SC.score>=70 and SC.score<80 then 1 else 0 end )/count(*)as 中等率,
    -> sum(case when SC.score>=80 and SC.score<90 then 1 else 0 end )/count(*)as 优良率,
    -> sum(case when SC.score>=90 then 1 else 0 end )/count(*)as 优秀率 
    -> from SC
    -> GROUP BY SC.CId
    -> ORDER BY count(*)DESC, SC.CId ASC;

 

mysql> select a.CId, a.SId, a.score, count(b.score)+1 as rank
    -> from SC as a 
    -> left join SC as b 
    -> on a.score<b.score and a.CId = b.CId
    -> group by a.CId, a.SId,a.score
    -> order by a.CId, rank ASC;

mysql> select q.SId, total, @crank := @crank +1 as rank from(
    -> select SC.SId, sum(SC.score) as total from SC
    -> group by SC.SId
    -> order by total desc)q;

mysql> select Course.cname, Course.CId,
    -> sum(case when SC.score<=100 and SC.score>85 then 1 else 0 end) as "[100-85]",
    -> sum(case when SC.score<=85 and SC.score>70 then 1 else 0 end) as "[85-70]",
    -> sum(case when SC.score<=70 and SC.score>60 then 1 else 0 end) as "[70-60]",
    -> sum(case when SC.score<=60 and SC.score>0 then 1 else 0 end) as "[60-0]"
    -> from SC left join Course
    -> on SC.CId = Course.CId
    -> group by SC.CId;

mysql> select * from SC
    -> where (
    -> select count(*) from SC as a 
    -> where SC.CId = a.CId and SC.score<a.score 
    -> )< 3
    -> order by CId asc, SC.score desc;

mysql> select CId, count(SId) from SC 
    -> group by CId;

mysql> select Student.SId, Student.sname from Student
    -> where Student.SId in
    -> (select SC.SId from SC
    -> group by SC.SId
    -> having count(SC.CId)=2
    -> );

mysql> select Ssex, count(*) from Student
    -> group by Ssex;

mysql> select *
    -> from Student 
    -> where Student.Sname like '%风%';

mysql> select Sname, count(*) from Student
    -> group by Sname
    -> having count(*)>1;

mysql> select *
    -> from Student
    -> where YEAR(Student.Sage)=1990;

select SC.CId, Course.Cname, AVG(SC.SCORE) as average from SC, Course
where SC.CId = Course.CId
group by SC.CId 
order by average desc,CId asc;

select Student.SId, Student.Sname, AVG(SC.Score) as aver from Student, SC
where Student.SId = SC.SId
group by SC.SId
having aver > 85;

mysql> select Student.Sname, SC.score from Student, SC, Course
    -> where Student.SId = SC.SId
    -> and Course.CId =SC.CId
    -> and Course.Cname = "数学"
    -> and SC.score < 60;

mysql> select Student.sname, Course.Cname,SC.score from Student,Course,SC
    -> where SC.score>70
    -> and Student.SId = SC.SId
    -> and SC.CId = Course.CId;

mysql> select DISTINCT SC.CId
    -> from SC
    -> where SC.score <60;

mysql> select Student.SId,Student.Sname 
    -> from Student,SC
    -> where CId="01"
    -> and score>=80
    -> and Student.SId = SC.SId;

mysql> select SC.CId,count(*) as 学生人数
    -> from SC
    -> GROUP BY SC.CId;

mysql> select Student.*, SC.score, SC.CId from Student, Teacher, Course,SC 
    -> where Teacher.TId = Course.TId
    -> and SC.SId = Student.SId
    -> and SC.CId = Course.CId
    -> and Teacher.Tname = "张三"
    -> order by score desc
    -> limit 1;

mysql> select Student.*, SC.score, SC.CId from Student,Teacher,Course,SC 
    -> where Teacher.TId = Course.TId
    -> and SC.SId = Student.SId
    -> and SC.CId = Course.CId
    -> and Teacher.Tname = "张三"
    -> and SC.Score = (
    ->     select Max(SC.score) 
    ->     from SC,Student, Teacher, Course
    ->     where Teacher.TId = Course.TId
    ->     and SC.SId = Student.SId
    ->     and SC.CId = Course.CId
    ->     and Teacher.Tname = "张三"
    -> );

mysql> select  a.CId, a.SId,  a.score from SC as a
    -> inner join 
    -> SC as b
    -> on a.SId = b.SId
    -> and a.CId != b.CId
    -> and a.score = b.score
    -> group by CId, SId;

mysql> select a.SId,a.CId,a.score from SC as a 
    -> left join SC as b 
    -> on a.CId = b.CId and a.score<b.score
    -> group by a.CId, a.SId
    -> having count(b.CId)<2
    -> order by a.CId;

mysql> select SC.CId, count(SId) as cc from SC
    -> group by CId
    -> having cc >5;

mysql> select SId, count(CId) as cc from SC
    -> group by SId
    -> having cc>=2;

mysql> select Student.*
    -> from SC ,Student 
    -> where SC.SId=Student.SId
    -> GROUP BY SC.SId
    -> HAVING count(*) = (select DISTINCT count(*) from Course );

mysql> select Student.SId as 学生编号,Student.Sname  as  学生姓名,
    -> TIMESTAMPDIFF(YEAR,Student.Sage,CURDATE()) as 学生年龄
    -> from Student;

mysql> select *
    -> from Student 
    -> where WEEKOFYEAR(Student.Sage)=WEEKOFYEAR(CURDATE());

mysql> select *
    -> from Student 
    -> where WEEKOFYEAR(Student.Sage)=WEEKOFYEAR(CURDATE())+1;

mysql> select *
    -> from Student 
    -> where MONTH(Student.Sage)=MONTH(CURDATE());

mysql> select *
    -> from Student 
    -> where MONTH(Student.Sage)=MONTH(CURDATE())+1;