从左到右列的含义依次为学号、姓名、年龄、英语成绩、数学成绩、总成绩。
2.4 过滤
如果要对表中的数据进行过滤,只保留满足我们需求的数据,那就要用到WHERE关键字了。WHERE关键字后跟的是由逻辑运算符连接的一个或多个表达式,每个表达式的最终结果为TRUE或FALSE,只保留表达式结果为TRUE的行。
例如,我们要获取英文成绩不合格的学生姓名和学号,则对应的SQL为
SELECT sno, name FROM student WHERE eng_score < 60运行结果如下:
sno | name |
22270202 | Lily |
22270203 | Tom |
2.5 运算符
运算符,顾名思义就是用于做运算的符号。常见的运算符有三种,比较运算符、算术运算符和逻辑运算符。
比较运算符 | 含义 |
= | 等值比较 |
> | 大于 |
< | 小于 |
>= | 大于或等于 |
<= | 小于或等于 |
<> | 不等于 |
不等于的判断,目前绝大部分的数据库管理系统厂商也都支持了"!=“运算符,与”<>"表达的含义相同。
算术运算符 | 含义 |
+ | 加法运算 |
- | 减法运算 |
* | 乘法运算 |
/ | 除法运算 |
算术运算符在书写时可以紧挨着字段名写,如eng_score-math_score,所以字段名和表名的命名中不能使用中划线(“-”),否则它会被误判为是在做减法运算的。
逻辑运算符 | 含义 |
AND | 与,并且 |
OR | 或,或者 |
NOT | 非,取反 |
当存在多种逻辑运算符时,为了避免歧义,需要使用括号来界定执行的先后顺序,使用括号组织的表达式,可读性也会更强。建议大家不要去记忆逻辑运算符的优先级,容易记混,而且写出的SQL可读性比较差,最好是使用括号,来厘清多个逻辑条件的关系,清晰易懂,可读性强,不容易出错。
了解了上面这些运算符,我们便可以通过组合各种运算符,书写出WHERE后面复杂的表达式,来满足我们的过滤需求了。
2.6 分组聚合
分组聚合是指,我们可以将表中的数据,根据某一列或多列进行分组,然后将其他列的值进行聚合计算,如计数、求和和求平均值等。用到的关键字是GROUP BY,对于分组后的计算结果,我们还可以使用HAVING进行过滤。
例如,从student表中,求出不同年龄的人数、英语总成绩和数学成绩的平均值,且过滤掉。对应的SQL为
SELECT age, COUNT(sno) AS student_num, SUM(eng_score) AS sum_eng_score, AVG(math_score) AS avg_math_scoreFROM studentGROUP BY ageHAVING avg_math_score >= 60
运行后结果如下所示:
age | student_num | sum_eng_score | avg_math_score |
10 | 2 | 138 | 70.5 |
12 | 1 | 89 | 82 |
这里需要注意的是,出现在group by后面的字段或计算公式,必须出现在对应的select的后面,并且除了这些字段或计算公式外,select后面不能有其他字段,只能使用聚合函数。
2.7 去重
DISTINCT关键字用于对一列或多列去重,返回剔除了重复行的结果。DISTINCT对多列去重时,必须满足每一列都相同时,才认为是重复的行进行剔除。DISTINCT不会过滤掉NULL值,但去重后的结果只会保留一个NULL值。
例如,从student表中,找出有几种年龄的学生,即求出去重后的年龄。对应的SQL为
SELECT DISTINCT age FROM student
运行后的结果如下所示:
age |
10 |
11 |
12 |
2.8 排序
日常生活场景里,我们经常对各种各样的排名比较感兴趣,比较关注排在前面的内容。在数据库中,求出排名,就需要用到ORDER BY子句。ORDER BY通常配合ASC和DESC使用,可以根据一列或多列,进行升序或降序排列,之后使用LIMIT取出满足条件的前N行。
例如,从student表中,求出数学成绩最好的前3名学生的姓名、年龄和其数学成绩。对应的SQL如下:
SELECT name, age, math_scoreFROM studentORDER BY math_score DESCLIMIT 3
运行后的结果为:
name | age | math_score |
Jack | 12 | 82 |
Alice | 10 | 76 |
Tom | 10 | 65 |
2.9 增加常量列
增加常量列,即把某一固定的常量值做为一列添加到我们的结果数据中。这种做法的应用场景,通常是结果集中所有的行在某个属性上值是相同的,这时便可以通过增加常量列的方式,来增加这一列。我们通过下面的例子来演示其语法形式。
例如,从student表中,查询英语成绩大于80分的学生的姓名和学号,并把他们都分入A班。对应的SQL如下:
SELECT sno, name, 'A' AS class FROM student WHERE eng_score > 80
运行后的结果为:
sno | name | class |
22270201 | Alice | A |
22270204 | Jack | A |
从示例中可以看出,直接通过"常量 AS 新列名"的方式就可以增加常量列,非常地方便。
3 数据库函数、谓词和CASE表达式
SQL之所以具有强大的分析表达能力,其中一个重要原因,就是它具备丰富的函数,通过这些函数的组合可以实现对数据的复杂处理,最终得到我们想要的数据。另外一方面,SQL也有丰富的谓词来对数据进行判断,匹配出符合我们需求的数据。CASE表达式是一种多条件判断表达式,可以根据不同条件返回不同的值,类似于编程语言中的IF ELSE。
3.1 聚合函数
聚合函数,又称分析函数,是将一组值通过聚合分析后得到一个值,因此得名聚合函数。使用频率最高的聚合函数有5个,如下表所示
函数名 | 含义 |
COUNT | 计数 |
SUM | 求和 |
AVG | 求平均值 |
MIN | 求最小值 |
MAX | 求最大值 |
聚合函数有一个共同的特点,即在计算过程中都会忽略掉NULL值,因为对NULL的聚合是没有任何意义的。COUNT、SUM和AVG三个函数还可以和DISTINCT配合使用,其含义为先对目标列进行去重,之后再对去重后的结果聚合。SUM和AVG只能应用于一列,且列的数据类型为数值型。MIN和MAX也是只能应用于一列,不过除了支持数值型外,还支持字符串类型和日期类型。COUNT可以应用于一列或多列,而且不限制列的类型。
3.2 算术函数
算术函数,主要用于对数值类型进行各种数学运算。SQL中除了加减乘除(±*/)四个运算符外,还提供了一系列的算术函数,如下表所示:
函数名 | 含义 |
CEIL(x) | 向上取整 |
FLOOR(x) | 向下取整 |
ABS(x) | 求绝对值 |
ROUND(x, d) | 四舍五入,对x保留d位小数 |
POWER(x, y) | 幂运算,求x的y次方 |
MOD(x, y) | 取余数,求x被y整除后的余数 |
RAND([n]) | 返回0-1.0的随机数,n为随机种子,可以省略不写 |
这里只罗列了常用的一些函数,通过他们之间的组合,可以实现复杂的运算,如果上述表格不满足你的分析需求,可以自行Google或查看官方文档,寻找匹配的算术函数。
3.3 日期函数
日常分析工作中,经常需要对日期进行加减、格式化等处理,这就离不开强大的日期处理函数,常用的日期函数如下:
函数名 | 含义 |
CURDATE() | 返回当前日期 |
CURRENT_DATE() | 返回当前日期,和上面的函数作用相同 |
CURRENT_TIME() | 返回当前时间 |
NOW() | 返回当前的日期和时间 |
DATE_ADD(d, interval n unit) | 返回日期d加上n个单位后的时间,unit为具体单位,如day,表示天 |
DATE_SUB(d, interval n unit) | 返回日期d减去n个单位后的时间,unit为具体单位,如day,表示天 |
DATE_DIFF(d1, d2) | 返回日期d1和日期d2的天数差 |
DATE_FORMAT(d, ‘format_exp’) | 返回使用日期格式表达式format_exp格式化日期d后得到的字符串 |
YEAR(d) | 返回日期d的年份 |
MONTH(d) | 返回日期d的月份 |
DATE(d) | 返回日期时间d的日期部分,舍弃时间部分 |
日期函数用于获取当前日期时间的函数多数是空参数函数,虽然参数为空,但是函数名后的括号不能省略不写。数据库厂商虽然也提供了部分与函数名相同的属性值,不带括号也能调用,不过笔者建议最好还是使用函数带上空括号,这样识别度更高,可读性更好。
3.4 字符串函数
字符串是信息的一个重要载体,其中包含着大量的重要信息,因此对字符串的处理非常重要,相应地字符串处理函数也是非常丰富,以下我们罗列出最常用的一些函数:
函数 | 含义 | 使用示例 | 返回值 |
LENGTH(str) | 求字符串str的长度 | LENGTH(‘bigdata’) | 7 |
INSTR(str, substr) | 返回substr在str第一次出现的位置(str不包含substr时返回0) | INSTR(‘bigdata’, ‘data’) | 4 |
LEFT(str, len) | 返回str的左端len个字符 | LEFT(‘bigdata’,3) | ‘big’ |
RIGHT(str, len) | 返回str的右端len个字符 | RIGHT(‘bigdata’,4) | ‘data’ |
SUBSTRING(str, pos, len) | 返回str的从位置pos起len个字符 | SUBSTRING(‘bigdata’,4,4) | ‘data’ |
SUBSTRING_INDEX(str, delim, count) | 当count为正数时,从左找到第count个分隔符delim所在位置,并返回其左侧的字符;否则从右开始找,并返回对应位置右侧的字符 | SUBSTRING_INDEX(‘180.97.33.108’, ‘.’, 3) | ‘180.97.33’ |
REPLACE(str, from_str, to_str) | 返回用to_str替换str中的from_str后的字符串 | REPLACE(‘bigdata’, ‘big’, ‘Big’) | ‘Bigdata’ |
LOWER(str) | 返回str转小写后的字符串 | LOWER(‘Bigdata’) | ‘bigdata’ |
UPPER(str) | 返回str转大写后的字符串 | UPPER(‘Bigdata’) | ‘BIGDATA’ |
CONCAT(str1, str2,…) | 将参数连接起来并返回 | CONCAT(‘big’, ‘data’) | ‘bigdata’ |
CONCAT_WS(delim, str1, str2,…) | 将参数使用分隔符delim连接起来并返回 | CONCAT_WS(‘_’, ‘big’, ‘data’) | ‘big_data’ |
3.5 转换函数
当某些数据的类型与我们需要的类型不符时,可以使用类型转换函数,将其类型转换为我们需要的类型。常用的类型转换函数有两个,分别为CAST和CONVERT,两个函数的作用是相同的,只是语法略有不同。CAST函数的用法为CAST(字段 AS 数据类型),而CONVERT的用法为CONVERT(字段, 数据类型)。
不过,并不是所有的类型都是可以互相转换的,而且有些转换会导致精度的损失,因此请谨慎使用。
3.6 其他函数
还有一些函数是使用在特定用途上的,本文也罗列出几个数据分析工作中常用的。
MD5函数,其作用是生成等长的信息摘要。在数据分析工作中,经常用于对敏感信息的脱敏,因为很难通过md5值反向推断加密前的内容,因此是非常安全的。其使用方法为,MD5(str),返回对str进行md5算法计算得到的校验和字符串。
IFNULL(expr1, expr2):如果expr1不为NULL,则返回expr1,否则返回expr2。通常用于对某个字段的NULL值填补,也叫缺失值填补。
IF(expr1, expr2, expr3):如果expr1不等于0或者不为NULL,则返回expr2的值,否则返回expr3的值。相当于编程语言中的IF ELSE条件判断语句
3.7 谓词
简单来说,谓词就是用于真假判断的关键字,用来判定两个对象间关系论断的真假,返回值只有真或假。这么说可能还是有点抽象。我们来举一些谓词的例子大家就明白了。
例如,我们前面讲到的比较运算符,就都属于谓词的范畴。还有一些其他谓词如下表所示:
谓词 | 含义 |
[NOT] LIKE | 模糊匹配,通常配合%和_使用 |
[NOT] IN | 多值包含关系判断 |
[NOT] BETWEEN … AND … | 区间判断 |
IS [NOT] NULL | 是否为NULL值判断 |
[NOT] EXISTS | 是否为空集合判断 |
[NOT] REGEXP | 是否满足正则表达式判断 |
3.8 CASE表达式
SQL语句中的CASE表达式,对应着编程语言中的条件分支,起到多条件判断返回多种值的作用。其语法形式为:
CASE
WHEN <求值表达式1> THEN <表达式1>
WHEN <求值表达式2> THEN <表达式2>
WHEN <求值表达式3> THEN <表达式3>
…
ELSE <表达式> END
其执行过程为,按照书写顺序,依次判断WHEN后面求值表达式返回的值为真或假,如果返回值为假,则继续向下搜索;如果返回值为真时,执行THEN后面对应的表达式,将执行后的值返回,CASE表达式退出;如果所有WHEN子句都不满足时,则执行ELSE后面的表达式,返回执行后得到的值,CASE表达式退出。
了解了执行过程,那么在书写CASE表达式时,就一定要注意顺序问题。这里需要注意一点的是,如果执行到第二个THEN的时候,实际生效的条件为<求值表达式1>的值为假,与此同时<求值表达式2>的值为真;如果执行到第三个THEN的时候,实际生效的条件为<求值表达式1>和<求值表达式2>的值都为假,与此同时<求值表达式3>的值为真,往后以此类推。
3.9 NULL值判断
NULL值的判断必须使用谓词IS,因为NULL和其他任何值(包括NULL值)比较结果都为NULL,也就对应着假。这一点很好理解,你可以把NULL值理解为未知。未知和任何值比较结果还是未知,未知和未知比较,结果也只能是未知。
4 关联查询与子查询
拥有了前面3部分的知识基础,那么我们就可以开始学习SQL的复杂查询。本文要讲的复杂查询有两个,一个是关联查询,一个是子查询。首先,我们先来看下他们的理论基础,集合运算。
4.1 集合运算
在第1部分,我们提到过,在数据库领域,集合是指一组记录的总和,它可以指代表,也可以指代视图、查询执行的结果。所以,表和查询执行的结果都是集合,那么就都可以参与集合运算。也就是说,可以把查询执行的结果看做是一张中间表或临时表,继续参与运算,这就是子查询的理论基础。
集合运算主要包含四种,并集、交集、差集和笛卡尔积。
并集,是求两个集合合并后的集合。在MySQL中使用关键字UNION或UNION ALL实现,两者的区别是,UNION会剔除掉合并后集合中的多余重复值,只保留一份;而UNION ALL,不会剔除重复值。因此,UNION操作,运行结束后,可能会导致记录数的减少。
交集,是求两个集合都共同拥有的元素的集合。在MySQL中没有提供专门的关键字,而是通过内关联实现的,下一小结会详细介绍。
差集,是求在一个集合中存在而在另一个集合中不存在的元素的集合。差集计算具有方向性,同样的,MySQL也没有提供差集计算的关键字,而是需要通过左/右关联然后再过滤出未关联成功的记录而得到。
笛卡尔积,是将两个集合中记录两两组合,相当于集合的乘法。它是关联查询的数学理论基础。你可以简单理解为,关联查询的过程就是,先做笛卡尔积,然后再通过on条件过滤出符合条件的记录。当然,实际的执行过程,不会这么简单,但是是在这个流程基础上去做优化,减少计算量的。
在进行集合的并集、交集和差集运算时,需要注意的是:
-
参与运算的两个集合记录的列数必须相同
-
参与运算的两个集合对应位置的列的类型必须一致
-
如果使用ORDER BY子句,必须写在最后
4.2 表关联类型
常见的表关联类型有四种,内连接(INNER JOIN)、左外连接(LEFT OUTER JOIN)、右外连接(RIGHT OUTER JOIN)、全外连接(FULL OUTER JOIN)。
关联的语法比较简单,拿内连接举例,书写为,A INNER JOIN B ON expr。其中,A和B表示两个表的名称,也可以是子查询。ON后面跟的expr表示关联条件,通常是由表A和表B关联字段组成的表达式。
内连接(INNER JOIN),通常可以省略掉INNER不写,它的含义是左右两个集合相乘后,只保留满足ON后面关联条件的记录。所以,可以利用内连接计算两个集合的交集,只需要把集合元素的字段都写在ON后面的关联条件里即可。
左外连接(LEFT OUTER JOIN),OUTER通常可以省略不写,它的含义是,左右两个集合相乘后,保留满足ON后面关联条件的记录加上左表中原有的但未关联成功的记录。因此,左外连接,可以用来计算集合的差集,只需要过滤掉关联成功的记录,留下左表中原有的但未关联成功的记录,就是我们要的差集。
右外连接(RIGHT OUTER JOIN),与左外连接含义相同,只是方向不同而已,通常也是省略OUTER不写。
全外连接(FULL OUTER JOIN),含义是,左右两个集合相乘后,保留满足ON后面关联条件的记录加上左表和右表中原有的但未关联成功的记录。
4种JOIN方式的示意图
4.3 多表关联
多表关联的本质,还是两两关联。例如,表A内关联表B再内关联表C,实际上就可以等价于表A内关联表B,运行后的结果作为一张中间表,然后再与表C内关联。所以,执行过程仍然是两两关联。
4.4 表关联注意事项
表关联是比较复杂的查询方式,在书写时,大家要在脑海中构建关联后的集合的样子,对应去选择需要使用的连接方法。下面是根据实际工作经验总结的容易出错的点,希望大家注意。
a. 使用UNION可能会导致记录数的减少,在使用聚合函数时,可能会导致计算出现偏差
b. 在使用1对多或多对多关系的表进行关联时,记录数可能会增多,也可能会导致计算出现偏差
**c.**左外连接和右外连接都有连接方向的问题,表放的位置对结果是有影响的,尤其是多表关联时,一定要关注书写的顺序,尽可能先做内连接再做左/右外连接。
**d.**尽量避免使用交叉连接
4.5 子查询
子查询,就是指被括号嵌套起来的查询SQL语句,通常是一条完整的SELECT语句。
子查询放在不同的位置,起到的作用也是不同的。它经常出现在3个位置上,分别是SELECT后面、FROM/JOIN后面,还有WHERE/HAVING后面。
- 当子查询出现在SELECT后面时,其作用通常是要为结果添加一列。不过,这里要注意的是,在SELECT后使用的子查询语句只能返回单个列,且要保证满足条件时子查询语句只会返回单行结果。企图检索多个列或返回多行结果将引发错误。
- 子查询出现在FROM/JOIN后面,是我们最常用的方式,就是将子查询的结果作为中间表,继续基于这个表做分析。
- 当子查询出现在WHERE/HAVING后面时,则表示要使用子查询返回的结果做过滤。这里根据子查询返回的结果数量,分三种情况,即1行1列、N行1列、N行N列。
- 当返回结果为1行1列时,实际上就是返回了一个具体值,这种子查询又叫标量子查询。标量子查询的结果,可以直接用比较运算符来进行计算。
- 当返回结果是N行1列时,实际上就是返回了一个相同类型数值的集合。因此可以使用IN谓词判断,同时也可以配合ANY、SOME、ALL等关键字使用。
- 当返回结果是N行N列时,实际上就是返回一个临时表,这时就不能进行值的比较了,而是使用EXISTS谓词判断返回的集合是否为空。
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,涵盖了95%以上大数据知识点,真正体系化!
由于文件比较多,这里只是将部分目录截图出来,全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频,并且后续会持续更新
用IN谓词判断,同时也可以配合ANY、SOME、ALL等关键字使用。
- 当返回结果是N行N列时,实际上就是返回一个临时表,这时就不能进行值的比较了,而是使用EXISTS谓词判断返回的集合是否为空。
[外链图片转存中…(img-N0DrBvco-1714395829300)]
[外链图片转存中…(img-DpRJC01u-1714395829300)]
[外链图片转存中…(img-tXX4jFxU-1714395829300)]
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,涵盖了95%以上大数据知识点,真正体系化!
由于文件比较多,这里只是将部分目录截图出来,全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频,并且后续会持续更新