MySql系统函数![image.png](https://p3-juejin.byteimg.com/tos-cn-i

详情可查看数据库8.0开发文档：dev.mysql.com/doc/refman/…

控制流函数

格式： IF (expr1,expr2,expr3)

描述：如果 expr1 是 true , 则 IF( )的返回值为expr2; 否则返回值则为 expr3。if ( ) 的返回值为数字值或字符串值，具体情况视其所在语境而定。案例：

SELECT IF(1>2,'yes','no');   #结果--->no 
SELECT IF(1>2,0,55);         #结果--->55
SELECT *,IF(disabled >= 1,'禁用','启用') flag FROM t_user;   #结果取决于disabled的值。

格式： COALESCE (a,b,c)

描述：如果a为null,则选择b；如果b为null,则选择c；如果a不为null,则选择a；如果a b c 都为null ，则返回为null（没意义）案例：

SELECT COALESCE(NULL,'b','c');     结果：b
SELECT COALESCE(NULL,NULL,'c');    结果：c
SELECT COALESCE('a',NULL,NULL);    结果：a
SELECT COALESCE(NULL,NULL,NULL);   结果：NULL

格式：IFNULL (expr1,expr2)

描述：如果expr1 不为 NULL，则 IFNULL() 的返回值为 expr1; 否则其返回值为 expr2。IFNULL()的返回值是数字或是字符串，具体情况取决于其所使用的语境。

案例：

SELECT IFNULL(1,10);     #结果--->1 
SELECT IFNULL(NULL,10);  #结果--->10

格式：ISNULL (expr)；

描述：判断expr是否为NULL。不是NULL返回1，是NULL返回0;

案例：

SELECT ISNULL(10);     #结果--->0 
SELECT ISNULL(NULL);   #结果--->1

格式：NULLIF (expr1,expr2)

描述：比较两个expr1与expr2，如果expr1与expr2相等则会返回NULL，否则返回expr1。

案例：

SELECT NULLIF(11,11);             #结果--->NULL 
SELECT NULLIF(11,22);             #结果--->11 
SELECT NULLIF('wuke','wuke');     #结果--->NULL 
SELECT NULLIF('wuke','www');      #结果--->wuke

格式：
    CASE expression

          WHEN condition1 THEN result1

          WHEN condition2 THEN result2；

          ...

         ELSE result
    END
描述：CASE 表示函数开始，END表示函数结束，如果condition1 成立则返回result1，如果condition2 成立则返回result2，当全部不成立，则返回result，而当有一个成立时后面的就不会再继续执行了。

案例：

#第一种写法 
    SELECT CASE 3 WHEN 1 THEN '等于1' 
        WHEN 2 THEN '等于1' 
        WHEN 3 THEN '等于3' 
        WHEN 4 THEN '等于4' 
        ELSE '谁都不等' 
    END as result; 
    #执行结果: 等于3 

#第二种写法 
    SELECT CASE 
        WHEN 1>3 THEN '大于1' 
        WHEN 2<1 THEN '小于1' 
        WHEN 3=4 THEN '等于3' 
        WHEN 4>3 THEN '大于4' 
        ELSE '谁都不等' 
    END as result; 
#执行结果: 大于4

字符函数

格式：CONCAT (str1,str2,…)

描述：返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。或许有一个或多个参数。如果所有参数均为非二进制字符串，则结果为非二进制字符串。但是如果自变量中含有任意二进制字符串，则结果为一个二进制字符串。一个数字参数被转化为与之相等的二进制字符串格式；若要避免这种情况，可使用显式类型 cast

案例：

SELECT CONCAT("MY","S","Q","L") AS ConcatenatedString;   结果： MYSQL
SELECT CONCAT("MY",null,"Q","L") AS ConcatenatedString;  结果： NULL
SELECT CONCAT(CAST(int_col AS CHAR), char_col); 将int类型使用cast函数转换为char类型

格式：CONCAT_WS (separator,str1,str2,…)

描述：separator是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。CONCAT_WS()不会忽略任何空字符串。(然而会忽略所有的 NULL）

案例：

SELECT CONCAT_WS ("，", "SQL", "Tutorial", "is", "fun!")AS Str;     结果：SQL,Tutorial,is,fun!

格式：GROUP_CONCAT ( [DISTINCT] 字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

描述：如果你要按照指定字段分组拼接，就要配合关键字GROUP BY来使用的。分组拼接的值之间默认分隔符是逗号>（，）。要明确指定分隔符，需要使用SEPARATOR关键字，紧跟其后的是你想设置的分隔符，要完全消除分隔符，就在SEPARATOR 关键字后面写 ' ' 就好了，
例如：GROUP_CONCAT(id) SEPARATOR ''.
注意：GROUP_CONCAT9 ()函数返回单个字符串，而不是值列表。这意味着我们不能在 IN 运算符中使用该函数。

案例：

select id,price from goods;

以id分组，把price字段的值在同一行打印出来，逗号分隔(默认)

select id, group_concat(price) from goods group by id;

以id分组，把price字段的值在一行打印出来，分号分隔

select id,group_concat(price separator ';') from goods group by id;

以id分组，把去除重复冗余的price字段的值打印在一行，逗号分隔

select id,group_concat(distinct price) from goods group by id;

以id分组，把price字段的值去重打印在一行，逗号分隔，按照price倒序排列

select id,group_concat(DISTINCT price order by price desc) from goods group by id;

格式：FIND_IN_SET (str,strlist)

描述：- 第一个参数str是要查找的字符串。第二个参数strlist是要搜索的逗号分隔的字符串列表。
注意：假如字符串str 在由N 子链组成的字符串列表strlist 中，则返回值的范围在 1 到 N 之间。一个字符串列表就是一个由一些被 ‘ ，’ 符号分开的字符串。如果第一个参数是一个常数字符串，而第二个是>type SET列，则 FIND_IN_SET() 函数被优化，使用比特计算。如果str不在strlist 或strlist 为空字符串，则返回值为 0 。如任意一个参数为NULL，则返回值为 NULL。这个函数在第一个参数包含一个逗号 ‘ ，’ 时将无法正常运行。

案例：

#使用案例：查找成绩有88的学生
SELECT * FROM t_student ts WHERE FIND_IN_SET('88',ts.score);

格式：LENGTH ()

描述：获取字节个数（utf-8 一个汉字为3个字节，gbk为2个字节）。

案例：

SELECT LENGTH('ijkio')      结果: 5
SELECT LENGTH('产生的z')     结果: 10

格式：SUBSTR (str,pos) 格式：SUBSTR (str,pos,len)

描述：截取字符串。str:要裁剪的字符串,pos：从哪个位置开始,len:裁剪几位。SUBSTRING同理。

案例：

SELECT SUBSTR('apple',2);    结果：pple
SELECT SUBSTR('apple',4,1);  结果：l

格式：TRIM([BOTH/LEADING/TRAILING] 目标字符串 FROM 源字符串）
格式：LTRIM ()
格式：RTRIM ()

描述：TRIM：去除两边指定字符包括空白符。LTRIM:去除左边指定字符包括空白符。RTRIM：去掉右边指定字符包括空白符。

案例：

SELECT TRIM(' 123 ');                   结果： 123  <两边无空格>
SELECT LTRIM('    123');                结果： 123  <左边无空格>
SELECT RTRIM('123     ');               结果： 123  <右边无空格>

SELECT TRIM(BOTH 'a' FROM 'applea');    结果： pple   #BOTH删除指定的首尾字符
SELECT TRIM(BOTH 'e' FROM 'applea');    结果： appleaa

SELECT TRIM(LEADING 'a' FROM 'applea'); 结果： pplea  #LEADING 删除指定的首字符

SELECT TRIM(TRAILING 'a' FROM 'applea');结果： apple  #TRAILING删除指定的尾字符

格式：LPAD (str,len,padstr) :左填充
格式：RPAD (str,len,padstr) :右填充

描述：用指定字符实现填充指定长度。str:要处理的对象。len:处理完后的str长度为len。padstr:如果str的长度小于len指定的值，那么长度差由padstr在左边填充；如果str的长度大于len指定的值，则截取str到len指定的长度。返回值说明：这两个函数返回的是处理后的str。

案例：

SELECT LPAD('wu',10,'-');  结果：--------wu
SELECT RPAD('wu',10,'-');  结果：wu--------

格式：REPLACE (field,find_str,replace_str)

描述：字段field的内容中的find_str 将被替换为 replace_str。

案例：


SELECT REPLACE('www.163.com','w','W')      结果：WWW.163.com

#把table中的name字段中的aa替换为bb
update table set name=replace(name,'aa','bb');

#现在有一条记录的字段是“abcdefg",现在我只想将该字段中的c改为C，update语句应该怎么写?
update 表名 set 字段1 = replace(字段1,'c','C');

格式：UPPER ()
格式：LOWER ()

描述：将字母转大小写。

案例：

SELECT UPPER('ok')      结果: OK
SELECT LOWER('JB')      结果: jb

格式：INSTR (str,substr)

描述：返回字符串 str 中子字符串的第一个出现位置。如果找不到返回0。

案例：

SELECT INSTR ('foobarbar','bar');   结果：4 
SELECT INSTR ('bar','foobar);       结果：0

格式：STRCMP (expr1,expr2)

描述：比较两个字符串的顺序是否完全一致,完全一致的意思是字符串里的每个字符、顺序都一致。expr 就是两个需要比较的字符串，若expr1 小于 expr2 ，则返回 -1，若expr1 大于 expr2 ，则返回 1，其他情况则返回 0（如相等）。

案例：

SELECT STRCMP('text', 'text2');  结果---> -1 
SELECT STRCMP('text2', 'text');  结果---> 1 
SELECT STRCMP('text', 'Text');   结果---> 0 

SELECT strcmp(123, 123);         结果---> 0 
SELECT strcmp(123, 122);         结果---> 1 
SELECT strcmp(123, 124);         结果---> -1 

SELECT strcmp('abc', 'Abc');     结果---> 0 
SELECT strcmp('abc', 'abb');     结果---> 1 
SELECT strcmp('abc', 'abd');     结果---> -1

格式：MAKE_SET (separator, str1, str2,...)

描述：返回一个设定值(含子字符串分隔字符串。，。字符)由那些在设置位的相应位的字符串。str1对应于位1，str2至位2，以此类推。NULL值在str1，str2，...不添加到结果。

案例：

Select make_set(1, 'apple','mango','grapes');  结果：apple
Select make_set(2, 'apple',NULL,'grapes');     无结果

数学函数

格式：ABS (val)

描述：取绝对值

案例：

SELECT ABS(-10);  结果10 
SELECT ABS(10)    结果10

格式：FLOOR (val)

描述：向下取整

案例：

SELECT FLOOR(1.1)  结果1 
SELECT FLOOR(9.9)  结果9

格式：CEIL (val)

描述：向上取整

案例：

SELECT CEIL(1.1)  结果1 
SELECT CEIL(9.9)  结果9

格式：ROUND (val)

描述：四舍五入

案例：

SELECT ROUND(3.51423)   结果4 
SELECT ROUND(3.4125,3)  结果3.413

格式：MOD (val，mun)

描述：val：除数,num：被除数,该函数可以取模。

案例：

SELECT MOD(X,Y)   结果会返回X除以Y的余数 
SELECT MOD(5,2)   结果1 
SELECT MOD(9,3)   结果0

格式：POW (val，num)

描述：val：表示底数,num表示次方数，该函数可以计算次方

案例：

SELECT POW(2,3)  结果8
SELECT POW(3,2)  结果9

格式：PI ()

描述：圆周率

案例：

SELECT PI() 结果3.14.593

格式：RAND ()

描述：RAND() 函数主要有两个用处：1.是产生随机数， 2.是随机排序（在数据较大的时候会变成性能杀手）

案例：

1.产生一个随机数，默认0~1之间的浮点数
SELECT RAND()
2.参数指定范围的的随机数，比如 500~5000
SELECT FLOOR(500 + (RAND() * 4500))
3. 随机排序，比如我有一张文章表，我要随机抽取5篇文章
SELECT * FROM content ORDER BY RAND();

聚合函数

格式：AVG ()

描述：返回数值列的平均值。

案例：

SELECT AVG(列名) 别名 FROM 表名

格式：COUNT ()

描述：返回匹配指定条件的行数（NULL 不计入）。

案例：

SELECT COUNT(*) 别名 FROM 表名

格式：MAX ()

描述：返回指定列的最大值。

案例：

SELECT MAX(列名) 别名 FROM 表名

格式：MIN ()

描述：返回指定列的最小值。

案例：

SELECT MIN(列名) 别名 FROM 表名

格式：SUM ()

描述：返回指定列的最小值。

案例：

SELECT SUM(列名) 别名 FROM 表名

格式：GREATEST (value1,value2,...): 得到value1,value2,...中的最大值。
格式：LEAST (value1,value2,...): 得到value1,value2,...中的最大值。

描述：这两个函数主要是进行行比较，value1,value2,...可以是数值也可以是时间，都可以比较。
GREATEST：横向求最大(一行记录)，MAX：纵向求最大(多行记录)。
LEAST: 横向求最小(一行记录)， MIN：纵向求最小(多行记录)。
注意：如果比较的值当中，只要含有一个null值，则两个函数直接会返回null，并不会进行比较。

案例：

SELECT id, GREATEST(Row1,Row2) 别名 FROM table;

日期函数

格式：UNIX_TIMESTAMP

描述：返回从1970年01月01日0时0分0秒到当前时间的毫秒值，也可以返回从1970_01_01到指定日期的毫秒值。

案例：

#返回从1970_01_01到当前的毫秒值 
SELECT UNIX_TIMESTAMP();                      结果：1641559501 
#也可以返回从1970_01_01到指定日期的毫秒值 
SELECT UNIX_TIMESTAMP('2021.2.3 08:08:05');   结果：1612310885

格式：FROM_UNIXTIME

描述：将指定时间的毫秒值转为指定格式的日期。

案例：

#将毫秒值转为日期 
SELECT FROM_UNIXTIME(1641559501);                      结果：2022-01-07 20:45:01 
#将毫秒值转为自己指定的日期格式 
SELECT FROM_UNIXTIME(1641559501,'%Y-%m-%d %H:%i:%s');  结果：2022-01-07 20:45:01

格式：CURDATE
格式：CURRENT_DATE

描述：获取当前日期的年月日。

案例：

#获取当前日期的年月日 
SELECT CURDATE();      结果：2022-01-07 
SELECT CURRENT_DATE;   结果：2022-01-07

格式：CURTIME 格式：CURRENT_TIME

描述：获取当前的时间。

案例：

#获取当前日期的年月日 
#获取当前的时间 
SELECT CURTIME();      结果：21:07:57 
SELECT CURRENT_TIME;   结果：21:07:57

格式：NOW 格式：CURRENT_TIMESTAMP

描述：获取当前的年月日时分秒。

案例：

#获取当前的年月日时分秒 
SELECT CURRENT_TIMESTAMP;     结果:2022-01-07 21:10:41 
#获取当前的年月日时分秒 
SELECT NOW();                 结果:2022-01-07 21:10:41

格式：DATE

描述：获取字符串中的年月日。

案例：

#获取字符串中的年月日
SELECT DATE('2022-01-07 21:10:41'); 结果:2022-01-07

格式：DateDiff

描述：获取两个日期之间的差值。

案例：

#获取两个日期之间的差值。 
select DateDiff('2022-01-01','2021-12-03');    结果:29 
#获取当前时间与指定时间的插值。 
select DateDiff(CURRENT_DATE,'2021-12-03');    结果:35

格式：TIMEDIFF

描述：获取两个时间之间的差值。

案例：

#获取两个时间之间的差值。 
SELECT TIMEDIFF('12:12:34','10:18:56');      结果：01:53:38 
#获取当前时间与指定时间的差值。 
SELECT TIMEDIFF(CURRENT_TIME,'10:18:56');    结果：11:07:44

格式：DATE_FORMAT

描述：格式化日期格式。

案例：

#日期格式化。 
SELECT DATE_FORMAT('2021-1-1 1:1:1','%Y-%m-%d %H:%i:%s');         结果：2021-01-01 01:01:01 
SELECT DATE_FORMAT('2021-01-01 01:01:01','%Y-%m-%d %H:%i:%s');    结果：2021-01-01 01:01:01

格式：STR_TO_DATE

描述：字符串转为日期格式。

案例：

#字符串转为日期格式。 
SELECT STR_TO_DATE('2021-8-30 23:59:59', '%Y-%m-%d %H:%i:%s');   结果：021-08-30 23:59:59 
#与DATE_FORMAT不同的是可以将可以识别英文字符串格式的日期。 
SELECT STR_TO_DATE('August 10 2017', '%M%d%Y');                  结果:021-08-30 23:59:59

格式：LAST_DAY

描述：获取指定日期所在月的最后一天。

案例：

#字符串转为日期格式。 
SELECT LAST_DAY('2022-01-01');结果2022-01-31

格式：MAKEDATE

描述：给定年份以及该年份当中的天数获取时间。

案例：

SELECT MAKEDATE(2019,31); 结果:2019-01-31' 
SELECT MAKEDATE(2019,32); 结果:2019-02-01'

格式：DATE_ADD (date,INTERVAL expr type) 格式：DATE_SUB (date,INTERVAL expr type)

描述：date 参数是合法的日期表达式。INTERVAL 为固定写法，expr 参数是您希望添加的时间间隔。type 参数可以是下列值之一：

MICROSECOND，SECOND，MINUTE，HOUR，DAY，WEEK，MONTH，QUARTER，YEAR，SECOND_MICROSECOND，MINUTE_MICROSECOND，MINUTE_SECOND，HOUR_MICROSECOND，HOUR_SECOND，HOUR_MINUTE，DAY_MICROSECOND，DAY_SECOND，DAY_MINUTE，DAY_HOUR，YEAR_MONTH。

案例：

#将指定日期减去两天 
SELECT DATE_SUB('2022-01-01',INTERVAL 2 day);     结果：2021-12-30 
#将指定日期加去两天 
SELECT DATE_('2022-01-01',INTERVAL 2 day);        结果：2022-01-03 
#查询一天： 
select * from table where to_days(列名) = to_days(now()); 
select * from table where date(列名) = curdate(); 
#查询一周： 
select * from table where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(列名); 
#查询一个月： 
select * from table where DATE_SUB(CURDATE(), INTERVAL 1 MONTH) <= date(列名); 
#查询指定天数 
select * from table where DATE_SUB(CURDATE(), INTERVAL 2 DAY) <= date(列名);

格式：EXTRACT(unit FROM date)

描述：返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。有个更简单的格式例如：获取年 YEAR('2022-01-01') 结果：2022。date 参数是合法的日期表达式。FROM 固定写法，unit 参数可以是下列的值之一：

案例：

#返回日期/时间的单独部分 
SELECT EXTRACT(YEAR FROM '2008-11-11 13:23:44.657');    结果：2008

类型转换函数

格式：CAST (expression AS data_type)

描述：expression：任何有效的SQServer表达式。AS：用于分隔两个参数，在AS之前的是要处理的数据，在AS之后是要转换的数据类型。data_type：目标系统所提供的数据类型，包括bigint和sql_variant，不能使用用户定义的数据类型。

案例：

SELECT CAST('9.0' AS decimal)        结果：9
#(精度与小数位数分别为10与2。精度是总的数字位数，包括小数点左边和右边位数的总和。而小数位数是小数点右边的位数)
SELECT CAST('9.5' AS decimal(10,2))  结果：9.50
SELECT  CAST(NOW() AS   DATE)        结果：2017-11-27

格式：CONVERT(data_type(length) ,data_to_be_converted,style)

描述：该函数是把日期转换为新数据类型的通用函数并可以用不同的格式显示日期/时间数据。
data_type(length)规定目标数据类型（带有可选的长度）。
data_to_be_converted 含有需要转换的值。
style 规定日期/时间的输出格式。
可以使用的 style 值：

Style ID Style 格式
100 或者 0 mon dd yyyy hh:miAM （或者 PM）
101 mm/dd/yy
102 yy.mm.dd
103 dd/mm/yy
104 dd.mm.yy
105 dd-mm-yy
106 dd mon yy
107 Mon dd, yy
108 hh:mm:ss
109 或者 9 mon dd yyyy hh:mi:ss:mmmAM（或者 PM）
110 mm-dd-yy
111 yy/mm/dd
112 yymmdd
113 或者 13 dd mon yyyy hh:mm:ss:mmm(24h)
114 hh:mi:ss:mmm(24h)
120 或者 20 yyyy-mm-dd hh:mi:ss(24h)
121 或者 21 yyyy-mm-dd hh:mi:ss.mmm(24h)
126 yyyy-mm-ddThh:mm:ss.mmm（没有空格）
130 dd mon yyyy hh:mi:ss:mmmAM
131 dd/mm/yy hh:mi:ss:mmmAM

Style ID	Style 格式
100 或者 0	mon dd yyyy hh:miAM （或者 PM）
101	mm/dd/yy
102	yy.mm.dd
103	dd/mm/yy
104	dd.mm.yy
105	dd-mm-yy
106	dd mon yy
107	Mon dd, yy
108	hh:mm:ss
109 或者 9	mon dd yyyy hh:mi:ss:mmmAM（或者 PM）
110	mm-dd-yy
111	yy/mm/dd
112	yymmdd
113 或者 13	dd mon yyyy hh:mm:ss:mmm(24h)
114	hh:mi:ss:mmm(24h)
120 或者 20	yyyy-mm-dd hh:mi:ss(24h)
121 或者 21	yyyy-mm-dd hh:mi:ss.mmm(24h)
126	yyyy-mm-ddThh:mm:ss.mmm（没有空格）
130	dd mon yyyy hh:mi:ss:mmmAM
131	dd/mm/yy hh:mi:ss:mmmAM

案例：

CONVERT(VARCHAR(19),GETDATE())      结果：Dec 29 2008 11:45 PM
CONVERT(VARCHAR(10),GETDATE(),110)  结果：12-29-2008
CONVERT(VARCHAR(11),GETDATE(),106)  结果：29 Dec 08
CONVERT(VARCHAR(24),GETDATE(),113)  结果：29 Dec 2008 16:25:46.635

正则匹配

格式：REGEXP 'REGEXP操作符'

描述：MySQL中正则表达式通常被用来检索或替换符合某个模式的文本内容，根据指定的匹配模式匹配文中符合要求的特殊字符串。
例如，从一个文件中提取电话号码，查找一篇文章中重复的单词或替换用户输入的敏感语汇等，这些地方都可以使用正则表达式。正则表达式强大而且灵活，常用于复杂的查询。
MySQL 中使用 REGEXP 关键字指定正则表达式的字符匹配模式，下表列出了 REGEXP 操作符中常用的匹配列表。

选项说明例子匹配值示例
^ 匹配文本的开始字符 '^b' 匹配以字母 b 开头的字符串 book、big、banana、 bike
$ 匹配文本的结束字符 'st$’ 匹配以 st 结尾的字符串 test、resist、persist
. 匹配任何单个字符 'b.t’ 匹配任何 b 和 t 之间有一个字符 bit、bat、but、bite
* 匹配零个或多个在它前面的字符 'f*n’ 匹配字符 n 前面有任意个字符 f fn、fan、faan、abcn
+ 匹配前面的字符 1 次或多次 'ba+’ 匹配以 b 开头，后面至少紧跟一个 a ba、bay、bare、battle
<字符串> 匹配包含指定字符的文本 'fa’ fan、afa、faad
[字符集合] 匹配字符集合中的任何一个字符 '[xz]'匹配 x 或者 z dizzy、zebra、x-ray、 extra
[^] 匹配不在括号中的任何字符 '[^abc]’ 匹配任何不包含 a、b 或 c 的字符串 desk、fox、f8ke
字符串{n,} 匹配前面的字符串至少 n 次 b{2} 匹配 2 个或更多的 b bbb、 bbbb、 bbbbbbb
字符串{n,m} 匹配前面的字符串至少 n 次，至多 m 次 b{2,4} 匹配最少 2 个，最多 4 个 b bbb、 bbbb

选项	说明	例子	匹配值示例
^	匹配文本的开始字符	'^b' 匹配以字母 b 开头的字符串	book、big、banana、 bike
$	匹配文本的结束字符	'st$’ 匹配以 st 结尾的字符串	test、resist、persist
.	匹配任何单个字符	'b.t’ 匹配任何 b 和 t 之间有一个字符	bit、bat、but、bite
*	匹配零个或多个在它前面的字符	'f*n’ 匹配字符 n 前面有任意个字符 f	fn、fan、faan、abcn
+	匹配前面的字符 1 次或多次	'ba+’ 匹配以 b 开头，后面至少紧跟一个 a	ba、bay、bare、battle
<字符串>	匹配包含指定字符的文本	'fa’	fan、afa、faad
[字符集合]	匹配字符集合中的任何一个字符	'[xz]'匹配 x 或者 z	dizzy、zebra、x-ray、 extra
[^]	匹配不在括号中的任何字符	'[^abc]’ 匹配任何不包含 a、b 或 c 的字符串	desk、fox、f8ke
字符串{n,}	匹配前面的字符串至少 n 次	b{2} 匹配 2 个或更多的 b	bbb、 bbbb、 bbbbbbb
字符串{n,m}	匹配前面的字符串至少 n 次，至多 m 次	b{2,4} 匹配最少 2 个，最多 4 个 b	bbb、 bbbb

查询该列名是否满足该操作符 
SELECT * FROM 表名 WHERE 列名 REGEXP 'REGEXP操作符'

关键字

格式：INSERT INTO … SELECT … WHERE NOT EXIST ...

描述：这种方式适合于插入的数据字段没有设置主键或唯一索引，当插入一条数据时，首先判断MySQL数据库中是否存在这条数据，如果不存在，则正常插入，如果存在，则忽略

案例：

insert into user (username,sex,address) select 'wuke','男'，'西安' from user 
WHERE NOT EXIST (select username from user where username = 'wuke')

格式：INSERT INTO ... SELECT

描述：将一张表的数据导入另外一张表中，就可以使用INSERT INTO SELECT语句。注意

案例：

insert into table2 (字段1，字段2..) select value1,value2... from table1  #注意要求table2必须存在。
或者
insert into table2 select * from table1                                 #注意要求table2必须存在。

格式：REPLACE INTO

描述：向数据库中插入数据，前提是重复的不能再次插入，replace具备替换拥有唯一索引或者主键索引重复数据的能力，也就是如果使用replace into插入的数据的唯一索引或者主键索引与之前的数据有重复的情况，将会删除原先的数据，然后再进行添加。

案例：

向table表中col1, col2, col3列replace数据val1，val2，val3
replace into table( col1, col2, col3 ) values ( val1, val2, val3 )

表数据,ID为主键

格式：INSERT IGNORE

描述：批量插入数据忽略失败的数据行，适用于批量插入大量的数据,其中的某些错,可以忽略不记的情况。

案例：

当我们执行一下插入语句时:
INSERT INTO house_user （id，house_id，user_id，create_time，type） VALUES 
(1，12347，12357，'2017-04-28'，2)， (4，1234，1235，'2017-04-28'，2)；

结果：报错了一个主键冲突的错,1确实主键冲突了,其实4主键没有冲突,
这时我们想将不冲突的数据依然插入到数据库,可以执行以下的语句:
INSERT IGNORE INTO house_user （id，house_id，user_id，create_time，type） VALUES 
(1，12347，12357，'2017-04-28'，2)， (4，1234，1235，'2017-04-28'，2)；

我们可以看到,插入成功了,并影响了一行数据，我们看下数据表: id等于4的这条数据已经插入进来了。

格式：INSERT ... ON DUPLICATE KEY UPDATE

描述：如果数据库已经存在同一主键的记录，则执行update操作，如果不存在，则执行insert操作。业务层一般做法是先查询，如果不存在再插入，如果存在则更新，但是查询和插入不是原子性操作，在并发量比较高的时候，可能两个线程都查询某个记录不存在，所以会执行两次插入，然后其中一条必然会因为主键（这里说的主键不是递增主键）冲突而失败。
数据库层mysql中INSERT ... ON DUPLICATE KEY UPDATE就可以做这个事情，并且是原子性操作。

案例：

1，单条记录下使用
INSERT INTO t1 (a,b,c) VALUES (1,2,3)ON DUPLICATE KEY UPDATE c = c+1;
如上sql假如t1表的主键或者UNIQUE 索引是a，那么当执行上面sql时候，如果数据库里面已经存在a=1的记录则更新这条记录
的c字段的值为原来值+1，然后返回值为2。如果不存在则插入a=1,b=2,c=3到数据库，然后返回值为1。

2，多记录下使用
INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c= VALUES (c);

格式：WITH ROLLUP

描述：对分组之后的数据进行再一次统计。

案例：

表结构：
CREATE TABLE `test` ( 
`Id` int(11) NOT NULL AUTO_INCREMENT, 
`title` varchar(25) DEFAULT NULL COMMENT '标题', 
`uid` int(11) DEFAULT NULL COMMENT 'uid', 
`money` decimal(2,0) DEFAULT '0', 
`name` varchar(25) DEFAULT NULL, PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;

表数据：
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('2', '国庆节', '2', '12', '周伯通'); 
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('3', '这次是8天假哦', '3', '33', '老顽童'); 
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('4', '这是Uid=1的第一条数据哦', '1', '70', '欧阳锋'); 
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('5', '灵白山少主', '4', '99', '欧阳克'); 
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('7', '九阴真经创始人', '3', '12', '小顽童'); 
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('8', '双手互博', '2', '56', '周伯通'); 
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('9', '销魂掌', '2', '19', '周伯通'); 
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('10', '蛤蟆功', '1', '57', '欧阳锋'); 
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('11', '绝杀掌', '3', '800', '小顽童'); 
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('12', '九阴真经', '3', '84', '老顽童');

#执行SQL：
SELECT name, SUM(money) as money FROM test GROUP BY name WITH ROLLUP;

执行结果:

格式：GROUPING（）

描述：MySQL 8.0 新增了 GROUPING() 函数，通常用于与用来WITH ROLLUP搭配来使用，理清 GROUP BY WITH ROLLUP 子句检索后所产生的每个分组汇>总结果。

Grouping :指示是否聚合 GROUP BY 列表中的指定列表达式。在结果集中，如果 GROUPING 返回 1 则指示聚合；>返回 0 则指示不聚合。如果指定了 GROUP BY，则 GROUPING 只能用在 SELECT 列表、HAVING 和 ORDER BY 子句中。

案例：

构建表结构
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; 
DROP TABLE IF EXISTS `depart`; 
CREATE TABLE `depart` ( 
`id` int NOT NULL AUTO_INCREMENT COMMENT '部门表主键ID', 
`depart_name` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部门', 
`staff_name` char(6) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '员工', 
`salary` int NULL DEFAULT NULL COMMENT '工资', PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'GROUP BY 的修饰符with rollup 以及grouping函数的使用' ROW_FORMAT = Dynamic; 

插入数据
INSERT INTO `depart` VALUES (1, '部门A', 'ZHANG', 100); 
INSERT INTO `depart` VALUES (2, '部门A', 'LI', 200); 
INSERT INTO `depart` VALUES (3, '部门A', 'WANG', 300); 
INSERT INTO `depart` VALUES (4, '部门A', 'ZHAO', 400); 
INSERT INTO `depart` VALUES (5, '部门A', 'DUAN', 500); 
INSERT INTO `depart` VALUES (6, '部门B', 'DUAN', 600); 
INSERT INTO `depart` VALUES (7, '部门B', 'DUAN', 700); 
SET FOREIGN_KEY_CHECKS = 1;

表数据展示：

SELECT id,depart_name,staff_name,SUM(salary) as total FROM depart GROUP BY depart_name,staff_name;

执行结果：

添加WITH ROLLUP后再执行语句：

SELECT id,depart_name,staff_name,SUM(salary) as total FROM depart GROUP BY depart_name,staff_name WITH ROLLUP;

执行结果：

红色框和蓝色框数据都是由于使用了WITH ROLLUP修饰符而新增的行

红色框中的数据是对各个部门数据的总计，蓝色框中的数据是对红色框数据的进一步总计，那么要是想知道哪行数据是使用了WITH ROLLUP修饰符而新增的行，此时就得使用Grouping函数

执行语句：

#字符串转为日期格式。 
SELECT id, depart_name, staff_name, SUM( salary ) AS total, grouping ( staff_name ) AS 'Grouping' 
FROM DEPART 
GROUP BY depart_name,staff_name WITH ROLLUP

执行结果：

此时我们可以看见凡是使用了WITH ROLLUP修饰符而新增的行，后面Grouping都是1，那么就可以用在HAVING语句中，用去选取或去掉合计值，例如：

#选取所有统计结果 
SELECT id, depart_name, staff_name, SUM( salary ) AS total FROM DEPART 
GROUP BY depart_name,staff_name WITH ROLLUP HAVING GROUPING(staff_name)=1;

执行结果：

执行语句：

#去掉统计结果 
SELECT id, depart_name, staff_name, SUM( salary ) AS total FROM 
DEPART GROUP BY depart_name,staff_name WITH ROLLUP HAVING GROUPING(staff_name)=0;

执行结果：

执行SQL：

#提取最后一行的统计结果 
SELECT id, depart_name, staff_name, SUM( salary ) AS total FROM DEPART 
GROUP BY depart_name,staff_name WITH ROLLUP HAVING GROUPING(depart_name)=1;

执行结果：

UNION & INTERSECT & EXCEPT

SQL组合查询可以使用以下关键字进行操作：

UNION(并集):比较两个查询的结果，返回两个集合所有非重复行。
INTERSECT(交集):比较两个查询的结果，返回由左右双侧输入查询输出的非重复行。
EXCEPT(差集):比较两个查询的结果，返回左侧查询集合中不包含左右集合交集部分的非重复行。
注意:使用 EXCEPT 或 INTERSECT 的两个查询的结果集组合起来的基本规则:所有查询中的列数和列的顺序必须相同；数据类型必须兼容。

--创建数据表T1 CREATE TABLE T1( A int NULL, B int NULL, C int NULL ); 
--创建数据表T2 CREATE TABLE T2( A int NULL, B int NULL, C int NULL ); 
--T1表插入基础数据 
INSERT INTO T1 VALUES(1,2,3); 
INSERT INTO T1 VALUES(2,3,4); 
INSERT INTO T1 VALUES(3,4,5); 
INSERT INTO T1 VALUES(4,5,6); 
INSERT INTO T1 VALUES(5,6,7); 
--T2表插入基础数据 
INSERT INTO T2 VALUES(3,4,5); 
INSERT INTO T2 VALUES(5,6,7); 
INSERT INTO T2 VALUES(6,7,8); 
INSERT INTO T2 VALUES(7,8,9);

UNION(并集)

# 查询T1和T2的所有数据，去重复（情况一）：
SELECT * FROM T1 UNION SELECT * FROM T2;

查询结果：

UNIONALL(全集)

# 查询T1和T2的所有数据，不去重复，在UNION关键字后面加上ALL，（情况二）：
SELECT * FROM T1 UNION ALL SELECT * FROM T2;

查询结果：

INTERSECT(交集)：

# 查询T1和T2的交集
SELECT * FROM T1 INTERSECT SELECT * FROM T2;

查询结果：

EXCEPT(差集)：

#查询T1和T2的差集：
SELECT * FROM T1 EXCEPT SELECT * FROM T2;

查询结果：

子查询

语法格式

SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table);

括号内的查询叫做子查询，也叫内部查询，先于主查询执行。
子查询的结果被主查询（外部查询）使用
expr operator包括比较运算符。
单行运算符：>、=、>=、<、<>、<=
多行运算符：IN、ANY、ALL
子查询可以嵌于以下SQL语句中
- WHERE子句中
- HAVING子句中
- FROM子句中
子查询的使用指导
- 子查询要用括号括起来
- 将子查询放在比较运算符的右边
- 对于单行子查询要使用单行运算符
- 对于多行子查询要使用多行运算符
子查询的类型
- 单行子查询
- 多行子查询
- 多列子查询

二、单行子查询

子查询只返回一行一列

SELECT ename,job,sal FROM emp WHERE sal = (SELECT sal FROM emp WHERE empno = 7396);

使用单行运算符

# 显示和雇员7396从事相同工作并且工资大于雇员7876的员工姓名和工作 
SELECT ename, job FROM emp WHERE job = (SELECT job FROM emp WHERE empno = 7369) 
AND sal > (SELECT sal FROM emp WHERE empno = 7876);

在子查询中使用组函数

#查询工资最低的员工姓名，岗位及工资 
SELECT ename,job,sal FROM emp WHERE sal = (SELECT MIN(sal) FROM emp);

HAVING子句中使用子查询

#查询部门最低工资比20部门最低工资高的部门编号及最低工资 
SELECT deptno,MIN(sal) FROM emp GROUP BY deptno HAVING MIN(sal) 
> (SELECT MIN(sal) FROM emp WHERE deptno = 20); 

#查询哪个部门的员工人数高于各部门的平均人数。 
SELECT deptno,COUNT(empno) FROM emp GROUP BY deptno HAVING COUNT(empno) 
> (SELECT AVG(COUNT(empno)) FROM emp GROUP BY deptno);

三、多行子查询

子查询返回记录的条数，可以是一条或者多条，和多行子查询进行比较时，需要使用多行操作符，多行操作符包括： IN , ANY , ALL , EXISTS , SOME [SOME与ANY的作用是一致的，SOME可以理解为ANY的别名。]

IN使用

IN操作符和以前介绍的功能一致，判断是否与子查询的任意一个返回值相同。

#查询是经理的员工姓名，工资 
SELECT ename,sal FROM emp WHERE empno IN (SELECT mgr FROM emp);

ANY的使用: 表示和子查询的任意一个结果进行比较，有一个满足条件即可。
- "< ANY"：表示小于子查询结果集中的任意一个，即小于最大值就可以。
- "> ANY"：表示大于子查询结果集中的任意一个，即大于最小值就可以。
- "= ANY"：表示等于子查询结果集中的任意一个，即等于谁都可以，相当于IN。

#查询是经理的员工姓名，工资 
SELECT ename,sal FROM emp WHERE empno = ANY (SELECT mgr FROM emp); 

#查询部门编号不为10，且工资比10部门任意一名员工工资高的员工编号，姓名，职位，工资。 
SELECT empno,name,job,sal FROM emp WHERE sal > ANY(SELECT sal FROM emp WHERE deptno = 10) AND deptno <>10;

ALL的使用：表示和子查询的所有行结果进行比较，每一行都必须满足条件
- "< ALL"：表示小于子查询结果集中的所有行，即小于最小值。
- "> ALL"：表示大于子查询结果集中的所有行，即大于最大值。
- "= ANL"：表示等于子查询结果集中的所有行，即等于所有值，通常来说没有什么实际意义。

#查询部门编号不为10，且工资比10部门任意一名员工工资高的员工编号，姓名，职位，工资。 
SELECT empno,name,job,sal FROM emp WHERE sal > ALL(SELECT sal FROM emp WHERE deptno = 10) AND deptno <>10; 

#查询部门编号不为10，且工资和10部门任意一名员工工资相同的员工编号，姓名，职位，工资。 
SELECT empno,name,job,sal FROM emp WHERE sal = ALL(SELECT sal FROM emp WHERE deptno = 10) AND deptno <> 10; 

#查询部门编号不为10，且工资比10部门任意一名员工工资低的员工编号，姓名，职位，工资。 
SELECT empno,name,job,sal FROM emp WHERE sal < ALL(SELECT sal FROM emp WHERE deptno = 10) AND deptno <> 10;

EXISTS的使用：

#格式： SELECT * FROM ... WHERE ... EXISTS(查询语句)

特点：

- 判断子查询结果中"如果有数据"(至少返回一行)，则EXISTS()的结果为"true"，外层查询继续执行。
- 判断子查询结果中"如果没有数据"，则EXISTS()的结果为"false"，外层查询不会执行。
- EXISTS关键字后面的子查询不返回任何实际数据，指挥返回"真"或者"假",当返回"真"时，WHERE条件成立。
- 注意：EXISTS关键字比IN关键字效率要高，因此在实际开发当中，特别是数据量比较大的时候，推荐使用EXISTS

#查询公司是否有大于60岁的员工，有则输出。 
SELECT * FROM emp3 e WHERE EXISTS(SELECT age FROM emp3 WHERE e.age > 60);

四、多列子查询

之前讲的子查询都是在一个条件表达式内和子查询的一个列进行比较，多列子查询可以在一个条件表达式内同时和子查询的多个列进行比较。
多列子查询通常用IN操作符完成。

#查询出和1981年入职的任意一个员工的部门和职位完全相同的员工姓名、部门、职位、入职日期，不包括1981年入职员工。 
SELECT ename,deptno,job,hiredate FROM emp WHERE (deptno, job) IN (SELECT deptno,job FROM emp WHERE to_char(hiredate,'YYYY')='1981') AND to_char(hiredate,'YYYY')<>'1981'; 

#查询出和1981年入职的任意员工的部门或职位相同的员工姓名、部门、职位、入职日期，不包括1981年入职员工。
SELECT ename,deptno,job,hiredate FROM emp WHERE (deptno, job) IN (SELECT deptno,job FROM emp WHERE to_char(hiredate,'YYYY')='1981') AND to_char(hiredate,'YYYY')<>'1981';

五、子查询中的空值

所有的条件和空值进行比较结果都是空值。
因此，无论什么时候只要空值有可能成为子查询结果集中的一部分，就不能使用 NOT IN 操作符。

六、在FROM子句中使用子查询

#查询比自己部门高平均工资高的员工姓名，工资，部门编号，部门平均工资。 
SELECT a.ename,a.sal,a.empno,b.salavg FROM emp a, (SELECT deptno,avg(sal) salavg FROM emp GROUP BY deptno) b WHERE a.deptno = b.deptno AND a.sal > b.salavg; 
#总结：在FROM子句中使用子查询，这个子查询相当于一张表的作用

窗口函数

语法结构：

格式：window_function (expr) OVER (

PARTITION BY ...

ORDER BY ...

frame_clause

):

描述：其中，window_function是窗口函数的名称；expr是参数，有些函数不需要参数；ORDER字句包含三个选项：

分区（PARTITION BY）：PARTITION BY选项用于将数据行拆分为多个分区（组），他的作用类似于GROUP BY分组。如果省略了PARTITION BY，所有的数据作为一个组进行计算。
排序（ORDER BY）：OVER子句中的ORDER BY选项用于指定分区内的排序方式，与ORDER BY子句的作用类似。
窗口大小（frame_clause）:frame_clause选项用于在当前分区内指定一个计算窗口，也就是一个与当前行相关联的数据子集。

序号函数 ROW_NUMBER，RANK，DENSE_RANK

准备数据：

ROW_NUMBER

#查询，每个部门的员工按照薪资降序，并给出排名 
SELECT dname,ename,salary, ROW_NUMBER() OVER(PARTITION BY dname ORDER BY salary DESC) as rn FROM employee;

执行结果：

rn多出来的排序列就是ROW_NUMBER() OVER(PARTITION BY dname ORDER BY salary DESC) as rn这段代码形成的。

注意：庞统与关羽的工资相同，但rn列却不同，不太符合我们的排序。

RANK

#查询，每个部门的员工按照薪资降序，并给出排名 
SELECT dname,ename,salary, RANK() OVER(PARTITION BY dname ORDER BY salary DESC) as rn FROM employee;

执行结果：

注意：rn列没有5；不太符合我们的排序。

DENSE_RANK

#查询，每个部门的员工按照薪资降序，并给出排名 
SELECT dname,ename,salary, DENSE_RANK() OVER(PARTITION BY dname ORDER BY salary DESC) as rn FROM employee;

执行结果：

挺符合我们的实际开发。

如果不加PARTITION BY dname则会全局排序:

#进行全局排序不进行分组 
SELECT dname,ename,salary, DENSE_RANK() OVER(ORDER BY salary DESC) as rn FROM employee;

执行结果：

开窗聚合函数--SUM，AVG，MIN，MAX

在窗口每条记录动态的记录应用聚合函数，可以动态的记录在指定窗口内的各种聚合函数值。

执行SQL：

SELECT dname,ename,salary, SUM(salary) OVER(PARTITION BY dname ORDER BY salary DESC) as '总计' FROM employee;

执行结果：

其它的就不做例子，使用都差不多其实以上的 SQL 都省略了ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 的写法，这句话的意思是从分组后的第一行开始到当前行。

ROWS BETWEEN 3 PRECEDING AND CURRENT ROW的案例：

执行SQL：

SELECT dname,ename,salary, SUM(salary) OVER(PARTITION BY dname ORDER BY salary DESC ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) as '总计' FROM employee;

执行结果：

该结果就会展示当前行的工资 + 上面三行的工资 = 总计，由于第一行没有前三行，则会只计算当前行即可。

ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING的案例:

执行SQL:

SELECT dname,ename,salary, SUM(salary) OVER(PARTITION BY dname ORDER BY salary DESC ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) as '总计' FROM employee;

执行结果：

该结果就会展示当前行的工资 + 上面三行的工资 + 下面一行的工资= 总计，由于第一行没有前三行，则会只计算当前行工资 + 下面一行的工资即可。

ROWS BETWEEN current ROW AND UNBOUNDED FOLLOWING的案例：

执行SQL:

SELECT dname,ename,salary, SUM(salary) OVER(PARTITION BY dname ORDER BY salary DESC ROWS BETWEEN current ROW AND UNBOUNDED FOLLOWING) as '总计' FROM employee;

执行结果：

该结果就会显示当前行的工资 + 下面的所有行的工资 = 总计

开窗分布函数--CUME_DIS，PERCENT_RANK(了解即可)

CUME_DIST：小于等于当前值的行数/分组内总行数

描述：通过 PARTITION BY 列名将划分为分区函数应用到的 FROM 子句生成的结果集。如果未指定，则此函数将查询结果集的所有行视为单个组。 order by 列名确定在其中执行该操作的逻辑顺序。 ORDER_BY_列名 是必需的。

返回类型：CUME_DIST 返回的值范围大于 0 并小于或等于 1的数值。

执行SQL：

SELECT dname,ename,salary, CUME_DIST() OVER(ORDER BY salary) AS '全比例',
    #全比例计算方式：小于等于当前行的工资的数据行数除以全部记录数 
    #例如： 小于等于8000的数据有12行，总行数为14行，则‘全比例’ = 12 / 14 = 0.8571428571428571     
    CUME_DIST() OVER(PARTITION BY dname ORDER BY salary DESC) as '组比例' 
    #组计算方式：同一个组内大于等于当前行的工资的数据行数除以同一个组内的全部记录数 
    #例如： 研发部大于等于8000的数据有2行，研发部总行数为7行，则‘组比例’ = 2 / 7 =
    0.2857142857142857 
FROM employee; 
    #注意：大于小于与ORDER BY有关,‘组比例’还是‘全比例’与PARTITION BY有关

执行结果：