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