【MySQL从入门到精通】常用SQL语句分享

# 查看实例参数 例如：
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'@'%';

# 这里先介绍下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;

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

# 拼接创建数据库语句（排除系统库）：
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'
  );
# 有密码字符串哦 在其他实例执行 可直接创建出与本实例相同密码的用户。

# 查看整个实例占用空间大小：
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';

# 查看某个库下所有表的碎片情况：
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;

# 查找某一个库无主键表：
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'
) ;