ORACLE数据库总结02

DQL:数据查询语言

至少包含两部分:

1:SELECT子句

2:FROM子句

SELECT之后指定要查询的字段，可以

使用"*"查询所有字段，也可以单独指定

具体的字段，或者一个函数，表达式。

FROM之后指定要查询的表

 

SELECT ename,job,sal,deptno 

FROM emp

 

查看函数或表达式的结果

SELECT ename,sal*12

FROM emp

 

字符串函数

1:CONCAT(char1,char2)

连接字符串

SELECT CONCAT(ename,sal)

FROM emp

 

SELECT 

 CONCAT(CONCAT(ename,':'),sal)

FROM emp

 

可以使用"||"的形式连接字符串

SELECT 

 ename||':'||sal

FROM 

 emp

 

LENGTH(char)函数

统计指定字符串的长度

对CHAR统计长度是固定的，通常

对VARCHAR2类型字段统计。

SELECT ename, LENGTH(ename) 

FROM emp

 

UPPER(),LOWER(),INITCAP()

将字符串转换为全大写，全小写

以及首字母大写

 

dual:伪表，当查询的内容与现实的

任何一张表没有关系时，可以使用

伪表来填充FROM子句，伪表只会查询

出一条记录

SELECT 

  UPPER('helloworld'),

  LOWER('HELLOWORLD'),

  INITCAP('hello world')

FROM dual

 

TRIM,LTRIM,RTRIM

去除字符串两边的指定字符

SELECT 

 TRIM('e' FROM 'eeeeeliteeee')

FROM dual

 

SELECT 

 LTRIM('etstesteliteseseses','set')

FROM dual

 

 

 

 

 

 

LPAD,RPAD

补位函数

SELECT 

  RPAD(sal,5,' ')

FROM 

  emp

 

 

SUBSTR(str,m,n)

截取当前字符串，从m指定的

位置开始连续截取n个字符。

n可以不指定，不指定则截取到

字符串末尾，n若超过该字符串

可以截取的最大值时，也是截取到

字符串末尾。

需要注意：数据库下标从1开始！！！！

SELECT 

 SUBSTR('thinking in java',1,5)

FROM 

 dual

 

INSTR(char1,char2,n,m)

查看char2在char1中的位置

n:可以不指定，不指定默认值为1

  表示从第几个字符开始检索

m:可以不指定，默认值为1

  表示第几次出现

SELECT 

  INSTR('thinking in java','in',

         4,2)

FROM 

  dual

 

数值函数:

 

ROUND(n,m)

四舍五入，保留n的小数点后m位。

若m不指定或为0则表示保留到整数

为，负数则是十位以上的数字。

SELECT ROUND(45.678, 2) FROM DUAL

SELECT ROUND(45.678, 0) FROM DUAL

SELECT ROUND(45.678, -1) FROM DUAL

 

TRUNC()

参数与ROUND一致，截取数字，不进行四舍五

入操作

SELECT TRUNC(45.678, 2) FROM DUAL

SELECT TRUNC(45.678, 0) FROM DUAL

SELECT TRUNC(45.678, -1) FROM DUAL

 

MOD(m,n)

求余数

SELECT 

  ename, sal, MOD(sal, 1000) 

FROM emp; 

 

CEIL(n),FLOOR(n)

向上取整与向下取整

SELECT CEIL(45.678) FROM dual

SELECT FLOOR(45.678) FROM dual

 

 

日期相关函数

 

SYSDATE,SYSTIMESTAMP

这两个关键字表示两个内部函数

返回的都是当前系统时间，只不过

一个以DATE型返回，一个以时间戳

类型返回。

SELECT SYSDATE FROM dual

SELECT SYSTIMESTAMP FROM dual

 

TO_DATE函数

可以将一个字符串按照指定的日期格式

解析为一个DATE类型的值

日期格式字符串中出现了除英文，数字，符号

以外的其他字符时都要使用双引号括起来。

SELECT

 TO_DATE('1992年08月05日 22:12:44',

         'YYYY"年"MM"月"DD"日" HH24:MI:SS' )

FROM 

 dual

 

日期可以比较大小与计算

时间越晚的越大

两个日期相减，差为相差的天数

对一个日期加减一个数字等于加减指定的天数

 

SELECT SYSDATE+1 FROM dual

 

查看每个员工到今天为止入职多少天了?

SELECT ename,SYSDATE-hiredate

FROM emp

 

查看自己到今天为止活了多少天？

SELECT 

  SYSDATE-TO_DATE('1992-08-02','YYYY-MM-DD')

FROM

  dual

 

查看82年以后入职的员工?

SELECT ename,hiredate

FROM emp

WHERE hiredate>TO_DATE('1982-01-01','YYYY-MM-DD')

 

 

TO_CHAR()

可以将给定的日期按照指定的日期

格式转换为字符串

SELECT 

  TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')

FROM 

  dual

 

RR在日期格式中也是2位数字表示

的年，但是与YY不同，它可以在

解析字符串的年时判定世纪

SELECT

 TO_CHAR(

  TO_DATE('49-08-08','RR-MM-DD'),

  'YYYY-MM-DD'

 ) 

FROM 

 dual

 

LAST_DAY(date)

返回给定日期所在月的月底

查看每个员工入职所在月的月底？

SELECT ename,LAST_DAY(hiredate)

FROM emp

 

ADD_MONTHS(date,i)

对指定的日期加上指定的月

若i为负数，则是减去。

 

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

SELECT 

  ename,ADD_MONTHS(hiredate,12*30)

FROM 

  emp

 

 

MONTHS_BETWEEN(date1,date2)

计算两个日期之间相差的月

 

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

SELECT 

 ename,MONTHS_BETWEEN(SYSDATE,hiredate)

FROM 

 emp

 

NEXT_DAY(date,i)

返回给定日期第二天起一周之内

的周几

SELECT NEXT_DAY(SYSDATE,4)

FROM dual

 

LEAST、GREATEST

求最小值与最大值,参数一个以上即可。

常用与日期，数字的求值

SELECT 

 LEAST(SYSDATE,

       TO_DATE('2008-08-05',

               'YYYY-MM-DD')

      ) 

FROM DUAL;

 

EXTRACT()

提取一个日期的指定时间分量

对应的值

1982年入职的员工?

SELECT ename,hiredate

FROM emp

WHERE EXTRACT(YEAR FROM hiredate)=1982

 

NULL值操作

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, '张无忌');

 

 

UPDATE student

SET gender = NULL

WHERE id=1000

 

SELECT * FROM student

 

删除性别为NULL的记录

判断一个字段的值是否为空，要使用

IS NULL或IS NOT NULL

不能使用"="判断NULL

DELETE FROM student

WHERE gender IS NOT NULL

 

CREATE TABLE student

    (id NUMBER(4),

     name CHAR(20), 

    gender CHAR(1) NOT NULL);

 

NULL与字符串连接等于什么也没做

NULL与数字运算记过还为NULL

 

查看每个员工的收入情况:

SELECT 

  ename,sal,comm,sal+comm

FROM emp

空值函数:

NVL(f1,f2)

当f1为NULL时，函数返回f2,

若不为NULL，函数返回f1自身。

所以NVL函数的作用是将NULL值

替换为非NULL值。

查看每个员工的收入情况:

SELECT 

  ename,sal,comm,sal+NVL(comm,0)

FROM emp

 

查看每个人的奖金情况，有奖金的

则显示为"有奖金",为NULL的则显示

为"没有奖金"

NVL2(f1,f2,f3)

当f1不为NULL时，函数返回f2

当f1为NULL时，函数返回f3

 

SELECT 

 ename,sal,comm,

 NVL2(comm,'有奖金','没有奖金')

FROM

 emp

 

SELECT 

  ename,NVL2(comm,sal+comm,sal)

FROM 

  emp