SQL视图

一、基本概念

USE test_views;
# 针对单表创建
# 情况一 ：视图中的字段与基表中的字段有一对一的关系
CREATE VIEW view_emps1
AS
SELECT * FROM emps
WHERE salary>8000;
# 情况二：视图中的字段基表中可能没有
CREATE VIEW view_emps2(dep_id,avg_sal)
AS
SELECT department_id,AVG(salary)
FROM emps
GROUP BY department_id;
# 针对多表创建
CREATE VIEW view_emps3
AS
SELECT emps.`employee_id`,emps.`last_name` "name",depts.department_id "dep_id",department_name,"dep_name"
FROM emps JOIN depts
ON emps.`department_id`=depts.`department_id`;
# 利用视图作数据格式化
CREATE VIEW view_emps4
AS
SELECT CONCAT(last_name,'(',job_id,')')
FROM emps;
# 针对视图创建
CREATE VIEW view_emps5(id,NAME,salary)
AS
SELECT employee_id,last_name,salary
FROM view_emps1;
# 查看视图和表
SHOW TABLES;
# 查看视图结构
SHOW CREATE VIEW emps_salary_greater_than8000;

# DML 操作视图会影响到基表，反之亦然
# 一般情况可以更新
UPDATE view_emps1 SET salary =8888
WHERE employee_id=101;
SELECT *
FROM view_emps1;
SELECT * FROM emps;
# 不能DML特殊情况
# 1. 视图字段值通过其他字段(包括本身)计算得出如avg(salary)
# 更改视图
# 方式一
CREATE OR REPLACE VIEW view_emps1(id,last_name,department_id)
AS
SELECT department_id,last_name,department_id
FROM emps
WHERE salary>10000;
# 方式二
ALTER VIEW view_emps2
AS
SELECT *
FROM emps;
# 删除视图
DROP VIEW IF EXISTS view_emps1;

二、练习

#1. 使用表employees创建视图employee_vu，其中包括姓名（LAST_NAME），员工号（EMPLOYEE_ID），部门
# 号(DEPARTMENT_ID)
CREATE VIEW employee_vu 
AS 
SELECT employee_id, last_name,department_id 
FROM atguigudb.`employees`;
#2. 显示视图的结构
DESC employee_vu;
#3. 查询视图中的全部内容
SELECT * FROM employee_vu;
#4. 将视图中的数据限定在部门号是80的范围内
SELECT *
FROM employee_vu
WHERE department_id=80;
-- ------ 练习二
CREATE  TABLE emps
AS
SELECT * FROM atguigudb.employees;
#1. 创建视图emp_v1,要求查询电话号码以‘011’开头的员工姓名和工资、邮箱
CREATE VIEW emp_v1
AS
SELECT last_name,salary,email
FROM emps
WHERE phone_number LIKE '011%';
#2. 要求将视图 emp_v1 修改为查询电话号码以‘011’开头的并且邮箱中包含 e 字符的员工姓名和邮箱、电话号码
CREATE OR REPLACE VIEW emp_v1
AS
SELECT last_name,email,phone_number,salary
FROM emps
WHERE phone_number LIKE '011%' AND email LIKE '%e%';
INSERT INTO emp_v1(last_name,salary,email,phone_number)
VALUES('Tom',2300,'toe@126.com','011232131');
#3. 向 emp_v1 插入一条记录，是否可以？
-- 可以  但必须符合条件
#4. 修改emp_v1中员工的工资，每人涨薪1000
UPDATE emp_v1 
SET salary=salary+1000;
#5. 删除emp_v1中姓名为Olsen的员工
DELETE FROM emp_v1
WHERE last_name ='Olsen';
#6. 创建视图emp_v2，要求查询部门的最高工资高于 12000 的部门id和其最高工资
CREATE VIEW emp_v2(dep_id,max_sal)
AS 
SELECT department_id,MAX(salary)
FROM emps
GROUP BY department_id
HAVING MAX(salary)>12000;
#7. 向 emp_v2 中插入一条记录，是否可以？
-- 不可以 不存在一对一的情况
#8. 删除刚才的emp_v2 和 emp_v1
DROP VIEW emp_v2,emp_v1;