文档库 最新最全的文档下载
当前位置:文档库 › 8.8上机实践源代码

8.8上机实践源代码

(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;

相关文档
相关文档 最新文档