文章目录

1 日期函数
2 字符串函数
3 数学函数
4 其它函数

1 日期函数

日期类内置函数表：

函数名称	描述
current date0	当前日期
current time()	当前时间
current timestamp)	当前时间戳
date(datetime)	返回datetime参数的日期部分
date add(date, interval d_value_type)	在date中添加日期或时间interval后的数值单位可以是: year minute second day
date sub(date, interval d_value_type)	在date中减去日期或时间interval后的数值单位可以是: year minute second day
datediff(datel, date2)	两个日期的差，单位是天
now()	当前日期时间

获取年月日:

mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2023-04-22     |
+----------------+
1 row in set (0.00 sec)

获得时分秒：

mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 10:47:31       |
+----------------+
1 row in set (0.00 sec)

获得时间戳：

mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2023-04-22 10:47:48 |
+---------------------+
1 row in set (0.01 sec)

获得时间戳中年月日：

mysql> select date(current_timestamp());
+---------------------------+
| date(current_timestamp()) |
+---------------------------+
| 2023-04-22                |
+---------------------------+
1 row in set (0.00 sec)

在日期的基础上加时间：

mysql> select date_add('2023-4-22', interval 10 day);
+----------------------------------------+
| date_add('2023-4-22', interval 10 day) |
+----------------------------------------+
| 2023-05-02                             |
+----------------------------------------+
1 row in set (0.01 sec)

在日期的基础上加减：

mysql> select date_sub('2023-5-2', interval 10 day);
+---------------------------------------+
| date_sub('2023-5-2', interval 10 day) |
+---------------------------------------+
| 2023-04-22                            |
+---------------------------------------+
1 row in set (0.01 sec)

计算两个日期之间相差多少天：

mysql> select datediff('2023-5-2', '2023-4-22') day;
+------+
| day  |
+------+
|   10 |
+------+
1 row in set (0.02 sec)

当前日期时间：

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2023-04-22 13:22:10 |
+---------------------+
1 row in set (0.01 sec)

案例：

留言板内容

mysql> select *from msg;
+----+-----------+---------------------+
| id(留言排序) | conten(留言内容)    | sedtime(留言时间)             |
+----+-----------+---------------------+
|  1 | 你好      | 2023-04-22 13:29:55 |
|  2 | 我不好    | 2023-04-22 13:29:55 |
|  3 | 哈哈哈    | 2023-04-22 13:29:55 |
+----+-----------+---------------------+
3 rows in set (0.01 sec)

显示所有留言信息，发布日期只显示日期，不用显示时间

mysql> select conten, date(sedtime) from msg;
+-----------+---------------+
| conten    | date(sedtime) |
+-----------+---------------+
| 你好      | 2023-04-22    |
| 我不好    | 2023-04-22    |
| 哈哈哈    | 2023-04-22    |
+-----------+---------------+
3 rows in set (0.00 sec)

请查询在10分钟内发布的帖子

mysql> select * from msg where date_add(sedtime, interval 10 minute) > now();
+----+-----------+---------------------+
| id | conten    | sedtime             |
+----+-----------+---------------------+
|  1 | 你好      | 2023-04-22 13:29:55 |
|  2 | 我不好    | 2023-04-22 13:29:55 |
|  3 | 哈哈哈    | 2023-04-22 13:29:55 |
+----+-----------+---------------------+
3 rows in set (0.00 sec)

2 字符串函数

函数名称	描述
charset(str)	返回字符串字符集
concat(string2 [,])	连接字符串
instr(string,substring)	返回substring在string中出现的位置，没有返回0
ucase(string2)	转换成大写
lcase(string2)	转换成小写
left(string2, length)	从string2中的左边起length个字符
right(string2, length)	从string2中的右边起length个字符
length(string)	string的长度
replace(str, search str, replace_str)	在str中用replace_str替换search_str
strcmp(stringl, string2)	逐字符比较两字符串大小
substring(str, position [,length])	从str的postion开始，取length个字符
ltrim(string) rtrim(string) trim(string)	去除前空格、去除后空格、去除前空格和后空格

案例:
相关内容已经准备好了

链接：https://gitee.com/ding-xushengyun/linux__cpp/blob/master/scott_data.sql
准备工作，创建一个雇员信息表（来自oracle 9i的经典测试表）
- emp员工表
- dept部门表
- salgrade工资等级表
获取emp表的ename列的字符集

mysql> select charset(ename) '雇员姓名类型' from emp;
+--------------------+
| 雇员姓名类型       |
+--------------------+
| utf8               |
| utf8               |
| utf8               |
| utf8               |
| utf8               |
| utf8               |
| utf8               |
| utf8               |
| utf8               |
| utf8               |
| utf8               |
| utf8               |
| utf8               |
| utf8               |
+--------------------+
14 rows in set (0.00 sec)

显示emp表中每位雇员工资情况。

mysql> select concat(`ename`, '先生/女士, 你所在的职位是', `job`, ', 每月薪资为', `sal`, '$') '公司工.情况'from `emp`;
+-----------------------------------------------------------------------------+
| 公司工资情况                                                                |
+-----------------------------------------------------------------------------+
| SMITH先生/女士, 你所在的职位是CLERK, 每月薪资为800.00$                      |
| ALLEN先生/女士, 你所在的职位是SALESMAN, 每月薪资为1600.00$                  |
| WARD先生/女士, 你所在的职位是SALESMAN, 每月薪资为1250.00$                   |
| JONES先生/女士, 你所在的职位是MANAGER, 每月薪资为2975.00$                   |
| MARTIN先生/女士, 你所在的职位是SALESMAN, 每月薪资为1250.00$                 |
| BLAKE先生/女士, 你所在的职位是MANAGER, 每月薪资为2850.00$                   |
| CLARK先生/女士, 你所在的职位是MANAGER, 每月薪资为2450.00$                   |
| SCOTT先生/女士, 你所在的职位是ANALYST, 每月薪资为3000.00$                   |
| KING先生/女士, 你所在的职位是PRESIDENT, 每月薪资为5000.00$                  |
| TURNER先生/女士, 你所在的职位是SALESMAN, 每月薪资为1500.00$                 |
| ADAMS先生/女士, 你所在的职位是CLERK, 每月薪资为1100.00$                     |
| JAMES先生/女士, 你所在的职位是CLERK, 每月薪资为950.00$                      |
| FORD先生/女士, 你所在的职位是ANALYST, 每月薪资为3000.00$                    |
| MILLER先生/女士, 你所在的职位是CLERK, 每月薪资为1300.00$                    |
+-----------------------------------------------------------------------------+
14 rows in set (0.02 sec)

instr函数使用

mysql> select instr('bca', 'c') pos;
+-----+
| pos |
+-----+
|   2 |
+-----+
1 row in set (0.00 sec)

mysql> select instr('bca', 'ca') pos;
+-----+
| pos |
+-----+
|   2 |
+-----+
1 row in set (0.00 sec)

mysql> select instr('bca', 'ac') pos;
+-----+
| pos |
+-----+
|   0 |
+-----+
1 row in set (0.00 sec)

大小写转换

mysql> select ucase('a');
+------------+
| ucase('a') |
+------------+
| A          |
+------------+
1 row in set (0.00 sec)

mysql> select lcase('A');
+------------+
| lcase('A') |
+------------+
| a          |
+------------+
1 row in set (0.00 sec)

左右字符取出

mysql> select left('cab', 2);
+----------------+
| left('cab', 2) |
+----------------+
| ca             |
+----------------+
1 row in set (0.00 sec)

mysql> select left('cab', 0);
+----------------+
| left('cab', 0) |
+----------------+
|                |
+----------------+
1 row in set (0.00 sec)

mysql> select right('cab', 0);
+-----------------+
| right('cab', 0) |
+-----------------+
|                 |
+-----------------+
1 row in set (0.00 sec)

mysql> select right('cab', 1);
+-----------------+
| right('cab', 1) |
+-----------------+
| b               |
+-----------------+
1 row in set (0.00 sec)

字符串长度

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

mysql> select length('哈哈哈');
+---------------------+
| length('哈哈哈')    |
+---------------------+
|                   9 |
+---------------------+
1 row in set (0.00 sec)

将emp表中所有名字中有S的替换成’上海’

mysql> select replace(`ename`, 'S', '上海') newename, ename from `emp`;
+------------+--------+
| newename   | ename  |
+------------+--------+
| 上海MITH   | SMITH  |
| ALLEN      | ALLEN  |
| WARD       | WARD   |
| JONE上海   | JONES  |
| MARTIN     | MARTIN |
| BLAKE      | BLAKE  |
| CLARK      | CLARK  |
| 上海COTT   | SCOTT  |
| KING       | KING   |
| TURNER     | TURNER |
| ADAM上海   | ADAMS  |
| JAME上海   | JAMES  |
| FORD       | FORD   |
| MILLER     | MILLER |
+------------+--------+

截取emp表中ename字段的第二个到第三个字符

mysql> select substring(`ename`, 2, 2) newename, `ename` from `emp`;
+----------+--------+
| newename | ename  |
+----------+--------+
| MI       | SMITH  |
| LL       | ALLEN  |
| AR       | WARD   |
| ON       | JONES  |
| AR       | MARTIN |
| LA       | BLAKE  |
| LA       | CLARK  |
| CO       | SCOTT  |
| IN       | KING   |
| UR       | TURNER |
| DA       | ADAMS  |
| AM       | JAMES  |
| OR       | FORD   |
| IL       | MILLER |
+----------+--------+
14 rows in set (0.01 sec)

以首字母小写的方式显示所有员工的姓名

mysql> select concat(lcase(substring(`ename`, 1, 1)), substring(`ename`, 2)) newename, `ename` from `emp`
`;
+----------+--------+
| newename | ename  |
+----------+--------+
| sMITH    | SMITH  |
| aLLEN    | ALLEN  |
| wARD     | WARD   |
| jONES    | JONES  |
| mARTIN   | MARTIN |
| bLAKE    | BLAKE  |
| cLARK    | CLARK  |
| sCOTT    | SCOTT  |
| kING     | KING   |
| tURNER   | TURNER |
| aDAMS    | ADAMS  |
| jAMES    | JAMES  |
| fORD     | FORD   |
| mILLER   | MILLER |
+----------+--------+
14 rows in set (0.00 sec

3 数学函数

函数名称	描述
abs(number)	绝对值函数
bin(decimal number)	十进制转换二进制
hex(decimalNumber)	转换成十六进制
conv(number,from base,to base)	进制转换
ceiling(number)	向上去整
floor(number)	向下去整
format(number,decimal places)	格式化，保留小数位数
rand()	返回随机浮点数，范围[0.0,1.0)
mod (numberdenominator)	取模，求余

案例：

绝对值函数

mysql> select abs(-2);
+---------+
| abs(-2) |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

十进制转换二进制

mysql> select bin(23);
+---------+
| bin(23) |
+---------+
| 10111   |
+---------+
1 row in set (0.00 sec)

转换成十六进制

mysql> select hex(23);
+---------+
| hex(23) |
+---------+
| 17      |
+---------+
1 row in set (0.00 sec)

进制转换

mysql> select conv(23, 10, 2);
+-----------------+
| conv(23, 10, 2) |
+-----------------+
| 10111           |
+-----------------+
1 row in set (0.00 sec)

mysql> select conv(10111, 2, 10);
+--------------------+
| conv(10111, 2, 10) |
+--------------------+
| 23                 |
+--------------------+
1 row in set (0.00 sec)

向上去整

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

向下去整

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

格式化，保留小数位数

mysql> select format(3.1415, 2);
+-------------------+
| format(3.1415, 2) |
+-------------------+
| 3.14              |
+-------------------+
1 row in set (0.00 sec)

mysql> select format(3.1415, 3);
+-------------------+
| format(3.1415, 3) |
+-------------------+
| 3.142             |
+-------------------+
1 row in set (0.00 sec

返回随机浮点数，范围[0.0,1.0)

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.8106659993628654 |
+--------------------+
1 row in set (0.00 sec)

取模，求余

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

4 其它函数

user() 查询当前用户

mysql> select user();
+--------+
| user() |
+--------+
| root@  |
+--------+
1 row in set (0.00 sec)

md5(str)对一个字符串进行md5摘要，摘要后得到一个32位字符串

mysql> select md5(123);
+----------------------------------+
| md5(123)                         |
+----------------------------------+
| 202cb962ac59075b964b07152d234b70 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select md5(122);
+----------------------------------+
| md5(122)                         |
+----------------------------------+
| a0a080f42e6f13b3a2df133f073095dd |
+----------------------------------+
1 row in set (0.00 sec)

database()显示当前正在使用的数据库

mysql> select database();
+------------+
| database() |
+------------+
| scott      |
+------------+
1 row in set (0.00 sec)

password()函数，MySQL数据库使用该函数对用户加密

mysql> select password('root');
+-------------------------------------------+
| password('root')                          |
+-------------------------------------------+
| *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select password('sssssssssssssss');
+-------------------------------------------+
| password('sssssssssssssss')               |
+-------------------------------------------+
| *43B2A837F64B9DE6A4C2F25F43BCAC10FC45E38B |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)

ifnull（val1， val2）如果val1为null，返回val2，否则返回val1的值

mysql> select ifnull(null, 1);
+-----------------+
| ifnull(null, 1) |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.00 sec)

mysql> select ifnull(2, 1);
+--------------+
| ifnull(2, 1) |
+--------------+
|            2 |
+--------------+
1 row in set (0.00 sec)