MySQL常用函数介绍

一、操作符优先级

　　　　下面展示了所有操作符的执行优先级，从高到低，同一行中的操作符优先级相同，相同优先级的情况下从左到右执行。

INTERVAL

BINARY, COLLATE

!

- (unary minus), ~ (unary bit inversion)

^

*, /, DIV, %, MOD

-, +

<<, >>

&

|

= (comparison), <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN

BETWEEN, CASE, WHEN, THEN, ELSE

NOT

AND, &&

XOR

OR, ||

= (assignment), :=

如果想改变优先级执行顺序，则可以使用括号。

mysql> select 1+2*3;

+-------+

| 1+2*3 |

+-------+

|     7 |

+-------+

1 row in set (0.00 sec)

 

mysql> select (1+2)*3;

+---------+

| (1+2)*3 |

+---------+

|       9 |

+---------+

1 row in set (0.00 sec)

二、比较函数

名称	描述
>	大于运算符
>=	大于或等于运算符
<	少于运算符
<>， !=	不等于运算符
<=	小于或等于运算符
<=>	NULL安全等于运算符
=	平等算子
BETWEEN ... AND ...	值是否在值范围内
COALESCE()	返回第一个非NULL参数
GREATEST()	返回最大参数
IN()	一个值是否在一组值内
INTERVAL()	返回小于第一个参数的参数的索引
IS	针对布尔值测试值
IS NOT	针对布尔值测试值
IS NOT NULL	非空值测试
IS NULL	空值测试
ISNULL()	测试参数是否为NULL
LEAST()	返回最小的参数
LIKE	简单模式匹配
NOT BETWEEN ... AND ...	值是否不在值范围内
NOT IN()	一个值是否不在一组值内
NOT LIKE	否定简单模式匹配
STRCMP()	比较两个字符串

对比操作符的执行结果为true，false，null三种。
between A and B代表检查值是否在A和B之间。

mysql> select * from students;

+-----+-------+--------+---------+

| sid | sname | gender | dept_id |

+-----+-------+--------+---------+

|   1 | a     | 3      |       4 |

|   4 | c     | 3      |       1 |

|   5 | d     | 1      |       2 |

|   6 | e     | 2      |       4 |

+-----+-------+--------+---------+

4 rows in set (0.00 sec)

 

mysql> select * from students where sid between 3 and 5;##该语句相当于下面的那个语句。

+-----+-------+--------+---------+

| sid | sname | gender | dept_id |

+-----+-------+--------+---------+

|   4 | c     | 3      |       1 |

|   5 | d     | 1      |       2 |

+-----+-------+--------+---------+

2 rows in set (0.00 sec)

 

mysql> select * from students where sid>=3 and sid<=5;

+-----+-------+--------+---------+

| sid | sname | gender | dept_id |

+-----+-------+--------+---------+

|   4 | c     | 3      |       1 |

|   5 | d     | 1      |       2 |

+-----+-------+--------+---------+

2 rows in set (0.00 sec)

COALESCE(value,...)对比操作符

　　返回第一个非NULL的值，如果没有非null值，则返回NULL

mysql> SELECT COALESCE(NULL,2);

 -> 2

mysql> SELECT COALESCE(NULL,NULL,NULL);

 -> NULL

=代表相等操作符。
>代表大于操作符。
>=代表大于等于操作符。
greatest()代表返回最大的值。

mysql> select greatest(1,5,10,2);

+--------------------+

| greatest(1,5,10,2) |

+--------------------+

|                 10 |

+--------------------+

1 row in set (0.00 sec)

lnterval()代表返回比第一个参数小的参数的位置。
is/is not代表检查值是否与布尔值相同/不同。

mysql> SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN;

+-----------+------------+-----------------+

| 1 IS TRUE | 0 IS FALSE | NULL IS UNKNOWN |

+-----------+------------+-----------------+

|         1 |          1 |               1 |

+-----------+------------+-----------------+

1 row in set (0.00 sec)

 

mysql> SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN;

+------------------+------------------+---------------------+

| 1 IS NOT UNKNOWN | 0 IS NOT UNKNOWN | NULL IS NOT UNKNOWN |

+------------------+------------------+---------------------+

|                1 |                1 |                   0 |

+------------------+------------------+---------------------+

1 row in set (0.00 sec)

is not null代表检查值是否是非NULL。

mysql> select * from students;

+-----+--------+--------+---------+---------------------+

| sid | sname  | gender | dept_id | brithday            |

+-----+--------+--------+---------+---------------------+

|   1 | Andrew | 0      |       1 | 1983-01-01 00:00:00 |

|   2 | Andy   | 0      |       1 | 1983-01-01 00:00:00 |

|   3 | Bob    | 0      |       1 | 1983-01-01 00:00:00 |

|   4 | Ruth   | 1      |       2 | 1983-01-01 00:00:00 |

|   5 | Mike   | 0      |       2 | 1986-01-01 00:00:00 |

|   6 | John   | 0      |       3 | 1986-01-01 00:00:00 |

|   7 | Cindy  | 1      |       3 | 1986-01-01 00:00:00 |

|   8 | Susan  | 1      |       3 | 1986-01-01 00:00:00 |

|   9 | NULL   | 0      |       1 | NULL                |

+-----+--------+--------+---------+---------------------+

9 rows in set (0.00 sec)

 

mysql> select * from students where sname is not null;

+-----+--------+--------+---------+---------------------+

| sid | sname  | gender | dept_id | brithday            |

+-----+--------+--------+---------+---------------------+

|   1 | Andrew | 0      |       1 | 1983-01-01 00:00:00 |

|   2 | Andy   | 0      |       1 | 1983-01-01 00:00:00 |

|   3 | Bob    | 0      |       1 | 1983-01-01 00:00:00 |

|   4 | Ruth   | 1      |       2 | 1983-01-01 00:00:00 |

|   5 | Mike   | 0      |       2 | 1986-01-01 00:00:00 |

|   6 | John   | 0      |       3 | 1986-01-01 00:00:00 |

|   7 | Cindy  | 1      |       3 | 1986-01-01 00:00:00 |

|   8 | Susan  | 1      |       3 | 1986-01-01 00:00:00 |

+-----+--------+--------+---------+---------------------+

8 rows in set (0.00 sec)

is null代表检查值是否是NULL。

mysql> select * from students;

+-----+--------+--------+---------+---------------------+

| sid | sname  | gender | dept_id | brithday            |

+-----+--------+--------+---------+---------------------+

|   1 | Andrew | 0      |       1 | 1983-01-01 00:00:00 |

|   2 | Andy   | 0      |       1 | 1983-01-01 00:00:00 |

|   3 | Bob    | 0      |       1 | 1983-01-01 00:00:00 |

|   4 | Ruth   | 1      |       2 | 1983-01-01 00:00:00 |

|   5 | Mike   | 0      |       2 | 1986-01-01 00:00:00 |

|   6 | John   | 0      |       3 | 1986-01-01 00:00:00 |

|   7 | Cindy  | 1      |       3 | 1986-01-01 00:00:00 |

|   8 | Susan  | 1      |       3 | 1986-01-01 00:00:00 |

|   9 | NULL   | 0      |       1 | NULL                |

+-----+--------+--------+---------+---------------------+

9 rows in set (0.00 sec)

 

mysql> select * from students where sname='null';

Empty set (0.00 sec)

 

mysql> select * from students where sname is null;

+-----+-------+--------+---------+----------+

| sid | sname | gender | dept_id | brithday |

+-----+-------+--------+---------+----------+

|   9 | NULL  | 0      |       1 | NULL     |

+-----+-------+--------+---------+----------+

1 row in set (0.00 sec)

<代表小于操作符。

<=代表小于等于操作符。

like代表字符匹配。

not between A and B代表检查值是否不在A和B的范围之内。

!=/<>代表不等于操作符。

mysql> select * from students;

+-----+-------+--------+---------+

| sid | sname | gender | dept_id |

+-----+-------+--------+---------+

|   1 | a     | 3      |       4 |

|   4 | c     | 3      |       1 |

|   5 | d     | 1      |       2 |

|   6 | e     | 2      |       4 |

+-----+-------+--------+---------+

4 rows in set (0.00 sec)

 

mysql> select * from students where sid!=6;

+-----+-------+--------+---------+

| sid | sname | gender | dept_id |

+-----+-------+--------+---------+

|   1 | a     | 3      |       4 |

|   4 | c     | 3      |       1 |

|   5 | d     | 1      |       2 |

+-----+-------+--------+---------+

3 rows in set (0.00 sec)

 

mysql> select * from students where sid<>6;

+-----+-------+--------+---------+

| sid | sname | gender | dept_id |

+-----+-------+--------+---------+

|   1 | a     | 3      |       4 |

|   4 | c     | 3      |       1 |

|   5 | d     | 1      |       2 |

+-----+-------+--------+---------+

3 rows in set (0.00 sec)

not in()代表检查值是否不在一系列值的当中。
not like代表检查值是否不匹配。
strcmp()对比两个字符串。
=号对比操作符。

mysql> select true;##在mysql中真用数字1表示。

+------+

| TRUE |

+------+

|    1 |

+------+

1 row in set (0.00 sec)

 

mysql> select false;##在mysql中假用数字0表示。

+-------+

| FALSE |

+-------+

|     0 |

+-------+

1 row in set (0.00 sec)

 

mysql> select 1=0;

+-----+

| 1=0 |

+-----+

|   0 |

+-----+

1 row in set (0.01 sec)

 

mysql> select 0=0;

+-----+

| 0=0 |

+-----+

|   1 |

+-----+

1 row in set (0.00 sec)

expr between min and max对比操作符。

　　如果expr大于或等于min且expr小于或等于 max，则BETWEEN返回1，否则返回0。如果所有参数均为同一类型。(min <= expr AND expr <= max)

mysql> SELECT 2 BETWEEN 1 AND 3, 2 BETWEEN 3 and 1;

    -> 1, 0

mysql> SELECT 1 BETWEEN 2 AND 3;

    -> 0

mysql> SELECT 'b' BETWEEN 'a' AND 'c';

    -> 1

mysql> SELECT 2 BETWEEN 2 AND '3';

    -> 1

mysql> SELECT 2 BETWEEN 2 AND 'x-3';

    -> 0

expr NOT BETWEEN min AND max

　　相当于NOT (expr BETWEEN min AND max)

expr IN (value,...)对比操作符。

　　当expr值能在values中找到，则返回1，否则返回0。

mysql> SELECT 2 IN (0,3,5,7);

    -> 0

mysql> SELECT 'wefwf' IN ('wee','wefwf','weg');

    -> 1

mysql> SELECT (3,4) IN ((1,2), (3,4));

    -> 1

mysql> SELECT (3,4) IN ((1,2), (3,5));

    -> 0

 

mysql> select * from students;

+-----+-------+--------+---------+

| sid | sname | gender | dept_id |

+-----+-------+--------+---------+

|   1 | a     | 3      |       4 |

|   4 | c     | 3      |       1 |

|   5 | d     | 1      |       2 |

|   6 | e     | 2      |       4 |

+-----+-------+--------+---------+

4 rows in set (0.00 sec)

 

mysql> select * from students where sid in (1,2,5,7,8);##要筛选的sid需要满足（1，2，5，7，8）中的任意一个。

+-----+-------+--------+---------+

| sid | sname | gender | dept_id |

+-----+-------+--------+---------+

|   1 | a     | 3      |       4 |

|   5 | d     | 1      |       2 |

+-----+-------+--------+---------+

2 rows in set (0.00 sec)

expr not IN (value,...)对比操作符。
lsnull(expr)操作符

　　　　如果expr是null，则返回1，否则返回0、

mysql> select isnull(1+1);

+-------------+

| isnull(1+1) |

+-------------+

|           0 |

+-------------+

1 row in set (0.00 sec)

 

mysql> select isnull(1/0);

+-------------+

| isnull(1/0) |

+-------------+

|           1 |

+-------------+

1 row in set (0.00 sec)

least(value1,value2,...)

　　　　返回最小值，如果其中有值为null，则返回null。

mysql> select least(0,-1,2);

+---------------+

| least(0,-1,2) |

+---------------+

|            -1 |

+---------------+

1 row in set (0.00 sec)

三、逻辑操作符

逻辑操作符返回1（TRUE）,0（FALSE）,或者NULL。

name	description
AND，&&	logical and
NOT，!	negates value
\|\|，OR	logical or
XOR	logical xor

NOT,!逻辑操作符代表非操作。

mysql> SELECT NOT 10;

 -> 0

mysql> SELECT NOT 0;

 -> 1

mysql> SELECT NOT NULL;

 -> NULL

mysql> SELECT ! (1+1);

 -> 0

mysql> SELECT ! 1+1;

 -> 1

And,&&逻辑操作符。

mysql> select * from students;

+-----+-------+--------+---------+

| sid | sname | gender | dept_id |

+-----+-------+--------+---------+

|   1 | a     | 3      |       4 |

|   4 | c     | 3      |       1 |

|   5 | d     | 1      |       2 |

|   6 | e     | 2      |       4 |

+-----+-------+--------+---------+

4 rows in set (0.00 sec)

 

mysql> select * from students where sid>3 and sid<7;

+-----+-------+--------+---------+

| sid | sname | gender | dept_id |

+-----+-------+--------+---------+

|   4 | c     | 3      |       1 |

|   5 | d     | 1      |       2 |

|   6 | e     | 2      |       4 |

+-----+-------+--------+---------+

3 rows in set (0.00 sec)

||，OR

mysql> select * from students;

+-----+-------+--------+---------+

| sid | sname | gender | dept_id |

+-----+-------+--------+---------+

|   1 | a     | 3      |       4 |

|   4 | c     | 3      |       1 |

|   5 | d     | 1      |       2 |

|   6 | e     | 2      |       4 |

+-----+-------+--------+---------+

4 rows in set (0.00 sec)

 

mysql> select * from students where sid>3 or sid<7;

+-----+-------+--------+---------+

| sid | sname | gender | dept_id |

+-----+-------+--------+---------+

|   1 | a     | 3      |       4 |

|   4 | c     | 3      |       1 |

|   5 | d     | 1      |       2 |

|   6 | e     | 2      |       4 |

+-----+-------+--------+---------+

4 rows in set (0.00 sec)

 

mysql> select * from students where sid>3 or sid<7 and sid>5;##由于and的优先级高于or，所以先执行后半部分。

+-----+-------+--------+---------+

| sid | sname | gender | dept_id |

+-----+-------+--------+---------+

|   4 | c     | 3      |       1 |

|   5 | d     | 1      |       2 |

|   6 | e     | 2      |       4 |

+-----+-------+--------+---------+

3 rows in set (0.00 sec)

Xor逻辑操作符

　　逻辑异或。NULL如果任一操作数为，则返回NULL。对于非NULL操作数，计算 1出奇数个操作数是否为非零，否则0返回。====>>>>不理解

https://dev.mysql.com/doc/refman/5.7/en/logical-operators.html#operator_xor

mysql> SELECT 1 XOR 1;

    -> 0

mysql> SELECT 1 XOR 0;

    -> 1

mysql> SELECT 1 XOR NULL;

    -> NULL

mysql> SELECT 1 XOR 1 XOR 1;

    -> 1

第1章分配操作符

l 分配操作符是指赋值操作

name	description
=	assign a value(as part of a set statement,or as part of the set clause in an update statement)
:=	assign a value

mysql> select @a,@b;##默认值是空的。

+------+------+

| @a | @b |

+------+------+

| NULL | NULL |

+------+------+

1 row in set (0.01 sec)

mysql> select @a:=10,@b;##通过“:=”赋值a的值是10。

+--------+------+

| @a:=10 | @b |

+--------+------+

| 10 | NULL |

+--------+------+

1 row in set (0.00 sec)

mysql> select @a;##查询a的值。

+------+

| @a |

+------+

| 10 |

+------+

1 row in set (0.00 sec)

mysql> set @a=17;##也可以使用set命令来赋值。

Query OK, 0 rows affected (0.00 sec)

mysql> select @a;

+------+

| @a |

+------+

| 17 |

+------+

1 row in set (0.00 sec)

l =操作符在两种情况下会被认为是赋值操作，而其他情况下会认为是对比操作符

在set语句中，=操作符会被认为是赋值操作

mysql> set @a=1;

mysql> select @a;

+------+

| @a |

+------+

| 1 |

l 在update语句中的set子句中，=操作符会被认为是赋值操作

一、算数运算符

名称	描述
%， MOD	模运算符
*	乘法运算符
+	加法运算符
-	减号
-	更改参数的符号
/	除法
DIV	整数除法

/代表除法

mysql> SELECT 3/5;

    -> 0.60

mysql> SELECT 102/(1-1);

    -> NULL

div代表整数型除法，相除之后只取整数部分

mysql> SELECT 5 DIV 2, -5 DIV 2, 5 DIV -2, -5 DIV -2;
    -> 2, -2, -2, 2

MOD(N,M), N % M, N MOD M

N % M，N MOD M模运算。返回N除以M的余数。一般用在分片场景

mysql> SELECT MOD(234, 10);

        -> 4

mysql> SELECT 253 % 7;

        -> 1

mysql> SELECT MOD(29,9);

        -> 2

mysql> SELECT 29 MOD 9;

        -> 2

二、数字函数

名称	描述
ABS()	返回绝对值
ACOS()	返回反余弦
ASIN()	返回反正弦
ATAN()	返回反正切
ATAN2()， ATAN()	返回两个参数的反正切
CEIL()	返回不小于参数的最小整数值
CEILING()	返回不小于参数的最小整数值
CONV()	在不同的基数之间转换数字
COS()	返回余弦
COT()	返回余切
CRC32()	计算循环冗余校验值
DEGREES()	将弧度转换为度
EXP()	提升力量
FLOOR()	返回不大于参数的最大整数值
LN()	返回参数的自然对数
LOG()	返回第一个参数的自然对数
LOG10()	返回参数的以10为底的对数
LOG2()	返回参数的以2为底的对数
MOD()	退还剩余
PI()	返回pi的值
POW()	将参数提高到指定的幂
POWER()	将参数提高到指定的幂
RADIANS()	返回参数转换为弧度
RAND()	返回一个随机浮点值
ROUND()	围绕论点
SIGN()	返回参数的符号
SIN()	返回参数的正弦
SQRT()	返回参数的平方根
TAN()	返回参数的切线
TRUNCATE()	截断为指定的小数位数

ABS(X)

　　　　绝对值函数。

mysql> SELECT ABS(2);

    -> 2

mysql> SELECT ABS(-32);

    -> 32

CEILING(X)/CEIL(X)

　　返回>=X值的最小整数。

mysql> SELECT CEILING(1.23);

    -> 2

mysql> SELECT CEILING(-1.23);

    -> -1

FLOOR(X)

　　返回<=X值的最大整数。

mysql> SELECT FLOOR(1.23), FLOOR(-1.23);

    -> 1, -2

RAND([N])

　　获取0到1之间的随机小数，比如当想获取7~12之间的随机整数是可以使用。一般用于生成测试数据

SELECT FLOOR(7 + (RAND() * 5));

select floor(10+(rand()*10));##取10-20的随机整数

Select * from students order by rand() limit 1;

ROUND(X), ROUND(X,D)

　　四舍五入为D位个小数，当D参数不存在是，则默认为0。D 可以为负数，从而导致D该值的小数点左边的数字，X变为零。

mysql> SELECT ROUND(-1.23);

    -> -1

mysql> SELECT ROUND(-1.58);

    -> -2

mysql> SELECT ROUND(1.58);

    -> 2

mysql> SELECT ROUND(1.298, 1);

    -> 1.3

mysql> SELECT ROUND(1.298, 0);

    -> 1

mysql> SELECT ROUND(23.298, -1);

    -> 20

TRUNCATE(X,D)

　　数字X只保留D位的小数，其余均舍弃。D可以为负数，从而导致D该值的小数点左边的数字X变为零。

mysql> SELECT TRUNCATE(1.223,1);

    -> 1.2

mysql> SELECT TRUNCATE(1.999,1);

    -> 1.9

mysql> SELECT TRUNCATE(1.999,0);

    -> 1

mysql> SELECT TRUNCATE(-1.999,1);

    -> -1.9

mysql> SELECT TRUNCATE(122,-2);

    -> 100

mysql> SELECT TRUNCATE(10.28*100,0);

    -> 1028

三、日期和时间函数

名称	描述
ADDDATE()	将时间值（间隔）添加到日期值
ADDTIME()	加时间
CONVERT_TZ()	从一个时区转换到另一个时区
CURDATE()	返回当前日期
CURRENT_DATE()， CURRENT_DATE	CURDATE（）的同义词
CURRENT_TIME()，CURRENT_TIME	CURTIME（）的同义词
CURRENT_TIMESTAMP()，CURRENT_TIMESTAMP	NOW（）的同义词
CURTIME()	返回当前时间
DATE()	提取日期或日期时间表达式的日期部分
DATE_ADD()	将时间值（间隔）添加到日期值
DATE_FORMAT()	指定格式日期
DATE_SUB()	从日期中减去时间值（间隔）
DATEDIFF()	减去两个日期
DAY()	DAYOFMONTH（）的同义词
DAYNAME()	返回工作日的名称
DAYOFMONTH()	返回月份中的一天（0-31）
DAYOFWEEK()	返回参数的工作日索引
DAYOFYEAR()	返回一年中的某天（1-366）
EXTRACT()	提取部分日期
FROM_DAYS()	将天数转换为日期
FROM_UNIXTIME()	将Unix时间戳记格式化为日期
GET_FORMAT()	返回日期格式字符串
HOUR()	提取时间
LAST_DAY	返回参数的月份的最后一天
LOCALTIME()， LOCALTIME	NOW（）的同义词
LOCALTIMESTAMP，LOCALTIMESTAMP()	NOW（）的同义词
MAKEDATE()	从一年中的年月日创建日期
MAKETIME()	从小时，分钟，秒创建时间
MICROSECOND()	从参数返回微秒
MINUTE()	返回参数的分钟
MONTH()	返回经过日期的月份
MONTHNAME()	返回月份名称
NOW()	返回当前日期和时间
PERIOD_ADD()	在一年的月份中添加一个期间
PERIOD_DIFF()	返回期间之间的月数
QUARTER()	从日期参数返回季度
SEC_TO_TIME()	将秒转换为“ hh：mm：ss”格式
SECOND()	返回第二个（0-59）
STR_TO_DATE()	将字符串转换为日期
SUBDATE()	用三个参数调用时DATE_SUB（）的同义词
SUBTIME()	减去时间
SYSDATE()	返回函数执行的时间
TIME()	提取传递的表达式的时间部分
TIME_FORMAT()	格式化为时间
TIME_TO_SEC()	返回参数转换为秒
TIMEDIFF()	减去时间
TIMESTAMP()	仅使用一个参数，此函数将返回日期或日期时间表达式。有两个参数，参数的总和
TIMESTAMPADD()	向日期时间表达式添加间隔
TIMESTAMPDIFF()	从日期时间表达式中减去一个间隔
TO_DAYS()	返回日期参数转换为天
TO_SECONDS()	返回从Year 0开始转换为秒的日期或datetime参数
UNIX_TIMESTAMP()	返回Unix时间戳
UTC_DATE()	返回当前UTC日期
UTC_TIME()	返回当前UTC时间
UTC_TIMESTAMP()	返回当前UTC日期和时间
WEEK()	返回星期数
WEEKDAY()	返回工作日索引
WEEKOFYEAR()	返回日期的日历周（1-53）
YEAR()	返回年份
YEARWEEK()	返回年和周

ADDDATE(date,INTERVAL expr unit),ADDDATE(expr,days)

　　当expr为正数时则为增加时间，为负数时则为减少时间。Unit参数可以是任意时间单位

mysql> SELECT ADDDATE('2008-01-02', INTERVAL 31 DAY);

    -> '2008-02-02'

mysql> SELECT ADDDATE('2008-01-02', 31);

    -> '2008-02-02'

ADDTIME(expr1,expr2)

　　将expr2的时间增加到expr1上

mysql> SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');

    -> '2008-01-02 01:01:01.000001'

mysql> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');

    -> '03:00:01.999997'

CONVERT_TZ(dt,from_tz,to_tz)

　　将时间dt从from_tz这个时区转换成to_tz这个时区并返回

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');

    -> '2004-01-01 13:00:00'

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');

    -> '2004-01-01 22:00:00‘

CURDATE()，CURRENT_DATE，CURRENT_DATE()

　　返回以yyyy-mm-dd或者yyyymmdd格式的当前日期

mysql> SELECT CURDATE();

    -> '2008-06-13'

mysql> SELECT CURDATE() + 0;

    -> 20080613

CURTIME()，CURRENT_TIME，CURRENT_TIME()

　　按照hh:mm:ss或者hhmmss格式返回当前时间

mysql> SELECT CURTIME();

    -> '23:50:26'

mysql> SELECT CURTIME() + 0;

    -> 235026.000000

Now()， CURRENT_TIMESTAMP，CURRENT_TIMESTAMP()

　　返回当前的日期和时间，格式为yyyy-mm-dd hh:mi:ss或者yyyymmddhhmiss

mysql> SELECT NOW();

    -> '2007-12-15 23:50:26'

mysql> SELECT NOW() + 0;

    -> 20071215235026.000000

DATE(expr)

　　获取expr中的日期

mysql> SELECT DATE('2003-12-31 01:02:03');

-> '2003-12-31'

YEAR(expr)

　　获取expr中的年份

mysql> select year('2020-01-15 19:41:57');

+-----------------------------+

| year('2020-01-15 19:41:57') |

+-----------------------------+

|                        2020 |

+-----------------------------+

1 row in set (0.00 sec)

DATEDIFF(expr1,expr2)

　　返回expr1和expr2之间的天数差异，忽略时分秒

mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');

    -> 1

mysql> SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');

    -> -31

DATE_ADD(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit

　　增加或者减少时间

表：时间间隔表达式和单位参数

unit 值	预期expr格式
MICROSECOND	MICROSECONDS
SECOND	SECONDS
MINUTE	MINUTES
HOUR	HOURS
DAY	DAYS
WEEK	WEEKS
MONTH	MONTHS
QUARTER	QUARTERS
YEAR	YEARS
SECOND_MICROSECOND	'SECONDS.MICROSECONDS'
MINUTE_MICROSECOND	'MINUTES:SECONDS.MICROSECONDS'
MINUTE_SECOND	'MINUTES:SECONDS'
HOUR_MICROSECOND	'HOURS:MINUTES:SECONDS.MICROSECONDS'
HOUR_SECOND	'HOURS:MINUTES:SECONDS'
HOUR_MINUTE	'HOURS:MINUTES'
DAY_MICROSECOND	'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS'
DAY_SECOND	'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE	'DAYS HOURS:MINUTES'
DAY_HOUR	'DAYS HOURS'
YEAR_MONTH	'YEARS-MONTHS'

mysql> SELECT DATE_ADD('2018-05-01',INTERVAL 1 DAY);

     -> '2018-05-02'

mysql> SELECT DATE_SUB('2018-05-01',INTERVAL 1 YEAR);

     -> '2017-05-01'

mysql> SELECT DATE_ADD('2020-12-31 23:59:59',INTERVAL 1 SECOND);

     -> '2021-01-01 00:00:00'

mysql> SELECT DATE_ADD('2018-12-31 23:59:59',INTERVAL 1 DAY);

     -> '2019-01-01 23:59:59'

mysql> SELECT DATE_ADD('2100-12-31 23:59:59',INTERVAL '1:1' MINUTE_SECOND);

     -> '2101-01-01 00:01:00'

mysql> SELECT DATE_SUB('2025-01-01 00:00:00',INTERVAL '1 1:1:1' DAY_SECOND);

     -> '2024-12-30 22:58:59'

mysql> SELECT DATE_ADD('1900-01-01 00:00:00',INTERVAL '-1 10' DAY_HOUR);

     -> '1899-12-30 14:00:00'

mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);

     -> '1997-12-02'

mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',INTERVAL '1.999999' SECOND_MICROSECOND);

     -> '1993-01-01 00:00:01.000001'

DATE_FORMAT(date,format)

　　将date日期时间转换成format格式

说明符	描述
%a	工作日的缩写名称（Sun.. Sat）
%b	月份缩写名称（Jan.. Dec）
%c	月份，数字（0.. 12）
%D	英语后缀月的一天（0th， 1st，2nd， 3rd，...）
%d	每月的某天，数字（00.. 31）
%e	每月的某天，数字（0.. 31）
%f	微秒（000000.. 999999）
%H	小时（00.. 23）
%h	小时（01.. 12）
%I	小时（01.. 12）
%i	分钟，数字（00.. 59）
%j	一年中的一天（001.. 366）
%k	小时（0.. 23）
%l	小时（1.. 12）
%M	月名（January.. December）
%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），其中星期日是一周的第一天； WEEK()模式0
%u	周（00.. 53），其中星期一是一周的第一天； WEEK()模式1
%V	周（01.. 53），其中星期日是一周的第一天； WEEK()模式2; 用于 %X
%v	周（01.. 53），其中星期一是一周的第一天； WEEK()模式3; 用于 %x
%W	工作日名称（Sunday.. Saturday）
%w	星期几（0=星期天.. 6=星期六）
%X	星期的年份，其中星期日是一周的第一天，数字，四位数；用于%V
%x	一周的年份，其中星期一是一周的第一天，数字，四位数；用于%v
%Y	年，数字，四位数
%y	年，数字（两位数字）
%%	文字%字符
%x	x，对于上面未列出的任何 “ x”

mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');

     -> 'Sunday October 2009'

mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');

     -> '22:23:00'

mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',

    ->                 '%D %y %a %d %m %b %j');

     -> '4th 00 Thu 04 10 Oct 277'

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',

    ->                 '%H %k %I %r %T %S %w');

     -> '22 22 10 10:23:00 PM 22:23:00 00 6'

mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');

     -> '1998 52'

mysql> SELECT DATE_FORMAT('2006-06-00', '%d');

     -> '00'

DAY(date), DAYOFMONTH(date)

　　返回date中日期在当前月份中是第几天

mysql> SELECT DAYOFMONTH('2007-02-03');

    -> 3

DAYNAME(date)

　　返回date时间是星期几

mysql> SELECT DAYNAME('2007-02-03');

    -> 'Saturday'

DAYOFWEEK(date)

　　返回date时间是星期“几”

mysql> SELECT DAYOFWEEK('2007-02-03');

    -> 7

DAYOFYEAR(date)

　　返回date是一年中的第几天，取值范围在1~366

mysql> SELECT DAYOFYEAR('2007-02-03');

    -> 34

EXTRACT(unit FROM date)

　　Unit单元和date_add/date_sub函数中的一样，是获取date日期的unit部分

mysql> SELECT EXTRACT(YEAR FROM '2009-07-02');

    -> 2009

mysql> SELECT EXTRACT(YEAR_MONTH FROM '2009-07-02 01:02:03');

    -> 200907

mysql> SELECT EXTRACT(DAY_MINUTE FROM '2009-07-02 01:02:03');

    -> 20102

mysql> SELECT EXTRACT(MICROSECOND FROM '2003-01-02 10:30:00.000123');

-> 123

UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)

　　如果没有date参数，则返回当前时间到1970-01-01 00:00:00之间的秒数，如果有date参数，则表示date到1970-01-01 00:00:00之间的秒数

mysql> SELECT UNIX_TIMESTAMP();

    -> 1447431666

mysql> SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19');

    -> 1447431619

FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format)

　　根据给定的unixtime，返回yyyy-mm-dd hh:mi:ss或者yyyymmddhhmiss格式的具体时间，或者按照format返回时间。

mysql> SELECT FROM_UNIXTIME(1447430881);

    -> '2015-11-13 10:08:01'

mysql> SELECT FROM_UNIXTIME(1447430881) + 0;

    -> 20151113100801

mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),'%Y %D %M %h:%i:%s %x');

    -> '2015 13th November 10:08:01 2015'

LAST_DAY(date)

　　返回date日期所在月份的最后一天日期

mysql> SELECT LAST_DAY('2004-02-05');

    -> '2004-02-29'

mysql> SELECT LAST_DAY('2004-01-01 01:01:01');

    -> '2004-01-31'

mysql> SELECT LAST_DAY('2003-03-32');

    -> NULL

SYSDATE()

　　返回当前日期和时间，格式为yyyy-mm-dd hh:mi:ss或者yyyymmddhhmiss和now()函数的区别在于now()返回的时间是语句执行的时间，而sysdate()返回的时间是该函数执行的时间

mysql> SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW() | SLEEP(2) | NOW() |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:36 | 0 | 2006-04-12 13:47:36 |
+---------------------+----------+---------------------+
mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE() ,now();
+---------------------+----------+---------------------+---------------------+
| SYSDATE()           | SLEEP(2) | SYSDATE()           | now()               |
+---------------------+----------+---------------------+---------------------+
| 2020-02-26 21:01:06 |        0 | 2020-02-26 21:01:08 | 2020-02-26 21:01:06 |
+---------------------+----------+---------------------+---------------------+

TIME(expr)

　　返回expr日期时间中的时间部分

mysql> SELECT TIME('2003-12-31 01:02:03');

    -> '01:02:03'

mysql> SELECT TIME('2003-12-31 01:02:03.000123');

    -> '01:02:03.000123'

四、字符串函数

4.1常用的字符串函数

char_length(str)

　　返回字符串的字符长度

mysql> select * from students;

+-----+-------+--------+---------+

| sid | sname | gender | dept_id |

+-----+-------+--------+---------+

|   1 | a     | 3      |       4 |

|   4 | c     | 3      |       1 |

|   5 | dd    | 2      |       2 |

|   6 | eee   | 2      |       4 |

+-----+-------+--------+---------+

4 rows in set (0.00 sec)

 

mysql> select sid,char_length(sname) from students;

+-----+--------------------+

| sid | char_length(sname) |

+-----+--------------------+

|   1 |                  1 |

|   4 |                  1 |

|   5 |                  2 |

|   6 |                  3 |

+-----+--------------------+

4 rows in set (0.00 sec)

concat(str1,str2,...)

　　返回括号里所有参数字符串连接在一起，当其中有参数为NULL时则返回NULL

mysql> SELECT CONCAT('My', 'S', 'QL');

    -> 'MySQL'

mysql> SELECT CONCAT('My', NULL, 'QL');

    -> NULL

mysql> SELECT CONCAT(14.3);

-> '14.3'

 

mysql> select * from students;

+-----+-------+--------+---------+

| sid | sname | gender | dept_id |

+-----+-------+--------+---------+

|   1 | a     | 3      |       4 |

|   4 | c     | 3      |       1 |

|   5 | dd    | 2      |       2 |

|   6 | eee   | 2      |       4 |

|   7 | NULL  | 3      |       1 |

+-----+-------+--------+---------+

5 rows in set (0.00 sec)

 

mysql> select concat(sid,sname) from students;##可以看到有null值的行，只返回null。

+-------------------+

| concat(sid,sname) |

+-------------------+

| 1a                |

| 4c                |

| 5dd               |

| 6eee              |

| NULL              |

+-------------------+

5 rows in set (0.00 sec)

 

mysql> select concat ('sid:',sid,'@@','sname:',sname) from students;##中间可以加一些分隔符

+-----------------------------------------+

| concat ('sid:',sid,'@@','sname:',sname) |

+-----------------------------------------+

| sid:1@@sname:a                          |

| sid:4@@sname:c                          |

| sid:5@@sname:dd                         |

| sid:6@@sname:eee                        |

| NULL                                    |

+-----------------------------------------+

5 rows in set (0.00 sec)

 

mysql> select concat ('sid:',sid,'@@','sname:',ifnull(sname,'')) from students;##去掉字符串中的null值嵌套ifnull函数，这里用空串表示

+----------------------------------------------------+

| concat ('sid:',sid,'@@','sname:',ifnull(sname,'')) |

+----------------------------------------------------+

| sid:1@@sname:a                                     |

| sid:4@@sname:c                                     |

| sid:5@@sname:dd                                    |

| sid:6@@sname:eee                                   |

| sid:7@@sname:                                      |

+----------------------------------------------------+

5 rows in set (0.00 sec)

CONCAT_WS(separator,str1,str2,...)

　　返回以第一个参数为分隔符的连接后的一个字符串，当有参数为NULL时则null被忽略。

mysql> select concat_ws(',','First name','Second name','Last Name');

+-------------------------------------------------------+

| concat_ws(',','First name','Second name','Last Name') |

+-------------------------------------------------------+

| First name,Second name,Last Name                      |

+-------------------------------------------------------+

1 row in set (0.00 sec)

mysql> SELECT CONCAT_WS(',','First name',NULL,'Last Name');

    -> 'First name,Last Name‘

mysql> select concat(sid,sname) from students;

+-------------------+

| concat(sid,sname) |

+-------------------+

| 1a                |

| 4c                |

| 5dd               |

| 6eee              |

| NULL              |

+-------------------+

5 rows in set (0.00 sec)

 

mysql> select concat_ws(',',sid,sname) from students;

+--------------------------+

| concat_ws(',',sid,sname) |

+--------------------------+

| 1,a                      |

| 4,c                      |

| 5,dd                     |

| 6,eee                    |

| 7                        |

+--------------------------+

5 rows in set (0.01 sec)

INSERT(str,pos,len,newstr)

　　将str中从pos位置开始后的len个字符替换成newstr字符串

mysql> select insert('Quadratic',3,4,'What');

+--------------------------------+

| insert('Quadratic',3,4,'What') |

+--------------------------------+

| QuWhattic                      |

+--------------------------------+

1 row in set (0.00 sec)

 

mysql> select insert('Quadratic',-1,4,'What');

+---------------------------------+

| insert('Quadratic',-1,4,'What') |

+---------------------------------+

| Quadratic                       |

+---------------------------------+

1 row in set (0.00 sec)

 

mysql> select insert('Quadratic',3,100,'What');

+----------------------------------+

| insert('Quadratic',3,100,'What') |

+----------------------------------+

| QuWhat                           |

+----------------------------------+

1 row in set (0.00 sec)

instr(str,substr)

　　返回str字符串中第一个出现substr字符串的位置。

mysql> select instr('foobarbar','bar');##在字符串fobarbar中第一次返回bar字符串的字符是第四个字符，故返回4。

+--------------------------+

| instr('foobarbar','bar') |

+--------------------------+

|                        4 |

+--------------------------+

1 row in set (0.00 sec)

 

mysql> select * from students;

+-----+-----------+--------+---------+

| sid | sname     | gender | dept_id |

+-----+-----------+--------+---------+

|   1 | foobarbar | 3      |       4 |

|   4 | c         | 3      |       1 |

|   5 | dd        | 2      |       2 |

|   6 | eee       | 2      |       4 |

|   7 | NULL      | 3      |       1 |

+-----+-----------+--------+---------+

5 rows in set (0.00 sec)

 

mysql> select instr(sname,'bar') from students;

+--------------------+

| instr(sname,'bar') |

+--------------------+

|                  4 |

|                  0 |

|                  0 |

|                  0 |

|               NULL |

+--------------------+

5 rows in set (0.00 sec)

LENGTH(str)

　　返回str字符串的byte字节长度。

mysql> select length('test');

+----------------+

| length('test') |

+----------------+

|              4 |

+----------------+

1 row in set (0.00 sec)

 

mysql> select * from students;

+-----+-----------+--------+---------+

| sid | sname     | gender | dept_id |

+-----+-----------+--------+---------+

|   1 | foobarbar | 3      |       4 |

|   4 | c         | 3      |       1 |

|   5 | dd        | 2      |       2 |

|   6 | eee       | 2      |       4 |

|   7 | NULL      | 3      |       1 |

+-----+-----------+--------+---------+

5 rows in set (0.00 sec)

 

mysql> select length(sname) from students;

+---------------+

| length(sname) |

+---------------+

|             9 |

|             1 |

|             2 |

|             3 |

|          NULL |

+---------------+

5 rows in set (0.00 sec)

locate(substr,str)

　　返回str字符串中第一次出现substr字符串的位置，如果没有则返回null

mysql> SELECT LOCATE('bar', 'foobarbar');

    -> 4

mysql> SELECT LOCATE('xbar', 'foobar');

    -> 0

LOCATE(substr,str,pos)

　　返回str字符串中从pos位置开始第一次出现substr字符串的位置，如果没有则返回null

mysql> SELECT LOCATE('bar', 'foobarbar', 5);

    -> 7

LOWER(str)

　　返回将str字符串中所有字符变换成小写后的字符串，但对二进制文本无效。

mysql> select lower('QWERTYUIOP');

+---------------------+

| lower('QWERTYUIOP') |

+---------------------+

| qwertyuiop          |

+---------------------+

1 row in set (0.00 sec)

LTRIM(str)

　　将str最左边的空格去掉并返回。

mysql> select ltrim('   barbar');

+--------------------+

| ltrim('   barbar') |

+--------------------+

| barbar             |

+--------------------+

1 row in set (0.00 sec)

RTRIM(str)

　　将字符串str右边的空格去掉并返回。

mysql> SELECT RTRIM('barbar ');

    -> 'barbar‘

TRIM(str)

　　将字符串str左右两边的空格去掉并返回。

mysql> select trim('  abcd  ');##这个trim等同于下面的嵌套命令

+------------------+

| trim('  abcd  ') |

+------------------+

| abcd             |

+------------------+

1 row in set (0.00 sec)

 

mysql> select rtrim(ltrim('  abcd  '));

+--------------------------+

| rtrim(ltrim('  abcd  ')) |

+--------------------------+

| abcd                     |

+--------------------------+

1 row in set (0.00 sec)

REPEAT(str,count)

　　将str重复count并组合成字符串返回，如果count<1，则返回空串。

mysql> select repeat('mysql',3);

+-------------------+

| repeat('mysql',3) |

+-------------------+

| mysqlmysqlmysql   |

+-------------------+

1 row in set (0.00 sec)

REPLACE(str,from_str,to_str)

　　将所有str字符串中匹配from_str字串的地方都替换成to_str子字符串。

mysql> select replace('www.mysql.com','w','Aa');

+-----------------------------------+

| replace('www.mysql.com','w','Aa') |

+-----------------------------------+

| AaAaAa.mysql.com                  |

+-----------------------------------+

1 row in set (0.00 sec)

SUBSTR(str,pos), SUBSTR(str FROM pos), SUBSTR(str,pos,len), SUBSTR(str FROM pos FOR len)
SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len),SUBSTRING(str FROM pos FOR len)

　　如果没有len参数，则返回从pos位置开始的str中的子字符串；如果有len参数，则从pos位置开始返回str中长度为len的子字符串；如果pos为负值，则代表pos从右边开始数。

mysql> select substring('Quadratically',5);

+------------------------------+

| substring('Quadratically',5) |

+------------------------------+

| ratically                    |

+------------------------------+

1 row in set (0.00 sec)

 

mysql> select substring('Quadratically'from 6);

+----------------------------------+

| substring('Quadratically'from 6) |

+----------------------------------+

| atically                         |

+----------------------------------+

1 row in set (0.00 sec)

 

mysql> select substring('Quadratically',5,6);

+--------------------------------+

| substring('Quadratically',5,6) |

+--------------------------------+

| ratica                         |

+--------------------------------+

1 row in set (0.00 sec)

 

mysql> select substring('Quadratically'from 5 for 3);

+----------------------------------------+

| substring('Quadratically'from 5 for 3) |

+----------------------------------------+

| rat                                    |

+----------------------------------------+

1 row in set (0.00 sec)

 

mysql> select substring('Quadratically',-5);

+-------------------------------+

| substring('Quadratically',-5) |

+-------------------------------+

| cally                         |

+-------------------------------+

1 row in set (0.00 sec)

 

mysql> select substring('Quadratically',-5,4);

+---------------------------------+

| substring('Quadratically',-5,4) |

+---------------------------------+

| call                            |

+---------------------------------+

1 row in set (0.00 sec)

实际场景：

mysql> select name,substring(name,1,instr(name,' ')),substring(name,instr(name,' ')) from teacher;

+-----------+-----------------------------------+---------------------------------+

| name      | substring(name,1,instr(name,' ')) | substring(name,instr(name,' ')) |

+-----------+-----------------------------------+---------------------------------+

| zhang san | zhang                             |  san                            |

| li si     | li                                |  si                             |

| wang wu   | wang                              |  wu                             |

+-----------+-----------------------------------+---------------------------------+

3 rows in set (0.00 sec)

4.2 不太常用的字符串函数

ASCII(str)

　　返回str字符串中最左边字符的ascii码值，如果是空串则返回0，如果str是null则返回null

mysql> SELECT ASCII('2');

    -> 50

mysql> SELECT ASCII(2);

    -> 50

mysql> SELECT ASCII('dx');

    -> 100

CHAR(N,... [USING charset_name])

　　将括号中的N转化成ascii码对应的字符，返回这些字符组成的字符串，其中的null会被忽略

mysql> SELECT CHAR(77,121,83,81,'76');

     -> 'MySQL'

mysql> SELECT CHAR(77,77.3,'77.3');

     -> 'MMM‘

mysql> SELECT CHARSET(CHAR(X'65')), CHARSET(CHAR(X'65' USING utf8));

+----------------------+---------------------------------+

| CHARSET(CHAR(X'65')) | CHARSET(CHAR(X'65' USING utf8)) |

+----------------------+---------------------------------+

| binary | utf8 |

+----------------------+---------------------------------+

LEFT(str,len)

　　返回str字符串中从左边开始的len个长度的字符

mysql> SELECT LEFT('foobarbar', 5);

    -> 'fooba‘

LOAD_FILE(file_name)

　　读取文件且返回文件内容为字符串

mysql> UPDATE t SET blob_col=LOAD_FILE('/tmp/picture') WHERE id=1;

LPAD(str,len,padstr)

　　将str的左边补充为padstr，直到补充成len长度的字符串并返回；如果str的长度比len长，则返回str中最左边开始的len长度的字符

mysql> SELECT LPAD('hi',4,'??');

    -> '??hi'

mysql> SELECT LPAD('hi',1,'??');

    -> 'h'

REVERSE(str)

　　将str字符串中的字符按照倒序组合并返回

mysql> SELECT REVERSE('abc');

    -> 'cba‘

RIGHT(str,len)

　　将str字符串中从右边开始的len个字符返回

mysql> SELECT RIGHT('foobarbar', 4);

    -> 'rbar'

RPAD(str,len,padstr)

　　将字符串str从右边开始补充为padstr直到整体长度为len，如果str的长度本身大于len，则返回str中len长度的字符串

mysql> SELECT RPAD('hi',5,'?');

    -> 'hi???'

mysql> SELECT RPAD('hi',1,'?');

    -> 'h'

RTRIM(str)

　　将字符串str右边的空格去掉并返回

mysql> SELECT RTRIM('barbar ');

    -> 'barbar'

SPACE(N)

　　返回N个长度的空格组成的空字符串

mysql> SELECT SPACE(6);

    -> ' '

SUBSTRING_INDEX(str,delim,count)

　　当count为正数，则返回delim出现在str字符串中第count次之前的子字符串，如果是负数，则从右边开始计算

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);

    -> 'www.mysql'

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);

    -> 'mysql.com‘

UPPER(str)

　　　　返回将str字符串中所有字符转换成大写的字符串

mysql> SELECT UPPER('Hej');

    -> 'HEJ‘

五、字符串对比函数

name	description
like	简单模式匹配
not like	简单模式匹配的否定
strcmp()	比较两个字符串

5.1 like对比函数

通配符%表示匹配0个或多个字符

mysql> select * from students;

+-----+-------+--------+---------+

| sid | sname | gender | dept_id |

+-----+-------+--------+---------+

|   1 | aa    | 3      |       1 |

|   4 | cc    | 3      |       1 |

|   5 | dd    | 1      |       2 |

|   6 | aac   | 1      |       1 |

|  10 | a     | 1      |       1 |

|  20 | bac   | 1      |       1 |

+-----+-------+--------+---------+

6 rows in set (0.00 sec)

 

mysql> select * from students where sname like 'a%';##匹配以"a"开头的0个或多个字符

+-----+-------+--------+---------+

| sid | sname | gender | dept_id |

+-----+-------+--------+---------+

|   1 | aa    | 3      |       1 |

|   6 | aac   | 1      |       1 |

|  10 | a     | 1      |       1 |

+-----+-------+--------+---------+

3 rows in set (0.00 sec)

 

mysql> select * from students where sname like '%a%';##配置字符串中有“a”的行

+-----+-------+--------+---------+

| sid | sname | gender | dept_id |

+-----+-------+--------+---------+

|   1 | aa    | 3      |       1 |

|   6 | aac   | 1      |       1 |

|  10 | a     | 1      |       1 |

|  20 | bac   | 1      |       1 |

+-----+-------+--------+---------+

4 rows in set (0.00 sec)

 

mysql> select * from students where sname like 'a%a%';##以“a”开头其中还有一个“a”的字符串。

+-----+-------+--------+---------+

| sid | sname | gender | dept_id |

+-----+-------+--------+---------+

|   1 | aa    | 3      |       1 |

|   6 | aac   | 1      |       1 |

+-----+-------+--------+---------+

2 rows in set (0.00 sec)

 

mysql> select * from students where sname like '%a';##匹配以a结尾的字符串

+-----+-------+--------+---------+

| sid | sname | gender | dept_id |

+-----+-------+--------+---------+

|   1 | aa    | 3      |       1 |

|  10 | a     | 1      |       1 |

+-----+-------+--------+---------+

2 rows in set (0.00 sec)

 

mysql> select * from students where sname like 'a%c';##匹配以“a”开头以“c”结尾的字符串

+-----+-------+--------+---------+

| sid | sname | gender | dept_id |

+-----+-------+--------+---------+

|   6 | aac   | 1      |       1 |

+-----+-------+--------+---------+

1 row in set (0.00 sec)

通配符“_”表示匹配1个字符

mysql> select * from students;

+-----+-------+--------+---------+

| sid | sname | gender | dept_id |

+-----+-------+--------+---------+

|   1 | aa    | 3      |       1 |

|   4 | cc    | 3      |       1 |

|   5 | dd    | 1      |       2 |

|   6 | aac   | 1      |       1 |

|  10 | a     | 1      |       1 |

|  20 | bac   | 1      |       1 |

+-----+-------+--------+---------+

6 rows in set (0.00 sec)

 

mysql> select * from students where sname like 'a_';##以“a”开头，后面的字符必须是一个字符。

+-----+-------+--------+---------+

| sid | sname | gender | dept_id |

+-----+-------+--------+---------+

|   1 | aa    | 3      |       1 |

+-----+-------+--------+---------+

1 row in set (0.00 sec)

 

mysql> select * from students where sname like 'a_%';##表示以“a”开头，后面必须有一个字符，然后后面可以匹配0到多个任意字符

+-----+-------+--------+---------+

| sid | sname | gender | dept_id |

+-----+-------+--------+---------+

|   1 | aa    | 3      |       1 |

|   6 | aac   | 1      |       1 |

+-----+-------+--------+---------+

2 rows in set (0.00 sec)

当匹配字符中有特殊字符时，可以用 \或者escape来指定特殊字符为字符

mysql> select * from students;

+-----+-------+--------+---------+

| sid | sname | gender | dept_id |

+-----+-------+--------+---------+

|   1 | aa    | 3      |       1 |

|   4 | cc    | 3      |       1 |

|   5 | dd    | 1      |       2 |

|   6 | aac   | 1      |       1 |

|  10 | a     | 1      |       1 |

|  20 | bac   | 1      |       1 |

+-----+-------+--------+---------+

6 rows in set (0.00 sec)

 

mysql> select * from students where sname like 'a\%';##该命令相当与where sname='a%'，百分号不再表示匹配0或多个字符，而是一个字符。

Empty set (0.00 sec)

5.2 not like对比函数

是上一个字符串对比函数的反义

expr NOT LIKE pat [ESCAPE 'escape_char']

5.3 strcmp(expr1,expr2)对比函数

当expr1等于expr2时等于0，当expr1小于expr2时为-1，反之为1

mysql> SELECT STRCMP('text', 'text2');

    -> -1

mysql> SELECT STRCMP('text2', 'text');

    -> 1

mysql> SELECT STRCMP('text', 'text');

    -> 0

六、格式转换函数

Cast()和convert()

两个函数都可以用来转换数据类型或者转换字符集

允许转换的数据类型包括：

Binary[N]

char[N]

Date

Datetime

decimal[M,[D]]

Time

Signed [integer]

Unsigned [integer]

SELECT CONVERT(_latin1'Müller' USING utf8);

SELECT CONVERT('test', CHAR CHARACTER SET utf8);

SELECT CAST('test' AS CHAR CHARACTER SET utf8);

select CAST('2000-01-01' AS DATE);

select CONVERT('2000-01-01', DATE);

七、聚合函数

用在存在group by子句的语句中

AVG([DISTINCT] expr)

　　计算expr的平均值，distinct关键词表示是否排除重复值

mysql> SELECT student_name, AVG(test_score) FROM student GROUP BY student_name;

COUNT(expr)

　　计算expr中的个数，如果没有匹配则返回0，注意NULL的区别

mysql> SELECT student.student_name,COUNT(*) FROM student,course WHERE student.student_id=course.student_id GROUP BY student_name;

COUNT(DISTINCT expr,[expr...])

　　计算有多少个不重复的expr值，注意是计算非NULL的个数

mysql> SELECT COUNT(DISTINCT results) FROM student;

MAX([DISTINCT] expr),MIN([DISTINCT] expr)

　　返回expr中最大或者最小的值

mysql> SELECT student_name, MIN(test_score), MAX(test_score) FROM student GROUP BY student_name;

SUM([DISTINCT] expr)

　　返回expr的求和值

八、

posted @ 2020-05-26 18:28 123comcn 阅读(213) 评论(0) 编辑收藏举报

刷新页面返回顶部

123comcn

MySQL常用函数介绍

一、操作符优先级

二、比较函数

三、逻辑操作符

第1章 分配操作符

一、算数运算符

二、数字函数

三、日期和时间函数

四、字符串函数

4.1常用的字符串函数

4.2 不太常用的字符串函数

五、字符串对比函数

5.1 like对比函数

5.2 not like对比函数

5.3 strcmp(expr1,expr2)对比函数

六、格式转换函数

七、聚合函数

八、

公告

第1章分配操作符