> Êý¾Ý¿â > SQL Server 2005 >

һЩsqlserverС֪ʶ

1¡¢Íù±íÖвåÈë¶àÌõÊý¾ÝʹÓÃunion¹Ø¼ü×Ö
insert into Users([name],password,nickName)
select 'aaa','bbbb','cccc' union
select 'dfdf','dfdf','dfdsf' union
select 'edtrer','dfgg','dfgfg'
ʹÓà union allʱ½«°ÑÒÔϺÍunionÊý¾ÝÏàͬµÄÐи²¸ÇÁË


2¡¢delete truncate dropµÄÇø±ð
deleteʱ²»Ð´Ìõ¼þ½«°Ñ±íÖеÄÊý¾Ý¶¼É¾ÁË,ɾ³ýÒ»ÌõÍùÈÕÖ¾ÎļþÀïдһÌõ¼Ç¼,±íÐÐÊýºÜ¶àËٶȻáºÜÂý
truncate table¾Í°Ñ±íÇå¿ÕÁË,Ëٶȿì
dropɾ³ý±íʱûÓÐÕâ¸ö±í½«»á±¨´í
deleteɾ³ý±íʱûÓÐÕâ¸ö±í½«»áÏÔʾӰÏìÐÐÊýΪ0


3¡¢¹ØÓÚÈÕÆڵĺ¯Êý
getDate()»ñµÃµ±Ç°ÈÕÆÚ
dateAdd(yyyy,2,'2008-11-10') ½«Ö¸¶¨ÈÕÆÚ¼Ó2Äê,Ò²¿ÉÒÔ¼ÓÔÂmm,¼ÓÌìddµÈ
dateDiff(yyyy,'2005-10-5','2007-10-5')·µ»ØÁ½¸öÈÕÆÚµÄÏà¸ôµÄÄêÔ»òÈÕ
dateName(dw,'2008-11-4')»ñµÃÐÇÆÚ¼¸(Ò²¿ÉÓÃw)½á¹ûΪÐÇÆÚ¶þ
datePart(day,'2007-12-21')»ñµÃÈÕÆÚµÄÖ¸¶¨ÕûÊýÐÎʽ
ÓëdatePartÀàËÆ»¹ÓÐ
Day()¡¢Month()¡¢Year()º¯Êý


4¡¢Ò»Ð©³£ÓõÄsqlServerº¯Êý
len('df´ó·ù¶È') »ñµÃʵ¼Ê³¤¶È5
datalength('df´ó·ù¶È')»ñµÃ×Ö½ÚÊý8
charindex('mn','mmmmn')»ñµÃµÚÒ»¸ö×Ö·û´®ÔÚµÚ¶þ¸ö×Ö·û´®ÖеÄÆðʼλÖÃ4
lower() ·µ»Ø×Ö·û´®µÄСд
upper() ·µ»Ø×Ö·û´®µÄ´óд
ltrim() Çå³ý×Ö·û´®×ó±ßµÄ¿Õ¸ñ
rtrim() Çå³ý×Ö·û´®ÓұߵĿոñ
right('sdfd',2) ´Ó×Ö·û´®Óұ߽ØÈ¡Ö¸¶¨ÊýÄ¿µÄ×Ö·û
left('dfds',2) ´Ó×Ö·û´®×ó±ß½ØÈ¡Ö¸¶¨ÊýÄ¿µÄ×Ö·û
replace('dfdsfd','d','a') ½«×Ö·û´®ÖÐ×Ö·ûd¶¼Ì滻Ϊ×Ö·ûa
stuff('addfgdg',2,3,'AAAAA') ´Ó×Ö·û´®µÚ¶þ¸öλÖÿªÊ¼½ØÈ¡3¸ö×Ö·ûÓÃAAAAA´úÌæ
numeric(5,3)
decimal(5,3) ÕâÁ½¸ö¹¦ÄÜÒ»Ñù,¾«¶ÈΪ5,¼´×ÜλÊýÊÇ5,СÊýλÊýΪ3
cast(123 AS varchar(5)) ת»»º¯Êý,½«ÕûÊýת»¯Îª×Ö·û´®
convert(int,'123') ½«×Ö·û´®×ª»¯ÎªÕûÊý
convert(varchar(10),getdate(),121) ʹÓÃÁËÑùʽ,»ñµÃÈÕÆÚµÄÄêÔÂÈÕ,ûÓÐʱ·ÖÃë
isnull(classess,'δ·Ö°à') ½«ËùÓÐclassessÁÐΪnullµÄÓÃδ·Ö°à´úÌæ


5¡¢Ò»Ð©³£ÓõĴ洢¹ý³Ì
sp_help users Áгö±í½á¹¹
sp_tables ²é³öµ±Ç°¿âÀïµÄËùÓбí
xp_cmdshell 'mkdir d:\bb'  ´´½¨Îļþ¼Ð
EXEC sp_addlogin  'adminMaster','theMaster' --´´½¨sqlserverµÇ½ÕʺÅ
--ÔÚmaster¿âÏÂ
EXEC sp_grantdbaccess 'adminMaster','adminMaster'--´´½¨Êý¾Ý¿âÓû§
USE bbsDB
--¸øÓû§¸³È¨ÏÞ£¬¶ÔÖ÷Ìù±íbbsTopicºÍ»ØÌû±íbbsReplyÓв鿴¡¢É¾ÌûµÄȨÏÞ
GRANT select,delete ON bbsTopic TO adminMaster
GRANT select,delete ON bbsTopic TO adminMaster
--¶ÔÓû§±íbbsUsersÓÐÐÞ¸ÄȨÏÞ
GRANT update ON bbsUsers TO adminMaster

scope_identity() »ñµÃÕæÕýµÄ×îºóÒ»´Î²åÈëµÄÊý¾ÝµÄ±êʶֵ,ÒòΪ¶àÓû§ÔÚinsertʱ,±ðÈËÒ²ÓпÉÄÜinsert
@@identity »ñµÃ×îºóÒ»´Î²åÈëµÄÊý¾ÝµÄ±êʶֵ
sp_who ²éѯ³öÓм¸¸öÓû§Á¬×ÅÊý¾Ý¿â
kill spidÕâ¸öÁеÄÖµ   ɾ³ýÕ¼ÓÃ×ÊÔ´µÄÓû§

print @@IDENTITY   --ÔÚ¶à¸÷Óû§¶¼Ö´ÐвåÈëʱ£¬ÓпÉÄܵõ½µÄ²»ÊÇÕæÕýµÄ±êʶֵ

print @@MAX_CONNECTIONS --¿ÉÒÔ´´½¨Í¬Ê±Á¬½ÓµÄ×î´óÊýÄ¿

print @@ROWCOUNT   --ÊÜÉÏÒ»¸öSQLÓï¾äµÄÓ°ÏìÐÐÊý

print @@SERVERNAME --±¾µØ·þÎñÆ÷µÄÃû³Æ 

print @@TIMETICKS --µ±Ç°¼ÆËã»úÿ¿Ì¶ÈµÄ΢ÃëÊý

print @@TRANCOUNT --µ±Ç°´ò¿ªµÄÊÂÎïÊý

print @@VERSION   --SQL ServerµÄ°æ±¾ 
ups ²»¼ä¶ÏµçÔ´


6¡¢½¨¿â¡¢½¨±í¡¢¼ÓÔ¼Êø
ÔÚmaster¿âÏÂ
if exists(select * from sysdatabases where name='mytest')
drop database mytest
create database mytest
on
(
name='mytest',
filename='e:\test.mdf'
)
ÔÚmytest¿âÏÂ
if exists(select * from sysobjects where name='stu')
drop table stu
create table stu
(
id int identity,
name varchar(50),
calsses varchar(50)
)
¼ÓÔ¼Êø
alter table stuInfo
add constraint ck_stuNo check(stuNo like 's253__') ¼ÓcheckÔ¼Êø
alter table stu
add constraint df_name default('aaa') for [name] ¼ÓĬÈÏÔ¼Êø
alter table stu
add constraint uq_name(name) ¼ÓΨһԼÊø
alter table stu
add constraint pk_id primary key ¼ÓÖ÷¼üÔ¼Êø
alter table stu
add constraint fk_id foreign key(Íâ¼üÁÐ) references Ö÷¼ü±í(Ö÷¼üÁÐ) ¼ÓÍâ¼üÔ¼Êø


7¡¢Ð¡ÖªÊ¶
SET NOCOUNT ON --²»ÏÔʾSQLÓï¾äÓ°ÏìµÄÐÐÊý
Íù±êʶÁвåÈëÏÔʾֵ±ØÐë
set identity_insert ±íÃû on
set identity_insert ±íÃû off ¹Ø±Õ²åÈëÏÔʾֵ
Ð޸ıíÖÐijһÁеÄÊý¾ÝÀàÐÍ
alter table ±íÃû
alter column ÁÐÃû ÀàÐÍ(´óС)
Íù±íÖвåÈëÒ»ÁÐ
alter table ±íÃû
add ÁÐÃû ÀàÐÍ(´óС)


8¡¢±í±äÁ¿
declare @myTable table
(
id int identity,
orderid varchar(20),
customerid varchar(20)
)


9¡¢Ö´ÐÐÄÚÈÝÊÇ×Ö·û´®µÄsqlÓï¾ä
declare @strsql varchar(800)
set @strsql='select * from orders'
exec(@strsql)
case when thenµÄÓ÷¨:
CASE WHEN writtenExam IS NULL THEN 'ȱ¿¼'
ELSE CAST(labExam AS VARCHAR)
END


10¡¢ÊÂÎï
BEGIN TRANSACTION
DECLARE @userID int,@sumError int
SET @sumError = 0
SELECT @userID = UID FROM bbsUsers WHERE Uname = '¿É¿¨Òò'
--¿É¿¨Òò·¢Ìû
INSERT INTO bbsTopic(TsID,TuID,Tface,Ttopic,Tcontents,Ttime,TclickCount,Tstate,TlastReply)
VALUES(2,@userID,4,'.NETÅäÖÃÎÊÌâ','ÎÒµÄ',GETDATE(),200,0,'2009-1-8')
SET @sumError = @sumError + @@ERROR
--·¢ÌùÖ®ºó.NET°æ¿é±í¼Ó1
IF(@sumError = 0)
UPDATE bbsSection SET StopicCount = StopicCount + 1 WHERE Sname LIKE '%.NET%'
SET @sumError = @sumError + @@ERROR
--ÅжϴíÎóÊÇ·ñÊÇ0£¬²»ÊÇ·¢Ìû²»³É¹¦
IF(@sumError <> 0)
BEGIN
  print 'ÄúËù·¢µÄÌû×Ó²»³É¹¦!'
  ROLLBACK TRANSACTION
END
--´íÎóºÅ²»ÊÇ0˵Ã÷·¢Ìû³É¹¦
ELSE
BEGIN
  print '·¢Ìû³É¹¦'
  COMMIT TRANSACTION
END
ÊÂÎï»áËø¶¨±í,¶ÀÕ¼´Ë±í,Ö»ÓÐÖ´ÐÐÍêºó,²ÅÄܶԴ˱í½øÐвÙ×÷
11¡¢Ë÷Òý
if exists(select * from sysobjects where name='myIndex')
drop index myIndex
create nonclustered myIndex --·Ç¾Û¼¯Ë÷Òý
on ±íÃû(ÁÐÃû)

12¡¢ÊÓͼ
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'myview')
DROP VIEW myview
GO
CREATE VIEW myview
AS
(
SELECT * from orders
)
ÊÓͼ²»´æ·ÅÊý¾Ý,½ö½öÊÇÓ³Éä,¼´ÒýÓÃÔ­±íÖеÄÊý¾Ý¶øÒÑ
¶ÔÊÓͼִÐÐupdateÓï¾ä,Ô­±íÖеÄÊý¾Ý»á¸Ä±ä,¶øÐéÄâ±í²»»á


13¡¢´¥·¢Æ÷
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'trig_bank')
DROP TRIGGER trig_bank
GO
CREATE TRIGGER trig_bank
ON bank
FOR INSERT
AS
......
Íùbank±íÖвåÈëÊý¾ÝʱִÐд¥·¢Æ÷µÄbeginµ½end


14¡¢´æ´¢¹ý³Ì
F EXISTS(SELECT * FROM sysobjects WHERE name = 'proc_stu')
DROP PROCEDURE proc_stu
GO
CREATE PROCEDURE proc_stu
@notpassnum int OUTPUT,--Êä³ö²ÎÊý£¬Î´Í¨¹ýÈËÊý
@writtenPass int = 60, --ÊäÈë²ÎÊý±ÊÊÔ¼°¸ñÏß
@labPass int =60 --ÊäÈë²ÎÊý£¬»úÊÔ¼°¸ñÏß
AS
......


15¡¢´´½¨Ò»¸ö×Ô¶¨Ò庯Êý
CREATE FUNCTION fun_myfun
(
@num int --²ÎÊý
)
returns int --·µ»ØÀàÐÍ
AS --beginºÍend²»ÄÜÊ¡
BEGIN
SET @num = @num + 1
RETURN @num --·µ»ØÖµ
END
SELECT dbo.fun_myfun(5) --µ÷Óú¯Êý


16¡¢ÍùÒ»¸ö±íÀï²åÈëÊý¾ÝµÄÁ½ÖÖ·½·¨
<1>select A.ÁÐ,A.ÁÐ into B  --B±íÊÂÏȲ»´æÔÚ
   from A
<2>insert into B(ÁÐ,ÁÐ)
   select ÁÐ,ÁÐ from A  --B±íÊÂÏÈÒª´æÔÚ,²¢ÇÒÊý¾ÝÀàÐͺ͸öÊý¶¼ÒªÆ¥Åä


17¡¢charºÍvarcharµÄÇø±ð
char:Èô¶¨ÒåÒ»¸ö×Ö·û´óСΪ10,Èôֻдһ¸ö×Ö·û1,ÔÚ1ºó¾ÍÓÐ9¸ö¿Õ¸ñ
varchar:Èô¶¨ÒåÒ»¸ö×Ö·û´óСΪ10,Èôֻдһ¸ö×Ö·û1,Ëü¾ÍÖ»Õ¼Ò»¸ö×Ö·ûµÄλÖÃ
   ËùÒÔvarchar¿É½ÚÊ¡´ÅÅÌ¿Õ¼ä
µ«ÊÇÒªÏë°É×Ö·û1¸Ä³É1111,¾Í±ØÐë°ÉÔ­À´µÄÍùºóÒƶ¯,ÌÚ³ö¿Õ¼ä,ËùÒÔЧÂʵÍ
Ò»°ã£º50¸ö×Ö·ûÒÔÄÚÓÃchar
50¸ö×Ö·ûÒÔÉÏÓÃvarchar
update ±íÃû set ÁÐÃû=null --½«Õâ¸öÁеÄÊý¾Ý¶¼ÖÃΪnull


18¡¢ÔÚ½á¹û¼¯ÖиıäÁÐÃûµÄÈýÖÖ·½·¨
<1>select codeId as 'ѧԱ±àºÅ' --¿É²»¼ÓÒýºÅ
<2>select codeId 'ѧԱ±àºÅ' --ÒòΪas¿ÉÒÔÊ¡ÂÔ
<3>select 'ѧԱ±àºÅ'=codeId

²éѯÊÇ°´°Ù·Ö±ÈÌáÈ¡Óùؼü×Öpercent
select top 20 percent * from student
Ó¦¸ÃÊÇ<1>where <2>group by <3>havingµÄʹÓÃ˳Ðò
whereºÍhaving¶¼ÊǹýÂËÌõ¼þ
Çø±ðÊÇÌõ¼þÖÐÓоۺϺ¯Êýʱ,Ö»ÄÜÓÃhaving,·ñÔòÓÃwhere


(ÔðÈα༭£ºIT)