系统内置函数
字符函数
函数名称 | 描述 |
CONCAT() | 字符连接 |
CONCAT_WS() | 使用指定的分隔符进行字符连接 |
FORMAT() | 数字格式化 |
LOWER() | 转换成小写字母 |
UPPER() | 转换成大写字母 |
LEFT() | 获取左侧字符 |
RIGHT() | 获取右侧字符 |
LENGTH() | 获取字符串长度 |
LTRIM() | 删除前导空格 |
RTRIM() | 删除后续空格 |
TRIM() | 删除前导和后续空格或特定字符 |
SUBSTRING() | 字符串截取 |
[NOT] LIKE | 模式匹配 |
REPLACE() | 字符串替换 |
连接字符
SELECT CONCAT('AAA','BBB','CCC');
连接表中字段
SELECT CONCAT(col_name1,col_name2,...) AS alias_name FROM table_name;
使用指定分隔符连接字符
CONCAT_WS()函数的第一个参数为指定的分隔符,其余的参数为要连接的字符
SELECT CONCAT_WS('-','AA','BB',...);
数字格式化
FORMAT()函数用于将数字转化成字符,第一个参数为待转化的数字,第二个参数为保留的小数位数
SELECT FORMAT(173.25,1);
获取左右侧字符
LEFT(), RIGHT()函数分别用于获取字符串左右侧字符,第一个参数为字符串,第二个参数为提取字符的个数
SELECT LEFT('mysql',2);
删除前导和后续空格或特定字符
TRIM()函数用于删除前导或者后续空格或指定字符
//删除空格
SELECT TRIM(' MYSQL ');
//删除前导字符'?'
SELECT TRIM(LEADING'?' FROM '???MYSQL??');
//删除后续字符'?'
SELECT TRIM(TRAILING'?' FROM '???MYSQL??');
//删除前导和后续'?'
SELECT TRIM(BOTH'?' FROM '???MYSQL??');
字符串替换
REPLACE()函数可以用于字符串中的字符替换,第一个参数为待处理的字符串,第二个参数为字符串中待替换的字符(字符个数不限),第三个参数为用于替换的字符(字符个数不限)
使用TRIM()函数无法去除字符串中间的字符,此时可以使用REPLACE('AAA??BB','??','')的方式来解决
SELECT REPLACE('??MY??SQL???','?','!!');
字符串截取
SUBSTRING()用于提取字符串,第一个参数为待截取的字符串,第二个参数为开始截取的位置(起始位置从1开始),第三个参数为截取字符的个数(若省略第三个参数,则一致截取到末尾)
第二个参数可以为负值,表示倒着数,但第三个参数不可以为负
//截取'MY'
SELECT SUBSTRING('MYSQL',1,2);
//截取'SQL'
SELECT SUBSTRING('MYSQL',3);
模式匹配
在MySQL中,%代表任意多个字符包括0个,_代表任意一个字符
//从表中寻找某个字段的值带有字符'0'的记录
SELECT * FROM table_name WHERE col_name LIKE '%0%';
//寻找带有字符'%'或'_'的值时,需要使用ESCAPE来设置转义字符,转移字符可以任意选取,下面的列子将'1'设为转义字符,即1后面的%不作为匹配符处理
SELECT * FROM table_name WHERE col_name LIKE '%1%%' ESCAPE '1';
数值运算符与函数
名称 | 描述 |
CEIL() | 进1取整 |
DIV | 整数除法 |
FLOOR() | 舍1取整 |
MOD | 取余数(取模) |
POWER() | 幂运算 |
ROUND() | 四舍五入 |
TRUNCATE() | 数字截取 |
"+", "-", "*", "/" 等数值运算符在MySQL中均适用,在除法运算时,若想使用整数除法,可以使用DIV
root@localhost (none)>SELECT 5/2;
+--------+
| 5/2 |
+--------+
| 2.5000 |
+--------+
1 row in set (0.00 sec)
root@localhost (none)>SELECT 5 DIV 2;
+---------+
| 5 DIV 2 |
+---------+
| 2 |
+---------+
1 row in set (0.00 sec)
取模运算符MOD既可以对整数取模又可以对小数取模,等价于运算符"%".
root@localhost (none)>SELECT 5.3 MOD 2;
+-----------+
| 5.3 MOD 2 |
+-----------+
| 1.3 |
+-----------+
1 row in set (0.00 sec)
root@localhost (none)>SELECT 5.3%2;
+-------+
| 5.3%2 |
+-------+
| 1.3 |
+-------+
1 row in set (0.00 sec)
四舍五入函数
root@localhost (none)>SELECT ROUND(4.217,2);
+----------------+
| ROUND(4.217,2) |
+----------------+
| 4.22 |
+----------------+
1 row in set (0.01 sec)
root@localhost (none)>SELECT ROUND(4.217,0);
+----------------+
| ROUND(4.217,0) |
+----------------+
| 4 |
+----------------+
1 row in set (0.00 sec)
数字截取
root@localhost (none)>SELECT TRUNCATE(123.89,1);
+--------------------+
| TRUNCATE(123.89,1) |
+--------------------+
| 123.8 |
+--------------------+
1 row in set (0.00 sec)
root@localhost (none)>SELECT TRUNCATE(123.89,0);
+--------------------+
| TRUNCATE(123.89,0) |
+--------------------+
| 123 |
+--------------------+
1 row in set (0.00 sec)
root@localhost (none)>SELECT TRUNCATE(123.89,-1);
+---------------------+
| TRUNCATE(123.89,-1) |
+---------------------+
| 120 |
+---------------------+
1 row in set (0.00 sec)
root@localhost (none)>SELECT TRUNCATE(123.89,-2);
+---------------------+
| TRUNCATE(123.89,-2) |
+---------------------+
| 100 |
+---------------------+
1 row in set (0.00 sec)
比较运算符和函数
名称 | 描述 |
[NOT] BETWEEN...AND... | [不]在范围之内 |
[NOT] IN() | [不]在列出值范围之内 |
IS [NOT] NULL | [不]为空 |
[NOT] BETWEEN...AND...用于判断一个数在不在某个闭合的区间内
root@localhost (none)>SELECT 2 BETWEEN 10 AND 20;
+---------------------+
| 2 BETWEEN 10 AND 20 |
+---------------------+
| 0 |
+---------------------+
1 row in set (0.00 sec)
[NOT] IN()用于判断某个值在不在列出的值之中
root@localhost (none)>SELECT 4 IN(1,2,4);
+-------------+
| 4 IN(1,2,4) |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
IS [NOT] NULL用于查询NULL值,NULL 是NULL,而 ' ', 0均不是NULL.可以查找NULL值
SELECT * FROM table_name WHERE col_name IS NULL;
root@localhost (none)>SELECT NULL IS NULL;
+--------------+
| NULL IS NULL |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
root@localhost (none)>SELECT '' IS NULL;
+------------+
| '' IS NULL |
+------------+
| 0 |
+------------+
1 row in set (0.00 sec)
日期时间函数
名称 | 描述 |
NOW() | 当前日期和时间 |
CURDATE() | 当前日期 |
CURTIME() | 当前时间 |
DATE_ADD() | 日期变化 |
DATEDIFF() | 日期差值 |
DATE_FORMAT() | 日期格式化 |
DATE_ADD()可以对日期做增加和减少,改变单位包括DAY, WEEK, YEAR等
root@localhost (none)>SELECT DATE_ADD('2019-1-20',INTERVAL 365 DAY);
+----------------------------------------+
| DATE_ADD('2019-1-20',INTERVAL 365 DAY) |
+----------------------------------------+
| 2020-01-20 |
+----------------------------------------+
1 row in set (0.01 sec)
root@localhost (none)>SELECT DATE_ADD('2019-1-20',INTERVAL -365 DAY);
+-----------------------------------------+
| DATE_ADD('2019-1-20',INTERVAL -365 DAY) |
+-----------------------------------------+
| 2018-01-20 |
+-----------------------------------------+
1 row in set (0.00 sec)
DATEDIFF()用于计算两个日期的日期差
root@localhost (none)>SELECT DATEDIFF('2018-3-15','2019-2-14');
+-----------------------------------+
| DATEDIFF('2018-3-15','2019-2-14') |
+-----------------------------------+
| -336 |
+-----------------------------------+
1 row in set (0.00 sec)
DATE_FORMAT()用于将日期格式化
root@localhost (none)>SELECT DATE_FORMAT('2015-10-5','%m/%d/%Y');
+-------------------------------------+
| DATE_FORMAT('2015-10-5','%m/%d/%Y') |
+-------------------------------------+
| 10/05/2015 |
+-------------------------------------+
1 row in set (0.00 sec)
信息函数
名称 | 描述 |
CONNECTION_ID() | 连接ID |
DATEBASE() | 当前数据库 |
LAST_INSERT_ID() | 最后插入记录的ID号 |
USER() | 当前用户 |
VERSION() | 版本信息 |
在插入操作时,若同时插入多条记录,使用 LAST_INSERT_ID() 获得的是多条记录的第一条记录的ID.
聚合函数
聚合函数的特点是只有一个返回值
名称 | 描述 |
AVG() | 平均值 |
COUNT() | 计数 |
MAX() | 最大值 |
MIN() | 最小值 |
SUM() | 求和 |
SELECT AVG(col_name) AS XXX FROM table_name;
//对所求结果保留两位小数
SELECT ROUND(AVG(col_name)) AS XXX FROM table_name;
加密函数
名称 | 描述 |
MD5() | 信息摘要算法 |
PASSWORD() | 密码算法 |
修改mysql登录密码
SET PASSWORD=PASSWORD('new_password');
自定义函数
- 用户自定义函数(user-defined function, UDF)是一种对 mysql 的扩展途径,其用法与内置函数相同
- 函数可以返回任意类型的值,也可以接收这些类型的参数
创建自定义函数
CREATE FUNCTION function_name
RETURNS {STRING | INTEGER | REAL | DECIMAL}
routine_body
- 函数体由合法的SQL语句构成
- 函数体可以是简单的SELECT或者INSERT语句
- 函数体如果为复合结构则使用 BEGIN...END 语句
- 复合结构可以包含声明,循环,控制结构
//不带参数的函数
CREATE FUNCTION f1()
RETURNS VARCHAR(30)
RETURN DATE_FORMATE(NOW(),'%Y年%M月%D日 %H时:%i分:%m秒');
//带有参数的函数
CREATE FUNCTION f2(num1 SMALLINT UNSIGNED, num2 SMALLINT UNSIGNED)
RETURNS FLOAT(10,2) UNSIGNED
RETURN (num1+num2)/2;
mysql 默认的语句结束符是分号,修改结束符可以通过DELIMITER语句
//将语句结束符修改为‘//’
DELIMITER //
//聚合体
CREATE FUNCTION adduser(username VARCHAR(20))
RETURNS INT UNSIGNED
BEGIN
INSERT test(username) VALUES (username);
RETURN LAST_INSERT_ID();
END//
调用函数
SELECT FUNCTION function_name;
删除函数
DROP FUNCTION function_name;