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

July 17, 2018
Tweet

More Decks by Cybozu

Other Decks in Programming

Transcript

  1.        EL C l

    X  l C  N D P l AQ S C I I I AQ
  2.      !%0  ! %0 

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

       
  4.    1 B2 A B l 5 B

    l 9 0 l 9 l 9 9 5 93
  5.   9 541 54 0 12 9 541 54

    . 13 9 = 0 6 2 3 53 54 0 1 2
  6. -     l ) ) ) l

         l    l ) (    l ( l (
  7.   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
  8. )(  2 l 0 7  - 1 l

    0  43 l 2        
  9.   :- 3 0 1 l 47 2 0

     D  D     I
  10.   n l o D1B l l a 

    o   ( ) ( B R a R i e R B 1
  11.   C  ) ( ) ) l l

    L  ) ) ) (/ ( l D  (/ ( l
  12.  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 :
  13.      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
  14. -         l

          SELECT [] FROM [ ]; SELECT name FROM employee;
  15. -   l SELECT [ 21 ] FROM [

    0 ] WHERE []; SELECT * FROM employee WHERE joined_at >= '2010/1/1';
  16. -  ] 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. _
  17. - iNO f   ( rL ) rL !=

    ds ) nI K )( ( c , tvap . ul moh q eT ./ . ul moh q eT E6 < = > 5 : : 5 : : 6
  18. -  41 l l  l 0 2 

    0 SELECT * FROM employee WHERE joined_at >= '2010/1/1' AND joined_at <= '2014/1/1';
  19. - 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;
  20.   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;
  21.   #$ %    ) l "

    ! SELECT [&'] FROM [(] GROUP BY []; SELECT organization_id, COUNT(*) FROM belong_to GROUP BY organization_id;
  22. ) * ( * ) * ( * ) *

    ( * 3 4 12 GROUP BY org_id SELECT org_id, COUNT(*) 0 0 2 3 4 1 ) *
  23.  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
  24.   l SELECT * FROM [ ] GROUP BY

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

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

      - 0 7   - 0 3 - 0 4 7 1 2
  27. - 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;
  28. -         

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

           … belong_to INNER JOIN organization ON belong_to.organization_id = organization.id …
  30. - . . . 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;
  31. -         

          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;
  32. - (  "!&%$ ' %#&   l "!&%#&

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

    (SELECT MAX(result) FROM grade);
  34. -  >  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;
  35. -  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;
  36.          

    SELECT class_id, AVG(result) FROM grade AS g1 WHERE [] GROUP BY class_id;
  37.         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;
  38. 1 g1        

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

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

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

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

       g2 g1        WHERE g1.result <> 100
  43.          

    g1 1 89 B 4 3 0 2 6 753
  44.       INSERT INTO [ 

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

         !  & UPDATE [  ] SET [  ] = [  ] WHERE [ ]; UPDATE employee SET joined_at = '2007/4/2' WHERE id = 10003;
  46.          DELETE

    FROM [] WHERE []; TRUNCATE []; DELETE FROM [];
  47.      Q l e j 

    c i l D  P E S IC A l PX A L N N N D j
  48. 3  L  :   88  

    Q  . 4 666 0 721 2 /
  49. &  h M 1 0 S d L Q

     M     c a  .774 888 1 : 2 /4 4
  50. U B  Ca B  JF  2 26

    N S   3 : / / 0 4: 1: .
  51.    9Q S  : a L c

     d :  h   .55 4 0 721 2 /