6月11日 python複習 mysql

標籤：mys   函數名   方便   鎖定   資料庫索引   cat   concat   需要   常用   

01. 列舉常見的關係型資料庫和非關係型都有那些？

1.關係型資料庫通過外部索引鍵關聯來建立表與表之間的關係，---------常見的有：SQLite、Oracle、mysql

2.非關係型資料庫通常指資料以對象的形式儲存在資料庫中，而對象之間的關係通過每個對象自身的屬性來決定 ---常見的有：MongoDb、redis

02. MySQL常見資料庫引擎及比較？　　

   MyISAM：預設的MySQL外掛程式式儲存引擎，它是在Web、資料倉儲和其他應用環境下最常使用的儲存引擎之一。

· InnoDB：用於交易處理應用程式，具有眾多特性，包括ACID事務支援。(提供行級鎖)

· BDB：可替代InnoDB的事務引擎，支援COMMIT、ROLLBACK和其他事務特性。

· Memory：將所有資料儲存在RAM中，在需要快速尋找引用和其他類似資料的環境下，可提供極快的訪問。

· Merge：允許MySQL DBA或開發人員將一系列等同的MyISAM表以邏輯方式組合在一起，並作為1個對象引用它們。對於諸如資料倉儲等VLDB環境十分適合。

· Archive：為大量很少引用的曆史、歸檔、或安全審計資訊的儲存和檢索提供了完美的解決方案。

· Federated：能夠將多個分離的MySQL伺服器連結起來，從多個物理伺服器建立一個邏輯資料庫。十分適合於分布式環境或資料集市環境。

· Cluster/NDB：MySQL的簇式資料庫引擎，尤其適合於具有高效能尋找要求的應用程式，這類尋找需求還要求具有最高的正常工作時間和可用性。 · Other：其他儲存引擎包括CSV（引用由逗號隔開的用作資料庫表的檔案），Blackhole（用於臨時禁止對資料庫的應用程式輸入），以及Example引擎（可為快速建立定製的外掛程式式儲存引擎提供協助）。

一般來說不使用事務的話，請使用MyISAM引擎，使用事務的話，一般使用InnoDB ------仙子啊學習現在我們用的主要是InnoDB 注意，通過更改STORAGE_ENGINE組態變數，能夠方便地更改MySQL伺服器的預設儲存引擎。

03. 簡述資料三大範式？

什麼是範式：簡言之就是，資料庫設計對資料的儲存效能，還有開發人員對資料的操作都有莫大的關係。所以建立科學的，規範的的資料庫是需要滿足一些 規範的來最佳化資料資料存放區方式。在關係型資料庫中這些規範就可以稱為範式。

什麼是三大範式：

第一範式：當關係模式R的所有屬性都不能在分解為更基本的資料單位時，稱R是滿足第一範式的，簡記為1NF。滿足第一範式是關係模式正常化的最低要 求，否則，將有很多基本操作在這樣的關係模式中實現不了。

第二範式：如果關係模式R滿足第一範式，並且R得所有非主屬性都完全依賴於R的每一個候選關鍵屬性，稱R滿足第二範式，簡記為2NF。

第三範式：設R是一個滿足第一範式條件的關係模式，X是R的任意屬性集，如果X非傳遞依賴於R的任意一個候選關鍵字，稱R滿足第三範式，簡記為3NF. 註：關係實質上是一張二維表，其中每一行是一個元組，每一列是一個屬性

 

04. 什麼是事務？MySQL如何支援事務？　　

事務就是一段sql 語句的批處理，但是這個批處理是一個原子 ，不可分割，要麼都執行，要麼復原（rollback）都不執行。

事務具體四大特性，也就是經常說的ACID ：

1.原子性（所有操作要麼全部成功，要麼全部失敗復原）

2.一致性（事務執行之前和執行之後都必須處於一致性狀態。）

3.隔離性（資料庫為每一個使用者開啟的事務，不能被其他事務的操作所幹擾，多個並發事務之間要相互隔離）

4.持久性（一個事務一旦被提交了，那麼對資料庫中的資料的改變就是永久性的，即使遭遇故障依然能夠通過日誌恢複最後一次更新） 在 MySQL 中只有使用了 Innodb 資料庫引擎的資料庫或表才支援事務

MYSQL 交易處理主要有兩種方法：

1、用 BEGIN, ROLLBACK, COMMIT來實現 BEGIN 開始一個事務 ROLLBACK 交易回復 COMMIT 事務確認

2、直接用 SET 來改變 MySQL 的自動認可模式: SET AUTOCOMMIT=0 禁止自動認可 SET AUTOCOMMIT=1 開啟自動認可

05. 簡述資料庫設計中一對多和多對多的應用情境？　　

一對多：學生與班級---一個學生只能屬於一個班級，一個班級可以有多個學生

多對多：學生與課程---一個學生可以選擇多個課程，一個課程也可以被多個學生選擇

06. 如何基於資料庫實現商城商品計數器？

建立一個商城表---包含（id，商品名，每一個商品對應數量）

create table product

　　(id primary key auto_increment,

　　　 pname varchar(64),

　　　　 pcount int);

 

07. 簡述觸發器、函數、視圖、預存程序？

觸發器：觸發器是一個特殊的預存程序，它是MySQL在insert、update、delete的時候自動執行的代碼塊。

     create trigger trigger_name

　　 after/before insert /update/delete on 表名

　　 for each row

　　 begin

　　 sql語句：（觸發的語句一句或多句）

　　 end

 函數：MySQL中提供了許多內建函數，還可以自訂函數（實現程式員需要sql邏輯處理）

　　 自訂函數建立文法：

　　　　   建立：CREATE FUNCTION 函數名稱(參數列表) 　

　　　       RETURNS 傳回值類型 　函數體

 　　修改： ALTER FUNCTION 函數名稱 [characteristic ...]

　　 刪除：DROP FUNCTION [IF EXISTS] 函數名稱

　　 調用：SELECT 函數名稱(參數列表)

　　

　　 視圖：視圖是由查詢結果形成的一張虛擬表，是表通過某種運算得到的一個投影

　　 　　 create view view_name as select 語句

　　 預存程序：把一段代碼封裝起來，當要執行這一段代碼的時候，可以通過調用該預存程序來實現（經過第一次編譯後再次調用不需要再次編譯，比一個個執行sql語句效率高）

　　 create procedure 預存程序名(參數,參數,…)

　　 begin

　　 //代碼

　　 end

 

08. MySQL索引種類

索引是一種特殊的檔案(InnoDB資料表上的索引是資料表空間的一個組成部分)，更通俗的說，資料庫索引好比是一本書前面的目錄，能加快資料庫的查詢速度

MySQL索引的類型：

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

　　 2.唯一索引：索引列的值必須唯一，但允許有空值，如果是複合式索引，則列值的組合必須唯一

　　 3.全文索引：全文索引僅可用於 MyISAM 表，可以從CHAR、VARCHAR或TEXT列中作為CREATE TABLE語句的一部分被建立，或是隨後使用ALTER TABLE 或CREATE INDEX被添加 （切記對於大容量的資料表，產生全文索引是一個非常消耗時間非常消耗硬碟空間的做法）

　　 4. 單列索引、多列索引：多個單列索引與單個多列索引的查詢效果不同，因為執行查詢時，MySQL只能使用一個索引，會從多個索引中選擇一個限制最為嚴格的索引。

　　 5.複合式索引（最左首碼）：簡單的理解就是只從最左面的開始組合（實在單列索引的基礎上進一步壓榨索引效率的一種方式）

09. 索引在什麼情況下遵循最左首碼的規則？　　

 

 

10. 主鍵和外鍵的區別？　　

1.主鍵是能確定一條記錄的唯一標識

2.外鍵用於與另一張表的關聯，是能確定另一張表記錄的欄位，用於保持資料的一致性

11. MySQL常見的函數？　　

彙總函式：

　　 AVG(col)返回指定列的平均值

　　 COUNT(col)返回指定列中非NULL值的個數

　　 MIN(col)返回指定列的最小值

　　 MAX(col)返回指定列的最大值

　　 SUM(col)返回指定列的所有值之和

　　 GROUP_CONCAT(col) 返回由屬於一組的列值串連組合而成的結果

數學函數：

　　 ABS(x) 返回x的絕對值

　　 BIN(x) 返回x的二進位（OCT返回八進位，HEX返回十六進位）

12. 列舉 建立索引但是無法命中索引的8種情況。　　

1.如果條件中有or，即使其中有條件帶索引也不會使用(這也是為什麼盡量少用or的原因)

2.對於多列索引，不是使用的第一部分，則不會使用索引

3.like查詢是以%開頭

4.如果列類型是字串，那一定要在條件中將資料使用引號引用起來,否則不使用索引

5.如果mysql估計使用全表掃描要比使用索引快,則不使用索引

6 對小表查詢

7 提示不使用索引

8 統計資料不真實

9.單獨引用複合索引裡非第一位置的索引列.

13. 如何開啟慢日誌查詢？　　

1 執行 SHOW VARIABLES LIKE “%slow%”，獲知 mysql 是否開啟慢查詢 slow_query_log 慢查詢開啟狀態 OFF 未開啟 ON 為開啟 slow_query_log_file 慢查詢日誌存放的位置（這個目錄需要MySQL的運行帳號的可寫入權限，一般設定為MySQL的資料存放目錄）

2 修改設定檔（ 放在[mysqld]下），重啟 long_query_time 查詢超過多少秒才記錄

3 測試是否成功

4 慢查詢記錄檔的資訊格式

14. 資料庫匯入匯出命令（結構+資料）？　　　　

1.匯出整個資料庫 　

　mysqldump -u使用者名稱 -p密碼 資料庫名 > 匯出的檔案名稱 　　

例如：C:\Users\jack> mysqldump -uroot -pmysql sva_rec > e:\sva_rec.sql 　

　2.匯出一個表，包括表結構和資料 　

　mysqldump -u使用者名稱 -p 密碼 資料庫名 表名> 匯出的檔案名稱 　　

例如：C:\Users\jack> mysqldump -uroot -pmysql sva_rec date_rec_drv> e:\date_rec_drv.sql 　

　3.匯出一個資料庫結構 　

　例如：C:\Users\jack> mysqldump -uroot -pmysql -d sva_rec > e:\sva_rec.sql

4.匯出一個表，只有表結構 　

　mysqldump -u使用者名稱 -p 密碼 -d資料庫名 表名> 匯出的檔案名稱 　　

  例如：C:\Users\jack> mysqldump -uroot -pmysql -d sva_rec date_rec_drv> e:\date_rec_drv.sql 　　

5.匯入資料庫 　　

常用source 命令 　　

進入mysql資料庫控制台， 　　

如mysql -u root -p 　　mysql>use 資料庫 　　

然後使用source命令，後面參數為指令檔(如這裡用到的.sql) 　　

mysql>source d:wcnc_db.sql

15. 資料庫最佳化方案？

總體思路從以下幾個方面：

1、選取最適用的欄位屬性

2、使用串連（JOIN）來代替子查詢(Sub-Queries)

3、使用聯合(UNION)來代替手動建立的暫存資料表

4、事務（當多個使用者同時使用相同的資料來源時，它可以利用鎖定資料庫的方法來為使用者提供一種安全的訪問方式，這樣可以保證使用者的操作不被其它的使用者所幹擾）

5.鎖定表（有些情況下我們可以通過鎖定表的方法來獲得更好的效能）

6、使用外鍵（鎖定表的方法可以維護資料的完整性，但是它卻不能保證資料的關聯性。這個時候我們就可以使用外鍵）

7、使用索引

8、最佳化的查詢語句（絕大多數情況下，使用索引可以提高查詢的速度，但如果SQL語句使用不恰當的話，索引將無法發揮它應有的作用）

16. char和varchar的區別？

char：定長，char的存取數度相對快

varchar：不定長，存取速度相對慢

到底如何取捨可以根據一下幾個方面考慮：

1、對於MyISAM表，盡量使用Char，對於那些經常需要修改而容易形成片段的myisam和isam資料表就更是如此，它的缺點就是佔用磁碟空間；

2、對於InnoDB表，因為它的資料行內部儲存格式對固定長度的資料行和可變長度的資料行不加區分（所有資料行共用一個表頭部分，這個標題部分存放著指向各有關資料列的指標）， 所以使用char類型不見得會比使用varchar類型好。事實上，因為char類型通常要比varchar類型佔用更多的空間，所以從減少空間佔用量和減少磁碟i/o的角度，使用varchar類型反而更有利；

3、儲存很短的資訊，比如門牌號碼101，201……這樣很短的資訊應該用char，因為varchar還要佔個byte用於儲存資訊長度，本來打算節約儲存的現在得不償失。

4、固定長度的。比如使用uuid作為主鍵，那用char應該更合適。因為他固定長度，varchar動態根據長度的特性就消失了，而且還要佔個長度資訊。

5、十分頻繁改變的column。因為varchar每次儲存都要有額外的計算，得到長度等工作，如果一個非常頻繁改變的，那就要有很多的精力用於計算，而這些對於char來說是不需要的。

 

17. 簡述MySQL的執行計畫？

執行計畫explain命令是查看查詢最佳化工具如何決定執行查詢的主要方法。

這個功能有局限性，並不總會說出真相，但它的輸出是可以擷取的最好資訊，通過輸出結果反推執行過程

18. 在對name做了唯一索引前提下，簡述以下區別：

select * from tb where name = ‘Oldboy’ -------------尋找到tb表中所有name = ‘Oldboy’的資料

select * from tb where name = ‘Oldboy’ limit 1------尋找到tb表中所有name = ‘Oldboy’的資料只取其中的第一條

 

6月11日 python複習 mysql