【MySQL-5】MySQL的内置函数

目录

1. 整体学习的思维导图

2. 日期函数

​编辑 2.1 current_date()

2.2 current_time() 

2.3 current_timestamp()

2.4 date(datetime)

2.5 now()

2.6 date_add()

2.7 date_sub()

2.8 datediff()

2.9 案例

2.9.1 创建一个出生日期登记簿

2.9.2 创建一个留言版

3. 字符串函数

案例：

3.1 charset() 获取字符串的字符集

3.2 concat() 拼接两个字符串 

3.3 instr(string, substring) 查找string是否出现substring

3.4 ucase(string)/lcase(string) 转换大写/小写 

3.5 length(string) 可以查看占用的字节数

3.6 replace(str, replace_str, seach_str) 替换

3.7 substring(str, pos, length) 去字符串从pos位置(起始位置为1)开始，length长度 

3.8 使用scott数据库emp表，以首字母小写的方式显示所有员工的姓名 

4. 数学函数

5. 其他函数

user() 查询当前用户

database()查看当前使用的数据库 

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

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

---

1. 整体学习的思维导图

2. 日期函数

 2.1 current_date()

        用于显示当前时间的年月日 

mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2024-11-19     |
+----------------+
1 row in set (0.00 sec)

2.2 current_time() 

        用于显示当前时间的时分秒 

mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 20:17:15       |
+----------------+
1 row in set (0.00 sec)

2.3 current_timestamp()

        用于显示当前时间的时间戳

mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2024-11-19 20:18:21 |
+---------------------+
1 row in set (0.00 sec)

2.4 date(datetime)

        用于返回当前时间的参数的日期部分，也就是年月日

mysql> select date('1949-10-1 00:00:00') as 日期参数;
+--------------+
| 日期参数     |
+--------------+
| 1949-10-01   |
+--------------+
1 row in set (0.00 sec)

2.5 now()

        返回当前时间，也可以配合前面的date()函数使用。

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-11-25 20:02:57 |
+---------------------+mysql> select date(now());
+-------------+
| date(now()) |
+-------------+
| 2024-11-25  |
+-------------+

2.6 date_add()

        在日期的基础上加上日期

select date_add('日期', interval () '单位');

• 给当前时间加上10天，加上10小时，加上10分钟

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-11-25 20:07:14 |
+---------------------+mysql> select date_add(now(), interval 10 day);
+----------------------------------+
| date_add(now(), interval 10 day) |
+----------------------------------+
| 2024-12-05 20:06:52              |
+----------------------------------+mysql> select date_add(now(), interval 10 hour);
+-----------------------------------+
| date_add(now(), interval 10 hour) |
+-----------------------------------+
| 2024-11-26 06:07:34               |
+-----------------------------------+mysql> select date_add(now(), interval 10 minute);
+-------------------------------------+
| date_add(now(), interval 10 minute) |
+-------------------------------------+
| 2024-11-25 20:17:48                 |
+-------------------------------------+

2.7 date_sub()

        在日期的基础上减去日期

select date_add('日期', interval () '单位');

• 给当前时间减去10天，减去10小时，减去10分钟

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-11-25 20:09:22 |
+---------------------+mysql> select date_sub(now(), interval 10 day);
+----------------------------------+
| date_sub(now(), interval 10 day) |
+----------------------------------+
| 2024-11-15 20:09:33              |
+----------------------------------+mysql> select date_sub(now(), interval 10 hour);
+-----------------------------------+
| date_sub(now(), interval 10 hour) |
+-----------------------------------+
| 2024-11-25 10:09:38               |
+-----------------------------------+mysql> select date_sub(now(), interval 10 minute);
+-------------------------------------+
| date_sub(now(), interval 10 minute) |
+-------------------------------------+
| 2024-11-25 19:59:43                 |
+-------------------------------------+

2.8 datediff()

        算出两个日期相差多少天

mysql> select datediff(date(now()), '1949-10-1');
+------------------------------------+
| datediff(date(now()), '1949-10-1') |
+------------------------------------+
|                              27449 |
+------------------------------------+

2.9 案例

2.9.1 创建一个出生日期登记簿

• 其中包含姓名，性别，出生日期

mysql> create table Date_Book(-> name varchar(20) comment '姓名',-> gender char(2) comment '性别',-> born date comment '出生日期'-> );mysql> insert into Date_Book(name, gender, born) values('欧阳', '男', current_date());mysql> select * from Date_Book where name='欧阳';
+--------+--------+------------+
| name   | gender | born       |
+--------+--------+------------+
| 欧阳   | 男     | 2024-11-25 |
+--------+--------+------------+

2.9.2 创建一个留言版

• 其中包含id，留言内容，留言时间

 

mysql> create table Talk_Book( -> id tinyint primary key auto_increment, -> content varchar(100) comment '留言内容', -> time datetime comment '留言时间' );mysql> insert into Talk_Book(content, time) 
values('不知天上宫阙，今夕是何年', current_timestamp);mysql> insert into Talk_Book(content, time) 
values('明月几时有, 把酒问青天', current_timestamp);mysql> insert into Talk_Book(content, time) 
values('人生若只如初见,何事秋风悲画扇', current_timestamp);mysql> select * from Talk_Book;
+----+---------------------------------------------+---------------------+
| id | content                                     | time                |
+----+---------------------------------------------+---------------------+
|  1 | 不知天上宫阙，今夕是何年                    | 2024-11-25 20:25:19 |
|  2 | 明月几时有, 把酒问青天                      | 2024-11-25 20:26:03 |
|  3 | 人生若只如初见,何事秋风悲画扇               | 2024-11-25 20:26:37 |
+----+---------------------------------------------+---------------------+

3. 字符串函数

案例：

3.1 charset() 获取字符串的字符集

mysql> select charset(ename) from emp;
+----------------+
| charset(ename) |
+----------------+
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
+----------------+

3.2 concat() 拼接两个字符串 

mysql> select concat(name,':','语文分数是', chinese,'分，', '数学分数是', math,'分，', '英语分数是', english, '分。') as 总分 from stu_SJ;
+------------------------------------------------------------------------------------+
| 总分                                                                               |
+------------------------------------------------------------------------------------+
| 唐三藏:语文分数是134分，数学分数是196分，英语分数是112分。                         |
| 孙悟空:语文分数是120分，数学分数是200分，英语分数是79分。                          |
| 猪悟能:语文分数是120分，数学分数是196分，英语分数是180分。                         |
| 曹孟德:语文分数是140分，数学分数是180分，英语分数是134分。                         |
| 刘玄德:语文分数是160分，数学分数是230分，英语分数是90分。                          |
| 孙权:语文分数是140分，数学分数是146分，英语分数是156分。                           |
| 宋公明:语文分数是150分，数学分数是190分，英语分数是60分。                          |
+------------------------------------------------------------------------------------+

3.3 instr(string, substring) 查找string是否出现substring

• 如果出现返回出现的开始位置

• 没有出现返回0

mysql> select instr('Hello, World', 'World');
+--------------------------------+
| instr('Hello, World', 'World') |
+--------------------------------+
|                              8 |
+--------------------------------+mysql> select instr('欧阳牛马', '牛马');
+---------------------------------+
| instr('欧阳牛马', '牛马')       |
+---------------------------------+
|                               3 |
+---------------------------------+

3.4 ucase(string)/lcase(string) 转换大写/小写 

mysql> select ucase('hello,world');
+----------------------+
| ucase('hello,world') |
+----------------------+
| HELLO,WORLD          |
+----------------------+mysql> select lcase('HELLO,WORLD');
+----------------------+
| lcase('HELLO,WORLD') |
+----------------------+
| hello,world          |
+----------------------+

3.5 length(string) 可以查看占用的字节数

空格不算字符，字符串长度（空格不算字符串）。

mysql> select * from stu_SJ limit 3;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |     134 |  196 |     112 |
|  2 | 孙悟空    |     120 |  200 |      79 |
|  3 | 猪悟能    |     120 |  196 |     180 |
+----+-----------+---------+------+---------+mysql> select length(name) 名字长度 from stu_SJ limit 3;
+--------------+
| 名字长度     |
+--------------+
|            9 |
|            9 |
|            9 |
+--------------+

注意：length函数返回字符串长度，以字节为单位。如果是多字节字符则计算多个字节数；如果是单字节字符则算作一个字节。比如：字母，数字算作一个字节，中文表示多个字节数（与字符集编码有关）

3.6 replace(str, replace_str, seach_str) 替换

mysql> select * from stu_SJ;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |     134 |  196 |     112 |
|  2 | 孙悟空    |     120 |  200 |      79 |
|  3 | 猪悟能    |     120 |  196 |     180 |
|  4 | 曹孟德    |     140 |  180 |     134 |
|  5 | 刘玄德    |     160 |  230 |      90 |
|  6 | 孙权      |     140 |  146 |     156 |
|  7 | 宋公明    |     150 |  190 |      60 |
+----+-----------+---------+------+---------+-- 将name中所有的'悟'字替换为'无'mysql> select replace(name, '悟', '无') from stu_SJ;
+-----------------------------+
| replace(name, '悟', '无')   |
+-----------------------------+
| 唐三藏                      |
| 孙无空                      |
| 猪无能                      |
| 曹孟德                      |
| 刘玄德                      |
| 孙权                        |
| 宋公明                      |
+-----------------------------+

3.7 substring(str, pos, length) 去字符串从pos位置(起始位置为1)开始，length长度 

mysql> select * from stu_SJ;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |     134 |  196 |     112 |
|  2 | 孙悟空    |     120 |  200 |      79 |
|  3 | 猪悟能    |     120 |  196 |     180 |
|  4 | 曹孟德    |     140 |  180 |     134 |
|  5 | 刘玄德    |     160 |  230 |      90 |
|  6 | 孙权      |     140 |  146 |     156 |
|  7 | 宋公明    |     150 |  190 |      60 |
+----+-----------+---------+------+---------+-- 截取表中name的前两个字符
mysql> select substring(name, 1, 2) from stu_SJ;
+-----------------------+
| substring(name, 1, 2) |
+-----------------------+
| 唐三                  |
| 孙悟                  |
| 猪悟                  |
| 曹孟                  |
| 刘玄                  |
| 孙权                  |
| 宋公                  |
+-----------------------+

3.8 使用scott数据库emp表，以首字母小写的方式显示所有员工的姓名 

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 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+mysql> select concat(lcase(substring(ename,1,1)), substring(ename,2)) 姓名 from emp;
+--------+
| 姓名   |
+--------+
| sMITH  |
| aLLEN  |
| wARD   |
| jONES  |
| mARTIN |
| bLAKE  |
| cLARK  |
| sCOTT  |
| kING   |
| tURNER |
| aDAMS  |
| jAMES  |
| fORD   |
| mILLER |
+--------+

4. 数学函数

 

-- 绝对值
mysql> select abs(-10);
+----------+
| abs(-10) |
+----------+
|       10 |
+----------+mysql> select abs(10);
+---------+
| abs(10) |
+---------+
|      10 |
+---------+-- 向上取整，取比当前大的一方整数
mysql> select ceiling(27.01);
+----------------+
| ceiling(27.01) |
+----------------+
|             28 |
+----------------+
mysql> select ceiling(-27.01);
+-----------------+
| ceiling(-27.01) |
+-----------------+
|             -27 |
+-----------------+-- 向下取整，取比当前小的一方整数
mysql> select floor(27.01);
+--------------+
| floor(27.01) |
+--------------+
|           27 |
+--------------+mysql> select floor(-27.01);
+---------------+
| floor(-27.01) |
+---------------+
|           -28 |
+---------------+-- format 保留小数位
mysql> select format(3.1415926, 2);
+----------------------+
| format(3.1415926, 2) |
+----------------------+
| 3.14                 |
+----------------------+mysql> select format(3.1415926, 3);
+----------------------+
| format(3.1415926, 3) |
+----------------------+
| 3.142                |
+----------------------+

5. 其他函数

user() 查询当前用户

mysql> select user();
+--------+
| user() |
+--------+
| root@  |
+--------+

database()查看当前使用的数据库 

mysql> select database();
+------------+
| database() |
+------------+
| scott      |
+------------+

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

mysql> select password('root');
+-------------------------------------------+
| password('root')                          |
+-------------------------------------------+
| *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+-------------------------------------------+

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

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 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+-- 判断comm是否为NULL,如果是NULL显示0，不为NULL显示原本的数字
mysql> select ifnull(comm, 0) 奖金 from emp;
+---------+
| 奖金    |
+---------+
|    0.00 |
|  300.00 |
|  500.00 |
|    0.00 |
| 1400.00 |
|    0.00 |
|    0.00 |
|    0.00 |
|    0.00 |
|    0.00 |
|    0.00 |
|    0.00 |
|    0.00 |
|    0.00 |
+---------+