常用SQL陳述句分享

前言：

日常作業或學習程序中，我們可能會經常用到某些SQL，建議大家多多整理記錄下這些常用的SQL，這樣后續用到會方便很多，筆者在作業及學習程序中也整理了下個人常用的SQL，現在分享給你！可能有些SQL你還不常用，但還是希望對你有所幫助，說不定某日有需求就可以用到，

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

1.show相關陳述句

# 查看實體引數 例如：
show variables like '%innodb%';
show global variables like '%innodb%';

# 查看實體狀態，例如：
show status like 'uptime%';
show global status like 'connection%';

# 查看資料庫鏈接：
show processlist;
show full processlist;

# 查詢某個表的結構：
show create table tb_name;

# 查詢某個表的詳細欄位資訊：
show full columns from tb_name;

# 查詢某個表的全部索引資訊：
show index from tb_name;

# 查詢某個庫以cd開頭的表：
show tables like 'cd%';

# 查詢某個庫中的所有視圖：
show table status where comment='view';

# 查詢某個用戶的權限：
show grants for 'test_user'@'%';

2.查看賬戶相關資訊

# 這里先介紹下CONCAT函式：在MySQL中 CONCAT()函式用于將多個字串連接成一個字串，
利用此函式我們可以將原來一步無法得到的sql拼接出來，后面部分陳述句有用到該函式，
# 當拼接字串中出現''時 需使用\轉義符

# 查看所有用戶名：
SELECT DISTINCT
	CONCAT(
		'User: \'',
		user,
		'\'@\'',
		host,
		'\';'
	) AS QUERY
FROM
	mysql.user;

# 查看用戶詳細資訊：
SELECT user,
	host,
	authentication_string,
	password_expired,
	password_lifetime,
	password_last_changed,
	account_locked 
FROM
	mysql.user;

3.KILL資料庫鏈接

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

4.拼接創建資料庫或用戶陳述句

# 拼接創建資料庫陳述句（排除系統庫）：
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'
	);
    
# 拼接創建用戶陳述句（排除系統用戶）：
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'
	);
# 有密碼字串哦 在其他實體執行 可直接創建出與本實體相同密碼的用戶，

5.查看庫或表大小

# 查看整個實體占用空間大小：
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`;
  
# 查看各個庫占用大小：
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;
  
# 查看單個庫占用空間大小：
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';
  
# 查看單個表占用空間大小：
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.查看表碎片及收縮陳述句

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

# 查找某一個庫無主鍵表：
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'
);

# 查找除系統庫外 無主鍵表：
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'
) ;

總結：

希望這些SQL陳述句能對你有所幫助，可以收藏一下，說不定某次就用到了呢！原創不易，感謝大家支持，

標籤：MySQL

上一篇：MySQL基礎SQL命令---增刪改查

下一篇：關于如何修改一張表中所有行與選定欄位的同sql多行陳述句的添加方法