专栏首页MySQL技术常用SQL语句分享

常用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'
) ;

本文分享自微信公众号 - MySQL技术(MySQL_SRE)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-10-18

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • SpringJDBC

    除了Spring自带的Jar包,还要下载几个Jar包,这里我吐槽下CSDN,下个Jar包都要积分,真是想钱想疯了,其实可以Google得到

    用户3112896
  • 经验:Library Cache Lock之异常分析-云和恩墨技术通讯精选

    为了及时共享行业案例,通知共性问题,达成共享和提前预防,我们整理和编辑了《云和恩墨技术通讯》,通过对过去一段时间的知识回顾,故障归纳,以期提供有价值的信息供大家...

    数据和云
  • 1.3万亿条数据查询如何做到毫秒级响应?

    TiDB 是一个开源的 MySQL 兼容的 NewSQL 混合事务/分析处理( HTAP)数据库,本文深入探讨TiDB如何在大量的数据上保持毫秒级的查询响应时间...

    数据和云
  • Redis持久化机制

    上一篇主要针对Redis的内存淘汰机制以及Redis容易引发的三大问题:缓存击穿、缓存穿透以及缓存雪崩进行了详细的讲解以及提供了业界常用的解决方案。本篇主要讲...

    逆月翎
  • 数据太多、太乱、太杂?你需要这样一套数据治理流程

    最近,明略科技与合肥工业大学的研究者在中文核心期刊《软件学报》上发表了一篇关于数据治理的论文。它介绍了数据治理的概念,并对数据清洗、交换和集成等进行具体分析,从...

    机器之心
  • Redis主从复制

    上一篇讲到了Redis的持久化机制,有RDB快照持久化以及AOF日志持久化。Redis的持久化机制保证了Redis即使服务重启,也可以将硬盘中已经持久化的数据进...

    逆月翎
  • Redis 21问,你接得住不?

    Redis本质上是一个Key-Value类型的内存数据库,很像memcached,整个数据库统统加载在内存当中进行操作,定期通过异步操作把数据库数据flush到...

    Java技术栈
  • [转]ASP.NET 缓存:方法和最佳实践

      原文链接:http://msdn.microsoft.com/zh-cn/library/aa478965.aspx

    thz
  • Cassandra原理 | Apache Cassandra简介

    Apache Cassandra 是一个开源的、分布式、无中心、弹性可扩展、高可用、容错、一致性可调、面向行的数据库,它基于 Amazon Dynamo 的分布...

    王知无
  • 盘点那些年被甲骨文前CEO埃里森炮轰过的厂商

    导读:甲骨文公司创始人前CEO拉里·埃里森(Larry Ellison)是出了名的“大嘴”,经常直接在公开场合炮轰竞争对手,甚至是直接鄙视的口吻,让我们来回顾下...

    数据和云

扫码关注云+社区

领取腾讯云代金券