一：视图
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注意：修改视图，也会引起原表的变化，我们不要这么做，视图仅用于查询