mysql创建视图

创建两张表，员工表(t_employee)和员工信息表(t_employee_detail);

CREATE TABLE t_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 t_employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'小红','女',20,'人事部','4000','广东','否','网球');  
INSERT INTO t_employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'明日','女',21,'人事部','9000','北京','否','网球');  
INSERT INTO t_employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'天天','男',22,'研发部','8000','上海','否','音乐');  
INSERT INTO t_employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'大大','女',23,'研发部','9000','重庆','否','无');  
INSERT INTO t_employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'王下','女',24,'研发部','9000','四川','是','足球');  
INSERT INTO t_employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'无名','男',25,'销售部','6000','福建','否','游戏');  
INSERT INTO t_employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'不知道','女',26,'销售部','5000','山西','否','篮球'); 
#创建员工信息表
CREATE TABLE t_employee_detail(  
ID INT PRIMARY KEY,  
POS CHAR(10) NOT NULL,  
EXPERENCE CHAR(10) NOT NULL,  
CONSTRAINT `FK_ID` FOREIGN KEY(ID) REFERENCES t_employee(ID)  
);
INSERT INTO t_employee_detail(ID,POS,EXPERENCE) VALUES(1,'人事管理','工作二年');  
INSERT INTO t_employee_detail(ID,POS,EXPERENCE) VALUES(2,'人事招聘','工作二年');  
INSERT INTO t_employee_detail(ID,POS,EXPERENCE) VALUES(3,'初级工程师','工作一年');  
INSERT INTO t_employee_detail(ID,POS,EXPERENCE) VALUES(4,'中级工程师','工作二年');  
INSERT INTO t_employee_detail(ID,POS,EXPERENCE) VALUES(5,'高级工程师','工作三年');  
INSERT INTO t_employee_detail(ID,POS,EXPERENCE) VALUES(6,'销售代表','工作二年');  
INSERT INTO t_employee_detail(ID,POS,EXPERENCE) VALUES(7,'销售员','工作一年'); 

#创建视图

#查看用户是否存在创建视图权限

SELECT SELECT_priv,create_view_priv FROM mysql.user WHERE USER='root' ;

#创建单张表视图

CREATE VIEW V_VIEW1(ID, NAME, SEX, AGE,DEPARTMENT) AS SELECT ID, NAME, SEX, AGE,DEPARTMENT FROM t_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 t_employee a,t_employee_detail b;

#视图查看

select * from v_view1;(查看视图内容)

describe v_view1;(输出视图结构)

show create view v_view1;(输出创建视图的信息)