菜单

Oracle之查询详细解释,SQL语句学习_数据库别的_脚本之家

2020年2月13日 - 新闻中心
Oracle之查询详细解释,SQL语句学习_数据库别的_脚本之家

好了,其他的不说现在就开始:

查询是数据的一个重要操作。用户发送查询请求,经编译软件变异成二进制文件供服务器查询,后返回查询结果集给用户,查询会产生一个虚拟表,看到的是表形式显示的结果,但结果并不真正的存储,每次执行查询只是从数据表中提取数据,并按照表的形式显示出来。

select
子句主要决定了从表中取出的列名,列数以及列的显示顺序等信息,”*”表示查询所有的列,有关select的用法应该结合其它子句的用法。

SELECT <列名>

1.from 子句:

FGROM <表名>

①from子句用于指定被查询的表,试图或快照。②如果指定多个实体,用逗号讲它们分割。为了查询方便,特别是进行自连接查询时,可以给表起别名。(这里我要说很多刚开始使用sql的朋友开始的时候总觉得这个很简单,没有什么可学的,可是到后来在做一些复杂的sql的时候总是想不起来这一点导致其实不难的sql语句无从下手)。③如果被查询的实体不在当前的模式中,则必须用模式名来限定该对象。select
*from
admin.emp④若查询远程数据库中的实体,必须使用数据库链路,如果查询分区表中某个分区的数据,则必须使用关键字Partition来指定分区名。select
*from emp@cbf107select *from emp partition

[WHERE <查询条件表达式>]

2.where 子句

[GROUP BY <分组表达式>]

where 子句用于限定处理的行数,选择满足条件的行,where
子句中的任何逻辑条件都使用比较运算符,满足where子句中逻辑条件的数据被返回或者被处理,在where子句中可以使用列名或表达式。

[HAVING <分组查询表达式>]

select ename from emp where deptno=10

[ORDER BY <排序的列名> [ASC或DESC]]

①如果列类型为字符型,where
ename=’smith’;②列类型为数字型deptno=20;③如果列值取其他列值,可用如下形式表示where
emp.depno=dept.deptno④in 与 not in
择列的值与值列表中某一个值相符的相关行信息

group by

group by 用于对查询的结果分组统计,通过对group
by后面的名字进行分组后输出结果。

select deptno,count(ename) from emp group by deptno;

澳门太阳集团,统计公司每个部门的员工人数

select deptno,avg(sal),max(sal) from emp group by deptno;

显示每个部门的平均工资和最高工资

select deptno,gender,count(ename) from emp group by deptno,gender;

按照性别统计各个部门人数//需要手动添加一个gender列,并添加属性

澳门太阳集团 1

group by后面还可以跟多列表示多列分组,在多列分组时放前面的优先分组。

group by 列名,列名

select count(*) as 人数,deptno as 部门号,job 工作 from emp group by
deptno,job order by deptno;

查询表EMP中有那些雇员和分析员select name,job from emp where job in
;查询表EMP中有那些人不是雇员和分析员select name,job from emp where job
not in ;

澳门太阳集团 2

有那些雇员的工资在/不在2000~3000之间select ename,job,sal from emp where
sal between 2000 and 3000;select ename,job,sal from emp where sal not
between 2000 and 3000

having

having 子句用于限制分组显示结果,其只能和group
by一起连用。在where中没有办法直接使用聚合函数,即sum
avg等无法使用,所以引用了having,在having中可以使用这些函数。

select deptno,avg(sal) from emp group by deptno having
avg(sal)<2000;

显示工资低于2000的部门号和它的平均工资

select deptno as 部门号,count(*) as 人数 from emp group by deptno
having count(*)>4;

显示部门人数大于4的部门

澳门太阳集团 3

select ename,oeptno from emp where ename like ‘s%’;”%”
匹配0个或n个长度的字符串,而”_”仅匹配一个字符。假如查询条件中本身含有”_”或”%”,为了与模式匹配符”_”和”%”区分,必须在like运算符中加ESCAPE”%子句.查询雇员名以A_开始的行select
*from emp where ename like ‘a_%’ ESCAPE ”;

order by

order by
表示排序,后跟列名和排序方式。如果什么都不加默认为升序。ASC表示升序,DESC表示降序。

select empno,ename,sal from emp order by sal;

select empno,ename,sal from emp order by sal asc;

select empno,ename,sal from emp order by sal desc;

在Oracle中还可以设置多列排序

order by 列名1 升降,列名2 升降;

前面的为主要排序,后面的为次一级排序。

澳门太阳集团 4

澳门太阳集团 5

注:碰到自己与自己比较的情况下,不能用having,可以创建一个新列。

select depton,count(deptno) from emp group by deptno having
sal>avg(sal);

select emp.deptno,count(*) from emp,(select deptno,avg(sal) avgsal
from emp group by deptno) a where emp.deptno=a.deptno and
emp.sal>a.avgsal group by emp.deptno;

查询每个部门中工资高于该部门平均工资的员工人数

注:如果select语句同时包含group by,having,order by,按group
by,having,order by排序

分组和聚合一起使用,目的是为了统计信息。

where是为了from服务的,只能跟真实的字段,用来筛选from子句中指定的操作所产生的行

group by 用来分组where子句的输出

having 用来从分组的结果中筛选行

order by用来对筛选的结果进行排序

is null 和 is not
null空值不等同于0,0是一个数字,而空值表示未知的,不存在的或不可用的数据.它不能像0那样进行算术运算,null用于查询列值为空值或非空的信息。在Oracle的RDBMS中,null不占空间,判断某一列值是否为空,不能用”=”或”!=”运算符号,而要用is
null或is not null。

复杂查询

(1)分组函数:max min avg sum count

max表示该列的最大值,min表示该列的最小值,avg表示该列的平均值,sum表示该列的和,count表示该列的行数。

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

select max(sal),min(sal) from emp;

如何显示所有员工最高工资和最低工资

select avg(sal),sum(sal) from emp;

显示所有员工的平均工资和工资总和

select count(ename) from emp; //count用于计算行

计算共有多少员工

select ename,job from emp where sal=(select max(sal) from emp);

显示工资最高的员工的名字,工作岗位(max等没办法直接跟在where后面)

select ename,sal from emp where sale>(select avg(sal) from emp);

显示工资高于平均员工信息

澳门太阳集团 6

(2)多表查询

多表查询是指两个和两个以上的表或者是视图的查询,在实际应用中,当查询单个表不能满足需求时,一般使用多表查询。如:显示sales部门位置和其员工的姓名,这种情况下需要使用到(dept表和emp表)。

多表查询的连接一般可以分为:内连接、左外连接、右外连接、全连接。

注:在使用多表查询的时候每个表可以设置别名,如果表指定了别名,那么语句中所有语句必须使用别名,而不能再使用实际表名。且在写属性的时候如果属性为其中一个表特有的属性则不需要写别名,如果是两个表都有则必须指定是哪一个表的哪个属性格式为:表名.属性名。

select 列名 from 表1 别名,表2 别名…

select d.loc,e.ename,e.job from emp e,dept d where e.deptno=d.deptno

注:e是emp的别名,d是dept的别名。

但如果对表进行了操作则需要设置别名,如:查询每个部门中工资高于该部门平均工资的员工人数。在其中有一个avg表,这个表必须设置别名(提醒:如果仅有一个被修改的表,则可以不设置别名,但如果有多个表则必须设置别名)。

select emp.deptno,count(*) from emp,(select deptno,avg(sal) avgsal
from emp group by deptno) a where emp.deptno=a.deptno and
emp.sal>a.avgsal group by emp.deptno;

内连接

内连接通过使用比较运算符来使每个表的通用列中的值匹配来组成一个新表,即:把两个表中间共有的那些行拿出来进行连接,如果某些行不是两个表共有的,则不进行连接。

select

from 表1

inner join 表2

on 匹配条件

select

from 表1 表2

where匹配条件

select * from emp,dept where emp.deptno=dept.deptno;

select emp.ename,emp.sal,dept.dname from emp,dept where
emp.deptno=dept.deptno;

澳门太阳集团 7

左外连接

左外连接与内连接的区别是:设置左外连接的时候设置了主表和附表,主表在前,附表在后。内连接是将两个表匹配的地方输出出来,而左外连接则是主表全写,附表一一对应,附表有则加上,没有不写。

select

from 表1

left join 表2

on 匹配条件

select e.ename,e.job,d.loc from dept d left join emp e on
d.deptno=e.deptno;

将dept表放在前面,以dept表为主表,emp表做辅表进行链接

select e.ename,d.dname,d.loc from emp e left join dept d on
e.deptno=d.deptno;

将emp表放在前面,是以emp表作为主表,dept表作为辅表进行链接

澳门太阳集团 8

右外连接

右外连接和左外连接基本相同只是右外连接的主表写在后边。

select

from 表1

right join 表2

on 匹配条件

select e.ename,d.loc from emp e right join dept d on
e.deptno=d.deptno;

这个dept表放在后面,以dept表为主表,emp做辅表进行链接

select dname,ename,sal from dept left join emp on
dept.deptno=emp.deptno;

查询每个部门下的员工的姓名,工资

澳门太阳集团 9

全连接

全连接是在等值连接的基础上将左表和右表的未匹配数据都加上,使用的关键字为full
outer join或者full join。

select

from 表1

full join 表2

on 匹配条件

select e.ename,d.loc from emp e full join dept d on e.deptno=d.deptno;

自连接

还有一种特殊情况即自连接,在Oracle中一个表无法与自己进行比较,所以当需要自己表的两个信息做比较的时候也需要使用连接来连接,即同一张表的连结查询。

select a.ename 员工,b.ename 领导 from emp a,emp b where
a.mgr=b.empno;

select a.ename 员工,b.ename 领导 from emp a left join emp b on
a.mgr=b.empno;

澳门太阳集团 10

(3)子查询

子查询是指嵌套在其他sql语句中的select语句,也叫嵌套查询。sql语句执行顺序为从右到左执行,所以在执行查询时会先执行左侧的子查询后进行主查询。

子查询分为单行子查询和多行子查询,单行子查询是指返回一行数据的子查询语句,多行子查询是指返回多行数据的查询语句。子查询还可以分为多列子查询、多行子查询、多列多行子查询。

在进行子查询时如果内部查询不返回任何记录,则外部条件中字段DEPTNO与NULL比较永远为假,也就是说外部查询不返回任何结果。

总结为:

单行子查询是指子查询只返回单列、单行数据

多行子查询是指返回单列多行数据,都是针对单列而言的

多列子查询则是指查询返回多个列数据的子查询语句

单行子查询

where deptno = (单行数值)

多行子查询

where deptno in ( 多行数值 )

多列子查询:

where (job,deptno)=(select job,deptno from emp where ename=’KING’)

多列多行子查询

where (job,deptno) in (select job,deptno from emp where ename=’KING’)

单行子查询

在单行子查询的外部查询中可以使用=、>、<、>=、<=、<>等比较运算符。

内部查询返回的结果必须与外部查询条件中字段(DEPTNO)相匹配。

select ename from emp where deptno=(select deptno from emp where
ename=’SMITH’);

查询和SMITH部门相同的员工的名字

select ename,job,sal from emp where deptno=(select deptno from dept
where dname=’SALES’);

查询出销售部(SALES)下面的员工姓名,工作,工资

select emp.deptno,count(*) from emp,(select deptno,avg(sal) avgsal
from emp group by deptno) a where emp.deptno=a.deptno and
emp.sal>a.avgsal group by emp.deptno;

查询每个部门中工资高于该部门平均工资的员工人数

澳门太阳集团 11

多行子查询

在WHERE子句中使用多行子查询时,可以使用多行比较运算符(IN,ALL,ANY)。

IN:等于任何一个。

ALL:和子查询返回的所有值比较。例如:sal>ALL(1,2,3)等价于sal>3,即大于所有。

ANY:和子查询返回的任意一个值比较。例如:sal>ANY(1,2,3)等价于sal>1,即大于任意一个就可以。

注:ANY运算符必须与单行比较运算符结合使用,并且返回行只要匹配子查询的任何一个结果即可。

select ename,sal,deptno from emp where sal>any(select sal from emp
where deptno=30);

如何显示工资比部门30的任意一个员工的工资高的员工的姓名、工资和部门号

还可以:select ename,sal,deptno from emp where sal > (select
min(sal) from emp where deptno=30);

select ename,sal,deptno from emp where sal > all(select sal from
emp where deptno=30);

显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

select * from emp where job in(select job from emp where
ename=’MARTIN’ or ename=’SMITH’);

查询emp表中工作和MARTIN和SMITH工作相同的员工的信息

select ename,hiredate from emp where deptno=(select deptno from emp
where ename=’BLAKE’) and ename<> ‘BLAKE’;

创建一个查询,显示与blake在同一部门工作的雇员的姓名和受雇日期,black不包含在内

select ename,sal,mgr from emp where mgr=(select empno from emp where
ename=’KING’);

显示被king直接管理的雇员的姓名以及工资

澳门太阳集团 12

多列子查询

多列子查询和多行子查询相同,只是使用多列子查询的时候会有多列进行匹配。

如何查询与smith的部门和岗位完全相同的所有雇员

select ename,job,deptno from emp where (deptno,job)=(select deptno,job
from emp where ename=’SMITH’);

select ename,job,deptno from emp where (deptno,job) in (select
deptno,job from emp where ename=’SMITH’);

澳门太阳集团 13

(4)集合运算

为了合并多个select语句的结果,可以使用集合操作符号union,union
all,intersect,minus。

union:该操作符用于取得两个结果集的并集。当使用该操作符时,会自动去掉结果集中重复行

select ename,sal,job from emp where sal>2500 union select
enmae,sal,job from emp where job=’manager’;

union all:该操作与union相似,但是它不会取消重复行,而且不会排序

select ename,sal,job from emp where sal>2500 union all select
ename,sal,job from emp where job=’manager’;

intersect:使用该操作符用于取得两个结果集的交集

select ename,sal,job from emp where sal>2500 intersect select
ename,sal,job from emp where job=’manager’;

minus:使用该操作符用于取得两个结果集的差集,它只会显示存在第一个集合中,而不存在第二个集合中的数据

select enmae,sal,job from emp where sal>2500 minus select
ename,sal,job from emp where job=’manager’;

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

intersect  交集 返回两个查询共有的记录

union all  并集 返回各个查询的所有记录,包括重复的记录

union      交集 返回各个查询的所有记录,不包括重复的记录

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

注意:当使用集合操作的时候,查询所返回的列数以及列的类型必须匹配,列名可以不同。

选择没有奖金的雇员信息select ename,job from emp where comm is null

(1)Distinct关键字

在Oracle中,可能出现若干相同的情况,那么可以用Distinct消除重复行

select distinct deptno from emp;

(2)多表查询与单行子查询可以实现相同的功能

查询出销售部(sales)下面的员工姓名,工作,工资

使用多表连接查询的方法:

select dname,ename,job,sal from emp,dept where emp.deptno=dept.deptno
and dname=’SALES’;

使用单行子查询:

select ename,job,sal from emp where deptno=(select deptno from dept
where dname=’SALES’);

(3)显示高于自己部门平均工资的员工信息

分析:

1.找到所有部门的平均工资

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

2.找到所有人的工资信息

select ename,sal,deptno from emp;

3.把两个结果集使用多表连接组合组合起来

select * from emp,(select deptno,avg(sal) avgsal from emp group by
deptno) damao where emp.deptno=damao.deptno;

4.去掉低于平均工资的那些数据即可:

select * from emp,(select deptno,avg(sal) avgsal from emp group by
deptno) damao where emp.deptno=damao.deptno and sal>avgsal;

(4)emp表介绍

字段              类型                              描述

empno          NUMBER(4)                 表示雇员编号,是唯一编号

ename          VAECHAR2(10)           表示雇员姓名

job                VARCHAR2(9)             表示工作职位

mgr              NUMBER(4)                 表示一个雇员的领导编号

hiredate       DATE                            表示雇佣日期

sal               NUMBER(7,2)               表示月薪,工资

comm          NUMBER(7,2)               表示奖金,或者称为佣金

deptno         NUMBER(2)                 部门编号

3.order by

在关系型模式中,行是没有顺序可言的。order by
子句确定的行信息显示时的先后顺序,当按多列进行排序时,首先由第一个列名确定顺序,其次由第二个列名确定顺序。

先按工资的降序排列,在工资相同的情况下,按姓名的字母顺序排列

select ename employee,sal salary from emp where deptno=30order by sal
desc employee;

假如在select子句中使用distinct关键字,在order by 子句中只能使用select
子句中列出来的列名,不能使用列的别名。

在查询语句中不仅可以使用列名或列的别名进行排序,也可以使用在select
子句中列的位置进行排序。(select
子句中有一个很长的表达式,并且要用此表达式的结果进行排序时用列的位置是很有用的)。对于涉及集合操作:union,minus的查询,不能使用列名,在这种情况下,必须提供列的位置。

select ‘name:’||ename “employee”,sal “salary”,from
empwheredeptno=30order by2,1;

4.group by

在select 语句中可以使用group by
子句将行划分成较小的组,然后,使用聚组函数返回每一个组的汇总信息,另外,可以使用having子句限制返回的结果集。group
by 子句可以将查询结果分组,并返回行的汇总信息Oracle 按照group by
子句中指定的表达式的值分组查询结果。

在带有group by 子句的查询语句中,在select 列表中指定的列要么是group by
子句中指定的列,要么包含聚组函数

select max,job emp group by job;,job的job并非一定要出现,但有意义)

查询语句的select 和group by ,having
子句是聚组函数唯一出现的地方,在where 子句中不能使用聚组函数。

select deptno,sum from emp where sal>1200 group by deptno having
sum>8500 order by deptno;

当在gropu by 子句中使用having
子句时,查询结果中只返回满足having条件的组。在一个sql语句中可以有where子句和having子句。having
与where 子句类似,均用于设置限定条件

where
子句的作用是在对查询结果进行分组前,将不符合where条件的行去掉,即在分组之前过滤数据,条件中不能包含聚组函数,使用where条件显示特定的行。having
子句的作用是筛选满足条件的组,即在分组之后过滤数据,条件中经常包含聚组函数,使用having
条件显示特定的组,也可以使用多个分组标准进行分组。

查询每个部门的每种职位的雇员数select deptno,job,count from emp group by
deptno,job;

5.多表连接

笛卡尔连接就是指从多张表中取数据时,在where子句中没有指定多张表的公共关系(即一张表中取出每条记录与另一张表中的每条记录作连接)。假如第一张表中有M条记录,而第二张表中用N条记录,结果是M*N条记录。要尽量避免产生笛卡尔连接。所以一般有N张表连接,至少有N-1个连接条件。

select D.dname,D.Loc,E.ename,e.sal from delpt D,emp E where
D.deptno=E.deptno;

一但定义了表的别名,在本条select语句中就不能用表名去限制列名,应该用表的别名去限制别名。

哪些雇员的工资属于第三级别?

select empno,ename,sal from emp,salgrade where grade=3 and sal between
losal and hisal;

找出emp表中的每一雇员属于哪一等级。

select empno,ename,sal,grade fromemp,sal,gradewhereemp.sal between
lowsal and hisal.

有时候,即使在连接的表中没有相应的行,用户可能想从一张表中看数据,Oracle提供了外连接实现该功能。内连接是指连接查询只显示完全满足连接条件的记录,即等值连接,外连接的查询结果是内连接查询结果的扩展。外连接不仅返回满足连接条件的所有记录而且也返回了一个表中那些在另一个表中没有匹配行的记录。外连接的操作符是“+”。“+”号放在连接条件中信息不完全的那一边。运算符“+”影响NULL行的建立。建一行或多行NULL来匹配连接的表中信息完全的行。

外连接运算符“+”只能出现在where子句中表达式的一边。

假如在多张表之间有多个连接条件,外连接运算符不能使用or,in逻辑运算符与其它条件组合。

假如emp表中deptno=10的ename为空值,dept表中deptno=20的loc为空值:

select ename,dept.deptno,loc from emp,dept where emp.depno=dept.deptno;

如果在dept.deptno中有的数值在emp.deptno中没有值,则在做外连接时,结果中ename会产生一个空值。

select ename,dept.deptno,loc from emp,dept where emp.depno=dept.deptno;

如果在emp.deptno中有的数值在dept.deptno中没有值,则在做外连接时,结果中loc会产生一个空值。。

自连接是指同一张表的不同行间的连接。该连接不受其他表的影响。用自连接可以比较同一张表中不同行的某一列的值。因为自连接查询仅涉及到某一张表与其自身的连接。所以在from子句中该表名出现两次,分别用两个不同的别名表示,两个别名当作两张不同的表进行处理,与其它的表连接一样,别名之间也使用一个或多个相关的列连接。为了区分同一张表的不同行的列,在名前永别名加以限制。

select worker.ename,manager.ename manager from emp worker,emp
managerwherework.mgr=manager.empno;

基合运算符可以用于从多张表中选择数据。

①UNION运算用于求两个结果集合的并集,并自动去掉重复行。

select ename,sal from account where sal>2000unionselect ename,sal
from research where sal>2000unionselect ename,sal from sales where
sal>2000;

注:ename,sal 是必须一致的。

②UNION ALL运算用于求两个结果集合的并集源码天空,并且不去掉重复行。

select ename,sal from account where sal>2000unionselect ename,sal
from research where sal>2000unionselect ename,sal from sales where
sal>2000;

③INTERSECT运算intersect运算返回查询结果中相同的部分。

各部门中有哪些相同的职位?

select Job from accountintersectselect Job from researchintersectselect
Job from sales;

④MINUS运算minus返回两个结果集的差集。(在第一个结果集中存在的,而在第二个结果集中不存在的行。)

有那些职位是财务部中有,而在销售部门中没有?

select Job from accountminusselect Job from sales;

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图