sql-workshop-for-web-director-1

ディレクタのためのSQL勉強会の時に使用したスライドです。

Transcript

1. 8FCσΟϨΫλʔͷͨΊͷ 42-ษڧձ 13೥4݄1೔݄༵೔
2. 42- wϦϨʔγϣφϧσʔλϕʔεͷૢ࡞Λ ߦ͏ͨΊͷݴޠ 13೥4݄1೔݄༵೔
3. SELECT CustomerName FROM Customers; 13೥4݄1೔݄༵೔
4. ϦϨʔγϣφϧσʔλϕʔεʁ w3%#
   3FMBUJPOBM
   %BUBCBTF w࠷΋޿͘ར༻͞Ε͍ͯΔσʔλϕʔε wσʔλΛਓ͕ؒཧղ͠΍͍͢ೋ࣍ݩද ͷܗࣜͰ؅ཧ 13೥4݄1೔݄༵೔
5. ϦϨʔγϣφϧσʔλϕʔε JE ࢯ໊ ෦ॺ ৬छ  ϩϦϙ͘Μ MPMJQPQ σβΠφʔ  Χό͘Μ IFUFNM ΤϯδχΞ  Ϧε͘Μ TRBMF σβΠφʔ  ͘·͘Μ NVVNVV σΟϨΫλʔ JE ࢯ໊ ෦ॺ ৬छ  ϩϦϙ͘Μ MPMJQPQ σβΠφʔ  Χό͘Μ IFUFNM ΤϯδχΞ  Ϧε͘Μ TRBMF σβΠφʔ  ͘·͘Μ NVVNVV σΟϨΫλʔ JE ࢯ໊ ෦ॺ ৬छ  ϩϦϙ͘Μ MPMJQPQ σβΠφʔ  Χό͘Μ IFUFNM ΤϯδχΞ  Ϧε͘Μ TRBMF σβΠφʔ  ͘·͘Μ NVVNVV σΟϨΫλʔ JE ࢯ໊ ෦ॺ ৬छ  ϩϦϙ͘Μ MPMJQPQ σβΠφʔ  Χό͘Μ IFUFNM ΤϯδχΞ  Ϧε͘Μ TRBMF σβΠφʔ  ͘·͘Μ NVVNVV σΟϨΫλʔ ࣾһ
   JE ࢯ໊ ෦ॺ ৬छ  ϩϦϙ͘Μ MPMJQPQ σβΠφʔ  Χό͘Μ IFUFNM ΤϯδχΞ  Ϧε͘Μ TRBMF σβΠφʔ  ͘·͘Μ NVVNVV σΟϨΫλʔ 13೥4݄1೔݄༵೔
6. ςʔϒϧʁ w ςʔϒϧͱද͸ݫີʹ͸ಉ͡΋ͷͰ͸ͳ͍ 13೥4݄1೔݄༵೔
7. ߲໨ ߲໨ ߲໨ ߲໨ ϩϦϙ͘Μ ͓͡͞Μ ϖύϘ ෱Ԭ Χό͘Μ ಈ෺ ੨ Ϧε͘Μ υϯάϧ ϧϏʔ γφτϥ ͘·͘Μ ৿ ࡪ ςʔϒϧͰ͸ͳ͍ʂ 13೥4݄1೔݄༵೔
8. ࣾһ ࣾһ
   JE ࢯ໊ ෦ॺ ৬छ  ϩϦϙ͘Μ MPMJQPQ σβΠφʔ  Χό͘Μ IFUFNM ΤϯδχΞ  Ϧε͘Μ TRBMF σβΠφʔ  ͘·͘Μ NVVNVV σΟϨΫλʔ ςʔϒϧʂ 13೥4݄1೔݄༵೔
9. ςʔϒϧ w ܗ͚ͩʮೋ࣍ݩදʯͰ͋Δ͚ͩͰ͸μϝ w ڞ௨఺Λ࣋ͬͨϨίʔυΛूΊͨ΋ͷ w ςʔϒϧ໊͸ෳ਺ܗͰॻ͚Δ 13೥4݄1೔݄༵೔
10. ࣾһ &NQMPZFFT ࣾһ
    JE ࢯ໊ ෦ॺ ৬छ  ϩϦϙ͘Μ MPMJQPQ σβΠφʔ  Χό͘Μ IFUFNM ΤϯδχΞ  Ϧε͘Μ TRBMF σβΠφʔ  ͘·͘Μ NVVNVV σΟϨΫλʔ ߦ ʢϨίʔυʣ ྻɺଐੑ ʢΧϥϜʣ 13೥4݄1೔݄༵೔
11. ࣾһ
    JE ࢯ໊ /BNF ෦ॺ ৬छ  ϩϦϙ͘Μ MPMJQPQ σβΠφʔ  Χό͘Μ IFUFNM ΤϯδχΞ  Ϧε͘Μ TRBMF σβΠφʔ  ͘·͘Μ NVVNVV σΟϨΫλʔ &NQMPZFFT
    ςʔϒϧ SELECT Name FROM Employees; ࢯ໊ /BNF ϩϦϙ͘Μ Χό͘Μ Ϧε͘Μ ͘·͘Μ &NQMPZFFT
    ςʔϒϧ͔Βࢯ໊ҰཡΛग़͢৔߹ DataBase 13೥4݄1೔݄༵೔
12. QIQ.Z"ENJOΛ࢖ͬͯσʔλϕʔε Λૢ࡞ͯ͠ΈΑ͏
    
    w.Z42-αʔόʔΛϒϥ΢β
    ͔Βૢ࡞Ͱ ͖·͢ wࠓճ͸IFUFNM ϔςϜϧ Λ࢖͍·͢ 13೥4݄1೔݄༵೔
13. IUUQIFUFNMKQ
    ʮਃ͠ࠐΈʯ͔ΒΞΧ΢ϯτΛ࡞੒ͯ͠Լ͍͞ʂ 13೥4݄1೔݄༵೔
14. ΞΧ΢ϯτ࡞੒ޙίϯτϩʔϧύωϧʹϩάΠϯɺ ʮσʔλϕʔεʯΛબ୒ 13೥4݄1೔݄༵೔
15. ʮσʔλϕʔε࡞੒ը໘΁ʯϘλϯΛΫϦοΫ 13೥4݄1೔݄༵೔
16. σʔλϕʔε໊ͱύεϫʔυΛೖྗͯ͠ ʮσʔλϕʔεΛ࡞੒͢ΔʯΛΫϦοΫ 13೥4݄1೔݄༵೔
17. σʔλϕʔεͷ࡞੒͕׬ྃͨ͠Βʮ໭ΔʯΛΫϦοΫ 13೥4݄1೔݄༵೔
18. ʮQIQ.Z"ENJOʯϘλϯΛΫϦοΫ͢Δ 13೥4݄1೔݄༵೔
19. ઃఆͨ͠Ϣʔβ໊ͱύεϫʔυΛೖྗͯ͠ ʮ࣮ߦ͢ΔʯϘλϯΛΫϦοΫ 13೥4݄1೔݄༵೔
20. ςʔϒϧͷ࡞੒ create table ςʔϒϧ໊ ( ΧϥϜ໊ ܕ, ΧϥϜ໊ ܕ ); ൒֯εϖʔε 13೥4݄1೔݄༵೔
21. σʔλͷܕ wJOU
    
    ੔਺ wDIBS
    WBSDIBSɹจࣈྻ wEBUF
    
    ೔෇ 13೥4݄1೔݄༵೔
22. ࣾһςʔϒϧͷ࡞੒ create table Employees ( EmployeeID int, EmployeeName varchar(24), Email varchar(256), Height int(255), Weight int(255), Birthday date, BloodType varchar(2) ); ൒֯εϖʔε ΧϯϚΛ๨Εͣʹ ࠷ޙʹηϛίϩϯ 13೥4݄1೔݄༵೔
23. ঎඼ςʔϒϧͷ࡞੒ create table Products ( ProductID int, ProductCode int, ProductName varchar(50), Price int, CategoryID varchar(100) ); 13೥4݄1೔݄༵೔
24. &NQMPZFF*% &NQMPZFF /BNF &NBJM )FJHIU 8FJHIU #JSUIEBZ #MPPE5ZQF &NQMPZFFT 1SPEVDU*% 1SPEVDU$PEF 1SPEVDU/BNF 1SJDF $BUFHPSZ*% 1SPEVDUT 13೥4݄1೔݄༵೔
25. ςʔϒϧͷ֬ೝ show tables; ςʔϒϧҰཡදࣔ ςʔϒϧͷ֬ೝ desc Employees; ൒֯εϖʔε 13೥4݄1೔݄༵೔
26. σʔλϕʔεͷૢ࡞ w࡞੒
    
    
    
    
    
    
    $SFBUF wಡΈग़͠
    3FBE wߋ৽
    
    
    
    
    
    
    6QEBUF w࡟আ
    
    
    
    
    
    
    %FMFUF 13೥4݄1೔݄༵೔
27. Ϩίʔυͷ௥Ճ insert into ςʔϒϧ໊ ( ΧϥϜ໊, ΧϥϜ໊ ) values ( ஋, ஋ ; ൒֯εϖʔε 13೥4݄1೔݄༵೔
28. &NQMPZFFTςʔϒϧʹϨίʔυΛ௥Ճ insert into Employees ( EmployeeID, EmployeeName, Email, Height, Weight, Birthday, BloodType ) values ( 1,! 'ϩϦϙ͓͡͞Μ', 'lolipop@example.com', 170, 70, '2002-01-01', 'A' ); ൒֯εϖʔε ΧϯϚΛ๨Εͣʹ ࠷ޙʹηϛίϩϯ จࣈྻ
    ೔෇͸
    b
    γϯάϧΫΥʔτ ͰғΉ 13೥4݄1೔݄༵೔
29. 1SPEVDUT
    ςʔϒϧʹϨίʔυΛ௥Ճ insert into Products ( ProductID, ProductCode, ProductName, Price, CategoryID ) values ( 1,! 1001, 'lolipop', 105, 1 ); 13೥4݄1೔݄༵೔
30. σʔλͷಡΈग़͠ select ྻ໊, ྻ໊ from ςʔϒϧ໊ ; 13೥4݄1೔݄༵೔
31. σʔλͷಡΈग़͠ select ProductName from Products ; select EmployeeName, Email from Employees ; 1SPEVDU
    ςʔϒϧ͔Β
    1SPEVDU/BNF
    ΛಡΈग़͢ &NQMPZFFT
    ςʔϒϧ͔Β
    &NQMPZFF/BNFͱ&NBJM
    ΛಡΈग़͢ 13೥4݄1೔݄༵೔
32. શΧϥϜಡΈग़͢ select * from Employees ; &NQMPZFFT
    ςʔϒϧ͔Β
    શΧϥϜಡΈग़͢ 13೥4݄1೔݄༵೔
33. ԋࢉ
    
    
    
    
    
    
    ଍͠ࢉ 
    
    
    
    
    
    
    
    Ҿ͖ࢉ
    
    
    
    
    
    
    
    ֻ͚ࢉ 
    
    
    
    
    
    
    
    ׂΓࢉ 
    
    
    
    
    
    
    ৒༨ࢉ 13೥4݄1೔݄༵೔
34. ԋࢉ select 1 + 1; select Price * 1.05 from Products ; select 2 * 2; 
    ͨ͢
     
    ͔͚Δ
     1SPEVDUT
    ςʔϒϧͷ
    1SJDF
    ͷ஋ʹ
    
    ͔͚Δ 13೥4݄1೔݄༵೔
35. ؔ਺ w $06/5 w "7( w 46. w ."9 w .*/ 13೥4݄1೔݄༵೔
36. Ձ֨ͷ࠷େ஋ɺฏۉ஋ΛٻΊΔ select AVG(Price) from Products; select MAX(Price) from Products; 1SPEVDUT
    ςʔϒϧͷ
    1SJDF
    ͷ஋ͷฏۉ஋ΛٻΊΔ 1SPEVDUT
    ςʔϒϧͷ
    1SJDF
    ͷ஋ͷ࠷େ஋ΛٻΊΔ 13೥4݄1೔݄༵೔
37. ৚݅Λ͚ͭͯಡΈग़͠ select * from Products where ProductID = 2 ; 1SPEVDUT
    ςʔϒϧͷ
    1SPEVDU*%͕
    
    ͷ
    ϨίʔυΛಡΈग़͢ 13೥4݄1೔݄༵೔
38. ৚݅Λ͚ͭͯಡΈग़͠ select * from Products where ProductCode >= 1000 and Price >= 1000 ; 1SPEVDUT
    ςʔϒϧͷ
    1SPEVDU$PEF͕
    Ҏ্͔ͭ
    1SJDF͕
    Ҏ্
    ͷϨίʔυΛಡΈग़͢ 13೥4݄1೔݄༵೔
39. ύλʔϯϚονͰݕࡧ ޙํҰக select EmployeeName from Employees where EmployeeName like 'ά%' ; &NQMPZFFT
    ςʔϒϧͷ
    &NQMPZFF/BNF
    ͷઌ಄จࣈ͕
    ά
    ͷ
    &NQMPZFF/BNFΛಡΈग़͢ 13೥4݄1೔݄༵೔
40. ύλʔϯϚονͰݕࡧ લํҰக select EmployeeName from Employees where EmployeeName like '%࿠' ; &NQMPZFFT
    ςʔϒϧͷ
    &NQMPZFF/BNF
    ͷ࠷ޙͷจࣈ͕
    ࿠
    ͷ
    &NQMPZFF/BNFΛಡΈग़͢ 13೥4݄1೔݄༵೔
41. ߱ॱʹฒͼସ͑ select * from Products where ProductCode >= 800 order by ProductID desc ; 1SPEVDUT
    ςʔϒϧͷ
    1SPEVDU$PEF͕
    Ҏ্
    ͷϨίʔυΛ
    1SPEVDU*%
    Ͱ߱ॱʹฒͼସ͑ 13೥4݄1೔݄༵೔
42. ঢॱʹฒͼସ͑ select * from Products where ProductCode >= 800 order by ProductID asc ; 1SPEVDUT
    ςʔϒϧͷ
    1SPEVDU$PEF͕
    Ҏ্
    ͷϨίʔυΛ
    1SPEVDU*%
    Ͱঢॱʹฒͼସ͑ 13೥4݄1೔݄༵೔
43. ୈೋճ΋΍ΔͷͰΑΖ͓͘͠ئ͍͠·͢ 13೥4݄1೔݄༵೔
44. ͓͢͢Ίຊ wվగ৽൛
    ൓෮ֶशιϑτ෇͖
    42-ॻ͖ ํυϦϧ 13೥4݄1೔݄༵೔