MySQL数据库第十二次作业-存储过程的应用

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";