數(shù)據(jù)庫常見面試題(開發(fā)者篇)

kmW5sSj.png

這里寫圖片描述

什么是存儲過程专普？有哪些優(yōu)缺點(diǎn)悯衬？

什么是存儲過程？有哪些優(yōu)缺點(diǎn)檀夹？

存儲過程就像我們編程語言中的函數(shù)一樣筋粗，封裝了我們的代碼(PLSQL、T-SQL)炸渡。

存儲過程的優(yōu)點(diǎn)：

• 能夠?qū)⒋a封裝起來
• 保存在數(shù)據(jù)庫之中
• 讓編程語言進(jìn)行調(diào)用
• 存儲過程是一個(gè)預(yù)編譯的代碼塊娜亿，執(zhí)行效率比較高
• 一個(gè)存儲過程替代大量T_SQL語句 ，可以降低網(wǎng)絡(luò)通信量蚌堵，提高通信速率

存儲過程的缺點(diǎn)：

• 每個(gè)數(shù)據(jù)庫的存儲過程語法幾乎都不一樣买决，十分難以維護(hù)（不通用）
• 業(yè)務(wù)邏輯放在數(shù)據(jù)庫上，難以迭代

三個(gè)范式是什么

三個(gè)范式是什么

第一范式（1NF）：數(shù)據(jù)庫表中的字段都是單一屬性的吼畏，不可再分策州。這個(gè)單一屬性由基本類型構(gòu)成，包括整型宫仗、實(shí)數(shù)、字符型旁仿、邏輯型藕夫、日期型等。
第二范式（2NF）：數(shù)據(jù)庫表中不存在非關(guān)鍵字段對任一候選關(guān)鍵字段的部分函數(shù)依賴（部分函數(shù)依賴指的是存在組合關(guān)鍵字中的某些字段決定非關(guān)鍵字段的情況）枯冈，也即所有非關(guān)鍵字段都完全依賴于任意一組候選關(guān)鍵字毅贮。
第三范式（3NF）：在第二范式的基礎(chǔ)上，數(shù)據(jù)表中如果不存在非關(guān)鍵字段對任一候選關(guān)鍵字段的傳遞函數(shù)依賴則符合第三范式尘奏。所謂傳遞函數(shù)依賴滩褥，指的是如果存在"A → B → C"的決定關(guān)系，則C傳遞函數(shù)依賴于A炫加。因此瑰煎，滿足第三范式的數(shù)據(jù)庫表應(yīng)該不存在如下依賴關(guān)系： 關(guān)鍵字段 → 非關(guān)鍵字段x → 非關(guān)鍵字段y

上面的文字我們肯定是看不懂的铺然，也不愿意看下去的。接下來我就總結(jié)一下：

• 首先要明確的是：滿足著第三范式酒甸，那么就一定滿足第二范式魄健、滿足著第二范式就一定滿足第一范式
• 第一范式：字段是最小的的單元不可再分
  • 學(xué)生信息組成學(xué)生信息表，有年齡插勤、性別沽瘦、學(xué)號等信息組成。這些字段都不可再分农尖，所以它是滿足第一范式的
• 第二范式：滿足第一范式,表中的字段必須完全依賴于全部主鍵而非部分主鍵析恋。
  • 其他字段組成的這行記錄和主鍵表示的是同一個(gè)東西，而主鍵是唯一的盛卡，它們只需要依賴于主鍵助隧，也就成了唯一的
  • 學(xué)號為1024的同學(xué)，姓名為Java3y窟扑，年齡是22歲喇颁。姓名和年齡字段都依賴著學(xué)號主鍵。
• 第三范式：滿足第二范式嚎货，非主鍵外的所有字段必須互不依賴
  • 就是數(shù)據(jù)只在一個(gè)地方存儲橘霎，不重復(fù)出現(xiàn)在多張表中，可以認(rèn)為就是消除傳遞依賴
  • 比如殖属，我們大學(xué)分了很多系（中文系姐叁、英語系、計(jì)算機(jī)系……）洗显，這個(gè)系別管理表信息有以下字段組成：系編號外潜，系主任，系簡介挠唆，系架構(gòu)处窥。那我們能不能在學(xué)生信息表添加系編號，系主任玄组，系簡介滔驾，系架構(gòu)字段呢？不行的俄讹，因?yàn)檫@樣就冗余了哆致，非主鍵外的字段形成了依賴關(guān)系(依賴到學(xué)生信息表了)！正確的做法是：學(xué)生表就只能增加一個(gè)系編號字段患膛。

參考鏈接：

• https://www.zhihu.com/question/24696366
• http://www.cnblogs.com/CareySon/archive/2010/02/16/1668803.html

什么是視圖摊阀？以及視圖的使用場景有哪些？

什么是視圖？以及視圖的使用場景有哪些胞此？

視圖是一種基于數(shù)據(jù)表的一種虛表

• （1）視圖是一種虛表
• （2）視圖建立在已有表的基礎(chǔ)上, 視圖賴以建立的這些表稱為基表
• （3）向視圖提供數(shù)據(jù)內(nèi)容的語句為 SELECT 語句,可以將視圖理解為存儲起來的 SELECT 語句
• （4）視圖向用戶提供基表數(shù)據(jù)的另一種表現(xiàn)形式
• （5）視圖沒有存儲真正的數(shù)據(jù)臣咖，真正的數(shù)據(jù)還是存儲在基表中
• （6）程序員雖然操作的是視圖，但最終視圖還會轉(zhuǎn)成操作基表
• （7）一個(gè)基表可以有0個(gè)或多個(gè)視圖

有的時(shí)候豌鹤，我們可能只關(guān)系一張數(shù)據(jù)表中的某些字段亡哄，而另外的一些人只關(guān)系同一張數(shù)據(jù)表的某些字段...

那么把全部的字段都都顯示給他們看，這是不合理的布疙。

我們應(yīng)該做到：他們想看到什么樣的數(shù)據(jù)蚊惯，我們就給他們什么樣的數(shù)據(jù)...一方面就能夠讓他們只關(guān)注自己的數(shù)據(jù)，另一方面灵临，我們也保證數(shù)據(jù)表一些保密的數(shù)據(jù)不會泄露出來...

image

我們在查詢數(shù)據(jù)的時(shí)候截型，常常需要編寫非常長的SQL語句，幾乎每次都要寫很長很長....上面已經(jīng)說了儒溉，視圖就是基于查詢的一種虛表宦焦，也就是說，視圖可以將查詢出來的數(shù)據(jù)進(jìn)行封裝顿涣。波闹。。那么我們在使用的時(shí)候就會變得非常方便...

值得注意的是：使用視圖可以讓我們專注與邏輯涛碑，但不提高查詢效率

drop精堕、delete與truncate分別在什么場景之下使用？

drop蒲障、delete與truncate分別在什么場景之下使用歹篓？

我們來對比一下他們的區(qū)別：

drop table

• 1)屬于DDL
• 2)不可回滾
• 3)不可帶where
• 4)表內(nèi)容和結(jié)構(gòu)刪除
• 5)刪除速度快

truncate table

• 1)屬于DDL
• 2)不可回滾
• 3)不可帶where
• 4)表內(nèi)容刪除
• 5)刪除速度快

delete from

• 1)屬于DML

• 2)可回滾

• 3)可帶where

• 4)表結(jié)構(gòu)在，表內(nèi)容要看where執(zhí)行的情況

• 5)刪除速度慢,需要逐行刪除

• 不再需要一張表的時(shí)候揉阎，用drop

• 想刪除部分?jǐn)?shù)據(jù)行時(shí)候庄撮，用delete，并且?guī)蟱here子句

• 保留表而刪除所有數(shù)據(jù)的時(shí)候用truncate

索引是什么毙籽？有什么作用以及優(yōu)缺點(diǎn)洞斯？

索引是什么？有什么作用以及優(yōu)缺點(diǎn)坑赡？

什么是索引【Index】

• （1）是一種快速查詢表中內(nèi)容的機(jī)制巡扇，類似于新華字典的目錄
• （2）運(yùn)用在表中某個(gè)些字段上，但存儲時(shí)垮衷，獨(dú)立于表之外

索引表把數(shù)據(jù)變成是有序的....

這里寫圖片描述

快速定位到硬盤中的數(shù)據(jù)文件...

這里寫圖片描述

rowid特點(diǎn)

rowid的特點(diǎn)

• （1）位于每個(gè)表中，但表面上看不見乖坠，例如：desc emp是看不見的
• （2）只有在select中搀突，顯示寫出rowid，方可看見
• （3）它與每個(gè)表綁定在一起熊泵，表亡仰迁，該表的rowid亡甸昏，二張表rownum可以相同，但rowid必須是唯一的
• （4）rowid是18位大小寫加數(shù)字混雜體徐许，唯一表代該條記錄在DBF文件中的位置
• （5）rowid可以參與=/like比較時(shí)施蜜，用''單引號將rowid的值包起來，且區(qū)分大小寫
• （6）rowid是聯(lián)系表與DBF文件的橋梁

索引特點(diǎn)

索引的特點(diǎn)

• （1）索引一旦建立,** Oracle管理系統(tǒng)會對其進(jìn)行自動(dòng)維護(hù)**, 而且由Oracle管理系統(tǒng)決定何時(shí)使用索引
• （2）用戶不用在查詢語句中指定使用哪個(gè)索引
• （3）在定義primary key或unique約束后系統(tǒng)自動(dòng)在相應(yīng)的列上創(chuàng)建索引
• （4）用戶也能按自己的需求雌隅，對指定單個(gè)字段或多個(gè)字段翻默，添加索引

需要注意的是：Oracle是自動(dòng)幫我們管理索引的，并且如果我們指定了primary key或者unique約束恰起，系統(tǒng)會自動(dòng)在對應(yīng)的列上創(chuàng)建索引..

什么時(shí)候【要】創(chuàng)建索引

• （1）表經(jīng)常進(jìn)行 SELECT 操作
• （2）表很大(記錄超多)修械，記錄內(nèi)容分布范圍很廣
• （3）列名經(jīng)常在 WHERE 子句或連接條件中出現(xiàn)

什么時(shí)候【不要】創(chuàng)建索引

• （1）表經(jīng)常進(jìn)行 INSERT/UPDATE/DELETE 操作
• （2）表很小(記錄超少)
• （3）列名不經(jīng)常作為連接條件或出現(xiàn)在 WHERE 子句中

索引優(yōu)缺點(diǎn)：

• 索引加快數(shù)據(jù)庫的檢索速度
• 索引降低了插入、刪除检盼、修改等維護(hù)任務(wù)的速度(雖然索引可以提高查詢速度肯污，但是它們也會導(dǎo)致數(shù)據(jù)庫系統(tǒng)更新數(shù)據(jù)的性能下降，因?yàn)榇蟛糠謹(jǐn)?shù)據(jù)更新需要同時(shí)更新索引)
• 唯一索引可以確保每一行數(shù)據(jù)的唯一性吨枉，通過使用索引蹦渣，可以在查詢的過程中使用優(yōu)化隱藏器，提高系統(tǒng)的性能
• 索引需要占物理和數(shù)據(jù)空間

索引分類：

• 唯一索引：唯一索引不允許兩行具有相同的索引值
• 主鍵索引：為表定義一個(gè)主鍵將自動(dòng)創(chuàng)建主鍵索引貌亭，主鍵索引是唯一索引的特殊類型柬唯。主鍵索引要求主鍵中的每個(gè)值是唯一的，并且不能為空
• 聚集索引(Clustered)：表中各行的物理順序與鍵值的邏輯（索引）順序相同属提，每個(gè)表只能有一個(gè)
• 非聚集索引(Non-clustered)：非聚集索引指定表的邏輯順序权逗。數(shù)據(jù)存儲在一個(gè)位置，索引存儲在另一個(gè)位置冤议，索引中包含指向數(shù)據(jù)存儲位置的指針斟薇。可以有多個(gè)恕酸，小于249個(gè)

深入理解索引可參考：

• https://kb.cnblogs.com/page/45712/
• https://www.cnblogs.com/drizzlewithwind/p/5707058.html

什么是事務(wù)堪滨？

什么是事務(wù)？

事務(wù)簡單來說：一個(gè)Session中所進(jìn)行所有的操作蕊温，要么同時(shí)成功袱箱，要么同時(shí)失敗

ACID — 數(shù)據(jù)庫事務(wù)正確執(zhí)行的四個(gè)基本要素

• 包含：原子性（Atomicity）、一致性（Consistency）义矛、隔離性（Isolation）发笔、持久性（Durability）。

一個(gè)支持事務(wù)（Transaction）中的數(shù)據(jù)庫系統(tǒng)凉翻，必需要具有這四種特性了讨，否則在事務(wù)過程（Transaction processing）當(dāng)中無法保證數(shù)據(jù)的正確性，交易過程極可能達(dá)不到交易。

舉個(gè)例子:A向B轉(zhuǎn)賬前计，轉(zhuǎn)賬這個(gè)流程中如果出現(xiàn)問題胞谭，事務(wù)可以讓數(shù)據(jù)恢復(fù)成原來一樣【A賬戶的錢沒變，B賬戶的錢也沒變】男杈。

事例說明：

        /*
        * 我們來模擬A向B賬號轉(zhuǎn)賬的場景
        *   A和B賬戶都有1000塊丈屹，現(xiàn)在我讓A賬戶向B賬號轉(zhuǎn)500塊錢
        *
        * */
            //JDBC默認(rèn)的情況下是關(guān)閉事務(wù)的，下面我們看看關(guān)閉事務(wù)去操作轉(zhuǎn)賬操作有什么問題

            //A賬戶減去500塊
            String sql = "UPDATE a SET money=money-500 ";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();

            //B賬戶多了500塊
            String sql2 = "UPDATE b SET money=money+500";
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();

從上面看伶棒，我們的確可以發(fā)現(xiàn)A向B轉(zhuǎn)賬旺垒，成功了“耄可是如果A向B轉(zhuǎn)賬的過程中出現(xiàn)了問題呢袖牙？下面模擬一下

            //A賬戶減去500塊
            String sql = "UPDATE a SET money=money-500 ";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();

            //這里模擬出現(xiàn)問題
            int a = 3 / 0;

            String sql2 = "UPDATE b SET money=money+500";
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();

顯然，上面代碼是會拋出異常的舅锄，我們再來查詢一下數(shù)據(jù)鞭达。A賬戶少了500塊錢估蹄，B賬戶的錢沒有增加涮毫。這明顯是不合理的。

我們可以通過事務(wù)來解決上面出現(xiàn)的問題

            //開啟事務(wù),對數(shù)據(jù)的操作就不會立即生效水慨。
            connection.setAutoCommit(false);

            //A賬戶減去500塊
            String sql = "UPDATE a SET money=money-500 ";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();

            //在轉(zhuǎn)賬過程中出現(xiàn)問題
            int a = 3 / 0;

            //B賬戶多500塊
            String sql2 = "UPDATE b SET money=money+500";
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();

            //如果程序能執(zhí)行到這里鳍烁，沒有拋出異常叨襟，我們就提交數(shù)據(jù)
            connection.commit();

            //關(guān)閉事務(wù)【自動(dòng)提交】
            connection.setAutoCommit(true);

        } catch (SQLException e) {
            try {
                //如果出現(xiàn)了異常，就會進(jìn)到這里來幔荒，我們就把事務(wù)回滾【將數(shù)據(jù)變成原來那樣】
                connection.rollback();

                //關(guān)閉事務(wù)【自動(dòng)提交】
                connection.setAutoCommit(true);
            } catch (SQLException e1) {
                e1.printStackTrace();
            }

上面的程序也一樣拋出了異常糊闽，A賬戶錢沒有減少，B賬戶的錢也沒有增加爹梁。

注意：當(dāng)Connection遇到一個(gè)未處理的SQLException時(shí)右犹，系統(tǒng)會非正常退出，事務(wù)也會自動(dòng)回滾姚垃，但如果程序捕獲到了異常念链，是需要在catch中顯式回滾事務(wù)的。

事務(wù)隔離級別

數(shù)據(jù)庫定義了4個(gè)隔離級別：

1. Serializable【可避免臟讀积糯，不可重復(fù)讀掂墓，虛讀】
2. Repeatable read【可避免臟讀，不可重復(fù)讀】
3. Read committed【可避免臟讀】
4. Read uncommitted【級別最低看成，什么都避免不了】

分別對應(yīng)Connection類中的4個(gè)常量

1. TRANSACTION_READ_UNCOMMITTED
2. TRANSACTION_READ_COMMITTED
3. TRANSACTION_REPEATABLE_READ
4. TRANSACTION_SERIALIZABLE

臟讀：一個(gè)事務(wù)讀取到另外一個(gè)事務(wù)未提交的數(shù)據(jù)

例子：A向B轉(zhuǎn)賬君编，A執(zhí)行了轉(zhuǎn)賬語句，但A還沒有提交事務(wù)川慌，B讀取數(shù)據(jù)啦粹，發(fā)現(xiàn)自己賬戶錢變多了偿荷！B跟A說，我已經(jīng)收到錢了唠椭。A回滾事務(wù)【rollback】，等B再查看賬戶的錢時(shí)忍饰，發(fā)現(xiàn)錢并沒有多贪嫂。

不可重復(fù)讀：一個(gè)事務(wù)讀取到另外一個(gè)事務(wù)已經(jīng)提交的數(shù)據(jù)，也就是說一個(gè)事務(wù)可以看到其他事務(wù)所做的修改

注：A查詢數(shù)據(jù)庫得到數(shù)據(jù)艾蓝，B去修改數(shù)據(jù)庫的數(shù)據(jù)力崇，導(dǎo)致A多次查詢數(shù)據(jù)庫的結(jié)果都不一樣【危害：A每次查詢的結(jié)果都是受B的影響的，那么A查詢出來的信息就沒有意思了】

虛讀(幻讀)：是指在一個(gè)事務(wù)內(nèi)讀取到了別的事務(wù)插入的數(shù)據(jù)赢织，導(dǎo)致前后讀取不一致亮靴。

注：和不可重復(fù)讀類似，但虛讀(幻讀)會讀到其他事務(wù)的插入的數(shù)據(jù)于置，導(dǎo)致前后讀取不一致

簡單總結(jié)：臟讀是不可容忍的茧吊，不可重復(fù)讀和虛讀在一定的情況下是可以的【做統(tǒng)計(jì)的肯定就不行】。

數(shù)據(jù)庫的樂觀鎖和悲觀鎖是什么八毯？

數(shù)據(jù)庫的樂觀鎖和悲觀鎖是什么搓侄？

確保在多個(gè)事務(wù)同時(shí)存取數(shù)據(jù)庫中同一數(shù)據(jù)時(shí)不破壞事務(wù)的隔離性和統(tǒng)一性以及數(shù)據(jù)庫的統(tǒng)一性，樂觀鎖和悲觀鎖是并發(fā)控制主要采用的技術(shù)手段话速。

• 悲觀鎖：假定會發(fā)生并發(fā)沖突讶踪，屏蔽一切可能違反數(shù)據(jù)完整性的操作
  • 在查詢完數(shù)據(jù)的時(shí)候就把事務(wù)鎖起來，直到提交事務(wù)
  • 實(shí)現(xiàn)方式：使用數(shù)據(jù)庫中的鎖機(jī)制
• 樂觀鎖：假設(shè)不會發(fā)生并發(fā)沖突泊交，只在提交操作時(shí)檢查是否違反數(shù)據(jù)完整性乳讥。
  • 在修改數(shù)據(jù)的時(shí)候把事務(wù)鎖起來，通過version的方式來進(jìn)行鎖定
  • 實(shí)現(xiàn)方式：使用version版本或者時(shí)間戳

悲觀鎖：

image

樂觀鎖：

image

參考資料：

• http://www.open-open.com/lib/view/open1452046967245.html

超鍵廓俭、候選鍵云石、主鍵、外鍵分別是什么白指？

超鍵留晚、候選鍵、主鍵告嘲、外鍵分別是什么错维？

• 超鍵：在關(guān)系中能唯一標(biāo)識元組的屬性集稱為關(guān)系模式的超鍵。一個(gè)屬性可以為作為一個(gè)超鍵橄唬，多個(gè)屬性組合在一起也可以作為一個(gè)超鍵赋焕。超鍵包含候選鍵和主鍵。
• 候選鍵(候選碼)：是最小超鍵仰楚，即沒有冗余元素的超鍵隆判。
• 主鍵(主碼)：數(shù)據(jù)庫表中對儲存數(shù)據(jù)對象予以唯一和完整標(biāo)識的數(shù)據(jù)列或?qū)傩缘慕M合犬庇。一個(gè)數(shù)據(jù)列只能有一個(gè)主鍵，且主鍵的取值不能缺失侨嘀，即不能為空值（Null）臭挽。
• 外鍵：在一個(gè)表中存在的另一個(gè)表的主鍵稱此表的外鍵。

候選碼和主碼：

例子：郵寄地址（城市名咬腕，街道名欢峰，郵政編碼，單位名涨共，收件人）

• 它有兩個(gè)候選鍵:{城市名纽帖，街道名} 和 {街道名，郵政編碼}
• 如果我選取{城市名举反，街道名}作為唯一標(biāo)識實(shí)體的屬性懊直，那么{城市名，街道名} 就是主碼(主鍵)

SQL 約束有哪幾種火鼻？

SQL 約束有哪幾種室囊？

• NOT NULL: 用于控制字段的內(nèi)容一定不能為空（NULL）。
• UNIQUE: 控件字段內(nèi)容不能重復(fù)凝危，一個(gè)表允許有多個(gè) Unique 約束波俄。
• PRIMARY KEY: 也是用于控件字段內(nèi)容不能重復(fù)，但它在一個(gè)表只允許出現(xiàn)一個(gè)蛾默。
• FOREIGN KEY: 用于預(yù)防破壞表之間連接的動(dòng)作懦铺，也能防止非法數(shù)據(jù)插入外鍵列，因?yàn)樗仨毷撬赶虻哪莻€(gè)表中的值之一支鸡。
• CHECK: 用于控制字段的值范圍冬念。

數(shù)據(jù)庫運(yùn)行于哪種狀態(tài)下可以防止數(shù)據(jù)的丟失？

數(shù)據(jù)庫運(yùn)行于哪種狀態(tài)下可以防止數(shù)據(jù)的丟失牧挣？

在archivelog mode(歸檔模式)只要其歸檔日志文件不丟失急前，就可以有效地防止數(shù)據(jù)丟失。

Mysql存儲引擎

Mysql的存儲引擎有以下幾種：

image

我的是5.7.15版本瀑构，默認(rèn)使用的是Innodb版本裆针！

常用的存儲引擎有以下：

• Innodb引擎，Innodb引擎提供了對數(shù)據(jù)庫ACID事務(wù)的支持寺晌。并且還提供了行級鎖和外鍵的約束世吨。它的設(shè)計(jì)的目標(biāo)就是處理大數(shù)據(jù)容量的數(shù)據(jù)庫系統(tǒng)。
• MyIASM引擎(原本Mysql的默認(rèn)引擎),不提供事務(wù)的支持呻征，也不支持行級鎖和外鍵耘婚。
• MEMORY引擎：所有的數(shù)據(jù)都在內(nèi)存中，數(shù)據(jù)的處理速度快陆赋，但是安全性不高沐祷。

image

同一個(gè)數(shù)據(jù)庫也可以使用多種存儲引擎的表嚷闭。如果一個(gè)表修改要求比較高的事務(wù)處理，可以選擇InnoDB赖临。這個(gè)數(shù)據(jù)庫中可以將查詢要求比較高的表選擇MyISAM存儲胞锰。如果該數(shù)據(jù)庫需要一個(gè)用于查詢的臨時(shí)表，可以選擇MEMORY存儲引擎兢榨。

參考資料：

• https://www.cnblogs.com/xiaohaillong/p/6079551.html
• http://blog.csdn.net/ls5718/article/details/52248040
• http://blog.csdn.net/t146lla128xx0x/article/details/78737290

MyIASM和Innodb兩種引擎所使用的索引的數(shù)據(jù)結(jié)構(gòu)是什么胜蛉？

MyIASM和Innodb兩種引擎所使用的索引的數(shù)據(jù)結(jié)構(gòu)是什么？

答案:都是B+樹!

MyIASM引擎色乾，B+樹的數(shù)據(jù)結(jié)構(gòu)中存儲的內(nèi)容實(shí)際上是實(shí)際數(shù)據(jù)的地址值。也就是說它的索引和實(shí)際數(shù)據(jù)是分開的领突，只不過使用索引指向了實(shí)際數(shù)據(jù)暖璧。這種索引的模式被稱為非聚集索引。

Innodb引擎的索引的數(shù)據(jù)結(jié)構(gòu)也是B+樹君旦，只不過數(shù)據(jù)結(jié)構(gòu)中存儲的都是實(shí)際的數(shù)據(jù)澎办，這種索引有被稱為聚集索引。

varchar和char的區(qū)別

varchar和char的區(qū)別

Char是一種固定長度的類型金砍，varchar是一種可變長度的類型

mysql有關(guān)權(quán)限的表都有哪幾個(gè)

mysql有關(guān)權(quán)限的表都有哪幾個(gè)

MySQL服務(wù)器通過權(quán)限表來控制用戶對數(shù)據(jù)庫的訪問局蚀，權(quán)限表存放在mysql數(shù)據(jù)庫里，由mysql_install_db腳本初始化恕稠。這些權(quán)限表分別user琅绅，db，table_priv鹅巍，columns_priv和host千扶。下面分別介紹一下這些表的結(jié)構(gòu)和內(nèi)容：

• user權(quán)限表：記錄允許連接到服務(wù)器的用戶帳號信息，里面的權(quán)限是全局級的骆捧。
• db權(quán)限表：記錄各個(gè)帳號在各個(gè)數(shù)據(jù)庫上的操作權(quán)限澎羞。
• table_priv權(quán)限表：記錄數(shù)據(jù)表級的操作權(quán)限。
• columns_priv權(quán)限表：記錄數(shù)據(jù)列級的操作權(quán)限敛苇。
• host權(quán)限表：配合db權(quán)限表對給定主機(jī)上數(shù)據(jù)庫級操作權(quán)限作更細(xì)致的控制妆绞。這個(gè)權(quán)限表不受GRANT和REVOKE語句的影響。

數(shù)據(jù)表損壞的修復(fù)方式有哪些枫攀？

數(shù)據(jù)表損壞的修復(fù)方式有哪些括饶？

使用 myisamchk 來修復(fù)，具體步驟：

• 1）修復(fù)前將mysql服務(wù)停止脓豪。
• 2）打開命令行方式巷帝，然后進(jìn)入到mysql的/bin目錄。
• 3）執(zhí)行myisamchk –recover 數(shù)據(jù)庫所在路徑/*.MYI

使用repair table 或者 OPTIMIZE table命令來修復(fù)扫夜，REPAIR TABLE table_name 修復(fù)表 OPTIMIZE TABLE table_name 優(yōu)化表 REPAIR TABLE 用于修復(fù)被破壞的表楞泼。
OPTIMIZE TABLE 用于回收閑置的數(shù)據(jù)庫空間驰徊，當(dāng)表上的數(shù)據(jù)行被刪除時(shí)，所占據(jù)的磁盤空間并沒有立即被回收堕阔，使用了OPTIMIZE TABLE命令后這些空間將被回收棍厂，并且對磁盤上的數(shù)據(jù)行進(jìn)行重排（注意：是磁盤上，而非數(shù)據(jù)庫）

MySQL中InnoDB引擎的行鎖是通過加在什么上完成

MySQL中InnoDB引擎的行鎖是通過加在什么上完成

InnoDB是基于索引來完成行鎖

例: select * from tab_with_index where id = 1 for update;

for update 可以根據(jù)條件來完成行鎖鎖定,并且 id 是有索引鍵的列,

如果 id 不是索引鍵那么InnoDB將完成表鎖,,并發(fā)將無從談起

數(shù)據(jù)庫優(yōu)化的思路

SQL優(yōu)化

在我們書寫SQL語句的時(shí)候超陆，其實(shí)書寫的順序牺弹、策略會影響到SQL的性能，雖然實(shí)現(xiàn)的功能是一樣的时呀，但是它們的性能會有些許差別张漂。

因此，下面就講解在書寫SQL的時(shí)候谨娜，怎么寫比較好航攒。

①選擇最有效率的表名順序

數(shù)據(jù)庫的解析器按照從右到左的順序處理FROM子句中的表名，F(xiàn)ROM子句中寫在最后的表將被最先處理

在FROM子句中包含多個(gè)表的情況下：

• 如果三個(gè)表是完全無關(guān)系的話趴梢，將記錄和列名最少的表漠畜，寫在最后，然后依次類推
• 也就是說：選擇記錄條數(shù)最少的表放在最后

如果有3個(gè)以上的表連接查詢：

• 如果三個(gè)表是有關(guān)系的話坞靶，將引用最多的表憔狞，放在最后，然后依次類推彰阴。
• 也就是說：被其他表所引用的表放在最后

例如：查詢員工的編號瘾敢，姓名，工資硝枉，工資等級廉丽，部門名

emp表被引用得最多，記錄數(shù)也是最多妻味，因此放在form字句的最后面

select emp.empno,emp.ename,emp.sal,salgrade.grade,dept.dname
from salgrade,dept,emp
where (emp.deptno = dept.deptno) and (emp.sal between salgrade.losal and salgrade.hisal)        

②WHERE子句中的連接順序

數(shù)據(jù)庫采用自右而左的順序解析WHERE子句,根據(jù)這個(gè)原理,表之間的連接必須寫在其他WHERE條件之左,那些可以過濾掉最大數(shù)量記錄的條件必須寫在WHERE子句的之右正压。

emp.sal可以過濾多條記錄，寫在WHERE字句的最右邊

      select emp.empno,emp.ename,emp.sal,dept.dname
      from dept,emp
      where (emp.deptno = dept.deptno) and (emp.sal > 1500) 

③SELECT子句中避免使用*號

我們當(dāng)時(shí)學(xué)習(xí)的時(shí)候责球，“*”號是可以獲取表中全部的字段數(shù)據(jù)的焦履。

• 但是它要通過查詢數(shù)據(jù)字典完成的，這意味著將耗費(fèi)更多的時(shí)間
• 使用*號寫出來的SQL語句也不夠直觀雏逾。

④用TRUNCATE替代DELETE

這里僅僅是：刪除表的全部記錄嘉裤，除了表結(jié)構(gòu)才這樣做。

DELETE是一條一條記錄的刪除栖博，而Truncate是將整個(gè)表刪除屑宠，保留表結(jié)構(gòu)，這樣比DELETE快

⑤多使用內(nèi)部函數(shù)提高SQL效率

例如使用mysql的concat()函數(shù)會比使用||來進(jìn)行拼接快仇让，因?yàn)閏oncat()函數(shù)已經(jīng)被mysql優(yōu)化過了典奉。

⑥使用表或列的別名

如果表或列的名稱太長了躺翻，使用一些簡短的別名也能稍微提高一些SQL的性能。畢竟要掃描的字符長度就變少了卫玖。公你。。

⑦多使用commit

comiit會釋放回滾點(diǎn)...

⑧善用索引

索引就是為了提高我們的查詢數(shù)據(jù)的假瞬，當(dāng)表的記錄量非常大的時(shí)候陕靠，我們就可以使用索引了。

⑨SQL寫大寫

我們在編寫SQL 的時(shí)候脱茉，官方推薦的是使用大寫來寫關(guān)鍵字剪芥，因?yàn)镺racle服務(wù)器總是先將小寫字母轉(zhuǎn)成大寫后，才執(zhí)行

⑩避免在索引列上使用NOT

因?yàn)镺racle服務(wù)器遇到NOT后琴许，他就會停止目前的工作粗俱，轉(zhuǎn)而執(zhí)行全表掃描

①①避免在索引列上使用計(jì)算

WHERE子句中，如果索引列是函數(shù)的一部分虚吟，優(yōu)化器將不使用索引而使用全表掃描，這樣會變得變慢

①②用 >= 替代 >

      低效：
      SELECT * FROM EMP WHERE DEPTNO > 3   
      首先定位到DEPTNO=3的記錄并且掃描到第一個(gè)DEPT大于3的記錄
      高效：
      SELECT * FROM EMP WHERE DEPTNO >= 4  
      直接跳到第一個(gè)DEPT等于4的記錄

①③用IN替代OR

      select * from emp where sal = 1500 or sal = 3000 or sal = 800;
      select * from emp where sal in (1500,3000,800);

①④總是使用索引的第一個(gè)列

如果索引是建立在多個(gè)列上签财，只有在它的第一個(gè)列被WHERE子句引用時(shí)串慰，優(yōu)化器才會選擇使用該索引。 當(dāng)只引用索引的第二個(gè)列時(shí)唱蒸，不引用索引的第一個(gè)列時(shí)邦鲫，優(yōu)化器使用了全表掃描而忽略了索引

      create index emp_sal_job_idex
      on emp(sal,job);
      ----------------------------------
      select *
      from emp  
      where job != 'SALES'; 

      上邊就不使用索引了。

數(shù)據(jù)庫結(jié)構(gòu)優(yōu)化

• 1）范式優(yōu)化： 比如消除冗余（節(jié)省空間神汹。庆捺。）
• 2）反范式優(yōu)化：比如適當(dāng)加冗余等（減少join）
• 3）拆分表： 垂直拆分和水平拆分

服務(wù)器硬件優(yōu)化

這個(gè)么多花錢咯！

SQL練習(xí)題

下列練習(xí)題參考自公眾號Java知音：

• https://mp.weixin.qq.com/s?__biz=MzI4Njc5NjM1NQ==&mid=2247483693&idx=1&sn=9fa301b0076778cd854a924e96cc356e&chksm=ebd63e01dca1b71745dca1f7e1c2aa2b7c80a393185db690b4fdfba22bb10ca87ea2cd6fa774&scene=21#wechat_redirect
• https://mp.weixin.qq.com/s?__biz=MzI4Njc5NjM1NQ==&mid=2247483696&idx=1&sn=5f472ce7720aede89e2e15ea64bed1bc&chksm=ebd63e1cdca1b70ad18dec268c9903b2cbe11f9ce7b0633980c78a28bd5b1b57c4efbe7a3411&scene=21#wechat_redirect

基本表結(jié)構(gòu)：

student(sno,sname,sage,ssex)學(xué)生表
course(cno,cname,tno) 課程表
sc(sno,cno,score) 成績表

teacher(tno,tname) 教師表

題目：

101屁魏，查詢課程1的成績比課程2的成績高的所有學(xué)生的學(xué)號
select a.sno from
(select sno,score from sc where cno=1) a,
(select sno,score from sc where cno=2) b
where a.score>b.score and a.sno=b.sno

102滔以，查詢平均成績大于60分的同學(xué)的學(xué)號和平均成績
select a.sno as "學(xué)號", avg(a.score) as "平均成績" 
from
(select sno,score from sc) a 
group by sno having avg(a.score)>60

103，查詢所有同學(xué)的學(xué)號氓拼、姓名你画、選課數(shù)、總成績
select a.sno as 學(xué)號, b.sname as 姓名,
count(a.cno) as 選課數(shù), sum(a.score) as 總成績
from sc a, student b
where a.sno = b.sno
group by a.sno, b.sname

或者：

selectstudent.sno as 學(xué)號, student.sname as 姓名,
 count(sc.cno) as 選課數(shù), sum(score) as 總成績
from student left Outer join sc on student.sno = sc.sno
group by student.sno, sname

104桃漾，查詢姓“張”的老師的個(gè)數(shù)

selectcount(distinct(tname)) from teacher where tname like '張%‘
或者：
select tname as "姓名", count(distinct(tname)) as "人數(shù)" 
from teacher 
where tname like'張%'
group by tname

105坏匪，查詢沒學(xué)過“張三”老師課的同學(xué)的學(xué)號、姓名
select student.sno,student.sname from student
where sno not in (select distinct(sc.sno) from sc,course,teacher
where sc.cno=course.cno and teacher.tno=course.tno and teacher.tname='張三')

106撬统，查詢同時(shí)學(xué)過課程1和課程2的同學(xué)的學(xué)號适滓、姓名
select sno, sname from student
where sno in (select sno from sc where sc.cno = 1)
and sno in (select sno from sc where sc.cno = 2)
或者：

selectc.sno, c.sname from
(select sno from sc where sc.cno = 1) a,
(select sno from sc where sc.cno = 2) b,
student c
where a.sno = b.sno and a.sno = c.sno
或者：

select student.sno,student.sname from student,sc where student.sno=sc.sno and sc.cno=1
and exists( select * from sc as sc_2 where sc_2.sno=sc.sno and sc_2.cno=2)

107，查詢學(xué)過“李四”老師所教所有課程的所有同學(xué)的學(xué)號恋追、姓名
select a.sno, a.sname from student a, sc b
where a.sno = b.sno and b.cno in
(select c.cno from course c, teacher d where c.tno = d.tno and d.tname = '李四')

或者：

select a.sno, a.sname from student a, sc b,
(select c.cno from course c, teacher d where c.tno = d.tno and d.tname = '李四') e
where a.sno = b.sno and b.cno = e.cno

108凭迹，查詢課程編號1的成績比課程編號2的成績高的所有同學(xué)的學(xué)號罚屋、姓名
select a.sno, a.sname from student a,
(select sno, score from sc where cno = 1) b,
(select sno, score from sc where cno = 2) c
where b.score > c.score and b.sno = c.sno and a.sno = b.sno

109，查詢所有課程成績小于60分的同學(xué)的學(xué)號蕊苗、姓名
select sno,sname from student
where sno not in (select distinct sno from sc where score > 60)

110沿后，查詢至少有一門課程與學(xué)號為1的同學(xué)所學(xué)課程相同的同學(xué)的學(xué)號和姓名
select distinct a.sno, a.sname
from student a, sc b
where a.sno <> 1 and a.sno=b.sno and
b.cno in (select cno from sc where sno = 1)

或者：

select s.sno,s.sname 
from student s,
(select sc.sno 
from sc
where sc.cno in (select sc1.cno from sc sc1 where sc1.sno=1)and sc.sno<>1
group by sc.sno)r1
where r1.sno=s.sno

111、把“sc”表中“王五”所教課的成績都更改為此課程的平均成績
update sc set score = (select avg(sc_2.score) from sc sc_2 wheresc_2.cno=sc.cno)
from course,teacher where course.cno=sc.cno and course.tno=teacher.tno andteacher.tname='王五'

112朽砰、查詢和編號為2的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)學(xué)號和姓名
這一題分兩步查：

1尖滚，

select sno
from sc
where sno <> 2
group by sno
having sum(cno) = (select sum(cno) from sc where sno = 2)

2，
select b.sno, b.sname
from sc a, student b
where b.sno <> 2 and a.sno = b.sno
group by b.sno, b.sname
having sum(cno) = (select sum(cno) from sc where sno = 2)

113瞧柔、刪除學(xué)習(xí)“王五”老師課的sc表記錄
delete sc from course, teacher
where course.cno = sc.cno and course.tno = teacher.tno and tname = '王五'

114漆弄、向sc表中插入一些記錄，這些記錄要求符合以下條件：
將沒有課程3成績同學(xué)的該成績補(bǔ)齊, 其成績?nèi)∷袑W(xué)生的課程2的平均成績
insert sc select sno, 3, (select avg(score) from sc where cno = 2)
from student
where sno not in (select sno from sc where cno = 3)

115造锅、按平平均分從高到低顯示所有學(xué)生的如下統(tǒng)計(jì)報(bào)表：
-- 學(xué)號,企業(yè)管理,馬克思,UML,數(shù)據(jù)庫,物理,課程數(shù),平均分
select sno as 學(xué)號
,max(case when cno = 1 then score end) AS 企業(yè)管理
,max(case when cno = 2 then score end) AS 馬克思
,max(case when cno = 3 then score end) AS UML
,max(case when cno = 4 then score end) AS 數(shù)據(jù)庫
,max(case when cno = 5 then score end) AS 物理
,count(cno) AS 課程數(shù)
,avg(score) AS 平均分
FROM sc
GROUP by sno
ORDER by avg(score) DESC

116撼唾、查詢各科成績最高分和最低分：

以如下形式顯示：課程號，最高分哥蔚，最低分
select cno as 課程號, max(score) as 最高分, min(score) 最低分
from sc group by cno

select  course.cno as '課程號'
,MAX(score) as '最高分'
,MIN(score) as '最低分'
from sc,course
where sc.cno=course.cno
group by course.cno

117倒谷、按各科平均成績從低到高和及格率的百分?jǐn)?shù)從高到低順序
SELECT t.cno AS 課程號,
max(course.cname)AS 課程名,
isnull(AVG(score),0) AS 平均成績,
100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/count(1) AS 及格率
FROM sc t, course
where t.cno = course.cno
GROUP BY t.cno
ORDER BY 及格率 desc

118、查詢?nèi)缦抡n程平均成績和及格率的百分?jǐn)?shù)(用"1行"顯示): 

企業(yè)管理（001）糙箍，馬克思（002）渤愁，UML （003），數(shù)據(jù)庫（004） 
select 
avg(case when cno = 1 then score end) as 平均分1,
avg(case when cno = 2 then score end) as 平均分2,
avg(case when cno = 3 then score end) as 平均分3,
avg(case when cno = 4 then score end) as 平均分4,
100 * sum(case when cno = 1 and score > 60 then 1 else 0 end) / sum(casewhen cno = 1 then 1 else 0 end) as 及格率1,
100 * sum(case when cno = 2 and score > 60 then 1 else 0 end) / sum(casewhen cno = 2 then 1 else 0 end) as 及格率2,
100 * sum(case when cno = 3 and score > 60 then 1 else 0 end) / sum(casewhen cno = 3 then 1 else 0 end) as 及格率3,
100 * sum(case when cno = 4 and score > 60 then 1 else 0 end) / sum(casewhen cno = 4 then 1 else 0 end) as 及格率4
from sc

119深夯、查詢不同老師所教不同課程平均分, 從高到低顯示
select max(c.tname) as 教師, max(b.cname) 課程, avg(a.score) 平均分
from sc a, course b, teacher c
where a.cno = b.cno and b.tno = c.tno
group by a.cno
order by 平均分 desc
或者：
select r.tname as '教師',r.rname as '課程' , AVG(score) as '平均分'
from sc,
(select  t.tname,c.cno as rcso,c.cname as rname
from teacher t ,course c
where t.tno=c.tno)r
where sc.cno=r.rcso
group by sc.cno,r.tname,r.rname 
order by AVG(score) desc

120抖格、查詢?nèi)缦抡n程成績均在第3名到第6名之間的學(xué)生的成績：
-- [學(xué)生ID],[學(xué)生姓名],企業(yè)管理,馬克思,UML,數(shù)據(jù)庫,平均成績
select top 6 max(a.sno) 學(xué)號, max(b.sname) 姓名,
max(case when cno = 1 then score end) as 企業(yè)管理,
max(case when cno = 2 then score end) as 馬克思,
max(case when cno = 3 then score end) as UML,
max(case when cno = 4 then score end) as 數(shù)據(jù)庫,
avg(score) as 平均分
from sc a, student b
where a.sno not in 

(select top 2 sno from sc where cno = 1 order by score desc)
  and a.sno not in (select top 2 sno from sc where cno = 2 order by scoredesc)
  and a.sno not in (select top 2 sno from sc where cno = 3 order by scoredesc)
  and a.sno not in (select top 2 sno from sc where cno = 4 order by scoredesc)
  and a.sno = b.sno
group by a.sno

Oracle和Mysql的區(qū)別

在Mysql中，一個(gè)用戶下可以創(chuàng)建多個(gè)庫：

這里寫圖片描述

而在Oracle中咕晋，Oracle服務(wù)器是由兩部分組成

• 數(shù)據(jù)庫實(shí)例【理解為對象雹拄，看不見的】
• 數(shù)據(jù)庫【理解為類，看得見的】

一個(gè)數(shù)據(jù)庫實(shí)例可擁有多個(gè)用戶掌呜，一個(gè)用戶默認(rèn)擁有一個(gè)表空間滓玖。

表空間是存儲我們數(shù)據(jù)庫表的地方，表空間內(nèi)可以有多個(gè)文件质蕉。

這里寫圖片描述

當(dāng)我們使用Oracle作為我們數(shù)據(jù)庫時(shí)呢撞，我們需要指定用戶、表空間來存儲我們所需要的數(shù)據(jù)饰剥！

最后

參考資料：

• http://blog.csdn.net/xlgen157387/article/details/46899031
• http://blog.csdn.net/beauty_1991/article/details/51209107
• https://zhuanlan.zhihu.com/p/23713529
• http://blog.csdn.net/wickedvalley/article/details/51527551
• http://blog.csdn.net/zhugewendu/article/details/73550414

如果文章有錯(cuò)的地方歡迎指正殊霞，大家互相交流。習(xí)慣在微信看技術(shù)文章汰蓉，想要獲取更多的Java資源的同學(xué)绷蹲，可以關(guān)注微信公眾號:Java3y