4.mysql知识兼练习（不好抽象出来总结）

/*
编写SQL语句，查看年龄比'李斯文'小的学生，
要求显示这些学生的信息 ？
*/
select * from student
where borndate>(
select borndate from student where studentname='李斯文'
);
#查询“Java”课程至少一次考试刚好等于60分的学生姓名
#1.表连接
select distinct studentname
from student join result
on student.studentno=result.studentno
join course
on course.subjectno=result.subjectno
where subjectname='java' and studentresult=60;

#2.子查询,先内后外,多条记录用in
select studentname from student
where studentno in
(select studentno from result
where subjectno=
(select subjectno from course where subjectname='java')
and studentresult=60);
/*查询参加最近一次“java”考试成绩最高分和最低分
*/
select * from result
where examdate=
(
    select max(examdate) from result
    where subjectno=
    (select subjectno from course where subjectname='java')
)
and#注意
subjectno=
(select subjectno from course where subjectname='java');
/*
INSERT INTO Student (StudentName,StudentNo,
LoginPwd,GradeId,Sex,BornDate,IdentityCard)
VALUES('武松',10011,'123456',1,'男',
'1990-12-31','11010119901231001x');
*/
/*查询参加“Java”课程最近一次考试的在读学生姓名   
*/
select studentname from student
where studentno in
( select studentno from result
    where examdate=
    (
        select max(examdate) from result
        where subjectno=
        (select subjectno from course where subjectname='java')
    )
    and#注意
    subjectno=
    (select subjectno from course where subjectname='java')
)
;
/*查询未参加“Java”课程最近一次考试的在读学生名单
*/
select studentname from student
where studentno  not in
( select studentno from result
    where examdate=
    (
        select max(examdate) from result
        where subjectno=
        (select subjectno from course where subjectname='java')
    )
    and#注意
    subjectno=
    (select subjectno from course where subjectname='java')
)
and gradeid=
(
select gradeid from course where subjectname='java'
)
;
#查询S1学期开设的课程名称
#1.表连接
select    subjectname from course join grade
on course.gradeid=grade.gradeid
where gradename='s1';
#2.子查询
select    subjectname from course where gradeid=
(select gradeid from grade where gradename='s1');
/*采用美国ABCDE五级打分制显示学生Java最近一次考试成绩 case
A级:   90分以上
B级： 80－89分
C级:   70－79分
D级： 60－69分
E级： 60分以下
输出(学生姓名,等级)  内部连接，哪个都可以
*/

select studentname,#需要,

case
    when studentresult >=90 then 'A'#没有,
    when studentresult between 80 and 89 then 'B'
    when studentresult between 70 and 79 then 'C'
    when studentresult between 60 and 69 then 'D'
    else 'E'
end '等级'#相当于查询的字段

from student join result
on student.studentno=result.studentno
join course
on result.subjectno=course.subjectno
where  #result
result.subjectno=
(select subjectno from course where subjectname='java')

and 
examdate=
(select max(examdate) from result join course
on result.subjectno=course.subjectno
where subjectname='java'#Java课程最近一次考试时间
);

/*
需求说明：
查询学号是10000的学生Java课程最近一次考试成绩，
输出学生姓名和考试等级
如果成绩大于85分，显示“优秀”
如果大于70分，显示“良好”
如果大于等于60分，显示“中等”；否则显示“差”
*/
select studentname,#需要,
#select *,
case
    when studentresult >85 then '优秀'#没有,
    #when studentresult between 71 and 85 then '良好'
    #when studentresult between 60 and 70 then '中等'
    when studentresult >70 then '良好'
    when studentresult >=60 then '中等'
    else '差'
end '等级'#相当于查询的字段

from student join result
on student.studentno=result.studentno
join course
on result.subjectno=course.subjectno
where  #result
result.studentno=10000

and 
examdate=
(select max(examdate) from result join course
on result.subjectno=course.subjectno
where subjectname='java'#Java课程最近一次考试时间
);

#以上所有的子查询都可以"单独执行",又称为"简单子查询"或"标量子查询"
#1.查询所有年级编号为1的学员信息，按学号升序排序,显示前2条记录
select * from student where gradeid=1 order by studentno limit 2;
#2.查询所有的学员信息,每页3条，显示第2页
#size:每页记录数,current:当前的页号(从1开始),
#select ... from ... limit (current-1)*size,size
select * from student order by studentno limit 3,3;
/*查询2011年11月4日考试前3名的学员的姓名,课程名称和分数
*/
select studentname,subjectname,studentresult from  student 
join result
on student.studentno=result.studentno
join course
on course.subjectno=result.subjectno
where examdate='2011-11-04'#2011年11月4日考试
order by studentresult desc                                                                                                                limit 3;
#查询参加过考试的学生姓名
#1.用in子查询
select studentname from student
where studentno in
(select studentno from result);
#2.表连接
select distinct studentname from student
join result
on student.studentno=result.studentno;
#3.用exists子查询(相关子查询),不能单独执行
/* exists(子查询)，如果子查询有返回结果，则(exists(子查询)返回true,否则返回false*/
select studentname from student
where exists(select * from result
where studentno=student.studentno
);
#查询没有参加过考试的学生姓名
#1.用in子查询
select studentname from student
where studentno not in
(select studentno from result);
#2.表连接(左外连接)
select distinct studentname from student
left join result
on student.studentno=result.studentno
where result.studentno is null;
#3.用exists子查询(相关子查询)
select studentname from student
where not exists(select * from result
where studentno=student.studentno
);

练习

/*
为每个学生制作在校期间每门课程的成绩单，
要求以每个学生参加每门课程的最后一次考试的
成绩作为该生本课程的最终成绩,
成绩单的输出数据项:
学生姓名
课程所属的年级名称
课程名称
考试日期
考试成绩

1.注意:中文排序:order by convert(studentname using gbk)
*/

/*  以下代码未达到要求
select studentname,gradename,subjectname,examdate,studentresult
from grade
join student 
on student.gradeid=grade.gradeid
join result
on student.studentno=result.studentno
join course
on course.subjectno=result.subjectno

where exists(
select studentno,subjectno,max(examdate) from result
group by studentno,subjectno
)
order by convert(studentname using gbk),gradename,subjectname

*/

#####自连接，相关子查询（必须取别名）
select * from result r
where examdate=
(
select max(examdate) from result
where studentno=r.studentno and subjectno=r.subjectno
);

#####老师答案
select studentname,gradename,subjectname,examdate,studentresult
from result r
join student s
on r.studentno=s.studentno
join course c
on r.subjectno=c.subjectno
join grade g
on c.gradeid=g.gradeid
where examdate=
(
select max(examdate) from result
where studentno=r.studentno and subjectno=r.subjectno
)
order by convert(studentname using gbk),gradename,subjectname
;
#我的----有问题
/*
select studentname,gradename,subjectname,examdate,studentresult
from grade
join student 
on student.gradeid=grade.gradeid
join result r
on student.studentno=r.studentno
join course
on course.subjectno=r.subjectno
where examdate=
(
select max(examdate) from result
where studentno=r.studentno and subjectno=r.subjectno
)
order by convert(studentname using gbk),gradename,subjectname
;

*/

#2.子查询作为一个表,必须取别名

select studentname,gradename,subjectname,examdate,studentresult
from result r
join
(
select studentno,subjectno,max(examdate) maxdate
from result group by studentno,subjectno
) temp1#临时表，不创建的那种
on r.studentno=temp1.studentno
and r.subjectno=temp1.subjectno
and r.examdate=temp1.maxdate
join student s
on r.studentno=s.studentno
join course c
on r.subjectno=c.subjectno
join grade g
on c.gradeid=g.gradeid
order by convert(studentname using gbk),gradename,subjectname
;

#3.union联合查询

#方法1
select studentname,sex,borndate from student
where studentname like '张%' or studentname like '李%'
order by studentno 
#order by borndate
;

#方法2
select studentname,sex,borndate from student
where studentname like '李%'
union
select studentname,sex,borndate from student
where studentname like '张%';

#select studentname,sex,borndate from student#Unknown column 'studentno' in 'order clause'
select studentno,studentname,sex,borndate from student
where studentname like '张%'
union
select studentno,studentname,sex,borndate from student
where studentname like '李%'
order by studentno ;