前言
本文包含视图的创建、查询、更新和删除操作。
实验要求
假如Henan、Hebei的三个学生参加Tsinghua University、Peking University的自学考试。现在需要用数据对其考试结果进行查询和管理,Tsinghua University 的分数线为40,Peking University的分数线为41。表结构如下所示
注:因为是在MySQL workbench中进行操作。截图效果不好也就没有截图。
创建test数据库
创建并使用test数据库
create database test;
use test;
stu学生表
stu表结构
字段名
数据类型
主键
外键
非空
唯一
自增
s_id
INT(11)
Y
N
Y
Y
N
s_name
VARCHAR(20)
N
N
Y
N
N
addr
VARCHAR(50)
N
N
Y
N
N
tel
VARCHAR(50)
N
N
Y
N
N
stu表内容
s_id
s_name
addr
tel
1
XiaoWang
Henan
0371-12345678
2
XiaoLi
Hebei
13889072345
3
ZhengGu
Henan
0371-12345670
CREATE TABLE stu (
s_id INT PRIMARY KEY,
s_name VARCHAR(20) NOT NULL,
addr VARCHAR(50) NOT NULL,
tel VARCHAR(50) NOT NULL
);
insert into stu
values(1,'XiaoWang','Henan','0371-12345678'),
(2,'XiaoLi','Hebei','13889072345'),
(3,'XiaoTian','Henan','0371-12345670');
select * from stu;
sign报名表
sign结构
字段名
数据类型
主键
外键
非空
唯一
自增
s_id
INT(11)
Y
N
Y
Y
N
s_name
VARCHAR(20)
N
N
Y
N
N
s_sch
VARCHAR(50)
N
N
Y
N
N
s_sign_sch
VARCHAR(50)
N
N
Y
N
N
sign内容
s_id
s_name
s_sch
s_sign_sch
1
XiaoWang
Middle School1
Peking University
2
XiaoLi
Middle School2
Tsinghua University
3
XiaoTian
Middle School3
Tsinghua University
CREATE TABLE sign (
s_id INT 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,'XiaoWang','Middle School1','Peking University'),
(2,'XiaoLi','Middle School2','Tsinghua University'),
(3,'XiaoTian','Middle School3','Tsinghua University');
select * from sign;
stu_mark成绩表
stu_mark结构
字段名
数据类型
主键
外键
非空
唯一
自增
s_id
INT(11)
Y
N
Y
Y
N
s_name
VARCHAR(20)
N
N
Y
N
N
mark
iINT(11)
N
N
Y
N
N
stu_mark内容
s_id
s_name
mark
1
XiaoWang
80
2
XiaoLi
71
3
XiaoTian
70
create table stu_mark
(
s_id int primary key,
s_name varchar(20) not null,
mark int not null
);
insert into stu_mark values(1,'XiaoWang',80),
(2,'XiaoLi',71),
(3,'XiaoTian',70);
select * from stu_mark;
创建视图
创建考上Peking University的学生视图
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>=41 and sign.s_sign_sch='Peking University';
select * from beida;
创建考上Tsinghua University的学生视图
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>=40 and sign.s_sign_sch='Tsinghua University';
select * from qinghua;
修改视图
XiaoTian的成绩出了问题多录入了50分,对其修正。
因为MySql运行在safe-updates模式下,该模式会导致非主键条件下无法执行update或者delete命令。代码如下
SET SQL_SAFE_UPDATES = 0;
//建议更改结束后将安全模式恢复。即把 “0” 改为 “1”
update stu_mark set mark=mark-50 where stu_mark.s_name='XiaoTian';
删除视图
删除beida、qinghua的视图。
drop view beida;
drop view qinghua;