前言
本文包含视图的创建、查询、更新和删除操作。
实验要求
假如Henan、Hebei的三个学生参加Tsinghua University、Peking University的自学考试。现在需要用数据对其考试结果进行查询和管理，Tsinghua University 的分数线为40，Peking University的分数线为41。表结构如下所示
- 注：因为是在MySQL workbench中进行操作。截图效果不好也就没有截图。
创建test数据库
创建并使用test数据库
create database test;
use test;
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 |
| 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报名表
| 字段名 |
数据类型 |
主键 |
外键 |
非空 |
唯一 |
自增 |
| 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 |
| 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成绩表
| 字段名 |
数据类型 |
主键 |
外键 |
非空 |
唯一 |
自增 |
| 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 |
| 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;
