SQL/视图

#视图
/*
含义：虚拟表，和普通表一样使用(视图保存的是逻辑关系，而不是真实的表)
mysql5.1版本出现的新特性，是通过表动态生成的数据

比如：舞蹈班和普通班的对比

视图的优点：

1、重用sql语句
2、简化复杂的sql操作，不必知道它的查询细节
3、保护数据，提高安全性


    创建的语法的关键字   是否实际占用物理空间    使用
视图    create view        只是保存了sql逻辑    增删改查，一般不能增删改

表    create table         保存了数据        增删改查


*/

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

#一、如何创建视图

/*
语法：
create view 视图名
as
查询语句;
*/

USE myemployees;
#1、查询员工名中包含a字符的员工名、部门名和工种信息
#
SELECT last_name, department_name,job_title
FROM employees s
INNER JOIN departments d
ON s.`department_id` = d.`department_id`
INNER JOIN jobs j
ON s.`job_id` = j.`job_id`
#
WHERE s.`email` LIKE '%a%';

#创建视图
CREATE VIEW v1
AS

SELECT s.last_name, d.department_name,j.job_title
FROM employees s
INNER JOIN departments d
ON s.`department_id` = d.`department_id`
INNER JOIN jobs j
ON s.`job_id` = j.`job_id`;

#按视图查询
SELECT * FROM v1 WHERE last_name LIKE '%a%';

#2、查询各部门的平均工资级别

#创建视图查看每个部门的平均工资
CREATE VIEW av_dep
AS

SELECT AVG(salary) avg_sal, department_id
FROM employees
GROUP BY department_id;

#select * from av_dep;

#使用视图， between ... and ...
SELECT avg_sal, grade_level
FROM av_dep
LEFT JOIN job_grades g
ON av_dep.`avg_sal` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

#查询平均工资最低的部门信息 #排序 limit 1

SELECT * FROM av_dep ORDER BY avg_sal LIMIT 1;

#查询平均工资最低的部门名和工资

SELECT department_name, min_av_sal.avg_sal
FROM departments d
INNER JOIN (SELECT * FROM av_dep ORDER BY avg_sal LIMIT 1) AS min_av_sal
ON d.`department_id` = min_av_sal.department_id;

#创建视图
CREATE VIEW min_av_sal
AS
SELECT * FROM av_dep ORDER BY avg_sal LIMIT 1;

#使用
SELECT department_name, min_av_sal.avg_sal
FROM departments d
INNER JOIN min_av_sal
ON d.`department_id` = min_av_sal.`department_id`;

#二、视图的修改

#方式一：
/*
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 av_dep, emp_v1,emp2,min_av_sal,myv3,v1;

#四、查看视图 (查看视图结构)

DESC 视图名;

#show create view 视图名\g;  # 在命令提示符下查看，其中g是格式化

#五、视图的更新  

/*
为视图添加权限，防止更改原始表
*/ 


/*
create or replace view myv1
as
select last_name,email,salary *12*(1+ifnull(commission_pct,0)) as 'annual salary'
from employees;
*/

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

SELECT * FROM myv1;
SELECT * FROM employees;

#1、插入
INSERT INTO myv1 VALUE('张飞',110000);

#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
CREATE OR REPLACE VIEW myv1
AS
SELECT MAX(salary) m,department_id
FROM employees
GROUP BY department_id;

SELECT *FROM myv1;

#更新
UPDATE myv1 SET m = 9000 WHERE department_id = 10;

#2、常量视图
CREATE OR REPLACE VIEW myv2
AS
SELECT 'john' NAME;

SELECT *FROM myv2;

#更新
UPDATE myv2 SET NAME = 'lucy';

#3、select 中包含子查询
CREATE OR REPLACE VIEW myv3
AS
SELECT (SELECT MAX(salary) FROM employees) 最高工资;

SELECT * FROM myv3;
#更新
UPDATE myv3 SET 最高工资 = 100000;

#4、join
CREATE OR REPLACE VIEW myv4
AS
SELECT last_name, department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;

#更新
SELECT * FROM myv4;
UPDATE myv4 SET last_name = '张飞' WHERE last_name = 'Whalen';#可以

INSERT INTO myv4 VALUE('陈真','xxxxx');#不可以


#5、from 一个不能更新的视图
CREATE OR REPLACE VIEW myv5
AS
SELECT *FROM myv3;

#更新
SELECT *FROM myv5;
UPDATE    myv5 SET 最高工资 = 200000;


#6、where 子句的子查询引用了from子句中的表
CREATE OR REPLACE VIEW myv6
AS
SELECT last_name, email, salary
FROM employees   #from 子句
WHERE employee_id IN (     #where 子句
    SELECT manager_id
    FROM employees     #使用了from子句的表
    WHERE manager_id IS NOT NULL
);

#更新
SELECT * FROM myv6;

UPDATE myv6 SET salary = 10000 WHERE last_name = 'K_ing';


#创建视图练习
#1、创建视图emp_v1,要求查询电话号码以011开头的员工姓名和工资、邮箱
CREATE OR REPLACE VIEW emp_v1
AS
SELECT last_name, salary, email
FROM employees
WHERE phone_number LIKE '011%';

SELECT * FROM emp_v1;

#2、创建视图emp2,要求查询部门的最高工资高于12000的部门信息

CREATE OR REPLACE VIEW emp2
AS
SELECT MAX(salary),department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 12000;

SELECT * FROM emp2;

#部门信息
SELECT *
FROM departments
INNER JOIN emp2
ON emp2.`department_id` = departments.`department_id`;


#练习
#1、创建表Book表，字段如下：
/*
bid 整形，要求主键
bname 字符型，要求设置唯一键，并非空
price 浮点型，要求有默认值 10
btypeId 类型编号，要求引用bookType表的 id 字段

已知表bookType 表(不用创建)，字段如下：
id
name

*/

CREATE TABLE IF NOT EXISTS Book(
    bid INT PRIMARY KEY,
    bname VARCHAR(20) UNIQUE NOT NULL,
    price DOUBLE DEFAULT 10,  # DEFAULT 10
    btypeId INT,  #references bookType(id)
    FOREIGN KEY(btypeId) REFERENCES bookType(id) # 外键
);

#2、开启事务 向表中插入一行数据，并结束
SET autocommit = 0;      #autocommit
INSERT INTO Book VALUES(1,'小李飞刀',100,1);
COMMIT; #rollback

#3、创建视图，实现查询价格大于100的书名和类型名
CREATE OR REPLACE VIEW myv1
AS
SELECT bname, t.name
FROM Book b
INNER JOIN bookType t
ON b.btypeId = t.id;
WHERE b.price > 100;

#4、修改视图，实现查询价格在90-120之间的书名和价格
CREATE OR REPLACE VIEW myv1
AS
SELECT bname, price
FROM Book
WHERE price BETWEEN 90 AND 120; # between ... and ...


#删除刚才建的视图
DROP VIEW myv1;