目录
(2)where的 =、IN、LIKE、BETWEEN...AND、AND、OR、NOT
一、oracle运算符
(1) Oracle算术运算符
+、-、*、/、mod()
select 5 + 3 from dual;
select 5 - 3 from dual;
select 5 * 3 from dual;
select 5 / 3 from dual;
select mod(5,3) from dual;
(2) Oracle关系运算符
符号 | 解释 | 符号 | 解释 |
= | 等于 | <>或者!= | 不等于 |
> | 大于 | >= | 大于或者等于 |
< | 小于 | <= | 小于或者等于 |
(3) Oracle的逻辑运算符
AND、OR、NOT
(4) 字符串连接符||
select 'w' || 123 || 'abc' from dual;
二、oracle常见查询
(1)DISTINCT语法结构
-- SELECT DISTINCT 列1,列2,列3... from 表名;
select distinct stuaddress,grade from jalen.stuinfo;
select distinct job from scott.emp;
(2)where的 =、IN、LIKE、BETWEEN...AND、AND、OR、NOT
-- where的 =、IN 、LIKE 、BETWEEN ... AND 、AND 、OR 、NOT
select * from scott.emp where job = 'CLERK';
select * from scott.emp where ename like '%A%';
select * from scott.emp where ename like 'A%';
select * from scott.emp where ename like '%E';
select * from scott.emp where ename like '_A%';
SELECT * FROM scott.emp where sal between 1000 and 1500;
select * from scott.emp where job='CLERK'and sal>=1000;
select * from scott.emp where comm <>0 and comm is not null;
select * from scott.emp where comm=0 or comm is null;
select * from scott.emp where deptno in (10,20) order by deptno desc;
(3)Oracle集合运算
Oracle集合运算就是把多个查询结果组合成一个查询结果,oralce的集合运算包括:INTERSECT(交集)、UINION ALL(交集重复)、UINION(交集不重复)、MINUS(补集)。表取别名时不要用as ,直接用空格。
- 1、intersect(交集),返回两个查询共有的记录。
- 2、union all(并集重复),返回各个查询的所有记录,包括重复记录。
- 3、union(并集不重复),返回各个查询的所有记录,不包括重复记录 (重复的记录只取一条)。
- 4、minus(补集),返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。
当我们使用Oracle集合运算时,要注意每个独立查询的字段名的列名尽量一致(列名不同时,取第一个查询的列名)、列的数据类型、列的个数要一致,不然会报错
-- 0、数据准备
create table scott.emp01 as select * from scott.emp;
select * from scott.emp; -- 14条记录
select * from scott.emp01; -- 复制emp表的14条记录
-- 1、INTERSECT(交集),返回两个查询共有的记录。
select * from scott.emp
intersect --交集
select * from scott.emp01; --两个表相同的记录,这里也是14条
-- 2、UNION ALL(并集重复),返回各个查询的所有记录,包括重复记录。
select * from scott.emp
union all --并集重复
select * from scott.emp01; --28条记录
-- 3、UNION(并集不重复),返回各个查询的所有记录,不包括重复记录 (重复的记录只取一条)。
select * from scott.emp
union --并集不重复
select * from scott.emp01; --14条记录
-- 4、MINUS(补集),返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录
select * from scott.emp
minus --补集
select * from scott.emp01; --0条记录
(4)Oracle连接查询
Oracle连接查询,包含内关联(inner jion )和外关联(outer join),其中外关联又分为左外关联(left [outer] join)、右外关联(right [outer] join)和全外关联(full [outer] join)其中外关联可以使用(+)来表示。
-- 内连接
select * from scott.emp e inner join scott.dept d on e.deptno=d.deptno;
select * from scott.emp e,scott.dept d where e.deptno=d.deptno;
-- 左外连接
select * from scott.emp e left join scott.dept d on e.deptno=d.deptno;
select * from scott.emp e,scott.dept d where e.deptno=d.deptno(+);
-- 右外连接
select * from scott.emp e right join scott.dept d on e.deptno=d.deptno;
select * from scott.emp e,scott.dept d where e.deptno(+)=d.deptno;
-- full join(全外连接)
select * from scott.emp e full join scott.dept d on e.deptno=d.deptno;
(5)Oracle子查询
--单行子查询
select * from scott.dept where deptno =
(select deptno from scott.emp where sal=800);
--多行子查询IN
select * from scott.dept where deptno in
(select deptno from scott.emp where sal>=800);
--多行子查询ANY :大于最小值
select * from scott.dept where deptno >ANY
(select deptno from scott.emp where sal>=800);
--多行子查询ALL :大于最大值
select * from scott.dept where deptno >ALL
(select deptno from scott.emp where sal>=800);
-- exists嵌套查询
select dname from scott.dept d where exists (
select sal from scott.emp e where e.deptno = d.deptno and sal > 3000);
三、Oracle的伪列
(1)rowid、rownum 伪列使用
Oracle的伪列是Oracle表在存储的过程中或查询的过程中,表会有一些附加列,称为伪列。伪列就像表中的字段一样,但是表中并不存储。伪列只能查询,不能增删改。Oracle的伪列有:rowid、rownum。
ORACLE ROWNUM表示的Oracle查询结果集的顺序,ROWNUM为每个查询结果集的行标识一个行号,第一行返回1,第二行返回2,依次顺序递增。(所以用作条件查询时只能使用< 或<=两个,where rownum<=6)
ROWNUM 与 ROWID 不同, ROWID 是插入记录时生成, ROWNUM 是查询数据时生成。ROWID 标识的是行的物理地址。 ROWNUM 标识的是查询结果中的行的次序。
-- ROWID 代码:
select e.*,e.rowid from scott.emp e;
select e.*,e.rowid from scott.emp e where e.rowid='AAAMgzAAEAAAAAgAAA';
-- rownum 代码:(注意:不加别名.)
select e.*,rownum from scott.emp e;
select e.*,rownum from scott.emp e where rownum < 5;-- 用于限定查询行数
select c.*,rownum from (select e.ename as ename,d.dname dname from scott.emp e,scott.dept d where e.deptno=d.deptno) c where rownum <= 5;
select * from (select * from scott.emp order by sal) where rownum<4;
(2)限定查询结果行数显示
select * from scott.emp where rownum <10;
四、Oracle函数
(1)Oracle字符型函数
函数 | 说明 | 案例 | 结果 |
---|---|---|---|
ASCII(X) | 求字符X的ASCII码 | select ASCII('A') FROM DUAL; | 65 |
CHR(X) | 求ASCII码对应的字符 | select CHR(65) FROM DUAL; | 'A' |
LENGTH(X) | 求字符串X的长度 | select LENGTH('ORACLE数据库')from DUAL; | 9 |
CONCATA(X,Y) | 返回连接两个字符串X和Y的结果 | select CONCAT('ORACLE','数据库') from DUAL; | ORACLE数据库 |
INSTR(X,Y[,START]) | 查找字符串X中字符串Y的位置,可以指定从Start位置开始搜索,不填默认从头开始 | SELECT INSTR('ORACLE数据库','数据') FROM DUAL; | 7 |
LOWER(X) | 把字符串X中大写字母转换为小写 | SELECT LOWER('ORACLE数据库') FROM DUAL; | oracle数据库 |
UPPER(X) | 把字符串X中小写字母转换为大写 | SELECT UPPER('Oracle数据库') FROM DUAL; | ORACLE数据库 |
INITCAP(X) | 把字符串X中所有单词首字母转换为大写,其余小写。 | SELECT INITCAP('ORACLE and mysql ') FROM DUAL; | Oracle And Mysql |
LTRIM(X[,Y]) | 去掉字符串X左边的Y字符串,Y不填时,默认的是字符串X左边去空格 | SELECT LTRIM('--ORACLE数据库','-') FROM DUAL; | ORACLE数据库 |
RTRIM(X[,Y]) | 去掉字符串X右边的Y字符串,Y不填时,默认的是字符串X右边去空格 | SELECT RTRIM('ORACLE数据库--','-') FROM DUAL; | ORACLE数据库 |
TRIM(X[,Y]) | 去掉字符串X两边的Y字符串,Y不填时,默认的是字符串X左右去空格 | SELECT TRIM('--ORACLE数据库--','-') FROM DUAL; | ORACLE数据库 |
REPLACE(X,old,new) | 查找字符串X中old字符,并利用new字符替换 | select replace('ORACLE数据库','ORACLE','关系型') fromdual; | 关系型数据库 |
SUBSTR(X,start[,length]) | 截取字符串X,从start位置(其中start是从1开始)开始截取长度为length的字符串,length不填默认为截取到字符串X末尾 | SELECT SUBSTR('ORACLE数据库',1,6) FROM DUAL; | ORACLE |
RPAD(X,length[,Y]) | 对字符串X进行右补字符Y使字符串长度达到length长度 | SELECT RPAD('ORACLE',9,'-') from DUAL; | ORACLE--- |
LPAD(X,length[,Y]) | 对字符串X进行左补字符Y使字符串长度达到length长度 | SELECT LPAD('ORACLE',9,'-') from DUAL; | ---ORACLE |
(2)Oracle日期函数
1、SYSDATE函数:该函数没有参数,可以得到系统的当前时间。
select sysdate from dual;
2、SYSTIMESTAMP函数:该函数没有参数,可以得到系统的当前时间,该时间包含时区信息,精确到微秒。
select systimestamp from dual;
3、DBTIMEZONE函数:该函数没有输入参数,返回数据库时区。
select dbtimezone from dual;
4、
to_char函数和to_date函数:
转换日期时间格式
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; --日期转指定格式的字符串
select to_date('2019-11-12','yyyy-mm-dd') from dual; --字符串转日期格式
5、ADD_MONTHS(r,n)函数:该函数返回在指定日期r上加上月份数n后的日期。其中
r:指定的日期。n:要增加的月份数,如果N为负数,则表示减去的月份数。
select to_char(add_months(
sysdate
,2),'yyyy-mm-dd') from dual;6、LAST_DAY(r)函数:返回指定r日期的当前月份的最后一天日期。
select to_char(last_day(sysdate),'yyyy-mm-dd') from dual;
7、NEXT_DAY(r,c)函数:返回指定R日期的后一周的与r日期字符(c:表示星期几,1:周天,2:周1)对应的日期。
select to_char(next_day(sysdate,1),'yyyy-mm-dd') from dual;
8、EXTRACT(time)函数:返回指定time时间当中的年、月、日、分等日期部分。
select
extract(year from sysdate) as year0,
extract(year from timestamp '2018-11-12 15:36:01') as year,
extract(month from timestamp '2018-11-12 15:36:01') as month,
extract(day from timestamp '2018-11-12 15:36:01') as day,
extract(hour from timestamp '2018-11-12 15:36:01') as hour,
extract(minute from timestamp '2018-11-12 15:36:01') as minute,
extract(second from timestamp '2018-11-12 15:36:01') as second
from dual;9、substr(to_char())--截取年、月、日、时、分、秒
select
substr(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),1,4) s_year ,
substr(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),6,2) s_month,
substr(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),9,2) s_day,
substr(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),13,2) s_hour,
substr(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),16,2) s_minute,
substr(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),19,2) s_second
from dual;10、MONTHS_BETWEEN(r1,r2)函数:该函数返回r1日期和r2日期直接的月份。当r1>r2时,返回的是正数,假如r1和r2是不同月的同一天,则返回的是整数,否则返回的小数。当r1<r2时,返回的是负数
select months_between(to_date('2019-10-1','yyyy-mm-dd'),to_date('2019-8-5','yyyy-mm-dd')) from dual;
11、ROUND(r[,f])函数:将日期r按f的格式进行四舍五入。如果f不填,则四舍五入到最近的一天。
select sysdate, --当前时间
round(sysdate, 'yyyy') as year, --按年
round(sysdate, 'mm') as month, --按月
round(sysdate, 'dd') as day, --按天
round(sysdate) as mr_day, --默认不填按天
round(sysdate, 'hh24') as hour --按小时
from dual;12、TRUNC(r[,f])函数:将日期r按f的格式进行截取。如果f不填,则截取到当前的日期。
select sysdate, --当前时间
trunc(sysdate, 'yyyy') as year, --按年
trunc(sysdate, 'mm') as month, --按月
trunc(sysdate, 'dd') as day, --按天
trunc(sysdate) as mr_day, --默认不填按天
trunc(sysdate, 'hh24') as hour --按小时
from dual;
(3)Oracle数值型函数
函数 | 解释 | 案例 | 结果 |
---|---|---|---|
ABS(X) | 求数值X的绝对值 | select abs(-5) from dual; | 5 |
COS(X) | 求数值X的余弦 | select cos(0.5) from dual; | 0.87758... |
ACOS(X) | 求数值X的反余弦 | select acos(0.5) from dual; | 1.04719... |
CEIL(X) | 求大于或等于数值X的最小整数 | select ceil(5.5) from dual; | 6 |
FLOOR(X) | 求小于或等于数值X的最大整数 | select floor(5.5) from dual; | 5 |
log(x,y) | 求x为底y的对数 | select log(2,32) from dual; | 5 |
mod(x,y) | 求x除以y的余数 | select mod(15,10) from dual; | 5 |
power(x,y) | 求x的y次幂 | select power(2,5) from dual; | 32 |
sqrt(x) | 求x的平方根 | select sqrt(25) from dual; | 5 |
round(x[,y]) | 求数值x在y位进行四舍五入。 | select round(5.615, 2), round(5.615), round(6.45,-1) from dual; | 5.62 | 6 |10 |
y不填时,默认为y=0; | |||
当y>0时,是四舍五入到小数点右边y位。 | |||
当y<0时,是四舍五入到小数点左边|y|位。 | |||
trunc(x[,y]) | 求数值x在y位进行直接截取 | select trunc(5.615, 2), trunc(5.615), trunc(5.615,-1) from dual; | 5.61 | 5 | 0 |
y不填时,默认为y=0; | |||
当y>0时,是截取到小数点右边y位。 | |||
当y<0时,是截取到小数点左边|y|位。 |
(4) Oracle转换函数
函数 | 解释 | 案例 | 结果 |
to_char(x[,f]) | 把字符串或时间类型x按格式f进行格式化转换为字符串。 | select to_char(123.46,'999.9') from dual; | 123.5 |
select to_char(sysdate,'yyyy-mm-dd') from dual; | 2019-12-16 | ||
to_date(x[,f]) | 可以把字符串x按照格式f进行格式化转换为时间类型结果。 | select to_date('2019-12-16','yyyy-mm-dd') from dual; | 2019/12/16 |
to_number(x[,f]) | 可以把字符串x按照格式f进行格式化转换为数值类型结果。 | select to_number('123.74','999.99') from dual | 123.74 |
asciistr(x) | 把字符串x转换为数据库字符集对应的ASCII值 | select asciistr('Oracle技术圈') from dual; | Oracle\6280\672F\5708 |
bin_to_num(x1[x2...]) | 把二进制数值转换为对应的十进制数值 | select bin_to_num(1,0,0) from dual; | 4 |
cast(x as type) | 数据类型转换函数,该函数可以把x转换为对应的type 的数据类型,基本上用于数字,字符,时间类型安装 数据库规则进行互转, | select cast('123' as number) num, cast(123 as varchar2(3)) as ch, cast(to_date('20181112','yyyymmdd') as varchar2(12)) as time from dual; | 123/'123'/12-11月-18 |
(三列值,用"/"隔开) | |||
convert(x,d_chset[,r_chset]) | 字符串在字符集间的转换函数,对字符串x按照原字符集 r_chset转换为目标字符集d_chset,当r_chset不填时, 默认选择数据库服务器字符集。 | select CONVERT('oracle技术圈','US7ASCII','ZHS16GBK') from dual; | oracle??? |
(5) Oracle聚合函数
函数 | 说明 | 备注 |
count(col) | 计数 | |
sum(col) | 求和 | |
avg(col) | 均值 | |
min(col)/max(col) | 最小/最大值 |