1.NOT EXISTS 和 NOT IN
SELECT COUNT(ca.aaa)
FROM xx ca
WHERE NOT EXISTS(
SELECT label.*
FROM xxx label
WHERE label.aaa = ca.aaa
)
SELECT COUNT(ca.aaa)
FROM xx ca
WHERE ca.aaa NOT IN(
SELECT label.aaa
FROM xxx label
WHERE label.aaa = ca.aaa
)
2.查詢表結構
-- 查詢指定表的欄位結構
select (@i:=@i+1) i,COLUMN_NAME,COLUMN_TYPE,IS_NULLABLE,COLUMN_COMMENT
from information_schema.columns,(SELECT @i:=0) as i where table_schema = 'xxx' #表所在資料庫
and table_name = 'xxx' ; #你要查的表
-- 查詢所有表結構
select table_name,COLUMN_NAME,COLUMN_TYPE,IS_NULLABLE,COLUMN_COMMENT,IF(columns.COLUMN_KEY='PRI','是',NULL) AS IS_KEY
from information_schema.columns columns where table_schema = 'xxx'; #表所在資料庫
-- 查詢所有表名
show tables;
-- 查詢所有表名和記錄總數
SELECT TABLE_NAME,TABLE_COMMENT,TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='xxx';
-- 查詢有url欄位的表
SELECT table_name, (@i:=@i+1) i,COLUMN_NAME,COLUMN_TYPE,IS_NULLABLE,COLUMN_COMMENT
FROM information_schema.columns,(SELECT @i:=0) as i
WHERE table_schema = 'xxx'
AND (COLUMN_NAME LIKE '%url%' OR COLUMN_NAME LIKE '%path%' OR COLUMN_NAME LIKE '%file%');
3.按照指定欄位分組，有著重複欄位的多條記錄中，取修改時間最新的那條記錄
SELECT ca.*
FROM (select aaa, MAX(change_time) as change_time from xxx group by aaa) AS temp
LEFT JOIN xxx ca ON temp.aaa = ca.aaa AND temp.change_time = ca.change_time
4.資料庫鎖、執行緒
（1）資料庫鎖等待超時
java中報錯：java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
先檢視當前庫的所有執行緒
show full processlist;
然後檢視innodb（儲存引擎）的事務表innodb_trx，看下里面是否有正在鎖定的事務執行緒
select * from information_schema.innodb_trx;
有一個正在鎖定的事務執行緒 trx_state：RUNNING trx_mysql_thread_id：527491，
看看ID是否在show full processlist裡面的sleep執行緒中：
如果是，就證明這個sleep的執行緒事務一直沒有commit或者rollback被卡住了，我們需要手動kill掉。
最後殺掉該執行緒
kill 527491;
information_schema資料庫 INNODB_LOCKS、INNODB_TRX
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout'
（2）死鎖：
Deadlock found when trying to get lock; try restarting transaction
找到引起死鎖的語句，然後針對語句進行最佳化
show engine innodb status;
5.IF函式
SELECT IF(lo.aaa=1,lo.bbb*12,lo.bbb) as rate
WHERE IF(ld.aaa!=0, lo.`bbb`=10, ld.ccc=0)
6.排序
-- DESC 降序時候預設null值排在後面、ASC升序時預設null值排在前面，可使用 IS NULL處理
null值放在最後
ORDER BY sort_num is null, sort_num
7.存放數字的字串欄位排序
SELECT data_value
FROM xxx
ORDER BY data_value + 0 DESC;
SELECT aaa
FROM xxx
GROUP BY aaa
ORDER BY STR_TO_DATE(aaa, '%Y年%m月') DESC;
8.FIELD函式
對查詢結果集進行指定順序排序
ORDER BY field(p.aaa, 'ccc','bbb')
9.FIND_IN_SET函式
查詢 某元素 是否存在於 集合型別的字串
SELECT *
FROM xxx
WHERE FIND_IN_SET('auditJob', job) -- auditJob存放值：job,test
10.case...when
CASE [col_name]
WHEN [value1] THEN [result1]
WHEN [value1] THEN [result1]
ELSE [default]
END
SELECT aaa as 測試,
CASE bbb
WHEN 0 THEN '開發'
WHEN 1 THEN '產品'
ELSE '專案'
END as 運維,
11.查詢結果集的序號
SELECT (@i:=@i+1) as 序號
FROM xxx, (select @i:=0) t
12.distinct
去重的統計
SELECT count(DISTINCT(aaa))
from xxx
WHERE `status`=1
13.REPLACE函式
url去掉第一個字元，並把/改成_
UPDATE xxx
SET aaa = REPLACE(SUBSTRING(aaa,2), '/', '_')
UPDATE xxx
SET aaa = REPLACE(aaa,'.png','-short.png');
14.GROUP_CONCAT()函式
分組後資料值的拼接（多個用,隔開，也可以指定分隔符）
SELECT GROUP_CONCAT(bbb)
FROM xxx
GROUP BY aaa
15.TRUNCATE TABLE
truncate刪除整表資料（或navicat工具中：右擊-截斷表）
與delete不同的是：
刪除後將重新水平線和索引(id從零開始)
16.設定資料包的大小
資料庫對大量資料插入或者更新有限制，可以自己設定
show global variables like 'max_allowed_packet';
set global max_allowed_packet=157286400;
mysql中，in語句中引數個數是不限制的，不過對整段sql語句的長度有了限制max_allowed_packet
17.upper(str)轉大寫、lower(str)轉小寫
18.字串連線concat函式
19.MAX、MIN函式
查詢最大id，用MAX(id)
查詢最小id，用MIN(id)
20.刪除或更新的表和條件用的表如果一樣
則條件表需起別名，刪除表不能有別名
delete from xxx where id in(
select u.id from xxx as u where u.status = 1
)
UPDATE xxx SET mobile = '110'
WHERE id in(
SELECT temp.id
FROM (
SELECT u.id
FROM xxx AS u
where u.status = 1
) AS temp
);
更新 SET 自己表
UPDATE xxx t1
INNER JOIN (select * from xxx as t where t.grant_num = 1) t2
ON t1.ccc = t2.ccc
SET t1.aaa = t2.bbb;
21.COUNT函式
SELECT COUNT(vo.id)
FROM(
SELECT ca.id
FROM xxx ca
LEFT JOIN xx ld ON ld.user_id = ca.user_id
WHERE ca.status=1 AND ld.id is NULL
GROUP BY ca.aaa
) vo
22.insert into select from
INSERT INTO `xxx`(`aaa`, `bbb`)
SELECT 1, '測試'
FROM `xx`
WHERE status= 0
LIMIT 1;
23.查詢重複資料
SELECT GROUP_CONCAT(id), GROUP_CONCAT(mobile)
FROM xxx
GROUP BY mobile HAVING count(mobile)>1;
24.時間查詢
timeType 1-近三年，2-近一年，3-近一週，4-近三天，5-今日
AND date(sentiment_time) BETWEEN CURDATE() - INTERVAL 3 YEAR AND CURDATE()
AND date(sentiment_time) BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 YEAR) AND CURDATE()
AND date(sentiment_time) BETWEEN DATE_SUB(CURDATE(),INTERVAL 7 DAY) AND CURDATE()
AND date(sentiment_time) BETWEEN CURDATE() - INTERVAL 3 DAY AND CURDATE()
AND date(sentiment_time) = CURDATE()
25.with as
26.聯合查詢
union 的查詢結果不會有重複項，因為它的內部使用了一次 distinct，
而 union all 的查詢結果包含重複項。
SELECT 'all' `bankTypeName`, COUNT(*) total
FROM xxx a
WHERE a.platform = 'xxx'
UNION ALL
SELECT 'bank' `bankTypeName`, COUNT(*) total
FROM xxx a
WHERE a.platform = 'xxx'
AND b.valid=1
注意：參與聯合查詢的各查詢結果的列數必須相同，對應項的資料型別也必須相同
同時查詢多張沒有關聯的表資料
(SELECT UNIT_TYPE, UNICODE,"NAME",'xxx' AS label from xxx)
union all
(SELECT UNIT_TYPE, UNICODE,"NAME",'xxx' AS label from xxx)
union all
(SELECT UNIT_TYPE, UNICODE,"NAME",'xxx' AS label from xxx)
列：相同數量、相似的資料型別、順序相同。返回第一個select的列名
27.評論區