mysql查询（一）

1 建表

create database stubase;

use stubase;

create table Student(Sid varchar(10), Sname varchar(10), Sage datetime ,Ssex varchar(10))engine=InnoDB default charset=utf8;;

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' , '女');


create table course(cid varchar(10), cname nvarchar(10) , tid varchar(10))engine=InnoDB default charset=utf8;;

insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');


create table teather(tid varchar(10), tname varchar(10))engine=InnoDB default charset=utf8;;
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

create table sc(sid varchar(10), cid varchar(10), score decimal(18,1))engine=InnoDB default charset=utf8;;
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);

2 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

select * from Student RIGHT JOIN (
    select t1.SId, class1, class2 from
          (select SId, score as class1 from sc where sc.CId = '01')as t1, 
          (select SId, score as class2 from sc where sc.CId = '02')as t2
    where t1.SId = t2.SId AND t1.class1 > t2.class2
)r 
on Student.SId = r.SId;

3 查询同时存在" 01 "课程和" 02 "课程的情况

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

4查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

select * from 
(select * from sc where sc.CId = '01') as t1
left join 
(select * from sc where sc.CId = '02') as t2
on t1.SId = t2.SId;

5 查询不存在" 01 "课程但存在" 02 "课程的情况

select * from sc
where sc.SId not in (
    select SId from sc 
    where sc.CId = '01'
) 
AND sc.CId= '02';

6 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

select student.SId,sname,ss from student,(
    select SId, AVG(score) as ss from sc  
    GROUP BY SId 
    HAVING AVG(score)> 60
    )r
where student.sid = r.sid;

7 查询在 SC 表存在成绩的学生信息

select DISTINCT student.*
from student,sc
where student.SId=sc.SId

8 查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和联合查询不会显示没选课的学生

select student.sid, student.sname,r.coursenumber,r.scoresum
from student,
(select sc.sid, sum(sc.score) as scoresum, count(sc.cid) as coursenumber from sc 
group by sc.sid)r
where student.sid = r.sid;

9 查询有成绩的学生

select * from student 
where exists (select sc.sid from sc where student.sid = sc.sid);
select * from student
where student.sid in (select sc.sid from sc);

10 查询[李]姓老师的数量

select count(*)
from teacher
where tname like '李%';

11 查询学过张三老师授课同学的信息

select student.* from student,teacher,course,sc
where 
    student.sid = sc.sid 
    and course.cid=sc.cid 
    and course.tid = teacher.tid 
    and tname = '张三';

12 查询没有学过张三老师一门课的学生

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 = "张三"
            )
        )
    );

13 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

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;

14 查询下个月过生日的学生

select *
from student 
where MONTH(student.Sage)=MONTH(CURDATE())+1;

15 查询下周过生日的学生

select *
from student 
where WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE())+1;

16 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

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;

17 查询每门成绩最好的前两名

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;

 

 

3. 查询在 SC 表存在成绩的学生信息