MySQL 视图视图一个虚拟表，是从一个或多个表中导出来的表，是一种虚拟存在的表，视图就像一个窗口，通过这个窗口可以看到系统专门提供的数据，这样，用户可以不用看到整个数据库表中的数据，而只关心对自已有用的数据
数据库只存放视图的定义，并没有存放视图中的数据
view视图的帮助信息：
mysql>? view
alter view
create view
drop view
查看视图
mysql>show tables;
删除视图
mysql>drop view v_t1
mysql视图
创建视图
mysql> create view v_t1 as select *from t1 where id>4 and id<11;
Query OK, 0 rows affected (0.00 sec)
view视图的帮助信息
mysql> ? view
ALTER VIEW
CREATE VIEW
DROP VIEW
查看视图
mysql> show tables;
删除视图v_t1
mysql> drop view v_t1;
创建视图
语法；
create[or replace] [algorithm={undefined|merge|temptable}] view 视图名 [{属性清单}] as select 语句 [with [cascaded|local]check option];
查看系统看是否有建立和查看视图的权限
mysql>select select_priv,create_view_priv from mysql.user whereuser='root'
在给定的数据库中创建视图：db_name.view_name
在单表的创建视图
mysql>create view v_t1 as select * from t1 where id>4 andid<11;
mysql> create view department_view1 as select * from department;
Query OK, 0 rows affected (0.01 sec)
mysql> desc department_view1;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| d_id | int(4) |NO | | 0 | |
| d_name | varchar(20) | NO | | NULL | |
| function | varchar(50) | YES | | NULL | |
| address | varchar(50) | YES | |NULL | |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
通过修改查询列来与源表区分开来
mysql> create view department_view2(name,function,location)
-> as selectd_name,function,address
-> from department;
Query OK, 0 rows affected (0.01 sec)
mysql> desc department_view2;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name | varchar(20) | NO | | NULL | |
| function | varchar(50) | YES | | NULL | |
| location | varchar(50) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
在多表上创建视图
mysql> create algorithm=merge view
-> worker_view1(name,department,sex,age,address)
-> as selectname,department.d_name,sex,birthday,address
-> from worker,departmentwhere worker.d_id=department.d_id
-> with local check option;
Query OK, 0 rows affected (0.01 sec)
mysql> desc worker_view1;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| name | varchar(20) |YES | | NULL | |
| department | varchar(20) | NO | | NULL | |
| sex | varchar(4) | YES | | NULL | |
| age | datetime | YES | | NULL | |
| address | varchar(50) |YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
查看视图
查看视图必须要有show view 权限，权限保存在user表中
方法包括：describe,show table status,show create view
describe 视图名
mysql> desc worker_view1;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| name | varchar(20) |YES | | NULL | |
| department | varchar(20) | NO | | NULL | |
| sex | varchar(4) | YES | | NULL | |
| age | datetime | YES | | NULL | |
| address | varchar(50) |YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
show table status like '视图名'
mysql> show table status like 'worker_view1'\G
*************************** 1. row ***************************
Name: worker_view1
Engine: NULL //说明这不是普通表，是一张虚表，
Version: NULL
Row_format: NULL
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: NULL
Checksum: NULL
Create_options: NULL
Comment: VIEW //说明该表为视图
1 row in set (0.01 sec)
其它信息为NULL说明这是一个虚表
下面是普通表
mysql> show table status like 'department'\G
*************************** 1. row ***************************
Name: department
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 1
Avg_row_length: 44
Data_length: 44
Max_data_length: 281474976710655
Index_length: 2048
Data_free: 0
Auto_increment: NULL
Create_time: 2012-09-0615:23:22
Update_time: 2012-09-0615:25:57
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment: //这就是视图和表的区别
1 row in set (0.01 sec)
show create view 视图名
查看视图详细定义:
mysql> show create view worker_view1 \G;
*************************** 1. row ***************************
View: worker_view1
Create View: CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQLSECURITY DEFINER VIEW `worker_view1` AS select `worker`.`name` AS`name`,`department`.`d_name` AS `department`,`worker`.`sex` AS`sex`,`worker`.`birthday` AS `age`,`department`.`address` AS `address` from(`worker` join `department`) where (`worker`.`d_id` = `department`.`d_id`) WITHLOCAL CHECK OPTION
1 row in set (0.01 sec)
ERROR:
No query specified
查看information_schem数据库下的view表
所有定义的视图都可以在这里查看到
mysql> select * from information_schema.views \G;
***************************1. row ***************************
TABLE_CATALOG: NULL
TABLE_SCHEMA: company
TABLE_NAME:department_view1
VIEW_DEFINITION:select `company`.`department`.`d_id` AS `d_id`,`company`.`department`.`d_name`AS `d_name`,`company`.`department`.`function` AS`function`,`company`.`department`.`address` AS `address` from`company`.`department`
CHECK_OPTION: NONE
IS_UPDATABLE: YES
DEFINER: root@localhost
SECURITY_TYPE: DEFINER
修改视图
create or replace view修改视图
语法；
create [or replace] [algorithm={undefined|merge|temptable}] view 视图名 [{属性清单}] as select 语句 [with [cascaded|local]check option];
mysql> desc department_view1;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| d_id | int(4) | NO | | 0 | |
| d_name | varchar(20) | NO | | NULL | |
| function | varchar(50) | YES | | NULL | |
| address | varchar(50) |YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> create or replace algorithm=temptable
-> viewdepartment_view1(department,function,localtion)
-> as selectd_name,function,address from department;
Query OK, 0 rows affected (0.00 sec)
mysql> desc department_view1;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| department | varchar(20) | NO | | NULL | |
| function | varchar(50) |YES | | NULL | |
| localtion | varchar(50) |YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
alter 语句修改视图
语法；
alter [algorithm={undefined|merge|temptable}] view 视图名 [{属性清单}] as select 语句 [with [cascaded|local]check option];
mysql>desc department_view2;
+----------+-------------+------+-----+---------+-------+
|Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
|name | varchar(20) | NO | | NULL | |
|function | varchar(50) | YES | | NULL | |
|location | varchar(50) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rowsin set (0.01 sec)
mysql>alter view department_view2(department,name,sex,localtion)
-> as selectd_name,worker.name,worker.sex,address
-> from department,worker wheredepartment.d_id=worker.d_id
->with check option;
QueryOK, 0 rows affected (0.00 sec)
mysql>desc department_view2;
+------------+-------------+------+-----+---------+-------+
|Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
|department | varchar(20) | NO | | NULL | |
|name | varchar(20) | YES | |NULL | |
|sex | varchar(4) | YES | | NULL | |
|localtion | varchar(50) | YES | |NULL | |
+------------+-------------+------+-----+---------+-------+
4 rowsin set (0.00 sec)
更新视图：
UPDATE语句更新的是视图，但实际上更新的是实体表,方法：insert,update,delete
表中含有如下函数，视图将不能更新
sum(),count(),max(),min()
如下关键字：
union,unionall,distince,group by havig
常量视图：
eg:createview worker_view6 as select 'aric' as name;
视图中包含子查询
eg:createview worker_view7 (name) as select (select name from worker);
由不可更新的视图导出的视图；
eg:createview worker_view8 as select * from worker_view7;
worker_view7是不可更新的视图，那么worker_view8也是不可更新的视图
创建视图时，algorithm 为temptable类型的也不可更新
eg:createalgorithm=temptable view worker_view9 as select * from worker;
with [cascaded|local] check option也将决定视图是否能更新
cascaded：参数表示更新视图时要满足所有相关视图和表的条件
local：参数表示更新视图要满足该视力本身的定义条件就可以
实际中，最好不用更新视图来更新主表，这样会出错
mysql>select * from department;
+------+--------+----------+----------------------+
| d_id| d_name | function | address |
+------+--------+----------+----------------------+
| 1 | rsc | worker | 2th,dongfeng,beijing|
+------+--------+----------+----------------------+
1 rowin set (0.01 sec)
mysql>update department_view3 set name='rscpass',function='jobs',addredd='3th';
QueryOK, 1 row affected (0.00 sec)
Rowsmatched: 1 Changed: 1 Warnings: 0
mysql>select * from department;
+------+---------+----------+---------+
| d_id| d_name | function | address |
+------+---------+----------+---------+
| 1 | rscpass | jobs | 3th |
+------+---------+----------+---------+
1 rowin set (0.00 sec)
删除视图：
删除视图只会删除视图的定义，不会删除数据
语法：
drop view [if exists ] 视图名列表 [restrict|cascade]
mysql> drop view if exists worker_view1;
QueryOK, 0 rows affected (0.00 sec)
mysql>show tables;
+-------------------+
|Tables_in_company |
+-------------------+
|department |
|department_view1 |
|department_view2 |
|department_view3 |
|worker |
+-------------------+
5 rowsin set (0.01 sec)
删除视图中的一条记录：
deletefrom view_t2 where price=5;
