(1)在example数据库下创建college表。SQL代码如下:
CREATE TABLE college (
number INT(10) NOT NULL UNIQUE PRIMARY KEY ,
name VARCHAR(20) NOT NULL ,
major VARCHAR(20) NOT NULL ,
age INT(5)
);
(2)使用CREATE VIEW语句来创建视图college_view。SQL代码如下:
CREATE ALGORITHM=UNDEFINED VIEW
college_view (student_num, student_name, student_age, department )
AS SELECT number, name, age, major FROM college
WITH LOCAL CHECK OPTION;
(3)执行SHOW CREATE VIEW语句来查看视图的详细结构。代码如下:
SHOW CREATE VIEW college_view \G
(4)更新视图。向视图中插入三条记录,SQL代码如下:
INSERT INTO college_view VALUES( 0901, '张三', 20, '外语' );
INSERT INTO college_view VALUES( 0902, '李四', 22, '计算机' );
INSERT INTO college_view VALUES( 0903, '王五', 19, '计算机' );
(5)修改视图,使视图中只显示专业为“计算机”的信息。SQL代码如下:
CREATE OR REPLACE ALGORITHM=UNDEFINED VIEW
college_view (student_num, student_name, student_age, department )
AS SELECT number, name, age, major
FROM college WHERE major='计算机'
WITH LOCAL CHECK OPTION;
(6)删除视图。SQL代码如下:
DROP VIEW college_view;