李興華Oracle學習筆記

1、1234567圖表 1891011121314多表查詢的基本語法多表查詢的基本語法查一張以上的表，就叫做多表查詢例子：查詢出雇員名稱，部門名稱和部門所在地的（一般多表查詢要用別名）15統(tǒng)計記錄數(shù)：統(tǒng)計記錄數(shù)：查詢 emp 有多少條紀錄左右連接（重點）左右連接（重點）select e.empno,e.ename,d.deptno,d.dname,d.locfromemp e,dept d where e.deptno=d.deptno;部門一共四個，這里只查詢出三個，因為在雇員表中沒有指定 40 部門的雇員，所以在消除笛卡爾乘機的時候沒有條件符合 40，如果喜歡 40 部門顯示出來，就要用左右連

2、接了。16select e.empno,e.ename,d.deptno,d.dname,d.locfromemp e,dept d where e.deptno(+)(+)(+)=d.deptno;（+）在左邊，表示以右邊的表為準，表示右鏈接。40 部門出來了，所以此時就用到了有連接，證明以下規(guī)律（+）在左表示右連接（+）在右表示左連接.SQL：1999 對對 SQL 的支持（了解）的支持（了解）范例：交叉連接（范例：交叉連接（cross join）產(chǎn)生笛卡爾積）產(chǎn)生笛卡爾積17select * from empt CROSS JOIN dept;查詢結(jié)果 產(chǎn)生笛卡爾積CREATE TABL

3、E EMP10 AS SELECT * FROM EMP WHERE DEPTNO=10;select * from emp NATURAL JOIN dept; 自動進行匹配范例：范例：USING 子句，直接關聯(lián)的操作列子句，直接關聯(lián)的操作列select * from emp e JOIN dept d USING (deptno) where deptno=30;把兩張表的詳細信息進行打印輸出范例：范例：ON 子句子句 自己編寫連接條件自己編寫連接條件select * from emp e JOIN dept d ON (e.deptno=d.deptno) where e.deptno=

4、30;范例：左連接（左外連接）右連接（右外連接）范例：左連接（左外連接）右連接（右外連接）LEFT JOIN RIGHT JOIN組函數(shù)和分組統(tǒng)計（重點）組函數(shù)和分組統(tǒng)計（重點）組函數(shù)組函數(shù)在 SQL 常用組函數(shù)有如下幾個：COUNT()求全部記錄數(shù)MAX()求最大記錄數(shù)MIN()求最小記錄數(shù)AVG()平均SUM()求和分組統(tǒng)計分組統(tǒng)計GROUP BYselect deptno,COUNT(empno) from emp GROUP BY deptno;算出部門表的平均工資：算出部門表的平均工資：18select AVG(sal) from emp ;算出每個部門的平均工資：算出每個部門的平均

5、工資：Select deptno,AVG(sal) from emp ; 之所以會出現(xiàn)這個錯誤是因為數(shù)據(jù)庫不知道怎樣在結(jié)果集中處理 deptno 列?？紤]一下：這個查詢既試圖使用 AVG 聚合函數(shù)對多 行記錄進行操作，卻又試圖從每行中獲得 deptno 列的值；這兩個操作是不可能同時完成的。此時必須提供一個 GROUP BY 子句告訴數(shù)據(jù)庫將 deptno 列相同的行分組在一起，然后數(shù)據(jù)庫就可以將這些組中的行傳遞給 AVG 函數(shù)。警告：如果查詢中包含聚合函數(shù)，而所選擇的列并不在聚合函數(shù)中，那么這些列就必須在 GROUP BY 子句中。按部門分組，并顯示部門名稱，以及部門員工數(shù)按部門分組，并顯示

6、部門名稱，以及部門員工數(shù)select d.dname,count(e.empno) from dept d,emp ewhere d.deptno=e.deptno GROUP BY d.dname;要求查出平均工資大于要求查出平均工資大于 2000 的部門編號和平均工資的部門編號和平均工資select deptno,AVG(sal) from emp WHERE AVG(sal) 2000 GROUP BY deptno; 之所以會出現(xiàn)這個錯誤是因為 WHEREWHEREWHERE 子句只能用來對單行而不是行組進行過濾子句只能用來對單行而不是行組進行過濾子句只能用來對單行而不是行組進行過濾子

7、句只能用來對單行而不是行組進行過濾。要過。要過濾行組，可以使用濾行組，可以使用 HAVING 子句。子句。19范例：顯示非銷售人員工作名稱以及從事同一工作雇員的月工資的總和，并且要滿足從事范例：顯示非銷售人員工作名稱以及從事同一工作雇員的月工資的總和，并且要滿足從事同一工作的雇員的月工資合計大于同一工作的雇員的月工資合計大于￥5000。輸出結(jié)果按月工資的合計升序排列。輸出結(jié)果按月工資的合計升序排列1.顯示全部的非銷售人員：顯示全部的非銷售人員：jobSALESMANselect * from emp where jobsalesman;2.按工作分組同時求出工資的總和按工作分組同時求出工資的總

8、和Select job,SUM(sal) from emp WHERE jobSALESMAN GROUP BY job; 3.對分組條件進行限制對分組條件進行限制Select job,SUM(sal) from emp WHERE jobSALESMAN GROUP BY job HAVING SUM(sal)5000; 4.使用排序，按升序排列使用排序，按升序排列Select job,SUM(sal) su from emp WHERE jobSALESMAN GROUP BY job HAVING SUM(sal)5000 order by su; 分組的簡單原則：分組的簡單原則：只要一

9、列上存在重復的內(nèi)容才考慮用分組注意：注意：分組函數(shù)可以嵌套使用，但是在組函數(shù)嵌套的時候不能再出現(xiàn)分組條件的查詢語句范例：范例：求出平均工資最高的部門錯誤代碼：Select deptno,MAX(AVG(sal) from emp GROUP BY deptno;Select MAX(AVG(sal) from emp GROUP BY deptno;(正確)20子查詢子查詢范例：要求查詢出比范例：要求查詢出比 7654 工資高的全部雇員信息工資高的全部雇員信息首先：要知道 7654 雇員的工資是多少然后：以此查詢結(jié)果為查詢依據(jù)，只要其他工資大于 sal，則表示符合條件首先首先：查詢出比查詢出比

10、 7654 工資高的全部雇員信息工資高的全部雇員信息select * from emp where sal(select sal from emp where empno=7654);其次：與其次：與 7788 工作一樣工作一樣Select job from emp where empno=7788所以：select * from emp where sal(select sal from emp where empno=7654) and job= （Select job from emp where empno=7788） ； ;21數(shù)據(jù)庫更新操作數(shù)據(jù)庫更新操作數(shù)據(jù)庫的主要操作分為兩種：1

11、.數(shù)據(jù)庫的查詢操作 SELECT2.數(shù)據(jù)庫的更新操作 uUPDATE， DELETE， INSERT 此時為了保存原始的 emp 表的信息，在進行更新 刪除 插入表前先將表復制一份Create table myemp AS select * from emp;此時數(shù)據(jù)已經(jīng)復制出來添加數(shù)據(jù)添加數(shù)據(jù)Insert into emp(empno,ename,job,hiredate,sal ,deptno)Values (7899,張三,清潔工,20-2 月-2000,9000, 40);使用簡略寫法使用簡略寫法(并不推薦并不推薦)，因為現(xiàn)在是要添加所有字段的內(nèi)容，所以可以不寫上任何字段，因為現(xiàn)在是要

12、添加所有字段的內(nèi)容，所以可以不寫上任何字段名稱，只要值的數(shù)量和順序和數(shù)據(jù)庫表中的順序一致。名稱，只要值的數(shù)量和順序和數(shù)據(jù)庫表中的順序一致。Insert into myemp values(7899,張三,清潔工，9000, 40);之前插入數(shù)據(jù)的時候，日期的格式是使用了表中固定好的格式，如果現(xiàn)在有這樣一個日期之前插入數(shù)據(jù)的時候，日期的格式是使用了表中固定好的格式，如果現(xiàn)在有這樣一個日期”2009-10-10”日期格式，那么現(xiàn)在如何把這種格式的日期插入進去呢？日期格式，那么現(xiàn)在如何把這種格式的日期插入進去呢？使用使用 TO_DATE()函數(shù)，將一個字符串類型的數(shù)據(jù)變?yōu)楹瘮?shù)，將一個字符串類型的數(shù)據(jù)

13、變?yōu)?DATE 類型的數(shù)據(jù)。類型的數(shù)據(jù)。Insert into myemp(empno,ename,job,hiredate,sal ,deptno)Values (7899,張三,清潔工,TO_DATE(2009-07-19,yyyy-mm-dd),9000, 40);修改數(shù)據(jù)修改數(shù)據(jù)UPDATE 表名稱 set 要修改的字段=新值，要修改的字段=新值.;UPDATE 表名稱 set 要修改的字段=新值，要修改的字段=新值WHERE 修改條件.;22修改數(shù)據(jù)修改數(shù)據(jù)刪除全部刪除全部: DELETE FROM 表名稱 局部刪除局部刪除: DELETE FROM 表名稱 WHERE 刪除條件;事

14、物處理事物處理范例：創(chuàng)建一張只包含范例：創(chuàng)建一張只包含 10 部分雇員的一張臨時表部分雇員的一張臨時表CREATE TABLE EMP10 AS SELECT * FROM EMP WHERE DEPTNO=10;打開一個 oracle 終端，進行刪除操作DELETE FROM EMP10 WHERE SAL=2450;顯示已經(jīng)刪除然后再打開另外一個 oracle 終端，查詢到 sal=2450 這條數(shù)據(jù)還在，證明這條數(shù)據(jù)并沒有被刪除，這就是 oracle 事務的概念。事務處理: 就是保證數(shù)據(jù)操作的完整性，所有的操作要么同時成功要么同時失敗。在 ORACLE 中對每一個連接到數(shù)據(jù)庫中的窗口，都

15、會與數(shù)據(jù)庫建立一個 Session。一個Session 對數(shù)據(jù)庫所做得修改不會馬上反應到數(shù)據(jù)庫的真實數(shù)據(jù)之上。是允許回滾的，當一個 Session 提交所有操作之后，數(shù)據(jù)庫才真正做出修改。進行同樣的刪除操作DELETE FROM EMP10 WHERE SAL=2450;結(jié)果它停住了，等待第一個終端操作結(jié)束再動。-這就是 ORACLE 死鎖死鎖提交事務：提交事務：COMMIT 回滾：回滾：Roolback23ORACLE 常用命令常用命令查看所有表查看所有表select table_name from user_tables;顯示表結(jié)構(gòu)顯示表結(jié)構(gòu)describe nchar_tst（nchar

16、_tst 為表名）查詢練習查詢練習1.列出至少有一個員工的所有部門信息列出至少有一個員工的所有部門信息第一步: 列出所有部門的員工數(shù)量Select deptno ,count(empno) from emp group by deptno;第二步:列出員工大于 1 的部門 Select deptno ,count(empno) from emp group by deptno HAVING COUNT(empno)1;第三步:通過多表關聯(lián)查，把子查詢做為一個查詢出來select d.*,ed.couFROM dept d,(SELECT deptno,COUNT(empno) cou FROM

17、 empGROUP BY deptno HAVING COUNT(empno)1) ed WHERE d.depno=ed.deptno ;Comment U1: Comment MS2: 查找 e 表的直屬領導編號等于 m 表的員工,也就是說 m表是領導表,m 表的雇傭日期晚于 e 表242.列出薪金比列出薪金比 SMITH 多的所有員工多的所有員工第一步：求出 SMITH 的工資SELECT salFROM EMP WHERE ENAME=SMITH;第二步：select ename from emp where sal( SELECT salFROM EMP WHERE ENAME=SM

18、ITH);3 列出所有員工姓名及其直接上級的姓名列出所有員工姓名及其直接上級的姓名此程序?qū)儆谧陨黻P聯(lián)查詢SELECT e.ename, d.ename FROM emp e ,emp d where e.mgr=d.empno;4. 列出受雇日期早于其直接上級的所有員工的編號列出受雇日期早于其直接上級的所有員工的編號,姓名姓名,部門名稱部門名稱自身關聯(lián),查找 mgr=empno 的同時還要比較 Hiredate第一步:SELECT e.empno,e.enameFROM emp e, emp m WHERE e.mgr=m.empno AND e.hiredatem.hiredate;第二步:

19、SELECT e1.empno,e1.ename,d.dname FROM (SELECT e.empno,e.enameFROM emp e, emp m WHERE e.mgr=m.empno AND e.hiredatem.hiredate) e1,dept dWHERE e1.deptno=d.deptno;ORA-00904 invalid identifier 這個錯誤是因為這個錯誤是因為 字段名寫錯了字段名寫錯了 檢查下字段名，發(fā)現(xiàn)檢查下字段名，發(fā)現(xiàn) e1 表的查詢結(jié)果沒有表的查詢結(jié)果沒有 deptno 字段字段,所以報錯了！所以報錯了！SELECT e1.empno,e1.en

20、ame,d.dname FROM (SELECT e.empno,e.ename,e.deptnoFROM emp e, emp m WHERE e.mgr=m.empno AND e.hiredatem.hiredate) e1,dept dWHERE e1.deptno=d.deptno;25李興華給出的標準答案是李興華給出的標準答案是:SELECT e.empno,e.ename,d.dname FROM emp e, emp m, dept dWHERE e.mgr=m.empno AND e.hiredate1500 GROUP BY job;結(jié)果出來,但是是錯的。李興華分析一：按工

21、作分組,分組條件最低工資大于 1500SELECT job, MIN(sal) FROM emp GROUP BY job HAVING MIN(sal) 1500;分析二:SELECT e.job,COUNT(e.empno) FROM emp e WHERE e.job IN( SELECT job FROM emp GROUP BY job HAVING MIN(sal)1500 ) GROUP BY e.job;Comment U3: e.mgr=m.empno e 表的領導編號等于 m 表的員工編號,emp 表自身關聯(lián) m 表的員工=e 表的領導,所以 m.ename 就是就是:領導

22、領導”。Comment U4: 工資屬于這個區(qū)間內(nèi)表示這個等級。278列出在部門列出在部門”SALES”(銷售部銷售部)工作的員工的姓名工作的員工的姓名,假定不知道銷售部的部門編號假定不知道銷售部的部門編號1.Select deptno from dept where dname= SALES; 將第一步驟作為條件寫子查詢2.select ename from emp where deptno=( Select deptno from dept where dname=SALES);9. 列出薪金高于公司平均薪金的所有員工列出薪金高于公司平均薪金的所有員工,所在部門所在部門,上級領導上級領導,

23、公司的工資等級。公司的工資等級。1.算出平均工資: Select AVG(sal) from emp 2. select * from emp where sal( Select AVG(sal) from emp);答案:第一步求出公司的平均工資-1,第二步:列出薪金高于平均工資的所有雇員的信息-2第三步:要求所在部門肯定要與部門表關聯(lián)關聯(lián)查所在部門的信息,3. SELECT e.*,d.dname,d.loc FROM emp e,dept d WHERE sal(SELECT AVG(sal) FROM emp ) AND e.deptno=d.deptno;第四步要想查詢上級領導肯定要

24、與自身關聯(lián)4.SELECT e.empno,e.ename,m.ename,d.dname,d.deptno,d.loc FROM emp e,dept d,emp m WHERE e.sal(SELECT AVG(sal) FROM emp ) AND e.deptno=d.deptno AND e.mgr=m.empno(+);第五步：求出雇員的工資等級SELECT e.empno,e.ename,s.grade,m.ename,d.dname,d.deptno,d.loc FROM emp e,dept d,emp m,salgrade s WHERE e.sal(SELECT AVG(

25、sal) FROM emp ) AND e.deptno=d.deptno AND e.mgr=m.empno(+) AND e.sal BETWEEN s.losal AND s.hisal;Comment U5: 合理的情況是沒有deptno=30 的2810 列出與列出與”SCOTT” 從事相同工作的所有員工及部門的名稱。從事相同工作的所有員工及部門的名稱。(dims)第一步:SELECT job FROM emp e where e.ename=SCOTT; 第二步:SELECT e.ename, d.dname FROM emp e ,dept dwhere e.job=( SELE

26、CT job FROM emp e where e.ename=SCOTT)AND e.enameSCOTT AND e.deptno=d.deptno;11 列出薪金等于部門列出薪金等于部門 30 中員工的薪金的所有員工的姓名和薪金中員工的薪金的所有員工的姓名和薪金(dims)SELECT e.ename,e.sal FROM emp e where sal IN(SELECT sal FROM emp where deptno=30) AND deptno30;12 列出薪金高于在部門列出薪金高于在部門 30 工作的所有員工的薪金的員工姓名和薪金工作的所有員工的薪金的員工姓名和薪金.，部門

27、名稱，部門名稱Select sal from emp where sal (MAX(SELECT sal FROM emp where deptno=30);答案: 在之前的程序改,使用ALL 比最大的還要大SELECT e.ename,e.sal,d.dname,d.loc FROM emp e ,dept d where sal ALL(SELECT sal FROM emp where deptno=30) AND e.deptno30 AND e.deptno=d.deptno;13 列出在每個部門工作的員工數(shù)量，平均工資和平均服務期限列出在每個部門工作的員工數(shù)量，平均工資和平均服務期

28、限第一步查詢出每個部門的員工數(shù)量:SELECT d.dname,COUNT(empno) FROM emp e ,dept d WHERE e.deptno=d.deptno GROUP BY d.dname;答案:在第一步的基礎上,第二步要讀出平均工資平均工資和服務期限服務期限:嘗試:SELECT d.dname,COUNT(empno),AVG(e.sal), AVG(e.hiredate) FROM emp e ,dept d WHERE e.deptno=d.deptno GROUP BY d.dname;SELECT d.dname,COUNT(empno),AVG(e.sal),

29、AVG(MONTHS_BETWEEN(sysdate,e.hiredate)/12) 年年FROM emp e ,dept d WHERE e.deptno=d.deptno GROUP BY d.dname;Comment U6: ORA-00979: 不是 GROUP BY 表達式.錯誤29oracle 中的 months_between(date1,date2)months_between(date1,date2)函數(shù)可以實現(xiàn)年齡的計算，此函數(shù)的返回值為 date1 與 date2 之間的月數(shù)， 比如 months_between(2008-8-8，2005-4-8)執(zhí)行的結(jié)果是 40（

30、執(zhí)行“select months_between(2008-8-8,2005-4-8) from dual;”不包括雙引號）。date1 和 date2 都是 DATE 類型的，用 SYSDATE 可以獲得系統(tǒng)時間，用現(xiàn)在的時間減去變量中的時間再與年齡總月 數(shù)比較即可實現(xiàn)這一功能。14 列出所有員工的姓名列出所有員工的姓名,部門名稱和工資部門名稱和工資(dims)Select e.ename,d.dname,e.sal from emp e,dept d where e.deptno=d.deptno; 15 列出所有部門的詳細信息和部門人數(shù)列出所有部門的詳細信息和部門人數(shù)第一步: 這種題型肯

31、定是多表關聯(lián)子查詢,首先列出部門的人數(shù)SELECT deptno dno, COUNT(empno) cou FROM emp GROUP BY deptno第二步:把以上的結(jié)果當成一張臨時表出現(xiàn);SELECT d.*,ed.cou FROM dept d,(SELECT deptno dno,COUNT(empno) cou FROM emp CROUP BY deptno ) ed WHERE d.deptno=ed.dno;16 列出各種工作的最低工資及從事此工作的雇員姓名列出各種工作的最低工資及從事此工作的雇員姓名1查找工資最低的工作：SELECT job,MIN(sal) FROM

32、emp GROUP BY job;2SELECT e.ename,e.job,cou.minsal FROM (SELECT job,MIN(sal) minsal, empno FROM emp GROUP BY job) cou,emp e WHERE e.empno= cou.empno ;答案:SELECT *FROM empWHERE sal IN(SELECT MIN(sal) FROM emp GROUP BY job);17 列出各個部門的列出各個部門的 MANAGER(經(jīng)理經(jīng)理)的最低薪金的最低薪金(dims)Select d.dname,MIN(e.sal)From emp

33、 e ,dept dWhere e.deptno=d.deptno AND e.job=MANAGER group by d.dname;18 列出所有員工的年工資列出所有員工的年工資,按年薪從低到高排序按年薪從低到高排序在處理年薪的時候,要處理獎金，獎金要使用 NVL 函數(shù)處理SELECT ename,(sal+NVL(comm,0)*12 income FROM empORDER BY income;19 查出某個員工的上級主管查出某個員工的上級主管,并要求出這些主管中的薪水超過并要求出這些主管中的薪水超過 3000SELECT DISTINCT m.* FROM emp e,emp mW

34、HERE e.mgr=m.empno AND m.sal3000;3020 求出部門名稱中求出部門名稱中,帶帶s字符的部門員工的工資合計字符的部門員工的工資合計,部門人數(shù)部門人數(shù)查詢部門表的部門名稱查詢部門表的部門名稱,使用模糊查詢使用模糊查詢,以確定部門的編號以確定部門的編號SELECT deptno FROM dept WHERE dname LIKE %S%;以上面的結(jié)果作為查詢的條件SELECT deptno,SUM(sal),COUNT(empno) FROM emp WHERE deptno IN (SELECT deptno FROM dept WHERE dname like

35、%S%) GROUP BY deptno;21 給任職日期超過給任職日期超過 10 年的人加薪年的人加薪 10%UPDATE emp SET sal=sal+(sal*0.1)WHERE MONTHS_BETWEEN(sysdate,hiredate)/12 10;31創(chuàng)建表和管理表（創(chuàng)建表和管理表（重點重點）ORACLE 復制表的語法：CREATE TABLE 表名稱 AS(子查詢)如果現(xiàn)在子查詢寫的是：SELECT *FROM emp 表示將表結(jié)構(gòu)和內(nèi)容一起復制如果現(xiàn)在子查詢寫的是：SELECT * FROM emp WHERE 1=2; 加入一個永遠不可能成立的條件，表示只是復制表結(jié)構(gòu)不

36、復制表內(nèi)容。CREATE TABLE person_f(Pid VARCHAR(18),Name VARCHAR(200),Age NUMBER(3),Birthday DATE,SEX VARCHAR(2) DEFAULT 男);表的刪除表的刪除DELETE TABLE 表名稱范例：刪除范例：刪除 person 表表 : DELETE TABLE person;32表的修改表的修改范例：為范例：為 person 表增加表增加 address 列列ALTER TABLE person ADD( address VARCHAR(200) DEFAULT 暫無地址);為表重命名為表重命名在 Ora

37、cle 中提供了 RENAME 命名,可以為表重新命名,但是此語法只能在 Oracle 中使用語法格式: RENAME 舊的表名稱 TO 新的表名稱范例：將 person 重新命名為了 tpersonRENAME person TO miaofang;33思考題思考題分析:這是一道面試題，只能用笛卡爾乘機:Select e.country,d.country from country d ,country e where e.countryd.country;34約束約束約束的分類約束的分類:主鍵約束主鍵約束(PRIMARY KEY)主鍵約束一般都在 id 上使用，而且本身已經(jīng)默認了內(nèi)容不能為

38、空，主鍵約束可以在建表的時候指定：范例范例:建立建立 person 表，在表，在 pid 上增加主鍵約束上增加主鍵約束CREATE TABLE person(Pid VARCHAR(18) PRIMARY KEY,Name VARCHAR(200),Age NUMBER(3),Birthday DATE,SEX VARCHAR(2) DEFAULT 男);insert into person values(null,柯為權,123,TO_DATE(2009-07-19,yyyy-mm-dd)TO_DATE(2009-07-19,yyyy-mm-dd)TO_DATE(2009-07-19,yyy

39、y-mm-dd),男);主鍵為空或者重復都會報錯主鍵為空或者重復都會報錯35范例范例:將將 person 中的中的 pid 指定名稱指定名稱CREATE TABLE person_a(Pid VARCHAR(18) ,Name VARCHAR(200),Age NUMBER(3),Birthday DATE,SEX VARCHAR(2) DEFAULT 男,CONSTRAINT person_pid_pk PRIMARY KEY(pid);ConstraintConstraintConstraint knstrentknstrentknstrent：約束：約束：約束; ; ;限制限制限制per

40、son_pid_pk：表示約束的名稱非空約束非空約束(NOT NULL)CREATE TABLE person_a(Pid VARCHAR(18) NOT NULL ,Name VARCHAR(200) NOT NULL,Age NUMBER(3),Birthday DATE,SEX VARCHAR(2) DEFAULT 男,CONSTRAINT person_pid_pk PRIMARY KEY(pid);表示插入了兩個非空約束的聲明，證明此時 PID 跟 name 是不允許為空的。唯一約束唯一約束(UNIQUE)表示一個字段中的內(nèi)容是唯一的，其他列不允許重復CREATE TABLE per

41、son_a(Pid VARCHAR(18) NOT NULL ,Name VARCHAR(200) UNIQUE NOT NULL,Age NUMBER(3),Birthday DATE,SEX VARCHAR(2) DEFAULT 男,CONSTRAINT person_pid_pk PRIMARY KEY(pid);表示姓名唯一。36檢查約束檢查約束 (CHECK)使用檢查約束來判斷一個列中插入的內(nèi)容是否合法，例如:年齡，性別的取值范圍。CREATE TABLE person_a(Pid VARCHAR(18) NOT NULL ,Name VARCHAR(200) NOT NULL,Ag

42、e NUMBER(3) CHECK( age BETWEEN 0 AND 150),Birthday DATE,SEX VARCHAR(2) DEFAULT 男 CHECK( sex I N (男,女,中性),CONSTRAINT person_pid_pk PRIMARY KEY(pid);修改約束修改約束(了解了解)一張表建立完成之后，則可以為其添加約束DROP TABLE person CASCADE CONSTRAINT;CREATE TABLE person(Pid VARCHAR(18) PRIMARY KEY,Name VARCHAR(200),Age NUMBER(3),Bir

43、thday DATE,SEX VARCHAR(2) DEFAULT 男);此時，需要為表中添加若干個約束，添加約束的語法如下：ALTER TABLE 表名稱 ADD CONSTRAINT 約束名稱 約束類型（約束字段）;關于約束類型的命名一定要統(tǒng)一:1)PRIMARY KEY:主鍵字段_PK2)UNIQUE：字段_UK3)CHECK：字段_CK4)FOREIGN KEY：父字段_子字段_FKALTER TABLE person ADD CONSTRAINT person_pid_PK PRIMARY KEY(pid);ALTER TABLE person ADD CONSTRAINT pers

44、on_name_UK UNIQUE(pid);ALTER TABLE person ADD CONSTRAINT person_age_CK CHECK(age BETWEEN 0 AND 150);ALTER TABLE person ADD CONSTRAINT person_sex_CK CHECK(sex IN (男,女,中);37ROWNUM(重點重點)ROWNUM:表示行號，實際上此是一個列,但是這個列是一個偽列,此列可以在每張表中出現(xiàn)。范例范例:在查詢雇員表上,加入 ROWNUMSELECT ROWNUM, empno,ename,job,sal,hiredate FROM em

45、p;從運行上看,ROWNUM 本身采用自動編號的形式出現(xiàn),有什么用處呢？例如例如,現(xiàn)在假設只想顯示前 5 條記錄。條件中：ROWNUM=5SELECT ROWNUM,empno,ename,job,sal,hiredate FROM emp WHERE ROWNUM 1000000在這里，我們不能用 where 來篩選超過 1000000 的地區(qū)，因為表中不存在這樣一條記錄。相反，HAVING 子句可以讓我們篩選成組后的各組數(shù)據(jù)53oracle 常見問題常見問題1.在打開在打開 PL/SQL DEVELOP 的時候的時候 回彈出回彈出: ORA-12170: TNS: 連接超時連接超時關閉掉殺

46、毒跟防火墻都無法解決：關閉掉殺毒跟防火墻都無法解決：點 Net Manager -設置好主機名，也可以設置設置好主機名，也可以設置 IP 地址，但是每次都要重新設置。地址，但是每次都要重新設置。Ok-成功解決！成功解決！542查找查找 SQL/PL 生成生成 下一個序列號下一個序列號在在 Oracle 庫中查詢出下個自動生成的庫中查詢出下個自動生成的 id 號號3. ORA-00904 INVALID IDENTIFIER 解決辦法解決辦法,字段名錯誤字段名錯誤ORA-00904 invalid identifier 這個錯誤是因為 字段名寫錯了 檢查下字段名3. oracle 10g 安裝成

47、功之后。安裝成功之后。scott 用戶被鎖定問題用戶被鎖定問題原因：默認 Oracle10g 的 scott 不能登陸。解決：（1）conn sys/sys as sysdba;/以 DBA 的身份登錄（2）alter user scott account unlock;/ 然后解鎖（3）conn scott/tiger /彈出一個修改密碼的對話框，修改一下密碼就可以了具體操作步驟如下：C: sqlplus請輸入用戶名：sys輸入口令：sys as sysdba /注意：在口令這里輸入的密碼后面必須要跟上 as sysdba 才可以。SQL alter user scott account u

48、nlock; 用戶已更改.SQL commit; 提交完成.SQL conn scott/tiger更改 scott 口令新口令：tiger重新鍵入新口令：tiger口令已更改已連接。/完成。4. ORACLE 連接其他地址的庫連接其他地址的庫在 ORACLE 安裝目錄下的：C:oracleproduct10.2.0db_1NETWORKADMIN 的 tnsnames.ora 文件# tnsnames.ora Network Configuration File: C:oracleproduct10.2.0db_1networkadmintnsnames.oraComment U20: 這就

49、是對應地址10.249.254.74 的配置55tnsnames.ora# Generated by Oracle configuration tools.ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) )EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL =

50、 IPC)(KEY = EXTPROC1) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) )orcl74= (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.249.254.74)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) ) 如果要連接jdbc.url =jdbc:oracle:thin:10.249.254.74:1521:orclpool.url=jdbc:ora

51、cle:thin:zhmwc/zhgmcc&$10.249.254.74:1521:orcljdbc.username =zhmwcjdbc.password =zhgmcc&$6.ORACLE 權限問題權限問題在用 SCOTT 用戶創(chuàng)建視圖的時候出現(xiàn)5657解決辦法是:首先在開始-運行SQLPLUS，然后輸入 SYS/CHANGE_ON_INSTALL AS SYSDBA以 SYS 權限登陸進去然后可以進行操作：grant create any view to SCOTT;/把創(chuàng)建視圖的權限賦給把創(chuàng)建視圖的權限賦給 scott;在用在用 scott 登入登入:SELECT *

52、 FROM USER_SYS_PRIVS;這樣就可以知道當前用戶的權限常用的賦權命令常用的賦權命令:1.創(chuàng)建用戶創(chuàng)建用戶 CREATE USER TEST INDENTIFIED BY TEST;這樣就創(chuàng)建了一個用戶名密碼都為 TEST 的用戶2.登錄權限登錄權限 GRANT CREATE SESSION TO TEST;這樣 TEST 用戶就能成功登陸進去3. 建表權限建表權限 GRANT CREATE TABLE TO TEST;4 .使用表空間權限使用表空間權限 GRANT UNLIMITED TABLESPACE TO TEST;5. 查詢當前用戶的系統(tǒng)權限：查詢當前用戶的系統(tǒng)權限：SELECT * FROM USER_SYS_PRIVS;這樣就可以知道當前用戶的權限這樣就可以知道當前用戶的權限7.撤銷權限撤銷權限 REVOKE CREATE TABLE FROM