SQL面試題大全.pdf

部分常見部分常見 ORACLEORACLE 面試題以及面試題以及 SQLSQL 注意事項注意事項 一 表的創(chuàng)建 一 表的創(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 NULL PRIMARY KEY id TYPE INNODB InnoDB Tables 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 表中不需要擴大鎖定 lock escalation 因 為 InnoDB 的 列 鎖 定 row level locks 適 宜 非 常 小 的 空 間 InnoDB 是 MySQL 上第一個提供外鍵約束 FOREIGN KEY constraints 的表引擎 InnoDB 的設(shè)計目標(biāo)是處理大容量數(shù)據(jù)庫系統(tǒng) 它的 CPU 利用率是其它基于磁盤的關(guān)系數(shù)據(jù)庫引擎所不能比的 從一個表中查詢出數(shù)據(jù)插入到另一個表中的方法 從一個表中查詢出數(shù)據(jù)插入到另一個表中的方法 select into destTbl from srcTbl insert into destTbl fld1 fld2 select fld1 5 from srcTbl 以 上 兩 句 都 是 將 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 如果只想要結(jié)構(gòu)而不要數(shù)據(jù) create table s emp 42 as select from s emp where 1 2 永假式 SQLSQL 查詢練習(xí)題查詢練習(xí)題 1 1 表1 book 表 字段有 id 主鍵 name 書名 表 2 bookEnrol 表 圖書借出歸還登記 字段有 id bookId 外鍵 dependDate 變更時間 state 1 借 出 2 歸還 id name 1 English 2 Math 3 JAVA id bookId dependDate state 1 1 2009 01 02 1 2 1 2009 01 12 2 3 2 2009 01 14 1 4 1 2009 01 17 1 5 2 2009 02 14 2 6 2 2009 02 15 1 7 3 2009 02 18 1 8 3 2009 02 19 2 要求查詢結(jié)果應(yīng)為 被借出的書和被借出的日期 Id Name dependDate 1 English 2009 01 17 2 Math 2009 02 15 Select e bookId b name e dependDate from book b bookEnrol e where 第二個表是用來登記的 不管你是借還是還 都要添加一條記錄 請寫一個SQL 語句 獲取到現(xiàn)在狀態(tài)為已借出的所有圖書的相關(guān)信息 參考語句 select book id book name max dependDate from book inner join bookEnrol on book id bookEnrol bookid AND booker state 1 group by book id 2 2 第 1 題練習(xí)使用 group by having 子句 類似的筆試題還有 表一 各種產(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 from sale where 年 a 年 and 銷量 a 銷量 or select from sale a inner join select 年 max 銷量 as 銷量 from sale group by 年 b on a 年 b 年 and a 銷量 b 銷量 3 3 查詢語句排名問題 查詢語句排名問題 名次 姓名 月積分 char 總積分 char 1 WhatIsJava 1 99 2 水王 76 981 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 表表tb tb uid mark 1 7 1 6 2 3 2 2 2 5 3 4 3 3 4 8 4 1 4 3 想查出uid 4的名次 uid mc 4 3 select uid sum mark as total from tab name group by uid order by total desc 4 4 表A字段如下 month name income 月份 人員 收入 1 a 1000 2 a 2000 3 a 3000 要 求 用 一 個 SQL 語 句 注 意 是 一 個 的 處 所 有 人 不 區(qū) 分 人 員 每 個 月 及 上 月 和 下 月 的 總 收 入 要求列表輸出為 月份 當(dāng)月收入 上月收入 下月收入 2 2000 1000 3000 Select 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 Where To Number Month To Number Extract Month From Sysdate 1 下月收 入 From Dual 5 5 刪除重復(fù)記錄刪除重復(fù)記錄 方法原理 1 Oracle中 每 一 條 記 錄 都 有 一 個rowid rowid 在 整 個 數(shù) 據(jù) 庫 中 是 唯 一 的 rowid確 定 了 每 條 記 錄 是 在ORACLE中 的 哪 一 個 數(shù) 據(jù) 文 件 塊 行 上 2 在 重復(fù) 的 記錄 中 可 能 所有 列 的 內(nèi) 容 都相 同 但 rowid 不 會相 同 所 以 只要 確 定出 重 復(fù)記 錄 中 那些具有最大rowid的就可以了 其余全部刪除 實現(xiàn)方法 SQL create table a 2 bm char 4 編碼 3 mc varchar2 20 名稱 4 5 SQL select rowid bm mc from a ROWID BM MC 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 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個記錄 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ù)組合函數(shù) Group by 子句 Distinct 關(guān)鍵字 偽列ROWNUM 用于為子查詢返回的每個行分配序列值 注意 組函數(shù)可以處理一組數(shù)據(jù) 返回一個值 組函數(shù)會忽略空值 where 后只能跟單行函數(shù) 不能有組函數(shù) 使用TOP N分析法 TOP N分析法基于條件顯示表中最上面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 list FROM SELECT column list FROM table name ORDER BY Top n column name WHERE ROWNUM N 例例1 1 查詢Employee表的頂部10條記錄 方法1 單表時可以用 select cEmployeeCode vFirstName vLastName 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 高乾競電話 010 88888888轉(zhuǎn)23 2 2 LTRIMLTRIM和和RTRIMRTRIM LTRIM 刪除左邊出現(xiàn)的字符串 RTRIM 刪除右邊出現(xiàn)的字符串 SQL select ltrim rtrim gao qian jing from dual LTRIM RTRIM gao qian jing 3 3 SUBSTR string start count SUBSTR string start count 取子字符串 從start開始 取count個 SQL select substr3 8 from dual SUBSTR 08888888 4日期函數(shù) 如 LAST DAY 返回本月日期的最后一天 具體參見oracle筆記 其他主要函數(shù) 其他主要函數(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 要替換的字符串 LOWER 返回字符串 并將所有的字符小寫 UPPER返 回字符串 并將所有的字符大寫 LENGTH 返回字符串的長度 ORALCEORALCE 常識 及 常識 及 SQL SQL 基本語法基本語法 1 ORACLE安裝完成后的初始口令 internal oracle sys change on install system manager scott tiger scott是Oracle的 核 心 開 發(fā) 人 員 之 一 tiger是 他 家 的 一 只 貓 的 名 字 sysman oem temp 例 conn scott tiger jspdev conn system manager jspdev as sysdba 2 IBM的Codd Edgar Frank 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 run SQL r SQL 8 在SQL PLUS中 修改當(dāng)前會話的日期顯示格式 SQL alter session set nls date format YYYY MM DD 9 使用臨時變量 提高輸入效率 SQL insert into emp empno ename sal values 10 從其他表中復(fù)制數(shù)據(jù)并寫入表 SQL insert into managers id name salary hiredate SQL select empno ename sal hiredate SQL from emp SQL where job MANAGER 11 修改表中的記錄 SQL update table set column value column value where 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ù)運算 因為 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 注意函數(shù)nvl的使用 NVL 原值 新值 SQL select ename 姓名 12 sal NVL comm 0 年薪 from emp where ename KING 員工姓名 員工薪水 KING 60000 19 使用友好的列名 有下面三種形式 SQL select ename as 姓名 sal 月薪 sal 12 年薪 from emp 20 過濾重復(fù)行 使用關(guān)鍵字 distinct SQL select distinct from emp 21 SQL PLUS訪問ORACLE數(shù)據(jù)庫的原理 SQL Plus Buffer Server Query Result 22 where 子句中 字符型 是區(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 Or 26 Order by 子句 中 asc 表示 升序 desc 表示降序 27 ORACLE 函數(shù) 分為 單行函數(shù) 每條記錄返回一個結(jié)果值 多行函數(shù) 多條記錄返回一個結(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 不夠number位 從左起用 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 50 31 數(shù)值截取函數(shù) trunct SQL SELECT TRUNC 45 923 2 TRUNC 45 923 0 TRUNC 45 923 1 FROM DUAL TRUNC 45 923 2 TRUNC 45 923 0 TRUNC 45 923 1 45 92 45 40 32 求模函數(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 3108341 KING 24 8697382 MILLER 24 6861766 36 日期函數(shù) MONTHS BETWEEN 返回兩個日期之間相差多少個月 ADD MONTHS 在日期上加上月份數(shù) NEXT DAY 下一個日子 select next day sysdate 星期一 from dual LAST DAY 該月的最后一天 ROUND 四舍五入日期 round sysdate year 或者 round sysdate month TRUNC 截取日期 trunc sysdate year 或者 trunc sysdate month 37 數(shù)據(jù)類型轉(zhuǎn)換 Oracle 可隱式轉(zhuǎn)換的情況有 From To varchar2 or char number 當(dāng)字符串是數(shù)字字符時 varchar2 or char date number varchar2 date varchar2 38 數(shù)據(jù)類型轉(zhuǎn)換 Oracle 數(shù)據(jù)類型轉(zhuǎn)換函數(shù) to char to number to date 39 日期格式模型字符 YYYY 代表完整的年份 YEAR 年份 MM 兩位數(shù)的月份 MONTH 月份的完整名稱 DY 每星期中天的三個字符縮寫 DAY 表示星期日 星期六 另外還有 D DD DDD 等 40 NVL value substitute value 是可能有null的列 substitute是缺省值 這個函數(shù)的作用就是當(dāng)出現(xiàn) null 值的時候 后缺省值替換 null 41 Coalesce exp name1 exp name2 exp n 42 Decode 函數(shù) Decode exp testvalue1 resultvalue1 testvalue2 resultvalue2 例如 根據(jù)國家名稱顯示相應(yīng)的國家代碼 1 創(chuàng)建國家表 create table countrys vCountryName varchar2 50 2 寫入幾行 分別為中國 日本 韓國 insert into countrys values 3 用DECODE函數(shù) 進行匹配和顯示 select vCountryName as 國家名稱 DECODE vCountryName 中國 086 日本 116 as 國家編號 from countrys 國家名稱 國家編號 中國 086 日本 116 韓國 結(jié)果 在DECODE中存在且成功匹配的值將會被顯示 否則顯示為 NULL S SQLQL 語句書可以提高執(zhí)行效率的語句書可以提高執(zhí)行效率的方法方法 1 1 操作符號 操作符號 NOT INNOT IN操作符 操作符 此操作是強列推薦不使用的 因為它不能應(yīng)用表的索引 推薦方案 用NOT EXISTS 或 外連接 判斷為空 方案代替 IS NULL NOT NOT EXISTS NOT IN NOT LIKE LIKE 500 因為他們不走索引全是表掃 描 NOT IN會多次掃描表 使用 EXISTS NOT EXISTS IN LEFT OUTER JOIN 來替代 特別是左連接 而Exists 比IN 更快 最慢的是NOT操作 2 2 注意union和union all 的區(qū)別 union比union all 多做了一步 distinct 操作 能用 union all 的情況下盡量不用 union 如 兩個表A 和B 都有一個序號字段ID 要求兩個表中的 ID 字段最大的值 select max id as max id from select id from 表A union all select id from 表B t 3 查詢時盡量不要返回不需要的行 列 另外在多表連接查詢時 盡量改成連接查詢 少用子查詢 4 盡量少用視圖 它的效率低 對視圖操作比直接對表操作慢 可以用存儲過程來代替它 特別的是不要用視圖嵌套 嵌 套視圖增加了尋找原始資料的難度 我們看視圖的本質(zhì) 它是存放在服務(wù)器上的被優(yōu)化好了的已經(jīng)產(chǎn)生了查詢規(guī)劃的 SQL 對單個表檢索數(shù)據(jù)時 不要使 用指向多個表的視圖 直接從表檢索或者僅僅包含這個表的視圖上讀 否則增加了不必要的開銷 查詢受到干擾 為了加快視圖的查詢 MsSQL 增加了視圖索引的功能 5 創(chuàng)建合理的索引 對于插入或者修改比較頻繁的表 盡量慎用索引 因為如果表中存在索引 插入和修改時也會引 起全表掃描 索引一般使用于where后經(jīng)常用作條件的字段上 6 在表中定義字段或者存儲過程 函數(shù)中定義參數(shù)時 將參數(shù)的大小設(shè)置為合適即可 勿設(shè)置太大 這樣開銷很大 7 Between 在某些時候比 IN 速度更快 Between 能夠更快地根據(jù)索引找到范圍 用查詢優(yōu)化器可見到差別 select from chineseresume where title in 男 女 Select from chineseresume where between 男 and 女 是一樣的 由于 in 會在比較多次 所以有時會慢些 8 在必要是對全局或者局部臨時表創(chuàng)建索引 有時能夠提高速度 但不是一定會這樣 因為索引也耗費大量的資源 他的創(chuàng)建同是實際表一樣 9 WHERE后面的條件順序影響 WHERE子 句 后 面 的 條 件 順 序 對 大 數(shù) 據(jù) 量 表 的 查 詢 會 產(chǎn) 生 直 接 的 影 響 如 Select from zl yhjbqk where dy dj 1KV以下 and xh bz 1 Select from zl yhjbqk where xh bz 1 and dy dj 1KV以下 以上兩個 SQL 中 dy dj 電壓等級 及 xh bz 銷戶標(biāo)志 兩個字段都沒進行索引 所以執(zhí)行的時候都是全表掃描 如果 dy dj 1KV 以下 條件在記錄集內(nèi)比率為 99 而 xh bz 1 的比率只為 0 5 在進行第一條 SQL 的時候 99 條記錄 都進行 dy dj 及 xh bz 的 比較 而在進行第二條 SQL 的時