資料庫面試題(開發者必看)

来源:https://www.cnblogs.com/Java3y/archive/2018/03/05/8507258.html
-Advertisement-
Play Games

資料庫常見面試題(開發者篇) 什麼是存儲過程？有哪些優缺點？ 什麼是存儲過程？有哪些優缺點？ 存儲過程就像我們編程語言中的函數一樣，封裝了我們的代碼(PLSQL、T SQL) 。 存儲過程的優點： 能夠將代碼封裝起來 保存在資料庫之中 讓編程語言進行調用 存儲過程是一個預編譯的代碼塊，執行效率比較高 ...


資料庫常見面試題(開發者篇)

這裡寫圖片描述

什麼是存儲過程？有哪些優缺點？

什麼是存儲過程？有哪些優缺點？

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

存儲過程的優點：

  • 能夠將代碼封裝起來
  • 保存在資料庫之中
  • 讓編程語言進行調用
  • 存儲過程是一個預編譯的代碼塊，執行效率比較高
  • 一個存儲過程替代大量T_SQL語句 ，可以降低網路通信量，提高通信速率

存儲過程的缺點：

  • 每個資料庫的存儲過程語法幾乎都不一樣，十分難以維護（不通用）
  • 業務邏輯放在資料庫上，難以迭代

三個範式是什麼

三個範式是什麼

第一範式（1NF）：資料庫表中的欄位都是單一屬性的，不可再分。這個單一屬性由基本類型構成，包括整型、實數、字元型、邏輯型、日期型等。
第二範式（2NF）：資料庫表中不存在非關鍵欄位對任一候選關鍵欄位的部分函數依賴（部分函數依賴指的是存在組合關鍵字中的某些欄位決定非關鍵欄位的情況），也即所有非關鍵欄位都完全依賴於任意一組候選關鍵字。
第三範式（3NF）：在第二範式的基礎上，數據表中如果不存在非關鍵欄位對任一候選關鍵欄位的傳遞函數依賴則符合第三範式。所謂傳遞函數依賴，指的是如果存在"A → B → C"的決定關係，則C傳遞函數依賴於A。因此，滿足第三範式的資料庫表應該不存在如下依賴關係： 關鍵欄位 → 非關鍵欄位x → 非關鍵欄位y

上面的文字我們肯定是看不懂的，也不願意看下去的。接下來我就總結一下：

  • 首先要明確的是：滿足著第三範式，那麼就一定滿足第二範式、滿足著第二範式就一定滿足第一範式
  • 第一範式：欄位是最小的的單元不可再分
    • 學生信息組成學生信息表，有年齡、性別、學號等信息組成。這些欄位都不可再分，所以它是滿足第一範式的
  • 第二範式：滿足第一範式,表中的欄位必須完全依賴於全部主鍵而非部分主鍵。
    • 其他欄位組成的這行記錄和主鍵表示的是同一個東西，而主鍵是唯一的，它們只需要依賴於主鍵，也就成了唯一的
    • 學號為1024的同學，姓名為Java3y，年齡是22歲。姓名和年齡欄位都依賴著學號主鍵。
  • 第三範式：滿足第二範式，非主鍵外的所有欄位必須互不依賴
    • 就是數據只在一個地方存儲，不重覆出現在多張表中，可以認為就是消除傳遞依賴
    • 比如，我們大學分了很多系（中文系、英語系、電腦系……），這個系別管理表信息有以下欄位組成：系編號，系主任，系簡介，系架構。那我們能不能在學生信息表添加系編號，系主任，系簡介，系架構欄位呢？不行的，因為這樣就冗餘了，非主鍵外的欄位形成了依賴關係(依賴到學生信息表了)！正確的做法是：學生表就只能增加一個系編號欄位。

參考鏈接：

什麼是視圖？以及視圖的使用場景有哪些？

什麼是視圖？以及視圖的使用場景有哪些？

視圖是一種基於數據表的一種虛表

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

有的時候，我們可能只關係一張數據表中的某些欄位，而另外的一些人只關係同一張數據表的某些欄位...

那麼把全部的欄位都都顯示給他們看，這是不合理的。

我們應該做到：他們想看到什麼樣的數據，我們就給他們什麼樣的數據...一方面就能夠讓他們只關註自己的數據，另一方面，我們也保證數據表一些保密的數據不會泄露出來...

我們在查詢數據的時候，常常需要編寫非常長的SQL語句，幾乎每次都要寫很長很長....上面已經說了，視圖就是基於查詢的一種虛表，也就是說，視圖可以將查詢出來的數據進行封裝。。。那麼我們在使用的時候就會變得非常方便...

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

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

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

我們來對比一下他們的區別：

drop table

  • 1)屬於DDL
  • 2)不可回滾
  • 3)不可帶where
  • 4)表內容和結構刪除
  • 5)刪除速度快

truncate table

  • 1)屬於DDL
  • 2)不可回滾
  • 3)不可帶where
  • 4)表內容刪除
  • 5)刪除速度快

delete from

  • 1)屬於DML
  • 2)可回滾
  • 3)可帶where
  • 4)表結構在，表內容要看where執行的情況

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

  • 不再需要一張表的時候，用drop
  • 想刪除部分數據行時候，用delete，並且帶上where子句

  • 保留表而刪除所有數據的時候用truncate

索引是什麼？有什麼作用以及優缺點？

索引是什麼？有什麼作用以及優缺點？

什麼是索引【Index】

  • （1）是一種快速查詢表中內容的機制，類似於新華字典的目錄
  • （2）運用在表中某個些欄位上，但存儲時，獨立於表之外

索引表把數據變成是有序的....
這裡寫圖片描述

快速定位到硬碟中的數據文件...

這裡寫圖片描述

rowid特點

rowid的特點

  • （1）位於每個表中，但錶面上看不見，例如：desc emp是看不見的
  • （2）只有在select中，顯示寫出rowid，方可看見
  • （3）它與每個表綁定在一起，表亡，該表的rowid亡，二張表rownum可以相同，但rowid必須是唯一的
  • （4）rowid是18位大小寫加數字混雜體，唯一表代該條記錄在DBF文件中的位置
  • （5）rowid可以參與=/like比較時，用''單引號將rowid的值包起來，且區分大小寫
  • （6）rowid是聯繫表與DBF文件的橋梁

索引特點

索引的特點

  • （1）索引一旦建立,** Oracle管理系統會對其進行自動維護**, 而且由Oracle管理系統決定何時使用索引
  • （2）用戶不用在查詢語句中指定使用哪個索引
  • （3）在定義primary key或unique約束後系統自動在相應的列上創建索引
  • （4）用戶也能按自己的需求，對指定單個欄位或多個欄位，添加索引

需要註意的是：Oracle是自動幫我們管理索引的，並且如果我們指定了primary key或者unique約束，系統會自動在對應的列上創建索引..

什麼時候【要】創建索引

  • （1）表經常進行 SELECT 操作
  • （2）表很大(記錄超多)，記錄內容分佈範圍很廣
  • （3）列名經常在 WHERE 子句或連接條件中出現

什麼時候【不要】創建索引

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

索引優缺點：

  • 索引加快資料庫的檢索速度
  • 索引降低了插入、刪除、修改等維護任務的速度(雖然索引可以提高查詢速度，但是它們也會導致資料庫系統更新數據的性能下降，因為大部分數據更新需要同時更新索引)
  • 唯一索引可以確保每一行數據的唯一性，通過使用索引，可以在查詢的過程中使用優化隱藏器，提高系統的性能
  • 索引需要占物理和數據空間

索引分類：

  • 唯一索引：唯一索引不允許兩行具有相同的索引值
  • 主鍵索引：為表定義一個主鍵將自動創建主鍵索引，主鍵索引是唯一索引的特殊類型。主鍵索引要求主鍵中的每個值是唯一的，並且不能為空
  • 聚集索引(Clustered)：表中各行的物理順序與鍵值的邏輯（索引）順序相同，每個表只能有一個
  • 非聚集索引(Non-clustered)：非聚集索引指定表的邏輯順序。數據存儲在一個位置，索引存儲在另一個位置，索引中包含指向數據存儲位置的指針。可以有多個，小於249個

深入理解索引可參考：

什麼是事務？

什麼是事務？

事務簡單來說：一個Session中所進行所有的操作，要麼同時成功，要麼同時失敗

ACID — 資料庫事務正確執行的四個基本要素

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

一個支持事務（Transaction）中的資料庫系統，必需要具有這四種特性，否則在事務過程（Transaction processing）當中無法保證數據的正確性，交易過程極可能達不到交易。

舉個例子:A向B轉賬，轉賬這個流程中如果出現問題，事務可以讓數據恢覆成原來一樣【A賬戶的錢沒變，B賬戶的錢也沒變】。

事例說明：



        /*
        * 我們來模擬A向B賬號轉賬的場景
        *   A和B賬戶都有1000塊，現在我讓A賬戶向B賬號轉500塊錢
        *
        * */
            //JDBC預設的情況下是關閉事務的，下麵我們看看關閉事務去操作轉賬操作有什麼問題

            //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();

從上面看，我們的確可以發現A向B轉賬，成功了。可是如果A向B轉賬的過程中出現了問題呢？下麵模擬一下


            //A賬戶減去500塊
            String sql = "UPDATE a SET money=money-500 ";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();
            
            //這裡模擬出現問題
            int a = 3 / 0;


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

顯然，上面代碼是會拋出異常的，我們再來查詢一下數據。A賬戶少了500塊錢，B賬戶的錢沒有增加。這明顯是不合理的。

我們可以通過事務來解決上面出現的問題


            //開啟事務,對數據的操作就不會立即生效。
            connection.setAutoCommit(false);
            
            //A賬戶減去500塊
            String sql = "UPDATE a SET money=money-500 ";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();

            //在轉賬過程中出現問題
            int a = 3 / 0;

            //B賬戶多500塊
            String sql2 = "UPDATE b SET money=money+500";
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();
            
            //如果程式能執行到這裡，沒有拋出異常，我們就提交數據
            connection.commit();

            //關閉事務【自動提交】
            connection.setAutoCommit(true);
            

        } catch (SQLException e) {
            try {
                //如果出現了異常，就會進到這裡來，我們就把事務回滾【將數據變成原來那樣】
                connection.rollback();
                
                //關閉事務【自動提交】
                connection.setAutoCommit(true);
            } catch (SQLException e1) {
                e1.printStackTrace();
            }

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

註意：當Connection遇到一個未處理的SQLException時，系統會非正常退出，事務也會自動回滾，但如果程式捕獲到了異常，是需要在catch中顯式回滾事務的。

事務隔離級別

資料庫定義了4個隔離級別：

  1. Serializable【可避免臟讀，不可重覆讀，虛讀】
  2. Repeatable read【可避免臟讀，不可重覆讀】
  3. Read committed【可避免臟讀】
  4. Read uncommitted【級別最低，什麼都避免不了】

分別對應Connection類中的4個常量

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

臟讀：一個事務讀取到另外一個事務未提交的數據

例子：A向B轉賬，A執行了轉賬語句，但A還沒有提交事務，B讀取數據，發現自己賬戶錢變多了！B跟A說，我已經收到錢了。A回滾事務【rollback】，等B再查看賬戶的錢時，發現錢並沒有多。

不可重覆讀：一個事務讀取到另外一個事務已經提交的數據，也就是說一個事務可以看到其他事務所做的修改

註：A查詢資料庫得到數據，B去修改資料庫的數據，導致A多次查詢資料庫的結果都不一樣【危害：A每次查詢的結果都是受B的影響的，那麼A查詢出來的信息就沒有意思了】

虛讀(幻讀)：是指在一個事務內讀取到了別的事務插入的數據，導致前後讀取不一致。

註：和不可重覆讀類似，但虛讀(幻讀)會讀到其他事務的插入的數據，導致前後讀取不一致

簡單總結：臟讀是不可容忍的，不可重覆讀和虛讀在一定的情況下是可以的【做統計的肯定就不行】。

資料庫的樂觀鎖和悲觀鎖是什麼？

資料庫的樂觀鎖和悲觀鎖是什麼？

確保在多個事務同時存取資料庫中同一數據時不破壞事務的隔離性和統一性以及資料庫的統一性，樂觀鎖和悲觀鎖是併發控制主要採用的技術手段。

  • 悲觀鎖：假定會發生併發衝突，屏蔽一切可能違反數據完整性的操作
    • 在查詢完數據的時候就把事務鎖起來，直到提交事務
    • 實現方式：使用資料庫中的鎖機制
  • 樂觀鎖：假設不會發生併發衝突，只在提交操作時檢查是否違反數據完整性。
    • 在修改數據的時候把事務鎖起來，通過version的方式來進行鎖定
    • 實現方式：使用version版本或者時間戳

悲觀鎖：

樂觀鎖：

參考資料：

超鍵、候選鍵、主鍵、外鍵分別是什麼？

超鍵、候選鍵、主鍵、外鍵分別是什麼？

  • 超鍵：在關係中能唯一標識元組的屬性集稱為關係模式的超鍵。一個屬性可以為作為一個超鍵，多個屬性組合在一起也可以作為一個超鍵。超鍵包含候選鍵和主鍵。
  • 候選鍵(候選碼)：是最小超鍵，即沒有冗餘元素的超鍵。
  • 主鍵(主碼)：資料庫表中對儲存數據對象予以唯一和完整標識的數據列或屬性的組合。一個數據列只能有一個主鍵，且主鍵的取值不能缺失，即不能為空值（Null）。
  • 外鍵：在一個表中存在的另一個表的主鍵稱此表的外鍵。

候選碼和主碼：

例子：郵寄地址（城市名，街道名，郵政編碼，單位名，收件人）

  • 它有兩個候選鍵:{城市名，街道名} 和 {街道名，郵政編碼}
  • 如果我選取{城市名，街道名}作為唯一標識實體的屬性，那麼{城市名，街道名} 就是主碼(主鍵)

SQL 約束有哪幾種？

SQL 約束有哪幾種？

  • NOT NULL: 用於控制欄位的內容一定不能為空（NULL）。
  • UNIQUE: 控制項欄位內容不能重覆，一個表允許有多個 Unique 約束。
  • PRIMARY KEY: 也是用於控制項欄位內容不能重覆，但它在一個表只允許出現一個。
  • FOREIGN KEY: 用於預防破壞表之間連接的動作，也能防止非法數據插入外鍵列，因為它必須是它指向的那個表中的值之一。
  • CHECK: 用於控制欄位的值範圍。

資料庫運行於哪種狀態下可以防止數據的丟失？

資料庫運行於哪種狀態下可以防止數據的丟失？

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

Mysql存儲引擎

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

我的是5.7.15版本，預設使用的是Innodb版本！

常用的存儲引擎有以下：

  • Innodb引擎，Innodb引擎提供了對資料庫ACID事務的支持。並且還提供了行級鎖和外鍵的約束。它的設計的目標就是處理大數據容量的資料庫系統。
  • MyIASM引擎(原本Mysql的預設引擎),不提供事務的支持，也不支持行級鎖和外鍵。
  • MEMORY引擎：所有的數據都在記憶體中，數據的處理速度快，但是安全性不高。

同一個資料庫也可以使用多種存儲引擎的表。如果一個表修改要求比較高的事務處理，可以選擇InnoDB。這個資料庫中可以將查詢要求比較高的表選擇MyISAM存儲。如果該資料庫需要一個用於查詢的臨時表，可以選擇MEMORY存儲引擎。

參考資料：

MyIASM和Innodb兩種引擎所使用的索引的數據結構是什麼？

MyIASM和Innodb兩種引擎所使用的索引的數據結構是什麼？

答案:都是B+樹!

MyIASM引擎，B+樹的數據結構中存儲的內容實際上是實際數據的地址值。也就是說它的索引和實際數據是分開的，只不過使用索引指向了實際數據。這種索引的模式被稱為非聚集索引。

Innodb引擎的索引的數據結構也是B+樹，只不過數據結構中存儲的都是實際的數據，這種索引有被稱為聚集索引。

varchar和char的區別

varchar和char的區別

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

mysql有關許可權的表都有哪幾個

mysql有關許可權的表都有哪幾個

MySQL伺服器通過許可權表來控制用戶對資料庫的訪問，許可權表存放在mysql資料庫里，由mysql_install_db腳本初始化。這些許可權表分別user，db，table_priv，columns_priv和host。下麵分別介紹一下這些表的結構和內容：

  • user許可權表：記錄允許連接到伺服器的用戶帳號信息，裡面的許可權是全局級的。
  • db許可權表：記錄各個帳號在各個資料庫上的操作許可權。
  • table_priv許可權表：記錄數據表級的操作許可權。
  • columns_priv許可權表：記錄數據列級的操作許可權。
  • host許可權表：配合db許可權表對給定主機上資料庫級操作許可權作更細緻的控制。這個許可權表不受GRANT和REVOKE語句的影響。

數據表損壞的修複方式有哪些？

數據表損壞的修複方式有哪些？

使用 myisamchk 來修複，具體步驟：

  • 1）修複前將mysql服務停止。
  • 2）打開命令行方式，然後進入到mysql的/bin目錄。
  • 3）執行myisamchk –recover 資料庫所在路徑/*.MYI

使用repair table 或者 OPTIMIZE table命令來修複，REPAIR TABLE table_name 修複表 OPTIMIZE TABLE table_name 優化表 REPAIR TABLE 用於修複被破壞的表。
OPTIMIZE TABLE 用於回收閑置的資料庫空間，當表上的數據行被刪除時，所占據的磁碟空間並沒有立即被回收，使用了OPTIMIZE TABLE命令後這些空間將被回收，並且對磁碟上的數據行進行重排（註意：是磁碟上，而非資料庫）

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

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

InnoDB是基於索引來完成行鎖

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

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

如果 id 不是索引鍵那麼InnoDB將完成表鎖,,併發將無從談起

資料庫優化的思路

SQL優化

在我們書寫SQL語句的時候，其實書寫的順序、策略會影響到SQL的性能，雖然實現的功能是一樣的，但是它們的性能會有些許差別。

因此，下麵就講解在書寫SQL的時候，怎麼寫比較好。

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

資料庫的解析器按照從右到左的順序處理FROM子句中的表名，FROM子句中寫在最後的表將被最先處理

在FROM子句中包含多個表的情況下：

  • 如果三個表是完全無關係的話，將記錄和列名最少的表，寫在最後，然後依次類推
  • 也就是說：選擇記錄條數最少的表放在最後

如果有3個以上的表連接查詢：

  • 如果三個表是有關係的話，將引用最多的表，放在最後，然後依次類推。
  • 也就是說：被其他表所引用的表放在最後

例如：查詢員工的編號，姓名，工資，工資等級，部門名

emp表被引用得最多，記錄數也是最多，因此放在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子句中的連接順序

資料庫採用自右而左的順序解析WHERE子句,根據這個原理,表之間的連接必須寫在其他WHERE條件之左,那些可以過濾掉最大數量記錄的條件必須寫在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子句中避免使用*號 ####

我們當時學習的時候，“*”號是可以獲取表中全部的欄位數據的。

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

④用TRUNCATE替代DELETE

這裡僅僅是：刪除表的全部記錄，除了表結構才這樣做。

DELETE是一條一條記錄的刪除，而Truncate是將整個表刪除，保留表結構，這樣比DELETE快

⑤多使用內部函數提高SQL效率

例如使用mysql的concat()函數會比使用||來進行拼接快，因為concat()函數已經被mysql優化過了。

⑥使用表或列的別名

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

⑦多使用commit

comiit會釋放回滾點...

⑧善用索引

索引就是為了提高我們的查詢數據的，當表的記錄量非常大的時候，我們就可以使用索引了。

⑨SQL寫大寫

我們在編寫SQL 的時候，官方推薦的是使用大寫來寫關鍵字，因為Oracle伺服器總是先將小寫字母轉成大寫後，才執行

⑩避免在索引列上使用NOT

因為Oracle伺服器遇到NOT後，他就會停止目前的工作，轉而執行全表掃描

①①避免在索引列上使用計算

WHERE子句中，如果索引列是函數的一部分，優化器將不使用索引而使用全表掃描，這樣會變得變慢

①②用 >= 替代 >

      低效：
      SELECT * FROM EMP WHERE DEPTNO > 3   
      首先定位到DEPTNO=3的記錄並且掃描到第一個DEPT大於3的記錄
      高效：
      SELECT * FROM EMP WHERE DEPTNO >= 4  
      直接跳到第一個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);

①④總是使用索引的第一個列

如果索引是建立在多個列上，只有在它的第一個列被WHERE子句引用時，優化器才會選擇使用該索引。 當只引用索引的第二個列時，不引用索引的第一個列時，優化器使用了全表掃描而忽略了索引


      create index emp_sal_job_idex
      on emp(sal,job);
      ----------------------------------
      select *
      from emp  
      where job != 'SALES'; 
      
      
      上邊就不使用索引了。

資料庫結構優化

  • 1）範式優化： 比如消除冗餘（節省空間。。）
  • 2）反範式優化：比如適當加冗餘等（減少join）
  • 3）拆分表： 垂直拆分和水平拆分

伺服器硬體優化

這個麽多花錢咯！

SQL練習題

下列練習題參考自公眾號Java知音：

基本表結構：

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

teacher(tno,tname) 教師表

題目：



101，查詢課程1的成績比課程2的成績高的所有學生的學號
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分的同學的學號和平均成績
select a.sno as "學號", avg(a.score) as "平均成績" 
from
(select sno,score from sc) a 
group by sno having avg(a.score)>60



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

或者：

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

104，查詢姓“張”的老師的個數

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



105，查詢沒學過“張三”老師課的同學的學號、姓名
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，查詢同時學過課程1和課程2的同學的學號、姓名
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，查詢學過“李四”老師所教所有課程的所有同學的學號、姓名
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的成績高的所有同學的學號、姓名
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分的同學的學號、姓名
select sno,sname from student
where sno not in (select distinct sno from sc where score > 60)



110，查詢至少有一門課程與學號為1的同學所學課程相同的同學的學號和姓名
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的同學學習的課程完全相同的其他同學學號和姓名
這一題分兩步查：

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、刪除學習“王五”老師課的sc表記錄
delete sc from course, teacher
where course.cno = sc.cno and course.tno = teacher.tno and tname = '王五'

114、向sc表中插入一些記錄，這些記錄要求符合以下條件：
將沒有課程3成績同學的該成績補齊, 其成績取所有學生的課程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、按平平均分從高到低顯示所有學生的如下統計報表：
-- 學號,企業管理,馬克思,UML,資料庫,物理,課程數,平均分
select sno as 學號
,max(case when cno = 1 then score end) AS 企業管理
,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 資料庫
,max(case when cno = 5 then score end) AS 物理
,count(cno) AS 課程數
,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、按各科平均成績從低到高和及格率的百分數從高到低順序
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、查詢如下課程平均成績和及格率的百分數(用"1行"顯示): 

企業管理（001），馬克思（002），UML （003），資料庫（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、查詢如下課程成績均在第3名到第6名之間的學生的成績：
-- [學生ID],[學生姓名],企業管理,馬克思,UML,資料庫,平均成績
select top 6 max(a.sno) 學號, max(b.sname) 姓名,
max(case when cno = 1 then score end) as 企業管理,
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 資料庫,
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的區別

在Mysql中，一個用戶下可以創建多個庫：

這裡寫圖片描述

而在Oracle中，Oracle伺服器是由兩部分組成

  • 資料庫實例【理解為對象，看不見的】
  • 資料庫【理解為類，看得見的】

一個資料庫實例可擁有多個用戶，一個用戶預設擁有一個表空間。

表空間是存儲我們資料庫表的地方，表空間內可以有多個文件。

這裡寫圖片描述

當我們使用Oracle作為我們資料庫時，我們需要指定用戶、表空間來存儲我們所需要的數據！

最後

參考資料：

如果文章有錯的地方歡迎指正，大家互相交流。習慣在微信看技術文章，想要獲取更多的Java資源的同學，可以關註微信公眾號:Java3y


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 使用sed刪除拼音的音調
    有時候我們對文檔過濾會有這樣的需求： 例如，製作一個比較全的姓氏拼音字典。首先去網上找一個常用姓氏拼音表。我隨便找了一個： 通常網上找到的都是些包含音調的字元序列。我們的主要目的包括： 假如上述文件存放在yindiao.txt中。 (1).去掉非拼音相關的字元，只保留拼音字母，並存放到yindiao ...
  • ubuntu16.04下安裝mysql，並開啟遠程訪問
    一、安裝 apt-get install mysql-server 二、本地連接 mysql預設開啟了本地連接 直接通過mysql -uuser -p，然後輸入密碼訪問 三、開啟遠程訪問 3.1、創建一個與管理員同等許可權的用戶 grant all privileges on *.* to 'user ...
  • 記Windows10下安裝Docker的步驟
    本文主要是記錄吾八哥本人在Windows10下安裝Docker的過程，當然這些步驟也是在網上搜索查閱資料得知而來的！具體步驟如下： 一、啟用Hyper-V 打開控制面板 - 程式和功能 - 啟用或關閉Windows功能，勾選Hyper-V，然後點擊確定即可，如圖： 點擊確定後，啟用完畢會提示重啟系統 ...
  • LNMP之Nginx
    Nginx (engine x) 是一個高性能的HTTP和反向代理伺服器，也是一個IMAP/POP3/SMTP伺服器。Nginx是由伊戈爾·賽索耶夫為俄羅斯訪問量第二的Rambler.ru站點（俄文：Рамблер）開發的，第一個公開版本0.1.0發佈於2004年10月4日。 其將源代碼以類BSD許 ...
  • kali網卡配置文件
    kali的網卡配置文件為/etc/network/interfaces 下麵定義了lo、eth0和eth1的配置 然後重啟網路 或者重啟某個網卡 ...
  • 在VMWare上安裝Arch Linux
    1、為什麼選擇Arch Linux Arch Linux 是通用 x86-64 GNU/Linux 發行版。Arch採用滾動升級模式，盡全力提供最新的穩定版軟體。初始安裝的Arch只是一個基本系統，隨後用戶可以根據自己的喜好安裝需要的軟體並配置成符合自己理想的系統。 —— 引自Arch Linux ...
  • Linux中查看jdk安裝目錄、Linux卸載jdk、rpm命令、rm命令參數
    Linux中查看jdk安裝目錄、Linux卸載jdk、rpm命令、rm命令參數 ...
  • LAMP實戰之構建博客網站
    1、首先檢查LAMP環境 2、配置博客軟體（wordpress） 上述mv的原因是cp出錯，查看錯誤日誌 缺少文件，所以cp無法全部拷貝，使用mv 輸入120.25.255.87,進入wordpress設置 3、配置資料庫 因為博客的安裝需要用到資料庫 許可權不夠，因為把httpd.conf用戶改為了 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類