mysql 对视图的操作_MySQL之视图应用的基本操作

前言

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

实验要求

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