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

 

posted @ 2022-04-08 09:54  狒狒桑  阅读(128)  评论(0)    收藏  举报