mysql 视图的创建

概述

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

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

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

创建

?

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

　　

示例1

?

1 2 3 4 5

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　

?

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

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,'初级工程师','工作一年');