Mysql零碎知識點（今日所學）

標籤：

1. 列出各個部門中工資高於本部門的平均工資的員工數和部門號，並按部門號排序

select a.deptid, count(*) from employee921 as a INNER JOIN

(select deptid, avg(salary)as avgsar from employee921 group by deptid) as b

where a.deptid = b.deptid and a.salary > b.avgsar group by a.deptid order by a.deptid

2. 什麼是預存程序？用什麼來調用？

預存程序是一個先行編譯的 SQL 語句，優點是允許模組化的設計，就是說只需建立一次，以後在該程式中就可以調用多次。如果某次操作需要執行多次 SQL ，使用預存程序比單純 SQL 語句執行要快。可以用一個命令對象來調用預存程序。

mysql> delimiter |

mysql> create procedure insertArticle_Procedure (pTitle varchar(50),pBid int,outpId int)

-> begin

-> insert into article1value(null,pTitle,pBid);

-> select max(id) into pId fromarticle1;

-> end;

-> |

Query OK, 0 rows affected (0.05sec)

mysql> callinsertArticle_Procedure(‘南方航空‘,1,@pid);

-> |

Query OK, 0 rows affected (0.00sec)

mysql> delimiter ;

3. MySQL中delimit命令

這個命令與預存程序沒什麼關係。

其實就是告訴mysql解譯器，該段命令是否已經結束了，mysql是否可以執行了。

4. 資料庫裡的觸發器

　　觸發器的定義就是說某個條件成立的時候，你觸發器裡面所定義的語句就會被自動的執行。因此觸發器不需要人為的去調用，也不能調用。一個表上最多可以有12個觸發器，但同一時間、同一事件、同一類型的觸發器只能有一個。還需要注意，各個觸發器之間不能有矛盾。在一個表上的觸發器越多，對在該表上的DML操作效能影響就越大。

　　觸發器用處還是很多的，比如校內網、開心網、Facebook，你發一個日誌，自動通知好友，其實就是在增加日誌時做一個後觸發， 再向通知表中寫入條目。 因為觸發器效率高。

create table board1(id intprimary key auto_increment,name varchar(50),articleCount int);

create table article1(id intprimary key auto_increment,title varchar(50),bid int referencesboard1(id));

delimiter |

create triggerinsertArticle_Trigger after insert on article1 for each row begin

-> update board1 setarticleCount=articleCount+1 where id= NEW.bid;

-> end;

-> |

delimiter ;

insert into board1 value(null,‘test‘,0);

insert into article1value(null,‘test‘,1);

5. 建立聯合主鍵

create table product(
pro_name varchar(20),
pro_type varchar(20),
primary key (pro_name,pro_type)
);

6. 什麼是主鍵?什麼是外鍵?

主鍵是表格裡的(一個或多個)欄位，只用來定義表格裡的行;主鍵裡的值總是唯一的。外鍵是一個用來建立兩個表格之間關係的約束。這種關係一般都涉及一個表格裡的主鍵欄位與另外一個表格(儘管可能是同一個表格)裡的一系列相連的欄位。那麼這些相連的欄位就是外鍵。

7. NULL是什麼意思?

NULL這個值表示UNKNOWN(未知):它不表示“”(Null 字元串)。

8. 什麼是事務？什麼是鎖？

事務就是被綁定在一起作為一個邏輯工作單元的 SQL 語句分組，如果任何一個語句操作失敗那麼整個操作就被失敗，以後操作就會復原到操作前狀態，或者是上有個節點。為了確保要麼執行，要麼不執行，就可以使用事務。要將有組語句作為事務考慮，就需要通過 ACID 測試，即原子性，一致性，隔離性和持久性。

鎖：在所以的 DBMS 中，鎖是實現事務的關鍵，鎖可以保證事務的完整性和並發性。與現實生活中鎖一樣，它可以使某些資料的擁有者，在某段時間內不能使用某些資料或資料結構。當然鎖還分層級的。

9. 什麼叫視圖？遊標是什嗎？

視圖是一種虛擬表，具有和物理表相同的功能。可以對視圖進行增，改，查，操作，試圖通常是有一個表或者多個表的行或列的子集。對視圖的修改不影響基本表。它使得我們擷取資料更容易，相比多表查詢。

遊標實際上是一種能從包括多條資料記錄的結果集中每次提取一條記錄的機制。遊標充當指標的作用。儘管遊標能遍曆結果中的所有行，但他一次只指向一行。
概括來講，SQL的遊標是一種臨時的資料庫物件，即可以用來存放在資料庫表中的資料行副本，也可以指向儲存在資料庫中的資料行的指標。遊標提供了在逐行的基礎上動作表中資料的方法。
遊標的一個常見用途就是儲存查詢結果，以便以後使用。遊標的結果集是由SELECT語句產生，如果處理過程需要重複使用一個記錄集，那麼建立一次遊標而重複使用若干次，比重複查詢資料庫要快的多。

10. 索引的作用？和它的優點缺點是什嗎？

索引就一種特殊的查詢表，資料庫的搜尋引擎可以利用它加速對資料的檢索。它很類似與現實生活中書的目錄，不需要查詢整本書內容就可以找到想要的資料。索引可以是唯一的，建立索引允許指定單個列或者是多個列。缺點是它減慢了資料錄入的速度，同時也增加了資料庫的尺寸大小。

11. Union 和 Union All 的區別

select *from studentwhere id < 4

union

select *from studentwhere id > 2 and id < 6

結果將是

1 Aaron 78

2 Bill 76

3 Cindy 89

4 Damon 90

5 Ella 73

如果換成 Union All 串連兩個結果集，則返回結果是：

1 Aaron 78

2 Bill 76

3 Cindy 89

3 Cindy 89

4 Damon 90

5 Ella 73

可以看到，Union 和 Union All 的區別之一在於對重複結果的處理。

UNION 在進行錶鏈接後會篩選掉重複的記錄，所以在錶鏈接後會對所產生的結果集進行排序運算，重複資料刪除的記錄再返回結果。實際大部分應用中是不會產生重複的記錄，而 UNION ALL 只是簡單的將兩個結果合并後就返回。這樣，如果返回的兩個結果集中有重複的資料，那麼返回的結果集就會包含重複的資料了。

從效率上說，UNION ALL 要比 UNION 快很多，所以，如果可以確認合并的兩個結果集

中不包含重複的資料的話，那麼就使用 UNION ALL。

12. 分頁語句

mysql 方案：select * from t order by idlimit 30,10

oracle 方案：select * from (select rownum r,* from t where r<=40) wherer>30

13. Class.forName  的作用? 為什麼要用?

按參數中指定的字串形式的類名去搜尋並載入相應的類， 如果該類位元組碼已經被載入過，則返回代表該位元組碼的 Class 執行個體對象，否則，按類載入器的委託機制去搜尋和載入該類，如果所有的類載入器都無法載入到該類，則拋出 ClassNotFoundException。載入完這個 Class 位元組碼後，接著就可以使用 Class 位元組碼的 newInstance 方法去建立該類的執行個體對象了。有時候，我們程式中所有使用的具體類名在設計時（即開發時）無法確定，只有程式運行時才能確定，這時候就需要使用 Class.forName 去動態載入該類，這個類名通常是在設定檔中配置的，例如，spring 的 ioc 中每次依賴注入的具體類就是這樣配置的，jdbc 的驅動類名通常也是通過設定檔來配置的， 以便在產品交付使用後不用修改來源程式就可以更換驅動類名。

14. 說出資料連線池的工作機制是什麼

J2EE 伺服器啟動時會建立一定數量的池串連，並一直維持不少於此數目的池串連。用戶端

程式需要串連時， 池驅動程式會返回一個未使用的池串連並將其表記為忙。 如果當前沒有空閑串連，池驅動程式就建立一定數量的串連，建立串連的數量有配置參數決定。當使用的池串連調用完成後，池驅動程式將此串連表記為空白閑，其他調用就可以使用這個串連。

實現方式，返回的 Connection 是原始 Connection 的代理，代理 Connection 的 close 方法

不是真正關串連，而是把它代理的 Connection 對象還回到串連池中。

15. JDBC  中的 PreparedStatement  相比 Statement

(a).PreparedStatement是先行編譯的,對於批量處理可以大大提高效率. 特別是如果帶有不同參數的同一SQL語句被多次執行的時候。

(b)可以防止 sql 注入。

16. 資料庫設計三大範式

為了建立冗餘較小、結構合理的資料庫，設計資料庫時必須遵循一定的規則。在關係型資料庫中這種規則就稱為範式。範式是符合某一種設計要求的總結。要想設計一個結構合理的關係型資料庫，必須滿足一定的範式。  

在實際開發中最為常見的設計範式有三個：

1．第一範式(確保每列保持原子性)

第一範式是最基本的範式。如果資料庫表中的所有欄位值都是不可分解的原子值，就說明該資料庫表滿足了第一範式。

第一範式的合理遵循需要根據系統的實際需求來定。比如某些資料庫系統中需要用到“地址”這個屬性，本來直接將“地址”屬性設計成一個資料庫表的欄位就行。但是如果系統經常會訪問“地址”屬性中的“城市”部分，那麼就非要將“地址”這個屬性重新拆分為省份、城市、詳細地址等多個部分進行儲存，這樣在對地址中某一部分操作的時候將非常方便。這樣設計才算滿足了資料庫的第一範式。

上表所示的使用者資訊遵循了第一範式的要求，這樣在對使用者使用城市進行分類的時候就非常方便，也提高了資料庫的效能。   

2．第二範式(確保表中的每列都和主鍵相關)

第二範式在第一範式的基礎之上更進一層。第二範式需要確保資料庫表中的每一列都和主鍵相關，而不能只與主鍵的某一部分相關（主要針對聯合主鍵而言）。也就是說在一個資料庫表中，一個表中只能儲存一種資料，不可以把多種資料儲存在同一張資料庫表中。

比如要設計一個訂單資訊表，因為訂單中可能會有多種商品，所以要將訂單編號和商品編號作為資料庫表的聯合主鍵。

這樣就產生一個問題：這個表中是以訂單編號和商品編號作為聯合主鍵。這樣在該表中商品名稱、單位、商品價格等資訊不與該表的主鍵相關，而僅僅是與商品編號相關。所以在這裡違反了第二範式的設計原則。

而如果把這個訂單資訊表進行拆分，把商品資訊分離到另一個表中，把訂單項目表也分離到另一個表中，就非常完美了。

這樣設計，在很大程度上減小了資料庫的冗餘。如果要擷取訂單的商品資訊，使用商品編號到商品資訊表中查詢即可。        

3．第三範式(確保每列都和主鍵列直接相關,而不是間接相關)

第三範式需要確保資料表中的每一列資料都和主鍵直接相關，而不能間接相關。

比如在設計一個訂單資料表的時候，可以將客戶編碼作為一個外鍵和訂單表建立相應的關係。而不可以在訂單表中添加關於客戶其它資訊（比如姓名、所屬公司等）的欄位。如下面這兩個表所示的設計就是一個滿足第三範式的資料庫表。

這樣在查詢訂單資訊的時候，就可以使用客戶編碼來引用客戶資訊表中的記錄，也不必在訂單資訊表中多次輸入客戶資訊的內容，減小了資料冗餘。

Mysql零碎知識點（今日所學）