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

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 字符函数(常用)

函数	说明
LENGTH	以字符给出字符串的长度
SUBSTR	取字符串的子串
CONCAT	拼接两个字符串，与 || 相同

--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 数值函数(常用)

函数	说明
round	按 precision 精度 4 舍 5 入
trunc	按照 precision 截取 value
mod	取模

--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 日期函数(常用)

函数	说明
add_months	在当前日期基础上加指定的月
last_day	获取所在月最后一天
trunc	日期截取

--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 转换函数(常用)

函数	说明
to_char	数字/日期转换为字符串
to_date	字符串转日期
to_number	字符串转数字

--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. 分析函数(排名使用)

​ 排名的规则：

--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(差集)，返回第一个查询出的记录减去第二个查询出的记录之后剩余的记录，就是第一个查询出的记录所独有的记录。

用法：

--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的学生数据