/*
emp 表 dept 部门表 salgrade 表
empno 员工编号 deptno 部门编号 grade 工资等级
ename 员工姓名 dname 部门名称 losal 工资范围下限
job 职位 loc 工作地点 hisal 工资范围上限
mgr 经理编号
hiredate 入职日期
sal 薪金
comm 奖金
deptno 部门编号
*/
--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
--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
--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
--(备注:字符可以和日期以及数值型进行互相转换)
--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