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

DQL数据查询语言

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

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

SELECT * FROM empSELECT ename,job,sal,deptnoFROM empSELECT ename,sal*12FROM emp

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

查看工资大于2000的员工?

SELECT ename,salFROM empWHERE sal>2000

不等于判断用”<>”

SELECT ename,job,sal,deptnoFROM empWHERE deptno<>20

字符串函数：

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

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

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

SELECT ename||':'||salFROM emp

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

SELECT ename,LENGTH(ename)FROM empSELECT ename,salFROM empWHERE LENGTH(ename)>4

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

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

SELECT UPPER('helloworld'),       LOWER('HELLOWORLD'),       INITCAP('HELLO WORLD')FROM dualSELECT ename,sal,deptnoFROM empWHERE ename=UPPER('scott')

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

SELECT   TRIM('e' FROM 'eeeeliteee')FROM dualSELECT   LTRIM('esfsffefesliteeee','efs')FROM dualSELECT   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 DUALSELECT ROUND(45.678, 0) FROM DUALSELECT ROUND(55.678, -2) FROM DUAL

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

SELECT TRUNC(45.678, 2) FROM DUALSELECT TRUNC(45.678, 0) FROM DUALSELECT 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 dualSELECT SYSTIMESTAMP FROM dual

TO_DATE()函数

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

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

日期可以进行计算

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

SELECT SYSDATE+1FROM dual

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

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

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

SELECT  ename,TO_CHAR(hiredate,'YYYY-MM-DD')FROM empSELECT  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,hiredateFROM empWHERE 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 studentSET gender=NULLWHERE id=1000

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

DELETE FROM studentWHERE gender IS NULL

NULL参与运算的注意事项:

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

SELECT  ename,sal,comm,sal+commFROM 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 empSELECT  ename,sal,comm, NVL2(comm,sal+comm,sal)FROM emp