文档章节

oracle学习笔记——查询篇

红焖鲤鱼
 红焖鲤鱼
发布于 2015/04/12 16:28
字数 4130
阅读 205
收藏 7

一、单表查询

        1、基本SELECT语句

                1)查看表结构用desc

                    desc dual;

                2)、空表dual,最常用的空表

                    select2 * 4from dual;

                    selectsysdatefrom dual;

                3)、在使用中我们可以将取到的字段起别名

                  Selectsysdate日期from dual;

                  双引号能保持格式

                  如:selectsysdate “toDay ” from dual;

                4)、查看表所属用户

                select owner from dba_tables where table_name='表名';

                注:system.tb_booktype tb_booktype属于system用户

                为了后续我们使用,我们现在创建图书类型表—tb_booktype”图书信息表—        tb_bookinfo”

                 

        2、比较运算符

                1)、 =<>>=<=<> !=

                  在图书类别表查看到ID等于2的数据

                  selectfrom  tb_booktype t where t.id='2'

                  在图书类别表查看到ID大于2的数据

                  selectfrom  tb_booktype t where t.id >'2'

                  在图书类别表查看到ID小于2的的数据

                  selectfrom  tb_booktype t where t.id<'2'

                 

                  在图书类别表查看到ID小于等于2的数据

                  selectfrom  tb_booktype t where t.id<='2'

                  在图书类别表查看到ID大于等于2的数据

                  selectfrom  tb_booktype t where t.id>='2'

                  在图书类别表查看到ID不等于等于2的数据

                    selectfrom  tb_booktype t where t.id<>'2'

                  selectfrom  tb_booktype t where t.id!='2'

                2)、innot in

                in(是满足一个字段的多个值)

                在图书类别表查看类别名称等于自然和人文的数据

                selectfrom  tb_booktype t where t .typename in ('自然','人文')

                在图书类别表查看类别名称等于自然和人文的数据且;并且类别ID等于12

                selectfrom  tb_booktype t where t .typename in ('自然','人文')and t.id notin ('1','2')

                   

                3)、like not like

                like模糊

                在图书类别表查看类别名称包含的数据

                selectfrom  tb_booktype t where t.typename  like'%'

                注意:百分号的位置不同查出的数据也不相同!

                selectfrom  tb_booktype t where t.typename  like'%'

                selectfrom  tb_booktype t where t.typename  like'%%'

                4)、is nullis not null

                在图书类别表查看类别名称为空的数据

                selectfrom  tb_booktype t where t.typename  isnull

        3、逻辑运算符

                1and

                and(就是和的意思,必须同时满足条件)

                在图书类别表查看到ID大于2的且小于45的数据

                selectfrom  tb_booktype t where t.id>'2'and t.id<'45'

                2)or

                or(是或者的意思,两个或者多个条件满足一个就可以)

                在图书类别表查看到ID大于2的或类别名称等于自然的数据

                selectfrom  tb_booktype t where t.id>'2'or t.typename='自然'

                4order by 子句

                orderbyasc(升序)desc(降序)

                在图书类别查看数据,按降序排序

                selectfrom  tb_booktype t orderby t.id desc

 

二、多表查询

        1、子查询

                    求部门分组后工资最高的员工信息

                    select emp.ename, emp.sal, emp.deptno from emp, (selectmax(sal) max_sal, deptno from emp groupby deptno) t where emp.sal = t.max_sal and emp.deptno = t.deptno;

                    求部门平均工资等级

                    select s.grade, t.deptno, t.avg_sal from scott.salgrade s, (select deptno, avg(sal) avg_sal from emp groupby deptno) t where t.avg_sal > s.losal and t.avg_sal < s.hisal;between

        2、连接查询

                innerjoin(等值连接只返回两个表中联结字段相等的行

                leftjoin(左联接返回包括左表中的所有记录和右表中联结字段相等的记录

                rightjoin(右联接返回包括右表中的所有记录和左表中联结字段相等的记录

                fulljoin()取到的除了左右两边都匹配上的记录数,对于左边表与右边表没有匹配的,用null补上作为右边表匹配的数据;右边表与左边表没有匹配的,用null补上作为左边表匹配的数据。总记录数=左边未匹配记录数+右边未匹配记录数+左右都匹配上的记录数

                查询出图书类型,和图书名称

                select t.typename,tb.bookname from tb_booktype t, tb_bookinfo tb where t.id = tb.typeid

                1inner join(等值连接)

                select t.typename,tb.bookname from tb_booktype t

                innerjoin tb_bookinfo tb on t.id=tb.typeid

                2left join(左联接)

                select t.typename,tb.bookname from tb_booktype t leftjoin tb_bookinfo tb on t.id=tb.typeid

                3right join(右联接)

                select t.typename,tb.bookname from tb_booktype t rightjoin tb_bookinfo tb on t.id=tb.typeid

                4full join

                select t.typename,tb.bookname from tb_booktype t fulljoin tb_bookinfo tb  on t.id=tb.typeid

        3、交集、并集、割集查询

                1union

                --并集:不带重复数据

                select t.id from tb_booktype t

                union

                select tb.typeid from tb_bookinfo tb;   

                2union all

                --并集:带重复数据

                select t.id from tb_booktype t

                unionall

                select tb.typeid from tb_bookinfo tb;

                3minus   

                    --割集,显示不同部分(指令是运用在两个 SQL 语句上。它先找出第一个 SQL 语句所产生的结果,然后看这些结果有没有在第二个 SQL 语句的结果中。如果有的话,那这一笔资料就被去除,而不会在最后的结果中出现。如果第二个 SQL 语句所产生的结果并没有存在于第一个 SQL 语句所产生的结果内,那这笔资料就被抛弃)

                select t.id from tb_booktype t

                minus

                select tb.typeid from tb_bookinfo tb;

                4intersect

                --交集。

                select t.id from tb_booktype t

                intersect

                select tb.typeid from tb_bookinfo tb;

                 

        4、条件表达式——decode 函数和CASE表达式

                1)、decode 函数用法

                含义解释:

                decode(条件,1,返回值1,2,返回值2,...n,返回值n,缺省值)

                该函数的含义如下:

                IF条件=THEN

                RETURN(翻译值1)

                ELSIF条件=THEN

                RETURN(翻译值2)

                ......

                ELSIF条件=THEN

                RETURN(翻译值n)

                ELSE

                RETURN(缺省值)

                ENDIF

                decode(字段或字段的运算,值1,值2,值3

                 这个函数运行的结果是,当字段或字段的运算的值等于值1时,该函数返回值2,否则返回值3

                当然值1,值2,值3也可以是表达式,这个函数使得某些sql语句简单了许多

                例子:在图书类型表中查询,当图书类型等于人文的时候那么它属于百科类,期他图说类型属于其他类

                select decode(t.typename,'人文','百科类','其他类') , t.typename name

                from tb_booktype t

                2)、select case when的一些用法

                概述:

                sql语句中的case语句与高级语言中的switch语句,是标准sql的语法,适用于一个条件判断有多种值的情况下分别执行不同的操作。

                首先,让我们看一下CASE的语法。在一般的SELECT中,其语法格式如下:

                SELECT <myColumnSpec> =

                CASE <单值表达式>

                       when <表达式值then <SQL语句或者返回值>

                       when <表达式值then <SQL语句或者返回值>

                       ...

                       when <表达式值then <SQL语句或者返回值>

                END

                应用CASE语句可以让SQL变得简洁高效,从而大大提高了执行效率。而且,CASE的使用一般不会引起性能(相比没有用CASE的语句)低下,反而增加了操作的灵活性

                有一张表,里面有3个字段:语文,数学,英语。其中有3条记录分别表示语文70分,数学80分,英语58分,请用一条sql语句查询出这三条记录并按以下条件显示出来(并写出您的思路):

                   大于或等于80表示优秀,大于或等于60表示及格,小于60分表示不及格。

                       显示格式:

                       语文              数学                英语

                       及格              优秀                不及格   

                ------------------------------------------

                select

                (casewhen语文>=80then'优秀'

                        when语文>=60then'及格'

                else'不及格'as语文,

                (casewhen数学>=80then'优秀'

                        when数学>=60then'及格'

                else'不及格'as数学,

                (casewhen英语>=80then'优秀'

                        when英语>=60then'及格'

                else'不及格'as英语,

                fromtable

三、函数

            1、大小写控制函数lowerupper

                    select lower(‘abcABC’) from dual;

                    select upper(‘abcABC’) from dual;

                    substr(target, startIndex, length)

                    select substr(‘abcABC’, 13from dual;

            2、数字函数truncroundsubstr

                    1TRUNC

                    语法:  TRUNC(x[,y])

                    功能:  计算截尾到y位小数的x. y缺省为0,结果变为一个整数值.如果y是一个负数,那么就截尾到小数点左边对应的位上.TRUNC()函数截取时不进行四舍五入,一般用于日期的截取上

                    select trunc(123.458from dual --123

                    select trunc(123.458,0from dual --123

                    select trunc(123.458,1from dual --123.4

                    select trunc(123.458,-1from dual --120

                    select trunc(123.458,-4from dual --0

                    select trunc(123.458,4from dual  --123.458

                    select trunc(123from dual  --123

                    select trunc(123,1from dual --123

                    select trunc(123,-1from dual --120

                    用在日期类型上,就是截取到日或时间

                    SELECT TRUNC(SYSDATEFROM DUAL;

                    默认是截取系统日期到日,得到2012-12-19

                    比如值为"2012-12-19 14:30:50"的日期变量

                    TRUNC(SYSDATE)得到2012-12-19 , TRUNC(SYSDATE,'DD')一样

                    TRUNC(SYSDATE,'YYYY'),得到2012-1-1

                    TRUNC(SYSDATE,'MM'),得到2012-12-1

                    TRUNC(SYSDATE,'DD'),得到2012-12-19

                    TRUNC(SYSDATE,'HH')HH24,得到2012-12-1914:00:00

                    TRUNC(SYSDATE,'MI'),得到2012-12-1914:30:00

                    TRUNC(SYSDATE,'SS'),是会报错的,直接用SYSDATE不用TRUNC

                    另外说明一下,截取后得到的仍为date数据类型

                    2Round 函数

                    (四舍五入)

                    描述 : 传回一个数值,该数值是按照指定的小数位元数进行四舍五入运算的结果。

                    SELECT ROUND( number, [ decimal_places ] ) FROM DUAL

                    参数:

                    number : 欲处理之数值

                    decimal_places : 四舍五入 , 小数取几位 ( 预设为0 )

                    例:select round(123.4560from dual;          得到:123

                    3Substr

                    取得字符串中指定起始位置和长度的字符串,  

                     格式:substr( string, start_position, [ length ] )

                     参数:start_position---正数从左边开始,负数从右边开始

                           length -----取得的位数

                     : substr('This is a test'62)     得到'is'

                         substr('This is a test'6)     得到'is a test'

                         substr('TechOnTheNet', -33)     得到'Net'

                         substr('TechOnTheNet', -63)     得到'The'

                     例:select substr('Thisisatest', -42valuefrom dual

                         --得到当前的那年那月

                         select substr(sysdate,1,6valuefrom dual

            3、通用函数——nul

                    nvl可以将某个字段的空值转换成指定的值

                    nvl(字段名,0),就是当你选出来的时候,这个字段虽然为空,但是显示的是0,当然这个0也可以换成其他东西,如:123……

                    在图书类别表查看数据类别名称为空的补零

                    select nvl(t.typename,0),t.id from tb_booktype t

            4、组函数—minmaxavgsumcount

                    1)max

                    在图书信息表查看最高的价格

                    selectmax(tb.price) from tb_bookinfo tb

                    2)min

                      在图书信息表查看最低的价格

                      selectmin(tb.price) from tb_bookinfo tb

                    3)avg

                      在图书信息表查看平均的价格

                         selectavg(tb.price) from tb_bookinfo tb

                    4)sum

                      在图书信息表算出表中所有价格的和

                         selectsum(tb.price) from tb_bookinfo tb

                    5)count

                      在图书信息表中价格不为空的数据条数

                         selectcount (tb.price) from tb_bookinfo tb

                      在图书信息表中价格不相同的数据条数

                         selectcount(distinct tb.price) from tb_bookinfo tb

                    6)distinct 关键字

                    去掉重复数据distinct

                        在图书类别表中查出有几类图书,但是表中有重复如何查询呢?,

                    这里我们用到了 distinct去掉重复数据

                    selectdistinct(t.typename) from tb_booktype t

            拓展:

            Oracle比较一列的最大值或者最小值,我们会用MAXMIN函数,但是对于比较一行的最大值或最小值呢?是不是日常用的少,很多人都不知道有ORACLE也有内置函数实现这个功能:COALESCE / GREATEST /LEAST

             1COALESCE返回该表达式列表的第一个非空value

                格式:COALESCE(value1, value2, value3, ...)

                含义:返回value列表第一个非空的值。

                           value列表必须是相同类型,也可以是一个表的同一行、不同列的值进行比较。

                EXAMPLE: selectcoalesce (1null2 ) from dual ; -- 返回1

                                 selectcoalesce ( null21 ) from dual ; -- 返回2

                                 selectcoalesce (t.empno, t.mgr ) from  scott.emp t ; -- 效果类似 NVL( t.empno, t.mgr )

            2. GREATEST 返回值列表中最大值

                格式: GREATEST(value1, value2, value3, ...)

                含义:返回value列表最大的值。

                           value列表必须是相同类型,也可以是一个表的同一行、不同列的值进行比较。

                           value值列表中有一个为NULL,则返回NULL值。

                EXAMPLE: select greatest (132 ) from dual ; -- 返回3

                          select greatest ( 'A''B''C' ) from dual ; -- 返回C

                          select greatest (null'B''C' ) from dual ; -- 返回null

              select greatest (t.empno, t.mgr ) from  scott.emp t ; -- 返回empnomgr 较大值

            3. LEAST 返回值列表中最小值

                格式: LEAST(value1, value2, value3, ...)

                含义:返回value列表最小的值。

                           value列表必须是相同类型,也可以是一个表的同一行、不同列的值进行比较。

                           value值列表中有一个为NULL,则返回NULL值。

                EXAMPLE: select least (132 ) from dual ; -- 返回1

                           select least ( 'A''B''C' ) from dual ; -- 返回A

                           select least (null'B''C' ) from dual ; -- 返回null

                           select least (t.empno, t.mgr ) from  scott.emp t ; -- 返回empnomgr 较小值

            5、数据分组

                    1)group by 分组

                        查询图书信息表中的图书数量图书类型id”进行分组;

                        select tb.typeid,count(tb.bookname) from tb_bookinfo tb

                    groupby tb.typeid;

                     

                    2)having 对分组数据进行过滤

                      having用法

                    groupby后面是要跟着的select中所有不是聚合函数的字段。

                    注释:聚合函数:聚合函数对一组值执行计算并返回单一的值。聚合函数忽略空值。聚合函数经常与SELECT语句的GROUPBY子句一同使用

                    1:  selectcount(*) from emp;        

                    //只是查询总总数 emp这张表里一共有多少条记录所以不用group by

                    2:  selectcount(*) , deptno from emp groupby deptno; 

                    // 根据deptno 分组,查到的数据就是  列出不同部门记录总数 

                    selectcount(*) ,  deptno ,  comm from emp groupby deptno , comm;

                     // 根据deptno  comm 分组  以此类推

                     groupby后面是要跟着的select中所有不是聚合函数的字段   否则会报错。

                    having相当于wherewhere的唯一区别是当查询语句中有聚合函数的时候就不能用where了只能用having

                    求图书平均工资大于30的数据:

                        select * from(

                    selectavg(tb.price) price,tb.typeid

                    from tb_bookinfo tb

                    groupby tb.typeid

                    )

                    where price>40

                    selectavg(tb.price) price,tb.typeid

                    from tb_bookinfo tb

                    groupby tb.typeid

                    havingavg(tb.price)>40

                     

                    6、日期型函数

                    常用日期数据格式(该段为摘抄)

                    YYYYYY 年的最后一位,两位或三位Select to_char(sysdate,’YYY’) from dual002表示2002

                    SYEARYEAR SYEAR使公元前的年份前加一负号Select to_char(sysdate,’SYEAR’) from dual -1112表示公元前111 2

                    季度,13月为第一季度Select to_char(sysdate,’Q’) from dual2表示第二季度

                    MM 月份数Select to_char(sysdate,’MM’) from dual12表示12

                    RM 月份的罗马表示Select to_char(sysdate,’RM’) from dual IV表示4

                    Month9个字符长度表示的月份名Select to_char(sysdate,’Month’) from dual May后跟6个空格表示5

                    WW 当年第几周Select to_char(sysdate,’WW’) from dual24表示2002613日为第24

                    本月第几周Select to_char(sysdate,’W’) from dual2002101日为第1

                    DDD 当年第几11日为00121日为032 Select to_char(sysdate,’DDD’) from dual363200229日为第363

                    DD 当月第几天Select to_char(sysdate,’DD’) from dual04104日为第4

                    周内第几天Select to_char(sysdate,’D’) from dual52002314日为星期一

                    DY 周内第几天缩写Select to_char(sysdate,’DY’) from dual SUN 2002324日为星期天

                    HHHH12 12进制小时数Select to_char(sysdate,’HH’) from dual02午夜2点过8分为02

                    HH24 24小时制Select to_char(sysdate,’HH24’) from dual14下午208分为14

                    MI 分钟数(059Select to_char(sysdate,’MI’) from dual17下午417

                    SS 秒数(059Select to_char(sysdate,’SS’) from dual2211322

                    提示注意不要将MM格式用于分钟(分钟应该使用MI)MM是用于月份的格式,将它用于分钟也能工作,但结果是错误的。

                    1)Sysdate 当前日期和时间

                    SQLSelectsysdatefrom dual;

                    2)Last_day 本月最后一天

                    SQLSelect last_day(sysdatefrom dual;

                    3)Add_months(d,n) 当前日期d后推n个月

                    用于从一个日期值增加或减少一些月份

                    date_value:=add_months(date_value,number_of_months)

                    函数可以得到某一时间之前或之后n个月的时间

                    select add_months(sysdate,-6from dual;

                    该查询的结果是当前时间半年前的时间

                    select add_months(sysdate,6from dual;

                    该查询的结果是当前时间半年后的时间

                    SQLSelect add_months(sysdate,2from dual;

                    4)Months_between(f,s) 日期fs间相差月数

                    SQLselect months_between(sysdate,to_date('2005-11-12','yyyy-mm-dd'))from dual;

                    5)NEXT_DAY(date,char)

                    date参数为日期型,

                    char:1~7Monday/Mon~Sunday/

                    指定时间的下一个星期几(由char指定)所在的日期,

                    char也可用17替代,1表示星期日,2代表星期一。。。。

                    还可以是星期一、星期二。。。星期日

                    Sql代码 

                    select next_day(sysdate,'星期日'from dual; 

                    返回由"char "命名的,在变量"date "指定的日期之后的第一个工作日的日期。参数"char "必须为该星期中的某一天。

                    SQLSELECT next_day(to_date('20050620','YYYYMMDD'),1FROM dual;

         

        

© 著作权归作者所有

共有 人打赏支持
红焖鲤鱼
粉丝 113
博文 40
码字总数 29610
作品 0
浦东
QA/测试工程师
Spring.NET学习笔记——目录(原)

目录 前言 Spring.NET学习笔记——前言 第一阶段:控制反转与依赖注入IoC&DI Spring.NET学习笔记1——控制反转(基础篇) Level 200 Spring.NET学习笔记2——环境搭建(基础篇) Level 200 Sprin...

长平狐
2012/06/11
887
1
Mini 容器学习笔记1——环境搭建(基础篇)

一. 环境下载 到Mini 容器的官方网站下载NLite框架的二进制文件,下载并解压后就可以了。 我们使用NLite框架需要用到下面的文件: NLite.dll(必要) 二. 建立NLite应用程序 新建一个控制台应用...

netcasewqs
2011/08/26
0
0
MyBatis多对多关联查询示例——MyBatis学习笔记之十八

MyBatis系列的上一篇博客发表时,笑笑还没有出生。转眼间八个月过去了,他已经是个大宝宝了。这么长时间未更新MyBatis系列的博客,想来真是罪过。不过有了宝宝之后,的确会分散自己很大一部分...

NashMaster2011
2014/04/23
0
0
MyBatis多对多保存示例——MyBatis学习笔记之十七

前几天有网友问到MyBatis多对多的问题,不过愧对网友厚爱的是,最近一直忙,直到现在才有时间处理此事。今天就先写一个多对多保存的示例,算是对这位网友的初步回应,以后会有更多相关的博文...

NashMaster2011
2013/08/11
0
0
[推荐推荐][提供下载]ORACLE SQL:经典查询练手系列文章收尾(目录篇)

[推荐推荐][提供下载]ORACLE SQL: 经典查询练手系列文章收尾(目录篇) ——通过知识共享树立个人品牌。 通过近一个月的努力,《经典查询练手系列》也快告一段落,但并不代表结束,以后还会增...

yonghu86
06/26
0
0

没有更多内容

加载失败,请刷新页面

加载更多

权限框架Shiro学习之表结构设计

权限框架Shiro学习之表结构设计 Shiro是一款优秀的开源安全框架,学习Shiro大家可以参考张开涛老师的博客:跟我学Shiro,当然也可参考我之前的笔记:Shiro实现身份认证、Shiro实现授权。 在学...

TyCoding
15分钟前
0
0
find命令和文件名后缀

9月18日任务 2.23/2.24/2.25 find命令 2.26 文件名后缀 which which 搜索可执行文件,必须在PATH环境变量目录中!!否则无法搜到! [root@centos7 ~]# which lsalias ls='ls --color=auto'...

robertt15
18分钟前
0
0
阿里Java程序员必备的Intellij IDEA 插件

善用Intellij插件可大幅提升我们的效率,以下是我用过不错的Intellij插件,分享给大家希望能帮到大家。 1. .ignore 生成各种ignore文件,一键创建git ignore文件的模板,免得自己去写 2. lom...

我是你大哥
28分钟前
0
0
为什么Java大神,都在看Spring Boot和Spring Cloud的书?

如果你是一名Java开发人员,并且最近正打算学习Spring Boot和Spring Cloud框架并寻找一些关于它们的最好的书籍,那么,你今天就来对地方了。 本文,我们将讨论一些学习Spring Boot和Spring ...

Java小铺
46分钟前
11
0
springboot logback日志配置

springboot 如果不使用外部tomcat的话,日志是需要自己配置的,不然的话就只有控制台的日志,但是日志又是我们在项目上了生产环境,出问题时,检查问题的唯一途径,所以我们要配置详细的日志...

曾大大胖
46分钟前
2
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部