数据库第十一次作业——视图的应用

create table stu (
s_id int(11) primary key,
s_name varchar(20) not null,
addr varchar(50) not null,
tel varchar(50) not null
);

insert into stu
values(1,"zhangpeng","hebei","13889075861"),
(2,"lixiao","shandong","13953508223"),
(3,"huangyum","shangdong","13905350996");

create table sign (
s_id int(11) primary key,
s_name varchar(20) not null,
s_sch varchar(50) not null,
s_sign_sch varchar(50) not null
);

insert into sign
values(1,"zhangpeng","high school1","peking university"),
(2,"lixiao","high school2","peking university"),
(3,"huangyun","high school3","tsinghua university");

 查询一下sign的数据信息

create table stu_mark (
s_id int(11) primary key,
s_name varchar(20) not null,
mark int not null
);

insert into stu_mark values(1,"zhangpeng",730),(2,"lixiao",725),(3,"huangyun",736);

  查询一下stu_mark的数据信息

create view beida(id,name,mark,sch) as
select stu_mark.s_id,stu_mark.s_name,stu_mark.mark,sign.s_sign_sch
from stu_mark,sign
where stu_mark.s_id = sign.s_id
and stu_mark.mark >=720
and sign.s_sign_sch ="peking university";

查看一下 

create view qinghua(id,name,mark,sch) as
select stu_mark.s_id,stu_mark.s_name,stu_mark.mark,sign.s_sign_sch
from stu_mark,sign
where stu_mark.s_id = sign.s_id
and stu_mark.mark >=725
and sign.s_sign_sch ="tsinghua university";

 查看一下

update stu_mark set mark=mark-10 where stu_mark.s_name="huangyun";