![v2-b1651f0255031bca0e81facab129d13a_1440w.jpg?source=172ae18b](http://img-03.proxy.5ce.com/view/image?&type=2&guid=ea2d31d4-172f-eb11-8da9-e4434bdf6706&url=https://pic2.zhimg.com/v2-b1651f0255031bca0e81facab129d13a_1440w.jpg?source=172ae18b)
1.关注sql运行顺序:1.先运行子查询
2.每个查询语句里运行顺序
a:先运行 from(可能也有联结)/where/group by/having
b:select语句
c:order by/limit
2.(自己熟悉的:)
逻辑/算术/比较运算符:
not/in/and/or/between
模糊查询:
like/%(任意字符)/_(单个字符)
汇总函数:
distinct/count/sum/avg/min/max
算术函数:
round/mod/abs(绝对值)
字符串函数:
length(字符串长度)/lower(大写转换成小写)/upper(小写转换成大写)/replace(字符串替换)/substring(字符串截取)/concat(将多个字符串连接成一个字符串)
eg:replace('知乎真好','真好','不错')——知乎不错
substring('abcde',2,3)——bcd
日期函数:
currentdate/currenttime/current_timestamp(当前日期和时间)/year/month/day/dayname(日期对应星期几)
分组:
group by
对分组制定条件:
having
排序:
oreder by (desc/asc)
limit 从查询结果中取出指定数量的结果
3.视图:
视图中存放的是sql语句,使用视图时,会运行视图里的sql查询语句创建出一张临时表。
原表更新的时候,视图会自动更新。
创建视图语句:
create view 视图名称(<视图列名1>,<视图列名2>,...)
as
<select 查询语句>;
![v2-7b7cb43a955c4fd97276d86b803326e1_b.jpg](http://img-02.proxy.5ce.com/view/image?&type=2&guid=ea2d31d4-172f-eb11-8da9-e4434bdf6706&url=https://pic2.zhimg.com/v2-7b7cb43a955c4fd97276d86b803326e1_b.jpg)
![v2-e160c2d04c45cf2bdb32f59aa7d6a8f7_b.jpg](http://img-02.proxy.5ce.com/view/image?&type=2&guid=ea2d31d4-172f-eb11-8da9-e4434bdf6706&url=https://pic4.zhimg.com/v2-e160c2d04c45cf2bdb32f59aa7d6a8f7_b.jpg)
4.子查询(重点"关联子查询")
http://a.in(子查询);any(子查询);all(子查询)
select name,continent
from world as x
where population >all
(select 3*population
from world as y
where y.continent = x.continent and y.name <> x.name
group by continent);
![v2-bd85d550c623dba5a0c15765dc7fef5f_b.jpg](http://img-01.proxy.5ce.com/view/image?&type=2&guid=ea2d31d4-172f-eb11-8da9-e4434bdf6706&url=https://pic4.zhimg.com/v2-bd85d550c623dba5a0c15765dc7fef5f_b.jpg)
b.查询每个课程成绩最优学生的信息:
select *
from score as s1
where 成绩 =
(select max(成绩)
from score as s2
where s2.课程号 = s1.课程号);
![v2-42108ba3b87f4173b0a1d4bbf5c2a370_b.jpg](http://img-01.proxy.5ce.com/view/image?&type=2&guid=ea2d31d4-172f-eb11-8da9-e4434bdf6706&url=https://pic1.zhimg.com/v2-42108ba3b87f4173b0a1d4bbf5c2a370_b.jpg)
4.case表达式
case when <判断表达式> then <表达式>
when <判断表达式> then <表达式>
when <判断表达式> then <表达式>
...
else <表达式>
end as 列名
eg:
select s1.课程号,sum(case when 成绩 between 85 and 100 then 1
else 0 end) as '优秀',
sum(case when 成绩>=70 and 成绩<85 then 1
else 0 end) as '良好',
sum(case when 成绩 >=60 and 成绩<70 then 1
else 0 end ) as '及格',
sum(case when 成绩 <60 then 1
else 0 end) as '不及格',
s2.课程名称
from score as s1 left join course as s2
on s1.课程号 = s2.课程号
group by s1.课程号,s2.课程名称
![v2-88030d7238ae52347b4bbc8383a1ca50_b.jpg](http://img-02.proxy.5ce.com/view/image?&type=2&guid=ea2d31d4-172f-eb11-8da9-e4434bdf6706&url=https://pic1.zhimg.com/v2-88030d7238ae52347b4bbc8383a1ca50_b.jpg)
5.多表查询
a.表的加法
union/union all
b.联结
cross join/inner join/left join/right join/full join(不用于mysql)