一 视图
1 是什么
数据库存放表,表中存放实际数据。视图中存放的则是SQL查询语句,使用视图是会运行视图中SQL语句创建出临时表
2 如何用
创建视图
create view <view_name>(<view_col1>,<view_col2>,....)
as
<select 查询语句>; # 视图中的列名和select查询语句中列名一一对应
例子
create view 按性别汇总(性别,人数) as select 性别,count(*) from student group by 性别;
在编辑器Navicat中创建视图
首先运行写好的的视图语句,再刷新视图,则可以看到新建的视图
![59e013885cd9cdad82b3850ff9f68208.png](https://img-blog.csdnimg.cn/img_convert/59e013885cd9cdad82b3850ff9f68208.png)
如何使用视图
select 性别,人数
from 按性别汇总; #在from字句中使用视图名称代替表名称;视图相当于一张临时表,随时可以取用。
如何删除视图
在Navicat编辑器中直接右键视图,选择删除视图。或运行SQL语句
DROP VIEW [IF EXISTS]
<View_name1>,<view_name2>;
有什么用(应用场景)
有些SQL查询语句需要频繁使用的时候,则可以创建视图。有些查询语句比较复杂又需要用到多次时,视图可以提高效率。
视图的数据会随着原表数据的变化而变化;因为视图中存放的是查询语句而不是表格,每次使用运行的是查询数据;
视图不需要保存数据,所以可以节省数据存放空间
注意事项
避免在视图的数据上再去创建视图,多重视图会降低SQL的性能和效率;
不能往视图中插入数据,会报错;
二 子查询
1 是什么
子查询就是在from子句中直接写定义视图的SQL查询语句;相当于一次性的视图;真正的临时表,不会保存在数据库的硬盘当中,运行结束后就消失了;
2 如何用
select 性别,人数
from (
select 性别,count(*) from student group by 性别
)as 按性别汇总; #as 后跟的临时表名称
运行顺序
先运行子查询,再运行外部查询语句
子查询不仅放到from 后面还可以放到in,all, any 后面
select ..... from (子查询) as temp;
select col1 from tb1 where col1 > all ( 子查询);
select col1 from tb1 where col1 in (子查询);
select col1 from tb1 where col1 > any (子查询);
in 例子
想要找出每门课程成绩最低的学生
如下图这样写SQL语句会得到错误结果,group by 语句查询出的只有汇总分析函数+group by 列名(按哪一列分组)的结果。因此如果增加别的列名,该列取出的数据是按照列名分组后随机选择了该列第一个数据
![f51b005d2daeeb26f197e812a5a63f3c.png](https://img-blog.csdnimg.cn/img_convert/f51b005d2daeeb26f197e812a5a63f3c.png)
因此要换一个思路,首先找出每门课的最低分,然后找出课程和分数与每门课最低分相对应的学号。
select 课程号,min(成绩) from score group by 课程号;# 1 因为提到每门课所以按照 课程 分组
取出每门课以及对应的最低分
select 课程,学号
from score
where (课程号,成绩) in (
select 课程号,min(成绩) from score group by 课程号);
![05f3b05a39459863fc00f9b8f6c62b93.png](https://img-blog.csdnimg.cn/img_convert/05f3b05a39459863fc00f9b8f6c62b93.png)
any (子查询)例子 与 some(子查询) 相同
select col1 from tb1 where col1 > any (子查询);
哪些学生的成绩比课程0002的全部成绩里的任意一个高呢?
第一步:课程0002的全部成绩
第二步:学生,他的学生成绩> 任意一个课程0002的成绩 > any
select score from score where course="0002"; #第一步
select name, score
from score
where score > any (select score from score where course="0002");
哪些学生的成绩比课程0002的全部成绩里的都高呢?
第一步:课程0002的全部成绩
第二步:学生,他的学生成绩> 课程0002的全部成绩 >all
select score from score where course="0002"; #第一步
select name, score
from score
where score > all(select score from score where course="0002");
有什么用(应用场景)
子查询与视图不同,它是偶尔使用的SQL语句;
注意事项
子查询语句的结果不可以运算,因为它的结果是表格(很多值,而不是一个值)
子查询语句不可以层层嵌套
select... from (子查询)as temp from子查询必须为子查询临时表命名
![7905a1c2e69a94f4481e64a06c6b94aa.png](https://img-blog.csdnimg.cn/img_convert/7905a1c2e69a94f4481e64a06c6b94aa.png)
SQL运行顺序
![ed61d76e4800ed99b074cb686bd050c7.png](https://img-blog.csdnimg.cn/img_convert/ed61d76e4800ed99b074cb686bd050c7.png)
三 标量子查询
是什么
标量子查询返回的只有一个值。因此他的返回值可以和比较运算符一起用
![d739b6a2012000469fbb9e7e6c1799ff.png](https://img-blog.csdnimg.cn/img_convert/d739b6a2012000469fbb9e7e6c1799ff.png)
select 学号,成绩
from score
where 成绩>(
select avg(成绩)from score
); 括号内的子查询只返回一个值 就是标量子查询
select 学号,成绩
from score
where 成绩 between (
select avg(成绩)from score where 成绩<=60
) and (
select avg(成绩)from score where 成绩>=80 );
#两个子查询均只返回一个值,两个都是标量子查询
如何用
![2b709f0cd0ab134fd6c80a21ca0f0a0f.png](https://img-blog.csdnimg.cn/img_convert/2b709f0cd0ab134fd6c80a21ca0f0a0f.png)
注意事项
![71a06314098b5139dc1bcfb923047109.png](https://img-blog.csdnimg.cn/img_convert/71a06314098b5139dc1bcfb923047109.png)
四 关联子查询
什么是关联子查询?如何使用?
超找出每个课程中 大于 对应课程平均成绩的学生
# 第一步:每门课的平均成绩
select avg(成绩) from score group by 课程号;
#第二步 学生的成绩> 平均成绩
select 学号,课程号,成绩
from score as s1
where 成绩 > ( select avg(成绩)
from score as s2
where s1.课程号=s2.课程号
group by 课程号 );
#关联条件只可以放在子查询里 因为s2 只在子查询里
有什么用?
在每个组里进行比较的时候
五 如何用SQL解决业务问题
翻译成白话--->学出分析思路-->写出对应的SQL字句
如何看懂报错信息
逐一排查,先取出子查询运行看是否报错,如果没有则是子查询外的语句出错。
六 各种函数
汇总函数
![854649e4660119a4dc601e50a9e727c3.png](https://img-blog.csdnimg.cn/img_convert/854649e4660119a4dc601e50a9e727c3.png)
算术函数
round(123.56,1)=123.6 (近似值)
round(123.55,-1)=120
abs(-100)=100 #绝对值
mod(5,2)=1 #求余数
![7315b73793ef5614951631111e2e8b7e.png](https://img-blog.csdnimg.cn/img_convert/7315b73793ef5614951631111e2e8b7e.png)
字符串函数
字符串拼接 concat(字符串1,字符串2) concat('调音师',‘:真好看’) =调音师:真好看
字符串替换 replace(字符串,被替换的字符串,用什么来替换)
replace('调音师: 真好看',‘:真好看’,‘真棒’) =调音师真棒
字符串截取 substring(字符串,截取起始位置,截取长度)
![4bd10b161e166a95a7a7784a28c80b54.png](https://img-blog.csdnimg.cn/img_convert/4bd10b161e166a95a7a7784a28c80b54.png)
日期函数,更多https://www.jianshu.com/p/3c52a4c25a2f
mysql 中 datediff(expr1,expr2) =expr1-expr2
sql server 中 DATEDIFF(interval,date1,date2)=date2-date1 interval 可以使year,month,day,minute,second
![34eacbae35968d61fe4d105c88f39c3c.png](https://img-blog.csdnimg.cn/img_convert/34eacbae35968d61fe4d105c88f39c3c.png)
SQLZOO 练习 select in select
![7f2710524898ce0d2927abf17eab9c8e.png](https://img-blog.csdnimg.cn/img_convert/7f2710524898ce0d2927abf17eab9c8e.png)
![fe644e524c4a95cccfd8122a0a60d5f4.png](https://img-blog.csdnimg.cn/img_convert/fe644e524c4a95cccfd8122a0a60d5f4.png)
![94d8f0106f7e4e8ef13098bbcd86efae.png](https://img-blog.csdnimg.cn/img_convert/94d8f0106f7e4e8ef13098bbcd86efae.png)
![907d72707e5114d3b71d72c7e345a87b.png](https://img-blog.csdnimg.cn/img_convert/907d72707e5114d3b71d72c7e345a87b.png)
![d5e2d51645e7259930613ed711426dd0.png](https://img-blog.csdnimg.cn/img_convert/d5e2d51645e7259930613ed711426dd0.png)
与同组的所有值进行对比时,需要排除自身 x.name<>y.name
![b36f0e7705f614c7696b9df979383dba.png](https://img-blog.csdnimg.cn/img_convert/b36f0e7705f614c7696b9df979383dba.png)