字符串函数
CONCAT(str1,str2,…)
把多个文本字符串合并成一个长字符串(参数中有null时返回null)
示例
mysql> select concat('CDA','数据', '分析');
+------------------------------+
| concat('CDA','数据', '分析') |
+------------------------------+
| CDA数据分析 |
+------------------------------+
mysql> select concat('CDA',null, '分析');
+----------------------------+
| concat('CDA',null, '分析') |
+----------------------------+
| NULL |
+----------------------------+
LENGTH(str)
返回字符串str的长度
示例
mysql> select length('CDA数据分析');
+-----------------------+
| length('CDA数据分析') |
+-----------------------+
| 11 |
+-----------------------+
INSTR(str,substr)
返回子字符串substr在文本字符串str中第一次出现的位置(文本字符串中不包含该子字符串时返回0)
示例
mysql> select instr('CDA', 'A');
+-------------------+
| instr('CDA', 'A') |
+-------------------+
| 3 |
+-------------------+
mysql> select instr('数据分析', 'CDA');
+--------------------------+
| instr('数据分析', 'CDA') |
+--------------------------+
| 0 |
+--------------------------+
LEFT(str,len)
返回字符串str的左端len个字符
示例
mysql> select left('CDA数据分析', 3);
+------------------------+
| left('CDA数据分析', 3) |
+------------------------+
| CDA |
+------------------------+
RIGHT(str,len)
返回字符串str的右端len个字符
示例
mysql> select right('CDA数据分析', 4);
+-------------------------+
| right('CDA数据分析', 4) |
+-------------------------+
| 数据分析 |
+-------------------------+
MID(str,pos,len)
返回字符串str的位置pos起len个字符
示例
mysql> select mid('CDA数据分析', 4, 2);
+--------------------------+
| mid('CDA数据分析', 4, 2) |
+--------------------------+
| 数据 |
+--------------------------+
SUBSTRING ( expression, start, length )
截取字符串
- expression:字符串、二进制字符串、文本、图像、列或包含列的表达式。请勿使用包含聚合函数的表达式。
- start:整数或可以隐式转换为int 的表达式,指定子字符串的开始位置。
- length:整数或可以隐式转换为 int 的表达式,指定子字符串的长度。
示例
mysql> select substring('CDA数据分析',1,3);
+------------------------------+
| substring('CDA数据分析',1,3) |
+------------------------------+
| CDA |
+------------------------------+
LTRIM(str)
返回删除了左空格的字符串str
示例
mysql> select ltrim(' CDA数据分析')
+-------------------------+
| ltrim(' CDA数据分析') |
+-------------------------+
| CDA数据分析 |
+-------------------------+
RTRIM(str)
返回删除了右空格的字符串str
示例
mysql> select rtrim('CDA数据分析 ');
+--------------------------+
| rtrim('CDA数据分析 ') |
+--------------------------+
| CDA数据分析 |
+--------------------------+
TRIM(str)
返回删除了两边空格的字符串str
示例
mysql> select trim(' CDA数据分析 ');
+------------------------------+
| trim(' CDA数据分析 ') |
+------------------------------+
| CDA数据分析 |
+------------------------------+
REPLACE(str,from_str,to_str)
用字符串to_str替换字符串str中的子串from_str并返回
示例
mysql> select replace('CDA数据分析', 'CDA', 'cda');
+--------------------------------------+
| replace('CDA数据分析', 'CDA', 'cda') |
+--------------------------------------+
| cda数据分析 |
+--------------------------------------+
REPEAT(str,count)
返回由count个字符串str连成的一个字符串(任何参数为null时返回null,count<=0时返回一个空字符串)
示例
mysql> select repeat('CDA', 3);
+------------------+
| repeat('CDA', 3) |
+------------------+
| CDACDACDA |
+------------------+
REVERSE(str)
颠倒字符串str的字符顺序并返回
示例
mysql> select reverse('CDA');
+----------------+
| reverse('CDA') |
+----------------+
| ADC |
+----------------+
UPPER(str)
返回大写的字符串str
示例
mysql> select upper('cda');
+--------------+
| upper('cda') |
+--------------+
| CDA |
+--------------+
LOWER(str)
返回大写的字符串str
示例
mysql> select lower('CDA');
+-----------------+
| lower('CDA') |
+-------------- --+
| CDA |
+-----------------+
数学函数
ABS(n)
返回n的绝对值
示例
mysql> select abs(-32);
+----------+
| abs(-32) |
+----------+
| 32 |
+----------+
FLOOR(n)
返回不大于n的最大整数值
示例
mysql> select floor(1.23);
+-------------+
| floor(1.23) |
+-------------+
| 1 |
+-------------+
CEILING(n)
返回不小于n的最小整数值
示例
mysql> select ceiling(1.23);
+---------------+
| ceiling(1.23) |
+---------------+
| 2 |
+---------------+
ROUND(n,d)
返回n的四舍五入值,保留d位小数(d的默认值为0)
示例
mysql> select round(1.58);
+-------------+
| round(1.58) |
+-------------+
| 2 |
+-------------+
RAND(n)
返回在范围0到1.0内的随机浮点值(可以使用数字n作为初始值)
示例
mysql> select rand();
+--------------------+
| rand() |
+--------------------+
| 0.8111234054072978 |
+--------------------+
mysql> select rand(2);
+--------------------+
| rand(2) |
+--------------------+
| 0.6555866465490187 |
+--------------------+
时期时间函数
DAY(date)
返回指定日期的日(范围在1到31)
示例
mysql> select day('2020-01-01');
+---------------------+
| day('2020-01-01') |
+---------------------+
| 1 |
+---------------------+
HOUR(datetime)
返回指定时间的小时(范围在0-23)
示例
mysql> select hour('2020-01-01 12:00:00');
+-----------------------------+
| hour('2020-01-01 12:00:00') |
+-----------------------------+
| 12 |
+-----------------------------+
DATE(date)
返回指定日期/时间表达式的日期部分或将文本转为日期格式
示例
mysql> select date('20200101');
+------------------+
| date('20200101') |
+------------------+
| 2020-01-01 |
+------------------+
MONTH(date)
返回指定日期的月份
示例
mysql> select month('2020-01-01');
+---------------------+
| month('2020-01-01') |
+---------------------+
| 1 |
+---------------------+
QUARTER(date)
返回指定日期是一年的第几个季度
示例
mysql> select quarter('2020-01-01');
+-----------------------+
| quarter('2020-01-01') |
+-----------------------+
| 1 |
+-----------------------+
YEAR(date)
返回指定日期的年份(范围在1000到9999)
示例
mysql> select year('20-01-01');
+------------------+
| year('20-01-01') |
+------------------+
| 2020 |
+------------------+
DATE_ADD(date,interval expr type)
ADDDATE(date,interval expr type)
DATE_SUB(date,interval expr type)
SUBDATE(date,interval expr type)
对日期时间进行加减运算
- date是一个datetime或date值
- expr对date进行加减法的一个表达式字符串
- type指明表达式expr应该如何被解释
type值 | 含义 | 期望的expr格式 |
---|---|---|
second | 秒 | seconds |
minute | 分钟 | minutes |
hour | 时间 | hours |
day | 天 | days |
month | 月 | months |
year | 年 | years |
minute_second | 分钟和秒 | “minutes:seconds” |
hour_minute | 小时和分钟 | “hours:minutes” |
day_hour | 天和小时 | “days hours” |
year_month | 年和月 | “years-months” |
hour_second | 小时, 分钟 | “hours:minutes:seconds” |
day_minute | 天, 小时, 分钟 | “days hours:minutes” |
day_second | 天, 小时, 分钟, 秒 | “days hours:minutes:seconds” |
示例
mysql> select date_add("2020-01-01",interval 1 day);
+---------------------------------------+
| date_add("2020-01-01",interval 1 day) |
+---------------------------------------+
| 2020-01-02 |
+---------------------------------------+
mysql> select date_sub("2020-01-01", interval 1 day);
+----------------------------------------+
| date_sub("2020-01-01", interval 1 day) |
+----------------------------------------+
| 2019-12-31 |
+----------------------------------------+
DATE_FORMAT(date,format)
根据format字符串格式化date值
在format字符串中可用标志符
- %m 月名字(january……december)
- %Y 年, 数字, 4 位
- %y 年, 数字, 2 位
- %a 缩写的星期名字(sun……sat)
- %d 月份中的天数, 数字(00……31)
- %e 月份中的天数, 数字(0……31)
- %m 月, 数字(01……12)
- %c 月, 数字(1……12)
- %b 缩写的月份名字(jan……dec)
- %j 一年中的天数(001……366)
- %h 小时(00……23)
- %k 小时(0……23)
- %i 分钟, 数字(00……59)
- %r 时间,12 小时(hh:mm:ss [ap]m)
- %t 时间,24 小时(hh:mm:ss)
- %s 秒(00……59)
- %p am或pm
- %w 一个星期中的天数(0=sunday ……6=saturday )
- %u 周数(0……52), 这里星期天是星期的第一天
示例
mysql> select date_format('2019-01-01','%Y-%m-%d');
+--------------------------------------+
| date_format('2020-01-01','%Y-%m-%d') |
+--------------------------------------+
| 2020-01-01 |
+--------------------------------------+
CURDATE()
以’yyyy-mm-dd’或yyyymmdd格式返回当前日期值(根据返回值所处上下文是字符串或数字)
示例
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2020-01-01 |
+------------+
mysql> select curdate() + 0;
+---------------+
| curdate() + 0 |
+---------------+
| 20200101 |
+---------------+
CURTIME()
以’hh:mm:ss’或hhmmss格式返回当前时间值(根据返回值所处上下文是字符串或数字)
示例
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 12:00:00 |
+-----------+
mysql> select curtime() + 0;
+---------------+
| curtime() + 0 |
+---------------+
| 120000 |
+---------------+
NOW()
以’yyyy-mm-dd hh:mm:ss’或yyyymmddhhmmss格式返回当前日期时间(根据返回值所处上下文是字符串或数字)
示例
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2020-01-01 12:00:00 |
+---------------------+
mysql> select now() + 0;
+----------------+
| now() + 0 |
+----------------+
| 20200101120000 |
+----------------+
TIMESTAMPDIFF(type,expr1,expr2)
返回起始日expr1和结束日expr2之间的时间差整数
时间差的单位由type指定:
- second 秒
- minute 分
- hour 时
- day 天
- month 月
- year 年
示例
mysql> select timestampdiff(month,'2020-01-01','2020-03-22');
+------------------------------------------------+
| timestampdiff(month,'2020-01-01','2020-03-22') |
+------------------------------------------------+
| 2 |
+------------------------------------------------+
UNIX_TIMESTAMP()
返回一个unix时间戳(从’1970-01-01 00:00:00’开始的秒数,date默认值为当前时间)
示例
mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
| 1564576533 |
+------------------+
mysql> select unix_timestamp('2020-01-01');
+------------------------------+
| unix_timestamp('2020-01-01') |
+------------------------------+
| 1577808000 |
+------------------------------+
FROM_UNIXTIME(unix_timestamp)
以’yyyy-mm-dd hh:mm:ss’或yyyymmddhhmmss格式返回时间戳的值(根据返回值所处上下文是字符串或数字)
示例
mysql> select from_unixtime(1577808000);
+---------------------------+
| from_unixtime(1577808000) |
+---------------------------+
| 2020-01-01 00:00:00 |
+---------------------------+
mysql> select from_unixtime(1577808000) + 0;
+-------------------------------+
| from_unixtime(1577808000) + 0 |
+-------------------------------+
| 20200101000000 |
+-------------------------------+
分组合并函数
GROUP_CANCAT([distinct] str [order by str asc/desc] [separator])
将group by产生的同一个分组中的值连接起来,返回一个字符串结果。
示例:查询每个部门的员工姓名
mysql> select deptno,group_concat(ename)
-> from emp
-> group by deptno;
+--------+--------------------------------------+
| deptno | group_concat(ename) |
+--------+--------------------------------------+
| 10 | clark,king,miller |
| 20 | smith,jones,scott,adams,ford |
| 30 | allen,ward,martin,blake,turner,james |
+--------+--------------------------------------+
逻辑函数
IFNULL(expression, alt_value)
判断第一个表达式是否为 NULL,如果为 NULL 则返回第二个参数的值,如果不为 NULL 则返回第一个参数的值。
示例:
mysql> select ifnull(comm,0)
-> from emp;
+----------------+
| ifnull(comm,0) |
+----------------+
| 0 |
| 300 |
| 500 |
| 0 |
| 1400 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
+----------------+
IF(expr1,expr2,expr3)
如果expr1的值为true,则返回expr2的值,如果expr1的值为false,则返回expr3的值。
示例:
mysql> select ename,sal,if(sal>=3000,'高',if(sal>=1500,'中','低')) 工资级别
-> from emp;
+--------+------+--------------+
| ename | sal | 工资级别 |
+--------+------+--------------+
| smith | 800 | 低 |
| allen | 1600 | 中 |
| ward | 1250 | 低 |
| jones | 2975 | 中 |
| martin | 1250 | 低 |
| blake | 2850 | 中 |
| clark | 2450 | 中 |
| scott | 3000 | 高 |
| king | 5000 | 高 |
| turner | 1500 | 中 |
| adams | 1100 | 低 |
| james | 950 | 低 |
| ford | 3000 | 高 |
| miller | 1300 | 低 |
+--------+------+--------------+
CASE WHEN expr1 THEN expr2 [WHEN expr3 THEN expr4…ELSE expr] END
如果expr1的值为true,则返回expr2的值,如果expr3的值为false,则返回expr4的值…
示例:
mysql> select ename,sal,case when sal>=3000 then '高' when sal>=1500 then '中' else '低' end 工资级别
-> from emp;
+--------+------+--------------+
| ename | sal | 工资级别 |
+--------+------+--------------+
| smith | 800 | 低 |
| allen | 1600 | 中 |
| ward | 1250 | 低 |
| jones | 2975 | 中 |
| martin | 1250 | 低 |
| blake | 2850 | 中 |
| clark | 2450 | 中 |
| scott | 3000 | 高 |
| king | 5000 | 高 |
| turner | 1500 | 中 |
| adams | 1100 | 低 |
| james | 950 | 低 |
| ford | 3000 | 高 |
| miller | 1300 | 低 |
+--------+------+--------------+