MySQL内置函数

目录

1.日期函数

1.1获得年月日

1.2获得时分秒

1.3获得年月日时分秒

1.4获得时间戳

1.5日期加天数

1.6日期减天数

1.7计算两个日期相隔多少天

1.8案例

2.字符串函数

2.1获取字符集

2.2字符串拼接

2.3字符串查询

2.4大小写转换

2.5字符串提取

2.6字符串长度

2.7字符串替换

2.8字符串截取

2.9去除空格

3.数学函数

3.1绝对值

3.2转二进制

3.3转十六进制

3.4进制转换

3.5向上向下取整

3.6保留n位小数

3.7随机数

3.8取模

4.其他函数

4.1查询当前用户

4.2显示当前数据库

4.3md5摘要

4.4passwor加密

4.5ifnull

---

1.日期函数

1.1获得年月日

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

1.2获得时分秒

mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 22:10:04       |
+----------------+
1 row in set (0.00 sec)

1.3获得年月日时分秒

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-03-24 22:30:14 |
+---------------------+
1 row in set (0.00 sec)

1.4获得时间戳

会以日期的方式显示。

mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2024-03-24 22:10:55 |
+---------------------+
1 row in set (0.00 sec)

1.5日期加天数

mysql> select date_add('2022-01-01', interval 12 day) as res;
+------------+
| res        |
+------------+
| 2022-01-13 |
+------------+
1 row in set (0.00 sec)

mysql> select date_add('2022-01-01', interval 3 month) as res;
+------------+
| res        |
+------------+
| 2022-04-01 |
+------------+
1 row in set (0.00 sec)

1.6日期减天数

mysql> select date_sub('2022-01-01', interval 12 day) as res;
+------------+
| res        |
+------------+
| 2021-12-20 |
+------------+
1 row in set (0.00 sec)

mysql> select date_sub('2022-01-01', interval 3 month) as res;
+------------+
| res        |
+------------+
| 2021-10-01 |
+------------+
1 row in set (0.00 sec)

1.7计算两个日期相隔多少天

mysql> select datediff('2024-03-24', '2022-01-01') as res;
+------+
| res  |
+------+
|  813 |
+------+
1 row in set (0.00 sec)

---

---

1.8案例

• 创建一张生日表

mysql> create table bir(
    -> id int primary key auto_increment,
    -> birthday date
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into bir(birthday) values('1998-05-28');
Query OK, 1 row affected (0.00 sec)

mysql> insert into bir(birthday) values(current_date);
Query OK, 1 row affected (0.00 sec)

mysql> insert into bir(birthday) values(current_timestamp());
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into bir(birthday) values(date(current_timestamp()));
Query OK, 1 row affected (0.00 sec)

mysql> select * from bir;
+----+------------+
| id | birthday   |
+----+------------+
|  1 | 2024-03-24 |
|  2 | 1998-05-28 |
|  3 | 2024-03-24 |
|  4 | 2024-03-24 |
+----+------------+
4 rows in set (0.00 sec)

第三次插入的为时间戳，mysql会自动转化为日期。

• 创建一个留言表

mysql> create table mes(
    -> id int primary key auto_increment,
    -> message varchar(100) not null,
    -> sendtime datetime
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into mes (message, sendtime) values ('窗前明月光', now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into mes (message, sendtime) values ('疑是地上霜', now());
Query OK, 1 row affected (0.00 sec)

mysql> select * from mes;
+----+-----------------+---------------------+
| id | message         | sendtime            |
+----+-----------------+---------------------+
|  1 | 窗前明月光      | 2024-03-24 22:29:33 |
|  2 | 疑是地上霜      | 2024-03-24 22:29:43 |
+----+-----------------+---------------------+
2 rows in set (0.00 sec)

• 查询3分钟之内发的评论

mysql> select * from mes
+----+-----------------+---------------------+
| id | message         | sendtime            |
+----+-----------------+---------------------+
|  1 | 窗前明月光      | 2024-03-24 22:29:33 |
|  2 | 疑是地上霜      | 2024-03-24 22:29:43 |
|  3 | 举头望明月      | 2024-03-24 22:31:42 |
|  4 | 低头思故乡      | 2024-03-24 22:31