Oracle（三）Oracle运算符

运算符

Oracle运算符包括算术运算符、关系运算符和逻辑运算符。

算术运算符（+、-、*、/ 四个）

求2018年上学期数学的平均成绩。

select a.*, b.coursename, c.stuname
  from score a, course b, stuinfo c
 where a.courseid = b.courseid
   and a.stuid = c.stuid;

select b.coursename, sum(a.score) / count(1)
  from score a, course b
 where a.courseid = b.courseid
   and a.courseid = 'R20180101'
 group by b.coursename;

关系运算符

符号	解释	符号	解释
=	等于	<>或者!=	不等于
>	大于	>=	大于或者等于
<	小于	<=	小于或者等于

逻辑运算符: AND、OR、NOT

案例2、查看2018年上学期数学成绩在85-95分之间的同学：

select a.*, b.coursename, c.stuname
  from score a, course b, stuinfo c
 where a.courseid = b.courseid
   and a.stuid = c.stuid
   and a.score >= '85'
   and a.score <= '95'

字符串连接符||

Oracle中利用字符串连接符||（即双竖线）来连接查询结果。

案例1、字符串连接符||：

select '姓名：' || c.stuname || ', 课程：' || b.coursename || ', 成绩：' || a.score || '分。' as sxcj
  from score a, course b, stuinfo c
 where a.courseid = b.courseid
   and a.stuid = c.stuid

Oracle DISTINCT

对Oracle查询结果进行重复数据的消除。

SELECT DISTINCT 列1,列2,列3... from 表名;

1、当关键字DISTINCT后面只有一个列1时，表示的是单个字段查询结果的不重复数据，当后面跟着多个列值时，表示的是多个字段组成的查询结果的所有唯一值，进行的是多个字段的分组消除。

案例1、查询学生成绩表中课程“数学(2018上学期)”的所有出现的成绩，不重复：(案例所需表结构)

select distinct b.coursename, t.score
  from score t, course b
 where t.courseid = b.courseid
   and t.courseid = 'R20180101';

Oracle 条件查询

Oracle条件查询时经常使用=、IN、LIKE、BETWEEN…AND来作为条件查询的操作符。在Oracle select 查询中where条件经常使用到这几个操作符。下列案例所需表结构参考：学生系统表结构。

=操作符

表示列值等于一个固定值所查询出的结果。

案例1、查询学生成绩表“score”中课程id为“R20180101”，成绩为“85”分的同学信息。

select t.stuid,
       t.courseid,
       t.score,
       b.stuname,
       b.sex,
       b.age,
       b.classno,
       b.grade
  from score t, stuinfo b
 where t.stuid = b.stuid
   and t.courseid = 'R20180101'
   and t.score = '85'

IN操作符

查询其列值在指定的列表中的查询结果。

案例2、查询学生成绩表“score”中课程id为“R20180101”，成绩为“79”、“85”、“89”分的同学信息。

--利用逻辑运算符or 和条件"=" 查询
select t.stuid,
       t.courseid,
       t.score,
       b.stuname,
       b.sex,
       b.age,
       b.classno,
       b.grade
  from score t, stuinfo b
 where t.stuid = b.stuid
   and t.courseid = 'R20180101'
   and (t.score = '85' or t.score ='89' or t.score ='79');


-- 利用Oracle操作符”IN“查询  
select t.stuid,
       t.courseid,
       t.score,
       b.stuname,
       b.sex,
       b.age,
       b.classno,
       b.grade
  from score t, stuinfo b
 where t.stuid = b.stuid
   and t.courseid = 'R20180101'
   and t.score in ('85','89','79');

BETWEEN…AND

查询列值包含在指定区间内的查询结果 。

案例3、查询学生成绩表“score”中课程id为“R20180101”，成绩在70-95之间的学生信息。

select t.stuid,
       t.courseid,
       t.score,
       b.stuname,
       b.sex,
       b.age,
       b.classno,
       b.grade
  from score t, stuinfo b
 where t.stuid = b.stuid
   and t.courseid = 'R20180101'
   and  t.score between '70' and '95'

LIKE模糊查询

通过字符匹配检索出所需要的数据行。字符匹配操作可以使用通配符“%”和“_” :

1、%：表示零个或者多个任意字符。

2、_：代表一个任意字符。

3、\：指转义字符，“%”在字符串中表示一个字符“%”。

案例4、查询学生基本信息表“STUINFO”中姓“张”的学生基本信息：

//姓张，表示开头第一个字为“张”，而后面的字符数不确定，所以用 % 代替
select * from STUINFO t where t.stuname like '张%';

案例5、查询学生基本信息表“STUINFO”中姓“张”的，并且姓名长度是两个字的学生基本信息：

//以 张 开头，且后面只跟一个字符
select * from STUINFO t where t.stuname like '张_';

Oracle 集合运算

集合运算 就是把多个查询结果组合成一个查询结果，包括

• INTERSECT(交集)：返回两个查询共有的记录。
• UINION ALL(并集重复)：返回各个查询的所有记录，包括重复记录。
• UINION(并集不重复)：返回各个查询的所有记录，不包括重复记录（重复的记录只取一条）
• MINUX(补集)：返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。

当我们使用Oracle集合运算时，要注意每个独立查询的字段名的列名尽量一致（列名不同时，取第一个查询的列名）、列的数据类型、列的个数要一致，不然会报错。

简单示例：

假如

A表中存在数据为（1,2,3,4）

B表中存在数据为（2,3,4,5）

那么各种集合运算的情况分别如下：

交集

select * from stuinfo 
intersect
select * from stuinfo_2018;

2,3,4

并集重复

select * from stuinfo 
uinion all
select * from stuinfo_2018;

1,2,3,4,2,3,4,5

并集不重复

select * from stuinfo 
uinion all
select * from stuinfo_2018;

1,2,3,4,5

补集

select * from stuinfo 
minus
select * from stuinfo_2018;

1

Oracle 连接查询

包括内关联（inner join）和外关联（outer join）；

外关联：左外关联（left outer join）、右外关联（right outer join）、全外关联（full outer join）。

外关联可以使用（+）表示。

内连接

两张表通过某个字段进行内关联，查询结果是通过该字段按关系运算符匹配出的数据行。其中可以包括：

1、等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列。

2、不等连接：在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值，这些关系运算符包括>、>=、<=、!>、!<、<>。

案例1、查询学生信息表（stuinfo）的学生信息，关联查询该学生的班级信息（class）

select a.stuid,
       a.stuname,
       a.classno,
       b.classno,
       b.classname,
       b.monitorid,
       b.monitorname,
       b.classaddress
  from stuinfo a, class b
 where a.classno = b.classno;

外连接：

外连接，返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接或左连接))、右表(右外连接或右连接)或两个边接表(全外连接)中的所有数据行。

1、left join(左联接)等价于（left outer join）返回包括左表中的所有记录和右表中联结字段相等的记录。

2、right join(右联接)等价于（right outer join）返回包括右表中的所有记录和左表中联结字段相等的记录。

3.、full join （全连接）等价于（full outer join）查询结果等于左外连接和右外连接的和。

下面案例利用学生信息表（stuinfo）和之前的备份表（stuinfo_2018）做案例解析：

stuinfo表数据：

stuinfo_2018表数据：

案例2、left join(左联接）

左外连接，+ 符号放在右面表的id后面。

--左外连接（stuinfo表中数据都存在,stuinfo_2018不在stuinfo中存在的学生相关字段为null值）
select a.*, b.stuid, b.stuname
  from stuinfo a
  left join stuinfo_2018 b
    on a.stuid = b.stuid;

--左外连接（利用（+）在右边）另外一种写法
select a.*, b.stuid, b.stuname
  from stuinfo a,stuinfo_2018 b 
  where a.stuid=b.stuid(+);

案例3、right join（右连接）

--右外连接（stuinfo_2018表中数据都存在,stuinfo不在stuinfo_2018存在的学生相关字段为null值）
select a.*, b.stuid, b.stuname
  from stuinfo a
  right join stuinfo_2018 b
    on a.stuid = b.stuid;

--右外连接（利用（+）在左边）另外一种写法
select a.*, b.stuid, b.stuname
  from stuinfo a,stuinfo_2018 b 
  where a.stuid(+)=b.stuid;

案例4、full join（全外连接）

--(全外连接（stuinfo、stuinfo_2018表中数据都存在,
 --stuinfo不在stuinfo_2018存在的学生相关字段为null值,
 --stuinfo_2018不在stuinfo存在的学生相关字段为null值）
select a.*, b.stuid, b.stuname
  from stuinfo a
  full join stuinfo_2018 b
    on a.stuid = b.stuid;

Oracle 的伪列

• 伪列是在Oracle表在存储过程中或查询过程中，表会有一些附加列，成为伪列。
• 伪列就像表中的字段一样，但是表中并不存储。伪列只能查询，不能增删改。
• Oracle的伪列有：rowid、rownum。

ORACLE ROWID

ROWID：表中的每行数据在数据文件中的物理地址。 可以通过其快速定位表中的某一行。

Oracle表中的每一行在数据文件中都有一个物理地址， ROWID 伪列返回的就是该行的物理地址。使用 ROWID 可以快速的定位表中的某一行。 ROWID 值可以唯一的标识表中的一行。通过Oracle select 查询出来的ROWID，返回的就是该行数据的物理地址。

查询ROWID，注意：查询工具可能默认不显示ROWID栏位，所以看不到ROWID的值，需要设置别名才能显示。

select t.stuid, stuname, t.rowid as rid from STUINFO t;
select t.stuid, stuname, t.rowid as rid from stuinfo t where t.rowid='AAAWjDAAGAABQFGAAA';

ORACLE ROWNUM

ROWNUM：代表查询结果集的序号。

ORACLE ROWNUM表示的Oracle查询结果集的顺序，ROWNUM为每个查询结果集的行标识一个行号，第一行返回1，第二行返回2，依次顺序递增。

ROWNUM 与 ROWID 不同， ROWID 是插入记录时生成， ROWNUM 是查询数据时生成。ROWID 标识的是行的物理地址。 ROWNUM 标识的是查询结果中的行的次序。

select t.stuid, stuname, ROWNUM from STUINFO t;

ROWNUM经常用来限制查询的结果返回的行数，求前几行或前几名的数据。（类似mysql的limit）

select * from
	(select t.stuid, stuname, ROWNUM from STUINFO t)
WHERE ROWNUM < 4;