DataBase and MySQL - Speaker Deck

Slide 1

Slide 1 text

DataBase and MySQL Fundamental PHP & Web Development lecturer : Lucien Lee 李柏緯 Lecture 4

Slide 2

Slide 2 text

? What is Database Management System 2

Slide 3

Slide 3 text

Slide 4

Slide 4 text

Database 4 Simply you can think it as table in excel

Slide 5

Slide 5 text

5 from http://www.lhu.edu.tw

Slide 6

Slide 6 text

6 DBMS DataBase Table Record hierarchy

Slide 7

Slide 7 text

table 7 id name price 1 apple 50 2 banana 60 3 orange 70 4 grape 80 record ﬁeld

Slide 8

Slide 8 text

phpmyadmin 8 handle the administration of MySQL

Slide 9

Slide 9 text

Slide 10

Slide 10 text

! Something you need to know before create table 10

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

index how to determine different columns? 14

Slide 15

Slide 15 text

Key •Unique: no duplicate •Primary: only one in table, not NULL, ‘special’ unique key 15

Slide 16

Slide 16 text

Let’s try phpmyadmin! 16

Slide 17

Slide 17 text

? SQL 17

Slide 18

Slide 18 text

SQL 18 Structured Query Language

Slide 19

Slide 19 text

SELECT SELECT “Field” FROM “TABLE” WHERE “Condition” 19

Slide 20

Slide 20 text

example SElECT “Name” FROM “student” 20 ID Name 1 Albert 2 Bruce 3 Calla 4 Dana student

Slide 21

Slide 21 text

sql compare operator •= equal •< less than •> great than •<> NOT EQUAL 21

Slide 22

Slide 22 text

example SElECT “Name” FROM “student” WHERE “ID” %2=1 22 ID Name 1 Albert 2 Bruce 3 Calla 4 Dana student

Slide 23

Slide 23 text

ORDER BY •order result by rule. •ASC: from small to large •DESC: from large to small •ORDER BY `field` [ASC|DESC] 23

Slide 24

Slide 24 text

LIMIT •limit return how many record •Limit [from], [amount] 24

Slide 25

Slide 25 text

LIKE •search for a specified pattern •WHERE `field` LIke “%match%” 25

Slide 26

Slide 26 text

AS •temporarily rename a table or a column heading. •`field` as `rename` •also can use space:`field` `rename` 26

Slide 27

Slide 27 text

IN •give possible condition •WHERE grade IN (60, 70, 80) 27

Slide 28

Slide 28 text

BETWEEN •give a scope condition •WHERE grade BETWEEN 80 AND 90 28

Slide 29

Slide 29 text

count •count how many record •SELECT COUNT("field") 29

Slide 30

Slide 30 text

sum •sum up record in a field •SELECT SUM("field”) 30

Slide 31

Slide 31 text

JOIN •combine rows from two or more tables. •JOIN `TableName` ON condition 31

Slide 32

Slide 32 text

inner, outer join •Inner Join •Natural Join •Left Outer Join •Right Outer Join •Full Outer Join •Cross Join 32

Slide 33

Slide 33 text

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

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

Slide 39

Slide 39 text

TRY in phpmyadmin again! 39

Slide 40

Slide 40 text

other Method 40 •INSERT •UPDATE •DELETE

Slide 41

Slide 41 text

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

Slide 42

Slide 42 text

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