创建用户(用户名区分大小写)
方法一(命令):
语法:
CREATE USER '用户名'@'主机ip地址' IDENTIFIED BY '密码';
实例: 添加dog用户, 密码123456
CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';
方法二(改表, 需要刷新权限):
语法:
insert into mysql.user(host,user,password) values("主机ip地址","用户名",password("密码"));
flush privileges;
实例: 添加cat用户, 密码123456
insert into mysql.user(host,user,password) values("localhost","cat",password("123456"));
flush privileges;
查看用户
SELECT HOST, USER, PASSWORD FROM mysql.USER;
image.png
测试一下登录
image.png
删除用户
方式一(改表, 需要刷新权限):
Delete FROM mysql.user Where User='用户名' and Host='主机ip地址';
flush privileges;
删除用户cat
Delete FROM mysql.user Where User='cat' and Host='localhost';
flush privileges;
方式二(命令):
DROP USER '用户名'@'主机ip地址';
删除用户dog
DROP USER 'dog'@'localhost';
修改用户名
创建一个用户叫Trident(海皇戟)
insert into mysql.user(host,user,password) values("localhost","Trident",password("123456"));
flush privileges;
方式一(改表, 需要刷新权限):
update mysql.user set user = '新名字' where user = '原来的名字';
flush privileges;
改名Aegis(宙斯盾)
update mysql.user set user = 'Aegis' where user = 'Trident';
flush privileges;
方式二(命令):
rename user '原来的用户名'@'主机ip地址' to '新名字'@'主机ip地址';
再改回Trident(海皇戟)
rename user 'Aegis'@'localhost' to 'Trident'@'localhost';
修改密码(知道密码)
方式一(命令):
set password for '用户名'@'主机ip地址' = password('新密码');
把''海皇戟''用户的密码改成"root"
set password for 'Trident'@'localhost' = password('root');
方式二(改表, 需要刷新权限):
UPDATE `mysql`.`user` SET `Password` = password('新密码') where user = '用户名' and host = 'localhost';
FLUSH PRIVILEGES;
把'海皇戟'用户的密码再次改成"123456"
UPDATE `mysql`.`user` SET `Password` = password('123456') where user = 'Trident' and host = 'localhost';
FLUSH PRIVILEGES;
