mysql 视图的创建

概述

MySQL视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

对其中所引用的基础表来说，MySQL视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少。

视图是存储在数据库中的查询的sql 语句，它主要出于两种原因：安全原因，视图可以隐藏一些数据，如：社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等，另一原因是可使复杂的查询易于理解和使用。

创建

create view 视图的名字 as select * from 表名

　　

示例1

sql查询：
select a.name as username, b.name as goodsname from user as a, goods as b, ug as c where a.id=c.userid and c.goodsid=b.id;

创建视图：
create view other as select a.name as username, b.name as goodsname from user as a, goods as b, ug as c where a.id=c.userid and c.goodsid=b.id

　

示例2　

CREATE TABLE employee(  
        ID INT  PRIMARY KEY  AUTO_INCREMENT,  
        NAME CHAR(30) NOT NULL,  
        SEX  CHAR(2) NOT NULL,  
        AGE INT NOT NULL,  
        DEPARTMENT CHAR(10) NOT NULL,  
        SALARY  INT NOT NULL,  
        HOME CHAR(30),  
        MARRY CHAR(2) NOT NULL DEFAULT  '否',         
        HOBBY CHAR(30)  
 ); 
 
INSERT INTO employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'李红','女',20,'人事资源部','7000','浙江','否','网球');
INSERT INTO employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'姜文','女',21,'人事资源部','9000','北京','是','看书');
INSERT INTO employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'李楠','男',22,'产品研发部','8000','上海','否','音乐');
INSERT INTO employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'潇潇','女',23,'产品研发部','9000','重庆','否','游泳');
INSERT INTO employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'王亮','女',24,'产品研发部','9000','四川','是','足球');
INSERT INTO employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'程默','男',25,'业务销售部','8500','福建','否','游戏');
INSERT INTO employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'陆明','男',26,'业务销售部','10000','山西','否','篮球');
INSERT INTO employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'韩路','男',26,'业务销售部','15000','江苏','否','足球');

CREATE TABLE employee_detail(
	ID INT PRIMARY KEY,
	POS CHAR(10) NOT NULL,
	EXPERENCE CHAR(10) NOT NULL,
	CONSTRAINT `FK_ID` FOREIGN KEY(ID) REFERENCES employee(ID)
);
 
INSERT INTO employee_detail(ID,POS,EXPERENCE) VALUES(1,'人事管理','工作二年');
INSERT INTO employee_detail(ID,POS,EXPERENCE) VALUES(2,'人事招聘','工作二年');
INSERT INTO employee_detail(ID,POS,EXPERENCE) VALUES(3,'初级工程师','工作一年');
INSERT INTO employee_detail(ID,POS,EXPERENCE) VALUES(4,'中级工程师','工作二年');
INSERT INTO employee_detail(ID,POS,EXPERENCE) VALUES(5,'高级工程师','工作三年');
INSERT INTO employee_detail(ID,POS,EXPERENCE) VALUES(6,'销售代表','工作二年');
INSERT INTO employee_detail(ID,POS,EXPERENCE) VALUES(7,'销售经理','工作一年');
INSERT INTO employee_detail(ID,POS,EXPERENCE) VALUES(8,'销售总监','工作一年');



#创建单表视图
CREATE VIEW V_VIEW1(ID, NAME, SEX, AGE,DEPARTMENT) AS SELECT ID, NAME, SEX, AGE,DEPARTMENT FROM employee;

#创建多表视图
CREATE VIEW V_VIEW2(ID, NAME, SEX, AGE,DEPARTMENT,POS,EXPERENCE) AS SELECT a.ID, a.NAME, a.SEX, a.AGE,a.DEPARTMENT,b.POS,b.EXPERENCE FROM employee a,employee_detail b WHERE a.ID=b.ID;

#修改视图中的数据，会影响原有表数据变化，例如：
UPDATE V_VIEW2 SET POS='高级工程师' WHERE NAME='李楠';

#修改原始表中的数据，会影响视图的变化，例如：
INSERT INTO employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'韩寒','男',24,'产品研发部','8000','上海','否','音乐');
INSERT INTO employee_detail(ID,POS,EXPERENCE) VALUES(9,'初级工程师','工作一年');