Mysql SQL

用户操作

use mysql;

# 查询用户

select host,user,plugin,authentication_string from mysql.user;

# 删除用户
# truncate table user;
drop user 'user'@'%';  # 'user'@'%'，要写对，否则报错，ERROR 1396 (HY000): Operation DROP USER failed


# 创建用户

GRANT ALL PRIVILEGES ON *.* TO 'username'@'hostname' IDENTIFIED BY '123456' WITH GRANT OPTION;
create user 'username'@'hostname' identified by 'password_str';

# ip段
create user 'testuser1'@'10.10.1.%' identified by 'password_str';

grant all privileges on testdb.* to 'testuser'@'%';

# 刷新权限
flush privileges;

建库

# utf8mb4

create database testdb default character set utf8mb4 collate utf8mb4_general_ci;

# utf8

create database testdb character set utf8

系统及配置查询

• 查询配置 show variables like '%slow%'
• 查询事务锁：select * from performance_schema.data_locks
• 版本： select @@version;
• 事务级别 select @@transaction_isolation;
• 自动提交状态 select @@autocommit;
• 引擎状态，如显示死锁信息 show engine innodb status;