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

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

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
    :
     8
    8 
    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 /