JavaWeb开发知识总结(二)-(oracle_查询)
来源:互联网 发布:定额软件app 编辑:程序博客网 时间:2024/06/08 12:13
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; -- 查询某个字段为nullselect 字段名 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) --适用于OracleS(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,anyselect 字段名 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 字符函数(常用)
--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函数:四舍五入函数select round(100.567) from dual;--默认的是只保留整数位,结果是100select round(100.567,2) from dual;--保留2位小数,结果是100.57--trunc函数:截取函数select round(100.567) from dual;--默认的是只保留整数位,结果是100select round(100.567,2) from dual;--保留2位小数,结果是100.56--mod函数:取模select mod(10,3) from dual;--结果是1
2.3 日期函数(常用)
--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:00select TRUNC(sysdate) from dual;--结果是2017/6/8select TRUNC(sysdate,'yyyy') from dual;--结果是2017/1/1select TRUNC(sysdate,'mm') from dual;--结果是2017/6/1select TRUNC(sysdate,'hh') from dual;--结果是2017/6/8 12:00:00select 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函数:数字转换为字符串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一次比较,相同则返回对应的值后的翻译值,如果没有找到则返回的是缺省值,当没有缺省值这返回nulldecode(条件,值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的学生数据
- JavaWeb开发知识总结(二)-(oracle_查询)
- JavaWeb开发知识总结(一)-(oracle_基础)
- JavaWeb开发知识总结(三)-(oracle_对象)
- JavaWeb开发知识总结(四)-(oracle_编程)
- JavaWeb开发知识总结(tomcat)
- JavaWeb开发知识总结(tomcat)
- JavaWeb开发知识总结(Listener)
- JavaWeb开发知识总结(filter)
- JavaWeb开发知识总结(网上商城项目总结)
- JavaWeb前端开发知识总结(HTML)
- JavaWeb前端开发知识总结(CSS)
- JavaWeb前端开发知识总结(javaScript)
- JavaWeb前端开发知识总结(jQuery)
- JavaWeb前端开发知识总结(mysql)
- JavaWeb数据库开发知识总结(jdbc基础)
- JavaWeb数据库开发知识总结(jdbc进阶)
- JavaWeb数据库开发知识总结(xml)
- JavaWeb前端开发知识总结(CSS)
- leetcode 3Sum(Java)
- python3 的 matplotlib绘图库的使用
- Android Studio中的Gradle文件基础
- Vim编辑器中如何编辑多个文件
- Java基础知识之继承
- JavaWeb开发知识总结(二)-(oracle_查询)
- BZOJ 2648: SJY摆棋子/BZOJ 2716: [Violet 3]天使玩偶 kdtree
- iOS开发-Umeng第三方登录-个人整理
- JavaWeb开发知识总结(三)-(oracle_对象)
- 50个必备的实用jQuery代码段+ 可以直接拿来用的15个jQuery代码片段
- 混合合作-竞争环境的多代理行动者鉴定
- JavaWeb开发知识总结(四)-(oracle_编程)
- 四级,傻逼了...
- MongoDB复制集深入(设计复制集,日志,写关注,读关注,标签)