MySQL复习（一）

月亮月亮要去太阳

已于 2024-04-22 15:19:48 修改

阅读量949

点赞数 8

文章标签： mysql 数据库

于 2024-04-16 22:06:57 首次发布

本文链接：https://blog.csdn.net/qq_43710593/article/details/137840945

版权

本文详细介绍了SQL中的各种函数类型，如聚合、标量、窗口、字符串、数学、日期时间、逻辑、条件、聚合窗口以及用户自定义函数。还涵盖了连接方式、判断筛选、GROUPBY和HAVING的使用，以及字符串处理和特殊函数如PERCENTILE和SOUNDEX。

摘要由CSDN通过智能技术生成

一、常见的 SQL 函数类型：

聚合函数（Aggregate Functions）：

- 用于对一组值执行计算，并返回单个结果。常见的聚合函数包括：SUM、AVG、COUNT、MIN、MAX等。

标量函数（Scalar Functions）：

- 对单个值进行操作，并返回单个值。常见的标量函数包括：UPPER、LOWER、SUBSTRING、TRIM等。

窗口函数（Window Functions）：

- 在特定的窗口（一组行）上执行计算，并返回结果。常见的窗口函数包括：ROW_NUMBER、RANK、DENSE_RANK、LEAD、LAG等。

字符串函数（String Functions）：

- 用于对字符串数据执行操作。常见的字符串函数包括：CONCAT、SUBSTRING、LENGTH、CHARINDEX等。

数学函数（Mathematical Functions）：

- 用于执行数学运算。常见的数学函数包括：ABS、ROUND、CEILING、FLOOR等。

日期和时间函数（Date and Time Functions）：

- 用于处理日期和时间数据。常见的日期和时间函数包括：GETDATE、DATEADD、DATEDIFF、YEAR、MONTH等。

逻辑函数（Logical Functions）：

- 用于执行逻辑运算。常见的逻辑函数包括：IF、CASE、COALESCE等。

条件函数（Conditional Functions）：

- 根据条件返回不同的结果。常见的条件函数包括：CASE、IFNULL、NULLIF等。

聚合窗口函数（Aggregate Window Functions）：

- 结合了聚合函数和窗口函数的功能，可以在指定的窗口上执行聚合操作。常见的聚合窗口函数包括：SUM() 、AVG() 、COUNT() 、OVER等。

用户自定义函数（User-defined Functions）：

- 用户可以根据自己的需求创建自定义函数，以实现特定的数据处理逻辑。

二、连接方式：

交叉连接：苹果*字母

JOIN: 如果表中有至少一个匹配，则返回行【这个连接就是指内连接】【当然也可以多表选择的方式】
LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行【注意：左连接，右表有多个匹配的，会重复显示】
RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行【这种左右连接，主匹配的列应该是唯一的，这应该是底层逻辑】
FULL JOIN: 只要其中一个表中存在匹配，就返回行
UNION 和UNION ALL 是否包含重复值的区别，且UNION会排序，UNION ALL只是简单地将两个结果集合并；因为UNION 会做去重和排序处理，因此效率比UNION ALL慢很多；

连接之后，尽量要打表的前缀，至少on的那个必须打，并且可以自连接。

三、一些判断和筛选

IS NULL

IS NOT NULL

BETWEEN AND

IN/NOT IN

DISTINCT【会区分空值否？】用于返回唯一不同的值且不会包含 NULL 值

select coalesce(success,period,1) from tableA

COALESCE是一个函数， (expression_1, expression_2, ...,expression_n)依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。使用COALESCE在于大部分包含空值的表达式最终将返回空值。

当success不为null，那么无论period是否为null，都将返回success的真实值，当success_cnt为null，而period不为null的时候，返回period的真实值。只有当success_cnt和period均为null的时候，将返回1。

四、GROUP BY 和HAVING

GROUP BY 之后的limit是分组limit

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。

除了聚集函数外，出现在GROUP BY 子句中的列也要出现在SELECT中。同理，对HAVING除了聚合函数和选定的列，不能有其他的列。错误例子如下：

SELECT employee_id, department_id 
FROM Employee 
GROUP BY employee_id 
HAVING count(employee_id)=1 or primary_flag='Y'

分组——GROUP BY；分组统计——with rollup：

with在sql语句中定义在group by之后。当需要对数据库数据进行分类统计的时候，往往会用上groupby进行分组。而在groupby后面还可以加入withcube和withrollup等关键字对数据进行汇总。不过这个cube在mysql中并不适用。

以谁分组就在谁底下汇总，默认名称为NULL。

选出哪些列就会对哪些列汇总

Group by

With rollup

HAIVING

注意：

ORDER BY不能在rollup中使用，两者为互斥关键字，如果使用，会抛出以下错误：Error Code:1221. Incorrect usage of CUBE/ROLLUP and ORDER BY。mysql5.7中是不支持的，在8.0以后支持。

五、count

`COUNT(CASE WHEN rating < 3 THEN 1 END)` 和 `COUNT(rating < 3)` 的不同：

1. `COUNT(CASE WHEN rating < 3 THEN 1 END)`:

- 这是一个条件计数的方法。它使用了 CASE WHEN 结构，对符合条件的行返回 1，不符合条件的行返回 NULL（或者不返回任何东西，因为没有 ELSE 子句）。然后 COUNT 函数会统计返回的非 NULL 值的数量，以得到符合条件的行数。

- 这个方法更加通用，可以用于更复杂的条件统计，例如统计多个条件的情况。

2. `COUNT(rating < 3)`:

- 这是一个逻辑表达式，返回一个布尔值（TRUE 或 FALSE），对每一行评估评分是否小于 3。在大多数数据库中，TRUE 会被解释为 1，FALSE 会被解释为 0。

- 因此，COUNT 函数会统计所有评分小于 3 的行，因为它们会被计为 1。

在一般情况下，两者可以实现相同的功能，但在某些情况下，使用 `COUNT(CASE WHEN ...)` 可能更加灵活，特别是当你需要统计多个条件时。

count(*)基本上想到于count(1)，只管行数不管NULL；count(列名)返回的是非空值的行数，中间有个判断的过程。
六、条件判断函数——CASE WHEN、IF、IFNULL
case when常常和聚合函数连用

CASE 字段 WHEN 预期值 THEN 结果1 ELSE 结果2 END

```
IF(expr,result_true,result_false)
```
```
IFNULL(column,值)
```

七、SQL中的字符串处理

SQL知识大全（三):SQL中的字符串处理和条件查询 (zhihu.com)
补充：
substring_index('www.baidu.com', '.', 2) 截取第二个 '.' 之前的所有字符

substring_index('www.baidu.com', '.', -2) 截取第二个 '.' （倒数）之后的所有字符
STUFF(string, start, length, new_string) 例：stuff('lo ina', 3, 1, 've ch')——love china