Mysql_view视图语法及介绍

#视图介绍含义：虚拟表，和普通表一样使用 mysql5.1版本出现的新特性，是通过表动态生成的数据应用场景：多个地方用到同样的查询结果该查询结果使用的sql语句较复杂视图与普通表的对比：创建语法的关键字是否实际...

YanQiu_Only

10445人浏览 · 2019-02-19 15:18:24

YanQiu_Only · 2019-02-19 15:18:24 发布

#视图介绍

含义：虚拟表，和普通表一样使用

mysql5.1版本出现的新特性，是通过表动态生成的数据

应用场景：

多个地方用到同样的查询结果

该查询结果使用的sql语句较复杂

视图与普通表的对比：

创建语法的关键字是否实际占用物理空间使用

视图 create view 只是保存sql逻辑增删改查，一般不能增删改一般只用到查

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

#案例：查新姓名以字母k开头的员工名和部门名

#不用视图做以前的连接查询

SELECT Last_name,department_name

FROM employees e

INNER JOIN departments d ON e.`department_id`=d.`department_id`

WHERE e.`last_name` LIKE 'k%';

#视图就相当于将查询进行封装了

CREATE VIEW v1

SELECT Last_name,department_name

FROM employees e

INNER JOIN departments d ON e.`department_id`=d.`department_id`;

#直接用视图做表进行筛选

SELECT * FROM v1 WHERE `last_name` LIKE 'k%';

select *from v1;　同是也可以查看创建视图的信息表

#一、创建视图

语句：

create view 视图名

查询语句;

#案例1.查询姓名中包含a字符的员工名、部门名和工种信息

#1.创建视图

CREATE VIEW myv1

SELECT Last_name,department_name,j.*

FROM employees e

INNER JOIN departments d ON e.department_id=d.department_id

INNER JOIN jobs j ON e.job_id=j.job_id;

#2.对视图进行筛选

SELECT *FROM myv1 WHERE Last_name LIKE '%a%';

#案例2.查询各部门的平均工资级别

#Ⅰ.创建视图查看每个部门的平均工资

CREATE VIEW myv2

SELECT AVG(e.salary) ag_sal,e.department_id

FROM employees e

GROUP BY e.department_id;

#Ⅱ.在Ⅰ的基础上进行一个非等值连接的操作

SELECT ag_sal,department_id,grade_level

FROM myv2 v2

INNER JOIN job_grades g

ON v2.`ag_sal` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

#mysql视图是不支持子查询的

CREATE VIEW myv2

SELECT ag_sal,department_id,grade_level

FROM (#子查询mysql不支持

SELECT AVG(e.salary) ag_sal,e.department_id

FROM employees e

GROUP BY e.department_id

) avg_dep

INNER JOIN job_grades g ON ag_sal BETWEEN g.lowest_sal AND g.highest_sal;

#案例3.查询平均工资最低的部门信息

#1.创建视图查询每个部门的平均工资的部门信息

CREATE VIEW myv3

SELECT AVG(salary) ag,d.*

FROM employees e

INNER JOIN departments d ON e.department_id=d.department_id

GROUP BY d.department_id;

#2.在myv3视图基础上进行筛选

SELECT MIN(myv3.`ag`),myv3.* FROM myv3;

#案例4.查询平均工资最低的部门名和工资

在myv3视图基础上进行筛选

SELECT MIN(myv3.`ag`) AS salary,department_name FROM myv3;

#二、视图的修改

#方式一：

create or replace view 视图名 v3视图如果存在则替换新的视图不存在直接创建

查询语句;

SELECT * FROM v1;

#将视图v1 替换成查询每个工种的平均工资

CREATE OR REPLACE VIEW v1

SELECT AVG(salary),job_id

FROM employees

GROUP BY job_id;

#方式二：

语法：

alter view 视图名

查询语句;

#v3修改为员工工资的级别

ALTER VIEW v1

SELECT last_name,salary,grade_level

FROM employees e

INNER JOIN job_grades g

ON e.salary BETWEEN `lowest_sal` AND `highest_sal`;

#三、删除视图

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

DROP VIEW v1;

#四、查看视图

#方式一 desc 视图名;

DESC v1;

#方式二 show create view 视图名;

SHOW CREATE VIEW v1;

#案例一、创建视图emp_v1,要求查询电话号码以'011'开头的员工姓名和工资、邮箱

CREATE OR REPLACE VIEW emp_v1

SELECT last_name,salary,email

FROM employees

WHERE phone_number LIKE '011%';

SELECT * FROM emp_v1;

#案例二.创建视图emp_v2，要求查询部门的最高工资高于12000的部门信息

CREATE OR REPLACE VIEW emp_2

SELECT MAX(salary) mx,department_id

FROM employees

GROUP BY department_id;

#通过查询emp_v2视图对视图进行筛选

SELECT d.*,mx

FROM emp_2

INNER JOIN departments d

ON emp_2.`department_id`=d.`department_id`

WHERE mx>12000;

#五、视图的更新

#可以更改视图的数据

CREATE OR REPLACE VIEW my_info

SELECT Last_name,email

FROM employees;

SELECT *FROM my_info;

#1.插入

INSERT INTO my_info VALUE('李四','admi999@qq.com');

视图插入数据成功

同时原始employees表中也成功插入数据

#2.更新

#my_info视图成功修改

UPDATE my_info SET Last_name='俊杰' WHERE Last_name='李四';

同时原始employees表中也成功修改数据

#3.删除

#my_info视图成功删除原employees表也修改删除

DELETE FROM my_info WHERE Last_name='俊杰';

同时原始employees表中也成功删除数据

对于视图的更新、修改、删除、会对原始表进行修改一般会对视图进行权限限制一般视图是只读

#具备以下特点的视图不允许更新的

#1.包含以下关键字的sql语句：分组函数、 distinct、 group by、 having、 union或者union all

#创建视图查询每个部门的最高工资

CREATE OR REPLACE VIEW max_sa_dep

SELECT MAX(salary) mx,department_id

FROM employees

GROUP BY department_id;

SELECT *FROM max_sa_dep;

#更新操作存在分组函数和group by子句执行更新操作会出现不允许更新错误

UPDATE max_sa_dep SET mx=11111 WHERE department_id=10

#2.常量视图

CREATE OR REPLACE VIEW max_sa_dep

SELECT 'tomcat' NAME;

SELECT *FROM max_sa_dep;

#对常量视图也不可更新等相关操作也会报错

UPDATE max_sa_dep SET NAME='lucy';

#3.Select中包含子查询

CREATE OR REPLACE VIEW v2

SELECT (SELECT MAX(salary) FROM employees) 最高工资;

#对Select中包含子查询视图也不可更新等相关操作也会报错

UPDATE v2 SET 最高工资=10000;

#4.join 可以update 但不能insert、delete

CREATE OR REPLACE VIEW v2

SELECT Last_name,department_name

FROM employees e

INNER JOIN departments d ON e.department_id=d.department_id;

#更新但还是更新没错但对原始表数据进行了修改插入就会报异常

UPDATE v2 SET Last_name='李四' WHERE Last_name='whalen';

INSERT INTO v2 VALUE('lucy','xxx'); 插入就会报异常

#5.from一个不能更新的视图

CREATE OR REPLACE VIEW v3

SELECT * FROM v2; #那视图v2做数据创建v3视图

#更新

INSERT INTO v3 VALUE('lucy','xxx');

#6.where子句的子查询引用了from子句中的表

CREATE OR REPLACE VIEW v4

SELECT Last_name,Salary,email

FROM employees

WHERE manager_id IN (

SELECT e.manager_id

FROM employees e

WHERE e.manager_id IS NOT NULL

);

#更新

SELECT * FROM v4;

UPDATE v4 SET Salary=12222 WHERE Last_name='kocjjar';

一门面向 Data 和 AI 的低代码、云原生的开源编程语言

无需安装部署，在线快速体验 Byzer

更多推荐

编译时权限控制

前言权限控制，对于 MLSQL 而言的重要程度可以说是生命线。 MLSQL 需要面对各式各样的资源访问，比如 MySQL, Oracle,HDFS，Hive，Kafka，Sorl，ElasticSearch，Redis，API，Web等等，不同用户对这些数据源（以及表，列）的权限是不一样的。传统模式是，每个用户都需要有个 proxy user，然后到每个数据源里面给这个 proxy user

Byzer 白泽

Byzer 支持 JDBC 聚合下推

聚合下推PR链接聚合下推我们知道 Byzer 支持多数据源和联邦查询，可以方便分析师在一个平台上快速的分析来自多种数据源的数据，从而进行灵活的探索式分析。使用场景和优势当前 Byzer 中加载数据的方式会拉取明细数据到 spark 中进行聚合计算，对于小数据量或者在分布式文件系统上的数据源来说是常规操作。但是对于 JDBC 数据源或者有分析能力的 OLAP 系统来说拉取明细数据可能就不是最

Byzer 白泽

Byzer 术语表

Byzer-lang Byzer ，又称为 Byzer-lang，一门面向 Data 和 AI 的低代码、云原生的开源编程语言。 Byzer 是一门结合了声明式编程和命令式编程的混合编程语言，其低代码且类 SQL 的编程逻辑配合内置算法及插件的加持，能帮助数据工作者们高效打通数据链路，完成数据的清洗转换，并快速地进行机器学习相关的训练及预测。 Byzer 希望能够提供一套语言、一个引擎，就能覆盖整