MySQL之视图应用的基本操作 - ic翼

2018-04-13 f88488

前言

本文包含视图的创建、查询、更新和删除操作。

实验要求

假如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;