1.视图
- 什么是视图:
- 视图存放的是sql查询语句(表中存放的是实际数据),使用视图时,会运行视图里的sql查询语句创建出一张临时表
- 如何创建视图:
create view 视图名(<视图列名1>,<视图列名2>,...)
as
<select 查询语句>;
示例:
create view 按性别汇总(性别,人数)
as
select 性别,count(*)
from student
group by 性别;
注意视图列名和查询语句中列名一一对应!
- 如何使用视图
![881e097ae9bce1d954b9a0130e8e7fd6.png](https://i-blog.csdnimg.cn/blog_migrate/25dee65faccb2b5979146276d39b124f.jpeg)
- 注意事项
- 避免在视图基础上再创建视图
- 不能往视图内插入数据
2.子查询
- 什么是子查询
- 一次性的视图,在一个select语句的from子句中插入另一个select语句
- sql会先运行子查询
- 如何使用子查询
- in
![fe190b91cfa681840d527e308fd451a4.png](https://i-blog.csdnimg.cn/blog_migrate/51b65d057442a367cbd44fa5b69a9507.jpeg)
![9cd8aa40181a446295eb80fbd974eefa.png](https://i-blog.csdnimg.cn/blog_migrate/c074958097d3fade98c43ecb83b7d649.jpeg)
![1af91d7c5312a59291d389e101556f0a.png](https://i-blog.csdnimg.cn/blog_migrate/c602ae1728dac17c9b905a7952870fe4.jpeg)
![f736a9bb787591229698908cf004aed0.png](https://i-blog.csdnimg.cn/blog_migrate/6b7f8e50b0135d5cfe202719ccadf2b5.jpeg)
any和all需要和比较运算符一起使用(=,<>,>=等)
-
- any
- any(子查询)与some(子查询相同)
- any
select 列名1
from 表名1
where列名1> any(子查询); --大于任意一个就符合条件
-
- 示例:哪些学生成绩比课程0002的全部成绩里的任意一个高呢?
select 学号,成绩
from score
where 成绩>any(
select 成绩
from score
where 课程号='0002'
);
-
- all
select 列名1
from 表名1
where列名1> all(子查询); --大于全部才符合条件
- 注意事项
- 不能写成
a>3 * all(b)
-
- 因为all产出的是一个集合,有n行数据,所以只能写成
a/3 > all(b)
- 避免使用多层嵌套子查询(会难看懂不好维护)
- 子查询的as关键词和子查询名称都是可以省略的
3.标量子查询
![7545ca71b189d5848b0605521172b053.png](https://i-blog.csdnimg.cn/blog_migrate/2c54f4cec82e1a8a28df00f1128074f9.jpeg)
select 学号,成绩
from score
where 成绩>(
select avg(成绩)
from score
);
- 标量子查询返回的是一个单一数值,只一行一列,所以它可以和比较运算符(=,>等)一起使用
- 另外,标量子查询也可以在select子句中使用:
select 学号,成绩,(select avg(成绩)from score) as 平均成绩
from score;
语句表示,一整列的平均成绩值都是一个数
- 注意事项:
- 标量子查询只能返回一个值,否则会报错
4.关联子查询
select 学号,课程号,成绩
from score as s1
where 成绩>(select avg(成绩)
from score as s2
where s1.课程号=s2.课程号 --关联条件
group by 课程号
);
注意,在该例子中,s2只在子查询中生效
- 在每个组里进行比较时使用关联子查询
![998826512d27d1edc7e93728cbc7f5b8.png](https://i-blog.csdnimg.cn/blog_migrate/91de779a30ec1e46edfbd935b578d8e3.jpeg)
5.如何用SQL查询业务问题
- 翻译成大白话
- 写出分析思路
- 写出对应sql子句
检查错误时,可以分别排查子查询
6.各种函数:汇总函数,算数函数,字符串函数,日期函数
- 汇总函数
- 之前提到过的:count,sum,avg,max,min
- 算数函数
- round(数值,保留小数的位数): 用于对数据四舍五入
- round(1234.567,1) >>> 1234.6
- round(1234.567,-1) >>> 1230
- round(数值,保留小数的位数): 用于对数据四舍五入
-
- abs(数值):绝对值
- abs(-100) >>> 100
- mod(被除数,除数):求余数
- mod(5,2) >>> 1
- abs(数值):绝对值
- 字符串函数(注意字符串都要加单引号)
- length(字符串): 字符串长度
- length('abcde') >>> 5
- lower(字符串):大写转换成小写
- upper(字符串):小写转换成大写
- concat(字符串1,字符串2):字符串拼接
- replace(字符串,被替换的字符串,替换进去的字符串):字符串替换
- replace(‘调音师真好看’,‘真好看’,‘真棒’)>>> 调音师真棒
- substring(字符串,截取的起始位置,截取长度):字符串截取
- substring(‘abcde’,2,3)>>>bcd
- length(字符串): 字符串长度
- 日期函数
- current_date:当前日期
- 输入current_date >>>2020-09-17(写文章此时日期)
- current_time:当前时间
- current_timestamp:当前日期和时间
- year(日期), month(日期), day(日期):获取日期的年份(月份,日期)
- year(‘2020-09-17’)>>>2020
- dayname(日期):日期对应星期几
- dayname(‘2020-09-17’)>>>星期四
- datediff(日期1,日期2):计算两个日期的天数差, 如果日期1比日期2大,结果为正;如果日期1比日期2小,结果为负。
- current_date:当前日期
SQLZOO题目记录:
![e98cba5fe2ba3309b9d63c1c42cf5baa.png](https://i-blog.csdnimg.cn/blog_migrate/4a5ca867df263cc08f4f4f4911323a49.jpeg)
![e8be47835afbdf70a8dbf2d16f968d3f.png](https://i-blog.csdnimg.cn/blog_migrate/52baae106b6027861b587bb41b8a9098.jpeg)
![c7e8ed159044f532ceaa72fb1a970bf8.png](https://i-blog.csdnimg.cn/blog_migrate/7035a728c027762c6792ddd5064278fa.jpeg)
![de29eed2a1fdc75c3de9810f1ed16633.png](https://i-blog.csdnimg.cn/blog_migrate/ae394b952fadcd8b9b100c919cedf1e2.jpeg)
非常值得复习的两道题!第6题注意gdp有些值是null,所以需要加限制条件gdp>0
第七题注意where子句定义的是同大洲的国家
![e66388d308502b5ecc449b016fd48c00.png](https://i-blog.csdnimg.cn/blog_migrate/f83cd8995e5e44de2a5950ce9b6e3e64.jpeg)
![a4e64cc944ce2aa73b86d678af005d82.png](https://i-blog.csdnimg.cn/blog_migrate/bc25a1272300ff2748e908b3876e63b7.jpeg)
第十题注意,遍历数据进行比较时,务必要去掉自己和自己比较,因为自己是不可能大于3倍的自己的!