SQL SERVER 2008 ±íÖµ²ÎÊý
/*
SQL SERVER 2008 ±íÖµ²ÎÊý
SQL SERVER ÒýÈëÁË¿¹ÒéÓÃÀ´½«Ðм¯´«Èëµ½´æ´¢¹ý³ÌºÍÓû§¶¨Ò庯ÊýµÄ±íÖµ²ÎÊý.
Õâ¸ö¹¦ÄÜ¿ÉÒÔʹ´æ´¢¹ý³ÌºÍº¯Êý¾ßÓзâ×°¶à¸öÐм¯µÄ¹¦ÄÜ,¶ø²»ÊDZØÐëÒ»ÐÐÒ»Ðеص÷
Êý¾ÝÐ޸Ĺý³ÌºÍ´©¼þ¶à¸öÊäÈë²ÎÊýÀ´ÉúÓ²µÄת»¯Îª¶àÐÐ.
ÎÒÃÇÔÚÓ¦ÓÃÖо³£Óõ½µÄ²åÈëʱ°Ñ´úÂë·â×°µ½´æ´¢¹ý³ÌÖС£
*/
CREATE DATABASE TESTDB
USE TESTDB
GO
CREATE TABLE USERINFO(USERID INT,USERNAME NVARCHAR(50))
GO
CREATE PROC USP_INSERT_USERINFO
@ID INT,
@NAME NVARCHAR(50)
AS
INSERT USERINFO
VALUES(@ID,@NAME)
GO
/*
ÉÏÃæµÄ»·¾³½¨Á¢ºÃºó£¬Èç¹ûÎÒÃÇÐèÒªÏò±íÖвåÈëÐÐÊý£¬¾ÍÐèÒªµ÷ÓôÎÕâ¸ö
´æ´¢¹ý³Ì¡£ÔÚ´ó¶àÊýÇé¿öÏÂÕâÑùµÄÇé¿öÊÇ¿ÉÒÔ½ÓÊܵģ¬Èç¹ûÄã¾³£ÐèÒªÒ»´Î²åÈë¶à
Ìõ¡£ÄÇô¾Í¿ÉÒÔÓÃÖÐÐÂÔöµÄ±íÖµ²ÎÊýÀàÐÍ£¬¿ÉÒÔ½«Òª²åÈëµÄÊý¾Ý´«Èëµ½±íÖµ²Î
ÊýÖУ¬È»ºóͨ¹ý±íÖµ²ÎÊýÒ»´ÎÐÔ²åÈëµÄ±íÖС£
ÏÂÃæÑÝʾ¸Ã²ÎÊýÀàÐÍ¡£
*/
--ҪʹÓñíÖµ²ÎÊý£¬Ê×ÏÈÒª¶¨ÒåÓû§¶¨Òå±íÊý¾ÝÀàÐÍ¡£
CREATE TYPE T_USERINFO AS TABLE
(USERID INT,
USERNAME NVARCHAR(50)
)
GO
--ÏÂÃæ¿ÉÒÔ¶ÔÉÏÃæµÄ¹ý³ÌUSP_INSERT_USERINFO½øÐÐÐ޸ġ£
CREATE PROC USP_INSERT_USERINFO_NEW
@USERINFO T_USERINFO READONLY --±ØÐëʹÓÃREADONLY Ñ¡ÏîÉùÃ÷±íÖµ²ÎÊý
AS
INSERT USERINFO
&nbs
Ïà¹ØÎĵµ£º
select
WorksheetID,worker,WorkDate,merchantName ,merchantNo ,manager
, case when insCount>0 then 'ÐÂ×°' else '' end InsStr
,case when repCount>0 then '»»×°' else '' end RepStr
,case when UnInsCount>0 then '³·»ú' else '' end UnInsStr
,case when FaultCount>0 then '¹ÊÕÏ´¦Àí' els ......
Entity sql ²éѯ·ÖÎöÆ÷
1¡¢eSqlBlast for VS 2008 SP1 ¿ªÔ´
download£ºhttp://code.msdn.microsoft.com/esql/Release/ProjectReleases.aspx?ReleaseId=991
Ó÷¨£ºhttp://www.cnblogs.com/xiaomi7732/archive/2008/09/09/1287952.html
2¡¢LINQPad
Ö÷Ò³ http://www.linqpad.net/
²»½öÖ§³Ö entity sql £¬»¹Ö§³ÖLinq £¬s ......
TOP ÔöÇ¿¹¦ÄÜ
1¡¢TOP ÔöÇ¿¡£¿ÉÒÔÖ¸¶¨Ò»¸öÊý×Ö±í´ïʽ£¬ÒÔ·µ»ØҪͨ¹ý²éѯӰÏìµÄÐÐÊý»ò°Ù·Ö±È£¬»¹¿ÉÒÔ¸ù¾ÝÇé¿öʹÓñäÁ¿»ò×Ó²éѯ¡£
¿ÉÒÔÔÚDELETE¡¢UPDATEºÍINSERT²éѯÖÐʹÓÃTOPÑ¡Ïî¡£
2¡¢¸üºÃµØÌæ»»SET ROWCOUNTÑ¡Ïʹ֮¸üΪÓÐЧ¡£
OUTPUT
1¡¢SQL Server 2005ÒýÈëÒ»¸öеÄOUTPUT×Ӿ䣬ÒÔʹÄú¿ÉÒÔ³åÐÞ¸ÄÓï¾ä(INSERT¡ ......
Ò»¡¢Ç°ÑÔ
“ÈËÀàʧȥËÑË÷£¬ÊÀ½ç½«»áÔõÑù?”£¬Í¬Ñù£¬ºÜÄÑÏëÏóÒ»¸öÓµÓм«´óÐÅÏ¢Á¿µÄÐÐÒµÍøÕ¾ÃÅ»§Ã»ÓÐÕ¾ÄÚÈ«ÎÄËÑË÷½«»á³öÏÖÔõÑùµÄ¾ÖÃ棬ÍøվȫÎļìË÷¶ÔÓÚÍÚ¾òÍøÕ¾ÐÅÏ¢ºÍÕûºÏÍøÕ¾×ÊÔ´µÄ¼ÛÖµÊDz»ÑÔ¶øÓ÷µÄ¡£ÎÒÃÇ¿´µ½£¬Í¨ÓÃÐ͵ÄËÑË÷ÒýÇæÒѾ³ÉΪÁË»¥ÁªÍøÊÀ½çµÄÃÅ»§£¬¶ø¶ÔÓÚÒ»¸öÐÅÏ¢Á¿¼«Æä·á¸»µÄÍøÕ¾¶øÑÔ£¬È«ÍøËÑË÷»òÐ ......
ÓÃsql*plus»òµÚÈý·½¿ÉÒÔÔËÐÐsqlÓï¾äµÄ³ÌÐòµÇ¼Êý¾Ý¿â£º
Ôö¼ÓÒ»¸öÁУº
ALTER TABLE ±íÃû ADD(ÁÐÃû Êý¾ÝÀàÐÍ);
È磺
ALTER TABLE emp ADD(weight NUMBER(38,0));
ÐÞ¸ÄÒ»¸öÁеÄÊý¾ÝÀàÐÍ(Ò»°ãÏÞÓÚÐ޸ij¤¶È£¬ÐÞ¸ÄΪһ¸ö²»Í¬ÀàÐÍʱÓÐÖî¶àÏÞÖÆ):
ALTER TABLE ±íÃû MODIFY(ÁÐÃû Êý¾ÝÀàÐÍ);
È磺
ALTER TABLE emp MODIFY(wei ......