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

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

 

 

 

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

 

 

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

 

 

insert into hongchenhui_stu values
(“1”,“ZhangPeng”,“Hebei”,“13889075861”),
(“2”,“LiXiao”,“Shandong”,“13953508223”),
(“3”,“HuangYun”,“Shandong”,“13905350996”);

 

 

insert into hongchenhui_sign values
(“1”,“ZhangPeng”,“Hign School1”,“Peking University”),
(“2”,“LiXiao”,“Hign School2”,“Peking University”),
(“3”,“HuangYun”,“Hign School3”,“Tsinghua University”);

 

 

insert into hongchenhui_stu_mark values
(“1”,“ZhangPeng”,“730”),
(“2”,“LiXiao”,“725”),
(“3”,“HuangYun”,“736”);

 

 

create view hongchenhui_baida(id,name,mark,sch)as
select m.s_id,m.s_name,m.mark,s.s_sign_sch
from hongchenhui_stu_mark m,hongchenhui_sign s
where m.s_id=s.s_id and m.mark>=720 and s.s_sign_sch=“Peking University”;

 

select * from hongchenhui_baida;

 

create view hongchenhui_qinghua(id,name,mark,sch)as
select m.s_id,m.s_name,m.mark,s.s_sign_sch
from hongchenhui_stu_mark m,hongchenhui_sign s
where m.s_id=s.s_id and m.mark>=725 and s.s_sign_sch=“Peking University”;

 

 

 

select * from hongchenhui_qinghua;

 

 

 

update hongchenhui_stu_mark set mark=mark-10
where hongchenhui_stu_mark.s_name=“HuangYun”;

 

 

 select * from hongchenhui_stu_mark;

 

 

select * from hongchenhui_qinghua;