日期函數(shù)
示例
- 獲得當前年月日
mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2023-09-03 |
+----------------+
1 row in set (0.00 sec)
- 獲得當前時分秒
mysql> select current_time;
+--------------+
| current_time |
+--------------+
| 09:20:15 |
+--------------+
1 row in set (0.00 sec)
- 獲得當前時間戳
mysql中該函數(shù)會自動將時間戳轉(zhuǎn)化為年月日時分秒的格式
mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2023-09-03 09:20:49 |
+---------------------+
1 row in set (0.00 sec)
- date(datetime),返回 datetime 參數(shù)中的日期部分（年月日部分）
mysql> select date(current_date());
+----------------------+
| date(current_date()) |
+----------------------+
| 2023-09-03 |
+----------------------+
1 row in set (0.00 sec)
//雖然currnt_time()獲得的只有時分秒時間，但該函數(shù)內(nèi)部獲得時間的時候是可以獲得日期（年月日）的
mysql> select date(current_time());
+----------------------+
| date(current_time()) |
+----------------------+
| 2023-09-03 |
+----------------------+
1 row in set (0.00 sec)
mysql> select date(current_timestamp());
+---------------------------+
| date(current_timestamp()) |
+---------------------------+
| 2023-09-03 |
+---------------------------+
1 row in set (0.00 sec)
- 在日期的基礎(chǔ)上加上日期
//在 2023-9-3 的基礎(chǔ)上加上 50 天
mysql> select date_add('2023-9-3',interval 50 day);
+--------------------------------------+
| date_add('2023-9-3',interval 50 day) |
+--------------------------------------+
| 2023-10-23 |
+--------------------------------------+
1 row in set (0.00 sec)
- 在日期的基礎(chǔ)上減去日期
//在 2023-10-23 的基礎(chǔ)上減去 50 天
mysql> select date_sub('2023-10-23',interval 50 day);
+----------------------------------------+
| date_sub('2023-10-23',interval 50 day) |
+----------------------------------------+
| 2023-09-03 |
+----------------------------------------+
1 row in set (0.00 sec)
- 計算兩個日期相差多少天
mysql> select datediff('2023-9-3','2023-10-23');
+-----------------------------------+
| datediff('2023-9-3','2023-10-23') |
+-----------------------------------+
| -50 |
+-----------------------------------+
1 row in set (0.00 sec)
案例-1
- 創(chuàng)建一張表，記錄生日
mysql> create table tmp (id int primary key auto_increment,birthday date not null);
Query OK, 0 rows affected (0.01 sec)
mysql> desc tmp;
+----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| birthday | date | NO | | NULL | |
+----------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
- 添加當前日期
mysql> insert into tmp(birthday) values(current_date());
Query OK, 1 row affected (0.00 sec)
mysql> select* from tmp;
+----+------------+
| id | birthday |
+----+------------+
| 1 | 2023-09-03 |
+----+------------+
1 row in set (0.00 sec)
案例-2
- 創(chuàng)建一個留言表
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> 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)
- 插入數(shù)據(jù)
mysql> insert into msg(content,sendtime) values('hello',now());
Query OK, 1 row affected (0.00 sec)
mysql> insert into msg(content,sendtime) values('hi',now());
Query OK, 1 row affected (0.00 sec)
mysql> select* from msg;
+----+---------+---------------------+
| id | content | sendtime |
+----+---------+---------------------+
| 1 | hello | 2023-09-03 09:48:45 |
| 2 | hi | 2023-09-03 09:48:52 |
+----+---------+---------------------+
2 rows in set (0.00 sec)
- 顯示所有留言信息，發(fā)布日期只顯示到年月日，不用顯示具體時間
mysql> select content ,date(sendtime) from msg;
+---------+----------------+
| content | date(sendtime) |
+---------+----------------+
| hello | 2023-09-03 |
| hi | 2023-09-03 |
+---------+----------------+
2 rows in set (0.00 sec)
- 查詢在 10 分鐘內(nèi)發(fā)布的帖子
mysql> select* from msg where date_add(sendtime,interval 10 minute) > now();
+----+---------+---------------------+
| id | content | sendtime |
+----+---------+---------------------+
| 1 | hello | 2023-09-03 09:48:45 |
| 2 | hi | 2023-09-03 09:48:52 |
+----+---------+---------------------+
2 rows in set (0.00 sec)
理解：
------------------------------|-----------|-------------|------------------
初始時間 now() 初始時間+10min
字符串函數(shù)
示例
- 獲取表中的某一列的字符集
mysql> select charset(content) from msg;
+------------------+
| charset(content) |
+------------------+
| utf8 |
| utf8 |
+------------------+
2 rows in set (0.00 sec)
- 字符串連接
mysql> select concat('app','le');
+--------------------+
| concat('app','le') |
+--------------------+
| apple |
+--------------------+
1 row in set (0.00 sec)
- 某字符串中是否包含某子串，包含則返回出現(xiàn)的位置，未包含則返回 0
mysql> select instr('apple','ple');
+----------------------+
| instr('apple','ple') |
+----------------------+
| 3 |
+----------------------+
1 row in set (0.00 sec)
mysql> select instr('apple','b');
+--------------------+
| instr('apple','b') |
+--------------------+
| 0 |
+--------------------+
1 row in set (0.00 sec)
- 轉(zhuǎn)換成大寫
mysql> select ucase('abcA');
+---------------+
| ucase('abcA') |
+---------------+
| ABCA |
+---------------+
1 row in set (0.00 sec)
- 轉(zhuǎn)換成小寫
mysql> select lcase('abcA');
+---------------+
| lcase('abcA') |
+---------------+
| abca |
+---------------+
1 row in set (0.00 sec)
- 從字符串的左邊或者右邊起取 length 個字符
mysql> select left('abcdef',2);
+------------------+
| left('abcdef',2) |
+------------------+
| ab |
+------------------+
1 row in set (0.00 sec)
mysql> select right('abcdef',2);
+-------------------+
| right('abcdef',2) |
+-------------------+
| ef |
+-------------------+
1 row in set (0.00 sec)
- 獲取字符串的長度
mysql> select length('abc');
+---------------+
| length('abc') |
+---------------+
| 3 |
+---------------+
1 row in set (0.00 sec)
mysql> select length('你好');
+------------------+
| length('你好') |
+------------------+
| 6 |
+------------------+
1 row in set (0.00 sec)
注意：length函數(shù)返回字符串長度，以字節(jié)為單位。如果是多字節(jié)字符則計算多個字節(jié)數(shù)；
如果是單字節(jié)字符則算作一個字節(jié)。比如：字母，數(shù)字算作一個字節(jié)，中文表示多個字節(jié)數(shù)
（與字符集編碼有關(guān)）文章來源：http://www.zghlxwxcb.cn/news/detail-696417.html
- 在字符串中用某子串替換掉其中的子串
//在 ‘hello world’ 中用 ‘jack’ 替換掉 ‘world’
mysql> select replace('hello world','world','jack');
+---------------------------------------+
| replace('hello world','world','jack') |
+---------------------------------------+
| hello jack |
+---------------------------------------+
1 row in set (0.00 sec)
- 逐字符比較字符串的大小
//第一個字符串小于第二個字符串返回 -1
mysql> select strcmp('abc','abd');
+---------------------+
| strcmp('abc','abd') |
+---------------------+
| -1 |
+---------------------+
1 row in set (0.00 sec)
//相等返回 0
mysql> select strcmp('abc','abc');
+---------------------+
| strcmp('abc','abc') |
+---------------------+
| 0 |
+---------------------+
1 row in set (0.00 sec)
//大于返回 1
mysql> select strcmp('abd','abc');
+---------------------+
| strcmp('abd','abc') |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.00 sec)
- 從字符串的第 position 開始，取 length 個字符
//從第一個位置開始取三個字符
mysql> select substring('abcdefg','1',3);
+----------------------------+
| substring('abcdefg','1',3) |
+----------------------------+
| abc |
+----------------------------+
1 row in set (0.00 sec)
- 去除 前空格 / 后空格 / 前后空格
mysql> select ' hello ' as res;
+-------------+
| res |
+-------------+
| hello |
+-------------+
1 row in set (0.00 sec)
mysql> select ltrim(' hello ') as res;//去除前空格
+----------+
| res |
+----------+
| hello |
+----------+
1 row in set (0.00 sec)
mysql> select rtrim(' hello ') as res;//去除后空格
+----------+
| res |
+----------+
| hello |
+----------+
1 row in set (0.00 sec)
mysql> select trim(' hello ') as res; //去除前后空格
+-------+
| res |
+-------+
| hello |
+-------+
1 row in set (0.00 sec)
數(shù)學函數(shù)
文章來源地址http://www.zghlxwxcb.cn/news/detail-696417.html
- 絕對值
mysql> select abs(-100.5);
+-------------+
| abs(-100.5) |
+-------------+
| 100.5 |
+-------------+
1 row in set (0.00 sec)
- 向上取整
mysql> select ceiling(23.4);
+---------------+
| ceiling(23.4) |
+---------------+
| 24 |
+---------------+
1 row in set (0.00 sec)
- 向下取整
mysql> select floor(24.7);
+-------------+
| floor(24.7) |
+-------------+
| 24 |
+-------------+
1 row in set (0.00 sec)
- 保留小數(shù)位數(shù)
//保留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.2013088168588549 |
+--------------------+
1 row in set (0.00 sec)
mysql> select rand();
+--------------------+
| rand() |
+--------------------+
| 0.2613807602425858 |
+--------------------+
1 row in set (0.00 sec)
其他函數(shù)
- user() 查詢當前用戶
- md5(str)對一個字符串進行md5摘要，摘要后得到一個32位字符串
- database()顯示當前正在使用的數(shù)據(jù)庫
- password()函數(shù)，MySQL數(shù)據(jù)庫使用該函數(shù)對用戶加密
- ifnull（val1， val2） 如果val1為null，返回val2，否則返回val1的值
到了這里，關(guān)于【MySQL學習筆記】（七）內(nèi)置函數(shù)的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
