ǰλ: > ݿ > SQL Server 2005 >

һЩsqlserverС֪ʶ

ʱ:2017-04-17 12:18Դ:linux.it.net.cn :IT
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ͬи


2delete 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   --ڶûִвʱпܵõIJıʶֵ

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
--֮.NET1
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вʱִдbeginend


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 --beginendʡ
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Ҫ,ͺ͸Ҫƥ


17charvarchar
char:һַСΪ10,ֻдһַ1,19ո
varchar:һַСΪ10,ֻдһַ1,ֻռһַλ
   varcharɽʡ̿ռ
Ҫַ1ij1111,ͱԭƶ,ڳռ,Чʵ
һ㣺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ʹ˳
wherehavingǹ
оۺϺʱ,ֻhaving,where


(α༭IT)
------ָ----------------------------