JavaWeb开发知识总结(二)-(oracle_查询)

我不是女神ヾ 2022-06-14 10:28 275阅读 0赞

# JavaWeb开发知识总结(Oracle-查询) #

## **1. Oracle查询** ##

### **1.1 单表查询** ###

#### **1.1.1 简单条件查询：和mysql数据库的语句相同** ####

--1.精确查询：能够精确的确定到查询的记录
    select 字段名  from 表名  where 字段名 = 值;
    --2.模糊查询：like查询,规则和mysql相同,%是匹配零个或多个字符,_是匹配一个字符
    select 字段名  from 表名  where 字段名  like 条件;
    --3.and查询：and关键字查询,用法和mysql相同,是条件的并列
    select 字段名  from 表名  where 条件1 and 条件2;
    --4.or查询：or查询,和mysql用法相同,是条件的或
    select 字段名  from 表名  where 条件1 or 条件2;
    --5.范围查询：使用>,>=,<,<=符号或者between ... and 关键字查询,用法和mysql相同
    select 字段名  from 表名  where 字段名 > 值;
    select 字段名  from 表名  where 字段名 between 值1 and 值2;
    --6.空值查询：is null和is not null关键字查询,用法和mysql相同
    select 字段名  from 表名  where 字段名 is null; -- 查询某个字段为null
    select 字段名  from 表名  where 字段名 is not null; -- 查询某个字段不为null

#### **1.1.2 去重查询：和mysql用法相同** ####

--语法：使用distinct关键字,规则和mysql相同,当查询的是多个字段时,是针对这多个字段的去重
    select distinct 字段名 from 表名 where 条件;

#### **1.1.3 排序查询：用法和mysql相同** ####

--语法：使用order by关键字,默认是asc升序,desc是降序
    select 字段名 from 表名 [条件] order by 字段名 [asc]; -- 升序排列
    select 字段名 from 表名 [条件] order by 字段名 desc; -- 降序排列

#### **1.1.4 聚合统计：和mysql有区别** ####

--1.基本的统计函数：用法和mysql相同
    select sum(字段名) from 表名 [条件]; -- sum求和统计
    select avg(字段名) from 表名 [条件]; -- avg求平均值统计
    select max(字段名) from 表名 [条件]; -- max求最大值统计
    select min(字段名) from 表名 [条件]; -- min求最小值统计
    select count(任意类型的值) from 表名 [条件]; -- count的参数可以是任意值,一般使用count(1)进行统计
        -- count(字段名):统计的是表中该字段不为null的个数,当表中无记录时,返回null;
        -- count(*):统计表中所有记录,当该字段为null时,会记录null的个数,当表中无记录时,返回null;
        -- count(1):统计表中的所有记录,当该字段为null时,会记录null的个数,当表中无记录时,返回0.
    --2.聚合统计：ORACLE的聚合统计是通过分组函数来实现的,和mysql有区别
    Oracle中：select后一定是分组聚合的条件或者是聚合函数,不能为*
    mysql中：select后可以是分组聚合的条件或者是聚合函数或者是表中的其他字段,也可以为*
    select [只能是group by分组中的字段],统计函数 from 表名 group by 分组字段名 [having 条件(条件只能是统计函数)];
    --3.注意事项：
    group by：后面的分组依据可以有多个字段或者是聚合函数的结果
    having：是分组之后的条件查询
    where：是分组之前的条件查询
    --4.语句的使用顺序：
    S(select)...F(from)...W(where)...G(group by)...H(having)...O(order by) --适用于Oracle
    S(select)...F(from)...W(where)...G(group by)...H(having)...O(order by)...L(limit) --适用于mysql

#### **1.1.5 伪列查询：Oracle数据库特有** ####

在Oracle的表的使用过程中，实际表中还有一些附加的列，称为伪列。伪列就像表中的列一样，但是在表中并不存储，伪列是Oracle数据库自动为每一个表创建的。**伪列只能查询，不能进行增删改操作。**常见的两个伪列：ROWID和 ROWNUM。  
**ROWID：**  
表中的每一行在数据文件中都有一个物理地址，ROWID 伪列返回的就是该行的物理地址。使用 ROWID 可以快速的定位表中的某一行。ROWID 值可以唯一的标识表中的一行。由于 ROWID 返回的是该行的物理地址，因此使用ROWID可以显示行是如何存储的，并且使用ROWID查询数据效率较高。  
用法：`select rowid from 表名;`

**ROWNUM：是Oracle数据库实现分页的基础**  
在查询的结果集中，ROWNUM 为结果集中每一行标识一个行号，第一行返回1，第二行返回2，以此类推。通过 ROWNUM伪列可以限制查询结果集中返回的行数。**注意： rownum 是在查询语句扫描每条记录时产生的。**  
用法：`select rownum from 表名;`

### **1.2 连接查询** ###

#### **1.2.1 内连接查询：用法和mysql相同** ####

语法:inner join语句,inner可以省略,显式内连接和隐式内连接可以相互转换
    --显式内连接查询:
    select 字段名 from 表名1 inner join 表名2 on 条件语句; -- 条件是两个表中相同的属性值判定条件,一般是外键约束
    -- 如:select * FROM 客户表 c inner join 订单表 o on c.客户id = o.客户id; 客户id是订单表中的外键
    --隐式内连接查询:
    select 字段名 from 表名1,表名2 where 条件语句; -- 条件一般是外键约束

#### **1.2.2 外连接查询：和mysql有区别** ####

--方式1:Oracle和mysql通用的格式
        语法:outer join语句,左右外连接可以相互转换,将表的先后顺序调换即可
        --左外连接查询:left outer join语句,outer一般可以省略,查询的是左边表的全部数据和两个表的交集数据,左边有的记录而右边没有的记录,右边显示为null值
        select 字段名 from 表名1 left join 表名2 on 条件语句;
        --右外连接查询:right outer join语句,outer一般可以省略,查询的是右边表的全部数据和两个表的交集数据,右边有的记录而左边没有的记录,左边显示为null值
        select 字段名 from 表名1 left join 表名2 on 条件语句;
    --方式2：Oracle特有的方式，左外连接在右表加(+)，右外连接在左表加(+)
    select 字段名 from 表名1 t1,表名2 t2 where t1.id=t2.id(+); -- 左外连接
    select 字段名 from 表名1 t1,表名2 t2 where t1.id(+)=t2.id; -- 右外连接

### **1.3 子查询：和mysql用法相同** ###

#### **1.3.1 单行子查询** ####

--就是将子查询的单条记录结果作为查询的条件值，子查询的语句只返回一条记录，连接符有：>,>=,=,<,<=,<>(不等于)
    select 字段名 from  表 where 字段 > (子查询);

#### **1.3.2 多行子查询** ####

--就是子查询的多条结果作为查询的条件范围，连接符：all，in，any
    select 字段名 from 表 where 字段名 in (子查询);--in代表在子查询的结果范围内进行查找,not in是去除子查询结果
    select 字段名 from 表 where 字段名 连接符(>) all (子查询);--all代表是子查询结果的所有值，如：>代表的是大于子查询的所有值，就是大于子查询的最大值；<是表示小于子查询的最小值
    select 字段名 from 表 where 字段名 连接符(>) any (子查询);--any代表是子查询的结果的任意一个值,如：>代表的就是大于子查询的最小值即可，<代表的是小于子查询的最大值即可

### **1.4 分页查询：和mysql有区别** ###

**mysql中的分页查询使用`limit` 关键字即可实现；Oracle中没有分页查询的关键字只能通过`rownum` 伪列才能实现分页查询。**

#### **1.4.1 简单分页：rownum是随着扫描产生，不能使用>=进行数据的截取** ####

--使用rownum伪列实现数据的分页，其思想就是先生成从1开始到要查询到数据总数，然后通过rownum进行截取，即先让rownum编号生成，然后再进行截取
    --假定每组显示10条数据：
    select rownum,字段名 from 表名 where rownum <= 10; -- 第一页数据
    select * from (select rownum r,字段名 from 表名 where rownum <= 20) where r>10; -- 第二页数据
    select * from (select rownum r,字段名 from 表名 where rownum <=30) where r>20; -- 第三页数据,后续页类似
    
    --注意事项：理解：rownum是随着查询语句扫描每行数据时生成的
    select rownum,字段名 from 表名 where rownum >=1; -- 查询结果是所有的数据
    select rownum,字段名 from 表名 where rownum =1; -- 查询的是第一条记录
    select rownum,字段名 from 表名 where rownum >1; -- 没有查询结果

**差集运算实现分页：了解**

--差集：是查询获取A集合中不包含B集合中数据的数据，运算符是：minus
    --差集实现分页的思路：先查询出前20条记录作为A集合，再查出前10条记录作为B集合，使用A集合对B集合进行差集运算
    select rownum,字段名 from 表名 where rownum <= 20 minus select rownum,字段名 from 表名 where rownum <= 10; -- 第一页数据
    select rownum,字段名 from 表名 where rownum <= 30 minus select rownum,字段名 from 表名 where rownum <= 20; -- 第二页数据，后续页数据类似

#### **1.4.2 排序分页** ####

**排序的分页查询思路：先将数据排序，再产生rownum伪列值，然后再进行分页查询。由于生成rownum伪列是在扫描时生成，分组函数是在查询之后生成，即rownum生成早于排序，如果在同一个语句中进行排序和生产rownum伪列，则排序的结果将rownum值打乱，这时进行分页查询时结果是未排序之前的结果。**

**使用嵌套查询实现分页：**

--实现排序分页思路：先进行数据排序，再生产rownum伪列，然后进行截取数据，三层嵌套查询
    select rownum r,t.* from (select 字段名 from 表名 order by 字段名) t where r<=10; --第一页
    select * from (select rownum r,t.* from (select 字段名 from 表名 order by 字段名) t where r<=20) where r>10; --第二页
    select * from (select rownum r,t.* from (select 字段名 from 表名 order by 字段名) t where r<=30) where r>20; --第三页，后续页数据类似查询

**使用分析函数`ROW_NUMBER` 实现分页：推荐使用**

--row_number是将数据按照排序后的顺序依次编号，不管值是否相同
    select * from (select row_number() over(order by 字段名 asc/desc) 别名/rownumber,字段名 from 表) where rownumber <=10;--第一页
    select * from (select row_number() over(order by 字段名 asc/desc) 别名/rownumber,字段名 from 表) where rownumber >10 and rownumber <= 20;--第二页
    select * from (select row_number() over(order by 字段名 asc/desc) 别名/rownumber,字段名 from 表) where rownumber >20 and rownumber <= 30;--第三页，后续页数据类似

## **2. 单行函数** ##

### **2.1 字符函数(常用)** ###

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>LENGTH</td> 
   <td>以字符给出字符串的长度</td> 
  </tr> 
  <tr> 
   <td>SUBSTR</td> 
   <td>取字符串的子串</td> 
  </tr> 
  <tr> 
   <td>CONCAT</td> 
   <td>拼接两个字符串，与 || 相同</td> 
  </tr> 
 </tbody> 
</table>

--Oracle数据库中提供了一张伪表，用于测试函数，该伪表只有1行1列
    --lenght函数：获取给定字符串的长度
    select length('somnus') from dual;
    --substr函数：获取指定字符串的子串，下标是从1开始，和Java有点区别
    select substr('somnus',2,2) from dual; --结果是om
    --concat函数：拼接两个字符串
    select concat('somn','us') from dual; --结果是somnus
    
    --注意：
    select 'somn'||'us' from dual; --拼接字符串，使用的||而不是+
    select 123||'' from dual; --当数字类型数据和空字符串拼接，可以将数字转换为字符串

### **2.2 数值函数(常用)** ###

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>round</td> 
   <td>按 precision 精度 4 舍 5 入</td> 
  </tr> 
  <tr> 
   <td>trunc</td> 
   <td>按照 precision 截取 value</td> 
  </tr> 
  <tr> 
   <td>mod</td> 
   <td>取模</td> 
  </tr> 
 </tbody> 
</table>

--round函数：四舍五入函数
    select round(100.567) from dual;--默认的是只保留整数位，结果是100
    select round(100.567,2) from dual;--保留2位小数，结果是100.57
    --trunc函数：截取函数
    select round(100.567) from dual;--默认的是只保留整数位，结果是100
    select round(100.567,2) from dual;--保留2位小数，结果是100.56
    --mod函数：取模
    select mod(10,3) from dual;--结果是1

### **2.3 日期函数(常用)** ###

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>add_months</td> 
   <td>在当前日期基础上加指定的月</td> 
  </tr> 
  <tr> 
   <td>last_day</td> 
   <td>获取所在月最后一天</td> 
  </tr> 
  <tr> 
   <td>trunc</td> 
   <td>日期截取</td> 
  </tr> 
 </tbody> 
</table>

--Oracle数据库可以通过sysdate系统变量获取当前系统的时间
    --add_months函数：在指定日期上进行月份的加减，加减后的日期与当前日期相差的是整数个月份
    select add_months(sysdate,2) from dual;--获取2个月前的当前时间
    --last_day函数：获取所在月的最后一天，获取的是当前时间所在月的最后一天的当前时间
    select last_day(sysdate) from dual;--获取当前时间所在月的最后一天
    select last_day(sysdate-1) from dual;--获取当前时间减去一天所在月的最后一天
    --trunc函数：日期截取，如：sysdate获取的当前时间是：2017/6/8 12:35:00
    select TRUNC(sysdate) from dual;--结果是2017/6/8
    select TRUNC(sysdate,'yyyy') from dual;--结果是2017/1/1
    select TRUNC(sysdate,'mm') from dual;--结果是2017/6/1
    select TRUNC(sysdate,'hh') from dual;--结果是2017/6/8 12:00:00
    select TRUNC(sysdate,'mi') from dual;--结果是2017/6/8 12:35:00
    --select TRUNC(sysdate,'ss') from dual;--会报错
    
    --注意事项：trunc截取函数只能截取到分钟级别，不能截取到秒级别
    Oracle中的时间的字符串不区分大小写
    yyyy：年
    mm：月份
    dd：天
    hh：时--注意：hh24代表的是采用24时制，默认的是12时制
    mi：分钟 -- 和Java中不一致
    ss：秒

### **2.4 转换函数(常用)** ###

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>to_char</td> 
   <td>数字/日期转换为字符串</td> 
  </tr> 
  <tr> 
   <td>to_date</td> 
   <td>字符串转日期</td> 
  </tr> 
  <tr> 
   <td>to_number</td> 
   <td>字符串转数字</td> 
  </tr> 
 </tbody> 
</table>

--to_char函数：数字转换为字符串
    select TO_CHAR(1024) from dual;
    --to_char函数：日期转换为字符串
    select TO_CHAR(sysdate,'yyyy-mm-dd') from dual;--将当前的时间转换为指定格式的字符串
    --to_date函数：字符串转换为日期
    select TO_DATE('1992-08-09','yyyy-mm-dd') from dual;--将字符串转换为指定个合适的日期
    --to_number函数：字符串转换为数字
    select to_number('100') from dual;
    
    --注意事项：
    select '100'+0 from dual;--可以将字符串转换为数字
    进行日期的比较时：比较的两个日期的格式要相同
    --按字符串比较，需要将日期类型时间转换为和字符串时间的格式相同
    --按日期比较，需要将字符串时间转换为和日期类型时间的格式相同

### **2.5 其他函数** ###

**空值函数：和mysql有区别**

--Oracle数据库的空值函数有两个nvl和nvl2，mysql中的空值函数是isnull，两种空值函数的用法相同
    --nvl函数：
    NVL(检测的值,如果为null的值)
    --nvl2函数：
    NVL2(检测的值,如果不为null的值,如果为null的值)
    --nvl和nvl2的区别：
    nvl：要显示的值必须和要转换字段值的类型相同，否则报错
    nvl2：要显示的值必须和要转换字段值的类型不一定相同

**条件取值函数：Oracle数据库特有**

--decode函数：条件只能是值，不能是表达式，将条件的值和后面的值n一次比较，相同则返回对应的值后的翻译值，如果没有找到则返回的是缺省值，当没有缺省值这返回null
    decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值);--根据条件返回相应值
    select decode(14,1,'somnus',2,'java','shu') from dual;--返回的是'shu'

**条件取值函数2：Oracle和mysql通用**

--case...when...then...else...end函数:也是条件取值函数,else可以省略,end不能省略,可以和decode表达式相互转换
    --方式1：条件值只能是值，不能是表达式，匹配规则和decode相同
    select (case 条件值 when 值1 then 翻译值1 when 值2 then 翻译值2 ... else 缺省值 end) from dual;
    select (case 5  when 1 then 'somnus'  when 2 then 'java' else 'heima' end) from dual;--结果是'heima'
    --方式1：逻辑表达式返回true则取对应的翻译值，此方式灵活性较高
    select (case when 逻辑表达式1 then 翻译值1 when 逻辑表达式2 then 翻译值2 ... else 缺省值 end) from dual;
    select (case when 2>3 then 'somnus'  when 2>1 then 'java' else 'heima' end) from dual;--结果是'java'

## **3. 行列转换(掌握)** ##

行列转换多用于报表统计，如先有区域表`t_area`和收自来水税费台帐表`t_account`表，其中`t_account`表中有一个区域`aeraid`字段依赖于`t_area`表的主键，先要求按月份统计2017年各个区域的水费：

--money是t_account表中的收费字段
    -- select sum(money) from t_account t where t.year='2017' group by t.areaid;统计的是分区域的全年的水费
    -- 当限定统计的月份时，则统计的就是这一地区的这一年的指定月份的数据
      select (select name from t_area where t_area.id = t.areaid) 区域, --Oracle中分组后select后的字段只能是group by后的字段或是聚合函数，则需要通过区域id再次查询该区域的名称
      sum(case when month='01' then money else 0 end ) 一月, -- 统一1月份数据，将其他月份数据置为0
      sum(case when month='02' then money else 0 end ) 二月,
      sum(case when month='03' then money else 0 end ) 三月,
      sum(case when month='04' then money else 0 end ) 四月,
      sum(case when month='05' then money else 0 end ) 五月,
      sum(case when month='06' then money else 0 end ) 六月,
      sum(case when month='07' then money else 0 end ) 七月,
      sum(case when month='08' then money else 0 end ) 八月,
      sum(case when month='09' then money else 0 end ) 九月,
      sum(case when month='10' then money else 0 end ) 十月,
      sum(case when month='11' then money else 0 end ) 十一月,
      sum(case when month='12' then money else 0 end ) 十二月
      from t_account t where t.year='2017' group by t.areaid -- 按照区域id进行分组

按照季度统计2017年各区域的水费：

select (select name from t_area where t_area.id = t.areaid) 区域,
    sum(case when month >='01' and month <='03' then money else 0 end ) 第一季度,
    sum(case when month >='04' and month <='06' then money else 0 end ) 第二季度,
    sum(case when month >='07' and month <='09' then money else 0 end ) 第三季度,
    sum(case when month >='10' and month <='12' then money else 0 end ) 第四季度,
    from t_account t where t.year='2017' group by t.areaid

## **4. 分析函数(排名使用)** ##

排名的规则：

![oracle分析函数][oracle]

--RANK函数：相同的值排名相同，排名跳跃
    select rank() over(order by 字段名 asc/desc ),字段名 from 表名;
        select rank() over(order by 分数 desc ),学生姓名 from 成绩表; --将学生的成绩按照分数进行排序
    --DENSE_RANK函数：相同的值排名相同，排名连续
    select dense_rank() over(order by 字段名 asc/desc ),字段名 from 表名;
        select dense_rank() over(order by 分数 desc ),学生姓名 from 成绩表; --将学生的成绩按照分数进行排序
    --ROW_NUMBER函数：返回连续的排名，无论值是否相等，该函数可以用于数据分页
    select row_number() over(order by 字段名 asc/desc ),字段名 from 表名;
        select row_number() over(order by 分数 desc ),学生姓名 from 成绩表; --将学生的成绩按照分数进行排序

## **5. 集合运算** ##

集合运算，集合运算就是将两个或者多个结果集组合成为一个结果集。集合运算包括：  
1. UNION ALL(并集)，返回各个查询的所有记录，包括重复记录。  
2. UNION(并集)，返回各个查询的所有记录，不包括重复记录。  
3. INTERSECT(交集)，返回两个查询共有的记录。  
4. MINUS(差集)，返回第一个查询出的记录减去第二个查询出的记录之后剩余的记录，就是第一个查询出的记录所独有的记录。

![oracle集合运算][oracle 1]

用法：

--union all:不去除重复值，两个查询语句没有顺序
    select * from student where id<=7 union all select * from student where id>=5;--id为567的会出现两次
    --union:去除重复值，两个查询语句没有顺序
    select * from student where id<=7 union select * from student where id>=5;--id为567的只会出现一次
    --intersect：取交集，两个查询语句没有顺序
    select * from student where id<=7 intersect select * from student where id>=5;--结果是id为567的学生数据
    --minus：差集运算，两个查询语句有顺序，取出的是左边的查询语句中的结果值，差集运算可以作为简单的数据分页
    select * from student where id<=7 minus select * from student where id>=5;--结果是id为1234的学生数据

[oracle]: /images/20220614/e772de52249c4183b96abc140e923665.png
[oracle 1]: /images/20220614/77cce2cc04864633b0dc5bd8e3cafb20.png