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

A97eee01397705443a72a48ce29d3e19?s=47 Cybozu
July 17, 2018

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

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

A97eee01397705443a72a48ce29d3e19?s=128

Cybozu

July 17, 2018
Tweet

Transcript

  1.    

  2. 

  3.   !     !  

    
  4.    %!  $'"  #( & 

     ()
  5.        EL C l

    X  l C  N D P l AQ S C I I I AQ
  6. 

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

  8.       l l l 

    l l / l /
  9.      !%0  ! %0 

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

  11.      A C  C l

    l l D I
  12.        l  

       
  13. 23 23 23  23 23 23  51 .

    0 4
  14. 0 0 0 0 0 0 2.34 1 10

  15. ! = A l ( ) A )

  16.    1 B2 A B l 5 B

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

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

    ( I 
  19. -     l ) ) ) l

         l    l ) (    l ( l (
  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
  21. )(    3 4 1 2  

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

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

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

    o   ( ) ( B R a R i e R B 1
  25. )( (

  26.   C  ) ( ) ) l l

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

  30. -         l

          SELECT [] FROM [ ]; SELECT name FROM employee;
  31. -  *  l * SELECT * FROM employee;

  32. -   l SELECT [ 21 ] FROM [

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

    ds ) nI K )( ( c , tvap . ul moh q eT ./ . ul moh q eT E6 < = > 5 : : 5 : : 6
  35. name LIKE '%%';  %  ' % l l

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

    '%¥%%'  ( '
  37. -  l id IN (10001, 10003) l  

     
  38. -  41 l l  l 0 2 

    0 SELECT * FROM employee WHERE joined_at >= '2010/1/1' AND joined_at <= '2014/1/1';
  39.   1 1 

  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;
  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;
  42.   #$ %    ) l "

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

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

  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
  46. SELECT class_id, COUNT(*), AVG(result), MAX(result), MIN(result) FROM grade GROUP BY

    class_id;
  47.  B O RO l U H E l G

    P
  48.   l SELECT * FROM [ ] GROUP BY

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

      GROUP BY org_id SELECT org_id, COUNT(*) 0 1 3         HAVING num >= 2 2 4
  50.  " $ !   #%   

     
  51. -    3 4 1 2  

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

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

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

  58. - (  "!&%$ ' %#&   l "!&%#&

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

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

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

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

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

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

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

       g2 g1        WHERE g1.result <> 100
  69.          

    g1 1 89 B 4 3 0 2 6 753
  70.   &  Q &  E C L

    &  Q
  71.       INSERT INTO [ 

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

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

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

  75.   !     !  

    
  76.      Q l e j 

    c i l D  P E S IC A l PX A L N N N D j
  77. 

  78. 3  L  :   88  

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

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

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

     d :  h   .55 4 0 721 2 /