查询总汇

查询基础

算术运算符

（+）（-）（*）（/） 值得注意的是：/ 在oracle中就相当于显示中的除法 5/2 = 2.5

!=,^=,<> 不等于

<  小于

>  大于

<= 小于等于

>= 大于等于

逻辑操作符

or and not

字符串连接操作符(||)

在Oracle中，字符串的连接用双竖线(||)表示。比如，在EMP表中，查询工资在2000元以上的姓名以及工作。

SELECT (ENAME || 'is a ' || JOB) AS "Employee Details"  FROM EMP  WHERE SAL>2000;

代码解析：

① Oracle中字符串可以用单引号，也可以用双引号，在别名中存在空格时，必须用双引号。在表名、列名时用双引号。

转换字符串为日期 to_date

例：

select empno,ename,hiredate

from emp

where hiredate >to_date('1980/01/01','yyyy/mm/dd') and hiredate；

聚合函数/分组函数

(聚合函数和分组函数都是描述的同一个概念，就是多行查询结果 聚合计算成一个结果返回，是查到多行返回一个结果，所以就涉及到按某个列进行分组的问题。)

聚合函数(distinct 对不同的值进行操作 /all 对所有值进行操作 )

avg 平均

select avg(sal) from emp;

select avg(distinct sal) from emp;

max 最大

select max(sal) from emp;

select max(distinct sal) from emp;

min 最小

select min(sal) from emp;

select min(distinct sal) from emp;

stddev 标准差

select stddev(sal) from emp;

select stddev(distinct sal) from emp;

variance 协方差

select variance(sal) from emp;

select variance(distinct sal) from emp;

sum 求和

select sum(sal) from emp;

select sum(distinct sal) from emp;

count 记录个数

select count(sal) from emp;

select count(distinct sal) from emp;

median 中值 没有 distinct 加持

nvl 空值处理函数

select id,nvl(name,'无名') name from test;

分组统计

select 列名 [as 别名],[聚合函数]

from 表名

where 查询条件表达式

group by 分组列名

having 分组查询表达式

order by 排序的列名[asc 升序 desc 降序] 默认是升序

例：

select CategoryName, count(*), AVG(Rating)

from BOOKSHELF

where Rating>1

group by CategoryName

having CategoryName like 'A%'

order by count(*) desc

分析：

1.基于where Rating>1 筛选出符合条件的行;

2.基于group by CategoryName 对筛选的结果进行分组;

3.为每个 CategoryName组计算count(*)

4.基于having CategoryName like 'A%' 留下符合条件的组

5.根据order by 的条件对剩下的行组进行排序,SQL中的count(*)也是分组函数

注意事项：

1.分组函数(max、min、avg、count、sum)只能出现在选择列表、having子句、order by子句中，不能出现在where子句和group by子句中

select empno, initcap(ename), avg(sal) from emp;--非法 不允许在 WHERE 子句中使用分组函数

2.如果在select语句中同时包含有group by,having,order by，顺序先group by,having,order by

select 列名1,.. From 表名 Where 条件 group by 分组列名 having 分组过滤 order by 排序列名

3.如果选择列表同时包含列、表达式和分组函数，则这些列、表达式都必须出现在group by中

select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal)<2000 这里deptno 就必须出现在group by中

group by 详解

Group by子句：

●  Group by子句将一个表分成许多小组，并对每一个小组返回一个计算值。

●  Group by expression：指按什么列进行分组

注意事项：

●  在select子句中，如果使用了分组函数，就不能对group by指定的列使用分组函数。

●  使用where子句可以预先排除某些记录

●  在Group by子句中必须有表中的列

●  Group by子句不能使用别名

●  可以通过Order by子句改变它的排序情况

基本用法

对于其基本的用法直接以实例的形式来展示。

1、统计各个部门的员工的工资的总和

[sql]

select deptno ,sum(sal) from emp group by deptno;

--升序排列

select deptno ,sum(sal) from emp group by deptno order by deptno asc;

2、统计各个部门各个职业的员工的工资的总和

[sql]

select deptno,job,sum(sal) from emp

group by(deptno,job) order by deptno;

Tips：第二个实例其实就是多列分组，先对部门进行分组，之后对职位进行分组。

DEPTNO JOB        SUM(SAL)

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

10 CLERK        2173.25

10 MANAGER        2450

10 PRESIDENT      6000

20 ANALYST        6000

20 CLERK          1900

20 MANAGER        2975

30 CLERK            950

30 MANAGER        2850

30 SALESMAN        5600

3、Having子句的使用

我们知道分组函数是不能卸载WHERE子句中的，但是有时候我们需要对分组进行限定

只有符合某个要求的分组才会被选择出来，那么就可以通过having子句来进行。具体的用法

同样以一个实例说明。

在2的基础上添加一个条件：工资总和必须在10000以上

[sql]

select deptno ,sum(sal) from emp group by deptno

having sum(sal)>10000;

DEPTNO  SUM(SAL)

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

20      10875

10  10623.25

Tips：思考一下Having子句与where子句的区别

两者都是对数据进行筛选，不同的是where是对原数据进行筛选而having则是对汇总后的结果进行一个筛选而已！

扩展用法

除了基本的用法外，group by还具有一些扩展的用法，不过大多数情况下基本的用法基本上

就可以满足我们的操作了。

1、使用rollup操作符

rollup，是group by子句的一种扩展，可以为每个分组返回小计记录以及对所有的分组返回

总计记录。下面看看其基本的用法吧。

⊙  向rollup传递一列

[sql]

select deptno,sum(sal) from emp where DEPTNO>=20 group by rollup(deptno) ;

DEPTNO  SUM(SAL)

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

20      10875

30      9400

20275 -对返回值进行一个总计

不过需要注意的是要对所有的记录进行一个总计的话，应该要一个聚合函数

不然根本没有实际的意义！

⊙  向rollup传递多列

Tips：需要注意的是rollup作用于多列的时候,之对第一列起作用！

[sql]

select deptno,job,sum(sal) from emp group by rollup(deptno,job);

select deptno,job,sum(sal) from emp group by rollup(job,deptno);

DEPTNO JOB        SUM(SAL)

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

10 CLERK        2173.25

20 CLERK          1900

30 CLERK            950

CLERK        5023.25

20 ANALYST        6000

ANALYST        6000

10 MANAGER        2450

20 MANAGER        2975

30 MANAGER        2850

MANAGER        8275

30 SALESMAN        5600

SALESMAN        5600

10 PRESIDENT      6000

PRESIDENT      6000

30898.25

可以看出的是除了最后又一个总计外，每个deptno都有一个小计，至于两个

列交换的结果原理是一样的，这里就不在演示了。

2、使用cube操作符

cube也是Group by子句的一种扩展，返回每一个列组合的小计记录，同时在头部加上

总计记录。（Oracle 11g）貌似和以前不一样？

⊙  向cube传递一列

[sql]

select deptno,sum(sal) from emp group by cube(deptno);

DEPTNO  SUM(SAL)

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

30898.25

10  10623.25

20      10875

30      9400

效果看起来和rollup没有什么两样嘛，只是总计的位置变了嘛，别慌看多列的情况！

⊙  向cube传递多列

[sql]

select deptno,job,sum(sal) from emp group by cube(deptno,job);

DEPTNO JOB        SUM(SAL)

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

30898.25

CLERK        5023.25

ANALYST        6000

MANAGER        8275

SALESMAN        5600

PRESIDENT      6000

10            10623.25

10 CLERK        2173.25

10 MANAGER        2450

10 PRESIDENT      6000

20                10875

20 CLERK          1900

20 ANALYST        6000

20 MANAGER        2975

30                9400

30 CLERK            950

30 MANAGER        2850

30 SALESMAN        5600

可以看出cube在每一个deptno都返回一个记录（部门的所有工资总数），并且就部门中的  每种工作的工资总数做了一个小计，而且就每种工作的工资做了一个小计(没有部门限制)，而且对所有的工资总数做了一个总计。

cube和rollup的区别小结：

通过上面的学习，我们可能有一种感觉，那就是直观上cube和rollup的组合情况不一样，

可以从这方面去区分，但是最好别从这方面思考问题。

ROLLUP (a,b,c...n)

group by (a,b,c)->(a,b)->(a)->全表group by  共分组n+1次

CUBE(a,b,c...n)

group by(a,b,c)->(a,b)->(a,c)->(a)->(b,c)-(b)-(c)->全表group by  共分组2n次方

至于其他的扩展用法，就先不展示了，之后会遇到的！Over！

模糊查询

like

在Where子句中，可以对datetime、char、varchar字段类型的列用Like子句配合通配符选取那些“很像...”的数据记录

通配符

%  零或者多个字符

_  单一任何字符（下划线）

/  特殊字符

[]  在某一范围内的字符，如[0-9]或者[aeth]

[^] 不在某范围内的字符，如[^0-9]或者[^aeth]

1.%：表示任意0个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。

比如 SELECT * FROM [user] WHERE u_name LIKE '%三%'

将会把u_name为“张三”，“张猫三”、“三脚猫”，“唐三藏”等等有“三”的记录全找出来。

另外，如果需要找出u_name中既有“三”又有“猫”的记录，请使用and条件

SELECT * FROM [user] WHERE u_name LIKE '%三%' AND u_name LIKE '%猫%'

若使用 SELECT * FROM [user] WHERE u_name LIKE '%三%猫%'

虽然能搜索出“三脚猫”，但不能搜索出符合条件的“张猫三”。

2._： 表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句：

比如 SELECT * FROM [user] WHERE u_name LIKE '_三_'

只找出“唐三藏”这样u_name为三个字且中间一个字是“三”的；

再比如 SELECT * FROM [user] WHERE u_name LIKE '三__';

只找出“三脚猫”这样name为三个字且第一个字是“三”的；

3.[ ]：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。

比如 SELECT * FROM [user] WHERE u_name LIKE '[张李王]三'

将找出“张三”、“李三”、“王三”（而不是“张李王三”）；

如 [ ] 内有一系列字符（01234、abcde之类的）则可略写为“0-4”、“a-e”

SELECT * FROM [user] WHERE u_name LIKE '老[1-9]'

将找出“老1”、“老2”、……、“老9”；

4.[^ ] ：表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。

比如 SELECT * FROM [user] WHERE u_name LIKE '[^张李王]三'

将找出不姓“张”、“李”、“王”的“赵三”、“孙三”等；

SELECT * FROM [user] WHERE u_name LIKE '老[^1-4]';

将排除“老1”到“老4”，寻找“老5”、“老6”、……

5.查询内容包含通配符时

由于通配符的缘故，导致我们查询特殊字符“%”、“_”、“[”的语句无法正常实现，而把特殊字符用“[ ]”括起便可正常查询。据此我们写出以下函数：

function sqlencode(str)

str=replace(str,"[","[[]") '此句一定要在最前

str=replace(str,"_","[_]")

str=replace(str,"%","[%]")

sqlencode=str

end function

空值查询

SELECT ename As 姓名，comm AS  奖金 FROM emp WHERE comm IS NULL

between and 条件

SELECT *  FROM teacher

WHERE birthday  between '1968/1/1'  and  '1970/1/1'

in运算符用法：把某一字段中内容与所列出的查询内容列表匹配的记录查询出来

集合运算：就是将两个或者多个结果集组合成为一个结果集。

INTERSECT(交集)，返回两个查询共有的记录

UNION ALL(并集)，返回各个查询的所有记录，包括重复的记录

UNION(并集)，返回各个查询的所有记录，不包括重复的记录

MINUS(补集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。

例：

SELECT deptno FROM  dept

MINUS

SELECT deptno FROM emp;

多表连接

inner join / join 内连接 on 条件

自然连接

natural join

自然连接是在两张表中寻找那些数据类型和列名都相同的字段，然后自动地将他们连接起来，并返回所有符合条件按的结果。(还会去相等行)

外连接

outer join(左外连接left outer join/ left join--左边为主表，右外连接right outer join/ right join--右边为主表，全外连接)

outer join则会返回每个满足第一个（顶端）输入与第二个（底端）输入的联接的行。它还返回任何在第二个输入中没有匹配行的第一个输入中的行。外连接分为三种： 左外连接，右外连接，全外连接。 对应SQL：LEFT/RIGHT/FULL OUTER JOIN。 通常我们省略outer 这个关键字。 写成：LEFT/RIGHT/FULL JOIN。

左右链接

在左外连接和右外连接时都会以一张表为基表，该表的内容会全部显示，然后加上两张表匹配的内容。 如果基表的数据在另一张表没有记录。 那么在相关联的结果集行中列显示为空值（NULL）。

用（+）来实现， 这个+号可以这样来理解： + 表示补充，即哪个表有加号，这个表就是匹配表。另一个就是主表;

select * from dave a right join bl b on a.id = b.id;

select * from dave a,bl b where a.id(+)=b.id;

2.         ID NAME              ID NAME

3. ---------- ---------- ---------- ----------

4.         1 dave                1 dave

5.         2 bl                  2 bl

6.         1 bl                  1 dave

7.         2 dave                2 bl

8.         3 dba                3 big bird

9.         4 sf-express          4 exc

10.                           9 怀宁

全外链接 full outer join/ full join

左表和右表都不做限制，所有的记录都显示，两表不足的地方用null 填充。 全外连接不支持（+）这种写法。

自连接

自连接(self join)是SQL语句中经常要用的连接方式，使用自连接可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。

每一个员工自己的名字和经理的名字都找出来

select work.ename worker,mgr.ename manger

from emp work,emp mgr

where work.mgr = mgr.empno(+)

order by work.ename;

子查询

子查询是指子查询在主查询前执行一次,主查询使用子查询的结果

注意事项：

在查询时基于未知时应考虑使用子查询

子查询必须包含在括号内

将子查询放在比较运算符的右侧,以增强可读性.

除非进行Top-N分析,否则不要再子查询中使用Order by子句

对单行子查询使用单行运算符

对多行子查询使用多行运算符

单行子查询

单行子查询

单行子查询只返回一行记录

对单行子查询可使用单行记录比较运算符

=-----------------等于

>-----------------大于

>=----------------大于等于

<-----------------小于

<=----------------小于等于

<>----------------不等于

select * from emp

where sal>(select sal

from emp where empno=7000);

子查询空值/多值问题

如果子查询未返回任何行,则主查询页不会返回任何结果

如果子查询返回单行结果,则为单行子查询,可以在主查询中对其使用相应的单行记录比较运算符

如果子查询返回多行结果,则为多行子查询,此时不允许对其使用单行记录比较运算符

多行子查询

多行子查询返回多行记录

对多行子查询只能使用多行记录比较运算符

in---------------等于列表中的任何一个

any--------------和子查询返回的任意一个值比较

all--------------和子查询返回的所有值比较

some-------------即一些。和any的用法基本相同。用any的地方都可以用some代替。不过some大多用在=操作中。表示等于所选集合中的任何一个。当然any也可以用于=操作中，效果和some相同

select * from emp

where sal>any(select avg(sal) from emp group by deptno);

select * from emp

where sal>all(select avg(sal) from emp group by deptno);

select * from emp

where job in(select job from emp where ename='martin' or ename='ssss');

select * from emp t

where t.sal=some(select sal from hhgy.emp where deptno=30);

TopN查询rownum高级查询

查询出3行到7行的员工名

select *

from (select ename,rownum rn from emp )

where rn > 3 and rn < 7;

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 216,125评论 6赞 498
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 92,293评论 3赞 392
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 162,054评论 0赞 351
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 58,077评论 1赞 291
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 67,096评论 6赞 388
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 51,062评论 1赞 295
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,988评论 3赞 417
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 38,817评论 0赞 273
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,266评论 1赞 310
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,486评论 2赞 331
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,646评论 1赞 347
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,375评论 5赞 342
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,974评论 3赞 325
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,621评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,796评论 1赞 268
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 47,642评论 2赞 368
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,538评论 2赞 352

推荐阅读更多精彩内容

  • Oracle之查询详解
    查询是数据的一个重要操作。用户发送查询请求，经编译软件变异成二进制文件供服务器查询，后返回查询结果集给用户，查询会...
    产品小正阅读 1,385评论 0赞 2
  • oracle高级查询
    幕课oracle学习笔记 --！！！scott用户 --一.分组查询 --1.常用的分组函数：AVG(平均数)，S...
    xiaoxiao苏阅读 1,264评论 0赞 5
  • Oracle数据库学习一
    1.简介 数据存储有哪些方式？电子表格，纸质文件，数据库。 那么究竟什么是关系型数据库？ 目前对数据库的分类主要是...
    乔震阅读 1,712评论 0赞 2
  • Oracle学习笔记
    SQL ==SQLPLUS== DML(Data Manipulation Language,数据操作语言)---...
    蝌蚪1573阅读 590评论 0赞 4
  • 春晖恩
    【1】 你记得自己的生辰吗？记得。 你记得父亲的生辰吗？不记得。 你记得母亲的生辰吗？不记...
    于悬阅读 551评论 5赞 1