输出一个结果新葡京32450网址:,查询结果只显示两个列中的值是等值条件的行数据

发布时间:2020-01-20  栏目:数据  评论:0 Comments

本节剖判Oracle多表查询中的相关内容。

本文所用到的表为oracle中scott客商下的emp表,dept表,数据如下:

新葡京32450网址 1新葡京32450网址 2

 

 

查询是数额的三个主要操作。客户发送查询诉求,经编写翻译软件造成成二进制文件供服务器询问,后归来查询结果集给客商,查询会产生一个虚构表,看见的是表格局显示的结果,但结果并不确实的蕴藏,每便实施查询只是从数据表中提取数据,并遵守表的款型体现出来。

5.多表查询

多表查询

目标:从多张表获取数据

前提:进行三翻五次的多张表中有联袂的列

等连接

通过七个表具备同样意义的列,创建连接条件.

查询结果只彰显四个列中的值是等值条件的行数据

表中同名列被增选时必得抬高表名前缀进行修饰
不然无从鲜明这一列是归于哪个表

差异连接

A表中的某列数据和B表中一列或多列的涉及是非等值关系,大于,小于,不等于,等标准化都归属差异连接的层面

自连接

数码都来自一张表,所以在from字句中需求对表增加外号,增多表别称后才干合法化的援用表中的列名.

真相正是将一张表设想成了两张表

外连接

就是选收取满足等连接条件及其以外的行

(+卡塔尔修饰符号用法:放置在选出结果只包罗等连接的列后,则另一列的结果就是等值行+非等值行

全连接

全链接是把做切合和不切合关联条件的七个表的新闻都显示出来

等链接

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

EMPNO ENAME      JOB   MGR HIREDATE       SAL COMM   DEPTNO    DEPTNO
DNAME   LOC



7782 CLARK      MANAGER 7839 09-JUN-81      2450       10 10 ACCOUNTING
  NEW YORK

7839 KING      PRESIDENT      17-NOV-81      5000       10 10 ACCOUNTING
  NEW YORK

7934 MILLER    CLERK 7782 23-JAN-82      1300       10 10 ACCOUNTING  
NEW YORK

7566 JONES      MANAGER 7839 02-APR-81      2975       20 20 RESEARCH  
DALLAS

7902 FORD      ANALYST 7566 03-DEC-81      3000       20 20 RESEARCH  
DALLAS

7876 ADAMS      CLERK 7788 23-MAY-87      1100       20 20 RESEARCH  
DALLAS

7369 SMITH      CLERK 7902 17-DEC-80        800       20 20 RESEARCH  
DALLAS

7788 SCOTT      ANALYST 7566 19-APR-87      3000       20 20 RESEARCH  
DALLAS

7521 WARD      SALESMAN 7698 22-FEB-81      1250   500       30 30 SALES
  CHICAGO

7844 TURNER    SALESMAN 7698 08-SEP-81      1500     0       30 30 SALES
  CHICAGO

7499 ALLEN      SALESMAN 7698 20-FEB-81      1600   300       30 30
SALES   CHICAGO

7900 JAMES      CLERK 7698 03-DEC-81        950       30 30 SALES  
CHICAGO

7698 BLAKE      MANAGER 7839 01-MAY-81      2850       30 30 SALES  
CHICAGO

7654 MARTIN    SALESMAN 7698 28-SEP-81      1250 1400       30 30 SALES
  CHICAGO

标准sql写法:

SQL> select empno,ename,loc from emp inner join dept on
emp.deptno=dept.deptno;

idle>

那才是我们要的结果.笛Carl积大约大家不会供给.多表查询时宗旨都含有where子句来汇报两个表的关联
幸免笛Carl集

当多少个表中有雷同的列名时,为了不一样 要在列前增加表名作前缀.

在一块儿的五个表内取数据:描述scott在哪个机构

idle> select empno,ename,dname,sal from emp,dept where
emp.deptno=dept.deptno and ename=’SCOTT’;

EMPNO ENAME      DNAME     SAL


7788 SCOTT      RESEARCH   3000

为了书写方便,大家能够给表起别名

表的别称

格式:表名 别名

给表取别称是很有不能缺少的,因为有个别表名很短 不方便人民群众引用时书写.

SQL> select *from emp a,dept b where a.deptno=b.deptno and
a.ename=’SCOTT’;

EMPNO ENAME      JOB   MGR HIREDATE       SAL COMM   DEPTNO    DEPTNO
DNAME   LOC



7788 SCOTT      ANALYST 7566 19-APR-87      3000       20 20 RESEARCH  
DALLAS

不等连接

纵然撤消完全相等条件以外的 >,<,!=, <=, >=, between and

重点在于不一致表之间显示特定范围的消息(也足以掌握成包括关系卡塔尔(قطر‎

求出各种职工的工薪等级

SQL> SELECT *FROM SALGRADE;

GRADE LOSAL   HISAL


1   700     1200

2 1201     1400

3 1401     2000

4 2001     3000

5 3001     9999

SQL> select empno,ename,sal,grade from emp a,salgrade b where a.sal
between b.losal and b.hisal;

EMPNO ENAME     SAL      GRADE


7369 SMITH     800   1

7900 JAMES     950   1

7876 ADAMS     1100   1

7521 WARD     1250   2

7654 MARTIN     1250   2

7934 MILLER     1300   2

7844 TURNER     1500   3

7499 ALLEN     1600   3

7782 CLARK     2450   4

7698 BLAKE     2850   4

7566 JONES     2975   4

7788 SCOTT     3000   4

7902 FORD     3000   4

7839 KING     5000   5

14 rows selected.

自连接

无差异于张表内的连年查询 即把一个表映射成五个表

首要用以表的自参照关系 比方emp中的上下级或档期的顺序关系

因为自连接是均等张表之间的链接查询 所以必得定义表小名

SQL> select a.empno,a.ename,b.empno,b.ename from emp a,emp b where
a.mgr=b.empno;

EMPNO ENAME   EMPNO ENAME


7902 FORD     7566 JONES

7788 SCOTT     7566 JONES

7844 TURNER     7698 BLAKE

7499 ALLEN     7698 BLAKE

7521 WARD     7698 BLAKE

7900 JAMES     7698 BLAKE

7654 MARTIN     7698 BLAKE

7934 MILLER     7782 CLARK

7876 ADAMS     7788 SCOTT

7698 BLAKE     7839 KING

7566 JONES     7839 KING

7782 CLARK     7839 KING

7369 SMITH     7902 FORD

13 rows selected.

外链接

不但再次来到满意接二连三的记录 还有大概会回到不满足一而再三番五次的记录

外链接的运算符(+卡塔尔

该符号能够放在等的左边或右边手,但必要求放在贫乏音讯(完全满意条件的行卡塔尔(قطر‎的后生可畏边.

若加在多新闻的一面
和没加同样,能够掌握为何人要求空行来补充,就在哪个人的背后加(+)

举个例子:emp中不包括40号部门的职工 而dept中有40号部门

SQL> select empno,ename,b.deptno,dname from emp a,dept b where
a.deptno(+)=b.deptno;

EMPNO ENAME   DEPTNO DNAME


7782 CLARK       10 ACCOUNTING

7839 KING       10 ACCOUNTING

7934 MILLER       10 ACCOUNTING

7566 JONES       20 RESEARCH

7902 FORD       20 RESEARCH

7876 ADAMS       20 RESEARCH

7369 SMITH       20 RESEARCH

7788 SCOTT       20 RESEARCH

7521 WARD       30 SALES

7844 TURNER       30 SALES

7499 ALLEN       30 SALES

7900 JAMES       30 SALES

7698 BLAKE       30 SALES

7654 MARTIN       30 SALES

40 OPERATIONS

15 rows selected.

标准sql写法 left join on

询问出装有部门名和10号部门内的职工

因为职工新闻体现少之又少 所以(+State of Qatar符号放在emp.deptno上

SQL> select b.deptno,b.dname,a.empno,a.ename from emp a,dept b where
a.deptno(+)=b.deptno and a.deptno(+)=10;

DEPTNO DNAME       EMPNO ENAME


10 ACCOUNTING 7782 CLARK

10 ACCOUNTING 7934 MILLER

10 ACCOUNTING 7839 KING

20 RESEARCH

30 SALES

40 OPERATIONS

6 rows selected.

SQL>

SQL> 若是不在全体的尺度方面写(+)就能够发出这么的结果

SQL> select b.deptno,b.dname,a.empno,a.ename from emp a,dept b where
a.deptno(+)=b.deptno and a.deptno=10;

DEPTNO DNAME       EMPNO ENAME


10 ACCOUNTING 7782 CLARK

10 ACCOUNTING 7839 KING

10 ACCOUNTING 7934 MILLER

外接连的注意事项

1.where中有四个标准期 必需每一种条件都亟需利用(+卡塔尔国符号

2.(+卡塔尔只利用于列 不能够是表明式

3.(+卡塔尔(قطر‎不能够与in or一同操作

实则测验中,in是能够的,给or加上括号,升高运算优先级,也是足以的。

全连接

相符连接条件的,就连接成意气风发行,不相符的,也整个来得出来

full join on

SQL> select a.deptno,a.dname,b.empno,b.ename from dept a full join
emp b on a.deptno=b.deptno and a.deptno=10;

DEPTNO DNAME       EMPNO ENAME


7369 SMITH

7499 ALLEN

7521 WARD

7566 JONES

7654 MARTIN

7698 BLAKE

10 ACCOUNTING 7782 CLARK

7788 SCOTT

10 ACCOUNTING 7839 KING

7844 TURNER

7876 ADAMS

7900 JAMES

7902 FORD

10 ACCOUNTING 7934 MILLER

30 SALES

40 OPERATIONS

20 RESEARCH

17 rows selected.

函数:oracle服务器先事写好的生机勃勃段具备一定效率的程序片段,内置于oracle服务器,供客商调用

导读

  明日首要内容:表管理、表操作(增加和删除改查)、表查询(轻巧查询&复杂查询)、创设数据库。

大器晚成、笛Carl集:

SELECT <列名>

单行函数:输入四个参数,输出七个结出,举例:upper(‘baidu.com’卡塔尔(قطر‎->BAIDU.COM

一、表管理

概念:若是A,B是2个集聚,A X B所产生的集结叫笛Carl集。

FGROM <表名>

1.标准的sql写法

natual join

五个表要有共有列,并且数据类型豆蔻年华致

若果七个表的共有列不只三个 natural join会按全体共有列连接

SQL> select empno,ename,deptno from emp e natural join dept d;

join using 是 inner join using的缩写

USING 子句

注意事项:

1.natural join 和 using子句互斥 无法同有时候选用

2.所引述的列无法应用表名前缀

3.被援用的列一定是多个表的共有列

4.多列相同期只可以选取一列

SQL> select empno,ename,deptno from emp e join dept d using (deptno);

join on 是inner join on的简写格局

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

left join on 是left outer join on的简写格局

左外连接

满意接二连三条件的数目,甚至不满意连接条件的侧面表的任何数据

SQL> select d.deptno,d.dname,d.loc,e.empno,ename from dept d left
join emp e on d.deptno=e.deptno and d.deptno=10;

DEPTNO DNAME   LOC     EMPNO ENAME


10 ACCOUNTING   NEW YORK       7782 CLARK

10 ACCOUNTING   NEW YORK       7839 KING

10 ACCOUNTING   NEW YORK       7934 MILLER

40 OPERATIONS   BOSTON

30 SALES   CHICAGO

20 RESEARCH   DALLAS

right join on 是right outer join的简写情势

右外链接

满意三番两回条件的数目,甚至不满足连接条件的右侧表的别的数据

full join on 是full outer join on的简写形式

全外连接

满足一而再条件的数码,以至不满意连接条件的两边的表的其他数据

连天条件和过滤条件的界别

总是条件出未来on子句中的条件,用于限定可以进行表连接的笔录

被一而再的表只展现相符连接条件的数据

比如:这里的emp表是被延续的表

SQL> select d.deptno,d.dname,d.loc,e.empno,ename from dept d left
join emp e on d.deptno=e.deptno and d.deptno=10;

DEPTNO DNAME   LOC     EMPNO ENAME


10 ACCOUNTING   NEW YORK       7782 CLARK

10 ACCOUNTING   NEW YORK       7839 KING

10 ACCOUNTING   NEW YORK       7934 MILLER

40 OPERATIONS   BOSTON

30 SALES   CHICAGO

20 RESEARCH   DALLAS

6 rows selected.

过滤条件是用在where子句中的,用于对连年后的结果举行过滤

多行函数:输入多少个参数,或然是个中扫描多次,输出三个结实,比如:count(*)->14

  1、表命名标准

  •  必需以字母开头;
  •  长度无法抢先三十二个字符;
  •  无法运用Oracle保留字;
  • 必须要动用如下字符 A-Z,a-z,0-9,$,#。

实例1:A={a,b} B={c,d,e},那么A X B={(a,c),(a,d),(a,e),(b,c),(b,d),(b,e)}

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

2.聚众操作

聚拢操作

用以多条select语句合併结果

union 并集 去重

union all 并集 不去重

intersect 交集

minus 差集

union

A集合和B集结的晤面,但去掉两集聚重复的局地 会排序

输出列名根据第一条sql的输出方式呈现,

享有对应的列必需持有肖似的数据类型,不然报错

聚焦的字段名使用第二个结实集的字段名称

SCOTT@ora11g> select deptno,ename from emp where deptno in (20,30)

2          union

3          select deptno,ename from emp where deptno in (20,10)

4  ;

DEPTNO ENAME


10 CLARK

10 KING

10 MILLER

20 ADAMS

20 FORD

20 JONES

20 SCOTT

20 SMITH

30 ALLEN

30 BLAKE

30 JAMES

30 MARTIN

30 TURNER

30 WARD

14 rows selected.

SCOTT@ora11g>

union all

A会集和B会集的会见,不去重,不排序

SCOTT@ora10g> select deptno,ename from emp where deptno in (20,30)

2       union all

3       select deptno,ename from emp where deptno in (20,10)

4*

SCOTT@ora10g> /

DEPTNO ENAME


20 SMITH

30 ALLEN

30 WARD

20 JONES

30 MARTIN

30 BLAKE

20 SCOTT

30 TURNER

20 ADAMS

30 JAMES

20 FORD

20 SMITH

20 JONES

10 CLARK

20 SCOTT

10 KING

20 ADAMS

20 FORD

10 MILLER

19 rows selected.

SCOTT@ora11g>

intersect

三个集聚的因陋就简部分,排序并去重

SCOTT@ora11g> select deptno,ename from emp where deptno in (20,30)

2       intersect

3       select deptno,ename from emp where deptno in (20,10)

4*

SCOTT@ora10g> /

DEPTNO ENAME


20 ADAMS

20 FORD

20 JONES

20 SCOTT

20 SMITH

SCOTT@ora11g>

minus

取五个汇聚的差集,A集结中留存,B集合中不设有的数额(取A集合中B集结海市蜃楼的多少卡塔尔(قطر‎去重

SCOTT@ora10g>  select deptno,ename from emp where deptno in (20,30)

2       minus

3       select deptno,ename from emp where deptno in (20,10)

4*

SCOTT@ora10g>

DEPTNO ENAME


30 ALLEN

30 BLAKE

30 JAMES

30 MARTIN

30 TURNER

30 WARD

6 rows selected.

总计emp表中职员和工人业总会人数

  2、Oracle中的数据类型

  字符型

  • char:定长,最大尺寸二〇〇一字节。如:char(10State of Qatar,表示存款和储蓄10字节长度的字符串,不足10字节的以空格后补。特点:浪费存款和储蓄空间,但询问速度快。
  • nchar:同char同样,只是nchar是Unicode编码的,扶植多国语言。
  • varchar2:边长,最大尺寸4000字节。如char(20State of Qatar,表示可存款和储蓄最大尺寸为20字节的字符串。特点:节约存款和储蓄空间,但询问速度慢。
  • nvarchar2:同varchar2相像,只是nvarchar2是Unicode编码的,援救多国语言。
  • clob(character large
    object):字符型大对象 ,最大尺寸4G。特点:够大。

  数字类型

  • number:可代表整数和小数,范围在[-10^38,10^38]。
  • number(m卡塔尔国:表示五个m个有效位数的平头。
  • number(m,n卡塔尔(قطر‎:表示一个m个有效位数的小数,此中包罗n个小数位。

  日期类型

  • date:准确到秒。暗许格式是“dd-mon-yy”,即“日-月-年”,而作者辈中中原人民共和国的习于旧贯是“年-月-日”,所以能够使用sql> alter
    session set nls_date_format =  ‘yyyy-mm-dd’;来改正日期的格式。

  • timestamp:
    是date的数据类型的扩展,能够标准到小数秒(fractional_seconds_precision)。

  二进制大对象类型

  • blob(Binary Large Object卡塔尔国:
    二进制数据, 能够贮存图片、声音、录像等,最大尺寸
    4G。ps:日常景况下大家只要求仓库储存大文件路线就能够。

图片实比如下:

A:新葡京32450网址 3 
B:新葡京32450网址 4

那么A X
B:新葡京32450网址 5

[GROUP BY <分组表明式>]

3.子查询

子查询

子查询指嵌入在此外SQL中的select语句,也称嵌套查询.

依照子查询重返结果,可将子查询分为:

单行单列  col = (sub_q)

单行多列  (col1,col2..卡塔尔=(sub_q)

多行单列  col in (sub_q)

多行多列  (col1,col2…State of Qatar in (sub_q)

数值 一对一  = > <

数据 一对多  > any  > all

遵照子查询where条件来分

标量子查询

关联子查询

特点:

1.事情发生在此之前执行子查询,主查询再使用子查询的结果

2.子查询再次回到的列数和类型要同盟

3.子查询要用括号括起来

4.子查询重返多行要用多行关系运算符

单列单行子查询

子查询重临黄金年代行记录

询问和scott在长久以来单位的职员和工人

SCOTT@ora11g> select deptno,ename,sal from emp where deptno=(select
deptno from emp where ename=’SCOTT’);

DEPTNO ENAME     SAL


20 SMITH     800

20 JONES     2975

20 SCOTT     3000

20 ADAMS     1100

20 FORD     3000

SCOTT@ora11g>

也得以把手查询结果真是一列

SCOTT@ora10g> select deptno,ename,(select deptno from emp where
ename=’SCOTT’) AA from emp where deptno=10;

DEPTNO ENAME       AA


10 CLARK       20

10 KING       20

10 MILLER       20

多行子查询

多行子查询指重回多行数据的子查询语句

当在where中央银行使时,必得运用多行比较符(in all any卡塔尔国

ALL和any操作符不可能独立运用 要与单行相比符(= > < >= <=
<>State of Qatar结合使用

in  匹配于子查询结果的任叁个值就可以

ALL  必定要切合子查询结果的全部值

ANY  只要相符子查询结果的随机一个值就能够

in 操作

SCOTT@ora10g> select empno from emp where deptno=10;

EMPNO


7782

7839

7934

SCOTT@ora10g> select empno,ename,sal from emp where empno in (select
empno from emp where deptno=10);

EMPNO ENAME     SAL


7782 CLARK     2450

7839 KING     5000

7934 MILLER     1300

any 操作

稍低于最大的就可以

SCOTT@ora10g> select deptno,ename,sal from emp where deptno < any
(select distinct deptno from emp where deptno = 20 or deptno = 30);

DEPTNO ENAME     SAL


10 CLARK     2450

10 KING     5000

10 MILLER     1300

20 JONES     2975

20 FORD     3000

20 ADAMS     1100

20 SMITH     800

20 SCOTT     3000

8 rows selected.

SCOTT@ora10g>

ALL 操作

低于最小的就能够

SCOTT@ora10g> select deptno,ename,sal from emp where deptno < all
(select distinct deptno from emp where deptno = 20 or deptno = 30);

DEPTNO ENAME     SAL


10 CLARK     2450

10 KING     5000

10 MILLER     1300

多列子查询

指子查询再次来到多个列的数额

当三个列独有意气风发行数据时 能够动用单行比较符

当多少个列有多行数据时,依旧必要 IN

多列单行

正是多规格比较

询问和SMITH相仿部门 相像岗位的人

SCOTT@ora11g> select deptno,ename,job,sal from emp where (deptno,job)
= (select deptno,job from emp where ename=’SMITH’);

DEPTNO ENAME      JOB       SAL


20 SMITH      CLERK       800

20 ADAMS      CLERK       1100

SCOTT@ora10g>

多列多行

IN

找寻官员和薪水与SCOTT和WALANDD意气风发致的人

SCOTT@ora11g> select ename,mgr,sal from emp where ename in
(‘SCOTT’,’WARD’);

ENAME   MGR     SAL


WARD 7698     1250

SCOTT 7566     3000

SCOTT@ora11g> select deptno,ename,mgr,sal from emp where (mgr,sal) in
(select mgr,sal from emp where ename in (‘SCOTT’,’WARD’)) and ename not
in (‘SCOTT’,’WARD’);

DEPTNO ENAME     MGR SAL


30 MARTIN     7698      1250

20 FORD     7566      3000

搜寻各样部门的万丈工资的职员和工人姓名

SCOTT@ora10g> select deptno,max(sal) from emp group by deptno;

DEPTNO  MAX(SAL)


30 2850

20 3000

10 5000

SCOTT@ora10g> select deptno,ename,sal from emp where (deptno,sal) in
(select deptno,max(sal) from emp group by deptno);

DEPTNO ENAME     SAL


30 BLAKE     2850

20 SCOTT     3000

10 KING     5000

20 FORD     3000

关联子查询

将主查询的剧情传递给子查询 子查询再把询问构造反馈给主查询

子查询施行的次数决意于主查询传递值的次数

寻觅每一个机构报酬最高的人

不行使关联子查询 使用in分组织承办法得以完毕

寻找每一个部门的参天工资的职工姓名

SCOTT@ora10g> select deptno,max(sal) from emp group by deptno;

DEPTNO  MAX(SAL)


30 2850

20 3000

10 5000

SCOTT@ora10g> select deptno,ename,sal from emp where (deptno,sal) in
(select deptno,max(sal) from emp group by deptno);

DEPTNO ENAME     SAL


30 BLAKE     2850

20 SCOTT     3000

10 KING     5000

20 FORD     3000

逐一相比较形式

SQL> select deptno,ename,sal from emp where sal = (select max(sal)
from emp where deptno=10) and deptno=10;

DEPTNO ENAME     SAL


10 KING     5000

SQL> select deptno,ename,sal from emp where sal = (select max(sal)
from emp where deptno=20) and deptno=20;

DEPTNO ENAME     SAL


20 SCOTT     3000

20 FORD     3000

SQL> select deptno,ename,sal from emp where sal = (select max(sal)
from emp where deptno=30) and deptno=30;

DEPTNO ENAME     SAL


30 BLAKE     2850

SQL>

接收关联子查询格局

SQL> select deptno,ename,sal from emp e where sal = (select max(sal)
from emp where deptno=e.deptno);

DEPTNO ENAME     SAL


30 BLAKE     2850

20 SCOTT     3000

10 KING     5000

20 FORD     3000

SQL>

关联子查询出以往select字段中

SQL> select empno,ename,deptno,(select loc from dept where
deptno=e.deptno) loc from emp e;

EMPNO ENAME          DEPTNO LOC


7369 SMITH              20 DALLAS

7499 ALLEN              30 CHICAGO

7521 WARD              30 CHICAGO

7566 JONES              20 DALLAS

7654 MARTIN            30 CHICAGO

7698 BLAKE              30 CHICAGO

7782 CLARK              10 NEW YORK

7788 SCOTT              20 DALLAS

7839 KING              10 NEW YORK

7844 TURNER            30 CHICAGO

7876 ADAMS              20 DALLAS

7900 JAMES              30 CHICAGO

7902 FORD              20 DALLAS

7934 MILLER            10 NEW YORK

已选择14行。

exists查询

使用EXISTS语句能够测量检验集合是或不是为空,

EXISTS语句平日与子查询结合在一同行使。

只要子查询中足足再次来到多少个值,则EXISTS语句的值就为True。\

查到就不再继续查

搜索首席营业官 此工作者的empno在mgr列设有正是领导

SCOTT@ora10g> select empno,ename,mgr from emp e where exists (select
1 from emp where mgr=e.empno);

EMPNO ENAME     MGR


7902 FORD     7566

7698 BLAKE     7839

7839 KING

7566 JONES     7839

7788 SCOTT     7566

7782 CLARK     7839

6 rows selected.

in也能幸不辱命 只是in的频率不高 in会一向比下去 而exists比成功就不在比了.

SCOTT@ora10g> select empno,ename,mgr from emp where empno in (select
mgr from emp);

EMPNO ENAME     MGR


7902 FORD     7566

7698 BLAKE     7839

7839 KING

7566 JONES     7839

7788 SCOTT     7566

7782 CLARK     7839

6 rows selected.

SCOTT@ora10g>

取反

用in改写时并不一定获得和not exists大器晚成致的结果

正是查找普通职员和工人 不是管理者的人

SQL> select empno,ename from emp e where not exists (select 1 from
emp where mgr=e.empno);

EMPNO ENAME


7844 TURNER

7521 WARD

7654 MARTIN

7499 ALLEN

7934 MILLER

7369 SMITH

7876 ADAMS

7900 JAMES

8 rows selected.

SQL>

SQL> select empno,ename from emp e where empno in (select mgr from
emp);

EMPNO ENAME


7902 FORD

7698 BLAKE

7839 KING

7566 JONES

7788 SCOTT

7782 CLARK

6 rows selected.

SQL> select empno,ename from emp e where empno not in (select mgr
from emp);

no rows selected

SQL> 原因是如何?

with 语句

当查问中每每用到某一片段时,能够用with语句创立叁个公共有的时候表。

因为子查询在内部存储器偶然表中,防止了重新解析,所以进行成效会增高不菲。

不常表在二遍查询甘休自动消亡。

语法: 三个select里的二个子查询  有效约束在此个SQL试行周期内

首先次举行(剖析 实施 拿到结果卡塔尔

其次次奉行(深入分析 施行 获得结果)

第叁次实践(分析 施行 取得结果卡塔尔(قطر‎ ==> 缓存到有的时候表

第叁回实施 (直接取结果)

with

alias_name1 as    (subquery1),

alias_name2 as    (subQuery2),

……,

alias_nameN as    (subQueryN)

select col1,col2…… col3

from alias_name1,alias_name2……,alias_nameN

例子:

SQL> with

q1 as (select 3+5 s from dual),

q2 as (select 3*5 m from dual),

q3 as (select s,m,s+m,s*m from q1,q2)

select * from q3;

S     M     S+M S*M


8   15       23 120

SQL>

询问每一个机构大于平均薪酬的职工,也足以如此来贯彻

SQL> with

2  a as (select deptno,avg(sal) x from emp group by deptno)

3  select * from emp,a where emp.deptno=a.deptno and emp.sal>a.x;

EMPNO ENAME      JOB       MGR HIREDATE     SAL      COMM    DEPTNO  
DEPTNO   X



7499 ALLEN      SALESMAN       7698 20-FEB-81   1600        300   30    
  30 1566.66667

7566 JONES      MANAGER       7839 02-APR-81   2975   20       20     
2175

7698 BLAKE      MANAGER       7839 01-MAY-81   2850   30       30
1566.66667

7788 SCOTT      ANALYST       7566 19-APR-87   3000   20       20     
2175

7839 KING      PRESIDENT   17-NOV-81   5000   10       10 2916.66667

7902 FORD      ANALYST       7566 03-DEC-81   3000   20       20     
2175

with子句中的视图叫做询问块,询问块的复杂性查询在

with子句中只运转一回,运维成功后会将掌握块的结果集

保存到temp表空间,以往再度调用询问块时会在后台调换

为对结果集的直接待上访谈

select count(*) from emp;

  3、表的创设

  比如我们接纳scott来创设贰个学员表,包括字段音讯:学号、姓名、性别、出生辰期和一个班级表,包涵音信:班级号、班级名。

  所以可以执行sql语句:

 1 --学生表   
 2 create table student (    ---表名   
 3           sid         number(4),   --学号   
 4           sname    varchar2(20),   --姓名   
 5           sex         char(2),     --性别   
 6           birthday   date,         --出生日期    
 7 );   
 8 --班级表   
 9 CREATE TABLE class(   
10     cid   NUMBER(2),   
11     cName   VARCHAR2(40)   
12 );

由此可看出笛卡尔集的结果为列数相加行数相乘,此中有广大再度项。

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

4.DDL

DDL 语句 数据定义语言

CREATE 创建

ALTER 修改

DROP 删除

TRUNCATE 截断

COMMNET 注释

RENAME 更名

改革表名

rename old_name to new_name

SQL> rename t5 to t6;

表已重命名。

增加列

alter table table_name add(column datatype [default expr] [,column
datatype…])

SQL> alter table t6 add(id number default 1000);

改过列的定义

(校订后的数据类型无法与列中当前的数量冲突,

不然列必得为空,default值的退换只对接轨insert数占有效,早前的多寡不受影响)

alter table table_name modify(column datatype [default expr]
[,column datatype…)

删除列,不管有无数据均能够去除,可是末了一列是不可能被剔除的。

alter table drop (column)

SQL> alter table t2 drop (id);

表已改成。

应用SET UNUSED 选项标志一列或多列为unused

选用DROP UNUSED COLUMNS 选项删除unused列。

本条意义能够在系统高峰期因删除字段而端来影响,因为除去字段会记录undo,因而得以先把列标志为unused等系统不繁忙的时候再drop掉。

语法:

ALTER TABLE  <table_name>SET  UNUSED()(<column_name>);

ALTER TABLE  <table_name>SET  UNUSED
COLUMN(<column_name>);

ALTER TABLE  <table_name>DROP  UNUSED COLUMNS;

校正列名(列名称不能与第一字冲突,比不上类型名称,命令名称等)

alter table table_name rename column old_column_name to
new_column_name

SQL> alter table t2 rename column sal to salary;

表已退换。

日增注释

comment on table table_name is ‘text’

comment on column table_name.columb is ‘text’

user_tab_comments  表的批注音讯,在此此中能够查到

user_col_comments  列的注释音信,在此地能够查到

截断表

保留表布局

清空表的数据

tuncate table table_name

归属ddl语句,同样都有删除数据的机能,可是有以下两样

truncate 能够回笼藏保存款和储蓄空间,delete不会

delete能够回降,truncate不得以

做事中,严谨使用truncate

删除表

drop table table_name [purge]

10G中加进了回笼站作用,表未有被完全删 只是放到回笼站里去了

SQL> drop table t2;

表已删除。

从回笼站中苏醒表

SQL> show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE  DROP TIME



T4 BIN$M1uFYjFloJrgUAB/AQALVQ==$0 TABLE     2016-05-21:21:52:41

或者

select *from recyclebin;

SQL> flashback table t4 to before drop;

Flashback complete.

例如:

SQL> flashback table t4 to before drop;

Flashback complete.

SQL> select *from tab;

TNAME       TABTYPE CLUSTERID


BONUS       TABLE

DEPT       TABLE

EMP       TABLE

SALGRADE       TABLE

T4       TABLE

SQL> drop table t4;

Table dropped.

SQL>

SQL>

SQL> show recycle

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE  DROP TIME



T4 BIN$M1uFYjFmoJrgUAB/AQALVQ==$0 TABLE     2016-05-21:21:57:42

复原已删除的表并改名

SQL> flashback table t4 to before drop rename to t7;

Flashback complete.

SQL> select *from tab;

TNAME       TABTYPE CLUSTERID


BONUS       TABLE

DEPT       TABLE

EMP       TABLE

SALGRADE       TABLE

T7       TABLE

固然回笼站里的表名有再次,默许苏醒以来一回删除的那张表

SQL> show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE  DROP TIME



T4 BIN$OASw9TBNurHgUAB/AQBbYw==$0 TABLE     2016-07-20:05:26:13

T4 BIN$N++H8pxalGbgUAB/AQAvxA==$0 TABLE     2016-07-19:04:08:26

T4 BIN$N+52pkGdHVLgUAB/AQAgTQ==$0 TABLE     2016-07-19:02:52:00

SQL> flashback table t4 to before drop;

Flashback complete.

SQL> show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE  DROP TIME



T4 BIN$N++H8pxalGbgUAB/AQAvxA==$0 TABLE     2016-07-19:04:08:26

T4 BIN$N+52pkGdHVLgUAB/AQAgTQ==$0 TABLE     2016-07-19:02:52:00

倘若回笼站里的表名有双重,钦命要重整旗鼓的表,须要使用回笼站里的表名

SQL> show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE  DROP TIME



T4 BIN$OASw9TBOurHgUAB/AQBbYw==$0 TABLE     2016-07-20:05:29:27

T4 BIN$N+52pkGdHVLgUAB/AQAgTQ==$0 TABLE     2016-07-19:02:52:00

点名回收站里的名字要求加双引号

SQL> flashback table “BIN$N+52pkGdHVLgUAB/AQAgTQ==$0” to before drop;

Flashback complete.

SQL> show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE  DROP TIME



T4 BIN$OASw9TBOurHgUAB/AQBbYw==$0 TABLE     2016-07-20:05:29:27

清空回笼站

SQL> show recycle;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE  DROP TIME



T7 BIN$M1uFYjFnoJrgUAB/AQALVQ==$0 TABLE     2016-05-21:21:59:28

SQL> purge recyclebin;

Recyclebin purged.

SCOTT@ora10g> show recycle; 查看回笼站.

SCOTT@ora10g> purge recyclebin; 清空回笼站.

SCOTT@ora10g> purge table t2 ; 清空回笼站中t2的表.

SQL>  purge dba_recyclebin; 清空全数顾客回笼站里的内容.

SQL> alter session set recyclebin=off; 关闭当前对话的回笼站;

SQL> alter system set recyclebin=off; 关闭系统的回笼站;

各类客商分配二个回笼站。

注意:*号适用于表字段非常少的情景下,假使字段超多,扫描多间多,效用低,项目中倡导使用某八个非null唯意气风发的字段,平日是主键

  4、修改表

  有时候大家会对表格实行字段等音讯的改良,平淡无奇的改变内容有:

  加上贰个字段   sql> alter table 表名 add (字段名 字段类型卡塔尔(قطر‎;

    改善一个字段的长度
  sql> alter table 表名 modify (字段名 字段类型State of Qatar;

  改进字段名  
  sql> alter table 表名  
rename  column 旧字段名 to 
新字段名;

  改进字段的等级次序(供给该字段下并未有数据卡塔尔国   sql> alter table 表名 modify (字段名 字段类型卡塔尔(قطر‎;

  除去四个字段   sql>
alter table 表名 drop column 字段名;

  修正表的名字
  sql>rename 旧表名 to    新表名;

那么在多表查询中只要查询出来的结果有再一次项,平日会对大家须求的结酚酞致苦恼,为了幸免这种笛Carl集的产出,能够加上有效的总是条件,即使有n张表,那么连接条件起码为n-1个。

[O冠道DESportage BY <排序的列名> [ASC或DESC]]

总结公司有微微个不另行的机构

  5、删除表

  sql> drop table 表名;

二、交叉连接:

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列,并加多属性

新葡京32450网址 6

group by前边还是能够跟多列表示多列分组,在多列分组时放前边的事情发生前分组。

group by 列名,列名

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

select count(distinct deptno) from emp;

二、表的多少管理:增加和删除改查

在oracle中笛Carl集也许有对应的求解,称为交叉连接cross join,示比如下:

新葡京32450网址 7

总计有回扣的工作者人数

  1、改革日期的私下认可格式

  暗许格式是“dd-mon-yy”,即“日-月-年”,而大家中华的习于旧贯是“年-月-日”,所以能够行使sql> alter
session set nls_date_format = 
‘yyyy-mm-dd’;来纠正日期的格式;也足以应用to_date(卡塔尔(قطر‎函数来传播自定义格式的日子。注意:那只是一时改革,数据库重启后会复苏为默许,如要改进要求修正注册表。

Example:

select d.dname,e.ename,d.deptno,e.deptno from dept d cross join emp e;

新葡京32450网址 8新葡京32450网址 9新葡京32450网址 10

 emp表14条数据,dept表4条数据,14×4=56刚刚等于笛Carl集的结果。

having

having 子句用于节制分组展现结果,其只得和group
by一齐连用。在where中并没有主意直接行使聚合函数,即sum
avg等无法运用,所以援引了having,在having中得以选择这几个函数。

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

展示薪给低于二〇〇三的部门号和它的平均薪酬

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

浮现部门人数超越4的单位

新葡京32450网址 11

select count(comm) from emp;

  2、插入数据:insert into

  SQL> INSERT INTO
表名[(column [, column…])] VALUES(value [, value…]);

  具备字段都插入数据 
  比方咱们在学员表中插入一条数据:在插入数据在此以前,先改正私下认可的日期格式:sql>
alter session set nls_date_format =
‘yyyy-mm-dd’;然后在实施插入语句:sql> insert into student
values(‘二零一五015332′,’fzz’,’男’,’一九九八-11-11’State of Qatar;

  插入部分字段
  sql> INSERT INTO student(column1,
column2, column3…) VALUES (value1, value2, value3…);
  如在student表中只插入学号和出华诞期:sql> insert into student(sid,birthday)values(‘二零一五015001’,’1995-9-22’卡塔尔国;

  插入空值   填写数据的地点用null替代就可以。

三、自然连接:

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 升降;

前面的为首要排序,前面包车型的士为次顶级排序。

新葡京32450网址 12

新葡京32450网址 13

注:蒙受自身与投机相比较的景观下,无法用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用来对筛选的结果举行排序

静心:这一个四个行函数,不总结NULL值

  3、删除数据:delete from…where

  delete清空表格
  
语法:sql> delete table 表名;
  表达:删除全部记录,表结构还在,写日记,能够过来删除的多寡,速度慢。
  ps:删除前应用savepoint可还原删除的数码:savepoint a; –成立保存点
;DELETE FROM student; rollback to a;  –恢复生机到保存点

  truncate清空表格
  语法:sql >truncate table
表名;
  表明:与delete的分别在于:不带where删除,不写日记,删除后无法上升,但速度快。

  剔除表格
  语法:drop table 表名;
  表达:删除表的结议和数据,即表格自个儿也会被剔除。不恐怕复苏。

  去除内定记录
  
语法:sql> delete from
student where 删除条件

 概念:natural join是大器晚成种特别的等值连接,将表中负有相像名称的列举行自动匹配,无需再加等值条件。

复杂查询

(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(enameState of Qatar 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);

显示报酬超过平均职员和工人信息

新葡京32450网址 14

(2)多表查询

多表查询是指三个和多个以上的表大概是视图的查询,在骨子里行使中,当查问单个表无法满足须求时,平常接受多表查询。如:呈现sales部门地方和其工作者的全名,这种情状下须求选取到(dept表和emp表State of Qatar。

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

注:在行使多表查询的时候各样表能够安装外号,假若表钦定了外号,那么语句中有着语句必需使用外号,而无法再利用实际表名。且在写属性的时候假使属性为内部多少个表特有的属性则无需写别称,尽管是两个表都有则必得钦赐是哪一个表的哪些属特性式为:表名.属性名。

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;

新葡京32450网址 15

左外连接

左外连接与内一而再的差别是:设置左外连接的时候设置了主表和附表,主表在前,附表在后。内接连是将多少个表相配的地点输出出来,而左外连接则是主表全写,附表风度翩翩意气风发对应,附表有则拉长,未有不写。

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表作为辅表进行链接

新葡京32450网址 16

右外连接

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

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;

询问各类机关下的职工的姓名,薪金

新葡京32450网址 17

全连接

全连接是在等值连接的功底上校左表和右表的未匹配数据都拉长,使用的首要字为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;

新葡京32450网址 18

(3)子查询

子查询是指嵌套在别的sql语句中的select语句,也叫嵌套查询。sql语句试行顺序为从右到左执行,所以在实行查询时会先实行左边的子查询后举行主查询。

子查询分为单行子查询和多行子查询,单行子查询是指再次来到风华正茂行数据的子查询语句,多行子查询是指再次来到多行数据的查询语句。子查询还足以分为多列子查询、多行子查询、多列多行子查询。

在拓宽子查询时假如中间查询不回来任何记录,则外界条件中字段DEPTNO与NULL比较长久为假,也就是说外界查询不回来任何结果。

总结为:

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

多行子查询是指再次来到单列多行数据,都以指向单列来讲的

多列子查询则是指查询重临多少个列数据的子查询语句

单行子查询

where deptno = (单行数值State of Qatar

多行子查询

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’);

询问出发卖部(SALESState of Qatar下边包车型客车职工姓名,工作,薪给

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;

询问每一个部门江苏中华南理艺术学院程公司资高于该部门平均报酬的职工人数

新葡京32450网址 19

多行子查询

在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直接拘系的雇员的真名以致工资

新葡京32450网址 20

多列子查询

多列子查询和多行子查询同意气风发,只是利用多列子查询的时候会有多列举办相配。

哪些查询与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’);

新葡京32450网址 21

(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   补集
重返第三个查询检查出的笔录减去第三个查询检索出来的记录之后剩余的记录

只顾:当使用集结操作的时候,查询所重临的列数以至列的种类必须同盟,列名能够分歧。

职工业总会薪资,平均工资,四舍五入,保留小数点后0位

  4、修改数据:update set

  语法:sql> update 表名 set
column1=value1,column2=value2… [where 修改法规];

Example:

select ename,deptno,dname from emp natural join dept;

新葡京32450网址 22

(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          NUMBE中华V(4卡塔尔(قطر‎                 表示雇员编号,是独步一时编号

ename          VAECHALacrosse2(10卡塔尔国           表示雇员姓名

job                VARCHAPRADO2(9卡塔尔国             表示专门的学业职位

mgr              NUMBE中华V(4卡塔尔(قطر‎                 表示一个雇员的首长编号

hiredate       DATE                            表示雇佣日期

sal               NUMBEENCORE(7,2)               表示年薪,薪资

comm          NUMBE巴博斯 SL级(7,2卡塔尔               表示奖金,只怕叫做酬薪

deptno         NUMBEENVISION(2卡塔尔(قطر‎                 部门编号

select sum(salState of Qatar “总薪俸”,round(avg(sal卡塔尔国,0卡塔尔 “平均报酬”

三、表查询

  表查询是很要紧的操作,大家单独拿出去计算。为了便于说明,大家利用scott客户下的emp表和dept表举行询问深入分析。

能够看到dname是dept表中的列,条件中并从未参预emp.deptno=dept.deptno,不过结果如故正确,那正是自然连接的效果,非常注意的是要求列名近似才足以。

from emp;

  1、scott.emp和scott.dept

  • emp表是雇员表,是employee的缩写:表结构如下:

  新葡京32450网址 23

  • dept表是机关表,是department的缩写:表结构如下:

  新葡京32450网址 24

四、内连接:

查询职员和工人表中最高报酬,最低工资

  2、轻巧询问

  • 规格查询:where
    语法:select * from 表名 where 查询条件;

    1 --查询上级为7788的员工 
    2 select * from emp where mgr=7788;
    
  • 混淆查询:like
    %:表示0到多个字符;
    _:表示任性单个字符

    1 --显示首字母为S的员工姓名和工资  
    2 select ename, sal from emp where ename like 'S%';   
    3 
    4 --显示第三个字母为O的员工信息  
    5 select * from emp where ename like '___O%';  
    
  • 总括查询:使用函数查询
    常用的聚合函数包蕴: count(字段卡塔尔、
    sum(字段卡塔尔国、 avg(字段卡塔尔、 min(字段卡塔尔国、 max(字段卡塔尔(قطر‎。

     1 --查询emp的总记录数
     2 select count(*)  from emp;
     3 
     4 --查询emp的平均工资
     5 select avg(sal) from emp;
     6 
     7 --查询emp的最低工资
     8 select min(sal) from emp;
     9 
    10 --查询emp的最高工资
    11 select max(sal) from emp;
    
  • 使用算数表明式查询&函数nvl
    算数表明式+、-、*、/都能够行使;
    函数NVL( string1,
    replace_withState of Qatar。它的作用是假使string1为NULL,则NVL函数再次来到replace_with的值,否则再次来到string1的值,如若多个参数都为NULL
    ,则赶回NULL。

     1 --查询所有员工的年工资  
     2 select sal * 13 + nvl(comm * 13, 0) "年工资", ename from emp;  
     3       
     4 --查询工资高于3000的员工  
     5 select * from emp where sal > 3000;  
     6       
     7 --查询1982年1月1号以后入职的员工  
     8 select * from emp where hiredate > '1-1月-1982';  
     9       
    10 --显示工资在2000-2500的员工  
    11 select * from emp where sal > 2000 and sal < 2500;  
    12 select * from emp where sal between 2001 and 2499;  
    
  • 动用逻辑操作符
    我们常说的逻辑运算符即:NOT、 AND、
    OEnclave,即非、且、或。

    --查询工资高于500或者岗位是MANAGER的员工,同时姓名首写字母是大写的J  
    select * from emp where (sal > 500 or job = 'MANAGER') and ename like 'J%';  
    
  • 运用操作符is null
    isnull是查询值为Null的字段;isnotnull是查询值非空的字段。

    1 --显示没有上级的人的员工信息  
    2 select * from emp where mgr is null;  
    
  • 使用操作符in
    in(value1,value2…卡塔尔(قطر‎表示在钦定值中询问。

    1 --显示empno为123,345,800的员工信息  
    2 select * from emp where empno = 123 or empno = 345 or empno = 800;  
    3 select * from emp where empno in (123, 345, 800);  
    
  • 排序查询:oder by
    asc表示升序(从小到大),暗许是asc;desc表示降序(从大到小)。

    --按照工资从低到高的顺序显示员工的信息  
    select * from emp order by sal ASC;  
    
    --按照工资从高到低的顺序显示员工信息  
    select * from emp order by sal DESC;  
    
    --按照部门号升序而员工工资降序排列  
    select * from emp order by deptno ASC, sal DESC;  
    
    --按照部门号升序而入职时间降序排列  
    select * from emp order by deptno ASC, hiredate DESC;  
    

概念:inner join只回去满意条件的相相称的查询结果,inner可省略。

select max(sal卡塔尔 “最高级技术员资”,min(sal卡塔尔国 “最低薪酬”

  3、复杂查询

  分组查询(2种)

  1. 分组查询之数据分组:使用聚合函数
    即大家地点说的总括查询:

    1 --显示所有员工中的最高工资和最低工资  
    2 select max(sal) 最高工资, min(sal) 最低工资 from emp;   
    3   
    4 --显示所有员工中的最高工资和最低工资并显示它们的名字  
    5 select ename,sal from emp where sal = (select max(sal) from emp) or sal = (select min(sal) from emp);  
    6   
    7 --显示工资高于平均工资的员工信息  
    8 select * from emp where sal > (select avg(sal) from emp);  
    
  2. 分组查询之group by和having
    group
    by语法能够依据钦命的字段对查询结果举行分组总括,最后得到三个分组汇总表。
    出于where关键字在采纳会集函数时不能够利用,所以在集结函数中增长了having来起到测验查询结果是或不是切合条件的功能。

     1 --显示每个部门的平均工资和最高工资  
     2 select deptno, avg(sal) 平均工资, max(sal) 最高工资 from emp group by deptno order by deptno;  
     3   
     4 --显示每个部门的每种岗位的平均工资和最高工资  
     5 select deptno, job, avg(sal) 平均工资, max(sal) 最高工资 from emp group by deptno, job;  
     6   
     7 --显示每个部门的每种岗位的平均工资和最高工资,并按部门号升序,平均工资降序排列  
     8 select deptno, job, avg(sal) 平均工资, max(sal) 最高工资 from emp group by deptno, job order by deptno ASC,avg(sal) DESC;  
     9   
    10 --显示平均工资低于2000的部门号和他的平均工资  
    11 select deptno, avg(sal) from emp group by deptno having avg(sal) < 2000;  
    

  **分组查询总计**

  1 分组函数只可以出以往甄选列表、having、order by子句中(不能够冒出在where中卡塔尔 

  2 意气风发旦在select语句中并且含有有group by, having, order by 那么它们的依次是group by, having, order by 

  3 在接受列中风姿洒脱经有列、表达式和分组函数,那么这么些列和表明式必得有一个冒出在group by 子句中,否则就能够出错。 如SELECT deptno, AVG(sal卡塔尔(قطر‎, MAX(sal卡塔尔国 FROM emp GROUP by deptno HAVING AVG(sal卡塔尔 < 二零零二;
这里deptno就自然要出今后group by 中。

  多表查询(3种)   多表查询分为:交叉连接(笛卡尔积);内连接;外接连(外接连又分为左外和右外)。
  交叉连接是两把若是要询问两表的插花或未交的部分,就各自选拔内接二连三查询和外接连查询,如下图所示:
  新葡京32450网址 25

  1. 多表查询之交叉连接
    交叉连接不带WHERE子句,它回到被连接的七个表全数数据行的笛Carl积,重临结果集结中的数据行数等于第贰个表中相符查询条件的数目行数乘以第叁个表中切合查询条件的数码行数。

    1 --交叉连接查询:emp和dept的笛卡尔积
    2 select * from emp , dept;
    
  2. 多表查询之内连接
    卓越的对接运算,使用比较运算符进行标准化查询。使用表A inner join 表B 表示,可粗略。

    1 --显示员工名,员工工资以及所在部门名  
    2 select ename, sal, dname from emp,dept where emp.deptno = dept.deptno;  
    3   
    4 --显示部门号为10的部门名、员工名和工资  
    5 select dname,ename,sal from emp e, dept d where e.deptno = d.deptno and d.deptno = 10;  
    
  3. 多表查询之外连接
    左外连接:表A left  [outer]  join 表B on;左向外对接的结果集满含内定的左表的有着行,而不只是联接列所相配的行。借使表A的某行在右表中未有相称行,则在相关联的结果集行中表B的具有选取列表列均为空值。(如上海教室)
    右外连接:表A right 
    [outer]  join 表B
    on;右向外过渡是左向外对接的反向联接。将再次来到右表的有所行。若是表B的某行在左表中并未有相称行,则将为表A重临空值。
    全外连接: 表A full  [outer]  join 表B on;完整外部联接重返左表A和右表B中的全数行。当某行在另四个表中未有相配行时,则另贰个表的拈轻怕重列表列包罗空值。假若表之间有相称行,则全体结果集行李包裹蕴基表的数据值。

    1 --左外:查询员工姓名和的部门地址
    2 select   e.ename,d.loc   from  emp e  left   join  dept d  on   e.deptno=d.deptno;
    3 --右外:查询员工姓名和部门地址
    4 select   e.ename,d.loc   from  emp e  right  join  dept d  on   e.deptno=d.deptno;
    5 --全外连接:查询员工姓名和部门地址
    6 select   e.ename,d.loc   from  emp e  full   join  dept d  on   e.deptno=d.deptno;
    

  子查询(5种)   当贰个查询是另三个查询的口径时,称之为子查询。

  1. 子查询之单行子查询:=

    1 --显示与SMITH同一部门的所有员工  
    2 select * from emp where deptno = (select deptno from emp where ename='SMITH') and ename != 'SMITH'; 
    
  2. 子查询之多行子查询:any&all

     1 --查询和部门10工作相同的雇员的名字、岗位、工资、部门号  
     2 select ename,job,sal,deptno from emp where job in(select job from emp where deptno = 10) and deptno != 10;  
     3 select ename,job,sal,deptno from emp where job = any(select job from emp where deptno = 10) and deptno != 10;  
     4    
     5 --显示工资比部门30的所有员工的工资还要高的员工的姓名、工资和部门号  
     6 select ename,sal,deptno from emp where sal > (select max(sal) from emp where deptno=30);  
     7 select ename,sal,deptno from emp where sal > all(select sal from emp where deptno=30);  
     8     
     9 --显示工资比部门30的任意一个员工的工资还要高的员工的姓名、工资和部门号  
    10 select ename,sal,deptno from emp where sal > (select min(sal) from emp where deptno=30);  
    11 select ename,sal,deptno from emp where sal > any(select sal from emp where deptno=30);  

**补充**:带有ANY或ALL谓词的子查询

子查询**单列多行值**时可以使用ANY或ALL谓词,使用ANY或ALL谓词时则必**须同时使用比较运算符**,其语义为(总结来说就是all是所有数据、any是任意数据):  
*![](https://images2017.cnblogs.com/blog/1250367/201801/1250367-20180121184512349-789048877.png)*  
事实上,用聚合函数来实现子查询通常比直接用ANY或ALL查询效率要高,ANY与ALL与聚合函数的对应关系如下所示:  
*![](https://images2017.cnblogs.com/blog/1250367/201801/1250367-20180121184611537-2046211915.png)*  
  1. 多列子查询

    1 --查询与SMITH的部门和岗位完全相同的所有员工
    2 select e1.* from emp e1,(select deptno,job from emp where ename='SMITH') e2 where e1.deptno = e2.deptno and e1.job = e2.job and ename != 'SMITH';
    
  2. 在from子句中使用子查询
    在此供给验证的当在from子句中使用子查询时,该子查询会被视作二个视图来对待,因而称为内嵌视图,当在from子句中使用子查询时,必得给子查询钦赐小名,注:子查询的外号无法加as。即给表取小名的时候,不能加as;不过给列取别称,是足以加as的。

    1 --显示高于自己部门平均工资的员工信息  
    2 select e1.*,e2.avgsal from emp e1,(select deptno,avg(sal) avgsal from emp group by deptno) e2 where e1.deptno = e2.deptno and e1.sal > e2.avgsal;
    
  3.  使用查询结果制造新表

    1 --使用查询结果创建新表emp2
    2 create table  emp2(eid,ename,sal) as select empno,ename,sal from emp;

这样emp的查询结果包括字段、字段类型甚至是数据都会导入到新表emp2中。

  分页查询(3种)

  1. 使用rownum分页
    率先步:查询:rownum是Oracle对查询结果进行逻辑排序的序号,比方语句:
    --查询emp及rownum信息:
    select e.*,rownum rn from(select * from emp)e ;

即:select \* from
emp是子查询,并命名为e,然后rownum就会自动子查询的结果进行排序,这些序号信息就是rownum,并以rn字段显示出来。如图:  
第二步:分页:使用where利用rn信息来进行分页,即使用比较符&lt;、&gt;、≥、=、≤或区间表达式between
m and n,即区间\[m,n\]来分页。  


    1 --比较符查询:查询roenum前10条数据
    2 select e.*,rownum rn from(select * from emp)e where rownum<=10;
    3 --区间查询case1:查询rownum在[5,10]的数据
    4 select e.*,rownum rn from(select * from emp)e where rn between 5 and 6;
    5 --区间查询case2:查询rownum在[2,6]的数据
    6 select * from 
    7 (select e.*,rownum rn from(select * from emp)e where rownum<=10) 
    8 where rn>=5;

**说明**:注意区间查询的两种格式,由于rownum在一个sql语句中只能使用一次,所以不能使用where
rownum&lt;=10 and rownum&gt;=5。所以case1中我们使用between and
来表示;而在case2中,相当于两次分页,首先我们查询了rownum&lt;=10的数据,是第一次分页,然后再把这些数据作为第二次分页的子查询,第二次分页我们将rn&gt;=5就最终得到\[5,10\]之间的分页数据。  
ps:区间查询中case1格式简单明了,但查询速度却不及case2的两次分页查询速度。  
注意1:我们上面的查询例子是查询所有字段信息即select
\*查询。例如case2只需要查询部分字段,如只需要查询员工名称ename和员工薪资sal,我们只需要修改**最里层**的查询即可:  


    1 --查询rownum为[5,10]的员工姓名和工资
    2 select * from 
    3 (select e.*,rownum rn from(select ename,sal from emp)e where rownum<=10) 
    4 where rn>=5;

注意2:有时候我们需要排序后再分页,这是也只需要修改最里层的查询即可:  


    1 --查询rownum为[5,10]的员工姓名和薪资,并按照薪资降序排序
    2 select * from 
    3 (select e.*,rownum rn from(select ename,sal from emp order by sal desc)e where rownum<=10) 
    4 where rn>=5;

ps:其他情况也是如此,修改最里层的查询即可。
  1. 使用rowid分页
    rowid差距与rownum,rownum是逻辑序号,是基于每一遍的查询结果机关编号的,而rowid是情理序号,oracle每一遍插入一条记下时都会转移唯风流倜傥的rowid序号,也正是记录的id。总计来讲正是rownum是依据自动生成的,而rowid是读取表中已部分唯生机勃勃序号。
    rowid分页查询相比较复杂,但功用超级高:
    1 --查询表t_xiaoxi按cid降序排序的第9981-9999条记录,表中有70000条记录
    2 select * from t_xiaoxi where rowid in(
    3     select rid from 
    4          (select rownum rn,rid from 
    5                (select rowid rid,cid from t_xiaoxi order by cid desc)
    6          where rownum<10000)
    7 where rn >9980) order by cid desc;

执行时间:0.03秒。  
可以看出rowid是层层嵌套的,比较复杂,一般情况下不推荐使用,除非是查询数据很大,用时较长的情况。
  1. 利用分析函数row_number()分页
    频率相当低,不引入应用。
    1 --查询表t_xiaoxi按cid降序排序的第9981-9999条记录,表中有70000条记录
    2 select * from 
    3     (select t.*,row_number() over over(order by cid desc) rk
    4         from t_xiaoxi t) where rk<10000 and rk>9980;

执行时间:1.01秒。

  分页查询总括
   
分页查询的三种艺术中,相仿的分页查询中,rowid查询速度最快(0.03s),但书写很复杂;rownum查询次之(0.1s),书写方便;row_number(卡塔尔国函数查询最慢(1.01s)。所以平时大家选用rownum进行分页查询就可以。

*  合并查询(4种)
  *有的时候候在实际应用中,为了统生龙活虎八个select语句的查询结果,基本上能用集结操作符号union、union
all,intersect、minus来进展统生龙活虎。

  1. union查询(并集)
    1 --查询薪水大于2500和job为MANAGER的员工
    2 select ename,sal,job from emp where sal > 2500 union select ename,sal,job from emp where job='MANAGER'; 

即,两条查询记录进行合并,重复的记录会自动去掉。
  1. union all查询(并集)
    也是并集查询,与union查询的区分在于重新的记录不会去掉。

  2. intersect查询(交集)

    1 --查询薪水大于2500并且job为MANAGER的员工
    2 select ename,sal,job from emp where sal > 2500 intersect select ename,sal,job from emp where job='MANAGER'; 
    
  3. minus查询(差集)

    1 --查询薪水大于2500但是job不为MANAGER的员工
    2 select ename,sal,job from emp where sal > 2500 minus select ename,sal,job from emp where job='MANAGER';
    

  能够观望,其实有的合并查询能够利用逻辑运算符落成,如并集便是or、交集正是and。那怎么还要选取那个根本字呢?原因在于统风流浪漫查询的进度是比选拔逻辑运算符查询速度要快的,所以会集查询一时候可以对查询进行优化。

(1)第生龙活虎种用using子句创建连接

from emp;

四、数据库的始建

Example:

select ename,deptno,dname from emp join dept using(deptno);

新葡京32450网址 26

入职最初,入职最迟职员和工人

 1、使用Oracle提供的工具创造(精晓)

  使用工具dbca(database configuration
assistant:数据库配置帮手)。

  1. 启动dbca
    找到dbca的Logo,运维就能够;也许接受命令行运行:win+r
    输入cmd,然后在指令行分界面输入dbca就能够。

  2. 接下来遵照提醒操作就可以(百度有好多课程,这里就不再描述)

(2)第两种:用on子句创设连接:

select max(hiredate卡塔尔 “最迟入职时间”,min(hiredate卡塔尔 “最初入职时间”

 2、手动创设(领悟)

  手动创造很艰巨,并且便于出错,具体可参谋:、或活动百度

Example:

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

新葡京32450网址 27

from emp;

那2种都得以查询到我们想要的结果,那么那2种语句的意义在哪个地方吗,刚才有讲过自然连接会将列名相像作为基准,

多行函数:count/sum/avg/max/min

假设此刻2张表中有五个列值对应相等,那么oracle会自动将那些列都作为典型,而大家有十分大概率只要求中间的生龙活虎有些

按机关求出该机构平均薪水,且平均薪水取整数,接收截断

作为标准,那么那时候就用到了那2种语句,也是它们存在的市场股票总值。

select deptno “部门编号”,trunc(avg(salState of Qatar,0卡塔尔国 “部门平均薪金”

五、外连接

from emp

概念:分为左外连接,右外连接,完全(外)连接,它们都回去知足结果的连天,差异的是左外连接还回去不满意左表的局地数据,

group by deptno;

平等的右外接连还回到不满足右表的部分数据,完全连接则还回来不满足条件的某个数据,也正是说,左外连接重临左表的成套多少

(继续卡塔尔查询部门平均薪水大于2004元的机构

右表的满足条件数据,右外连接再次来到右表的万事数据左表满意条件的多少,完全连接再次回到左右表的所有的事数量。

select deptno “部门编号”,trunc(avg(sal卡塔尔(قطر‎,0卡塔尔(قطر‎ “部门平均薪酬”

Example:

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

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

新葡京32450网址 28

from emp

右外连接能够当作是将左外连接的表换了个岗位,查询结果黄金时代致,所以那边只讲左外连接,dept表中有deptno=40的机关,可是

group by deptno

在emp表中并从未deptno=40的职工,假若这里未有用左外连接,那么就不会显得最终一行,如下图:

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

新葡京32450网址 29

having trunc(avg(sal),0) > 2000;

可以看出并不曾deptno=40的那生机勃勃行。

(继续卡塔尔按机关平均报酬降序排列

Example2:

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

新葡京32450网址 30

select deptno “部门编号”,trunc(avg(sal卡塔尔国,0卡塔尔(قطر‎ “部门平均薪俸”

一同外接连能够视作是将左外连接和右外连接举行了多少个“合体”,不满意的结果也会显得出来,能够看出来左外连接用到的景况最多。

from emp

除此以外,左外连接和右外连接还大概有另生龙活虎种表达的格局,使用(+卡塔尔(قطر‎操作符,

group by deptno

Expression:左外连接:select table1.column,table2.column from table t1,table2 t2 where t1.column = t2.column(+卡塔尔(قطر‎

having trunc(avg(sal),0) > 2000

右外连接:select table1.column,table2.column from table1 t1,table2 t2 where t1.column(+卡塔尔(قطر‎ = t2.column

order by 2 desc;

Example:

select e.ename,d.dname from dept d,emp e where e.deptno(+) = d.deptno;

select e.ename,d.dname from emp e ,dept d where e.deptno(+)=d.deptno;

新葡京32450网址 31

除10号部门外,查询部门平均报酬大于二零零三元的单位,情势生龙活虎【having
deptno<>10】

所查结果相同,这里自个儿故意将标准的地方写反,表达并非(+卡塔尔的岗位固定,而是要基于标准中的表的名字的义务判别,那也是外接连查询的大器晚成种写法,注意此处是where子句不再是on子句。

select deptno,avg(sal)

 六、自连接:

from emp

概念:自连接非常轻巧领会,就是同一张表,将它看做是2张大同小异的表,当中一张表的列能够看作另贰个表的询问条件。

group by deptno

Example:在emp表中查询工作者要好的上司。

select e.ename,m.ename from emp e,emp m where e.mgr=m.empno;

新葡京32450网址 32

having deptno<>10;

剖判:emp e表中的mgr是她的上级的empno,而他的上司的empno也在emp那张表中,那就将后生可畏律张表看成了2张表。

除10号部门外,查询部门平均工资大于二零零四元的部门,格局二【where
deptno<>10】

 七、set运算符:

select deptno,avg(sal)

概念:特地用来统风度翩翩多条select语句的结果,分为二种。

from emp

新葡京32450网址 33左图代表2个查询select1和select2,个中B是2个查询的双重数据。

where deptno<>10

1、union操作符:用于获取2个查询的并集,并且会将当中的双重数据去掉,最终按第一列的结果实行升序排序。

group by deptno;

 用图来代表便是     select1 union select2 = A+B+C

提倡

2、union all操作符:相近用于获取2个查询的并集,但不会将再也的多少去掉,也不交易会开排序。

呈现部门平均薪水的最大值

用图来表示正是   select1 union all select2 = A+B+B+C

select max(avg(sal卡塔尔卡塔尔(قطر‎ “部门平均工资的最大值”

3、intersect操作符:同于获得2个查询的交集 ,并且会以第一列的结果升序排序   

from emp

用图表示正是:select1 intersect select2 =B

group by deptno;

4、minus操作符:用于获取在率先此中的结果,但不在第四个中的结果,并且会以第一列的结果升序排序

观念:展现部门平均薪金的最大值和该部门编号?

用图表示便是    select1 minus select2 = A

select max(avg(salState of QatarState of Qatar “部门平均薪水的最大值”,deptno “部门编号”

Example:

from emp

先依照emp表和dept表做2张新表,emp01取部门号为10和20的,emp02取部门号为20和30的:

create table emp01asselect * from emp where deptno in(10,20); 

create table emp02asselect * from emp where deptno in(20,30);

group by deptno;

数码如下:

新葡京32450网址 34

新葡京32450网址 35

错误

union操作符的选拔:

select deptno,empno,ename from emp01unionselect deptno,empno,ename from emp02;

新葡京32450网址 36

group by 子句的内幕:

emp表中唯有10,20,30机关的数码,所以union现在应该适逢其会是emp表的数额。

1)在select子句中冒出的非多行函数的具有列,【必需】出今后group by子句中

union all操作符的应用:

select deptno,empno,ename from emp01union allselect deptno,empno,ename from emp02;

新葡京32450网址 37

2)在group by子句中冒出的具备列,【可现身可不现】在select子句中

union all现在会将再度的也显示出来,所以部门号为20的职员和工人会呈现2次

where和having的区别:

intersect操作符的采纳:

select deptno,empno,ename from emp01intersectselect deptno,empno,ename from emp02;

新葡京32450网址 38

where:

intersect现在会将再一次数据展现,所以只展现部门号为20 的数额。

1)行过滤器

minus操作符的选择:

select deptno,empno,ename from emp01minusselect deptno,empno,ename from emp02;

新葡京32450网址 39

2)针对原始的笔录

minus只呈现第二个查询的结果,把个中和第二有的查询重复的去除,所以只展现10号部门不交易会示20号部门的音讯。

 

 

到此甘休,多表查询任何总计收尾,有任何提出足以在凡尘批评。

                                                    2018-08-10 
  16:57:07

 

3)跟在from后面

4)where可省

5)先执行

having:

1)组过滤器

2)针对分组后的笔录

3)跟在group by后面

4)having可省

5)后执行

oracle中回顾语法:

1)select子句—–必须

2)from子句——-必需,不知写什么表了,就写dual

3)where子句——可选

4)group by子句—可选

5)having子句—–可选

6)order by 子句–可选,假如现身列名,外号,表明式,字段

————————————————————————————-多表查询

职工表emp和单位表dept的笛Carl集(笛Carl集表=列数之和,行数之积,笛Carl集表内中稍加数据是不适合须求的)

select emp.ename,dept.dname

from emp,dept;

选拔等值连接/内连接(只好动用=号),展现职员和工人的号子,姓名,部门名,使用表小名简化

select emp.empno,emp.ename,dept.dname,dept.deptno

from emp,dept

where emp.deptno = dept.deptno;

行使非等值连接(无法应用=号,其余符号能够,举例:>=,<=,<>,betwen
and等),显示职员和工人的号码,姓名,月收入,工资等级

select e.empno,e.ename,e.sal,s.grade

from emp e,salgrade s

where e.sal between s.losal and s.hisal;

内接连查询:只好查询出相符条件的笔录

留下评论

网站地图xml地图