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 '\_%' ;