MySQL学习-常用函数和关键字总结

1、group by 

分组函数，常用于结果显示或分组计算，若有查询结果要求显示两个键和次数，且次数是这两个键相同的组合，则用count（）统计其中任意一个键或者直接count（*）即可，之后group by 未用聚合函数的字段，会返回双键相同组合出现的次数。

另一种理解方式：group by 把后面的字段视为主键或者主键组合，主键是不会重复的，也不会有完全相同的主键组合出现，这样达到了分组的效果。分组后，聚合函数均会在组内生效，而不会跨组。

2、if函数，结构IF(condition, value_if_true, value_if_false)#如果条件为 TRUE，IF() 函数返回一个值，如果条件为 FALSE，则返回另一个值。可返回字段，可用于对字段设置条件，返回不同的字段，可以遍历。

3、round函数，用于四舍五入，ROUND(number, decimals)#

number	必需。要四舍五入的数字
decimals	可选。number 要四舍五入的小数位数。 如果省略，则返回整数（无小数）

4、count，计算数值个数，若要去重，在字段前加上distinct

5、（）括号里面写查询语句相当于建立了一个新表，可用于join

6、IFNULL(expression_1,expression_2); #内容值若为null，会默认返回0。它接受两个参数，如果不是NULL，则返回第一个参数。 否则，IFNULL函数返回第二个参数。

7、CASE WHEN语句 ###CASE 具有两种格式：简单 CASE 函数将某个表达式与一组简单表达式进行比较以确定结果。 CASE 搜索函数计算一组布尔表达式以确定结果。  两种格式都支持可选的 ELSE 参数。###若要使用count计算字段值为特定值的个数，括号内不能使用if语句，应使用case when ，这样可以跳过没有被case 到的数据，准确count case到的数据。（原因：除了count(*)之外的其他聚集函数（包括count(column-name), sum(), avg(), max(), min()）都只操作non-NULL的值。未case到的会返回null，if则不一样。count(1)和count(*)在mysql中没有区别，无论是结果还是性能都是相同的。）

1）简单 CASE 函数：

CASE input_expression
WHEN when_expression THEN
    result_expression [...n ] [
ELSE
    else_result_expression
END

占位符 ，表明可以使用多个 WHEN when_expression THEN result_expression 子句或 WHEN Boolean_expression THEN result_expression 子句。简单 CASE 函数： 返回结果值介绍：
 
计算 input_expression，然后按指定顺序对每个 WHEN 子句的 input_expression = when_expression 进行计算。
返回第一个取值为 TRUE 的 (input_expression = when_expression) 的 result_expression。如果没有取值为 TRUE 的 input_expression = when_expression，则当指定 ELSE 子句时 SQL Server 将返回 else_result_expression；若没有指定 ELSE 子句，则返回 NULL 值。

2）CASE 搜索函数：

CASE
WHEN Boolean_expression THEN
    result_expression [...n ] [
ELSE
    else_result_expression
END

参数介绍：
WHEN Boolean_expression 使用 CASE 搜索格式时所计算的布尔表达式。Boolean_expression 是任意有效的布尔表达式。结果类型从 result_expressions 和可选 else_result_expression 的类型集合中返回最高的优先规则类型。有关更多信息，请参见数据类型的优先顺序。
 
CASE 搜索函数：返回结果值介绍：
按指定顺序为每个 WHEN 子句的 Boolean_expression 求值。返回第一个取值为 TRUE 的 Boolean_expression 的 result_expression。
如果没有取值为 TRUE 的 Boolean_expression，则当指定 ELSE 子句时 SQL Server 将返回 else_result_expression；若没有指定 ELSE 子句，则返回 NULL 值。

8、avg##

AVG()函数是一个聚合函数，它用于计算一组值或表达式的平均值。会忽略null数据，跳过，既不统计值也不统计进个数，

AVG(DISTINCT expression) ###您可以使用AVG()函数中的DISTINCT运算符来计算不同值的平均值。eg:1,1,2,3，具有DISTINCT操作的AVG()函数将返回不同值的和，即：(1 + 2 + 3)/3 = 2.00 。

avg()###括号里面可以放一个等式，eg：AVG( a.order_date = d.customer_pref_delivery_date）这样会统计a.order_date中数据等于 d.customer_pref_delivery_date的个数在a.order_date总数中的平均值。

9、like 作常用连接词，表示一个字段类似或者包含like 后面的内容，例子：description not like 'boring'，表示description字段不包含boring文本。

10、mod（被除数，除数），返回被除数的余数。常用于判断奇偶性。

11、RANK()函数为结果集的分区中的每一行分配一个排名。行的等级由一加上前面的等级数指定。 以下显示了RANK()函数的语法：

RANK() OVER (
    PARTITION BY <expression>[{,<expression>...}]
    ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
) 

在这个语法中：

• 首先，PARTITION BY子句将结果集划分为分区。RANK()功能在分区内执行，并在跨越分区边界时重新初始化。
• 其次，ORDER BY子句按一个或多个列或表达式对分区内的行进行排序。

与ROW_NUMBER()函数不同，RANK()函数并不总是返回连续的整数。遇到相同会并列排名，然后下一个排名会跳过一位

12、

一、LEAD(expr [, N[, default]] over(partition by order by )

功能：会返回分区内当前行后边N行的字段值，如果没有这样的行，会返回你设置的default （如果N和default省略了，则默认为 1和null)

lead(field, num, defaultvalue) field 需要查找的字段，num 往后查找的 num 行的数据，defaultvalue 没有符合条件的默认值

注意：

①N必须是一个非负整数，如果N=0，expr是当前行的字段值

②从mysql8.0.22开始，N不能为空，且必须是1-2^63的整数（可以是变量）

二、LAG(expr [, N[, default]]) over(partition by order by )

功能：会返回分组内当前行前边N行的字段值，如果没有这样的行，会返回你设置的default

例1： 

注意：当使用了多个窗口函数，且这些窗口函数中的over()的内容都相同，可使用命名窗口函数。

13、CONCAT()函数需要一个或多个字符串参数，并将它们连接成一个字符串。CONCAT()函数需要至少一个参数，否则会引起错误。

下面说明了CONCAT()函数的语法。

CONCAT(string1,string2, ... ); 

CONCAT()函数在连接之前将所有参数转换为字符串类型。如果任何参数为NULL，则CONCAT()函数返回NULL值。

CONCAT_WS()函数。CONCAT_WS()函数将两个或多个字符串值与预定义的分隔符相连接。

CONCAT_WS(seperator,string1,string2, ... ); 

第一个参数是其他参数：string1，string2，...的分隔符。

CONCAT_WS函数在字符串参数之间添加分隔符，并返回单个字符串，并在字符串参数之间插入分隔符。

以下语句连接两个字符串值：Max和Su，并用逗号分隔这两个字符串：

SELECT CONCAT_WS(',','Max','Su'); 

且仅当作为分隔符的第一个参数为NULL时，CONCAT_WS函数才返回NULL。

与CONCAT函数不同，CONCAT_WS函数在分隔符参数之后跳过NULL值。 换句话说，它忽略NULL值。 

GROUP_CONCAT()连接某个字段下的数据，默认用，连接，可以在后面加上排序语句 

GROUP_CONCAT(DISTINCT product ORDER BY product)

14、LEFT()函数是一个字符串函数，它返回具有指定长度的字符串的左边部分。

LEFT(str,length); 

LEFT()函数接受两个参数：

• str是要提取子字符串的字符串。
• length是一个正整数，指定将从左边返回的字符数。

LEFT()函数返回str字符串中最左边的长度字符。如果str或length参数为NULL，则返回NULL值。

如果length为0或为负，则LEFT函数返回一个空字符串。如果length大于str字符串的长度，则LEFT函数返回整个str字符串。

15、SUBSTRING函数从特定位置开始的字符串返回一个给定长度的子字符串。

SUBSTRING(string,position);

SUBSTRING(string FROM position);

有两个参数：

• string参数是要提取子字符串的字符串。
• position参数是一个整数，用于指定子串的起始字符，position可以是正或负整数。

请注意，如果position参数为零，则SUBSTRING函数返回一个空字符串：

如果要指定要从字符串中提取的子字符串的长度，可以使用以下形式的SUBSTRING函数

SUBSTRING(string,position,length); 

SUBSTRING(string FROM position FOR length); 

要查询包含某些连续字符的字符串有哪些可以使用%表示，eg：

like '%AAA'表示AAA在结尾的字符串，like 'AAA%'在开头的字符串，like '%AAA%'在中间的字符串

16、limit 关键字

limit m,n: 从第m+1条开始，取n条数据 例如获取第二高的薪水limit 1, 1, 可以解释为从 2 条开始取 1 条数据(第一条数据是最高的薪水)