mysql 的奇妙历险

阅读目录

• not in 的使用
• case when 的应用：
• 如何设置变量呢？

mysql 的奇妙历险

这几天在练习sql的时候，碰到下面几个题， 如下

他的表字段是这些

create table Student(
SId varchar(10), # 学生id
Sname varchar(10), # 学生姓名
Sage datetime, # 学生出生日期
Ssex varchar(10) # 学生性别
);

create table Course(
CId varchar(10), # 课程id
Cname nvarchar(10), # 课程名
TId varchar(10) # 任课教师id
);

create table Teacher(
TId varchar(10), # 任课教师id
Tname varchar(10) # 教师姓名
);

create table SC(
SId varchar(10), # 学生id
CId varchar(10), # 课程id
score decimal(18,1) # 分数
);

回到顶部

not in 的使用

之前呢总想着 用子查询 把所满足条件的sid查出来，在通过 in 判断sid是否在满足条件的sid里

思想没有转变，现在有了这样一种思想，用 not in ，来解决不太好用子查询来查询满足条件的查询。 就是通过判断sid他不在那些不满足条件的sid中

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

select s.sid,s.sname,avg(sc.score) from student s join sc on s.sid not in (select distinct sid from sc where sid not in (select sid from sc where score<60 group by sid having count(cid)>=2)) and s.sid=sc.sid group by sid

select s.sid,s.sname,avg(sc.score) from Student s join SC on s.sid=sc.sid join (select sid,count(score) c from sc where score<60 group by sid having c>=2) a on s.sid = a.sid group by s.sid

回到顶部

case when 的应用：

case when 条件 then 返回值 end

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

select * from (select sc.cid as x,c.cname,max(sc.score) zg,min(sc.score) zd,avg(sc.score) pj from sc join Course c on sc.cid=c.cid group by sc.cid) aa join 
(select cid as x,concat(count(CASE WHEN 80>score and score>=70 then '中等' end)/count(cid)*100,'%') zd,
concat(count(CASE WHEN 90>score and score>=80 then '优良' end)/count(cid)*100,'%') yl,
concat(count(CASE WHEN 100>score and score>=90 then '优秀' end)/count(cid)*100,'%') yx,
concat(count(case when score>=60 then '及格' end)/count(cid)*100,'%') jg from sc group by sc.cid) bb on aa.x=bb.x	

//查询成绩的区间
(select cid,concat(count(CASE WHEN 80>score and score>=70 then '中等' end)/count(cid)*100,'%') zd,
concat(count(CASE WHEN 90>score and score>=80 then '优良' end)/count(cid)*100,'%') yl,
concat(count(CASE WHEN 100>score and score>=90 then '优秀' end)/count(cid)*100,'%') yx,
concat(count(case when score>=60 then '及格' end)/count(cid)*100,'%') jg  from sc group by cid)

想用百分比来显示小数 可以用 concat((8/5)*100,'%')

如何实现给查询的数据进行排名？ 可以通过定义变量 例如：按各科成绩进行排序，并显示排名， Score 重复时合并名次

select sid,cid,score,@rank:=@rank+1 as rank from sc join (select @rank:=0) q order by score desc

就是通过设置@rank：变量

回到顶部

如何设置变量呢？

定义变量：
1. set @变量名 = 值
2. select @变量名 := 值
假设：我想新增一个变量
set @strTest1 = '长沙';
select @strTest1 := '长沙';
select @strTest1;

mysql中的mid()函数

MID() 函数用于得到一个字符串的一部分。
MID() 函数语法为：
SELECT MID(ColumnName, Start [, Length]) FROM TableName
这里的下标是从1开始的。

判断生日是否在近期 这里要使用到mid()函数

查询本周过生日的学生
SELECT * FROM student
WHERE WEEK(CONCAT(YEAR(CURDATE()),"-" ,MID(sage,6,5))) = WEEK(NOW())
# 1、选取原生日里的月日成分(按照字符串进行处理)
# >>> MID(sage,6,5)
# 2、将选取的月日成分与今年的年份相接
# >>> CONCAT(YEAR(CURDATE()),"-" ,MID(sage,6,5))
# 3、使拼接后的日期的周数和 now/curdate 的周数相等即可确定本周过生日
查询下周过生日的学生
SELECT * FROM student AS s
WHERE WEEK(CONCAT(YEAR(CURDATE()),"-",MID(s.sage,6,5))) = WEEK(CURDATE())+1
# 原理同41
查询本月/下月过生日的学生
SELECT * FROM student AS s
WHERE MONTH(CONCAT(YEAR(CURDATE()),"-",MID(s.sage,6,5))) = MONTH(CURDATE())
# 改用 month函数，查询下月过生日则+1
CHAP 3.

查询每门功成绩最好的前两名
这里在使用union将两个结果加起来的时候，报错Incorrect usage of UNION and ORDER BY，因为union只能存在一个order by，所以可以使用括号括起来
(select sid,cid,score from sc where cid=01 order by score  desc limit 2)
union
(select sid,cid,score from sc where cid=02 order by score  desc limit 2)
union
(select sid,cid,score from sc where cid=03 order by score  desc limit 2)