概要

執行順序

# 書寫順序
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

常用資料型別

數值型別

日期和時間型別

字串型別

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個）

# 加一天
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