SQL面試題大全.pdf

部分常見(jiàn)部分常見(jiàn) ORACLEORACLE 面試題以及面試題以及 SQLSQL 注意事項(xiàng)注意事項(xiàng) 一 表的創(chuàng)建 一 表的創(chuàng)建 一個(gè)通過(guò)單列外鍵聯(lián)系起父表和子表的簡(jiǎ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 建表時(shí)注意不要用關(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 類(lèi)型一致的不加鎖讀取 non locking read in SELECTs 這些特性均提高了多用戶(hù)并發(fā)操作的性能表現(xiàn) 在 InnoDB 表中不需要擴(kuò)大鎖定 lock escalation 因 為 InnoDB 的 列 鎖 定 row level locks 適 宜 非 常 小 的 空 間 InnoDB 是 MySQL 上第一個(gè)提供外鍵約束 FOREIGN KEY constraints 的表引擎 InnoDB 的設(shè)計(jì)目標(biāo)是處理大容量數(shù)據(jù)庫(kù)系統(tǒng) 它的 CPU 利用率是其它基于磁盤(pán)的關(guān)系數(shù)據(jù)庫(kù)引擎所不能比的 從一個(gè)表中查詢(xún)出數(shù)據(jù)插入到另一個(gè)表中的方法 從一個(gè)表中查詢(xún)出數(shù)據(jù)插入到另一個(gè)表中的方法 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 不 存 在 因 為 在 插 入 時(shí) 會(huì) 自 動(dòng) 創(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ún)練習(xí)題查詢(xún)練習(xí)題 1 1 表1 book 表 字段有 id 主鍵 name 書(shū)名 表 2 bookEnrol 表 圖書(shū)借出歸還登記 字段有 id bookId 外鍵 dependDate 變更時(shí)間 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 要求查詢(xún)結(jié)果應(yīng)為 被借出的書(shū)和被借出的日期 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 第二個(gè)表是用來(lái)登記的 不管你是借還是還 都要添加一條記錄 請(qǐng)寫(xiě)一個(gè)SQL 語(yǔ)句 獲取到現(xiàn)在狀態(tài)為已借出的所有圖書(shū)的相關(guān)信息 參考語(yǔ)句 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 子句 類(lèi)似的筆試題還有 表一 各種產(chǎn)品年銷(xiāo)售量統(tǒng)計(jì)表 sale 年 產(chǎn)品 銷(xiāo)量 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)品 銷(xiāo)量 2005 a 700 2006 b 500 2007 b 350 即 每年銷(xiāo)量最多的產(chǎn)品的相關(guān)信息 參考答案 Select from sale a where not exists select from sale where 年 a 年 and 銷(xiāo)量 a 銷(xiāo)量 or select from sale a inner join select 年 max 銷(xiāo)量 as 銷(xiāo)量 from sale group by 年 b on a 年 b 年 and a 銷(xiāo)量 b 銷(xiāo)量 3 3 查詢(xún)語(yǔ)句排名問(wèn)題 查詢(xún)語(yǔ)句排名問(wèn)題 名次 姓名 月積分 char 總積分 char 1 WhatIsJava 1 99 2 水王 76 981 3 新浪網(wǎng) 65 96 4 牛人 22 9 5 中國(guó)隊(duì) 64 89 6 北林信息 66 66 7 加太陽(yáng) 53 66 8 中成藥 11 33 9 西洋參 25 26 10 大拿 33 23 如果用總積分做降序排序 因?yàn)榭偡e分是字符型 所以排出來(lái)是這樣子 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 要 求 用 一 個(gè) SQL 語(yǔ) 句 注 意 是 一 個(gè) 的 處 所 有 人 不 區(qū) 分 人 員 每 個(gè) 月 及 上 月 和 下 月 的 總 收 入 要求列表輸出為 月份 當(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中 每 一 條 記 錄 都 有 一 個(gè)rowid rowid 在 整 個(gè) 數(shù) 據(jù) 庫(kù) 中 是 唯 一 的 rowid確 定 了 每 條 記 錄 是 在ORACLE中 的 哪 一 個(gè) 數(shù) 據(jù) 文 件 塊 行 上 2 在 重復(fù) 的 記錄 中 可 能 所有 列 的 內(nèi) 容 都相 同 但 rowid 不 會(huì)相 同 所 以 只要 確 定出 重 復(fù)記 錄 中 那些具有最大rowid的就可以了 其余全部刪除 實(shí)現(xiàn)方法 SQL create table a 2 bm char 4 編碼 3 mc varchar2 20 名稱(chēng) 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 查詢(xún)到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個(gè)記錄 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 用于為子查詢(xún)返回的每個(gè)行分配序列值 注意 組函數(shù)可以處理一組數(shù)據(jù) 返回一個(gè)值 組函數(shù)會(huì)忽略空值 where 后只能跟單行函數(shù) 不能有組函數(shù) 使用TOP N分析法 TOP N分析法基于條件顯示表中最上面N條記錄或最下面N條記錄 TOP N查詢(xún)包含以下內(nèi)容 1 一個(gè)用于排序數(shù)據(jù)的內(nèi)聯(lián)視圖 2 使用ORDER BY子句或DESC參數(shù)的子查詢(xún) 3 一個(gè)外層查詢(xún) 由它決定最終記錄中行的數(shù)目 這包括 ROWNUM 偽列和用于比較運(yùn)算符的 WHERE子句 語(yǔ)法 SELECT ROWNUM column list FROM SELECT column list FROM table name ORDER BY Top n column name WHERE ROWNUM N 例例1 1 查詢(xún)Employee表的頂部10條記錄 方法1 單表時(shí)可以用 select cEmployeeCode vFirstName vLastName from employee where rownum 10 方法2 較復(fù)雜的查詢(xún) 建議使用這種 select from select rownum as num cEmployeeCode vFirstName vLastName from employee where num select concat 010 88888888 轉(zhuǎn)23 高 乾 競(jìng) 電 話(huà) from dual 高乾競(jìng)電話(huà) 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開(kāi)始 取count個(gè) SQL select substr3 8 from dual SUBSTR 08888888 4日期函數(shù) 如 LAST DAY 返回本月日期的最后一天 具體參見(jiàn)oracle筆記 其他主要函數(shù) 其他主要函數(shù) TRUNC 按照指定的精度截取一個(gè)數(shù) SQRT 返回?cái)?shù)字 n的根 POWER n1 n2 返回 n1的n2次方 根 MOD n1 n2 返回一個(gè) n1除以 n2 的余數(shù) FLOOR 對(duì)給定的數(shù)字取整數(shù) REPLACE string s1 s2 string 希望被 替換的字符或變量 s1 被替換的字符串 s2 要替換的字符串 LOWER 返回字符串 并將所有的字符小寫(xiě) UPPER返 回字符串 并將所有的字符大寫(xiě) LENGTH 返回字符串的長(zhǎng)度 ORALCEORALCE 常識(shí) 及 常識(shí) 及 SQL SQL 基本語(yǔ)法基本語(yǔ)法 1 ORACLE安裝完成后的初始口令 internal oracle sys change on install system manager scott tiger scott是Oracle的 核 心 開(kāi) 發(fā) 人 員 之 一 tiger是 他 家 的 一 只 貓 的 名 字 sysman oem temp 例 conn scott tiger jspdev conn system manager jspdev as sysdba 2 IBM的Codd Edgar Frank Codd 博士提出 大型共享數(shù)據(jù)庫(kù)數(shù)據(jù)的關(guān)系模型 3 ORACLE 9i 中的 i internet 是因特網(wǎng)的意思 4 ORACLE的數(shù)據(jù)庫(kù)的物理結(jié)構(gòu) 數(shù)據(jù)文件 日志文件 控制文件 5 ORACLE的數(shù)據(jù)庫(kù)的邏輯結(jié)構(gòu) 表空間 表 段 區(qū)間 塊 表空間 類(lèi)似于 SQLSERVER 中數(shù)據(jù)庫(kù)的概念 6 SYSDATE 返回當(dāng)前系統(tǒng)日期 說(shuō)明 當(dāng)函數(shù)沒(méi)有參數(shù)時(shí)可以省略括號(hào) 7 在SQL PLUS中 執(zhí)行緩沖區(qū)中的SQL命令的方式 SQL run SQL r SQL 8 在SQL PLUS中 修改當(dāng)前會(huì)話(huà)的日期顯示格式 SQL alter session set nls date format YYYY MM DD 9 使用臨時(shí)變量 提高輸入效率 SQL insert into emp empno ename sal values 10 從其他表中復(fù)制數(shù)據(jù)并寫(xiě)入表 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ù)庫(kù)事務(wù) 事務(wù)是數(shù)據(jù)庫(kù)一組邏輯操作的集合 一個(gè)事務(wù)可能是 多個(gè)DML語(yǔ)句 單個(gè)DDL語(yǔ)句 單個(gè)DCL 語(yǔ)句 14 事務(wù)控制使用 savepoint rollback commit 關(guān)鍵字 SQL savepoint aaa SQL rollback to aaa SQL commit 15 查詢(xún)表中的數(shù)據(jù) select from table name select column list from table name 16 Number and Date 可以用于算術(shù)運(yùn)算 因?yàn)?Date 類(lèi)型 其實(shí)存儲(chǔ)為 Number 類(lèi)型 17 用運(yùn)算表達(dá)式產(chǎn)生新列 SQL select ename sal sal 3000 from emp SQL select ename sal 12 sal 100 from emp 18 算術(shù)表達(dá)式中NULL值錯(cuò)誤的處理 因 為 任 何 數(shù) 與 NULL 運(yùn) 算 無(wú) 意 義 所 以 為 避 免 錯(cuò) 誤 需 要 用 其 他 值 替 換NULL 值 例如 SQL select ename 姓名 12 sal comm 年薪 from emp where ename KING 姓名 薪水 KING 因 為 comm 提 成 工 資 列 為 NULL 值 結(jié) 果 也 出 現(xiàn) 了 NULL 值 所 以 需 要 用 0 來(lái) 替 換 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 過(guò)濾重復(fù)行 使用關(guān)鍵字 distinct SQL select distinct from emp 21 SQL PLUS訪問(wèn)ORACLE數(shù)據(jù)庫(kù)的原理 SQL Plus Buffer Server Query Result 22 where 子句中 字符型 是區(qū)分大小寫(xiě)的 最好都轉(zhuǎn)成大寫(xiě) 因?yàn)樵贠RACLE庫(kù)中 字符會(huì)轉(zhuǎn)換成大寫(xiě)來(lái)保存 23 比較運(yùn)算符 等于 不等于有兩種 或者 24 復(fù)雜的比較運(yùn)算符 between and in value list like 代表匹配至多個(gè)任意字符 代表單個(gè)任意字符 null 與 NULL 進(jìn)行比較時(shí) 需要使用 is null 或者 is not null 25 邏輯運(yùn)算符 按優(yōu)先級(jí)從高到低排列 Not And Or 26 Order by 子句 中 asc 表示 升序 desc 表示降序 27 ORACLE 函數(shù) 分為 單行函數(shù) 每條記錄返回一個(gè)結(jié)果值 多行函數(shù) 多條記錄返回一個(gè)結(jié)果值 28 字符函數(shù) 轉(zhuǎn)換函數(shù) LOWER 轉(zhuǎn)為小寫(xiě) UPPER 轉(zhuǎn)為大寫(xiě) INITCAP 將每個(gè)單詞的首字母大寫(xiě) 其他字母小寫(xiě) 29 字符函數(shù) 操縱函數(shù) 注意 ORACLE以UNICODE存儲(chǔ)字符 CONCAT 連接兩個(gè)字符串 與并置運(yùn)算符 類(lèi)似 SUBSTR substr string position length 從string中的position開(kāi)始取length個(gè)字符 LENGTH 返回字符串的長(zhǎng)度 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類(lèi)型的算術(shù)運(yùn)算 以天為單位 例如 部門(mén)編號(hào)為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 返回兩個(gè)日期之間相差多少個(gè)月 ADD MONTHS 在日期上加上月份數(shù) NEXT DAY 下一個(gè)日子 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ù)類(lèi)型轉(zhuǎn)換 Oracle 可隱式轉(zhuǎn)換的情況有 From To varchar2 or char number 當(dāng)字符串是數(shù)字字符時(shí) varchar2 or char date number varchar2 date varchar2 38 數(shù)據(jù)類(lèi)型轉(zhuǎn)換 Oracle 數(shù)據(jù)類(lèi)型轉(zhuǎn)換函數(shù) to char to number to date 39 日期格式模型字符 YYYY 代表完整的年份 YEAR 年份 MM 兩位數(shù)的月份 MONTH 月份的完整名稱(chēng) DY 每星期中天的三個(gè)字符縮寫(xiě) DAY 表示星期日 星期六 另外還有 D DD DDD 等 40 NVL value substitute value 是可能有null的列 substitute是缺省值 這個(gè)函數(shù)的作用就是當(dāng)出現(xiàn) null 值的時(shí)候 后缺省值替換 null 41 Coalesce exp name1 exp name2 exp n 42 Decode 函數(shù) Decode exp testvalue1 resultvalue1 testvalue2 resultvalue2 例如 根據(jù)國(guó)家名稱(chēng)顯示相應(yīng)的國(guó)家代碼 1 創(chuàng)建國(guó)家表 create table countrys vCountryName varchar2 50 2 寫(xiě)入幾行 分別為中國(guó) 日本 韓國(guó) insert into countrys values 3 用DECODE函數(shù) 進(jìn)行匹配和顯示 select vCountryName as 國(guó)家名稱(chēng) DECODE vCountryName 中國(guó) 086 日本 116 as 國(guó)家編號(hào) from countrys 國(guó)家名稱(chēng) 國(guó)家編號(hào) 中國(guó) 086 日本 116 韓國(guó) 結(jié)果 在DECODE中存在且成功匹配的值將會(huì)被顯示 否則顯示為 NULL S SQLQL 語(yǔ)句書(shū)可以提高執(zhí)行效率的語(yǔ)句書(shū)可以提高執(zhí)行效率的方法方法 1 1 操作符號(hào) 操作符號(hào) NOT INNOT IN操作符 操作符 此操作是強(qiáng)列推薦不使用的 因?yàn)樗荒軕?yīng)用表的索引 推薦方案 用NOT EXISTS 或 外連接 判斷為空 方案代替 IS NULL NOT NOT EXISTS NOT IN NOT LIKE LIKE 500 因?yàn)樗麄儾蛔咚饕潜頀?描 NOT IN會(huì)多次掃描表 使用 EXISTS NOT EXISTS IN LEFT OUTER JOIN 來(lái)替代 特別是左連接 而Exists 比IN 更快 最慢的是NOT操作 2 2 注意union和union all 的區(qū)別 union比union all 多做了一步 distinct 操作 能用 union all 的情況下盡量不用 union 如 兩個(gè)表A 和B 都有一個(gè)序號(hào)字段ID 要求兩個(gè)表中的 ID 字段最大的值 select max id as max id from select id from 表A union all select id from 表B t 3 查詢(xún)時(shí)盡量不要返回不需要的行 列 另外在多表連接查詢(xún)時(shí) 盡量改成連接查詢(xún) 少用子查詢(xún) 4 盡量少用視圖 它的效率低 對(duì)視圖操作比直接對(duì)表操作慢 可以用存儲(chǔ)過(guò)程來(lái)代替它 特別的是不要用視圖嵌套 嵌 套視圖增加了尋找原始資料的難度 我們看視圖的本質(zhì) 它是存放在服務(wù)器上的被優(yōu)化好了的已經(jīng)產(chǎn)生了查詢(xún)規(guī)劃的 SQL 對(duì)單個(gè)表檢索數(shù)據(jù)時(shí) 不要使 用指向多個(gè)表的視圖 直接從表檢索或者僅僅包含這個(gè)表的視圖上讀 否則增加了不必要的開(kāi)銷(xiāo) 查詢(xún)受到干擾 為了加快視圖的查詢(xún) MsSQL 增加了視圖索引的功能 5 創(chuàng)建合理的索引 對(duì)于插入或者修改比較頻繁的表 盡量慎用索引 因?yàn)槿绻碇写嬖谒饕?插入和修改時(shí)也會(huì)引 起全表掃描 索引一般使用于where后經(jīng)常用作條件的字段上 6 在表中定義字段或者存儲(chǔ)過(guò)程 函數(shù)中定義參數(shù)時(shí) 將參數(shù)的大小設(shè)置為合適即可 勿設(shè)置太大 這樣開(kāi)銷(xiāo)很大 7 Between 在某些時(shí)候比 IN 速度更快 Between 能夠更快地根據(jù)索引找到范圍 用查詢(xún)優(yōu)化器可見(jiàn)到差別 select from chineseresume where title in 男 女 Select from chineseresume where between 男 and 女 是一樣的 由于 in 會(huì)在比較多次 所以有時(shí)會(huì)慢些 8 在必要是對(duì)全局或者局部臨時(shí)表創(chuàng)建索引 有時(shí)能夠提高速度 但不是一定會(huì)這樣 因?yàn)樗饕埠馁M(fèi)大量的資源 他的創(chuàng)建同是實(shí)際表一樣 9 WHERE后面的條件順序影響 WHERE子 句 后 面 的 條 件 順 序 對(duì) 大 數(shù) 據(jù) 量 表 的 查 詢(xún) 會(huì) 產(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以下 以上兩個(gè) SQL 中 dy dj 電壓等級(jí) 及 xh bz 銷(xiāo)戶(hù)標(biāo)志 兩個(gè)字段都沒(méi)進(jìn)行索引 所以執(zhí)行的時(shí)候都是全表掃描 如果 dy dj 1KV 以下 條件在記錄集內(nèi)比率為 99 而 xh bz 1 的比率只為 0 5 在進(jìn)行第一條 SQL 的時(shí)候 99 條記錄 都進(jìn)行 dy dj 及 xh bz 的 比較 而在進(jìn)行第二條 SQL 的時(shí)