视图的特点:
视图可以来自不同的表,是表的抽象和在逻辑意义上建立的新关系
视图是基本表(实表)产生的表(虚表)
视图的删除和建立不影响基本表
对视图内容的更新(添加、删除和修改)直接影响基本表
当视图来自多个基本表时,不允许添加和删除数据。
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;