常用 SQL 语句分享

用户头像
Simon
关注
发布于: 2020 年 08 月 21 日
常用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语句能对你有所帮助,可以收藏一下,说不定某次就用到了呢!原创不易,感谢大家支持。





发布于: 2020 年 08 月 21 日 阅读数: 38
用户头像

Simon

关注

MySQL技术学习者 2018.06.08 加入

公众号『MySQL技术』作者

评论

发布
暂无评论
常用SQL语句分享