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)