MySQL基础系列之 DCL数据控制语句大全

1. CREATE USER创建用户
例：创建一个用户 名为jeffrey 密码mypass 主机localhost
CREATE USER ‘jeffrey’ @‘localhost’ IDENTIFIED BY ‘mypass’;
用户表查看刚创建的用户
SELECT * FROM mysql.user WHERE User =‘jeffrey’;
SELECT Password(‘mypass’);

2. GRANT给用户授权，是创建新用户并添加访问权限的首选方法
CREATE [privileges] ON db.tb_name TO 用户名 @host
[IDENTIFIED BY ‘password’][,uesr[IDENTIFIED BY ‘password’]]
[WITH GRANT OPTION];
privileges:用户被授予的权限UPDATE/INSERT/DELETE等
db.tb_name：数据表
IDENTIFIED BY：密码
WITH GRANT OPTION:可选参数

例：创建一个新用户testUser 密码testpwd 并授予用户对所有数据表的
SELECT和UPDATE权限
授权语句：GRANT SELECT,UPDATE ON . TO ‘testUser’@localhost
IDENTIFIED BY ‘testpwd’;
查询结果
SELECT Host,User,Select_priv,Update_priv
FROM mysql.user WHERE User =‘testUser’;

例：使用grant语句创建一个新的用户grantUser 密码grantPwd 用户grantUser对所有的数据
有查询 插入权限并授予GRANT权限，拥有GRANT权限用户可以创建新用户
GRANT SELECT,UPDATE ON . TO ‘grantUser’@‘localhost’
IDENTIFIED BY ‘grantPwd’
WITH GRANT OPTION;

3. 直接插入INSERT

从上面添加用户方法可以看出创建用户都是在mysql.user表中添加记录
即可以直接插入用户信息
INSERT INTO mysql.user(Host,User,Password,[Priv_list])
VALUES(‘host’,‘username’,PASSWORD(‘password’),privilegeList);

Priv_list:权限集合INSERT UPDATE DELETE SELECT…
注意:ssl_type,ssl_cipher,x509_issuer,x509_subject无默认值 插入时得赋值
INSERT INTO mysql.user(Host,User,Password,Select_priv,Update_priv,Delete_priv,
ssl_type,ssl_cipher,x509_issuer,x509_subject)
VALUES(‘localhost’,‘nameTest’,PASSWORD(‘123456’),‘y’,‘y’,‘y’,’’,’’,’’,’’);
刷新授权
FLUSH PRIVILEGES;

4. 删除用户
（1）DROP USER ‘username’@localhost;
（2）DELETE FROM mysql.user WHERE Host=‘hostname’ AND User =‘username’;

5. root用户修改密码
（1）mysqladmin -u username -h localhost -p password"newpassword";
（2）UPDATE mysql.user SET Password = PASSWORD(‘123456’)
WHERE User=‘root’ AND Host=‘localhost’;

6. 修改其他用户的密码
（1）SET Password FOR ‘username’@‘host’ = PASSWORD(‘newpassword’);
（2）UPDATE mysql.user SET Password = PASSWORD(‘newpassword’)
WHERE User = ‘username’;
使用GRANT修改普通用户密码（最好）
GRANT USAGE ON . TO ‘username’@‘host’ IDENTIFIED BY ‘newpassword’;

7. 普通用户修改密码
普通用户登录后可以通过SET语句设置自己的密码
SET Password = PASSWORD(‘newpassword’);

8. root用户密码丢失解决办法
通过特殊方法登录mysql服务器 然后在root用户下设置新的密码
1.使用 --skip-grant-tables选项启动Mysql服务器
mysqld --skip-grant-tables
mysqld-nt --skip-grant-tables

9. 收回权限
REVOKE ALL PRIVILEGES,GRANT OPTION
FROM ‘userName’@‘host’;

10. 收回指定权限
REVOKE priv_type[INSERT,UPDATE…]
ON tb_name,…(*.*所有数据库的表) FROM ‘username’@‘host’;
例：REVOKE SELECT ON . FROM ‘grantUser’@‘localhost’;

11. 查看权限
SHOW GRANTS FOR ‘username’@‘host’;
SELECT priv_list FROM mysql.user WHERE user='username’AND host=‘hostname’;

12. 开启事物并提交
start transaction;
delete from tb_name where id=1;
delete from tb_name2 where id=2;
commit;

13. 开启回滚点
start transaction;
delete from tb_name where id=3;
savepoint test;
delete from tb_name2 where id=4;
rollback to savepoint test;
conmmit;

14. 设置隔离级别(不区分大小写)
用户可以用SET TRANSACTION语句改变单个会话或者所有新进连接的隔离级别。它的语法如下：
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}