镇江建设局网站,代理记账 营销型网站,wordpress 在线知识库,中国排名前十互联网公司View#xff08;视图#xff09;是一个不存在的虚拟表。 其实质是根据SQL语句获取动态数据集并命名。 用户只需要使用视图名就可以获取结果集#xff0c;并作为表来使用。数据库中只存储了视图的定义#xff0c;不存储视图中的数据。 这些数据存储在原始表中。当使用视图查…View视图是一个不存在的虚拟表。 其实质是根据SQL语句获取动态数据集并命名。 用户只需要使用视图名就可以获取结果集并作为表来使用。数据库中只存储了视图的定义不存储视图中的数据。 这些数据存储在原始表中。当使用视图查询数据时数据库系统会从原始表中检索出相应的数据。 因此视图中的数据依赖于原始表中的数据。 一旦表中的数据发生变化视图中显示的数据也会发生变化。
视图的作用为简化代码将可重用的查询封装到视图中以实现可重用性并使复杂的查询易于理解和使用。为了安全起见如果一个表中有很多数据而且有很多信息你不想让大家看到这时候可以使用视图比如社保基金表可以使用 视图只显示姓名、地址不显示社保号和工资等不同用户可设置不同视图。
一、创建视图
create [or replace] [algorithm {undefined | merge | temptable}]
view view_name [(column_list)]
as select_statement
[with [cascaded | local] check option] 参数说明 1algorithm可选项表示视图选择的算法。 2view_name 表示要创建的视图名称。 3column_list可选项指定视图中各个属性的名词默认情况下与SELECT语句中的查询的属性相同。 4select_statement表示一个完整的查询语句将查询记录导入视图中。 5[with [cascaded | local] check option]可选项表示更新视图时要保证在该视图的权限范围之内。 1. 数据准备
创建数据库mydb6_view
create database mydb6_view;create or replace view view1_emp
as
select ename,job from emp; -- 查看表和视图
show full tables;2. 修改视图
修改视图是指修改数据库中现有表的定义。 当基础表的某些字段发生变化时可以通过修改视图来保持视图与基础表的一致性。
通过 CREATE OR REPLACE VIEW 语句和 ALTER VIEW 语句在 MySQL 中修改视图。
alter view 视图名 as select语句;
例如
alter view view1_emp
as
select a.deptno,a.dname,a.loc,b.ename,b.sal from dept a, emp b where a.deptno b.deptno;某些视图是可更新的。 也就是说它们可以用在 UPDATE、DELETE 或 INSERT 等语句中以更新基表的内容。 对于可更新的视图视图中的行与基表中的行之间必须存在一对一的关系。 如果视图包含以下任何结构则该视图不可更新 聚合函数SUM(), MIN(), MAX(), COUNT()等 DISTINCT GROUP BY HAVING UNION或UNION ALL 位于选择列表中的子查询 JOIN FROM子句中的不可更新视图 WHERE子句中的子查询引用FROM子句中的表。 仅引用文字值在该情况下没有要更新的基本表
虽然可以在视图中更新数据但是有很多限制。一般来说最好使用视图作为查询数据的虚拟表而不是通过视图来更新数据。因为在使用视图更新数据时如果没有充分考虑视图中更新数据的局限性可能会导致数据更新失败。
-- ---------更新视图-------
create or replace view view1_emp
as
select ename,job from emp;update view1_emp set ename 周瑜 where ename 鲁肃; -- 可以修改
insert into view1_emp values(孙权,文员); -- 不可以插入-- ----------视图包含聚合函数不可更新--------------
create or replace view view2_emp
as
select count(*) cnt from emp;insert into view2_emp values(100);
update view2_emp set cnt 100; -- ----------视图包含distinct不可更新---------
create or replace view view3_emp
as
select distinct job from emp;insert into view3_emp values(财务);-- ----------视图包含group by 、having不可更新------------------create or replace view view4_emp
as
select deptno ,count(*) cnt from emp group by deptno having cnt 2;insert into view4_emp values(30,100);-- ----------------视图包含union或者union all不可更新----------------
create or replace view view5_emp
as
select empno,ename from emp where empno 1005
union
select empno,ename from emp where empno 1005;insert into view5_emp values(1015,韦小宝);-- -------------------视图包含子查询不可更新--------------------
create or replace view view6_emp
as
select empno,ename,sal from emp where sal (select max(sal) from emp);insert into view6_emp values(1015,韦小宝,30000);-- ----------------------视图包含join不可更新-----------------
create or replace view view7_emp
as
select dname,ename,sal from emp a join dept b on a.deptno b.deptno;insert into view7_emp(dname,ename,sal) values(行政部,韦小宝,30000);-- --------------------视图包含常量文字值不可更新-------------------
create or replace view view8_emp
as
select 行政部 dname,杨过 ename;insert into view8_emp values(行政部,韦小宝);
3. 其他操作
重命名视图
-- rename table 视图名 to 新视图名;
rename table view1_emp to my_view1;删除视图
-- drop view 视图名[,视图名…];
drop view if exists view_student;删除视图时只能删除视图的定义不会删除数据。
练习
-- 1查询部门平均薪水最高的部门名称
select dname from dept a ,(select deptno,avg(sal) from emp group by deptno order by avg(sal) desc limit 1) b
where a.deptno b.deptno; -- 2查询员工比所属领导薪资高的部门名、员工名、员工领导编号
select * from dept x,
(select a.ename aname ,a.sal asal,b.ename bname,b.sal bsal,a.deptno
from emp a, emp b
where a.mgr b.empno and a.sal b.sal) y
where x.deptno y.deptno;-- 3查询工资等级为4级2000年以后入职的工作地点为北京的员工编号、姓名和工资并查询出薪资在前三名的员工信息
create view xxx
as
SELECT e.empno,e.ename,e.sal,e.hiredate
FROM emp e,dept d,salgrade s
WHERE (e.sal BETWEEN losal AND hisal) AND s.GRADE 4
AND year(e.hiredate) 2000
AND d.loc 北京;select * from
(
select *,dense_rank() over(order by sal desc ) rn
from xxx
) t
where t.rn 3;
