函数
1. 日期函数
获得年月日：
mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2024-03-23 |
+----------------+
1 row in set (0.00 sec)
获得时分秒：
mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 12:35:51 |
+----------------+
1 row in set (0.00 sec)
获得时间戳：
mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2024-03-23 12:36:36 |
+---------------------+
1 row in set (0.00 sec)
在日期的基础上加日期：
select date_add('2017-10-28', interval 10 day);
+-----------------------------------------+
| date_add('2017-10-28', interval 10 day) |
+-----------------------------------------+
| 2017-11-07 |
+-----------------------------------------+
在日期的基础上减去时间：
select date_sub('2017-10-1', interval 2 day);
+---------------------------------------+
| date_sub('2017-10-1', interval 2 day) |
+---------------------------------------+
| 2017-09-29 |
+---------------------------------------+
计算两个日期之间相差多少天：
select datediff('2017-10-10', '2016-9-1');
+------------------------------------+
| datediff('2017-10-10', '2016-9-1') |
+------------------------------------+
| 404 |
+------------------------------------+
案例-1：
创建一张表，记录生日
create table tmp(
id int primary key auto_increment,
birthday date
);
添加当前日期：
insert into tmp(birthday) values(current_date());
mysql> select * from tmp;
+----+------------+
| id | birthday |
+----+------------+
| 1 | 2017-11-19 |
+----+------------+
案例-2：
创建一个留言表
mysql> create table msg (
id int primary key auto_increment,
content varchar(30) not null,
sendtime datetime
);
插入数据
mysql> insert into msg(content,sendtime) values('hello1', now());
mysql> insert into msg(content,sendtime) values('hello2', now());
mysql> select * from msg;
+----+---------+---------------------+
| id | content | sendtime |
+----+---------+---------------------+
| 1 | hello1 | 2017-11-19 14:12:20 |
| 2 | hello2 | 2017-11-19 14:13:21 |
+----+---------+---------------------+
显示所有留言信息，发布日期只显示日期，不用显示时间
select content,date(sendtime) from msg;
请查询在2分钟内发布的帖子
select * from msg where date_add(sendtime, interval 2 minute) > now();
理解：
------------------------------|-----------|-------------|------------------
初始时间 now() 初始时间+2min
2. 字符串函数
这里的学习需要用到：
之前的exam_result 表格
具体的数据请看这篇文章！
和 scott 数据库
在标题7的位置
案例:
mysql> select * from emp;
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
| 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 |
| 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 |
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 |
| 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
| 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
| 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 |
| 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 |
| 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
| 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
| 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
| 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)
获取emp表的ename列的字符集
mysql> select charset(ename) from emp;
+----------------+
| charset(ename) |
+----------------+
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
+----------------+
14 rows in set (0.00 sec)
mysql> select * from exam_result;
+----+----------+---------+------+---------+
| id | name | chinese | math | english |
+----+----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 78 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
| 6 | 孙权 | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 65 | 30 |
+----+----------+---------+------+---------+
7 rows in set (0.00 sec)
要求显示exam_result表中的信息，显示格式：“XXX的语文是XXX分，数学XXX分”
mysql> select concat(name, '的语文是',chinese,'分，数学是',math,'分') as '分数' from exam_result;
+------------------------------------------+
| 分数 |
+------------------------------------------+
| 唐三藏的语文是67分，数学是98分 |
| 孙悟空的语文是87分，数学是78分 |
| 猪悟能的语文是88分，数学是98分 |
| 曹孟德的语文是82分，数学是84分 |
| 刘玄德的语文是55分，数学是85分 |
| 孙权的语文是70分，数学是73分 |
| 宋公明的语文是75分，数学是65分 |
+------------------------------------------+
求学生表中学生姓名占用的字节数
mysql> select length(name), name from exam_result;
+--------------+----------+
| length(name) | name |
+--------------+----------+
| 9 | 唐三藏 |
| 9 | 孙悟空 |
| 9 | 猪悟能 |
| 9 | 曹孟德 |
| 9 | 刘玄德 |
| 6 | 孙权 |
| 9 | 宋公明 |
+--------------+----------+
7 rows in set (0.00 sec)
注意：length函数返回字符串长度，以字节为单位。如果是多字节字符则计算多个字节数；
如果是单字节字符则算作一个字节。比如：字母，数字算作一个字节，中文表示多个字节数（与字符集编码有关）
将EMP表中所有名字中有S的替换成'上海'
mysql> select replace(ename, 'S', '上海') ,ename from emp;
+------------------------------+--------+
| replace(ename, 'S', '上海') | 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 |
+-------------------------------+--------+
14 rows in set (0.00 sec)
截取EMP表中ename字段的第二个到第三个字符
mysql> select substring(ename, 2, 2), ename from emp;
+------------------------+--------+
| substring(ename, 2, 2) | 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.00 sec)
以首字母小写的方式显示所有员工的姓名
mysql> select concat(lcase(substring(ename, 1, 1)),substring(ename,2)) from emp;
+----------------------------------------------------------+
| concat(lcase(substring(ename, 1, 1)),substring(ename,2)) |
+----------------------------------------------------------+
| sMITH |
| aLLEN |
| wARD |
| jONES |
| mARTIN |
| bLAKE |
| cLARK |
| sCOTT |
| kING |
| tURNER |
| aDAMS |
| jAMES |
| fORD |
| mILLER |
+----------------------------------------------------------+
14 rows in set (0.00 sec)
3. 数学函数
绝对值
mysql> select abs(-100.2);
+-------------+
| abs(-100.2) |
+-------------+
| 100.2 |
+-------------+
1 row in set (0.00 sec)
向上取整
mysql> select ceiling(23.04);
+----------------+
| ceiling(23.04) |
+----------------+
| 24 |
+----------------+
1 row in set (0.00 sec)
向下取整
mysql> select floor(23.7);
+-------------+
| floor(23.7) |
+-------------+
| 23 |
+-------------+
1 row in set (0.00 sec)
保留2位小数位数（小数四舍五入)
mysql> select format(12.3456, 2);
+--------------------+
| format(12.3456, 2) |
+--------------------+
| 12.35 |
+--------------------+
1 row in set (0.00 sec)
产生随机数
mysql> select rand();
+-------------------+
| rand() |
+-------------------+
| 0.663455332315951 |
+-------------------+
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('admin');
+----------------------------------+
| md5('admin') |
+----------------------------------+
| 21232f297a57a5a743894a0e4a801fc3 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> select md5(' ');
+----------------------------------+
| md5(' ') |
+----------------------------------+
| 7215ee9c7d9dc229d2921a40e899ec5f |
+----------------------------------+
1 row in set (0.00 sec)
mysql> select md5('');
+----------------------------------+
| md5('') |
+----------------------------------+
| d41d8cd98f00b204e9800998ecf8427e |
+----------------------------------+
1 row in set (0.00 sec)
在MySQL当中所有的密码存储都是不可显的，防止用户的密码信息泄露
md5函数会将任意类型的字符串转为不规律的32位字符串
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)
ifnull（val1， val2） 如果val1为null，返回val2，否则返回val1的值
mysql> select ifnull('abc', '123');
+----------------------+
| ifnull('abc', '123') |
+----------------------+
| abc |
+----------------------+
1 row in set (0.00 sec)
mysql> select ifnull(null, '123');
+---------------------+
| ifnull(null, '123') |
+---------------------+
| 123 |
+---------------------+
1 row in set (0.00 sec)
