1.创建学生表,并插入三条数据
(1) 创建学生表stu:
代码:
create table stu(
-> s_id int(11) not null primary key unique,
-> s_name varchar(20) not null,
-> addr varchar(50) not null,
-> tel varchar(50) not null
-> );
截图:
(2)插入数据:
代码:
insert into stu values
-> ('1','ZhangPeng','Hebei','13889075861'),
-> ('2','LiXiao','Shandong','13953508223'),
-> ('3','HuangYun','Shandong','13905350996');
截图:
2.创建报名表sign,并插入三条数据;
(1)创建报名表:
代码:
create table sign(
-> s_id int(11) not null primary key unique,
-> s_name varchar(20) not null,
-> s_sch varchar(50) not null,
-> s_sign_sch varchar(50) not null
-> );
截图:
(2)插入数据:
代码:
insert into sign values
-> ('1','ZhangPeng','High_School1','Peking_University'),
-> ('2','LiXiao','High_School2','Peking_University'),
-> ('3','HuangYun','High_School3','Tsinghua_University');
截图:
3.创建成绩表stu_mark,然后插入三条数据;
(1)创建成绩表:
代码:
create table stu_mark(
-> s_id int(11) not null primary key unique,
-> s_name varchar(20) not null,
-> mark int(11) not null
-> );
截图:
(2)插入数据:
代码:
insert into stu_mark values
-> ('1','ZhangPeng','730'),
-> ('2','LiXiao','725'),
-> ('3','HuangYun','736');
截图:
4.创建考上北京大学(Peking university)的学生视图,视图的名字为beida,视图的内容包含考上北大的学号、姓名、成绩和报考学校名称4个字段,创建beida视图的sql语句:
代码:
create 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';
截图:
5.创建考上清华大学(Tsinghua_University)的学生视图。
视图的名称为qinghua,视图的内容包含考上清华的学生学号、姓名、成绩和报考学校4个字段,创建qinghua视图的sql语句:
代码:
create 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';
截图:
6.更新视图qinghua
HuangYun的成绩在录入的时候录入错误,多录入了10分,接下来对HuangYun的成绩进行修改,减去多录入的10分。在视图中可以使用update语句对基本表stu_mark的数据进行更新,sql语句为:
代码:
update stu_mark set mark = mark-10 where stu_mark.s_name ='huangyun';
截图:
从上述查询结果可以看出,s. name值为HuangYun的学生成绩减去了多录的10分,变为了726分。接下来查看qinghua视图表中的信息情况,结果如下:
代码:
select * from qinghua;
截图: