2018-11-データベース / 2018-11 database

データベースの概要を知る。
データベースに求められる特性を知る。
SQLの概要を把握する。
自分でSQLを書けるようになる。

Transcript

1. 
   
   
   

   View Slide

2. 
   

   View Slide

3. 
   !
   
   !
   
   
   

   View Slide

4. 
   
   %!
   
   $'"
   #( &
   
   ()
   

   View Slide

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

   View Slide

6. 
   

   View Slide

7. 
   &%# ("'"$
   !
   '
   

   View Slide

8. 
   
   
   l
   l
   l
   
   l
   l /
   l /
   

   View Slide

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

   View Slide

10. 
    
    

    View Slide

11. 
    
    A
    C
    C
    l
    l
    l D I
    

    View Slide

12. 
    
    
    l
    
    

    View Slide

13. 23 23
    23
    
    23
    23 23
    
    51
    . 0 4
    

    View Slide

14. 0 0
    0 0
    0 0
    2.34
    1
    10
    

    View Slide

15. !
    = A
    l (
    ) A
    )
    

    View Slide

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

    View Slide

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

    View Slide

18. 
    
    =
    l
    ) ) ) )
    l
    ( I
    
    

    View Slide

19. -
    
    l ) ) )
    l
    
    l
    
    
    l
    ) (
    
    
    l (
    l (
    

    View Slide

20. 
    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
    

    View Slide

21. )(
    
    
    3
    4
    1 2
    
    
    
    - 0
    7
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

25. )( (
    

    View Slide

26. 
    C
    ) ( ) )
    l
    l L
    ) ) ) (/ (
    l D
    (/ (
    l
    

    View Slide

27. 
    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 :
    

    View Slide

28. 
    
    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
    

    View Slide

29. 
    
    

    View Slide

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

    View Slide

31. -
    *
    
    l *
    SELECT * FROM employee;
    

    View Slide

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

    View Slide

33. -
    ] 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. _
    

    View Slide

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

    View Slide

35. name LIKE '%%';
    %
    ' %
    l
    l
    l
    l
    
    l
    l
    

    View Slide

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

    View Slide

37. -
    
    l id IN (10001, 10003)
    l
    
    

    View Slide

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

    View Slide

39. 
    1 1
    
    

    View Slide

40. -
    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;
    

    View Slide

41. 
    
    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;
    

    View Slide

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

    View Slide

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

    View Slide

44. 
    l
    
    ) (
    )
    

    View Slide

45. 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
    

    View Slide

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

    View Slide

47. B O RO
    l U H E
    l G P
    

    View Slide

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

    View Slide

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

    View Slide

50. 
    " $ !
    
    #%
    
    
    

    View Slide

51. -
    
    
    3
    4
    1 2
    
    
    
    - 0
    7
    
    
    
    - 0 3
    - 0 4
    7 1 2
    

    View Slide

52. -
    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;
    

    View Slide

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

    View Slide

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

    View Slide

55. -
    . . .
    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;
    

    View Slide

56. -
    
    
    
    
    
    
    
    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;
    

    View Slide

57. 
    
    

    View Slide

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

    View Slide

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

    View Slide

60. -
    >
    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;
    

    View Slide

61. -
    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;
    

    View Slide

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

    View Slide

63. 
    
    
    
    
    
    
    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;
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

69. 
    
    
    
    
    
    
    g1
    1 89 B 4 3
    0 2 6 753
    

    View Slide

70. 
    &
    Q &
    E C L &
    Q
    

    View Slide

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

    View Slide

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

    View Slide

73. 
    
    
    
    
    DELETE FROM [] WHERE [];
    TRUNCATE [];
    DELETE FROM [];
    

    View Slide

74. 
    
    
    

    View Slide

75. 
    !
    
    !
    
    
    

    View Slide

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

    View Slide

77. 
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide