SQL基礎語句

數據庫面試常見題

一、SQL語言包括數據定義語言、數據操作語言、數據控制語言和事務控制語言

1：DDL(Data Definition Language)，是用于描述數據庫中要存儲的現實世界實體的語言。?

• CREATE TABLE?- 創建新表

• ALTER TABLE?- 變更（改變）數據庫表

• DROP TABLE?- 刪除表

• CREATE INDEX?- 創建索引（搜索鍵）

• DROP INDEX?- 刪除索引

? ? ? 2：DML（Data Manipulation Language），用戶通過它可以實現對數據庫的基本操作

? ? ? ??SELECT?- 從數據庫表中獲取數據

? ? ? ? UPDATE?- 更新數據庫表中的數據

? ? ? ? DELETE?- 從數據庫表中刪除數據

? ? ? ? INSERT - 向數據庫表中插入數據

? ? ? 3:DCL

? ? ? ? GRANT它可以把語句權限或者對象權限授予其他用戶和角色。

? ? ? ? DENY它用于拒絕給當前數據庫內的用戶或者角色授予權限

? ? ? ? REVOKE它能夠將以前在當前數據庫內的用戶或者角色上授予或拒絕的權限刪除。

? ? ? ?4：TCL

? ? ? ? SAVEPOINT:“保存點”就像是一個標記，標記事務中的某個點以便將來可以回滾，用來將很長的事務劃分為若干個較小的事務，它與回滾一起使用以回滾當前事務部分；?

? ? ? ? ROLLBACK:用于事務出錯時回滾數據；?

? ? ? ? COMMIT:用于結束事務，對數據庫做永久性修改，擦除事務中的所有保存點。COMMIT語句表示所有SQL語句均已執行完畢，事務成功結束，數據庫處于一致狀態；?

?

二、關系數據庫設計范式：

第一范式：無重復的列

第二范式：非主屬性完全依賴主鍵

第三范式：每個非主屬性都不傳遞依賴主鍵；一個數據庫表中不包含已在其他表中包含的非主關鍵字信息，否則存在大量的數據冗余。

BCNF： ? 消除主屬性對關鍵字的部分和傳遞函數依賴

第四范式：在一個沒有單獨列被定義為唯一的主鍵的表中，用多個列組合一起被定義為唯一主鍵。

三、SQL語句需要注意的地方： ?

? ? ? 主碼約束 Primary Key ? ?唯一性約束 Unique ? 非空值約束 NULL

其中Primary Key與Unique的區別如下：primary key = unique + ?not null

? ? ? 1：作為Primary Key的域/域組不能為null，而Unique Key可以。?

? ? ? 2：在一個表中只能有一個Primary Key，而多個Unique Key可以同時存在。

distinct短語的作用范圍是所有目標列（相當于保證查詢結果的每一行數據都不重復，而不是每一個字段都不重復）

未對查詢結果分組，集函數將作用于整個查詢結果

對查詢結果分組后，集函數將分別作用于每個組?

Union all 和Union的區別與聯系

Union：對兩個結果集進行并集操作，不包括重復行，同時進行默認規則的排序；?
? ? ? ?Union All：對兩個結果集進行并集操作，包括重復行，不進行排序；?

四、索引的工作原理與類別

索引的優點：創建索引可以大大提高系統的性能。

? ? ? ?通過創建唯一性索引，可以保證數據庫表中每一行數據的唯一性。

? ? ? ?可以大大加快數據的檢索速度，這也是創建索引的最主要的原因。

? ? ? ?可以加速表和表之間的連接，特別是在實現數據的參考完整性方面特別有意義。

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

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

索引的代價：

? ? ? ?索引需要占用數據表以外的物理存儲空間

? ? ? ?創建索引和維護索引要花費一定的時間

? ? ? ?當對表進行更新操作時，索引需要被重建，這樣降低了數據的維護速度。

?

索引的類別：

普通索引：這是最基本的索引，它沒有任何限制。

唯一索引：與普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值（注意和主鍵不同）。如果是組合索引，則列值的組合必須唯一，創建方法和普通索引類似。

主鍵索引：主鍵是一種唯一性索引，但它必須指定為“PRIMARY KEY”。

全文索引：在MySQL中，全文索引的索引類型為FULLTEXT。全文索引可以在VARCHAR或者TEXT類型的列上創建。

全文檢索是對大數據文本進行索引，在建立的索引中對要查找的單詞進行進行搜索，定位哪些文本數據包括要搜索的單詞。

SQL SERVER中聚集索引與非聚集索引的區別：見http://www.cnblogs.com/aspnethot/articles/1504082.html

A）聚集索引，表數據按照索引的順序來存儲的。對于聚集索引，葉子結點即存儲了真實的數據行，不再有另外單獨的數據頁。

? ? ? ?B）非聚集索引，表數據存儲順序與索引順序無關。對于非聚集索引，葉結點包含索引字段值及指向數據頁數據行的邏輯指針，該層緊鄰數據頁，其行數量與數據表行數據量一致。?

? ? ? ?在一張表上只能創建一個聚集索引，因為真實數據的物理順序只可能是一種。如果一張表沒有聚集索引，那么它被稱為“堆集”（Heap）。這樣的表中的數據行沒有特定的順序，所有的新行將被添加的表的末尾位置。?

五、數據庫事務的四個特性以及隔離級別

? ? ? ?數據庫事務transanction正確執行的四個基本要素。ACID,原子性(Atomicity)、一致性(Correspondence)、隔離性(Isolation)、持久性(Durability)。

? ? ? ?原子性：整個事務中的所有操作，要么全部完成，要么全部不完成，不可能停滯在中間某個環節。事務在執行過程中發生錯誤，會被回滾（Rollback）到事務開始前的狀態，就像這個事務從來沒有執行過一樣。
? ? ? ?持久性:在事務完成以后，該事務所對數據庫所作的更改便持久的保存在數據庫之中，并不會被回滾。

?

? ? ? ?數據庫事務隔離級別：

? ? ? ?1：讀取未提交

? ? ? ?READ UNCOMMITTED是限制性最弱的隔離級別，因為該級別忽略其他事務放置的鎖。使用READ UNCOMMITTED級別執行的事務，可以讀取尚未由其他事務提交的修改后的數據值，這些行為稱為“臟”讀。這是因為在Read Uncommitted級別下，讀取數據不需要加S鎖，這樣就不會跟被修改的數據上的X鎖沖突。

? ? ? ?2：讀取已提交

? ? ? ?READ COMMITTED(Nonrepeatable reads)是SQL Server默認的隔離級別。該級別通過指定語句不能讀取其他事務已修改但是尚未提交的數據值，禁止執行臟讀。在當前事務中的各個語句執行之間，其他事務仍可以修改、插入或刪除數據，從而產生無法重復的讀操作，或“影子”數據。比如，事務1讀取了一行，事務2修改或者刪除這一行并且提交。如果事務1想再一次讀取這一行，它將獲得修改后的數據或者發現這一樣已經被刪除，因此事務的第二次讀取結果與第一次讀取結果不同，因此也叫不可重復讀。

? ? ? ?3：可重復讀

? ? ??REPEATABLE READ是比READ COMMITTED限制性更強的隔離級別。該級別包括READ COMMITTED，并且另外指定了在當前事務提交之前，其他任何事務均不可以修改或刪除當前事務已讀取的數據。并發性低于?READ COMMITTED，因為已讀數據的共享鎖在整個事務期間持有，而不是在每個語句結束時釋放。比如，事務1讀取了一行，事務2想修改或者刪除這一行并且提交，但是因為事務1尚未提交，數據行中有事務1的鎖，事務2無法進行更新操作，因此事務2阻塞。如果這時候事務1想再一次讀取這一行，它讀取結果與第一次讀取結果相同，因此叫可重復讀。

? ? ? ?4：串行讀取

? ? ? ?SERIALIZABLE?是限制性最強的隔離級別，因為該級別鎖定整個范圍的鍵，并一直持有鎖，直到事務完成。該級別包括REPEATABLE READ，并增加了在事務完成之前，其他事務不能向事務已讀取的范圍插入新行的限制。比如，事務1讀取了一系列滿足搜索條件的行。事務2在執行SQL statement產生一行或者多行滿足事務1搜索條件的行時會沖突，則事務2回滾。這時事務1再次讀取了一系列滿足相同搜索條件的行，第二次讀取的結果和第一次讀取的結果相同。

?

? ? ?隔離級別與鎖的關系：

1. 在Read Uncommitted級別下，讀操作不加S鎖；
2. 在Read Committed級別下，讀操作需要加S鎖，但是在語句執行完以后釋放S鎖；
3. 在Repeatable Read級別下，讀操作需要加S鎖，但是在事務提交之前并不釋放S鎖，也就是必須等待事務執行完畢以后才釋放S鎖。
4. 在Serialize級別下，會在Repeatable Read級別的基礎上，添加一個范圍鎖。保證一個事務內的兩次查詢結果完全一樣，而不會出現第一次查詢結果是第二次查詢結果的子集。

?

六、視圖的作用

? ? ?* 簡單性。

? ? ? ? 看到的就是需要的。視圖不僅可以簡化用戶對數據的理解，也可以簡化他們的操作。那些被經常使用的查詢可以被定義為視圖，從而使得用戶不必為以后的操作每次指定全部的條件。

? ? ?* 安全性。

? ? ? ?通過視圖用戶只能查詢和修改他們所能見到的數據。數據庫中的其它數據則既看不見也取不到。數據庫授權命令可以使每個用戶對數據庫的檢索限制到特定的數據庫對象上，但不能授權到數據庫特定行和特定的列上。通過視圖，用戶可以被限制在數據的不同子集上：

?????? 使用權限可被限制在基表的行的子集上。

?????? 使用權限可被限制在基表的列的子集上。

?????? 使用權限可被限制在基表的行和列的子集上。

?????? 使用權限可被限制在多個基表的連接所限定的行上。

?????? 使用權限可被限制在基表中的數據的統計匯總上。

?????? 使用權限可被限制在另一視圖的一個子集上，或是一些視圖和基表合并后的子集上。

? ? * 邏輯數據獨立性。視圖可幫助用戶屏蔽真實表結構變化帶來的影響。

?

? ? ? ? CREATE VIEW <視圖名>[(列名組)]

? ? ? ? ? ? ? ? ? ? ? ??AS?<子查詢>

? ? ? ? DROP VIEW <索引名>

?

? ? ? 參考的博客地址：

? ? ? http://zhanghao007008.blog.163.com/blog/static/44874122200932293642494/

?

七、數據庫的鎖機制

? ? ? 1.共享鎖

? ? ? 2.排他鎖

? ? ? 3.更新鎖：因為共享鎖與排他鎖是不能共存的，排他鎖只能等共享鎖釋放掉才可以。所以為了避免死鎖，引入了更新鎖機制。一個事物只能有一個更新鎖。更新鎖其實為了預定排它鎖而存在，更新鎖與排它鎖也是不能共存的，需要將更新鎖升級為排他鎖。

?

? ? ? 參考博客地址：

? ? ? http://www.cnblogs.com/zhouqianhua/archive/2011/04/15/2017049.html

?

八、存儲過程

? ? ? 一、存儲過程與函數的區別：

　　1.一般來說，存儲過程實現的功能要復雜一點，而函數的實現的功能針對性比較強。

　　2.對于存儲過程來說可以返回參數(output)，而函數只能返回值或者表對象。

　　3.存儲過程一般是作為一個獨立的部分來執行，而函數可以作為查詢語句的一個部分來調用，由于函數可以返回一個表對象，因此它可以在查詢語句中位于FROM關鍵字的后面。

?

? ? ? 二、存儲過程的優點：

　　1.執行速度更快 – 在數據庫中保存的存儲過程語句都是編譯過的

　　2.允許模塊化程序設計 – 類似方法的復用

　　3.提高系統安全性 – 防止SQL注入（攻擊者把SQL命令插入到Web表單的輸入域或者頁面請求的查詢字符中，欺騙服務器執行惡意的SQL命令）

　　4.減少網絡流通量 – 只要傳輸存儲過程的名稱，在服務器上運行

?

? ? ? 三、定義存儲過程語法，"[" ? 里面的內容表示可選項

　　create proc 存儲過程名

　　@參數1????????數據類型 [=默認值] [output],

　　@參數2????????數據類型 [=默認值] [output],

　　...

　　as

　　SQL語句

?

九、觸發器

? ? ? 觸發器可以分為語句級觸發器和行級觸發器。簡單的說就是語句級的觸發器可以在某些語句執行前或執行后被觸發。而行級觸發器則是在定義觸發的表中的行數據改變時就會被觸發一次。

? ? ? 觸發器是一種特殊的存儲過程。一般的存儲過程是通過存儲過程名直接調用，而觸發器主要是通過事件(增、刪、改)進行觸發而被執行的，在表中數據發生變化時自動強制執行。

　　常見的觸發器有兩種：after(for)、instead of,用于insert、update、delete事件。

　　after(for) ? ? ? ? 表示執行代碼后，執行觸發器

　　instead of????????表示執行代碼前，用已經寫好的觸發器代替你的操作

?

? ? ? 觸發器語法：

　　 ? ?create trigger 觸發器的名字 ? on 操作表

　　 ? ? ? ? ? ? ?for|after ????????instead of

　　 ? ? ? ? ? ? ?update|insert|delete

　　 ? ?as

　　 ? ? ? ? ? ? ?SQL語句

?

? ? ? 存儲過程與觸發器的區別：

? ? ? 觸發器與存儲過程非常相似，觸發器也是SQL語句集，兩者唯一的區別是觸發器不能用EXECUTE語句調用，而是在用戶執行事務語句時自動觸發（激活）執行。觸發器是在一個修改了指定表中的數據時執行的存儲過程。通常通過創建觸發器來強制實現不同表中的邏輯相關數據的引用完整性和一致性。觸發器不同于存儲過程，觸發器主要是通過事件執行觸發而被執行的，而存儲過程可以通過存儲過程名稱名字而直接調用。當對某一表進行諸如UPDATE、INSERT、DELETE這些操作時，會自動執行觸發器所定義的SQL語句，從而確保對數據的處理必須符合這些SQL語句所定義的規則。

?

十、數據庫引擎

? ? ?參考博客地址：http://www.cnblogs.com/sopc-mc/archive/2011/11/01/2232212.html

? ? ??Innodb引擎提供了對數據庫ACID事務的支持，并且實現了SQL標準的四種隔離級別。該引擎還提供了行級鎖和外鍵約束，它的設計目標是處理大容量數據庫系統，它本身其實就是基于MySQL后臺的完整數據庫系統，MySQL運行時Innodb會在內存中建立緩沖池，用于緩沖數據和索引。但是該引擎不支持FULLTEXT類型的索引，而且它沒有保存表的行數，當SELECT COUNT(*) FROM TABLE時需要掃描全表。當需要使用數據庫事務時，該引擎當然是首選。由于鎖的粒度更小，寫操作不會鎖定全表，所以在并發較高時，使用Innodb引擎會提升效率。但是使用行級鎖也不是絕對的，如果在執行一個SQL語句時MySQL不能確定要掃描的范圍，InnoDB表同樣會鎖全表。

?

? ? ? MyIASM是MySQL默認的引擎，但是它沒有提供對數據庫事務的支持，也不支持行級鎖和外鍵，因此當INSERT(插入)或UPDATE(更新)數據時即寫操作需要鎖定整個表，效率便會低一些。不過和Innodb不同，MyIASM中存儲了表的行數，于是SELECT COUNT(*) FROM TABLE時只需要直接讀取已經保存好的值而不需要進行全表掃描。如果表的讀操作遠遠多于寫操作且不需要數據庫事務的支持，那么MyIASM也是很好的選擇。

?

?

十一、關系數據庫與非關系數據庫的區別

? ? ?參考博客地址：http://blog.csdn.net/robinjwong/article/details/18502195/

? ? ?

? ? ? 關系型數據庫的最大特點就是事務的一致性：傳統的關系型數據庫讀寫操作都是事務的，具有ACID的特點，這個特性使得關系型數據庫可以用于幾乎所有對一致性有要求的系統中，如典型的銀行系統。

? ? ? 但是，在網頁應用中，尤其是SNS應用中，一致性卻不是顯得那么重要，用戶A看到的內容和用戶B看到同一用戶C內容更新不一致是可以容忍的，或者說，兩個人看到同一好友的數據更新的時間差那么幾秒是可以容忍的，因此，關系型數據庫的最大特點在這里已經無用武之地，起碼不是那么重要了。

? ? ? 相反地，關系型數據庫為了維護一致性所付出的巨大代價就是其讀寫性能比較差，而像微博、facebook這類SNS的應用，對并發讀寫能力要求極高，關系型數據庫已經無法應付(在讀方面，傳統上為了克服關系型數據庫缺陷，提高性能，都是增加一級memcache來靜態化網頁，而在SNS中，變化太快，memchache已經無能為力了)，因此，必須用新的一種數據結構存儲來代替關系數據庫。

? ? ? 關系數據庫的另一個特點就是其具有固定的表結構，因此，其擴展性極差，而在SNS中，系統的升級，功能的增加，往往意味著數據結構巨大變動，這一點關系型數據庫也難以應付，需要新的結構化數據存儲。

? ? ? 于是，非關系型數據庫應運而生，由于不可能用一種數據結構化存儲應付所有的新的需求，因此，非關系型數據庫嚴格上不是一種數據庫，應該是一種數據結構化存儲方法的集合。

? ? ?

? ? ?非關系型數據庫的分類：

? ? ?

• 面向高性能并發讀寫的key-value數據庫：key-value數據庫的主要特點即使具有極高的并發讀寫性能，Redis,Tokyo Cabinet,Flare就是這類的代表

• 面向海量數據訪問的面向文檔數據庫：這類數據庫的特點是，可以在海量的數據中快速的查詢數據，典型代表為MongoDB以及CouchDB

• 面向可擴展性的分布式數據庫：這類數據庫想解決的問題就是傳統數據庫存在可擴展性上的缺陷，這類數據庫可以適應數據量的增加以及數據結構的變化

?

?

十二、數據庫優化

? ? ?參考博客地址：http://blog.sina.com.cn/s/blog_76c0a1c50101d0gq.html? ? ?

常見的數據庫優化方法：索引（數據庫），緩存，分表，分庫，sql優化。

索引：創建索引一般有以下兩個目的：維護被索引列的唯一性和提供快速訪問表中數據的策略。索引有助于提高檢索性能，但過多或不當的索引也會導致系統低 效。因為用戶在表中每加進一個索引，數據庫就要做更多的工作。過多的索引甚至會導致索引碎片。?

分表：針對每個時間周期產生大量的數據，可以考慮采用一定的策略將數據存到多個數據表中。

分庫：就是將系統按照模塊相關的特征分布到不同的數據中，以提高系統整體負載能力。

sql優化：

1.in 和 not in 也要慎用，因為IN會使系統無法使用索引,而只能直接搜索表中的數據。如：

select id from t where num in(1,2,3)對于連續的數值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

2.當判斷真假是，如果帶and 或者 or :（當存在 “where 條件1 and 條件2” 時，數據庫先執行右邊的語句）

and盡量把假的放到右邊（一個為假就為假） ?Or盡量把為真的放到右邊（一個為真就為真）

3.應盡量避免在 where 子句中對字段進行表達式操作，這將導致引擎放棄使用索引而進行全表掃描。 如：

SELECT * FROM T1 WHERE F1/2=100?

應改為:?SELECT * FROM T1 WHERE F1=100*2

SELECT * FROM RECORD WHERE SUBSTRING(CARD_NO,1,4)=’5378’?

應改為:?SELECT * FROM RECORD WHERE CARD_NO LIKE ‘5378%’

SELECT member_number, first_name, last_name FROM members?WHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21?

應改為:?SELECT member_number, first_name, last_name FROM members?WHERE dateofbirth < DATEADD(yy,-21,GETDATE())?

即：任何對列的操作都將導致表掃描，它包括數據庫函數、計算表達式等等，查詢時 要盡可能將操作移至等號右邊。

4.很多時候用 exists是一個好的選擇：

如果你想校驗表里是否存在某條紀錄，不要用count(*)那樣效率很低，而且浪費服務 器資源。可以用EXISTS代替。如：?

IF (SELECT COUNT(*) FROM table_name WHERE column_name = 'xxx')?

可以寫成：?IF EXISTS (SELECT * FROM table_name WHERE column_name = 'xxx')

? ? ?5.充分利用連接條件，在某種情況下，兩個表之間可能不只一個的連接條件，這時在 WHERE 子句中將 連接條件完整的寫上，有可能大大提高查詢速度。?

SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO?

SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO AND A.ACCOUNT_NO=B.ACCOUNT_NO?

第二句將比第一句執行快得多。

6.使用視圖加速查詢?

把表的一個子集進行排序并創建視圖，有時能加速查詢。它有助于避免多重排序操作，而且在其他方面還能簡化優化器的工作。

視圖中的行要比主表中的行少，而且物理順序就是所要求的順序，減少了磁盤I/O，所以查詢工作量可以得到大幅減少。

EXISTS語法是說說exists后面的語句執行的結果是不是有記錄，只產生邏輯真值‘true’與邏輯假值‘False’。由EXISTS引出的子查詢，其目標列表達式通常都用*（用null也可以），因為帶有EXISTS的子查詢只返回真值或假值，給出列名沒有實際意義。

十三、分頁查詢

? ? ?參考博客地址：http://blog.csdn.net/guyong1018/article/details/2183057

? ? ?MySQL數據庫實現分頁比較簡單，提供了LIMIT函數。一般只需要直接寫到sql語句后面就行了。

LIMIT子句可以用來限制由SELECT語句返回過來的數據數量，它有一個或兩個參數，

如果給出兩個參數， 第一個參數指定返回的第一行在所有數據中的位置，從0開始（注意不是1），第二個參數指定最多返回行數。例如：

select * from table WHERE … LIMIT 10; #返回前10行

select * from table WHERE … LIMIT 0,10; #返回前10行

select * from table WHERE … LIMIT 10,20; #返回第10-20行數據