SQLiteÖв»Ö§³ÖµÄsqlÓï·¨
½ñÌìºÜ×ÔÈ»µÄÔÚдSqlÓï¾äµÄʱºòÓÃÁËTop£¬Ò»¿ªÊ¼Ã»·¢ÏÖÎÊÌ⣬ÒòΪÎÒ´ÓÊý¾Ý¿â¶Á³öµÄÖµÕýºÃÊÇ0£¬¶øÎÒϰ¹ß±äÁ¿¶¨ÒåµÄʱºòÒ²¶¼¸³Öµ0£¬¿ÉÊǵ½ÎÒ²»Òª0µÄʱºòÎҾͷ¢ÏÖÎÊÌâÁË¡£ºóÀ´²ÅÖªµÀ£¬¿É°®µÄСsqlite¾¹È»Óв»Ö§³ÖµÄsqlÓï·¨¡£
¡¡¡¡¿´µ½Ä³¸öÂÛ̳ÓиöÐÂÊÖÒ²·¢ÏÖÁËÕâ¸öÎÊÌâ²¢·¢ÌûÁË£¬ÏÂÃæÒ»Å£È˵Ļظ´ÊÇ“topÊÇÄļҵĹؼü´Ê?sqlite¼ÒûÓÐÕâ´ÊµÄ”£¬¹þ£¬ÔÀ´topÕâ´ÊÊÇmssqlÀï²ÅÓеÄ~
¡¡¡¡×îºó»¹Ïë˵£¬Êý¾Ý¿â¾ø¶ÔÊǺÜÖØÒªµÄÒ»¿é¡£ÎҵĻù´¡Óï·¨ºÜÀ¬»ø£¬¹ý¼¸ÌìÈÏÕæ¼Ç¼Ï¡£
¡¡¡¡Google֮ϵĽá¹û£º
1 TOP
¡¡¡¡ÕâÊÇÒ»¸ö´ó¼Ò¾³£Îʵ½µÄÎÊÌ⣬ÀýÈçÔÚSQLSERVERÖпÉÒÔʹÓÃÈçÏÂÓï¾äÀ´È¡µÃ¼Ç¼¼¯ÖеÄǰʮÌõ¼Ç¼£º
¡¡¡¡SELECT TOP 10 * from [index] ORDER BY indexid DESC;
¡¡¡¡µ«ÊÇÕâÌõSQLÓï¾äÔÚSQLiteÖÐÊÇÎÞ·¨Ö´Ðеģ¬Ó¦¸Ã¸ÄΪ£º
¡¡¡¡SELECT * from [index] ORDER BY indexid DESC limit 0,10;
¡¡¡¡ÆäÖÐlimit 0,10±íʾ´ÓµÚ0Ìõ¼Ç¼¿ªÊ¼£¬ÍùºóÒ»¹²¶ÁÈ¡10Ìõ
2 ´´½¨ÊÓͼ£¨Create View£©
¡¡¡¡SQLiteÔÚ´´½¨¶à±íÊÓͼµÄʱºòÓÐÒ»¸öBUG£¬ÎÊÌâÈçÏ£º
¡¡¡¡CREATE VIEW watch_single AS SELECT DISTINCTwatch_item.[watchid],watch_item.[itemid] from watch_item;
¡¡¡¡ÉÏÃæÕâÌõSQLÓï¾äÖ´Ðкó»áÏÔʾ³É¹¦£¬µ«ÊÇʵ¼ÊÉϳýÁË
¡¡¡¡SELECT COUNT(*) from [watch_single ] WHERE watch_ single.watchid = 1;
¡¡¡¡ÄÜÖ´ÐÐÖ®ÍâÊÇÎÞ·¨Ö´ÐÐÆäËûÈκÎÓï¾äµÄ¡£ÆäÔÒòÔÚÓÚ½¨Á¢ÊÓͼµÄʱºòÖ¸¶¨ÁË×Ö¶ÎËùÔڵıíÃû£¬¶øSQLite²¢²»ÄÜÕýÈ·µØÊ¶±ðËü¡£ËùÒÔÉÏÃæµÄ´´½¨Óï¾äÒª¸ÄΪ£º
¡¡¡¡CREATE VIEW watch_single AS SELECT DISTINCT [watchid],[itemid] from watch_item;
¡¡¡¡µ«ÊÇËæÖ®¶øÀ´µÄÎÊÌâÊÇÈç¹ûÊǶà±íµÄÊÓͼ£¬ÇÒ±í¼äÓÐÖØÃû×ֶεÄʱºò¸ÃÔõô°ì£¿
3 COUNT£¨DISTINCT column£©
¡¡¡¡SQLiteÔÚÖ´ÐÐÈçÏÂÓï¾äµÄʱºò»á±¨´í£º
¡¡¡¡¡¡¡¡SELECT COUNT(DISTINCT watchid) from [watch_item] WHERE watch_item.watchid = 1;
¡¡¡¡¡¡¡¡ÆäÔÒòÊÇSQLiteµÄËùÓÐÄÚÖú¯Êý¶¼²»Ö§³ÖDISTINCTÏÞ¶¨£¬ËùÒÔÈç¹ûҪͳ¼Æ²»Öظ´µÄ¼Ç¼ÊýµÄʱºò»á³öÏÖһЩÂé·³¡£±È½Ï¿ÉÐеÄ×ö·¨ÊÇÏȽ¨Á¢Ò»¸ö²»Öظ´µÄ¼Ç¼±íµÄÊÓͼ£¬È»ºóÔÙ¶Ô¸ÃÊÓͼ½øÐмÆÊý¡£
4 ÍâÁ¬½Ó
¡¡¡¡ËäÈ»SQLite¹Ù·½ÒѾÉù³ÆLEFT OUTER JOIN ÒѾʵÏÖ£¬µ«»¹Ã»ÓÐ RIGHT OUTER JOIN ºÍ FULL OUTER JOIN¡£µ«ÊÇʵ¼Ê²âÊÔ±íÃ÷ËÆºõ²¢²»Äܹ»Õý³£µÄ¹¤×÷¡£ÒÔÏÂÈýÌõÓï¾äÔÚÖ´ÐеÄʱºò¾ù»á±¨´í£º
¡¡¡¡SELECT tags.[tagid] from [tags],[tag_rss] WHERE tags.[tagid]
Ïà¹ØÎĵµ£º
ÎÊÌâÌá³ö
¡¡¡¡ÎÒʹÓùý¼¸´ÎSQL Server£¬µ«ËùÓÐÓëÊý¾Ý¿âµÄ½»»¥¶¼ÊÇͨ¹ýÓ¦ÓóÌÐòµÄ±àÂëÀ´ÊµÏֵġ£ÎÒ²»Öªµ½ÔÚÄÄÀïʹÓô洢¹ý³Ì£¬Ò²²»Á˽âʵÏÖ´æ´¢¹ý³ÌÐèÒª×öÄÄЩ¹¤×÷¡£Ï£ÍûÄÜÏêϸ˵Ã÷¡£
¡¡¡¡×¨¼Ò´ðÒÉ
¡¡¡¡´æ´¢¹ý³ÌÊÇ´æ´¢ÓÚÊý¾Ý¿âÖеÄÒ»×éT-SQLÓï¾ä¡£ÓÐÁË´æ´¢¹ý³ÌÖ®ºó£¬ÓëÊý¾Ý¿âµÄ½»»¥¾ÍûÓбØÒªÔÚ³ÌÐòÖÐдһ¶ÑµÄSQLÓï¾ä£¬¶ ......
ÔÚSQL serverÖо³£»áת»»stringµ½datetimeÀàÐÍ£¬×î³£Óõĺ¯Êý¾ÍÊÇConvert¡£ÄÇô¶ÔConvertÕâ¸öº¯Êý£¬²»µÃ²»ÏêϸµÄÑо¿Ò»Ï¡£ConvertÕâ¸öº¯ÊýµÄ¹¦ÄܺÜÇ¿´ó£¬¸ñʽÓֺܼòµ¥CONVERT ( data_type [ ( length ) ] , expression [ , style ] )¡£µ¥¾Í½«stringµ½datetimeÀàÐ͵Äת»»¾ÍÓкܶàÑùʽ¡£Èç: Convert(datetime, expression), ......
http://www.cnblogs.com/dwjaissk/archive/2006/07/25/459476.aspx
Ôö¼Ó×Ö¶Î
alter table docdsp add dspcode char(200)
ɾ³ý×Ö¶Î
ALTER TABLE
table_NAME DROP COLUMN column_NAME
ÐÞ¸Ä×Ö¶ÎÀàÐÍ
ALTER TABLE table_name
ALTER COLUMN column_name new_data_type
¸ÄÃû
sp_rename
¸ü¸Äµ±Ç°Êý¾Ý¿âÖÐ ......
¡¡²Ù×÷·ûÓÅ»¯
¡¡¡¡IN ²Ù×÷·û
¡¡¡¡ÓÃINд³öÀ´µÄSQLµÄÓŵãÊDZȽÏÈÝÒ×д¼°ÇåÎúÒ×¶®£¬Õâ±È½ÏÊʺÏÏÖ´úÈí¼þ¿ª·¢µÄ·ç¸ñ¡£
¡¡¡¡µ«ÊÇÓÃINµÄSQLÐÔÄÜ×ÜÊDZȽϵ͵쬴ÓORACLEÖ´ÐеIJ½ÖèÀ´·ÖÎöÓÃINµÄSQLÓë²»ÓÃINµÄSQLÓÐÒÔÏÂÇø±ð£º
¡¡¡¡ORACLEÊÔͼ½«Æäת»»³É¶à¸ö±íµÄÁ¬½Ó£¬Èç¹ûת»»²»³É¹¦ÔòÏÈÖ´ÐÐINÀïÃæµÄ×Ó²éѯ£¬ÔÙ²éѯÍâ²ãµÄ±í¼Ç¼£ ......
ÔÚwindows XP SP3 ÖÐÊÇ¿ÉÒÔ°²×° SQL Server 2005 µÄ²»»áÓмæÈÝÎÊÌâ ÎÒÔÚ°²×°µÄʱºò¾ÍʲôÎÊÌⶼû¿´µ½ °²×°ºÃºó¸½¼ÓÒ»¸öÊý¾Ý¿â ÓÃBorland JBuilder 2006 Enterprise ºÍeclipse ҲûÎÊÌâ
SQL2005 ·ÖÎå¸ö°æ±¾£¬ÈçÏÂËùÁУ¬
¡¡¡¡1.Enterprise(ÆóÒµ°æ),
2.Development(¿ª·¢°æ),
¡¡¡¡3.Workgroup,(¹¤×÷Ⱥ°æ)
¡¡¡¡4.St ......