sql 余数函数_【连载4】-SQL-复杂查询

0、整体行文大纲如下

1.视图
2.子查询
3.标量子查询
4.关联子查询
5.如何用SQL解决业务问题
6.各种函数

• select 查询结果 -- 第五步
• from 查询表名 -- 第一步
• where 查询指定条件 -- 第二步
• group by 分组 -- 第三步
• having 对分组结果指定条件 -- 第四步
• order by 查询结果排序 -- 第六步
• limit 从结果中取出指定行数; -- 第七步

一、视图

数据表中存放的是数据，视图中存放的是查询语句。

使用视图时，会运行视图里面的sql查询语句，创建出一张临时表。当客户端与数据库的连接断开后临时表会被自动删除。

• 注意事项：避免在视图的基础上再创建视图，这样会降低SQL的执行效率。不能向视图里面插入数据，不然会报错（因为视图本身没有数据，只是SQL语句）

CREATE VIEW 按性别汇总(性别,人数) 
AS  
SELECT 性别, COUNT(*) 
FROM student  
GROUP BY 性别; 

• 创建好的视图会出现在红框这里

• 在下次需要使用按性别分组的情况，直接把这个视图当做数据表使用即可（上张图片的第7、8行）即：

SELECT 性别,人数
FROM 按性别汇总;

• 视图的优点：

1. 视图中的结果是根据原数据库的数据变化而变化的。
2. 对于一些需要频繁使用的SQL语句，可以保存为视图，从而减少反复输入SQL语句的工作量；同时如果遇到一些比较复杂的SQL语句，使用视图也会提高效率。
3. 视图无需保存数据，从而节省存储空间

二、子查询

(偶尔使用SQL，视图经常使用)

在子句中嵌套SQL查询语句：先运行子查询，再运行外部语句。可以在from、where字句中嵌套SQL查询语句。

select 性别,人数
from(
select 性别,count(*) as 人数
from student
group by 性别
) as count_as_sex;

子查询注意事项：

• 1）两个数字比较倍数应该使用：a/3>all(b) 而不能使用：a>3*all(b)
• 2）避免使用多层嵌套子查询：很难维护、执行效率很差。
• 3）子查询里面的as 子查询名称是可以省略的。（最好不要省略）

3.标量子查询

（子查询返回的必须是1行1列的单一数据，可以用在where子句中）

大于平均成绩学生的学号和成绩(注意，不区分课程号)

where子句中不能使用汇总函数：where 成绩>avg(成绩)

但是可以使用标量子查询：

select 学号,课程号,成绩
from score
where 成绩>
(select avg(成绩) from score);

扩展：大于平均成绩学生的学号和成绩(注意，要区分课程号)

select t1.学号,t1.课程号,t1.成绩
from score as t1, (select 课程号,avg(成绩) as 平均成绩
from score
group by 课程号) 
as t2
where t1.成绩>t2.平均成绩 and t1.课程号=t2.课程号;

• 标量子查询可以使用在任何使用单一数值的位置：

select 学号,课程号,成绩,(select avg(成绩)from score)as 总平均成绩
from score;

扩展：按照课程号计算出平均成绩

from score as t1, (select 课程号,avg(成绩)as 平均成绩 from score group by 课程号)as t2

where t1.课程号=t2.课程号

order by 课程号 asc, 学号 asc;

4.关联子查询

在每个组里面进行比较的时候就可以使用关联子查询。

• 案例：大于平均成绩学生的学号和成绩(注意，要区分课程号)

select 学号,课程号,成绩
from score as s1
where 成绩>
(select avg(成绩)
 from score as s2 
where s1.课程号=s2.课程号 
group by 课程号);

5.如何使用SQL解决业务问题

理解问题（翻译成大白话），写出分析思路，写出对应子句。

• 哪些学生的成绩比课程0002的全部成绩里的 其中任意一个高？

select 学号,课程号,成绩
from score
where 成绩>any(select 成绩 from score where 课程号='0002');

6.各种函数

汇总函数：

• count()求某列的行数，Null不计算在内。count(*)求所有的行数，Null值会被计算在内。
• sum()对某列数值求和，只能计算数值类型。
• avg()对某列数值求平均，只能计算数值类型。
• max()求某列的最大值。
• min()求某列的最小值。

算数函数：

• 四舍五入 round(数值,保留的小数位数) round(123.32,1) 结果：123.3 round(123.32,-1) 结果：120
• 绝对值 abs(数值) abs(-100) 结果：100
• 求余数 mod(被除数,除数) mod(7,2) 结果：1

字符串函数：

• 字符串长度 length(字符串) length('abcd') 结果：4
• 大写转小写 lower(字符串) lower('ABC') 结果：abc
• 小写转大写 upper(字符串) upper('abc') 结果：ABC
• 字符串拼接 concat(字符串1，字符串2) concat('我是','恐怖大魔王') 结果：我是恐怖大魔王
• 字符串替换 replace(字符串,被替换的字符串,用什么字符串替换) replace('找不到工作','找不到','一定能') 结果：一定能找到工作
• 字符串截取 substring(字符串,截取的起始位置,截取长度) substring('abcde',2,3) 结果：bcd

日期函数：

• 当前日期 current_date
• 当前时间 current_time
• 当前日期和时间 current_timestamp
• 获取日期的年份 year(日期)
• 获取日期的月份 month(日期)
• 获取日期的日子 day(日期)
• 日期对应星期几 dayname(日期)