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);