Oracle的SQL语言基础（二）

最新推荐文章于 2023-03-03 10:35:36 发布

秋殇_

最新推荐文章于 2023-03-03 10:35:36 发布

阅读量379

点赞数

分类专栏： Oracle 文章标签： oracle

本文链接：https://blog.csdn.net/weixin_37121625/article/details/53786697

版权

Oracle 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

SQL的基本函数

1.日期函数

因为日期在Oracle中是以数字形式存储的，所以可以对它进行加减运算，计算是以天为单位。可以表示日期范围:(公元前)4712至（公元）9999

时间格式

1. SQL>select to_date('2016-12-21 00:00:00','YYYY-MM-DD HH24:MI:SS') from dual;

TO_DATE('2016-
--------------
21-12月-16

2. SQL> select sysdate+2 from dual; //当前时间加两天

SYSDATE+2
--------------
23-12月-16

3. SQL> select sysdate+2/24 from dual; //当前时间加2小时

SYSDATE+2/24
--------------
21-12月-16
4. SQL> select (sysdate-hiredate)/7 week from emp; //两个date类型差，结果是以天为整数位的实数

      WEEK
----------
 1879.0951
1869.80939
1869.52368
1863.95225
1838.38082
1859.80939
1854.23796
1548.52368
1831.23796
1841.23796
1543.66653

MONTHS_BETWEEN —->计算两个日期之间的月数

//计算两个日期之间的月数
SQL> select months_between(to_date('2014-3-21','yyyy-mm-dd'), to_date('2014-1-10
','yyyy-mm-dd')) mm from dual;

        MM
----------
2.35483871  

//查找 emp 表中参加工作时间>30 年的员工
SQL> select * from emp where months_between(sysdate,hiredate)/12>30;
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980/12/17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30

考点：很容易认为单行函数返回的数据类型与函数类型一致，对于数值函数类型确实如此，但字符型和日期函数可以返回任意数据类型的值。比如instr函数是字符型的，months_between函数是日期型的，但它们返回的都是数值。

ADD_MONTHS—->给日期增加月份

SQL> select add_months(to_date('2016-12-23','yyyy-mm-dd'),4) from dual;
ADD_MONTHS(TO_DATE('2016-12-23
------------------------------
2017/4/23

last_day 日期当前月份最后一天

SQL> select last_day(to_date('2016-12-23','yyyy-mm-dd')) l_d from dual;
L_D
-----------
2016/12/31

next_day
next_day的第二个参数可以是数字1-7，分别表示周日–周六，比如今天是周五要获取周六的日期：

SQL> select next_day(sysdate,7) from dual;
NEXT_DAY(SYSDATE,7)
-------------------
2016/12/24 15:43:27

round(p,s) , trunc(p,s) 在日期中的应用，如何舍入要看具体情况，s是month按30天计，应该是15舍1入6 ，s是year应该按6舍7入计算。

--s为month
SQL> SELECT empno, hiredate,
  2  round(hiredate,'MONTH') AS round,
  3  trunc(hiredate,'MONTH') AS trunc
  4  FROM emp
  5  WHERE empno=7788;
EMPNO HIREDATE    ROUND       TRUNC
----- ----------- ----------- -----------
 7788 1987/4/19   1987/5/1    1987/4/1

--s为year
SQL> SELECT empno, hiredate,
  2  round(hiredate,'YEAR') AS round,
  3  trunc(hiredate,'YEAR') AS trunc
  4  FROM emp
  5  WHERE empno=7839;
EMPNO HIREDATE    ROUND       TRUNC
----- ----------- ----------- -----------
 7839 1981/11/17  1982/1/1    1981/1/1

2.decode函数和case表达式

2.1 decode函数

实现 sql 语句中的条件判断语句，具有类似高级语言中的 if 语句的功能。
decode 函数源自 oracle, case 表达式源自 sql 标准，实现功能类似，decode 语法更简单些。

1）decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)
该函数的含义如下：

IF 条件=值1 THEN
　　　　RETURN(翻译值1)
ELSIF 条件=值2 THEN
　　　　RETURN(翻译值2)
　　　　……
ELSIF 条件=值n THEN
　　　　RETURN(翻译值n)
ELSE
　　　　RETURN(缺省值)
END IF

--例子
SQL> SELECT job, sal,
  2         DECODE(job, 'ANALYST', SAL*1.1,
  3                     'CLERK', SAL*1.15,
  4                     'MANAGER', SAL*1.20,
  5                     SAL)
  6                     REVISED_SALARY
  7  FROM emp
  8  /
JOB             SAL REVISED_SALARY
--------- --------- --------------
CLERK        800.00            920
SALESMAN    1600.00           1600
SALESMAN    1250.00           1250
MANAGER     2975.00           3570

2) decode(字段或字段的运算，值1，值2，值3）
这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3。当然值1，值2，值3也可以是表达式，这个函数使得某些sql语句简单了许多

--取较小值
令: a = 10,b = 20
SQL> select decode(sign(10-20),-1,'a','b') min from dual;
MIN
---
a

则sign(a - b)返回-1，decode解码结果为“-1”，达到了取较小值的目的。

2.2 case表达式

case表达式第一种用法：

SQL> select job, sal,case job
  2         when 'ANALYST' then SAL*1.1
  3         when 'CLERK' then SAL*1.15
  4         when 'MANAGER' then SAL*1.20
  5         else sal end
  6         REVISED_SALARY
  7  from emp
  8  /
JOB             SAL REVISED_SALARY
--------- --------- --------------
CLERK        800.00            920
SALESMAN    1600.00           1600
SALESMAN    1250.00           1250
MANAGER     2975.00           3570

case表达式第二种用法

SQL> select job, sal,
  2         case when job='ANALYST' then SAL*1.1
  3              when job='CLERK' then SAL*1.15
  4              when job='MANAGER' then SAL*1.20
  5              else sal
  6         end
  7  REVISED_SALARY
  8  from emp
  9  /
JOB             SAL REVISED_SALARY
--------- --------- --------------
CLERK        800.00            920
SALESMAN    1600.00           1600
SALESMAN    1250.00           1250

case 第二种语法比第一种语法增加了搜索功能。形式上第一种 when 后跟定值，而第二种还
可以使用比较符。例如：

SQL> select ename,sal,
  2         case when sal>=3000 then '高级'
  3              when sal>=2000 then '中级'
  4         else '低级' end
  5  级别
  6  from emp
  7  /
ENAME            SAL 级别
---------- --------- ----
SMITH         800.00 低级
ALLEN        1600.00 低级
WARD         1250.00 低级

3. distinct (去重)限定词：

distinct 貌似多行函数，严格来说它不是函数

--消除表行重复值
SQL> select distinct job from emp;
JOB
---------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST

--两列综合考虑
SQL> select distinct job,deptno from emp;
JOB       DEPTNO
--------- ------
MANAGER       20
PRESIDENT     10
CLERK         10
SALESMAN      30
ANALYST       20
MANAGER       30
MANAGER       10
CLERK         30
CLERK         20

4.chr()函数和ASCII()函数

chr()函数将ASCII码转为字符；
ascii()函数将字符转为ASCII码，
在Oracle中chr()函数和ascii()函数是一对反函数。

--求字符对应的ASCII值
SQL> select ASCII('A') FROM dual;
ASCII('A')
----------
        65

--求ASCII值对应的字符
SQL> select chr(65) from dual;
CHR(65)
-------
     A

5.sys_context 获取环境上下文的函数(较重要)

--查看登录用户的ip
SQL> select SYS_CONTEXT('USERENV','IP_ADDRESS') from dual;
SYS_CONTEXT('USERENV','IP_ADDR
-------------------------------------------
10.1.84.94

--获取登录用户的sid
SQL> select sid from v$session where audsid=userenv('sessionid');
       SID
----------
       726

--获取登录用户的主机名
SQL> select sys_context('userenv','terminal') from dual;
SYS_CONTEXT('USERENV','TERMINAL')
-----------------------------------
TIMRAN-222C75E5