MySQL内置函数
1. 日期函数
- 获得年月日:(current_date()函数的使用)
mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2024-07-11 |
+----------------+
1 row in set (0.00 sec)
- 获得时分秒:(current_time()函数的使用)
mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 09:29:58 |
+----------------+
1 row in set (0.00 sec)
- 获得时间戳:(current_timestamp()函数的使用)
mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2024-07-11 09:30:54 |
+---------------------+
1 row in set (0.00 sec)
- 在日期的基础上加日期:(date_add()函数的使用)
mysql> select date_add("2024-07-11", interval 30 day);
+-----------------------------------------+
| date_add("2024-07-11", interval 30 day) |
+-----------------------------------------+
| 2024-08-10 |
+-----------------------------------------+
1 row in set (0.00 sec)
- 在日期的基础上减去时间:(date_sub()函数的使用)
mysql> select date_sub("2024-07-11", interval 30 day);
+-----------------------------------------+
| date_sub("2024-07-11", interval 30 day) |
+-----------------------------------------+
| 2024-06-11 |
+-----------------------------------------+
1 row in set (0.00 sec)
- 计算两个日期之间相差多少天:(time_diff()函数的使用)
mysql> select datediff("2024-07-11", "2004-06-29");
+--------------------------------------+
| datediff("2024-07-11", "2004-06-29") |
+--------------------------------------+
| 7317 |
+--------------------------------------+
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.01 sec)
- 添加当前日期:(current_date()函数的使用)
mysql> insert into tmp (birthday)values(current_date());
Query OK, 1 row affected (0.00 sec)
mysql> select * from tmp;
+----+------------+
| id | birthday |
+----+------------+
| 1 | 2024-07-11 |
+----+------------+
1 row in set (0.00 sec)
案例2:
- 创建一个留言表
mysql> create table msg (
-> id int primary key auto_increment,
-> content varchar(30) not null,
-> sendtime datetime
-> );
Query OK, 0 rows affected (0.01 sec)
- 插入数据
mysql> insert into msg (content,sendtime)values('hello1', now());
Query OK, 1 row affected (0.00 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-07-11 09:44:23 |
| 2 | hello2 | 2024-07-11 09:44:30 |
+----+---------+---------------------+
2 rows in set (0.00 sec)
- 显示所有留言信息,发布日期只显示日期,不用显示时间(date()函数的使用)
mysql> select content, date(sendtime) from msg;
+---------+----------------+
| content | date(sendtime) |
+---------+----------------+
| hello1 | 2024-07-11 |
| hello2 | 2024-07-11 |
| hello3 | 2024-07-11 |
+---------+----------------+
3 rows in set (0.00 sec)
- 查询在2分钟内发布的帖子(date_add(), now()函数的使用)
mysql> select * from msg where date_add(sendtime, interval 2 minute) > now();
+----+---------+---------------------+
| id | content | sendtime |
+----+---------+---------------------+
| 3 | hello3 | 2024-07-11 09:57:25 |
+----+---------+---------------------+
1 row in set (0.00 sec)
2. 字符串函数
案例:
- 获取emp表的ename列的字符集(charset()函数的使用)
mysql> select distinct charset(ename) from emp;
+----------------+
| charset(ename) |
+----------------+
| utf8mb3 |
+----------------+
1 row in set (0.00 sec)
- 要求显示student表中的信息,显示格式:“XXX的学号是XXX”(concat()函数的使用)
mysql> select * from students;
+-----+-------+-----------+-------+
| id | sn | name | qq |
+-----+-------+-----------+-------+
| 100 | 10010 | 唐大师 | NULL |
| 101 | 10001 | 孙悟空 | 11111 |
| 103 | 20002 | 孙仲谋 | NULL |
| 105 | 20001 | 曹阿瞒 | NULL |
+-----+-------+-----------+-------+
4 rows in set (0.00 sec)
mysql> select concat(name, '的学号是', sn) as '信息表' from students;
+----------------------------+
| 信息表 |
+----------------------------+
| 唐大师的学号是10010 |
| 孙悟空的学号是10001 |
| 孙仲谋的学号是20002 |
| 曹阿瞒的学号是20001 |
+----------------------------+
4 rows in set (0.00 sec)
- 求学生表中学生姓名占用的字节数(length()函数的使用)
mysql> select length(name), name from students;
+--------------+-----------+
| length(name) | name |
+--------------+-----------+
| 9 | 唐大师 |
| 9 | 孙悟空 |
| 9 | 孙仲谋 |
| 9 | 曹阿瞒 |
+--------------+-----------+
4 rows in set (0.01 sec)
注意:
length函数返回字符串长度,以字节为单位
。如果是多字节字符则计算多个字节数;如果是单字节字符则算作一个字节。比如:字母,数字算作一个字节,中文表示多个字节数(与字符集编码有关)
- 将EMP表中所有名字中有S的替换成’上海’(replace()函数的使用)
# 查看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 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)
# 显示replace更换后的信息
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)
# 不会影响原来表的结构
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字段的第二个到第三个字符(substring()函数的使用)
# 查看原表信息
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)
# 截取显示
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)
- 以首字母小写的方式显示所有员工的姓名(lcase()函数的使用)
mysql> select concat(lcase(substring(ename, 1, 1)),substring(ename,2)) as '姓名首字母小写' from emp;
+-----------------------+
| 姓名首字母小写 |
+-----------------------+
| sMITH |
| aLLEN |
| wARD |
| jONES |
| mARTIN |
| bLAKE |
| cLARK |
| sCOTT |
| kING |
| tURNER |
| aDAMS |
| jAMES |
| fORD |
| mILLER |
+-----------------------+
14 rows in set (0.00 sec)
3. 数学函数
- 绝对值(abs()函数的使用)
mysql> select abs(-100.2);
+-------------+
| abs(-100.2) |
+-------------+
| 100.2 |
+-------------+
1 row in set (0.00 sec)
- 向上取整(ceiling()函数的使用)
mysql> select ceiling(23.04);
+----------------+
| ceiling(23.04) |
+----------------+
| 24 |
+----------------+
1 row in set (0.00 sec)
- 向下取整(floor()函数的使用)
mysql> select floor(23.7);
+-------------+
| floor(23.7) |
+-------------+
| 23 |
+-------------+
1 row in set (0.00 sec)
- 保留2位小数位数(小数四舍五入)(format()函数的使用)
mysql> select format(12.3456, 2);
+--------------------+
| format(12.3456, 2) |
+--------------------+
| 12.35 |
+--------------------+
1 row in set (0.00 sec)
- 产生随机数(rand()函数的使用)
mysql> select rand();
+---------------------+
| rand() |
+---------------------+
| 0.08504683625423055 |
+---------------------+
1 row in set (0.00 sec)
mysql> select rand();
+-------------------+
| rand() |
+-------------------+
| 0.783232943884314 |
+-------------------+
1 row in set (0.00 sec)
4. 其它函数
- user() 查询当前用户
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
- md5(str)对一个字符串进行md5摘要,摘要后得到一个32位字符串
mysql> select md5('root@localhost');
+----------------------------------+
| md5('root@localhost') |
+----------------------------------+
| b1a4b2518dbbdd47dd4a713d5cd1df94 |
+----------------------------------+
1 row in set (0.00 sec)
- database()显示当前正在使用的数据库
mysql> select database();
+------------+
| database() |
+------------+
| scott |
+------------+
1 row in set (0.00 sec)
- password()函数,MySQL数据库使用该函数对用户加密
在 MySQL 8.0 中,
PASSWORD()
函数已经被移除,所以不能再使用它来设置密码。
select password('root');
# mysql8.0以后
UPDATE mysql.user
SET authentication_string = 'your_new_password'
WHERE User = 'root' AND Host = 'localhost';
- 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)
mysql> select ifnull(null, null);
+--------------------+
| ifnull(null, null) |
+--------------------+
| NULL |
+--------------------+
1 row in set (0.00 sec)
5.OJ演练 - 查找字符串中 ‘,’ 出现的次数cnt
题目链接:查找字符串中逗号出现的次数_牛客网
select id, (length(string) - length(replace(string, ',', ''))) as cnt from strings;
–+
1 row in set (0.00 sec)
mysql> select ifnull(null, null);
±-------------------+
| ifnull(null, null) |
±-------------------+
| NULL |
±-------------------+
1 row in set (0.00 sec)
## 5.OJ演练 - 查找字符串中 ',' 出现的次数cnt
题目链接:[查找字符串中逗号出现的次数_牛客网](https://www.nowcoder.com/practice/e3870bd5d6744109a902db43c105bd50?tpId=82&tqId=29819&rp=10&ru=%2Factivity%2Foj&qru=%2Fta%2Fsql%2Fquestion-ranking&tPage=3)
```mysql
select id, (length(string) - length(replace(string, ',', ''))) as cnt from strings;