mysql基础 | 14.视图

一、视图

#视图
/*
含义：虚拟表，和普通表一样使用
mysql15.1版本出现的新特性，是通过表动态生成的数据

比如：舞蹈班和普通班级的对比

	创建语法的关键字	是否实际占用物理空间	使用
视图	create view		只是保存了sql逻辑	增删改查，只是一般不能增删改

表	create table		保存了数据		增删改查

*/

#案例：查询姓张的学生名和专业名
USE students;
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m
ON s.`majorId`=m.`id`
WHERE s.`stuname` LIKE '张%';

CREATE VIEW v1
AS
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m
ON s.`majorId`=m.`id`;

SELECT * FROM v1 WHERE stuname LIKE '张%';


#一、创建视图
/*
语法：
create view 视图名
as
查询语句;

*/
USE myemployees;

#1.查询邮箱中包含a字符的员工名、部门名和工种信息
#①创建
CREATE VIEW v2
AS
SELECT Last_name,department_name,job_title
FROM employees e
INNER JOIN departments d ON e.department_id=d.department_id
INNER JOIN jobs j ON j.job_id=e.job_id;

#②使用
SELECT * FROM v2 WHERE Last_name LIKE '%a%';


#2.查询各部门的平均工资级别
#①创建视图查看每个部门的平均工资
CREATE VIEW myv2
AS
SELECT AVG(Salary) ag,department_id
FROM employees
GROUP BY department_id;

#②使用
SELECT myv2.`ag`,g.grade_level
FROM myv2
INNER JOIN job_grades g
ON myv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;


#3.查询平均工资最低的部门信息
SELECT * FROM myv2
ORDER BY ag ASC
LIMIT 1;


#4.查询平均工资最低的部门名和工资
CREATE VIEW myv3
AS
SELECT * FROM myv2 ORDER BY ag LIMIT 1;

SELECT d.department_name,ag
FROM myv3
INNER JOIN departments d
ON d.`department_id`=myv3.`department_id`;



#二、视图的修改

#方式一：
/*
create or replace view 视图名
as
查询语句;

*/
SELECT * FROM myv3;

CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(Salary),job_id
FROM employees
GROUP BY job_id;


#方式二：
/*
语法：
alter view 视图名
as
查询语句;

*/

ALTER VIEW myv3
AS
SELECT * FROM employees;



#三、删除视图

/*
语法：
drop view 视图名,视图名,...;

*/

DROP VIEW v2,myv2,myv3;



#四、查看视图

DESC myv2; #查看视图的结构

SHOW CREATE VIEW myv2;  #查看视图的创建语句，但在sqllog界面下太长了，不方便看
#可以在cmd界面下查看并格式化



#案例讲解：
#1.创建视图emp_v1，要求查询电话号码以‘011’开头的员工姓名和工资、邮箱
CREATE OR REPLACE VIEW emp_v1
AS
SELECT Last_name,Salary,email
FROM employees
WHERE phone_number LIKE '011%';

SELECT * FROM emp_v1;

#2.创建视图emp_v2，要求查询部门的最高工资高于12000的部门信息
CREATE OR REPLACE VIEW emp_v2
AS
SELECT MAX(Salary) mx_dep,department_id
FROM employees
GROUP BY department_id
HAVING MAX(Salary)>12000;

SELECT d.*,m.mx_dep
FROM departments d
INNER JOIN emp_v2 m
ON d.`department_id`=m.`department_id`;



#五、视图的更新

CREATE OR REPLACE VIEW myv1
AS
SELECT Last_name,email
FROM employees;

SELECT * FROM myv1;
SELECT * FROM employees;

#以下操作，原始表和视图都插入、修改、删除了
#1.插入

INSERT INTO myv1 VALUES('张飞','zf@qq.com');

#2.修改
UPDATE myv1 SET Last_name='张无忌' WHERE Last_name='张飞';

#3.删除
DELETE FROM myv1 WHERE last_name='张无忌';


#具备以下特点的视图不允许更新
#①包含以下关键词的sql语句：分组函数、distinct、group by、having、union或者union all
CREATE OR REPLACE VIEW myv1
AS
SELECT MAX(Salary) m,department_id
FROM employees
GROUP BY department_id;
#更新
UPDATE myv1 SET m=9000 WHERE department_id=10; #不允许更新，因为有分组函数和group by


#②常量视图
CREATE OR REPLACE VIEW myv2
AS
SELECT 'john' NAME;

SELECT * FROM myv2;

#更新
UPDATE myv2 SET NAME='lucy'; #不可更新


#③select中包含子查询
CREATE OR REPLACE VIEW myv3
AS
SELECT (SELECT MAX(Salary) FROM employees) 最高工资;

#更新
SELECT * FROM myv3;
UPDATE myv3 SET 最高工资=100000; #不可更新


#④join(可以更新但不能插入)
CREATE OR REPLACE VIEW myv4
AS
SELECT Last_name,department_name
FROM employees e 
INNER JOIN departments d
ON e.department_id=d.department_id;

#更新
SELECT * FROM myv4;
UPDATE myv4 SET Last_name='张飞' WHERE last_name='Whalen';
INSERT INTO myv4 VALUES('陈真','xxxx');


#⑤from一个不能更新的视图
CREATE OR REPLACE VIEW myv5
AS
SELECT * FROM myv3;

#更新
SELECT * FROM myv5;
UPDATE myv5 SET 最高工资=10000 WHERE department_id=60; #不可更新


#⑥where子句的子查询引用了from子句中的表
CREATE OR REPLACE VIEW myv6
AS
SELECT Last_name,email,Salary
FROM employees
WHERE employee_id IN (
	SELECT manager_id
	FROM employees
	WHERE manager_id IS NOT NULL
);

SELECT * FROM myv6;
UPDATE myv6 SET Salary=1000 WHERE Last_name='k_ing'; #不可更新


#测试题
/*
1.创建Book表，字段如下：
bid 整型，要求主键
bname 字符型，要求设置唯一键，并非空
price 浮点型，要求有默认值 10
btypeId 类型编号，要求引用bookType表的 id字段

已知bookType表（不用创建），字段如下：
id
name
*/
CREATE TABLE Book(
	bid INT PRIMARY KEY,
	bname VARCHAR(20) UNIQUE NOT NULL,
	price DOUBLE DEFAULT 10,
	btypeId INT,
	FOREIGN KEY(btypeId) REFERENCES bookType(id)
)


#2.开启事务：向表中插入1行数据，并结束
SET autocommit=0;
START TRANSACTION;
INSERT INTO Book VALUES(1,'haha',110,2);
COMMIT; / ROLLBACK;

#3.创建视图，实现查询价格大于100的书名和类型名
CREATE VIEW v1
AS
SELECT bname,NAME
FROM book b
INNER JOIN bookType t
ON b.btypeId=t.id
WHERE price>100;

#4.修改视图，实现查询价格在90-120之间的书名和价格
CREATE OR REPLACE VIEW v2
AS
SELECT bname,price
FROM book
WHERE price BETWEEN 90 AND 120;

#5.删除刚才建的视图
DROP VIEW v1,v2;

• 在cmd界面下查看视图，未格式化时不方便查看

• 加上\G格式化之后，方便查看