黑马程序员MySQL-视图SQL笔记

对应课程地址

-- 视图
-- 准备数据
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;