MySQL中的常用函数(附实例)

最新推荐文章于 2024-07-25 15:20:00 发布

陨尘oO

最新推荐文章于 2024-07-25 15:20:00 发布

阅读量283

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/weixin_38610643/article/details/112646518

版权

与日期相关的函数	解释
addtime(date2, time interval)	日期上加一定的时间差
current_date()	当前系统日期
current_time()	当前系统时间
current_timestamp()	当前系统的时间戳(日期+时间)
now()	同current_timestamp()
date(datetime)	获取日期时间的日期部分
date_addd(date2, INTERVAL d_value d_type)	给一个日期上加一定的日期或时间
date_sub(date2, INTERVAL d_value d_type)	给一个日期减去一定的日期或时间
datediff(date1, date2)	返回两个日期的差
year/month/day/hour/minute/second(datetime)	获取当前年/月/…部分

与字符串相关的函数	解释
charset(str)	返回字符串的字符集
concat(string2 [,…])	连接字符串
instr(string, substring)	返回子串在字符串当中出现的位置
ucase(string2)	转为大写字母
lcase(string2)	转为小写字母
left(string2, length)	截取子串
length(string)	返回字符串长度
replace(str, search_str, replace_str)	替换字符串
strcmp(string1, string2)	比较字符串大小
substring(str, position [,length ])
ltrim(string2) / rtrim(string2)	trim()去掉字符串两端的空格 l是left r是right

与数学运算相关的函数	解释
ceiling()	向上取整(天花板)
floor()	向下取整(天花板)
mod()	取余
round()	四舍五入
rand()	0-1的随机数

其它一些常用函数	解释
ifnull(item,value)	空值处理,如果item是null,则用value替换
md5()	加密

实例：
（用select语句直接查询函数的返回值）

与日期相关的函数

mysql> select addtime("2020-01-14 22:12:40","5:20:40");
+------------------------------------------+
| addtime("2020-01-14 22:12:40","5:20:40") |
+------------------------------------------+
| 2020-01-15 03:33:20                      |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2021-01-14     |
+----------------+
1 row in set (0.00 sec)

mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 22:18:19       |
+----------------+
1 row in set (0.00 sec)

mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2021-01-14 22:18:52 |
+---------------------+
1 row in set (0.00 sec)

mysql> select current_timestamp;  //解释：有没有() 都可以
+---------------------+
| current_timestamp   |
+---------------------+
| 2021-01-14 22:18:58 |
+---------------------+
1 row in set (0.00 sec)

mysql> select now;	//错误表示 必须为now()
ERROR 1054 (42S22): Unknown column 'now' in 'field list'

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-01-14 22:20:30 |
+---------------------+
1 row in set (0.00 sec)

mysql> select date_add("2021-01-01",interval 2 day);
+---------------------------------------+
| date_add("2021-01-01",interval 2 day) |
+---------------------------------------+
| 2021-01-03                            |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select date_add("2021-01-01 00:00:00",interval 30 second);
+----------------------------------------------------+
| date_add("2021-01-01 00:00:00",interval 30 second) |
+----------------------------------------------------+
| 2021-01-01 00:00:30                                |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select datediff("2021-01-14","2020-01-14");
+-------------------------------------+
| datediff("2021-01-14","2020-01-14") |
+-------------------------------------+
|                                 366 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> select year("2021-01-14 00:00:00");
+-----------------------------+
| year("2021-01-14 00:00:00") |
+-----------------------------+
|                        2021 |
+-----------------------------+
1 row in set (0.00 sec)

与字符串相关的函数

mysql> select charset("张三");
+-------------------+
| charset("张三")   |
+-------------------+
| utf8mb4           |
+-------------------+
1 row in set (0.00 sec)

mysql> select concat("张三","李四","王五"); //用concat连接，不能用加号连接
+------------------------------------+
| concat("张三","李四","王五")       |
+------------------------------------+
| 张三李四王五                       |
+------------------------------------+
1 row in set (0.00 sec)

mysql> select instr("abcdefg","cde");  //编号从1开始，不是从0开始
+------------------------+
| instr("abcdefg","cde") |
+------------------------+
|                      3 |
+------------------------+
1 row in set (0.00 sec)

mysql> select left("abcdefg",4);  //截取左边四个字符
+-------------------+
| left("abcdefg",4) |
+-------------------+
| abcd              |
+-------------------+
1 row in set (0.00 sec)

mysql> select length("abcdefg");
+-------------------+
| length("abcdefg") |
+-------------------+
|                 7 |
+-------------------+
1 row in set (0.00 sec)

mysql> select length("张三");
+------------------+
| length("张三")   |
+------------------+
|                6 |
+------------------+
1 row in set (0.00 sec)

mysql> select replace("abbb","bb","22");
+---------------------------+
| replace("abbb","bb","22") |
+---------------------------+
| a22b                      |
+---------------------------+
1 row in set (0.00 sec)

mysql> select strcmp("a","ab"); //第一个比第二个小，-1
+------------------+
| strcmp("a","ab") |
+------------------+
|               -1 |
+------------------+
1 row in set (0.00 sec)

mysql> select strcmp("ab","a"); //第一个比第二个大，+1
+------------------+
| strcmp("ab","a") |
+------------------+
|                1 |
+------------------+
1 row in set (0.01 sec)

mysql> select strcmp("ab","ab");	//第一个等于第二个，0
+-------------------+
| strcmp("ab","ab") |
+-------------------+
|                 0 |
+-------------------+
1 row in set (0.00 sec)

mysql> select length("  w");
+---------------+
| length("  w") |
+---------------+
|             3 |
+---------------+
1 row in set (0.00 sec)

mysql> select length(trim("  w"));
+---------------------+
| length(trim("  w")) |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.00 sec)

与数学运算相关的函数

mysql> select ceiling(5.3);
+--------------+
| ceiling(5.3) |
+--------------+
|            6 |
+--------------+
1 row in set (0.00 sec)

mysql> select floor(5.3);
+------------+
| floor(5.3) |
+------------+
|          5 |
+------------+
1 row in set (0.00 sec)

mysql> select mod(10,3);
+-----------+
| mod(10,3) |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

mysql> select round(5.3);
+------------+
| round(5.3) |
+------------+
|          5 |
+------------+
1 row in set (0.00 sec)

mysql> select round(5.5);
+------------+
| round(5.5) |
+------------+
|          6 |
+------------+
1 row in set (0.00 sec)

mysql> select rand(); //0-1的随机数
+--------------------+
| rand()             |
+--------------------+
| 0.7927387252382363 |
+--------------------+
1 row in set (0.00 sec)

4.其它一些常用函数

mysql> select md5("123");
+----------------------------------+
| md5("123")                       |
+----------------------------------+
| 202cb962ac59075b964b07152d234b70 |		//将密码123变为秘文
+----------------------------------+
1 row in set (0.00 sec)