阿里云RDS數(shù)據(jù)庫(kù)常用SQL語(yǔ)句分享

日常工作或?qū)W習(xí)過程中，會(huì)常用到某些SQL語(yǔ)句，又不太容易記憶的。建議大家多多整理記錄下這些常用的SQL，這樣后續(xù)用到會(huì)方便很多。我在工作及學(xué)習(xí)過程中也整理了下個(gè)人常用的SQL，現(xiàn)在借云棲社區(qū)這個(gè)平臺(tái)分享給大家。可能有些SQL你還不常用，但還是希望有所幫助，說不定將來哪天有需求就能用到。

注：下文分享的SQL適用于MySQL 5.7 版本，低版本可能稍許不同。有些SQL可能執(zhí)行需要較高權(quán)限。都在阿里云RDS數(shù)據(jù)庫(kù)中使用過，沒問題了。

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開頭的表：

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ù)我們可以將原來一步無(wú)法得到的sql拼接出來，后面部分語(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ǔ)句

下面列舉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';

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

查看整個(gè)實(shí)例占用空間大小：

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ù)占用大?。?/p>

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ù)占用空間大?。?/p>

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è)表占用空間大?。?/p>

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ù)下所有表的碎片情況：

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;