SQL-视图

/**
关于视图的理解(view)
1.视图的理解：
可以看做一个存储起来的select 语句，是虚拟的表

2.视图中select 语句中涉及的表，称为基表
3.针对视图做dml操作，会影响对应的基表中的数据，反之亦然
4.视图本身的删除，不会导致基表中的数据的删除
5.视图的应用场合：针对于小型项目，不推荐使用视图，针对大型项目，推荐使用视图
总结： 优点1： 简化查询 控制数据的访问
*/
#视图的创建
CREATE DATABASE dbtest4;
USE dbtest4;
CREATE TABLE emps
AS SELECT * FROM atguigudb.`employees`;
CREATE TABLE dept
AS SELECT * FROM atguigudb.`departments`;

DESC emps;
DESC atguigudb.`employees`;
#针对单表创建视图
CREATE VIEW Myview
AS
SELECT employee_id,last_name,salary
FROM emps;
SELECT *
FROM Myview;
#
CREATE VIEW vu_emp2
AS
SELECT employee_id, last_name,salary
FROM emps
WHERE salary > 8000;
SELECT * FROM vu_emp2;

#
CREATE VIEW uv_dept
AS
SELECT department_id,AVG(salary)
FROM emps
WHERE department_id IS NOT NULL
GROUP BY department_id;
SELECT * FROM uv_dept;
# 利用视图对 数据进行格式化
CREATE VIEW emp_depart AS
SELECT
CONCAT(
last_name,
'(',
department_name,
')'
) AS emp_dept
FROM
employees e
JOIN departments d
WHERE e.department_id = d.department_id ;

#基于视图创建 视图
CREATE VIEW vu_emp4
AS
SELECT employee_id,last_name
FROM vu_emp2;

SELECT * FROM vu_emp4;

# 查看视图
# 查看的表和视图
SHOW TABLES;
#查看视图的结构
DESC vu_emp2;

#语法三：查看视图的属性信息
SHOW TABLE STATUS LIKE 'vu_emp2';
#4
SHOW CREATE VIEW vu_emp2;


#更新视图中的数据 会修改基表中的数据
#一般情况下 可以更新视图中的数据
UPDATE vu_emp2
SET salary = 20000
WHERE employee_id = 101;
SELECT * FROM vu_emp2;
SELECT * FROM emps;
#修改视图
CREATE OR REPLACE VIEW empvu80
(id_number, NAME, sal, department_id)
AS
SELECT employee_id, first_name || ' ' || last_name, salary, department_id
FROM employees
WHERE department_id = 80;
#
/*ALTER VIEW 视图名称
AS
查询语句*/
# 删除视图
DROP VIEW vu_emp2;