- 详情可查看数据库8.0开发文档：dev.mysql.com/doc/refman/…
字符函数
LENGTH（）
描述：获取字节个数（utf-8 一个汉字为3个字节，gbk为2个字节）。
案例：
SELECT LENGTH('ijkio'); --结果: 5 SELECT LENGTH('产生的z'); --结果: 10
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');
SUBSTR (str,pos);
描述：截取字符串。
str:要裁剪的字符串；pos：从哪个位置开始；len:裁剪几位。SUBSTRING同理。案例：
SELECT SUBSTR('apple',2); --结果：pple SELECT SUBSTR('apple',4,1); --结果：l
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'); --无结果
UPPER ();/LOWER ();
描述：将字母转大小写。
案例：
SELECT UPPER('ok') --结果: OK SELECT LOWER('JB') --结果: jb
UPPER ();/LOWER ();
格式：
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删除指定的尾字符
L(R)PAD (str,len,padstr);
格式：
LPAD (str,len,padstr):左填充
格式：RPAD (str,len,padstr):右填充
格式：RTRIM ()
描述：用指定字符实现填充指定长度。str:要处理的对象。len:处理完后的str长度为len。padstr:如果str的长度小于len指定的值，那么长度差由padstr在左边填充；如果str的长度大于len指定的值，则截取str到len指定的长度。返回值说明：这两个函数返回的是处理后的str。案例：
SELECT LPAD('wu',10,'-'); 结果：--------wu SELECT RPAD('wu',10,'-'); 结果：wu--------
CONCAT (str1,str2,…);
格式：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,…);
格式：CONCAT (str1,str2,…)
描述：separator是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。如果分隔符为NULL，则结果为NULL。函数会忽略任何分隔符参数后的NULL值。CONCAT_WS()不会忽略任何空字符串。(然而会忽略所有的NULL）。案例：
SELECT CONCAT_WS ("，", "SQL", "Tutorial", "is", "fun!")AS Str; --结果：SQL,Tutorial,is,fun!
GROUP_CONCAT(字段);
格式：
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;
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);
数学函数
ABS (val)
格式：CONCAT (str1,str2,…)
描述：取绝对值。案例：
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) --结果2 SELECT CEIL(9.9) --结果10
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
POW (val，num)
描述：圆周率
案例：
SELECT PI() --结果3.14.593
RAND ()
描述：
RAND()函数主要有两个用处：1.是产生随机数， 2.是随机排序（在数据较大的时候会变成性能杀手）。案例：
--产生一个随机数，默认0~1之间的浮点数 SELECT RAND() --参数指定范围的的随机数，比如 500~5000 SELECT FLOOR(500 + (RAND() * 4500)) --随机排序，比如我有一张文章表，我要随机抽取5篇文章 SELECT * FROM content ORDER BY RAND();
控制流函数
IF(expr1,expr2,expr3)；
格式：
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的值。
IFNULL(expr1,expr2)；
格式：
IFNULL (expr1,expr2)；
描述：如果expr1不为NULL，则IFNULL()的返回值为expr1; 否则其返回值为expr2。IFNULL()的返回值是数字或是字符串，具体情况取决于其所使用的语境。案例：
SELECT IFNULL(1,10); #结果--->1 SELECT IFNULL(NULL,10); #结果--->10
NULLIF (expr1,expr2)；
格式：
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
ISNULL (expr)；
格式：
ISNULL (expr)；
描述：判断expr是否为NULL。不是NULL返回1，是NULL返回0;案例：
SELECT ISNULL(10); #结果--->0 SELECT ISNULL(NULL); #结果--->1
COALESCE (a,b,c);
格式：
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
CASE WHEN;
格式：
CASE expression WHEN condition1 THEN result1 WHEN condition2 THEN result2； ... ELSEresult 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
聚合函数
AVG()；
描述：返回数值列的平均值。
案例：
SELECT AVG(列名) 别名 FROM 表名
COUNT()；
描述：返回匹配指定条件的行数（
NULL不计入）。案例：
SELECT COUNT(*) 别名 FROM 表名
COUNT()；
描述：返回指定列的最大值。
案例：
SELECT MAX(列名) 别名 FROM 表名；
MIN ()；
描述：返回指定列的最小值。
案例：
SELECT MIN(列名) 别名 FROM 表名；
SUM ()；
描述：返回指定列的和。
案例：
SELECT SUM(列名) 别名 FROM 表名
GREATEST (value1,value2,...)/LEAST (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参数可以是下列的值之一： 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 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 值：
案例：
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 操作符中常用的匹配列表。
案例：
-查询该列名是否满足该操作符 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)
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就可以做这个事情，并且是原子性操作。案例：
--单条记录下使用 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。 --多记录下使用 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()
描述：
MySQL8.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 & UNION ALL
UNION: 返回两个结果集的并集并取去重。
UNION All:返回两个结果集的并集包含重复列。
注意:UNION内部的SELECT语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时每条SELECT语句中的列的顺序必须相同。总的来说，union去重且排序，union all不去重不排序，因此，union all的执行效率比union高。在确定不需要去除重复项的前提下，优先选择union all。语法：
select * from 表1 union all select * from 表2;
递归查询
前提Mysql8.1以上的版本才会支持递归查询
+--------+----------+------------+ | cat_id | name | parent_cid | +--------+----------+------------+ | 12 | 美妆 | 0 | | 4 | 服装 | 0 | | 5 | 女装 | 4 | | 6 | 男装 | 4 | | 7 | 童装 | 4 | | 19 | 美容美体 | 12 | | 18 | 彩妆 | 12 | | 13 | 护肤 | 12 | | 15 | 护肤套装 | 13 | | 40 | 防晒 | 13 | | 39 | 卸妆 | 13 | | 38 | 润唇膏 | 13 | | 17 | 乳液面霜 | 13 | | 16 | 面膜 | 13 | | 14 | 化妆水 | 13 | +--------+----------+------------+1/查询出“服装”分类下的所有子分类
with recursive type_cte as ( select * from t_category where cat_id = 4 union all select t.* from t_category t inner join type_cte type_cte2 on t.parent_cid = type_cte2.cat_id ) select cat_id, name, parent_cid from type_cte执行结果
+--------+------+------------+ | cat_id | name | parent_cid | +--------+------+------------+ | 4 | 服装 | 0 | | 5 | 女装 | 4 | | 6 | 男装 | 4 | | 7 | 童装 | 4 | +--------+------+------------+2/查询出所有“美妆”分类下的所有子分类，并且分类名称上带上分类的名称。
with recursive type_cte as ( select cat_id,name,parent_cid from t_category where cat_id = 12 union all select t.cat_id,concat(type_cte2.name,'>',t.name),t.parent_cid from t_category t inner join type_cte type_cte2 on t.parent_cid = type_cte2.cat_id ) select cat_id, name, parent_cid from type_cte;执行结果:
+--------+------------------------+------------+ | cat_id | name | parent_cid | +--------+------------------------+------------+ | 12 | 美妆 | 0 | | 13 | 美妆>护肤 | 12 | | 18 | 美妆>彩妆 | 12 | | 19 | 美妆>美容美体 | 12 | | 14 | 美妆>护肤>化妆水 | 13 | | 15 | 美妆>护肤>护肤套装 | 13 | | 16 | 美妆>护肤>面膜 | 13 | | 17 | 美妆>护肤>乳液面霜 | 13 | | 35 | 美妆>护肤>洁面 | 13 | | 36 | 美妆>护肤>精华 | 13 | | 37 | 美妆>护肤>眼霜 | 13 | | 38 | 美妆>护肤>润唇膏 | 13 | | 39 | 美妆>护肤>卸妆 | 13 | | 40 | 美妆>护肤>防晒 | 13 | +--------+------------------------+------------+3/查询所有的父级分类
with recursive type_cte as ( select cat_id,name,parent_cid from t_category where cat_id = 40 union all select t.cat_id,concat(type_cte2.name,'>',t.name),t.parent_cid from t_category t inner join type_cte type_cte2 on t.cat_id = type_cte2.parent_cid ) select cat_id, name, parent_cid from type_cte;执行结果:
+--------+----------------+------------+ | cat_id | name | parent_cid | +--------+----------------+------------+ | 40 | 防晒 | 13 | | 13 | 防晒>护肤 | 12 | | 12 | 防晒>护肤>美妆 | 0 | +--------+----------------+------------+
子查询
子查询就是一个
SELECT语句的结果作为另外一个SQL语句（主查询）的数据来源和判断条件。子查询可以嵌入SELECT、INSERT、UPDATE和DELETE语句中，也可以和=、<、>、IN、BETWEEN、EXISTS等运算符一起使用。案例：
select * from test; +----+--------+------+------+ | id | name | sex | age | +----+--------+------+------+ | 1 | name1 | 女 | 15 | | 2 | name1 | 女 | 15 | | 4 | name2 | 男 | 30 | | 5 | name50 | 男 | 12 | +----+--------+------+------+
- in 关键字
in关键字在子查询中主要用在列子查询中代替人为手罗列出来的多个“字面值”数据。举例：
select * from 表名 where 地区 in ('上海', '北京', ...); select * from 表名 where 地区 in (select 地区列表 from 地图表数据);
- any关键字
any可以与(=、>、>=、<、<=、<>)结合起来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的任何一个数据。
any关键字用在比较操作符(=、>、>=、<、<=、<>...)的后面，表示查询结果的多个数据中的任一个满足该比较操作符就算满足。
语法形式:
select * from 表名 where 字段名=any(必须为子查询); 结果相当于： select * from 表名 where 字段名=值1 or 字段名=值2 or 字段名=值3 or ...;注意：any()括号不可以写成固定值，必须为子查询，以下这种写法是错误的：
select * from 表名 where 字段名=any(1, 2, 3); 举例: select * from 表名 where 字段名=any(select 字段名 from 表名);
- some关键字
some 的作用与使用跟 any 一样，两者没差别。
- all 关键字
all可以与(=、>、>=、<、<=、<>)结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有数据。
all关键字用在比较操作符(=、>、>=、<、<=、<>...)的后面，表示查询结果的多个数据中的所有都满足该比较操作符才算满足。语法形式:
select * from 表名 where 字段名>all(必须为子查询); 结果相当于： select * from 表名 where 字段名>值1 and 字段名>值2 and 字段名>值3 and ...;注意：
all()括号不可以写成固定值，必须为子查询，这种写法是错误的：select * from 表名 where 字段名>all(1, 2, 3); select * from 表名 where 字段名>all(select 字段名 from 表名);
-exists 关键字
该子查询如果“有数据”则该
exists()的结果为true即相当于where true。
该子查询如果“没有数据”则该exists()的结果为false即相当于where false。案例：
select * from 表名 where exists(任何子查询);
