MySQL学习笔记（9）视图

/*1.创建视图
语法格式：
CREATE [ALGORITHM = {UNDEFINED |MERGE | TEMPTABLE}]
VIEW 视图名 [(视图列表)]
AS 查询语句
[WITH [CASCADED| LOCAL] CHECK OPTTION] */



CREATE VIEW student_view1(SNAME,CNAME,SCORE) 
AS SELECT SN,CN,SCORE FROM S
INNER JOIN SC
ON S.SNO=SC.SNO
INNER JOIN C
ON SC.CNO=C.CNO;

SELECT * FROM student_view1;

CREATE VIEW  student_view2(SNUM,SNAME,SAGE) 
AS SELECT SNO,SN,AGE FROM S ;

CREATE VIEW student_view3 AS SELECT S.SN  ,D.SCORE FROM S
JOIN (SELECT SNO ,SCORE FROM SC WHERE SC.CNO='C1' ) AS D
ON D.SNO=S.SNO;

CREATE VIEW student_view4(AVGSCORE) AS SELECT AVG(SCORE) FROM SC 
GROUP BY CNO  
HAVING CNO='C2';

CREATE VIEW student_view5 AS SELECT SNO ,SCORE
FROM SC WHERE CNO='C2' AND SCORE>
(SELECT AVG(SCORE) FROM SC 
GROUP BY CNO  
HAVING CNO='C2');

/*2.查看视图
语法格式：
1) DESCRIBE语句，语法格式：DESCRIBE 视图名称;  或者DESC 视图名称;
2) SHOW TABLE STATUS语句,语法格式: SHOW TABLE STATUS LIKE '视图名'
3) SHOW CREATE VIEW语句，语法格式：SHOW CREATE VIEW '视图名'
*/

DESC student_view1;
SHOW TABLE STATUS LIKE 'student_view1'\G
SHOW CREATE VIEW student_view2 ;

/*2.修改视图
语法格式：
1) CREATE OR REPLACE 语句格式:
CREATE OR REPLACE [ALGORITHM = {UNDEFINED |MERGE | TEMPTABLE}]
VIEW 视图名 [(视图列表)]
AS 查询语句
[WITH [CASCADED| LOCAL] CHECK OPTTION];

2) ALTER语句,语法格式: 
ALTER  [ALGORITHM = {UNDEFINED |MERGE | TEMPTABLE}]
VIEW 视图名 [(视图列表)]
AS 查询语句
[WITH [CASCADED| LOCAL] CHECK OPTTION];
3) SHOW CREATE VIEW语句，语法格式：SHOW CREATE VIEW '视图名'
*/

CREATE OR REPLACE VIEW student_view1(姓名,课程名,成绩) 
AS SELECT SN,CN,SCORE FROM S
INNER JOIN SC
ON S.SNO=SC.SNO
INNER JOIN C
ON SC.CNO=C.CNO;


ALTER VIEW student_view1(SNAME,CNAME,SCORE) 
AS SELECT SN,CN,SCORE FROM S
INNER JOIN SC
ON S.SNO=SC.SNO
INNER JOIN C
ON SC.CNO=C.CNO;


/*4.删除视图
语法格式：DROP VIEW [IF EXISTS]
VIEW_NAME[，VIEW_NAME2]…
*/

DROP VIEW IF EXISTS student_view1;

/*查询视图*/
SELECT SNAME,SCORE FROM student_view1;



/*5.更新视图数据
 尽量不要用视图更新数据
*/
UPDATE student_view1 SET SCORE=75 WHERE SNAME='李立勇' AND CNAME='数据库';
UPDATE student_view2 SET SAGE=35 WHERE SNAME='李立勇';
--视图中包含子查询，并且内层查询的from子句中涉及的表也是导出该视图的基本表，则此视图不能允许更新；
UPDATE student_view3 SET SCORE=35 WHERE SNAME='李立勇';
--视图中包含sum()，count()等聚集函数的；
UPDATE student_view4  SET AVGSCORE=85;
--视图中包含union、union all、distinct、group by、having等关键字的；
UPDATE student_view5 SET SCORE=70 WHERE SNO='S1';

--级联的数据表无法进行插入和删除操作
INSERT INTO student_view1 SET SNAME='士大夫',CNAME='英语',SCORE='70';
INSERT INTO student_view2 SET SNUM='S7',SNAME='王含',SAGE=22;
DELETE FROM student_view1;
DELETE FROM student_view2 WHERE SNUM='S1';