常用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 QUERYFROM    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 CreateDatabaseQueryFROM    information_schema.SCHEMATAWHERE    SCHEMA_NAME NOT IN (        'information_schema',        'performance_schema',        'mysql',        'sys'    );# 拼接创建用户语句（排除系统用户）：SELECT    CONCAT(        'create user \'',    user,    '\'@\'',    Host,    '\''    ' IDENTIFIED BY PASSWORD \'',    authentication_string,        '\';'    ) AS CreateUserQueryFROM    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.查找无主键表

# 查找某一个库无主键表：SELECTtable_schema,table_nameFROM    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_nameFROM    information_schema.`TABLES` t1LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS t2 ON t1.table_schema = t2.TABLE_SCHEMAAND t1.table_name = t2.TABLE_NAMEAND t2.CONSTRAINT_NAME IN ('PRIMARY')WHERE    t2.table_name IS NULLAND t1.TABLE_SCHEMA NOT IN (    'information_schema',    'performance_schema',    'mysql',    'sys') ;

总结：

希望这些SQL语句能对你有所帮助，可以收藏一下，说不定某次就用到了呢！原创不易，感谢大家支持。