常用SQL语句分享

常用SQL语句分享

前言：

日常工作或学习过程中，我们可能会经常用到某些SQL，建议大家多多整理记录下这些常用的SQL，这样后续用到会方便很多。笔者在工作及学习过程中也整理了下个人常用的SQL，现在分享给你!可能有些SQL你还不常用，但还是希望对你有所帮助，说不定某日有需求就可以用到。

注：下文分享的SQL适用于MySQL 5.7 版本，低版本可能稍许不同。有些SQL可能执行需要较高权限。

1.show相关语句

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语句能对你有所帮助，可以收藏一下，说不定某次就用到了呢!

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。