MySQL对象之视图

最新推荐文章于 2025-02-17 15:16:51 发布

qepyd.com

最新推荐文章于 2025-02-17 15:16:51 发布

阅读量457

点赞数

分类专栏： # MySQL 5.7.X 文章标签： mysql

本文链接：https://blog.csdn.net/weixin_43733154/article/details/106073718

版权

MySQL 5.7.X 专栏收录该内容

52 篇文章

订阅专栏

文章目录

第1章 MySQL视图的介绍
- 1.1 视图的认识
- 1.2 视图的简介
第2章 MySQL视图的实践

第1章 MySQL视图的介绍

1.1 视图的认识

第一种方法：客户端工具连接mysql实例后进行查看

第二种方法：查看某个库下是否有视图

## 查看sys库下有哪些视图
select
	table_schema as "库名",
	table_name as "表名",
	table_type as "表类型"
from
	information_schema.tables
where
	table_schema="sys" and table_type = "view"
;

1.2 视图的简介

视图的数据来自于select语句的结果集(不能有重复的字段名,可给重复的字段名进行别名)，它是为了方便数据的查询，而不是为了方便数据的更改。我们创建视图后:

01：在操作系统上看，就是一个表结构文件.frm，没有.ibd文件；当你mysql实例启动后，视图会根据在创建时指定
    的select语句到相关的基表中查询数据，然后将数据保存到视图中，这样就可以直接通过视图查看到相应数据，
    而不需要再写一次查询语句。若基表的数据发生变化了,视图中的数据可能(看视图中的select语句)也会发生变化。

02：在mysql实例中用 show tables from 库名; 查看到的就是一个表名，但我们可通过information_schema.tales来检查
    它是不是视图(table_type等于view就是视图)。

视图是为了方便数据的查询，而不是方便数据的更改

01：视图中的数据可不可以改变，这个不一定，得看视图中的select语句是否有使用函数或者连接查询，若有函数或连接查询
    那么是视图中的数据是不可改变的，会直接报错，反之。
02：视图中的数据若更改了，基表中的相关数据也会改变。     <== but我们不这样干。

视图虽然是为了方便数据的查询，但是在程序开发过程中不要使用视图。

01：若程序开发过程了使用了视图，程序里面就可直接调用视图而得到数据，而不需要再写sql语句了。
02：如果一但需求变更，就得改视图，这时就得需要相关部门的人员(运维人员、DBA)的配合，加大了成本；

第2章 MySQL视图的实践

2.1 测试数据准备

测试数据产生的SQL语句

#### 创建lili库,并进入到lili库下面
create database if not exists lili character set utf8 collate utf8_general_ci;
use lili;         
select database();


#### 准备的基本数据
-- 创建department表
create table if not exists department(
    id int unsigned not null comment"部门ID",
    name varchar(20) not null comment"部门名"
)engine=innodb character set utf8 collate utf8_general_ci comment"部门表";

-- 创建employee表
create table if not exists employee(
	id int unsigned primary key auto_increment comment"序列",
	name varchar(20) not null comment"姓名",
	sex enum('male','female') not null default 'male' comment"性别",
	age int unsigned not null comment"年龄",
	dep_id int unsigned not null comment"部门ID"
)engine=innodb character set utf8 collate utf8_general_ci comment"员工表";

-- 往department表中插入数据
insert into department values
   (200,'技术'),(201,'人力资源'),(202,'销售'),(203,'运营');
commit;

-- 往employee表中插入数据
insert into employee(name,sex,age,dep_id) values
   ('王保长','male',18,200),('刘墉','female',48,201),
   ('喜来乐','male',38,201),('李保田','female',28,202),
   ('刘常明','male',18,200),('纪学礼','female',18,204);
commit;

employee表与department表的关联说明

2.2 视图的实践一(select结果集来自于单表)

视图中的select语句使用了函数

#### 需求：统计出employee表中总共有多少员工
mysql> select count(*) from employee;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)


#### 把 统计employee表中有多少员工的需求 放在一个视图中
create view v_employee_count
  as
    select count(*) from employee;    /* select语句中用到了函数哈 */


#### 查看是否存在v_employee_count
mysql> show tables like "v_employee_count";
+-----------------------------------+
| Tables_in_lili (v_employee_count) |
+-----------------------------------+
| v_employee_count                  |   <== 是存在的哈
+-----------------------------------+
1 row in set (0.00 sec)


#### 查看v_employee_count在操作系统上的表现
mysql> system ls -l /data/mysql/3306/data/lili/v_employee_count.*
-rw-r----- 1 mysql mysql 405 9月   2 02:55 /data/mysql/3306/data/lili/v_employee_count.frm
   ^== 结果只有表结构文件.frm


#### 判断v_employee_count是表还是视图
select
	table_schema,
  table_name,
  table_type
from
	information_schema.tables
where
	table_schema="lili" and table_name="v_employee_count"
;

+--------------+------------------+------------+
| table_schema | table_name       | table_type |
+--------------+------------------+------------+
| lili         | v_employee_count | VIEW       | 
+--------------+------------------+------------+
1 row in set (0.00 sec)             ^== table_type字段的值是view表示v_employee_count是视图


#### 查看视图v_employee_count中的数据
mysql> select * from v_employee_count;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)


#### 查看视图v_employee_count的创建语句
mysql> show create table v_employee_count\G
*************************** 1. row ***************************
                View: v_employee_count
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_employee_count` AS select count(0) AS `count(*)` from `employee`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)


#### 需求变更：统计employee表中男员工和女员工各多少人
-- 统计employee表中男/女员工各多少人
select
  sex,
  count(sex)
from
  employee
group by
  sex
;

+--------+------------+
| sex    | count(sex) |
+--------+------------+
| male   |          3 |
| female |          3 |
+--------+------------+
2 rows in set (0.00 sec)

-- 更改v_employee_count视图中的select语句
alter view v_employee_count 
as
	select
	  sex,
	  count(sex)
	from
	  employee
	group by
	  sex
	;

-- 查看v_employee_count视图中的数据
mysql> select * from v_employee_count;
+--------+------------+
| sex    | count(sex) |
+--------+------------+
| male   |          3 |
| female |          3 |
+--------+------------+
2 rows in set (0.00 sec)

-- 查看v_employee_count视图的创建语句
mysql> show create view v_employee_count\G
*************************** 1. row ***************************
                View: v_employee_count
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_employee_count` AS select `employee`.`sex` AS `sex`,count(`employee`.`sex`) AS `count(sex)` from `employee` group by `employee`.`sex`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)


#### 删除employee表中id=6且name="纪学礼"的记录后,再看视图v_employee_count中的数据
-- 删除employee表中id=6且name=“纪学礼”的记录
mysql> select * from employee where id=6 and name="纪学礼";
+----+-----------+--------+-----+--------+
| id | name      | sex    | age | dep_id |
+----+-----------+--------+-----+--------+
|  6 | 纪学礼    | female |  18 |    204 |
+----+-----------+--------+-----+--------+
1 row in set (0.00 sec)

mysql> delete from employee where id=6 and name="纪学礼";
Query OK, 1 row affected (0.00 sec)

mysql> select * from employee where id=6 and name="纪学礼";
Empty set (0.00 sec)

-- 查看v_employee_count视图中的数据
mysql> select * from v_employee_count;
+--------+------------+
| sex    | count(sex) |
+--------+------------+
| male   |          3 |
| female |          2 |  <== 性别为女的人数只有2个了,因为前面删除的"纪学礼"是female
+--------+------------+
2 rows in set (0.00 sec)


#### 更改v_employee_count中的数据,看是否成功,基表employee表的数据是否会改变
-- 改变v_employee_count中的数据
mysql> select * from v_employee_count;
+--------+------------+
| sex    | count(sex) |
+--------+------------+
| male   |          3 |
| female |          2 |
+--------+------------+
2 rows in set (0.00 sec)

mysql> delete from v_employee_count where sex="male";
ERROR 1288 (HY000): The target table v_employee_count of the DELETE is not updatable
    ^== 直接报错,说table v_employee_count不可更改,因为它是视图,且视图中的select语句
     中使用了函数count()；这样视图中的数据不会改变，其基表中的数据也不会改变

-- 查看v_employee_count视图和它对应的基表employee表中的数据
mysql> select * from v_employee_count;   
+--------+------------+
| sex    | count(sex) |
+--------+------------+
| male   |          3 |
| female |          2 |
+--------+------------+
2 rows in set (0.00 sec)

mysql> select * from employee;
+----+-----------+--------+-----+--------+
| id | name      | sex    | age | dep_id |
+----+-----------+--------+-----+--------+
|  1 | 王保长    | male   |  18 |    200 |
|  2 | 刘墉      | female |  48 |    201 |
|  3 | 喜来乐    | male   |  38 |    201 |
|  4 | 李保田    | female |  28 |    202 |
|  5 | 刘常明    | male   |  18 |    200 |
+----+-----------+--------+-----+--------+
5 rows in set (0.00 sec)


#### 删除视图v_employee_count
mysql> drop view v_employee_count;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables like "v_employee_count";
Empty set (0.00 sec)


#### 总结：
01：基于中的数据改变了,视图中的数据也会改变；
02：视图中的select语句若使用了函数，则视图中的数据是不能修改的，会直接报错，但是
    我们不这样干（视图不是为了方便你更改数据的）。

视图中的select语句未使用了函数

## 需求：查询出employee表中员工的姓名、性别、年龄
mysql> select * from employee;
+----+-----------+--------+-----+--------+
| id | name      | sex    | age | dep_id |
+----+-----------+--------+-----+--------+
|  1 | 王保长    | male   |  18 |    200 |
|  2 | 刘墉      | female |  48 |    201 |
|  3 | 喜来乐    | male   |  38 |    201 |
|  4 | 李保田    | female |  28 |    202 |
|  5 | 刘常明    | male   |  18 |    200 |
+----+-----------+--------+-----+--------+
5 rows in set (0.00 sec)


mysql> select name,sex,age from employee;
+-----------+--------+-----+
| name      | sex    | age |
+-----------+--------+-----+
| 王保长    | male   |  18 |
| 刘墉      | female |  48 |
| 喜来乐    | male   |  38 |
| 李保田    | female |  28 |
| 刘常明    | male   |  18 |
+-----------+--------+-----+
5 rows in set (0.00 sec)


## 将需求放在视图v_employee_1视图中
create view v_employee_1
	as 
		select name,sex,age from employee;  /* select语句是没有使用函数和连接查询的哈 */


## 查看视图v_employee_1中的数据
mysql> select * from v_employee_1;
+-----------+--------+-----+
| name      | sex    | age |
+-----------+--------+-----+
| 王保长    | male   |  18 |
| 刘墉      | female |  48 |
| 喜来乐    | male   |  38 |
| 李保田    | female |  28 |
| 刘常明    | male   |  18 |
+-----------+--------+-----+
5 rows in set (0.00 sec)


## 删除视图v_employee_1中的数据(name=“王保长”)，看是否成功,再看基表employee表中的数据
mysql> select * from v_employee_1;   # 查看视图v_employee_1中的数据
+-----------+--------+-----+
| name      | sex    | age |
+-----------+--------+-----+
| 王保长    | male   |  18 |
| 刘墉      | female |  48 |
| 喜来乐    | male   |  38 |
| 李保田    | female |  28 |
| 刘常明    | male   |  18 |
+-----------+--------+-----+
5 rows in set (0.00 sec)

mysql> delete from v_employee_1 where name="王保长";  # 删除视图v_employee_1中的数据
Query OK, 1 row affected (0.00 sec)

mysql> select * from v_employee_1;                    # 查看视图v_employee_1中的数据
+-----------+--------+-----+
| name      | sex    | age |
+-----------+--------+-----+
| 刘墉      | female |  48 |
| 喜来乐    | male   |  38 |
| 李保田    | female |  28 |
| 刘常明    | male   |  18 |
+-----------+--------+-----+
4 rows in set (0.00 sec)   <== 可以看出视图v_employee_1中name=“王保长”的记录也不存在


mysql> select * from employee;  # 查看基表employee表中的数据
+----+-----------+--------+-----+--------+
| id | name      | sex    | age | dep_id |
+----+-----------+--------+-----+--------+
|  2 | 刘墉      | female |  48 |    201 |
|  3 | 喜来乐    | male   |  38 |    201 |
|  4 | 李保田    | female |  28 |    202 |
|  5 | 刘常明    | male   |  18 |    200 |
+----+-----------+--------+-----+--------+
4 rows in set (0.00 sec) <== 可以看出基表employee中name=“王保长”的记录也不存在了


## 删除视图v_employee_1
mysql> drop view v_employee_1;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables like "v_employee_1";
Empty set (0.00 sec)

## 总结
01:视图中的select语句没用到函数,视图中的数据是可改变的,同时它的基表中的数据也会改变。
02：but我们不这样干，因为视图是为了方便查询，而不是为了方便数据更改。

2.3 视图的实践二(select结果集来自于多表)

## 需求：统计出技术部的员工信息(姓名、性别、年龄、部门名)
select
	employee.name,
  employee.sex,
  employee.age,
  department.name
from
	employee inner join department
on
  employee.dep_id=department.id
where
  department.name="技术"
;

+-----------+------+-----+--------+
| name      | sex  | age | name   |
+-----------+------+-----+--------+
| 刘常明    | male |  18 | 技术   |
+-----------+------+-----+--------+
1 row in set (0.00 sec)


## 将需求的结果放在视图v_employee2department_1中
create view v_employee2department_1
  as 
	select
		employee.name,
	  employee.sex,
	  employee.age,
	  department.name
	from
		employee inner join department
	on
	  employee.dep_id=department.id
	where
	  department.name="技术"
	;    /* select语句中用到了连接(内连接inner join)查询 */

   PS：会报错错：ERROR 1060 (42S21): Duplicate column name 'name'

create view v_employee2department_1
  as 
	select
		employee.name,
	  employee.sex,
	  employee.age,
	  department.name as "d_name"    /*这里取了个别名 */
	from
		employee inner join department
	on
	  employee.dep_id=department.id
	where
	  department.name="技术"
	; /* select语句中用到了连接(内连接inner join)查询 */

   PS：解决了上面报错


## 查看视图v_employee2department_1中的数据
mysql> show tables like "v_employee2department_1";
+------------------------------------------+
| Tables_in_lili (v_employee2department_1) |
+------------------------------------------+
| v_employee2department_1                  |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from v_employee2department_1;
+-----------+------+-----+--------+
| name      | sex  | age | d_name |
+-----------+------+-----+--------+
| 刘常明    | male |  18 | 技术   |
+-----------+------+-----+--------+
1 row in set (0.00 sec)


## 删除v_employee2department_1视图中的数据(name="刘常明")
mysql> select * from v_employee2department_1;   # 查看视图中的数据
+-----------+------+-----+--------+
| name      | sex  | age | d_name |
+-----------+------+-----+--------+
| 刘常明    | male |  18 | 技术   |
+-----------+------+-----+--------+
1 row in set (0.00 sec)

mysql> delete from v_employee2department_1 where name="刘常明";  # 删除视图中的数据
ERROR 1395 (HY000): Can not delete from join view 'lili.v_employee2department_1'
   ^== 报错:无法从联接视图“lili.v_employee2department_1”中删除

mysql> select * from v_employee2department_1;       # 视图中的数据还是存在的
+-----------+------+-----+--------+
| name      | sex  | age | d_name |
+-----------+------+-----+--------+
| 刘常明    | male |  18 | 技术   |
+-----------+------+-----+--------+
1 row in set (0.00 sec)

mysql> select * from employee where name="刘常明";  # 基表中的数据也是存在的
+----+-----------+------+-----+--------+
| id | name      | sex  | age | dep_id |
+----+-----------+------+-----+--------+
|  5 | 刘常明    | male |  18 |    200 |
+----+-----------+------+-----+--------+
1 row in set (0.00 sec)

## 删除视图v_employee2department_1
mysql> drop view v_employee2department_1;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables like "v_employee2department_1";
Empty set (0.00 sec)

## 总结
视图中的select语句中若使用了连接查询，那么视图中的数据是不可改变的，若改变会直接报错。
当然我们不这样干，视图是为了方便查询，而不是为了方便数据更改。

2.4 视图的总结

01：视图是来自于select的结果集(字段名不能重复,可对重复的字段名取别名);

02：视图在操作系统上就是一个表结构文件.frm，没有数据文件.idb，当mysql实例启动后，
    视图会根据它关联的select语句去基表找数据;
    
03：视图是为了方便数据的查询,而不是方便数据的更改;        ## 五颗星*****

04：基表中的数据变化了,视图中的数据也会变化，            ## 看"03"说明点

05：视图中的数据是可以改变的,但有条件，且我们不这样干；    ## 看"03"说明点
    A：视图中的关联select语句若有使用函数或者连接查询，修改视图中的数据会直接报错;
    B：视图中的关联select语句若没有使用函数或者连接查询,修改视图中的数据会成功,基表中的数据也会变化；

视图的创建语法

## 通过help create view得到的
CREATE
    [OR REPLACE]                            # 若视图存存，则进行替换
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]   # 这个是算法
    [DEFINER = user]                        # 用户：你用哪个用户创建的就是哪个用户
    [SQL SECURITY { DEFINER | INVOKER }]    # SQL安全,默认你的sql_mode
    VIEW view_name [(column_list)]          # 视图名
    AS select_statement                     # 关联select语句
    [WITH [CASCADED | LOCAL] CHECK OPTION]  

## 基本使用的语法
create view   [视图名]  as  [select 语句];

查看视图是否存在

## 第一种方法：查看某个业务库下是否有视图
select
  *
from
  information_schema.tables
where
  table_schema="chenliang" and table_type="view"
;      /* 这里是查看chenliang业务库下是否有视图(view) */


## 第二种方法：查看某个业务库下是否有视图
利用客户端工具(SQLyog、naicat)连接到mysql实例实例,点开相应业务库下的"视图"图标，参考1.1章节


## 第三种方法：例如：查看chenliang库下的test1是不是视图
select
	table_schema as "库名",
	table_name as "表名",
	table_type as "表类型"      /* 结果中：若该字段的值是view,就表示是视图,反之. */
from
	information_schema.tables
where
	table_schema="chenliang" and table_name= "test1"
;

查看视图的创建语句

show create view [视图名]；

*查看视图中的数据

select * from 视图名

更改视图中关联的select语句

alter view 视图名   as   新的select语句

删除视图

drop view 视图名