oracle基本查詢操作子查詢用法實例分析

更新時間：2020年02月20日 10:26:01 作者：懷素真

這篇文章主要介紹了oracle基本查詢操作子查詢用法,結合實例形式分析了oracle數(shù)據(jù)庫子查詢相關概念、原理、語法、使用技巧與操作注意事項,需要的朋友可以參考下

本文實例講述了oracle基本查詢操作子查詢用法。分享給大家供大家參考，具體如下：

一、子查詢語法

SELECT select_list
FROM table
WHERE expr operator (SELECT select_list FROM table);

子查詢在主查詢之前一次執(zhí)行完成。
子查詢的結果被主查詢使用。

select ename from emp where sal > (select sal from emp where ename='SCOTT');

(*注意：子查詢要包含在括號內，將子查詢放在比較條件的右側。單行操作符對應單行子查詢，多行操作符對應多行子查詢。)

單行子查詢，只返回一行，使用單行比較符(> = < >= <= != <>)

--子查詢中使用組函數(shù)
select ename,sal from emp where sal=(select min(sal) from emp);
--子查詢中的having子句
--首先執(zhí)行子查詢
--向主查詢中的having子句返回結果
select deptno, min(sal)
 from emp
 group by deptno
having min(sal) > (select min(sal) from emp);

多行子查詢，返回多行，使用多行比較符(IN ANY ALL)

--查詢比部門10里任意一個人工資高的員工信息
select ename, sal
 from emp
 where sal > any (select sal from emp where deptno = 10);
--查詢比部門20里所有人工資高的員工信息
select ename, sal
 from emp
 where sal > all (select sal from emp where deptno = 20);
--查詢不是老板的員工信息
select ename from emp where empno not in(select mgr from emp);

二、集合運算

并集
UNION運算符返回兩個集合去掉重復元素后的所有記錄。
UNION ALL 返回兩個集合的所有記錄，包括重復的。
交集
INTERSECT 運算符返回同時屬于兩個集合的記錄

--返回工資在500-1000和900-1200的員工信息
select ename, sal
from emp
where sal between 500 and 1000
intersect
select ename, sal
from emp
where sal between 900 and 1200;

差集

MINUS 返回屬于第一個集合，但不屬于第二個集合的記錄。

--返回工資屬于500-1000，但不屬于900-1200的員工信息
select ename, sal
from emp
where sal between 500 and 1000
minus
select ename, sal
from emp
where sal between 900 and 1200;

集合使用的注意事項

1、select語句中參數(shù)類型和個數(shù)保持一致。
2、可以使用括號改變集合執(zhí)行的順序。
3、如果有order by，必須放到最后一句查詢語句后。
4、集合運算采用第一個語句的表頭作為表頭。

三、數(shù)據(jù)操作語言

插入數(shù)據(jù)

INSERT INTO table [(column [,column...])]
VALUES (value [,value...]);

insert into dept(deptno,dname,loc) values(50,'test','test');

從其他表中拷貝數(shù)據(jù)

insert into dept(deptno, dname, loc)
select 60, dname, loc from dept where deptno = 10;

更新數(shù)據(jù)

UPDATE table
SET column=value [, column=value, ...]
[WHERE codition]

--更新一條數(shù)據(jù)
update emp set sal=sal+100 where empno=7369;

--update使用子查詢
update emp
set sal = (select max(sal) from emp)
where empno = (select empno from emp where sal = (select min(sal) from emp));

刪除數(shù)據(jù)

DELETE [FROM] table
[WHERE condition];

--刪除一條數(shù)據(jù)
delete from dept where deptno=60;

delete和truncate

1、都是刪除表中的數(shù)據(jù)。
2、delete操作可以rollback，可以閃回。
3、delete可能產(chǎn)生碎片，并且不釋放空間。
4、truncate清空表。

四、數(shù)據(jù)庫事務

數(shù)據(jù)庫事務由以下的部分組成：
1、一個或多個DML語句
2、一個DDL數(shù)據(jù)定義語句
3、一個DCL數(shù)據(jù)控制語句

以第一個DML語句的執(zhí)行作為開始
以下面的其中之一作為結束：
顯示結束：commit rollback
隱式結束(自動提交)：DDL語句，DCL語句，exit(事務正常退出)
隱式回滾(系統(tǒng)異常終了)：關閉窗口，死機，掉電

commit和rollback語句的優(yōu)點
1、確保數(shù)據(jù)完整性。
2、數(shù)據(jù)改變被提交之前預覽。
3、將邏輯上相關的操作分組。

回滾到保留點
使用savepoint語句在當前事務中創(chuàng)建保存點。
使用rollback to savepoint語句回滾到創(chuàng)建的保存點。

update emp set sal=sal+100 where empno=7369;
savepoint update_empno7369;
delete from emp where empno=7369;
rollback to update_empno7369;

五、創(chuàng)建和管理表

常見的數(shù)據(jù)庫對象
如下：
表        基本的數(shù)據(jù)存儲集合，由行和列組成。
視圖     從表中抽出的邏輯上相關的數(shù)據(jù)集合。
序列     提供有規(guī)律的數(shù)值。
索引     提高查詢的效率。
同義詞給對象起別名。

創(chuàng)建表

CREATE TABLE [schema.]table (column datatype [DEFAULT expr][, ...]);

create table test(
id number(12),
name varchar2(32));

通過子查詢創(chuàng)建表

CREATE TABLE table [(column, column...)]
AS subquery;

create table test2 as select empno,ename from emp where sal>1000;

修改表

--添加列
ALTER TABLE table
ADD (column datatype [DEFAULT expr] [, column datatype] ...);

--添加info列
alter table test add (info varchar2(256) default '');

--修改列
ALTER TABLE table
MODIFY (column datatype [DEFAULT expr] [, column datatype] ...);

--修改info列
alter table test modify (info varchar2(64) default '');

--刪除列
ALTER TABLE table
DROP column (column);

--刪除info列
alter table test drop column info;

--修改列名
ALTER TABLE table
rename column old_column_name to new_column_name;

--修改name列名
alter table test rename column name to name2;

刪除表

1、數(shù)據(jù)和結構都被刪除
2、所有正在運行的相關事物被提交
3、所有相關索引被刪除
4、DROP TABLE語句不能回滾，但是可以閃回。