mysql 取余函数 表名拼接 sql语句取余数

• 视图
• 子查询
• 标量子查询
• 关联子查询
• 如何使用SQL解决业务问题
• 各种函数

一、视图

需要频繁使用某一类数据，存放sql查询语句，不需要保存数据

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

可以多次使用

二、子查询

嵌套查询，sql查询语句运行结束后会删掉，偶尔使用

• 先运行子查询得到临时表
• 作为外部查询的部分，

in（子查询）

Q：找出每个课程里成绩最低的学号

-- 第1步，出现“每”→分组，group by ，查找出每门课程的最低成绩有哪些值
SELECT 课程号,min(成绩)
FROM score
GROUP BY 课程号
-- 第2步，在成绩表里查找这些值对应的学号
SELECT 学号,成绩
FROM score
WHERE 成绩 in (80,50,40)

放进子查询，嵌套起来

SELECT 学号,成绩
FROM score
WHERE 成绩 in (
SELECT min(成绩)
FROM score
GROUP BY 课程号
);

any（子查询）：与比较运算符一起使用

any（子查询）与some（子查询）相同

SELECT 列名1
FROM 表名1
WHERE 列名1> any(子查询);

Q：哪些学生的成绩比课程0002的全部成绩里的任意一个高呢？

-- 第1步，课程0002的全部成绩
SELECT 成绩
FROM score
WHERE 课程号 = "0002"
-- 第2步，某个学生的成绩 大于 任意一个第1步里的成绩，就符合条件
SELECT 学号,成绩
FROM score
WHERE 成绩 >any(30,60,50)

嵌套起来

SELECT 学号,成绩
FROM score
WHERE 成绩> any(
SELECT 成绩
FROM score
WHERE 课程号 = '0002'
);

all（子查询）：与比较运算符一起使用

Q：哪些学生的成绩比课程0002的全部成绩里的都高呢？

SELECT 学号,成绩
FROM score
WHERE 成绩> all(
SELECT 成绩
FROM score
WHERE 课程号 = '0002'
);

*使用子查询的注意事项：

1. a > 3 * all(b)【×】：得到一个集合，所以不行

a/3 > all(b) 【√】

2. 避免使用层层嵌套： select ...from(子查询(子查询))

3. select...from...子查询 as 子查询名称（方便查看）

SQL运行顺序：

三、标量子查询

Q：大于平均成绩学生的学号和成绩

正确：子查询先返回标量

SELECT 学号,成绩
FROM score
WHERE 成绩 > (
SELECT avg(成绩)
FROM score
);

定义差生（平均成绩<=60），优等生（平均成绩>80）

Q：查找分数在差生和优等生平均成绩之间的分数

SELECT 学号,成绩
FROM score
WHERE 成绩 BETWEEN
(SELECT avg(成绩)
FROM score
WHERE 成绩 <= 60)AND
(SELECT avg(成绩)
FROM score
WHERE 成绩 > 80);

*注意<=号之间不能有空格

任何使用单一值的地方都可以使用标量子查询

select后面跟的是三列，每一列对应一个平均成绩，都是完整的一列，都是标量子查询的结果

标量子查询只返回单一值，才可以使用比较运算符

四、关联子查询

Q：查找出每个课程中 大于对应课程平均成绩的学生

-- 查找出每门课程的平均成绩
SELECT 课程号,avg(成绩)
FROM score
GROUP BY 课程号

关联子查询

SELECT 学号,课程号,成绩
FROM score AS s1
WHERE 成绩 > (
SELECT avg(成绩)
FROM score AS s2
WHERE s1.课程号 = s2.课程号 -- s2只在子查询里有效
GROUP BY 课程号);

同一个表，关联条件需要写在子查询里

关联子查询：组内比较

五、如何使用SQL解决业务问题

1.翻译成大白话

2.写出分析思路

3.写出对应的SQL语句

六、各种函数

汇总函数

• count：求某列的行数
• sum：求某列的和（只能对数值类型的列计算）
• avg：求某列的平均值（只能对数值类型的列计算）
• max：求某列数据的最大值
• min：求某列数据的最小值

算术函数

• round(数值,保留小数的位数)：对数据进行四舍五入。比如：round(1234.567,1)→1234.6；round(1234.567,-1)→1230
• abs(数值)：绝对值。比如：abs(-100)→100
• mod(被除数,除数)：求余数。比如：mod(5,2)→1

字符串函数

• length(字符串)：字符串长度。比如：length('abbcde')→5
• lower(字符串)：大写转换为小写。比如：lower('A')→a
• upper(字符串)：小写转换为大写。比如：upper('a')→A
• concat(字符串1,字符串2)：字符串拼接。比如：concat('好好','学习')→好好学习
• replace(字符串,被替换的字符串,用什么字符串替换)：字符串替换。比如：replace('好好学习','学习','做作业')→好好做作业
• substring(字符串,截取的起始位置,截取长度)：字符串截取。比如：substring('abcde',2,3)→bcd

日期函数

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

练习作业见：https://zhuanlan.zhihu.com/p/82722127