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

一、创建stu表

 create table 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
    -> );

 alter table stu rename huanglexiang_stu;

二、创建sign表

create table huanglexiang_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
    -> );

三、创建stu_mark表

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

四、添加stu表的数据

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

 五、添加sign表的数据

insert into huanglexiang_sign values
    -> ("1","ZhangPeng","Hign School1","Peking University"),
    -> ("2","LiXiao","Hign School2","Peking University"),
    -> ("3","HuangYun","Hign School3","Tsinghua University");

六、添加stu_mark表的数据

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

七、查看stu、sign、stu_mark三个表的数据

select * from huanglexiang_stu;

select * from huanglexiang_sign;

select * from huanglexiang_stu_mark;

 

八、创建考上北京大学（Peking_University）的学生视图并查看

create view huanglexiang_baida(id,name,mark,sch)as
    -> select m.s_id,m.s_name,m.mark,s.s_sign_sch
    -> from huanglexiang_stu_mark m,huanglexiang_sign s
    -> where m.s_id=s.s_id and m.mark>=720 and s.s_sign_sch="Peking University";

 select * from huanglexiang_baida;

九、创建考上清华大学（Tsinghua University）的学生视图并查看

create view huanglexiang_qinghua(id,name,mark,sch)as
    -> select m.s_id,m.s_name,m.mark,s.s_sign_sch
    -> from huanglexiang_stu_mark m,huanglexiang_sign s
    -> where m.s_id=s.s_id and m.mark>=725 and s.s_sign_sch ="tsinghua university";

 select * from huanglexiang_qinghua;

十、修改stu_mark表并查看，更新视图qinghua

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

 

select * from huanglexiang_stu_mark

 

 

select * from huanglexiang_qinghua;