MySQL的DCL语句

一、创建用户

create user '用户名'@'主机名' identified by '密码';
1

1.1 关键字说明

1. 用户名：将创建的用户名
2. 主机名：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
3. 密码：该用户的登录密码，密码可以为空，如果为空则该用户可以不需要密码登录服务器

1.2 具体操作

-- user1用户只能在localhost这个IP登陆mysql服务器
create user 'user1'@'localhost' identified by '123';

-- user2用户可以在任何电脑上登陆mysql服务器
create user 'user2'@'%' identified by '123';
1
2
3
4
5

二、授权用户

用户创建之后，基本没有什么权限，需要给用户授权。

2.1 授权格式

grant 权限1，权限2... on 数据库名.表名  to '用户名'@'主机名'
1

2.2 关键字说明

1. grant是授权关键字
2. 授予用户的权限，如SELECT，INSERT，UPDATE等。如果要授予所有的权限就使用all
3. 数据库名.表名：该用户可以操作哪个数据库的哪些表。如果要授予该用户对所有数据库和表的相应操作权限则可以用*表示，如*.*。
4. ‘用户名’@‘主机名’：给哪个用户授权

2.3 具体操作

• 给user1用户分配对test这个数据库操作的权限

grant create,alter,drop,insert,update,delete,select on test.* to 'user1'@'localhost';
1

• 给user2用户分配对所有数据库操作的权限

grant all on *.* to 'user2'@'%';
1

三、撤销授权

3.1 语法格式

revoke 权限1,权限2,...  on 数据库.表名 from '用户名'@'主机名';
1

3.2 具体操作

• 撤销user1用户对test操作的权限

revoke all on test.* from 'user1'@'localhost';
1

四、查看权限

4.1 语法格式

show grants for '用户名'@'主机名';
1

4.2 具体操作

• 查看user1用户的权限

show grants for 'user1'@'localhost';
1

五、删除用户

5.1 语法格式

drop user '用户名'@'主机名';
1

5.2 具体操作

• 删除user2

drop user 'user2'@'%';
1

六、修改用户密码

6.1 修改管理员密码

6.1.1 语法格式

mysqladmin -uroot -p password 新密码
1

需要在未登录MySQL的情况下操作。

6.1.2 具体操作

mysqladmin -uroot -p password root
输入老密码
1
2

6.2 修改普通用户密码

6.2.1 语法格式

set password for '用户名'@'主机名' = password('新密码');
1

需要在登录MySQL的情况下操作。

6.2.2 具体操作

set password for 'user1'@'localhost' = password('666666');
1

七、常见问题

7.1 编码问题

命令行操作SQL乱码问题

• 修改my.ini配置文件

在路径：mysql的数据存储路径下 找到my.ini文件
修改内容1：
	找到[mysql]命令，大概在63行左右，在其下一行添加 
		default-character-set=utf8
修改内容2:
	找到[mysqld]命令，大概在76行左右，在其下一行添加
		character-set-server=utf8
		collation-server=utf8_general_ci

修改完毕后，重启MySQL57服务
1
2
3
4
5
6
7
8
9
10

• 查看编码命令

show variables like 'character_%';

show variables like 'collation_%';
1
2
3

7.2 忘记root用户密码问题

1. 关闭MYSQL57服务

net stop MYSQL57
1

2. 设置mysql服务不检查权限密码

mysql5.5版本

mysqld --skip-grant-tables
1

mysql5.7版本

-- 修改my.ini文件
-- 在[mysqld]下方添加
skip-grant-tables = true
1
2
3

3. 开启MYSQL57服务

-- 以管理员权限运行cmd
net start MYSQL57
1
2

4. mysql -uroot 无密码登陆服务器
5. 修改root账户的密码为“root”

mysql5.5版本

update user set authentication_string=password("root") where user="root";
1

mysql5.7版本

update mysql.user set authentication=passwor('root') where user='root';
1

6. 刷新：flush privileges;
7. 退出登录的mysql。exit;
8. 修改my.ini文件，删除掉之前添加在[mysqld]下方的那一行
9. 验证无密码登陆是否会失败。mysql -uroot
10. 以新密码登陆。mysql -uroot -proot

7.3 查看字符集和校对规则

SQL的关键字和函数名等不区分大小写，但是对于数据值是否区分大小写，和字符集与校对规则有关。

• ci：大小写不敏感
• cs：大小写敏感
• _bin：二元，即比较是基于字符编码的值，而与language无关

1. 查看所有字符集和其默认的校对规则

show character set;
1

2. 查看GBK和UTF-8字符集的校对规则

show collation like 'gbk%';

show collation like 'utf8%';
1
2
3

utf8_unicode_ci和utf8_general_ci对中、英文来说没有实质的差别。
utf8_general_ci校对速度快，但准确度稍差。
utf8_unicode_ci准确度高，但校对速度稍慢。

3. 查看服务器的字符集和校对规则

show variables like '%_server';
1

4. 查看和修改某个数据库的字符集和校对规则

show variables like '%_database';
1

show create database ceshi_db;
1

5. 修改数据库的字符集和校对规则

alter database 数据库名称 default character set 字符集名称 [collate 校对规则名称]
1

ALTER DATABASE ceshi_db DEFAULT CHARACTER SET utf8 collate utf8_general_ci;
1

注意：

修改了数据库的默认字符集和校对规则后，原来已经创建的表格的字符集和校对规则并不会改变，如果需要，那么需要单独修改。

6. 查看某个表格的字符集和校对规则

6.1 查看字符集

show create table users;
1

6.2 查看校对规则

show table status from bookstore like '%users%';
1

6.3 修改表的默认字符集

alter table 表名称 default character set 字符集名称 [collate 校对规则名称]
1

6.4 把表默认的字符集和所有字符

alter table 表名称 convert to character set 字符集名称 [collate 校对规则名称];
1

学海无涯苦作舟