视图的操作

视图的特点：
视图可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系
视图是基本表（实表）产生的表（虚表）
视图的删除和建立不影响基本表
对视图内容的更新（添加、删除和修改）直接影响基本表
当视图来自多个基本表时，不允许添加和删除数据。

1.创建视图
create view view_name
AS 查询语句

eg：
CREATE VIEW view_selectproduct
AS
SELECT id,name
FROM t_product;

创建完视图，可将视图当作表一样来执行查询操作
SELECT *
FROM view_selectproduct;

2.创建各种视图
封装实现查询常量语句的视图，即常量视图
CREATE VIEW view_test1
AS
SELECT 3.1415926;

封装使用聚合函数
CREATE VIEW view_test2
AS
SELECT COUNT(name)
FROM t_student;

封装实现排序功能查询语句的视图
CREATE VIEW view_test3
AS
SELECT name
FROM t_student
ORDER BY id DESC;

封装实现表内连接查询语句的视图
CREATE VIEW view_test4
AS
SELECT s.name
FROM t_student as s,t_group as g
WHERE s.group_id=g.id AND g.id=2;

封装实现表外连接查询语句的视图
CREATE VIEW view_test5
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;

封装实现子查询相关语句的视图
CREATE VIEW view_test6
AS
SELECT s.name
FROM t_student AS s
WHERE s.group_id IN (SELECT id FROM t_group);

封装实现记录联合查询语句的视图
CREATE VIEW view_test7
AS
SELECT id,name FROM t_student
UNION ALL
SELECT id,name FROM t_group;

2.查看视图
查看视图名
show tables;

查看视图详细信息
SHOW TABLE STATUS 【FROM db_name】【LIKE 'pattern'】

eg:
SHOW TABLE STATUS
FROM view \G

SHOW TABLE STATUS
FROM view
LIKE "view_selectproduct" \G

查看视图定义信息
SHOW CREATE VIEW viewname

查看视图设计信息
DESC viewname;

通过系统查看视图信息
（1）、USE information_schema;
（2）、SELECT * FROM views WHERE table_name='view_selectproduct' \G

3.删除视图
DROP VIEW view_name;

检验视图是否还存在
SELECT * FROM view_name;

4.修改视图
create OR REPLACE view view_name
as 查询语句

eg：
CREATE OR REPLACE VIEW view_selectproduct
AS
SELECT name
FROM t_product;

ALTER语句修改视图
ALTER VIEW viewname
AS 查询语句

eg：
ALTER VIEW view_selectproduct
AS
SELECT name
FROM t_product;