05 - MySQL - OpenWebSchool

Transcript

1. MySQL Ensky / 林宏昱
2. Why Database 你也可以選擇用 fopen + CSV 土法煉鋼，但很快的 你會遇到很多很多問題 … 林宏昱 , 小天 , 資工系 , 交通大學 張哲彬 , 變態 , 資工系 , 交通大學 你會發現，當多一點使用者的時候，兩個使用者 同時註冊 / 發表文章的時候你的資料就會毀掉。
3. Why Database flock! 沒錯，但以檔案為基礎的管理方式，速度非 常的慢，若純用檔案可能使用者 100 人就會花 很多時間在等待開鎖解鎖上了。 還有很多很多問題阿，比如說你突然要新增 一個欄位 email 此時用檔案系統的情況下，你 需要每一行都打開來檢查
4. Why Database 上面提到都是架構面的，而實際上使用者會 需要對資料庫問一些奇怪的問題： • 找出鳥彬的學期成績 這還好，怕的是 • 找出鳥彬平均 >90 的學期，最高的三門科 目的教授們，他們的實驗室同學有哪些 這種問題就需要關聯式資料庫來幫你囉！
5. Database Management System
6. Common DBMS
7. • 免費 • 功能完整 • 普及（大家都在用） • 我很熟 …XD
8. phpMyAdmin
9. 由於 Console 的操作方式實在不直覺 因此有人用 PHP 寫出一套管理 MySQL 的網站 叫做 phpMyAdmin 直接解壓縮到網頁根目錄就可以用了 :P phpMyAdmin
10. Architecture • 一台 DB server 可以開很多個 database ( 一個 project 一個 database 這樣 ) • 一個 database 可以有很多 table (ex: user, forum, …) • 一個 table 可以有很多 column (ex in user: name, sex, email, password, …)
11. 行 ＆ 列 姓名 姓名 姓名 姓名 性別 性別 性別 性別 綽號 綽號 綽號 綽號 林宏昱 Boy 小天 張哲彬 ? 啪啪啪 吳孟謙 女 腦 b Table 的基礎，就是行 (column) 與列 (row) column 代表不同的類別， row 代表一列一列的資料。
12. 資料型態 （DataType） • INT • VARCHAR • TEXT • DATE • DATETIME • BINARY • ENUM • SET 123 abcd “ 這是有很多字的文章 ” 2012-08-15 2012-08-15 14:00:00 010101010111110001 單選 複選
13. Live Demo : Create table
14. 索引 （ index ） Database 有一個技術，叫做 index ， 當一個 table 的某個 column 被加上了 index 之後， 他會被 database 建立索引， 用他找資料會很快。 比如說有個 table user: stdid, name, sex, email 我在學號上建立索引， 就可以很快的用學號找到人。
15. 索引 （ index ） 索引有分以下類別 • Primary key ( 主鍵 ) 資料中最具有代表性的一個欄位，唯一 ex: user 中的學號 特性：一定要有值、唯一 • Unique key ( 唯一鍵 ) 毫無反應，唯一的索引 • Index ( 索引鍵 ) 毫無反應，不唯一的索引
16. 索引 （ index ） 當設定成 Primary key 的時候，會有一個功能可 以用，就是 Auto Increment 。 有時候你可能會給每個 user 一個編號 (id) ，第 一個 user 編號是 1 ，第二個編號是 2 ，以此類推。 此時就可以將 id 這個 column 設定成 primary key ， 並且讓他有 auto increment 功能，此時每插入 一筆新的 user ， id 就會自動是 max(id) + 1
17. Live Demo : Setting Index
18. 關聯 有些時候， Table 之間會有一些關係 user user_skill id username name nickname 1 ensky 林宏昱 小天 2 Jalex 張哲彬 啪啪啪 3 fancy0305 羅宛琪 重訓一姊 user_id blood skill power 1 100 寫網頁 150 2 200 爆肝 100 3 50 重訓 10000
19. 關聯 有些時候， Table 之間會有一些關係 id username name nickname 1 ensky 林宏昱 小天 2 Jalex 張哲彬 啪啪啪 3 fancy0305 羅宛琪 重訓一姊 id name attack defense 1 寫網頁 50 50 2 爆肝 -50 -50 3 重訓 1000 1000 user_id skill_id 1 1 1 2 2 2 3 2 3 3 user_skill Skill User
20. 關聯 我們可以藉由設定 foreign – key 來建立兩個 table 之間的關係 條件： • 要建立關係的兩個 column 都必須是 index • 引擎必須是 InnoDB
21. Live Demo: foreign key setting
22. Structured Query Language SQL 是一個很特別的語言，他是專門設計來操 作 DBMS 的。 主要的操作方式，有 Select( 讀出來 ) 、 Update( 更新 ) 、 Insert( 插入資料 ) 、 Delete( 刪除 ) 不同的 DBMS ， SQL 語句會有點小小的不同， 但都大同小異，一個通其他的也會通。
23. Insert INSERT INTO `user` (`name`, `nick`) VALUES (" 林宏昱 ", " 天天 "), (" 張哲彬 ", " 啪啪啪 ")
24. Select SELECT * FROM `user` SELECT `username` FROM `user` SELECT `id`, `username` FROM `user` id username name nickname 1 ensky 林宏昱 小天 2 Jalex 張哲彬 啪啪啪 3 fancy0305 羅宛琪 重訓一姊 user username ensky Jalex fancy0305 id username 1 ensky 2 Jalex 3 fancy0305
25. Select Order By SELECT * FROM `user` ORDER BY username SELECT * FROM `user` ORDER BY username DESC id username name nickname 1 ensky 林宏昱 小天 2 Jalex 張哲彬 啪啪啪 3 fancy0305 羅宛琪 重訓一姊 user id username name nickname 1 ensky 林宏昱 小天 3 fancy0305 羅宛琪 重訓一姊 2 Jalex 張哲彬 啪啪啪 id username name nickname 2 Jalex 張哲彬 啪啪啪 3 fancy0305 羅宛琪 重訓一姊 1 ensky 林宏昱 小天
26. Select Where SELECT name FROM `user` WHERE `username` = "ensky" SELECT username, nickname FROM `user` WHERE `username` = "ensky" OR `username` = "Jalex" id username name nickname 1 ensky 林宏昱 小天 2 Jalex 張哲彬 啪啪啪 3 fancy0305 羅宛琪 重訓一姊 user name 林宏昱 username nickname ensky 小天 Jalex 啪啪啪
27. Select Limit SELECT * FROM `user` SELECT * FROM `user` LIMIT 1 SELECT * FROM `user` LIMIT 1, 2 SELECT * FROM `user` LIMIT 2, 1 id username name nickname 1 ensky 林宏昱 小天 2 Jalex 張哲彬 啪啪啪 3 fancy0305 羅宛琪 重訓一姊 id username name nickname 1 ensky 林宏昱 小天 id username name nickname 3 fancy0305 羅宛琪 重訓一姊 id username name nickname 2 Jalex 張哲彬 啪啪啪 3 fancy0305 羅宛琪 重訓一姊
28. Select Join id username nickname 1 ensky 小天 2 Jalex 啪啪啪 3 fancy0305 重訓一姊 id name 1 寫網頁 2 爆肝 3 重訓 user_id skill_id 1 1 1 2 2 2 3 2 3 3 user_skill User Skill SELECT * FROM `user_skill` JOIN User ON User.id = user_skill.id user_id skill_id User. id User. username User. nickname 1 1 1 ensky 小天 1 2 2 Jalex 啪啪啪 2 2 2 Jalex 啪啪啪 3 2 3 fancy0305 重訓一姊 3 3 3 fancy0305 重訓一姊
29. Select Distinct 假設現在我想撈 unique 的 username SELECT DISTINCT `username` FROM user1 username nickname ensky 小天 Jalex 啪啪啪 Jalex 啪啪啪 fancy0305 重訓一姊 fancy0305 重訓一姊 user1 username ensky Jalex fancy0305
30. Select SELECT SUM(`score`) FROM `score` SELECT SUM(`score`) AS `sum` FROM `score` SELECT MAX(`score`) AS `max` FROM `score` username score ensky 75 Jalex 85 hwchiu 90 crack1108 90 smartboy 10 score SUM(`score`) 350 sum 350 max 90
31. Select Count SELECT COUNT(*) FROM `user1` SELECT COUNT(*) AS `count` FROM `user1` SELECT COUNT(DISTINCT username) AS `count` FROM ` user1 ` COUNT(*) 5 count 5 max 3 username nickname ensky 小天 Jalex 啪啪啪 Jalex 啪啪啪 fancy0305 重訓一姊 fancy0305 重訓一姊 user1
32. Select subQuery SELECT * FROM `score` WHERE score = ( SELECT MAX(`score`) FROM `score` ) username score ensky 75 Jalex 85 hwchiu 90 crack1108 90 smartboy 10 score username score hwchiu 90 crack1108 90
33. Update UPDATE `score` SET username = "ensky" WHERE username = "enskyy" UPDATE `score` SET `score` = 85 WHERE username = "Jalex" UPDATE `score` SET `score` = `score` + 10 username score enskyy 75 Jalex 5 hwchiu 90 crack1108 90 smartboy 10 score
34. Delete DELETE FROM `user` WHERE nickname = " 啪啪啪 " id username nickname 1 ensky 小天 2 Jalex 啪啪啪 3 fancy0305 重訓一姊 User
35. Not Only SQL 關聯式資料好用歸好用，也不一定適合所有 case ，他也有一些缺點，如： • 速度很慢 • 擴展性不夠好 • 容錯率不夠高 • 要先定義欄位，對彈性的使用上不便
36. Not Only SQL 而這些缺點在百 TB 、甚至 PB 為單位的資料量 處理的時候就會浮現出來。 比方說 google 會將使用者「搜尋紀錄」以及 「最終連過去哪裡」存起來，這資料量是非 常龐大的，無法以傳統 Database 做儲存及操作。
37. Not Only SQL 也因此有很多「非關連式」的 Database 興起， 其中分為以下種類： • 文件式資料庫： – MongoDB, SimpleDB • Key-Value 儲存 – 最終一致性 – Cassandra – 硬碟 – BigTable, HBase – Ram – Redis, Memcached
38. Homework • http://vm2.ensky.tw/phpMyAdmin 1. 在 test database 裡面的 user table 裡 新增自己的資料（用介面） 2. 用 SQL 把自己的資料刪掉 3. 用 INSERT 新增自己的資料 用 UPDATE 把自己的 user.id 改成 user.id – 1 4. 用 INSERT 把 interest, user_interest 的資料新增完 5. 用 SELECT + JOIN 一次把三個 table 的資料讀出來