DataBase and MySQL

Fundamental PHP & Web Development

Transcript

1. DataBase and MySQL Fundamental PHP & Web Development lecturer : Lucien Lee 李柏緯 Lecture 4
2. ? What is Database Management System 2
4. Database 4 Simply you can think it as table in excel
5. 5 from http://www.lhu.edu.tw
6. 6 DBMS DataBase Table Record hierarchy
7. table 7 id name price 1 apple 50 2 banana 60 3 orange 70 4 grape 80 record field
8. phpmyadmin 8 handle the administration of MySQL
10. ! Something you need to know before create table 10
11. 11 type size scope TINYINT 1 byte –128 to 127 INT 4 byte –2147483648 to 2147483647 BIGINT 8 byte –9223372036854775808 to 9223372036854775807 FLOAT 4 or 8 byte –3.402823466E+38 to –1.175494351E-38 DOUBLE 8 byte -1.7976931348623157E+308 to -2.2250738585072014E-308, number
12. 12 type size scope DATE 3 byte 1000-01-01~ 9999-12-31 DATETIME 8 byte 1000-01-01 00:00:00~ 9999-12-31 23:59:59 TIMESTAMP 4 byte 1970-01-01 00:00:00~ 2038-01-09 03:14:07 TIME 3 byte -838:59:59 ~ 838:59:59 YEAR 1 byte 1901~2155 & 0000 date
13. 13 type size scope CHAR M*V byte 0~255 characters VARCHAR L+1(2) byte 0~65535 characters TEXT L+2 byte 2^16-1 characters LONGTEXT L+4 byte 2^32-1 characters string
14. index how to determine different columns? 14
15. Key •Unique: no duplicate •Primary: only one in table, not NULL, ‘special’ unique key 15
16. Let’s try phpmyadmin! 16
17. ? SQL 17
18. SQL 18 Structured Query Language
19. SELECT SELECT “Field” FROM “TABLE” WHERE “Condition” 19
20. example SElECT “Name” FROM “student” 20 ID Name 1 Albert 2 Bruce 3 Calla 4 Dana student
21. sql compare operator •= equal •< less than •> great than •<> NOT EQUAL 21
22. example SElECT “Name” FROM “student” WHERE “ID” %2=1 22 ID Name 1 Albert 2 Bruce 3 Calla 4 Dana student
23. ORDER BY •order result by rule. •ASC: from small to large •DESC: from large to small •ORDER BY `field` [ASC|DESC] 23
24. LIMIT •limit return how many record •Limit [from], [amount] 24
25. LIKE •search for a specified pattern •WHERE `field` LIke “%match%” 25
26. AS •temporarily rename a table or a column heading. •`field` as `rename` •also can use space:`field` `rename` 26
27. IN •give possible condition •WHERE grade IN (60, 70, 80) 27
28. BETWEEN •give a scope condition •WHERE grade BETWEEN 80 AND 90 28
29. count •count how many record •SELECT COUNT("field") 29
30. sum •sum up record in a field •SELECT SUM("field”) 30
31. JOIN •combine rows from two or more tables. •JOIN `TableName` ON condition 31
32. inner, outer join •Inner Join •Natural Join •Left Outer Join •Right Outer Join •Full Outer Join •Cross Join 32
34. 34 Student ID Name 1 Lucien 2 Andy 4 Eva Phone ID Student ID Phone 1 1 0912-111-222 2 2 0934-333-444 3 5 0956-555-666 We have these
35. Inner Join 35 Student ID Name Phone ID Student ID Phone 1 Lucien 1 1 0912-111-222 2 Andy 2 2 0934-333-444
36. Left Outer 36 Student ID Name Phone ID Student ID Phone 1 Lucien 1 1 0912-111-222 2 Andy 2 2 0934-333-444 4 Eva Null Null Null
37. Right Outer 37 Student ID Name Phone ID Student ID Phone 1 Lucien 1 1 0912-111-222 2 Andy 2 2 0934-333-444 Null Null 3 5 0956-555-666
38. Full Outer 38 Student ID Name Phone ID Student ID Phone 1 Lucien 1 1 0912-111-222 2 Andy 2 2 0934-333-444 Null Null 3 5 0956-555-666 4 Eva Null Null Null
39. TRY in phpmyadmin again! 39
40. other Method 40 •INSERT •UPDATE •DELETE
41. practice 41 • create a table to save student data like example in textbook. • try to do: • insert a new record • count how many students’ grade over 250 • sum score of every subject • select failed student • delete student where math under 60
42. 42 Student ID Name Math Science Art 1 Lucien 89 83 87 2 Andy 36 85 99 3 Eva 65 59 88 4 Cindy 71 64 78 5 Julia 90 37 82 6 Lisa 95 88 83 7 Bella 60 58 86 8 Sandy 23 89 96 Grades