Éè¼Æ¸ßЧ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ͨÅä·û%ÔÚ´ÊÊ×ʱ£¬ÏµÍ³²»Ê¹ÓÃË÷
Ïà¹ØÎĵµ£º
Sql ServerÖеÄÈÕÆÚÓëʱ¼äº¯Êý
1. µ±Ç°ÏµÍ³ÈÕÆÚ¡¢Ê±¼ä
select getdate()
2. dateadd ÔÚÏòÖ¸¶¨ÈÕÆÚ¼ÓÉÏÒ»¶Îʱ¼äµÄ»ù´¡ÉÏ£¬·µ»ØÐ嵀 datetime Öµ
ÀýÈ磺ÏòÈÕÆÚ¼ÓÉÏ2Ìì
sele ......
µÚÒ»ÖÖ·½·¨: ʹÓÃ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 ......
ServerÊý¾Ý¿âÖÐʵÏÖ×îÓÅ×î¼òµÄÕû¸ö¹ý³Ì¡£
Ëã·¨ÊǼÆËã»ú¿ÆѧÖÐÒ»¸öÖØÒªµÄÑо¿·½Ïò£¬Êǽâ¾ö¸´ÔÓÎÊÌâµÄ¹Ø¼ü¡£ÔÚ¼ÆËã»úÊÀ½çÖУ¬Ëã·¨ÎÞ´¦²»ÔÚ¡£Êý¾Ý¿âÊÇ´æ´¢Êý¾ÝºÍÖ´ÐдóÅúÁ¿¼ÆËãµÄ³¡Ëù£¬ÔÚÊý¾Ý¿âÖÐʹÓÃһЩ¼òµ¥µÄSQLÃüÁ½øÐд洢¡¢²éѯ¡¢Í³¼Æ¡¢ÒÔ½â¾öÏÖʵÊÀ½çÖеÄÎÊÌâÒѾÊÇÂżû²»ÏÊ¡£Ëæ×ÅÊý¾ÝÁ¿µÄ´ó·ù¶ÈÔö¼ÓºÍÒµÎñ¹æ ......
¡¡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
¡ ......
1¡¢Ã»ÓÐË÷Òý»òÕßûÓÐÓõ½Ë÷Òý(ÕâÊDzéѯÂý×î³£¼ûµÄÎÊÌ⣬ÊdzÌÐòÉè¼ÆµÄȱÏÝ)¡¡¡¡
¡¡¡¡2¡¢I/OÍÌÍÂÁ¿Ð¡£¬ÐγÉÁËÆ¿¾±Ð§Ó¦¡£¡¡¡¡
¡¡¡¡3¡¢Ã»Óд´½¨¼ÆËãÁе¼Ö²éѯ²»ÓÅ»¯¡£¡¡¡¡
¡¡¡¡4¡¢ÄÚ´æ²»×ã¡¡¡¡
¡¡¡¡5¡¢ÍøÂçËÙ¶ÈÂý¡¡¡¡
¡¡¡¡6¡¢²éѯ³öµÄÊý¾ÝÁ¿¹ý´ó£¨¿ÉÒÔ²ÉÓöà´Î²éѯ£¬ÆäËûµÄ·½·¨½µµÍÊý¾ÝÁ¿£©¡¡¡¡
¡¡¡¡7¡¢Ëø»òÕßËÀËø ......