Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

SQL ¶¯Ì¬ÅÅÐòµÄ·½·¨

ÔÚ¹«¹²ÐÂÎÅ×éÖУ¬Ò»¸ö¾­³£³öÏÖµÄÎÊÌâÊÇ“ÔõÑù²ÅÄܸù¾Ý´«µÝ¸ø´æ´¢¹ý³ÌµÄ²ÎÊý·µ»ØÒ»¸öÅÅÐòµÄÊä³ö£¿”¡£ÔÚһЩ¸ßˮƽר¼ÒµÄ°ïÖú֮ϣ¬ÎÒÕûÀí³öÁËÕâ¸öÎÊÌâµÄ¼¸ÖÖ½â¾ö·½°¸¡£
Ò»¡¢ÓÃIF...ELSEÖ´ÐÐÔ¤ÏȱàдºÃµÄ²éѯ
¡¡¡¡¶ÔÓÚ´ó¶àÊýÈËÀ´Ëµ£¬Ê×ÏÈÏëµ½µÄ×ö·¨Ò²ÐíÊÇ£ºÍ¨¹ýIF...ELSEÓï¾ä£¬Ö´Ðм¸¸öÔ¤ÏȱàдºÃµÄ²éѯÖеÄÒ»¸ö¡£ÀýÈ磬¼ÙÉèÒª´ÓNorthwindÊý¾Ý¿â²éѯµÃµ½Ò»¸ö»õÖ÷£¨Shipper£©µÄÅÅÐòÁÐ±í£¬·¢³öµ÷ÓõĴúÂëÒÔ´æ´¢¹ý³Ì²ÎÊýµÄÐÎʽָ¶¨Ò»¸öÁУ¬´æ´¢¹ý³Ì¸ù¾ÝÕâ¸öÁÐÅÅÐòÊä³ö½á¹û¡£Listing 1ÏÔʾÁËÕâÖÖ´æ´¢¹ý³ÌµÄÒ»¸ö¿ÉÄܵÄʵÏÖ£¨GetSortedShippers´æ´¢¹ý³Ì£©¡£
¡¾Listing 1: ÓÃIF...ELSEÖ´Ðжà¸öÔ¤ÏȱàдºÃµÄ²éѯÖеÄÒ»¸ö¡¿
CREATE PROC GetSortedShippers
@OrdSeq AS int
AS
IF @OrdSeq = 1
SELECT * from Shippers ORDER BY ShipperID
ELSE IF @OrdSeq = 2
SELECT * from Shippers ORDER BY CompanyName
ELSE IF @OrdSeq = 3
SELECT * from Shippers ORDER BY Phone
¡¡¡¡ÕâÖÖ·½·¨µÄÓŵãÊÇ´úÂëºÜ¼òµ¥¡¢ºÜÈÝÒ×Àí½â£¬SQL ServerµÄ²éѯÓÅ»¯Æ÷Äܹ»ÎªÃ¿Ò»¸öSELECT²éѯ´´½¨Ò»¸ö²éѯÓÅ»¯¼Æ»®£¬È·±£´úÂë¾ßÓÐ×îÓŵÄÐÔÄÜ¡£ÕâÖÖ·½·¨×îÖ÷ÒªµÄȱµãÊÇ£¬Èç¹û²éѯµÄÒªÇó·¢ÉúÁ˸ı䣬Äã±ØÐëÐ޸Ķà¸ö¶ÀÁ¢µÄSELECT²éѯ——ÔÚÕâÀïÊÇÈý¸ö¡£
¶þ¡¢ÓÃÁÐÃû×Ö×÷Ϊ²ÎÊý
¡¡¡¡ÁíÍâÒ»¸öÑ¡ÔñÊÇÈòéѯÒÔ²ÎÊýµÄÐÎʽ½ÓÊÕÒ»¸öÁÐÃû×Ö¡£Listing 2ÏÔʾÁËÐ޸ĺóµÄGetSortedShippers´æ´¢¹ý³Ì¡£CASE±í´ïʽ¸ù¾Ý½ÓÊÕµ½µÄ²ÎÊý£¬È·¶¨SQL ServerÔÚORDER BY×Ó¾äÖÐʹÓÃÄÄÒ»¸öÁÐÖµ¡£×¢Ò⣬ORDER BY×Ó¾äÖеıí´ïʽ²¢Î´ÔÚSELECTÇåµ¥ÖгöÏÖ¡£ÔÚANSI SQL-92±ê×¼ÖУ¬ORDER BY×Ó¾äÖв»ÔÊÐí³öÏÖûÓÐÔÚSELECTÇåµ¥ÖÐÖ¸¶¨µÄ±í´ïʽ£¬µ«ANSI SQL-99±ê×¼ÔÊÐí¡£SQL ServerÒ»Ö±ÔÊÐíÕâÖÖÓ÷¨¡£
¡¾Listing 2£ºÓÃÁÐÃû×Ö×÷Ϊ²ÎÊý£¬µÚÒ»´Î³¢ÊÔ¡¿
CREATE PROC GetSortedShippers
@ColName AS sysname
AS
SELECT *
from Shippers
ORDER BY
CASE @ColName
WHEN 'ShipperID' THEN ShipperID
WHEN 'CompanyName' THEN CompanyName
WHEN 'Phone' THEN Phone
ELSE NULL
END
¡¡¡¡ÏÖÔÚ£¬ÎÒÃÇÀ´ÊÔÒ»ÏÂеĴ洢¹ý³Ì£¬ÒÔ²ÎÊýµÄÐÎʽָ¶¨ShipperIDÁУº
EXEC GetSortedShippers 'ShipperID'
¡¡¡¡´ËʱһÇÐÕý³£¡£µ«ÊÇ£¬µ±ÎÒÃÇÊÓͼ°ÑCompanyNameÁÐ×÷Ϊ²ÎÊýµ÷Óô洢¹ý³Ìʱ£¬Ëü²»ÔÙÓÐЧ£º
EXEC GetSortedShippers 'CompanyName'
¡¡¡¡×Ðϸ¿´Ò»Ï´íÎóÐÅÏ¢£º
Server


Ïà¹ØÎĵµ£º

ʹÓÃSQLServerÄ£°åÀ´Ð´¹æ·¶µÄSQLÓï¾ä

Èç¹ûÄã¾­³£Óöµ½ÏÂÃæµÄÎÊÌ⣬Äã¾ÍÒª¿¼ÂÇʹÓÃSQL ServerµÄÄ£°åÀ´Ð´¹æ·¶µÄSQLÓï¾äÁË£º
SQL³õѧÕß¡£
¾­³£Íü¼Ç³£ÓõÄDML»òÊÇDDL SQL Óï¾ä¡£
ÔÚ¶àÈË¿ª·¢Î¬»¤µÄSQLÖУ¬Ã¿¸öÈ˶¼ÓÐ×Ô¼ºµÄSQLϰ¹ß£¬Ã»ÓÐÒ»Ì×ͳһµÄ¹æ·¶¡£
ÔÚSQL Server Management StudioÖУ¬ÒѾ­¸ø´ó¼ÒÌṩÁ˺ܶೣÓõÄÏÖ³ÉSQL¹æ·¶Ä£°å¡£
SQL Server Management ......

SQL 2K Êý¾ÝÀàÐÍ £¨×ª£©

£¨×ªÔØ£©SQL 2K Êý¾ÝÀàÐÍ 
(1)char¡¢varchar¡¢textºÍnchar¡¢nvarchar¡¢ntext
charºÍvarcharµÄ³¤¶È¶¼ÔÚ1µ½8000Ö®¼ä£¬ËüÃǵÄÇø±ðÔÚÓÚcharÊǶ¨³¤×Ö·ûÊý¾Ý£¬¶øvarcharÊDZ䳤×Ö·ûÊý¾Ý¡£Ëùν¶¨³¤¾ÍÊdz¤¶È¹Ì¶¨µÄ£¬µ±ÊäÈëµÄÊý¾Ý³¤¶ÈûÓдﵽָ¶¨µÄ³¤¶Èʱ½«×Ô¶¯ÒÔÓ¢ÎĿոñÔÚÆäºóÃæÌî³ä£¬Ê¹³¤¶È´ïµ½ÏàÓ¦µÄ³¤¶È£»¶ø±ä³¤×Ö·ûÊý¾Ý ......

sqlÊý¾Ý¿âÖÃÒɽâ¾ö°ì·¨

×¢'svw'Ϊ³öÎÊÌâµÄÊý¾Ý¿â,´Ë·½Ê½¶Ôsql7.0ÒÔÉϰ汾ÓÐЧ,ÆäËüµÍ°æ±¾Îª²âÊÔ
sp_configure  'allow',1 
go
reconfigure  with  override 
go
update  sysdatabases  set  status=32768  where  name='svw' 
go
dbcc  rebuild_log('svw','D:\mssql7\data ......

SQLÃæÊÔÌâÄ¿Óë³£¼ûÎÊÌâ

SQLÃæÊÔÌâ
Sql³£ÓÃÓï·¨
ÏÂÁÐÓï¾ä²¿·ÖÊÇMssqlÓï¾ä£¬²»¿ÉÒÔÔÚaccessÖÐʹÓá£
 
SQL·ÖÀࣺ
DDL—Êý¾Ý¶¨ÒåÓïÑÔ(CREATE£¬ALTER£¬DROP£¬DECLARE)
DML—Êý¾Ý²Ù×ÝÓïÑÔ(SELECT£¬DELETE£¬UPDATE£¬INSERT)
DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK) Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º
1¡¢ËµÃ÷£º´´½¨ ......

sql¶à±íÁ¬½Ó²éѯ

inner join,full outer join,left join,right jion
ÄÚ²¿Á¬½Ó inner join Á½±í¶¼Âú×ãµÄ×éºÏ
full outer È«Á¬ Á½±íÏàͬµÄ×éºÏÔÚÒ»Æð£¬A±íÓУ¬B±íûÓеÄÊý¾Ý£¨ÏÔʾΪnull£©,ͬÑùB±íÓÐ
A±íûÓеÄÏÔʾΪ(null)
A±í left join  B±í ×óÁ¬,ÒÔA±íΪ»ù´¡£¬A±íµÄÈ«²¿Êý¾Ý£¬B±íÓеÄ×éºÏ¡£Ã»ÓеÄΪnull
A±í right join B±í ÓÒÁ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ