MySQL数据库—数据库管理常用命令

一、用户管理

关于用户管理的相关语句需要使用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)