《Oracle大型數據庫基礎開發(fā)教程》（微課視頻版）課件 第4章 Oracle SQL

第4章OracleSQL4.1SQL概述一、SQL的發(fā)展1、發(fā)展歷程1970年E.F.Codd在《CommunicationoftheACM》發(fā)表：“ARelationalModelofDataforLargeSharedDataBanks”(大型共享數據庫的關系模型)，首次提出了關系數據模型。1974年Boycee和Chamberlin提出了SEQUEL(StructuredEnglishQUEryLanguage)，首次在IBM公司研制的關系數據庫管理系統(tǒng)SYSTEMR上實現。SQL一推出由于它簡單易學、功能強大，深受用戶及數據庫廠商的歡迎，逐步被各數據庫廠商采用，并得到了計算機工業(yè)界的認可。1979年Oracle公司首先提供了商業(yè)版的SQL,后來IBM也在自己的DB2中實現了SQL。1986年10月美國國家標準局ANSI（AmercianNationalStandardInstitute）把SQL作為關系數據庫語言的美國標準，同年公布了SQL（SQL-86）標準文本。1987年國際化標準組織ISO把SQL采納為國際標準。4.1SQL概述2、SQL標準標準隨著計算機技術的發(fā)展不斷發(fā)展、豐富，下表為SQL標準的發(fā)展過程。4.1SQL概述二、SQL功能1.數據定義功能

SQL的數據定義功能通過數據定義語言DDL（DataDefinitionLanguage）實現，用來定義數據庫的對象，包括定義表、索引、視圖等。主要有CREATE、DROP和ALTER三個命令。2．數據操縱功能SQL的數據操縱功能通過數據操縱語言DML（DataManipulationLanguage）實現,用來對數據庫的數據進行查詢和更新操作。主要有查詢（SELECT），更新包括插入（INSERT）、修改（UPDATE）和刪除（DELETE）等命令。3．數據控制功能

SQL的數據控制功能通過數據查控制語言DCL（DataControlLanguage）實現，用來實現相關數據的存取控制，以保證數據庫的安全性。它包括授權（GRANT）和回收權限（REVOKE）兩個命令。4.1SQL概述4.事務控制功能SQL的事務控制功能通過事務控制語言TCL（TransactionControlLanguage）實現，用來實現數據庫數據的一致性和并發(fā)控制。它包括提交（COMMIT）和回滾（ROLLBACK）兩個命令。三、SQL特點1.綜合統(tǒng)一SQL是集DDL、DML、DCL、TCL于一體的語言，而且SQL風格統(tǒng)一，能夠獨立完成數據庫生命周期中的全部工作，包括定義、刪除和修改關系模式，定義和刪除視圖，插入、修改和更新數據，建立數據庫，查詢數據和修改數據以及數據庫的安全控制等一系列操作，為開發(fā)數據庫應用系統(tǒng)提供了良好的環(huán)境。。而非關系的語言風格不統(tǒng)一。2.高度非過程化SQL是高度的非過程化的語言，用戶只需要提出“干什么”，而不需指出“怎么干”，用戶只需要將SQL語句提交給系統(tǒng)，由系統(tǒng)自動完成需求，SQL的執(zhí)行過程完全由系統(tǒng)自動完成。4.1SQL概述

3.面向集合的操作方式SQL是面向集合操作方式，操作的對象和結果都是表中的記錄集合，而且更新操作的對象也可以是一個記錄集合。4.使用方式靈活SQL可以支持自含式、嵌入式兩種使用方式。自含式是指SQL能夠直接以聯(lián)機交互的方式使用；嵌入式是指SQL可以嵌入到高級程序設計語言中，比如C、C++、Java、VisualStudio等。這兩種使用方式語法結構上基本是一致的，使得系統(tǒng)開發(fā)更加靈活方便。5.語言簡潔，易學易用SQL功能極強，語言十分簡潔，完成核心功能的需要11個動詞，就能實現數據庫的大部分操作，如下表所示。4.1SQL概述四、

SQL的書寫規(guī)則1.命令動詞的書寫Oracle中SQL命令動詞是不區(qū)分大小寫的，可以使用大寫字母，也可以使用小寫字母，或者大小寫混用。SQL命令中引用的對象、字段名、函數等也不區(qū)分大小寫。2.命令結束符Oracle中SQL命令可以書寫在一行，也可以分多行書寫，不管哪一種書寫格式在OracleSQL*plus中SQL命令的結束符為分號。如果只是單行語句，在SQLDeveloper中就不需要語句結束符。如果有多行語句，每一行以回車終止，在最后一行用分號結束符。4.1SQL概述五、ORCL數據庫本章以OracleORCL數據庫為例來講解SQL的數據定義、數據操縱、數據查詢語句。OracleORCL數據庫SCOTT用戶有如下3張表，分別是：DEPT(DEPTNO,DNAME,LOC)EMP(EMPNO,ENAME,JOB,MGR,HIERDATE,SAL,COMM,DEPTNO)SALGRADE(GRADE,LOSAL,HISAL)其數據記錄如下三個表所示：4.1SQL概述4.2表的管理一、表的概念Oracle數據庫中的對象大約有40多種，常見的對象主要有五種：表（TABLE）、視圖（VIEW）、索引（INDEX）、序列（SEQUENCE）、同義詞（SYNONYM），其中表是數據庫中最基本和最重要的對象，是數據實際存放的地方，其它許多數據庫對象（索引、視圖等）都以表為基礎。對于關系數據庫中的表，其存儲數據的邏輯結構是一張二維表，由行和列兩部分組成。表中的一行為一個元組也稱為記錄，描述一個實體，表中的一列為關系的屬性，也稱為字段。每一行的記錄順序按輸入的先后順序存放，字段的順序按照創(chuàng)建表時定義的先后順序存放。Oracle中表的概念和關系數據模型的對應如下表所示：4.2表的管理二、Oracle數據類型Oracle數據類型主要有數值類型、字符類型、日期類型、LOB類型、二進制類型和行類型等。1．數值類型NUMBER[(P,S)]，用于存儲整數和實數。P是精度，表示數值的總位數，最大38位，S是刻度范圍，可在-84...127間取值。例：NUMBER（5，2）可以用來存儲表示-999.99...999.99間的數值。P、S可以在定義時省略，例如：NUMBER（3）、NUMBER等。2．字符類型（1）CHAR[(n[BYTE|CHAR])]，用于存儲定長的字符串。例：CHAR（n）。n為字符串長度，最大為2000字節(jié)。4.2表的管理（2）VARCHAR2[(n[BYTE|CHAR])]，描述變長字符串。例：VARCHAR2（n）。n為字符串長度，最大為4000字節(jié)。當字段中保存的字符串長度小于n時，按實際長度分配空間。（3）LONG，用于存儲高達2GB的的可變字符串。（4）NCHAR、NVARCHAR2，國家字符集，用來存儲Unicode類型字符串。3．日期類型（1）DATE：用于存儲固定長度的日期和時間數據。（2）TIMESTAMP[(n)]：允許存儲小數形式的秒值。（3）INTERVALYEAR(n)TOMONTH：存儲以年份和月份表示的時間段。例：INTERVAL‘2-5’YEARTOMONTH表示2年5個月。4.2表的管理（4）INTERVALDAY(m)TOSECOND(n)：存儲以天數、小時數、分鐘數和秒數表示的時間段。例：INTERVAL‘210:30:20’DAYTOSECOND表示2天10小時30分20秒。4．LOB類型（1）CLOBCLOB的全稱為CharacterLargeObject，即字符大對象，可以存儲大量字符數據，最大容量可以達到4GB，CLOB處理方式和普通的字符不同，需要特殊的處理方式。（2）NCLOBNCLOB是CLOB的擴展，用來存儲可變長度的Unicode字符數據。（3）BLOBBLOB是二進制LOB，存儲較大的可變長度的二進制對象，如圖形、視頻剪輯和聲音文件。4.2表的管理（4）BFILEBFILE是文件性LOB，存儲指向二進制格式文件的定位器。5．RAW和LONGRAW類型用來存儲二進制數據。（1）RAW類似于CHAR，聲明方式RAW（L），L為長度，以字節(jié)為單位，作為數據庫字段最大2000，作為變量最大32767字節(jié)。（2）LONGRAW類似于LONG，作為數據庫字段最大存儲2G字節(jié)的數據，作為變量最大32760字節(jié)。4.2表的管理6．行類型（1）ROWIDROWID數據類型被稱為“偽列類型”，用于Oracle內部保存表中每條記錄的物理地址。Oracle通過ROWID可最快地定位某行具體數據的位置。在使用ROWID字段時必須顯式指定名稱。（2）UROWID行標識符，用于表示索引化表中行的邏輯地址。三、表的創(chuàng)建1．創(chuàng)建表的語法格式創(chuàng)建表的命令是CREATETABLE，只有具有CREATETABLE權限的用戶才能創(chuàng)建表，基本語法格式如下：4.2表的管理CREATETABLE[schema.]table_name(column_namedatatype[DEFAULTexpression][column_constraint],…n)[ASsubquery];各子句的含義如下：table_name：表的名稱。column_name：指定表的一個列的名字。Datatype：該列的數據類型。DEFAULTexpresssion：指定由expresssion表達式定義的默認值。column_constraint：定義一個完整性約束作為列定義的一部分，格式如下：4.2表的管理CONSTRAINTconstraint_name[NOT]NULL[UNIQUE][PRIMARYKEY][REFERENCES[schema.]table_name(column_name)][CHECK(condition)]，其中[NOT]NULL定義該列是否允許為空；UNIQUE定義字段的唯一性；PRIMARYKEY定義字段為主鍵；REFERENCES定義外鍵約束；CHECK(condition)定義該字段數據必須符合的條件。ASsubquery：表示將由子查詢返回的行插入到所創(chuàng)建的表中。4.2表的管理例：創(chuàng)建表DEPT2，該表包含字段DEPTNO、DNAME、LOC，具體命令如下：CREATETABLEDEPT2(DEPTNONUMBER(2),DNAMEVARCHAR2(14),LOCVARCHAR2(13));例：創(chuàng)建表DEPT3，該表包含字段DEPTNO、DNAME、LOC，指定LOC的默認值為’北京’，具體命令如下：CREATETABLEDEPT2(DEPTNONUMBER(2),DNAMEVARCHAR2(14),LOCVARCHAR2(13)DEFAULT'北京');4.2表的管理2.通過子查詢創(chuàng)建表CREATETABLE表名(字段名...)ASSQL查詢語句;例：CREATETABLEDEPT_SUBASSELECT*

FROMDEPT

WHEREDEPTNO=30;四、表的操作1.查看表結構其語法格式如下：DESC[RIBE]表名例：顯示DEPT_SUB表的結構：DESCDEPT_SUB;4.2表的管理2.表的重命名語法如下：RENAME舊表名TO新表名。例：RENAMEDEPT_SUBTODEPT_TAB;3.添加注釋（1）為表添加注釋語法如下：COMMENTONTABLE表名IS'...';該語法為表添加注釋字符串。如IS后的字符串為空，則清除表注釋。例：COMMENTONTABLEDEPT_TABIS‘部門表’;（2）為列添加注釋可以為字段添加注釋，語法如下：COMMENTONCOLUMN表名.字段名IS'...';該語法為字段添加注釋字符串。如IS后的字符串為空，則清除字段注釋。例：COMMENTONCOLUMNDEPT.DEPTNOIS‘部門編號;’4.2表的管理二、修改表其語法格式如下：ALTERTABLE[schema.]tablename[ADD(columnnamedatatype[DEFAULTexpression][column_constraint],…n)][MODIFY(columnnamedatatype[DEFAULTexpression][column_constraint],…n)][DROP[COLUMN]（columnname,……）]修改表結構有以下要求：可以增加字段、修改字段的屬性和刪除字段，可進行表參數的修改以及表的重命名和約束的添加、修改、刪除和禁用等。增加的新字段總是位于表的最后。4.2表的管理假如新字段定義了默認值，則新字段的所有行自動填充默認值。對于有數據的表，新增加字段的值為NULL。所以有數據的表，新增加字段不能指定為NOTNULL約束條件1.增加新字段使用ALTERTABLE…ADD語句實現表中字段的添加。例：為DEPT表增加一字段MAIL（電子郵件）。ALTERTABLEDEPTADDMAILVARCHAR2(20);例：為DEPT表增加兩個字段PHONE（電話）、MPR(部門負責人)。ALTERTABLEDEPTADD(PHONEVARCHAR2(11),MPRVARCHAR2（20）);4.2表的管理2.修改字段名使用ALTERTABLE…RENAMECOLUMN語句修改字段的名稱。例：把DEPT表中MAIL改為EMAIL。ALTERTABLEDEPTRENAMECOLUMNMAILTOEMAIL;3.修改字段使用ALTERTABLE…MODIFY語句實現表中字段的修改。例：把DEPT中PHONE的數據類型改為CHAR(11)。ALTERTABLEDEPTMODIFYPHONECHAR（11）;4.2表的管理例：把DEPT表中EMAIL和MPR的長度分別改為40和30。ALTERTABLEDEPTMODIFY(EMAILVARCHAR2（40）,MPRVARCHAR(30));修改字段還有如下的要求：字段的寬度可以增加或減小，在表的字段沒有數據或數據為NULL時才能減小寬度。在表的字段沒有數據或數據為NULL時才能改變數據類型，CHAR和VARCHAR2之間可以隨意轉換。只有當字段的值非空時，才能增加約束條件NOTNULL。修改字段的默認值，只影響以后插入的數據。4.2表的管理4.刪除字段使用ALTERTABLE…DROPCOLUMN語句直接刪除字段。例：刪除DEPT表中的MPR字段。ALTERTABLEDEPTDROPCOLUMNMPR;例：刪除DEPT表中的EMAIL和PHONE。ALTERTABLEDEPTDROPCOLUMN(EMAIL,PHONE);六、刪除表刪除表的語法如下：DROPTABLE表名［CASCADECONSTRAINTS］;4.2表的管理例：刪除表DEPT_TAB。DROTTABLEDEPT_TAB;七、數據更新數據更新操作有三種，分別是：向表中添加添加數據，修改表中的數據和刪除表中的數據。在Oracle中對應的命令是：INERT、UPDATE、DELETE。1.插入數據INSERT語句用于向指定的表中添加數據，通常有兩種形式，一種是插入一個記錄，另一種是插入子查詢的結果，語法格式如下。INSERTINTOtable_name[column_list]VALUES(values)。4.2表的管理INTO子句：指定要插入數據的表名及字段；字段的順序可與表定義中的順序不一致；沒有指定字段：表示要插入的是一條完整的元組，且字段屬性與表定義中的順序一致；指定部分字段：插入的元組在其余字段上取空值。VALUES子句：提供的值必須與INTO子句匹配包括值的個數和值的類型。（1）單行數據的插入例：向DEPT表插入全部字段的數據：INERTINTODEPTVALUES(40,'開發(fā)部','北京')；例：向EMP表插入部分字段的數據：INSERTINTOEMP(EMPNO,ENAME,JOB,HIREDATE)VALUES(2022,'馬明','CLERK','10-10月-22');Oracle在EMP新插入的記錄中，其他的字段上自動地賦NULL。4.2表的管理（2）多行數據的插入語法格式如下：INSERTINTOtable_name[(column1[,column2,…])SubquerySubquery：表示從有一個子查詢來向表中插入數據。例：創(chuàng)建一個新表CLERK，將EMP表中職位為CLERK的員工復制到CLERK表中。首先，創(chuàng)建空表CLERK：CREATETABLECLERKASSELECTEMPNO,ENAME,SALFROMEMPWHERE1=0;然后向CLERK插入子查詢的結果：INSERTINTOCELRKSELECTEMPNO,ENAME,SALFROMEMPWHEREJOB='CLERK';4.2表的管理2．修改數據（1）修改數據的語句UPDATE的基本語法如下：UPDATEtable_nameSETcolumn_name=value[WHEREcondition];Table_name:要更新數據的表名稱。Column_name:要更新數據的表中的字段名。Value:表示將要更新字段的更改值。WHERE：指定哪些記錄需要更新值。若沒有此項，則將更新所有記錄的指定字段的值。例：修改(編號為7369)的工資為3000。UPDATE EMPSETSAL=3000WHERE EMPNO=7369;4.2表的管理例：將(編號為7369的雇傭日期改成當前系統(tǒng)日期，部門編號改為10。UPDATEEMPSET

HIREDATE=SYSDATE,DEPTNO=10WHEREEMPNO=7369;如果修改的值沒有賦值或定義，將把原來字段的內容清為NULL。若修改值的長度超過定義的長度，則會出錯。（2）帶有子查詢的修改格式其語法格式如下：UPDATEtablename1SET（column_name1，column_name2,…）=(SELECTcolumn_name1，column_name2,…FROMtablename2[WHEREcondition]);例：將CLERK表中編號為7369的記錄的雇員名字和工資修改成為EMP表編號為7788的雇員的名字和工資。UPDATECLERKSET(ENAME,SAL)=(SELECTENAME,SALFROMEMPWHEREEMPNO=7788)WHEREEMPNO=7369;4.2表的管理3.刪除數據（1）DELETE語句刪除數據的語句為DELETE，可以刪除表中的一條或多條記錄。其語法格式如下。DELETEFROMtable_nameWHEREcondition例：將編號為‘2022’的員工信息刪除DELETEFROMEMPWHEREEMPNO=2022;例：刪除CLERK表中的全部記錄DELETEFROMCLERK（2）TRUNCATE命令如果確實要刪除一個大表里的全部記錄，可以用TRUNCATE命令，其語法格式如下：TRUNCATETABLE表名;4.2表的管理例：TRUNCATETABLECLERK;此命令和不帶WHERE條件的DELETE語句功能類似，不同的是，DELETE命令進行的刪除可以回滾，但此命令進行的刪除不可回滾。八、序列1.序列的概念序列(SEQUENCE)是序列號生成器，可以為表中的行自動生成序列號，產生一組等間隔的數值(類型為數字)。其主要的用途是生成表的主鍵值，可以在插入語句中引用，也可以通過查詢檢查當前值，或使序列增至下一個值。例如：1、2、3、4……；-3，-1、2、4……；10、20、30……，這些都是序列。4.2表的管理2.序列的的創(chuàng)建創(chuàng)建序列的命令是CREATESEQUENCE。序列的創(chuàng)建語法如下：CREATESEQUENCE序列名[INCREMENTBYn][STARTWITHn][{MAXVALUEn|NOMAXVALUE}][{MINVALUEn|NOMINVALUE}][{CYCLE|NOCYCLE}][{CACHEn|NOCACHE}]；各子句含義如下：4.2表的管理INCREMENTBY用于定義序列的步長，如果省略，則默認為1，如果出現負值，則代表序列的值是按照此步長遞減的。STRATWITH定義序列的初始值（即產生的第一個值），默認為1。MAXVALUE定義序列生成器能產生的最大值。選項NOMAXVALUE是默認選項，代表沒有最大值定義，這時對于遞增序列，系統(tǒng)能夠產生的最大值是10的27次方；對于遞減序列，最大值是-1。MINVALUE定義序列生成器能產生的最小值。選項NOMAXVALUE是默認選項，代表沒有最小值定義，這時對于遞減序列，系統(tǒng)能夠產生的最小值是-10的26次方；對于遞增序列，最小值是1。CYCLE和NOCYCLE表示當序列生成器的值達到限制值后是否循環(huán)。CYCLE代表循環(huán)，NOCYCLE代表不循環(huán)。如果循環(huán)，則當遞增序列達到最大值是時，循環(huán)到最小值；對于遞減序列達到最小值時，循環(huán)到最大值。如果不循環(huán)，達到限制值后，繼續(xù)產生新值就會發(fā)生錯誤。CACHE（緩沖）定義存放序列的內存塊的大小，默認為20.NOCACHE表示不對序列進行內存緩沖。對序列進行內存緩沖，可以改善序列的性能。4.2表的管理例：創(chuàng)建一個序列ids，。CREATESEQUENCEidsINCREMENTBY1STARTWITH10MAXVALUE1000NOCYCLENOCACHE;3.序列的引用使用CURRVAL和NEXTVAL來引用序列的值。調用NEXTVAL將生成序列中的下一個序列號，調用時要指出序列名，調用方式：序列名.NEXTVALCURRVAL用于產生序列的當前值，無論調用多少次都不會產生序列的下一個值。如果序列還沒有通過調用NEXTVAL產生過序列的下一個值，先引用CURRVAL沒有意義。調用方式：序列名.CURRVAL4.2表的管理例：序列ids的引用：SELECTids.NEXTVALFROMDUAL;產生序列的下一個值：SELECTids.NEXTVALFROMDUAL;產生序列的當前值：SELECTids.CURRVALFROMDUAL;4.序列的應用序列的用途主要是生成表的主鍵值。例：使用序列自動產生ST表的學號：創(chuàng)建一個表ST(SNO,SNAME,SAGE,SSEX)4.2表的管理CREATETABLEST(SNOCHAR(12),SNAMECHAR(8),SAGENUMBER(2,0),SSEXCHAR(2));創(chuàng)建一個序列SN：CREATESEQUENCESNINCREMENTBY1STARTWITH1MAXVALUE1000NOCYCLENOCACHE;4.2表的管理使用序列自動產生學號：INSERTINTOSTVALUES(‘2022090601’||TO_CHAR(SN.NEXTVAL,’fm00’),’張明’,21,’男’)；INSERTINTOSTVALUES(‘2022090601’||to_char(SN.NEXTVAL,’fm00’),’李小萌’,20,’女’)；其中函數TO_CHAR將序列數字轉換為字符。格式字符串“fm00”表示轉換為2位的字符串，空位用0填充。fm表示去掉轉換結果的空格查看序列是否起作用，學號是否自動生成：SELECT*FROMST;可以看到學號自動從01,02依次生成。4.2表的管理注意，當我們使用序列作為插入數據時，如果使用了“延遲段”技術，則跳過序列的第一個值。Oracle從11.2.0.1版本開始，提供了一個“延遲段創(chuàng)建”特性：即當我們創(chuàng)建了新的表(table)和序列(sequence)，在插入(insert)語句時，序列會跳過第一個值(1)。所以結果是插入的序列值從2(序列的第二個值)開始，而不是1開始。解決的方法是：把數據庫的“延遲段創(chuàng)建”特性改為FALSE（需要有相應的權限），SQL語句如下：

ALTERSYSTEMSETdeferred_segment_creation=FALSE;在創(chuàng)建表時讓SEGMENT立即執(zhí)行，SQL語句如下：CREATETABLEtbl_test(test_idNUMBERPRIMARYKEY,test_nameVARCHAR2(20))SEGMENTCREATIONIMMEDIATE；4.2表的管理5.序列的查看查看用戶擁有的序列可以查看DBA_SEQUENCES，查看當前用戶的序列可以查看USER_SEQUENCES。（1）查看當前用戶的所有序列SELECTSEQUENCE_OWNER,SEQUENCE_NAMEFROMDBA_SEQUENCESWHERESEQUENCE_OWNER='用戶名';（2）查看當前用戶所有的序列SELECT*FROMUSER_SEQUENCES;6.序列的刪除刪除序列使用DROPSEQUENCEsequence_name;例：刪除序列ids：DROPSEQUENCE；4.3數據查詢數據查詢是從數據庫中檢索出符合條件的數據記錄，是數據庫應用中最常用的操作，是數據庫的核心操作。其一般格式為：SELECT[ALL|DISTINCT]select_listFROM[schema.]table_name|[schema.]view_name[,[schema.]table_name|[schema.]view_name]……[WHEREsearch_condition][GROUPBYgroup_by_expression[HAVINGsearch_condition]][ORDERBYorder_expression[ASC|DESC]]各子句含義如下：ALL|DISTINCT：ALL表示篩選出表中滿足條件的所有記錄，一般情況下可省略；DISTINCT表示從查詢結果集中去掉重復的行。4.3數據查詢select_list：指定查詢的字段，如果要查詢的所有字段可以使用星號（*）代替。[schema.]table_name：指定要查詢的數據源的表名稱和它的方案名，如果表是當前數據庫連接用戶方案下的表，則方案名可以省略。[schema.]view_name:指定查詢的數據源的視圖名稱和它的方案名，方案名也可以省略。一、單表查詢單表查詢是指僅僅涉及一個表的查詢，是從一個表選出某些行列值，或從一個表中得到一些特定的數據。1.選擇表中的若干字段選擇表中全部的字段或部分字段也就是關系的投影運算。（1）選擇指定的字段當用戶只關心表中一部分字段的時候，這時可以在SELECT后select_list中指定要查詢的字段。4.3數據查詢例：查詢所有雇員的雇員編號與雇員姓名。SELECTEMPNO，ENAMEFROMEMP;例：查詢每個雇員的雇員姓名、雇員編號與雇員職務。SELECTENAME，EMPNO，JOBFROMEMP;（2）選擇所有字段SELECT子句中可以把所有字段名列出，或使用星號（*）顯示表中所有的字段例：查詢emp表中的所有字段。SELECT*FROMEMP;或SELECTEMPNO，ENAME，JOB，MGR，HIREDATE，SAL，COMM，DEPTNOFROMEMP;4.3數據查詢（3）使用計算字段SELECT后除了可以使用目標字段之外，還可以使用算術表達式進行計算、常量和函數，可以使用算術運算符和字符串運算符（||）進行計算，算術運算符包括加（+）、減（-）、乘（*）、除（/）和取模（%）運算。函數包括普通函數和集函數。例：查詢每個雇員的編號、雇員姓名和雇傭年限。SELECTEMPNO，ENAME，2023-TO_CHAR(HIREDATE,'YYYY’)FROMEMP;例：查詢每個雇員的編號、雇員姓名和雇傭年限。SELECTEMPNO，ENAME,'雇傭年限是'2023-TO_CHAR(HIREDATE,'YYYY’),'年'FROMEMP;（4）使用字段別名用戶可以通過指定別名來改變查詢結果的字段標題，用戶可以根據要求在SELECT語句中改變字段標題，語法格式如下：SELECTcolumn_name1[AS]alias,column_name2[AS]alias,········FROM[schema.]table_name|[schema.]view_name其中，column_name是要查詢的字段名；AS是為字段起別名的關鍵字，可以用空格替代；alias是為字段起的別名。例：查詢每個雇員的編號、雇員姓名和雇傭年限。SELECTEMPNO雇員編號，ENAME雇員姓名，2023-TO_CHAR(HIREDATE,'YYYY’)雇傭年限FROMEMP;4.3數據查詢（5）字符連接運算在Oracle中對字符串進行連接運算使用“||”，方法是在查詢中使用連接運算。通過連接運算可以將兩個字符串連接在一起。例：查詢每個雇員的編號、雇員姓名和雇傭年份。SELECTEMPNO雇員編號，ENAME雇員姓名，TO_CHAR(HIREDATE,’YYYY’)||‘年’雇傭年份

FROMEMP;2．DISTINCT關鍵字使用DISTINCT關鍵字可以從結果集中消除重復的行例：查詢所有雇員的職務，要求取消重復的行。SELECTDISTINCTJOBFROMEMP;3.選擇表中的行從表中篩選出滿足指定條件的行，可以在SELECT語句中使用WHERE子句。字符串和日期必須用單引號括起來。字符串數據區(qū)分大小寫。日期數據的格式是敏感的，默認的日期格式是DD-MON月-YY。（1）比較運算符WHERE子句允許使用的運算符包括以下幾種：=(等于)、<(小于)、>（大于）、<=(小于等于)、>=（大于等于）、<>或！=(不等于)、！>(不大于)、！<（不小于）。例：查詢部門20的雇員編號與雇員姓名。SELECTEMPNO，ENAMEFROMEMPWHEREDEPTNO=20;4.3數據查詢例：查詢工資大于3000的雇員。SELECT*FROMEMPWHERESAL>3000;（2）BETWEENAND根據一個屬性值范圍的查詢可以用BETWEENAND謂詞例：查詢工資在3000-5000的雇員，包含3000和5000兩個邊界值。SELECT*FROMEMPWHERESALBETWEEN3000AND5000;例：查詢工資不在3000-5000這個范圍的雇員。SELECT*FROMEMPWHERESALNOTBETWEEN3000AND5000;（3）使用查詢列表如果要查詢的字段的取值范圍不是一個連續(xù)的區(qū)間，而是一些離散的值，使用關鍵字IN進行查詢4.3數據查詢例：雇員職務分別為CLERK、ANALYST、MANAGER的雇員信息。SELECT*FROMEMPWHEREJOBIN('CLERK','ANALYST','MANAGER');例：雇員職務不是CLERK、ANALYST、MANAGER的雇員信息。SELECT*FROMEMPWHEREJOBNOTIN('CLERK','ANALYST','MANAGER');（4）字符串模式匹配其語句法為：[NOT]LIKE'string'[ESCAPE'<換碼字符>']其中string是匹配字符串，匹配字符串可以是一個完整的字符串，也可以使用%和_兩種匹配符。%代表字符串中包含零個或多個任意字符，例如：a%b表示以a開頭，以b結尾的任意長度的字符串，如adb，affgjb，ab等都滿足該匹配串；_代表字符串中包含一個任意字符,例如：a_b表示以a開頭，以b結尾的長度為3的任意字符串，如ahb，atb等都滿足該匹配串。NOT關鍵字是對LIKE運算符的否定，表示可以查詢那些不匹配的記錄。4.3數據查詢①匹配固定值如果匹配串中無通配符時LIKE和”=“等價。例：查詢雇員職務為‘CLERK’的雇員信息。SELECT*FROMEMPWHEREJOBLIKE'CLERK';等價于SELECT*FROMEMPWHEREJOB='CLERK';②含有通配符的匹配串如果匹配串中含有通配符時LIKE和”=“不等價。例：查詢姓名以M開頭的雇員信息。SELECT*FROMEMPWHEREENAMELIKE'M%';例：查詢姓名不以M開頭的雇員信息。4.3數據查詢SELECT*FROMEMPWHEREENAMENOTLIKE'M%';例：查詢姓名的倒數第二個字母是E的雇員信息。SELECT*FROMEMPWHEREENAMELIKE'%E_';③ESCAPE短語用戶要查詢的字符串本身就含有%或_時,就需要使用ESCAPE'<換碼字符>'短語對通配符進行轉義，就是將通配符轉義為普通字符。查詢DEPT表中部門名為SALES_A的部門信息。首先，執(zhí)行：INSERTINTODEPTVALUES(60,'SALES_A','濟南');INSERTINTODEPTVALUES(70,'SALESCA','青島');無ESCAPE短語，執(zhí)行查詢：SELECT*FROMDEPTWHEREDNAMELIKE'SALES_A';4.3數據查詢指定ESCAPE短語，執(zhí)行查詢：SELECT*FROMDEPTWHEREDNAMELIKE'SALES\_A'ESCAPE'\';（5）空值的判定空值是不知道或無意義的值，該運算就是：IS[NOT]NULL例：查詢沒有津貼的雇員信息。SELECT*FROMEMPWHERECOMMISNULL;例：查詢所有有津貼的雇員信息。SELECT*FROMEMPWHERECOMMISNOTNULL;（6）多重條件多重條件可以使用AND和OR進行連接多個條件。AND的優(yōu)先級高于OR，可以使用圓括號改變優(yōu)先級。4.3數據查詢例：查詢工資在3000-5000的雇員，包含3000和5000兩個邊界值。SELECT*FROMEMPWHERESAL>=3000ANDSAL<=5000;等價于：SELECT*FROMEMPWHERESALBETWEEN3000AND5000;例：查詢雇員職務分別為CLERK、ANALYST、MANAGER的雇員信息。SELECT*FROMEMPWHEREJOB='CLERK'ORJOB='ANALYST'ORJOB='MANAGER';4.排序如果要把查詢結果排序顯示，可以使用短語ORDERBY，表示對查詢結果按照一個字段或多個字段的升序（ASC）或降序（DESC）顯示。如果不指明排序順序，默認的排序順序為升序。如果要降序，必須指定DESC關鍵字。4.3數據查詢（1）按單字段排序例：查詢所有的雇員信息，按工資降序排序顯示。SELECT*FROMEMPORDERBYSALDESC;（2）按多字段排序排序是可以按多字段進行排序，先按第一個字段，然后按第二個字段、第三個字段.....。例：查詢所有的雇員信息，先按部門編號升序排序，再按雇員編號的降序排序。SELECT*FROMEMPORDERBYDEPTNO,EMPNODESC;（3）按字段別名排序例：查詢每個雇員的編號、雇員姓名和雇傭年限，按雇傭年限降序排序顯示。SELECTEMPNO雇員編號，ENAME雇員姓名，2023-TO_CHAR(HIREDATE,‘YYYY’)雇傭年限

FROMEMPORDERBY雇傭年限DESC：4.3數據查詢（4）按字段編號排序排序時可以按照SELECT后的字段列表編號排序，字段列表編號從左到右依次編號1、2、3…。例：查詢每個雇員的編號、雇員姓名和雇傭年限，按雇傭年限降序排序顯示。SELECTEMPNO雇員編號，ENAME雇員姓名，2023-TO_CHAR(HIREDATE,'YYYY’)雇傭年限FROMEMPORDERBY3DESC：5.集函數Oracle中提供了一些統(tǒng)計計算的集函數，可以對表中的數據進行分類、統(tǒng)計、匯總等操作，常用的集函數如下表所示。4.3數據查詢例：查詢所有雇員的平均工資。SELECTAVG(SAL)FROMEMP;例：查詢10號部門雇員的最高工資。SELECTMAX(SAL)FROMEMPWHEREDEPTNO=10;例：求所有雇員的人數。SELECTCOUNT(*)FROMEMP;6.分組GROUPBY子句。該子句的功能是根據指定的一個字段或多個字段值分組，值相等的為一組，有幾個不同的值就有幾個不同的分組。如果分組中的字段是多個，那么先按照第一個字段值分組，也就是將第一個分組字段值相同的行為一組，然后在每個組內再按照第二個字段值進行分組，也就是說最終是基于這些列的唯一組合進行分組的，最后在分好的組中進行匯總。4.3數據查詢使用GROUPBY子句時，需要注意以下幾個原則：使用GROUPBY子句時，將分組字段值相同的行做為一組，而且每組只產生一個匯總結果，每個組織返回一行，不返回詳細信息。在SELECT子句的后面，只能有兩種類型的表達式，一種是出現在GROUPBY子句后面的字段名或者是它的非集函數表達式，另一種是其他非分組字段的集函數表達式。如果在該查詢語句中使用了WHERE子句，那么先在表中查詢滿足WHERE條件的記錄，再將這些記錄按照GROUPBY子句分組，也就是說WHERE子句先生效。GROUPBY子句后面可以出現多個分組字段名，它們用逗號隔開。例：查詢每個部門的部門編號和平均工資。SELECTDEPTNO,AVG(SAL)FROMEMPGROUPBYDEPTNO;4.3數據查詢例：查詢各個部門中的部門編號及各種職務的雇員人數，查詢結果按部門編號升序排序。SELECTDEPTNO,JOB,COUNT(*)AS人數FROMEMPGROUPBYDEPTNO,JOBORDERBYDEPTNO;7.HAVING子句如果對分組查詢的結果進行篩選，要使用HAVING子句。例：查詢平均工資大于3000的部門編號和平均工資。SELECTDEPTNO,AVG(SAL)FROMEMPGROUPBYDEPTNOHAVINGAVG(SAL)>3000;例：查詢1982年后參加工作的、雇員人數超過了2人的部門編號和人數。4.3數據查詢SELECTDEPTNO,COUNT(*)AS人數FROMEMPWHEREHIREDATE>='1-1月-1982'GROUPBYDEPTNOHAVINGCOUNT(*)>=2;8.SQL函數單行函數：單行函數返回表中查詢的每一行的值。常用的單行函數包括字符函數，數值函數，日期函數，轉換函數等。（1）DUAL表DUAL是Oracle中的一個實際存在的表，任何用戶均可讀取，常用在沒有目標表的SELECT語句塊中。Oracle中的DUAL表是一個單行單列的表，是Oracle與數據字典一起自動創(chuàng)建的一個表。這個表只有1列：DUMMY，數據類型為VARCHAR2(1)，只有一個數據'X'。Oracle有內部邏輯保證DUAL表中永遠只有一條數據。例：查詢當前的系統(tǒng)日期。SELECTSYSDATEFROMDUAL;4.3數據查詢（2）數值函數數值函數對數值數據進行計算，常用的數值函數如表所示：4.3數據查詢（3）日期函數Oracle數據庫中的日期默認格式為DD-MON-YY，可通過參數NLS_DATE_FORMAT設置當前會話的日期格式，可以設置通過參數NLS_DATE_LANGUAGE設置當前日期的字符集。例如：設置日期格式為YYYY-MM-DDALTERSESSIONSETNLS_DATE_FORMAT='YYYY-MM-DD';例如：設置當前日期為中文字符集。ALTERSESSIONSETNLS_DATE_LANGUAGE='Simplifiedchinese';常用的日期函數如表所示：4.3數據查詢常用的日期轉換格式字符，如表所示：4.3數據查詢（4）字符函數字符函數接受字符輸入，用于對字符串進行處理，并返回字符或數值。主要的字符函數如表所示：4.3數據查詢（5）轉換函數①自動類型轉換字符串到數值，數值到字符串，字符串到日期，日期到字符串，可以實現自動轉換。例：SELECT'33'+15FROMDUAL;

結果為：48例：SELECT'33'||15FROMDUAL;

結果為：3315例:SELECT*FROMEMPWHEREHIREDATE>='1-1月-1982';

例：INSERTINTOEMP(EMPNO,ENAME,HIREDATE)VALUES(2023,'黃大鵬','1-1月-2023');4.3數據查詢②轉換函數常用的轉換函數如表4-12所示，常用的數值轉換符如表4-13所示。4.3數據查詢（6）空值函數常用的空值函數如表4-14所示4.3數據查詢（7）其他函數Oracle還有一些函數，如DECODE，這些函數也很有用，如表4-15所示。二、連接查詢從兩個或多個表中獲取數據，這種查詢稱為連接查詢。1.交叉連接查詢交叉連接是不帶連接條件的連接，是兩個表所有的連接可能，也就是兩個表的笛卡爾積的結果。交叉連接的連接謂詞為CROSSJOIN。4.3數據查詢例：SELECT*FROMEMPCROSSJOINDEPT;

或：SELECT*FROMEMP，DEPT;2.內連接只連接兩表中字段值滿足連接條件的記錄，只有滿足連接條件的數據才會出現在結果集中。（1）相等內連接使用等號（=）指定連接條件的連接查詢，相等內連接的連接短語為[INNER]JOIN。例：查詢所有雇員的姓名、所在的部門的編號和名稱。SELECTENAME,EMP.DEPTNO,DNAMEFROMEMPINNERJOINDEPTONEMP.DEPTNO=DEPT.DEPTNO;或：SELECTENAME,EMP.DEPTNO,DNAMEFROMEMP,DEPTWHEREEMP.DEPTNO=DEPT.DEPTNO;4.3數據查詢例：查詢工資大于4000的雇員姓名、所在的部門的編號和名稱。SELECTENAME,EMP.DEPTNO,DNAMEFROMEMPINNERJOINDEPTONEMP.DEPTNO=DEPT.DEPTNOANDSAL>4000;或：SELECTENAME,EMP.DEPTNO,DNAMEFROMEMPINNERJOINDEPTONEMP.DEPTNO=DEPT.DEPTNOWHERESAL>4000;或：SELECTENAME,EMP.DEPTNO,DNAMEFROMEMP,DEPTWHEREEMP.DEPTNO=DEPT.DEPTNOANDSAL>4000;（2）非等內連接非等內連接是在連接條件中使用除“=”運算符以外的其它運算符。4.3數據查詢例：查詢雇員姓名，工資和所屬工資等級。SELECTENAME,SAL,GRADEFROMEMPINNERJOINSALGRADEONSALBETWEENLOSALANDHISAL;（3）自然連接自然連接是使用等號（=）指定連接條件的連接查詢，連接時兩表必須有同名字段，且查詢結果會自動去掉重復的字段。連接條件是兩表同名字段的（=）比較，且是隱含的。連接字段引用時不能加表名，自然連接的連接短語為NATURALJOIN，其一般語法格式如下：SELECTselectlistFROMtable_name1NATURALJOINtable_name24.3數據查詢例：查詢工資大于4000的雇員姓名、所在的部門的編號和名稱。SELECTENAME,DEPTNO,DNAMEFROMEMPNATURALJOINDEPTWHERESAL>4000;DEPTNO字段前不能指定表名。當兩個表有一個或多個字段同名時可以用USING指定需要連接的字段。例：查詢工資大于4000的雇員姓名、所在的部門的編號和名稱。SELECTENAME,DEPTNO,DNAMEFROMEMPJOINDEPTUSING(DEPTNO)WHERESAL>4000;指定USING(DEPTNO)時，可省略NATURAL。（4）自連接一個表與其自己進行連接，稱為表的自連接。4.3數據查詢例：查詢所有雇員編號、雇員姓名和雇員的經理姓名。SELECTA.EMPNO,A.ENAME,B.ENAMEFROMEMPAJOINEMPBONA.MGR=B.EMPNO;3.外連接普通連接操作只輸出滿足連接條件的記錄，把普通連接中舍棄的不滿足連接條件的記錄一并輸出，而在其他字段上填空值（NULL），這就是外連接，外連接的連接短語為OUTERJOIN或（+）。（1）左外連接左外連接是把左表中舍棄的記錄也一并輸出，在其他字段填空值（NULL），左外連接的連接短語為LEFT[OUTER]JOIN或（+）。例：查詢雇員的姓名、工資和所在的部門名稱及沒有屬于任何部門的雇員。首先執(zhí)行：INSERTINTOEMP(EMPNO,ENAME,JOB,DEPTNO)VALUES(7345,'李萍',3500，NULL);4.3數據查詢然后執(zhí)行：SELECTENAME,SAL,DNAMEFROMEMPLEFTJOINDEPTONEMP.DEPTNO=DEPT.DEPTNO;或SELECTENAME,SAL,DNAMEFROMEMP,DEPTWHEREEMP.DEPTNO=DEPT.DEPTNO(+);（2）右外連接右外連接是把左表中舍棄的記錄也一并輸出，在其他字段填空值（NULL），右外連接的連接短語為RIGHT[OUTER]JOIN或（+）。例：查詢雇員的姓名、工資和所在的部門名稱及沒有任何雇員的部門。SELECTENAME,SAL,DNAMEFROMEMPRIGHTJOINDEPTONEMP.DEPTNO=DEPT.DEPTNO;或SELECTENAME,SAL,DNAMEFROMEMP,DEPTWHEREEMP.DEPTNO(+)=DEPT.DEPTNO;4.3數據查詢（3）完全外連接完全外連接是連接時把左表和右表中舍棄的記錄也一并輸出，完全外連接的連接短語為FULL[OUTER]JOIN。例：查詢雇員的姓名、工資和所在的部門名稱、沒有屬于任何部門的雇員及沒有任何雇員的部門。SELECTENAME,SAL,DNAMEFROMEMPFULLJOINDEPTONEMP.DEPTNO=DEPT.DEPTNO;三、子查詢我們把SELECT-FROM-WHERE語句稱為一個查詢塊，如果將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢。4.3數據查詢子查詢的一般形式為。SELECTselectlist1FROMtable_name1WHEREexproperator

(SELECTselectlist2FROMtable_name2);1.單行子查詢子查詢結果返回的是一行的子查詢稱為單行子查詢，可以返回單行單字段，也可以返回單行多字段。如果子查詢返回多字段，則對應的比較條件中也應該出現多字段。（1）單行單字段例：查詢與SCOTT在同一個部門的雇員姓名。SELECTENAMEFROMEMPWHEREDEPTNOIN(SELECTDEPTNOFROMEMPWHEREENAME='SCOTT');4.3數據查詢或：SELECTENAMEFROMEMPWHEREDEPTNO=(SELECTDEPTNOFROMEMPWHEREENAME='SCOTT');（2）單行多字段例：查詢與SCOTT在同一個部門且職務相同的雇員姓名。SELECTENAMEFROMEMPWHERE(DEPTNO,JOB)IN(SELECTDEPTNO,JOBFROMEMPWHEREENAME='SCOTT');4.3數據查詢或：SELECTENAMEFROMEMPWHERE(DEPTNO,JOB)=(SELECTDEPTNO,JOBFROMEMPWHEREENAME='SCOTT');2.多行子查詢子查詢返回多行的查詢稱為多行子查詢。多行子查詢可以返回多行單字段，也可以返回多行多字段，如果子查詢返回多字段，則對應的比較條件中也應該出現多字段。多行子查詢可以使用IN、ANY、ALL、EXISTS等謂詞。4.3數據查詢（1）IN謂詞IN是用來檢測和集合列表里某個值相同的運算。例：查詢工資大于2000雇員的雇員經理。SELECTMGRFROMEMPWHEREEMPNOIN(SELECTEMPNOFROMEMPWHERESAL>2000);例：查詢與工資大于1500雇員的雇員職務、部門相同的雇員姓名。SELECTENAMEFROMEMPWHERE(DEPTNO,JOB)IN(SELECTDEPTNO,JOBFROMEMPWHERESAL>1500)

4.3數據查詢（2）ANY、ALL謂詞ANY表示任意一個值，ALL表示所有值。ALL和ALL必須和比較運算符（=，<，>，>=，<=，!=）配合使用。例：查詢比30部門某個雇員工資高的雇員雇員編號、雇員姓名和雇員工資。SELECTEMPNO,ENAME,SALFROMEMPWHERESAL>ANY(SELECTSAL

FROMEMP

WHEREDEPTNO=30);例：查詢比30部門所有雇員工資高的雇員雇員編號、雇員姓名和雇員工資。SELECTEMPNO,ENAME,SALFROMEMPWHERESAL>ALL(SELECTSAL

FROMEMP

WHEREDEPTNO=30);4.3數據查詢（3）EXISTS謂詞EXISTS謂詞稱為存在謂詞，用來檢測子查詢結果是否存在某些行，如果子查詢非空（至少有一行），則該謂詞返回邏輯真，否則返回邏輯假。例：查詢在‘SALES’部門工作的雇員姓名SELECTEMPNO,ENAMEFROMEMPWHEREEXISTS(SELECT*FROMDEPTWHEREEMP.DEPTNO=DEPT.DEPTNOANDDNAME='SALES');3.替代表達式的子查詢如果子查詢出現在SELECT后的字段列表中稱為替代表達式的子查詢。4.3數據查詢例：查詢在部門30工作的雇員編號、雇員姓名部門30的平均工資。SELECTEMPNO,ENAME,(SELECTAVG(SAL)FROMEMPWHEREDEPTNO=30)部門30的平均工資FROMEMPWHEREDEPTNO=30;4.在FROM子句中使用子查詢在FROM子句中也可以使用子查詢，如果子查詢出現在FROM子句中，