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

 

  create table qiufaqing_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 qiufaqing_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 qiufaqing_stu_mark(
 s_id int(11) primary key not null unique,
 s_name varchar(20) not null,
 mark int(11) not null
);

 

 

insert into qiufaqing_stu values
 ("1","ZhangPeng","Hebei","13889075861"),
 ("2","LiXiao","Shandong","13953508223"),
 ("3","HuangYun","Shandong","13905350996");

 

 

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

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

 

 

 create view qiufaqing_beida(id,name,mark,sch)as
  select m.s_id,m.s_name,m.mark,s.s_sign_sch
 from qiufaqing_stu_mark m,qiufaqing_sign s
 where m.s_id=s.s_id and m.mark>=720 and s.s_sign_sch="Peking University";

 

  select * from qiufaqing_beida;

 

 

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

 

 

 

 select * from qinghua;

 

 

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

 

 

 create view qiufaqing_qinghua(id,name,mark,sch)as
   select m.s_id,m.s_name,m.mark,s.s_sign_sch
   from qiufaqing_stu_mark m,qiufaqing_sign s
   where m.s_id=s.s_id and m.mark>=725 and s.s_sign_sch="Peking University";

 

 select * from qiufaqing_qinghua;

 

 

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