视图基本操作

1[简答题] 创建查询信息系的学生的视图v_part_students，然后查询视图。

CREATE VIEW v_part_students AS
SELECT s.stdid, s.stdname, s.dob, s.gender, s.classcode
FROM students s
JOIN classes c ON s.classcode = c.classcode
JOIN departments d ON c.dptcode = d.dptcode
WHERE d.dptname = '信息工程系';

SELECT * FROM v_part_students;

2[简答题]利用视图v_part_students，插入一名信息系的学生，然后查询视图。

INSERT INTO classes (classcode, classname, dptcode) VALUES ('IN-21SE1', '21信息安全技术1班', 'INDE');
INSERT INTO students (stdid, stdname, dob, gender, classcode) VALUES (11, '张三', '2002-08-01', '男', 'IN-21SE1');

SELECT * FROM v_part_students;

3[简答题]修改视图v_part_students，查询所有女生的信息，并要求视图数据操作时要检查。

CREATE OR REPLACE VIEW v_part_students AS
SELECT s.stdid, s.stdname, s.dob, s.gender, s.classcode
FROM students s
JOIN classes c ON s.classcode = c.classcode
JOIN departments d ON c.dptcode = d.dptcode
WHERE d.dptname = '信息工程系' AND s.gender = '女';

SELECT * FROM v_part_students;

4[简答题]利用视图v_part_students，删除陈红同学的信息。

DELETE FROM students WHERE stname = "陈红";

SELECT * FROM v_part_students;

5[简答题]删除视图v_part_students，然后查看视图列表。

DROP VIEW IF EXISTS v_part_students;

SHOW FULL TABLES WHERE TABLE_TYPE LIKE 'VIEW';