Fundamental and Application of Database ? o?¨$ ? o?¨$ ? B? ? B? ? e/ ?D1???ù?? ? e/ ?D1???ù?? ? ￥t ? ￥t 3.6 ? ÷? = 1. ? ?0°?2T z? ì T INSERT INTO <V > [(< ??  1> [< ??  2>… )] 0°?  z? ? |0°?2T? ?·?V? ? ?0°?2T ? [ è 3]  ?B?" pD 3￥ ü ( M -i ü2Ti ? ? ob ?B?yV CREATE TABLE Deptage (Sdept CHAR(15) /* " */ Avgage SMALLINT) /*D 3 ü ( M - */ ? ?0°?2T ? ?=?? ? ? INSERT INTO Deptage(SdeptAvgage) SELECT SdeptAVG(Sage) FROM Student GROUP BY Sdept ? ?0°?2T ? – INTO0 (D? ??HíF ? ? ) z ·?1? ? ? ￥V # ??  z ?? ￥ ¨? VDV?l?￥ ¨??Bá z àμ·? ?? V U1? ?￥ ^BH??￥í F z ·??s ?? ? ?￥íF ? ??   | b′ – 0°? z SELECT0  "S A?D INTO0  ? ￥ –′￥? ? –′￥ ?? ? ?0°?2T ? DBMS3?? ??  H?_° ??íF ^ ? ?êV X?l￥????5 – L8??? – ?v??? – ¨??l￥???¨??l￥??? z ?μ NOT NULL? ?￥ ??  ^?4 d b′ z ?μ UNIQUE? ?￥ ??  ^?4 d×ˉ′ z ?μ′×? ?￥ ??  ?4￥ ??′ ^?′×S? = 2. {0°?￥???  [ è ] |9 ? SD" ?8D 3￥?? ,b UPDATE SC SET Grade=0 WHERE 'CS'= (SELETE Sdept FROM Student WHERE Student.Sno = SC.Sno) 3. {0°?￥ ÷"?  [ è 11] ÷"9 ? SD" ?μD 3￥ê [: cb DELETE FROM SC WHERE 'CS'= (SELETE Sdept FROM Student WHERE Student.Sno=SC.Sno) 3.7 j m jm￥+? z′V ^VB?+?'V  j m?￥V zoib jm￥?l??C ? o? zV?￥ ? ? 3M?V jm?°? ￥ ? 9 ?-?M 3.7 j m ? jm￥?T z °? z ÷" z sK÷? z?l?? jm￥? jm 3.7 j m 3.7.1 ?l jm ?l jm 3.7.2 °? jm 3.7.3 ÷? jm 3.7.4 jm￥T¨ 1. y ? jm z? ì T CREATE VIEW < jm > [(<  > [<  >]…)] AS <0°? > [WITH CHECK OPTION] y ? jm ? DBMS3? CREATE VIEW?  Ho ^ü jm￥?li ? ? 3?i?3? ? ￥ SELECT? b  jm°? H? jm￥?lV'V ?| ? °b F? jm￥ ??  ?? ?? 8 { ?? ?? ·? – 8 { : ?0°?? SELECT "S ?￥í3 F? – ü ?·? jm￥ ?μ  : (1) ? "S  ^"f ? Vr T (2) "S 1 * (3) V ?¤ Hê +?] T1 jm￥3 (4) 31 jm?1 ?  ¨?￥÷? a￥ 3 ? 0" jm [ è 1] y ??"D 3￥ jmb CREATE VIEW IS_Student AS SELECT SnoSnameSage FROM Student WHERE Sdept= 'IS' V??'V? o ^ ?? 'V￥ t?? t   =  ? y ? jm ? z WITH CHECK OPTION iV jmé?9 ÷??T H?¤ ?ê j m?l?￥ìMHq  '0°??￥HqVr T WITH CHECK OPTION￥ jm [ è 2] y ??"D 3￥ jmi1 piV? j mé?￥÷??To #?"D 3b CREATE VIEW IS_Student AS SELECT SnoSnameSage FROM Student WHERE Sdept= 'IS' WITH CHECK OPTION  IS_Student jm￥÷??T z ???T DBMS1?F  Sdept= 'IS'￥Hq z ÷"?T DBMS1?F  Sdept= 'IS'￥Hq z ? ??T DBMS1?_° Sdept ??′ ^?1 'IS' – ?T? ^5 '?? ??T – ?T àμ4 Sdept ??′51??l Sdept 1 'IS' ??V￥ jm [ è 4] y ??"ê?  1| [?￥D 3 jmb CREATE VIEW IS_S1(SnoSnameGrade) AS SELECT Student.SnoSnameGrade FROM StudentSC WHERE Sdept= 'IS' AND Student.Sno=SC.Sno AND SC.Cno= '1' ? jm￥ jm [ è 5] y ??"ê?  1| [? O? 90s [ ￥D 3￥ jmb CREATE VIEW IS_S2 AS SELECT SnoSnameGrade FROM IS_S1 WHERE Grade>=90 {Vr T￥ jm [ è 6] ?lB?Q?D 3 3 Mz￥ jmb CREATE VIEW BT_S(SnoSnameSbirth) AS SELECT SnoSname2000-Sage FROM Student !?Bt ? 3 ??  , 9 ?1′ E  --Sbirth {Vr T￥ jmA? ü ??lF? jm￥ò? ??  y ?sF jm [ è 7] |D 3￥D|# e￥ ü (??l1B? jm L ! SCV? “? ” Grade1 ?3? CREAT VIEW S_G(SnoGavg) AS SELECT SnoAVG(Grade) FROM SC GROUP BY Sno y ? jm ? zB ??^ ? ￥ jm – [ SELECT * Z T7y￥ jm V ? ?μ ?D V ?E ? y ? jm ? [ è 8]| StudentV? ?μ o 3: c?l1B? jm CREATE VIEW F_Student1(stdnumnamesexagedept) AS SELECT * FROM Student WHERE Ssex=' o ' ????V Student￥2a StudentV D F_Student1 jm￥?`1"$ ?ê ?á? jm? ?? ?yTb y ? jm ? CREATE VIEW F_Student2 (stdnumnamesexagedept) AS SELECT SnoSnameSsexSageSdept FROM Student WHERE Ssex=' o ' 1V Student9F ?? ?? ?ê StudentV D F_Student2 jm￥?`1"b èn￥ jm? T – ? 0" jm – WITH CHECK OPTION￥ jm – ??V￥ jm – ? jm￥ jm – {Vr T￥ jm – sF jm 2. ÷" jm z DROP VIEW < jm > – ?? V ? 3?? ÷"·?￥ jm?l – ?? jm?￥  e jm?l ˉ ? 3? ??X? ? P¨A?A T ÷" – ÷"V H??V?￥ ?μ jm?l ?A?A T ÷" ÷" jm (? [ è 9] ÷" jm IS_S1 DROP VIEW IS_S1 3.7 j m 3.7.1 ?l jm 3.7.2 °? jm 3.7.3 ÷? jm 3.7.4 jm￥T¨ 3.7.2 °? jm z V¨??°? jmD°?'VM] z DBMS LC jm°?￥ZE – L8? jm View Materialization z μr?_°_° ?°?￥ jm ^?i z 3? jm?l| jm " H L8? 3? " HV z °? jmD1°? " HV z °??8 ÷"$ L8?￥ jm ( " HV ) °? jm ? – jmh3E View Resolution z é?μr?_°_°°?￥Va jm? ^?i b ?Ti5V ? 3?? | jm￥?l z ü jm?l?￥0°?D¨?￥°?2?  ? D??N￥'V￥°? D??N￥'V￥°? z 3? ?? a￥°?a￥°? °? jm ? [ è 1] ?"D 3￥ jm?s M -l? 20 ?￥D 3b SELECT SnoSage FROM IS_Student WHERE Sage<20 IS_Student jm￥?l jm￥?l ( jm?l è 1) CREATE VIEW IS_Student AS SELECT SnoSnameSage FROM Student WHERE Sdept= 'IS‘ °? jm ?  – jm L8?E – jmh3E Da￥°?? 1 SELECT SnoSage FROM Student WHERE Sdept= 'IS' AND Sage<20 °? jm ? [ è 2] °??"ê?  1| [?￥D 3 SELECT SnoSname FROM IS_StudentSC WHERE IS_Student.Sno =SC.Sno AND SC.Cno= '1' °? jm ? z jmh3E￥ K – μt f ?/ jmh3E? ? 3?? ?° ?b?¨ jmh3E￥%#.4?K?? ?° ?b °? jm ? < è>4@( jm?°? ü (?s[ ￥D 3D| ? ü (? 4&-&$5 '30.4@( 8)&3&(BWH S_G jm?l CREATE VIEW S_G (SnoGavg) AS SELECT SnoAVG(Grade) FROM SC GROUP BY Sno °?D p SELECT SnoAVG(Grade) FROM SC WHERE AVG(Grade)>=90 GROUP BY Sno ? ? SELECT SnoAVG(Grade) FROM SC GROUP BY Sno HAVING AVG(Grade)>=90 3.7 j m 3.7.1 ?l jm 3.7.2 °? jm 3.7.3 ÷? jm 3.7.4 jm￥T¨ 3.7.3 ÷? jm z ¨??÷? jmD÷?'VM] z DBMS LC jm÷?￥ZE – jm L8?E View Materialization – jmh3E View Resolution z ·? WITH CHECK OPTION0 a DBMS÷? jm H?é?_°]?¨?YV jm  ? ?? jmS? = ￥'V ? é?÷? ÷? jm ? [ è 1] |?"D 3 jm IS_Student?D| 95002 ￥D 3? ?1 “ >a ”b UPDATE IS_Student SET Sname= ' >a ' WHERE Sno= '95002' Da￥?  UPDATE Student SET Sname= ' >a ' WHERE Sno= '95002' AND Sdept= 'IS' ÷? jm ? [ è 2] _?"D 3 jm IS_S?? ?B??￥D 3: c 95029u? 20 ? INSERT INTO IS_Student VALUES(‘95029’‘u? ’20) D1'V￥÷? INSERT INTO Student(SnoSnameSageSdept) VALUES('95029''u? '20'IS' ) ÷? jm ? [ è 3] ÷" jm CS_S?D|1 95029￥: c DELETE FROM IS_Student WHERE Sno= '95029' D1'V￥÷? DELETE FROM Student WHERE Sno= '95029' AND Sdept= 'IS' ÷? jm￥K? z Bt jm ^? V÷?￥y1?t jm￥÷ ?? ?·B1μil1D?M?'V￥ ÷? (  ?ZE ( ?N ) è jm S_G1? V÷? jmb CREATE VIEW S_G (SnoGavg) AS SELECT SnoAVG(Grade) FROM SC GROUP BY Sno ÷? jm ? ? ?/÷??  UPDATE S_G SET Gavg=90 WHERE Sno= '95001' í ? L8?E? ^h3E?íE| D? 'V SC￥÷? jm￥ V÷?? ? ?  V÷? ? ? ? V÷?  ?÷? ? ?÷? ? ?÷? L="d jm÷?￥K? z  ?? 0" jmé?÷? z   e ?? jm￥÷??]"dμ?]K? DB2 jm÷?￥K? (1) ? jm ^? ?[ 'V?￥5N j m? ?÷?b (2) ? jm￥3 ?13 Vr Tè ?5?  ?N jm3? INSERT? UPDATE?T ? ?3? DELETE?Tb ÷? jm ? (3) ? jm￥3 ?1"f ?5N jm? ?÷ ?b (4) ? jm?l?cμ GROUP BY0 5N jm? ?÷?b (5) ? jm?l?cμ DISTINCT ?5N jm? ?÷?b (6) ? jm?l?μ 3*°?i O =a°?￥ FROM 0 ? #￥V9 ^?? jm￥'V5N jm? ?÷?b (7) B?? ?÷?￥ jm ?l￥ jm9? ?÷? ÷? jm ? è jm GOOD_SC(? [? ü (?- ￥íF ) CREATE VIEW GOOD_SC AS SELECT SnoCnoGrade FROM SC WHERE Grade > (SELECT AVG(Grade) FROM SC) 3.7 j m 3.7.1 ?l jm 3.7.2 °? jm 3.7.3 ÷? jm 3.7.4 jm￥T¨ 1. jm ? e? e? ¨?￥?T ¨?￥?T ? jm? ? ? ^°¤ ?1'V H? l jm ?e?¨?￥?T – ?fV ?¤??￥ jm – ?ˉ 3*°?￥ jm – c? ??￥ jm 2. jm P¨? ?[ ?? A?]B ? z jm? ? P?]¨?[?]Z T A? ]B ?  a? ? o Z￥31 3. jm× ? o4 B??￥ ?  ? ?? è ? o ? 2? 3?M D 31" Student(SnoSnameSsexSageSdept) “<° ”1s? ?'V SX(SnoSnameSage) SY(SnoSsexSdept) 3. jm× ? o4 B??￥ ?  ? ?? YVy ?B? jm Student CREATE VIEW Student(SnoSnameSsexSageSdept) AS SELECT SX.SnoSX.SnameSY.SsexSX.SageSY.Sdept FROM SXSY WHERE SX.Sno=SY.Sno P¨?￥?  T ??MV7e StudentV￥ °????A?? 3. jm× ? o4 B??￥ ?  ? ?? z t ?? ??D ? ? ??￥à Q z jmB??  ￡  ? ￥ ? ? ?? z jmo ?B?? 4 ? ￥ ? ? ?? – ?? jm￥÷? ^μHq￥yN?¨???? ? ? ￥?  V ? ˉ?y'V2￥?M7? Mb 4. jm ? á ? 4? ? 1 z?]¨??l?] jm P ??¨? o ? A? eμ ? A?￥ ? zYV WITH CHECK OPTION1o ? ?l?T HWK? y ? jm ? [ è 3 ] y ? 1| [?￥ê [ jmi1 piV? jmé ?￥÷??To # 1| [?] H? jm￥ ??? To ?yT HWé?b CREATE VIEW IS_SC AS SELECT SnoCnoGrade FROM SC WHERE Cno= '1' AND TO_CHAR(SYSDATE,'HH24') BETWEEN 9 AND 17 AND TO_CHAR(SYSDATE,'D') BETWEEN 2 AND 6 WITH CHECK OPTION; 3.8 ? e? zà ? z 3.8.1 q ? z 3.8.2 lí ?K zl2 à ? ? e?g?1 ?  1 ? ? ￥ z? ?? e? z??? e? zi? e? z?ˉ SQL?y￥ ? e?? ? 42-?y4  ? e?? ? ?B ??  ￡ ? o? ? ￥? ??a ???i4 B?￥i? e?#? ˉ ? ?b 1. ??? z ? o￥??? ^· ? o? ? ￥? ??DM ??b z SQL?y?l???? ?Hq CREATE TABLE?  ALTER TABLE?  z ? z |′·B￥  z ?v??? z  e? ?Hq 2. i? e? z i? e? : ??¨?i?1 ? oé?? T H e ìF[ e?ax?[ ￡i?? T? ?3? ? ? o￥Bá?b z SQL?yi? e? ? ? 4 Ya Y 7 Sa Y2 ?a4??à Q 3. ?ˉ ?ˉ z?ˉ : ?? 3ò? ??￥#p?á ? o )??Bá ? H| ? o?ˉ?B á ?￥? ?b z SQL?y?ˉ? ? 4 YíOa×S?à Q  UNDOaREDO 4. ? ?? ? ?? z? ?? 1 ? o]???E￥ P ¨ ?/?￥ ? ? ]? ?êb z ￡ ? ? ??￥?1n @ i | e? e?¨?o ?i | eμ ?i |￥ ? ???]¨???] ? ` ? ?3?￥?T DBMS LC ? ? ?? 1￥V? ? ?? 1￥V? z¨? DBAü q ? %?á?"d z SQL￥ GRANT? REVOKE z DBMSü q ?￥2Ti ? ? 3? z?¨?4?T h p H DBMS? q ??lé?_°[ %? ^?3??T h p ? ?? ? ?? (? ) z ￠?l$ %#"?V￥y ?? 'V￥ ?? z ???l$ SQL?  GRANT REVOKE 3.8.1 q ? ? z (3"/5? ￥B?ì T (3"/5 ?K<  ?K> <0/` ??` > 50¨?< ¨?> <8*5)(3"/5015*0/> z ￠?l$%#"?V￥y ?? 'V￥ ?? z REVOKE? ?|·??T`￥·??T ?K qí·?￥¨? b (1) ?T ?K  ` ` ?? ?T ?K  ??  5"#-& 4&-&$5 */4&35 61%"5& %&-&5& "--3*7*&(&4  j m 5"#-& 4&-&$5 */4&35 61%"5& %&-&5& "--3*7*&(&4 'V 5"#-& 4&-&$5 */4&35 61%"5& %&-&5&"-5&3 */%&9 "--13*7*&(&4  ? o %"5"#"4& $3&"5&5"# (2) ¨?￥ ?K ¨?￥ ?K zyV $3&"5&5"#￥ ?K ??%#" z%#" qí ?Y¨? z'V jm￥ ???μ?V jm ￥B M?T ?K z¤ s ?K￥¨? : B?? 8¨? 16#-*$ ?8¨?  8*5)(3"/5015*0/0  z·? 8*5)(3"/5015*0/0  ¤ ? ?K￥¨?? V[ü?? ?K  qí Y￥¨?b z àμ·?8*5)(3"/5015*0/0  ¤ ? ?K￥¨?o ? P¨? ?K ? ?.l ? ?K è5 èü°?4UVEFOUV ?K qó¨?6 GRANT SELECT ON TABLE Student TO U1; è5 ? èü4UVEFOUV?$PVSTF V￥ ?? ?K qí¨?6?6 (3"/5"--13*7*-*(&4 0/5"#-&4UVEFOU $PVSTF 506 6 è 4 è5 ? èüV4$￥°? ?K qí ?μ¨? (3"/54&-&$5 0/5"#-&4$ 5016#-*$ è5 ? èü°?4UVEFOU V???D 3D|￥ ?K qó¨?6 (3"/561%"5& 4OP 4&-&$5 0/5"#-&4UVEFOU 506 è5 ? èüV4$￥*/4&35 ?K qí6¨? i ? e|N ?K qí  e¨? (3"/5*/4&35 0/5"#-&4$ 506 8*5)(3"/5015*0/ .l ?K 3? èa6???μ V4$￥*/4&35 ? K ? V[.lN ?K (3"/5*/4&350/5"#-&4$506 8*5)(3"/5015*0/ ]"6? V[|N ?K qí6 (3"/5*/4&350/5"#-&4$506 ?6? ?.lN ?Kb 666 è5 ? è%#"ü ? o4@$?y ?V￥ ?K q í¨?6 (3"/5$3&"5&5"# 0/%"5"#"4& 4@$ 506 3.8 ? e? zà ? z 3.8.1 q ? z 3.8.2 lí ?K zl2 SQL lí ?K￥? ? lí ?K￥? ? z3&70,&? ￥B?ì T1 3&70,& ?K<  ?K> <0/` ??` > '30.¨?< ¨?> z? ?V·? ¨? * ú lí·? ` ￥·? ?K è5 èü¨?6??D 3D|￥ ?K lí 3&70,&61%"5& 4OP 0/5"#-&4UVEFOU '30.6 è5 ? è lí ?μ¨?V4$￥°? ?K 3&70,&4&-&$5 0/5"#-&4$ '30.16#-*$ è5 ? èü¨?64$V￥*/4&35 ?K lí 3&70,&*/4&35 0/5"#-&4$ '30.6 ?K￥) óí l "d| lí°¤W¤V U5)¤￥ SC V￥ INSERT ?K : -->U5--> U6--> U7 lí U5aU6aU7¤￥ SCV￥ INSERT ?K : <--U5<-- U6<-- U7 l2 :42- 2￥ q ?? z %#"?μ ? o? ?μ`￥ ?μ ?Ki V[? ?¨￥?μ ? o? ?μ`￥ ?μ ?Ki V[? ?¨￥ 31|?]￥ ?K qí?]￥¨?b 31|?]￥ ?K qí?]￥¨?b z ¨?1-y ?￥'V? jm?μ ?? ￥?T ?Ki O V¨?1-y ?￥'V? jm?μ ?? ￥?T ?Ki O V [¨ [¨ (3"/5? ü ? t ?K qí  e¨?b ? ü ? t ?K qí  e¨?b z $ q ?￥¨? ?Tμ$ q ?￥¨? ?Tμ “?? q ??? q ? ”￥? V? V[ü¤￥ ?￥? V? V[ü¤￥ ? K qí  e¨?b K qí  e¨?b z ?μ qí ?￥ ? ?A1 H?? V[¨ ?μ qí ?￥ ? ?A1 H?? V[¨ 3&70,&?  líb?  líb 3.9 ? e?-= ??? e? z L8??? L8??? z?v??? ?v??? z ??′K? ??′K? zíF)K? íF)K? z????? ?????