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