-- 视图
-- 准备数据
create database if not exists mydb6_view;
use mydb6_view;
create table dept(
    deptno int primary key,
  dname varchar(20),
    loc varchar(20)
);
insert into dept values(10, '教研部','北京'),
(20, '学工部','上海'),
(30, '销售部','广州'),
(40, '财务部','武汉');

create table emp(
    empno int primary key,
    ename varchar(20),
    job varchar(20),
    mgr int,
    hiredate date,
    sal numeric(8,2),
    comm numeric(8, 2),
    deptno int,
--     FOREIGN KEY (mgr) REFERENCES emp(empno),
    FOREIGN KEY (deptno) REFERENCES dept(deptno) ON DELETE SET NULL ON UPDATE CASCADE
);
insert into emp values
(1001, '甘宁', '文员', 1013, '2000-12-17', 8000.00, null, 20),
(1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000.00, 3000.00, 30),
(1003, '殷天正', '销售员', 1006, '2001-02-22', 12500.00, 5000.00, 30),
(1004, '刘备', '经理', 1009, '2001-4-02', 29750.00, null, 20),
(1005, '谢逊', '销售员', 1006, '2001-9-28', 12500.00, 14000.00, 30),
(1006, '关羽', '经理', 1009, '2001-05-01', 28500.00, null, 30),
(1007, '张飞', '经理', 1009, '2001-09-01', 24500.00, null, 10),
(1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000.00, null, 20),
(1009, '曾阿牛', '董事长', null, '2001-11-17', 50000.00, null, 10),
(1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000.00, 0.00, 30),
(1011, '周泰', '文员', 1008, '2007-05-23', 11000.00, null, 20),
(1012, '程普', '文员', 1006, '2001-12-03', 9500.00, null, 30),
(1013, '庞统', '分析师', 1004, '2001-12-03', 30000.00, null, 20),
(1014, '黄盖', '文员', 1007, '2002-01-23', 13000.00, null, 10);

create table salgrade(
    grade int primary key,
    losal int,
    hisal int
);
insert into salgrade values
(1, 7000, 12000),
(2, 12010, 14000),
(3, 14010, 20000),
(4, 20010, 30000),
(5, 30010, 99990);

-- 创建视图
CREATE 
    OR REPLACE VIEW view1_emp AS SELECT
    ename,
    job 
FROM
    emp;
    
-- 查看表和视图
show tables;
show full tables;

-- 查看视图结构
DESCRIBE view1_emp;

-- 查询视图
SELECT
    * 
FROM
    view1_emp;
    
-- 修改视图结构
ALTER VIEW view1_emp AS SELECT
a.deptno,
a.dname,
a.loc,
b.ename,
b.sal 
FROM
    dept a,
    emp b 
WHERE
    a.deptno = b.deptno;
    
    
    
-- 更新视图：修改原表数据
-- 先创建一个视图
CREATE 
    OR REPLACE VIEW view1_emp AS SELECT
    ename,
    job 
FROM
    emp;
    
-- 查询一下视图
SELECT
    * 
FROM
    view1_emp;

-- 更新视图
UPDATE view1_emp 
SET ename = '鲁肃' 
WHERE
    ename = '谢逊';
    
-- 1、插入数据时，视图只是引用表格中的某些字段，
-- 而另外一些字段又没有指定默认值时会插入失败    
INSERT INTO view1_emp
VALUES
    ( '周瑜', '文员' );
    
-- 2、视图包含聚合函数不可更新
CREATE 
    OR REPLACE VIEW view2_emp AS SELECT
    count(*) cnt
FROM
    emp;
    
SELECT
    * 
FROM
    view2_emp;
INSERT INTO view2_emp
VALUES
    ( 100 );
    
UPDATE view2_emp 
SET cnt = 100;

-- 3、视图包含distinct不可更新
CREATE 
    OR REPLACE VIEW view3_emp AS SELECT DISTINCT
    job 
FROM
    emp;

SELECT
    * 
FROM
    view3_emp;
    
INSERT INTO view3_emp
VALUES
    ( '财务' );

-- 4、视图包含group by、having不可更新
CREATE 
    OR REPLACE VIEW view4_emp AS SELECT
    deptno 
FROM
    emp 
GROUP BY
    deptno 
HAVING
    deptno > 10;

SELECT
    * 
FROM
    view4_emp;

insert into view4_emp values(40);

-- 5、视图包含union、union all不可更新
-- union all不去重，union会去重
CREATE 
    OR REPLACE VIEW view5_emp AS SELECT
    empno,
    ename 
FROM
    emp 
WHERE
    empno <= 5 UNION SELECT empno, ename FROM emp WHERE empno > 8;

SELECT
    * 
FROM
    view5_emp;

INSERT INTO view5_emp
VALUES
    ( 1015, '宋江' );
    
-- 6、视图包含子查询不可更新
CREATE 
    OR REPLACE VIEW view6_emp AS SELECT
    empno,
    ename,
    sal 
FROM
    emp 
WHERE
    sal = (
    SELECT
        max( sal ) 
    FROM
    emp);

SELECT
    * 
FROM
    view6_emp;

INSERT INTO view6_emp
VALUES
    ( 1015, '血刀老祖', 30000.0 );

-- 7、视图包含join不可更新
CREATE VIEW view7_emp AS SELECT
dname,
ename,
sal 
FROM
    emp a
    JOIN dept b ON a.deptno = b.deptno;

INSERT INTO view7_emp
VALUES
    ( '行政部', '韦小宝', 6500.00 );
    
-- 8、视图包含常量文字值不可更新
CREATE 
    OR REPLACE VIEW view8_emp AS SELECT
    '行政部' dname,
    '杨过' ename;
    
INSERT INTO view8_emp
VALUES
    ( '行政部', '韦小宝' );

CREATE 
    OR REPLACE VIEW view9_emp AS SELECT
    * 
FROM
    emp;
-- 重命名视图
RENAME TABLE view9_emp TO view9_1_emp;
-- 删除视图
DROP VIEW
IF
    EXISTS view9_1_emp;

-- 视图练习
-- 1：查询部门平均薪水最高的部门名称
-- 最原始做法：不使用开窗函数，不使用视图
SELECT
    dname 
FROM
    dept 
WHERE
    deptno = (
    SELECT
        b.deptno 
    FROM
        (
        SELECT
            a.deptno,
            max( a.avg_sal ) 
        FROM
            ( SELECT deptno, avg( sal ) avg_sal FROM emp GROUP BY deptno ) a 
        ) b 
    );

-- 1：增加一点难度查询部门平均薪水处于最高两位的部门名称
-- 使用开窗函数，不使用视图
-- 1.1 先查出每个部门编号对应的平均薪水
-- 1.2 用开窗函数进行排序
-- 1.3 找到rank小于等于2的deptno
-- 1.4 再跟dept表联合查找出dname
SELECT
    dname 
FROM
    dept d,
    (
    SELECT
        deptno 
    FROM
        (
        SELECT
            *,
            rank() over ( ORDER BY avg_sal DESC ) rn 
        FROM
            ( SELECT deptno, avg( sal ) avg_sal FROM emp GROUP BY deptno ) a 
        ) b 
    WHERE
        rn = 1 
    ) c 
WHERE
    d.deptno = c.deptno;
    
-- 1：增加一点难度查询部门平均薪水处于最高两位的部门名称
-- 使用开窗函数，并且使用视图
-- 1.1 先查出每个部门编号对应的平均薪水，创建一个视图
CREATE 
    OR REPLACE VIEW view_dept_avg_sal AS SELECT
    deptno,
    avg( sal ) avg_sal 
FROM
    emp 
GROUP BY
    deptno;
-- 1.2 用开窗函数进行排序，创建一个视图
CREATE 
    OR REPLACE VIEW view_dept_avg_sal_rank AS SELECT
    *,
    rank() over ( ORDER BY avg_sal DESC ) rn 
FROM
    view_dept_avg_sal;
-- 1.3 找到rank小于等于2的deptno，创建一个视图
CREATE 
    OR REPLACE VIEW view_dept_avg_sal_top2 AS SELECT
    * 
FROM
    view_dept_avg_sal_rank 
WHERE
    rn <= 2;
-- 1.4 再跟dept表联合查找出dname
CREATE 
    OR REPLACE VIEW view_dept_avg_sal_top2_dname AS SELECT
    dname 
FROM
    dept a,
    view_dept_avg_sal_top2 b 
WHERE
    a.deptno = b.deptno;

-- 2：查询员工比所属领导薪资高的部门名、员工名、员工领导编号
-- 最原始做法：不使用视图
SELECT
    dname,
    ename,
    mgr 
FROM
    dept b,
    (
    SELECT
        e1.deptno,
        e1.ename,
        e1.sal,
        e1.mgr,
        e2.sal mgr_sal 
    FROM
        emp e1,
        emp e2 
    WHERE
        e1.mgr = e2.empno 
        AND e1.sal > e2.sal 
    ) a 
WHERE
    b.deptno = a.deptno;
    
-- 2：查询员工比所属领导薪资高的部门名、员工名、员工领导编号
-- 使用视图
-- 2.1查询员工比所属领导薪资高的部门号，然后创建一个视图
CREATE 
    OR REPLACE VIEW view_deptno_ename_mgr AS SELECT
    e1.deptno,
    e1.ename,
    e1.mgr 
FROM
    emp e1,
    emp e2 
WHERE
    e1.mgr = e2.empno and e1.sal > e2.sal;
-- 2.2将上一步查询出来的部门号和部门表进行连表查询
CREATE 
    OR REPLACE VIEW view_dname_ename_mgr AS SELECT
    dname,
    ename,
    mgr 
FROM
    dept a,
    view_deptno_ename_mgr b 
WHERE
    a.deptno = b.deptno;
    
-- 3：查询工资等级为4级，2000年以后入职的工作地点为上海的员工编号、姓名和工资，
-- 并查询出薪资在前2名的员工信息
-- 最原始做法，不使用视图
SELECT
    empno,
    ename,
    sal 
FROM
    (
    SELECT
        empno,
        ename,
        sal,
        rank() over ( ORDER BY sal DESC ) rn 
    FROM
        emp e,
        dept d,
        salgrade s 
    WHERE
        e.deptno = d.deptno 
        AND YEAR ( hiredate ) >= '2000' 
        AND loc = '上海' 
        AND grade = 4 
        AND sal BETWEEN losal 
        AND hisal 
    ) a 
WHERE
    rn <= 2;
    
-- 3：查询工资等级为4级，2000年以后入职的工作地点为上海的员工编号、姓名和工资，
-- 并查询出薪资在前2名的员工信息
-- 使用视图
-- 3.1 查询工资等级为4级，2000年以后入职的工作地点为上海的员工编号、姓名和工资，创建一个视图
CREATE 
    OR REPLACE VIEW view_ename_sal_after2000_grade4 AS SELECT
    empno, ename, sal 
FROM
    emp e,
    dept d,
    salgrade s 
WHERE
    e.deptno = d.deptno 
    AND grade = 4 
    AND sal BETWEEN losal 
    AND hisal 
    AND loc = '上海' 
    AND YEAR ( hiredate ) > '2000'
-- 此处三张表联查还可以用join，逻辑会更清晰
SELECT
    empno,
    ename,
    sal 
FROM
    emp e
    JOIN dept d ON e.deptno = d.deptno AND loc = '上海' AND YEAR ( hiredate ) > '2000';
    JOIN salgrade s ON grade = 4 AND ( sal BETWEEN losal AND hisal )
    

-- 3.2 查询出薪资在前2名的员工信息
SELECT
    empno,
    ename,
    sal 
FROM
    ( SELECT *, rank() over ( ORDER BY sal DESC ) rn FROM view_ename_sal_after2000_grade4 ) a
WHERE
    rn <= 2;