-- 判斷數(shù)據(jù)表是否存在，存在則刪除
DROP TABLE IF EXISTS tb_staff;
 
-- 創(chuàng)建數(shù)據(jù)表
CREATE TABLE IF NOT EXISTS tb_staff
( 
	id INT AUTO_INCREMENT PRIMARY KEY COMMENT '編號',
	NAME VARCHAR(50) NOT NULL COMMENT '姓名',
	sex INT COMMENT '性別（1：男；2：女；）',
	dept_code VARCHAR(10) COMMENT '部門編號',
	is_post BIT COMMENT '是否在職（0：否；1：是）'
) COMMENT = '員工信息表';

-- 新增數(shù)據(jù)
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES('pan_junbiao的博客_01',1,'1001',1);
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES('pan_junbiao的博客_02',2,'1002',1);
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES('pan_junbiao的博客_03',1,'1003',0);
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES('pan_junbiao的博客_04',1,'1001',1);
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES('pan_junbiao的博客_05',2,'1008',1);
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES('pan_junbiao的博客_06',1,'1001',0);
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES('pan_junbiao的博客_07',2,'1002',1);
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES('pan_junbiao的博客_08',1,'1003',0);
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES('pan_junbiao的博客_09',1,'1001',1);
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES('pan_junbiao的博客_10',2,'1008',0);

-- 創(chuàng)建視圖
CREATE OR REPLACE VIEW view_staff
AS
	SELECT id 
	,NAME
	,IF(sex=1,'男','女') AS sex_name
	,CASE dept_code
		WHEN '1001' THEN '研發(fā)部'
		WHEN '1002' THEN '人事部'
		WHEN '1003' THEN '財務(wù)部'
		ELSE '其他'
	END AS dept_name
	,IF(is_post,'在職','離職') AS is_post_name
	FROM tb_staff
;