MySQL视图

1、准备数据库表

 使用sql语句创建学生-课程数据库，该数据库包含学生表、课程表和学生选课表。

//创建数据库
CREATE DATABASE student_course;
//创建学生表
CREATE TABLE Student(
    Sno CHAR(9) PRIMARY KEY,   /*列级完整性约束条件,Sno是主键*/
    Sname CHAR(9) UNIQUE,      /*Sname去唯一值*/
    Ssex CHAR(2),
    Sage SMALLINT,
    Sdept CHAR(20)
);

//创建课程表
CREATE TABLE Course(
    Cno CHAR(4) PRIMARY KEY,  /*列级完整性约束条件,Cno是主键*/
    Cname CHAR(40) NOT NULL,  /*列级完整性约束条件,Cname不能取空值*/
    Cpno CHAR(4),
    Ccredit SMALLINT
);
//创建学生选课表SC
CREATE TABLE SC(
    Sno CHAR(9),
    Cno CHAR(4),
    Grade SMALLINT NULL check (Grade is null or (Grade between 0 and 100)),
    PRIMARY KEY(Sno,Cno),
    FOREIGN KEY (Sno) REFERENCES Student(Sno),
    /*表级完整性约束条件,Sno是外键,参照表是Student,被参照列是Sno*/
    FOREIGN KEY (Cno) REFERENCES Course(Cno)
    /*表级完整性约束条件,Cno是外键,参照表是Course,被参照列是Cno*/
);

 在对应的表里面添加下面表格中的数据
学生表

Sno	Sname	Sex	Sage	Sdept
202015121	张三	男	20	CS
202015121	李四	男	19	CS
202015121	王敏	女	21	MS
202015121	刘晨	女	20	IS

插入学生数据的SQL语句如下：

insert into Student values
(202015121,'张三','男',20,'CS'),
(202015122,'李四','男',19,'CS'),
(202015123,'王敏','女',21,'MS'),
(202015124,'刘晨','女',20,'IS');

课程表

Cno	Cname	Cpno	Ccredit
1	数据库	5	4
2	数学		2
3	信息系统	1	4
4	操作系统	6	3
5	数据结构	7	4
6	Python		2
7	PASCAL语言	6	4

插入课程数据的SQL语句如下：

insert into Course values("1","数据库","5","4");
insert into Course values("2","数学","","2");
insert into Course values("3","信息系统","1","4");
insert into Course values("4","操作系统","6","3");
insert into Course values("5","数据结构","7","4");
insert into Course values("6","数据处理","","2");
insert into Course values("7","PASCAL语言","6","4");

选课表

Sno	Cno	Grade
202015121	1	92
202015121	2	85
202015121	3	88
202015122	2	90
202015122	3	80

插入选课数据的SQL语句如下：

insert into SC values
(202015121,'1','92'),
(202015121,'2','85'),
(202015121,'3','88'),
(202015122,'2','90'),
(202015122,'3','80');

2、视图

2.1 视图的概述

 视图是从一个或几个基本表（或视图）导出的表。

2.2 视图的概念

 它与基本表不同，是一个虚拟表。数据库中只存放视图的定义，而不存放视图对应的数据，这些数据人存放在原来的基本表中。所以一旦基本表中的数据发生改变，从视图中查询的数据也随之改变。从这个意义上讲，视图就像一个窗口，通过它可以看到数据库中自己感兴趣的数据及其变化。

2.3 视图的作用

1. 数据抽象
    视图可以隐藏复杂的查询细节，只显示用户关心的特定字段，简化数据访问。
2. 安全性
    可以通过视图限制用户对基础表的直接访问，只允许他们看到或操作视图定义的特定数据。
3. 数据一致性
    视图可以反映数据的特定状态，即使基础表中的数据发生变化，视图中的数据也会自动更新。
4. 性能提升
    如果视图的查询被优化，可以减少对底层表的直接查询，提高查询效率。

注意：
 视图本身并不能提升查询效率，只能提升开发效率.

2.4 视图的定义

2.4.1 创建视图

 视图的创建语法如下：

CREATE VIEW view_name
AS
[SELECT|INSERT|UPDATE] column1, column2, ...
FROM table_name
WHERE condition
[GROUP BY column]
[HAVING condition]
[ORDER BY column];

 组成视图的属性列名要么全部指定，要么全部省略。
注意：
 但在下列三种情况下必须明确指定组成视图的所以列名：
  （1）某个目标列不是单纯的属性名，而是聚集函数或者列表达式；
  （2）多表连接时选出了几个同名列作为视图的字段
  （3）需要在视图中为某个列启用新的更适合的名字

[例1] 创建信息系学生的视图

CREATE VIEW IS_Student 
AS 
SELECT Sno,Sname,Sage FROM Student WHERE Sdept = 'IS';

 关系数据库管理系统执行 CREAETE VIEW 语句的结果只是把视图的定义存入数据字典，并不执行其中的 select 语句。只是在对视图查询时，才按照视图的定义从基本表中将查询数据
 若一个视图是从单个基本表导出的，并且只是去掉了基本表的某些行和某些列，但保留了主码，我盟称这类视图为行列子集视图

[例2] 建立信息系选修了1号课程的学生视图（包括学号，姓名，成绩）

CREATE VIEW IS_S1(Sno, Sname, Grade) 
AS 
SELECT Student.Sno, Sname, Grade 
FROM Student, SC 
WHERE Sdept= 'IS' AND Student.Sno=SC.Sno AND SC.Cno= '1';

[例3]  建立信息系选修了1号课程且成绩在90分以上的学生的视图

CREATE VIEW IS_S2 
AS 
SELECT Sno, Sname, Grade 
FROM IS_S1 
WHERE Grade>=90;

[例4]  定义一个反映学生出生年份的视图

CREATE VIEW BT_S(Sno, Sname, Sbirth)
AS 
SELECT Sno, Sname, 2000-Sage FROM Student;

[例5] 将学生的学号及他的平均成绩定义为一个视图

CREATE VIEW S_G(Sno, Gavg)
AS 
SELECT Sno, AVG(Grade) FROM SC GROUP BY Sno;

 用带有聚集函数和GROUP BY子句的查询来定义视图，这种视图称为分组视图。

 由于AS子语句中SELECT语句语句的目标列平均成绩是通过作用聚集函数得到的，所以CREATE VIEW中必须明确定义组成S_G视图的每个属性列名，S_G是一个分组视图。

2.4.2 删除视图

该语法格式为：

DROP VIEW view_name [CASCADE]

[例6] 删除视图BT_S视图和IS_S1

DROP VIEW BT_S; /*成功执行*/
DROP VIEW IS_S1； /*拒绝执行*/

 删除视图对生成视图的基本表没有任何影响。

 删除基表时，由该基表导出的所有视图定义没有被删除，但已不能使用，必须使用 DROP VIEW 显式删除。

DROP VIEW IS_S1 CASCADE； /*删除了视图IS_S1和由它导出的所有视图*/

2.5 查询视图

 视图定义以后，用户就可以像查询基本表一样查询视图了。

 因此从用户角度看，查询视图与查询基本表相同。不过，所有对视图的查询，都会被数据库管理软件解释成对基本数据表的查询，因为视图是在基本表的基础上抽象出来的。

[例7] 在信息系学生的视图中找出年龄小于20岁的学生

SELECT Sno, Sage 
FROM IS_Student 
WHERE Sage < 20;

IS_Student视图的定义(视图定义例1)：

CREATE VIEW IS_Student 
AS 
SELECT Sno, Sname, Sage 
FROM Student 
WHERE Sdept= 'IS';

转换后的查询语句为：

SELECT Sno, Sage 
FROM Student 
WHERE Sdept='IS' AND Sage < 20;

2.6 更新视图

 更新视图是指通过视图插入（INSERT）、删除（DELETE）和修改（UPDATE）数据。

 由于视图是不实际存储数据的虚表，因此对视图的更新最终要转换为对基本表的更新。

 从用户角度看，更新视图与更新基本表相同；RDBMS将之转化为对基本表的更新操作。

 为防止用户通过视图对数据进行更新时，有意无意地对不属于视图范围内的基本表数据进行操作，可以在定义视图时加上WITH CHECK OPTION子句。这样在视图上增删改数据时，关系数据库管理系统（RDBMS）会检查视图定义中的条件，若不满足条件，则拒绝执行该操作。

[例7] 将信息系学生视图 IS_Student 中学号 202015122 的学生姓名改为“刘辰”

UPDATE IS_Student 
SET Sname='刘辰' 
WHERE Sno='202015122';

转换后的语句：
UPDATE Student 
SET Sname='刘辰' 
WHERE Sno='202015122' AND Sdept='IS';

 在关系数据库中，并不是所有的视图都是可更新的，因为有些视图的更新不能唯一地有意义地转换成对相应基本表的更新。

 一般地，行列子集视图是可更新的，除此之外，还有一些视图理论上是可更新的，但他们的确切特征还是尚待研究的课题，还有些视图从理论上就是不可更新的。