oracle11g講義第3天數(shù)據(jù)庫Database是按照數(shù)據(jù)結(jié)構(gòu)來組織、存儲和管理數(shù)據(jù)的

1、1Oracle 概述1.1常見數(shù)據(jù)庫數(shù)據(jù)庫（Database）是按照數(shù)據(jù)結(jié)構(gòu)來組織、和管理數(shù)據(jù)的倉庫。數(shù)據(jù)庫通常分為層次式數(shù)據(jù)庫、網(wǎng)絡(luò)式數(shù)據(jù)庫和關(guān)系式數(shù)據(jù)庫三種；不同的數(shù)據(jù)庫是按不同的數(shù)據(jù)結(jié)構(gòu)來聯(lián)系和組織的。將反映數(shù)據(jù)聯(lián)系的方法稱為數(shù)據(jù)模型。層次結(jié)構(gòu)模型實質(zhì)上是一種有根結(jié)點的定向有序樹，按照層次模型建立的數(shù)據(jù)庫系統(tǒng)稱為層次模型數(shù)據(jù)庫系統(tǒng)；按照網(wǎng)狀數(shù)據(jù)結(jié)構(gòu)建立的數(shù)據(jù)庫系統(tǒng)稱為網(wǎng)狀數(shù)據(jù)庫系統(tǒng)；關(guān)系式數(shù)據(jù)結(jié)構(gòu)把一些復(fù)雜的數(shù)據(jù)結(jié)構(gòu)歸結(jié)為簡單的二元關(guān)系(即二維表格形式)，由關(guān)系數(shù)據(jù)結(jié)構(gòu)組成的數(shù)據(jù)庫系統(tǒng)被稱為關(guān)系數(shù)據(jù)庫系統(tǒng)。數(shù)據(jù)庫管理系統(tǒng)(Database Management System)是一種和管理

2、數(shù)據(jù)庫的大型，用于建立、使用和數(shù)據(jù)庫，簡稱 DBMS。它對數(shù)據(jù)庫進行的管理和控制，以保證數(shù)據(jù)庫的安全性和完整性。用戶通過 DBMS數(shù)據(jù)庫中的數(shù)據(jù)。數(shù)據(jù)庫管理系統(tǒng)是數(shù)據(jù)庫系統(tǒng)的，是管理數(shù)據(jù)庫的。常見的關(guān)系型數(shù)據(jù)庫有：DB2,Sybase,Oracle,Acs,MS SQL Server.1.2Oracle 簡介Oracle 甲骨文公司是第一個跨整個產(chǎn)品線（數(shù)據(jù)庫、業(yè)務(wù)應(yīng)用應(yīng)用軟件開發(fā)與決策支持工具）開發(fā)和部署 100%基于互聯(lián)網(wǎng)的企業(yè)的公司。Oracle是世界領(lǐng)先的信息管理供應(yīng)商和世界第二大獨立公司。其主要的有：數(shù)據(jù)庫服務(wù)器：oracle(9i,10g/11g,12c),應(yīng)用服務(wù)器：WegLog

3、ic，GlassFish開發(fā)語言：Java開發(fā)集成環(huán)境：NetBeanoracle 數(shù)據(jù)庫是當(dāng)前最主流的數(shù)據(jù)庫之一。2Oracle 安裝與組成2.1安裝 oracle 11g 數(shù)據(jù)庫詳見oracle 11g 32 位安裝.docx2.2Oracle 11g 數(shù)據(jù)庫的組成Oracle 的整體架構(gòu)：上圖示；一般Oracle 數(shù)據(jù)庫管理系統(tǒng)由：實例和數(shù)據(jù)庫兩部分組成。1、數(shù)據(jù)庫是一系列物理文件的集合（數(shù)據(jù)文件，控制文件，聯(lián)機日志，參數(shù)文件等）；Oracle 數(shù)據(jù)庫由操作系統(tǒng)文件組成，這些文件也稱為數(shù)據(jù)庫文件，為數(shù)據(jù)庫信息提供實際物理區(qū)。Oracle 數(shù)據(jù)庫包括邏輯結(jié)構(gòu)和物理結(jié)構(gòu)。數(shù)據(jù)庫的物理結(jié)構(gòu)包含

4、數(shù)據(jù)庫中的一組操作系統(tǒng)文件。數(shù)據(jù)庫的邏輯結(jié)構(gòu)是指數(shù)據(jù)庫創(chuàng)建之后形成的邏輯概念之間的關(guān)系，如表、視圖、索引等對象。2、實例則是一組 Oracle進程/線程以及在服務(wù)器分配的共享內(nèi)存區(qū)。Oracle 可以創(chuàng)建多個 oracle 數(shù)據(jù)庫，一個 oracle 數(shù)據(jù)庫將又由實例和數(shù)據(jù)庫。如默認安裝時創(chuàng)建的 orcl 數(shù)據(jù)庫外還可再創(chuàng)建其它數(shù)據(jù)庫。創(chuàng)建的數(shù)據(jù)庫將在$oracleHome/oradata/數(shù)據(jù)庫名 目錄下以一個個的*.DBF 文件體現(xiàn)出來。2.3Oracle 11g 數(shù)據(jù)庫服務(wù)Oracle * VSS Writer Service - Oracle 卷拷貝寫入服務(wù)，VSS（VolumeSha

5、dow Copy Service）能夠讓基礎(chǔ)設(shè)備（比如磁盤，陣列等）創(chuàng)建高保真的時間點映像，即拷貝（shadow copy）。它可以在多卷或者單個卷上創(chuàng)建映射拷貝，同時不會影響到系統(tǒng)的系統(tǒng)能。(非必須啟動)OracleDBConsole* - Oracle 數(shù)據(jù)庫控制臺服務(wù)；在運行 EntriseManager(企業(yè)管理器 EM)的時候，需要啟動這個服務(wù)；此服務(wù)被默認設(shè)置為自動開機啟動的(非必須啟動)OracleJobScheduler* - Oracle 作業(yè)調(diào)度服務(wù)。此服務(wù)被默認設(shè)置為禁用狀態(tài)(非必須啟動)OracleMTSRecoveryService - 服務(wù)端控制。該服務(wù)允許數(shù)據(jù)庫充

6、當(dāng)一個微軟事務(wù)服務(wù)器、+對象和分布式環(huán)境下的事務(wù)的資源管理器。恢復(fù)、閃回需要開啟該服務(wù)(非必須啟動)OracleOraDb11g_home1ClrAgent - Oracle 數(shù)據(jù)庫.NET 擴展服務(wù)的一部分。(非必須啟動)OracleOraDb11g_home1TNSListener -器服務(wù)，服務(wù)只有在數(shù)據(jù)庫需要遠程或使用 SQL Developer 等工具的時候才需要，此服務(wù)被默認的設(shè)置為開機啟動(非必須啟動)OracleService* - 數(shù)據(jù)庫服務(wù)，是 Oracle服務(wù)該服務(wù)，是數(shù)據(jù)庫啟動的基礎(chǔ)， 只有該服務(wù)啟動，Oracle 數(shù)據(jù)庫才能正常操作。此服務(wù)被默認的設(shè)置為開機啟動。(必

7、須啟動)連接 Oracle3SQL Plus 連接3.1打開 SQL Plus：在上述界面中可以輸入用戶名，如在安裝時了的用戶 scott,口令為：tiger輸入語句查詢該用戶下的對象：另外；也可以直接在命令行中輸入 sqlplus scott/tiger 進入并登錄SQL Developer 連接3.2打開 SQLDeveloper；在出現(xiàn)界面的左邊右擊鼠標，新建連接：注意在上圖中；主機名：如果是本機的按照配置在網(wǎng)絡(luò)管理中的服務(wù)的配置設(shè)置，可以為localhost；如果是連接其它機器的數(shù)據(jù)庫則指定其 ip；SID：是指定數(shù)據(jù)庫服務(wù)器上的全局數(shù)據(jù)庫名稱，默認安裝的話一般是 orclPLSQL

8、Developer 連接3.3安裝 PLSQL Develper；見PLSQL Developer 安裝及.docxJdbc 連接3.41、在安裝目錄下找到 oracle 的驅(qū)動包；如下路徑可以找到 oracle 的驅(qū)動包：C:oracle11gproduct11.2.0dbhome_1jdbclibojdbc6.jar 到項目中進行連接測試；2、新建 java 項目測試連接；SQL Plus 設(shè)置與常用命令4顯示設(shè)置4.1- 設(shè)置每行顯示的最長字符數(shù)set linesize 120- 設(shè)置一頁顯示的行數(shù)set pagesize 20- 設(shè)置是否顯示一頁的數(shù)set feedback on/of

9、f- 打開或取消oracle自帶的輸出方法dbms_output，并輸出內(nèi)容set serveroutput on/off- 格式化列的內(nèi)容：將列名對應(yīng)的列的值格式化為四位數(shù)值長度4.2常用命令命令說明show all查看系統(tǒng)所有變量值show user顯示當(dāng)前連接用戶show error顯示錯誤desc 表名顯示表的結(jié)構(gòu)；如：desc emp/* */-多行注釋單行注釋/執(zhí)行緩沖區(qū)中的語句ed打開默認編輯器，Windows 系統(tǒng)中默認是 notepad.exe，把緩沖區(qū)中最后一條 SQL 語句調(diào)入 afiedt.buf 文件中進行編輯（如果提示沒有afiedt.buf 請使用管理員 打開 S

10、LQ Plus）；常用于語句比較長需要修改時。spool 文件地址spool 文件地址 append假脫機命令；將命令行的內(nèi)容（從設(shè)置后開始令行內(nèi)容到文本。添加 appendcol 表中對應(yīng)的列名 for 9999column 表中對應(yīng)的列名 format 9999【示例】- 表明將 empno 列名對應(yīng)的列值格式為 4 位長度的數(shù)值型col empno for 9999 - 格式化列的內(nèi)容：將列名對應(yīng)的列的值格式化為10位字母長度col 表中對應(yīng)的列名 for a10【示例】- 表明將ename列名對應(yīng)的列值格式為10位長度的字符型col ename for a105表空間表空間是數(shù)據(jù)庫中最

11、大的邏輯，Oracle 數(shù)據(jù)庫采用表空間將相關(guān)的邏輯組件組合在一起，一個 Oracle 數(shù)據(jù)庫至少包含一個表空間。每個表空間由一個或多個數(shù)據(jù)文件組成，一個數(shù)據(jù)文件只能與一個表空間相聯(lián)系。在每一個數(shù)據(jù)庫中都有一個名為 SYSTEM 的表空間，即系統(tǒng)表空間，該表空間是在創(chuàng)建數(shù)據(jù)庫或數(shù)據(jù)庫安裝時自動創(chuàng)建的，用于系統(tǒng)的數(shù)據(jù)字典表、程序單元、過程、函數(shù)、包和觸發(fā)器等。5.1表空間類型性表空間：一般保存表、視圖、過程和索引等的數(shù)據(jù)臨時性表空間：只用于保存系統(tǒng)中短期活動的數(shù)據(jù)撤銷表空間：用來幫助回退未提交的事務(wù)數(shù)據(jù)5.2操作與運用創(chuàng)建表空間【語法】CREATE TABLESPACE 表空間名spool of

12、f的意思是在原有的文本內(nèi)容上追加后續(xù)的命令行的內(nèi)容；需要注意的是所有的這些內(nèi)容都將在 spool off 之后才。如： spool d:.txtspool d:test.sql appendspool offclear screen 或者 host cls清屏exit退出 SQL Plus查詢表空間修改表空間【語法】ALTER TABLESPACE 表空間名ADD DATAFILE 文件路徑 SIZE 大小 AUTOEXTEND ON NEXT 大小 MAXSIZE 大小;【示例】ALTER TABLESPACE _ts ADD DATAFILE d:oracle_data02.DBF SIZ

13、E 5MAUTOEXTEND ON;-管理員角色查看表空間SELECT file_name,tablespace_name,bytes,autoextensible FROM dba_data_files WHERE tablespace_name=_TS;DATAFILE 數(shù)據(jù)文件路徑 SIZE 大小 AUTOEXTEND ON NEXT 大小 MAXSIZE 大小;【說明】里面內(nèi)容可選項；數(shù)據(jù)文件路徑中若包含目錄需要先創(chuàng)建SIZE 為初始表空間大小，為K 或者M AUTOEXTEND ON 是否自動擴展NEXT 為文件滿了后擴展大小MAXSIZE 為文件最大大小，值為數(shù)值或 UNLIMIT

14、ED（表示不限大?。臼纠?DATAFILE d:oracle_data01.dbf SIZE 10M AUTOEXTEND ON;CREATE TABLESPACE_ts刪除表空間6數(shù)據(jù)庫用戶6.1系統(tǒng)常見用戶用戶說明sys超級用戶，主要用來 系統(tǒng)信息和管理實例，以 SYSDBA 或 SYSOPER 角色登錄。密碼為在安裝時設(shè)置的管理口令，如一般設(shè)置為：orclsystem默認的系統(tǒng)管理員，擁有 DBA 權(quán)限，通常用來管理 Oracle 數(shù)據(jù)庫的用戶、權(quán)限和存儲，以 Normal 方式登錄。 為在安裝時設(shè)置的管理口令，如一般設(shè)置為：orclscott示范用戶，使用 users 表空間。一般

15、該用【語法】DROP TABLESPACE 表空間名;DROP TABLESPACE 表空間名 INCLUDING CONTENTS AND DATAFILES;【說明】第一個刪除語句只刪除表空間；第二個刪除語句則刪除表空間及數(shù)據(jù)文件【示例】DROP TABLESPACE_ts;DROP TABLESPACE_ts INCLUDING CONTENTS AND DATAFILES;6.2用戶管理Oracle 中有個模式（schema）的概念，它是用戶的所有數(shù)據(jù)庫對象的集合；一般在創(chuàng)建用戶的同時會自動創(chuàng)建一個這樣的模式，名稱和用戶名稱一樣。6.2.1 查詢系統(tǒng)用戶用戶.3 創(chuàng)建用

16、戶【語法】CREATE USER 用戶名 IDENTIFIED BY DEFAULT TABLESPACE 表空間;【示例】CREATE USER IDENTIFIED BY DEFAULT TABLESPACE_ts【語法】ALTER USER 用戶名 ACCOUNT UNLOCK;【示例】hr 用戶alter user hr account unlock;select * from all_users;或select * from dba_users; -更詳細的用戶信息戶默認為 tiger6.2.4 修改用戶6.2.5 刪除用戶7DCL 數(shù)據(jù)控制語言7.1授予【語法 1】GRANT 角色

17、權(quán)限（角色）,角色權(quán)限 TO 用戶;【示例 1】-授予CONNECT和RESOURCE兩個角色GRANT connect, resource TO;【語法】DROP USER 用戶名 CASCADE;【示例】DROP USERCASCADE;【語法】ALTER USER 用戶名 identified by【示例】ALTER USERidentified by it;TEMPORARY TABLESPACE temp;7.2撤銷【語法 1】REVOKE 角色權(quán)限（角色）,角色權(quán)限 FROM 用戶;【示例 1】-撤銷CONNECT和RESOURCE兩個角色REVOKE connect, resou

18、rce FROM;【備注】使用如下語句可以查看 resource 角色下的權(quán)限SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE=RESOURCE【語法 2】GRANT 操作 ON 模式.對象 TO 用戶;【示例 2】GRANT select,update ON SCOTT.emp TO;-查看當(dāng)前用戶的系統(tǒng)權(quán)限select * from user_sys_privs;-查看當(dāng)前用戶的對象權(quán)限select * from user_tab_privs;-查看當(dāng)前用戶的所有角色select * from user_role_privs;-允許用戶查看、更新 EMP

19、表中的8DDL 數(shù)據(jù)定義語言8.1創(chuàng)建表【語法】CREATE TABLE (column1 DAYPE NOT NULL PRIMARY KEY, column2 DAYPE NOT NULL,.constra 約束類型 (要約束的字段). );【說明】DAYPE -是 Oracle 的數(shù)據(jù)類型NUT NULL -可不可以允許資料有空的（尚未有資料填入） PRIMARY KEY -是本表的主鍵constra-是對表里的字段添加約束.(約束類型有 Check,Unique,Primary key,not null,Foreign key);【示例】create table t_student(【

20、語法 2】REVOKE 操作 ON 模式.對象 FROM 用戶;【示例 2】REVOKE select,update ON SCOTT.emp FROM;-撤銷用戶查看、更新 EMP 表中的的操作s_id number(8) PRIMARY KEY,s_name varchar2(20) not null,s_sex varchar2(8),clsid number(8),constra u_1 unique(s_name),constra c_1 check (s_sex in (MALE,FEMALE);-從現(xiàn)有的表創(chuàng)建表及其數(shù)據(jù)【語法】CREATE TABLE as 【示例】create

21、 table emp aect * from scott.emp;create table emp aect empno,ename from scott.emp -表結(jié)構(gòu)只有empno 和 ename 兩個字段及該兩字段對應(yīng)的數(shù)據(jù)-如果只表的結(jié)構(gòu)不表的數(shù)據(jù)則:createtable emp aect * from scott.emp where 1=2;8.2修改表【語法 1】向表中添加新字段ALTER TABLE ADD (字段 1 類型 NOT NULL,字段 2 類型 NOT NULL . );【示例 1】alter table t_student add (s_age number(

22、3),s_address varchar2(20);8.3刪除表【語法 1】-刪除表結(jié)構(gòu)及數(shù)據(jù)（刪除后可在回收站查看并恢復(fù)） DROP TABLE ;-刪除表結(jié)構(gòu)及數(shù)據(jù)（刪除后不可在回收站查看并恢復(fù)）DROP TABLE PURGE;【語法 2】修改表中字段ALTER TABLE MODIFY(字段 1 類型,字段 2 類型 . );【示例 2】altertablet_studentmodify(s_namevarchar2(50),s_address varchar2(100);【語法 3】刪除表中字段ALTER TABLE DROP(字段 1,字段 2. );【示例 3】alter tab

23、le t_student drop(s_age,s_address);【語法 4】修改表字段名稱ALTER TABLE RENAME COLUMN 原字段名稱 TO 新字段名稱;【示例 4】alter table t_student rename column s_id to s_no;8.4回收站8.4.1 查看回收站8.4.2 清空回收站8.5oracle 數(shù)據(jù)類型數(shù)據(jù)類型描述VARCHAR2(size)可變長度的字符串,其最大長度為 size 個字節(jié);size的最大值是 4000,而最小值是 1;你必須指定一個 VARCHAR2 的 size;NVARCHAR2(size)可變長度的字符

24、串,依據(jù)所選的國家字符集,其最大長度為 size 個字符或字節(jié);size 的最大值取決于每個字符所需的字節(jié)數(shù),其上限為 4000;你必須指定一個 NVARCHAR2 的 size;NUMB,s)精度為p 并且數(shù)值范圍為 s 的數(shù)值;精度 p 的范圍從1 到 38;數(shù)值范圍 s 的范圍是從-84 到 127;例如:NUMBER(5,2) 表示整數(shù)部分最大 3 位，小數(shù)部-清空回收站purge recycin;-查看回收站show recycin; 或 select * from recycin;【示例 1】drop table t_student;9DML 數(shù)據(jù)操作語言9.1新增【語法 1】分為

25、 2 位；NUMBER(5,-2) 表示數(shù)的整數(shù)部分最大為 7 其中對整數(shù)的倒數(shù) 2 位為 0,前面的取整。NUMBER表示使用默認值,即等同于 NUMBER(5);LONG可變長度的字符數(shù)據(jù),其長度可達 2G 個字節(jié);DATE有效日期范圍從公元前 4712 年 1 月 1 日到公元后9999 年 12 月 31 日RAW(size)長度為 size 字節(jié)的原始二進制數(shù)據(jù),size 的最大值為 2000 字節(jié)；你必須為 RAW 指定一個 size;LONG RAW可變長度的原始二進制數(shù)據(jù)，其最長可達 2G 字節(jié);CHAR(size)固定長度的字符數(shù)據(jù),其長度為 size 個字節(jié);size 的最

26、大值是 2000 字節(jié),而最小值和默認值是 1;NCHAR(size)也是固定長度。根據(jù) Unicode 標準定義CLOB一個字符大型對象,可容納單字節(jié)的字符;不支持寬度不等的字符集;最大為 4G 字節(jié)NCLOB一個字符大型對象,可容納單字節(jié)的字符;不支持寬度不等的字符集;最大為 4G 字節(jié);國家字符集BLOB一個二進制大型對象;最大 4G 字節(jié)BFILE包含一個大型二進制文件的,其在數(shù)據(jù)庫的外面；使得可以以字符流 I/O存在數(shù)據(jù)庫服務(wù)器上的外部 LOB;最大大小為 4G 字節(jié).9.2修改9.3查詢9.3.1 偽表 dualDUAL 是一個虛擬表，用來select 的語則，oracle 保證

27、dual 里面【語法 1】UPDATE table_name SET column1=new value,column2=new value,. WHERE ;【示例 1】update emp set sal=3000 where ename=;INSERTO table_name (column1,column2,.) VALUES ( value1,value2, .);【示例 1】【語法 2】INSERTO ;【示例 2】create table t1 aect * from emp where 1=2; inserto t1 select * from emp where sal200

28、0;inserto emp (empno,ename) values(1111,);只有一條。以用它來做很多事情，如：1.查看當(dāng)前用戶2.用來調(diào)用系統(tǒng)函數(shù)3.得到序列的下一個值或當(dāng)前值4.可以用做計算器9.3.2 偽列 rowidrowid 是物理結(jié)構(gòu)上的，在每條insert 到數(shù)據(jù)庫中時，都會有一個唯一的物理，同一條在不同查詢中對應(yīng)的 rowid 相同?！居梅ā縎ELECT ROWID,字段名. FROM 表名;【示例】select rowid, emp.* from emp;select 2*8 from dual;-獲得序列seq的下一個值select seq.nextval from

29、dual;-獲得序列seq的當(dāng)前值select seq.currval from dual;-查詢系統(tǒng)的當(dāng)前時間并格式化select to_char(sysdate,yyyy-mm-dd hh24:mi:ss) from dual;select user from dual;9.3.3 偽列 rownumrownum 是根據(jù) sql 查詢出的結(jié)果給每行分配一個邏輯；每次的查詢都會有不同的。從 1 開始?！居梅ā縎ELECT ROWNUM,字段名. FROM 表名;【注意】ROWNUM 不能使用大于號“”即 select rownum, emp.* from emp where rownum 2

30、 是不對的，沒有任何結(jié)果【示例】select rownum, emp.* from emp;/* 關(guān)于分頁：由于不能使用，所以為了達到分頁目的得如下執(zhí)行；如獲取第2 頁數(shù)據(jù)（每頁 3 條）*/select * from (select rownum r,emp.* from emp where rownum 3;/* 關(guān)于排序：由于 rownum 是查詢結(jié)果的行，排序后這個便有可能被打亂，如果需要該和排序的結(jié)果列表序號保持一致可以如下執(zhí)行*/ select rownum,t.* from (select empno,ename from emp order by empnodesc) t;9.

31、3.4 連接查詢準備查詢數(shù)據(jù)，將 scott 用戶下的dept 表到用戶下。1、 等值查詢2、左外/右外連接查詢：左外連接是在等號左邊的集合，無論條件是否成立均在結(jié)果集合，寫法就是在等號右邊使用(+)，這個寫法是 oracle的，如果需要全數(shù)據(jù)庫類型通用應(yīng)該使用 left join）3、自連接查詢：查詢的 2是同一，一般是該表的字段之間存在上下級關(guān)系-按部門統(tǒng)計員工的人數(shù)，要求顯示部門號、部門名稱、和部門人數(shù)，部門下沒有人的也將顯示select d.deptno,d.dname,count(e.empno) from dept d,emp e where d.deptno=e.deptno(+

32、) group by d.deptno,d.dname;-上述語句的通用數(shù)據(jù)庫寫eft join方式)select d.deptno,d.dname,count(e.empno) from dept d left join emp e on d.deptno=e.deptno group by d.deptno,d.dname;-查詢emp表中各用戶對應(yīng)的部門名稱select empno,ename,dname from emp,dept where emp.deptno=dept.deptno;-練習(xí)：按部門統(tǒng)計員工的人數(shù)，要求顯示部門號、部門名稱、和部門人數(shù)select d.deptno,

33、d.dname,count(e.empno) from dept d,emp e where d.deptno=e.deptno group by d.deptno,d.dname;使用 sys 用戶登錄系統(tǒng)；替用戶創(chuàng)建 dept 表，表結(jié)構(gòu)和數(shù)據(jù)來自scott.dept。-執(zhí)行語句如下create table .dept aect * from scott.dept;9.3.5 組合查詢1、 計算部門工資總和，最高工資，最低工資2、部門平均工資3、 子查詢：將子查詢放入括號中；group by 后不能使用子查詢；select、from、where 后面都可以使用子查詢；可以將子查詢看作一張新

34、表-查詢部門的平均工資select deptno,avg(sal) from emp group by deptno;-查詢平均工資大于2000的部門，并按照平均工資降序排序select deptno,avg(sal) 平均工資 from emp group by deptnohaving avg(sal)2000 order by 平均工資 desc ;-查詢除了20部門以外，平均工資大于2000的部門select deptno,avg(sal) from emp where deptno 20group by deptno having avg(sal)2000;【注意】SQL 語句中的各

35、子句執(zhí)行順序：from-where-group by-having-select-order byselect deptno,sum(sal),max(sal),min(sal) from emp group by deptno;-查詢員工和的上下級關(guān)系select e.ename | 的是: | b.ename from emp e,emp b where e.mgr=b.empno;【注意】上述查詢語句中的|表示為字符的連接4、其它查詢9.4刪除-根據(jù)條件刪除表數(shù)據(jù)delete from emp where empno=0000-查詢是5個字符的員工，且第二個字符是C，使用_只匹配一個字符

36、并且 不能標識0或多個字符 select * from emp where ename like _C;-查詢員工中含有_的員工，使用轉(zhuǎn)義字符 select * from emp where ename like %_% esc ;-select后面的子查詢select (select dname from dept where deptno=10),ename from emp where deptno=10;-from后面的子查詢select * from (select ename,sal from emp);-將子查詢視為一個表select e.ename,e.sal from (se

37、lect ename,sal from emp) e;-where后面的子查詢；查詢工資比10號部門員工中任意一個員工的工資低的 員工信息select * from emp where sal (select min(sal) from emp wheredeptno=10);10 TCL 事務(wù)控制語言10.1 提交事務(wù)的提交比較簡單；直接在執(zhí)行 DML 語句后進行提交即可，如果不提交事務(wù)則剛剛通過 DML 語句進行修改的內(nèi)容還未保存到數(shù)據(jù)庫中，只在當(dāng)前用戶的連接會話中有效。要變更數(shù)據(jù)需要顯示地執(zhí)行提交、回滾或者退出當(dāng)前回話（如退出 sqlplus）。提交令為：commit;10.2 保存點與

38、回滾保存點 savepo一般與回滾 rollback 配合使用。在設(shè)置了 savepo后事務(wù)的粒度可以控制的更加細化，可以回滾到特定的保存點?！菊Z法】保存點 savepo SAVEPO;【示例】-創(chuàng)建一個保存點，名稱為a savepoa;【注意】當(dāng)創(chuàng)建保存點之后執(zhí)行的 DML 操作，可以進行回滾，而保存點之前未-清空表數(shù)據(jù)（表還在），不寫日志，省資源，效率高，屬于數(shù)據(jù)定義語言-先創(chuàng)建要清空數(shù)據(jù)的表create table myemp aect * from emp; -清空表數(shù)據(jù)truncate table myemp;提交的 DML 操作不受影響?！菊Z法】回滾ROLLBACK TO save

39、po;【示例】-回滾到保存點 a,即在保存點 a 之后的所有未提交的 DML 都無效。rollback to a;/*保存點與回滾完整示例*/-1、創(chuàng)建保存點asavepo a;-2、emp數(shù)據(jù) it1insert o emp(empno,ename) values(1234,it1);-3、創(chuàng)建保存點bsavepo b;-4、emp數(shù)據(jù) it2insert o emp(empno,ename) values(1235,it2);-5、查看emp表數(shù)據(jù)，存在it1、it2兩條數(shù)據(jù)select ename from emp;-6、回滾到保存點b，即it2數(shù)據(jù)將rollback to b;-7、查

40、看emp表數(shù)據(jù)，存在it1的數(shù)據(jù)，it2已不在select ename from emp;-8、提交數(shù)據(jù)commit;-9、查看emp表數(shù)據(jù)，存在it1的數(shù)據(jù)select ename from emp;-10、回滾到保存點a，將報錯保存點不存在的錯誤信息rollback to a;11 運算符11.1 算術(shù)運算符+、-、*、/11.2 比較（關(guān)系）運算符、!、 、 = 、 bet、is nulln.and.、in、like11.3 邏輯運算符AND(邏輯與)，表示兩個條件必須同時滿足OR(邏輯或)，表示兩個條件中有一個條件滿足即可NOT(邏輯非)，返回與某條件相反的結(jié)果11.4 連接運算符|1

41、1.5 集合運算符union（并集無重復(fù)）union all（并集有重復(fù)）ersect（交集，共有部分）minus（減集，第一個查詢具有，第二個查詢不具有的數(shù)據(jù)）【注意】：列數(shù)相關(guān)，對應(yīng)列的數(shù)據(jù)類型兼容，不能含有 Long 類型的列，第一個【示例】select 工號為： | empno | 的員工的為：| ename from emp;select 語句的列或別名作為結(jié)果標題11.6 運算符優(yōu)先級可以使用括號改變優(yōu)先級順序；OR 的優(yōu)先級最低，算術(shù)運算符的優(yōu)先級最高。優(yōu)先級運算符1算術(shù)運算符2連接符3比較符4ISNOTNULL, LIKE, NOTIN5NOT BETN6NOT7AND8OR-

42、union(并集將去重復(fù))select * from emp where deptno=10 unionselect * from emp where deptno=20;- ersect(交集) 查詢工資即屬于10002000區(qū)間和15002500區(qū)間的工資select ename,sal from emp where sal betn 1000 and 2000 ersectselect ename,sal from emp where sal betn 1500 and 2500;-minus(減集)select ename,sal from emp where sal betn 100

43、0 and 2000 minusselect ename,sal from emp where sal betn 1500 and 2500;12 常用函數(shù)12.1 數(shù)值型函數(shù)round(x,y)trunc(x,y)12.2 字符型函數(shù)LENGTH(c1)【功能】返回x 按精度 y 截取后的值【參數(shù)】x,y，數(shù)字型表達式,如果 y 不為整數(shù)則截取y 整數(shù)部分，如果 y0 則截取到y(tǒng) 位小數(shù)，如果 y 小于 0 則截取到小數(shù)點向左第y 位，小數(shù)前其它數(shù)據(jù)用 0 表示?！痉祷亍繑?shù)字【示例】select trunc(5555.66666,2.1),trunc(5555.66666,-2.6),tru

44、nc(5555.033333) from dual;返回：5555.6655005555【功能】返回四舍五入后的值【參數(shù)】x,y，數(shù)字型表達式,如果 y 不為整數(shù)則截取y 整數(shù)部分，如果 y0 則四舍五入為y 位小數(shù)，如果 y 小于 0 則四舍五入到小數(shù)點向左第y 位?！痉祷亍繑?shù)字【示例】select round(5555.6666,2.1),round(5555.6666,-2.6),round(5555.6666) from dual;返回：5555.67,5600,5556LPAD(c1,n,c2)、RPAD(c1,n,c2)REPLACE(c1,c2,c3)【功能】將字符表達式值中，部

45、分相同字符串，替換成新的字符串【參數(shù)】c1希望被替換的字符或變量c2被替換的字符串c3要替換的字符串，默認為空(即刪除之意，不是空格)【返回】字符型【功能】在字符串 c1 的左（右）邊用字符串c2 填充，直到長度為n 時為止【說明】如果 c1 長度大于n，則返回 c1 左邊 n 個字符【參數(shù)】C1 字符串n 追加后字符總長度c2 追加字符串,默認為空格【返回】字符型【示例】select lpad(,10,*),rpad(,10,*) from dual;【功能】返回字符串的長度;【說明】多字節(jié)符(漢字、全角符等)，按 1 個字符計算【參數(shù)】C1 字符串【返回】數(shù)值型【示例】select len

46、gth(播客),length(播客) from dual;LENGTH(播客) LENGTH(播客)410SUBSTR(c1,n1,n2)12.3 日期函數(shù)sysdateadd_months(d1,n1)【功能】：返回在日期 d1 基礎(chǔ)上再加n1 個月后新的日期?！緟?shù)】：d1，日期型，n1 數(shù)字型【返回】：日期【示例】select sysdate,add_months(sysdate,3) from dual;【功能】：返回當(dāng)前日期?！緟?shù)】：沒有參數(shù)，沒有括號【返回】：日期【示例】select sysdate from dual;【功能】取子字符串【說明】多字節(jié)符(漢字、全角符等)，按 1

47、 個字符計算【參數(shù)】在字符表達式 c1 里，從 n1 開始取 n2 個字符;若不指定 n2,則從第 n1個字符直到結(jié)束的字串.【返回】字符型【示例】select substr(123456789,4,4),substr(123456789,3) from dual;【示例】select replace(he love you,he,i) from dual;months_betn(d1,d2)extract(c1from d1)【功能】：日期/時間 d1 中，參數(shù)(c1)的值【參數(shù)】：d1 日期型(date)/日期時間型(timest),c1 為字符型(參數(shù))【參數(shù)表】：c1 對應(yīng)的參數(shù)表詳見

48、示例【返回】：字符【示例】select extract(YEAR from timest 2015-5-1 12:26:18 ) 年, extract(MONTH from timest 2015-5-1 12:26:18 ) 月, extract(DAY from timest 2015-1-5 12:26:18 ) 日, extract(hour from timest 2015-5-1 12:26:18 ) 小時, extract(minute from timest 2015-5-1 12:26:18 ) 分鐘, extract(second from timest 2015-5-1

49、12:26:18 ) 秒 from dual;select extract (YEAR from date 2015-5-1 ) from dual; select sysdate 當(dāng)前日期,extract(YEAR from sysdate ) 年,【功能】：返回日期 d1 到日期d2 之間的月數(shù)?！緟?shù)】：d1，d2 日期型【返回】：數(shù)字如果 d1d2，則返回正數(shù)如果 d1d2，則返回負數(shù)【示例】months_betn(sysdate,to_date(2015-01-01,YYYY-MM-DD) 距2015元 旦,months_betn(sysdate,to_date(2016-01-01

50、,YYYY-MM-DD) 距2016元旦 from dual;select sysdate,12.4 轉(zhuǎn)換函數(shù)TO_CHAR(x,c2,C3)TO_DATE(X,c2,c3)【功能】將字符串X 轉(zhuǎn)化為日期型【參數(shù)】c2,c3,字符型，參照 to_char()【返回】字符串如果x 格式為日期型(date)格式時，則相同表達：date x如果x 格式為日期時間型(timest)格式時，則相同表達：timestx【示例】select to_date(201212,yyyymm), to_date(2012.12.20,yyyy.mm.dd),【功能】將日期或數(shù)據(jù)轉(zhuǎn)換為 char 數(shù)據(jù)類型【參數(shù)】x

51、是一個 date 或 number 數(shù)據(jù)類型。c2 為格式參數(shù)c3 為 NLS 設(shè)置參數(shù)【返回】varchar2 字符型【示例】select to_char（sysdate,YYYY-MM-DD HH24:MI:SS） FROM dual; select to_char（1210.7, $9,999.00） FROM dual;extract(MONTH from sysdate ) 月,extract(DAY from sysdate ) 日from dual;-如下語句也可獲取年份、月份等select to_number(to_char(sysdate,yyyy) from dual;(d

52、ate 2012-12-20) XXdate,to_date(2012-12-20 12:31:30,yyyy-mm-dd hh24:mi:ss),to_timest(2012-12-20 12:31:30,yyyy-mm-dd hh24:mi:ss),(timest 2012-12-20 12:31:30) XXtimestfrom dual;TO_NUMBER(X,c2,c3)12.5 聚合函數(shù)sum:求和avg:求平均數(shù)count：計數(shù)max：求最大值min：求最小值12.6 分析函數(shù)分析函數(shù)中了解r()/dense_r()/row_number()的使用：-查詢部門的員工工種情況，并在

53、部門內(nèi)重新進行排序；PARTITION BY類似 group by,根據(jù)ORDER BY排序字段的值重新由1開始排序。- 使用相同排序一樣，后繼數(shù)據(jù)空出；即有2個排序為1的，那【功能】將字符串X 轉(zhuǎn)化為數(shù)字型【參數(shù)】c2,c3,字符型【返回】數(shù)字串【示例】select TO_NUMBER(201212) + 3,TO_NUMBER(450.05) + 1 from dual;-等同上述結(jié)果select 201212 + 3 from dual;12.7 其它函數(shù)NVL()/NVL2()decode(條件,值 1,翻譯值 1,值 2,翻譯值 2,.值 n,翻譯值 n,缺省值)【語法】NVL (e

54、xpr1, expr2)【功能】若 expr1 為 NULL，返回 expr2；expr1 不為 NULL，返回 expr1。注意兩者的類型要一致【示例】將員工的獎金如果是空的話則設(shè)置為 0 selectm,nvl(comm,0) from emp;【語法】NVL2 (expr1, expr2, expr3)【功能】expr1 不為 NULL，返回 expr2；expr2 為 NULL，返回 expr3。 expr2 和 expr3 類型不同的話，expr3 會轉(zhuǎn)換為 expr2 的類型【示例】select ename,job,nvl2(job,job 有值,job 無值) from emp;

55、么接下來的排序號則為3select deptno,ename,job,r() ovartition by deptno order by job) as myR from emp e;by job) as myDenseR from emp e;-ROW_NUMBER使用，不管是否一樣，都按順序；即有2個排序為1的，那么排序號不會重現(xiàn)重復(fù)select deptno,ename,job,row_number() ovartition by deptno orderby job) as myRowNumber from emp e;-DENSE_使用，使用相同排序一樣，后繼數(shù)據(jù)不空出；即有2個排序

56、為1的，那么接下來的排序號則為2select deptno,ename,job,dense_r() ovartition by deptno order13 視圖13.1 視圖簡介視圖是由一個或者多個表組成的虛擬表；那些用于產(chǎn)生視圖的表叫做該視圖的基表。視圖不占用物理空間，這個也是相對概念，因為視圖本身的定義語句還【功能】根據(jù)條件返回相應(yīng)值【參數(shù)】c1, c2,字符型/數(shù)值型/日期型，必須類型相同或 null注：值 1n 不能為條件表達式,這種情況只能用 case when then end 解決含釋：decode(條件,值 1,翻譯值 1,值 2,翻譯值 2,.值 n,翻譯值 n,缺省值)該

57、函數(shù)的含義如下： IF 條件=值 1 THEN RETURN(翻譯值 1) ELSIF 條件=值 2 THEN RETURN(翻譯值 2)ELSIF 條件=值n THENRETURN(翻譯值 n) ELSERETURN(缺省值) END IF【示例】根據(jù)員工的部門號，條件判斷找到對應(yīng)的部門名稱select ename,deptno,decode(deptno,10,ACCOUNTING,20,RESEARCH,30,SALES,無部門) from emp;是要在數(shù)據(jù)字典里的。視圖只有邏輯定義。每次使用的時候只是重新執(zhí)行SQL。一個視圖也可以從另一個視圖中產(chǎn)生。視圖沒有真正的數(shù)據(jù)，真正的數(shù)據(jù)還是

58、在基表中。一般出于對基本的安全性和常用的查詢語句會建立視圖；并一般情況下不對視圖進行新增、更新操作?！菊Z法】13.2 視圖操作- 授予用戶 創(chuàng)建視圖 的權(quán)限grant create view to;- 登錄，創(chuàng)建視圖 create or replace view v_emp as select empno,ename from emp;-通過視圖查詢數(shù)據(jù)select * from v_emp;-通過視圖添加數(shù)據(jù),需要保證基表的其它數(shù)據(jù)項可以為空inserto v_emp(empno,ename) values(3333,3);-通過視圖修改數(shù)據(jù)-創(chuàng)建視圖CREATE OR REPLACE VI

59、EW AS;-刪除視圖DROP VIEW ;14 同義詞同義詞是數(shù)據(jù)庫模式對象的一個別名，經(jīng)常用于簡化對象和提高對象訪問的安全性。在使用同義詞時，Oracle 數(shù)據(jù)庫將它翻譯成對應(yīng)模式對象的名字。與視圖類似，同義詞并不占用實際空間，只有在數(shù)據(jù)字典中保存了同義詞的定義。在 Oracle 數(shù)據(jù)庫中的大部分數(shù)據(jù)庫對象，如表、視圖、同義詞、序列、過程等，數(shù)據(jù)庫管理員都可以根據(jù)實際情況為他們定義同義詞。隱藏對象名稱和所有者。update v_emp set ename=播客3 where empno=3333;-通過視圖刪除數(shù)據(jù)delete from v_empwhere empno=3333;-基于多

60、個基表的視圖，不建議使用視圖進行增刪改操作create or replace view v_dept_emp as select dept.deptno,dept.dname,ename from emp inner join dept on emp.deptno=dept.deptno;-查詢多個基表的視圖select * from v_dept_emp;-創(chuàng)建基于視圖的視圖create or replace view vv_emp as select ename from v_emp;-刪除視圖drop view v_emp;drop view v_dept_emp; drop view