mysql中视图

mysql中视图

直接po代码

#视图
/*

含义：虚拟表，和普通表一样使用
mysql5.1版本出现的新特性，是通过表动态生成的数据

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

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


*/
USE student;

#案例：查询姓张的学生名和专业名
SELECT studentname,majorname
FROM student s
INNER JOIN major m ON s.majorid = m.majorid
WHERE s.studentname LIKE '张%';

#创建视图(小试牛刀)
CREATE VIEW stuAndMajor
AS
SELECT studentname,majorname
FROM student s
INNER JOIN major m ON s.majorid = m.majorid;

#使用视图
SELECT * FROM stuAndMajor WHERE studentname LIKE '张%';

#
USE myemployees;
#一、创建视图
/*
语法：
create view 视图名
as
查询语句;

*/

#1.查询姓名中包含a字符的员工名、部门名和工种信息
#创建视图
CREATE VIEW myView1
AS
SELECT `last_name` AS 员工名, `department_name` AS 部门名, `job_title` AS 工种名
FROM `employees` AS emp INNER JOIN `departments` AS dep
ON emp.`department_id` = dep.`department_id`
INNER JOIN `jobs` AS j
ON emp.`job_id` = j.`job_id`;
#where last_name like '%a%';


#使用视图(有效果，能查出数据)
SELECT * FROM myView1 WHERE 员工名 LIKE '%a%';

#使用视图(无效果且还报错，不存在last_name列，原因是创建视图时给last_name列起了别名)
SELECT * FROM myView1 WHERE last_name LIKE '%a%';


#2.查询各部门的平均工资级别
CREATE VIEW myView2
AS
SELECT department_id, AVG(salary) AS ag FROM employees emp
GROUP BY department_id;

#使用视图
SELECT department_id, ag, gra.`grade_level` FROM myView2 AS myV
INNER JOIN job_grades AS gra
ON myV.`ag` BETWEEN gra.`lowest_sal` AND gra.`highest_sal`;

#也可以如下写法(会晕死，查询语句没问题却总是报错，在网上查了下原因，mysql视图中是不可以使用子查询的)
#既然mysql视图中不支持子查询，我们可以拆分成多个视图，再将多个视图关联成一个大的视图(即移花接木)
CREATE VIEW myView3
AS
SELECT avg_dep.*, gra.`grade_level` FROM (
SELECT department_id, AVG(salary) AS ag
FROM employees emp
GROUP BY department_id) AS avg_dep
INNER JOIN job_grades gra
ON avg_dep.ag BETWEEN gra.`lowest_sal` AND gra.`highest_sal`;
#

#创建视图1
CREATE VIEW myV_avg_dep
AS
SELECT department_id, AVG(salary) AS ag
FROM employees emp
GROUP BY department_id;

#创建视图2
CREATE VIEW myV_grades
AS
SELECT * FROM job_grades;

#使用视图
SELECT myV_avg_dep.*, myV_grades.grade_level FROM myV_avg_dep INNER JOIN 
myV_grades
ON myV_avg_dep.ag
BETWEEN myV_grades.lowest_sal AND myV_grades.highest_sal;

#创建视图
CREATE VIEW myView3
AS
SELECT myV_avg_dep.*, myV_grades.grade_level FROM myV_avg_dep INNER JOIN 
myV_grades
ON myV_avg_dep.ag
BETWEEN myV_grades.lowest_sal AND myV_grades.highest_sal;

#使用视图
SELECT * FROM myView3;


#3.查询平均工资最低的部门信息
SELECT * FROM myView2 ORDER BY ag ASC LIMIT 0, 1;
SELECT * FROM myView2 ORDER BY ag ASC LIMIT 1;




#4.查询平均工资最低的部门名和工资
CREATE VIEW myView4
AS
SELECT * FROM myView2 ORDER BY ag ASC LIMIT 1;

#
SELECT dep.*, m.ag FROM myView4 m
INNER JOIN departments dep 
ON m.department_id = dep.department_id;


#二、视图的修改

#方式一：
/*
create or replace view  视图名
as
查询语句;

*/
SELECT * FROM myView4;

#修改视图
CREATE OR REPLACE VIEW myView4
AS
SELECT job_id, AVG(Salary) FROM employees
GROUP BY job_id;

#方式二：
/*
语法：
alter view 视图名
as 
查询语句;

*/

#修改视图
ALTER VIEW myView4
AS
SELECT * FROM employees;

#三、删除视图
/*

语法：drop view 视图名,视图名,...;

注意：前提是必须有该视图的drop权限
*/
#一次删除一个视图
DROP VIEW myview2;
#一次删除多个视图
DROP VIEW myview3, myview4;
#
DROP VIEW IF EXISTS myview3, myview4;


#四、查看视图
DESC myView4;
#查询某个视图的定义
SHOW CREATE VIEW myView4;


#创建视图
CREATE VIEW testView1
AS
SELECT COUNT(*) FROM employees;

#查询视图
SELECT * FROM testView1;

#修改视图
CREATE OR REPLACE VIEW testView1
AS
SELECT AVG(salary) FROM employees;

#案例
#一、创建视图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;

#二、创建视图emp_v2，要求查询部门的最高工资高于12000的部门信息
#创建视图
CREATE OR REPLACE VIEW max_sal_dep
AS
SELECT MAX(salary) max_sal ,department_id FROM employees
GROUP BY department_id
HAVING max_sal > 12000;

#创建视图
CREATE OR REPLACE VIEW emp_v2
AS
SELECT max_sal_dep.max_sal, dep.* FROM departments AS dep
INNER JOIN max_sal_dep
ON dep.department_id = max_sal_dep.department_id;

#使用视图(移花接木)
SELECT * FROM emp_v2;


#注意：mysql中，视图不支持子查询(虽然不支持子查询，但是我们可以移花接木)
CREATE OR REPLACE VIEW emp_v2
AS
SELECT max_sal_dep.*, dep.* FROM departments AS dep
INNER JOIN
(SELECT MAX(salary) AS max_sal ,department_id FROM employees
GROUP BY department_id
HAVING max_sal > 12000) max_sal_dep
ON dep.department_id = max_sal_dep.department_id;

#
#
SELECT * FROM my_employees;
INSERT INTO my_employees(last_name, userid) VALUES('段誉', 5);

#
SHOW VARIABLES LIKE  'autocommit%';

#五、视图的更新(指的是对视图的数据的更新)

DESC book;
DESC booktype;
SHOW CREATE TABLE book;
ALTER TABLE book MODIFY COLUMN bid INT AUTO_INCREMENT;
#
SELECT * FROM book;
SELECT * FROM booktype;

#创建视图(这是一个超级简单的视图)
CREATE OR REPLACE VIEW v1
AS
SELECT bName, price FROM book;

#使用视图
SELECT * FROM v1;

#
DESC book;
#
SELECT * FROM book;


/*
注意：对于特别简单的视图，是可以更新视图中的数据的，但是对于复杂的视图，是没办法更新视图中的数据的，
如果对视图中的数据更新成功，那也会对原始表中的数据进行更新(即会影响原始表中的数据)
*/

#对一个超级简单的视图，插入数据，可以成功插入数据，同时原始表中也相应的插入了该条数据
#对视图进行插入数据的操作(下面这条sql语句，能更新视图中的数据，同时也更新了原始表中的数据)
#1.向视图中插入数据(成功向视图中插入数据，同时成功向原始表中插入数据)
INSERT INTO v1 VALUES('平凡的世界', 88.36);

#2.向视图中修改数据(视图中的数据修改成功，原始表中的数据也修改成功)
UPDATE v1 SET bName = '新笑傲江湖' WHERE bName = '笑傲江湖';


#3.向视图中删除数据(视图中的数据删除成功，原始表中的数据也删除成功)
DELETE FROM v1 WHERE bName = '平凡的世界';


#创建一个稍微复杂一点的视图
CREATE OR REPLACE VIEW v2
AS
SELECT bName, price, price * 5 AS myTest FROM book;
#查看视图
SELECT * FROM v2;

#向视图中插入数据(向视图中插入数据失败)对于复杂的视图，是没办法更新视图中的数据的
INSERT INTO v2 VALUES('围城', 26.37, 135.67);


#以下情况不能对视图更新
/*
具备以下特点的视图不允许更新

1.包含以下关键字的SQL语句：分组函数、DISTINCT、GROUP BY、HAVING、UNION或者UNION ALL
2.常量视图
3.SELECT中包含子查询
4.JOIN
5.FROM一个不能更新的视图
6.WHERE子句的子查询引用了FROM子句中的表
等等等，可能还有其他情况，就不依次列举了

总之，对于特别简单的视图，是可以更新视图中的数据的，但是对于复
杂的视图，是没办法更新视图中的数据的

*/

#1.包含以下关键字的sql语句：分组函数、distinct、group  by、having、union或者union all
CREATE OR REPLACE VIEW v3
AS
SELECT MAX(price) m, bName
FROM book
GROUP BY bName;

#
SELECT * FROM v3;
#更新
UPDATE v3 SET m = 127.58 WHERE bName = '三国演义';

#2.常量视图
CREATE OR REPLACE VIEW v4
AS
SELECT '令狐冲' AS userName;

#
SELECT * FROM v4;

#更新
UPDATE v4 SET userName = '韦小宝';

#3.Select中包含子查询
CREATE OR REPLACE VIEW v5
AS
SELECT bName,(SELECT MAX(price) FROM book) AS max_price
FROM book;

#
SELECT * FROM v5;
#更新
UPDATE v5 SET max_price = 220.56;

#4.join
CREATE OR REPLACE VIEW v6
AS
SELECT bName,price,bt.name
FROM book b
JOIN bookType bt
ON b.bTypeId = bt.id;

#
SELECT * FROM v6;
SELECT * FROM book;
#更新可以成功，但是插入却是失败，我们统一认为该视图不能更新
#更新(更新成功)
UPDATE v6 SET bName = '新射雕英雄传' WHERE bName='射雕英雄传';
#插入(插入失败)
INSERT INTO v6 VALUES('水浒传', 55.36, '历史');

#5.from一个不能更新的视图
CREATE OR REPLACE VIEW v7
AS
SELECT * FROM v5;

#
SELECT * FROM v7;

#更新
UPDATE v7 SET max_price = 188.56 WHERE bName = '天龙八部';

#6.where子句的子查询引用了from子句中的表
CREATE OR REPLACE VIEW v8
AS
SELECT bName, price
FROM book
WHERE bid IN(
	SELECT bid
	FROM book
	WHERE price BETWEEN 100 AND 150
);

#
SELECT * FROM v8;
#更新
UPDATE v8 SET price = 89.76 WHERE bName = '三国演义';