综合案例4——视图应用

本部分介绍了MySQL数据库中视图的含义和作用，并且讲解了创建视图、修改视图和删除视图的方法。创建视图和修改视图是重点，希望同学们认真学习这两部分内容，并在计算机上进行操作。同学们在创建视图后一定要查看视图的结构，确保创建的视图是正确的，修改视图后也要查看视图的结构，保证修改是正确的。

1、案例目的

掌握视图的创建、查询、更新和删除操作。

假如河南河北有三个学生参加了清华、北大的自学考试，现在需要用数据对其考试结果进行查询和管理，清华的分数线是40，北大的分数线是41.学生表包含了学生的学号、姓名、家庭地址和电话号码；报名表包含学号、姓名、所在学校和报名的学校，表结构以及表中的内容分别如表1~表6所示。

表1 stu表结构

字段名	数据类型	主键	外键	非空	唯一	自增
序号	int（11）	是	否	是	是	否
姓名	varchar（20）	否	否	是	否	否
地址	varchar（50）	否	否	是	否	否
电话	varchar（50）	否	否	是	否	否

表2 sign表结构

字段名	数据类型	主键	外键	非空	唯一	自增
序号	int（11）	是	否	是	是	否
姓名	varchar（20）	否	否	是	否	否
学校	varchar（50）	否	否	是	否	否
报名学校	varchar（50）	否	否	是	否	否

表3 stu_mark表结构

字段名	数据类型	主键	外键	非空	唯一	自增
序号	int（11）	是	否	是	是	否
姓名	varchar（20）	否	否	是	否	否
分数	int（11）	否	否	是	否	否

表4 stu表内容

序号	姓名	地址	电话
1	小王	河南	0317-12345678
2	小李	河北	13889072345
3	小田	河南	0317-12345670

表5 sign表内容

序号	姓名	学校	报名学校
1	小王	中学1	北大
2	小李	中学2	清华
3	小田	中学3	清华

表6 stu_mark表中的记录

序号	姓名	分数
1	小王	80
2	小李	71
3	小田	70

2、案例操作过程

（1）登录MySQL数据库。

mysql -u root -p

截图：

（2）创建数据库stu_exam并选择使用此数据库。

create database stu_exam;
use stu_exam;

截图：

（3）按照表1、表2、表3创建表，创建成功后用desc查看表结构。

create table stu(
序号 int(11) not null unique,
姓名 varchar(20) not null,
地址 varchar(50) not null,
电话 varchar(50) not null,
primary key(序号)
);

create table sign(
序号 int(11) not null unique,
姓名 varchar(20) not null,
学校 varchar(50) not null,
报名学校 varchar(50) not null,
primary key(序号)
);

create table stu_mark(
序号 int(11) not null unique,
姓名 varchar(20) not null,
分数 int(11) not null,
primary key(序号)
);

截图：

（4）将表4、表5、表6的记录分别插入三个表中。

insert into stu values
(1,'小王','河南','0317-12345678'),
(2,'小李','河北','13889072345'),
(3,'小田','河南','0317-12345670');

insert into sign values
(1,'小王','中学1','北大'),
(2,'小李','中学2','清华'),
(3,'小田','中学3','清华');

insert into stu_mark values
(1,'小王',80),
(2,'小李',71),
(3,'小田',70);

截图：

（5）创建考上北大的学生视图（视图中包含序号、姓名、分数和报名学校）。

create view 北大
(序号,姓名,分数,报名学校) as
select stu_mark.序号,stu_mark.姓名,stu_mark.分数,sign.报名学校
from stu_mark,sign
where stu_mark.序号=sign.序号 and sign.报名学校='北大' and stu_mark.分数>=41;

截图：

（6）创建考上清华的学生视图（视图中包含序号、姓名、分数和报名学校）。

create view 清华
(序号,姓名,分数,报名学校) as
select stu_mark.序号,stu_mark.姓名,stu_mark.分数,sign.报名学校
from stu_mark,sign
where stu_mark.序号=sign.序号 and sign.报名学校='清华' and stu_mark.分数>=40;

截图：

（7）小田的成绩在录入的时候录入错误多录了50分，对其录入成绩进行更新。

update 清华 set 分数=分数-50 where 姓名='小田';

截图：

（8）查询更新过后的视图和表的情况。

select * from 清华;
select * from stu_mark;

截图：

（9）查看视图的创建信息。

show table status like '北大';
show table status like '清华';

截图：

（10）删除创建的视图。

drop view 北大;
drop view if exists 清华;

截图：