常用SQL语句分享

本文涉及的产品
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 日常工作或学习过程中,我们可能会经常用到某些SQL,建议大家多多整理记录下这些常用的SQL,这样后续用到会方便很多。笔者在工作及学习过程中也整理了下个人常用的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'
) ;


总结:


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

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
SQL XML Oracle
Mybatis动态SQL语句查询,实现一个参数 可查询多个字段。
Mybatis动态SQL语句查询,实现一个参数 可查询多个字段。
372 0
Mybatis动态SQL语句查询,实现一个参数 可查询多个字段。
|
SQL 存储 缓存
一文搞懂MySQL中一条SQL语句是如何执行的
一文搞懂MySQL中一条SQL语句是如何执行的
|
SQL 程序员 开发工具
【Sql Server】基础之分组查询重复出现多条记录的SQL语句,以及group by和having、min函数运用
基础之分组查询重复出现多条记录的SQL语句,以及group by和having、min函数运用
433 0
【Sql Server】基础之分组查询重复出现多条记录的SQL语句,以及group by和having、min函数运用
SQL 查询表中每门课程成绩最好的前n名学生 优于group by语句的方法
SQL 查询表中每门课程成绩最好的前n名学生 优于group by语句的方法
SQL 查询表中每门课程成绩最好的前n名学生 优于group by语句的方法
|
SQL 关系型数据库 MySQL
SQL语句优化方法
SQL语句优化方法
136 0
|
SQL 数据库 数据安全/隐私保护
使用SQL语句 查询电话号码 加密显示
使用SQL语句 查询电话号码 加密显示
181 0
使用SQL语句 查询电话号码 加密显示
|
SQL 存储 安全
MYSQL数据库初窥门径, SQL语句地熟练使用, 图形化界面提高效率
MYSQL数据库初窥门径, SQL语句地熟练使用, 图形化界面提高效率
MYSQL数据库初窥门径, SQL语句地熟练使用, 图形化界面提高效率
|
SQL 存储 关系型数据库
数据库语句执行过程理解,存储引擎的学习,字符集的理解,加各种sql数据类型理解介绍加之SQL各种数据查询分析和题目专练(2)
数据库语句执行过程理解,存储引擎的学习,字符集的理解,加各种sql数据类型理解介绍加之SQL各种数据查询分析和题目专练(2)
数据库语句执行过程理解,存储引擎的学习,字符集的理解,加各种sql数据类型理解介绍加之SQL各种数据查询分析和题目专练(2)
|
存储 SQL 缓存
数据库语句执行过程理解,存储引擎的学习,字符集的理解,加各种sql数据类型理解介绍加之SQL各种数据查询分析和题目专练(1)
数据库语句执行过程理解,存储引擎的学习,字符集的理解,加各种sql数据类型理解介绍加之SQL各种数据查询分析和题目专练(1)
数据库语句执行过程理解,存储引擎的学习,字符集的理解,加各种sql数据类型理解介绍加之SQL各种数据查询分析和题目专练(1)
|
SQL 关系型数据库 MySQL
SQL常用语句
SQL常用语句
110 0
SQL常用语句