一.关于视图
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*fromtest_main;
+----+-------+
|id|value|
+----+-------+
|1|ONE|
|2|TWO|
|3|THREE|
+----+-------+
3rowsinset(0.00sec)
mysql>createtabletest_main2select*fromtest_main;
QueryOK,3rowsaffected(0.04sec)
Records:3Duplicates:0Warnings:0
--创建视图,sql语句加了括号,报错。
CREATEVIEWview_test_main
AS
(
SELECT*FROMtest_main
UNIONALL
SELECT*FROMtest_main2
);
ERROR1064(42000):YouhaveanerrorinyourSQLsyntax;checkthemanualthat
correspondstoyourMySQLserverversionfortherightsyntaxtousenear'UNION
ALL
SELECT*FROMtest_main2
)'atline5
--创建视图,sql语句不加括号,通过。
mysql>CREATEVIEWview_test_main
->AS
->SELECT*FROMtest_main
->UNIONALL
->SELECT*FROMtest_main2;
QueryOK,0rowsaffected(0.01sec)
二.关于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使用,会被语法分析器优化分析时去除。
三.关于group by
group by语法可以根据给定数据列的每个成员对查询结果进行分组统计,最终得到一个分组汇总表。
SELECT子句中的列名必须为分组列或列函数。列函数对于GROUP BY子句定义的每个组各返回一个结果。
注意:在SELECT语句中指定的每个列名也在GROUP BY子句中提到。未在这两个地方提到的列名将产生错误。
在GROUP BY子句之后使用HAVING子句
可应用限定条件进行分组,以便系统仅对满足条件的组返回结果。为此,在GROUP BY子句后面包含一个HAVING子句。HAVING子句可包含一个或多个用AND和OR连接的谓词。
group by + group_concat()
(1) group_concat(字段名)可以作为一个输出字段来使用,
(2) 表示分组之后,根据分组结果,使用group_concat()来放置每一组的某字段的值的集合。
group by + 集合函数
(1) 通过group_concat()的启发,我们既然可以统计出每个分组的某字段的值的集合,那么我们也可以通过集合函数来对这个"值的集合"做一些操作
#group by + having
(1) having 条件表达式:用来分组查询后指定一些条件来输出查询结果
(2) having作用和where一样,但having只能用于group by
group by + with rollup
(1) with rollup的作用是:在最后新增一行,来记录当前列里所有记录的总和