✨个人主页： 熬夜学编程的小林
💗系列专栏： 【C语言详解】 【数据结构详解】【C++详解】【Linux系统编程】【MySQL】
目录
1. 函数
1.1 日期函数
- 获得年月日：
mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2024-09-17 |
+----------------+
1 row in set (0.00 sec)
- 获得时分秒：
mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 21:37:52 |
+----------------+
1 row in set (0.00 sec)
- 获得时间戳(转化为日期+时间)：
mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2024-09-17 21:39:02 |
+---------------------+
1 row in set (0.00 sec)
- 获得当前时间(日期+时间)
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2024-09-17 21:44:10 |
+---------------------+
1 row in set (0.00 sec)
- 在日期的基础上加日期：
# 加10天
mysql> select date_add('2022-01-1',interval 10 day);
+---------------------------------------+
| date_add('2022-01-1',interval 10 day) |
+---------------------------------------+
| 2022-01-11 |
+---------------------------------------+
1 row in set (0.00 sec)
# 加10分钟
mysql> select date_add('2022-01-1',interval 10 minute);
+------------------------------------------+
| date_add('2022-01-1',interval 10 minute) |
+------------------------------------------+
| 2022-01-01 00:10:00 |
+------------------------------------------+
1 row in set (0.00 sec)
# 加10秒
mysql> select date_add('2022-01-1',interval 10 second);
+------------------------------------------+
| date_add('2022-01-1',interval 10 second) |
+------------------------------------------+
| 2022-01-01 00:00:10 |
+------------------------------------------+
1 row in set (0.00 sec)- 在日期的基础上减去时间：
# 减15天
mysql> select date_sub('2023-10-12',interval 15 day);
+----------------------------------------+
| date_sub('2023-10-12',interval 15 day) |
+----------------------------------------+
| 2023-09-27 |
+----------------------------------------+
1 row in set (0.00 sec)
# 减15分钟
mysql> select date_sub('2023-10-12',interval 15 minute);
+-------------------------------------------+
| date_sub('2023-10-12',interval 15 minute) |
+-------------------------------------------+
| 2023-10-11 23:45:00 |
+-------------------------------------------+
1 row in set (0.00 sec)
# 减15秒
mysql> select date_sub('2023-10-12',interval 15 second);
+-------------------------------------------+
| date_sub('2023-10-12',interval 15 second) |
+-------------------------------------------+
| 2023-10-11 23:59:45 |
+-------------------------------------------+
1 row in set (0.00 sec)
- 计算两个日期之间相差多少天(前面 - 后面)：
mysql> select datediff('2023-10-15','2023-9-8');
+-----------------------------------+
| datediff('2023-10-15','2023-9-8') |
+-----------------------------------+
| 37 |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> select datediff('2023-9-8','2023-10-15');
+-----------------------------------+
| datediff('2023-9-8','2023-10-15') |
+-----------------------------------+
| -37 |
+-----------------------------------+
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.03 sec)
mysql> desc tmp;
+----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| birthday | date | YES | | NULL | |
+----------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
- 添加当前日期：
mysql> insert into tmp (birthday) values(current_date());
Query OK, 1 row affected (0.01 sec)
mysql> select * from tmp;
+----+------------+
| id | birthday |
+----+------------+
| 1 | 2024-09-17 |
+----+------------+
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.02 sec)
mysql> desc msg;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| content | varchar(30) | NO | | NULL | |
| sendtime | datetime | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
- 插入数据
mysql> insert into msg (content,sendtime) values('hello mysql',now());
Query OK, 1 row affected (0.01 sec)
mysql> insert into msg (content,sendtime) values('hello friend',now());
Query OK, 1 row affected (0.01 sec)
mysql> select * from msg;
+----+--------------+---------------------+
| id | content | sendtime |
+----+--------------+---------------------+
| 1 | hello mysql | 2024-09-17 21:50:49 |
| 2 | hello friend | 2024-09-17 21:50:56 |
+----+--------------+---------------------+
2 rows in set (0.00 sec)
- 显示所有留言信息，发布日期只显示日期，不用显示时间
mysql> select content,date(sendtime) from msg;
+--------------+----------------+
| content | date(sendtime) |
+--------------+----------------+
| hello mysql | 2024-09-17 |
| hello friend | 2024-09-17 |
+--------------+----------------+
2 rows in set (0.00 sec)
- 请查询在2分钟内发布的帖子
表中插入数据的时间 + 2分钟大于当前时间就是两分钟内发布的帖子。
# 直接查看两分钟内f发布的帖子，已经过了两分钟因此没有查到
mysql> select * from msg where date_add(sendtime,interval 2 minute) > now();
Empty set (0.00 sec)
# 再插入一条信息
mysql> insert into msg (content,sendtime) values('excuse me',now());
Query OK, 1 row affected (0.01 sec)
# 继续查两分钟内的信息
mysql> select * from msg where date_add(sendtime,interval 2 minute) > now();
+----+-----------+---------------------+
| id | content | sendtime |
+----+-----------+---------------------+
| 3 | excuse me | 2024-09-17 21:55:52 |
+----+-----------+---------------------+
1 row in set (0.00 sec)
1.2 字符串函数
- 获取字符串字符集
mysql> select charset('aaa');
+----------------+
| charset('aaa') |
+----------------+
| utf8 |
+----------------+
1 row 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 concat('abc','123',456);
+-------------------------+
| concat('abc','123',456) |
+-------------------------+
| abc123456 |
+-------------------------+
1 row in set (0.00 sec)
- 要求显示exam_result表中的信息，显示格式：“XXX的语文是XXX分，数学XXX分，英语XXX分”
mysql> select concat(name,'的语文是',chinese,'分，数学是',math,'分') from exam_result;
+------------------------------------------------------------------+
| concat(name,'的语文是',chinese,'分，数学是',math,'分') |
+------------------------------------------------------------------+
| 唐三藏的语文是134分，数学是98分 |
| 猪悟能的语文是176分，数学是98分 |
| 曹孟德的语文是140分，数学是90分 |
| 刘玄德的语文是110分，数学是115分 |
| 孙权的语文是140分，数学是73分 |
| 宋公明的语文是150分，数学是95分 |
+------------------------------------------------------------------+
6 rows in set (0.00 sec)
- 求学生表中学生姓名占用的字节数
# 求的是字节大小，utf8编码下中文占三个字节
mysql> select name,length(name) from student;
+--------+--------------+
| name | length(name) |
+--------+--------------+
| 张三 | 6 |
| 李四 | 6 |
| 王五 | 6 |
| 孙权 | 6 |
| 妲己 | 6 |
+--------+--------------+
5 rows in set (0.00 sec)
注意：length函数返回字符串长度，以字节为单位。如果是多字节字符则计算多个字节数；
如果是单字节字符则算作一个字节。比如：字母，数字算作一个字节，中文表示多个字节数
（与字符集编码有关）
- 求字符串占用字节数
# 英文字符和数字占一个字节，中文占3字节(utf8编码)
mysql> select length('abcd中');
+-------------------+
| length('abcd中') |
+-------------------+
| 7 |
+-------------------+
1 row in set (0.00 sec)
- 判断子串是否在主串中
在子串中返回起始下标(从1开始)，否则返回0.
mysql> select instr('abcdef123','123a') bool;
+------+
| bool |
+------+
| 0 |
+------+
1 row in set (0.00 sec)
mysql> select instr('abcdef123','123') bool;
+------+
| bool |
+------+
| 7 |
+------+
1 row in set (0.00 sec)- 转换成大小写
# 转换为大写
mysql> select ucase('abcdef') res;
+--------+
| res |
+--------+
| ABCDEF |
+--------+
1 row in set (0.00 sec)
# 转换为小写
mysql> select lcase('ABCDEF') res;
+--------+
| res |
+--------+
| abcdef |
+--------+
1 row in set (0.00 sec)
- 从左右获取字符串
# 从左边取3个字符
mysql> select left('abcdef',3);
+------------------+
| left('abcdef',3) |
+------------------+
| abc |
+------------------+
1 row in set (0.00 sec)
# 从右边取3个字符
mysql> select right('abcdef',3);
+-------------------+
| right('abcdef',3) |
+-------------------+
| def |
+-------------------+
1 row in set (0.00 sec)
- 将EMP表中所有名字中有S的替换成'上海'
mysql> select ename from emp;
+--------+
| ename |
+--------+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| SCOTT |
| KING |
| TURNER |
| ADAMS |
| JAMES |
| FORD |
| MILLER |
+--------+
14 rows in set (0.00 sec)
mysql> select replace(ename,'S','上海') from emp;
+-----------------------------+
| replace(ename,'S','上海') |
+-----------------------------+
| 上海MITH |
| ALLEN |
| WARD |
| JONE上海 |
| MARTIN |
| BLAKE |
| CLARK |
| 上海COTT |
| KING |
| TURNER |
| ADAM上海 |
| JAME上海 |
| FORD |
| MILLER |
+-----------------------------+
14 rows in set (0.00 sec)
- 截取EMP表中ename字段的第二个到第三个字符
# 原表数据
mysql> select ename from emp;
+--------+
| ename |
+--------+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| SCOTT |
| KING |
| TURNER |
| ADAMS |
| JAMES |
| FORD |
| MILLER |
+--------+
14 rows in set (0.00 sec)
# 截取后数据
mysql> select substring(ename,2,2) from emp;
+----------------------+
| substring(ename,2,2) |
+----------------------+
| MI |
| LL |
| AR |
| ON |
| AR |
| LA |
| LA |
| CO |
| IN |
| UR |
| DA |
| AM |
| OR |
| IL |
+----------------------+
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)
- 去除前后空格
ltrim() 去除前空格，rtrim() 去除后空格，trim() 去除前后空格。
# 去除前空格
mysql> select ltrim(' nihao ') res;
+------------------+
| res |
+------------------+
| nihao |
+------------------+
1 row in set (0.00 sec)
# 去除后空格
mysql> select rtrim(' nihao ') res;
+----------+
| res |
+----------+
| nihao |
+----------+
1 row in set (0.00 sec)
# 去除后空格
mysql> select trim(' nihao ') res;
+-------+
| res |
+-------+
| nihao |
+-------+
1 row in set (0.00 sec)
1.3 数学函数
- 绝对值
mysql> select abs(100);
+----------+
| abs(100) |
+----------+
| 100 |
+----------+
1 row in set (0.00 sec)
mysql> select abs(-100);
+-----------+
| abs(-100) |
+-----------+
| 100 |
+-----------+
1 row in set (0.00 sec)- 向上取整(有小数则数值变大)
mysql> select ceiling(3.0);
+--------------+
| ceiling(3.0) |
+--------------+
| 3 |
+--------------+
1 row in set (0.00 sec)
mysql> select ceiling(3.1);
+--------------+
| ceiling(3.1) |
+--------------+
| 4 |
+--------------+
1 row in set (0.00 sec)
mysql> select ceiling(-3.1);
+---------------+
| ceiling(-3.1) |
+---------------+
| -3 |
+---------------+
1 row in set (0.00 sec)
mysql> select ceiling(-3.9);
+---------------+
| ceiling(-3.9) |
+---------------+
| -3 |
+---------------+
1 row in set (0.00 sec)- 向下取整(有小数则数值变大)
mysql> select floor(3.9);
+------------+
| floor(3.9) |
+------------+
| 3 |
+------------+
1 row in set (0.00 sec)
mysql> select floor(3.1);
+------------+
| floor(3.1) |
+------------+
| 3 |
+------------+
1 row in set (0.00 sec)
mysql> select floor(-3.1);
+-------------+
| floor(-3.1) |
+-------------+
| -4 |
+-------------+
1 row in set (0.00 sec)
- 保留2位小数位数（小数四舍五入)
mysql> select format(12.34125,2);
+--------------------+
| format(12.34125,2) |
+--------------------+
| 12.34 |
+--------------------+
1 row in set (0.00 sec)
# 5及以上则进1
mysql> select format(12.34525,2);
+--------------------+
| format(12.34525,2) |
+--------------------+
| 12.35 |
+--------------------+
1 row in set (0.00 sec)
- 产生随机数 [0.0，1.0)，左边可取，右边不可取。
mysql> select rand();
+-------------------+
| rand() |
+-------------------+
| 0.586795278440039 |
+-------------------+
1 row in set (0.00 sec)
# 产生1-10之间的数
mysql> select rand()*10;
+-------------------+
| rand()*10 |
+-------------------+
| 8.999853217042846 |
+-------------------+
1 row in set (0.00 sec)
# 产生1-10的整数，使用format保留0位小数
mysql> select format(rand()*10,0);
+---------------------+
| format(rand()*10,0) |
+---------------------+
| 7 |
+---------------------+
1 row in set (0.00 sec)
1.4 其它函数
- user() 查询当前用户
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
- md5(str)对一个字符串进行md5摘要，摘要后得到一个32位字符串
可以使用对密码进行加密转换。
mysql> select md5('abcd123');
+----------------------------------+
| md5('abcd123') |
+----------------------------------+
| 79cfeb94595de33b3326c06ab1c7dbda |
+----------------------------------+
1 row in set (0.00 sec)
创建用户表
mysql> create table user1
-> (id int primary key auto_increment,
-> password char(32));
Query OK, 0 rows affected (0.02 sec)
插入密码
mysql> insert into user1 (password) values(123456);
Query OK, 1 row affected (0.00 sec)
mysql> select * from user1;
+----+----------+
| id | password |
+----+----------+
| 1 | 123456 |
+----+----------+
1 row in set (0.00 sec)
通过上下键找不到插入密码的那行，因为密码会被mysql自动隐藏，但是如果直接能够使用select查看密码不安全，我们可以使用md5进行加密转换。
加密
mysql> insert into user1 (password) values(md5(123456));
Query OK, 1 row affected (0.01 sec)
mysql> select * from user1;
+----+----------------------------------+
| id | password |
+----+----------------------------------+
| 1 | 123456 |
| 2 | e10adc3949ba59abbe56e057f20f883e |
+----+----------------------------------+
2 rows in set (0.00 sec)
mysql> delete from user1 where id = 2;
Query OK, 1 row affected (0.01 sec)
mysql> insert into user1 (password) values(md5(123));
Query OK, 1 row affected (0.00 sec)
mysql> select * from user1;
+----+----------------------------------+
| id | password |
+----+----------------------------------+
| 1 | 123456 |
| 3 | 202cb962ac59075b964b07152d234b70 |
+----+----------------------------------+
2 rows in set (0.00 sec)
mysql> select id from user1 where password = '123';
Empty set (0.00 sec)
mysql> select id from user1 where password = md5('123');
+----+
| id |
+----+
| 3 |
+----+
1 row in set (0.01 sec)- password()函数，MySQL数据库使用该函数对用户加密
mysql> select password('12');
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements对 '12' 字符串进行加密，会报不满足密码要求，此时需要查看密码的最低长度，该字符串的长度大于等于最低长度才能正确使用。
补充
# 查看密码最低长度
show variables like '%password%';
show variables like 'validate_password%';
# 设置密码最低长度
set global validate_password_legnth=4;查看密码最低长度
mysql> show variables like 'validate_password%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| validate_password_check_user_name | OFF |
| validate_password_dictionary_file | |
| validate_password_length | 4 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | LOW |
| validate_password_special_char_count | 1 |
+--------------------------------------+-------+
7 rows in set (0.00 sec)
因为密码长度最少为4，因此对'12'进行加密会报错。
正确方式
mysql> select password('root');
+-------------------------------------------+
| password('root') |
+-------------------------------------------+
| *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> select password('12345');
+-------------------------------------------+
| password('12345') |
+-------------------------------------------+
| *00A51F3F48415C7D4E8908980D443C29C69B60C9 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)
- ifnull（val1， val2） 如果val1为null，返回val2，否则返回val1的值
类似于C语言三目表达式，第一个值不为空返回第一个值，为空返回第二个值。
# 第一个值为空返回第二个值
mysql> select ifnull(null,12);
+-----------------+
| ifnull(null,12) |
+-----------------+
| 12 |
+-----------------+
1 row in set (0.00 sec)
# 第一个值不为空返回第一个值
mysql> select ifnull(12,10);
+---------------+
| ifnull(12,10) |
+---------------+
| 12 |
+---------------+
1 row in set (0.00 sec)
mysql> select ifnull(12,null);
+-----------------+
| ifnull(12,null) |
+-----------------+
| 12 |
+-----------------+
1 row in set (0.00 sec)
转载自CSDN-专业IT技术社区
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
原文链接：https://blog.csdn.net/2201_75584283/article/details/142319360
