一、视图的特点:
1- 视图的列可以来自不同的表,是表的抽象和在逻辑意义上建立的新关系;
2- 视图是由基本表(实表)产生的表(虚表);
3- 视图的建立和删除不影响基本表;
4- 对视图内容的更新(添加、删除和修改)直接影响基本表;
5- 当视图来自多个基本表时,不允许添加和删除数据;
二、创建视图
CREATE TABLE t_product (
fid INT,
fname VARCHAR(32),
fprice DOUBLE
);
INSERT INTO t_product VALUES (1,'apple',6.5),(2,'banana',4.5),(3,'orange',1.5),(4,'pear',2.5);
SELECT * FROM t_product;
# 创建视图
CREATE VIEW view_selectproduct AS SELECT fid,fname FROM t_product;
SELECT * FROM view_selectproduct;
DROP VIEW view_selectproduct;
# 创建各种视图
CREATE TABLE t_student(
sid INT,
sname VARCHAR(32),
ssex VARCHAR(1),
sgroup_id INT
);
INSERT INTO t_student VALUES
(1,'Tom1','M',1),
(2,'Tom2','W',2),
(3,'Tom3','M',3),
(4,'Tom4','W',4),
(5,'Tom5','M',1),
(6,'Tom6','W',2),
(7,'Tom7','M',3),
(8,'Tom8','W',4),
(9,'Tom9','M',1),
(10,'Tom10','W',2);
CREATE TABLE t_group(
gid INT,
gname VARCHAR(32)
);
INSERT INTO t_group VALUES
(1,'group1'),
(2,'group2'),
(3,'group3'),
(4,'group4'),
(5,'group5');
## 创建各种视图
#1- 常量视图
CREATE VIEW view_test1 AS SELECT 3.1415926;
SELECT * FROM view_test1;
#2- 聚合函数(SUM,MIN,MAX,COUNT)视图
CREATE VIEW view_test2 AS SELECT COUNT(sname) FROM t_student;
SELECT * FROM view_test2;
#3- 排序 order by 视图
CREATE VIEW view_test3 AS SELECT sname FROM t_student ORDER BY sid DESC;
SELECT * FROM view_test3;
#4- 内连查询视图
CREATE VIEW view_test4 AS SELECT s.sname FROM t_student s, t_group g WHERE s.sgroup_id = g.gid AND g.gid = 2;
SELECT * FROM view_test4;
#5- 外连接(LEFT JOIN RIGHT JOIN) 视图
CREATE VIEW view_test5 AS
SELECT s.sname FROM t_student s LEFT JOIN t_group g ON s.sgroup_id = g.gid WHERE g.gid = 2;
SELECT * FROM view_test5;
#6- 子查询视图
CREATE VIEW view_test6 AS
SELECT g.gname FROM t_group g WHERE g.gid IN (SELECT DISTINCT s.sgroup_id FROM t_student s);
SELECT * FROM view_test6;
三、查看视图
# 查看视图名
SHOW TABLES;
# 查看视图详细信息
SHOW TABLE STATUS;
#查看视图定义信息
SHOW CREATE VIEW view_test3;
# 查看视图设计信息
DESC VIEW_TEST3;
四、修改视图
## 修改视图
#1- create or replace view 修改视图
CREATE OR REPLACE VIEW view_selectproduct AS SELECT fname FROM t_product;
SELECT * FROM view_selectproduct;
#2- alter 修改视图
ALTER VIEW view_selectproduct AS SELECT fprice FROM t_product;
SELECT * FROM view_selectproduct;