java高级开发技术，MySQL 数据库 day-06

as

查询语句;

案例1：查询姓名中包含a字符的员工名、部门名和工种信息

#①创建

CREATE VIEW myv1

AS

SELECT last_name,department_name,job_title

FROM employees e

JOIN departments d ON d.department_id=e.department_id

JOIN jobs j ON j.job_id=e.job_id;

#②使用

SELECT * FROM myv1 WHERE last_name LIKE ‘%a%’;

案例2：查询各部门的平均工资级别

#①创建

CREATE VIEW myv2

AS

SELECT AVG(salary) ag

FROM employees

GROUP BY department_id;

#②使用

SELECT grade_level

FROM myv2

JOIN job_grades

ON ag BETWEEN lowest_sal AND highest_sal;

案例3：查询平均工资最低的部门信息

SELECT * FROM myv2 ORDER BY ag LIMIT 1;

二、视图的修改

---

方式一：★

语法：

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 myv1,myv2;

四、查看视图

---

DESC myv3;

SHOW CREATE VIEW myv3;

案例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_v3

AS

SELECT MAX(salary) ms,department_id

FROM employees

GROUP BY department_id

HAVING MAX(salary)>12000;

SELECT d.*,m.ms

FROM departments d

JOIN emp_v3 m

ON m.department_id=d.department_id;

五、视图的更新

---

CREATE OR REPLACE VIEW myv1

AS

SELECT last_name,email

FROM employees;

SELECT * FROM myv1;

1、插入数据

INSERT INTO myv1 VALUES(‘张飞’,‘zhangyd@163.com’);

SELECT * FROM employees;

2、修改数据

UPDATE myv1 SET last_name=‘张无忌’ WHERE last_name=‘张飞’;

3、删除

DELETE FROM myv1 WHERE last_name=‘张无忌’;

_说明：视图的可更新性和视图中查询的定义有关系，一下类型的视图是不能更新的

1、包含一下关键字的sql语句:分组函数、distinct、group by、having、union或者union all

2、常量视图

3、select中包含子查询

4、join

5、from一个不能更新的视图

6、where子句的子查询引用了from子句中的表_

表和视图的对比

视图 ： create view 　　　 只是保存了sql语句 　　增删改查，一般不能增删改