合 MySQL常用SQL语句
👉 本文共约272个字，系统预计阅读时间或需2分钟。
目录
MySQL表结构
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | -- MySQL表结构 SELECT a.TABLE_NAME "表", a.COLUMN_NAME "列", a.COLUMN_TYPE "类型", a.COLUMN_DEFAULT "默认值", a.IS_NULLABLE "是否为空", a.CHARACTER_SET_NAME "表字符集", a.COLLATION_NAME "校验字符集", CONCAT(a.COLUMN_COMMENT,a.COLUMN_KEY, a.EXTRA) "列备注", b.TABLE_COMMENT "表备注" , b.ENGINE "引擎" FROM information_schema.COLUMNS a,information_schema.TABLES b WHERE a.TABLE_SCHEMA=b.TABLE_SCHEMA AND a.TABLE_SCHEMA='lhrdb' AND a.TABLE_NAME=b.TABLE_NAME; |
MySQL索引大小
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | select iis.database_name, iis.table_name, iis.index_name, round((iis.stat_value@@innodb_page_size)/1024/1024, 2) SizeMB, -- round(((100/(SELECT INDEX_LENGTH FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_NAME = iis.table_name and t.TABLE_SCHEMA = iis.database_name))(stat_value*@@innodb_page_size)), 2) Percentage, s.NON_UNIQUE, s.INDEX_TYPE, GROUP_CONCAT(s.COLUMN_NAME order by SEQ_IN_INDEX) COLUMN_NAME from (select * from mysql.innodb_index_stats WHERE index_name not in ('PRIMARY','GEN_CLUST_INDEX') and stat_name='size' order by (stat_value@@innodb_page_size) desc limit 10 ) iis left join INFORMATION_SCHEMA.STATISTICS s on (iis.database_name=s.TABLE_SCHEMA and iis.table_name=s.TABLE_NAME and iis.index_name=s.INDEX_NAME) GROUP BY iis.database_name,iis.TABLE_NAME,iis.INDEX_NAME,(iis.stat_value@@innodb_page_size),s.NON_UNIQUE,s.INDEX_TYPE order by (stat_value*@@innodb_page_size) desc; |
MySQL索引信息
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | -- MySQL索引信息 SELECT TABLE_SCHEMA, TABLE_NAME, NON_UNIQUE, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, INDEX_TYPE, CONCAT(COMMENT,INDEX_COMMENT) INDEX_COMMENT FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'lhrdb' ORDER BY TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,SEQ_IN_INDEX; -- MySQL索引信息 方便生成alter语句 SELECT a.TABLE_SCHEMA, a.TABLE_NAME, a.INDEX_NAME, MAX(a.NON_UNIQUE) NON_UNIQUE, MAX(a.INDEX_TYPE) INDEX_TYPE, MAX(a.INDEX_COMMENT) INDEX_COMMENT, GROUP_CONCAT(a.COLUMN_NAME order by SEQ_IN_INDEX) COLUMN_NAME FROM (SELECT TABLE_SCHEMA, TABLE_NAME, NON_UNIQUE, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, INDEX_TYPE, CONCAT(COMMENT,INDEX_COMMENT) INDEX_COMMENT FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'lhrdb' ORDER BY TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,SEQ_IN_INDEX) a GROUP BY a.TABLE_SCHEMA,a.TABLE_NAME,a.INDEX_NAME; |
MySQL无主键的表
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | --- MySQL无主键的表 select table_schema, table_name from information_schema.tables where table_type='BASE TABLE' and (table_schema, table_name) not in ( select /*+ subquery(materialization) */ a.TABLE_SCHEMA,a.TABLE_NAME from information_schema.TABLE_CONSTRAINTS a where a.CONSTRAINT_TYPE in ('PRIMARY KEY','UNIQUE') and table_schema not in ('mysql', 'information_schema', 'sys', 'performance_schema') ) AND table_schema not in ('mysql', 'information_schema', 'sys', 'performance_schema') limit 100 ; select table_schema, table_name from information_schema.tables where table_type='BASE TABLE' and (table_schema,table_name) not in (select /*+ subquery(materialization) */ table_schema, table_name from information_schema.columns where column_key in ( 'PRI','UNI') ) AND table_schema not in ('mysql', 'information_schema', 'sys', 'performance_schema') and table_schema not in ('mysql', 'information_schema', 'sys', 'performance_schema') limit 100 ; |
MySQL对象统计
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | ---- MySQL迁移完成后的数据对象 select db 数据库,type 对象类型,cnt 对象数量 from (select '表' type,table_schema db, count(*) cnt from information_schema.TABLES a where table_type='BASE TABLE' group by table_schema union all select '事件' type,event_schema db,count(*) cnt from information_schema.EVENTS b group by event_schema union all select '触发器' type,trigger_schema db,count(*) cnt from information_schema.TRIGGERS c group by trigger_schema union all select '存储过程' type,routine_schema db,count(*) cnt from information_schema.ROUTINES d whereROUTINE_TYPE = 'PROCEDURE' group by db union all select '函数' type,routine_schema db,count(*) cnt from information_schema.ROUTINES d whereROUTINE_TYPE = 'FUNCTION' group by db union all select '视图' type,table_schema db,count(*) cnt from information_schema.VIEWS f group by table_schema ) t where db='lhrdb' order by db,type; drop view vw_ob_lhr; create view vw_ob_lhr as select db ,type ,cnt from (select 'TABLE' type,table_schema db, count(*) cnt from information_schema.TABLES a where table_type='BASE TABLE' group by table_schema union all select 'EVENTS' type,event_schema db,count(*) cnt from information_schema.EVENTS b group by event_schema union all select 'TRIGGERS' type,trigger_schema db,count(*) cnt from information_schema.TRIGGERS c group by trigger_schema union all select 'PROCEDURE' type,routine_schema db,count(*) cnt from information_schema.ROUTINES d where ROUTINE_TYPE = 'PROCEDURE' group by db union all select 'FUNCTION' type,routine_schema db,count(*) cnt from information_schema.ROUTINES d where ROUTINE_TYPE = 'FUNCTION' group by db union all select 'VIEWS' type,table_schema db,count(*) cnt from information_schema.VIEWS f group by table_schema ) t order by db,type; select * from vw_ob_lhr where db='lhrdb'; |
mysql建库
1 2 3 4 | create database lhrdb1 charset utf8mb4; create database lhrdb2 character set utf8mb4; |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
