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

最新推荐文章于 2022-01-23 21:01:43 发布

Jalen data analysis

最新推荐文章于 2022-01-23 21:01:43 发布

阅读量1.3k

点赞数 1

分类专栏： Oracle 文章标签： Oracle运算符 Oracle查询语句 Oracle伪列 Oracle函数

本文链接：https://blog.csdn.net/weixin_41685388/article/details/103534206

版权

Oracle 专栏收录该内容

6 篇文章 3 订阅

订阅专栏

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