Oracle的sql基本语法总结(2) -- 运算符、查询(distinct、where条件运算、交并补集合运算、连接查询、子查询)、伪列、限定查询结果行数、函数

 

目录

一、oracle运算符

(1) Oracle算术运算符

(2) Oracle关系运算符

(3) Oracle的逻辑运算符

(4) 字符串连接符||

二、oracle常见查询

(1)DISTINCT语法结构

(2)where的 =、IN、LIKE、BETWEEN...AND、AND、OR、NOT

(3)Oracle集合运算

(4)Oracle连接查询

(5)Oracle子查询

三、Oracle的伪列

(1)rowid、rownum 伪列使用

(2)限定查询结果行数显示

四、Oracle函数

(1)Oracle字符型函数

(2)Oracle日期函数

(3)Oracle数值型函数

(4) Oracle转换函数

(5) Oracle聚合函数


一、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字符型函数

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数值型函数

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 dual123.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)最小/最大值 
评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值