暑假实训-3_Oracle数据库-3_单行函数

-- 2018/07/11 13:30-16:30 实训第2天下午

SELECT * FROM emp;

-- 单行函数

-- 字符函数
-- 2-1 查询员工姓名中包含大写或小写字母A的员工姓名
SELECT ename AS 员工姓名
FROM emp
WHERE INSTR( UPPER(ename), 'A') > 0;

-- 2-2 查询部门编号为10或20，入职时间在81年5月1日之后，并且姓名中包含大写子午A的员工姓名，员工姓名长度（提示、要求使用INSTR函数，不能使用like进行判断）
SELECT ename AS 员工姓名, LENGTH(ename) AS 姓名长度
FROM emp
WHERE deptno IN (10, 20)
AND hiredate > '1-5月-81'
AND INSTR(ename, 'A') > 0;

-- 2-3 查询每个职工的编号、姓名、工资
SELECT CONCAT( CONCAT( RPAD (empno, 10, '*'), RPAD (ename, 10, '*')), LPAD (sal, 10, '*')) AS 编号姓名工资
FROM emp;

-- 数值函数
-- ROUND函数（四舍五入），DUAL 虚表，VIEW也是虚表
SELECT ROUND (45.923, 2), ROUND (45.923, 0), ROUND (45.923, -1)
FROM DUAL;

-- TRUNC函数（截取）
SELECT TRUNC (45.923, 2), TRUNC (45.923, 0), TRUNC (45.923, -1)
FROM DUAL;

-- 3-1 写一个查询语句，分别计算100.456四舍五入到小数点后第2位，第1位，整数位的值
SELECT ROUND (100.456, 2), ROUND (100.456, 1), ROUND (100.456, 0)
FROM DUAL;

-- 3-2 写一个查询语句，分别计算100.456从小数点后第2位，第1位，整数位截断的值
SELECT TRUNC (100.456, 2), TRUNC (100.456, 1), TRUNC (100.456, 0)
FROM DUAL;

-- 日期的处理 DD-MON-RR
-- 日期的运算 '10-AUG-06'+15 => '25-AUG-06'
SELECT ename 员工姓名, ROUND ((SYSDATE-hiredate)/7) 在职周数
FROM emp
WHERE deptno = 10;

-- RR 日期格式
-- 4-1 查询每个员工截止到现在一共入职多少天
SELECT ename 员工姓名, ROUND (SYSDATE-hiredate) 在职天数
FROM emp;

-- 4-2 当前日期为2015年，指定日期格式为DD-MON-RR，指定日期为01-1月-01，该日期实际所代表的日期为
-- 2001/01/01

-- 4-3 当前日期为2015年，指定日期格式为DD-MON-RR，指定日期为01-1月-95，该日期实际所代表的日期为
-- 1995/01/01

-- 4-4 当前日期为1995年，指定日期格式为DD-MON-RR，指定日期为01-1月-01，该日期实际所代表的日期为
-- 2001/01/01

-- 4-5 当前日期为1998年，指定日期格式为DD-MON-RR，指定日期为01-1月-95，该日期实际所代表的日期为
-- 1995/01/01

-- 4-6 当前日期为2015年，指定日期格式为DD-MON-YY，指定日期为01-1月-01，该日期实际所代表的日期为
-- 2001/01/01

-- 4-7 当前日期为1998年，指定日期格式为DD-MON-YY，指定日期为01-1月-95，该日期实际所代表的日期为
-- 1995/01/01

-- 5-1 查询服务器当前的时间
SELECT SYSDATE
FROM DUAL;

-- 5-2 查询部门10,20的员工截止到2000年1月1日，工作了多少个月，入职的月份（使用months_between, extract）
SELECT MONTHS_BETWEEN ('01-1月-01', hiredate) 工作月数, EXTRACT (MONTH FROM hiredate) 入职月份
FROM emp;

-- 5-3 如果员工使用日期为6个月，查询职位不是MANAGER的员工姓名，入职日期，转正日期，入职日期后的第一个星期一，入职当月的最后一天（提示：使用add_months, next_day, last_day）
SELECT ename 员工姓名, hiredate 入职日期, ADD_MONTHS (hiredate, 6) 转正日期, NEXT_DAY (hiredate, '星期一') 入职日期后的第一个星期一, LAST_DAY (hiredate) 入职当月的最后一天
FROM emp
WHERE job != 'MANAGER';

-- 转换函数
-- TO_CHAR函数用于日期型
SELECT ename 员工姓名, TO_CHAR (hiredate, 'MM/YY') 入职日期
FROM emp;

SELECT ename 员工姓名, TO_CHAR (hiredate, 'DD Month YYYY') 入职日期
FROM emp;

-- TO_CHAR 用于数值型
SELECT TO_CHAR (sal, '$99,999') 工资
FROM emp
WHERE ename = 'SCOTT';

-- 6-1 显示服务器系统当前时间，格式为2015-10-12（提示：使用to_char函数）
SELECT TO_CHAR (SYSDATE, 'YYYY-MM-DD HH24:MI:SS') 系统当前时间
FROM DUAL;

-- 6-2 显示ename、hiredate和雇员开始工作日是星期几，列标签DAY（提示：使用to_char函数）
SELECT ename 员工姓名, hiredate 入职日期, TO_CHAR (hiredate, 'DAY') 开始于星期几
FROM emp;

-- 6-3 查询员工姓名，工资，格式化的工资（$999,999.99）（提示：使用to_cahsr函数）
SELECT ename 员工姓名, sal 工资, TO_CHAR (sal, '$999,999.99') 格式化工资
FROM emp;

-- 6-4 把字符串2015-3月-18 13:13:13 转换成日期格式，并计算和系统当前时间间隔多少天（提示：使用to_date函数）
SELECT  ROUND (SYSDATE - TO_DATE('2015-3月-18 13:13:13', 'YYYY-MONTH-DD HH24:MI:SS')) 距离系统时间多少天
FROM DUAL;

-- 通用函数
-- NVL(espr1, exor2)函数（表达式1不为空则返回表达式1，否则返回表达式2）
SELECT ename 员工姓名, sal 工资, comm 奖金, (sal*12) + NVL (comm, 0) 总工资
FROM emp;

-- COALESCE(expr1, expr2, ... exprn)函数（返回第一个不为空的表达式）
SELECT ename 员工姓名, COALESCE (comm, 0) 奖金, deptno 部门编号  -- NVL (comm, 0)
FROM emp;

-- CASE和DECODE函数
CASE expr
  WHEN comparison_expr1 THEN return_expr1
  [WHEN comparison_expr2 THEN return_expr2
  WHEN comparison_exprn THEN return_exprn
  ELSE else_expr]
END;

-- CASE函数
SELECT ename 员工姓名,
       deptno 部门编号,
       (CASE deptno
         WHEN 10 THEN
          '销售部'
         WHEN 20 THEN
          '技术部'
         WHEN 30 THEN
          '管理部'
         ELSE
          '无'
       END) 部门名字
  FROM emp;

-- DECODE函数
SELECT ename 员工姓名, deptno 部门编号, DECODE (deptno, 10, '销售部', 20, '技术部', 30, '管理部', '无') 部门名字
FROM emp;

-- 函数的嵌套（由内到外）
SELECT ename 员工姓名, NVL (TO_CHAR (mgr), '没有经理') 经理号
FROM emp
WHERE mgr IS NULL;

-- 课后作业
-- 1 计算2000年1月1日到现在有多少月，多少周（四舍五入）
SELECT ROUND (MONTHS_BETWEEN (SYSDATE, TO_DATE('20001月01', 'YYYYMONTHDD'))) 距今多少月,
ROUND ((SYSDATE - TO_DATE('20001月01', 'YYYYMONTHDD'))/7) 距今多少周
FROM DUAL;

-- 2 查询员ename的第三个字母是A的员工的信息（使用2个函数）
SELECT * 
FROM emp
WHERE INSTR (UPPER (ename), 'A') = 3 -- 第三个字符是A

-- 3 使用trim函数将字符串'hello'、' Hello '、'bllb'、' hello    '分别处理得到下列字符串ello、Hello、ll、hello
SELECT TRIM ('h' FROM 'hello'), TRIM (' ' FROM ' Hello '), TRIM (BOTH 'b' FROM 'bllb'), TRIM (' ' FROM ' hello    ')
FROM DUAL;

-- 4 将员工工资按如下格式显示：123,234.00 RMB
SELECT TO_CHAR (sal, '999,999.99')||' RMB' 员工工资
FROM emp;

-- 5 查询员工的姓名及其经理编号，要求对于没有经理的显示"No Manager"字符串
SELECT ename 员工姓名, NVL (TO_CHAR (mgr), 'No Manager') 经理编号
FROM emp;

-- 6 将员工的参加工作日按如下格式显示：月份/年份
SELECT TO_CHAR (hiredate, 'MM/YY')
FROM emp;

-- 7 在员工表中查询出员工的工资，并计算应交税款：如果工资小于1000，税率为0，如果工资大于等于1000并小于2000，税率为10%，如果工资大于等于2000并小于3000，税率为15%，如果工资大于3000，说率为20%
SELECT sal 工资, 
(CASE
  WHEN sal > 0 AND sal < 1000 THEN sal*0
  WHEN sal >= 1000 AND sal < 2000 THEN sal*0.1
  WHEN sal >= 2000 AND sal < 3000 THEN sal*0.15
  WHEN sal >= 3000 THEN sal*0.2
ELSE 0
END) 应交税款
FROM emp;

-- 8 创建一个查询显示所有雇员的ename和sal。格式化sal为15个字符长度，用$左填充，列标签SALARY
SELECT ename 员工姓名, LPAD (TO_CHAR (sal), 15, '$') SALARY
FROM emp;