Slide 1

Slide 1 text

Slide 2

Slide 2 text

Slide 3

Slide 3 text

! !

Slide 4

Slide 4 text

%! $'" #( & ()

Slide 5

Slide 5 text

EL C l X l C N D P l AQ S C I I I AQ

Slide 6

Slide 6 text

Slide 7

Slide 7 text

&%# ("'"$ ! '

Slide 8

Slide 8 text

l l l l l / l /

Slide 9

Slide 9 text

!%0 ! %0 l 75)+42 l #1-&( l ' *3 $,6./ "

Slide 10

Slide 10 text

Slide 11

Slide 11 text

A C C l l l D I

Slide 12

Slide 12 text

l

Slide 13

Slide 13 text

23 23 23 23 23 23 51 . 0 4

Slide 14

Slide 14 text

0 0 0 0 0 0 2.34 1 10

Slide 15

Slide 15 text

! = A l ( ) A )

Slide 16

Slide 16 text

1 B2 A B l 5 B l 9 0 l 9 l 9 9 5 93

Slide 17

Slide 17 text

9 541 54 0 12 9 541 54 . 13 9 = 0 6 2 3 53 54 0 1 2

Slide 18

Slide 18 text

= l ) ) ) ) l ( I

Slide 19

Slide 19 text

- l ) ) ) l l l ) ( l ( l (

Slide 20

Slide 20 text

V PfdcK S l CD B .) ,B 4 .) B B l 4G 4 OR G .) aMN , 4D D C l eQ , 4 Rb , 4D D C aMN L B D C l ( G D B l C 4 4 GB 4 C 4 D D B l 4

Slide 21

Slide 21 text

)( 3 4 1 2 - 0 7

Slide 22

Slide 22 text

)( 2 l 0 7 - 1 l 0 43 l 2

Slide 23

Slide 23 text

:- 3 0 1 l 47 2 0 D D I

Slide 24

Slide 24 text

n l o D1B l l a o ( ) ( B R a R i e R B 1

Slide 25

Slide 25 text

)( (

Slide 26

Slide 26 text

C ) ( ) ) l l L ) ) ) (/ ( l D (/ ( l

Slide 27

Slide 27 text

E C l E C L , ( R l E C PS ), R l E C IN R l E C ( R E C D : A :

Slide 28

Slide 28 text

0/. -73394 l e atQ0/. qL QF d S c i l 6 ## 9 73394 2 :# # 1 l k 0/. s Q q p ! ! ! ! n o ! ! fhm 7 4l belong_to organization employee

Slide 29

Slide 29 text

Slide 30

Slide 30 text

- l SELECT [] FROM [ ]; SELECT name FROM employee;

Slide 31

Slide 31 text

- * l * SELECT * FROM employee;

Slide 32

Slide 32 text

- l SELECT [ 21 ] FROM [ 0 ] WHERE []; SELECT * FROM employee WHERE joined_at >= '2010/1/1';

Slide 33

Slide 33 text

- ] a l [ _ d 1 ./ 5 75 .7 :/05 6 /6 / :/ 276 1 5 joined_at > '2007/1/1' 726/. _ ' name = '' 6 5/ _ c e id <> 10001 2. _

Slide 34

Slide 34 text

- iNO f ( rL ) rL != ds ) nI K )( ( c , tvap . ul moh q eT ./ . ul moh q eT E6 < = > 5 : : 5 : : 6

Slide 35

Slide 35 text

name LIKE '%%'; % ' % l l l l l l

Slide 36

Slide 36 text

) ) ( % l name LIKE '%¥%%' ( '

Slide 37

Slide 37 text

- l id IN (10001, 10003) l

Slide 38

Slide 38 text

- 41 l l l 0 2 0 SELECT * FROM employee WHERE joined_at >= '2010/1/1' AND joined_at <= '2014/1/1';

Slide 39

Slide 39 text

1 1

Slide 40

Slide 40 text

- D E l E ) E l AE E l C C( E D l SELECT [ ] FROM [ ] ORDER BY [ ] [ASC/DESC]; SELECT * FROM employee ORDER BY joined_at ASC;

Slide 41

Slide 41 text

l l 2 SELECT [ ] FROM [ ORDER BY [ ] [ASC/DESC] LIMIT []; SELECT * FROM employee ORDER BY joined_at ASC LIMIT 2; SELECT * FROM employee ORDER BY joined_at ASC LIMIT 1, 2;

Slide 42

Slide 42 text

#$ % ) l " ! SELECT [&'] FROM [(] GROUP BY []; SELECT organization_id, COUNT(*) FROM belong_to GROUP BY organization_id;

Slide 43

Slide 43 text

) * ( * ) * ( * ) * ( * 3 4 12 GROUP BY org_id SELECT org_id, COUNT(*) 0 0 2 3 4 1 ) *

Slide 44

Slide 44 text

l ) ( )

Slide 45

Slide 45 text

15 0 I a D l 678 047 2 a l 3 66 2 l 506837 d_ 9 : l _ l g d l ed l ced grade

Slide 46

Slide 46 text

SELECT class_id, COUNT(*), AVG(result), MAX(result), MIN(result) FROM grade GROUP BY class_id;

Slide 47

Slide 47 text

B O RO l U H E l G P

Slide 48

Slide 48 text

l SELECT * FROM [ ] GROUP BY [ 2 ] HAVING []; SELECT organization_id, COUNT(*) AS num FROM belong_to GROUP BY organization_id HAVING num >= 2;

Slide 49

Slide 49 text

GROUP BY org_id SELECT org_id, COUNT(*) 0 1 3 HAVING num >= 2 2 4

Slide 50

Slide 50 text

" $ ! #%

Slide 51

Slide 51 text

- 3 4 1 2 - 0 7 - 0 3 - 0 4 7 1 2

Slide 52

Slide 52 text

- SELECT organization.name, employee.name FROM belong_to INNER JOIN organization ON belong_to.organization_id = organization.id INNER JOIN employee ON belong_to.employee_id = employee.id;

Slide 53

Slide 53 text

- belong_to organization " $%"!# … belong_to INNER JOIN organization ON belong_to.organization_id = organization.id …

Slide 54

Slide 54 text

- . . . … belong_to INNER JOIN organization ON belong_to.organization_id = organization.id …

Slide 55

Slide 55 text

- . . . 143 2 0 … belong_to INNER JOIN organization ON belong_to.organization_id = organization.id INNER JOIN employee ON belong_to.employee_id = employee.id;

Slide 56

Slide 56 text

- SELECT organization.name, employee.name FROM belong_to INNER JOIN organization ON belong_to.organization_id = organization.id INNER JOIN employee ON belong_to.employee_id = employee.id;

Slide 57

Slide 57 text

Slide 58

Slide 58 text

- ( "!&%$ ' %#& l "!&%#& l "!&%$ ' %#& SELECT MAX(result) FROM grade; SELECT AVG(result) FROM grade WHERE result <> [];

Slide 59

Slide 59 text

- 1 SELECT AVG(result) FROM grade WHERE result <> (SELECT MAX(result) FROM grade);

Slide 60

Slide 60 text

- > l < l < l < SELECT class_id, MAX(result) FROM grade GROUP BY class_id; SELECT class_id, AVG(result) FROM grade WHERE (class_id = 1 AND result <> [1 ]) OR (class_id = 2 AND result <> [2 ]) OR … GROUP BY class_id;

Slide 61

Slide 61 text

- 1 1 SELECT class_id, AVG(result) FROM grade AS g1 WHERE g1.result <> ( SELECT MAX(result) FROM grade AS g2 WHERE g1.class_id = g2.class_id ) GROUP BY class_id;

Slide 62

Slide 62 text

SELECT class_id, AVG(result) FROM grade AS g1 WHERE [] GROUP BY class_id;

Slide 63

Slide 63 text

6 63 8 3 9 7 0 2 1 5 4 g1 SELECT class_id, AVG(result) FROM grade AS g1 WHERE [] GROUP BY class_id;

Slide 64

Slide 64 text

1 g1 WHERE g1.result <> ( SELECT MAX(result) FROM grade AS g2 WHERE g1.class_id = g2.class_id )

Slide 65

Slide 65 text

g1 g2 WHERE g1.result <> ( SELECT MAX(result) FROM grade AS g2 WHERE g1.class_id = g2.class_id )

Slide 66

Slide 66 text

. g2 g1 WHERE g1.result <> ( SELECT MAX(result) FROM grade AS g2 WHERE g1.class_id = g2.class_id )

Slide 67

Slide 67 text

MAX(result) g2 g1 WHERE g1.result <> ( SELECT MAX(result) FROM grade AS g2 WHERE g1.class_id = g2.class_id )

Slide 68

Slide 68 text

36 4215 0 g2 g1 WHERE g1.result <> 100

Slide 69

Slide 69 text

g1 1 89 B 4 3 0 2 6 753

Slide 70

Slide 70 text

& Q & E C L & Q

Slide 71

Slide 71 text

INSERT INTO [ ] ([ ]) VALUES ([]); INSERT INTO employee (id, name, joined_at) VALUES (10001, '', '2013/04/01'), (10002, '', '2014/04/01'), (10003, '', '2007/04/01');

Slide 72

Slide 72 text

$#" % ! & UPDATE [ ] SET [ ] = [ ] WHERE [ ]; UPDATE employee SET joined_at = '2007/4/2' WHERE id = 10003;

Slide 73

Slide 73 text

DELETE FROM [] WHERE []; TRUNCATE []; DELETE FROM [];

Slide 74

Slide 74 text

Slide 75

Slide 75 text

! !

Slide 76

Slide 76 text

Q l e j c i l D P E S IC A l PX A L N N N D j

Slide 77

Slide 77 text

Slide 78

Slide 78 text

3 L : 88 Q . 4 666 0 721 2 /

Slide 79

Slide 79 text

& h M 1 0 S d L Q M c a .774 888 1 : 2 /4 4

Slide 80

Slide 80 text

U B Ca B JF 2 26 N S 3 : / / 0 4: 1: .

Slide 81

Slide 81 text

9Q S : a L c d : h .55 4 0 721 2 /