Oracle之查詢詳解

查詢是數(shù)據(jù)的一個重要操作。用戶發(fā)送查詢請求，經(jīng)編譯軟件變異成二進制文件供服務器查詢，后返回查詢結果集給用戶，查詢會產(chǎn)生一個虛擬表，看到的是表形式顯示的結果，但結果并不真正的存儲，每次執(zhí)行查詢只是從數(shù)據(jù)表中提取數(shù)據(jù)，并按照表的形式顯示出來。

成都網(wǎng)站設計、成都做網(wǎng)站服務團隊是一支充滿著熱情的團隊，執(zhí)著、敏銳、追求更好，是創(chuàng)新互聯(lián)的標準與要求，同時竭誠為客戶提供服務是我們的理念。創(chuàng)新互聯(lián)公司把每個網(wǎng)站當做一個產(chǎn)品來開發(fā)，精雕細琢，追求一名工匠心中的細致，我們更用心！

SELECT 列名

FGROM 表名

[WHERE 查詢條件表達式]

[GROUP BY 分組表達式]

[HAVING 分組查詢表達式]

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

group by 用于對查詢的結果分組統(tǒng)計，通過對group by后面的名字進行分組后輸出結果。

group by后面還可以跟多列表示 多列分組 ，在多列分組時放前面的優(yōu)先分組。

group by 列名,列名

having 子句用于限制分組顯示結果，其只能和group by一起連用。在where中沒有辦法直接使用聚合函數(shù)，即sum avg等無法使用，所以引用了having，在having中可以使用這些函數(shù)。

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

在Oracle中還可以設置多列排序

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

前面的為主要排序，后面的為次一級排序。

注：碰到自己與自己比較的情況下，不能用having，可以創(chuàng)建一個新列。

注：如果select語句同時包含group by,having,order by,按group by,having,order by排序

分組和聚合一起使用，目的是為了統(tǒng)計信息。

where是為了from服務的，只能跟 真實的字段 ，用來篩選from子句中指定的操作所產(chǎn)生的行

group by 用來分組where子句的輸出

having 用來從分組的結果中篩選行

order by用來對篩選的結果進行排序

（1）分組函數(shù)：max min?avg sum count

max表示該列的最大值，min表示該列的最小值，avg表示該列的平均值，sum表示該列的和，count表示該列的行數(shù)。

注：分組函數(shù)(max、min、avg、count、sum)只能出現(xiàn)在選擇列表中having子句、order by子句、不能出現(xiàn)在where子句和group by子句中。

（2）多表查詢

多表查詢是指兩個和兩個以上的表或者是視圖的查詢，在實際應用中，當查詢單個表不能滿足需求時，一般使用多表查詢。如：顯示sales部門位置和其員工的姓名，這種情況下需要使用到(dept表和emp表)。

多表查詢的連接一般可以分為：內(nèi)連接、左外連接、右外連接、全連接。

注：在使用多表查詢的時候每個表可以設置別名，如果表指定了別名，那么語句中所有語句必須使用別名，而不能再使用實際表名。且在寫屬性的時候如果屬性為其中一個表特有的屬性則不需要寫別名，如果是兩個表都有則必須指定是哪一個表的哪個屬性格式為：表名.屬性名。

select 列名 from 表1 別名,表2 別名...

注：e是emp的別名，d是dept的別名。

但如果對表進行了操作則需要設置別名，如：查詢每個部門中工資高于該部門平均工資的員工人數(shù)。在其中有一個avg表，這個表必須設置別名（提醒：如果僅有一個被修改的表，則可以不設置別名，但如果有多個表則必須設置別名）。

內(nèi)連接

內(nèi)連接通過使用比較運算符來使每個表的通用列中的值匹配來組成一個新表，即：把兩個表中間共有的那些行拿出來進行連接，如果某些行不是兩個表共有的，則不進行連接。

select

from 表1

inner join 表2

on 匹配條件

或

select

from 表1 表2

where匹配條件

左外連接

左外連接與內(nèi)連接的區(qū)別是：設置左外連接的時候設置了主表和附表，主表在前，附表在后。內(nèi)連接是將兩個表匹配的地方輸出出來，而左外連接則是主表全寫，附表一一對應，附表有則加上，沒有不寫。

select

from 表1

left join 表2

on 匹配條件

右外連接

右外連接和左外連接基本相同只是右外連接的主表寫在后邊。

select

from 表1

right join 表2

on 匹配條件

全連接

全連接是在等值連接的基礎上將左表和右表的未匹配數(shù)據(jù)都加上，使用的關鍵字為full outer join或者full join。

select

from 表1

full join 表2

on 匹配條件

自連接

還有一種特殊情況即自連接，在Oracle中一個表無法與自己進行比較，所以當需要自己表的兩個信息做比較的時候也需要使用連接來連接，即同一張表的連結查詢。

（3）子查詢

子查詢是指嵌套在其他sql語句中的select語句，也叫嵌套查詢。sql語句執(zhí)行順序為從右到左執(zhí)行，所以在執(zhí)行查詢時會先執(zhí)行左側的子查詢后進行主查詢。

子查詢分為單行子查詢和多行子查詢，單行子查詢是指返回一行數(shù)據(jù)的子查詢語句，多行子查詢是指返回多行數(shù)據(jù)的查詢語句。子查詢還可以分為多列子查詢、多行子查詢、多列多行子查詢。

在進行子查詢時如果內(nèi)部查詢不返回任何記錄，則外部條件中字段DEPTNO與NULL比較永遠為假，也就是說外部查詢不返回任何結果。

總結為：

單行子查詢是指子查詢只返回單列、單行數(shù)據(jù)

多行子查詢是指返回單列多行數(shù)據(jù)，都是針對單列而言的

多列子查詢則是指查詢返回多個列數(shù)據(jù)的子查詢語句

單行子查詢

where deptno = (單行數(shù)值)

多行子查詢

where deptno in ( 多行數(shù)值 )

多列子查詢：

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

多列多行子查詢

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

單行子查詢

在單行子查詢的外部查詢中可以使用=、、、=、=、等比較運算符。

內(nèi)部查詢返回的結果必須與外部查詢條件中字段(DEPTNO)相匹配。

多行子查詢

在WHERE子句中使用多行子查詢時，可以使用多行比較運算符（IN,ALL,ANY）。

IN：等于任何一個。

ALL：和子查詢返回的所有值比較。例如：salALL(1,2,3)等價于sal3，即大于所有。

ANY：和子查詢返回的任意一個值比較。例如：salANY(1,2,3)等價于sal1，即大于任意一個就可以。

注：ANY運算符必須與單行比較運算符結合使用,并且返回行只要匹配子查詢的任何一個結果即可。

多列子查詢

多列子查詢和多行子查詢相同，只是使用多列子查詢的時候會有多列進行匹配。

（4）集合運算

為了合并多個select語句的結果，可以使用集合操作符號union，union all，intersect，minus。

union：該操作符用于取得兩個結果集的并集。當使用該操作符時，會自動去掉結果集中重復行

union all：該操作與union相似，但是它不會取消重復行，而且不會排序

intersect：使用該操作符用于取得兩個結果集的交集

minus：使用該操作符用于取得兩個結果集的差集，它只會顯示存在第一個集合中，而不存在第二個集合中的數(shù)據(jù)

總結為集合運算就是將兩個或者多個結果集組合成一個結果集。

intersect ?交集 返回兩個查詢共有的記錄

union all ?并集 返回各個查詢的所有記錄，包括重復的記錄

union ? ? ?交集 返回各個查詢的所有記錄，不包括重復的記錄

MINUS ? 補集 返回第一個查詢檢查出的記錄減去第二個查詢檢索出來的記錄之后剩余的記錄

注意：當使用集合操作的時候，查詢所返回的列數(shù)以及列的類型必須匹配，列名可以不同。

（1）Distinct關鍵字

在Oracle中，可能出現(xiàn)若干相同的情況，那么可以用Distinct消除重復行

（2）多表查詢與單行子查詢可以實現(xiàn)相同的功能

查詢出銷售部（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.去掉低于平均工資的那些數(shù)據(jù)即可：

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

（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) ? ? ? ? ? ? ? ? 部門編號

ORACLE SQL語句查詢一個字段在另一表字段中有兩條或以上 的數(shù)據(jù)

1、創(chuàng)建兩張測試表，

create table test_student(student_id varchar2(20), student_name varchar2(20));

create table test_class(student_id varchar2(20), class_id varchar2(20));

2、插入測試數(shù)據(jù)

insert into test_student values(1001,'陳XX');

insert into test_student values(1002,'許XX');

insert into test_student values(1003,'張XX');

insert into test_student values(1004,'吳XX');

insert into test_class values (1001,'C01');

insert into test_class values (1001,'C02');

insert into test_class values (1002,'C01');

insert into test_class values (1003,'C01');

insert into test_class values (1004,'C01');

insert into test_class values (1004,'C02');

3、查詢表的記錄，select t.*, rowid from test_class t;

4、編寫sql，查詢出來test_student表中在test_class表中是多條而不是單條的記錄，可以看到1001、1004學生是有多條記錄的，

select t.student_id,

?count(1) a

from test_student t , test_class b

where t.student_id = b.student_id

group by t.student_id

having count(*)1，

Oracle查詢2張表差集、交集、并集

select 字段1 from 表1 minus select 字段1 from 表2

select 字段1 from 表1 intersect select 字段1 from 表2

select 字段1 from 表1 union all select 字段1 from 表2

oracle 子查詢中有兩個字段，怎么同時查出來

-- 先取并集,然后查詢student2是否兩條（根據(jù)id排序）

SELECT student FROM (SELECT student FROM A union SELECT student FROM B) a1,A a2 WHERE a2.student=a1.student and (SELECT count(*) FROM B GROUP BY student) = 2;

-- 或者

SELECT * FROM A a, B b WHERE a.student=b.student ORDER BY b.student HAVING COUNT(*)=2;

oracle union all的用法是什么？

union all 操作符用于合并兩個或多個 SELECT 語句的結果集，并且不做踢重操作。

用法舉例如下：

1、創(chuàng)建測試表，

create table test_uni_a(id varchar2(20),remark varchar2(20));

create table test_uni_b(id varchar2(20),remark varchar2(20));

2、插入測試數(shù)據(jù)；

insert into test_uni_a values('123', 'abc');

insert into test_uni_a values('456', 'efg');

insert into test_uni_b values('789', 'ABC');

insert into test_uni_b values('000', 'EFG');

commit;

3、查詢表中全量數(shù)據(jù)；select t.*, rowid from test_uni_a t;

4、編寫語句，使用UNION ALL將兩表聯(lián)合；

select * from test_uni_a a

union all

select * from test_uni_b b

擴展資料

1、UNION ALL操作符用于合并兩個或多個 SELECT 語句的結果集。UNION ALL內(nèi)部的 SELECT 語句必須擁有相同數(shù)量的列。列也必須擁有相似的數(shù)據(jù)類型。同時，每條 SELECT 語句中的列的順序必須相同。

2、UNION與UNION ALL區(qū)別

1）、Union：對兩個結果集進行并集操作，不包括重復行，同時進行默認規(guī)則的排序；

2）、Union All：對兩個結果集進行并集操作，包括重復行，不進行排序；

參考資料

Oracle官方文檔-UNION ALL

oracle中不用union如何獲取并集的結果？

oracle不同表之間使用union合并

有關聯(lián)關系的表，可以使用其他方式，沒有關聯(lián)關系，一定使用union

跨過oracle，使用程序查詢多次，然后放到一個集合里，這樣也可以達到同樣的效果