SQL——SQL语句总结(6)
常用函数
常用函数分为单行函数和聚合函数
1.数学函数
数学函数主要用来处理数值数据，主要的数学函数有：绝对值函数、三角函数（包括正弦函数、余弦函数、正切函数、余切函数等）、对数函数、随机数函数等。有错误产生时，数学函数将会返回空值null。
| 函数名 | 描述 | 实例 | 结果 |
| pi() | 返回圆周率的值 | seelct pi(); | 3.141593 |
| abs(x) | 返回x的绝对值 | select abs(-1005); | 1005 |
| sqrt(x) | 返回非负数 x 的二次方根 | select sqrt(29); | 5.385164807134504 |
| pow(x,y) | 返回 x 的 y 次乘方 | select pow(9, 5); | 59049 |
| ceiling(x) | 返回大于 x 的最小整数值 | select ceiling(1005.5); | 1006 |
| floor(x) | 返回小于 x 的最大整数值 | select floor(1005.6); | 1005 |
| mod(x,y) | 返回x/y的模（余数） | select mod(10.52,3); | 1.52 |
| round(x,y) |
返回参数 x 的四舍五入的 有 y 位小数的值 |
select round(10.05,2); |
10.05 |
| truncate(x,y) | 返回数字 x 截断为 y 位小数的结果 | select truncate(1.00591,4); | 1.0059 |
| rand() |
返回0到1内的随机值，可以通过提供一个参数（种子）使rand()随机数生成一个指定的值 |
select rand(); select rand(10); |
0.9522263807730995 0.6570515219653505 |
2.字符串函数
字符串函数主要用来处理数据库中的字符串数据，MySQL 中字符串函数有：计算字符串长度函数、字符串合并函数、字符串替换函数、字符串比较函数】查找指定字符串位置函数等。
| 函数名 | 描述 | 示例 | 结果 |
| char_length(str) | 返回字符串str所含的字符个数 | select char_length('你好 xz'); | 5 |
| length(str) | 返回字符串str中的字符的字节长度 | select length('你好 xz'); | 9 |
| ascii(char) | 返回字符的ASCII码值 | select ascii('z'); | 122 |
| concat(s1,s2..,sn) | 将s1,s2...,sn连接成一个字符串 | select concat('I ', 'am ' ,'fine'); | I am fine |
| concat_ws(sep,s1,s2...,sn) | 将s1,s2...,sn连接成一个字符串，并用sep字符间隔 | select concat_ws('/', '2020','12','4'); | 2020/12/4 |
| insert(str,x,y,instr) | 将字符串str从第x位置开始的y个字符长的字符串替换为字符串instr，返回结果 | select insert('you me',3,3,'and'); | yoande |
| lcase(str) 或 lower(str) | 返回字符串str中所有字符改变为小写后的结果 |
select lcase('china'); select lower('china'); |
china china |
| ucase(str) 或 upper(str) | 返回字符串str中所有字符改变为大写后的结果 |
select ucase('china'); select upper('china'); |
CHINA CHINA |
| left(str, x) | 返回将字符串str中最左边的 x 个字符 | select left('hello', 2); | he |
| right(str, x) | 返回将字符串str中最右边的 x 个字符 | select right('hello', 2); | lo |
| ltrim(str) | 从字符串 str 中去掉开头的空格 |
select ltrim(' seanxiao') |
seanxiao |
| instr(str,substr) | 返回字符串 substr 在字符串 str 第一个出现的位置 | select instr('seanxiao', 'ao'); | 7 |
| position(substr in str) | 返回字符串 substr 在字符串 str 第一个出现的位置 | select position('i' in 'seanxiao'); | 6 |
| reverse(str) | 返回颠倒字符串str的值 | select reverse('oaixnaes'); | seanxiao |
| strcmp(s1, s2) | 比较字符串s1和s2,所有字符均相同，返回0；第一个小于第二个，返回-1，其他返回1 | select strcmp('xz', 'ab'); | 1 |
| trim(str) | 去除字符串首部和尾部的所有空格 | select trim(' me '); | me |
|
lpad(st, len, padstr) rpad(st, len, padstr) |
用字符串 padstr 填补 st 左或右端，直到字符串长度为len |
select lpad('seanxiao', 10, '*' ); select rpad('seanxiao', 10, '*'); |
**seanxiao seanxiao** |
| substring(str, n, len) | 从 str 字符串的第 n 个位置截取 len 长度个字符。n 若为负值，则从末尾倒数 |
select substring('seanxiao', -2, 3); |
ao |
| replace(str, s1, s2) | 使用字符串s2替代字符串 str 中所有的字符串s1 | select replace('xxx.mysql.com', 'x', 'w'); | www.mysql.com |
| space(n) | 返回一个由 n 个空格组成的字符串 | select concat('*', space(6), '*'); | * * |
3.日期和时间函数
日期和时间函数主要用来处理日期和时间值，一般大额日期函数除了使用 date 类型的参数外，也可以使用 datetime 或者 timestamp 类型的参数，但是忽略这些值的时间部分。相同的，以 time 类型值为参数的函数，可以接受 timestamp 类型的参数，但会忽略日期部分，许多日期函数可以同时接受数字和字符串类型的两种参数。
| 函数名 | 描述 | 实例 | 结果 |
| curdate()或current_date()或current_date | 返回当前的日期 |
select current_date(); current_date(); |
2020-12-04| 2020-12-04 |
| curtime()或current_time() | 返回当前的时间 | select curtime(),current_time(); | 10:48:22| 10:48:22 |
| now() | 返回当前时间和日期 | select now(); | 2020-12-04 10:49:39 |
| date_add(date,interval int keyword) | 返回日期date加上间隔时间的结果（int 必须按照关键字进行格式化） |
select date_add(current_date,interval 6 month); |
2021-06-04 |
| dayofweek(date) | 返回date所代表的一个星期中的第几天（1~7） | select dayofweek('1991-10-05'); | 7 |
| dayofmonth(date) | 返回date是一个月的第几天（1~31） | select dayofmonth('1999-09-15'); | 15 |
| dayofyear(date) |
返回date是一年的第几天（1~366） |
select dayofyear('1991-10-05'); | 278 |
| monthname(date) | 返回date是几月（按英文名返回） | select monthname('1991-10-05'); | October |
| dayname(date) | 返回date的星期名 | select dayname(current_date); | Friday |
| weekday(date) | 返回日期date是星期几（0 =星期一，以此类推） | select weekday(current_date); | 4 |
| week(date) | 返回日期date为一年中第几周（0~53） | select week(current_date); | 48 |
| year(date) | 返回日期date的年份（1000~9999） | select year(current_date); | 2020 |
| hour(time) | 返回日期time的最小值（0~23） | select hour(current_time); | 11 |
| date_format(date, fmt) | 依照指定的fmt格式格式化日期date值 | select date_format('2020-12-04', '%w %m %y'); | 5 12 20 |
date_format(date, fmt)函数中的fmt字符串中可用的格式
| 格式 | 描述 |
| %a | 缩写星期名 |
| %b | 缩写月名 |
| %c | 月，数值 |
| %D | 带有英文前缀的月中的天 |
| %d | 月的天，数值（00-31） |
| %e | 月的天，数值（0-31） |
| %f | 微秒 |
| %H | 小时（00-23） |
| %h | 小时（01-12） |
| %I | 小时（01-12） |
| %i | 分钟，数值（00-59） |
| %j | 年的天（001-366） |
| %k | 小时（0-23） |
| %l | 小时（1-12） |
| %M | 月名 |
| %m | 月，数值（00-12） |
| %p | AM或PM |
| %r | 时间，12-小时（hh:mm:ss AM 或 PM） |
| %S | 秒（00-59） |
| %s | 秒（00-59） |
| %T | 时间，24-小时（hh:mm:ss） |
| %U | 周（00-53）星期日是一周的第一天 |
| %u | 周（00-53）星期一是一周的第一天 |
| %V | 周（01-53）星期日是一周的第一天，与%X使用 |
| %v | 周（01-53）星期一是一周的第一天，与%x使用 |
| %W | 星期名 |
| %w | 周的天（0=星期天，6=星期六） |
| %X | 年，其中的星期日是周的第一天，4位，与%V使用 |
| %x | 年，其中的星期一是周的第一天，4位，与%v使用 |
| %Y | 年，4位 |
| %y | 年，2位 |
4.转换函数
使用类型转换函数可以在各种类型数据之间转换数据类型。MySQL中常有的转换函数有cast() 和 convert() 函数，可用来获取一个类型的值，并产生另一个类型的值。
1)cast(value as type)，即cast(xxx as 类型)
2)convert(value, type) ，即convert(xxx, 类型)
5.聚合函数
select子句的表达式中可以包含聚合函数。聚合函数常常用于对一组值进行计算，然后返回单个值。
| 函数名 | 描述 | 实例 |
| avg(all | distinct | col) | 返回指定字段的平均值 | select avg(age) from stu; |
| count(all | distinct | col | *) | 返回指定字段中非null值的个数 | select count(*) from stu; |
| min(all | distinct | col) | 返回指定字段的最小值 | select min(age) from stu; |
| max(all | distinct | col) | 返回指定字段的最大值 | select max(age) from stu; |
| sum(all | distinct | col) | 返回指定字段所有的所有值 | select sum(score) from stu; |
| group_concat(col) | 返回由属于一组的字段值连接组合而成的结果 | select class, group_concat(name) from stu group by class; |
6.条件判断函数
条件判断函数也称为流程流程函数，根据满足的条件的不同，执行相关的流程。
(1) if(expr,v1,v2)函数
如果表达式expr是true(expr != 0 and expr != null )，if() 的返回值为v1；否则返回值为v2。if() 的返回值为数值或字符串值，具体情况是其所在语境而定。
(2) ifunll(v1,v2)函数
假如 v1 不为 null，则 ifnull() 的返回值为 v1；否则其返回值为 v2。ifnull() 的返回值是数字或字符串，具体情况取决于其所在的语境。
