MySQL视图

准备工作，新建名为students的数据，三张表分别是student，courses，stu_cou，并创建外键约束，级联删除更新，插入数据。

/*创建数据库*/
create database if not EXISTS students character set utf8 collate utf8_general_ci;
/*创建表*/
use students;
create table if not EXISTS student
(
    stuID  int(5)  not null primary key,
    stuName  varchar(50)  not null,
    stuSex CHAR(10),
    stuAge smallint
);

CREATE TABLE if not EXISTS courses(
         couID int  not null primary key auto_increment COMMENT '学号',
         couName varchar(50) not null DEFAULT('大学英语'),
         couHours  smallint UNSIGNED COMMENT '学时',
         couCredit  float DEFAULT(2) COMMENT '学分'
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE  utf8mb4_0900_ai_ci;

CREATE TABLE if not EXISTS stu_cou(
         ID int not null primary key auto_increment,
         stuID int(5)  not null  COMMENT '学号',
         couID int  not null  COMMENT '课程编号',
         time timestamp not null DEFAULT(now())
);

 /*添加外键约束*/
 alter table stu_cou add CONSTRAINT fk_stuid foreign key(stuID) REFERENCES student(stuID) ON UPDATE CASCADE ON DELETE CASCADE ;
 alter table stu_cou add CONSTRAINT fk_couid foreign key(couID) REFERENCES courses(couID) ON UPDATE CASCADE ON DELETE CASCADE ;

  /*插入数据*/
 insert into student(stuID,stuName,stuSex,stuAge) values(1001,'张三','男',19),(1002,'李四','男',18),(1003,'王五','男',18),(1004,'黄丽丽','女',18),(1005,'李晓辉','女',19),(1006,'张敏','女',18); insert into student VALUES(1007,'五条人','男',20),(1008,'胡五伍','女',19);
 insert into courses(couID,couName,couHours,couCredit) values(50,'大学英语',64,2),(60,'计算机基础',78,2.5),(70,'Java程序设计',108,6),(80,'数据库应用',48,2.5);
 insert into stu_cou(stuID,couID) values(1001,50),(1001,60),(1001,70),(1001,80),(1002,50),(1002,60),(1002,70),(1002,80),(1003,50),(1003,60),(1003,70),(1003,80),(1004,50),(1004,60),(1004,70),(1004,80),(1005,50),(1005,60),(1005,70),(1005,80),(1006,50),(1006,60),(1006,70),(1006,80);

alter table stu_cou add COLUMN grade FLOAT null;
UPDATE stu_cou set grade=(SELECT FLOOR(50 +RAND() * 50));

alter table student add COLUMN stuColleage varchar(100) null;
update student set stuColleage='大数据学院' where stuID BETWEEN 1001 and  1003;
update student set stuColleage='物流学院' where stuID BETWEEN 1004 and  1006;
update student set stuColleage='康养学院' where stuID BETWEEN 1007 and  1008;

alter table courses add COLUMN couColleage varchar(50) null;

update courses set couColleage='通识教育学院' where couName='大学英语';
update courses set couColleage='通识教育学院' where couName='计算机基础';
update courses set couColleage='大数据学院' where couName='Java程序设计';
update courses set couColleage='大数据学院' where couName='数据库应用';

insert into courses values(90,'大学体育',56,1.5,'通识教育学院'),(100,'Android程序设计',92,5,'大数据学院'),(101,'大学物理',48,2,'通识教育学院');

insert into stu_cou(stuID,couID,grade) values(1007,50,86),(1007,60,71),(1008,70,56),(1008,80,63);
 insert into student VALUES(1009,'曾小小','男',17,'物流学院'),(1010,'项XXX','女',21,'大数据学院');

View Code

1.视图的创建

语法格式：create view 视图名称 [列名1,列名2,...] as 查询语句 [with check option];

其中，AS后的查询可以是任意具体的数据库系统支持的SELECT语句。语句WITH CHECK OPTION表示通过视图进行更新操作时要保证更新的数据满足子查询的条件表达式。

组成视图的列名要么省略，要么全部指定。如果省略，则视图的列名就由子查询中的列名组成。

在下列情况下，必须指定视图列名:

子查询的某个目标列是聚合函数或列表达式;
多表连接时出现同名列作为视图的列;
需要在视图中指定新列名替代子查询列名。

例1：创建一个名为“查询所有大数据学院学生”的视图（单表查询）。

create view `查询所有大数据学院学生`
as
select * from student where stuColleage='大数据学院'
with check option;

例2：创建一个名为“查询学生姓名,课程名称和分数”的视图（多表查询）。

create view `查询学生姓名,课程名称和分数`
as
SELECT stuName as '姓名',couName as '课程名称',grade as '分数' from student s INNER JOIN stu_cou sc on s.stuID=sc.stuID INNER JOIN courses c on c.couID=sc.couID
with check option;

例3：创建一个名为“查询学生表中男女同学的人数”的视图。

create view `查询学生表中男女同学的人数`(`性别`,`人数`)
as
select stuSex,count(stuID) as '人数' from student 
GROUP BY stuSex with check option;

注意视图名称和字段列表中的中文使用键盘上1旁边的英文波浪号括起来。

2.视图的查询

视图同表一样可以使用select关键字进行查询；

例4：在视图“查询学生姓名,课程名称和分数”中查询张三的所有信息。

select * from  `查询学生姓名,课程名称和分数` where `姓名`='张三';

注意：

（1）where条件后面的字段名称要与视图中的字段名称一致，不是源表的字段名称；

（2）若视图关联的表中添加了新的字段，则该视图将不包含新字段。如果与视图关联的表或视图被删除，则视图将不能再使用。

例5：创建一个名为“查询大数据学院学生的姓名，课程名称，分数和学院名称”的视图，使用已创建的视图来实现。

create view `查询大数据学院学生的姓名，课程名称，分数和学院名称`
as
select a.stuName as '姓名',b.`课程名称`,b.`分数`,a.stuColleage as '学院名称' from `查询所有大数据学院学生` a inner join 
`查询学生姓名,课程名称和分数` b on a.stuName=b.`姓名`
with check option;

查询视图数据

select * from `查询大数据学院学生的姓名，课程名称，分数和学院名称`;

3.查看视图的字段信息

语法：DESCRIBE 视图名; 或者 DESC 视图名;

例6：查看视图“查询大数据学院学生的姓名，课程名称，分数和学院名称”的字段信息。

desc `查询大数据学院学生的姓名，课程名称，分数和学院名称`;

注意：查看表的字段信息与查看视图的字段信息类似，查看表的字段信息的语法为：DESC 表名;

4.查看视图的定义信息

语法：SHOW CREATE VIEW 视图名;

例7：查看视图“查询大数据学院学生的姓名，课程名称，分数和学院名称”的详细定义信息。

show create view `查询大数据学院学生的姓名，课程名称，分数和学院名称`;

注意：查看表的字段信息与查看视图的字段信息类似，查看表的字段信息的语法为：SHOW CREATE table 表名;

5.修改视图的定义(转自：http://c.biancheng.net/view/2586.html)

语法：ALTER VIEW <视图名> AS <SELECT语句>

语法说明如下：

<视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。
<SELECT语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。

需要注意的是，对于 ALTER VIEW 语句的使用，需要用户具有针对视图的 CREATE VIEW 和 DROP 权限，以及由 SELECT 语句选择的每一列上的某些权限。
修改视图的定义，除了可以通过 ALTER VIEW 外，也可以使用 DROP VIEW 语句先删除视图，再使用 CREATE VIEW 语句来实现。

例8：修改`查询所有大数据学院学生`视图，将视图显示的字段名改为学号，姓名，性别，学院。

alter view `查询所有大数据学院学生`(`学号`,`姓名`,`性别`,`学院`)
as
select stuID,stuName,stuSex,stuColleage from student 
where stuColleage='大数据学院';

6.修改视图的内容(转自：http://c.biancheng.net/view/2586.html)

视图是一个虚拟表，实际的数据来自于基本表，所以通过插入、修改和删除操作更新视图中的数据，实质上是在更新视图所引用的基本表的数据。

注意：对视图的修改就是对基本表的修改，因此在修改时，要满足基本表的数据定义。

某些视图是可更新的。也就是说，可以使用 UPDATE、DELETE 或 INSERT 等语句更新基本表的内容。对于可更新的视图，视图中的行和基本表的行之间必须具有一对一的关系。
还有一些特定的其他结构，这些结构会使得视图不可更新。更具体地讲，如果视图包含以下结构中的任何一种，它就是不可更新的：

聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
DISTINCT 关键字。
GROUP BY 子句。
HAVING 子句。
UNION 或 UNION ALL 运算符。
位于选择列表中的子查询。
FROM 子句中的不可更新视图或包含多个表。
WHERE 子句中的子查询，引用 FROM 子句中的表。
ALGORITHM 选项为 TEMPTABLE（使用临时表总会使视图成为不可更新的）的时候。

例9：向`查询所有大数据学院学生`视图插入一条数据。

insert into `查询所有大数据学院学生`(`学号`,`姓名`,`性别`,`学院`)
VALUES (1020,'乔峰','男','大数据学院');

此时视图和student表中都增加了一条数据。

以下3中情况不能在视图中插入数据：

（1）若基本表中有非空字段是视图中没有的，如年龄在视图`查询所有大数据学院学生`中是没有的，此时若向视图插入数据则不能成功。如下例（先删除例9已经添加的数据）：

/*修改student表中的stuAge为非空字段*/
alter table student MODIFY COLUMN stuAge SMALLINT not null;
/*向`查询所有大数据学院学生`视图插入一条数据*/
insert into `查询所有大数据学院学生`(`学号`,`姓名`,`性别`,`学院`)
VALUES (1020,'乔峰','男','大数据学院');

提示错误为：

（2）在视图中插入数据时，也只能是创建视图时where中条件为“大数据学院”的学生，如果添加其他学院的学生信息也会出错，如下例（先将student表中的stuAge字段改回可为空）：

第一步：修改视图`查询所有大数据学院学生`添加with check option关键字，添加后每次操作视图都会去检查原来创建视图时候的条件。

alter view `查询所有大数据学院学生`(`学号`,`姓名`,`性别`,`学院`)
as
select stuID,stuName,stuSex,stuColleage from student 
where stuColleage='大数据学院'
with check option;

第二步：修改student表中的stuAge改回可空字段

alter table student MODIFY COLUMN stuAge SMALLINT null;

第三步：向`查询所有大数据学院学生`视图插入一条数据

insert into `查询所有大数据学院学生`(`学号`,`姓名`,`性别`,`学院`)
VALUES (1021,'段誉','男','康养学院');

提示出错：

（3）当视图依赖多个表的时候，不能向视图中插入数据，比如向例4“查询学生姓名,课程名称和分数”视图中插入一条数据（'乔峰','Java程序设计',88）。

insert into `查询学生姓名,课程名称和分数`(`姓名`,`课程名称`,`分数`) values('乔峰','Java程序设计',88);

提示错误：

例10-1:创建一个名为'查询大数据学院男同学的年龄'的视图，视图显示学号、姓名、性别、年龄、学院。

create view `查询大数据学院男同学的年龄`(`学号`,`姓名`,`性别`,`年龄`,`学院`)
as
select stuID,stuName,stuSex,stuAge,stuColleage from student where 
stuSex='男' and stuColleage='大数据学院';
SELECT * from `查询大数据学院男同学的年龄`;

例10-2:将视图`查询大数据学院男同学的年龄`中的所有年龄都增加1。

update `查询大数据学院男同学的年龄` set
`年龄`=`年龄`+1;

视图中的所有年龄都+1，且基本表student中的记录对应的年龄也+1；

例11：修改视图“查询学生姓名,课程名称和分数”，将姓名为‘张三’的同学名字改为‘张小波’且其大学英语成绩改为95。

update `查询学生姓名,课程名称和分数` set 
`姓名`='张小波',`分数`=95
where `姓名`='张三' and `课程名称`='大学英语';

弹出错误如下：

若视图依赖多张表，则一次修改视图只能修改张基本表。例11分两次进行修改

update `查询学生姓名,课程名称和分数` set 
`姓名`='张小波' where `姓名`='张三';

update `查询学生姓名,课程名称和分数` set 
`分数`=95 where `姓名`='张小波' and `课程名称`='大学英语';

例12:删除视图`查询大数据学院男同学的年龄`中姓名为'李四'的信息。

delete from `查询大数据学院男同学的年龄` where `姓名`='李四';

例13:删除视图`查询学生姓名,课程名称和分数`中姓名为'张小波'的信息。

delete from `查询学生姓名,课程名称和分数` where `姓名`='张小波';

如果视图来源于多张表，则不能使用delete语句删除视图内容。

7.删除视图

可以使用 DROP VIEW 语句来删除视图。

语法格式如下：

DROP VIEW [if exists]<视图名1> [ , <视图名2> …]

其中：<视图名>指定要删除的视图名。DROP VIEW 语句可以一次删除多个视图，但是必须在每个视图上拥有 DROP 权限。

例14:删除视图`查询大数据学院男同学的年龄`。

drop view `查询大数据学院男同学的年龄`;

posted @ 2022-11-15 19:20 YorkShare 阅读(326) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

YorkShare_z

笨鸟先飞

MySQL视图

公告