常用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.拼接创建数据库或用户语句

​
# 下面列举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'; 

​

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 
 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; 

​

总结：

希望这些SQL语句能对你有所帮助，可以收藏一下，说不定某次就用到了呢!