创建用户：
create user '张三';
设置密码/修改也可以用：
SET PASSWORD FOR '张三'@'%' = PASSWORD('123456');
所有表权限
grant all on . to 张三@'%';
生效设置
flush privileges;
查看对应人的权限
show grants for 张三;
查看Global=>sql_Mode配置
SELECT @@GLOBAL.sql_mode; -- 查看
SET @@`GLOBAL.sql_mode` = ''; -- 修改
查看进程-方法一
select * from information_schema.PROCESSLIST;
重置表ID，从1开始
truncate table 表名；
查询表结构
SELECT * FROM information_schema.TABLES WHERE TABLE_NAME='表名';
查询列
SELECT * FROM information_schema.`columns` WHERE table_name='表名';
清楚host缓存
flush hosts;
修改数据库配置
set global max_connections = 1000; -- max_connections可以替换成你要改的属性
查看数据库配置
show GLOBAL variables like 'max_connection_errors'; -- max_connection_errors可以替换成你要查看的属性
获取当前数据库
database();
重置表ID
alter table users AUTO_INCREMENT=200;
执行计划
EXPLAIN
uuid去掉 -
SELECT REPLACE(UUID(), '-', '');
查看库下的表和字段 8.0版本
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
查看库下的表和字段 8.0以下的版本 😄 库名替换成你要查询的库
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='库名') AS AA,(select @i := -1,@F1 := NULL) sortTable ORDER BY AA.表名 ) BB
查看库下的所有表信息 8.0版本 😄库名替换成要查看的数据库名称
SELECT ROW_NUMBER() OVER (ORDER BY TABLE_NAME) AS '序号',TABLE_NAME AS '表名',TABLE_COMMENT AS '注释/说明' FROM information_schema.`TABLES` WHERE TABLE_SCHEMA='库名';
查看库下的所有表信息 8.0以下的版本 😄库名替换成要查看的数据库名称
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='索引名'
评论区