java就業(yè)數(shù)據(jù)庫面試題

1、1. sql2000和sql2005有什么區(qū)別？a、 最直觀的一點就是兩者的界面有很明顯的不同。sql2005 的操作風格更接近于 vs2005。b、sql2005 比 sql2000 存儲的數(shù)據(jù)量要大很多， sql2005 支持數(shù)據(jù)庫邏輯分區(qū)；c、sql2000 與 sql20005 jdbc 連接不同：sql2000 jdbc 連接： url 地址： jdbc:microsoft:sqlserver:/:;driver ： com.microsoft.jdbc.sqlserver.SQLServerDriver;sql2005 jdbc 連接： url 地址： jdbc:sqlserver

2、:/:;databaseName=; driver ： com.microsoft.sqlserver.jdbc.SQLServerDriver;d、安全性能的提高： sql Server 2005 包括了一些在安全性能上的改進， 例如將用戶和對象分開、 數(shù)據(jù) 庫加密、設(shè)置安全默認值、增強密碼政策、縝密的許可控制、以及一個增強型的安全模式。e、sql2005 還提供了一些新的特性， 比如文件流支持 （向第三代數(shù)據(jù)庫邁出了可喜的一步 ） 、T-Sql 的改進（處理錯誤、 遞歸查詢功能、 關(guān)系運算符 PIVOT, APPLY , ROW_NUMBER 和其他數(shù)據(jù)列排行功能， 等等 ）、數(shù)據(jù)庫鏡像、

3、透明的客戶端重定向、新的基于架構(gòu)的安全模型、內(nèi)建的 Http 服務(wù)1. 存儲過程的作用？ 存儲過程可以使得對數(shù)據(jù)庫的管理、以及顯示關(guān)于數(shù)據(jù)庫及其用戶信息的工作容易得多。存儲過程是SQL 語句和可選控制流語句的預(yù)編譯集合， 以一個名稱存儲并作為一個單元處理。存儲過程存儲在數(shù)據(jù)庫內(nèi)，可由應(yīng)用程序通過一個調(diào)用執(zhí)行， 而且允許用戶聲明變量、有條件執(zhí)行以及其它強大的編程功能。存儲過程可包含程序流、邏輯以及對數(shù)據(jù)庫的查詢。它們可以接受參數(shù)、輸出參數(shù)、返回單個或多個 結(jié)果集以及返回值?？梢猿鲇谌魏问褂?SQL 語句的目的來使用存儲過程，它具有以下優(yōu)點： 可以在單個存儲過程中執(zhí)行一系列 SQL 語句。 可以從

4、自己的存儲過程內(nèi)引用其它存儲過程，這可以簡化一系列復(fù)雜語句。 存儲過程在創(chuàng)建時即在服務(wù)器上進行編譯，所以執(zhí)行起來比單個 SQL 語句快。2.sqlserver和oracle數(shù)據(jù)庫的性能優(yōu)化3.1 選用適合的 ORACLE 優(yōu)化器ORACLE 的優(yōu)化器共有 3 種:a. RULE （基于規(guī)則 ） b. COST （基于成本 ） c. CHOOSE （選擇性）在缺省情況下 ,ORACLE 采用 CHOOSE 優(yōu)化器 , 為了避免那些不必要的全表掃描 （full table scan） , 你必須盡量避免使用 CHOOSE 優(yōu)化器 ,而直接采用基于規(guī)則或者基于成本的優(yōu)化器.訪問 Table 的方式3

5、.2ORACLE 采用兩種訪問表中記錄的方式 :a. 全表掃描 全表掃描就是順序地訪問表中每條記錄 . ORACLE 采用一次讀入多個數(shù)據(jù)塊 （database block） 的 方式優(yōu)化全表掃描 .b. 通過 ROWID 訪問表你可以采用基于 ROWID 的訪問方式情況 ,提高訪問表的效率 ,RowId 包含了表中記錄的物理位置 信息 .ORACLE 采用索引 （INDEX） 實現(xiàn)了數(shù)據(jù)和存放數(shù)據(jù)的物理位置 （RowId） 之間的聯(lián)系。 通常索引提供了快速訪問 ROWID 的方法， 因此那些基于索引列的查詢就可以得到性能上的提高 .3.3. 選擇最有效率的表名順序 (只在基于規(guī)則的優(yōu)化器中有

6、效)3.4. WHERE 子句中的連接順序ORACLE 采用自下而上的順序解析 WHERE 子句 , 根據(jù)這個原理 , 表之間的連接必須寫在其他 WHERE 條件之前 , 那些可以過濾掉最大數(shù)量記錄的條件必須寫在 WHERE 子句的末尾 .3.5. SELECT 子句中避免使用 * 當你想在 SELECT 子句中列出所有的 COLUMN 時,使用動態(tài) SQL 列引用 * 是一個方便的方法 . 不幸的是 ,這是一個非常低效的方法.實際上ORACLE在解析的過程中，會將* 依次轉(zhuǎn)換成所有的列名，這個工作是通 過查詢數(shù)據(jù)字典完成的 , 這意味著將耗費更多的時間 .3.6. 減少訪問數(shù)據(jù)庫的次數(shù)當執(zhí)行

7、每條 SQL 語句時 ， ORACLE 在內(nèi)部執(zhí)行了許多工作 : 解析 SQL 語句 ， 估算索引的利用率 ， 綁 定變量 ， 讀數(shù)據(jù)塊等等 . 由此可見， 減少訪問數(shù)據(jù)庫的次數(shù)， 就能實際上減少 ORACLE 的工作量 .3.7. 整合簡單 ，無關(guān)聯(lián)的數(shù)據(jù)庫訪問如果你有幾個簡單的數(shù)據(jù)庫查詢語句，你可以把它們整合到一個查詢中(即使它們之間沒有關(guān)系 )3.8. 使用表的別名 (Alias)當在SQL語句中連接多個表時，請使用表的別名并把別名前綴于每個Column上這樣一來，就可以減少解析的時間并減少那些由 Column 歧義引起的語法錯誤 .3.9. 對經(jīng)常要查詢的字段可以建立一個視圖3.10.

8、 在表中的某個字段上建立索引，可以提高檢索速度。3. 模糊查詢可以用索引嗎？(1) 如果模糊查詢是下邊這個樣子:where a like *%， 就可以用索引；如果模糊查詢是 where a like %* 的形式 ，就不能使用索引 !(2) 建全文索引后使用 constain4. sql 的關(guān)鍵字及其一些函數(shù)sql 關(guān)鍵字： all， alter， as， asc， container， create， delete， desc， drop， first， from， in， index， insert， into， join， last， on， schema， select， selec

9、tschema， selectsecurity， table， to， update， updateidentity， updateowner， updatesecurity， user， with;函數(shù)： Avg()， Count()， First()， Last()， Max()， Min()， Sum()，StDev()StDevP()StDev，StDevP 返回總體或總體樣本的標準偏差的估計值 ， Var()， VarP()Var，VarP 返回一個總體或總體樣本的方差的估計值ODBC 標量函數(shù)：1. 字 符 串 函 數(shù) ASCIILENGTH RTRIMCHARLOCATESPAC

10、ECONCATLTRIMSUBSTRINGLCASE RIGHT UCASELEFT2.數(shù)字函數(shù)ABS FLOOR SIN ATANLOGSQRTCEILINGPOWERTANCOSRANDMODEXP SIGN3.時間和日期 函數(shù)CURDATEDAYOFYEARMONTHCURTIMEYEARWEEKNOWHOUR QUARTER DAYOFMONTHMINUTEMONTHNAMEDAYOFWEEKSECOND DAYNAME4.數(shù)據(jù)類型變換CONVERTORACLE5. Oracle中的事務(wù)Oracle在缺省情況下任何一個 DML語句都會開始一個事務(wù)， 直到用戶發(fā)出Commit或Rollb

11、ack操作， 這個事務(wù)才會結(jié)束。在 Oracle中，執(zhí)行 DDL語句(如Create Table、Create View等)時，會在執(zhí)行之前自動 發(fā)出一個Commit命令，并在隨后發(fā)出一個 Commit或者Rollback命令。(1) 提交事務(wù)提交一事務(wù)，即將在事務(wù)中由 SQL語句所執(zhí)行的改變永久化。在提交前，ORACLE已有下列情況：在SGA的回滾段緩沖區(qū)已生成回滾段記錄，回滾信息包含有所修改值的老值。在SGA的日志緩沖區(qū)已生成日志項。這些改變在事務(wù)提交前可進入磁盤。對SGA的數(shù)據(jù)庫緩沖區(qū)已作修改，這些修改在事務(wù)真正提交之前可進入磁盤。在事務(wù)提交之后，有下列情況：對于與回滾段相關(guān)的內(nèi)部事務(wù)表

12、記錄提交事務(wù)，并賦給一個相應(yīng)的唯一系統(tǒng)修改號(SCN)，記錄在表中。在SGA的日志緩沖區(qū)中日志項由 LGWR進程寫入到在線日志文件，這是構(gòu)成提交事務(wù)的原子事務(wù)。在行上和表上的封鎖被釋放。該事務(wù)標志為完成 。注意：對于提交事務(wù)的數(shù)據(jù)修改不必由DBWR后臺進程立即寫入數(shù)據(jù)文件，可繼續(xù)存儲在SGA的數(shù)據(jù)庫緩沖區(qū)中，在最有效時將其寫入數(shù)據(jù)文件。(2) 回滾事務(wù)回滾事務(wù)的含義是撤消未提交事務(wù)中的SQL語句所作的對數(shù)據(jù)修改。ORALCE允許撤消未提交的整個事務(wù)，也允許撤消部分。在回滾整個事務(wù)(沒有引用保留點)時，有下列情況：在事務(wù)中所有SQL語句作的全部修改，利用相應(yīng)的回滾段被撤消。所有數(shù)據(jù)的事務(wù)封鎖被釋

13、放。事務(wù)結(jié)束。當事務(wù)回滾到一保留點(具有 SAVEPOINT )時，有下列情況：僅在該保留點之后執(zhí)行的語句被撤消。該指定的保留點仍然被保留，該保留點之后所建立的保留點被刪除。自該保留點之后所獲取的全部表封鎖和行封鎖被釋放，但指定的保留點以前所獲取的全部數(shù)據(jù)封鎖繼續(xù)保持。該事務(wù)仍可繼續(xù)。(3) 保留點保留點(savepoint)是在一事務(wù)范圍內(nèi)的中間標志，經(jīng)常用于將一個長的事務(wù)劃分為小的部分。保留 點可標志長事務(wù)中的任何點，允許可回滾該點之后的工作。在應(yīng)用程序中經(jīng)常使用保留點；例如一過程包 含幾個函數(shù)，在每個函數(shù)前可建立一個保留點，如果函數(shù)失敗，很容易返回到每一個函數(shù)開始的情況。在 回滾到一個保

14、留點之后，該保持點之后所獲得的數(shù)據(jù)封鎖被釋放。6. 視圖在什么情況下可以對其，刪除，修改操作：DML操作應(yīng)遵循的原則：1) .簡單視圖可以執(zhí)行 DML 操作；2) .在視圖包含 GROUP 函數(shù)， GROUP BY 子句， DISTINCT 關(guān)鍵字時不能刪除數(shù)據(jù)行；3) .在視圖不出現(xiàn)下列情況時可通過視圖修改基表數(shù)據(jù)或插入數(shù)據(jù)：a. 視圖中包含 GROUP函數(shù)，GROUP BY子句，DISTINCT關(guān)鍵字；b. 使用表達式定義的列；c. ROWNUM 偽列。d. 基表中未在視圖中選擇的其他列定義為非空且無默認值。WITH CHECK OPTION 子句限定：通過視圖執(zhí)行的 INSERTS 和

15、UPDATES 操作不能創(chuàng)建該視圖檢索不到的數(shù)據(jù)行， 因為它會對插入或修改的數(shù)據(jù)行執(zhí)行完整性約束和數(shù)據(jù)有效性檢查。視圖的刪除：DROP VIEW VIEW_NAME 語句刪除視圖。刪除視圖的定義不影響基表中的數(shù)據(jù)。只有視圖所有者和具備 DROP VIEW 權(quán)限的用戶可以刪除視圖。 視圖被刪除后，基于被刪除視圖的其他視圖或應(yīng)用將無效。7. oracle 中各種連接的寫法 :內(nèi)連接，外連接 ，左外連接，右外連接 以上幾種連接一般用于多表之間復(fù)雜的查詢， 下面先說說內(nèi)連接，也即相等連接，如右兩張表 dept ， employee ， dept 里有字段 deptid ， deptname ， emp

16、loyee 有字段 empno ， deptid ， ename ， sal ， age 等 那連接查詢語句如下： select A.* ,B.* from dept A,employee B where A.deptid=B.deptid 一般出現(xiàn)這種情況的是一張是主表，另一張是子表，主表的主鍵作為子表的外鍵，顯示結(jié)果按照子表的顯 示， 內(nèi)連接簡單，這里就不多說了 外連接在 oralce 里用()表示，先看例子 select empno,ename,sal,emp.deptno,dept.deptnofrom emp,dept where emp.deptno(+)=dept.deptno

17、這是一個右連接 看下面的例子selectempno,ename,sal,emp.deptno,dept.deptnofromemp,deptwheredept.no=emp.deptno(+) 這是一個左連接兩個查詢顯示的查詢結(jié)果是一樣的，剛開始學 oracle 的時候很多人都很難明白這個，現(xiàn)在有個笨辦法， 大家不防記下來， ()出現(xiàn)查詢條件的左邊即右連接，出現(xiàn)在右邊即左連接。 ()可以放在左邊也可以 放在右邊，但是一定要放在缺少條件的那一邊，如上面的例子中，如果公司最近準備成立一個新的部門， 測試部門，但現(xiàn)在還沒有招新員工，而現(xiàn)在 Boss 想知道公司有知道公司的員工在那個部門并且想知道 沒

18、有員工的部門，我們可以用上面的查詢語句實現(xiàn)。左外連接 (left outer join) 和右外連接 (reght outer join) 如同上面的外連接，查詢的結(jié)果是一樣的。 左外連接 :select empno,ename,sal,emp.deptno,dept.deptno from dept left outer join emp on (emp.deptno=dept.deptno)右外連接 :select empno,ename,sal,emp.deptno,dept.deptno from emp reight outer join dept on (emp.deptno=de

19、pt.deptno) 以上顯示的結(jié)果和外連接查詢出來的結(jié)果是一樣的 ，只是寫法不一樣而已， 實際工作中外連接用的比較多， 沒有必要都要去用它，但是概念大家一定要了解。全外關(guān)聯(lián) ：FULL OUTER JOINSELECT e.last_name, e.department_id, d.department_nameFROM employees eFULL OUTER JOIN departments dON (e.department_id = d.department_id);結(jié)果為：所有員工及對應(yīng)部門的記錄，包括沒有對應(yīng)部門編號 department_id 的員工記錄和沒有任何員 工的部門

20、記錄。8. 查詢的優(yōu)化1) .根據(jù)查詢條件建立合適的index2) .因為 SQL 是從右向左解析，多表查詢時，記錄數(shù)少的表放在右邊3) .多個條件時，收斂快的條件放在右邊。4) .避免使用復(fù)雜的集合函數(shù)，象not in 等。5) .避免在條件中對字段進行函數(shù)操作6) .盡量避免使用 select * ，應(yīng)該寫出需要查詢的字段7) .在java中盡量使用preparestatement執(zhí)行sql,從而共享執(zhí)行計劃9. 怎么看執(zhí)行計劃使用explain或者autotrace查看執(zhí)行計劃10.如何強制一個 SQL 語句使用索引 ? 使用 index hints11.Oracle 中的 PL/SQL

21、 塊是否熟悉 ?Oracle 中的內(nèi)置函數(shù)是否熟悉 ? 內(nèi)置函數(shù)： ASCII , CHR, CONCAT, decode,to_date,to_char,to_number 等等。12. 如何取一個沒有排序的查詢結(jié)果中的前 10條記錄？select fielda from tablea where rownum=10;13. 問：如何取一個有排序的查詢結(jié)果中的前 10 條記錄？select fielda from (select fielda from tablea order by fieldb ) where rownum=10; 錯誤寫法： select fielda from tab

22、lea where rownum=10 order by fieldb;14. 如何取一個沒有排序的查詢結(jié)果中的第 11 條到第 20 條記錄？select fielda from(select fielda,rownum rn from tablea where rownum=11;15. 如何取一個有排序的查詢結(jié)果中的第11條到第 20 條記錄？select fielda from (select fielda ,rownum rn from (select fielda from tablea order by fieldb )where rownum=11;16. 數(shù)據(jù)庫設(shè)計中,主鍵的

23、作用？主鍵的作用保持數(shù)據(jù)表記錄的唯一性,建立主鍵索引和作為外鍵使用.使用主鍵與外鍵的配對來表示實體間的連接 .17. Oracle 數(shù)據(jù)庫設(shè)計中,建立外鍵的方法(1)用Create table命令語句在設(shè)計數(shù)據(jù)庫表時建立外鍵Create table Depart (DepNO Number(2),DepName varchar2(10),CONSTRAINT pk_dept PRIMARY KEY (DepNO);Create table Emp (EmpNo Number(4),EmpName varchar2(20),DepNo CONSTRAINT fk_deptno Reference

24、s Depart(DepNo);( 2)用 Alter table 命令語句，為一個存在的表添加外鍵Create table Depart (DepNO Number(2),DepName varchar2(10),CONSTRAINT pk_dept PRIMARY KEY (DepNO);Create table Emp(EmpNo Number(4),EmpName varchar2(20),DepNo Number(2);Alter table EmpADD CONSTRAINT fk_deptno Foreign key(DepNo) References Depart(DepNo)

25、;18. oracle 中的常用函數(shù)，如：日期函數(shù)1. 日期函數(shù)ADD_MONTHS ：返回給指定的日期加上指定的月數(shù)后的日期。其格式為 add_months(d,n) , 其中 d 是日期,n表示月數(shù)。MONTHS_BETWEEN ：返回兩個日期之間的月數(shù)。其格式為： months_between(d1,d2) , d1 晚于 d2， 結(jié)果返回正數(shù) ,否則返回負數(shù)。LAST_DAY ：返回指定日期當月的最后一天的日期值。格式為： last_day(d) 。ROUND ：返回日期值，此日期四舍五入為格式模型指定的單位。格式為： round(d,fmt) 。 NEXT_DAY ：返回指定的下一個

26、星期幾的日期，格式為：next_day(d,day)。TRUNC ：將指定日期截斷為由格式模型指定的單位的日期， 與 round 函數(shù)不同的是它只舍不入。 格 式為：trunc(d,fmt),如果不指定fmt，日期則被截斷為天。EXTRACT ：提取日期時間類型中的特定部分。 格式為： extract(fmt from d) 。注意：此處的格式不 用使用單引號。2. 字符函數(shù)INITCAP(char) ：將首字母轉(zhuǎn)換為大寫。LOWER(char) ： 轉(zhuǎn)換為小寫UPPER(char) ： 轉(zhuǎn)換為大寫LTRIM(char,set) ：左剪裁RTRIM(char,set) ：右剪裁TRANSLAT

27、E(char,from) ：按字符翻譯REPLACE(char,search_str,replace_str)： 字符串替換 INSTR(char,substr,pos) ：查找子串位置 SUBSTR(char,pos,len) ： 取子字符串CONCA T(char1,char2) ： 連接字符串CHR ：根據(jù)提供的 ASCII 碼返回對應(yīng)的字符LPAD 和 RPAD: 左填充和右填充LENGTH ：返回字符串的長度translate函數(shù)不同的是decode函數(shù)進行逐個值的替換。SIGN(n) : 取符號FLOOR(n) : 向下取整FOWER(m,n): m 的 n 次冪MOD(m,n):

28、 取余數(shù)TRUNC(m,n): 截斷DECODE ：與執(zhí)行逐個字符替換的3. 數(shù)字函數(shù)ABS(n) : 取絕對值CEIL(n) : 向上取整SIN(n) : 正弦COS(n) : 余弦ROUND(m,n): 四舍五入SQRT(n): 平方根4. 轉(zhuǎn)換函數(shù)TO_CHARTO_DATETO_NUMBER5. 其他函數(shù)NVL(expre1,expre2) ： 將空值替換為指定的值NVL2(expre1,expre2,expre3) : exprel 不是空值，則 NVL2 返回 expre2，否則返回 expre3NULLIF(expre1,expre2) ： 比較兩個表達式 ,如果相等 ,返回空值

29、 ,否則返回 expre16. 分組函數(shù)AVGMIN :參數(shù)列中所有值的最小值MAXSUMCOUNT7. 分析函數(shù)ROW_NUMBER ， RANK ， DENSE_RANK數(shù)據(jù)庫 簡單 SQL 語句總結(jié)1. 在查詢結(jié)果中顯示列名:a. 用 as 關(guān)鍵字： select name as 姓名from students order by ageb. 直接表示： select name 姓名from students order by age2. 精確查找 :a. 用 in 限定范圍：select * from students where native in ( 湖南,四川)b. between

30、.and ： select * from students where age between 20 and 30c. “=：”select * from students where name = 李山d. like:select * from students where namelike 李% (注意查詢條件中有 “ %，”則說明是部分匹配，而且還有先后信息在里面， 即查找以 “李”開頭的匹配項。 所以若查詢有 “李”的所有對象， 應(yīng)該命令： % 李%;若是第二個字為李，則應(yīng)為 _李%或_李或_李_。)e .匹配檢查符： select * from courses where cno l

31、ike AC% ( 表示或的關(guān)系，與 in(.) 類似，而且 可以表示范圍，如：select * from courses where cno like A-C%)3. 對于時間類型變量的處理a.smalldatetime ：直接按照字符串處理的方式進行處理，例如：select * from students where birth = 1980-1-1 and birth = 1980-12-314. 集合函數(shù)a. count() 求和，如： select count(*) from students ( 求學生總?cè)藬?shù) )b. avg( 列) 求平均， 如： select avg(mark)

32、 from grades where cno=?B2?c. max(列)和 min(列),求最大與最小5. 分組 group常用于統(tǒng)計時，如分組查總數(shù)： select gender,count(sno) from students group by gender(查看男女學生各有多少 ) 注意：從哪種角度分組就從哪列 group by 對于多重分組，只需將分組規(guī)則羅列。比如查詢各屆各專業(yè)的男女同學人數(shù)，那么分組規(guī)則有：屆別 (grade) 、專業(yè) (mno) 和性別 (gender) ，所以有 group by grade, mno, genderselect grade, mno, gend

33、er, count(*) from students group by grade, mno, gender通常 group 還和 having 聯(lián)用，比如查詢 1 門課以上不及格的學生，則按學號 (sno) 分類有：select sno,count(*) from grades where mark16. UNION 聯(lián)合合并查詢結(jié)果，如： SELECT * FROM studentsWHERE name like , 張%? UNION ALL SELECT * FROM students WHERE name like , 李%?7. 多表查詢a. 內(nèi)連接select g.sno,s.n

34、ame,c.coursename from grades g JOIN students s ON g.sno=s.snoJOINcourses c ON o=o( 注意可以引用別名 )b. 外連接b1. 左連接select o,max(coursename),count(sno)from courses LEFT JOIN grades ONo=o group by o 左連接特點：顯示全部左邊表中的所有項目，即使其中有些項中的數(shù)據(jù)未填寫完全。左外連接返回那 些存在于左表而右表中卻沒有的行，再加上內(nèi)連接的行。b2. 右連接與左連接類似b3. 全連接select sno,name,major from students FULL JOIN majors ON students.mno=majors.mno 兩邊表中的內(nèi)容全部顯示c. 自身連接select o,c1.coursename,c1.pno,c2.coursename from courses c1,courses c2 where c1.pno=o 采用別名解決問題。d. 交叉連接select lastname+firstname from lastname