Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

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

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

Cybozu
PRO

July 17, 2018
Tweet

More Decks by Cybozu

Other Decks in Programming

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