·¢²¼Ê±¼ä : ÐÇÆÚÎå ÎÄÕÂSqlServer ³£ÓÃÃüÁî˵Ã÷¸üÐÂÍê±Ï¿ªÊ¼ÔĶÁdb348b09581b6bd97f19eada
ÓѺãͨÓÐÏÞ¹«Ë¾
where charindex('\and b.id = '31') µÚÈý½Ú SET
1¡¢SET IDENTITY_INSERT table_name ON | OFF ÔÊÐí½«ÏÔʽֵ²åÈë±íµÄ±êʶÁÐÖС£
2¡¢SET NOCOUNT ON ʱ£¬²»·µ»Ø¼ÆÊý£¨±íʾÊÜ Transact-SQL Óï¾äÓ°ÏìµÄÐÐÊý£©¡£µ±
SET NOCOUNT Ϊ OFF ʱ£¬·µ»Ø¼ÆÊý¡£
3¡¢SET ROWCOUNT { number | @number_var }
ÔÚ·µ»ØÖ¸¶¨µÄÐÐÊýÖ®ºóÍ£Ö¹´¦Àí²éѯ¡£ ÈôÒª¹Ø±Õ¸ÃÑ¡ÏÒԱ㷵»ØËùÓеÄÐУ©£¬Ç뽫 SET ROWCOUNT Ö¸¶¨Îª 0£¬·ñÔòÖ±µ½Óöµ½SET ROWCOUNTÃüÁîΪֹ¡£
Èç¹ûÐÐÊýµÄÖµ½ÏС£¬Ôò SET ROWCOUNT Ìæ´ú SELECT Óï¾ä TOP ¹Ø¼ü×Ö¡£
µÚËÄ½Ú ¿ØÖÆÁ÷ÓïÑÔ
1¡¢ BEGIN ºÍ END ¶¨ÒåһϵÁÐÒ»ÆðÖ´ÐÐµÄ Transact-SQL Óï¾ä¡£ if @@error<>0 begin rollback tran select @errmsg=\³õʼ»¯ÐÂÔöÒ©Æ·µĄ̈ÕÊʱ³ö´í£¡\ return end
2¡¢IF Boolean_expression
{ sql_statement | statement_block } [ ELSE
{ sql_statement | statement_block } ]
Èç¹ûÔÚ IF...ELSE ¿éµÄ IF ÇøºÍ ELSE Çø¶¼Ê¹ÓÃÁË CREATE TABLE Óï¾ä»ò SELECT INTO Óï¾ä£¬ÄÇô CREATE TABLE Óï¾ä»ò SELECT INTO Óï¾ä²»ÄÜÖ¸ÏòÏàͬµÄ±íÃû¡£
3¡¢GOTO ¶¨Òå±êÇ©£º label : ¸Ä±äÖ´ÐУº GOTO label
µÚ 13 Ò³ ¹² 53 Ò³
ÓѺãͨÓÐÏÞ¹«Ë¾
½«Ö´ÐÐÁ÷±ä¸üµ½±êÇ©´¦¡£Ìø¹ý GOTO Ö®ºóµÄ Transact-SQL Óï¾ä£¬ÔÚ±êÇ©´¦¼ÌÐø´¦Àí¡£GOTO Óï¾äºÍ±êÇ©¿ÉÔÚ¹ý³Ì¡¢Åú´¦Àí»òÓï¾ä¿éÖеÄÈκÎλÖÃʹÓá£GOTO Óï¾ä¿ÉǶÌ×ʹÓᣠ²»ÂÛÊÇ·ñʹÓà GOTO Óï¾ä£¬±êÇ©¾ù¿É×÷ΪעÊÍ·½·¨Ê¹Óá£
4¡¢WHILE Boolean_expression
{ sql_statement | statement_block } [ BREAK ]
{ sql_statement | statement_block } [ CONTINUE ]
Ö»ÒªÖ¸¶¨µÄÌõ¼þΪÕ棬¾ÍÖظ´Ö´ÐÐÓï¾ä¡£
¿ÉÒÔʹÓà BREAK ºÍ CONTINUE ¹Ø¼ü×Ö»òÆäËû¸Ä±ä±í´ïʽֵµÄ·½·¨ÔÚÑ»·ÄÚ²¿¿ØÖÆ WHILE Ñ»·ÖÐÓï¾äµÄÖ´ÐС£
5¡¢caseº¯Êý
select sum(case when jlzt in(0,1) then 1 when jlzt in (2) then -1 else 0 end) ½»Ò×±ÊÊý from VW_MZBRJSK
where sfrq between @ksrq and @jsrq+'24' and ybjszt=2 and jlzt in (0,1,2)
»ò£º
select sum(case jlzt when 0 then 1 when 2 then 1 when 1 then -1 else 0 end) ½áËã±ÊÊý from ZY_BRJSK
where jsrq between '20041201' and '2005030124' and ybjszt=2 and jlzt in (0,1,2)
6¡¢/*...*/£¨×¢ÊÍ£©
--£¨×¢ÊÍ£©
7¡¢DECLARE @local_variable
ÔÚÅú´¦Àí»ò¹ý³ÌµÄÕýÎÄÖÐÓà DECLARE Óï¾äÉùÃ÷±äÁ¿£¬²¢Óà SET »ò SELECT Óï¾ä¸øÆäÖ¸ÅÉÖµ¡£Óαê±äÁ¿¿Éͨ¹ý¸ÃÓï¾äÉùÃ÷£¬²¢ÇÒ¿ÉÓÃÔÚÆäËüÓëÓαêÏà¹ØµÄÓï¾äÖС£ËùÓбäÁ¿ÔÚÉùÃ÷ºó¾ù³õʼ»¯Îª NULL¡£
Declare @ksrq ut_rq8,@jsrq ut_rq8
Declare cs_dxmdm cursor for select id,name from YY_SFDXMK
8¡¢ execute»òexec
Ö´ÐÐÓû§¶¨Ò庯Êý¡¢ÏµÍ³¹ý³Ì¡¢Óû§¶¨Òå´æ´¢¹ý³Ì»òÀ©Õ¹´æ´¢¹ý³Ì¡£
ÔÚÖ´Ðд洢¹ý³Ìʱ£¬Èç¹ûÓï¾äÊÇÅú´¦ÀíÖеĵÚÒ»¸öÓï¾ä£¬Ôò²»Ò»¶¨ÒªÖ¸¶¨ EXECUTE ¹Ø¼ü×Ö¡£
exec usp_sf_bb_kssrtj '20050101','20050131' declare @errmsg varchar(50)
exec usp_yy_gxtz 1, 0, '4001', 0, '200110', @errmsg output exec('drop table '+@@tablename+')'
µÚ 14 Ò³ ¹² 53 Ò³
ÓѺãͨÓÐÏÞ¹«Ë¾
µÚÎå½Ú ³£ÓÃϵͳ´æ´¢¹ý³Ì
1¡¢ sp_who
Ìṩ¹ØÓÚµ±Ç° Microsoft? SQL Server? Óû§ºÍ½ø³ÌµÄÐÅÏ¢¡£
ÁÐ spid ecid Êý¾ÝÀàÐÍ smallint smallint ϵͳ½ø³Ì ID¡£ ÓëÖ¸¶¨ SPID Ïà¹ØÁªµÄ¸ø¶¨Ï̵߳ÄÖ´ÐÐÉÏÏÂÎÄ ID¡£ ECID = {0, 1, 2, 3, ...n}£¬ÆäÖÐ 0 ʼÖÕ±íʾÖ÷»ò¸¸Ị̈߳¬²¢ÇÒ {1, 2, 3, ...n} ±íʾ×ÓÏ̡߳£ ½ø³Ì״̬¡£ ÓëÌض¨½ø³ÌÏà¹ØÁªµÄµÇ¼Ãû¡£ ÿ¸ö½ø³ÌµÄÖ÷»ú»ò¼ÆËã»úÃû¡£ Èç¹û´æÔÚ×èÈû½ø³Ì£¬ÔòÊǸÃ×èÈû½ø³ÌµÄϵͳ½ø³Ì ID¡£·ñÔò¸ÃÁÐΪÁã¡£ ½ø³ÌʹÓõÄÊý¾Ý¿â¡£ Ϊ´Ë½ø³ÌÖ´ÐÐµÄ SQL Server ÃüÁTransact-SQL Óï¾ä¡¢SQL Server ÄÚ²¿ÒýÇæ´¦ÀíµÈ£©¡£ ÃèÊö status loginame hostname blk nchar(30) nchar(128) nchar(128) char(5) dbname cmd nchar(128) nchar(16) KILL ÃüÁîͨ³£ÓÃÓÚÖÕÖ¹ÕâÑùһЩ½ø³Ì£¬ËüÃÇËøסÁËÆäËüÖØÒª½ø³Ì£¬»òÕßÕýÔÚÖ´ÐÐÒ»¸ö²éѯ£¬¶ø¸Ã²éѯÕýÔÚʹÓñØÐèµÄϵͳ×ÊÔ´¡£
2¡¢ sp_depends
ÏÔʾÓйØÊý¾Ý¿â¶ÔÏóÏà¹ØÐÔµÄÐÅÏ¢£¨ÀýÈ磬ÒÀÀµ±í»òÊÓͼµÄÊÓͼºÍ¹ý³Ì£¬ÒÔ¼°ÊÓͼ»ò¹ý³ÌËùÒÀÀµµÄ±íºÍÊÓͼ£©¡£
sp_depends YY_KSBMK
3¡¢ sp_help
±¨¸æÓйØÊý¾Ý¿â¶ÔÏó£¨sysobjects ±íÖÐÁгöµÄÈκζÔÏ󣩡¢Êý¾ÝÀàÐÍ¡¢Óû§¶¨ÒåÊý¾ÝÀàÐ͵ÄÐÅÏ¢¡£
Sp_help YY_KSBMK Áгö±í½á¹¹¡¢Ë÷ÒýµÈ Sp_help ut_rq8 Áгöut_rq8µÄÊý¾ÝÀàÐͶ¨Òå
4¡¢ sp_helpindex
sp_helpindex YY_KSBMK ÁгöYY_KSBMKµÄË÷ÒýÐÅÏ¢
5¡¢ sp_helptext
ÏÔʾ¹æÔò¡¢Ä¬ÈÏÖµ¡¢Î´¼ÓÃܵĴ洢¹ý³Ì¡¢Óû§¶¨Ò庯Êý¡¢´¥·¢Æ÷»òÊÓͼµÄÎı¾¡£ sp_helptext usp_sf_sfcl
µÚ 15 Ò³ ¹² 53 Ò³
ÓѺãͨÓÐÏÞ¹«Ë¾
6¡¢ sp_password ÔÃÜÂë,ÐÂÃÜÂë,¡¯sa¡¯ ÐÞ¸ÄsaµÄÃÜÂë¡£Èç¹ûÃÜÂëΪ¿Õ£¬ÔòÊäÈënull¡£
µÚÁù½Ú ±í¡¢´æ´¢¹ý³ÌµÄ´´½¨
1¡¢ create table
create table YK_YPCGZD (
xh ut_xh12 identity(1,1) not null, czyh ut_czyh not null, cjrq ut_rq16 not null, djh ut_sjh null , ksdm ut_ksdm not null, shry ut_czyh null , shrq ut_rq16 null , jlzt ut_bz not null, jzbz ut_bz not null,
memo ut_memo null , constraint PK_YK_YPCGZD primary key (xh) ) go
create index idx_djh on YK_YPCGZD(djh) create index idx_cjrq on YK_YPCGZD(cjrq)
--ÐòºÅ --²Ù×÷Ô± --´´½¨ÈÕÆÚ --µ¥¾ÝºÅ --¿ÆÊÒ´úÂë --ÉóºËÔ± --ÉóºËÈÕÆÚ
--¼Ç¼״̬ 0¼Èë1ÉóºË2×÷·Ï --¼ÇÕ˱êÖ¾ 0:¼Èë 1£º¼ÇÕË --memo
¢ÙIDENTITY
±íʾÐÂÁÐÊDZêʶÁС£µ±Ïò±íÖÐÌí¼ÓÐÂÐÐʱ£¬×Ô¶¯½«Îª¸Ã±êʶÁÐÌṩһ¸öΨһµÄ¡¢µÝÔöµÄÖµ¡£±êʶÁÐͨ³£Óë PRIMARY KEY Ô¼ÊøÒ»ÆðÓÃ×÷±íµÄΨһÐбêʶ·û¡£¿ÉÒÔ½« IDENTITY ÊôÐÔÖ¸Åɸø tinyint¡¢smallint¡¢int¡¢bigint¡¢decimal(p,0) »ò numeric(p,0) ÁС£¶ÔÓÚÿ¸ö±íÖ»ÄÜ´´½¨Ò»¸ö±êʶÁС£²»ÄܶԱêʶÁÐʹÓðó¶¨Ä¬ÈÏÖµºÍ DEFAULT Ô¼Êø¡£±ØÐëͬʱָ¶¨ÖÖ×ÓºÍÔöÁ¿£¬»òÕ߶þÕ߶¼²»Ö¸¶¨¡£Èç¹û¶þÕ߶¼Î´Ö¸¶¨£¬ÔòȡĬÈÏÖµ (1,1)¡£
¢Ú¹ØÓÚ×Ô¶¨ÒåÀàÐ͵Ä˵Ã÷£º
execute sp_addtype ut_bz, 'smallint', null go
create default D_ut_bz as 0 go
execute sp_bindefault D_ut_bz, ut_bz go
execute sp_addtype ut_xh12, 'numeric(12,0)','NOT NULL' go
¢ÛCONSTRAINT
µÚ 16 Ò³ ¹² 53 Ò³