Éè¼Æ¸ßЧsqlÒ»°ã¾Ñé̸ £¨×ª£©
1²»ÓÃÔÚsqlÓï¾äʹÓÃϵͳĬÈϵı£Áô¹Ø¼ü×Ö
2¾¡Á¿ÓÃexists ºÍ not exists ´úÌæ in ºÍ not in
ÕâÌõÔÚsql2005Ö®ºó£¬ÔÚË÷ÒýÒ»Ñù£¬Í³¼ÆÐÅÏ¢Ò»ÑùµÄÇé¿öÏ£¬exists £¬inЧ¹ûÊÇÒ»ÑùµÄ¡£
ÒÔAdventureWorksÊý¾Ý¿âΪÀý£¬²éѯÔÚHumanResources.EmployeeAddressÓеØÖ·µÄEmployeeÐÅÏ¢£¬
ÓÃin Óï¾äÈçÏ£º
SET STATISTICS IO ON
SELECT * from HumanResources.Employee
WHERE EmployeeID IN (SELECT EmployeeID from HumanResources.EmployeeAddress ea)
SET STATISTICS IO OFF
Ö´Ðкó£¬ÏûÏ¢ÈçÏ£º
(290 ÐÐÊÜÓ°Ïì)
±í'EmployeeAddress'¡£É¨Ãè¼ÆÊý1£¬Âß¼¶ÁÈ¡4 ´Î£¬ÎïÀí¶ÁÈ¡0 ´Î£¬Ô¤¶Á0 ´Î£¬lob Âß¼¶ÁÈ¡0 ´Î£¬lob ÎïÀí¶ÁÈ¡0 ´Î£¬lob Ô¤¶Á0 ´Î¡£
±í'Employee'¡£É¨Ãè¼ÆÊý1£¬Âß¼¶ÁÈ¡9 ´Î£¬ÎïÀí¶ÁÈ¡0 ´Î£¬Ô¤¶Á0 ´Î£¬lob Âß¼¶ÁÈ¡0 ´Î£¬lob ÎïÀí¶ÁÈ¡0 ´Î£¬lob Ô¤¶Á0 ´Î¡£
Ö´Ðмƻ®Èçͼ
ÓÃexists £¬Óï¾äÈçÏ£¬
SET STATISTICS IO ON
SELECT * from HumanResources.Employee
WHERE EXISTS(SELECT EmployeeID from HumanResources.EmployeeAddress ea
WHERE
HumanResources.Employee.EmployeeID=ea.EmployeeID)
SET STATISTICS IO OFF
Ö´Ðкó£¬ÏûÏ¢ÈçÏ£º
(290 ÐÐÊÜÓ°Ïì)
±í'EmployeeAddress'¡£É¨Ãè¼ÆÊý1£¬Âß¼¶ÁÈ¡4 ´Î£¬ÎïÀí¶ÁÈ¡0 ´Î£¬Ô¤¶Á0 ´Î£¬lob Âß¼¶ÁÈ¡0 ´Î£¬lob ÎïÀí¶ÁÈ¡0 ´Î£¬lob Ô¤¶Á0 ´Î¡£
±í'Employee'¡£É¨Ãè¼ÆÊý1£¬Âß¼¶ÁÈ¡9 ´Î£¬ÎïÀí¶ÁÈ¡0 ´Î£¬Ô¤¶Á0 ´Î£¬lob Âß¼¶ÁÈ¡0 ´Î£¬lob ÎïÀí¶ÁÈ¡0 ´Î£¬lob Ô¤¶Á0 ´Î¡£
Ö´Ðмƻ®Èçͼ£º
3¾¡Á¿²»ÓÃselect * from …..,¶øҪд×Ö¶ÎÃû select field1,field2,…
ÕâÌõûʲôºÃ˵µÄ£¬Ö÷ÒªÊÇ°´Ðè²éѯ£¬²»Òª·µ»Ø²»±ØÒªµÄÁкÍÐС£
4ÔÚsql ²éѯÖÐÓ¦¾¡Á¿Ê¹ÓÃË÷ÒýÁÐÀ´¼Ó¿ì²éѯËÙ¶È
5ÈκÎÔÚOrder by Óï¾äµÄ·ÇË÷ÒýÏî»òÕßÓмÆËã±í´ïʽ¶¼½«½µµÍ²éѯËÙ¶È
6ÈκÎÔÚwhere×Ó¾äÖÐʹÓÃis null »ò is not null µÄÓï¾ä²»ÔÊÐíʹÓÃË÷Òý£¬Ð§ÂʽϵÍ
7ͨÅä·û%ÔÚ´ÊÊ×ʱ£¬ÏµÍ³²»Ê¹ÓÃË÷
Ïà¹ØÎĵµ£º
µÚÒ»ÖÖ·½·¨: ʹÓÃoutput²ÎÊý
USE AdventureWorks;
GO
IF OBJECT_ID ( 'Production.usp_GetList', 'P' ) IS NOT NULL
DROP PROCEDURE Production.usp_GetList;
GO
CREATE PROCEDURE Production.usp_GetList @product varchar(40)
, @maxprice money
&nb ......
Êý¾ÝÔ´......°üº¬ÎÞ·¨ÓÃÓÚ´¦Àí²Ù×÷µÄ ImpersonationMode¡£
½â¾ö·½·¨£º
·þÎñÆ÷Á¬½Ó¸ÄΪ“ʹÓ÷þÎñÕË»§”¡£
¾ßÌå²Ù×÷£º
ÔÚÊý¾ÝÔ´ÀïµÄÏîÄ¿Éϵã»÷ÓÒ¼ü->´ò¿ª->Êý¾ÝÔ´Éè¼ÆÆ÷ £¬ Ñ¡Ôñ“Ä£ÄâÐÅÏ¢”±êÇ©->Ñ¡Ôñ“ʹÓ÷þÎñÕË»§”£¬È·¶¨£¬Ö´Ðв¿Êð¡£ ......
ServerÊý¾Ý¿âÖÐʵÏÖ×îÓÅ×î¼òµÄÕû¸ö¹ý³Ì¡£
Ëã·¨ÊǼÆËã»ú¿ÆѧÖÐÒ»¸öÖØÒªµÄÑо¿·½Ïò£¬Êǽâ¾ö¸´ÔÓÎÊÌâµÄ¹Ø¼ü¡£ÔÚ¼ÆËã»úÊÀ½çÖУ¬Ëã·¨ÎÞ´¦²»ÔÚ¡£Êý¾Ý¿âÊÇ´æ´¢Êý¾ÝºÍÖ´ÐдóÅúÁ¿¼ÆËãµÄ³¡Ëù£¬ÔÚÊý¾Ý¿âÖÐʹÓÃһЩ¼òµ¥µÄSQLÃüÁ½øÐд洢¡¢²éѯ¡¢Í³¼Æ¡¢ÒÔ½â¾öÏÖʵÊÀ½çÖеÄÎÊÌâÒѾÊÇÂżû²»ÏÊ¡£Ëæ×ÅÊý¾ÝÁ¿µÄ´ó·ù¶ÈÔö¼ÓºÍÒµÎñ¹æ ......
µÚÒ»ÖÖ·½·¨: ʹÓÃoutput²ÎÊý
USE AdventureWorks;
GO
IF OBJECT_ID ( 'Production.usp_GetList', 'P' ) IS NOT NULL
DROP PROCEDURE Production.usp_GetList;
GO
CREATE PROCEDURE Production.usp_GetList @product varchar(40)
, @maxprice money
, ......
¡¡1¡¢varchar(max)¡¢nvarchar(max)ºÍvarbinary(max)Êý¾ÝÀàÐÍ×î¶à¿ÉÒÔ±£´æ2GBµÄÊý¾Ý£¬¿ÉÒÔÈ¡´útext¡¢ntext»òimageÊý¾ÝÀàÐÍ¡£
CREATE TABLE myTable
(
id INT,
content VARCHAR(MAX)
)
¡¡¡¡2¡¢XMLÊý¾ÝÀàÐÍ
¡¡¡¡XMLÊý¾ÝÀàÐÍÔÊÐíÓû§ÔÚSQL ServerÊý¾Ý¿âÖб£´æXMLƬ¶Î»òÎĵµ¡£
¡¡¡¡´íÎó´¦Àí Error Handling
¡ ......