Oracle学习笔记(二)

查询Oracle中常见的默认格式:select * from v$nls_parameters;
Oracle中的默认数据格式：如1987年10月11日:11-10-87
修改本次会话的Oracle中的数据格式:alter session set NLS_DATE_FORMAT='yyyy-mm-dd';
修改数据库中全局的数据格式(这里需要用到管理员权限):alter system set NLS_DATE_FORMAT='yyyy-mm-dd';
模糊查询中%或_的转义:select * from emp where ename like '%t_%' escape 't'; 可以通过escape关键字来声明转义字符
在Oracle中的函数分为两种,单行函数和多行函数。

单行函数:
字符函数:
首先是控制大小写的字符函数

SQL> select lower('Hello World!') 小写,upper('Hello World!') 大写,initcap('hello world!') 首字母大写 from dual;
小写         大写         首字母大写
------------ ------------ ------------
hello world! HELLO WORLD! Hello World!

然后是字符控制函数:

SQL> -- substr(a,b) 从a中的第b位开始取
SQL> select substr('Hello World!',4) from dual;
SUBSTR('H
---------
lo World!
-- substr(a,b,c)从a中的第b位开始取c位字符
SQL> select substr('Hello World!',3,5) from dual;
SUBST
-----
llo W

SQL> -- length 字符数 lengthb 字节数
SQL> select length('Hello World!') 字符数,lengthb('Hello World!') 字节数 from dual;
    字符数     字节数
---------- ----------
        12         12

SQL> -- instr(a,b) 在a中查找b，找到返回位置找不到返回0
SQL> select instr('Hello World!','ll') 位置 from dual;
      位置
----------
         3

--字符填充函数
SQL> select lpad('abcd',10,'#') 用10个#左填充原来的字符,rpad('abcd',10,'#') 用10个#来右填充原来的字符 from dual;
用10个#左  用10个#来
---------- ----------
######abcd abcd######

SQL> -- trim去掉前后指定的字符,注意字符必须在开头或结尾
SQL> select trim('d' from 'dHello World') from dual;
TRIM('D'FR
----------
Hello Worl

SQL> --字符串的替换函数
SQL> select replace('Hello World','l','$') from dual;
REPLACE('HE
-----------
He$$o Wor$d

数值函数:

SQL> --round四舍五入并且保留指定位数的小数
SQL> select round(21.738,2) 一, round(21.738,-1) 二,round(21.738,-2) 三 from dual;
        一         二         三
---------- ---------- ----------
     21.74         20          0

SQL> --trunc截断指定小数的位数的数字
SQL> select trunc(21.738,2) from dual;
TRUNC(21.738,2)
---------------
          21.73

SQL> --按照指定的格式将货币转化成标准格式,
SQL> select to_char(sal,'L9,999,99') from emp;

SQL> --日期,查看当前的日期
SQL> select sysdate from dual;
SYSDATE
----------
2017-02-02

SQL> --按照指定的格式来输出日期格式
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2017-02-02 21:33:57

SQL> --日期直接进行数学运算之后还是日期
SQL> alter session set NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';
会话已更改。
SQL> select (sysdate-1) 昨天,sysdate 今天,(sysdate+1) 明天 from dual;
昨天                今天                明天
------------------- ------------------- -------------------
2017-02-01 21:36:46 2017-02-02 21:36:46 2017-02-03 21:36:46

日期函数:

SQL> --months_between()能够得到相邻的日期之间的月数
SQL> select ename,hiredate,months_between(sysdate,hiredate) from emp;

SQL> --add_months加上指定月份之后的日期
SQL> select add_months(sysdate,3) from dual;
ADD_MONTHS(SYSDATE,
-------------------
2017-05-02 21:51:05

SQL> --last_day 得到月的最后一天
SQL> select last_day(sysdate) from dual;
LAST_DAY(SYSDATE)
-------------------
2017-02-28 21:51:46

SQL> --next_day下一个指定时间的日期
SQL> select next_day(sysdate,'星期四') from dual;
NEXT_DAY(SYSDATE,'
-------------------
2017-02-09 21:53:38

SQL> --to_char()进行字符串的格式化
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss "今天是" day') from dual;

TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:S
------------------------------------
2017-02-02 22:37:41 今天是 星期四

通用函数:
nvl(a,b):当a为空值的时候返回b，否则返回a值
nvl2(a,b,c):当a为空值的时候返回c，否则返回b值

SQL> -- nullif(a,b) 当a与b相等的时候返回null,否则返回a
SQL> select nullif(1,1) from dual;
NULLIF(1,1)
-----------
SQL> select nullif('aaa','vvv') from dual;
NUL
---
aaa

SQL> -- coalesce 从左到右返回第一个不为null的值
SQL> select comm,sal,coalesce(comm,sal) "第一个不为空的值" from emp;

条件表达式:

SQL> select ename,job,sal 涨前,
  2  case job when 'PRESIDENT' then sal+1000
  3  when 'MANAGER' then sal+800
  4  else sal+400
  5  end 涨后
  6  from emp;

ENAME      JOB              涨前       涨后
---------- ---------- ---------- ----------
SMITH      CLERK             800       1200
ALLEN      SALESMAN         1600       2000
WARD       SALESMAN         1250       1650
JONES      MANAGER          2975       3775
MARTIN     SALESMAN         1250       1650
BLAKE      MANAGER          2850       3650
CLARK      MANAGER          2450       3250
SCOTT      ANALYST          3000       3400
KING       PRESIDENT        5000       6000
TURNER     SALESMAN         1500       1900
ADAMS      CLERK            1100       1500

ENAME      JOB              涨前       涨后
---------- ---------- ---------- ----------
JAMES      CLERK             950       1350
FORD       ANALYST          3000       3400
MILLER     CLERK            1300       1700