TCL-视图

VIEW视图
CREATE创建
replace更换

---

MySQL5.1的新特性，是一个虚拟表（有行有列）。和普通表一样使用。
它的数据来自表，通过执行时动态生成。

只保存SQL逻辑，没有保存查询结果。

做一个复杂的查询，把查询封装到一个视图里，形成一个虚拟的结果集。
下次需要的时候就不用在写查询语句了，直接用视图

例如：从每个班里挑选一部分人，组成舞蹈班。有领导来的时候，舞蹈班表演
领导走了，舞蹈班就解散回班。
舞蹈班就是视图，班就是普通的表。

特点：

1.临时性
2.重复利用
3.简化复杂的SQL操作。不用知道它查询的细节
4.保护数据，提高安全。视图和基本表相分离，提高信息的保密

应用场景：
多次用到重复的查询语句

#案例：查询姓张的学生名和专业名
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 '张%';

1.如何创建视图

CREATE VIEW 视图名
AS
查询语句

#1.查询姓名中包含a字符的员工名、部门名和工种信息
#1.创建视图
CREATE VIEW myv1
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

 

2.使用视图

SELECT *
FROM myv1
WHERE last_name LIKE '%a%'

#2.查询各部门的平均工资级别
#1.创建视图查看每个部门的平均工资
CREATE VIEW myv2
AS
SELECT department_id,avg(salary)
FROM employees
GROUP BY department_id;
#2.使用视图
SELECT myv2.`avg(salary)`,g.grade_level
FROM myv2
JOIN job_grades g
ON myv2.`avg(salary)` BETWEEN g.lowest_sal AND g.highest_sal;

#3.查询平均工资最低的部门信息
SELECT *
FROM myv2
ORDER BY myv2.`avg(salary)`#默认升序从小到大
LIMIT 1;

#4.查询平均工资最低的部门名和工资
CREATE VIEW myv3
AS
SELECT *
FROM myv2
ORDER BY myv2.`avg(salary)`#默认升序从小到大
LIMIT 1;

SELECT d.*,m.`avg(salary)`
FROM myv3 m
JOIN departments d
ON m.department_id=d.department_id;

 

3.视图的修改

方式一：
CREATE or replace VIEW 视图名
AS
查询语句；
如果存在就修改，不存在就创建

方式二：
ALTER VIEW 视图名
AS 
查询语句；

#方式一：修改myv3
CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

SELECT * FROM myv3

#方式二：修改myv3
ALTER VIEW myv3
AS
SELECT * FROM employees;

 

4.视图的删除

语法：drop view 视图名，视图名....；
权限要够，以后新建的不知道够不够
现在用的是root超级管理员。

DROP VIEW myv1,myv2,myv3;

 

5.查看视图

SHOW CREATE VIEW myv3;

 

 

*********************
视图名：
如何创建：
字符集：

 

6.视图的更新：原始表也会被更新

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

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

SELECT * FROM myv1;

#1.插入数据
INSERT INTO myv1
VALUES('张飞','zf@qq.com')
/*
not insertable-into不能更新
*/

#2.修改
update myv1 SET last_name='张无忌' 
WHERE last_name='张飞';

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

#涉及到表中的数据才需要from

 

 视图用来查询的，而不是更新，所以具备以下特点的视图不容许更新

1.包含以下关键字的SQL语句
　　分组函数、distinct去重、group by分组、having分组后查询、union联合查询、union all联合查询显示重复的
2.常量视图（select 'john' NAME;查询 常量 别名 形成一个常量视图）
3.select中包含子查询
4.join（SQL92的逗号也不行）连接查询（可以使用update改变，不能插入）
5.from一个不能更新的视图（创建视图的时候，from了一个不能更新的视图）
6.where子句的子查询，引用了from子句中的表。（查询领导的信息）

视图和表的对比

	创建的关键子	是否占用物理空间	使用
视图	create view	没有为数据开辟空间，只保存SQL逻辑	只做查询。一般不能使用：增删改
表	create table	保存了数据	增删改查