部分常見ORACLE面試題以及SQL注意事項

1、部分常見ORACLE面試題以及SQL注意事項一、表的創(chuàng)建：一個通過單列外鍵聯(lián)系起父表和子表的簡單例子如下：CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id) CREATE TABLE child(id INT, parent_id INT,INDEX par_ind (parent_id),FOREIGN KEY (parent_id) REFERENCES parent(id)ON DELETE CASCADE) 建表時注意不要用關(guān)鍵字當(dāng)表名或字段名，如insert,use等。CREATE TABLE parent(id INT NOT

2、 NULL,PRIMARY KEY (id) TYPE=INNODB;二、InnoDB Tables 概述 InnoDB給MySQL提供了具有事務(wù)(commit)、回滾(rollback)和崩潰修復(fù)能力(crash recovery capabilities)的事務(wù)安全(transaction-safe (ACID compliant)型表。 InnoDB 提供了行鎖(locking on row level)，提供與 Oracle 類型一致的不加鎖讀取(non-locking read in SELECTs)。這些特性均提高了多用戶并發(fā)操作的性能表現(xiàn)。在InnoDB表中不需要擴大鎖定(loc

3、k escalation)， 因為 InnoDB 的列鎖定(row level locks)適宜非常小的空間。 InnoDB 是 MySQL 上第一個提供外鍵約束(FOREIGNKEYconstraints)的表引擎。InnoDB的設(shè)計目標(biāo)是處理大容量數(shù)據(jù)庫系統(tǒng)，它的CPU利用率是其它基于磁盤的關(guān)系數(shù)據(jù)庫引擎所不能比的。 三、從一個表中查詢出數(shù)據(jù)插入到另一個表中的方法：select * into destTbl from srcTbl ;（在oracle中不適用）insert into destTbl(fld1, fld2) select fld1, 5 from srcTbl insert

4、into aa select * from dept 第三句同第二句。create table s_emp_42 as select * from s_emp where 1=2;/ 永假式 只想要結(jié)構(gòu)而不要數(shù)據(jù)。以上三句都是將 srcTbl 的數(shù)據(jù)到 destTbl，但兩句又有區(qū)別的。 第一句（select into from）要求目標(biāo)表（destTbl）不存在，因為在插入時會自動創(chuàng)建。 第二句（insert into select from）要求目標(biāo)表（destTbl）存在，由于目標(biāo)表已經(jīng)存在，所以我們除了插入源表（srcTbl）的字段外，還可以插入常量，如例中的：5，或者插入字符 字符四

5、、SQL查詢練習(xí)題(1) 表1：book表，字段有id(主鍵)，name (書名)；表2：bookEnrol表（圖書借出歸還登記），字段有id，bookId（外鍵），dependDate（變更時間），state（1.借出 2.歸還）。 id name 1 English2 Math3 JAVAiddependDate state bookId12009-01-02 1 122009-01-12 2 13 2009-01-14 1 242009-01-17 1 152009-02-14 2 262009-02-15 1 272009-02-18 1 382009-02-19 2 3要求查詢結(jié)果應(yīng)

6、為：(被借出的書和被借出的日期)Id Name dependDate1 English 2009-01-172 Math 2009-02-15第二個表是用來登記的，不管你是借還是還，都要添加一條記錄。請寫一個SQL語句，獲取到現(xiàn)在狀態(tài)為已借出的所有圖書的相關(guān)信息。參考語句：select book.id,,max(dependDate)from book inner join bookEnrol on book.id=bookEnrol.bookid AND booker.state=1 group by book.id ;（這個答案是錯誤的）正確的語句一：select boo

7、k.id,,dependdate from book,bookEnrol where dependdate in（select max(dependdate) from bookEnrol group by bookid） and book.id=bookEnrol.bookid and bookEnrol.state=1;正確的語句二：select book.id,,dependdate from book inner join bookEnrol on book.id=bookEnrol.bookid where dependdate in(select

8、 max(dependdate) from bookEnrol group by bookid) and bookEnrol.state=1;（2）表一：各種產(chǎn)品年銷售量統(tǒng)計表 sale 年 產(chǎn)品 銷量 2005 a 700 2005 b 550 2005 c 600 2006 a 340 2006 b 500 2007 a 220 2007 b 350 要求得到的結(jié)果應(yīng)為： 年 產(chǎn)品 銷量 2005 a 700 2006 b 500 2007 b 350 即：每年銷量最多的產(chǎn)品的相關(guān)信息。參考答案一： Select * from sale a where not exists(select

9、* from sale where 年=a.年 and 銷量a.銷量);參考答案二： select * from sale a inner join (select 年,max(銷量) as 銷量from sale group by 年) b on a.年=b.年 and a.銷量=b.銷量 參考答案三（我的答案）：select * from sale where 銷量 in (select max(銷量) from sale group by 年);（3）.查詢語句排名問題：表一：名次 姓名 月積分(char) 總積分（char） 1 WhatIsJava 1 99 2 水王 76 981

10、3 新浪網(wǎng) 65 96 4 牛人 22 9 5 中國隊 64 89 6 北林信息 66 66 7 加太陽 53 66 8 中成藥 11 33 9 西洋參 25 26 10 大拿 33 23 如果用總積分做降序排序.因為總積分是字符型，所以排出來是這樣子(9,8,7,6,5.)，要求按照總積分的數(shù)字大小排序。參考答案：select * from tablename order by cast(總積分 as int) desc 參考答案（我的答案）：select * from tablename order by to_number(總積分) desc；表tb uid mark 1 7 1 6 2

11、 3 2 2 2 5 3 4 3 3 4 8 4 1 4 3 想查出uid=4的名次： uid he mc 412 2 我的答案：select * from (select rownum mc,ui,he from (select ui,sum(mark) he from tb group by ui order by he desc) where ui=4;表A字段如下 month name income 月份 人員 收入 1 a 10002 a 20003 a 3000要求用一個SQL語句（注意是一個）的處所有人（不區(qū)分人員）每個月及上月和下月的總收入 要求列表輸出為 月份 當(dāng)月收入 上月

12、收入 下月收入 2 2000 1000 3000Select (Select Month From Table Where Month = To_Char(Sysdate, mm) 月份,(Select Sum(Income) From Table Where Month = To_Char(Sysdate, mm) 當(dāng)月收入,(Select Sum(Income) From Table Where To_Number(Month) = To_Number(Extract(Month From Sysdate) - 1) 上月收入,(Select Sum(Income) From Table

13、Where To_Number(Month) = To_Number(Extract(Month From Sysdate) + 1) 下月收入From Dual算排名列出每一行的排名是一個常見的需求，可惜 SQL 并沒有一個很直接的方式達到這個需求。要以 SQL 列出排名，基本的概念是要做一個表格自我連結(jié) (self join)，將結(jié)果依序列出，然后算出每一行之前 (包含那一行本身) 有多少行數(shù)。這樣講讀者聽得可能有點困惑，所以最好的方式是用一個實例來介紹。假設(shè)我們有以下的表格： NameSalesJohn10Jennifer15Stella20Sophia40Greg50Jeff20Tot

14、al_Sales 表格要找出每一行的排名，我們就打入以下的 SQL 語句：SELECT a1.Name, a1.Sales, COUNT(a2.sales) Sales_Rank FROM Total_Sales a1, Total_Sales a2 WHERE a1.Sales = a2.Sales or (a1.Sales=a2.Sales and a1.Name = a2.Name) GROUP BY a1.Name, a1.Sales ORDER BY a1.Sales DESC, a1.Name DESC;結(jié)果:Name Sales Sales_Rank Greg 50 1 Soph

15、ia 40 2 Stella 20 3 Jeff 20 3 Jennifer 15 5 John 10 6 我們先來看 WHERE 子句。在字句的第一部分 (a1.Sales = a2.Sales)，我們算出有多少筆資料 Sales 欄位的值是比自己本身的值小或是相等。如果在 Sales 欄位中沒有同樣大小的資料，那這部分的 WHERE 子句本身就可以產(chǎn)生出正確的排名。 子句的第二部分，(a1.Sales=a2.Sales and a1.Name = a2.Name)，則是讓我們在 Sales 欄位中有同樣大小的資料時 (像 Stella 及 Jeff 這兩筆資料)，仍然能夠產(chǎn)生正確的排名。

16、算中位數(shù)要算出中位數(shù)，我們必須要能夠達成以下幾個目標(biāo)： 將資料依序排出，并找出每一行資料的排名。 找出中間的排名為何。舉例來說，如果總共有 9 筆資料，那中間排名就是 5 (有 4 筆資料比第 5 筆資料大，有 4 筆資料比第 5 筆資料小)。 找出中間排名資料的值。 來看看以下的例子。假設(shè)我們有以下的表格： Total_Sales 表格NameSalesJohn10Jennifer15Stella20Sophia40Greg50Jeff20要找出中位數(shù)，我們就鍵入：SELECT Sales Median FROM (SELECT a1.Name, a1.Sales, COUNT(a1.Sal

17、es) Rank FROM Total_Sales a1, Total_Sales a2 WHERE a1.Sales a2.Sales OR (a1.Sales=a2.Sales AND a1.Name = a2.Name) group by a1.Name, a1.Sales order by a1.Sales desc) a3 WHERE Rank = (SELECT (COUNT(*)+1) DIV 2 FROM Total_Sales); 結(jié)果:Median 20 讀者將會發(fā)現(xiàn)，第 2 行到第 6 行是跟產(chǎn)生 排名 的語句完全一樣。第 7 行則是算出中間的排名。DIV 是在 MySQ

18、L 中算出商的方式。在不同的數(shù)據(jù)庫中會有不同的方式求商。第 1 行則是列出排名中間的資料值。 算累積總計算出累積總計是一個常見的需求，可惜以 SQL 并沒有一個很直接的方式達到這個需求。要以 SQL 算出累積總計，基本上的概念與列出排名類似：第一是先做個表格自我連結(jié) (self join)，然后將結(jié)果依序列出。在做列出排名時，我們算出每一行之前 (包含那一行本身) 有多少行數(shù)；而在做累積總計時，我們則是算出每一行之前 (包含那一行本身) 的總合。 來看看以下的例子。假設(shè)我們有以下的表格： Total_Sales 表格NameSalesJohn10Jennifer15Stella20Sophia

19、40Greg50Jeff20要算出累積總計，我們就鍵入：SELECT a1.Name, a1.Sales, SUM(a2.Sales) Running_Total FROM Total_Sales a1, Total_Sales a2 WHERE a1.Sales = a2.sales or (a1.Sales=a2.Sales and a1.Name = a2.Name) GROUP BY a1.Name, a1.Sales ORDER BY a1.Sales DESC, a1.Name DESC;結(jié)果:Name Sales Running_Total Greg 50 50 Sophia 4

20、0 90 Stella 20 110 Jeff 20 130 Jennifer 15 145 John 10 155 在以上的 SQL 語句中， WHERE 子句和 ORDER BY 子句讓我們能夠在有重復(fù)值時能夠算出正確的累積總計。 算總合百分比要用 SQL 算出總合百分比，我們需要用到算排名和累積總計的概念，以及運用子查詢的做法。在這里，我們把子查詢放在外部查詢的 SELECT 子句中。讓我們來看以下的例子： Total_Sales 表格NameSalesJohn10Jennifer15Stella20Sophia40Greg50Jeff20要算出總合百分比，我們鍵入：SELECT a1.

21、Name, a1.Sales, a1.Sales/(SELECT SUM(Sales) FROM Total_Sales) Pct_To_Total FROM Total_Sales a1, Total_Sales a2 WHERE a1.Sales = a2.sales or (a1.Sales=a2.Sales and a1.Name = a2.Name) GROUP BY a1.Name, a1.Sales ORDER BY a1.Sales DESC, a1.Name DESC;結(jié)果:Name Sales Pct_To_Total Greg 50 0.3226 Sophia 40 0.

22、2581 Stella 20 0.1290 Jeff 20 0.1290 Jennifer 15 0.0968 John 10 0.0645 SELECT SUM(Sales) FROM Total_Sales 這一段子查詢是用來算出總合。總合算出后，我們就能夠?qū)⒚恳恍幸灰怀钥偤蟻砬蟪雒恳恍械目偤习俜直取?算累積總合百分比要用 SQL 累積總合百分比算出，我們運用類似總合百分比的概念。兩者的不同處在于在這個情況下，我們要算出到目前為止的累積總合是所有總合的百分之幾，而不是光看每一筆資料是所有總合的百分之幾。讓我們來看看以下的例子： Total_Sales 表格NameSalesJohn10J

23、ennifer15Stella20Sophia40Greg50Jeff20要算出累積總合百分比，我們鍵入：SELECT a1.Name, a1.Sales, SUM(a2.Sales)/(SELECT SUM(Sales) FROM Total_Sales) Pct_To_Total FROM Total_Sales a1, Total_Sales a2 WHERE a1.Sales create table a ( 2 bm char(4), -編碼 3 mc varchar2(20) -名稱 4 ) 5 / SQL select rowid,bm,mc from a; ROWID BM M

24、C - - - 000000D5.0000.0002 1111 1111 000000D5.0001.0002 1112 1111 000000D5.0002.0002 1113 1111 000000D5.0003.0002 1114 1111 000000D5.0004.0002 1111 1111 000000D5.0005.0002 1112 1111 000000D5.0006.0002 1113 1111 000000D5.0007.0002 1114 1111 查詢到8記錄. 查出重復(fù)記錄 SQL select rowid,bm,mc from a where a.rowid!=

25、(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc); ROWID BM MC - - - 000000D5.0000.0002 1111 1111 000000D5.0001.0002 1112 1111 000000D5.0002.0002 1113 1111 000000D5.0003.0002 1114 1111 刪除重復(fù)記錄 SQL delete from a a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc); 刪除4個

26、記錄. SQL select rowid,bm,mc from a; ROWID BM MC - - - 000000D5.0004.0002 1111 1111 000000D5.0005.0002 1112 1111 000000D5.0006.0002 1113 1111 000000D5.0007.0002 1114 1111 其他組合函數(shù)Group by 子句Distinct 關(guān)鍵字偽列ROWNUM，用于為子查詢返回的每個行分配序列值注意：組函數(shù)可以處理一組數(shù)據(jù)，返回一個值。組函數(shù)會忽略空值。where 后只能跟單行函數(shù)，不能有組函數(shù)。使用TOP-N分析法TOP-N分析法基于條件顯示

27、表中最上面N條記錄或最下面N條記錄TOP-N查詢包含以下內(nèi)容：1，一個用于排序數(shù)據(jù)的內(nèi)聯(lián)視圖2，使用ORDER BY子句或DESC參數(shù)的子查詢3，一個外層查詢。由它決定最終記錄中行的數(shù)目。這包括ROWNUM偽列和用于比較運算符的WHERE子句/語法：SELECT ROWNUM,column_listFROM (SELECT column_list FROM table_name ORDER BY Top-n-column_name)WHERE ROWNUM = N例1：查詢Employee表的頂部10條記錄/方法1：單表時可以用select cEmployeeCode,vFirstName,v

28、LastName from employee where rownum = 10/方法2：較復(fù)雜的查詢，建議使用這種select * from (select rownum as num,cEmployeeCode,vFirstName,vLastName from employee)where num select concat(010-,88888888)|轉(zhuǎn)23 高乾競電話 from dual;高乾競電話232.LTRIM和RTRIMLTRIM 刪除左邊出現(xiàn)的字符串RTRIM 刪除右邊出現(xiàn)的字符串SQL select ltrim(rtrim( gao qian

29、 jing , ), ) from dual;LTRIM(RTRIM(-gao qian jing3.SUBSTR(string,start,count)取子字符串,從start開始,取count個4日期函數(shù)如:LAST_DAY 返回本月日期的最后一天具體參見oracle筆記.其他主要函數(shù)：.TRUNC 按照指定的精度截取一個數(shù)；SQRT 返回數(shù)字n的根；POWER（n1,n2）返回n1的n2次方根；MOD(n1,n2) 返回一個n1除以n2的余數(shù)；FLOOR 對給定的數(shù)字取整數(shù)；REPLACE(string,s1,s2) string 希望被替換的字符或變量 s1 被替換的字符串 s2 要替

30、換的字符串；LOWER 返回字符串,并將所有的字符小寫；UPPER返回字符串,并將所有的字符大寫；LENGTH返回字符串的長度。ORALCE常識 及 SQL 基本語法1，ORACLE安裝完成后的初始口令？internal/oraclesys/change_on_installsystem/managerscott/tiger scott是Oracle的核心開發(fā)人員之一，tiger是他家的一只貓的名字sysman/oem_temp例：conn scott/tigerjspdev; conn system/managerjspdev as sysdba;2，IBM的Codd （Edgar Fran

31、k Codd）博士提出大型共享數(shù)據(jù)庫數(shù)據(jù)的關(guān)系模型3,ORACLE 9i 中的 i （internet）是因特網(wǎng)的意思4,ORACLE的數(shù)據(jù)庫的物理結(jié)構(gòu)：數(shù)據(jù)文件、日志文件、控制文件5,ORACLE的數(shù)據(jù)庫的邏輯結(jié)構(gòu)：表空間表段區(qū)間塊表空間 類似于SQLSERVER中數(shù)據(jù)庫的概念6,SYSDATE 返回當(dāng)前系統(tǒng)日期（說明：當(dāng)函數(shù)沒有參數(shù)時可以省略括號）7,在SQL PLUS中 執(zhí)行緩沖區(qū)中的SQL命令的方式：SQL runSQL rSQL /8,在SQL PLUS中 修改當(dāng)前會話的日期顯示格式SQL alter session set nls_date_format = YYYY-MM-DD9

32、,使用臨時變量，提高輸入效率SQL insert into emp(empno,ename,sal) values(&employeeno,&employeename,&employeesal);10,從其他表中復(fù)制數(shù)據(jù)并寫入表SQL insert into managers(id,name,salary,hiredate)SQL select empno,ename,sal,hiredateSQL from empSQL where job = MANAGER;11,修改表中的記錄SQL update table set column = value ,column = value, whe

33、re condition;12,刪除表中的記錄SQL delete from table where condition;13,數(shù)據(jù)庫事務(wù)，事務(wù)是數(shù)據(jù)庫一組邏輯操作的集合一個事務(wù)可能是：多個DML語句 單個DDL語句 單個DCL語句14,事務(wù)控制使用 savepoint,rollback,commit 關(guān)鍵字SQL savepoint aaa;SQL rollback to aaa;SQL commit;15,查詢表中的數(shù)據(jù)select * from table_name;select column_list from table_name;16,Number and Date 可以用于算術(shù)運

34、算因為 Date 類型 其實存儲為 Number 類型17,用運算表達式產(chǎn)生新列SQL select ename,sal,sal+3000 from emp;SQL select ename,sal,12*sal+100 from emp;18,算術(shù)表達式中NULL值錯誤的處理因為任何數(shù)與NULL運算無意義，所以為避免錯誤，需要用其他值替換NULL值例如：SQL select ename 姓名,12*sal+comm 年薪 from emp where ename = KING;姓名 薪水- -KING因為comm（提成工資）列為NULL值，結(jié)果也出現(xiàn)了NULL值，所以需要用0來替換NULL注

35、意函數(shù)nvl的使用 NVL(原值,新值)SQL select ename 姓名,12*sal+NVL(comm,0) 年薪 from emp where ename = KING;員工姓名 員工薪水- -KING 6000019,使用友好的列名,有下面三種形式SQL select ename as 姓名, sal 月薪, sal*12 年薪 from emp20,過濾重復(fù)行，使用關(guān)鍵字 distinctSQL select distinct * from emp;21,SQL PLUS訪問ORACLE數(shù)據(jù)庫的原理SQL*Plus Buffer Server Query Result22,whe

36、re 子句中 字符型 是區(qū)分大小寫的，最好都轉(zhuǎn)成大寫因為在ORACLE庫中，字符會轉(zhuǎn)換成大寫來保存23,比較運算符：等于=,不等于有兩種或者!=24,復(fù)雜的比較運算符：between and in (value list)like （% 代表匹配至多個任意字符，_ 代表單個任意字符）null （與NULL進行比較時，需要使用 is null 或者 is not null）25,邏輯運算符，按優(yōu)先級從高到低排列Not , And , Or26,Order by 子句 中 （ asc 表示 升序，desc 表示降序）27,ORACLE 函數(shù)，分為單行函數(shù)：每條記錄返回一個結(jié)果值多行函數(shù)：多條記錄返

37、回一個結(jié)果值28,字符函數(shù)轉(zhuǎn)換函數(shù)LOWER：轉(zhuǎn)為小寫UPPER：轉(zhuǎn)為大寫INITCAP：將每個單詞的首字母大寫，其他字母小寫29,字符函數(shù)操縱函數(shù)（注意：ORACLE以UNICODE存儲字符）CONCAT:連接兩個字符串，與并置運算符“|”類似SUBSTR:substr(string,position,length) 從string中的position開始取length個字符LENGTH:返回字符串的長度INSTR: instr(string,value) 返回 value 在 string 的起始位置LPAD: lpad(string,number,value) 若string不夠numb

38、er位，從左起用vlaue字符串填充(不支持中文)30,四舍五入函數(shù) round(數(shù)值,小數(shù)位)SQL SELECT ROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1) FROM DUAL;ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)- - -45.92 46 5031,數(shù)值截取函數(shù) trunctSQL SELECT TRUNC(45.923,2),TRUNC(45.923,0),TRUNC(45.923,-1) FROM DUAL;TRUNC(45.923,2) TRUNC(45.923,0)

39、TRUNC(45.923,-1)- - -45.92 45 4032,求模函數(shù) MOD(a,b) 返回a被b整除后的余數(shù)33,Oracle內(nèi)部默認(rèn)的日期格式: DD-MON-YY （24-9月 -06）34,DUAL ：啞元系統(tǒng)表，是名義表，只能范圍唯一值35,Date類型的算術(shù)運算，以天為單位例如：部門編號為10的員工分別工作了多少年SQL select ename,(sysdate-hiredate)/365 as years from emp where deptno = 10;ENAME YEARS- -CLARK 25.3108341KING 24.8697382MILLER 24.686176636,日期函