·¢²¼Ê±¼ä : ÐÇÆÚÎå ÎÄÕÂÄϾ©Àí¹¤´óѧ¡¶Êý¾Ý¿âϵͳ»ù´¡½Ì³Ì¡·ÊÔÌâºÍ´ð°¸¸üÐÂÍê±Ï¿ªÊ¼ÔĶÁ6b75ff552f60ddccda38a09e
Ëã¡£ Having×Ó¾ä
Having×Ó¾äÌõ¼þ±íʾʲô£¿
ÔÚ·Ö×é¾ÛºÏÖ®ºó°´Ä³Ìõ¼þ¹ýÂË·Ö×飬ʹÓÃHaving×Ó¾äÈ·¶¨Ìõ¼þ¡£ ÊÓͼ
ʲôÊÇÊÓͼview£¿
ÒÀ¾ÝÒ»ÌõSELECTÓï¾ä²úÉúµÄÒ»¸öÃüÃûµÄ¡°Ðé¹Øϵ¡±»ò¡°Ðé±í¡±¡£ÊÓͼµÄÊý¾Ý¾ùÀ´×Ô»ù±¾±í£¬²¢²»Êµ¼Ê´æ´¢¡£Ò»¸ö¶¨ÒåºÃµÄÊÓͼ¿É¿´×÷»ù±¾±í£¬ÓÃÓÚ¶¨ÒåеÄÊÓͼ£¬»ò¶ÔÖ®½øÐвéѯÉõÖÁ¸üС£ Ò»¸öÊý¾Ý¿â¿É¶¨Òå¶à¸öÊÓͼ¡£ÊÓͼµÄÃû³Æ²»ÄÜÓëÒÑÓеıíÃû³åÍ»¡£ÊÓͼÊÇÊý¾Ý¿âģʽµÄÒ»²¿·Ö¡£ ΪºÎÐèÒªÊÓͼ£¿
¼ò»¯¼ÆËã¡£
²»Í¬Óû§¹ØÐÄͬһ±íÖеIJ»Í¬ÊôÐÔ¡£ Ìá¸ßÊý¾ÝµÄ°²È«ÐÔ¡£
´´½¨ÊÓͼ ÈçºÎ´´½¨ÊÓͼ£¿
CREATE VIEW [ owner .]view-name [( column-name , ... )] AS select-without-order-by [with check option] ÀýÈ磺
CREATE View orderamount As
Select salesorder.orderno, signdate, empid, custid, Sum(quantity) as qty, Sum(singlecost*quantity) as amount
From dba.salesitem, dba.salesorder
Where salesorder.orderno = salesitem.orderno Group by salesorder.orderno, signdate, empid, custid ²úÉúÒ»¸öÐé¹Øϵ£º
orderamount(orderno, signdate, empid, custid, qty, amount)
21
ÊÓͼ²éѯ ÈçºÎ²éѯÊÓͼ£¿
¶ÔÊÓͼµÄ²éѯÓë±í²éѯ×ñÑÏàͬµÄÓï·¨¡£
Àý£º¼ÆËãÏúÊÛ½ð¶î×î¸ßµÄ¶©µ¥ºÅ¡¢Ç©µ¥ÈÕÆÚ¼°ÏúÊÛ½ð¶î¡£ Select orderno, signdate, amount From orderamount
Where amount = (Select Max(amount) From orderamount) ¶ÔÊÓͼµÄ²éѯ½«ÓÉϵͳת»»Îª¶Ô»ù±¾±íµÄ²éѯ¡£ SQLÔ¼ÊøºÍ´¥·¢Æ÷ SQLÖеļü
¼ükeyÊÇ×îÖØÒªµÄÔ¼Êø¡£Ã¿¸ö±í¶¼±ØÐëÈ·¶¨×Ô¼ºµÄ¼ü¡£
ÿ¸ö±í¶¼¿ÉÄÜÓжà¸öÊôÐÔ¼¯¿É×÷Ϊ¼ü£¬³ÆΪ¡°ºòÑ¡¼ücandicate key¡±¡£ Ò»¸ö±íÖ»ÄÜÈ·¶¨Ò»¸öÖ÷¼ü(Primary Key)¡£ Èôij¸öÊôÐÔ˵Ã÷ΪUnique£¬ÔòËüÊÇÒ»¸öºòÑ¡¼ü¡£ Ö÷¼üºÍUniqueÊôÐÔÖ®¼äÓкÎÇø±ðºÍÁªÏµ£¿
Ò»¸ö±íÓÐÇÒ½öÓÐÒ»¸öÖ÷¼ü£»¶øUniqueÊôÐÔ¿ÉÓжà¸ö»òûÓС£ Ö÷¼ü¿ÉÒÔÊǶàÊôÐÔ£¬¶øUniqueÖ»ÄÜ˵Ã÷µ¥¸öÊôÐÔ¡£ ÈôÖ÷¼üÊǵ¥¸öÊôÐÔ£¬Ôò¸ÃÊôÐÔÒþº¬ÎªUnique¡£ ÈôÖ÷¼üÊǶà¸öÊôÐÔ£¬Ôòÿ¸öÊôÐÔ¶¼²»¿ÉÄÜΪUnique¡£ ʵʩÖ÷¼üÔ¼Êø
ʲô²Ù×÷»áµ¼ÖÂÎ¥±³¼üÔ¼Êø£¿
insertºÍupdate¿ÉÄÜÎ¥±³¸Ã±íµÄ¼üÔ¼Êø¡£
SQLʵʩÖ÷¼üÔ¼Êø¼´ÊÇÔÚinsertºÍupdateʱ¼ìÑé¼üÖµ£¬±ÜÃâ¿ÕÖµ»òÖظ´¼üÖµ¡£ ²ÎÕÕÍêÕûÐÔºÍÍâ¼ü
SQLÖÐÒÀ¿¿Íâ¼ü±£Ö¤²ÎÕÕÍêÕûÐÔ¡£Íâ¼ü¶¨ÒåʱҪעÒâÇø·Ö²ÎÕÕ¹ØϵºÍ±»²ÎÕÕ¹Øϵ¡£ ˵Ã÷Íâ¼üÔ¼Êø
22
ÊÇ·ñ¿É¶¨ÒåÒ»¸ö±í²ÎÕÕ×Ô¼º£¿ ¿ÉÒÔ¡£
ÀýÈ磺salesman(empid, idno, name, managerid, deptid, ¡) Íâ¼üÊÇ·ñ¿ÉÈ¡NULLÖµ£¿ ¿ÉÒÔ¡£ SQLϵͳ¸Å¿ö SQLÊÂÎñ
ÊÂÎñtransactionÊÇʲô¸ÅÄ ÊÂÎñÓкÎÌØÐÔ£¿ACID¡£ ÈçºÎ¿ØÖÆÊÂÎñÔËÐУ¿
ÊÂÎñµÄÒ»´ÎÔËÐÐÖ»ÄÜÊÇÏÂÃæÁ½¸ö½á¹ûÖ®Ò»£º CommitÌá½»£»»òRollbackÍË»Ø
CommitÖ¸ÁîÌá½»µ±Ç°ÊÂÎñ£¬Íê³É¶ÔÊý¾ÝµÄ¸üС£
RollbackÖ¸ÁîÍ˻ص±Ç°ÊÂÎñ£¬²¢È¡Ïûµ±Ç°ÊÂÎñ¶ÔÊý¾ÝÒÑ×öµÄ¸üС£ CommitºÍRollback¶¼½áÊøµ±Ç°ÊÂÎñ£¬²¢×Ô¶¯Æô¶¯ÏÂÒ»¸öÊÂÎñ¡£ ÔÚ¶àÓû§¶àÈÎÎñ»·¾³ÖУ¬²¢·¢ÊÂÎñ¿ÉÄÜÒýÆðÄÄЩÎÊÌ⣿ ͬʱ¸üÐÂͬһÊý¾Ý Ôà¶Ádirty read
²»¿ÉÖظ´¶Ánon-repeatable read ÐéÐÐ(»ÃÏóÔª×é)phantom rows ¡°Ð´Ëø¡±write lockÊÇʲô¸ÅÄ
ijÊý¾Ý±»Ò»¸öÊÂÎñÐ޸ģ¬ÔÚ¸ÃÊÂÎñÌá½»»òÍË»Ø֮ǰ£¬ÆäËûÊÂÎñ²»ÄÜÐ޸Ļòɾ³ý´ËÊý¾Ý¡£ Ôà¶ÁÊÇʲôº¬Ò壿
µ±ÊÂÎñAÐÞ¸ÄijÊý¾Ýºó£¬ÔÚÌá½»»òÍË»ØÇ°£¬¸ÃÊý¾Ý±»ÊÂÎñB¶ÁÈ¡£¬ÈôÊÂÎñAÍ˻أ¬ÔòÊÂÎñB¶ÁÈ¡µÄÊý¾ÝΪ¡°ÔàÊý¾Ý¡±£¬¼´ÊÂÎñB·¢ÉúÔà¶Á¡£ ²»¿ÉÖضÁnon-repeatable readÊÇʲôº¬Ò壿
ÊÂÎñAÏȶÁijÐÐÊý¾Ý£¬ËæºóÊÂÎñBÐÞ¸Äupdate»òɾ³ýdelete¸ÃÐÐÊý¾Ý²¢Ìá½»£¬ÊÂÎñAÖظ´ÔÙ¶Á¸ÃÐÐÊý¾Ý£¬½á¹û¾Í²»Ò»Ö¡£
23
ÐéÐÐphantom rowsÊÇʲôº¬Ò壿
ÊÂÎñA°´Ä³Ìõ¼þC²éѯһ×éÊý¾Ý£¬ËæºóÊÂÎñBÔö¼ÓinsertÂú×ãÌõ¼þCµÄÊý¾Ý£¬»òÐÞ¸ÄupdateÌõ¼þCÏà¹ØµÄÊý¾Ý£¬Ê¹µÃÊÂÎñA°´ÔÌõ¼þCÔٴβéѯµÄ½á¹û²»Ò»Ö¡£Ò²³ÆΪ¡°»ÃÏó¡±Ôª×é¡£ ¡°¶ÁËø¡±read lockÊÇʲô¸ÅÄ
µ±Ä³Êý¾Ý±»Ò»¸öÊÂÎñ´¦Àíʱ£¬ÆäËûÊÂÎñÏÞÖƶÁ¸ÃÊý¾Ý¡£ Grant, RevokeÖ¸ÁîÓкÎÓÃ;£¿
Ò»¡¢Ñ¡ÔñÌâ(Ñ¡ÔñÒ»¸ö×îºÏÊʵĴð°¸)60%
1. ÔÚʵÌå/ÁªÏµÍ¼(E/Rͼ)ÖУ¬ÊµÌ弯µÄÿ¸öÊôÐÔ¶¼Òþº¬Îª£º
A ¶àÖµ¡£ B µ¥Öµ¡£ C ²»ÔÊÐíΪNULL¡£ D Ç°ÈýÕ߶¼²»¶Ô¡£ 2. ¡°Á½¸öʵÌ弯֮¼äÈôÓÐÁªÏµ£¬Ôò¸ÃÁªÏµÊÇΨһµÄ¡£¡±ÕâÖÖ˵·¨¶ÔÂð£¿
A ÕýÈ· B ²»ÕýÈ· C ²»Äܶ϶¨ D Ó¦¸Ã˵£¬Èô¸ÃÁªÏµÊÇÒ»¶ÔÒ»£¬ÔòÊÇΨһµÄ¡£ 3. ¡°Ò»¸ö¶à¶ÔÒ»µÄÁªÏµÒþº¬×ÅÒ»¶ÔÒ»µÄÁªÏµ¡£¡±ÕâÖÖ˵·¨¶ÔÂð
A ÕýÈ· B ²»ÕýÈ· C ²»Äܶ϶¨ D Ó¦¸Ã˵£¬¶à¶Ô¶àµÄÁªÏµÒþº¬Ò»¶ÔÒ»ÁªÏµ¡£ 4. ¿¼ÂÇÒøÐÐÒµÎñÖС°´¢»§¡±Óë¡°Õʺš±Ö®¼äµÄÁªÏµ£¬¸ÃÁªÏµµÄ¶àÖØÐÔÓ¦¸ÃÊÇ£º
A Ò»¶ÔÒ» B ¶à¶ÔÒ» C Ò»¶Ô¶à D ¶à¶Ô¶à
5. ÏÂÃæÄÄÖÖÔ¼ÊøÒªÇóÒ»×éÊôÐÔÔÚÈÎÒâÁ½¸ö²»Í¬ÊµÌåÉϵÄÈ¡Öµ²»Í¬¡£
A ¼ü(key)Ô¼Êø¡£ B µ¥ÖµÔ¼Êø¡£ C ²ÎÕÕÍêÕûÐÔ¡£ D Óò(domain)Ô¼Êø 6. ²ÎÕÕÍêÕûÐÔÔ¼ÊøÒªÇóij¸öÁªÏµÖб»²ÎÕÕµÄʵÌå________¡£
A ÖÁÉÙÓÐÒ»¸ö¡£ B ÓÐÒ»¸öÇÒ½öÒ»¸ö¡£ CÁã¸öµ½¶à¸ö¡£ D Ç°ÈýÕ߶¼²»¶Ô¡£ 7. ¶ÔÓÚÒ»¸ö¡°ÈõʵÌ弯¡±£¬ ÏÂÃæÄÄÖÖ˵·¨²»ÕýÈ·:
A Ò»¶¨ÓÐÒ»¸ö¶à¶ÔÒ»µÄÁªÏµµ½ÆäËüʵÌ弯¡£ B ×ÔÉíÊôÐÔ²»Äܹ¹³ÉÍêÕûµÄ¼ü¡£ C ¿Éµ¥¶À´æÔÚ£¬¶øÎÞÐèÆäËüʵÌ弯¡£
D ¿ÉÒÔÓÐ×Ô¼ºµÄÊôÐÔ¡£
8. ¹ØϵģÐÍÒªÇó¸÷Ôª×éµÄÿ¸ö·ÖÁ¿µÄÖµ±ØÐëÊÇÔ×ÓÐԵġ£¶ÔÔ×ÓÐÔ£¬ÏÂÃæÄÄÖÖ½âÊͲ»
ÕýÈ·£º
A ÿ¸öÊôÐÔ¶¼Ã»ÓÐÄÚ²¿½á¹¹¡£
B ÿ¸öÊôÐÔ¶¼²»¿É·Ö½â¡£
D ÊôÐÔÖµ²»ÔÊÐíΪNULL¡£
C ¸÷ÊôÐÔÖµÓ¦ÊôÓÚijÖÖ»ù±¾Êý¾ÝÀàÐÍ¡£
24