创建两张表,员工表(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;(输出创建视图的信息)