1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
<code> </code><code>/*视图*/</code><code>--视图不占用物理空间</code>
<code>/*表和视图共享数据库中相同的名称空间，因此，数据库不能包含具有相同名称的表和视图*/</code>
<code>/*视图缩减业务逻辑 http://blog.itpub.net/28194062/viewspace-772902/</code>
<code>视图用来隐藏复杂的业务逻辑，从</code><code>join</code><code>连接查询产生一个</code><code>view</code><code>。先使用 视图完成一定的逻辑，再在视图的基础上完成另外的逻辑。</code>
<code>通常，视图完成的逻辑都是相对比较基础的逻辑。</code>
<code> </code><code>注意：</code>
<code>1、 尽量使用视图完成读操作</code>
<code>2、 如果使用视图，则需要注意，对视图的修改，也是对基表的修改，会即时生效；</code>
<code>3、 删除视图时，不会销毁实体表内的数据</code>
<code>4、 如果大家做的是外部接口，一个数据库多个应用，针对每一个应用，采用不同的视图接口。</code>
<code>5. 视图支持嵌套，也就是说可以利用其他视图检索出来的数据创建新的视图</code>
<code>5. 在视图中可以使用 OREDR </code><code>BY</code><code>，但是如果视图内已经使用该排序子句，则视图的</code><code>ORDER</code> <code>BY</code> <code>将覆盖前面的 </code><code>ORDER</code> <code>BY</code><code>。</code>
<code>6. 视图不能索引，也不能关联触发器或默认值。</code>
<code>7 视图可以和表同时使用</code>
<code>*/</code>
<code>--检查用户是否拥有select权限和create_view权限</code>
<code>select</code> <code>select_priv,create_view_priv </code><code>from</code> <code>mysql.</code><code>user</code> <code>where</code> <code>user</code> <code>=</code><code>'guest'</code>
<code>--创建普通视图</code>
<code>create</code> <code>view</code> <code>department_view1 </code><code>as</code> <code>select</code> <code>* </code><code>from</code> <code>department </code><code>--从department中选取数据直接定义到视图department_view1中</code>
<code>--创建普通视图并自定义字段名</code>
<code>create</code> <code>view</code> <code>department_view2(</code><code>name</code><code>,</code><code>function</code><code>,location) </code><code>--创建视图department_view2包含name,function,location字段</code>
<code>as</code> <code>select</code> <code>d_name,</code><code>function</code><code>,address </code><code>from</code> <code>department </code><code>--从department表中分别select三个字段的数据到视图的对应三个字段中</code>
<code>/*视图的执行算法：</code>
<code>存在两种执行算法：</code>
<code>1、 Merge：合并的执行方式，每当执行的时候，先将我们视图的sql语句与外部查询视图的sql语句，混合在一起，最终执行；</code>
<code>2、 Temptable：临时表模式，每当查询的时候，将视图所使用的</code><code>select</code><code>语句生成一个结果的临时表，再在当前的临时表内进行查询。</code>
<code>指的是一个视图是在什么时候执行，依据哪些方式执行；</code>
<code>对于MERGE，会将引用视图的语句的文本与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分。(现用现查？)</code>
<code> </code>
<code>对于TEMPTABLE，视图的结果将被置于临时表中，然后使用它执行语句。(临时表无法随时更新数据？)</code>
<code>对于UNDEFINED，MySQL将选择所要使用的算法。如果可能，它倾向于MERGE而不是TEMPTABLE，这是因为MERGE通常更有效，而且如果使用了临时表，视图是不可更新</code>
<code>当用户创建视图时，mysql默认使用一种undefine的处理算法，就是会自动在合并和临时表内进行选择。</code>
<code> </code><code>*/</code>
<code> </code><code>--补坑</code>
<code>CREATE</code> <code>TABLE</code> <code>worker ( </code>
<code>id </code><code>INT</code><code>(4) </code><code>NOT</code> <code>NULL</code> <code>UNIQUE</code> <code>PRIMARY</code> <code>KEY</code> <code>AUTO_INCREMENT,</code>
<code>num </code><code>INT</code><code>(10) </code><code>NOT</code> <code>NULL</code> <code>UNIQUE</code> <code>,</code>
<code>d_id </code><code>INT</code><code>(4) ,</code>
<code>name</code> <code>VARCHAR</code><code>(20) </code><code>NOT</code> <code>NULL</code> <code>,</code>
<code>sex </code><code>VARCHAR</code><code>(4) </code><code>NOT</code> <code>NULL</code> <code>,</code>
<code>birthday </code><code>DATE</code> <code>,</code>
<code>address </code><code>VARCHAR</code><code>(50) ,</code>
<code>CONSTRAINT</code> <code>worker_fk </code><code>FOREIGN</code> <code>KEY</code> <code>(d_id)</code>
<code>REFERENCES</code> <code>department (d_id)</code>
<code>);</code><code>--只找到表定义语句，没有找到表中数据，看来没法实验了</code>
<code> </code>
<code>--在多表上创建视图</code>
<code>create</code> <code>algorithm = merge </code><code>view</code> <code>-- 创建合并执行算法的视图，现用现查？等会试试看</code>
<code>worker_view1(</code><code>name</code><code>,department,sex,age,address) </code><code>--视图名为worker_view1，包含name,department,sex,age,address字段</code>
<code>as</code> <code>--少了as一直报错</code>
<code>select</code> <code>name</code><code>,department,d_name,sex,2009-birthday,address </code><code>--选取name,department等列，并将计算2009-birthday的值作为字段</code>
<code>from</code> <code>worker,department </code><code>where</code> <code>worker.d_id=department.d_id</code><code>--报错没有worker表，补坑</code>
<code>with</code> <code>local</code> <code>check</code> <code>option</code><code>;</code><code>--local参数是更新视图时只考虑此视图的约束，不考虑数据来源或父视图的约束，cascaded更新时考虑满足所有相关视图和表的条件(默认)</code>
<code>--查看视图</code>
<code>--查看视图定义</code>
<code>describe worker_view1 </code>
<code>--查看视图基本信息</code>
<code>show </code><code>table</code> <code>status </code><code>like</code> <code>'worker_view1'</code> <code>--comment项为view，说明是视图，engine等其他项为空，说明是虚拟表</code>
<code>--查看视图详细信息</code>
<code>show </code><code>create</code> <code>view</code> <code>worker_view1 </code>
<code>--输出如下</code>
<a href="http://s4.51cto.com/wyfs02/M02/80/18/wKiom1c3FJmCN1mFAACgbUmVSfg191.png" target="_blank"></a>
<code>CREATE</code> <code>ALGORITHM=MERGE </code>
<code>DEFINER=`root`@`localhost` </code><code>--与表定义语句不同的是，这里出现了被谁定义（创建）的信息</code>
<code>SQL SECURITY DEFINER </code><code>VIEW</code>
<code>`worker_view1` </code>
<code>AS</code>
<code> </code><code>select</code> <code>`worker`.`</code><code>name</code><code>` </code><code>AS</code> <code>`</code><code>name</code><code>`,</code>
<code> </code><code>`department`.`d_name` </code><code>AS</code> <code>`department`,</code>
<code> </code><code>`worker`.`sex` </code><code>AS</code> <code>`sex`,</code>
<code> </code><code>(2009 - `worker`.`birthday`) </code><code>AS</code> <code>`age`,</code>
<code> </code><code>`worker`.`address` </code><code>AS</code> <code>`address` </code>
<code> </code><code>from</code> <code>(`worker` </code><code>join</code> <code>`department`) </code>
<code> </code><code>where</code> <code>(`worker`.`d_id` = `department`.`d_id`) </code>
<code> </code><code>WITH</code> <code>LOCAL</code> <code>CHECK</code> <code>OPTION</code>
<code>---修改视图</code>
<code>--使用create or replace view语句，||不仅可以修改已经存在的视图，也可以创建新视图，优选create or replace方式</code>
<code>describe department_view1</code><code>--查看原来的定义</code>
<code>create</code> <code>or</code> <code>replace</code> <code>algorithm=temptable </code><code>view</code> <code>department_view1(department,</code><code>function</code><code>,location)</code>
<code>as</code> <code>select</code> <code>d_name,</code><code>function</code><code>,address </code>
<code>from</code> <code>department</code>
<code>describe department_view1</code><code>--查看现在的定义,只剩三个字段了</code>
<code>--使用alter语句||只可以修改已经存在的语句</code>
<code>alter</code> <code>view</code> <code>department_view2(department,</code><code>name</code><code>,sex,location)</code>
<code>as</code> <code>select</code> <code>d_name,</code><code>name</code><code>,worker.sex,department.address</code>
<code>from</code> <code>department,worker </code><code>where</code> <code>department.d_id=worker.d_id</code>
<code>with</code> <code>check</code> <code>option</code>
<code>---更新视图</code>
<code> </code><code>--是指通过视图来Insert，update，delete表中的数据--→视图是一个虚拟表--→没有数据--→转换到源数据的表中进行更新</code>
<code> </code><code>create</code> <code>view</code> <code>department_view3(</code><code>name</code><code>,</code><code>function</code><code>,address)</code>
<code> </code><code>as</code> <code>select</code> <code>d_name,</code><code>function</code><code>,address </code><code>from</code> <code>department </code><code>where</code> <code>d_id=1001;</code>
<code> </code><code>--向视图中更新一条记录</code>
<code> </code><code>update</code> <code>department_view3 </code><code>set</code> <code>name</code><code>=</code><code>'科研部'</code><code>,</code><code>function</code><code>=</code><code>'新产品研发'</code><code>,address=</code><code>'3号楼5层'</code>
<code> </code><code>select</code> <code>* </code><code>from</code> <code>department </code><code>--源表中id=1001的数据也被修改</code>
<code> </code><code>--对视图的更新最后都是实现在基本表上的</code>
<code> </code><code>--但除了一下几种情况</code>
<code> </code><code>--1，视图中包含sum()、count()、max()、min()等统计函数,如</code>
<code> </code><code>create</code> <code>view</code> <code>worker_view4(</code><code>name</code><code>,sex,total)</code>
<code> </code><code>as</code> <code>select</code> <code>name</code><code>,sex,</code><code>count</code><code>(</code><code>name</code><code>) </code><code>from</code> <code>worker</code>
<code> </code><code>--2,视图中包含union(合并select结果时去除重复值),union all（不去除）,distinct,group by,having等关键字</code>
<code> </code><code>create</code> <code>view</code> <code>worker_view4(</code><code>name</code><code>,sex,address)</code>
<code> </code><code>as</code> <code>select</code> <code>name</code><code>,sex,homeaddress </code><code>from</code> <code>worker</code>
<code> </code><code>group</code> <code>by</code> <code>d_id</code>
<code> </code><code>--3,常量视图</code>
<code> </code><code>create</code> <code>view</code> <code>worker_view6</code>
<code> </code><code>as</code> <code>select</code> <code>'Aric'</code> <code>as</code> <code>name</code>
<code> </code><code>--4,视图的select中包含子查询</code>
<code> </code><code>create</code> <code>view</code> <code>worker_view7</code>
<code> </code><code>as</code> <code>select</code><code>(</code><code>select</code> <code>name</code> <code>from</code> <code>worker)</code><code>--为啥没有from子句也行？--实验结果：无法从此视图中查询数据，报错，正确写法应该是下面的实验</code>
<code> </code><code>--查看下view创建语句</code>
<code> </code><code>show </code><code>create</code> <code>view</code> <code>worker_view7 </code><code>--还是不明白</code>
<code> </code><code>--实验</code>
<code> </code><code>use sakila</code>
<code> </code><code>create</code> <code>view</code> <code>worker_view8 </code><code>as</code>
<code> </code><code>select</code><code>* </code><code>from</code> <code>(</code><code>select</code> <code>last_name </code><code>from</code> <code>customer ) a</code>
<code> </code><code>select</code> <code>* </code><code>from</code> <code>worker_view8</code>
<code> </code><code>--5,由不可更新视图创造出的视图同样不可更新，algorithm=temptable，临时表算法的视图也同样不可更新</code>
<code> </code><code>--6,with local check option选项的视图更新时只需考虑本视图的约束不需考虑上级视图的约束</code>
<code> </code><code>--7,with cascaded check option选项的视图更新时需考虑本视图与上级所有视图的约束</code>
<code> </code><code>---删除视图</code>
<code> </code><code>drop</code> <code>view</code> <code>if exists worker_view1 </code><code>--删除列举出来的视图，如果列举的视图存在就删除</code>
<code> </code><code>drop</code> <code>view</code> <code>if exists worker_view1,worker_view2 </code><code>--可以一次删除多个视图，存在几个就删除几个</code>
本文转自 angry_frog 51CTO博客，原文链接:http://blog.51cto.com/l0vesql/1773387
![【MySQL数据库】数据库索引事务1.索引2.事务[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)