create database yican_chapter07;
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,"huangyun","shandong","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");
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);
create view 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 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";