Oracle第七课

2022-06-01 15:30:24

Oracle第七课

一、函数

内置函数

单行函数

字符函数，数字函数，日期函数，转换函数

多行函数

统计函数(最大，最小，平均值，求和，计数)

-- 有多少员工（计数）
SELECT COUNT(*) FROM emp;
-- 最高工资
SELECT MAX(sal) FROM emp;
-- 最低工资
SELECT MIN(sal) FROM emp;
-- 平均工资
SELECT AVG(sal) FROM emp;
-- 求每个部门的最高，最低，平均工资，人数，工资总和
SELECT deptno,MAX(sal),MIN(sal),AVG(sal),SUM(sal),COUNT(*) FROM emp GROUP BY deptno HAVING deptno IS NOT NULL;
-- 删除员工表里部门为空的员工
DELETE FROM emp WHERE deptno IS NULL;

-- group by having 过滤条件,having是在分组之后，根据其结果再过滤

-- 分组和统计是相关的，使用的比较多，having只能跟在分组后，不能单独使用

-- SQL优化：尽量在分组前过滤数据，而不是在分组后过滤数据

nv1(x1,x2)函数：如果x1为null,则值为x2

-- 将员工中奖金为空的换成0
SELECT nv1(comm,0) FROM emp;

nv3(x1,x2,x3)函数：如果值是x1,则返回x2,否则的话返回x3
```
-- 将员工有奖金的置为1，没奖金的置为0
select nv2(comm,1,2) from emp;
```
-- nvl是解决空值的问题，nvl2也是空值的问题，区别是nvl2当参数1不为空时提供了额外的

decode函数：相当于switch选择语句

常用作报表的处理

-- 如果job = 'CLERK',则显示为店员，如果job = 'SALESMAN',则显示为业务员...
SELECT DECODE(job,'CLERK','店员','SALESMAN','业务员','MANAGER','经理','PRESIDENT','董事长','ANALYST','分析员','未知') FROM emp;
-- 60到80为普通，80到90为良好，90到100为优秀
SELECT decode(trunc(&cj/10),6,'普通',7,'普通',8,'良好',9,'优秀',10,'优秀','不及格') FROM dual;

sum和decode的应用：行转列

CREATE TABLE t_score(NAME VARCHAR(10),course VARCHAR(10),score NUMBER(4,1));
INSERT INTO t_score VALUES('赵一','Java',100);
INSERT INTO t_score VALUES('赵一','C',96);
INSERT INTO t_score VALUES('赵一','C++',85);
INSERT INTO t_score VALUES('钱一','Java',95);
INSERT INTO t_score VALUES('钱一','C',84);
INSERT INTO t_score VALUES('钱一','C++',93);
SELECT NAME,SUM(score) 总成绩, SUM(DECODE(course,'Java',score)) JAVA,SUM(DECODE(course,'C',score)) C,SUM(DECODE(course,'C++',score)) CPlus FROM t_score GROUP BY NAME;

-- 用sum函数把多行数据统计起来，一个sum是一列

应用

-- 建一个居民收入表，1-12月，每月输入它的工资，表中有居民姓名name
CREATE TABLE resident(NAME VARCHAR(6) NOT NULL,r_month number(2) NOT NULL,sal NUMBER(8) NOT NULL);
INSERT INTO resident VALUES('赵一',1,10000);
INSERT INTO resident VALUES('赵一',2,10500);
INSERT INTO resident VALUES('赵一',3,9000);
INSERT INTO resident VALUES('赵一',4,10030);
INSERT INTO resident VALUES('赵一',5,10500);
INSERT INTO resident VALUES('赵一',6,10000);
INSERT INTO resident VALUES('赵一',7,12000);
INSERT INTO resident VALUES('赵一',8,10500);
INSERT INTO resident VALUES('赵一',9,10800);
INSERT INTO resident VALUES('赵一',10,10900);
INSERT INTO resident VALUES('赵一',11,10010);
INSERT INTO resident VALUES('赵一',12,10800);
SELECT * FROM resident;
DROP TABLE resident;
-- 显示每个人每月的工资，以列的形式显示，2020我国的平均收入是40000元左右，显示该居民是否达到或拖后腿
SELECT NAME,SUM(DECODE(r_month,1,sal)) 一月,
SUM(DECODE(r_month,2,sal)) 二月,
SUM(DECODE(r_month,3,sal)) 三月,
SUM(DECODE(r_month,4,sal)) 四月,
SUM(DECODE(r_month,5,sal)) 五月,
SUM(DECODE(r_month,6,sal)) 六月,
SUM(DECODE(r_month,7,sal)) 七月,
SUM(DECODE(r_month,8,sal)) 八月,
SUM(DECODE(r_month,9,sal)) 九月,
SUM(DECODE(r_month,10,sal)) 十月,
SUM(DECODE(r_month,11,sal)) 十一月,
SUM(DECODE(r_month,12,sal)) 十二月,
DECODE(TRUNC(SUM(sal)/40000),0,'拖后腿','合格') 是否合格
 FROM resident r GROUP BY NAME;

SQL的递归

查出一个奶牛场某头奶牛的所有后代和父辈

-- 第一代奶牛的父非为0
insert into cow values(1,0);
insert into cow values(100,1);
insert into cow values(101,1);
insert into cow values(800,100);
insert into cow values(902,101);
insert into cow values(2,0);
insert into cow values(302,2);

-- 找id为1的父辈和字辈
-- 从id为1开始，找儿子：当前id等于下一行的parent_id，找父亲：当前id的parent_id等于下一个要找的id
SELECT * FROM cow START WITH ID=1 CONNECT BY PRIOR ID = parent_id;

二、作业

一个公司有很多部门有上级部门

一个员工有他所在的部门，根据员工编号找到他所在的所有上级部门。

编号	姓名	部门
1	张三	301
2	李四	401

编号	名称	上级部门
301	财务部	11
11	集团中心	2
2	财务总监	0

显示出张三的上级部门：财务部，集团中心，财务总监

码农公寓

Oracle第七课

一、函数

二、作业

相关文章