SQLÓïÑÔ»ù´¡¿¼ºË(¶þ)(oracle)
2.¸ù¾ÝOracle Êý¾Ý¿âscott ģʽϵÄemp ±íºÍdept ±í£¬Íê³ÉÏÂÁвÙ×÷£º
(1) ²éѯ20ºÅ²¿ÃŵÄËùÓÐÔ±¹¤ÐÅÏ¢£»
(2) ²éѯËùÓй¤ÖÖΪCLERK µÄÔ±¹¤µÄÔ±¹¤ºÅ¡¢Ô±¹¤ÃûºÍ²¿Ãźţ»
(3) ²éѯ½±½ðCOMM ¸ßÓÚ¹¤×ÊSAL µÄÔ±¹¤ÐÅÏ¢£»
(4) ²éѯ½±½ð¸ßÓÚ¹¤×ʵÄ20%µÄÔ±¹¤ÐÅÏ¢£»
(5) ²éѯ10ºÅ²¿ÃÅÖй¤ÖÖΪMANAGER ºÍ20ºÅ²¿ÃÅÖй¤ÖÖΪCLERK µÄÔ±¹¤µÄÐÅÏ¢£»
(6) ²éѯËùÓй«ÖÚ²»ÊÇMANAGER ºÍCLERK £¬ÇÒ¹¤×Ê´óÓÚ»òµÈÓÚ2000µÄÔ±¹¤µÄÏêϸÐÅÏ¢£»
(7) ²éѯÓн±½ðµÄÔ±¹¤µÄ²»Í¬¹¤ÖÖ£»
(8) ²éѯËùÓÐÔ±¹¤¹¤×÷Óë½±½ðµÄºÍ£»
(9) ²éѯûÓн±½ð»ò½±½ðµÍÓÚ100µÄÔ±¹¤ÐÅÏ¢£»
(10)²éѯ¸÷Ôµ¹ÊýµÚ¶þÌìÈëÖ°µÄÔ±¹¤ÐÅÏ¢£»
(11)²éѯ¹¤Áä´óÓÚ»òµÈÓÚ10ÄêµÄÔ±¹¤ÐÅÏ¢£»
(12)²éѯԱ¹¤ÐÅÏ¢£¬ÒªÇóÒÔÊ××Öĸ´óдµÄ·½Ê½ÏÔʾËùÓÐÔ±¹¤µÄÐÕÃû£»
(13)²éѯԱ¹¤ÃûÕýºÃΪ6¸ö×ÖĸµÄÔ±¹¤ÐÅÏ¢£»
(14)²éѯԱ¹¤Ãû×ÖÖв»°üº¬×ÖĸS µÄÔ±¹¤£»
(15)²éѯԱ¹¤ÐÕÃûµÄµÚ¶þ¸ö×ÖĸΪM µÄÔ±¹¤ÐÅÏ¢£»
(16)²éѯËùÓÐÔ±¹¤ÐÕÃûµÄÇ°Èý¸ö×Ö·û£»
(17)²éѯËùÓÐÔ±¹¤µÄÐÕÃû£¬Èç¹û°üº¬×Öĸs £¬ÔòÓÃS Ìæ»»£»
(18)²éѯԱ¹¤µÄÐÕÃûºÍÈëÖ°ÈÕÆÚ£¬²¢°´ÈëÖ°ÈÕÆÚ´ÓÏȵ½ºó½øÐÐÅÅÐò£»
(19)ÏÔʾËùÓÐÔ±¹¤µÄÐÕÃû¡¢¹¤ÖÖ¡¢¹¤×ʺ;©½ò£¬°´¹¤ÖÖ½µÐòÅÅÐò£¬Èô¹¤ÖÖÏàͬÔò°´¹¤×ÊÉýÐòÅÅÐò£»
(20)ÏÔʾËùÓÐÔ±¹¤µÄÐÕÃû¡¢ÈëÖ°µÄÄê·ÝºÍÔ·ݣ¬°´ÈëÖ°ÈÕÆÚËùÔÚµÄÔ·ÝÅÅÐò£¬ÈôÔ·ÝÏàͬÔò°´ÈëÖ°µÄÄê·ÝÅÅÐò£»
(21)²éѯÔÚ2Ô·ÝÈëÖ°µÄËùÓÐÔ±¹¤ÐÅÏ¢£»
(22)²éѯËùÓÐÔ±¹¤ÈëÖ°ÒÔÀ´µÄ¹¤×÷ÆÚÏÞ£¬ÓÓ**Äê**ÔÂ**ÈÕ”µÄÐÎʽ±íʾ£»
(23)²éѯÖÁÉÙÓÐÒ»¸öÔ±¹¤µÄ²¿ÃÅÐÅÏ¢£»
(24)²éѯ¹¤×ʱÈSMITH Ô±¹¤ ¹¤×ʸߵÄËùÓÐÔ±¹¤ÐÅÏ¢£»
(25)²éѯËùÓÐÔ±¹¤µÄÐÕ
Ïà¹ØÎĵµ£º
where 1=1ÓÐʲôÓã¿ÔÚSQLÓïÑÔÖУ¬Ð´Õâôһ¾ä»°¾Í¸úûдһÑù¡£
select * from table1 where 1=1Óëselect * from table1ÍêȫûÓÐÇø±ð£¬ÉõÖÁ»¹ÓÐÆäËûÐí¶àд·¨£¬1<>2£¬'a'='a','a'<>'b'£¬ÆäÄ¿µÄ¾ÍÖ»ÓÐÒ»¸ö£¬whereµÄÌõ¼þΪÓÀÕ棬µÃµ½µÄ½á¹û¾ÍÊÇδ¼ÓÔ¼ÊøÌõ¼þµÄ¡£
ÔÚSQL×¢Èëʱ»áÓõ½Õâ¸ö£¬ÀýÈçselect * from table1 ......
µÚ¾ÅÕÂ½Ú :´¥·¢Æ÷±àÂë
´¥·¢Æ÷ÊÇ´æ´¢ÔÚÊý¾Ý¿â´æÖÐÒþʽµÄÔËÐлòÕß±»´¥·¢µÄÒ»¶Î³ÌÐò£¬µ±Ä³Ð©Ê¼þ·¢Éú¡£Í¨³£Çé¿öÏ£¬´¥·¢Æ÷ÖÐÖ§³Ö¿ÉÖ´ÐеÄÒ»¶Îpl/sql´úÂë¿é£¬µ±Ò»¸öinsert,update,»òÕßdeleteÃüÃû·¢ÉúÔÚÒ»¸ö±í»òÕßÊÓͼÉϵÄʱºò¡£ÆäÒ²Ö§³ÖϵͳºÍÊý¾Ý¿â»òÕß·½°¸Ê¼þ.oracleÊý¾Ý¿âÒ²Ö§³Ö´¥·¢Æ÷Öк¬Ó ......
½ñÌìÔÚ¹äÂÛ̳µÄʱºò¿´µ½shiyiwanͬѧдÁËÒ»¸öºÜ¼òµ¥µÄÓï¾ä£¬¿ÉÊÇorder byºóÃæµÄÐÎʽȴ±È½ÏÐÂÓ±£¨¶ÔÓÚÎÒÀ´ËµÅ¶£©£¬ÒÔÇ°´ÓÀ´Ã»¿´¹ýÕâÖÖÓ÷¨£¬¾ÍÏë¼ÇÏÂÀ´£¬ÕýºÃ×ܽáÒ»ÏÂORDER BYµÄ֪ʶ¡£
1¡¢ORDER BY ÖйØÓÚNULLµÄ´¦Àí
ȱʡ´¦Àí£¬OracleÔÚOrder by ʱÈÏΪnullÊÇ×î´óÖµ£¬ËùÒÔÈç¹ûÊÇASCÉýÐòÔòÅÅÔÚ×îºó£¬DESC½µÐòÔòÅÅÔÚ×îÇ°¡£
......
Êý¾Ý×ÖµäÊÇOracle´æ·ÅÓйØÊý¾Ý¿âÐÅÏ¢µÄµØ·½£¬ÆäÓÃ;ÊÇÓÃÀ´ÃèÊöÊý¾ÝµÄ¡£±ÈÈçÒ»¸ö±íµÄ´´½¨ÕßÐÅÏ¢£¬´´½¨Ê±¼äÐÅÏ¢£¬ËùÊô±í¿Õ¼äÐÅÏ¢£¬Óû§·ÃÎÊȨÏÞÐÅÏ¢µÈ¡£µ±Óû§ÔÚ¶ÔÊý¾Ý¿âÖеÄÊý¾Ý½øÐвÙ×÷ʱÓöµ½À§ÄѾͿÉÒÔ·ÃÎÊÊý¾Ý×ÖµäÀ´²é¿´ÏêϸµÄÐÅÏ¢¡£
OracleÖеÄÊý¾Ý×ÖµäÓо²Ì¬ºÍ¶¯Ì¬Ö®·Ö¡£¾²Ì¬Êý¾Ý×ÖµäÖ÷ÒªÊÇÔÚÓû§·ÃÎÊÊý¾Ý×Öµäʱ²»»á·¢ ......
µÚÒ»²¿·Ö¡¢SQL&PL/SQL
[Q]ÔõôÑù²éѯÌØÊâ×Ö·û£¬ÈçͨÅä·û%Óë_
[A]select * from table where name like 'A_%' escape ''
[Q]ÈçºÎ²åÈëµ¥ÒýºÅµ½Êý¾Ý¿â±íÖÐ
[A]¿ÉÒÔÓÃASCIIÂë´¦Àí£¬ÆäËüÌØÊâ×Ö·ûÈç&Ò²Ò»Ñù£¬Èç
insert into t values('i'||chr(39)||'m'); -- chr(39)´ú±í×Ö·û'
»òÕßÓÃÁ½¸öµ¥ÒýºÅ±íʾһ¸ö
or ......