Oracle 通用表
/*
emp 表 dept 部门表 salgrade 表
empno 员工编号 deptno 部门编号 grade 工资等级
ename 员工姓名 dname 部门名称 losal 工资范围下限
job 职位 loc 工作地点 hisal 工资范围上限
mgr 经理编号
hiredate 入职日期
sal 薪金
comm 奖金
deptno 部门编号
*/
Oracle实例演练
1.字符型函数
--upper（参数）：转换成大写 注：参数可以为字符串，也可以为列的字段
Select UPPER(ename) --列的字段
FROM emp
SElect UPPER('ename') --字符串
from dual
--lower(参数) :转换成小写 注：参数可以为字符串，也可以为列的字段
Select LOWER(ename) --列的字段
FROM emp
--查找员工姓名为king的信息
SELECT ename
FROM emp
where upper(ename) = 'KING'
--initcap(参数): 字符串转换为首字符大写，其余都小写 （ 注：参数可以为字符串，也可以为列的字段）
SELECT initcap(ename)
FROM emp
--concat(参数1，参数2) ：连接两个字符串
SELECT concat('Hello', 'World')
FROM dual --dual:为虚表，利用oracle的引擎，完成计算，并显示内容
--等同于||
SELECT 'Hello'||'World' FROM dual
--substr(s1,n1,n2)
--s1:要操作的字符数据或字段
--n1:要从第几位开始截取， n1若为负数，代表从倒数第几位开始截取
--n2:截取长度, n2省略代表，字符有多长截取多长； n2若为负数，什么也截取不了
--n2不能为负数
--截取每个员工姓名中的第三个字符
SELECT ename, substr(ename, 3, 1)
FROM emp
--查询员工姓名中第三个字符是‘R’的
SELECT *
FROM emp
where substr(ename, 3, 1) ='R'
--length(字符数据参数)
--返回员工姓名字符的长度
SELECT ename, length(ename)
FROM emp
--instr(s1,s2,n1,n2)
--功能：返回S2在s1中，第n1位开始，第n2次出现的位置
--第3,4个参数，不写默认都是1
--查找，在员工姓名中第一次出现‘A’的位置
SELECT ename, INSTR(ename, 'A', 2,1)
FROM emp
--查找，员工姓名中第三个字符是'A"
SELECT ename
FROM emp
WHERE INSTR(ename, 'A', 2, 1) = 3
--查找，员工姓名中，出现'A'的
SELECT ename
FROM emp
WHERE INSTR(ename, 'A',1, 1) <> 0 --等同于 WHERE INSTR(ename, 'A') <> 0
--LPAD(s1,n1,s2)
--返回s1被s2从左面填充到n1长度后的字符串。
SELECT LPAD('Hello', 10, 'World11212')
FROM dual
--RPAD(s1,n1,s2)
--返回s1被s2从右面填充到n1长度后的字符串。
SELECT RPAD('Hello', 15, 'World11212')
FROM dual
--trim(s1 from s2)
--功能：从s2中裁剪s1, 将s2中出现s1的字符的开头和结尾去掉(掐头去尾)
SELECT Trim(' ' FROM ' He llo ')
FROM dual
SELECT TRim('s' FROM 'ssfjdllkfjdlkfsss')
FROM dual
--replace(s1,s2,s3)
--功能：将s1中的s2，用s3替换
SELECT REplace('abc', 'b', 'd')
FROM dual
2.数值型函数
--round(n1, n2) :对n1进行四舍五入到小数点第n2位
--n2为0，或者不写，都是四舍五入到整数位
--n2若为负数，则精确到四舍五入到小数点前n2位
SELECT ROUND(99.567, 2)
FROM dual
SELECT ROUND(99.567)
FROM dual
--trunc(n1, n2):截断函数，截取n1到小数点第n2位
--n2为0，或者不写，都是截取到整数位
--n2若为负数，则精确到截取到小数点前n2位
SELECT TRUNC(99.567, 2)
FROM dual
--mod(n1, n2)
--取余函数
--对0取余，余数为被除数
--对负数取余，取决于被除数的正负关系，
--第一个数为正，无论第二个参数为正或负，都按正数处理
--第一个数为负，无论第二个参数为正或负，都按负数处理
SELECT mod(-300, -500)
FROM dual
3.日期型函数
--sysdate :返回系统当前日期
SELECT SYSDATE
FROM dual
--months_between: 返回两个日期间隔的月
--计算四舍五入后的间隔月份
SELECT ROUND(months_between(sysdate, '01-1月-2000'))
from dual
--add_months :为日期添加月
SELECT hiredate, add_months(hiredate, 6)
from emp
--next_day :返回日期的最近的下一个星期几(星期一~星期日)
SELECT next_day(sysdate, '星期五')
from dual
--last_day: 返回指定日期的当月的最后一天
SELECT last_day(sysdate)
FROM dual
--round : 对日期的四舍五入（精确到...）
-- ROUND(date,‘fmt')将date按照fmt指定的格式进行四舍五入，
--fmt为可选项，如果没有指定fmt，则默认为DD，将date四舍五入为最近的天。
--格式码：世纪CC,年YY，月MM,日DD，小时HH24，分MI，秒SS
--– TRUNC(date[,‘fmt’])将date按照fmt指定的格式进行截断，fmt为可选项，
--如果没有指定fmt，则默认为‘DD’，将date截取为最近的天。
SELECT hiredate, ROUND(hiredate, 'MM')
FROM emp
--Trunc: 对指定日期进行截取
SELECT hiredate, Trunc(hiredate,'MONTH')
FROM emp
--extract ：提取日期中的指定年，月，日(day, month, year)
SELECT hiredate, extract(DAY from hiredate)
from emp
--查询12月份入职的
SELECT *
FROM emp
WHERE extract(MONTH from hiredate) = 12
--对当前日期进行加法： 天/小时/分钟
SELECT sysdate, sysdate + 10 / 24 / 60
FROM dual
3.数据类型转换（字符-日期-数值）
--(备注：字符可以和日期以及数值型进行互相转换)
--to_char
--1、转换日期
select hiredate, to_char(hiredate, 'YYYY') "1" --数字表示年份
,to_char(hiredate, 'YEAR') "2" --全英文大写 表示年份
,to_char(hiredate, 'MM') "3" --不带 ‘月’
,to_char(hiredate, 'MONTH') "4" --带‘月’
,to_char(hiredate, 'DAY') "5" --星期
，to_char(hiredate, 'DY') "6" --星期
，to_char(hiredate, 'HH24:MI:SS AM') "7"
，to_char(hiredate, 'DD "of" MONTH') "8"
FROM emp
--2、转换数值
--$:显示美元符号
--L:显示本地货币符号
--. :显示小数点
--，:显示千分符
SELECT sal, to_char(sal, 'L99999,999.999')
from emp
--to_number(字符转数字)
SELECT to_number('1234567.89', '9999999.99')
FROM dual
--to_date
SELECT sysdate - to_date('01-1月-1981')
from dual
--进行日期相减(第二个参数需要对第一个日期参数进行解释)
SELECT sysdate - to_date('1981年 1月 1日', 'YYYY"年" MONTH DD"日"')
from dual
--查找1981年入职的员工信息
SELECT *
FROM emp
WHERE to_char(hiredate, 'YYYY') = '1981'
--通用函数
--NVL(参数1， 参数2) ： 若参数1不为空返回参数1，否则返回参数2
SELECT ename, NVL(to_char(mgr), '没有经理')
FROM emp
--NVL2(参数1，参数2，参数3)：若参数1不为空返回参数2，否则返回参数3
SELECT sal, comm, NVL2(comm, comm, 0)
FROM emp
--NULLIF(n1,n2): 如果n1, n2相等，返回空值；否则返回n1
SELECT NULLIF(5 + 4, 3 * 3)
FROM dual
--COALESCE(参数多个) ：功能：返回参数中第一个不为空的值
SELECT sal, comm, COALESCE(comm, comm, 0)
FROM emp
--CASE 、 DECODE
SELECT ename, deptno,(case deptno
WHEN 10 THEN '销售部'
WHEN 20 THEN '技术部'
WHEN 30 THEN '管理部'
ELSE '小卖部 ' END) 部门名称
FROM emp
SELECT ename, deptno, DECODE(deptno,
10, '技术部',
20，'销售部',
30, '管理部'，
'小卖不') 部门名称
FROM emp
--多表连接
--查询员工姓名，工资，部门编号，部门名称，工作地点
--”笛卡尔积现象“--一张表中所有行与另一张表中的所有行都发生连接的现象
--产生原因：没有指定有效的连接条件
--等值连接
SELECT ename, sal, emp.deptno, dname, loc
FROM emp, dept
WHERE emp.deptno = dept.deptno
--非等值连接(工资等级)
SELECT ename, sal, grade
FROM emp, salgrade
WHERE sal BETWEEN losal AND hisal
--多表连接
SELECT ename, sal, grade, d.deptno, dname, loc
FROM emp e, dept d, salgrade
WHERE e.deptno = d.deptno
AND sal BETWEEN losal AND hisal
AND grade >= 3
--外部连接(让那些按照连接条件匹配不上的信息，也显示出来)
--备注：不可两侧都有（+）
--查询员工姓名， 部门编号，部门名称，要求将没有员工的部门也显示出来
SELECT ename, d.deptno, dname
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno --(左侧出空行，匹配右边：哪边缺数据，加（+）)
--WHERE e.deptno = d.deptno(+) --没有部门的员工
--插入一行数据
INSERT INTO emp(empno) VALUES (9999)
--在员工表中查询出员工的工资，并计算应交税款：如果工资小于1000,税率为0，
-- 如果工资大于等于1000并小于2000，税率为10％，
-- 如果工资大于等于2000并小于3000，税率为15％，
-- 如果工资大于等于3000，税率为20％.
Select ename,sal,(case trunc(sal/1000)
When 0 then 0
When 1 then sal*0.1
When 2 then sal*0.15
Else sal*0.2 end) 应交税款
From emp