前言：

日常工作或?qū)W習(xí)過(guò)程中，我們可能會(huì)經(jīng)常用到某些SQL，建議大家多多整理記錄下這些常用的SQL，這樣后續(xù)用到會(huì)方便很多。筆者在工作及學(xué)習(xí)過(guò)程中也整理了下個(gè)人常用的SQL，現(xiàn)在分享給你!可能有些SQL你還不常用，但還是希望對(duì)你有所幫助，說(shuō)不定某日有需求就可以用到。

[[279620]]

注：下文分享的SQL適用于MySQL 5.7 版本，低版本可能稍許不同。有些SQL可能執(zhí)行需要較高權(quán)限。

1.show相關(guān)語(yǔ)句

# 查看實(shí)例參數(shù) 例如： 
show variables like '%innodb%'; 
show global variables like '%innodb%'; 
# 查看實(shí)例狀態(tài)，例如： 
show status like 'uptime%'; 
show global status like 'connection%'; 
# 查看數(shù)據(jù)庫(kù)鏈接： 
show processlist; 
show full processlist; 
# 查詢某個(gè)表的結(jié)構(gòu)： 
show create table tb_name; 
# 查詢某個(gè)表的詳細(xì)字段信息： 
show full columns from tb_name; 
# 查詢某個(gè)表的全部索引信息： 
show index from tb_name; 
# 查詢某個(gè)庫(kù)以cd開(kāi)頭的表： 
show tables like 'cd%'; 
# 查詢某個(gè)庫(kù)中的所有視圖： 
show table status where comment='view'; 
# 查詢某個(gè)用戶的權(quán)限： 
show grants for 'test_user'@'%'; 

2.查看賬戶相關(guān)信息

# 這里先介紹下CONCAT函數(shù)：在MySQL中 CONCAT()函數(shù)用于將多個(gè)字符串連接成一個(gè)字符串， 
利用此函數(shù)我們可以將原來(lái)一步無(wú)法得到的sql拼接出來(lái)，后面部分語(yǔ)句有用到該函數(shù)。 
# 當(dāng)拼接字符串中出現(xiàn)''時(shí) 需使用\轉(zhuǎn)義符 
# 查看所有用戶名： 
SELECT DISTINCT 
 CONCAT( 
 'User: \'', 
 user, 
 '\'@\'', 
 host, 
 '\';' 
 ) AS QUERY 
FROM 
 mysql.user; 
# 查看用戶詳細(xì)信息： 
SELECT user, 
 host, 
 authentication_string, 
 password_expired, 
 password_lifetime, 
 password_last_changed, 
 account_locked  
FROM 
 mysql.user; 

3.KILL數(shù)據(jù)庫(kù)鏈接

# 下面列舉SQL只是拼接出kill 鏈接的語(yǔ)句，若想執(zhí)行 直接將結(jié)果復(fù)制執(zhí)行即可。 
# 殺掉空閑時(shí)間大于2000s的鏈接： 
SELECT 
 concat( 'KILL ', id, ';' )  
FROM 
 information_schema.`PROCESSLIST`  
WHERE 
 Command = 'Sleep'  
 AND TIME > 2000; 
  
# 殺掉處于某狀態(tài)的鏈接： 
SELECT 
 concat( 'KILL ', id, ';' )  
FROM 
 information_schema.`PROCESSLIST`  
WHERE 
 STATE LIKE 'Creating sort index'; 
  
# 殺掉某個(gè)用戶的鏈接： 
SELECT 
 concat( 'KILL ', id, ';' )  
FROM 
 information_schema.`PROCESSLIST`  
WHERE 
 where user='root'; 

4.拼接創(chuàng)建數(shù)據(jù)庫(kù)或用戶語(yǔ)句

# 拼接創(chuàng)建數(shù)據(jù)庫(kù)語(yǔ)句（排除系統(tǒng)庫(kù)）： 
SELECT 
 CONCAT( 
 'create database ', 
 '`', 
 SCHEMA_NAME, 
 '`', 
 ' DEFAULT CHARACTER SET ', 
 DEFAULT_CHARACTER_SET_NAME, 
 ';' 
 ) AS CreateDatabaseQuery 
FROM 
 information_schema.SCHEMATA 
WHERE 
 SCHEMA_NAME NOT IN ( 
 'information_schema', 
 'performance_schema', 
 'mysql', 
 'sys' 
 ); 
  
# 拼接創(chuàng)建用戶語(yǔ)句（排除系統(tǒng)用戶）： 
SELECT 
 CONCAT( 
 'create user \'', 
 user, 
 '\'@\'', 
 Host, 
 '\'' 
 ' IDENTIFIED BY PASSWORD \'', 
 authentication_string, 
 '\';' 
 ) AS CreateUserQuery 
FROM 
 mysql.`user` 
WHERE 
 `User` NOT IN ( 
 'root', 
 'mysql.session', 
 'mysql.sys' 
 ); 
# 有密碼字符串哦 在其他實(shí)例執(zhí)行 可直接創(chuàng)建出與本實(shí)例相同密碼的用戶。 

5.查看庫(kù)或表大小

# 查看整個(gè)實(shí)例占用空間大?。?nbsp;
SELECT 
 concat( round( sum( data_length / 1024 / 1024 ), 2 ), 'MB' ) AS data_length_MB, 
 concat( round( sum( index_length / 1024 / 1024 ), 2 ), 'MB' ) AS index_length_MB  
FROM 
 information_schema.`TABLES`; 
  
# 查看各個(gè)庫(kù)占用大?。?nbsp;
SELECT 
 TABLE_SCHEMA, 
 concat( TRUNCATE ( sum( data_length )/ 1024 / 1024, 2 ), ' MB' ) AS data_size, 
 concat( TRUNCATE ( sum( index_length )/ 1024 / 1024, 2 ), 'MB' ) AS index_size  
FROM 
 information_schema.`TABLES` 
GROUP BY 
 TABLE_SCHEMA; 
  
# 查看單個(gè)庫(kù)占用空間大?。?nbsp;
SELECT 
 concat( round( sum( data_length / 1024 / 1024 ), 2 ), 'MB' ) AS data_length_MB, 
 concat( round( sum( index_length / 1024 / 1024 ), 2 ), 'MB' ) AS index_length_MB  
FROM 
 information_schema.`TABLES` 
WHERE 
 table_schema = 'test_db'; 
  
# 查看單個(gè)表占用空間大小： 
SELECT 
 concat( round( sum( data_length / 1024 / 1024 ), 2 ), 'MB' ) AS data_length_MB, 
 concat( round( sum( index_length / 1024 / 1024 ), 2 ), 'MB' ) AS index_length_MB  
FROM 
 information_schema.`TABLES` 
WHERE 
 table_schema = 'test_db'  
 AND table_name = 'tbname'; 

6.查看表碎片及收縮語(yǔ)句

# 查看某個(gè)庫(kù)下所有表的碎片情況： 
SELECT 
 t.TABLE_SCHEMA, 
 t.TABLE_NAME, 
 t.TABLE_ROWS, 
 concat( round( t.DATA_LENGTH / 1024 / 1024, 2 ), 'M' ) AS size, 
 t.INDEX_LENGTH, 
 concat( round( t.DATA_FREE / 1024 / 1024, 2 ), 'M' ) AS datafree  
FROM 
 information_schema.`TABLES` t  
WHERE 
 t.TABLE_SCHEMA = 'test_db'  
ORDER BY 
 datafree DESC; 
  
# 收縮表，減少碎片： 
alter table tb_name engine = innodb; 
optimize table tb_name; 

7.查找無(wú)主鍵表

# 查找某一個(gè)庫(kù)無(wú)主鍵表： 
SELECT 
table_schema, 
table_name 
FROM 
 information_schema.`TABLES` 
WHERE 
 table_schema = 'test_db' 
AND TABLE_NAME NOT IN ( 
 SELECT 
 table_name 
 FROM 
 information_schema.table_constraints t 
 JOIN information_schema.key_column_usage k USING ( 
 constraint_name, 
 table_schema, 
 table_name 
 ) 
 WHERE 
 t.constraint_type = 'PRIMARY KEY' 
 AND t.table_schema = 'test_db' 
); 
# 查找除系統(tǒng)庫(kù)外 無(wú)主鍵表： 
SELECT 
 t1.table_schema, 
 t1.table_name 
FROM 
 information_schema.`TABLES` t1 
LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS t2 ON t1.table_schema = t2.TABLE_SCHEMA 
AND t1.table_name = t2.TABLE_NAME 
AND t2.CONSTRAINT_NAME IN ('PRIMARY') 
WHERE 
 t2.table_name IS NULL 
AND t1.TABLE_SCHEMA NOT IN ( 
 'information_schema', 
 'performance_schema', 
 'mysql', 
 'sys' 
) ; 

總結(jié)：

希望這些SQL語(yǔ)句能對(duì)你有所幫助，可以收藏一下，說(shuō)不定某次就用到了呢!