-- -h参数 hostname为主机，hostIP为主机IP。
-- -P参数 MySQL服务的默认端口是3306，
-- -u参数 后面接用户名，username为用户名。
-- -p参数 会提示输入密码。DatabaseName参数 指明登录到哪一个数据库中。如果没有该参数，就会直接登录到MySQL数据库中，然后可以使用USE命令来选择数据库。
-- -e参数 后面可以直接加SQL语句。登录MySQL服务器以后即可执行这个SQL语句，然后退出MySQL服务器。
-- 直接选择 test数据库执行命令
mysql -uroot -proot test -e "select * from book";
创建用户
-- CREATE USER 用户名 [IDENTIFIED BY '密码'][,用户名 [IDENTIFIED BY '密码']];
CREATE USER zhang3 IDENTIFIED BY '123123'; # 默认host是 %
CREATE USER 'kangshifu'@'localhost' IDENTIFIED BY '123456';
-- 查询用户
select user,host from user;
修改用户
update user set user='zhangsan1' where user='zhangsan';
FLUSH PRIVILEGES;
删除用户
drop user lisi@'localhost';
修改密码
alter user 'zhangsan1' identified by '1234';
权限管理
-- 查看所有权限
show privileges;
权限
上下文
描述
Alter
Tables
改变表格
Alter routine
Functions,Procedures
更改或删除存储的功能/程序
Create
Databases,Tables,Indexes
创建新的数据库和表
Create routine
Databases
To use CREATE FUNCTION/PROCEDURE
Create temporary tables
Databases
To use CREATE TEMPORARY TABLE
Create view
Tables
创建视图
Create user
Server Admin
创建用户
Delete
Tables
删除行
Drop
Databases,Tables
To drop databases, tables, and views
Event
Server Admin
创建、更改、删除和执行事件
Execute
Functions,Procedures
执行存储的例程
File
File access on server
在服务器上读取和写入文件
Grant option
Databases,Tables,Functions,Procedures
To give to other users those privileges you possess
Index
Tables
To create or drop indexes
Insert
Tables
To insert data into tables
Lock tables
Databases
To use LOCK TABLES (together with SELECT privilege)
Process
Server Admin
To view the plain text of currently executing queries
Proxy
Server Admin
To make proxy user possible
References
Databases,Tables
To have references on tables
Reload
Server Admin
To reload or refresh tables, logs and privileges
Replication client
Server Admin
To ask where the slave or master servers are
Replication slave
Server Admin
To read binary log events from the master
Select
Tables
To retrieve rows from table
Show databases
Server Admin
To see all databases with SHOW DATABASES
Show view
Tables
To see views with SHOW CREATE VIEW
Shutdown
Server Admin
To shut down the server
Super
Server Admin
To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.
Trigger
Tables
To use triggers
Create tablespace
Server Admin
To create/alter/drop tablespaces
Update
Tables
To update existing rows
Usage
Server Admin
No privileges - allow connect only
-- 授予权限
GRANT 权限1,权限2,…权限n ON 数据库名称.表名称 TO 用户名@用户地址 [IDENTIFIED BY ‘密码口令’];
GRANT SELECT,INSERT,DELETE,UPDATE ON db1.* TO li4@localhost ;
-- 查看当前用户权限
SHOW GRANTS;
-- 查看某个用户权限g
SHOW GRANTS FOR 'user'@'主机地址' ;
show grants for zhangsan1@'%';
-- 收回权限
#收回全库全表的所有权限
REVOKE ALL PRIVILEGES ON *.* FROM joe@'%';
#收回mysql库下的所有表的插删改查权限
REVOKE SELECT,INSERT,UPDATE,DELETE ON mysql.* FROM joe@localhost;