mysql学习笔记05-视图

最新推荐文章于 2025-05-12 13:57:25 发布

Charlotte297

最新推荐文章于 2025-05-12 13:57:25 发布

阅读量525

点赞数 12

CC 4.0 BY-SA版权

文章标签： mysql 学习笔记

本文链接：https://blog.csdn.net/qq_68289621/article/details/136034300

本文详细介绍了如何在数据库中使用SQL进行视图的创建、修改、更新，包括视图的语法、重命名和删除，以及涉及的SQL命令如JOIN、子查询和聚合函数的应用实例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

视图

创建视图

-- create {or replace } view  名字  as select语句 
-- or replace 加上表示视图存在就修改
create or replace view view1 as select * from employee;

修改视图

-- 方法1：创建视图也是修改视图  create {or replace } view  名字  as select语句 
create or replace view view1 as select * from employee;
-- 方法2：alter view 视图名字 as select语句
alter view view1 as select ename,eid;

更新视图

-- 修改视图得值，也相当于修改了原表的值,必须一一对应表中
--  insert into view(原表的字段) values（对应的值）
--  update view set …… where ……
insert into view1(empno,ename,job,mgr,hiredate,sal,comm,depyno) values('1001','甘雨'，'文员','1013','2002-11-02',8000,1000,20);

update view1 set ename='李白' where empno='1001';
-- 聚合函数（sum(),min(),max())
-- distinct
-- group by 
-- having 
-- union
-- 子查询
-- join
-- 以上不可更改，因为以上查询的值不是原表中存在的值或者已经被某函数处理的值

重命名视图

-- rename table 视图名 to 新视图名
rename table view to view1;

删除视图

-- drop view [if exists] 视图名
drop view if exists view1;

练习

use test_view;
CREATE TABLE dept  (
  deptno int NOT NULL,
  dname varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  loc varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`deptno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO dept VALUES (10, '教研部', '北京');
INSERT INTO dept VALUES (20, '学工部', '上海');
INSERT INTO dept VALUES (30, '销售部', '广州');
INSERT INTO dept VALUES (40, '财务部', '武汉');

CREATE TABLE emp  (
  empno int NOT NULL,
  ename varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  job varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  mgr int NULL DEFAULT NULL,
  hiredate date NULL DEFAULT NULL,
  sal decimal(7, 2) NULL DEFAULT NULL,
  COMM decimal(7, 2) NULL DEFAULT NULL,
  deptno int NULL DEFAULT NULL
) ;
drop table emp;
INSERT INTO emp VALUES (1001, '甘宁', '文员', 1013, '2000-12-17', 8000.00, NULL, 20);
INSERT INTO emp VALUES (1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000.00, 3000.00, 30);
INSERT INTO emp VALUES (1003, '殷天正', '销售员', 1006, '2001-02-22', 12500.00, 5000.00, 30);
INSERT INTO emp VALUES (1004, '刘备', '经理', 1009, '2001-04-02', 29750.00, NULL, 20);
INSERT INTO emp VALUES (1005, '谢逊', '销售员', 1006, '2001-09-28', 12500.00, 14000.00, 30);
INSERT INTO emp VALUES (1006, '关羽', '经理', 1009, '2001-05-01', 28500.00, NULL, 30);
INSERT INTO emp VALUES (1007, '张飞', '经理', 1009, '2001-09-01', 24500.00, NULL, 10);
INSERT INTO emp VALUES (1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000.00, NULL, 20);
INSERT INTO emp VALUES (1009, '曾阿牛', '董事长', NULL, '2001-11-17', 50000.00, NULL, 10);
INSERT INTO emp VALUES (1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000.00, 0.00, 30);
INSERT INTO emp VALUES (1011, '周泰', '文员', 1008, '2007-05-23', 11000.00, NULL, 20);
INSERT INTO emp VALUES (1012, '程普', '文员', 1006, '2001-12-03', 9500.00, NULL, 30);
INSERT INTO emp VALUES (1013, '庞统', '分析师', 1004, '2001-12-03', 30000.00, NULL, 20);
INSERT INTO emp VALUES (1014, '黄盖', '文员', 1007, '2002-01-23', 13000.00, NULL, 10);

CREATE TABLE salgrade  (
  grade int NOT NULL,
  losal int NULL DEFAULT NULL,
  hisal int NULL DEFAULT NULL
) ;

INSERT INTO salgrade VALUES (1, 7000, 12000);
INSERT INTO salgrade VALUES (2, 12010, 14000);
INSERT INTO salgrade VALUES (3, 14010, 20000);
INSERT INTO salgrade VALUES (4, 20010, 30000);
INSERT INTO salgrade VALUES (5, 30010, 99990);




-- 查询部门平均薪水最高的部门名称
create or replace view view1 as select  emp.deptno,dept.dname,round(avg(sal)) rn from emp join dept on dept.deptno = emp.deptno group by emp.deptno;
select * 
from (select * ,rank() over (order by rn desc) grade from view1) t
where grade = 1;
-- select *from 
--                      (select *, dense_rank() over (order by avg desc) as grade 
--                      from  
--                      (select deptno, avg(sal) as avg     from emp  group by deptno
--                          )t)tt
-- 
-- where grade =1;
-- 
-- select sum(sal),deptno ,count(empno),sum(sal)/count(empno) from emp group by deptno order by sal ;
-- -- 查询员工比领导薪资高的部门名，员工名，员工领导编号
select a.deptno,c.dname,a.ename,a.mgr,a.sal sal1,b.sal from emp a join emp b on a.mgr = b.empno join dept c on a.deptno=c.deptno where a.sal > b.sal;
-- 3：查询工资等级为4级，2000年以后入职的工作地点为上海的员工编号、姓名和工资，并查出薪资在前三名的员工信息
-- 3.1需求1：查询工资等级为4级，2000年以后入职的工作地点为上海的员工编号、姓名和工资
select * 
from emp a left join dept b on b.deptno = a.deptno  left join salgrade c on c.grade= 4 and a.sal between c.losal and c.hisal 
where  loc = '北京' and YEAR(hiredate) > '2000'

create or replace view  view2 
as select a.deptno,b.dname,a.ename,b.loc,a.empno ,a.sal  from emp a join salgrade  c on a.sal between c.losal and c.hisal join dept b on a.deptno = b.deptno 
where loc = '上海'and YEAR(hiredate) > '2000' ;

select * 
from 
(
select * ,rank() over (order by sal desc) as grade 
from view2 ) t
where  grade <=3;