Oracle數(shù)據(jù)庫(kù) 入門(mén)教程
版權(quán)聲明：本文為博主原創(chuàng)文章，遵循 CC 4.0 by-sa 版權(quán)協(xié)議，轉(zhuǎn)載請(qǐng)附上原文出處鏈接和本聲明。
本文鏈接：https://blog.csdn.net/qq_42589612/article/details/80943957
Oracle數(shù)據(jù)庫(kù)
1.1 數(shù)據(jù)庫(kù)環(huán)境安裝
1.Oracle數(shù)據(jù)庫(kù)產(chǎn)品是免費(fèi)的，我們可以從Oracle的官方網(wǎng)站(http://www.oracle.com)下載到程序安裝包，Oracle在Windows下的安裝非常方便，安裝開(kāi)始后，一直點(diǎn)擊安裝程序的“下一步”即可。
下載Oracle10g后，解壓到一個(gè)文件夾下，單擊“setup.exe”文件即可啟動(dòng)安裝界面。。填寫(xiě)全局?jǐn)?shù)據(jù)庫(kù)名，以及管理員的密碼。全局?jǐn)?shù)據(jù)庫(kù)名是數(shù)據(jù)庫(kù)在服務(wù)器網(wǎng)絡(luò)中的唯一標(biāo)識(shí)。
數(shù)據(jù)庫(kù)創(chuàng)建完畢后，需要設(shè)置數(shù)據(jù)庫(kù)的默認(rèn)用戶。Oracle中為管理員預(yù)置了兩個(gè)用戶分別是SYS和SYSTEM。同時(shí)Oracle為程序測(cè)試提供了一個(gè)普通用戶scott，口令管理中，可以對(duì)數(shù)據(jù)庫(kù)用戶設(shè)置密碼，設(shè)置是否鎖定。 Oracle客戶端使用用戶名和密碼登錄Oracle系統(tǒng)后才能對(duì)數(shù)據(jù)庫(kù)操作。默認(rèn)的用戶中，SYS和SYSTEM用戶是沒(méi)有鎖定的，安裝成功后可以直接使用，SCOTT用戶默認(rèn)為鎖定狀態(tài)，因此不能直接使用，需要把SCOTT用戶設(shè)定為非鎖定狀態(tài)才能正常使用。
Oracle數(shù)據(jù)庫(kù)中，默認(rèn)情況下，所有系統(tǒng)的數(shù)據(jù)，SQL關(guān)鍵字等都是大寫(xiě)的，在操作過(guò)程中，Oracle會(huì)自動(dòng)把這些內(nèi)容轉(zhuǎn)換為大寫(xiě)，因此用戶操作時(shí)不需考慮大小寫(xiě)問(wèn)題，一般情況下，為了良好的程序風(fēng)格，程序中建議關(guān)鍵字用大寫(xiě)，非關(guān)鍵字可以使用小寫(xiě)。
4.該工具是Oracle系統(tǒng)默認(rèn)安裝下，自帶的一個(gè)客戶端工具。在Windows命令行中輸入“sqlplusw”命令，就能夠啟動(dòng)該工具了
，輸入：sqlplus輸入密碼后就可以直接使用了 Oracle數(shù)據(jù)庫(kù)的端口號(hào)是：1521
1.2 數(shù)據(jù)庫(kù)的卸載
1、我的電腦右擊《管理》選擇《服務(wù)和應(yīng)用程序》->《服務(wù)》找到《Oracle》右擊選擇停止。
2、控制面板卸載程序
3、去安裝Oracle的目錄下面把Oracle的文件夾刪除
4、用CMD打開(kāi)regedit注冊(cè)表
刪除HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\Oracle文件夾
刪除HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\Eventlog\Application\Oracle文件夾
刪除HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\Oracle文件夾
刪除HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\Eventlog\Application\Oracle文件夾。
刪除HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Oracle文件夾
刪除HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application\Oracle的文件夾。
5、以上操作完成后即表示Oracle已經(jīng)完全卸載。
1.3 Oracle數(shù)據(jù)庫(kù)---scott用戶
sqlplus無(wú)圖形界面,如果想要打開(kāi)文件，使用 ed 文件路徑 默認(rèn)打開(kāi)的是以.sql結(jié)尾的文件 如果想要執(zhí)行某個(gè)文件。使用** @** 文件路徑 如果打開(kāi)的是.sql文件后綴可以不寫(xiě).
1、scott是默認(rèn)用戶，需要導(dǎo)入才能使用
2、導(dǎo)入scott的用戶 (默認(rèn)scott用戶是鎖定狀態(tài))
@D:\oracle\app\oracle\product\11.2.0\server\rdbms\admin\scott
3、解鎖scott用戶 alter user scott account unlock;
4、設(shè)置scott密碼生效 alter User scott identified by tiger;
1.3.1 切換用戶
登錄sys用戶的時(shí)候需要以sysdba的身份登錄 sys/root as sysdba;
切換用戶 conn 用戶名/用戶密碼 注意切換到sys用戶 conn sys/root as sysdba;
顯示當(dāng)前用戶 show user;
查看用戶下所有的表
select * from tab;
1.3.2 sqlplus中的顯示格式化
設(shè)置每頁(yè)顯示的條數(shù) set pagesize 30;
設(shè)置每行顯示的寬度 set linesize 300;
設(shè)置某個(gè)字段顯示的寬度 字符串 col 字段名 for a10;
數(shù)值 col 字段名 for 9999;（幾位數(shù)就填幾個(gè)9）
1.4 Oracle數(shù)據(jù)庫(kù)的用戶管理
1、用戶的管理
創(chuàng)建用戶：create user 用戶名 identified by 密碼;
2、用戶的授(包含兩個(gè)權(quán)限 CONNECT,RESOUCE)
grant CONNECT,RESOUCE to 用戶;
3、修改密碼
ALTER USER 用戶名 IDENTIFIED BY 新密碼
4、讓密碼失效：登陸之后需要立即修改密碼 ALETR USER 用戶名 PASSWORD EXPIRE ;
5、鎖定用戶 ALTER USER 用戶名 ACCOUNT LOCK;
6、解鎖用戶 ALTER USER 用戶名 ACCOUNT UNLOCK;
7、將A用戶名的操作權(quán)限授予其他用戶 ps:在A用戶下訪問(wèn)B 用戶名的表
GRANT SELECT,INSET,UPDATE ,DELETE ON 用戶名.表名 TO 其他用戶
8、回收權(quán)限
REVOKE CONNECT ,RESOURCE FROM 用戶名;
9、刪除用戶
DROP USER 用戶名 (CASCADE); 需要先刪除用戶名創(chuàng)建的內(nèi)容
sys管理員的默認(rèn)密碼為change_on_install
如果在一個(gè)用戶下訪問(wèn)其他用戶的表，需要加上模式名稱
ps: select * from scott.emp;
1.5 Oracle 數(shù)據(jù)庫(kù)數(shù)據(jù)定義語(yǔ)言Data Definition Language（DDL）
CHAR(length)
存儲(chǔ)固定長(zhǎng)度的字符串。參數(shù)length指定了長(zhǎng)度，如果存儲(chǔ)的字符串長(zhǎng)度小于length，用空格填充。默認(rèn)長(zhǎng)度是1，最長(zhǎng)不超過(guò)2000字節(jié)。
VARCHAR2(length)
存儲(chǔ)可變長(zhǎng)度的字符串。length指定了該字符串的最大長(zhǎng)度。默認(rèn)長(zhǎng)度是1，最長(zhǎng)不超過(guò)4000字符。
NUMBER(p，s)
既可以存儲(chǔ)浮點(diǎn)數(shù)，也可以存儲(chǔ)整數(shù)，p表示數(shù)字的最大位數(shù)（如果是小數(shù)包括整數(shù)部分和小數(shù)部分和小數(shù)點(diǎn)，p默認(rèn)是38為），s是指小數(shù)位數(shù)。
FlOAT/DOUBLE 小數(shù)型
INT/INTEGER 整數(shù)型
DATE 存儲(chǔ)日期和時(shí)間，存儲(chǔ)紀(jì)元、4位年、月、日、時(shí)、分、秒，存儲(chǔ)時(shí)間從公元前4712年1月1日到公元后4712年12月31日。
TIMESTAMP 不但存儲(chǔ)日期的年月日，時(shí)分秒，以及秒后6位，同時(shí)包含時(shí)區(qū)。
CLOB 存儲(chǔ)大的文本，比如存儲(chǔ)非結(jié)構(gòu)化的XML文檔
BLOB 存儲(chǔ)二進(jìn)制對(duì)象，如圖形、視頻、聲音等。
ALTER TABLE命令 創(chuàng)建表
ALTER TABLE 表名 ADD CONSTRAINT約束名約束內(nèi)容。
創(chuàng)建表和約束 ALTER TABLE SCORES
ADD CONSTRAINT CK_SCORES_TERM CHECK(TERM = 'S1' OR TERM ='S2') 只能在S1和S2中二選一，約束
1.6 Oracle 數(shù)據(jù)庫(kù) 數(shù)據(jù)操縱語(yǔ)言（DML）
1.6.1 INSERT 增加表
簡(jiǎn)單查詢 分組查詢
SELECT *|列名|表達(dá)式 FROM 表名 WHERE 條件 ORDER BY 列名
根據(jù)結(jié)果集創(chuàng)建表
CREATE TABLE 表名 AS SELECT語(yǔ)句
EX:INSERT INTO EMP VALUES(1000,'JACK','SALESMAN',7902,SYSDATE,2000,NULL,10);
插入指定的數(shù)據(jù)
INSERT INTO EMP (EMPNO,ENAME,SAL)VALUES(1001,'ROSE',3000);
修改
復(fù)制表結(jié)構(gòu)
如果只復(fù)制表結(jié)構(gòu)，只需使查詢的條件不成立（比如where 1=2），就不會(huì)查詢從出任何數(shù)據(jù)，從而復(fù)制一個(gè)表結(jié)構(gòu)。
EX： CREATE TABLE INFOS2 AS SELECT * FROM INFOS WHERE 1=2;
根據(jù)結(jié)果集創(chuàng)建表
INSERT INTO 表名(列名1，列名2……) VALUES (值1，值2……)
在Oracle中，日期是國(guó)際化的，不同的區(qū)域安裝的數(shù)據(jù)庫(kù)，默認(rèn)的日期格式不同，因此為了程序便于移植，日期的輸入要使用TO_DATE函數(shù)對(duì)日期格式化后輸入，采用格式化字符串對(duì)日期進(jìn)行格式化時(shí)，格式化字符串中字符不區(qū)分大小寫(xiě)，常見(jiàn)的格式化字符如下：
yyyy表示四位年份mm表示兩位月份，比如3月表示為03dd表示兩位日期hh24表示小時(shí)從0-23，hh12也表示小時(shí)從0-11。mi 表示分鐘ss表示秒
commit是把用戶操作（添加、刪除、修改操作）提交，只有提交操作后，數(shù)據(jù)才能真正更新到表中，否則其他用戶無(wú)法查詢到當(dāng)前用戶操作的結(jié)果。
INSERT向表中插入一個(gè)結(jié)果集
INSERT INTO INFOS2 SELECT * FROM INFOS;
INSERT向表中插入一個(gè)常量結(jié)果集
INSERT INTO INFOS
SELECT 's100106','盧俊義','男',23,5,
TO_DATE('2009-8-9 08:00:10','YYYY-MM-DD HH24:MI:SS'),
'青龍寺','1001'
FROM DUAL;
1.6.2 UPDATE 更新表
UPDATE操作
UPDATE 表名 SET 列名1=值，列名2=值…… WHERE 條件
DELETE FROM 表名稱 [WHERE 刪除條件] ;
1、刪除指定的員工(刪除jack)
DELETE FROM EMP WHERE ENAME='JACK';
2、刪除若干個(gè)員工(1001,7902,7844)
DELETE FROM EMP WHERE EMPNO IN (1001,7902,7844);
3、刪除工資最高的員工
DELETE FROM EMP WHERE SAL = (SELECT MAX(SAL) FROM EMP);
刪除語(yǔ)句中如果沒(méi)有WHERE子句,表示全部刪除(謹(jǐn)慎)
物理刪除：真刪
邏輯刪除：利用表中的字段做標(biāo)記。FLAG = 0
SELECT * FROM EMP WHERE FLAG = 1;
1.6.3 DELECT 刪除表
DELETE操作
DELETE FROM表名 WHERE 條件
EX：DELETE FROM INFOS WHERE STUID='s100103';
TRUNCAT命令（是一個(gè)DDL命令）可以把表中的所有數(shù)據(jù)一次性全部刪除
TRUNCATE TABLE 表名
刪除表
DROP TABLE 表名稱 [PURGE 清除緩存];
DROP TABLE STUDENT;
DROP TABLE STUDENT PURGE;
TRUNCATE和DELETE都能把表中的數(shù)據(jù)全部刪除，他們的區(qū)別是：
TRUNCATE是DDL命令，刪除的數(shù)據(jù)不能恢復(fù)；DELETE命令是DML命令，刪除后的數(shù)據(jù)可以通過(guò)日志文件恢復(fù)。如果一個(gè)表中數(shù)據(jù)記錄很多，TRUNCATE相對(duì)DELETE速度快。
由于TRUNCATE命令比較危險(xiǎn)，因此在實(shí)際開(kāi)發(fā)中，TRUNCATE命令慎用。
EX:1、將公司的最低工資改為平均工資
UPDATE EMP SET SAL = (SELECT AVG(SAL) FROM EMP) WHERE SAL = (SELECT MIN(SAL) FROM EMP);
2、將所有在81年入職的員工的入職日期改為今天，工資上漲20%
UPDATE EMP SET HIREDATE = SYSDATE,SAL = SAL*1.2 WHERE HIREDATE BETWEEN '01-1月-81' AND '31-12月-81';
修改語(yǔ)句在沒(méi)有WHERE子句的時(shí)候，表示修改表中所有的記錄
1.6.4 SELECT 查詢表
DISTINCT消除重復(fù)行（高級(jí)查詢）
SELECT DISTINCT DEPTNO FROM EMP;
1.7 Oracle 數(shù)據(jù)庫(kù)操作符
求余運(yùn)算只能借助函數(shù)
MOD(x,y)：返回x除以y的余數(shù)。
算術(shù)運(yùn)算
SELECT ENAME,SAL,(SAL*12+2000) FROM EMP WHERE SAL>2000;
=等于
大于
<>或者!=不等于
<=小于或者等于
<小于>=大于或者等于
邏輯運(yùn)算符有三個(gè)：AND（與）、OR（或）、NOT（非）
字符串連接操作符（||）
EX：SELECT (ENAME || 'is a ' || JOB) AS "Employee Details" 1
2 FROM EMP
3 WHERE SAL>2000;
Oracle中字符串可以用單引號(hào)，也可以用雙引號(hào)，在別名中存在空格時(shí)，必須用雙引號(hào)。在表名、列名時(shí)用雙引號(hào)。
NULL操作
如果某條記錄中有缺少的數(shù)據(jù)值，就是空值（NULL值）?？罩挡坏扔?或者空格，空值是指未賦值、未知或不可用的值。任何數(shù)據(jù)類型的列都可以包括NULL值，除非該列被定義為非空或者主鍵。
**在查詢條件中NULL值用IS NULL作條件，非NULL值用NOT IS NULL做條件。 **
IN 操作
EX：WHERE job = 'SALESMAN ' OR job = 'PRESIDENT ' OR job = 'ANALYST '
WHERE job IN ('SALESMAN', 'PRESIDENT', 'ANALYST')
對(duì)應(yīng)IN操作的還有NOT IN，用法一樣，結(jié)果相反。
BETWEEN...AND...
在WHERE子句中，可以使用BETWEEN操作符來(lái)查詢列值包含在指定區(qū)間內(nèi)的行。
EX：WHERE SAL>=1000 AND SAL<=2000
BWTWEEN操作所指定的范圍也包括邊界。
LIKE模糊查詢
在一些查詢時(shí)，可能把握不準(zhǔn)需要查詢的確切值，比如百度搜索時(shí)輸入關(guān)鍵字即可查詢出相關(guān)的結(jié)果，這種查詢稱為模糊查詢。模糊查詢使用LIKE關(guān)鍵字通過(guò)字符匹配檢索出所需要的數(shù)據(jù)行。字符匹配操作可以使用通配符“%”和“_”:
%：表示零個(gè)或者多個(gè)任意字符。_：代表一個(gè)任意字符。
1.8 Oracle 數(shù)據(jù)庫(kù)函數(shù)
大小寫(xiě)轉(zhuǎn)換：
upper(列|數(shù)據(jù))、lower(列|數(shù)據(jù))
通過(guò)輸入人名得到這個(gè)人的完整信息
在sqlplus中輸入某個(gè)值 使用 &標(biāo)記（了解）
SELECT * FROM EMP WHERE ENAME = &標(biāo)記;
SELECT * FROM EMP WHERE ENAME = upper('&name');
首字母大寫(xiě)：
initcap(列|數(shù)據(jù))
字符串長(zhǎng)度：ength(列)
字符串替換：replace(列|數(shù)據(jù),替換數(shù)據(jù),新數(shù)據(jù))
字符串截取:substr(列|數(shù)據(jù),開(kāi)始位置);substr(列|數(shù)據(jù),開(kāi)始位置,長(zhǎng)度)
四舍五入：round(列|數(shù)值 [保留小數(shù)位]);不寫(xiě)保留小數(shù)位則不保留
如果小數(shù)位為負(fù)數(shù),則從小數(shù)點(diǎn)左邊計(jì)算。
截取小數(shù)位：trunc(列|數(shù)值 [保留小數(shù)位])
直接截取小數(shù)位，默認(rèn)不保留小數(shù)位
如果是負(fù)數(shù)直接整數(shù)位為0
取模： 取余mod(列1|數(shù)值1,列2|數(shù)值2)
時(shí)間戳 獲取系統(tǒng)當(dāng)前的時(shí)間戳
SELECT SYSTIMESTAMP FROM DUAL;
獲取系統(tǒng)當(dāng)前的時(shí)間
SELECT SYSDATE FROM DUAL;
日期三種計(jì)算方式:
日期函數(shù)
1、日期+天數(shù) = 日期(n天后)
2、日期 -天數(shù) = 日期(n天前)
3、日期-日期 = 天數(shù)(時(shí)間間隔)
MONTHS_BETWEEN(日期1，日期2) 兩個(gè)日期間的相隔的月份
ADD_MONTHS(日期，月數(shù));在指定的日期上增加月數(shù)
LAST_DAY(日期)指定日期的最后一天
NEXT_DAY(日期，周幾);
查詢所有員工工作年限(月)
SELECT TRUNC((SYSDATE-HIREDATE)/30) year FROM EMP;
SELECT MONTHS_BETWEEN(SYSDATE,HIREDATE) FROM EMP;
計(jì)算畢業(yè)時(shí)間
SELECT SYSDATE+120 FROM DUAL;
SELECT ADD_MONTHS(SYSDATE,-4) FROM DUAL;
查詢?cè)碌?25號(hào)以后)入職的員工
SELECT * FROM EMP WHERE HIREDATE > LAST_DAY(HIREDATE)-5;
SELECT LAST_DAY(SYSDATE) FROM DUAL;
找到當(dāng)前日期的下一個(gè)星期幾的是哪一天(1表示星期日，7表示星期六)
SELECT NEXT_DAY(SYSDATE,1) FROM DUAL;
|
1.9 Oracle 數(shù)據(jù)庫(kù)轉(zhuǎn)換函數(shù)
|
TO_CHAR(列|日期|數(shù)字，轉(zhuǎn)換格式)
|
日期轉(zhuǎn)換成字符串 年(yyyy)月(mm)日(dd)時(shí)(hh24)分(mi)秒(ss);
EX:將系統(tǒng)當(dāng)前時(shí)間轉(zhuǎn)換成'yyyy-mm-dd'格式
SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd') FROM DUAL;
SELECT TO_CHAR(SYSDATE,'yyyy/mm/dd') FROM DUAL;
SELECT TO_CHAR(SAL)+10 FROM EMP;
SELECT '10' +10 from dual;
數(shù)字轉(zhuǎn)換成字符串 任意一位數(shù)字(9)、貨幣(L 本地貨幣)TO_CHAR
SELECT TO_CHAR(1234567876,'999,999,999,999') FROM DUAL;
SELECT TO_CHAR(1234567876,'L999,999,999,999') FROM DUAL;
TO_DATE(字符串,'時(shí)間格式');
oracle中默認(rèn)的時(shí)間格式 xx日x月xx年
將'2018-07-04'日期類型的屬性插入到EMP表
INSERT INTO EMP (EMPNO,ENAME,HIREDATE,SAL) VALUES(8888,'李四','04-7月-18',3000);
INSERT INTO EMP (EMPNO,ENAME,HIREDATE,SAL) VALUES(8888,'李四',TO_DATE('2018-07-04','yyyy-mm-dd'),3000);
TO_NUMBER(列|數(shù)值);
SELECT TO_NUMBER('10') FROM DUAL;
可以將字符串中全是數(shù)字的類型轉(zhuǎn)換為數(shù)字類型，oracle中如果是全是數(shù)字的字符串類型可以直接加減乘除，系統(tǒng)會(huì)默認(rèn)轉(zhuǎn)換成CHAR類型。如果該字符串中不僅僅只有數(shù)字的話 該TO_NUMBER 類型沒(méi)有任何作用。
2.0 Oracle 數(shù)據(jù)庫(kù)中的多表查詢
SELECT E.ENAME,E.HIREDATE,E.JOB,E.SAL,D.DNAME,D.DEPTNO,D.LOC
FROM EMP E,DEPT D
WHERE E.DEPTNO = D.DEPTNO;
查詢每個(gè)雇員的編號(hào)、姓名、職位、基本工資、部門(mén)名稱、部門(mén)位置
1、確定要使用的數(shù)據(jù)表
EMP:雇員的編號(hào)、姓名、職位、基本工資
DEPT:部門(mén)名稱、部門(mén)位置
2、確定數(shù)據(jù)表之間的關(guān)聯(lián)關(guān)系
EMP.DEPTNO = DEPT.DEPTNO;
內(nèi)連接----->等價(jià)連接
SELECT E.EMPNO,E.ENAME,E.JOB,E.SAL,D.DNAME,D.LOC
FROM EMP E,DEPT D
WHERE E.DEPTNO = D.DEPTNO;
一、查詢每個(gè)雇員編號(hào)、姓名、職位、基本工資、工資等級(jí)
(提示：emp中的sal在salgrade表中的losal和hisal之間)
1、確定要使用的數(shù)據(jù)表
EMP : 雇員編號(hào)、姓名、職位、基本工資
SALGRADE:工資等級(jí)
2、確定數(shù)據(jù)表之間的關(guān)聯(lián)關(guān)系
EMP.SAL BETWEEN LOSAL AND HISAL;
SELECT E.EMPNO,E.ENAME,E.JOB,E.SAL,S.GRADE
FROM EMP E,SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;
二、查詢每個(gè)雇員的編號(hào)、姓名、基本工資、部門(mén)名稱、工資等級(jí)
1、確定要使用的數(shù)據(jù)表
EMP:編號(hào)、姓名、基本工資
DEPT:部門(mén)名稱
SALGRADE:工資等級(jí)
2、確定數(shù)據(jù)表之間的關(guān)聯(lián)關(guān)系
E.DEPTNO = D.DEPTNO AND E.SAL BETWEEN S.LOSAL AND S.HISAL;
SELECT E.EMPNO,E.ENAME,E.SAL,D.DNAME,S.GRADE
FROM EMP E,DEPT D,SALGRADE S
WHERE E.DEPTNO = D.DEPTNO AND E.SAL BETWEEN S.LOSAL AND S.HISAL;
右連接---->（+）在左邊
SELECT E.ENAME,E.HIREDATE,E.JOB,E.SAL,D.DNAME,D.DEPTNO,D.LOC
FROM EMP E,DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO;
左連接---->（+）在右邊
SELECT E.ENAME,E.HIREDATE,E.JOB,E.SAL,D.DNAME,D.DEPTNO,D.LOC
FROM EMP E,DEPT D
WHERE E.DEPTNO = D.DEPTNO(+);
2.1 Oracle 數(shù)據(jù)庫(kù) 數(shù)據(jù)集合操作
數(shù)據(jù)集合操作主要用于數(shù)據(jù)結(jié)果的連接，就相當(dāng)于多個(gè)查詢結(jié)果連接成一個(gè)結(jié)果返回。但是要求必須多個(gè)查詢返回結(jié)果列的結(jié)構(gòu)必須一致
UNION
得到兩個(gè)結(jié)果合并到一起，但是會(huì)去除重復(fù)數(shù)據(jù)
EX:SELECT * FROM EMP UNION SELECT * FROM DEPT;
UNION ALL
得到兩個(gè)結(jié)果合并到一起，不會(huì)去除重復(fù)數(shù)據(jù),是所有表的集合，包含全部數(shù)據(jù)
EX:SELECT * FROM EMP UNION ALL SELECT * FROM DEPT;
INTERSECT
得到兩個(gè)結(jié)果的并集，是所有表的交集，相重復(fù)的部分
EX:SELECT * FROM EMP INTERSECT SELECT * FROM DEPT;
MINUS
得到第一個(gè)結(jié)果減去第二個(gè)結(jié)果的差集
EX:SELECT * FROM EMP MINUS SELECT * FROM DEPT;
2.2 Oracle 數(shù)據(jù)庫(kù) 計(jì)數(shù)函數(shù)
COUNT(*)
返回表中的數(shù)據(jù)個(gè)數(shù)
EX:SELECT COUNT(*) FROM EMP GROUP BY DEPTNO;
COUNT(字段)
返回不為null的數(shù)據(jù)個(gè)數(shù)
COUNT(DISTINCT 字段)
返回去除重復(fù)之后的數(shù)據(jù)個(gè)數(shù)
2.3 Oracle 數(shù)據(jù)庫(kù) 分組查詢
分組統(tǒng)計(jì)查詢的順序
④SELECT 分組字段 [別名],分組字段[別名],...
①FORM 表名稱 [別名]
②[WHERE 限定條件s]
③[GROUP BY 分組字段,分組字段2....]
⑤[ORDER BY 列名 [ASC|DESC],列名 [ASC|DESC],列名 [ASC|DESC]...]
EX：
1、按照職位分組，查詢每個(gè)職位的名稱、人數(shù)、平均工資
SELECT JOB,COUNT(*),AVG(SAL) FROM EMP GROUP BY JOB;
2、查詢每個(gè)部門(mén)編號(hào)，每個(gè)部門(mén)的人數(shù)，最高工資，最低工資
SELECT DEPTNO,COUNT(*),MAX(SAL),MIN(SAL) FROM EMP GROUP BY DEPTNO;
oracle中分組統(tǒng)計(jì)查詢的三點(diǎn)要求：
1、在沒(méi)有GROUP BY子句的時(shí)候，在使用統(tǒng)計(jì)函數(shù)的時(shí)候不能出現(xiàn)任何其他字段
SELECT sal,avg(sal) FROM EMP;錯(cuò)
2、在使用GROUP BY 子句的時(shí)候，只允許出現(xiàn)分組字段和統(tǒng)計(jì)函數(shù)
SELECT DEPTNO,AVG(SAL),ENAME FROM EMP GROUP BY DEPTNO;錯(cuò)
3、統(tǒng)計(jì)函數(shù)可以嵌套，但是嵌套之后，SELECT子句中不能出現(xiàn)其他字段。只能使用嵌套函數(shù)。分組字段也不能查詢。
SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO;
SELECT DEPTNO,MAX(AVG(SAL)) FROM EMP GROUP BY DEPTNO;錯(cuò)
HAVING子句與WHERE子句的區(qū)別
WHERE子句是在GROUP BY 子句之前執(zhí)行(不允許出現(xiàn)統(tǒng)計(jì)函數(shù))，
HAVING在GROUP BY 子句之后執(zhí)行
所有的分組查詢中的執(zhí)行順序：
⑤SELECT 分組字段 [別名],分組字段[別名],...
①FORM 表名稱 [別名]
②[WHERE 限定條件s]
③[GROUP BY 分組字段,分組字段2....]
④[HAVING 條件....]
⑥[ORDER BY 列名 [ASC|DESC],列名 [ASC|DESC],列名 [ASC|DESC]...]
2.4 Oracle 數(shù)據(jù)庫(kù) 子查詢操作符
子查詢
SELECT [DISTINCT] 分組字段[別名],...|統(tǒng)計(jì)函數(shù),(子查詢)
FROM 表名稱(子查詢)
[WHERE 過(guò)濾條件(s)] (子查詢)
[GROUP BY 分組字段,分組字段...]
HAVING 分組后的過(guò)濾條件
[ORDER BY 字段[ASC|DESC],..]
WHERE子句：返回單行單列、多行多列、單行多列
HAVING子句：返回單行單列，同時(shí)要使用統(tǒng)計(jì)函數(shù)
FROM 子句：返回多行多列
子查詢的操作符
1、IN操作
子查詢會(huì)返回多行單列的結(jié)果,IN操作符的含義在這個(gè)多行單列的結(jié)果中的其中某一個(gè)相
EX：SELECT * FROM EMP WHERE SAL IN(SELECT SAL FROM EMP
WHERE JOB = 'MANAGER');
2、NOT IN
與IN操作符含義相反,不能與NULL值比較
EX:SELECT *FROM EMP WHERE COMM NOT IN(SELECT COMM FROM EMP
WHERE COMM IS NULL);錯(cuò)
子查詢的操作符
3、ANY操作
=ANY 與 =IN操作完全一樣
EX:SELECT *FROM EMP WHERE SAL =ANY(SELECT SAL FROM EMP WHERE JOB = 'MANAGER');
4、>ANY 大于最小值
EX:SELECT *FROM EMP WHERE SAL >ANY(SELECT SAL FROM EMP WHERE JOB = 'MANAGER');
5、<ANY 小于最大值
EX:SELECT * FROM EMP WHERE SAL <ANY(
SELECT SAL FROM EMP WHERE JOB ='MANAGER');
6、=ALL不存在
7、>ALL 大于最大值
EX:SELECT *FROM EMP WHERE SAL >ALL(SELECT SAL FROM EMP WHERE JOB = 'MANAGER');
8、<ALL小于最小值
EX:SELECT *FROM EMP WHERE SAL <ALL(SELECT SAL FROM EMP WHERE JOB = 'MANAGER');
2.5 Oracle 數(shù)據(jù)庫(kù) 子查詢
1、在WHERE 子句中使用子查詢
EX:1、低于平均工資的員工信息
SELECT *FROM EMP WHERE SAL < (SELECT AVG(SAL) FROM EMP);
2、查詢公司最早入職的員工
1)查詢?nèi)肼毜娜掌?br>
SELECT MIN(HIREDATE) FROM EMP;
2)將查詢的日期作為WHERE子查詢的語(yǔ)句
SELECT *FROM EMP WHERE HIREDATE = (SELECT MIN(HIREDATE) FROM EMP) ;
3、查詢與WARD職位相同，工資相等的員工信息
1)查詢WARD的部門(mén)和工資
SELECT JOB,SAL FROM EMP WHERE ENAME = 'WARD';
2)將查詢的結(jié)果作為WHERE子查詢的語(yǔ)句
SELECT *FROM EMP WHERE (JOB,SAL) = (SELECT JOB,SAL，F(xiàn)ROM EMP WHERE ENAME = 'WARD')
AND ENAME <> 'WARD';
2、在HAVING子句中使用子查詢
EX:1、查詢高于平均工資的職位名稱、職位人數(shù)、平均工資
SELECT JOB,COUNT(EMPNO),AVG(SAL) FROM EMP GROUP BY JOB HAVING AVG(SAL) > (SELECT AVG(SAL) FROM EMP);
3、再FROM 子句中使用子查詢
EX:1、查詢每個(gè)部門(mén)名稱、位置、部門(mén)人數(shù)
SELECT DEPTNO,COUNT(EMPNO) FROM EMP GROUP BY DEPTNO;
SELECT D.DNAME,D.LOC,TEMP.COUNT
FROM DEPT D,(SELECT E.DEPTNO,COUNT(EMPNO) COUNT
FROM EMP E GROUP BY DEPTNO) TEMP WHERE D.DEPTNO = TEMP.DEPTNO(+);
4、在SELECT子句中使用子查詢(了解,一般不使用)
2、查詢每個(gè)員工的編號(hào)、姓名、職位、部門(mén)名稱
SELECT E.EMPNO,E.ENAME,E.JOB,D.DNAME FROM EMP E,DEPT D
WHERE E.DEPTNO = D.DEPTNO;
SELECT E.EMPNO,E.ENAME,E.JOB,
(SELECT D.DNAME FROM DEPT D WHERE E.DEPTNO = D.DEPTNO) DNAME FROM EMP E;
此查詢語(yǔ)句會(huì)出現(xiàn)'1+N' 的問(wèn)題
2.6 Oracle 數(shù)據(jù)庫(kù)表約束的修改
1、增加約束
ALTER TABLE 表名稱 ADD CONSTRAINT 約束名稱 約束類型(字段)
EX:將stu表中的sid添加主鍵約束
ALTER TABLE STU ADD CONSTRAINT PK_SID PRIMARY KEY(SID);
因?yàn)镹OT NULL 約束是在創(chuàng)建字段的時(shí)候就要加上,所有不能添加NOT NULL
約束。如果非要添加,只能通過(guò)[修改表結(jié)構(gòu)]來(lái)添加NOT NULL 約束。
2、刪除約束
ALTER TABLE 表名稱 DROP CONSTRAINT 約束名稱
EX:將stu表中的sid刪除主鍵約束
ALTER TABLE STU DROP CONSTRAINT PK_SID;
通常情況下，修改約束是不建議使用的， 也就是說(shuō)數(shù)據(jù)約束要在數(shù)據(jù)庫(kù)設(shè)計(jì)之初就應(yīng)該設(shè)計(jì)好。
2.7 Oracle數(shù)據(jù)庫(kù)修改表結(jié)構(gòu)
1.重命名表名：
ALTER TABLE 表名 RENAME 新表名;
EX: ALTER TABLE TEACHER RENAME TEACHERS;
2.向表中添加一列：
ALTER TABLE 表名 ADD 字段名 字段類型;
EX: ALTER TABLE TEACHERS ADD INFO TEXT;
3.刪除表中一列：
ALTER TABLE 表名 DROP COLUMN 列名 ；
EX: ALTER TABLE TEACHERS DROP COLUMN INFO;
4.修改表中一列的數(shù)據(jù)類型：
ALTER TABLE 表名 MODIFY 字段名 字段類型 ;
EX: ALTER TABLE TEACHERS MODIFY SAL DECIMAL(7,2);
5.重命名一列：
ALTER TABLE 表名 CHANGE COLUMN 舊字段名 新字段名 新字段類型
EX: ALTER TABLE TEACHERS CHANGE COLUMN NAME T_NAME VARCHAR(30);
2.8 Oracle 數(shù)據(jù)庫(kù) 事務(wù)
事務(wù)的特點(diǎn)
特性：ACID
1.原子性（A）: 事務(wù)其實(shí)就是執(zhí)行一組SQL語(yǔ)句，他們中只要有一條語(yǔ)句執(zhí)行失敗，整個(gè)事務(wù)都失敗。
2.一致性（C）：即在事務(wù)開(kāi)始之前和事務(wù)結(jié)束以后，數(shù)據(jù)庫(kù)的設(shè)定是一樣的。
3.隔離性（I）：當(dāng)使用事務(wù)對(duì)數(shù)據(jù)進(jìn)行操作時(shí)，我們另外的會(huì)話是無(wú)法對(duì)此條數(shù)據(jù)進(jìn)行操作的。我們的InnoDB使用的是數(shù)據(jù)行鎖。
4.持久性（D）：當(dāng)事務(wù)成功，數(shù)據(jù)將提交到數(shù)據(jù)庫(kù)，數(shù)據(jù)就被持久化了。所有的DDL語(yǔ)句默認(rèn)是自動(dòng)提交的，所有的DML 語(yǔ)句需要手工提交；
事務(wù)的特點(diǎn)
所有的關(guān)系型數(shù)據(jù)庫(kù)在操作的時(shí)候都應(yīng)該考慮到事務(wù)帶來(lái)的影響。
每一個(gè)用戶在操作oralce數(shù)據(jù)庫(kù)的是都會(huì)開(kāi)啟一個(gè)SEESION會(huì)話。
默認(rèn)情況下ORALCE數(shù)據(jù)庫(kù)的DML語(yǔ)句是手動(dòng)提交的。DDL語(yǔ)句是自動(dòng)提交
提交數(shù)據(jù)： commit;
回滾數(shù)據(jù)： rollback;
設(shè)置回滾點(diǎn) 也可以設(shè)置標(biāo)簽進(jìn)行回滾：也可以設(shè)置回滾點(diǎn) savepoint 名字;
回滾到回滾點(diǎn) rollback to 名字;
2.9 Oracle 數(shù)據(jù)庫(kù) 序列
CREATE SEQUENCE 序列名
[INCREMENT BY 步長(zhǎng)]
[START WITH 開(kāi)始值]
[MAXVALUE 最大值|NOMAXVALUE][MINVALUE 最小值|NOMINVALUE]
[CYCLE | NOCYCLE] 循環(huán)
[CACHE |NOCACHE] 緩存
序列提供了兩個(gè)函數(shù)
序列名.nextval; 當(dāng)前序列的下一個(gè)值
序列名.currval; 當(dāng)前序列的值
刪除序列：DROP SEQUENCE 序列名稱;
序列只能在INSERT語(yǔ)句中使用.不能在CREATE中使用(區(qū)別MySQL)
3.0 Oracle數(shù)據(jù)庫(kù) 數(shù)據(jù)編號(hào) ROWNUM
SELECT ROWNUM,ENAME FROM EMP;
SELECT ROWNUM,ENAME FROM EMP WHERE DEPTNO = 10;
ROWNUM是ORACLE中的數(shù)據(jù)偽列,是根據(jù)數(shù)據(jù)的順序自動(dòng)生成的，不是固定的
SELECT ROWNUM,ENAME FROM EMP ORDER BY SAL;
主要包含兩個(gè)作用
1、取出第一行數(shù)據(jù)
SELECT * FROM EMP WHERE ROWNUM = 1;
2、取出前N行數(shù)據(jù)
SELECT * FROM EMP WHERE ROWNUM < 10;
查詢emp表中前5條數(shù)據(jù)
SELECT * FROM EMP WHERE ROWNUM <= 5;
查詢emp表中前10條數(shù)據(jù)
SELECT * FROM EMP WHERE ROWNUM <= 10;
查詢emp表中6~10條數(shù)據(jù)
SELECT * FROM EMP WHERE ROWNUM BETWEEN 6 AND 10;錯(cuò)誤
查詢emp表中11~15條數(shù)據(jù)(第三頁(yè)currpage = 3,每一頁(yè)顯示5條數(shù)據(jù) pagesize = 5 )
SELECT * FROM (
SELECT ROWNUM RN,EMPNO,ENAME,JOB,HIREDATE,SAL,COMM,MGR,DEPTNO
FROM EMP
WHERE ROWNUM <= 15) TEMP currpagepagesize
WHERE TEMP.RN > 10;(currpage-1)pagesize
oracle分頁(yè)查詢公式
SELECT 列1,列2...
FROM (SELECT ROWNUM RN,列1,列2...
FROM 表名稱
WHERE RN<(pagesizecurrpage)) TEMP
WHERE TEMP.RN > (pagesize-1)currpage;
3.1 Oracle 數(shù)據(jù)庫(kù) 同義詞
如：如果要在其他用戶訪問(wèn)scott用戶的下的emp表,需要加上模式名稱
‘scott.emp’，而且是scott用戶授予了其他用的增刪改查的權(quán)限
如：SELECT SYSDATE FROM DUAL;
SELECT TNAME FROM TAB WHERE TNAME = 'DUAL';
通過(guò)查詢發(fā)現(xiàn)DUAL表是sys用戶下的表。按道理應(yīng)該sys.dual去訪問(wèn)
原因是因?yàn)閛racle給sys.dual創(chuàng)建了一個(gè)同義詞dual;
創(chuàng)建同義詞的語(yǔ)法：
CREATE [PUBLIC] SYNONYM 同義詞名稱 FOR 用戶名.表名;
EX:CREATE PUBLIC SYNONYM emp FOR scott.emp;
SELECT * FROM EMP;(此時(shí)的EMP就是剛剛創(chuàng)建的同義詞) 如果不加PUBLIC只能在創(chuàng)建的那個(gè)用戶下使用，如果加了PUBLIC則可以在所有的用戶下面使用。這個(gè)創(chuàng)建需要在管理員用戶下
刪除創(chuàng)建的同義詞
DROP SYNONYM emp;
3.2 Oracle 數(shù)據(jù)庫(kù) 視圖
視圖(VIEW)的基本概念就是將復(fù)雜的查詢進(jìn)行封裝
語(yǔ)法：創(chuàng)建視圖的語(yǔ)法
CREATE [OR REPLACE] VIEW 視圖名稱 AS 子查詢;
EX:CREATE VIEW myview AS SELECT * FROM EMP WHERE DEPTNO =30;
使用視圖：
EX：SELECT ENAME,JOB FROM myview;
創(chuàng)建的視圖會(huì)被存放在‘user_views’字典表中。
SELECT * FROM USER_VIEWS;
普通用戶需[授權(quán)]后才能創(chuàng)建視圖
默認(rèn)在scott用戶下無(wú)法創(chuàng)建視圖權(quán)限不足
授予scott創(chuàng)建視圖的權(quán)限
GRANT CREATE VIEW TO scott;
替換視圖：
CREATE OR REPLACE VIEW myview AS SELECT * FROM EMP WHERE DEPTNO =30;
DEPTNO =30;就是視圖的創(chuàng)建條件
如果視圖不存在則創(chuàng)建視圖，如果視圖已經(jīng)存在則替換視圖
修改視圖的創(chuàng)建條件
UPDATE MYVIEW SET deptno = 40 WHERE empno= 7521; 此時(shí)會(huì)改變?cè)碇械男畔ⅰ?br>
如果想要原表中的信息不被更改，需要加上關(guān)鍵字
執(zhí)行這個(gè)SQL語(yǔ)句會(huì)改變視圖的創(chuàng)建條件。為了保護(hù)視圖的創(chuàng)建條件不被
修改，可以加上'WITH CHECK OPTION'
EX:CREATE OR REPLACE VIEW myview AS SELECT * FROM EMP WHERE DEPTNO =30 WITH CHECK OPTION;
但是加上個(gè)條件只會(huì)讓創(chuàng)建條件不被修改，而其他字段可以修改
UPDATE MYVIEW SET sal = 99999 WHERE empno= 7521;
如果想要讓視圖的所有字段都不會(huì)被修改加上WITH READ ONLY
CREATE OR REPLACE VIEW myview AS SELECT * FROM EMP WHERE DEPTNO =30 WITH READ ONLY;
UPDATE MYVIEW SET sal = 99999 WHERE empno= 7521;
此時(shí)只會(huì)改變視圖中的顯示，并不會(huì)改變?cè)碇械娜魏涡畔ⅲ藭r(shí)值賦予了視圖中查看功能，但是修改，刪除等功能將不被允許
刪除創(chuàng)建的視圖
DROP VIEW myview;
3.3 Oracle 數(shù)據(jù)庫(kù) 數(shù)據(jù)的導(dǎo)入導(dǎo)出
導(dǎo)出：
1、創(chuàng)建導(dǎo)入文件的路徑(d:\backup)
2、通過(guò)命令行進(jìn)入文件路徑
3、exp命令
4、登陸用戶名
默認(rèn)導(dǎo)出的文件名為expdat.dmp
導(dǎo)入：
1、進(jìn)入到備份的文件夾
2、執(zhí)行imp命令
9.0 實(shí)際案例
1、查詢每個(gè)雇員編號(hào)、姓名、職位、基本工資、工資等級(jí)
(提示：emp中的sal在salgrade表中的losal和hisal之間)
SELECT EMPNO,ENAME,JOB,SAL,GRADE
FROM EMP,SALGRADE
WHERE SAL BETWEEN LOSAL AND HISAL;
2、查詢每個(gè)雇員的編號(hào)、姓名、基本工資、部門(mén)名稱、工資等級(jí)
SELECT EMPNO,ENAME,SAL,DNAME,GRADE
FROM EMP E,SALGRADE,DEPT D
WHERE E.DEPTNO =D.DEPTNO AND(SAL BETWEEN LOSAL AND HISAL);
1、查詢工資高于在30部門(mén)的所有員工的姓名、工資、部門(mén)名稱、部門(mén)人數(shù)
第一步：查詢部門(mén)30 的所有員工的工資(多行單列的結(jié)果)
SELECT SAL
FROM EMP
WHERE DEPTNO = 30;
第二步：查詢高于30部門(mén)的工資的員工姓名、工資(使用>ALL)
SELECT ENAME,SAL
FROM EMP
WHERE SAL >ALL (SELECT SAL
FROM EMP
WHERE DEPTNO = 30);
第三步：查詢部門(mén)名稱（多表查詢）;
SELECT E.ENAME,E.SAL,D.DNAME
FROM EMP E,DEPT D
WHERE E.SAL >ALL (SELECT SAL
FROM EMP
WHERE DEPTNO = 30)
AND D.DEPTNO = E.DEPTNO;
第四步：查詢部門(mén)人數(shù)
SELECT DEPTNO,COUNT(EMPNO)
FROM EMP
GROUP BY DEPTNO;
SELECT E.ENAME,E.SAL,D.DNAME,TEMP.COUNT
FROM EMP E,DEPT D,(SELECT DEPTNO,COUNT(EMPNO) COUNT
FROM EMP
GROUP BY DEPTNO
) TEMP
WHERE E.SAL >ALL (SELECT SAL
FROM EMP
WHERE DEPTNO = 30)
AND D.DEPTNO = E.DEPTNO
AND E.DEPTNO = TEMP.DEPTNO;
2、查詢工資比‘SMITH’或‘ALLEN’ 高的所有員工的編號(hào)、姓名、部門(mén)名稱、領(lǐng)導(dǎo)姓名、最高、低工資
第一步：查詢‘SMITH’或‘ALLEN’ 的工資
SELECT SAL
FROM EMP
WHERE ENAME IN('SMITH','ALLEN');
第二步：查詢高于SMITH’或‘ALLEN’ 員工姓名、編號(hào)(在WHERE子句中使用子查詢)
SELECT ENAME,EMPNO
FROM EMP
WHERE SAL >ANY(SELECT SAL
FROM EMP
WHERE ENAME IN('SMITH','ALLEN'))
AND ENAME NOT IN ('SMITH','ALLEN');
第三步：查詢領(lǐng)導(dǎo)姓名
SELECT E.ENAME,E.EMPNO,M.ENAME
FROM EMP E,EMP M
WHERE E.SAL >ANY(SELECT SAL
FROM EMP
WHERE ENAME IN('SMITH','ALLEN'))
AND E.ENAME NOT IN ('SMITH','ALLEN')
AND E.MGR = M.EMPNO(+);
第四步：查詢部門(mén)信息
SELECT E.ENAME,E.EMPNO,M.ENAME,D.DNAME
FROM EMP E,EMP M,DEPT D
WHERE E.SAL >ANY(SELECT SAL
FROM EMP
WHERE ENAME IN('SMITH','ALLEN'))
AND E.ENAME NOT IN ('SMITH','ALLEN')
AND E.MGR = M.EMPNO(+)
AND E.DEPTNO = D.DEPTNO;
第五步：查詢最高、底工資
SELECT E.ENAME,E.EMPNO,M.ENAME,D.DNAME,TEMP.MAX,TEMP.MIN,TEMP.AVG,TEMP.COUNT
FROM EMP E,EMP M,DEPT D,(
SELECT DEPTNO,MAX(SAL) MAX,MIN(SAL) MIN,AVG(SAL)AVG,COUNT(EMPNO) COUNT
FROM EMP
GROUP BY DEPTNO) TEMP
WHERE E.SAL >ANY(SELECT SAL
FROM EMP
WHERE ENAME IN('SMITH','ALLEN'))
AND E.ENAME NOT IN ('SMITH','ALLEN')
AND E.MGR = M.EMPNO(+)
AND E.DEPTNO = D.DEPTNO
AND TEMP.DEPTNO = E.DEPTNO;
</article>
