mysql视图

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

-- 视图
-- 准备数据
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;
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;
select * from view1_emp;

 

 

-- 更新视图
use mydb6_view;
create or replace view view1_emp
as
select ename,job from emp;
select * from view1_emp;
update view1_emp set ename='周瑜' where ename = '鲁肃';
insert into view1_emp values('周瑜','文员');#给视图插入值实际上是给原表插入，此处只插入两个值，原表有空值，无法插入，
create or replace view view3_emp
as
select ename,job from emp;
select * from view2_emp;
insert into view2_emp values(100);#视图包含聚合函数不能插入
update view2_emp set cnt = 100;
create or replace view view3_emp
as
select ename,job from emp;
select * from view3_emp;
insert into view2_emp values('财务');#视图包含distinct不能插入
update view2_emp set cnt = 100;
-- 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 view1_emp to mt_view1;
-- 删除视图
drop view if exists mt_view1;

 

 

-- 查询部门平均薪水最高的部门名称
create view test_view11
as
SELECT
a.deptno,
a.dname,
a.loc,
avg_sal
FROM
dept a,test_view3 ttt
WHERE
a.deptno = ttt.deptno;
create view test_view1
as
select deptno,avg(sal) avg_sal from emp group by deptno
create view test_view2
as
SELECT
*,
rank() over ( ORDER BY avg_sal DESC ) rn
FROM
test_view1;
create view test_view3
as
SELECT
*
FROM
test_view2 tt
WHERE
rn = 1;
select * from test_view11;

-- 查询员工比所属领导薪资高的部门名、员工名、员工领到编号
-- 查询员工比领导工资高的部门号
create view test_view4
as
select
a.ename ename,
a.sal esal,
b.ename mgrname,
b.sal msal,
a.deptno
FROM
emp a,
emp b
WHERE
a.mgr = b.empno
AND a.sal > b.sal;-- 将第一步查询的部门号和部门表进行联表查询
select * from dept a join test_view4 b on a.deptno = b.deptno;

-- 查询工资等级为4级，2000年以后入职的工作地点为北京的员工编号、姓名和工资，并查询薪资在前三名的员工信息
-- 3.1查询工资等级为4级，2000年以后入职的工作地点为上海的员工编号、姓名和工资
CREATE VIEW test_view5 AS SELECT
a.deptno,
a.dname,
a.loc,
b.empno,
b.ename,
b.sal,
c.grade
FROM
dept a
JOIN emp b ON a.deptno = b.deptno
AND YEAR ( hiredate ) > '2000'
AND a.loc = '上海'
JOIN salgrade c ON grade = 4
AND ( b.sal BETWEEN c.losal AND c.hisal );
SELECT
*
FROM
( SELECT *, rank() over ( ORDER BY sal DESC ) rn FROM test_view5 ) t
WHERE
rn <= 3;