第四章 MYSQL基礎知識考察點 · PHP面試之道

[TOC] ## 字段類型 ### 整數類型 `TINYINT、 SMALLINT、 MEDIUMINT、INT、 BIGINT` 屬性: `UNSIGNED` 長度:可以為整數類型指定寬度,例如:INT(11)、對大多數應用是沒有意義的,它不會限制值的合法范圍,只會影響顯示字符的個數 > int(3) 可以存入值為1234567 ,且不會切斷, > 設置長度是為了如果設置了 `Zerofill` 存入123 會變為 0123, > 所以 int(0) 也是具有意義的 ### 實數類型 `FLOAT、 DOUBLE、 DECIMAL` DECIMAL可存儲比 BIGINT還大的整數;可以用于存儲精確的小數,相當于存為字符串 FLOAT和 DOUBLE類型支持使用標準的浮點進行近似計算 ### 字符串類型 `VARCHAR、CHAR、TEXT、BLOB` VARCHAR類型用于存儲可變長字符串,它比定長類型更節省空間 VARCHAR使用1或2個額外字節記錄字符串的長度,列長度小于 255字節,使用1個字節表示,否則用2個 > `char` 定長 ,如 `char(10)` 不管你存入多長, 都占用10 個字節,用于存儲定長的字符串 > `varchar` 與 `char` 會切斷長度長度的字符串 ,超過255字節的只能用`varchar`或者`text`； > 如果存儲值很短或定長,可以選擇` char` > 能用`varchar`的地方不用`text`； ### 枚舉 `create table user_sex( sex enum('M','F'));` 有時可以使用枚舉代替常用的字符串類型把不重復的集合存儲成一個預定義的集合非常緊湊,把列表值壓縮到一個或兩個字節部存儲的是整數 ### 日期和時間類型盡量使用 `TIMESTAMP`,比 `DATETIME`空間效率高用整數保存時間戳的格式通常不方便處理如果需要存儲微秒,可以使用 bigint存儲 ``` date 1000-01-01 ~ 9999-12-31 datetime 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 timestamp 1970-01-01 00:00:01 ~ 2038-01-19 03:14:07 ``` ## 數據表引擎 ### Innodb表引擎默認事務型引擎,最重要最廣泛的存儲引擎,性能非常優秀數據存儲在共享表空間,可以通過配置分開對主鍵查詢的性能高于其他類型的存儲引擎內部做了很多優化,從磁盤讀取數據時自動在內存構建hash索引插入數據時自動構建插入緩沖區通過一些機制和工具支持真正的熱備份支持崩潰后的安全恢復支持行級鎖支持外鍵 ### MYISAM表引擎 51版本前, MYISAM是默認的存儲引擎擁有全文索引l、壓縮、空間函數表鎖,不支持事務和行級鎖,不支持崩潰后的安全恢復表存儲在兩個文件,MYD和MYI 設計簡單,某些場景下性能很好 ### 其他表引擎 Archive、 Blackhole、csV、 Memory ## 鎖機制表鎖是日常開發當中常見的問題,當多個查詢同一時刻進行數據修改時,就會產生并發控制的問題 ### 共享鎖(讀鎖)和排他鎖(寫鎖) #### 讀鎖共享的,不堵塞,多個用戶可以同時讀一個資源,互不干擾 #### 寫鎖排他的,一個寫鎖會阻塞其他的寫鎖和讀鎖,這樣可以只允許一人進行寫入,防止其他用戶讀取正在寫入的資源。 ### 鎖粒度 - 表鎖,系統性能開銷最小,會鎖定整張表, MYISAM使用表鎖 - 行鎖,最大程度地支持并發處理,但是也帶來了最大的鎖開銷,Innodb實現行級鎖 ## 事務處理 MYSQL提供事務處理的表引|擎, Inno DB 服務器層不管理事務,由下層的引擎實現,所以同一個事務中,使用多種存儲引擎不靠譜在非事務的表上執行事務操作 MYSQL不會發出提醒,也不會報錯 ## 存儲過程為以后的使用而保存的一條或多條 MYSQL語句的集合存儲過程就是有業務邏輯和流程的集合可以在存儲過程中創建表,更新數據,刪除等等 ## 觸發器提供給程序員和數據分析員來保證數據完整性的一種方法,它是與表事件相關的特殊的存儲過程 ### 使用場景可通過數據庫中的相關表實現級聯更改實時監控某張表中的某個字段的更改而需要做出相應的處理某些業務編號的生成等濫用會造成數據庫及應用程序的維護困難 > [MySQL 視圖、函數、存儲過程和觸發器簡書詳解](https://www.jianshu.com/p/814d8aee700a) ## 索引的基礎和類型 ### 索引對性能的影響大大減少服務器需要掃描的數據量幫助服務器避免排序和臨時表將隨機I/O變順序I/O 大大提高查詢速度,降低寫的速度、占用磁盤 ### 索引的使用場景對于非常小的表,大部分情況下全表掃描效率更高特大型的表,建立和使用索引的代價將隨之增長,可以使用分區技術來解決 ### 索引的類型索引|有很多種類型,都是實現在存儲引擎層的 - 普通索引:最基本的索引,沒有任何約束限制 - 唯一索引:與普通索引類似,但是具有唯一性約束 - 主鍵索引:主鍵可以與外鍵構成參照完整性約束,防止數據不一致 - 組合索引:將多個列組合在一起創建索引,可以覆蓋多個列 - 外鍵索引:只有 Innodb類型的表才可以使用外鍵索引,保證數據的一致性、完整性和實現級聯操作 - 全文索引: MYSQL自帶的全文索引只能用于 MYISAM,并且只能對英文進行全文檢索 ### 創建索引的原則 1. 最適合索引的列是出現在 WHERE子句中的列,或連接子句中的列而不是出現在 SELECT關鍵字后的列 2. 索引列的基數越大,索引的效果越好 3. 對字符串進行索引,應該制定一個前綴長度,可以節省大量的索引空間 4. 根據情況創建復合索引,復合索引可以提高查詢效率 5. 避免創建過多索引,索引|會額外占用磁盤空間,降低寫操作效率 6. 主鍵盡可能選擇較短的數據類型,可以有效減少索引的磁盤占用提高查詢效率 ### 索引的注意事項 1. 復合索引遵循前綴原則 ``` 創建的索引有順序,非順序的索引不生效 KEY(a, b, c) //創建 a b c 索引 WHERE a=l and b=2 and c= 3 //生效 WHERE a=1 and b=2 //生效 WHERE a= 1 //生效 WHERE b=2 andc= 3 //無效 WHERE a= 1 and c=3 //無效 ``` 2. like查詢,%在前,則素以失效,可以使用全文索引 3. column is null可以使用索引 `where name=null //也是可以使用索引` 4. 如果 MYSQL估計使用索引比全表掃描更慢,會放棄使用索引 `如果只有100 條數據 where id 1>and id<100 會自動轉為全表索引` 5. 如果or前的條件中的列有索引,后面的沒有,索引都不會被用到 `where name='cpj' or age='12'` 7. 列類型是字符串,查詢時一定要給值加引|號,否則索引失效 ``` name varchar(16) 存了 "100" Where name =100 //雖然能得到值但是沒有使用索引 ``` ## 六種關聯查詢 ### 交叉連接 CROSS JOIN ``` SELECT* FROM A,B(C)或者 SELECT* FROM A CROSS JOIN B(CROSS JOIN C //沒有任何關聯條件,結果是笛卡爾積,結果集會很大,沒有意義,很少使用 ``` ### 內連接 INNER JOIN ``` SELECT* FROM A, B WHERE A id=Bd或者 SELECT FROM A INNER JOIN B ON A.id=Bid ``` #### 內連接分為三類 - 等值連接: ON Aid=Bid - 不等值連接: ON A id>B.d - 自連接: `SELECT* FROM A T1 INNER JOIN A T2 on T1.id=T2. pid` - ### 外連接 LEFT JOIN/ RIGHT JOIN #### 左外連接 LEFT OUTER JOIN,以左表為主,先查詢出左表,按照ON后的關聯條件匹配右表,沒有匹配到的用NULL填充,可以簡寫成 LEFT JOIN #### 右外連接 RIGHT OUTER JOIN,以右表為主,先查詢出右表,按照ON后的關聯條件匹配左表,沒有匹配到的用NULL填充,可以簡寫成 RIGHT JOIN ### 聯合查詢 UNION與 UNION ALL `SELECT* FROM A UNION SELECT* FROM B UNION··` 就是把多個結果集集中在一起, UNION前的結果為基準,需要注意的是聯合查詢的列數要相等,相同的記錄行會合并 UNION ALL不會合并重復的結果集 ### 嵌套語句用一條SQL語句的結果作為另外一條SQL語句的條件 `SELECT FROM A WHERE id IN(SELECT id FROM B)` ## 真題 1. 更新 b 表的 c1 c2 到 A 表的 c1 c2 ``` A(id, sex, par, cl, c2) B(id, age, cl, c2) //方法一 update a, b set A.cl= B.cl, A.c2=B.c2 where A.id = B.id and B.age >50 //方法二 update a inner join B on Aid B id set A.c1=B.c1,A.c2= B.c2 where Bage >50 ```