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

输入代码

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,'zhongprng','hebei','13889075861'),(2,'lixiang','shangdong','13953508223'),(3,'huangyun','shandong','13905350996');

查看：

 

代码：

create table chengweiqiang_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 chengweiqiang_sign values(1,'zhangpeng','high school1','peking university'),(2,'lixiao','high school2','peking unversity'),(3,'huangyun','high school3','tsinghua universuty');

 查看：

 

 

代码：

create table chengweiqiang_stu_mark(s_id int(11) primary key,s_name varchar(20) not null,mark int not null);

 

 

 

 代码：

insert into chengweiqiang_stu_mark values(1,'zhangpeng',730),(2,'lixiao',725),(3,'huangyun',736);

 

查看：

 

 

 代码：

 create view beida(id,name,mark,sch) as select a.s_id,a.s_name,a.mark,b.s_sign_sch from chengweiqiang_stu_mark a,chengweiqiang_sign b where a.s_id=b.s_id and a.mark >=720 and b.s_sign_sch='peking university';

 

 查看：

 

 代码：

create view qinghua(id,name,mark,sch) as select a.s_id,a.s_name,a.mark,b.s_sign_sch from chengweiqiang_stu_mark a,chengweiqiang_sign b where a.s_id=b.s_id and a.mark >=725 and b.s_sign_sch='tsinghua universuty';

查看：

 

代码：

update chengweiqiang_stu_mark set mark=mark-10 where chengweiqiang_stu_mark.s_name="huangyun";

 

查看：