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

create table guochangsheng_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 guochangsheng_stu values
(1,"ZhangPeng","Hebei","13889075861"),
(2,"LiXiao","Shandong","13953508223"),
(3,"HuangYun","Shandong","13905350996");

 

 

select * from guochangsheng_stu;

 

create table guochangsheng_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 guochangsheng_sign values
(1,"ZhangPeng","High School1","Peking University"),
(2,"LiXiao","High School2","Peking University"),
(3,"HuangYun","High School3","Tsinghua University"
);

select * from guochangsheng_sign;

 

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

 

 

insert into guochangsheng_stu_mark values
(1,"ZhangPeng",730),
(2,"LiXiao",725),
(3,"HuangYun",736);

 

 

select * from guochangsheng_stu_mark;

 

 

create view view_guochangsheng_beida
(id,name,mark,sch) as
select guochangsheng_stu_mark.s_id,guochangsheng_stu_mark.s_name,guochangsheng_stu_mark.mark,
guochangsheng_sign.s_sign_sch
from guochangsheng_stu_mark,guochangsheng_sign
where guochangsheng_stu_mark.s_id = guochangsheng_sign.s_id
and guochangsheng_stu_mark.mark >= 720
and guochangsheng_sign.s_sign_sch = "Peking University";

 

select * from view_guochangsheng_beida;

 

 

create view view_guochangsheng_qinghua
(id,name,mark,sch) as
select guochangsheng_stu_mark.s_id,guochangsheng_stu_mark.s_name,guochangsheng_stu_mark.mark,
guochangsheng_sign.s_sign_sch
from guochangsheng_stu_mark,guochangsheng_sign
where guochangsheng_stu_mark.s_id = guochangsheng_sign.s_id
and guochangsheng_stu_mark.mark >= 725
and guochangsheng_sign.s_sign_sch = "Tsinghua University";

 

 

select * from view_guochangsheng_qinghua;

 

 

update guochangsheng_stu_mark set
mark = mark-10
where guochangsheng_stu_mark.s_name = "HuangYun";

 

 

select * from guochangsheng_stu_mark;

 

 

select * from view_guochangsheng_qinghua;