【竺】数据库笔记10——视图3实操

多表视图：

创建视图：

CREATE VIEW grade AS SELECT c.coursename,sc.studentNo,s.`name`,sc.score FROM courses as c JOIN

scores as sc ON c.courseNo = sc.courseNo join students as s ON sc.studentNo = s.studentNo;

查询视图：

SELECT * from grade;

删除视图：

drop VIEW IF EXISTS grade;

更新视图：

UPDATE grade SET score=100 WHERE name = "王昭君";

删除多表视图数据：

DELETE from grade WHERE studentNo = 6;//多表视图删除数据，失败

插入多表视图数据：

INSERT INTO grade VALUES ("java",7,"李白",90);//多表视图插入数据，失败

单表视图：

CREATE VIEW stu1 AS SELECT studentNo,name,age,class FROM students;

DROP view IF EXISTS stu1;

SELECT * from stu1;

INSERT INTO stu1 VALUES ('13',"刘备",20,"2班");//单表视图插入数据成功，前提，未插入的选项可以为空

DELETE FROM stu1 WHERE name = '刘备';//单表视图删除数据成功