Oracle学习——第二讲（函数）

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