关于用mysql创建视图以及union all 和group by相关介绍

最新推荐文章于 2024-04-11 16:28:11 发布

转载最新推荐文章于 2024-04-11 16:28:11 发布 · 2.8k 阅读

2 ·

本内容遵循CC 4.0 BY-SA版权协议

原文链接：https://my.oschina.net/u/2479387/blog/871531

标签

#数据库

本文详细介绍了MySQL中的视图概念，包括视图的含义、不可更新的视图情况以及创建、修改和删除视图的操作。此外，还讨论了UNION ALL的用法，包括其语法、注意事项和与GROUP BY结合的应用。通过实例展示了如何创建和使用视图，以及UNION ALL在消除重复行和分组统计中的角色。

一.关于视图

1.视图的含义：

视图就是一个存在于数据库中的虚拟表。

视图本身没有数据，只是通过执行相应的select语句完成获得相应的数据。

2.不可更新的视图：

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

· 聚合函数（SUM(), MIN(), MAX(), COUNT()等）。

· DISTINCT

· GROUP BY

· HAVING

· UNION或UNION ALL

· 位于选择列表中的子查询

· Join

· FROM子句中的不可更新视图

· WHERE子句中的子查询，引用FROM子句中的表。

· 仅引用文字值（在该情况下，没有要更新的基本表）。

· ALGORITHM = TEMPTABLE（使用临时表总会使视图成为不可更新的）。

3.创建视图：

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

MySQL> create view v_emp as select empno,ename,job from emp;

Query OK, 0 rows affected (0.00 sec)

4.删除视图：

Drop view view_name;

5.修改视图：

mysql> alter view v_emp as select empno,ename,job,deptno from emp;

Query OK, 0 rows affected (0.01 sec)

修改视图结构，即修改所使用的字段的名称（可以隐含基表的字段名称）：

mysql> alter view v_emp(v1,v2,v3,v4) as select empno,ename,job,deptno from emp;

Query OK, 0 rows affected (0.00 sec)

6.视图缩减业务逻辑

视图用来隐藏复杂的业务逻辑，从join连接查询产生一个view。先使用视图完成一定的逻辑，再在视图的基础上完成另外的逻辑。

通常，视图完成的逻辑都是相对比较基础的逻辑。

7.视图的执行算法：

存在两种执行算法：

1、 Merge：合并的执行方式，每当执行的时候，先将我们视图的sql语句与外部查询视图的sql语句，混合在一起，最终执行；

2、 Temptable：临时表模式，每当查询的时候，将视图所使用的select语句生成一个结果的临时表，再在当前的临时表内进行查询。

指的是一个视图是在什么时候执行，依据哪些方式执行；

对于MERGE，会将引用视图的语句的文本与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分。

对于TEMPTABLE，视图的结果将被置于临时表中，然后使用它执行语句。

对于UNDEFINED，MySQL将选择所要使用的算法。如果可能，它倾向于MERGE而不是TEMPTABLE，这是因为MERGE通常更有效，而且如果使用了临时表，视图是不可更新

当用户创建视图时，mysql默认使用一种undefine的处理算法，就是会自动在合并和临时表内进行选择。

注意：

1、尽量使用视图完成读操作

2、如果使用视图，则需要注意，对视图的修改，也是对基表的修改，会即时生效；

3、删除视图时，不会销毁实体表内的数据

4、如果大家做的是外部接口，一个数据库多个应用，针对每一个应用，采用不同的视图接口。

mysql中使用视图和union all常出现的问题：

mysql> select * from test_main;

+----+-------+

| id | value |

+----+-------+

| 1 | ONE |

| 2 | TWO |

| 3 | THREE |

+----+-------+

3 rows in set (0.00 sec)

mysql> create table test_main2 select * from test_main;

Query OK, 3 rows affected (0.04 sec)

Records: 3 Duplicates: 0 Warnings: 0

-- 创建视图， sql 语句加了括号，报错。

CREATE VIEW view_test_main

(

SELECT * FROM test_main

UNION ALL

SELECT * FROM test_main2

);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that

corresponds to your MySQL server version for the right syntax to use near 'UNION

ALL

SELECT * FROM test_main2

)' at line 5

-- 创建视图， sql 语句不加括号，通过。

mysql> CREATE VIEW view_test_main

-> AS

-> SELECT * FROM test_main

-> UNION ALL

-> SELECT * FROM test_main2;

Query OK, 0 rows affected (0.01 sec)

二.关于union
一、UNION和UNION ALL的作用和语法
UNION 用于合并两个或多个 SELECT 语句的结果集，并消去表中任何重复行。
UNION 内部的 SELECT 语句必须拥有相同数量的列，列也必须拥有相似的数据类型。
同时，每条 SELECT 语句中的列的顺序必须相同.
SQL UNION 语法：
sql脚本代码如下:
SELECT column_name FROM table1
UNION
SELECT column_name FROM table2
注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行
SQL UNION ALL 语法
sql脚本代码如下:
SELECT column_name FROM table1
UNION ALL
SELECT column_name FROM table2
注释：另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。
注意：1、UNION 结果集中的列名总是等于第一个 SELECT 语句中的列名
2、UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同
二、union的用法及注意事项
union:联合的意思，即把两次或多次查询结果合并起来。
要求：两次查询的列数必须一致
推荐：列的类型可以不一样，但推荐查询的每一列，想对应的类型以一样
可以来自多张表的数据：多次sql语句取出的列名可以不一致，此时以第一个sql语句的列名为准。
如果不同的语句中取出的行，有完全相同(这里表示的是每个列的值都相同)，那么union会将相同的行合并，最终只保留一行。也可以这样理解，union会去掉重复的行。
如果不想去掉重复的行，可以使用union all。
如果子句中有order by,limit，需用括号()包起来。推荐放到所有子句之后，即对最终合并的结果来排序或筛选。
如：
sql脚本代码如下:
(select * from a order by id) union (select * from b order id);
在子句中，order by 需要配合limit使用才有意义。如果不配合limit使用，会被语法分析器优化分析时去除。