mysql 创建 或删除视图_MySQL学习笔记--视图的创建，删除，修改，算法

/*视图*/--视图不占用物理空间

/*表和视图共享数据库中相同的名称空间，因此，数据库不能包含具有相同名称的表和视图*/

/*视图缩减业务逻辑 http://blog.itpub.net/28194062/viewspace-772902/

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

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

注意：

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

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

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

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

5.   视图支持嵌套，也就是说可以利用其他视图检索出来的数据创建新的视图

5.   在视图中可以使用 OREDR BY，但是如果视图内已经使用该排序子句，则视图的ORDER BY 将覆盖前面的 ORDER BY。

6.   视图不能索引，也不能关联触发器或默认值。

7    视图可以和表同时使用

*/

--检查用户是否拥有select权限和create_view权限

select select_priv,create_view_priv from mysql.user where user ='guest'

--创建普通视图

create view department_view1 as select * from department --从department中选取数据直接定义到视图department_view1中

--创建普通视图并自定义字段名

create view department_view2(name,function,location) --创建视图department_view2包含name,function,location字段

as select d_name,function,address from department --从department表中分别select三个字段的数据到视图的对应三个字段中

/*视图的执行算法：

存在两种执行算法：

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

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

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

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

对于TEMPTABLE，视图的结果将被置于临时表中，然后使用它执行语句。(临时表无法随时更新数据？)

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

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

*/

--补坑

CREATE  TABLE  worker (

id  INT(4)  NOT NULL  UNIQUE  PRIMARY KEY  AUTO_INCREMENT,

num  INT(10)  NOT NULL  UNIQUE ,

d_id  INT(4) ,

name  VARCHAR(20)  NOT NULL ,

sex  VARCHAR(4)  NOT NULL ,

birthday  DATE ,

address  VARCHAR(50) ,

CONSTRAINT  worker_fk  FOREIGN KEY (d_id)

REFERENCES  department (d_id)

);--只找到表定义语句，没有找到表中数据，看来没法实验了

--在多表上创建视图

create algorithm = merge view -- 创建合并执行算法的视图，现用现查？等会试试看

worker_view1(name,department,sex,age,address) --视图名为worker_view1，包含name,department,sex,age,address字段

as --少了as一直报错

select name,department,d_name,sex,2009-birthday,address --选取name,department等列，并将计算2009-birthday的值作为字段

from worker,department where worker.d_id=department.d_id--报错没有worker表，补坑

with local check option;--local参数是更新视图时只考虑此视图的约束，不考虑数据来源或父视图的约束，cascaded更新时考虑满足所有相关视图和表的条件(默认)

--查看视图

--查看视图定义

describe worker_view1

--查看视图基本信息

show table status like 'worker_view1' --comment项为view，说明是视图，engine等其他项为空，说明是虚拟表

--查看视图详细信息

show create view worker_view1

--输出如下

CREATE ALGORITHM=MERGE 

DEFINER=`root`@`localhost` --与表定义语句不同的是，这里出现了被谁定义(创建)的信息

SQL SECURITY DEFINER VIEW

`worker_view1`

AS

select `worker`.`name` AS `name`,

`department`.`d_name` AS `department`,

`worker`.`sex` AS `sex`,

(2009 - `worker`.`birthday`) AS `age`,

`worker`.`address` AS `address`

from (`worker` join `department`)

where (`worker`.`d_id` = `department`.`d_id`)

WITH LOCAL CHECK OPTION

---修改视图

--使用create or replace view语句，||不仅可以修改已经存在的视图，也可以创建新视图，优选create or replace方式

describe department_view1--查看原来的定义

create or replace algorithm=temptable view department_view1(department,function,location)

as select d_name,function,address

from department

describe department_view1--查看现在的定义,只剩三个字段了

--使用alter语句||只可以修改已经存在的语句

alter view department_view2(department,name,sex,location)

as select d_name,name,worker.sex,department.address

from department,worker where department.d_id=worker.d_id

with check option

---更新视图

--是指通过视图来Insert，update，delete表中的数据--→视图是一个虚拟表--→没有数据--→转换到源数据的表中进行更新

create view department_view3(name,function,address)

as select d_name,function,address from department where d_id=1001;

--向视图中更新一条记录

update department_view3 set name='科研部',function='新产品研发',address='3号楼5层'

select * from department --源表中id=1001的数据也被修改

--对视图的更新最后都是实现在基本表上的

--但除了一下几种情况

--1，视图中包含sum()、count()、max()、min()等统计函数,如

create view worker_view4(name,sex,total)

as select name,sex,count(name) from worker

--2,视图中包含union(合并select结果时去除重复值),union all(不去除),distinct,group by,having等关键字

create view worker_view4(name,sex,address)

as select name,sex,homeaddress from worker

group by d_id

--3,常量视图

create view worker_view6

as select 'Aric' as name

--4,视图的select中包含子查询

create view worker_view7

as select(select name from worker)--为啥没有from子句也行？--实验结果：无法从此视图中查询数据，报错，正确写法应该是下面的实验

--查看下view创建语句

show create view worker_view7 --还是不明白

--实验

use sakila

create view worker_view8 as

select* from (select last_name from customer ) a

select * from worker_view8

--5,由不可更新视图创造出的视图同样不可更新，algorithm=temptable，临时表算法的视图也同样不可更新

--6,with local check option选项的视图更新时只需考虑本视图的约束不需考虑上级视图的约束

--7,with cascaded check option选项的视图更新时需考虑本视图与上级所有视图的约束

---删除视图

drop view if exists worker_view1   --删除列举出来的视图，如果列举的视图存在就删除

drop view if exists worker_view1,worker_view2 --可以一次删除多个视图，存在几个就删除几个