前言：

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

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

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

# 查看實(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ù)庫鏈接：
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è)庫以cd開頭的表：
show tables like 'cd%';

# 查詢某個(gè)庫中的所有視圖：
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ù)我們可以將原來一步無法得到的sql拼接出來酵镜，后面部分語句有用到該函數(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ù)庫鏈接

# 下面列舉SQL只是拼接出kill 鏈接的語句，若想執(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ù)庫或用戶語句

# 拼接創(chuàng)建數(shù)據(jù)庫語句（排除系統(tǒng)庫）：
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)建用戶語句（排除系統(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.查看庫或表大小

# 查看整個(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è)庫占用大锌糠唷：
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è)庫占用空間大欣伞：
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.查看表碎片及收縮語句

# 查看某個(gè)庫下所有表的碎片情況：
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.查找無主鍵表

# 查找某一個(gè)庫無主鍵表：
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)庫外 無主鍵表：
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語句能對(duì)你有所幫助昔善，可以收藏一下，說不定某次就用到了呢畔乙！原創(chuàng)不易君仆，感謝大家支持。