MySQL笔记-07视图

视图含义：一种虚拟存在的表，行和列是数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存了SQL逻辑，不保存查询结果。

应用场景：

多个地方用到同样的查询结果
该查询结果使用的SQL语句较复杂
视图和表的区别：

	创建语法的关键字	是否实际占有物理空间	使用
视图	CREATE VIEW	只是保存了SQL逻辑	一般不能增删改查
表	CREATE TABLE	保存了数据	增删改查

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


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

CREATE VIEW v1
AS
SELECT stuname, majorname
FROM student s
JOIN major m
ON s.majorid = m.major.id
WHERE s.majorid = 1;

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

一、创建视图

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52


/*
	语法：
		CREATE VIEW 视图名
        AS
        查询语句;
        
*/
USE myemployees;

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

	# ②使用
	SELECT * FROM my_v1 WHERE last_name LIKE '%a%';

# 2.查询各部门的平均工资级别
	# ①创建视图查看每个部门的平均工资
    CREATE VIEW my_v2
    AS
    SELECT department_id, AVG(salary) AS 'ag'
    FROM employees
    GROUP BY  department_id;
	#②使用
    SELECT department_id, my_v2.ag, g.grade_level
    FROM my_v2
    JOIN job_grades g
    ON my_v2.ag BETWEEN g.lowest_sal AND g.highest_sal;
    
# 3.查询平均工资最低的部门信息
	SELECT * FROM my_v2
    ORDER BY ag
    LIMIT 1;
    
# 4.查询平均工资最低的部门名和工资
	CREATE VIEW my_v3
    AS
    SELECT * FROM my_v2
    ORDER BY ag
    LIMIT 1;
    
    SELECT d.*, m.ag
    FROM my_v3 m
    JOIN departments d
    ON m.department_id = d.department_id;

视图的好处：

重用SQL语句
简化复杂的SQL操作，不必知道它的查询细节
保护数据，提高安全性

二、视图的修改

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22


/*
	方式一：
		CREATE OR REPLACE VIEW 视图名
        AS
        查询语句;
*/

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

/*
	方式二：
		ALTER VIEW 视图名
        AS
        查询语句;    
*/
ALTER VIEW my_v3
AS
SELECT * FROM employees;

三、删除及查看视图

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


#删除语法：DROP VIEW 视图名1, 视图名2,……;
DROP VIEW my_v1, my_v2, my_v3;

# 查看语法
/*
	DESC 视图名;   返回视图结构
    SHOW CREATE VIEW 视图名;  返回视图名、视图创建的语法、字符集……
*/    
CREATE VIEW my_v1
AS
SELECT *FROM employees;

DESC my_v1;
SHOW CREATE VIEW my_v1;

四、视图的更新

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29


CREATE OR REPLACE VIEW my_v1
AS
SELECT last_name, email, salary*12*(1 + IFNULL(commission_pct, 0)) AS 'annual salary'
FROM employees;

CREATE OR REPLACE VIEW my_v2
AS
SELECT last_name, email
FROM employees;

# 1、插入
	INSERT INTO my_v1
    VALUES ('张飞', 'zf@qq.com', 1000000);		#插入失败

	INSERT INTO my_v2
    VALUES ('张飞', 'zf@qq.com');		#插入成功,原表中也会插入
    
# 2、修改
	UPDATE my_v1
    SET last_name = '张无忌'
    WHERE last_name = '张飞';		#修改成功，原表也会修改
    
    UPDATE my_v2
    SET last_name = '张无忌'
    WHERE last_name = '张飞';		#修改成功，原表也会修改

# 3、删除
	DELETE FROM my_v1 WHERE last_name = '张无忌';		#删除成功，原表也会修改
	DELETE FROM my_v2 WHERE last_name = '张无忌'; 		#删除成功，原表也会修改

视图的可更新性和视图中查询的定义有关，以下类型的视图是不能更新的：（基本没有应用场所）

包含以下关键字的SQL语句：分组函数、DISTINCT、GROUP BY、HAVING、UNION或者UNION ALL
常量视图
SELECT 中包含子查询
JOIN
FROM 一个不能更新的视图
WHERE子句的子查询引用了FROM子句中的表

五、练习

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19


# 1、创建视图emp_v1,要求查询电话号码以'011'开头的员工姓名和工资、邮箱
CREATE OR REPLACE VIEW emp_v1
AS
SELECT last_name, salary, email
FROM employees
WHERE phone_number LIKE '011%';

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

SELECT d.*, v.mx_dep
FROM departments d
JOIN emp_v2 v
ON d.department_id= v.department_id;

六、测试题

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43


# 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 FLOAT DEFAULT 10,
    btypeId INT,
    FOREIGN KEY (btypeId) REFERENCES bookType(id)
);

# 2、开启事务，向表中插入1行数据，并结束
SET AUTOCOMMIT = 0;
START TRANSACTION;
INSERT INTO Book VALUES(1, 'C语言', 100, 1);
COMMIT;

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

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

# 5、删除刚才建的视图
DROP VIEW my_v1;

文章目录

一、创建视图

二、视图的修改

三、删除及查看视图

四、视图的更新

五、练习

六、测试题