第4章管理數(shù)據(jù)

陜西工院2023/2/4Oracle11g

數(shù)據(jù)庫技術(shù)蔡蘇亞主講caisuya@126.com第4章管理數(shù)據(jù)4.1SQL*Plus4.2SQL語言基礎(chǔ)4.1SQL*Plus4.1.1SQL*Plus簡介4.1.2啟動和退出SQL*Plus4.1.3交互式命令4.1.4編輯和運行語句4.1.5格式化輸出4.1.6自定義SQL*PLUS環(huán)境4.1.1SQL*Plus簡介

在Oracle系統(tǒng)中，SQL*Plus工具用于執(zhí)行SQL或PL/SQL語言，是數(shù)據(jù)庫管理員操作數(shù)據(jù)庫中數(shù)據(jù)并執(zhí)行各種數(shù)據(jù)庫管理最直接和有效的工具，可以滿足Oracle數(shù)據(jù)庫管理員的大部分需求。SQL*Plus工具在服務(wù)器端和客戶端都可以使用，在安裝Oracle服務(wù)器端或客戶端軟件時都會自動安裝SQL*Plus工具。SQL*Plus工具的主要功能有：1）啟動關(guān)閉數(shù)據(jù)庫實例。2）編輯、執(zhí)行SQL或PL/SQL語句。3）創(chuàng)建、編輯和執(zhí)行腳本文件。4）格式化輸出結(jié)果。5）設(shè)置環(huán)境參數(shù)。4.1.2啟動和退出SQL*Plus1以命令行方式啟動SQL*Plus在DOS方式下輸入“sqlplus”命令，接著再輸入用戶名和口令，這里可以輸入默認的數(shù)據(jù)庫用戶system和相應(yīng)的口令。當出現(xiàn)SQL>提示符后，就可以使用SQL*Plus工具

2以圖形界面方式啟動SQL*Plus在開始菜單中，選擇程序→Oracle-OraDb10g_home1→應(yīng)用程序開發(fā)→SQLPlus，輸入對應(yīng)的口令，在“主機字符串”一欄中輸入服務(wù)名“orcl”，如果在本機登錄，可以不輸入內(nèi)容保留空白，則會連接到默認的數(shù)據(jù)庫實例。單擊【確定】按鈕，進入SQL*PLUS主界面。3退出SQL*Plus在SQL>命令提示符后面輸入quit或exit命令，回車即可退出SQL*Plus工具。4.1.3交互式命令

SQL*Plus命令大致可以分為交互式命令、編輯命令和報表命令。4.1.3交互式命令下面介紹一些示例。SQL>CONNsystem/orcl@orcl;SQL>SHOWUSER;SQL>SELECTSYSDATEFROMDUAL;SQL>SELECT*FROMALL_USERS;SQL>SHOWSGA下面的語句用來創(chuàng)建一個假脫機文件。SQL>SPOOLD:\file.txt --開始假脫機輸出SQL>SHOWSGASQL>SPOOLOFF --假脫機結(jié)束SQL>DISCONNECT4.1.4編輯和運行語句

SQL*PLus的常用編輯命令。4.1.4編輯和運行語句下面語句以HR用戶的身份新建BOOK表，表結(jié)構(gòu)見表。首先，如果當前Oracle數(shù)據(jù)庫系統(tǒng)中的HR用戶處于鎖定狀態(tài)，可以先執(zhí)行下面的語句對該用戶解鎖。SQL>connsystem/orclassysdbaSQL>alteruserhraccountunlockSQL>connhr/hr已連接4.1.4編輯和運行語句接著以HR用戶的身份登錄，在SQL*Plus中輸入并執(zhí)行以下語句。SQL>CREATETABLEBOOK(NO CHAR(8)PRIMARYKEY,TITLE VARCHAR2(50)NOTNULL,AUTHOR VARCHAR2(20),PUBLISH VARCHAR2(20),PUB_DATE DATE,PRICE NUMBER(6,2));表已創(chuàng)建。如果用戶在輸入上面的語句時，少輸了最后的右括號，執(zhí)行時就會出錯，如下面的形式：SQL>CREATETABLEBOOK(NO CHAR(8)PRIMARYKEY,4.1.4編輯和運行語句TITLE VARCHAR2(50)NOTNULL,AUTHOR VARCHAR2(20),PUBLISH VARCHAR2(20),PUB_DATE DATE,PRICE NUMBER(6,2);錯誤信息顯示如下：PRICENUMBER(6,2)*第7行出現(xiàn)錯誤:ORA-00907:缺少右括號下面用SQL*Plus的編輯命令修改此錯誤。先用List命令列出發(fā)生錯誤的第7行。SQL>L77*PRICENUMBER(6,2)4.1.4編輯和運行語句再用Change命令修改發(fā)生錯誤的地方，命令格式是“C/<舊文本>/<新文本>”。SQL>C/)/))7*PRICENUMBER(6,2))接著再用List命令列出緩沖區(qū)的全部語句：SQL>L1CREATETABLEBOOK(2NO CHAR(8)PRIMARYKEY,3TITLE VARCHAR2(50)NOTNULL,4AUTHOR VARCHAR2(20),5PUBLISH VARCHAR2(20),6PUB_DATE DATE,7*PRICE NUMBER(6,2))4.1.4編輯和運行語句用Run命令重新執(zhí)行如下：SQL>R1CREATETABLEBOOK(2NO CHAR(8)PRIMARYKEY,3TITLE VARCHAR2(50)NOTNULL,4AUTHOR VARCHAR2(20),5PUBLISH VARCHAR2(20),6PUB_DATE DATE,7*PRICE NUMBER(6,2))表已創(chuàng)建。接著，在BOOK表中插入數(shù)據(jù)。在SQL*Plus中輸入下面的語句。4.1.4編輯和運行語句SQL>INSERTINTOBOOKVALUES('10000001','Oracle9i數(shù)據(jù)庫系統(tǒng)管理','李代平等','冶金工業(yè)出版社',TO_DATE('2003/01/01','yyyy/mm/dd'),38.00);已創(chuàng)建1行。SQL>INSERTINTOBOOKVALUES('10000002','Oracle9i中文版入門和提高','趙松濤','人民郵電出版社',TO_DATE('2002/07/01','yyyy/mm/dd'),35.00);已創(chuàng)建1行。SQL>INSERTINTOBOOKVALUES('10000003','Oracle9i開發(fā)指南:PL/SQL程序設(shè)計','JoanCasteel','清華大學出版社',TO_DATE('2004/04/03','yyyy/mm/dd'),49.00);已創(chuàng)建1行。其中，TO_DATE是數(shù)據(jù)類型轉(zhuǎn)換函數(shù)，用于將字符串轉(zhuǎn)換為日期型。4.1.4編輯和運行語句下面的語句用來查詢BOOK表中的所有記錄，并且將查詢語句保存到book.SQL腳本文件中，并調(diào)用腳本文件。SQL>SELECT*FROMBOOK;SQL>SAVEd:\book.SQLSQL>DEL清除緩沖區(qū)的內(nèi)容SQL>R執(zhí)行緩沖區(qū)的內(nèi)容SQL>GETd:\book.SQLSQL>RSQL>@d:\book.SQL4.1.5格式化輸出

SQL*PLUS可以格式化輸出。1.COLUMN命令:可以修改列標題名稱、顯示寬度以及列標題的對齊格式等。COLUMN命令對列進行格式化后，其輸出效果一直有效，直到重新設(shè)置該列的輸出格式或者退出SQL*Plus。COLUMN命令的FORMAT子句用來定義列的寬帶和顯示格式，如果字段是字符型數(shù)據(jù)，則用An對其進行格式化。如果是數(shù)字型數(shù)據(jù)，可使用的格式化字符有“90$.，S”。4.1.5格式化輸出其中，COLUMN命令的HEADING子句用來定義列標題。JUSTIFY子句表示對齊方式，可以選擇的對齊方式有LEFT、CENTER和RIGHT。如果要還原列的默認輸出格式，則可以使用COLUMN命令的CLEAR子句，如還原TITLE字段的輸出格式命令為：SQL>COLUMNTITLECLEAR如果要還原所有列的默認輸出格式，則使用命令:SQL>CLEARCOLUMN例：查詢BOOK表并格式化輸出。SQL>COLTITLEFORMATA30HEADING'書名'JUSTIFYRIGHT;SQL>COLAUTHORFORMATA20HEADING'作者'JUSTIFYLEFT;SQL>SELECTTITLE,AUTHORFROMBOOK;4.1.5格式化輸出2.REPHEADER命令設(shè)置報表的標題，REPFOOTER命令設(shè)置報表的腳注。報表指一個完整的查詢結(jié)果。下面的語句查詢BOOK表的所有記錄，設(shè)置報表的標題為“圖書信息表”，報表的腳注為“制表人：孟欣”。SQL>REPHCENTER'圖書信息表';SQL>REPFRIGHT'制表人：孟欣';3.TTITLE命令設(shè)置每頁的頁標題格式，BTITLE命令設(shè)置每頁的頁尾格式。每頁所能顯示的行數(shù)由“SETpagesizen”命令來指定。下面的語句查詢HR.employees表的first_name，last_name字段，設(shè)置每頁的標題為“雇員表信息”，每頁的頁尾為“每頁頁尾”。SQL>TTITLECENTER'雇員表信息';SQL>BTITLERIGHT'每頁頁尾';SQL>SELECTfirst_name,last_nameFROMhr.employees;4.1.5格式化輸出SQL*Plus工具可以使用ON或OFF來打開或關(guān)閉報表和頁的標題及腳注的設(shè)置，即分別執(zhí)行下面的命令：SQL>TTITLEonSQL>TTITLEoffSQL>BTTILEonSQL>BTTILEoffSQL>REPHEADERonSQL>REPHEADERoffSQL>REPFOOTERonSQL>REPFOOTERoff4.1.6自定義SQL*PLUS環(huán)境SQL*Plus通過SET命令和菜單兩種方式設(shè)置環(huán)境參數(shù)。1.SET命令：SET命令通過設(shè)置系統(tǒng)變量的值更改SQL*Plus的環(huán)境設(shè)置。1）ECHO2）PAGESIZE3）LINESIZE4）SERVEROUTPUT5）TRIMSPOOL2.環(huán)境對話框：用戶可以直接單擊SQL*Plus中的選項菜單中的環(huán)境菜單項，設(shè)置環(huán)境參數(shù)。4.2SQL語言基礎(chǔ)

4.2.1概述4.2.2使用SELECT語句查詢數(shù)據(jù)4.2.3使用INSERT語句插入數(shù)據(jù)4.2.4使用UPDATE語句更新數(shù)據(jù)4.2.5使用DELETE語句刪除數(shù)據(jù)4.2.6事務(wù)控制命令4.2.1概述SQL（StructureQueryLanguage，結(jié)構(gòu)化查詢語言）于1975年由Boyce和Chamberlin提出，用來實現(xiàn)關(guān)系運算中查詢、選擇等操作，是一種綜合的、功能極強的同時又簡單易學的語言。作為關(guān)系數(shù)據(jù)庫操作的標準語言，目前已被ANSI（美國國家標準化組織）正式批準為數(shù)據(jù)庫的工業(yè)標準。SQL語言功能極強，可以完成數(shù)據(jù)庫整個生命周期的全部活動。SQL是一種面向集合的操作語言，即操作對象和操作結(jié)果都是元組的集合。SQL語句可以在Oracle提供的SQL*Plus或SQL*Plus工作單工具中直接執(zhí)行，并返回執(zhí)行結(jié)果。SQL還可以嵌入到其他高級程序設(shè)計語言中，實現(xiàn)對后臺數(shù)據(jù)庫的訪問。SQL語言按照功能可以分為3類：DDL、DML和DCL。4.2.2使用SELECT語句查詢數(shù)據(jù)

SQL語言使用SELECT語句實現(xiàn)對數(shù)據(jù)表的任何查詢，包括選擇符合條件的行或列及其他操作等。常用的SELECT語法格式如下：SELECT字段1,字段2,……FROM表1[,表2]…WHERE查詢條件GROUPBY分組字段1[,分組字段2]…HAVING分組條件ORDERBY列1[,列2]……4.2.2使用SELECT語句查詢數(shù)據(jù)1.單表查詢下面的語句查詢DEPT表中的所有數(shù)據(jù)。SQL>SELECT*FROMSCOTT.DEPT;下面的語句查詢EMP表的雇員編號、雇員姓名和工資信息。SQL>SELECTEMPNO,ENAME,SALFROMSCOTT.EMP;下面的語句查詢EMP表中的工作種類（Job），去掉重復的記錄。SQL>SELECTDISTINCTJOBFROMSCOTT.EMP;下面的語句查詢雇員姓名（ENAME）為“BLAKE”的雇員編號、姓名、工作和雇用日期。SQL>SELECT*FROMSCOTT.EMPWHEREENAME='BLAKE';4.2.2使用SELECT語句查詢數(shù)據(jù)查詢條件運算符。4.2.2使用SELECT語句查詢數(shù)據(jù)下面的語句查詢EMP表雇員以“S”開頭的雇員姓名。SQL>SELECTENAMEFROMSCOTT.EMPWHEREENAMELIKE'S％';下面的語句查詢EMP表，對姓名按字母升序排列。SQL>SELECTENAMEFROMSCOTT.EMPORDERBYENAMEASC;下面的語句按照工作種類（JOB）分組統(tǒng)計EMP表中各部門的員工人數(shù)。SQL>SELECTJOB,COUNT(*)FROMSCOTT.EMPGROUPBYJOB;其中，COUNT函數(shù)用來統(tǒng)計符合條件的記錄行數(shù)。GROUPBY語句還可以使用HAVING子句來檢查分組的各組記錄是否滿足條件。相對于WHERE語句而言，HAVING只能配合GROUPBY語句使用。4.2.2使用SELECT語句查詢數(shù)據(jù)2.多表查詢指從多個有關(guān)聯(lián)的表中查詢數(shù)據(jù)。一般來說，多表查詢的表要用等值連接聯(lián)系起來，如果沒有連接，則查詢結(jié)果是這多個查詢表的笛卡爾積。下面的語句查詢雇員姓名和所在部門名稱。SQL>SELECTENAME,DNAMEFROMSCOTT.EMPA,SCOTT.DEPTBWHEREA.DEPTNO=B.DEPTNO;下面的語句查詢SALES部門的雇員姓名。SQL>SELECTENAMEFROMSCOTT.EMPA,SCOTT.DEPTBWHEREA.DEPTNO=B.DEPTNOANDB.DNAME='SALES';4.2.2使用SELECT語句查詢數(shù)據(jù)3.嵌套查詢指一個SELECT查詢中包含一個以上的子查詢，所謂子查詢指嵌套在另一個SELECT、INSERT、UPDATE或DELETE語句中的SELECT查詢語句。子查詢的語法與SELECT語法類似，但有所限制，如子查詢不能含有ORDERBY和INTO等關(guān)鍵字。下面的語句也可以查詢“SALES”部門的雇員姓名。SQL>SELECTENAMEFROMSCOTT.EMPWHEREDEPTNOIN(SELECTDEPTNOFROMSCOTT.DEPTWHEREDNAME='SALES');下面的語句在EMP表中查詢比平均工資高的雇員名單。SQL>SELECTENAMEFROMSCOTT.EMPWHERESAL>(SELECTAVG(SAL)FROMSCOTT.EMP);4.2.2使用SELECT語句查詢數(shù)據(jù)4.使用函數(shù)的查詢?？梢允褂玫暮瘮?shù)列表4.2.2使用SELECT語句查詢數(shù)據(jù)可以使用的函數(shù)（續(xù)表）4.2.2使用SELECT語句查詢數(shù)據(jù)下面的語句查詢雇員總?cè)藬?shù)。SQL>SELECTCOUNT(*)FROMSCOTT.EMP;下面的語句把查詢出來的部門名稱以小寫字符顯示出來。SQL>SELECTLOWER(DNAME)AS"部門名稱"FROMSCOTT.DEPT;下面的語句查詢“2008/01/01”和“2006/01/01”兩個日期之間的月份間隔。SQL>SELECTMONTHS_BETWEEN(TO_DATE('2008/01/01','yyyy/mm/dd'),TO_DATE('2006/01/01','yyyy/mm/dd'))AS"月份間隔"FROMDUAL;下面的語句使用TO_NUMBER函數(shù)用于求和的示例。SQL>SELECTTO_NUMBER('100')+TO_NUMBER('200')AS"求和"FROMDUAL;4.2.3使用INSERT語句插入數(shù)據(jù)

SQL語言用INSERT語句在數(shù)據(jù)表中插入數(shù)據(jù)。INSERT語句的使用語法一般有如下兩種：INSERTINTO表名[字段1,字段2,……]VALUES(值1,值2,……)INSERTINTO表名[字段1,字段2,……]SELECT(字段1,字段2,……)FROM其他表名其中，第1條插入單條記錄，第2條用于把從其他表中查詢出來的數(shù)據(jù)插入到當前表中，用于多條記錄的插入。無論是哪一種用法，都應(yīng)該注意要插入的值與要插入的字段相互對應(yīng)。1.單行記錄的插入SQL>INSERTINTOSCOTT.EMPVALUES(7700,'John','ANALYS',7902,'08-9月-81',2500,'0',20);4.2.3使用INSERT語句插入數(shù)據(jù)SQL>INSERTINTOSCOTT.EMP(EMPNO,ENAME,JOB)VALUES(7100,'Mary','ANALYS')2多行記錄的插入新建NEWEMP表，與EMP表相同結(jié)構(gòu)，并將EMP表數(shù)據(jù)插入NEWEMP表。SQL>CREATETABLEscott.NEWEMP(EMPNONUMBER(5,0)NOTNULL,ENAMEVARCHAR2(10),JOBVARCHAR2(9),MGRNUMBER(5,0),HIREDATEDATE,SALNUMBER(7,2),COMMNUMBER(7,2),DEPTNONUMBER(2,0))SQL>INSERTINTOSCOTT.NEWEMPSELECT*FROMSCOTT.EMP;4.2.4使用UPDATE語句更新數(shù)據(jù)

UPDATE語句的一般語法如下：UPDATE表名SET字段1=值1[,字段2=值2]……WHERE條件表達式;其中，UPDATE后的表名指定要更新的表，SET指定要更新的字段及其相應(yīng)的值，WHERE指定更新條件，如果沒有指定更新條件，則對表中所有記錄進行更新。下面的語句為雇員“BLAKE”加薪10％。SQL>UPDATESCOTT.EMPSETSAL=SAL*1.1WHEREENAME='BLAKE'下面的語句為EMP表中的所有雇員加薪10％。SQL>UPDATESCOTT.EMPSETSAL=SAL*1.1;4.2.5使用DELETE語句刪除數(shù)據(jù)1使用DELETE語句刪除記錄DELETE語句刪除數(shù)據(jù)表中的記錄，語法格式如下：DELETEFROM表名[Where條件];其中，F(xiàn)ROM后面的表名指定要刪除數(shù)據(jù)表，WHERE子句指定要刪除數(shù)據(jù)的條件。值得注意的是，使用DELETE語句刪除表中數(shù)據(jù)時，并不能釋放被占用的數(shù)據(jù)塊空間，只是把那些被刪除的數(shù)據(jù)塊標記為無效（Unused），將來可以回退（Rollback）恢復過來。下面的語句刪除NEWEMP表中的姓名為Mary的記錄。SQL>DELETEFROMSCOTT.NEWEMPWHEREENAME='Mary'下面的語句刪除NEWEMP表中的所有記錄。SQL>DELETEFROMSCOTT.NEWEMP;4.2.5使用DELETE語句刪除數(shù)據(jù)2使用TRUNCATE語句刪除所有記錄TRUNCATE語句全部清空表中的數(shù)據(jù)但保留表結(jié)構(gòu)，釋放掉那些占用的數(shù)據(jù)塊，不能進行回退操作。因此進行此操作時，一定要慎重。語法格式如下：TRUNCATETABLE表名;下面的語句刪除NEWEMP表中的所有記錄。SQL>TRUNCATETABLESCOTT.NEWEMP;表已截掉。如再執(zhí)行下面語句，看到表中的數(shù)據(jù)已全部清空。SQL>SELECT*FROMSCOTT.NEWEMP;4.2.6事務(wù)控制命令

1.事務(wù)概述事務(wù)（Transaction）是由一系列相關(guān)的SQL語句組成的最小邏輯工作單元。Oracle系統(tǒng)以事務(wù)為單位來處理數(shù)據(jù)，用以保證數(shù)據(jù)的一致性。對于事務(wù)中的每一個操作要么全部完成，要么全部不執(zhí)行。事務(wù)當遇到以下情況時事務(wù)結(jié)束。1）執(zhí)行COMMIT或ROLLBACK命令。2）執(zhí)行DDL命令。3）LOGOFF。4）程序異常結(jié)束。如果一個程序沒有遇到以上幾種情況，則在程序正常結(jié)束時將整個程序作為一個事務(wù)提交。如在SQL*Plus中正常退出時，系統(tǒng)會自動提交最后一個事務(wù)。事務(wù)可以是一組SQL命令，也可以是一條SQL語句。這些SQL語句只能是DML語句，對于其他SQL語句，如CREATETABLE語句，一旦執(zhí)行就立即提交給數(shù)據(jù)庫，不可能回滾。4.2.6事務(wù)控制命令事務(wù)控制的命令有以下幾種：提交事務(wù)（COMMIT）、設(shè)置保留點（SAVEPOINT）、回滾事務(wù)（ROLLBACK）和設(shè)置事務(wù)（SETTRANSACTION）等。在對數(shù)據(jù)庫發(fā)出DML操作時，只有當事務(wù)提交后才確保操作完成。在事務(wù)提交前所作的修改只有操作者本人可以查看操作結(jié)果，其他用戶只有在事務(wù)提交后才能夠看到。2提交事務(wù)提交事務(wù)有以下3種類型。1）自動提交設(shè)置AUTOCOMMIT為ON時，自動提交事務(wù)SQL>SETAUTOCOMMITON/OFF因為Oracle系統(tǒng)維護自動提交所消耗的系統(tǒng)資源較多，建議取消自動提交功能。2）顯式提交使用COMMIT命令顯式提交事務(wù)。使用的語句如下：SQL>COMMIT;4.2.6事務(wù)控制命令3．設(shè)置保留點保留點是設(shè)置在事務(wù)中的標記，把一個較長的事務(wù)劃分為若干個短事務(wù)。通過設(shè)置保留點，在事務(wù)需要回滾操作時，可以只回滾到某個保留點。設(shè)置保留點的語法如下：SAVEPOINT保留點名下面的語句設(shè)置兩個保留點SP1和SP2。SQL>insertintoscott.emp(empno,ename,job)values(7878,'john','programee');SQL>savepointsp1;SQL>select*fromscott.emp;SQL>savepointsp2;SQL>deletefromscott.empwhereename='button';SQL>commit;4.2.6事務(wù)控制命令4．回滾事務(wù)有時用戶在事務(wù)提交前取消所作的修改或由于系統(tǒng)故障等原因，Oracle系統(tǒng)將恢復到執(zhí)行事務(wù)執(zhí)行前的一致性狀態(tài)，這稱為回滾事務(wù)。Oracle系統(tǒng)允許回滾整個事務(wù)，也可以只回滾到某個保留點，但不能回滾已經(jīng)被提交的事務(wù)?；貪L到某個保留點的事務(wù)將撤銷保留點之后的所有修改，而保留點之前的所有操作不受影響。同時，Oracle系統(tǒng)還刪除該保留點之后的所有保留點，而該保留點還保留，以便多次回滾到同一保留點。如下面的語句將回滾整個事務(wù)：SQL>ROLLBACK;下面的語句將回滾到指定的保留點SP1：SQL>ROLLBACKTOSP1;4.2.6事務(wù)控制命令下面的語句回滾事務(wù)。SQL>insertintoscott.emp(empno,ename,job)values(7878,'john','programee');SQL>savepointtosp1;SQL>select*fromscott.emp;SQL>savepointtosp2;SQL>deletefromscott.empwhereename='button';SQL>rollbacktosp2;回滾到保留點SP2，則SP2保留點之后的所有DELETE操作將撤銷。4.2.6事務(wù)控制命令5．設(shè)置事務(wù)設(shè)置事務(wù)用于指定事務(wù)是讀寫還是只讀。一般情況下，讀寫事務(wù)可以包含任何SQL語句，如查詢、修改和刪除的DML語句。只讀事務(wù)只能包含查詢語句，可以使用的SQL命令如SELECT（不含forupdate子句）等。默認情況下的Oracle事務(wù)為讀寫事務(wù)。設(shè)置事務(wù)的語法如下：settransaction[readwrite|readonly][userollbacksegment回退段]其中，readwrite：讀寫，readonly：只讀。userollbacksegment設(shè)置事務(wù)使