文章目录
一、用户管理
关于用户管理的相关语句需要使用root用户
1. 创建用户
创建用户格式如下:
mysql> create user '用户名'@'IP地址' identified by '密码';
例1:允许Taoism用户在本地登录
mysql> create user 'Taoism'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
例2:允许10.0.122.*网段使用Taoism用户登录
mysql> create user 'Taoism'@'10.0.122.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
例3:允许用户在任何地方登录
mysql> create user Taoism identified by '123456';
Query OK, 0 rows affected (0.00 sec)
或者:
mysql> create user 'Taoism'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
2. 查看用户
我们可以在mysql数据库中的user表中查看用户的相关信息
mysql> use mysql;
Database changed
mysql> select user,host from user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| debian-sys-maint | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)
注意:使用use mysql语句,可以切换到mysql数据库
3. 删除用户
删除用户格式如下:
mysql> drop user '用户名'@'IP地址';
例如:
mysql> select user,host from user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| Taoism | % |
| debian-sys-maint | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
mysql> drop user Taoism;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| debian-sys-maint | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)
4. 修改用户密码
修改用户密码格式如下:
mysql> set password for '用户名'@'IP地址' = '新密码';
mysql> set password for '用户名'@'IP地址' = password('新密码');
例如:修改Taoism用户的密码
mysql> set password for Taoism = '147258';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
或者:
mysql> set password for Taoism = password('88888888');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
注意:新设置用户或更改密码后需用flush privileges将数据读取到内存,立即生效,刷新MySQL的系统权限相关表,否则会出现拒绝访问,还有一种方法,就是重新启动mysql服务器,来使新设置生效。
5. 更改用户可登录ip地址
例如:把Taoism用户改成可以远程登录
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> update user set host='%' where user='Taoism';
Query OK, 0 rows affected (0.03 sec)
Rows matched: 1 Changed: 0 Warnings: 0
二、授权管理
关于授权管理的相关语句需要使用root用户
1. 查看用户权限
格式如下:
mysql> show grants for '用户名'@'IP地址';
例如:
mysql> show grants for root@localhost;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
2. 授权
格式如下:
mysql> grant 权限 on 数据库.表 to '用户名'@'IP地址';
例1:只授予Taoism用户对School数据库中的class表的查询权限
mysql> grant select on School.class to Taoism;
Query OK, 0 rows affected (0.01 sec)
例2:授予Taoism用户对School数据库中的所有表的查询权限
mysql> grant select on School.* to Taoism;
Query OK, 0 rows affected (0.00 sec)
例3:添加对Schoolf数据库下所有表,除grant以外的所有权限
mysql> grant all privileges on School.* to Taoism;
Query OK, 0 rows affected (0.00 sec)
例4:赋予用户操作服务器上所有数据库所有表除grant以外所有的权限
mysql> grant all privileges on *.* to Taoism;
Query OK, 0 rows affected (0.00 sec)
3. 取消授权
格式如下:
revoke 权限 on 数据库.表 from '用户名'@'IP地址';
例如:取消授予Taoism用户对School数据库下所有表的所有操作权限
mysql> revoke all privileges on School.* from Taoism;
Query OK, 0 rows affected (0.00 sec)
三、管理mysql常用命令
这里是重要的MySQL命令,经常在MySQL数据库的管理或工作中使用
1. 创建数据库
例:默认字符集分别为utf-8和gbk
mysql> create database college default charset utf8;
Query OK, 1 row affected (0.00 sec)
mysql> create database university default charset gbk;
Query OK, 1 row affected (0.00 sec)
2. 删除数据库
格式:
drop database database_name;
例如:删除university数据库
mysql> drop database university;
Query OK, 0 rows affected (0.01 sec)
3. 查看当前选择的数据库
mysql> select database();
+------------+
| database() |
+------------+
| college |
+------------+
1 row in set (0.01 sec)
4. 查看所有用户信息
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| Taoism | % |
| debian-sys-maint | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
5. 查看前用户
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
6. 用于选择在MySQL工作区指定的数据库
格式:
use database_name;
例如:
mysql> use college;
Database changed
7. 列出了MySQL数据库管理系统中的所有可访问的数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| college |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
6 rows in set (0.00 sec)
8. 显示已经选择数据库中的表
mysql> show tables;
+-------------------+
| Tables_in_college |
+-------------------+
| booking |
| meeting |
| school |
| student |
+-------------------+
4 rows in set (0.00 sec)
9. MySQL数据库断开
mysql> exit
Bye
10. 查看表结构
格式:
dsc tables_name;
例如:
mysql> desc school;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| num | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)