一、作业
一个公司有很多部门有上级部门
一个员工有他所在的部门，根据员工编号找到他所在的所有上级部门。
| 编号 | 姓名 | 部门 |
|---|---|---|
| 1 | 张三 | 301 |
| 2 | 李四 | 401 |
| 编号 | 名称 | 上级部门 |
|---|---|---|
| 301 | 财务部 | 11 |
| 11 | 集团中心 | 2 |
| 2 | 财务总监 | 0 |
显示出张三的上级部门：财务部，集团中心，财务总监
-- 创建员工表
CREATE TABLE emp(ID NUMBER(2) PRIMARY KEY,
NAME VARCHAR(6) NOT NULL,
deptno NUMBER(4)CONSTRAINT fk_dept REFERENCES dept(ID) );
INSERT INTO emp VALUES(1,'张三',301);
INSERT INTO emp VALUES(2,'李四',401);
SELECT * FROM emp;
-- 创建部门表
CREATE TABLE dept(ID NUMBER(4) PRIMARY KEY,NAME VARCHAR(10) NOT NULL,pre_id NUMBER(4));
INSERT INTO dept VALUES(301,'财务部',11);
INSERT INTO dept VALUES(11,'集团中心',2);
INSERT INTO dept VALUES(2,'财务总监',0);
INSERT INTO dept VALUES(401,'保安部',NULL);
-- 张三的上级部门
SELECT * FROM dept START WITH ID=(SELECT deptno FROM emp WHERE NAME='张三') CONNECT BY PRIOR pre_id = ID;
二、内置函数
-
单行函数
-
字符函数
-- 返回字符的ascii值：ascii函数
-- 将连个字符或字符串拼接起来：concat函数
-- 查找字符：instr函数
-- 返回字符串的长度：length函数
-- 将所有字符全部转换为小写：lower函数
-- 将所有字符全部转换为大写：upper函数
-- 去掉字符串中的空格：ltrim(去掉左边的空格),rtrim(去掉字符串右边的空格),trim(去掉字符串两边的空格)
-- 替换字符：replace函数
-- 截取字符中的一段：substr函数 -
数字函数
-- 求绝对值：abs函数
-- 求大于或等于某个数的最小值：ceil函数
-- 求小于或等于某个数的最大值：floor函数
-- 四舍五入：round函数
-- 截断函数：trunc函数
-- 取余：mod函数 -
日期函数
-- 显示当前时间：sysdate关键字
-- 在当前时间上增加一个月：add_months函数
-- 求当前月的最后一天十几号：last_day函数
-- 四舍五入日期：round函数
-- 求月份差：months_between函数
-- 求当前日期的下一个星期几是几号：next_day函数
-- 提取员工的入职月份：extract函数
-- 截断日期：trunc函数 -
转换函数
-- 转换成字符：to_char函数
-- 转换成数字：to_number函数
-- 转换成日期：to_date函数
-
-
多行函数
-
统计函数
-- 求最大值：max函数
-- 求最小值：min函数
-- 求员工的个数：count函数
-- 求平均值：avg函数
-- 求和：sum函数 -
解决空值：nvl
-- nv1(comm,0)函数：如果奖金（comm)为空的话，则转换为0
-- nv2(comm,1,0)函数：如果奖金(comm)不为空，则转换为1，否则，转换为0 -
decode函数
-- decode(job,'CLERK','店员');
-- 注释：如果job=’CLERK‘，则显示为店员
-
三、应用
-
行转列
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递归的实现：
-- SELECT * FROM XX STRAT WITH 从什么地方开始 CONNECT BY PRIOR 递归条件
-- 找id为1的子辈
SELECT * FROM cow START WITH ID=1 CONNECT BY PRIOR ID = parent_id;