欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

MySQL常用命令

程序员文章站 2022-05-29 16:51:07
...

1、登录

mysql -u root -p密码
mysql -u root -p
mysql -hlocalhost -uroot -p

2、清屏

cls

3、操作用户

# 查看现有用户
select host,user,authentication_string from mysql.user;
# 新建用户
# create user "username"@"host" identified by "password";
# host="localhost"为本地登录用户,host="ip"为ip地址登录,host="%",为外网ip登录
create user 'test'@'localhost' identified by '123';
create user 'test'@'192.168.46.120' identified by '123';
create user 'test'@'%' identified by '123';
# 删除用户
drop user 'username'@'host';
# 更改用户名
rename user 'test'@'%' to 'dev'@'%';
# 修改密码
# 1、
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');
# 2、格式:mysqladmin -u用户名 -p旧密码 password 新密码
mysqladmin -uroot -p123456 password 1234abcd
# 3、用update直接编辑user表

4、授权

# grant privileges on databasename.tablename to 'username'@'host' IDENTIFIED BY 'PASSWORD';

# 授予用户通过外网IP对于该数据库的全部权限
grant all privileges on `test`.* to 'test'@'%' ;
# 授予用户在本地服务器对该数据库的全部权限
grant all privileges on `test`.* to 'test'@'localhost';   
# 给予查询权限
grant select on test.* to 'user1'@'localhost';
# 添加插入权限
grant insert on test.* to 'user1'@'localhost';
# 添加删除权限
grant delete on test.* to 'user1'@'localhost';
# 添加更新权限
grant update on test.* to 'user1'@'localhost';
# 刷新权限
flush privileges;
# 查看权限
show grants;
# 查看某个用户的权限
show grants for 'test'@'%';
# 删除权限
revoke delete on test.* from 'test'@'localhost';

 

相关标签: 数据库相关