数据库第十一次作业-视图的应用【带源码】

目录

​​1.创建表​​

​​2.添加数据​​

​​3.查看数据​​

​​4.创建视图​​

1.创建表

create table stu(

    -> s_id int(11) not null unique primary key,

    -> s_name varchar(20) not null,

    -> addr varchar(50) not null,

    -> tel varchar(50) not null

-> );

create table sign(

    -> s_id int(11) not null unique primary key,

    -> s_name varchar(20) not null,

    -> s_sch varchar(50) not null,

    -> s_sign_sch varchar(50) not null

    -> );

create table stu_mark(

    -> s_id int(11) not null unique primary key,

    -> s_name varchar(20) not null,

    -> mark int(11) not null

    -> );

2.添加数据

insert into stu values

    -> (1,"ZhangPeng","Hebei",13889075861),

    -> (2,"LiXiao","Shandong",13953508223),

    -> (3,"HangYun","Shandong",13905350996);

insert into sign values

    -> (1,"ZhangPeng","High_School1","Peking_University"),

    -> (2,"LiXiao","High_School2","Peking_University"),

    -> (3,"HuangYun","High_School3","Tsinghua_University");

insert into stu_mark values

    -> (1,"ZhangPeng",730),

    -> (2,"LiXiao",725),

    -> (3,"HuangYun",736);

3.查看数据

4.创建视图

create view beida as

    -> select a.s_id,a.s_name,c.mark,b.s_sign_sch from

    -> stu a inner join stu_mark c on  a.s_id = c.s_id

    -> inner join sign b on c.s_id = b.s_id where

    -> c.mark>=720 and

    -> b.s_sign_sch ="Peking_University";

create view qinghua as

    -> select a.s_id,a.s_name,c.mark,b.s_sign_sch from

    -> stu a inner join stu_mark c on  a.s_id = c.s_id

    -> inner join sign b on c.s_id = b.s_id where

    -> c.mark>=725 and

    -> b.s_sign_sch ="Tsinghua_University";

update stu_mark set

    -> mark=mark-10

    -> where stu_mark.s_name ="HuangYun";