日期函數(shù)?

規(guī)定：日期：年月日? ? ? ?時間：時分秒

函數(shù)名稱	作用描述
current_date()	當前日期
current_time()	當前時間
current_timestamp()	當前時間戳
date(datetime)	返回datetime參數(shù)的日期部分
date_add(date,interval d_value_type)	在date中添加時間或日期。interval后面可以是year、day、minute、second
date_sub(date,interval d_value_type)	在date中減去時間或日期。interval后面可以是year、day、minute、second
datediff(date1,date2)	兩個日期的時間差，單位是天
now()	當前時間日期

函數(shù)使用演示

獲得年月日：

mysql> select current_date;
+--------------+
| current_date |
+--------------+
| 2023-08-16   |
+--------------+
1 row in set (0.00 sec)

獲得時分秒：

mysql> select current_time;
+--------------+
| current_time |
+--------------+
| 23:37:33     |
+--------------+
1 row in set (0.00 sec)

獲得時間戳：

mysql> select current_timestamp;
+---------------------+
| current_timestamp   |
+---------------------+
| 2023-08-16 23:38:11 |
+---------------------+
1 row in set (0.00 sec)

在日期的基礎上加日期：

mysql> select date_add(now(),interval 10 day);  ---加10天
+---------------------------------+
| date_add(now(),interval 10 day) |
+---------------------------------+
| 2023-08-26 23:39:04             |
+---------------------------------+
1 row in set (0.00 sec)mysql> select now();-----當前的日期天數(shù)
+---------------------+
| now()               |
+---------------------+
| 2023-08-16 23:39:11 |
+---------------------+
1 row in set (0.00 sec)

在日期的基礎上減去時間：

mysql> select now();-----原本的日期
+---------------------+
| now()               |
+---------------------+
| 2023-08-16 23:40:08 |
+---------------------+
1 row in set (0.00 sec)mysql> select date_sub(now(),interval 5 day);----5天前
+--------------------------------+
| date_sub(now(),interval 5 day) |
+--------------------------------+
| 2023-08-11 23:40:26            |
+--------------------------------+
1 row in set (0.00 sec)

計算兩個日期之間相差多少天：

mysql> select datediff('2019-12-31','2023-8-16');
+------------------------------------+
| datediff('2019-12-31','2023-8-16') |
+------------------------------------+
|                              -1324 |
+------------------------------------+
1 row in set (0.00 sec)mysql> select datediff('2023-8-16','2019-12-31');
+------------------------------------+
| datediff('2023-8-16','2019-12-31') |
+------------------------------------+
|                               1324 |
+------------------------------------+
1 row in set (0.00 sec)

案例

1.創(chuàng)建一張表，記錄生日，添加當前日期：
?

mysql> create table tmp(-> id int primary key auto_increment,-> birthday date-> );
Query OK, 0 rows affected (0.03 sec)mysql> insert into tmp(birthday) values(current_date());
Query OK, 1 row affected (0.01 sec)mysql> select * from tmp;
+----+------------+
| id | birthday   |
+----+------------+
|  1 | 2023-08-16 |
+----+------------+
1 row in set (0.00 sec)

2.創(chuàng)建一個留言表，插入相關數(shù)據(jù)。①顯示所有留言信息，發(fā)布日期只顯示日期，不用顯示時間②查詢在2分鐘內(nèi)發(fā)布的帖子。

• 建表

mysql> create table msg(-> id int primary key auto_increment,-> content varchar(30) not null,-> sendtime datetime);
Query OK, 0 rows affected (0.03 sec)

• 插入數(shù)據(jù)

mysql> insert into msg(content,sendtime) values('中午吃什么？',now());
Query OK, 1 row affected (0.00 sec)mysql> insert into msg(content,sendtime) values('我想吃螺螄粉，可以嗎',now());
Query OK, 1 row affected (0.00 sec)mysql> select * from msg;
+----+--------------------------------+---------------------+
| id | content                        | sendtime            |
+----+--------------------------------+---------------------+
|  1 | 中午吃什么？                   | 2023-08-16 23:51:57 |
|  2 | 我想吃螺螄粉，可以嗎           | 2023-08-16 23:52:09 |
+----+--------------------------------+---------------------+
2 rows in set (0.00 sec)

• 顯示所有留言信息，發(fā)布日期只顯示日期，不用顯示時間
  ?

mysql> select content,date(sendtime) from msg;
+--------------------------------+----------------+
| content                        | date(sendtime) |
+--------------------------------+----------------+
| 中午吃什么？                   | 2023-08-16     |
| 我想吃螺螄粉，可以嗎           | 2023-08-16     |
+--------------------------------+----------------+
2 rows in set (0.00 sec)

• 請查詢在2分鐘內(nèi)發(fā)布的帖子
  ?

mysql> insert into msg(content,sendtime) values('項目做了嗎？',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 | 項目做了嗎？       | 2023-08-16 23:56:26 |
+----+--------------------+---------------------+
1 row in set (0.00 sec)

字符串函數(shù)

案例：

• 獲取stu表的 name的字符集----使用charset字符串函數(shù)

stu表：?

mysql> desc stu;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(30) | NO   |     | NULL    |       |
| class_id | int(11)     | YES  | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)mysql> select charset(name) from stu;  ----獲取字符串的字符集
+---------------+
| charset(name) |
+---------------+
| utf8          |
| utf8          |
+---------------+
2 rows in set (0.00 sec)

• 要求顯示exam_result表中的信息，顯示格式：“XXX的語文是XXX分，數(shù)學XXX分，英語XXX分”----使用concat字符串函數(shù)
  ?

mysql> desc exam_result-> ;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20)      | NO   |     | NULL    |                |
| chinese | float            | YES  |     | 0       |                |
| math    | float            | YES  |     | 0       |                |
| english | float            | YES  |     | 0       |                |
| qq      | char(10)         | YES  |     | NULL    |                |
+---------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)mysql> select concat(name,'的語文是',chinese,'分, 數(shù)學是',math,'分') as '分數(shù)' from exam_result;
+----------------------------------------------+
| 分數(shù)                                         |
+----------------------------------------------+
| 唐三藏的語文是134分, 數(shù)學是98分              |
| 豬悟能的語文是176分, 數(shù)學是98分              |
| 曹孟德的語文是140分, 數(shù)學是90分              |
| 劉玄德的語文是110分, 數(shù)學是115分             |
| 孫權(quán)的語文是140分, 數(shù)學是73分                |
| 宋公明的語文是150分, 數(shù)學是95分              |
+----------------------------------------------+
6 rows in set (0.00 sec)

• 求exam_result中學生姓名占用的字節(jié)數(shù)---使用length字符串函數(shù)

注意：length函數(shù)返回字符串長度，以字節(jié)為單位。如果是多字節(jié)字符則計算多個字節(jié)數(shù)；如果是單字節(jié)字符則算作一個字節(jié)。比如：字母，數(shù)字算作一個字節(jié)，中文表示多個字節(jié)數(shù)（與字符集編碼有關）。

mysql> select length(name),name from exam_result;
+--------------+-----------+
| length(name) | name      |
+--------------+-----------+
|            9 | 唐三藏    |
|            9 | 豬悟能    |
|            9 | 曹孟德    |
|            9 | 劉玄德    |
|            6 | 孫權(quán)      |
|            9 | 宋公明    |
+--------------+-----------+
6 rows in set (0.00 sec)

• 以首字母小寫的方式顯示所有同學的姓名

select concat(lcase(substring(name,1,1)),substring(name,2)) from exam_result;

數(shù)學函數(shù)

案例：

• 絕對值

mysql> select abs(-100.2);
+-------------+
| abs(-100.2) |
+-------------+
|       100.2 |
+-------------+
1 row in set (0.00 sec)

• 向上取整

mysql> select ceiling(23.04);
+----------------+
| ceiling(23.04) |
+----------------+
|             24 |
+----------------+
1 row in set (0.00 sec)

• 向下取整

mysql> select floor(23.7);
+-------------+
| floor(23.7) |
+-------------+
|          23 |
+-------------+
1 row in set (0.00 sec)

• 保留2位小數(shù)位數(shù)（小數(shù)四舍五入)

mysql> select format(12.3456,2);
+-------------------+
| format(12.3456,2) |
+-------------------+
| 12.35             |
+-------------------+
1 row in set (0.00 sec)

• 產(chǎn)生隨機數(shù)

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.3399681042320729 |
+--------------------+
1 row in set (0.00 sec)mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.5697201356009768 |
+--------------------+
1 row in set (0.00 sec)mysql> select rand();
+------------------+
| rand()           |
+------------------+
| 0.82869639604231 |
+------------------+
1 row in set (0.00 sec)

其它函數(shù)

• user() 查詢當前用戶
  ?

select user();

• md5(str)對一個字符串進行md5摘要，摘要后得到一個32位字符串

mysql> select md5('admin');
+----------------------------------+
| md5('admin')                     |
+----------------------------------+
| 21232f297a57a5a743894a0e4a801fc3 |
+----------------------------------+
1 row in set (0.00 sec)

• database()顯示當前正在使用的數(shù)據(jù)庫

mysql> select database();
+------------+
| database() |
+------------+
| my_test    |
+------------+
1 row in set (0.00 sec)

• password()函數(shù)，MySQL數(shù)據(jù)庫使用該函數(shù)對用戶加密

mysql> select password('123456');
+-------------------------------------------+
| password('123456')                        |
+-------------------------------------------+
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------------------------+
1 row in set, 1 warning (0.02 sec)

• 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)