Oracle數據庫命令操作

1、Oracle數據庫命令操作1，查看oracle版本：select * from v$version2，重新進行數據庫連接 CONNECT SCOTT/TIGERMYDB SCOTT登陸名 TIGER口令 MYDB稱為網絡服務名或稱為連接字符串3，關閉或退出SQL*Plus EXIT”或“QUIT”命令4，查看SQL*Plus的環(huán)境參數。 通過SHOW ALL命令可以查看設置命令的格式為：SET 參數 ON|OFF|值5，設置輸出頁面的大小。步驟1：輸入并執(zhí)行以下命令，觀察顯示結果：SELECT * FROM emp;步驟2：在輸入區(qū)輸入并執(zhí)行以下命令：SET PAGESIZE 100 SET

2、LINESIZE 120 或SET PAGESIZE 100 LINESIZE 120 步驟3：重新輸入并執(zhí)行以下命令，觀察顯示結果：SELECT * FROM emp;6，顯示當前用戶，輸入并執(zhí)行命令：SHOW USER執(zhí)行結果是：USER為SCOTT7，使用SPOOL命令記錄操作內容。步驟1：執(zhí)行命令：SPOOL C:TEST步驟2：執(zhí)行命令：SELECT * FROM emp; 步驟3：執(zhí)行命令：SELECT * FROM dept;步驟4：執(zhí)行命令：SPOOLOFF步驟5：用記事本打開C:TEST.LST并查看內容：8，打開/關閉查詢結果表頭的顯示，默認為ON。Set heading

3、on/off：打開/關閉查詢結果表頭的顯示，默認為ON。 Set feedback on/off：打開/關閉查詢結果中返回行數的顯示，默認為ON。 Set echo on/off：打開/關閉命令的回顯，默認為ON。 Set time on/off：打開/關閉時間顯示，默認為OFF。9,使用DESCRIBE命令查看表結構DESCRIBE（可簡寫成DESC）來檢查表的結構信息。輸入并執(zhí)行 以下命令(emp為要顯示結構的表名)：DESCRIBE emp10,注釋的方法是：* 在一行的開頭處書寫REM，將一行注釋掉。* 在一行中插入“-”，將其后的內容注釋掉。* 使用/* */，可以用來注釋任何一段內

4、容。11,保存輸入區(qū)的內容。步驟1：在輸入區(qū)重新輸入命令：SELECT * FROM emp;SELECT * FROM dept;步驟2：選擇“文件”菜單下的“將輸入另存為”命令，彈出文件存盤對話框。選擇正確的磁盤位置，為存盤的文件起一個名字。如果輸入區(qū)中的內容是SQL命令或命令序列，則使用擴展名.SQL；否則可以省略或以.TXT做擴展名。在本例中，選SELECT.SQL做文件名。12,創(chuàng)建新用戶USER1，口令為ABC123CREATE USER USER1 IDENTIFIED BY ABC123（create user user1 identified by abc123） 授予連接數

5、據庫權限、創(chuàng)建表權限、創(chuàng)建存儲過程和表空間使用權限。GRANT CONNECT TO USER1; (grant connect to user1)GRANT CREATE TABLE TO USER1; (grant create table to user1)GRANT CREATE PROCEDURE TO USER1; (grant create procedure to user1)GRANT UNLIMITED TABLESAPCE TO USER1;(grant unlimited tablesapce to user1) 使用新賬戶登錄。CONNECT USER1/ABC123

6、MYDB輸出結果：已連接。步驟5：以創(chuàng)建表的方式復制數據到新賬戶。 CREATE TABLE EMP AS SELECT * FROM SCOTT.EMP;(create table emp as select * from scott.emp) CREATE TABLE DEPT AS SELECT * FROM SCOTT.DEPT;CREATE TABLE SALGRADES AS SELECT * FROM SCOTT.SALGRADE;13,每個賬戶對應一個模式(賬戶.表)以不同的賬戶登錄數據庫時，就進入了不同的模式 SELECT * FROM SCOTT.EMP;14, 顯示表的行

7、號輸入并執(zhí)行查詢：SELECT rownum,ename FROM emp;15,查詢語句中的加減乘除運算加（+）、減（-）、乘（*）、除（/）顯示雇員工資上浮20%的結果。輸入并執(zhí)行查詢:SELECT ename,sal,sal*(1+20/100) FROM emp;顯示結果為：ENAME SAL SAL*(1+20/100) SMITH 800 960ALLEN 1600 192016,在查詢中使用列別名。輸入并執(zhí)行：SELECT ename AS 名稱, sal 工資 FROM emp;顯示結果為：名稱 工資 SMITH 800ALLEN 160017,在查詢中使用字符串常量。輸入并執(zhí)

8、行查詢：SELECTename| IS |job AS 雇員和職務表 FROM emp;輸出結果為：雇員和職務表SMITH IS CLERKALLEN IS SALESMAN18，消除重復行顯示。 使用DISTINCT輸入并執(zhí)行查詢：SELECT DISTINCT job FROM emp;19，升序排序【訓練1】 查詢雇員姓名和工資，并按工資從小到大排序。輸入并執(zhí)行查詢：SELECT ename, sal FROM emp ORDER BY sal;執(zhí)行結果為：ENAME SAL SMITH 800JAMES 950注意：若省略ASC和DESC，則默認為ASC，即升序排序。 降序排序?【訓練

9、2】 查詢雇員姓名和雇傭日期，并按雇傭日期排序，后雇傭的先顯示。輸入并執(zhí)行查詢：SELECT ename,hiredate FROM emp ORDER BY hiredate DESC;結果如下：ENAME HIREDATE ADAMS 23-5月 -87SCOTT 19-4月 -87多列排序SELECT ename,deptno,hiredate FROM emp ORDER BY deptno,hiredate;在排序中使用別名SELECT empno, ename, sal*Months_between(sysdate,hiredate) AS total FROM emp ORDER

10、 BY total;20，條件查詢 SELECT ename,job,sal FROM emp WHERE job=SALESMAN;SELECT ename, job,sal FROM emp WHERE sal=3000;SELECT ename,hiredate FROM emp WHERE hiredate=1-1月-82;SELECT ename, job,sal FROM emp WHERE sal1000 AND sal2000;SELECT * FROM emp WHERE job=CLERK OR job=MANAGER;SELECT * FROM emp WHERE NOT

11、 deptno=10;SELECT * FROM emp WHERE (deptno=10 OR deptno=20) AND sal0;24，日期函數返回系統(tǒng)的當前日期。輸入并執(zhí)行查詢：SELECT sysdate FROM dual返回2003年2月的最后一天。輸入并執(zhí)行查詢：SELECT last_day(08-2月-03) FROM dual;假定當前的系統(tǒng)日期是2003年2月6日，求再過1000天的日期。輸入并執(zhí)行查詢：SELECT sysdate+1000 AS NEW DATE FROM dual;假定當前的系統(tǒng)日期是2003年2月6日，顯示部門10雇員的雇傭天數。輸入并執(zhí)行查詢

12、：SELECT ename, round(sysdate-hiredate) DAYSFROM emp WHERE deptno = 10;25，類型轉換函數自動轉換字符型數據到數值型。輸入并執(zhí)行查詢：SELECT 12.5+11 FROM dual;自動轉換數值型數據到字符型。執(zhí)行以下查詢：SELECT 12.5|11 FROM dual;26，日期類型轉換 將日期轉換成帶時間和星期的字符串并顯示。執(zhí)行以下查詢：SELECT TO_CHAR(sysdate,YYYY-MM-DD HH24:MI:SS AM DY) FROM dual;將日期顯示轉換成中文的年月日。輸入并執(zhí)行查詢：SELECT

13、 TO_CHAR(sysdate,YYYY年MM月DD日) FROM dual;將雇傭日期轉換成字符串并按新格式顯示。輸入并執(zhí)行查詢：SELECTename, to_char(hiredate, DD Month YYYY) HIREDATEFROM emp;以全拼和序列顯示時間。執(zhí)行以下查詢：SELECT SYSDATE,to_char(SYSDATE,yyyysp),to_char(SYSDATE,mmspth),to_char(SYSDATE,ddth) FROM dual;時間顯示的大小寫。步驟1：執(zhí)行以下查詢：SELECT SYSDATE,to_char(SYSDATE,yyyysp

14、) FROM dual;結果為：SYSDATE TO_CHAR(SYSDATE,YYYYSP) 07-2月 -04 two thousand four步驟2：執(zhí)行以下查詢：SELECT to_char(SYSDATE,Yyyysp) FROM dual;結果為：SYSDATE TO_CHAR(SYSDATE,YYYYSP) Two Thousand Four27, 數字類型轉換 將數值轉換成字符串并按新格式顯示。執(zhí)行以下查詢：SELECT TO_CHAR(123.45,0000.00), TO_CHAR(12345,L9.9EEEE) FROM dual;結果為：TO_CHAR( TO_CHA

15、R(12345,L9.9 0123.45 RMB1.2E+04將數值轉換成字符串并按新格式顯示。執(zhí)行以下查詢：SELECT TO_CHAR(sal,$99,999) SALARY FROM emp WHERE ename = SCOTT;結果為：SALARY $4,000NVL: 如果不為空，返回原值；為空，則返回給定的值。 使用nvl函數轉換空值。執(zhí)行以下查詢：SELECTename,nvl(job,無),nvl(hiredate,01-1月-97),nvl(comm,0) FROM emp;結果為：ENAME NVL(JOB,N NVL(HIREDA NVL(COMM,0) SMITH C

16、LERK 17-12月-80 0ALLEN SALESMAN 20-2月 -81 300Decode：將職務轉換成中文顯示。執(zhí)行以下查詢：SELECTename,decode(job, MANAGER, 經理, CLERK,職員, SALESMAN,推銷員, ANALYST,系統(tǒng)分析員,未知) FROM emp;userenv函數* ISDBA：判斷會話用戶的角色是否為SYSDBA，是則返回TRUE。* INSTANCE：返回會話連接的INSTANCE標識符。* LANGUAGE：返回語言、地區(qū)、數據庫字符集信息。* LANG：返回會話語言的ISO簡稱。 * TERMINAL：返回正在會話的終

17、端或計算機的標識符。返回用戶終端或系統(tǒng)標識信息。執(zhí)行以下查詢：SELECTuserenv(TERMINAL) FROM dual;結果為：ORASERVER返回語言、地區(qū)、數據庫字符集信息。執(zhí)行以下查詢：SELECTuserenv(LANGUAGE) FROM dual;結果為：SIMPLIFIED CHINESE_CHINA.ZHS16GBK28，相等連接 顯示雇員的名稱和所在的部門的編號和名稱。執(zhí)行以下查詢：SELECT emp.ename,emp.deptno,dept.dname FROM emp,dept WHERE emp.deptno=dept.deptno;使用表別名。執(zhí)行以下

18、查詢：SELECT ename,e.deptno,dname FROM emp e,dept d WHERE e.deptno=d.deptno;29，外連接 外連操作符為(+)使用外連顯示不滿足相等條件的記錄。步驟1：顯示雇員的名稱、工資和所在的部門名稱及沒有任何雇員的部門。執(zhí)行以下查詢：SELECT ename,sal,dname FROM emp,dept WHERE emp.deptno(+)=dept.deptno;執(zhí)行結果為：ENAME SAL DNAME SELECT ename,sal,dname FROM emp right outer join dept on emp.de

19、ptno = dept.deptno;SELECT ename,sal,dname FROM emp right outer join dept on emp.deptno = dept.deptno;？ select * from TBL_10200_FAC_ORDER B inner join DTCXHDY A on B.MT_FAC_NAME=A.MC30，統(tǒng)計查詢分組函數中SUM和AVG只應用于數值型的列，MAX、MIN和COUNT可以應用于字符、數值和日期類型的列。組函數忽略列的空值。使用GROUP BY 從句可以對數據進行分組。所謂分組，就是按照列的相同內容，將記錄劃分成組，對組

20、可以應用組函數。如果不使用分組，將對整個表或滿足條件的記錄應用組函數。在組函數中可使用DISTINCT或ALL關鍵字。ALL表示對所有非NULL值(可重復)進行運算(COUNT除外)。DISTINCT 表示對每一個非NULL值，如果存在重復值，則組函數只運算一次。如果不指明上述關鍵字，默認為ALL。 求雇員總人數。執(zhí)行以下查詢：SELECT COUNT(*) FROM emp;求有傭金的雇員人數。執(zhí)行以下查詢：SELECT COUNT(comm) FROM emp;求部門10的雇員的平均工資。執(zhí)行以下查詢：SELECT AVG(sal) FROM emp WHERE deptno=10;求最晚

21、和最早雇傭的雇員的雇傭日期。執(zhí)行以下查詢：SELECT MAX(hiredate),MIN(hiredate) FROM emp;求雇員表中不同職務的個數。執(zhí)行以下查詢：SELECT COUNT( DISTINCT job) FROM emp;返回結果為：COUNT(DISTINCT JOB) 5按職務統(tǒng)計工資總和。步驟1：執(zhí)行以下查詢：SELECT SUM(sal) FROM emp GROUP BY job; SELECT job,SUM(sal) FROM emp GROUP BY job;按部門和職務分組統(tǒng)計工資總和。執(zhí)行以下查詢：SELECT deptno, job, sum(sal

22、) FROM emp GROUP BY deptno, job;統(tǒng)計各部門的最高工資，排除最高工資小于3000的部門。執(zhí)行以下查詢：SELECT deptno, max(sal) FROM emp GROUP BY deptno HAVING max(sal)=3000;注意：WHERE條件在GROUP BY之前執(zhí)行，HAVING條件在分組后執(zhí)行。按職務統(tǒng)計工資總和并排序。執(zhí)行以下查詢：SELECT job 職務, SUM(sal) 工資總和 FROM emp GROUP BY jobORDER BY SUM(sal); 注意：使用ORDER BY從句對統(tǒng)計的結果進行排序，ORDER BY從句

23、要出現(xiàn)在語句的最后。求各部門平均工資的最高值。執(zhí)行以下查詢：SELECT max(avg(sal) FROM emp GROUP BY deptno;31，子查詢 子查詢一般出現(xiàn)在SELECT語句的WHERE子句中，Oracle也支持在FROM或HAVING子句中出現(xiàn)子查詢。子查詢比主查詢先執(zhí)行，結果作為主查詢的條件，在書寫上要用圓括號擴起來，并放在比較運算符的右側。子查詢可以嵌套使用，最里層的查詢最先執(zhí)行。子查詢可以在SELECT、INSERT、UPDATE、DELETE等語句中使用。查詢比SCOTT工資高的雇員名字和工資。執(zhí)行以下查詢：SELECT ename,sal FROM emp W

24、HERE sal(SELECT sal FROM emp WHERE empno=7788);查詢和SCOTT同一部門且比他工資低的雇員名字和工資。執(zhí)行以下查詢：SELECT ename,sal FROM emp WHERE sal(SELECT AVG(sal) FROM emp);32，多行子查詢如果子查詢返回多行的結果，則我們稱它為多行子查詢。多行子查詢要使用不同的比較運算符號，它們是IN、ANY和ALL。查詢工資低于任何一個“CLERK”的工資的雇員信息。執(zhí)行以下查詢：SELECT empno, ename, job,sal FROM emp WHERE sal ANY (SELECT

25、 sal FROM emp WHERE job = CLERK)AND job CLERK;查詢工資比所有的“SALESMAN”都高的雇員的編號、名字和工資。執(zhí)行以下查詢：SELECT empno, ename,sal FROM emp WHERE sal ALL(SELECT sal FROM emp WHERE job= SALESMAN);查詢部門20中職務同部門10的雇員一樣的雇員信息。執(zhí)行以下查詢：SELECT empno, ename, job FROM emp WHERE job IN (SELECT job FROM emp WHERE deptno=10)AND deptno

26、 =20;查詢職務和SCOTT相同，比SCOTT雇傭時間早的雇員信息。執(zhí)行以下查詢：SELECT empno, ename, job FROM emp WHERE job =(SELECT job FROM emp WHERE empno=7788)AND hiredate (SELECT hiredate FROM emp WHERE empno=7788);33，多列子查詢如果子查詢返回多列，則對應的比較條件中也應該出現(xiàn)多列，這種查詢稱為多列子查詢。以下是多列子查詢的訓練實例?！居柧?】 查詢職務和部門與SCOTT相同的雇員的信息。執(zhí)行以下查詢：SELECT empno, ename, s

27、al FROM emp WHERE (job,deptno) =(SELECT job,deptno FROM emp WHERE empno=7788);34，在FROM從句中使用子查詢 在FROM從句中也可以使用子查詢，在原理上這與在WHERE條件中使用子查詢類似。 查詢雇員表中排在第69位置上的雇員。執(zhí)行以下查詢：SELECT ename,sal FROM (SELECT rownum as num,ename,sal FROM emp WHERE rownum=6;35，集合運算 多個查詢語句的結果可以做集合運算，結果集的字段類型、數量和順序應該一樣。查詢部門10和部門20的所有職務。

28、執(zhí)行以下查詢：SELECT job FROM emp WHERE deptno=10UNIONSELECT job FROM emp WHERE deptno=20;查詢部門10和20中是否有相同的職務和工資。執(zhí)行以下查詢：SELECT job,sal FROM emp WHERE deptno=10INTERSECTSELECT job,sal FROM emp WHERE deptno=20;查詢只在部門表中出現(xiàn)，但沒有在雇員表中出現(xiàn)的部門編號。執(zhí)行以下查詢：SELECT deptno FROM deptMINUSSELECT deptno FROM emp ;顯示人數最多的部門名稱。輸入

29、并執(zhí)行以下查詢：SELECT DECODE(dname,SALES,銷售部,ACCOUNTING,財務部,RESEARCH,研發(fā)部,未知) 部門名 FROM emp,dept WHERE emp.deptno=dept.deptno GROUP BY dname HAVING COUNT(*)=(SELECT MAX(COUNT(*) FROM emp GROUP BY deptno);顯示各部門的平均工資、最高工資、最低工資和總工資列表，并按平均工資高低順序排序。輸入并執(zhí)行以下查詢：SELECT dname 部門,AVG(sal) 平均工資,MAX(sal) 最高工資,MIN(sal) 最低

30、工資,SUM(sal) 總工資 FROM emp,dept WHERE emp.deptno=dept.deptno GROUP BY dname ORDER BY AVG(sal) DESC;36，插入數據 可以使用INSERT命令，向已經存在的表插入數據，語法格式如下：INSERT INTO 表名 (字段列表) VALUES(表達式1, 表達式2,.)|QUERY語句；插入字段的值的類型要和字段的類型一一對應。字符串類型的字段值必須用單引號括起來字符串類型的字段值超過定義的長度會出錯, 最好在插入前進行長度校驗。字段列表如果省略則代表全部字段。將新雇員插入到emp表：INSERT INTO

31、 emp(empno,ename,job)VALUES (1000, 小李, CLERK);其他沒有插入的字段，系統(tǒng)會填寫為表的默認值。如果在表的創(chuàng)建時沒有說明默認值，則將插入NULL值日期型的數據默認格式為DD-MON-YY，默認的世紀為當前的世紀，默認的時間為午夜12點。如果指定的世紀不是本世紀或時間不是午夜12點，則必須使用TO_DATE系統(tǒng)函數對字符串進行轉換。如果要插入表的全部字段，則表名后的字段列表可以省略37，復制數據 另一種插入數據(相當于復制)方法的語法格式是：INSERT INTO 表名(字段列表) SELECT(字段名1, 字段名2, .) FROM 另外的表名;該形式一

32、次可以插入多行數據。步驟1：創(chuàng)建一個新表manager CREATE TABLE manager AS SELECT empno,ename,sal FROM emp WHERE job=MANAGER;執(zhí)行結果：表已創(chuàng)建。步驟2：從emp表拷貝數據到manager：INSERT INTO managerSELECTempno, ename, sal FROM emp WHEREjob = CLERK;38序列使用 使用INSERT語句時，可以通過序列來填寫某些數值型或字符型的列。序列是一個要預先定義的有序的數值序列, 應該先建立一個序列，然后在插入語句中使用 插入數據中使用序列的練習。步驟1

33、：創(chuàng)建從2000起始，增量為1 的序列abc：CREATE SEQUENCE abc INCREMENT BY 1 START WITH 2000 MAXVALUE 99999 CYCLE NOCACHE; 在INSERT 語句使用序列，序列的名稱為abc：INSERT INTO manager VALUES(abc.nextval,小王,2500);39, 修改數據 修改數據的語句UPDATE對表中指定字段的數據進行修改，一般需要通過添加WHERE條件來限定要進行修改的行，如果不添加WHERE條件，將對所有的行進行修改。(1) 修改數據的語句UPDATE的基本語法如下：UPDATE 表名 S

34、ET 字段名1=表達式1, 字段名2=表達式2, . WHERE 條件; 修改小李(編號為1000)的工資為3000。執(zhí)行以下的查詢：UPDATE emp SET sal = 3000 WHERE empno = 1000; 如果修改的值沒有賦值或定義，將把原來字段的內容清為NULL。若修改值的長度超過定義的長度，則會出錯。(2) UPDATE語句的另外一種用法：UPDATE 表名 SET(字段名1, 字段名2, .)=SELECT (字段名1, 字段名2, .) FROM 另外的表名WHERE條件;【訓練4】 根據其他表修改數據。執(zhí)行以下的查詢：UPDATE managerSET (enam

35、e, sal) =(SELECT ename,sal FROM emp WHERE empno = 7788)WHERE empno = 1000;執(zhí)行結果：40, 刪除數據 刪除數據的基本語法如下：DELETE FROM表名 WHERE 條件;要從表中刪除滿足條件的記錄，WHERE條件一般不能省略，如果省略就會刪除表的全部數據?！居柧?】 刪除雇員編號為1000的新插入的雇員。步驟1：刪除編號為1000的雇員：DELETE FROM emp WHERE empno=1000;如果確實要刪除一個大表里的全部記錄，可以用TRUNCATE 命令，它可以釋放占用的數據塊表空間，語法為：TRUNCAT

36、E TABLE 表名;徹底刪除manager表的內容。執(zhí)行以下的命令：TRUNCATE TABLE manager;TRUNCATE TABLE命令用來刪除表的全部數據而不是刪除表，表依舊存在。41,數據庫事務 事務是由相關操作構成的一個完整的操作單元。兩次連續(xù)成功的COMMIT或ROLLBACK之間的操作，稱為一個事務。在一個事務內，數據的修改一起提交或撤銷，如果發(fā)生故障或系統(tǒng)錯誤，整個事務也會自動撤銷。我們可以這樣理解數據庫事物：對數據庫所做的一系列修改，在修改過程中，暫時不寫入數據庫，而是緩存起來，用戶在自己的終端可以預覽變化，直到全部修改完成，并經過檢查確認無誤后，一次性提交并寫入數據

37、庫，在提交之前，必要的話所做的修改都可以取消。提交之后，就不能撤銷，提交成功后其他用戶才可以通過查詢?yōu)g覽數據的變化。以事務的方式對數據庫進行訪問，有如下的優(yōu)點：* 把邏輯相關的操作分成了一個組。* 在數據永久改變前，可以預覽數據變化。* 能夠保證數據的讀一致性。數據庫事務處理可分為隱式和顯式兩種。顯式事務操作通過命令實現(xiàn)，隱式事務由系統(tǒng)自動完成提交或撤銷(回退)工作，無需用戶的干預。隱式提交的情況包括：當用戶正常退出SQL*Plus或執(zhí)行CREATE、DROP、GRANT、REVOKE等命令時會發(fā)生事務的自動提交。還有一種情況，如果把系統(tǒng)的環(huán)境變量AUTOCOMMIT設置為ON(默認狀態(tài)為OF

38、F)，則每當執(zhí)行一條INSERT、DELETE或UPDATE命令對數據進行修改后，就會馬上自動提交。設置命令格式如下：SET AUTOCOMMIT ON/OFF隱式回退的情況包括：當異常結束SQL*Plus或系統(tǒng)故障發(fā)生時，會發(fā)生事務的自動回退。顯式事務處理的數據庫事務操作語句有3條，如表3-2所示。COMMIT操作把多個步驟對數據庫的修改，一次性地永久寫入數據庫，代表數據庫事務的成功執(zhí)行。ROLLBACK操作在發(fā)生問題時，把對數據庫已經作出的修改撤消，回退到修改前的狀態(tài)。在操作過程中，一旦發(fā)生問題，如果還沒有提交操作，則隨時可以使用ROLLBACK來撤消前面的操作。SAVEPOINT則用于在

39、事務中間建立一些保存點，ROLLBACK可以使操作回退到這些點上邊，而不必撤銷全部的操作。一旦COMMIT完成，就不能用ROLLBACK來取消已經提交的操作。一旦ROLLBACK完成，被撤消的操作要重做，必須重新執(zhí)行相關操作語句如何開始一個新的事務呢？一般情況下，開始一個會話(即連接數據庫)，執(zhí)行第一條SQL語句將開始一個新的事務，或執(zhí)行COMMIT提交或ROLLBACK撤銷事務，也標志新的事務的開始。另外，執(zhí)行DDL(如CREATE)或DCL命令也將自動提交前一個事務而開始一個新的事務。數據在修改的時候會對記錄進行鎖定，其他會話不能對鎖定的記錄進行修改或加鎖，只有當前會話提交或撤銷后，記錄的

40、鎖定才會釋放。 我們通過以下的訓練來為雇員SCOTT增加工資，SCOTT的雇員號為7788。 學習使用COMMIT和ROLLBACK。步驟1：執(zhí)行以下命令，提交尚未提交的操作：COMMIT;執(zhí)行結果：提交完成。顯示SCOTT的現(xiàn)有工資：SELECT ename,sal FROM emp WHERE empno=7788;執(zhí)行結果：ENAME SAL SCOTT 3000步驟2：修改雇員SCOTT的工資：UPDATE emp SET sal=sal+100 WHERE empno=7788;執(zhí)行結果：已更新1行。顯示修改后的SCOTT的工資：SELECT ename,sal FROM emp W

41、HERE empno=7788;執(zhí)行結果：ENAME SAL SCOTT 3100步驟3：假定修改操作后發(fā)現(xiàn)增加的工資應該為1000而不是100，為了取消剛做的操作，可以執(zhí)行以下命令：ROLLBACK;執(zhí)行結果：回退已完成。顯示回退后SCOTT的工資恢復為3000：SELECT ename,sal FROM emp WHERE empno=7788;執(zhí)行結果：ENAME SAL SCOTT 3000步驟4：重新修改雇員SCOTT的工資，工資在原有基礎上增加1000：UPDATE emp SET sal=sal+1000 WHERE empno=7788;執(zhí)行結果：已更新 1 行。顯示修改后SC

42、OTT的工資：SELECT ename,sal FROM emp WHERE empno=7788;執(zhí)行結果：ENAME SAL SCOTT 4000步驟5：經查看修改結果正確，提交所做的修改：COMMIT;執(zhí)行結果：提交完成。 說明：在執(zhí)行COMMIT后，工資的修改被永久寫入數據庫。本訓練的第1步，先使用COMMIT命令提交原來的操作，同時標志一個新的事務的開始。注意：在事務執(zhí)行過程中，隨時可以預覽數據的變化。對于比較大的事務，可以使用SAVEPOINT命令在事務中間劃分一些斷點，用來作為回退點?！居柧?】 學習使用SAVEPOINT命令。步驟1：插入一個雇員：INSERT INTO emp

43、(empno, ename, job)VALUES (3000, 小馬,STUDENT);執(zhí)行結果：已創(chuàng)建 1 行。步驟2：插入保存點，檢查點的名稱為PA：SAVEPOINT pa;執(zhí)行結果：保存點已創(chuàng)建。步驟3：插入另一個雇員：INSERT INTO emp(empno, ename, job)VALUES (3001, 小黃,STUDENT);執(zhí)行結果：已創(chuàng)建 1 行。 步驟4：回退到保存點PA，則后插入的小黃被取消，而小馬仍然保留。ROLLBACK TOpa;執(zhí)行結果：回退已完成。步驟5: 提交所做的修改：COMMIT;執(zhí)行結果：提交完成。說明：第4步的回退，將回退到保存點PA，即第3步

44、被撤銷。所以最后的COMMIT只提交了對小馬的插入。請自行檢查插入的雇員。在Oracle數據庫中，有一個叫回滾段的特殊的存儲區(qū)域。在提交一個事物之前，如果用戶進行了數據的修改，在所謂的回滾段中將保存變化前的數據。有了回滾段才能在必要時使用ROLLBACK命令或自動地進行數據撤銷。在提交事物之前，用戶自己可以看到修改的數據，但因為修改還沒有最終提交，其他用戶看到的應該是原來的數據，也就是回滾段中的數據，這時用戶自己看到的數據和其他用戶看到的數據是不同的，只有提交發(fā)生后，變化的數據才會被寫入數據庫，此時用戶自己看到的數據和其他用戶看到的數據才是一致的，這叫做數據的讀一致性。當第1個SQL*Plus

45、會話刪除一條后，第2個SQL*Plus會話仍然可以看到該條記錄，直到第1個SQL*Plus會話提交（COMMIT）該刪除操作后，兩個會話看到的才是一致的數據。42，表的鎖定 鎖出現(xiàn)在數據共享的場合，用來保證數據的一致性。當多個會話同時修改一個表時，需要對數據進行相應的鎖定。鎖有“只讀鎖”、“排它鎖”，“共享排它鎖”等多種類型，而且每種類型又有“行級鎖”(一次鎖住一條記錄)，“頁級鎖”(一次鎖住一頁，即數據庫中存儲記錄的最小可分配單元)，“表級鎖”(鎖住整個表)。 若為“行級排它鎖”，則除被鎖住的行外，該表中其他行均可被其他的用戶進行修改(Update)或刪除(delete)。若為“表級排它鎖”

46、，則所有其他用戶只能對該表進行查詢(select)操作，而無法對其中的任何記錄進行修改或刪除。當程序對所做的修改進行提交(commit)或回滾(rollback)后，鎖住的資源便會得到釋放，從而允許其他用戶進行操作。如果兩個事務，分別鎖定一部分數據，而都在等待對方釋放鎖才能完成事務操作，這種情況下就會發(fā)生死鎖。在Oracle數據庫中，修改數據操作時需要一個隱式的獨占鎖，以鎖定修改的行，直到修改被提交或撤銷為止。如果一個會話鎖定了數據，那么第二個會話要想對數據進行修改，只能等到第一個會話對修改使用COMMIT命令進行提交或使用ROLLBACK命令進行回滾撤銷后，才開始執(zhí)行。因此應養(yǎng)成一個良好的習

47、慣：執(zhí)行修改操作后，要盡早地提交或撤銷，以免影響其他會話對數據的修改。主動鎖表： 鎖定行：對emp表的部門10的雇員記錄加顯式鎖，并測試。步驟1：對部門10加顯式鎖：SELECT empno,ename,job,sal FROM emp WHERE deptno=10 FOR UPDATE;步驟2：啟動第二個SQL*Plus(第二個會話)，以SCOTT賬戶登錄數據庫，對部門10的雇員CLARK進行修改操作。UPDATE emp SET sal=sal+100 where empno=7782;執(zhí)行結果：沒有任何輸出(處于等待解鎖狀態(tài))。步驟3：在第一個會話進行解鎖操作：COMMIT;步驟4：查

48、看第二個會話，有輸出結果：已更新 1 行。說明：步驟1對選定的部門10的雇員加鎖，之后其他會話不能對部門10的雇員數據進行修改或刪除。如果此時要進行修改或刪除，則會處于等待狀態(tài)。使用COMMIT語句進行解鎖之后，如果有掛起的修改或刪除操作，則等待的操作此時可以執(zhí)行。鎖定表： LOCK語句用于對整張表進行鎖定。語法如下：LOCK TABLE 表名 IN SHARE|EXCLUSIVE MODE對表的鎖定可以是共享(SHARE)或獨占(EXCLUSIVE)模式。共享模式下，其他會話可以加共享鎖，但不能加獨占鎖。在獨占模式下，其他會話不能加共享或獨占鎖?！居柧?】 對emp表添加獨占鎖。步驟1：對e

49、mp表加獨占鎖：LOCK TABLE emp IN EXCLUSIVE MODE;結果為：表已鎖定。步驟2：對表進行解鎖操作：COMMIT;說明：當使用LOCK語句顯式鎖定一張表時，死鎖的概率就會增加。同樣地，使用COMMIT或ROLLBACK命令可以釋放鎖。 注意：必須沒有其他會話對該表的任何記錄加鎖，此操作才能成功。43，表的創(chuàng)建 表的創(chuàng)建需要CREATE TABLE 系統(tǒng)權限，表的基本創(chuàng)建語法如下：CREATE TABLE 表名(列名 數據類型(寬度)DEFAULT 表達式COLUMN CONSTRAINT,.TABLE CONSTRAINTTABLE_PARTITION_CLAUSE)

50、；創(chuàng)建表最主要的是要說明表名、列名、列的數據類型和寬度，多列之間用“，”分隔?？梢允怯弥形幕蛴⑽淖鳛楸砻土忻?。表名最大長度為30個字符。在同一個用戶下，表不能重名，但不同用戶表的名稱可以相重。另外，表的名稱不能使用Oracle的保留字。在一張表中最多可以包含2000列。該語法中的其他部分根據需要添加，作用如下：DEFAULT 表達式：用來定義列的默認值。COLUMN CONSTRAINT：用來定義列級的約束條件。TABLE CONSTRAINT：用來定義表級的約束條件。TABLE_PARTITION_CLAUSE：定義表的分區(qū)子句。創(chuàng)建圖書和出版社表。步驟1：創(chuàng)建出版社表，輸入并執(zhí)行以下命令

51、：CREATE TABLE 出版社(編號 VARCHAR2(2),出版社名稱 VARCHAR2(30),地址 VARCHAR2(30),聯(lián)系電話 VARCHAR2(20);步驟2：創(chuàng)建圖書表，輸入并執(zhí)行以下命令：CREATE TABLE 圖書(圖書編號 VARCHAR2(5),圖書名稱 VARCHAR2(30),出版社編號 VARCHAR2(2),作者 VARCHAR2(10),出版日期 DATE,數量 NUMBER(3),單價 NUMBER(7,2);2通過子查詢創(chuàng)建表如果要創(chuàng)建一個同已有的表結構相同或部分相同的表，可以采用以下的語法：CREATE TABLE 表名(列名.) AS SQL查

52、詢語句;該語法既可以復制表的結構，也可以復制表的內容，并可以為新表命名新的列名。新的列名在表名后的括號中給出，如果省略將采用原來表的列名。復制的內容由查詢語句的WHERE條件決定。通過子查詢創(chuàng)建新的圖書表。步驟1：完全復制圖書表到“圖書1”，輸入并執(zhí)行以下命令：CREATE TABLE 圖書1 AS SELECT *FROM 圖書;執(zhí)行結果：表已創(chuàng)建。步驟2：創(chuàng)建新的圖書表“圖書2”，只包含書名和單價，輸入并執(zhí)行以下命令：CREATE TABLE 圖書2(書名,單價) AS SELECT 圖書名稱,單價 FROM 圖書;步驟3：創(chuàng)建新的圖書表“圖書3”，只包含書名和單價，不復制內容，輸入并執(zhí)行

53、以下命令：CREATE TABLE 圖書3(書名,單價) AS SELECT 圖書名稱,單價 FROM 圖書 WHERE 1=2;3設置列的默認值可以在創(chuàng)建表的同時指定列的默認值，這樣在插入數據時，如果不插入相應的列，則該列取默認值，默認值由DEFAULT部分說明?！?創(chuàng)建表時設置默認值。步驟1：創(chuàng)建表時，設置表的默認值。CREATE TABLE 圖書4(圖書編號 VARCHAR2(5) DEFAULT NULL,圖書名稱 VARCHAR2(30) DEFAULT 未知,出版社編號 VARCHAR2(2) DEFAULT NULL,出版日期 DATE DEFAULT 01-1月-1900,作者

54、 VARCHAR2(10) DEFAULT NULL,數量 NUMBER(3) DEFAULT 0,單價 NUMBER(7,2) DEFAULT NULL,借出數量 NUMBER(3) DEFAULT 0);4刪除已創(chuàng)建的表刪除表的語法如下：DROP TABLE 表名CASCADE CONSTRAINTS；表的刪除者必須是表的創(chuàng)建者或具有DROP ANY TABLE權限。CASCADE CONSTRAINTS表示當要刪除的表被其他表參照時，刪除參照此表的約束條件。44，表的操作 1表的重命名語法如下：RENAME 舊表名 TO 新表名; 2清空表清空表的語法為：TRUNCATE TABLE 表

55、名；清空表可刪除表的全部數據并釋放占用的存儲空間。 3添加注釋(1) 為表添加注釋的語法為：COMMENT ON TABLE 表名 IS .;該語法為表添加注釋字符串。如IS后的字符串為空，則清除表注釋。 4 為列添加注釋的語法為：COMMENT ON COLUMN 表名.列名 IS .該語法為列添加注釋字符串。如IS后的字符串為空，則清除列注釋。 為emp表的deptno列添加注釋：“部門編號”。COMMENT ON COLUMN emp.deptno IS 部門編號; 5查看表的結構：DESCRIBE 表名；DESCRIBE可以簡寫為DESC?？梢酝ㄟ^對數據字典USER_OBJECTS的查

56、詢，顯示當前模式用戶的所有表。 顯示當前用戶的所有表。SELECT object_name FROM user_objects WHERE object_type=TABLE;45，數據完整性約束 表的數據有一定的取值范圍和聯(lián)系，多表之間的數據有時也有一定的參照關系。在創(chuàng)建表和修改表時，可通過定義約束條件來保證數據的完整性和一致性。約束條件是一些規(guī)則，在對數據進行插入、刪除和修改時要對這些規(guī)則進行驗證，從而起到約束作用。完整性包括數據完整性和參照完整性，數據完整性定義表數據的約束條件，參照完整性定義數據之間的約束條件。數據完整性由主鍵(PRIMARY KEY)、非空(NOT NULL)、惟一(

57、UNIQUE)和檢查(CHECK)約束條件定義，參照完整性由外鍵(FOREIGN KEY)約束條件定義。表共有五種約束，它們是主鍵、非空、惟一、檢查和外鍵。1主鍵(PRIMARY KEY)主鍵是表的主要完整性約束條件，主鍵惟一地標識表的每一行。一般情況下表都要定義主鍵，而且一個表只能定義一個主鍵。主鍵可以包含表的一列或多列，如果包含表的多列，則需要在表級定義。主鍵包含了主鍵每一列的非空約束和主鍵所有列的惟一約束。主鍵一旦成功定義，系統(tǒng)將自動生成一個B*樹惟一索引（？），用于快速訪問主鍵列。比如圖書表中用“圖書編號”列作主鍵，“圖書編號”可以惟一地標識圖書表的每一行。主鍵約束的語法如下：CONS

58、TRANT 約束名 PRIMARY KEY-列級CONSTRANT 約束名 PRIMARY KEY(列名1,列名2,.)-表級2非空(NOT NULL)非空約束指定某列不能為空，它只能在列級定義。在默認情況下，Oracle允許列的內容為空值。比如“圖書名稱”列要求必須填寫，可以為該列設置非空約束條件。非空約束語法如下：CONSTRANT 約束名 NOT NULL-列級約束分為兩級，一個約束條件根據具體情況，可以在列級或表級定義。列級約束：約束表的某一列，出現(xiàn)在表的某列定義之后，約束條件只對該列起作用。表級約束：約束表的一列或多列，如果涉及到多列，則必須在表級定義。表級約束出現(xiàn)在所有列定義之后。

59、3惟一(UNIQUE)惟一約束條件要求表的一列或多列的組合內容必須惟一，即不相重，可以在列級或表級定義。但如果惟一約束包含表的多列，則必須在表級定義。比如出版社表的“聯(lián)系電話”不應該重復，可以為其定義惟一約束。惟一約束的語法如下：CONSTRANT 約束名 UNIQUE-列級CONSTRANT 約束名 UNIQUE(列名1,列名2,.)-表級4檢查(CHECK)檢查約束條件是用來定義表的一列或多列的一個約束條件，使表的每一列的內容必須滿足該條件(列的內容為空除外)。在CHECK條件中，可以調用SYSDATE、USER等系統(tǒng)函數。一個列上可以定義多個CHECK約束條件，一個CHECK約束可以包含

60、一列或多列。如果CHECK約束包含表的多列，則必須在表級定義。比如圖書表的“單價”的值必須大于零，就可以設置成CHECK約束條件。檢查約束的語法如下：CONSTRANT 約束名 CHECK(約束條件) -列級，約束條件中只包含本列CONSTRANT 約束名 CHECK(約束條件) -表級，約束條件中包含多列5外鍵(FOREIGN KEY)指定表的一列或多列的組合作為外鍵，外鍵參照指定的主鍵或惟一鍵。外鍵的值可以為NULL，如果不為NULL，就必須是指定主鍵或惟一鍵的值之一。外鍵通常用來約束兩個表之間的數據關系，這兩個表含有主鍵或惟一鍵的稱為主表，定義外鍵的那張表稱為子表。如果外鍵只包含一列，則