case、if、开窗函数查询语句
1、练习 case。。。 when 。。 then 。。 else 。。。 end as 。。。
SELECT
CASE
WHEN degree BETWEEN 0 AND 59 THEN
'及格'
WHEN degree BETWEEN 60 and 80 THEN
'良好'
ELSE
'优秀'
END as '等级'
FROM Score;
2. 练习 -- if( , , )
select sno,cno,degree,if(degree BETWEEN 0 and 75, '不及格','及格') from Score;
3. 练习 --开窗函数
SELECT sno,cno,degree, RANK()over(PARTITION by sno order by degree) FROM Score;
SELECT sno,cno,degree, ROW_NUMBER() over() AS '行号' from Score;
SELECT sno,cno,degree,sum(degree) over(PARTITION by cno order by degree) from Score;
4. 模糊查询,通配符匹配
SELECT * FROM Score WHERE cno LIKE 'a_%' ESCAPE 'a';
SELECT * FROM Score WHERE cno LIKE '\_%' ;
4. 练习group_concat
SELECT cno,GROUP_CONCAT(cname),count(cname) from t1 GROUP BY cno;
5. 通配符按正常字符匹配
SELECT * from t1 where cno LIKE '3\_%';
SELECT * from t1 where cno LIKE '3$_%' ESCAPE '$';
6. 练习:正则匹配
select * from t1 WHERE cname REGEXP '^计' or cname REGEXP '学$';
7. 练习:union(去重)、union all(不去重),实现全连接(full join,mysql不支持full join语法)
select * from Score left join Student on Score.sno = Student.sno
UNION
select * from Score RIGHT join Student on Score.sno = Student.sno;
8. 练习:exits
# 查询3_245班级的学生
select * from Student s1 where EXISTS (select sno from Score s2 where cno = '3_245' and s1.sno=s2.sno);
EXISTS子查询返回的是一个布尔值true或false