sql²éѯÓÅ»¯
1¡¢ ÓóÌÐòÖУ¬±£Ö¤ÔÚʵÏÖ¹¦ÄܵĻù´¡ÉÏ£¬¾¡Á¿¼õÉÙ¶ÔÊý¾Ý¿âµÄ·ÃÎÊ´ÎÊý£»Í¨¹ýËÑË÷²ÎÊý£¬¾¡Á¿¼õÉÙ¶Ô±íµÄ·ÃÎÊÐÐÊý,×îС»¯½á¹û¼¯£¬´Ó¶ø¼õÇáÍøÂ縺µ££»Äܹ»·Ö¿ªµÄ²Ù×÷¾¡Á¿·Ö¿ª´¦Àí£¬Ìá¸ßÿ´ÎµÄÏìÓ¦ËÙ¶È£»ÔÚÊý¾Ý´°¿ÚʹÓÃSQLʱ£¬¾¡Á¿°ÑʹÓõÄË÷Òý·ÅÔÚÑ¡ÔñµÄÊ×ÁУ»Ëã·¨µÄ½á¹¹¾¡Á¿¼òµ¥£»ÔÚ²éѯʱ£¬²»Òª¹ý¶àµØÊ¹ÓÃͨÅä·ûÈçSELECT * from T1Óï¾ä£¬ÒªÓõ½¼¸ÁоÍÑ¡Ôñ¼¸ÁÐÈ磺SELECT COL1,COL2 from T1£»ÔÚ¿ÉÄܵÄÇé¿öϾ¡Á¿ÏÞÖÆ¾¡Á¿½á¹û¼¯ÐÐÊýÈ磺SELECT TOP 300 COL1,COL2,COL3 from T1,ÒòΪijЩÇé¿öÏÂÓû§ÊDz»ÐèÒªÄÇô¶àµÄÊý¾ÝµÄ¡£²»ÒªÔÚÓ¦ÓÃÖÐʹÓÃÊý¾Ý¿âÓα꣬ÓαêÊǷdz£ÓÐÓõŤ¾ß£¬µ«±ÈʹÓ󣹿µÄ¡¢ÃæÏò¼¯µÄSQLÓï¾äÐèÒª¸ü´óµÄ¿ªÏú£»°´ÕÕÌØ¶¨Ë³ÐòÌáÈ¡Êý¾ÝµÄ²éÕÒ¡£
2¡¢ ±ÜÃâʹÓò»¼æÈݵÄÊý¾ÝÀàÐÍ¡£ÀýÈçfloatºÍint¡¢charºÍvarchar¡¢binaryºÍvarbinaryÊDz»¼æÈݵġ£Êý¾ÝÀàÐ͵IJ»¼æÈÝ¿ÉÄÜʹÓÅ»¯Æ÷ÎÞ·¨Ö´ÐÐһЩ±¾À´¿ÉÒÔ½øÐеÄÓÅ»¯²Ù×÷¡£ÀýÈç:
SELECT name from employee WHERE salary £¾ 60000
ÔÚÕâÌõÓï¾äÖÐ,Èçsalary×Ö¶ÎÊÇmoneyÐ͵Ä,ÔòÓÅ»¯Æ÷ºÜÄÑ¶ÔÆä½øÐÐÓÅ»¯,ÒòΪ60000ÊǸöÕûÐÍÊý¡£ÎÒÃÇÓ¦µ±ÔÚ±à³Ìʱ½«ÕûÐÍת»¯³ÉΪǮ±ÒÐÍ,¶ø²»ÒªµÈµ½ÔËÐÐʱת»¯¡£
3¡¢ ¾¡Á¿±ÜÃâÔÚWHERE×Ó¾äÖжÔ×ֶνøÐк¯Êý»ò±í´ïʽ²Ù×÷£¬Õ⽫µ¼ÖÂÒýÇæ·ÅÆúʹÓÃË÷Òý¶ø½øÐÐÈ«±íɨÃè¡£È磺
SELECT * from T1 WHERE F1/2=100
Ó¦¸ÄΪ:
SELECT * from T1 WHERE F1=100*2
SELECT * from RECORD WHERE SUBSTRING(CARD_NO,1,4)=’5378’
Ó¦¸ÄΪ:
SELECT * from RECORD WHERE CARD_NO LIKE ‘5378%’
SELECT member_number, first_name, last_name from members
WHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21
Ó¦¸ÄΪ:
SELECT member_number, first_name, last_name from members
WHERE dateofbirth < DATEADD(yy,-21,GETDATE())
¼´£ºÈκζÔÁеIJÙ×÷¶¼½«µ¼Ö±íɨÃ裬Ëü°üÀ¨Êý¾Ý¿âº¯Êý¡¢¼ÆËã±í´ïʽµÈµÈ£¬²éѯʱҪ¾¡¿ÉÄܽ«²Ù×÷ÒÆÖÁµÈºÅÓұߡ£
4¡¢ ±ÜÃâʹÓÃ!=»ò£¼£¾¡¢IS NULL»òIS NOT NULL¡¢IN £¬NOT INµÈÕâÑùµÄ²Ù×÷·û,ÒòΪÕâ»áʹϵͳÎÞ·¨Ê¹ÓÃË÷Òý,¶øÖ»ÄÜÖ±½ÓËÑË÷±íÖеÄÊý¾Ý¡£ÀýÈç:
SELECT id from employee WHERE id != 'B%'
ÓÅ»¯Æ÷½«ÎÞ·¨Í¨¹ýË÷ÒýÀ´È·¶¨½«ÒªÃüÖеÄÐÐÊý,Òò´ËÐèÒªËÑË÷¸Ã±íµÄËùÓÐÐС£
5¡¢ ¾¡Á¿Ê¹ÓÃÊý×ÖÐÍ×ֶΣ¬Ò»²¿·Ö¿ª·¢ÈËÔ±ºÍÊý¾Ý¿â¹ÜÀíÈËԱϲ
Ïà¹ØÎĵµ£º
TABLE MASTER ×Ö¶Î ID DETAIL.....
TABLE BIZ ×Ö¶Î SYS_ID CODE_ID .......
²éѯʱÐèÒªµÄÊÇIDµÄÃèÊö
1,SELECT A.SYS_ID,A.CODE_ID,B.DETAIL,C.DETAIL...... from BIZ A,MASTER B,MASTER C WHERE A.SYS_ID=B.ID AND A.CODE_ID=C.ID
2,SELECT SYS_ID,(SELECT DETAIL from MASTER ......
selectÓï¾äÖÐÖ»ÄÜʹÓÃsqlº¯Êý¶Ô×ֶνøÐвÙ×÷£¨Á´½Ósql server£©£¬
select ×Ö¶Î1 from ±í1 where ×Ö¶Î1.IndexOf("ÔÆ")=1;
ÕâÌõÓï¾ä²»¶ÔµÄÔÒòÊÇindexof£¨£©º¯Êý²»ÊÇsqlº¯Êý£¬¸Ä³Ésql¶ÔÓ¦µÄº¯Êý¾Í¿ÉÒÔÁË¡£
left£¨£©ÊÇsqlº¯Êý¡£
select ×Ö¶Î1 from ±í1 where charindex£¨'ÔÆ',×Ö¶Î1£©=1;
×Ö·û´®º¯Êý¶Ô¶þ½øÖÆÊý¾Ý¡¢×Ö·û´®º ......
*
sql xml ÈëÃÅ:
--by jinjazz
--http://blog.csdn.net/jinjazz
1¡¢xml: ÄÜÈÏÊ¶ÔªËØ¡¢ÊôÐÔºÍÖµ
2¡¢xpath: ѰַÓïÑÔ£¬ÀàËÆwind ......
sql serverºÍoracleÐÐתÁеÄÒ»ÖÖµäÐÍ·½·¨
ǰÑÔ£ºÍøÉÏÓв»ÉÙÎÄÕÂÊǽ²ÐÐתÁе쬵«ÊǴ󲿷ֶ¼ÊÇÖ±½ÓÌù´úÂ룬ºöÊÓÁËÖмä¹ý³Ì£¬±¾ÈË×Ô¼ºË¼¿¼ÁËÏÂΪʲôҪÕâÑùʵÏÖ£¬²¢ÇÒ×öÁËÈçϵıʼǣ¬¶ÔÓÐЩ¶®µÄÈËÀ´Ëµ¿ÉÄÜûÓмÛÖµ£¬Ï£Íû¶Ô»¹²»¶®µÄÈËÓÐÒ»µã½è¼øÒâÒå¡£
¶ÔÓÚÓÐЩҵÎñÀ´Ëµ£¬Êý¾ÝÔÚ±íÖеĴ洢ºÍÆä×îÖÕµÄGrid±íÏÖÇ¡ºÃÏ൱ÓÚ°ÑÔ ......
ÆÕͨÐÐÁÐת»»
ÎÊÌ⣺¼ÙÉèÓÐÕÅѧÉú³É¼¨±í(tb)ÈçÏÂ:
ÐÕÃû ¿Î³Ì ·ÖÊý
ÕÅÈý ÓïÎÄ 74
ÕÅÈý Êýѧ 83
ÕÅÈý ÎïÀí 93
ÀîËÄ ÓïÎÄ 74
ÀîËÄ Êýѧ 84
ÀîËÄ ÎïÀí 94
Ïë±ä³É(µÃµ½ÈçϽá¹û)£º
ÐÕÃû ÓïÎÄ Êýѧ ÎïÀí
---- ---- ---- ----
ÀîËÄ 74 84 94
ÕÅÈý 74 83 93
-------------------
*/
create table tb(Ð ......