MySQL之视图

一：视图

1. 什么是视图？

本质上是一个虚拟的表。即看的见，但是实际不存在。

2. 为什么需要虚拟表，使用场景是什么？

​ 场景1：我们希望某些查询语句只能查看到某个表中的一部分数据，就可以使用视图

​ 场景2：简化sql语句的编写

3. 使用方法

创建的语法：

# 语法
create [or replace]  view view_name as 查询语句
or replace 如果视图已经存在了，就替换里面的查询语句


# 使用：
测试数据
create table salarys(
 	id int primary key auto_increment,
 	name char(10),
 	money float
) charset utf8;
insert into salarys values(null,"张三丰",500000),(null,"张无忌",40000);


# 第一种使用方式： 只能查看一部分数据（隔离数据）
mysql> create view zwj_view as select money from salarys where name="张无忌";
Query OK, 0 rows affected (0.29 sec)

mysql> show tables;
+-------------------+
| Tables_in_day41_1 |
+-------------------+
| salarys           |
| stu_class_view    |
| stu_info          |
| student           |
| zwj_view          |
+-------------------+
5 rows in set (0.00 sec)


mysql> select * from zwj_view;
+-------+
| money |
+-------+
| 40000 |
+-------+
1 row in set (0.00 sec)


mysql> update salarys set money = 100  where name = "张无忌";
Query OK, 1 row affected (0.29 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from zwj_view;
+-------+
| money |
+-------+
|   100 |
+-------+
1 row in set (0.00 sec)


# 总结：当我们在查询zwj_view视图的时候，就去执行“select * from salarys where name = "张无忌";”这个sql。如果salarys表中的张无忌的数据改变了。那么zwj的视图的结果也会发生改变。因为视图的sql语句是根据salarys表中的数据来查询的


# 第二种使用方式：简化sql

测试数据
create table student(
  s_id int(3),
  name varchar(20),
  math float,
  chinese float 
);
insert into student values(1,'tom',80,70),(2,'jack',80,80),(3,'rose',60,75);

create table stu_info(
  s_id int(3),
  class varchar(50),
  addr varchar(100)
);
insert into stu_info values(1,'二班','安徽'),(2,'二班','湖南'),(3,'三班','黑龙江');


# 查询班级和学员的对应关系
select student.s_id,student.name,stu_info.class from student join stu_info on student.s_id = stu_info.s_id; 

# 然后将对应的sql，制作成一个视图
create view  stu_class_view  as  select student.s_id,student.name,stu_info.class from student join stu_info on student.s_id = stu_info.s_id;


# 之后就使用视图，做对应的查询
mysql> create view  stu_class_view  as  select student.s_id,student.name,stu_info.class from student join stu_info on student.s_id = stu_info.s_id;
Query OK, 0 rows affected (0.29 sec)

mysql> show tables;
+-------------------+
| Tables_in_day41_1 |
+-------------------+
| salarys           |
| stu_class_view    |
| stu_info          |
| student           |
| zwj               |
+-------------------+
5 rows in set (0.00 sec)

mysql> select * from stu_class_view;
+------+------+--------+
| s_id | name | class  |
+------+------+--------+
|    1 | tom  | 二班   |
|    2 | jack | 二班   |
|    3 | rose | 三班   |
+------+------+--------+
3 rows in set (0.00 sec)

修改表

# 语法
alter view view_name as sql语句


mysql> select * from zwj;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  2 | 张无忌    |   100 |
+----+-----------+-------+
1 row in set (0.00 sec)

# 对应的zwj的视图，我们修改为查看“张三丰”的视图

mysql> alter view zwj as select * from salarys where id=1;
Query OK, 0 rows affected (0.29 sec)

mysql> select * from zwj;
+----+-----------+--------+
| id | name      | money  |
+----+-----------+--------+
|  1 | 张三丰    | 500000 |
+----+-----------+--------+

删除

# 语法
drop view view_name;


mysql> drop view zwj;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+-------------------+
| Tables_in_day41_1 |
+-------------------+
| salarys           |
| stu_class_view    |
| stu_info          |
| student           |
+-------------------+
4 rows in set (0.00 sec)

查看

# 语法
desc view_name;

show create view view_name

注意：修改视图，也会引起原表的变化，我们不要这么做，视图仅用于查询