探索MySQL中的视图奥秘

1个月前发布 gsjqwyl
16 0 0

文章标题：

探秘MySQL中的视图

文章内容：

目录

一. 视图

1.1 视图的定义

1.2 视图的创建

1.3 视图数据的修改

1.4 视图的删除

1.5 视图的优势

二. 用户管理

2.1 用户的查看

2.2 新用户的创建

2.3 用户密码的更改

2.4 用户的删除

三. 权限管理

3.1 当前用户权限的查看

3.2 权限的赋予

3.3 权限的收回

一. 视图

之前我们学习了联合查询，不过联合查询的语句通常比较复杂，当我们需要多次使用相同的查询语句时，就会显得很麻烦，于是我们引入视图来对复杂的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用户（超级管理员），该用户拥有最高权限能够操纵和管理所有数据库，不过我们有时只希望某个用户操纵和管理当前应用对应的数据库，而不能操纵其他数据库，这时就需要为当前数据库添加用户并指定权限。

![](https://i-blog.csdnimg.cn/direct/babf7ef6b16c49ce89981ba8b417e266.png

© 版权声明
文章版权归作者所有，未经允许请勿转载。
上一篇

没有更多了...

下一篇

没有更多了...

相关文章

没有相关内容!

暂无评论

none
暂无评论...
添加小工具
点此为“正文侧边栏”添加小工具