oracle 四大函数,Oracle--SQL基础

Oralce基础数据类型

四大类，下面列出一些常用类型

字符型

char(最大2000)，nchar(最大1000，支持Unicode) 。固定长度

varchar2(最大4000), nvarchar2(最大2000, 支持Unicode)--->可变长度

数字类型

包括整数和小数

number(有效数字, 总位数);

float()--->存储二进制类型的数据, 1-126位0.30103

double()

日期类型

date:取值范围:公元前4712年1月1号---公元9999年12月31号, 可以直接精确到秒

timestamp(时间戳: 更精确的数据, 可以精确到毫秒)

其它类型:大文本, 文件

blob: 最大4G, 以二进制的形式来存放数据

clob: 最大4G, 以字符串的形式存放

优先级：在数据转换时，优先转换为高级别的数据类型

date type > binary_double > binary_float > number > char

Oracle运算符

算术运算符(+ - * /)

比较运算符(> >= < <= = <>/!=)

逻辑运算符(and or not)

字符串连接符(||)

Oracle单行函数

单行函数格式：

函数名[(参数1，参数2，参数3，...)]

其中参数可以为用户定义的常量、变量、列名和表达式。

单行函数只对表中的一行数据进行操作，并且对每一行数据只产生一个输出结果。

单行函数可用在SELECT、WHERE和ORDER BY的子句中，而且单行函数可以嵌套。

单行函数包含字符型。数字型、日期型、转换型和一般型函数。

单行字符型函数

(1) LOWER(列名|表达式)：该函数用于把字符转换成小写。

SELECT LOWER('SQL: Structural Query Language')

FROM dual;

dual是系统的一个虚表(伪表)，原因：

在查询语句中必须包含SELECT和FROM两个子句，可是LOWER('SQL: Structural Query Language')不属于任何表，于是用Oracle提供的虚表dual来解决这一难题。

(2)UPPER(列名|表达式)：该函数用于把字符转换成大写。

SELECT UPPER('sql is used exclusively in rdbmses')

FROM dual;

(3)INITCAP(列名|表达式)：该函数用于把每个字的头一个字符转换成大写，其余转换成小写。

SELECT INITCAP('SQL is an ENGLISH LIKE language')

FROM dual;

(4)CONCAT(列名|表达式，列名|表达式)：该函数用于把第1个字符串和第2个字符串连接成一个字符串。

SELECT CONCAT('SQL alows you to manipulate the data in DB',' without any programming knowledge')

FROM dual;

(5)SUBSTR(列名|表达式，m，[n])：该函数用于返回指定的子串，该子串从第m个字符开始，其长度为n。

SELECT SUBSTR('SQL lets you concentrate on what has to be done',14)

FROM dual;

(6)LENGTH(列名|表达式)：该函数用于返回列或表达式中字符串的长度。

SELECT LENGTH('SQL does no let you concentrate on how it will be achieved')

FROM dual;

(7)INSTR(列名|表达式，‘字符串’，[m]，[n])：该函数用于返回所给字符串的数字位置，m表示从第m个字符开始搜索，n表示所给字符串出现的次数，它们的默认值都为1。

SELECT INSTR('SQL allows for dynamic DB changes','F')

FROM dual;

SELECT INSTR('SQL allows for dynamic DB changes','f')

FROM dual;

(8)TRIM([leading|trailing|both]要去掉的字符FROM源字符串)：该函数能够从“源字符串”中的头(leading)部、尾(trailing)部或头部和尾部中(both)去掉“要去掉的字符串”。如果没有指定头或尾，TRIM函数按默认(both)处理(该函数是8i引入的，在8i之前的版本中是LTRIM和RTRIM两个函数)。

trim 去掉前后指定的字符

select trim('H' from 'Hello WorldH') from dual;-->ello World

(9)REPLACE(正文表达式，要搜寻的字符串，替换字符串)：该函数用于在“正文表达式”中查找“要搜寻的字符串”，如果找到了就用“替换字符串”替代。

select replace('Hello World','l','') from dual;-->Heo Word

(10)NVL(列名|表达式 1，列名|表达式 2)将一个NULL转换为另外一个值，如果1为NULL,则返回2,否则返回1值本身

select nvl(address,'北京市') from student;

数字型函数

(1)ROUND(列名|表达式，n)：该函数将列名或表达式所表示的数值四舍五入到小数点的n位。注意下标从0开始。

select round(555.666) from dual;--返回结果为556，不加n时默认去掉小数部分

select round(555.666,2) from dual;--返回结果为555.67

select round(555.666,-2) from dual;--返回结果为600

(2)TRUNC(列名|表达式，n)：该函数将列名或表达式所表示的数值取到小数点的后n位。

select trunc(555.666) from dual; --返回结果为555，不加n时默认去掉小数部分

select trunc(555.666,2) from dual;--返回结果为555.66

select trunc(555.666,-2) from dual;--返回结果为500

(3)MOD(m，n)：该函数将m除以n并取余数。

select mod(1600,300) from dual;--返回结果是100

(4)ABS(value)返回value的绝对值

select abs(-10) from dual;--返回结果为10

(5)CEIL(value)返回大于等于value的最小整数

select ceil(2.3) from dual; --返回结果为3

(6)FLOOR(value)返回小于等于value的最大整数

select floor(2.3) from dual; --返回结果为2

注意：1. trunc和round用法类似，只不过trunc是硬生生截取，并不进行四舍五入，而round进行截取时四舍五入

2. 都还可以对日期的截取

日期函数

-- 系统时间

select sysdate from dual;

把一个日期型数据和一个数字相加减，结果仍为日期型。

select sysdate-10 from dual;--当前时间减10天，返回的是天数

select sysdate+10 from dual;--日期不能相加

--计算员工的工龄：天 星期 月 年 (月数不是个准确的值)

select ename,hiredate,(sysdate-hiredate) 天,(sysdate-hiredate)/7 星期, (sysdate-hiredate)/30 月,(sysdate-hiredate)/365 年 from emp;

(1)MONTHS_BETWEEN(x,y)，两个日期相差的月数

--计算员工的工龄：months_between()计算后月数是个准确的值

select ename,hiredate,(sysdate-hiredate)/30 一, months_between(sysdate,hiredate) 二

from emp;

(2)ADD_MONTHS(d,n)，在某一个日期 d 上，加上指定的月数 n，返回计算后的新日期。d 表示日期，n 表示要加的月数。

SELECT SYSDATE,add_months(SYSDATE,5) FROM dual;

------->

2017/12/10 23:17:34 -- > 2018/5/10 23:17:34

(3)NEXT_DAY(x,y)，指定日期的下一个日期

select next_day(sysdate,'星期二') from dual;-->从当前时间开始算，下一个星期二的日期。

(4)LAST_DAY(d)，返回指定日期当月的最后一天。

SELECT SYSDATE,last_day(SYSDATE) FROM dual;

(5)ROUND(d[,fmt])，返回一个以 fmt 为格式的四舍五入日期值， d 是日期， fmt 是格式模型。默认 fmt 为 DDD，即月中的某一天。

① 如果 fmt 为“YEAR”则舍入到某年的 1 月 1 日，即前半年舍去，后半年作为下一年。

② 如果 fmt 为“MONTH”则舍入到某月的 1 日，即前半月舍去，后半月作为下一月。

③ 默认为“DDD”，即月中的某一天，最靠近的天，前半天舍去，后半天作为第二天。

④ 如果 fmt 为“DAY”则舍入到最近的周的周日，即上半周舍去，下半周作为下一周周日。

SELECT SYSDATE,ROUND(SYSDATE),ROUND(SYSDATE,'day'),

ROUND(SYSDATE,'month'),ROUND(SYSDATE,'year') FROM dual;

rerere.png

(6)ROUND(d[,fmt])，返回指定一个截取后的日期，不会四舍五入，而是直接截取。

YYTYTY.png

(7)EXTRACT(fmt FROM d)，提取日期中的特定部分。

fmt 为：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。其中 YEAR、MONTH、DAY可以为 DATE 类型匹配，也可以与 TIMESTAMP 类型匹配；但是 HOUR、MINUTE、SECOND 必须与 TIMESTAMP 类型匹配。

HOUR 匹配的结果中没有加上时区，因此在中国运行的结果小 8 小时。

yuyuyu.png

转换函数

转换函数将值从一种数据类型转换为另外一种数据类型。

(1)TO_CHAR(d|n[,fmt])把日期和数字转换为制定格式的字符串。Fmt是格式化字符串。

SELECT TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日" HH24:MI:SS')"date" FROM dual;

(2)TO_DATE(X,[,fmt])把一个字符串以fmt格式转换成一个日期类型

SELECT TO_DATE('2017/12/10 23:40:34','YYYY-MM-DD HH24:MI:SS') FROM dual;

(3)TO_NUMBER(X,[,fmt])把一个字符串以fmt格式转换为一个数字

格式值            含义

9           代表一个数字

0           强迫0显示

$           显示美元符号

L           强制显示一个当地的货币符号

.           显示一个小数点

,           显示一个千位分隔符号

①select to_number('88877') from dual;

②如果数字在格式范围内的话，就是正确的，否则就是错误的；如：

select to_number('$12345.678', '$999999.99') from dual; -- 错误

select to_number('$12345.678', '$999999.999') from dual;--正确

③可以用来实现进制转换；16进制转换为10进制：

select to_number('19f','xxx') from dual;

select to_number('f','xx') from dual;

Oracle聚合函数

(1)avg(x)：返回x的平均值

select avg(grade) from sc;

(2)count(x)：返回统计的行数

select count(name) from sc;

count(*)和count(列名)的区别：count(*)不会忽略列值为空的情况，count(列名)会忽略。如：

--平均奖金(某些员工可能没有奖金)

select sum(comm)/count(*) 一,

sum(comm)/count(comm) 二,

avg(comm) 三

from emp;

一 二 三

---------- ---------- ----------

157.142857 550 550

原因是：

select count(*),count(comm) from emp;

COUNT(*) COUNT(COMM)

---------- -----------

14 4

上述例子中avg(comm)跟sum(comm)/count(comm)结果一样的，

原因是聚合函数自动过滤掉为空的列，

如果不想过滤可以这样：

count(comm) --> count(nvl(comm,0))

avg(comm) --> avg(nvl(comm,0))

(3)max(x)：返回x的最大值

select max(grade) from sc;

(4)min(x)：返回x的最小值

select min(grade) from sc;

(5)sum(x)：返回x的总计值

select sum(grade) from sc;

聚合函数需要注意的是：

1.当分组时select子句后边要检索的列中出现聚合函数，其他的列必须与group by子句后的列名一致。

select deptno,avg(sal) from EMP;--错误，因为deptno不是聚集函数，也不是group by后面跟的列名

2.不能使用聚集函数作为WHERE子句的筛选条件

select deptno from emp where avg(sal)>1000;--错误

3.分组后，需要使用条件进行筛选，则使用having过滤分组后的行，不能使用where，where只能放在group by前面。

select deptno, avg(sal) from emp               where deptno<>10

group by deptno

having avg(sal) > 900;

Oracle表达式

oracle中sql的表达式可以用if-else if-else来理解

CASE表达式：

-- 按员工职位加薪

select ename,job,sal 涨前,

case job when 'PRESIDENT' then sal+1000

when 'MANAGER' then sal+800

else sal+400

end 涨后

from emp;

ENAME JOB 涨前 涨后

---------- --------- ---------- ----------

MARTIN SALESMAN 1250 1650

BLAKE MANAGER 2850 3650

KING PRESIDENT 5000 6000

TURNER SALESMAN 1500 1900

ADAMS CLERK 1100 1500

DECODE表达式：

select ename,job,sal 涨前,

decode(job,'PRESIDENT',sal+1000,

'MANAGER',sal+800,

sal+400) 涨后

from emp;

ENAME JOB 涨前 涨后

---------- --------- ---------- ----------

MARTIN SALESMAN 1250 1650

BLAKE MANAGER 2850 3650

KING PRESIDENT 5000 6000

TURNER SALESMAN 1500 1900

ADAMS CLERK 1100 1500

多表查询

*笛卡尔积

当多表关联查询时必然产生笛卡尔积。产生笛卡尔积后的结果是:

总列数 = 表1的列数 + 表2的列数 + ....

总行数 = 表1的行数 * 表2的行数 * ....

1513085800(1).png

多表查询造成结果变大而且有些数据错乱，那么就要通过下面的各种连接来消除这种弊端。

等值连接

select e.empno,e.ename,e.sal,d.dname

from emp e,dept d

where e.deptno=d.deptno;

条件中有 “=” 号的连接就是等值连接

不等值连接

条件中不是 “=” 号的连接都是不等值连接

外连接

左外连接: 当where e.deptno=d.deptno 不成立的时候,等号左边的表任然被包含(这样的话至少可以保证左边表数据的完整)

写法: where e.deptno=d.deptno(+)

右外连接: 当where e.deptno=d.deptno 不成立的时候,等号右边的表任然被包含(这样的话至少可以保证右边表数据的完整)

写法: where e.deptno(+)=d.deptno

自连接

自连接: 通过表的别名，将同一张表视为 多张表

select e.ename 员工姓名,b.ename 老板姓名

from emp e,emp b

where e.mgr=b.empno;

自连接不适合操作大表

自连接--层次查询

层次查询.png

层次查询是通过start with和connect by子句标识的：

select level,empno,ename,mgr --levle是伪列

from emp

connect by prior empno=mgr

start with mgr is null -- 从根节点开始

order by 1;

-- Start with是表示开始节点

-- connect by prior是指定父子关系

LEVEL EMPNO(员工id) ENAME(员工姓名) MGR(员工对应领导的id)

---------- ---------- ---------- ----------

1 7839 KING

2 7566 JONES 7839

2 7698 BLAKE 7839

2 7782 CLARK 7839

3 7902 FORD 7566

3 7521 WARD 7698

3 7900 JAMES 7698

3 7934 MILLER 7782

3 7499 ALLEN 7698

3 7788 SCOTT 7566

3 7654 MARTIN 7698

子查询

子查询其实就是select 嵌套 select

可以在主查询的where select having from 后面放置子查询

不可以在group by后面放置子查询

主查询和子查询可以不是同一张表；只要子查询返回的结果，主查询可以使用即可

一般不在子查询中使用order by；但在Top-N分析问题中，必须对子查询排序

一般先执行子查询，再执行主查询；但相关子查询例外

单行子查询只能使用单行操作符；多行子查询只能使用多行操作符

in

只要在集合中有值就匹配成功，注意如果集合中有null值不能使用in，因为得不到任何结果。但可以使用not in 。oracle中判断是否是null不能使用 = 号来判断如果这样判断永远是不等的，只能通过 is null 或 is not null来判断。

any

和集合中的任意一个值比较

查询工资比30号部门任意一个员工高的员工

select *

from emp

where sal > any (select sal from emp where deptno=30);

等同于：

select *

from emp

where sal > (select min(sal) from emp where deptno=30)

all

和集合的所有值比较

查询工资比30号部门所有员工高的员工

select *

from emp

where sal > all (select sal from emp where deptno=30);