天天看點
傳回

劍指Offer(更新)之資料庫 & JDBC（三）

1.資料庫三範式是什麼

第一範式：每一列屬性都是不可再分的屬性值，確定每一列的原子性

第二範式：（確定表中每列都和主鍵相關）一張資料表至少有一個主鍵

第三範式：（確定每列都和主鍵列直接相關，而不是間接相關）一張資料表有且隻有一個主鍵

2.SQL分為哪幾個大類

資料操縱語言（DML），資料定義語言（DDL）,資料控制語言（DCL）

  • DML 資料操控:如select,insert,update,delete
  • DCL 資料控制:如權限控制，grant,revoke
  • DDL 資料定義:如create,alter,drop等表定義語句

3.SQL 限制有哪幾種

六大限制：

  • not null：非空
  • default：預設
  • unique key：唯一
  • primary key：主鍵（包含非空為唯一兩種限制）
  • auto_increase：自動增長
  • foreign key：外鍵

4.having、where、groupby的執行順序

Where, Group By, Having, Order by

5.什麼是事務，事物有哪四個特性（詳細解釋，其中由隔離性觸發的問題有哪些）

事務就是将一組SQL語句放在同一批次内去執行；如果一個SQL語句出錯，則該批次内的所有SQL都将被取消執行

  • 原子性
  • 一緻性
  • 隔離性

  • 持久性

    （1）更新丢失：兩事務同時更新，一個失敗復原覆寫另一個事務的更新

    （2）髒讀：讀到另一個未送出事務的資料

    （3）幻讀：在一個事務過程中已經讀取了一次表，此時恰巧另一個事務commit，導緻這次事務再一次讀取表時前後不一緻。（表影響）

    （4）不可重複讀：在一個事務過程中已經讀取了一次a資料，此時恰巧另一個事務commit，導緻這次事務再一次讀取a資料時前後不一緻。（行影響）

6.并發下事務會産生哪些問題，可用對應的哪個事務隔離級别來解決

  • 讀未送出（Read Uncommitted）（一事務寫時禁止其他事務寫）
  • 讀送出（Read Committed）（一事務寫時禁止其他事務讀寫）
  • 可重複讀（Repeatable Read）（一事務寫時禁止其他事務讀寫、一事務讀時禁止其他事務寫）
  • 串行化（Serializable）（一事務寫時禁止其他事務讀寫、一事務讀時禁止其他事務寫）
更新丢失 髒讀 不可重複讀 幻讀
RU（讀未送出） 避免
RC（讀送出） 避免 避免
RR（可重複讀） 避免 避免 避免
S（串行化） 避免 避免 避免 避免

7.Oracle和MySQL的差別（包括預設事務隔離級别）

  • Oracle 的端口是1521 mysql的端口是3306
  • Oracle中隻有一個數值類型 number mysql中有B數值型和浮點型
  • Oracle 是以使用者分類的 ，mysql隻有一個使用者 是以資料庫分類的；
  • SQL文法分類： Oracle 中文法delete【from】 from可以省略，MYSQL不可以

  • 組函數用法規則：

    mysql中組函數在select語句中可以随意使用，但在oracle中如果查詢語句中有組函數，那其他列名必須是組函數處理過的，或者是group by子句中的列否則報錯

  • 自動增長的資料類型處理：

    MYSQL有自動增長的資料類型，插入記錄時不用操作此字段，會自動獲得資料值。ORACLE沒有自動增長的資料類型，需要建立一個自動增長的序列号，插入記錄時要把序列号的下一個值賦于此字段。

  • 單引号的處理：

    MYSQL裡可以用雙引号包起字元串，ORACLE裡隻可以用單引号包起字元串。在插入和修改字元串前必須做單引号的替換：把所有出現的一個單引号替換成兩個單引号。

  • 翻頁的SQL語句的處理

    MYSQL處理翻頁的SQL語句比較簡單，用LIMIT開始位置，記錄個數；ORACLE處理翻頁的SQL語句就比較繁瑣了。每個結果集隻有一個ROWNUM字段标明它的位置，并且隻能用ROWNUM<100，不能用ROWNUM>80。

    以下是經過分析後較好的兩種ORACLE翻頁SQL語句(ID是唯一關鍵字的字段名)：

    語句一：

SELECT ID, [FIELD_NAME,...] FROM TABLE_NAME WHERE ID IN ( SELECT ID FROM (SELECT ROWNUM AS NUMROW, ID FROM TABLE_NAME WHERE 條件1 ORDER BY 條件2) WHERE NUMROW > 80 AND NUMROW < 100 ) ORDER BY 條件3;

語句二：

SELECT * FROM (( SELECT ROWNUM AS NUMROW, c.* from (select [FIELD_NAME,...] FROM TABLE_NAME WHERE 條件1 ORDER BY 條件2) c) WHERE NUMROW > 80 AND NUMROW < 100 ) ORDER BY 條件3;

  • 長字元串的處理

    長字元串的處理ORACLE也有它特殊的地方。INSERT和UPDATE時最大可操作的字元串長度小于等于4000個單位元組，如果要插入更長的字元串，請考慮字段用CLOB類型，方法借用ORACLE裡自帶的DBMS_LOB程式包。插入修改記錄前一定要做進行非空和長度判斷，不能為空的字段值和超出長度字段值都應該提出警告，傳回上次操作。

  • 日期字段的處理

    MYSQL日期字段分DATE和TIME兩種，ORACLE日期字段隻有DATE，包含年月日時分秒資訊，用目前資料庫的系統時間為SYSDATE，精确到秒，或者用字元串轉換成日期型函數TO_DATE(‘2001-08-01’,’YYYY-MM-DD’)年-月-日24小時:分鐘:秒的格式YYYY-MM-DD HH24:MI:SS TO_DATE()還有很多種日期格式，可以參看ORACLE DOC.日期型字段轉換成字元串函數TO_CHAR(‘2001-08-01’,’YYYY-MM-DD HH24:MI:SS’)

  • 空字元的處理

    MYSQL的非空字段也有空的内容，ORACLE裡定義了非空字段就不容許有空的内容。按MYSQL的NOT NULL來定義ORACLE表結構，導資料的時候會産生錯誤。是以導資料時要對空字元進行判斷，如果為NULL或空字元，需要把它改成一個空格的字元串

  • 字元串的模糊比較

    MYSQL裡用字段名like%‘字元串%’，ORACLE裡也可以用字段名like%‘字元串%’但這種方法不能使用索引，速度不快，用字元串比較函數instr(字段名，‘字元串’)>0會得到更精确的查找結果。

8.什麼是事務的傳播行為，為什麼要有傳播行為

事務傳播行為用來描述由某一個事務傳播行為修飾的方法被嵌套進另一個方法的時事務如何傳播。

  • PROPAGATION_REQUIRED 如果目前沒有事務，就建立一個事務，如果已經存在一個事務中，加入到這個事務中。這是最常見的選擇。
  • PROPAGATION_SUPPORTS 支援目前事務，如果目前沒有事務，就以非事務方式執行。
  • PROPAGATION_MANDATORY 使用目前的事務，如果目前沒有事務，就抛出異常。
  • PROPAGATION_REQUIRES_NEW 建立事務，如果目前存在事務，把目前事務挂起。
  • PROPAGATION_NOT_SUPPORTED 以非事務方式執行操作，如果目前存在事務，就把目前事務挂起。
  • PROPAGATION_NEVER 以非事務方式執行，如果目前存在事務，則抛出異常。
  • PROPAGATION_NESTED 如果目前存在事務，則在嵌套事務内執行。如果目前沒有事務，則執行與PROPAGATION_REQUIRED類似的操作。

9.drop table、delete from table和truncate的差別

truncate table和delete from table都可以删除整個資料庫表的記錄

delete
  • DML語言
  • 可以回退
  • 可以有條件的删除
  • DELETE FROM 表名 WHERE 條件
drop
  • 用于删除表（表的結構、屬性以及索引也會被删除）;
  • drop table 表名
truncate
  • DDL語言
  • 無法回退
  • 預設所有的表内容都删除

  • 删除速度比delete快

    truncate table 表名

1、TRUNCATE在各種表上無論是大的還是小的都非常快。如果有ROLLBACK指令Delete将被撤銷，而TRUNCATE則不會被撤銷。

2、TRUNCATE是一個DDL語言，向其他所有的DDL語言一樣，他将被隐式送出，不能對TRUNCATE使用ROLLBACK指令。

3、TRUNCATE将重新設定高水準線和所有的索引。在對整個表和索引進行完全浏覽時，經過TRUNCATE操作後的表比Delete操作後的表要快得多。

4、TRUNCATE不能觸發任何Delete觸發器。

5、當表被清空後表和表的索引講重新設定成初始大小，而delete則不能。

6、不能清空父表。

10.列舉幾種表連接配接方式，有什麼差別

  • 笛卡爾積

    兩表關聯，把左表的列和右表的列通過笛卡爾積的形式表達出來。

select * from 表1 join 表2

  • 左連接配接

    兩表關聯，左表全部保留，右表關聯不上用null表示。

select * from t1 left join t2 on t1.id = t2.id;

  • 右連接配接

    右表全部保留，左表關聯不上的用null表示。

select * from t1 right join t2 on t1.id =t2.id;

  • 内連接配接

    兩表關聯，保留兩表中交集的記錄。

select * from t1 inner join t2 on t1.id = t2.id;

  • 左表獨有

    兩表關聯，查詢左表獨有的資料。

select * from t1 left join t2 on t1.id = t2.id where t2.id is null;

  • 右表獨有

    兩表關聯，查詢右表獨有的資料。

select * from t1 right join t2 on t1.id = t2.id where t1.id is  null;

  • 全連接配接

    兩表關聯，查詢它們的所有記錄。

select * from t1 left join t2 on t1.id = t2.id
    -> union 
    -> select * from t1 right join t2 on t1.id = t2.id

  • 并集去交集

    兩表關聯，取并集然後去交集。

select * from t1 left join t2 on t1.id = t2.id where t2.id is null
    -> union 
    -> select * from t1 right join t2 on t1.id = t2.id where t1.id is null;

11.什麼是視圖，以及視圖的優缺點

視圖是一個虛拟表，是從資料庫中一個或者多個表中導出來的表。

優點：

  • 簡單性；視圖不僅可以簡化使用者對資料的了解，也可以簡化他們的操作
  • 安全性；通過視圖使用者隻能查詢和修改他們所能見到的資料。資料庫中的其他資料則既看不見也取不到。
  • 邏輯資料獨立性；視圖可以使應用程式和資料庫表在一定程度上獨立。如果沒有視圖，應用一定是建立在表上的。

12.什麼是索引，索引的分類，索引有哪些優缺點，建立索引有哪些原則

索引是對資料庫表中一列或多列的值進行排序的一種結構

記住兩點：

（1）提高查詢速度

（2）索引不是越多越好

  • 主鍵索引（primary key）
  • 唯一索引（unique）
  • 正常索引（index）

  • 全文索引（fulltext）

    優點：

    第一，通過建立唯一性索引，可以保證資料庫表中每一行資料的唯一性。

    第二，可以大大加快資料的檢索速度，這也是建立索引的最主要的原因。

    第三，可以加速表和表之間的連接配接，特别是在實作資料的參考完整性方面特别有意義。

    第四，在使用分組和排序子句進行資料檢索時，同樣可以顯著減少查詢中分組和排序的時間。

    第五，通過使用索引，可以在查詢的過程中，使用優化隐藏器，提高系統的性能。

    缺點：

    第一，建立索引和維護索引要耗費時間，這種時間随着資料量的增加而增加。

    第二，索引需要占實體空間，除了資料表占資料空間之外，每一個索引還要占一定的實體空間，如果要建立聚簇索引，那麼需要的空間

    就會更大。

    第三，當對表中的資料進行增加、删除和修改的時候，索引也要動态的維護，這樣就降低了資料的維護速度。

    問題：

    原則：

    1．選擇唯一性索引

    2．為經常需要排序、分組和聯合操作的字段建立索引

    3．為常作為查詢條件的字段建立索引

    4．限制索引的數目

    5．盡量使用資料量少的索引

    6．盡量使用字首來索引

    7．删除不再使用或者很少使用的索引

    8 . 最左字首比對原則，非常重要的原則。

    9 .=和in可以亂序。

    10 . 盡量選擇區分度高的列作為索引。

    11 .索引列不能參與計算，保持列“幹淨”。

    12 .盡量的擴充索引，不要建立索引。

13.如何優化資料庫

優化資料庫

當然最核心的是 怎麼合理建立索引 怎麼使用索引 索引失效 合理建立表字段 這4個方面

1：在子查詢中慎重使用IN或者NOT IN語句，使用where (NOT) exists的效果要好的多或用 join 代替，小表關聯大表（索引失效）

2：對查詢進行優化，要盡量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。（怎麼建立索引）

3：最好不要給資料庫留NULL，盡可能的使用 NOT NULL填充資料庫.:

備注、描述、評論之類的可以設定為 NULL，其他的，最好不要使用NULL。

注意是盡量不要null，請根據實際業務選擇

（合理建立表字段）

4：應盡量避免在 where 子句中使用 != 或 <> 操作符，否則将引擎放棄使用索引而進行全表掃描。（索引失效）

5：應盡量避免在 where 子句中使用 or 來連接配接條件，如果一個字段有索引，一個字段沒有索引，将導緻引擎放棄使用索引而進行全表掃描，（索引失效）

6：索引最左字首比對原則，SQL代碼需要注意這個原則 （索引失效）

7：盡量避免向用戶端傳回大資料量，若資料量過大,建議分頁或數量限制（設計上，這個和SQL沒關系羅）

8：其他，非常多注意mysql 、oracle差別，版本問題 不同版本特性也不一樣

14.描述JDBC連接配接資料庫的步驟

JDBC程式設計的六個步驟：

1.加載驅動程式jar包

2.在java中加載驅動程式Class.forName(指定資料庫的驅動程式)com.mysql.jdbc.Driver

3.建立資料庫連接配接對象

String url = “jdbc:mysql://localhost:3306/ishop1?characterEncoding=utf8”;

String user = “root”;

String password = “123456”;

Connection conn = DriverManager.getConnection(url, user, password);

4.建立Statement 對象Statement state = conn.createStatement();

5.編寫sql語句state.executeUpdate(insert_sql)

6.關閉資料庫

15.什麼是SQL注入，怎樣防止

通過把SQL指令插入到Web表單送出或輸入域名或頁面請求的查詢字元串，最終達到欺騙伺服器執行惡意的SQL指令。

如何防止：

  • 采用PreparedStatement來避免sql注入，會自動對使用者填寫的資料進行驗證(簡單有效)；sql注入隻對sql語句的準備(編譯)過程有破壞作用；而PreparedStatement已經準備好了,執行階段隻是把輸入串作為資料處理,而不再對sql語句進行解析,準備,是以也就避免了sql注入問題.
  • 使用正規表達式過濾傳入的參數（典型的SQL 注入攻擊的正規表達式 ）
  • 字元串過濾，敏感詞過濾

16.什麼是資料庫連接配接池，實作原理以及優勢

通過建立一個資料庫連接配接池以及一套連接配接使用管理政策，使得一個資料庫連接配接可以得到高效、安全的複用，避免了資料庫連接配接頻繁建立、關閉的開銷。

優勢：

1． 資源重用

由于資料庫連接配接得到重用，避免了頻繁建立、釋放連接配接引起的大量性能開銷。在減少系統消耗的基礎上，另一方面也增進了系統運作環境的平穩性（減少記憶體碎片以及資料庫臨時程序/線程的數量）。

2． 更快的系統響應速度

資料庫連接配接池在初始化過程中，往往已經建立了若幹資料庫連接配接置于池中備用。此時連接配接的初始化工作均已完成。對于業務請求處理而言，直接利用現有可用連接配接，避免了資料庫連接配接初始化和釋放過程的時間開銷，進而縮減了系統整體響應時間。

3． 新的資源配置設定手段

對于多應用共享同一資料庫的系統而言，可在應用層通過資料庫連接配接的配置，實作資料庫連接配接池技術，幾年錢也許還是個新鮮話題，對于目前的業務系統而言，如果設計中還沒有考慮到連接配接池的應用，那麼…….快在設計文檔中加上這部分的内容吧。某一應用最大可用資料庫連接配接數的限制，避免某一應用獨占所有資料庫資源。

4． 統一的連接配接管理，避免資料庫連接配接洩漏

在較為完備的資料庫連接配接池實作中，可根據預先的連接配接占用逾時設定，強制收回被占用連接配接。進而避免了正常資料庫連接配接操作中可能出現的資源洩漏。一個最小化的資料庫連接配接池實作：

17.什麼是存儲過程，什麼是函數，怎樣建立存儲過程和函數

存儲過程（Stored Procedure）是一組為了完成特定功能的SQL 語句集，經編譯後存儲在資料庫中,使用者通過指定存儲過程的名字并給出參數（如果該存儲過程帶有參數）來執行它。

create procedure sp_name(proc_parameter[...])
begin
[characteristic ...] routine_body
end

sp_name：存儲過程名稱

proc_parameter：存儲過程的參數清單

characteristic：存儲過程的特性

routine_body：SQL語句的内容，可以用begin…end來标志SQL語句的開始和結束

18.什麼是觸發器，觸發器有哪些作用

觸發器是一種特殊的存儲過程，它在試圖更改觸發器所保護的資料時自動執行。

作用：

1.安全性。可以基于資料庫的值使使用者具有操作資料庫的某種權利。

2.審計。可以跟蹤使用者對資料庫的操作。

3.實作複雜的非标準的資料庫相關完整性規則。觸發器可以對資料庫中相關的表進行連環更新。觸發器能夠拒絕或回退那些破壞相關完整性的變化，取消試圖進行資料更新的事務。當插入一個與其主健不比對的外部鍵時，這種觸發器會起作用。

4.同步實時地複制表中的資料。

5.自動計算資料值，如果資料的值達到了一定的要求，則進行特定的處理。

建立：

CREATE TRIGGER trigger_name
        trigger_time
        trigger_event ON tbl_name
        FOR EACH ROW
        trigger_stmt

trigger_name:用來表示觸發器的名稱，可以自己設計

trigger_time:辨別觸發器的觸發時機，取值是BEFORE或AFTER

trigger_event:辨別觸發事件，取值為INSERT,UPDATE和DELETE

tbl_name:辨別建立觸發器的表名，即在哪張表上建立觸發器

trigger_stmt:觸發器程式體，可以是一句SQL語句，或者用 BEGIN 和 END包含的多條語句。

舉例：

delimiter $
create trigger tri_1
after insert on `order`
for each row 
begin 
update commodity set c_num=c_num-new.o_num where c_id=new.o_cid;
end$
delimiter ;
劍指offer 資料庫面試題 JDBC
上一篇: 由于濫用void *引發的bug
下一篇: [論文解讀]TensorFuzz: Debugging Neural Networks with Coverage-Guided FuzzingTensorFuzz: Debugging Neural Networks with Coverage-Guided Fuzzing

繼續閱讀