常用SQL语句分享

迈不过友情╰ 2023-06-08 12:56 23阅读 0赞

**前言：**

日常工作或学习过程中，我们可能会经常用到某些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语句能对你有所帮助，可以收藏一下，说不定某次就用到了呢！原创不易，感谢大家支持。

![公众号.jpg][.jpg]

[.jpg]: /images/20230531/d7539026cb1441ebb170fa9bcd2222b7.png