Java学习进程——MySQL基础学习

MySQL多表查询：

select name,password,ppd from where a.id=b.id;

等价于

select name,password,ppd from a join b on a.id=b.id;

尽量使用联结代替嵌套（效率低）：

select id,name from a where sid=(select sid from a where id=1);

可以用自联结代替为：

select p1.id,p2.name from a as p1 join a as p2  on p1.sid=p2.sid where id =2;

UNION查询适用于where条件过多，或者多表查询时使用；（select UNION select）

外部查询:

left join       right join

 

MySQL进阶习题：

查询“01”课表比“02”课表成绩高的学生的信息及课程分数；

select stu.sid,stu.sname,s.score  from  student as stu join (select s1.sid,s1.score from (select sid,score from sc where cid='01') as s1 join (select sid,score from sc where cid='02') as s2 on s1.sid=s2.sid where s1.score > s2.score) as s on stu.sid = s.sid;

 

查询“01”和“02”课程同时存在的课程信息：

select s1.* from (select sid ,score from sc where cid='01') as s1 join (select sid,score from sc where cid='02') as s2 on s1.sid=s2.sid;

 

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

select sc.sid,sname,round(avg(score),2) as avg_score  from sc,student where sc.sid=student.sid group by sc.sid,sname having avg_score>=60;

round(avg(score),2)  : 控制小数为两位

 

查询在成绩表（sc）存在成绩的学生信息

select distinct stu.* from student as stu join sc on sc.sid=stu.sid;

 

查询所有同学的学生编号、姓名、选课总数、所有课程的总成绩；

select stu.sid,stu.sname,count(sc.cid) as num,sum(sc.score) as total_score from student as stu left join sc on stu.sid=sc.sid group by stu.sid,stu.sname;

 

查询和“01”号的同学学习的课程完全相同的其他同学的信息

select s2.sid,student.sname from sc as s1 join as s2 on s1.cid=s2.cid and s1.sid='01' and s2.sid !='01' join student on s2.sid=student.sid group by s2.sid = student.sname having count(s2,cid) = (select count(*) from sc where sid='01');

 

查询两门及其以上不及格课程的同学的学号，姓名和平均成绩；

select stu.sid,stu.sname,round(avg(sc.score),2) as avg from student as stu join sc on stu.sid=sc.sid where sc.score <60 group by stu.sid,stu.sname having count(sc.cid)<=2;

 

检索“01”课程分数小于60，按照分数降序排列的学生信息；

select sc.sid, stu.sname, sc.score from sc join student as stu on sc.sid=stu.sid where sc.cid='01' and sc.score <60 order by sc.score desc;

 

按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select sc.*,s2.avg_score
from sc
join (select sid,avg(score) as avg_score from sc group by sid) as s2
on sc.sid = s2.sid
order by s2.avg_score desc,sc.sid;

+------+------+-------+-----------+
| SId  | CId  | score | avg_score |
+------+------+-------+-----------+
| 07   | 03   |  98.0 |  93.50000 |
| 07   | 02   |  89.0 |  93.50000 |
| 01   | 01   |  80.0 |  89.66667 |
| 01   | 02   |  90.0 |  89.66667 |
| 01   | 03   |  99.0 |  89.66667 |

可视程度更高：

select
stu.sname,
a.score as '语文',
b.score as '数学',
c.score as '英语',
avg(d.score) as '平均成绩'
from student as stu
left join sc as a on stu.sid = a.sid and a.cid = '01'
left join sc as b on stu.sid = b.sid and b.cid = '02'
left join sc as c on stu.sid = c.sid and c.cid = '03'
left join sc as d on stu.sid = d.sid
group by stu.sname,语文,数学,英语
order by 平均成绩 desc;

+--------+--------+--------+--------+--------------+
| sname  | 语文   | 数学    | 英语    |  平均成绩     |
+--------+--------+--------+--------+--------------+
| 郑竹   |   NULL |   89.0 |   98.0 |     93.50000 |
| 赵雷   |   80.0 |   90.0 |   99.0 |     89.66667 |
| 周梅   |   76.0 |   87.0 |   NULL |     81.50000 |
| 孙风   |   80.0 |   80.0 |   80.0 |     80.00000 |
| 钱电   |   70.0 |   60.0 |   80.0 |     70.00000 |
| 李云   |   50.0 |   30.0 |   20.0 |     33.33333 |
| 吴兰   |   31.0 |   NULL |   34.0 |     32.50000 |

 

CASE WHEN用法：

17.查询各科成绩最高分、最低分和平均分：
以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

CASE WHEN sc.score >= 60 THEN 1 ELSE 0 END
相当于编程中 if
if  sc.score >= 60:
    return 1
else:
    return 0

select
sc.cid,
c.cname,
max(sc.score) as '最高分',
min(sc.score) as '最低分',
round(avg(sc.score),2) as '平均分',
count(sc.cid) as '选修人数',
sum(CASE WHEN sc.score >= 60 THEN 1 ELSE 0 END) / count(sc.cid) as '及格率',            //会自动根据条件筛选出合适的数据
sum(CASE WHEN sc.score >= 70 and sc.score < 80 THEN 1 ELSE 0 END) / count(sc.cid) as '中等率',
sum(CASE WHEN sc.score >= 80 and sc.score < 90 THEN 1 ELSE 0 END) / count(sc.cid) as '优良率',
sum(CASE WHEN sc.score >= 90 THEN 1 ELSE 0 END) / count(sc.cid) as '优秀率'
from sc join course as c on sc.cid = c.cid
group by sc.cid,c.cname
order by '选修人数' desc,sc.cid;

+------+--------+-----------+-----------+-----------+--------------+-----------+-----------+-----------+-----------+
| cid  | cname  | 最高分  | 最低分   | 平均分   | 选修人数  | 及格率   | 中等率     | 优良率    | 优秀率    |
+------+--------+-----------+-----------+-----------+--------------+-----------+-----------+-----------+-----------+
| 01   | 语文   |      80.0 |      31.0 |     64.50 |            6 |    0.6667 |    0.3333 |    0.3333 |    0.0000 |
| 02   | 数学   |      90.0 |      30.0 |     72.67 |            6 |    0.8333 |    0.0000 |    0.5000 |    0.1667 |
| 03   | 英语   |      99.0 |      20.0 |     68.50 |            6 |    0.6667 |    0.0000 |    0.3333 |    0.3333 |
+------+--------+-----------+-----------+-----------+--------------+-----------+-----------+-----------+-----------+