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;
（6）ROUND(d[,fmt])，返回指定一个截取后的日期，不会四舍五入，而是直接截取。
（7）EXTRACT(fmt FROM d)，提取日期中的特定部分。
fmt 为：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。其中 YEAR、MONTH、DAY可以为 DATE 类型匹配，也可以与 TIMESTAMP 类型匹配；但是 HOUR、MINUTE、SECOND 必须与 TIMESTAMP 类型匹配。
HOUR 匹配的结果中没有加上时区，因此在中国运行的结果小 8 小时。
- 转换函数
转换函数将值从一种数据类型转换为另外一种数据类型。
（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的行数 * ....
多表查询造成结果变大而且有些数据错乱，那么就要通过下面的各种连接来消除这种弊端。
- 等值连接
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);
