技術文章 » mysql學習知識點大全

mysql學習知識點大全

2020-10-03 14:00:21

概要

執行順序

# 書寫順序
select distinct * from '表名' where '限制條件'  group by '分組依據' having '過濾條件' order by  limit '展示條數'

# 執行順序
from       -- 查詢
where      -- 限制條件
group by   -- 分組
having     -- 過濾條件
order by   -- 排序
limit      -- 展示條數
distinct   -- 去重
select     -- 查詢的結果

單表約束

# 主鍵約束：PRIMARY KEY          要求被裝飾的欄位：唯一和非空
# 唯一約束：UNIQUE               要求被裝飾的欄位：唯一，
#                             .聯合唯一：在結尾：unique(欄位1，欄位2)
# 非空約束：NOT NULL             要求被裝飾的欄位：非空
# 外來鍵約束：FOREIGN KEY          某主表的外來鍵
# 自動增加：AUTO_INCREMENT       自動增加(需要和主鍵 PRIMARY KEY 同時用)
# 設定預設值: DEFAULT            為該屬性設定預設值
# 在int、char中：zerofill        不足位數預設填充0

常用資料型別

數值型別

型別大小範圍（有符號）範圍（無符號）用途
TINYINT1 byte(-128，127)(0，255)小整數值
SMALLINT2 bytes(-32 768，32 767)(0，65 535)大整數值
MEDIUMINT3 bytes(-8 388 608，8 388 607)(0，16 777 215)大整數值
INT或INTEGER4 bytes(-2 147 483 648，2 147 483 647)(0，4 294 967 295)大整數值
BIGINT8 bytes(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)(0，18 446 744 073 709 551 615)極大整數值
FLOAT4 bytes(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)0，(1.175 494 351 E-38，3.402 823 466 E+38)單精度 浮點數值
DOUBLE8 bytes(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)雙精度 浮點數值
DECIMAL對DECIMAL(M,D) ，如果M>D，為M+2否則為D+2依賴於M和D的值依賴於M和D的值小數值

日期和時間型別

型別大小 ( bytes)範圍格式用途
DATE31000-01-01/9999-12-31YYYY-MM-DD日期值
TIME3‘-838:59:59’/‘838:59:59’HH:MM:SS時間值或持續時間
YEAR11901/2155YYYY年份值
DATETIME81000-01-01 00:00:00/9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和時間值
TIMESTAMP41970-01-01 00:00:00/2038結束時間是第 2147483647 秒，北京時間 2038-1-19 11:14:07，格林尼治時間 2038年1月19日 凌晨 03:14:07YYYYMMDD HHMMSS混合日期和時間值，時間戳

字串型別

型別大小用途
CHAR0-255 bytes定長字串
VARCHAR0-65535 bytes變長字串
TINYBLOB0-255 bytes不超過 255 個字元的二進位制字串
TINYTEXT0-255 bytes短文字字串
BLOB0-65 535 bytes二進位制形式的長文字資料
TEXT0-65 535 bytes長文字資料
MEDIUMBLOB0-16 777 215 bytes二進位制形式的中等長度文字資料
MEDIUMTEXT0-16 777 215 bytes中等長度文字資料
LONGBLOB0-4 294 967 295 bytes二進位制形式的極大文字資料
LONGTEXT0-4 294 967 295 bytes極大文字資料

char(n) 和 varchar(n) 中括號中 n 代表字元的個數，並不代表位元組個數，比如 CHAR(30) 就可以儲存 30 個字元

常規設定及操作

伺服器及設定

伺服器設定遠端存取

grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;

Linux中資料庫的基本操作命令

啟動：service mysql start
停止：service mysql stop
重新啟動：service mysql restart
清屏：clear,reset

備份資料庫

# mysqldump -uroot -p密碼 資料庫名 > D:/備份檔名.sql

恢復備份的資料庫

# 首先在mysql裡建好資料庫名
# mysql -uroot -p密碼 資料庫名 < D:/備份檔名.sql

查詢binlog紀錄檔是否開啟

show variables like 'log_%';

基本操作

檢視資料列表

show databases;					# 檢視所有資料庫
show create table 表名;		# 檢視表的建立細節
desc 表名; 					# 檢視表結構

進入資料庫

# use 資料名
use localdb

建立資料庫

# CREATE DATABASE 資料庫名
CREATE DATABASE localdb; 
CREATE DATABASE localdb charset utf8;

# 修改資料庫編碼
alter database localdb charset gbk;

刪除資料庫

# drop database 需要刪除的資料庫名
drop database localdb;

檢視表

select database(); 	 	# 檢視當前所在的庫
show tables; 		 	# 檢視資料庫中所有表
desc 表名; 				# 檢視表結構
show create table 表名; 	# 檢視表的建立細節

建立表

# 建立新表
# create table 新建資料表名（
#      欄位名 型別(長度) [約束(具體見1)],
#      欄位名 型別(長度) [約束(具體見1)]
#  );
create table class(
   id INT AUTO_INCREMENT,
name varchar(32) UNIQUE,
age varchar(32) NOT NULL
);

# 根據已有的表建立新表
create table 新表 like 舊錶  	# 使用舊錶建立新表,只複製表結構和全部索引
create table 新表 as select 欄位1 欄位2... from definition only  # as用來建立相同表結構並複製源表資料

# 可以拷貝一個表中其中的一些欄位:
CREATE TABLE newadmin AS
(
    SELECT username, password FROM admin
)

# 可以將新建的表的欄位改名:
CREATE TABLE newadmin AS
(  
    SELECT id, username AS uname, password AS pass FROM admin
)

# 可以拷貝一部分資料
CREATE TABLE anewadmin AS
(
    SELECT id,`name` AS username FROM users WHERE id = 1
)

# 可以在建立表的同時定義表中的欄位資訊:
CREATE TABLE newadmin
(
    id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY/id varchar(20)等等
)
AS
(
    SELECT * FROM admin
)

刪除表

# drop table 資料庫表名
drop table Python

修改表

alter table 表名 add 欄位名 型別（長度） [約束];	 # 新增列
alter table 表名 modify 欄位名 型別（長度） [約束]; # 修改列的型別長度及約束
alter table 表名 change 舊欄位名 新欄位名 型別（長度） [約束]; # 修改列表名
alter table 表名 drop 欄位名; # 刪除列
alter table 表名 character set 字元集; # 修改表的字元集
rename table 表名 to 新表名; # 修改表名

增加資料

insert into 表(欄位名1，欄位名2..) values(值1，值2..); # 向表中插入某些列
insert into 表 values(值1，值2，值3..); 		# 向表中插入所有列

# ON DUPLICATE KEY UPDATE   不存在則插入、存在則更新
# 此為mysql特有語法。不是標準sql語法。
# mysql語法支援如果資料存在則更新，不存在則插入，首先判斷資料存在還是不存在的那個欄位要設定成unique索引。
INSERT INTO 表名(唯一索引列, 列2, 列3) VALUE(值1, 值2, 值3) ON DUPLICATE KEY UPDATE 列=值, 列=值

# REPLACE INTO 不存在則插入、存在則先刪除後插入
REPLACE INTO 表名稱(列1, 列2, 列3) VALUES(值1, 值2, 值3)

修改資料

update 表名 set 欄位名=值,欄位名=值...;  # 這個會修改所有的資料，把一列的值都變了
update 表名 set 欄位名=值,欄位名=值... where 條件; # 只改符合where條件的行

刪除資料

# 1. delete可以刪除行資料，也可以對整表資料刪除，一般與where關聯使用。delete刪除內容，釋放內容空間，但是表結構和表定義不刪除。delete每次刪除一行時，都會將該行的刪除操作作為事務記錄在紀錄檔中。以便進行回滾操作。
delete from 表名  # 刪除表中所有記錄
delete from 表名 where 條件 # 刪除符合 where條件的資料

# 2. truncate只清除表中的內容，釋放內容所佔空間，但是表結構和表定義不刪除。其次truncate不能刪除行資料，比delete刪除的徹底。
truncate table 表名; 

# 3. drop刪除資料和定義，釋放空間，它會將表的結構也一起刪除掉。
drop table 表名

執行速度：drop > truncate > delete：因為delete每執行一次，都要在事務紀錄檔中記錄一次。所以最慢

資料的簡單查詢

select * from 表名; # 查詢所有列
select 欄位名1,欄位名2,欄位名3.. from 表名; # 查詢指定列

基本的sql語句

select * from 表名 where 範圍                           # 選擇查詢
insert into 表名(field1,field2) values(value1,value2)  # 插入
delete from 表名 where 範圍                             # 刪除
update 表名 set field1=value1 where 範圍                # 更新
select * from 表名 where field1 like ’%value1%’        # 查詢
select * from 表名 order by field1,field2 [desc]       # 排序
select count as 需要統計總數的欄位名 from 表名             # 總數
select sum(field1) as sumvalue from 表名               # 求和
select avg(field1) as avgvalue from 表名               # 平均
select max(field1) as maxvalue from 表名               # 最大
select min(field1) as minvalue from 表名               # 最小

儲存引擎

# 檢視所有的儲存引擎
show engines;

# 檢視不同儲存引擎儲存表結構檔案特點
create table t1(id int)engine=innodb; # MySQL預設的儲存引擎,支援事務,支援行鎖，支援外來鍵。有且只有一個主鍵，用來組織資料的依據
create table t2(id int)engine=myisam; # 不支援事務，不支援外來鍵，支援全文索引，處理速度快。
create table t3(id int)engine=blackhole; # 黑洞，寫入它的任何內容都會消失
create table t4(id int)engine=memory; # 將表中的資料儲存在記憶體中。表結構以檔案儲存於磁碟。

insert into t1 values(1);
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);

設定嚴格模式

# 查詢
show variables like '%mode%';
# 設定
set session # 設定當前視窗下有效
set global  # 全域性有效，終身受用
set global sql_mode = "STRICT_TRANS_TABLES";
# 設定完成後需要退出使用者端，重新登入使用者端即可，不需要重新啟動伺服器端

group by分組涉及到的模式：
設定sql_mode為only_full_group_by，意味著以後但凡分組，只能取到分組的依據，
不應該在去取組裡面的單個元素的值，那樣的話分組就沒有意義了，因為不分組就是對單個元素資訊的隨意獲取

set global sql_mode="strict_trans_tables,only_full_group_by";
# 重新連結使用者端

like 的用法

A:% 包含零個或多個字元的任意字串：
1、like'Mc%' 將搜尋以字母 Mc 開頭的所有字串（如 McBadden）。
2、like'%inger' 將搜尋以字母 inger 結尾的所有字串（如 Ringer、Stringer）。
3、like'%en%' 將搜尋在任何位置包含字母 en 的所有字串（如 Bennet、Green、McBadden）。

B:_（下劃線） 任何單個字元：
like'_heryl' 將搜尋以字母 heryl 結尾的所有六個字母的名稱（如 Cheryl、Sheryl）。

C：[ ] 指定範圍 ([a-f]) 或集合 ([abcdef]) 中的任何單個字元： 
1，like'[CK]ars[eo]n' 將搜尋下列字串：Carsen、Karsen、Carson 和 Karson（如 Carson）。
2、like'[M-Z]inger' 將搜尋以字串 inger 結尾、以從 M 到 Z 的任何單個字母開頭的所有名稱（如 Ringer）。

D：[^] 不屬於指定範圍 ([a-f]) 或集合 ([abcdef]) 的任何單個字元：
like'M[^c]%' 將搜尋以字母 M 開頭，並且第二個字母不是 c 的所有名稱（如MacFeather）。

E：* 它同於DOS命令中的萬用字元，代表多個字元：
c*c代表cc,cBc,cbc,cabdfec等多個字元。

F：？同於DOS命令中的？萬用字元，代表單個字元 :
b?b代表brb,bFb等

G：# 大致同上，不同的是代只能代表單個數位。k#k代表k1k,k8k,k0k 。

下面我們來舉例說明一下：

例1，查詢name欄位中包含有「明」字的。
select * from table1 where name like '%明%'

例2，查詢name欄位中以「李」字開頭。
select * from table1 where name like '李%'

例3，查詢name欄位中含有數位的。
select * from table1 where name like '%[0-9]%'

例4，查詢name欄位中含有小寫字母的。
select * from table1 where name like '%[a-z]%'

例5，查詢name欄位中不含有數位的。
select * from table1 where name like '%[!0-9]%'

查詢操作

外來鍵表建立

一對多（Foreign Key）
# foreign key(需要關聯的本欄位) references 需要關聯對錶的表(需要關聯對錶的欄位)
例如：
建立dep
foreign key(dep_id) references dep(id)
# 同步更新，同步刪除
on update cascade # 同步更新
on delete cascade # 同步刪除

複製表

# 複製表
create table t1 select * from test;
# 只複製表結構
create table t1 select * from test where 1=2;

單表查詢

where 查詢

# between 在...之間
select * from emp where id between 3 and 6;  
相當於：
select id,name from emp where id >= 3 and id <= 6;

# or 或者
select *  from emp where id >= 3 or id <= 6;

# in，後面可以跟多個值
select * from 表名 where 欄位名 in (條件1,條件2,條件3); 

# char——length()  取字元長度
select * from 表名 where char_length(需要獲取長度的欄位名) = 4;

not 配合使用
注意：判斷空不能用 = ，只能用 is 
# null 表示什麼也不是， 不能=、>、< … 所有的判斷，結果都是false，所有隻能用 is null進行判斷。
# 預設情況下，推薦使用 IS NOT NULL去判斷，因為SQL預設情況下對！= Null的判斷會永遠返回0行，但沒有語法錯誤.如果你一定想要使用！= Null來判斷，需要加上這個語句：
set ANSI_NULLS off # 這時你會發現IS NOT NULL 和 != null 是等效的

group by 分組

select 查詢欄位1,查詢欄位2,... from 表名
   where 過濾條件
   group by分組依據  # 分組後取出的是每個組的第一條資料

聚合查詢 ：以組為單位統計組內資料>>>聚合查詢(聚集到一起合成為一個結果)

# max 最大值
# 每個部門的最高工資
select post,max(salary) from emp group by post;

# min 最小值
# 每個部門的最低工資
select post,min(salary) from emp group by post;

# avg 平均值
# 每個部門的平均工資
select post,avg(salary) from emp group by post;
# 每個部門的工資總和

# sum 求和
select post,sum(salary) from emp group by post;

# 求班級男女人數統計
select sum(case when sex ='男' then 1 else 0 end) 男,sum(case when sex ='女' then 1 else 0 end) 女,count(*) 總數 from student

# count（需要計數位段） 計數
# 每個部門的人數
select post,count(id) from emp group by post;

# concat() 函數用於將多個字串連線成一個字串
CONCAT(str1,str2,…)  # 返回結果為連線引數產生的字串。如有任何一個引數為NULL ，則返回值為 NULL。可以有一個或多個引數
舉例：
   select concat("NAME: ",name) as 姓名 from emp;
   
# concat_ws: 指定拼接引數之間的分隔符
CONCAT_WS(separator,str1,str2,…) # CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。第一個引數是其它引數的分隔符。分隔符的位置放在要連線的兩個字串之間。分隔符可以是一個字串，也可以是其它引數。如果分隔符為 NULL，則結果為 NULL。函數會忽略任何分隔符引數後的 NULL 值。但是CONCAT_WS()不會忽略任何空字串。 (然而會忽略所有的 NULL）。
舉例：
   select concat_ws(':',name,age,sex) as info from emp;
   
# group_concat函數返回一個字串結果，該結果由分組中的值連線組合而成。
舉例:
select post,group_concat(name) from emp group by post;  # post：分組欄位，name 需要分組後顯示的欄位

# group_concat完整的語法如下:
group_concat([DISTINCT] 要連線的欄位 [Order BY ASC/DESC 排序欄位] [Separator '分隔符'])

# group_concat 的理解
select * from aa; 

+------+------+
| id| name |
+------+------+
|1 | 10|
|1 | 20|
|1 | 20|
|2 | 20|
|3 | 200 |
|3 | 500 |
+------+------+
6 rows in set (0.00 sec)

# 以id分組，把name欄位的值列印在一行，逗號分隔(預設)
select id,group_concat(name) from aa group by id;  
+------+--------------------+
| id| group_concat(name) |
+------+--------------------+
|1 | 10,20,20|
|2 | 20 |
|3 | 200,500|
+------+--------------------+
3 rows in set (0.00 sec)

# 以id分組，把name欄位的值列印在一行，分號分隔
select id,group_concat(name separator ';') from aa group by id;  
+------+----------------------------------+
| id| group_concat(name separator ';') |
+------+----------------------------------+
|1 | 10;20;20 |
|2 | 20|
|3 | 200;500 |
+------+----------------------------------+
3 rows in set (0.00 sec)

# 以id分組，把name欄位的值列印在一行，逗號分隔，以name排倒序
select id,group_concat(name order by name desc) from aa group by id; 
+------+---------------------------------------+
| id| group_concat(name order by name desc) |
+------+---------------------------------------+
|1 | 20,20,10 |
|2 | 20|
|3 | 500,200|
+------+---------------------------------------+
3 rows in set (0.00 sec)

having 過濾查詢

# having的語法格式與where一致，只不過having是在分組之後進行的過濾，即where雖然不能用聚合函數，但是having可以！

# 用法
select 查詢欄位1,查詢欄位2,... from 表名
   where 過濾條件1
   group by分組依據
having avg(過濾條件2) > 10000;

distinct 去重

# 對有重複的展示資料進行去重操作
select distinct 需取重欄位 from 表名;

order by 排序

select * from emp order by salary asc; # 預設升序
select * from emp order by salary desc; # 降序

# 多條件排序
# 先按照age降序，在年齡相同的情況下再按照薪資升序
select * from emp order by age desc,salary asc; 

# 按指定內容排序
SELECT xxi.CALLPER, xxi.QXMC, COUNT(1) AS CS, exi.EVENT_TYPE
FROM XFR_XFJ_INFO xxi
	LEFT JOIN EVENT_XFJ_INFO exi ON xxi.EVENT_ID = exi.EVENT_ID
WHERE exi.EVENT_CLASS = '其他'
GROUP BY xxi.CALLPER
ORDER BY CASE 
	WHEN exi.EVENT_TYPE LIKE '%越級%' THEN 3
	WHEN exi.EVENT_TYPE LIKE '%重複%' THEN 2
	WHEN exi.EVENT_TYPE LIKE '%群體%' THEN 1
	WHEN exi.EVENT_TYPE LIKE '%敏感事件%' THEN 0
END DESC
LIMIT 5

limit 限制展示條數

# 限制展示條數
select * from emp limit 3;
# 查詢工資最高的人的詳細資訊
select * from emp order by salary desc limit 1;

# 分頁顯示
select * from emp limit 0,5;  # 第一個參數列示起始位置，第二個參數列示的是條數，不是索引位置
select * from emp limit 5,5;

# 如果確定查詢結果只有一條時，加上limti 1 可以提高查詢速度。

正則

select * from emp where name regexp '^j.*(n|y)$';

replace 替換

replace(str1,old,new) # str1：需要替換的欄位名
update gd_km set mc=replace（mc,'土地','房子'）
說明：new替換str1中出現的所有old，返回新的字串，如果有某個引數為NULL，此函數返回NULL 
該函數可以多次替換，只要str1中還有old存在，最後都被替換成new 
若new為空，則刪除old

多表查詢

內連線、左連線、右連線、全連線

# 內連線：只取兩張表有對應關係的記錄（只拼兩個表共有的）
左表 inner join 右表 on 條件
select * from emp inner join dep on emp.dep_id = dep.id
    where dep.name = "技術";

# 左連線：在內連線的基礎上，保留左邊的資料，右邊沒有就為空
左表 inner left 右表 on 條件


# 右連線：在內連線的基礎上，保留右邊的資料，左邊沒有就為空
左表 inner right 右表 on 條件

# 全連線：左右連線都有，用union連線
左表 inner left 右表 on 條件 union 左表 inner right 右表 on 條件

select * from emp left join dep on emp.dep_id = dep.id
union
select * from emp right join dep on emp.dep_id = dep.id;

子查詢

# 就是將一個查詢語句的結果用括號括起來當作另外一個查詢語句的條件去用
select name from where id in(select dep_id from emp where age>25);

日期

時間格式化

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')

常用方法

獲取當前時間

SELECT CURDATE() -- 2020-09-27
CURRENT_TIME() -- 00:00:00
SELECT NOW() -- 2020-09-27 00:00:00

獲取當時時間戳（10位）

SELECT UNIX_TIMESTAMP() -- 1601190811

格式化時間戳

SELECT FROM_UNIXTIME(unix_timestamp(),'%Y-%m-%d %H:%i:%s')

處理13位的時間戳

SELECT FROM_UNIXTIME(16011908110000/1000,'%Y-%m-%d %H:%i:%s')

PS:

  1. 10位時間戳和13位時間戳的區別：

    ​ java的date預設精度是毫秒，也就是說生成的時間戳就是13位的，而像c++或者php因為其精度是秒，所以生成的時間戳預設就是10位的。

  2. 如何相互轉換

    ​ 將13位時間戳除以1000取整，將10位時間戳*1000L

返回兩個日期相減（expr1 − expr2 ）相差的天數

DATEDIFF(expr1, expr2) 

SELECT DATEDIFF('2001-01-01','2001-02-02') -- 32
SELECT DATEDIFF('2019-01-14 14:32:59','2019-01-02') -- 12

返回兩個日期相減（expr1 − expr2 ）相差的時間數

TIMEDIFF(expr1, expr2) # 兩個引數型別必須相同
SELECT TIMEDIFF('18:32:59','60000') -- 12:32:59  
SELECT TIMEDIFF('18:32:59','2019-1-1 60000') -- NULL

日期時間運算函數

# 分別為給定的日期date加上(add)或減去(sub)一個時間間隔值expr
　# 格式:
　DATE_ADD(date, INTERVAL  expr  unit);
　DATE_SUB(date, INTERVAL  expr  unit);
　
　interval是間隔型別關鍵字
  expr是一個表示式，對應後面的型別
  unit是時間間隔的單位(間隔型別)（20個）
interval含義
HOUR小時
MINUTE分
SECOND秒
MICROSECOND毫秒
YEAR年
MONTH月
DAY日
WEEK周
QUARTER季
YEAR_MONTH年和月
DAY_HOUR日和小時
DAY_MINUTE日和分鐘
DAY_ SECOND日和秒
HOUR_MINUTE小時和分
HOUR_SECOND小時和秒
MINUTE_SECOND分鐘和秒
# 加一天
select now(),date_add(now(),interval 1 day);　　
+---------------------+--------------------------------+
| now()               | date_add(now(),interval 1 day) |
+---------------------+--------------------------------+
| 2020-01-01 00:59:59 | 2020-01-02 00:59:59          |
+---------------------+--------------------------------+

# 減1天1小時1分1秒
SELECT date_sub('2020-01-03 00:00:00',INTERVAL '1 1:1:1' DAY_SECOND) -- 2020-01-01 22:58:59

選取日期時間的各個部分(常用)

SELECT now(),date(now()); -- 日期

SELECT now(),time(now()); -- 時間

SELECT now(),year(now()); -- 年

SELECT now(),quarter(now()); -- 季度

SELECT now(),month(now()); -- 月

SELECT now(),week(now()); -- 周

SELECT now(),day(now()); -- 日

SELECT now(),hour(now()); -- 小時

SELECT now(),minute(now()); -- 分鐘

SELECT now(),second(now()); -- 秒

SELECT now(),microsecond(now()); -- 微秒

日期位置計算

# DAYOFWEEK函數返回日期的工作日索引值，即星期日為1，星期一為2，星期六為7。 這些索引值對應於ODBC標準。

# 返回日期d在一週、一月、一年中是第幾天
dayofweek(d)
dayofmonth(d)
dayofyear(d)

# 計算日期 d 是本年的第幾個星期，範圍是 0 到 53
WEEK(d)
SELECT WEEK('2011-11-11 11:11:11') -- 45

# 計算日期 d 距離 0000 年 1 月 1 日的天數
TO_DAYS(d)
SELECT TO_DAYS('0001-01-01 01:01:01') -- 366

# 日期 d 是星期幾，0 表示星期一，1 表示星期二
WEEKDAY(d)
SELECT WEEKDAY("2017-06-15") -- 3

# 計算日期 d 是本年的第幾個星期，範圍是 0 到 53
WEEKOFYEAR(d)
SELECT WEEKOFYEAR('2011-11-11 11:11:11') -- 45

查詢例子

# 查詢近一天：
select * from table where to_days(column_time) = to_days(now());
select * from table where date(column_time) = curdate(); 

# 查詢近一週：
select * from table  where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(column_time);

# 查詢近一個月：
select * from table  where DATE_SUB(CURDATE(), INTERVAL INTERVAL 1 MONTH) <= date(column_time);

mybatis xml 查詢時間範圍

<if test="eventCondition.times != null">
            <foreach collection="eventCondition.times" index="index" item="item">
                <if test="index == 0 and item != ''">
                    AND DATE_FORMAT( XFRQ ,'%Y-%m-%d') &gt;= DATE_FORMAT( #{item},'%Y-%m-%d')
                </if>
                <if test="index == 1 and item != ''">
                    AND DATE_FORMAT( XFRQ ,'%Y-%m-%d') &lt;= DATE_FORMAT( #{item},'%Y-%m-%d')
                </if>
            </foreach>
        </if>

查前十來電比例

SELECT EVENT_NAME, LDCS
	, CONCAT(ROUND(LDCS / (
		SELECT SUM(LDCS)
		FROM EVENT_12345_INFO
	) * 100.00, 2), '%') AS proportion
FROM EVENT_12345_INFO
ORDER BY LDCS DESC
LIMIT 10

查詢每天的平均辦理時間和最大辦理所花時間

SELECT DATE_FORMAT(xi.BJSJ, '%Y-%m-%d') AS BJSJ
	, ROUND(AVG(TIMESTAMPDIFF(SECOND, xi.DJSJ, xi.BJSJ) / 3600), 2) AS avgHours
	, MAX(TIMESTAMPDIFF(HOUR, xi.DJSJ, xi.BJSJ)) AS maxHours
	, COUNT(1) AS once
FROM EVENT_XFJ_INFO exi
	LEFT JOIN XFJ_INFO xi ON exi.EVENT_ID = xi.EVENT_ID
WHERE exi.EVENT_CLASS = '其他'
	AND xi.BJBZ = 1
GROUP BY DATE_FORMAT(xi.BJSJ, '%Y-%m-%d')
ORDER BY DATE_FORMAT(xi.BJSJ, '%Y-%m-%d') DESC