ORACLE Unit02 Oracle字符串操作 、 Oracle数值操作 、 Oracle日期操作 、 空值操作

DQL数据查询语言

查询语句基本由SELECT子句由FROM子句构成.

SELECT子句指定要查询的字段. FROM指定数据来源.
SELECT子句中可以出现表中的字段,函数或表达式.

SELECT * FROM emp

SELECT ename,job,sal,deptno
FROM emp

SELECT ename,sal*12
FROM emp

WHERE子句，可以添加过滤条件，用在
查询中可以将只满足WHERE条件的记录
查询出来

查看工资大于2000的员工?

SELECT ename,sal
FROM emp
WHERE sal>2000

不等于判断用”<>”

SELECT ename,job,sal,deptno
FROM emp
WHERE deptno<>20

字符串函数：

CONCAT(c1,c2)：连接字符串

SELECT CONCAT(ename,sal)
FROM emp

SELECT CONCAT(CONCAT(ename,':'),sal)
FROM emp

“||”可以同时连接多个字符串

SELECT ename||':'||sal
FROM emp

LENGTH(ch)：查看字符串长度

SELECT ename,LENGTH(ename)
FROM emp

SELECT ename,sal
FROM emp
WHERE LENGTH(ename)>4

UPPER,LOWER,INITCAP：转换大写，小写，首字母大写

dual:伪表。当查询的内容不是任何表中的内容时使用。

SELECT UPPER('helloworld'),
       LOWER('HELLOWORLD'),
       INITCAP('HELLO WORLD')
FROM dual

SELECT ename,sal,deptno
FROM emp
WHERE ename=UPPER('scott')

TRIM,LTRIM,RTRIM：去除字符串两边，左边，右边的指定内容

SELECT 
  TRIM('e' FROM 'eeeeliteee')
FROM dual

SELECT 
  LTRIM('esfsffefesliteeee','efs')
FROM dual

SELECT 
  RTRIM('eeeeliteeee','e')
FROM dual

LPAD,RPAD：补位函数

SELECT RPAD(ename,10,' ')
FROM emp

SUBSTR(ch,m,n)

截取字符串，从第m个字符开始截取n个字符。n不指定则是截取到末尾，n超过实际可以截取的个数也是截取到末尾。
m若是负数，则是从倒数位置开始截取。数据库下标都是从1开始

SELECT 
 SUBSTR('thinking in java',-7,2)
FROM 
 dual

INSTR(ch1,ch2,m,n)：查看ch2在ch1中的位置

m表示从哪里开始查找
n表示第几次出现
m,n不写默认都是1

SELECT 
  INSTR('thinking in java','in',4,2)
FROM
  dual

数字函数:

ROUND(n[, m])：四舍五入

SELECT ROUND(45.678, 2) FROM DUAL

SELECT ROUND(45.678, 0) FROM DUAL

SELECT ROUND(55.678, -2) FROM DUAL

TRUNC(n[, m])：截取字符串

SELECT TRUNC(45.678, 2) FROM DUAL

SELECT TRUNC(45.678, 0) FROM DUAL

SELECT TRUNC(55.678, -1) FROM DUAL

MOD(m,n)：求余

SELECT ename,MOD(sal,1000)
FROM emp

CEIL(n)和FLOOR(n)：向上取整和向下取整

SELECT CEIL(45.678) FROM DUAL 
SELECT FLOOR(45.678) FROM DUAL

日期类型相关的关键字

SYSDATE:对应一个内置的函数，返回一个DATE类型的当前系统时间。

SYSTIMESTAMP:返回一个时间戳类型的当前系统时间。

SELECT SYSDATE FROM dual

SELECT SYSTIMESTAMP FROM dual

TO_DATE()函数

可以将一个字符串按照给定的日期格式转换为DATE

SELECT 
 TO_DATE('1992-08-03','YYYY-MM-DD')
FROM
 dual

日期可以进行计算

对一个日期加减一个数字，结果相当于加减了天。
两个日期之间相减差为相差的天数。
日期可以比大小，越晚的越大。

SELECT SYSDATE+1
FROM dual

查看每个员工入职到今天共多少天?

SELECT ename,SYSDATE-hiredate
FROM emp

SELECT 
 SYSDATE-TO_DATE('1992-08-03','YYYY-MM-DD')
FROM dual

TO_CHAR()：将日期按照指定格式转换为字符串

SELECT 
 ename,TO_CHAR(hiredate,'YYYY-MM-DD')
FROM
 emp

SELECT 
 ename,TO_CHAR(hiredate,'YYYY"年"MM"月"DD"日"')
FROM
 emp

YY与RR在TO_DATE函数中将2位数字解析为DATE时是有所区别的:

YY:不关注世纪
RR:自行判定世纪

SELECT
 TO_CHAR(
  TO_DATE('49-05-01','RR-MM-DD'),
  'YYYY-MM-DD'
 )
FROM
  dual

LAST_DAY(date)：返回给定日期所在月的月底日期

SELECT LAST_DAY(SYSDATE)
FROM dual

ADD_MONTHS(date,i)：对指定日期加指定的月,若i是负数则是减去。

查看每个员工入职20周年纪念日

SELECT 
 ename,ADD_MONTHS(hiredate,12*20)
FROM emp

MONTHS_BETWEEN(date1,date2)：计算两个日期之间相差的月

查看每个员工入职至今共多少个月?

SELECT 
 ename,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate))
FROM emp

NEXT_DAY(date,i)：返回给定日期第二天开始一周内的指定周几的日期。

SELECT NEXT_DAY(SYSDATE,3)
FROM dual

LEAST、GREATEST：求最小值与最大值

SELECT 
 LEAST(SYSDATE,
   TO_DATE('2008-08-08',
           'YYYY-MM-DD')
 )
FROM
 dual

EXTRACT函数：获取给定日期指定时间分量对应的值

SELECT EXTRACT(YEAR FROM SYSDATE)
FROM dual

查看1980年入职的员工?

SELECT ename,hiredate
FROM emp
WHERE EXTRACT(YEAR FROM hiredate)=1981

空值操作

CREATE TABLE student(
  id NUMBER(4), 
  name CHAR(20), 
  gender CHAR(1)
);

INSERT INTO student VALUES(1000, '李莫愁', 'F');

INSERT INTO student VALUES(1001, '林平之', NULL);

INSERT INTO student(id, name) VALUES(1002, '张无忌');

SELECT * FROM student 更新成NULL

UPDATE student
SET gender=NULL
WHERE id=1000

在判断一个字段值是否为NULL要使用IS NULL 或 IS NOT NULL

DELETE FROM student
WHERE gender IS NULL

NULL参与运算的注意事项:

1:NULL与字符串连接等于什么也没做
2:NULL与数字运算结果还是NULL

SELECT 
 ename,sal,comm,sal+comm
FROM emp

空值函数

NVL(arg1,arg2)
若第一个参数为NULL函数返回
第二个参数，否则直接返回第一个参数

SELECT ename,sal,comm,
       sal+NVL(comm,0)
FROM emp

查看每个员工的绩效情况，即:有绩效的显示为”有绩效”，为NULL的则显示为”没有绩效”

NVL2(arg1,arg2,arg3)
若arg1不为NULL，函数返回arg2
若arg1为NULL，函数返回arg3

SELECT 
 ename,comm,
 NVL2(comm,'有绩效','没有绩效')
FROM
 emp

SELECT 
 ename,sal,comm,
 NVL2(comm,sal+comm,sal)
FROM
 emp