MySQL 运算符和函数（六）

6、运算符和函数

mysql 内置函数

6.1、mysql 字符函数

名称	描述
CONCAT()	字符连接
CONCAT_WS()	使用指定的分隔符进行字符连接
FORMAT()	数字格式化
LOWER()	转换为小写字母
UPPER()	转换为大写字母
LEFT()	获取左侧字符
RIGHT()	获取右侧字符

6.1.1、concat（）和 concat_ws（） 函数

/*concat（）函数*/
mysql> SELECT CONCAT('imooc','-','mysql');
+-----------------------------+
| CONCAT('imooc','-','mysql') |
+-----------------------------+
| imooc-mysql                 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM tb_1;
+------------+-----------+
| first_name | LAST_NAME |
+------------+-----------+
| a          | b         |
| c          | d         |
+------------+-----------+
2 rows in set (0.00 sec)

mysql> SELECT CONCAT(first_name,last_name) AS full_name FROM tb_1;
+-----------+
| full_name |
+-----------+
| ab        |
| cd        |
+-----------+
2 rows in set (0.00 sec)

/*concat_ws（）函数,至少需要三个参数，第一个为分隔符*/
mysql> SELECT CONCAT_WS('|','A','B','C');
+----------------------------+
| CONCAT_WS('|','A','B','C') |
+----------------------------+
| A|B|C                      |
+----------------------------+
1 row in set (0.00 sec)

6.1.2、format（）函数

/*保留小数点后一位，返回值为字符串*/
mysql> SELECT FORMAT(12560.75,1);
+--------------------+
| FORMAT(12560.75,1) |
+--------------------+
| 12,560.8           |
+--------------------+
1 row in set (0.00 sec)

6.1.3、lower（）、upper（）、left（）和 right（）函数

mysql> SELECT LOWER('MYSQL');        /*lower（）函数*/
+----------------+
| LOWER('MYSQL') |
+----------------+
| mysql          |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT UPPER('mysql');        /*upper（）函数*/
+----------------+
| UPPER('mysql') |
+----------------+
| MYSQL          |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT LEFT('MYSQL',2);      /*left（）函数*/
+-----------------+
| LEFT('MYSQL',2) |
+-----------------+
| MY              |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT LOWER(LEFT('MYSQL',2));     /*函数嵌套*/
+------------------------+
| LOWER(LEFT('MYSQL',2)) |
+------------------------+
| my                     |
+------------------------+
1 row in set (0.00 sec)

名称	描述
LENGTH()	获取字符串长度
LTRIM()	删除前导空格
RTRIM()	删除后续空格
TRIM()	删除前导和后续空格
SUBSTRING()	字符串截取
[NOT] LIKE	模式匹配
REPLACE()	字符串替换

6.1.4、length（）函数

mysql> SELECT LENGTH(' MYSQL');
+------------------+
| LENGTH(' MYSQL') |
+------------------+
|                6 |
+------------------+
1 row in set (0.00 sec)

6.1.5、ltrim（）、rtrim（）和 trim（）函数

mysql> SELECT LENGTH(LTRIM('  MYSQL '));   /*删除前面的空格*/
+---------------------------+
| LENGTH(LTRIM('  MYSQL ')) |
+---------------------------+
|                         6 |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT LENGTH(RTRIM('  MYSQL '));   /*删除后面的空格*/
+---------------------------+
| LENGTH(RTRIM('  MYSQL ')) |
+---------------------------+
|                         7 |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT LENGTH(TRIM('  MYSQL '));       /*删除前后的所有空格*/
+--------------------------+
| LENGTH(TRIM('  MYSQL ')) |
+--------------------------+
|                        5 |
+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT TRIM(LEADING '?' FROM '??MYSQL???');  /*删除前面特殊的符号，（leading：领先的）*/
+-------------------------------------+
| TRIM(LEADING '?' FROM '??MYSQL???') |
+-------------------------------------+
| MYSQL???                            |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT TRIM(TRAILING '?' FROM '??MYSQL???');   /*删除后面特殊的符号，（trailing：落后的）*/
+--------------------------------------+
| TRIM(TRAILING '?' FROM '??MYSQL???') |
+--------------------------------------+
| ??MYSQL                              |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT TRIM(BOTH '?' FROM '??MYSQL???');    /*前后都删除*/
+----------------------------------+
| TRIM(BOTH '?' FROM '??MYSQL???') |
+----------------------------------+
| MYSQL                            |
+----------------------------------+
1 row in set (0.00 sec)

6.1.6、replace（）函数

mysql> SELECT REPLACE('??MY??SQL???','?','');    /*将？替换成空字符串*/
+--------------------------------+
| REPLACE('??MY??SQL???','?','') |
+--------------------------------+
| MYSQL                          |
+--------------------------------+
1 row in set (0.00 sec)

mysql> SELECT REPLACE('??MY??SQL???','?','!');    /*也可以将？替换成别的符号，也可以一个替换成多个*/
+---------------------------------+
| REPLACE('??MY??SQL???','?','!') |
+---------------------------------+
| !!MY!!SQL!!!                    |
+---------------------------------+
1 row in set (0.00 sec)

6.1.7、[NOT] LIKE（）函数

mysql> SELECT 'MYSQL' LIKE 'M%';          /*%匹配任意字符*/
+-------------------+
| 'MYSQL' LIKE 'M%' |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

mysql> select * from tb_1;
+------------+-----------+
| first_name | LAST_NAME |
+------------+-----------+
| a          | b         |
| c          | d         |
| tom%       | 123       |
+------------+-----------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM tb_1 WHERE first_name LIKE '%o%';    /*%匹配任意字符，查询包含 o 的字符串*/
+------------+-----------+
| first_name | LAST_NAME |
+------------+-----------+
| tom%       | 123       |
+------------+-----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM tb_1 WHERE first_name LIKE '%1%%' ESCAPE 1;  /*%匹配任意字符，查询包含 % 的字符串，加上 ESCAPE（逃避） 1 表示 1 后面的 % 不匹配任意字符*/
+------------+-----------+
| first_name | LAST_NAME |
+------------+-----------+
| tom%       | 123       |
+------------+-----------+
1 row in set (0.00 sec)

6.2、数值运算符和函数

名称	描述
CEIL（）	进一取整
DIV	整数除法
FLOOR（）	舍一取整
MOD	取余数（取模）
POWER（）	幂运算
ROUND（）	四舍五入
TRUNCATE（）	数字截取

6.2.1、ceil（）和 floor（）函数

/*ceil()函数向上进一位只取整数，不论小数点后是小于5还是大于5，对结果没有影响*/
mysql> select ceil(3.01);  
+------------+
| ceil(3.01) |
+------------+
|          4 |
+------------+

mysql> SELECT CEIL(3.99);
+------------+
| CEIL(3.99) |
+------------+
|          4 |
+------------+

/*floor()函数向下舍一位只取整数，不论小数点后是小于5还是大于5，对结果没有影响*/
mysql> SELECT FLOOR(4.01);
+-------------+
| FLOOR(4.01) |
+-------------+
|           4 |
+-------------+

6.2.2、div 和 mod 运算符

/*div 运算符相当于整除，只取结果的整数部分*/
mysql> SELECT 3/4;
+--------+
| 3/4    |
+--------+
| 0.7500 |
+--------+

mysql> SELECT 3 DIV 4;
+---------+
| 3 DIV 4 |
+---------+
|       0 |
+---------+

/*mod 运算符与取余一样*/
mysql> SELECT 5 % 3;
+-------+
| 5 % 3 |
+-------+
|     2 |
+-------+

mysql> SELECT 5 MOD 3;
+---------+
| 5 MOD 3 |
+---------+
|       2 |
+---------+

mysql> SELECT 5.3 MOD 3;
+-----------+
| 5.3 MOD 3 |
+-----------+
|       2.3 |
+-----------+

6.2.3、round（）、power（） 和 truncate（）函数

/*round（）函数四舍五入，两个参数，第二个参数为保留小数点后几位*/
mysql> SELECT ROUND(3.56,1);
+---------------+
| ROUND(3.56,1) |
+---------------+
|           3.6 |
+---------------+

/*power（）函数幂运算，两个参数，第二个参数为几次幂*/
mysql> SELECT POWER(3,3);
+------------+
| POWER(3,3) |
+------------+
|         27 |
+------------+

/*truncate（）函数截取数字，两个参数，第二个参数为保留小数点后几位，与四舍五入不同*/
mysql> SELECT TRUNCATE(125.89,1);
+--------------------+
| TRUNCATE(125.89,1) |
+--------------------+
|              125.8 |
+--------------------+

mysql> SELECT TRUNCATE(125.89,-1);   /*也可以是负一*/
+---------------------+
| TRUNCATE(125.89,-1) |
+---------------------+
|                 120 |
+---------------------+

6.3、比较运算符与函数

名称	描述
[NOT] BETWEEN...AND...	[不] 在范围之内
[NOT] IN()	[不] 在列出值范围内
IS [NOT] NULL	[不] 为空

/*[not] between...and..*/
mysql> SELECT 15 BETWEEN 1 AND 20;
+---------------------+
| 15 BETWEEN 1 AND 20 |
+---------------------+
|                   1 |
+---------------------+

/*[not] in()*/
mysql> SELECT 15 IN(1,10,15,20);        /*15 在这些列出值的范围内*/
+-------------------+
| 15 IN(1,10,15,20) |
+-------------------+
|                 1 |
+-------------------+

/*is [not] null*/
mysql> select * from tb_1;
+------------+-----------+
| first_name | LAST_NAME |
+------------+-----------+
| a          | b         |
| c          | d         |
| tom%       | 123       |
+------------+-----------+

mysql> SELECT * FROM tb_1 WHERE first_name IS NOT NULL;  /*tb_1数据表中 first_name列中不为空的行*/
+------------+-----------+
| first_name | LAST_NAME |
+------------+-----------+
| a          | b         |
| c          | d         |
| tom%       | 123       |
+------------+-----------+

mysql>  SELECT NULL IS NULL;     /*空为空*/
+--------------+
| NULL IS NULL |
+--------------+
|            1 |
+--------------+

mysql> SELECT '' IS NULL;     /*空字符串不为空*/
+------------+
| '' IS NULL |
+------------+
|          0 |
+------------+

6.4、日期时间函数

名称	描述
NOW（）	当前时间和日期
CURDATE（）	当前日期
CURTIME（）	当前时间
DATE_ADD（）	日期变化
DATEDIFF（）	日期差值
DATE_FORMAT（）	日期格式化

mysql> SELECT NOW();         /*显示当前时间和日期*/
+---------------------+
| NOW()               |
+---------------------+
| 2018-04-03 16:19:36 |
+---------------------+

mysql> SELECT CURDATE();    /*显示当前日期*/
+------------+
| CURDATE()  |
+------------+
| 2018-04-03 |
+------------+

mysql> SELECT CURTIME();    /*显示当前时间*/
+-----------+
| CURTIME() |
+-----------+
| 16:19:54  |
+-----------+

mysql> SELECT DATE_ADD('2018-04-03', INTERVAL 365 DAY);  /*日期的增加（单位也可以是year、month、week等）*/
+------------------------------------------+
| DATE_ADD('2018-04-03', INTERVAL 365 DAY) |
+------------------------------------------+
| 2019-04-03                               |
+------------------------------------------+

mysql> SELECT DATE_ADD('2018-04-03', INTERVAL -365 DAY);  /*日期的减少*/
+-------------------------------------------+
| DATE_ADD('2018-04-03', INTERVAL -365 DAY) |
+-------------------------------------------+
| 2017-04-03                                |
+-------------------------------------------+

mysql> SELECT DATEDIFF('2013-04-03','2014-04-03');     /*两个日期的之间的差值*/
+-------------------------------------+
| DATEDIFF('2013-04-03','2014-04-03') |
+-------------------------------------+
|                                -365 |
+-------------------------------------+

mysql> SELECT DATE_FORMAT('2018-04-03','%m/%d/%Y');    /*日期的格式化（转换显示格式）*/
+--------------------------------------+
| DATE_FORMAT('2018-04-03','%m/%d/%Y') |
+--------------------------------------+
| 04/03/2018                           |
+--------------------------------------+

6.5、信息函数

名称	描述
CONNECTION_ID（）	连接 ID（返回数据库的连接次数）
DATABASE（）	当前数据库
LAST_INSERT_ID（）	最后插入记录的 ID 号
USER（）	当前用户
VERSION（）	版本信息

mysql> SELECT CONNECTION_ID();   /*连接数据库的次数*/
+-----------------+
| CONNECTION_ID() |
+-----------------+
|               3 |
+-----------------+

mysql> SELECT DATABASE();     /*当前数据库*/
+------------+
| DATABASE() |
+------------+
| test       |
+------------+

mysql> INSERT tb_1(first_name,LAST_NAME) VALUES('A','B');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM tb_1;
+----+------------+-----------+
| id | first_name | LAST_NAME |
+----+------------+-----------+
|  1 | a          | b         |
|  2 | c          | d         |
|  3 | tom%       | 123       |
|  4 | A          | B         |
+----+------------+-----------+

mysql> SELECT LAST_INSERT_ID();   /*返回最后插入记录的 id，一次性插入多条记录的话只返回第一条*/
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                4 |
+------------------+

mysql> SELECT USER();         /*返回当前用户*/
+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+

mysql> SELECT VERSION();       /*返回版本信息*/
+------------+
| VERSION()  |
+------------+
| 5.7.20-log |
+------------+

6.6、聚合函数

名称	描述
AVG（）	平均值
COUNT（）	计数
MAX（）	最大值
MIN（）	最小值
SUM（）	求和

/*聚合函数里面的参数不能直接是数字，必须连接数据表*/
SELECT AVG(goods_prices) AS avg_prices FROM tdb_goods;  /*求数据表tdb_goods中 goods_prices的平均值*/

SELECT COUNT(goods_id) AS count_id FROM tdb_goods;   /*求数据表tdb_goods中 goods_id 共有几个*/

6.7、加密函数

名称	描述
MD5（）	信息摘要算法
PASSWORD（）	密码算法

mysql> SELECT MD5('admin');                    /*适用于web应用做准备*/
+----------------------------------+
| MD5('admin')                     |
+----------------------------------+
| 21232f297a57a5a743894a0e4a801fc3 |
+----------------------------------+

mysql> SELECT PASSWORD('admin');               /*适用于修改用户密码*/
+-------------------------------------------+
| PASSWORD('admin')                         |
+-------------------------------------------+
| *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
+-------------------------------------------+