MySQL 运算符和函数

字符函数

CONCAT() 字符连接

• 可以两个字符串连接也可多个字符串连接。

mysql> SELECT CONCAT('immoc', 'MySQL');
+--------------------------+
| CONCAT('immoc', 'MySQL') |
+--------------------------+
| immocMySQL               |
+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT('immoc', '-', 'MySQL');
+-------------------------------+
| CONCAT('immoc', '-', 'MySQL') |
+-------------------------------+
| immoc-MySQL                   |
+-------------------------------+
1 row in set (0.00 sec)

• 创建数据表

mysql> CREATE TABLE IF NOT EXISTS test(
    -> first_name VARCHAR(20) DEFAULT NULL,
    -> last_name VARCHAR(20) DEFAULT NULL
    -> );
Query OK, 0 rows affected (0.02 sec)

• 查看表结构

mysql> DESC test;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| first_name | varchar(20) | YES  |     | NULL    |       |
| last_name  | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

• 添加数据

mysql> INSERT test (first_name, last_name) VALUES('A', 'B');
mysql> INSERT test (first_name, last_name) VALUES('C', 'D');
mysql> INSERT test (first_name, last_name) VALUES('tom%', '123');
mysql> INSERT test (last_name) VALUES( '11');

mysql> SELECT * FROM test;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| A          | B         |
| C          | D         |
| tom%       | 123       |
| NULL       | 11        |
+------------+-----------+
4 rows in set (0.00 sec)

• 连接表中的字符串 first_name 和 last_name

mysql> SELECT CONCAT(first_name, last_name) AS fillname FROM test;
+----------+
| fillname |
+----------+
| AB       |
| CD       |
| tom%123  |
| NULL     |
+----------+
4 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)

FORMAT() 数字格式化

保留多少位小数

mysql> SELECT FORMAT(1256.75, 1);
+--------------------+
| FORMAT(1256.75, 1) |
+--------------------+
| 1,256.8            |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT FORMAT(1256.75, 0);
+--------------------+
| FORMAT(1256.75, 0) |
+--------------------+
| 1,257              |
+--------------------+
1 row in set (0.00 sec)

LOWER() 转换为小写字母

mysql> SELECT LOWER('MySQL');
+----------------+
| LOWER('MySQL') |
+----------------+
| mysql          |
+----------------+
1 row in set (0.00 sec)

UPPER() 转换为大写字母

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

LEFT() 获取左侧字符

mysql> SELECT LEFT('MySQL', 2);
+------------------+
| LEFT('MySQL', 2) |
+------------------+
| My               |
+------------------+
1 row in set (0.00 sec)

RIGHT() 获取右侧字符

mysql> SELECT RIGHT('MySQL', 3);
+-------------------+
| RIGHT('MySQL', 3) |
+-------------------+
| SQL               |
+-------------------+
1 row in set (0.00 sec)

LENGTH() 获取字符串长度

字符串中的空格也计算在长度之内。

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

LTRIM() 删除前导空格

mysql> SELECT LTRIM('    MySQL    ');
+------------------------+
| LTRIM('  MySQL    ') |
+------------------------+
| MySQL                  |
+------------------------+
1 row in set (0.00 sec)

RTRIM() 删除后续空格

mysql> SELECT LENGTH(RTRIM('  MySQL    '));
+------------------------------+
| LENGTH(RTRIM('  MySQL    ')) |
+------------------------------+
|                            7 |
+------------------------------+
1 row in set (0.00 sec)

TRIM() 删除前导和后续空格

mysql> SELECT LENGTH(TRIM('  MySQL    '));
+-----------------------------+
| LENGTH(TRIM('  MySQL    ')) |
+-----------------------------+
|                           5 |
+-----------------------------+
1 row in set (0.00 sec)

删除指定的前导符号

mysql> SELECT TRIM(LEADING '?' FROM '??MySQL???');
+-------------------------------------+
| TRIM(LEADING '?' FROM '??MySQL???') |
+-------------------------------------+
| MySQL???                            |
+-------------------------------------+
1 row in set (0.00 sec)

删除指定的后续符号

mysql> SELECT TRIM(TRAILING '?' FROM '??MySQL???');
+--------------------------------------+
| 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)

REPLACE() 字符串替换

mysql> SELECT REPLACE('??My?SQL???', '?', '');
+---------------------------------+
| REPLACE('??My?SQL???', '?', '') |
+---------------------------------+
| MySQL                           |
+---------------------------------+
1 row in set (0.00 sec)

SUBSTRING() 字符串截取

字符串编号从0开始。

mysql> SELECT SUBSTRING('MySQL', 1, 2);
+--------------------------+
| SUBSTRING('MySQL', 1, 2) |
+--------------------------+
| My                       |
+--------------------------+
1 row in set (0.00 sec)

// 从第3位到最后
mysql> SELECT SUBSTRING('MySQL', 3);
+-----------------------+
| SUBSTRING('MySQL', 3) |
+-----------------------+
| SQL                   |
+-----------------------+
1 row in set (0.00 sec)

// 起始位置可以为负值，表示从倒数第3个开始取到最后
mysql> SELECT SUBSTRING('MySQL', -3);
+------------------------+
| SUBSTRING('MySQL', -3) |
+------------------------+
| SQL                    |
+------------------------+
1 row in set (0.00 sec)

// 从倒数第3个开始取2个
mysql> SELECT SUBSTRING('MySQL', -3, 2);
+---------------------------+
| SUBSTRING('MySQL', -3, 2) |
+---------------------------+
| SQ                        |
+---------------------------+
1 row in set (0.00 sec)

[NOT] LIKE 模式匹配

%（百分号）： 代表任意个字符

**_（下划线）：**代表任意一个字符

mysql> SELECT 'MySQL' LIKE 'M%';
+-------------------+
| 'MySQL' LIKE 'M%' |
+-------------------+
|                 1 |   # 1代表ture
+-------------------+
1 row in set (0.00 sec)

• 查找 test 表中 first_name 包含 'o’的行：

mysql> SELECT * FROM test WHERE first_name LIKE '%o%';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| tom%       | 123       |
+------------+-----------+
1 row in set (0.00 sec)

• 匹配通配符 ESCAPE

mysql> SELECT * FROM test WHERE first_name LIKE '%2%%' ESCAPE 2;  # 字符2也可以为其他任意字符
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| tom%       | 123       |
+------------+-----------+
1 row in set (0.00 sec)

---

数值运算符和函数

算数符 + - * /

mysql> SELECT 8 / 3;
+--------+
| 8 / 3  |
+--------+
| 2.6667 |
+--------+
1 row in set (0.00 sec)

CEIL() 向上取整

mysql> SELECT CEIL(3.01);
+------------+
| CEIL(3.01) |
+------------+
|          4 |
+------------+
1 row in set (0.00 sec)

FLOOR() 向下取整

mysql> SELECT FLOOR(3.99);
+-------------+
| FLOOR(3.99) |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)

DIV 整数除法

DIV 是运算符，不是函数。

mysql> SELECT 5 DIV 4;
+---------+
| 5 DIV 4 |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

MOD 取余数（取模）

MOD 是运算符， 等价于==%==，不是函数， 既可以对整数取模，也可以对浮点数取模。

mysql> SELECT 5 MOD 3;
+---------+
| 5 MOD 3 |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

mysql> SELECT 5 % 3;
+-------+
| 5 % 3 |
+-------+
|     2 |
+-------+
1 row in set (0.00 sec)

mysql> SELECT 5.4 % 3;
+---------+
| 5.4 % 3 |
+---------+
|     2.4 |
+---------+
1 row in set (0.00 sec)

POWER() 幂运算

mysql> SELECT POWER(2, 10);
+--------------+
| POWER(2, 10) |
+--------------+
|         1024 |
+--------------+
1 row in set (0.00 sec)

ROUND() 四舍五入

mysql> SELECT ROUND(3.248, 2);
+-----------------+
| ROUND(3.248, 2) |
+-----------------+
|            3.25 |
+-----------------+
1 row in set (0.00 sec)

TRUNCATE() 数字截取

mysql> SELECT TRUNCATE(125.89, 1);
+---------------------+
| TRUNCATE(125.89, 1) |
+---------------------+
|               125.8 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT TRUNCATE(125.89, -1);
+----------------------+
| TRUNCATE(125.89, -1) |
+----------------------+
|                  120 |
+----------------------+
1 row in set (0.00 sec)

---

比较运算符和函数

[NOT] BETWEEN … AND … [不] 在范围内

mysql> SELECT 15 BETWEEN 1 AND 20;
+---------------------+
| 15 BETWEEN 1 AND 20 |
+---------------------+
|                   1 |  # true 
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT 25 BETWEEN 1 AND 20;
+---------------------+
| 25 BETWEEN 1 AND 20 |
+---------------------+
|                   0 |  #false
+---------------------+
1 row in set (0.00 sec)

[NOT] IN() [不] 在列出值内

mysql> SELECT 10 IN (5, 10, 15, 20);
+-----------------------+
| 10 IN (5, 10, 15, 20) |
+-----------------------+
|                     1 |  // true
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT 12 IN (5, 10, 15, 20);
+-----------------------+
| 12 IN (5, 10, 15, 20) |
+-----------------------+
|                     0 |  // false
+-----------------------+
1 row in set (0.00 sec)

IS [NOT] NULL [不] 为空

‘’(空串)，0 不是NULL。

mysql> SELECT NULL is NULL
+--------------+
| NULL is NULL |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT '' is NULL;
+------------+
| '' is NULL |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT 0 is NULL;
+-----------+
| 0 is NULL |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

SELECT first_name, last_name FROM test WHERE first_name IS NULL;
SELECT first_name, last_name FROM test WHERE first_name IS NOT NULL;

---

时间和日期函数

NOW() 当前时间和日期

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2019-02-20 16:51:16 |
+---------------------+
1 row in set (0.01 sec)

CURDATE() 当前日期

mysql> SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2019-02-20 |
+------------+
1 row in set (0.00 sec)

CURTIME() 当前时间

mysql> SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 16:52:26  |
+-----------+
1 row in set (0.01 sec)

DATE_ADD() 日期变化

// 在指定日期前加上365天
mysql> SELECT DATE_ADD('2019-2-20', INTERVAL 365 DAY);
+-----------------------------------------+
| DATE_ADD('2019-2-20', INTERVAL 365 DAY) |
+-----------------------------------------+
| 2020-02-20                              |
+-----------------------------------------+
1 row in set (0.00 sec)

// 在指定日期前减去365天
mysql> SELECT DATE_ADD('2019-2-20', INTERVAL -365 DAY);
+------------------------------------------+
| DATE_ADD('2019-2-20', INTERVAL -365 DAY) |
+------------------------------------------+
| 2018-02-20                               |
+------------------------------------------+
1 row in set (0.00 sec)

DATEDIFF() 日期差值

mysql> SELECT DATEDIFF('2018-2-20', '2019-2-20');
+------------------------------------+
| DATEDIFF('2018-2-20', '2019-2-20') |
+------------------------------------+
|                               -365 |
+------------------------------------+
1 row in set (0.00 sec)

DATE_FORMAT() 日期格式化

mysql> SELECT DATE_FORMAT('2019-2-20', '%m/%d/%Y');
+--------------------------------------+
| DATE_FORMAT('2019-2-20', '%m/%d/%Y') |
+--------------------------------------+
| 02/20/2019                           |
+--------------------------------------+
1 row in set (0.00 sec)

---

信息函数

CONNECTION_ID() 当前用户连接的ID

mysql> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.00 sec)

DATABASE() 当前数据库

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)

LAST_INSERT_ID() 最后一次插入记录的ID号

返回的上一次往数据库插入数据的自动排序的且为主键的ID（没有不显示），同时写入多条时返回多条中的第一条的ID。

mysql> SELECT LAST_INSERT_ID() ;
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

USER() 当前用户

mysql> SELECT USER();
+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

VERSION()

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.5.37    |
+-----------+
1 row in set (0.00 sec)

---

聚合函数（多行函数）

AVG() 平均值

mysql> SELECT AVG(goods_price) AS avg_price FROM tdb_goods;

COUNT() 计数

mysql> SELECT COUNT(goods_id) AS counts FROM tdb_goods;

MAX() 最大值

mysql> SELECT MAX(goods_price) AS expensive_price FROM tdb_goods;

MIN() 最小值

mysql> SELECT Min(goods_price) AS cheap_price FROM tdb_goods;

SUM() 求和

mysql> SELECT SUM(goods_price) AS sum_price FROM tdb_goods;

---

加密函数

MD5() 信息摘要算法

mysql> SELECT MD5('MySQL');
+----------------------------------+
| MD5('MySQL')                     |
+----------------------------------+
| 62a004b95946bb97541afa471dcca73a |
+----------------------------------+
1 row in set (0.00 sec)

PASSWORD() 密码算法

修改数据库登录密码

mysql> SET PASSWORD = PASSWORD('111');
Query OK, 0 rows affected (0.01 sec)