TG客服:@SSjiejie — 官方频道:@SSwangluo
三生网络 © 2009-2023 超15年出海经验,跨境项目专家
日常工作或学习过程中,会常用到某些SQL语句,又不太容易记忆的。建议大家多多整理记录下这些常用的SQL,这样后续用到会方便很多。我在工作及学习过程中也整理了下个人常用的SQL,现在借云栖社区这个平台分享给大家。可能有些SQL你还不常用,但还是希望有所帮助,说不定将来哪天有需求就能用到。
注:下文分享的SQL适用于MySQL 5.7 版本,低版本可能稍许不同。有些SQL可能执行需要较高权限。都在阿里云RDS数据库中使用过,没问题了。
查看实例参数 例如:
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";
下面列举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( 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 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;
TG客服:@SSjiejie — 官方频道:@SSwangluo
三生网络 © 2009-2023 超15年出海经验,跨境项目专家