7.1 日期函数
获得年月日:
mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2024-05-24 |
+----------------+
1 row in set (0.00 sec)
获得时分秒:
mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 17:16:03 |
+----------------+
1 row in set (0.00 sec)
获得时间戳:
mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2024-05-24 17:16:38 |
+---------------------+
1 row in set (0.00 sec)
在日期的基础上加日期:
mysql> select date_add('2024-05-24',interval 20 day);
+----------------------------------------+
| date_add('2024-05-24',interval 20 day) |
+----------------------------------------+
| 2024-06-13 |
+----------------------------------------+
1 row in set (0.00 sec)
在日期的基础上减去时间:
mysql> select date_sub('2024-05-24',interval 1 year);
+----------------------------------------+
| date_sub('2024-05-24',interval 1 year) |
+----------------------------------------+
| 2023-05-24 |
+----------------------------------------+
1 row in set (0.00 sec)
计算两个日期之间相差多少天:
mysql> select datediff('2024-05-24','2023-05-24');
+-------------------------------------+
| datediff('2024-05-24','2023-05-24') |
+-------------------------------------+
| 366 |
+-------------------------------------+
1 row in set (0.00 sec)
案例-1:
创建一张表,记录生日
mysql> create table tmp(
-> id int primary key auto_increment,
-> birthday date
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into tmp(birthday) values(current_date());
Query OK, 1 row affected (0.00 sec)
mysql> select * from tmp;
+----+------------+
| id | birthday |
+----+------------+
| 1 | 2024-05-24 |
+----+------------+
1 row in set (0.00 sec)
案例-2:
mysql> create table msg(
-> id int primary key auto_increment,
-> content varchar(20) not null,
-> sendtime datetime
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> insert into msg(content,sendtime) values('hello1',now());
Query OK, 1 row affected (0.01 sec)
mysql> insert into msg(content,sendtime) values('hello2',now());
Query OK, 1 row affected (0.00 sec)
mysql> select * from msg;
+----+---------+---------------------+
| id | content | sendtime |
+----+---------+---------------------+
| 1 | hello1 | 2024-05-24 17:25:49 |
| 2 | hello2 | 2024-05-24 17:25:53 |
+----+---------+---------------------+
2 rows in set (0.00 sec)
显示所有留言信息,发布日期只显示日期,不用显示时间
mysql> select content,date(sendtime) from msg;
+---------+----------------+
| content | date(sendtime) |
+---------+----------------+
| hello1 | 2024-05-24 |
| hello2 | 2024-05-24 |
+---------+----------------+
2 rows in set (0.00 sec)
请查询在2分钟内发布的帖子
mysql> select * from msg where date_add(sendtime,interval 2 minute) > now();
Empty set (0.00 sec)
理解:
------------------------------|-----------|-------------|------------------
初始时间 now() 初始时间+2min
mysql> select * from msg where date_add(sendtime,interval 2 minute) < now();
+----+---------+---------------------+
| id | content | sendtime |
+----+---------+---------------------+
| 1 | hello1 | 2024-05-24 17:25:49 |
| 2 | hello2 | 2024-05-24 17:25:53 |
+----+---------+---------------------+
2 rows in set (0.00 sec)
7.2 字符串函数
案例:
获取emp表的ename列的字符集
mysql> select ename,charset(ename) from emp;
+--------+----------------+
| ename | charset(ename) |
+--------+----------------+
| SMITH | utf8 |
| ALLEN | utf8 |
| WARD | utf8 |
| JONES | utf8 |
| MARTIN | utf8 |
| BLAKE | utf8 |
| CLARK | utf8 |
| SCOTT | utf8 |
| KING | utf8 |
| TURNER | utf8 |
| ADAMS | utf8 |
| JAMES | utf8 |
| FORD | utf8 |
| MILLER | utf8 |
+--------+----------------+
14 rows in set (0.00 sec)
要求显示exam_result表中的信息,显示格式:“XXX的语文是XXX分,数学XXX分,英语XXX分”
mysql> select concat(name,'的语文是',chinese,'分,数学',math,'分,英语',english,'分') as '分数' from exam_result;
+----------------------------------------------------------+
| 分数 |
+----------------------------------------------------------+
| 唐三藏的语文是134分,数学98分,英语56分 |
| 猪悟能的语文是176分,数学98分,英语90分 |
| 曹孟德的语文是140分,数学90分,英语67分 |
| 刘玄德的语文是110分,数学115分,英语45分 |
| 孙权的语文是140分,数学73分,英语78分 |
| 宋公明的语文是150分,数学95分,英语30分 |
+----------------------------------------------------------+
6 rows in set (0.00 sec)
求学生表中学生姓名占用的字节数
mysql> select name,length(name) from exam_result;
+-----------+--------------+
| name | length(name) |
+-----------+--------------+
| 唐三藏 | 9 |
| 猪悟能 | 9 |
| 曹孟德 | 9 |
| 刘玄德 | 9 |
| 孙权 | 6 |
| 宋公明 | 9 |
+-----------+--------------+
6 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表中所有名字中有S的更新成’上海’
mysql> update emp set ename=replace(ename,'S','上海');
Query OK, 5 rows affected (0.01 sec)
Rows matched: 14 Changed: 5 Warnings: 0
mysql> select * from emp;
+--------+------------+-----------+------+---------------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+------------+-----------+------+---------------------+---------+---------+--------+
| 007369 | 上海MITH | 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 | JONE上海 | 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 | 上海COTT | 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 | ADAM上海 | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
| 007900 | JAME上海 | 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 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)
7.3 数学函数
绝对值
mysql> select abs(-100.11);
+--------------+
| abs(-100.11) |
+--------------+
| 100.11 |
+--------------+
1 row in set (0.00 sec)
向上取整
mysql> select ceiling(19.23);
+----------------+
| ceiling(19.23) |
+----------------+
| 20 |
+----------------+
1 row in set (0.00 sec)
向下取整
mysql> select floor(29.9);
+-------------+
| floor(29.9) |
+-------------+
| 29 |
+-------------+
1 row in set (0.00 sec)
保留2位小数位数(小数四舍五入)
mysql> select format(12.3934548,2);
+----------------------+
| format(12.3934548,2) |
+----------------------+
| 12.39 |
+----------------------+
1 row in set (0.00 sec)
产生随机数
mysql> select rand();
+---------------------+
| rand() |
+---------------------+
| 0.24249482084298024 |
+---------------------+
1 row in set (0.00 sec)
7.4 其它函数
user() 查询当前用户
mysql> select user();
+--------+
| user() |
+--------+
| root@ |
+--------+
1 row in set (0.00 sec)
md5(str)对一个字符串进行md5摘要,摘要后得到一个32位字符串
mysql> select md5('sadfasfa');
+----------------------------------+
| md5('sadfasfa') |
+----------------------------------+
| 881de215ff99f43ba2861519091b29d6 |
+----------------------------------+
1 row in set (0.01 sec)
database()显示当前正在使用的数据库
mysql> select database();
+------------+
| database() |
+------------+
| scott |
+------------+
1 row in set (0.00 sec)
password()函数,MySQL数据库使用该函数对用户加密
mysql> select password('你好');
+-------------------------------------------+
| password('你好') |
+-------------------------------------------+
| *CF5BE1F39115E469E455BEC512DE978231206A6C |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)
ifnull(val1, val2) 如果val1为null,返回val2,否则返回val1的值
mysql> select ifnull(1111,'222');
+--------------------+
| ifnull(1111,'222') |
+--------------------+
| 1111 |
+--------------------+
1 row in set (0.00 sec)
mysql> select ifnull(null,'222');
+--------------------+
| ifnull(null,'222') |
+--------------------+
| 222 |
+--------------------+
1 row in set (0.00 sec)