# 1 查询姓名中包含a字符的员工名,部门名和工种信息(视图的创建)
CREATE VIEW includeA AS SELECT
emp.last_name,
dep.department_name,
job.*
FROM
employees emp
INNER JOIN departments dep ON emp.department_id = dep.department_id
INNER JOIN jobs job ON emp.job_id = job.job_id
WHERE
emp.last_name LIKE '%a%';
3 视图的修改
方式一:存在就修改,不存在就创建
CREATE OR REPLACE VIEW 视图名称
AS
查询语句;
方式二
ALTER VIEW 视图名称
AS
查询语句;
# 4 修改视图
# 方式一;
CREATE
OR REPLACE VIEW avgsalary AS SELECT
*
FROM
my_employees;
# 方式二:
ALTER VIEW avgsalary AS SELECT
*
FROM
employees;
mysql> SHOW CREATE VIEW avgSalary \G;
*************************** 1. row ***************************
View: avgsalary
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `avgsalary` AS select `employees`.`employee_id` AS `employee_id`,`employees`.`first_name` AS `first_name`,`employees`.`last_name` AS `last_name`,`employees`.`email` AS `email`,`employees`.`phone_number` AS `phone_number`,`employees`.`job_id` AS `job_id`,`employees`.`salary` AS `salary`,`employees`.`commission_pct` AS `commission_pct`,`employees`.`manager_id` AS `manager_id`,`employees`.`department_id` AS `department_id`,`employees`.`hiredate` AS `hiredate` from `employees`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)
6 视图的更新(更新数据)
可以通过INSERT,UPDATE,DELETE语句对视图中的数据进行更改
以下种类的视图,视图中的数据不可以更改
包含以下关键字的SQL语句:分组函数,DISTINCT,GROUP BY,HAVING,UNION,UNION ALL
常量视图(CREATE OR REPLACE VIEW myView AS SELECT 'A' name)
SELECT中包含子查询(CREATE OR REPLACE VIEW demo AS SELECT (SELECT MAX(emp.salary) FROM employees emp) AS MAXSALARY;)
创建视图的时候使用了连接(此时视图中的数据可以被更新,但是不能插入新的数据)
FROM一个不能更新的视图(CREATE OR REPLACE VIEW demo AS SELECT * FROM 不可更新的视图)
WHERE子句的子查询引用了FROM子句中的表
# WHERE子句的子查询引用了FROM子句中的表
CREATE
OR REPLACE VIEW demo AS SELECT
emp.last_name,
emp.email,
emp.salary
FROM
employees emp
WHERE
emp.employee_id IN (
SELECT
manager_id
FROM
employees
WHERE
manager_id IS NOT NULL
);