【MySQL】视图

Ne0inhk

24 Mar 2026 — 17 min read

一. 视图

上期我们学习了联合查询，但是往往联合查询语句是很复杂的，当我们需要多次使用同一个语句进行查询时，就会显得非常麻烦，所以我们引入的视图来对复杂的SQL语句进行封装，以此来简化语句。

1.1 什么是视图

视图是由SELECT语句定义的虚拟表，它的结构和数据来源于底层的基表，视图中存储的其实是查询语句，当用户访问视图时，数据库会执行该查询语句，从基表中提取数据并返回结果。用户可以像操作普通表一样使用视图进行查询、更新、管理。

像上述的SQL语句是一个4张表联合查询的语句，如此复杂的语句就可以使用视图进行封装，对SQL语句进行简化~

1.2 创建视图

创建视图语法：

# 语法 CREATE VIEW view_name [(column_list)] AS select_statement 创建 视图关键字 视图名称 查询语句

测试数据：查询MySQL成绩比Java成绩好的同学

mysql> select distinct *from student s,score s1,score s2,course c1,course c2 where s.id=s1.student_id -> and s.id=s2.student_id and s1.course_id=c1.id and s2.course_id=c2.id -> and c1.name='Java' and c2.name='MySQL' and s1.sco<s2.sco; +----+-----------+--------+------+--------+----------+------+------------+-----------+------+------------+-----------+----+------+----+-------+ | id | name | sno | age | gender | class_id | sco | student_id | course_id | sco | student_id | course_id | id | name | id | name | +----+-----------+--------+------+--------+----------+------+------------+-----------+------+------------+-----------+----+------+----+-------+ | 1 | 唐三藏 | 100001 | 18 | 男 | 1 | 70.5 | 1 | 1 | 98.5 | 1 | 3 | 1 | Java | 3 | MySQL | | 3 | 猪悟能 | 100003 | 18 | 男 | 1 | 33 | 3 | 1 | 68 | 3 | 3 | 1 | Java | 3 | MySQL | +----+-----------+--------+------+--------+----------+------+------------+-----------+------+------------+-----------+----+------+----+-------+ 2 rows in set (0.00 sec)

像以上这样复杂了SQL语句，我们每次都需要打这么多并且还需要思考联合查询的逻辑，是不是相当的麻烦，所以我们使用视图进行包装一下：

-- 创建视图 CREATE VIEW v_Java_or_MySQL AS SELECT DISTINCT s.id, s.name, s1.sco AS java_sco, -- 给 Java 课程成绩取别名 s2.sco AS mysql_sco -- 给 MySQL 课程成绩取别名 FROM student s, score s1, score s2, course c1, course c2 WHERE s.id = s1.student_id AND s.id = s2.student_id AND s1.course_id = c1.id AND s2.course_id = c2.id AND c1.name = 'Java' AND c2.name = 'MySQL' AND s1.sco < s2.sco; -- 查询视图 mysql> select *from v_Java_or_MySQL; +----+-----------+----------+-----------+ | id | name | java_sco | mysql_sco | +----+-----------+----------+-----------+ | 1 | 唐三藏 | 70.5 | 98.5 | | 3 | 猪悟能 | 33 | 68 | +----+-----------+----------+-----------+ 2 rows in set (0.01 sec)

查询学生的姓名和总分（隐藏学号和各科成绩）：

-- 直接使用真实表查询 mysql> select s.name,sum(sc.sco)from student s,score sc where s.id=sc.student_id group by s.name; +-----------+-------------+ | name | sum(sc.sco) | +-----------+-------------+ | 唐三藏 | 469 | | 孙悟空 | 179.5 | | 猪悟能 | 200 | | 沙悟净 | 218 | | 宋江 | 118 | | 武松 | 178 | | 李逹 | 172 | +-----------+-------------+ 7 rows in set (0.00 sec) -- 但是此时还可以使用学号进行查询 mysql> select s.sno,sum(sc.sco)from student s,score sc where s.id=sc.student_id group by s.sno; +--------+-------------+ | sno | sum(sc.sco) | +--------+-------------+ | 100001 | 469 | | 100002 | 179.5 | | 100003 | 200 | | 100004 | 218 | | 200001 | 118 | | 200002 | 178 | | 200003 | 172 | +--------+-------------+ 7 rows in set (0.00 sec) -- 使用视图 mysql> create view v_total as select s.name,sum(sc.sco)from student s,score sc where s.id=sc.student_id group by s.name; Query OK, 0 rows affected (0.01 sec) -- 查询视图 mysql> select name from v_total; +-----------+ | name | +-----------+ | 唐三藏 | | 孙悟空 | | 猪悟能 | | 沙悟净 | | 宋江 | | 武松 | | 李逹 | +-----------+ 7 rows in set (0.00 sec) -- 此时就只能从视图中查询到学生名字，查询不到学生的学号了

那么以上情况如果直接使用真实表进行查询，想要查看什么信息就只需要加上需要查看的字段即可，但是假设现在是一个银行系统，如果能够这样随机查看想要查看的内容，那么就没有办法保证信息的安全性了。

视图还可以与真实表进行表连接查询：

mysql> select *from student,v_total where student.name=v_total.name; +----+-----------+--------+------+--------+----------+-----------+-------------+ | id | name | sno | age | gender | class_id | name | sum(sc.sco) | +----+-----------+--------+------+--------+----------+-----------+-------------+ | 1 | 唐三藏 | 100001 | 18 | 男 | 1 | 唐三藏 | 469 | | 2 | 孙悟空 | 100002 | 18 | 女 | 1 | 孙悟空 | 179.5 | | 3 | 猪悟能 | 100003 | 18 | 男 | 1 | 猪悟能 | 200 | | 4 | 沙悟净 | 100004 | 18 | 男 | 1 | 沙悟净 | 218 | | 5 | 宋江 | 200001 | 18 | 女 | 2 | 宋江 | 118 | | 6 | 武松 | 200002 | 18 | 男 | 2 | 武松 | 178 | | 7 | 李逹 | 200003 | 18 | 男 | 2 | 李逹 | 172 | +----+-----------+--------+------+--------+----------+-----------+-------------+ 7 rows in set (0.00 sec)

1.3 修改数据

对真实表的数据进行修改，会影响视图，是因为视图的本质并没有保存数据，可以理解为保存的是查询语句，当我使用视图时就会调用保存的查询语句返回结果，所以真实表的数据不管怎么变，我每一次使用视图都是一次新的查询。

将孙悟空的Java成绩修改为99分：

-- 修改成绩 mysql> update score set sco=99 where student_id =(select student.id from student where name='孙悟空') -> and course_id= (select course.id from course where name='Java'); Query OK, 2 rows affected (0.01 sec) Rows matched: 2 Changed: 2 Warnings: 0 -- 查看修改结果 mysql> select *from student,score,course where student.id=score.student_id and course.id=score.course_id and student.name='孙悟空' and course.name='Java'; +----+-----------+--------+------+--------+----------+------+------------+-----------+----+------+ | id | name | sno | age | gender | class_id | sco | student_id | course_id | id | name | +----+-----------+--------+------+--------+----------+------+------------+-----------+----+------+ | 2 | 孙悟空 | 100002 | 18 | 女 | 1 | 99 | 2 | 1 | 1 | Java | | 2 | 孙悟空 | 100002 | 18 | 女 | 1 | 99 | 2 | 1 | 1 | Java | +----+-----------+--------+------+--------+----------+------+------------+-----------+----+------+ 2 rows in set (0.00 sec) -- 这里查询出两条数据是因为有重复数据

不仅通过真实表修改数据会影响视图，通过视图修改数据也会影响到基表：

-- 封装该语句 mysql> select student.id,student.name,student.sno,course.name,score.sco from student,score,course where student.id=score.student_id and course.id=score.course_id and student.name='孙悟空' and course.name='Java'; +----+-----------+--------+------+------+ | id | name | sno | name | sco | +----+-----------+--------+------+------+ | 2 | 孙悟空 | 100002 | Java | 99 | | 2 | 孙悟空 | 100002 | Java | 99 | +----+-----------+--------+------+------+ 2 rows in set (0.00 sec) -- 创建视图 mysql> create view v_java_sco as select student.id,student.name as'学生姓名',student.sno,course.name as '课程名称',score.sco from student,score,course where student.id=score.student_id and course.id=score.course_id and student.name='孙悟空' and course.name='Java'; Query OK, 0 rows affected (0.01 sec) -- 查看视图 mysql> select *from v_java_sco; +----+--------------+--------+--------------+------+ | id | 学生姓名 | sno | 课程名称 | sco | +----+--------------+--------+--------------+------+ | 2 | 孙悟空 | 100002 | Java | 99 | | 2 | 孙悟空 | 100002 | Java | 99 | +----+--------------+--------+--------------+------+ 2 rows in set (0.01 sec) -- 通过视图将孙悟空的java成绩修改成60 mysql> update v_java_sco set sco=60; Query OK, 2 rows affected (0.01 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> select *from v_java_sco; +----+--------------+--------+--------------+------+ | id | 学生姓名 | sno | 课程名称 | sco | +----+--------------+--------+--------------+------+ | 2 | 孙悟空 | 100002 | Java | 60 | | 2 | 孙悟空 | 100002 | Java | 60 | +----+--------------+--------+--------------+------+ 2 rows in set (0.00 sec) -- 查看真实表中孙悟空的java成绩 mysql> select student.id,student.name,student.sno,course.name,score.sco from student,score,course where student.id=score.student_id and course.id=score.course_id and student.name='孙悟空' and course.name='Java'; +----+-----------+--------+------+------+ | id | name | sno | name | sco | +----+-----------+--------+------+------+ | 2 | 孙悟空 | 100002 | Java | 60 | | 2 | 孙悟空 | 100002 | Java | 60 | +----+-----------+--------+------+------+ 2 rows in set (0.00 sec) --此时就修改好了

但是不是所有的视图都可以进行修改的：

具有以下条件的视图不可以修改：创建视图时使用聚合函数创建视图时使用distinct创建视图使用group by 以及having子句创建视图使用union 或者union all查询列表使用子查询在from子句中引用不可更新的视图

所以通过视图修改数据的情况是很苛刻的，大部分情况下我们还是直接修改真实表即可

1.4 删除视图

删除视图语法：

drop view view_name;

删除刚才创建的所有视图：

-- 查看创建的视图 mysql> show tables; +-----------------+ | Tables_in_test | +-----------------+ | class | | course | | score | | student | | student1 | | v_java_or_mysql | | v_java_sco | | v_total | +-----------------+ 8 rows in set (0.01 sec) -- 表名前面带v的都是刚才创建的视图 --删除视图 mysql> drop view v_java_or_mysql,v_java_sco,v_total; Query OK, 0 rows affected (0.01 sec) -- 查看 mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | class | | course | | score | | student | | student1 | +----------------+ 5 rows in set (0.00 sec)

那么现在就将刚才创建的视图全部删除啦

1.5 视图的优点

简单性：视图可以将复杂的SQL语句进行封装，变成一个简单的查询语句安全性：视图可以隐藏表中的敏感数据，比如上面举的银行的例子逻辑数据独立性：即使底层表结构发生变化，只需要修改视图定义，不需要修改依赖视图的应用程序，使用到应用程序与数据库的解耦合重命名列：视图允许用户重命名列，增强数据可读性

二. 用户

数据库服务安装完成后会存在一个默认的root用户（超级管理员），该用户拥有最高权限可以操纵和管理所有数据库，但是此时我们只希望某个用户操纵和管理当前应用对应的数据库，而不能操纵和管理其他的数据库，这个时候我们就需要为当前数据库添加一个用户并指定用户的权限。

对于上图中的用户来说，root用户可以操纵所有的数据库，普通用户1和普通用户分别可以操纵DB1和DB3，而只读用户1和只读用户2分别只能访问DB3和DB4

2.1 查看用户

在MySQL中，用户的信息保存在系统数据库中的user表中，通过select语句进行查看：

mysql> use mysql; Database changed mysql> show tables; +------------------------------------------------------+ | Tables_in_mysql | +------------------------------------------------------+ | columns_priv | | component | | db | | default_roles | | engine_cost | | func | | general_log | | global_grants | | gtid_executed | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | ndb_binlog_index | | password_history | | plugin | | procs_priv | | proxies_priv | | replication_asynchronous_connection_failover | | replication_asynchronous_connection_failover_managed | | replication_group_configuration_version | | replication_group_member_actions | | role_edges | | server_cost | | servers | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +------------------------------------------------------+ 38 rows in set (0.01 sec) -- 查看user表中的记录 mysql> select host,user,authentication_string from user; +-----------+------------------+------------------------------------------------------------------------+ | host | user | authentication_string | +-----------+------------------+------------------------------------------------------------------------+ | localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | root | $A$005$:lR RDCz`gy 6xV4l)8dprmrb/V/NB5bxkBfio9M6TlZ495kbMKJN6ThY2ngtA | +-----------+------------------+------------------------------------------------------------------------+ 4 rows in set (0.00 sec)

host：表示谁可以登录

user：表示用户名

authentication_string：表示加密后的用户密码

2.2 创建用户

那么我们试着创建一个用户，创建用户语法：

create user 'user_name'@'host_name' identified by 'auth_string';

'user_name'@'host_name'是一个用户描述的方式

‘user_name’部分就是用来登录MySQL的用户名

‘host_name’部分是可以登录的主机名或者IP，只有指定的机器才能访问当前MySQL，如果不指定host_name相当于'user_name'@'%'表示所有主机都可以连接到数据库，这样可能会导致安全问题，而'user_name'@'host_name'如果写成'user_name@host_name'就相当于'user_name@host_name'@‘%’

‘auth_string’是密码的明文

host_name可以通过子网掩码设置主机范围：

比如192.168.10.10/24

此时的网络号为192.168.10.0，主机IP为192.168.10.1 ~ 192.168.10.254的主机都可以访问数据库

创建用户：

mysql> create user 'sunny'@'localhost' identified by '8888'; Query OK, 0 rows affected (0.02 sec) mysql> select host,user,authentication_string from user; +-----------+------------------+------------------------------------------------------------------------+ | host | user | authentication_string | +-----------+------------------+------------------------------------------------------------------------+ | localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | root | $A$005$:lR RDCz`gy 6xV4l)8dprmrb/V/NB5bxkBfio9M6TlZ495kbMKJN6ThY2ngtA | | localhost | sunny | $A$005$WJbOO%|␦b.]1[\yThHOt7iiYP/L7zpGxlQF.7An0JcX0Z5S3cN8evWhI0 | +-----------+------------------+------------------------------------------------------------------------+ 5 rows in set (0.00 sec) -- 此时就创建出了一个sunny用户，只允许从本机登录，密码为8888

创建一个新用户，允许从192.168.10.1/24网段登录：

-- 创建用户 mysql> create user 'rain'@'192.168.10.1/24' identified by '666'; Query OK, 0 rows affected (0.01 sec) -- 查看 mysql> select host,user,authentication_string from user; +-----------------+------------------+------------------------------------------------------------------------+ | host | user | authentication_string | +-----------------+------------------+------------------------------------------------------------------------+ [email protected]/24 | rain | $A$005$[ !KAcYdfddk6qCr2pR1C1rsL5wg1GUesPDmkxHu6ugS/xB7 | | localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | root | $A$005$:lR RDCz`gy 6xV4l)8dprmrb/V/NB5bxkBfio9M6TlZ495kbMKJN6ThY2ngtA | | localhost | sunny | $A$005$WJbOO%|␦b.]1[\yThHOt7iiYP/L7zpGxlQF.7An0JcX0Z5S3cN8evWhI0 | +-----------------+------------------+------------------------------------------------------------------------+ 6 rows in set (0.00 sec)

创建完成后，我们使用新用户登录试试：

C:\Users\86130>mysql -usunny -p Enter password: **** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 19 Server version: 8.0.39 MySQL Community Server - GPL Copyright (c) 2000, 2024, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | performance_schema | +--------------------+ 2 rows in set (0.00 sec) -- 此时可以看见当前sunny用户是没有权限查询数据库中的表的，后面我们再设置 C:\Users\86130>mysql -urain -p Enter password: *** ERROR 1045 (28000): Access denied for user 'rain'@'localhost' (using password: YES) -- 此时我们无法使用rain用户登录，因为我当前电脑的ipv4地址不在192.168.10.0这个网段 C:\Users\86130>ipconfig Windows IP 配置 无线局域网适配器 本地连接* 1: 媒体状态 . . . . . . . . . . . . : 媒体已断开连接 连接特定的 DNS 后缀 . . . . . . . : 无线局域网适配器 本地连接* 2: 媒体状态 . . . . . . . . . . . . : 媒体已断开连接 连接特定的 DNS 后缀 . . . . . . . : 无线局域网适配器 WLAN: 连接特定的 DNS 后缀 . . . . . . . : 本地链接 IPv6 地址. . . . . . . . : fe80::6e1a:d0a1:d53b:f417%18 IPv4 地址 . . . . . . . . . . . . : 10.4.7.98 子网掩码 . . . . . . . . . . . . : 255.255.128.0 默认网关. . . . . . . . . . . . . : 10.4.0.1

2.3 修改密码

修改密码语法：

-- 为指定用户设置密码 alter user 'user_name'@'host_name' idenified by 'auth_string'; -- 方法2 set password for 'user_name'@'host_name'='anth_string'; -- 为当前用户设置密码 set password ='auth_string';

以root超级管理员身份登录，为'sunny'@'localhost'用户设置密码：

-- 修改密码 mysql> alter user 'sunny'@'localhost' identified by '6'; Query OK, 0 rows affected (0.01 sec) -- 使用sunny用户登录 C:\Users\86130>mysql -usunny -p Enter password: * Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 24 Server version: 8.0.39 MySQL Community Server - GPL Copyright (c) 2000, 2024, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. -- 查看当前登录用户 mysql> select user(); +-----------------+ | user() | +-----------------+ | sunny@localhost | +-----------------+ 1 row in set (0.00 sec)

2.4 删除用户

删除用户语法：

drop user 'user_name'@'host_name';

删除刚才创建的rain用户：

mysql> drop user 'rain'@'192.168.10.1/24'; Query OK, 0 rows affected (0.01 sec) -- 查看 mysql> use mysql; Database changed mysql> select host,user,authentication_string from user; +-----------+------------------+------------------------------------------------------------------------+ | host | user | authentication_string | +-----------+------------------+------------------------------------------------------------------------+ | localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | root | $A$005$:lR RDCz`gy 6xV4l)8dprmrb/V/NB5bxkBfio9M6TlZ495kbMKJN6ThY2ngtA | | localhost | sunny | $A$005$? ~\cbKr␦/Gzjgr.l8OElY0DFIhNwB18s3ec9bWO6n7VKkM1CzhzL/wo5 | +-----------+------------------+------------------------------------------------------------------------+ 5 rows in set (0.00 sec)

三. 权限

用户创建好之后，我们需要给用户赋予一些专属的权限，那么我们现在来看看有哪些权限

其中ALL表示所有的权限，root管理员则拥有这样的权限

对于刚创建的用户是没有任何权限的：比如sunny用户

C:\Users\86130>mysql -usunny -p Enter password: * Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 26 Server version: 8.0.39 MySQL Community Server - GPL Copyright (c) 2000, 2024, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | performance_schema | +--------------------+ 2 rows in set (0.00 sec) -- 此时连当前数据库中有哪些表都查看不了

3.1 查看当前用户权限

查看语法：

show grants for 'user_name'@'host_name'

查看sunny用户拥有的权限：

mysql> show grants for 'sunny'@'localhost'; +-------------------------------------------+ | Grants for sunny@localhost | +-------------------------------------------+ | GRANT USAGE ON *.* TO `sunny`@`localhost` | +-------------------------------------------+ 1 row in set (0.00 sec) -- USAGE表示没有任何权限

3.2 添加权限

添加权限语法：

grant priv_type on ptiv_level to 'user_name'@'host_name';

priv_type：权限类型

priv_level： * | *.* | db_name.* | db_name.tbl_name | tbl_name，*.*表⽰所有数据库下的所有表

为sunny用户授权于查看test数据的权限：

-- 授予权限 mysql> grant select on test.* to 'sunny'@'localhost'; Query OK, 0 rows affected (0.01 sec) -- 查看 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | performance_schema | | test | +--------------------+ 3 rows in set (0.00 sec) -- 查看test库中的表 mysql> use test; Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | class | | course | | score | | student | | student1 | +----------------+ 5 rows in set (0.00 sec) mysql> select *from student; +----+----------+--------+------+--------+----------+ | id | name | sno | age | gender | class_id | +----+----------+--------+------+--------+----------+ | 1 | 唐三藏 | 100001 | 18 | 男 | 1 | | 2 | 孙悟空 | 100002 | 18 | 女 | 1 | | 3 | 猪悟能 | 100003 | 18 | 男 | 1 | | 4 | 沙悟净 | 100004 | 18 | 男 | 1 | | 5 | 宋江 | 200001 | 18 | 女 | 2 | | 6 | 武松 | 200002 | 18 | 男 | 2 | | 7 | 李逹 | 200003 | 18 | 男 | 2 | | 8 | 不想毕业 | 200004 | 18 | 女 | 2 | +----+----------+--------+------+--------+----------+ 8 rows in set (0.00 sec) -- 添加数据失败，因为没有添加的权限 mysql> insert into student values(null,'张三','300001',18,'男',2); ERROR 1142 (42000): INSERT command denied to user 'sunny'@'localhost' for table 'student' -- 查看sunny用户的权限 mysql> show grants for 'sunny'@'localhost'; +-------------------------------------------------+ | Grants for sunny@localhost | +-------------------------------------------------+ | GRANT USAGE ON *.* TO `sunny`@`localhost` | | GRANT SELECT ON `test`.* TO `sunny`@`localhost` | +-------------------------------------------------+ 2 rows in set (0.00 sec) -- 此时可以发现多了一个select权限

为sunny用户授权test数据库所有权限：

mysql> grant ALL on test.* to 'sunny'@'localhost'; Query OK, 0 rows affected (0.01 sec) -- 查看 mysql> show grants for 'sunny'@'localhost'; +---------------------------------------------------------+ | Grants for sunny@localhost | +---------------------------------------------------------+ | GRANT USAGE ON *.* TO `sunny`@`localhost` | | GRANT ALL PRIVILEGES ON `test`.* TO `sunny`@`localhost` | +---------------------------------------------------------+ 2 rows in set (0.00 sec) -- 此时就拥有了所有的权限 -- 插入新的记录 mysql> insert into student values (null, '张三', '300001', 18, '男',2); Query OK, 1 row affected (0.01 sec) -- 此时就添加成功了 -- 删除刚才添加的记录 mysql> delete from student where name='张三'; Query OK, 1 row affected (0.00 sec)

3.3 回收权限

能够为用户授予权限，当前也能够回收权限啦，回收权限语法：

revoke priv_type on priv_level from 'user_name'@'host_name';

回收sunny用户的对于test数据库的权限：

mysql> revoke all on *.* from 'sunny'@'localhost'; Query OK, 0 rows affected (0.00 sec) -- 查看 mysql> show grants for 'sunny'@'localhost'; +-------------------------------------------+ | Grants for sunny@localhost | +-------------------------------------------+ | GRANT USAGE ON *.* TO `sunny`@`localhost` | +-------------------------------------------+ 1 row in set (0.00 sec) -- 此时就没有任何权限了