1、创建视图
【语法】
create view view_name as 查询语句
【实例】
mysql> create view view_employee select empno,ename from t_employee;
2、创建各种视图
(1)封装实现查询常量语句的视图,即所谓的常量视图
mysql> create view view_test1 as select 3.14;
(2)封装使用聚合函数(SUM、MIN、MAX、COUNT等)查询语句的视图;
mysql> create view v_student as select count(name) from t_student;
(3)封装了实现排序功能(ORDER BY)查询语句的视图
mysql> create view view_student as select name from t_student order by id desc;
(4)封装了实现表内连接查询语句的视图
mysql> create view v_test2
-> as
-> select s.name from t_student as s,t_group as g
-> where s.group_id=g.id and g.id=2;
(5)封装了实现表外连接(left join和right join)查询语句的视图
mysql> create view v_test3
-> as
-> select s.name
-> from t_student as s left join t_group as g on s.group_id=g.id
-> where g.id=2;
(6)封装了实现子查询相关查询语句的视图
mysql> create view v_test5
-> as
-> select s.name
-> from t_student as s where s.group_id in (select id from t_group);
(7)封装了实现记录联合(union和union all)查询语句的视图
mysql> create view v_test6
-> as
-> select id,name from t_student
-> union all
-> select id,name from t_group;
3、查看视图
- show tables语句查看视图
mysql> show tables;
- show tables status查看视图详细信息
mysql> show table status from 数据库名\G
show tables status返回的字段含义
- 设置like参数查看某一个具体表或者视图的详细信息
mysql> show table status from test like "表名|视图名"\G
- show create view语句查看视图定义信息
mysql> show create view 视图名 \G
- describe|desc 语句查看视图设计信息
mysql> describe|desc 视图名;
- 通过系统表查看视图信息
#选择数据库information_schema
mysql> use information_schema;
#查看表views里的数据信息
mysql> select * from views where table_name="字段table_name值"\G
4、删除视图
【语法】
drop view view_name 【,view_name1】……
【实例】
mysql> drop view v_test2,v_test3;
5、修改视图
(1)create or replace view语句修改视图
【语法】
create or replace view view_name as 查询语句
【实例】
mysql> create or replace view v_test4
-> as
-> select job from t_employee;
(2)alter语句修改视图
【语法】
alter view view_name as 查询语句
【实例】
mysql> alter view v_test4
-> as
-> select ename from t_employee;
6、利用视图操作基本表
(1)检索(查询)数据
mysql> select * from 视图名;
(2)利用视图操作基本表数据
- 添加数据操作
mysql> insert into view_employee (empno,ename) values(2,3);
- 删除数据操作
mysql> delete from view_employee where ename='3';
- 更新数据操作
mysql> update view_employee set ename=4 where empno=3;
注意:
- 对视图数据进行添加和删除操作直接影响基本表
- 视图来自多个表时,不允许删除和添加数据