create user '张三';

SET PASSWORD FOR '张三'@'%' = PASSWORD('123456');

grant all on . to 张三@'%';

flush privileges;

show grants for 张三;

SELECT @@GLOBAL.sql_mode; -- 查看

SET @@`GLOBAL.sql_mode` = ''; -- 修改

select * from information_schema.PROCESSLIST;

truncate table 表名；

SELECT * FROM information_schema.TABLES WHERE TABLE_NAME='表名';

SELECT * FROM information_schema.`columns` WHERE table_name='表名';

flush hosts;

set global max_connections = 1000;  -- max_connections可以替换成你要改的属性

show GLOBAL variables like 'max_connection_errors'; -- max_connection_errors可以替换成你要查看的属性

database();

alter table users AUTO_INCREMENT=200;

EXPLAIN

SELECT REPLACE(UUID(), '-', '');

SELECT BB.序号 , IF(BB.序号 = 0, CONCAT('表名称:', BB.表名), BB.字段名称) AS "字段名称" , BB.字段类型, BB.Size, BB.主键, BB.自增, BB.可空 , BB.默认值, BB.字段说明 FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY AA.`表名` ORDER BY AA.`字段名称`) - 1 AS "序号", AA.表名, AA.字段名称 , AA.字段类型, AA.Size, AA.主键, AA.自增, AA.可空 , AA.默认值, AA.字段说明 FROM ( SELECT DISTINCT TABLE_NAME AS "表名", '' AS "字段名称", '' AS "字段类型", '' AS "Size", '' AS "主键" , '' AS "自增", '' AS "可空", '' AS "默认值", '' AS "字段说明" FROM information_schema.COLUMNS UNION ALL SELECT TABLE_NAME AS "表名", COLUMN_NAME AS "字段名称", DATA_TYPE AS "字段类型", CHARACTER_MAXIMUM_LENGTH AS "Size" , IF(COLUMN_KEY = 'PRI', '√', '') AS "主键" , IF(EXTRA = 'auto_increment', '√', '') AS "自增" , IF(IS_NULLABLE = 'NO', '√', '') AS "可空" , COLUMN_DEFAULT AS "默认值", COLUMN_COMMENT AS "字段说明" FROM information_schema.COLUMNS ) AA ) BB

SELECT BB.序号 , IF(BB.序号 = 0 , CONCAT('表名称:', BB.表名), BB.字段名称) AS '字段名称' , BB.字段类型, BB.Size, BB.主键, BB.自增, BB.可空 , BB.默认值, BB.字段说明 FROM ( SELECT @i := IF(@F1 <> AA.表名,0,@i+1) AS '序号', @F1 := AA.表名 AS 表名, AA.字段名称 , AA.字段类型, AA.Size, AA.主键, AA.自增, AA.可空 , AA.默认值, AA.字段说明 FROM ( SELECT DISTINCT TABLE_NAME AS '表名', '' AS '字段名称', '' AS '字段类型', '' AS 'Size', '' AS '主键' , '' AS '自增', '' AS '可空', '' AS '默认值', '' AS '字段说明' FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='库名' UNION ALL SELECT TABLE_NAME AS '表名', COLUMN_NAME AS '字段名称', DATA_TYPE AS '字段类型', CHARACTER_MAXIMUM_LENGTH AS 'Size' , IF(COLUMN_KEY = 'PRI', '√', '') AS '主键' , IF(EXTRA = 'auto_increment', '√', '') AS '自增' , IF(IS_NULLABLE = 'NO', '√', '') AS '可空' , COLUMN_DEFAULT AS '默认值', COLUMN_COMMENT AS '字段说明' FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE()) AS AA,(select @i := -1,@F1 := NULL) sortTable ORDER BY AA.表名 ) BB

SELECT ROW_NUMBER() OVER (ORDER BY TABLE_NAME) AS '序号',TABLE_NAME AS '表名',TABLE_COMMENT AS '注释/说明' FROM information_schema.`TABLES` WHERE TABLE_SCHEMA='库名';

SELECT (@i :=  @i + 1) AS '序号',TABLE_NAME AS '表名',TABLE_COMMENT AS '注释/说明' FROM information_schema.TABLES,(select @i := 0) sortTable WHERE TABLE_SCHEMA='表名';

SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = '存储过程名称' AND ROUTINE_SCHEMA=DATABASE();-- AND ROUTINE_SCHEMA=DATABASE() 限制库

show full  processlist;

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

SELECT * FROM information_schema.INNODB_TRX;

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

 SELECT * FROM information_schema.statistics WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='表名' AND INDEX_NAME='索引名'