ä¸ãåºç¡
1ã说æï¼å建æ°æ®åº
Create DATABASE database-name
2ã说æï¼å é¤æ°æ®åº
drop database dbname
3ã说æï¼å¤ä»½sql server
--- å建 å¤ä»½æ°æ®ç device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
--- å¼å§ å¤ä»½
BACKUP DATABASE pubs TO testBack
4ã说æï¼å建æ°è¡¨
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
æ ¹æ®å·²æç表å建æ°è¡¨ï¼
Aï¼create table tab_new like tab_old (使ç¨æ§è¡¨å建æ°è¡¨)
Bï¼create table tab_new as select col1,col2⦠from tab_old definition only
5ã说æï¼å é¤æ°è¡¨
drop table tabname
6ã说æï¼å¢å ä¸ä¸ªå
Alter table tabname add column col type
注ï¼åå¢å åå°ä¸è½å é¤ãDB2ä¸åå ä¸åæ°æ®ç±»åä¹ä¸è½æ¹åï¼å¯ä¸è½æ¹åçæ¯å¢å varcharç±»åçé¿åº¦ã
7ã说æï¼æ·»å 主é®ï¼ Alter table tabname add primary key(col)
说æï¼å é¤ä¸»é®ï¼ Alter table tabname drop primary key(col)
8ã说æï¼å建索å¼ï¼create [unique] index idxname on tabname(colâ¦.)
å é¤ç´¢å¼ï¼drop index idxname
注ï¼ç´¢å¼æ¯ä¸å¯æ´æ¹çï¼æ³æ´æ¹å¿
é¡»å é¤éæ°å»ºã
9ã说æï¼å建è§å¾ï¼create view viewname as select statement
å é¤è§å¾ï¼drop view viewname
10ã说æï¼å 个ç®åçåºæ¬çsqlè¯å¥
éæ©ï¼select * from table1 where èå´
æå
¥ï¼insert into table1(field1,field2) values(value1,value2)
å é¤ï¼delete from table1 where èå´
æ´æ°ï¼update table1 set field1=value1 where èå´
æ¥æ¾ï¼select * from table1 where field1 like â%value1%â ---likeçè¯æ³å¾ç²¾å¦ï¼æ¥èµæ!
æåºï¼select * from table1 order by field1,field2 [desc]
æ»æ°ï¼select count as totalcount from table1
æ±åï¼select sum(field1) as sumvalue from table1
å¹³åï¼select avg(field1) as avgvalue from table1
æ大ï¼select max(field1) as maxvalue from table1
æå°ï¼select min(field1) as minvalue from table1
11ã说æï¼å 个é«çº§æ¥è¯¢è¿ç®è¯
Aï¼ UNION è¿ç®ç¬¦
UNION è¿ç®ç¬¦éè¿ç»åå
¶ä»ä¸¤ä¸ªç»æ表ï¼ä¾å¦ TABLE1 å TABLE2ï¼å¹¶æ¶å»è¡¨ä¸ä»»ä½éå¤è¡èæ´¾çåºä¸ä¸ªç»æ表ãå½ ALL é UNION ä¸èµ·ä½¿ç¨
æ¶ï¼å³ UNION ALLï¼ï¼ä¸æ¶é¤éå¤è¡ã两ç§æ
åµä¸ï¼æ´¾ç表çæ¯ä¸è¡ä¸æ¯æ¥èª TABLE1 å°±æ¯æ¥èª TABLE2ã
Bï¼ EXCEPT è¿ç®ç¬¦
EXCEPT è¿ç®ç¬¦éè¿å
æ¬ææå¨ TABLE1 ä¸ä½ä¸å¨ TABLE2 ä¸çè¡å¹¶æ¶é¤ææéå¤è¡èæ´¾çåºä¸ä¸ªç»æ表ãå½ ALL é EXCEPT ä¸èµ·ä½¿ç¨æ¶
(EXCEPT ALL)ï¼ä¸æ¶é¤éå¤è¡ã
Cï¼ INTERSECT è¿ç®ç¬¦
INTERSECT è¿ç®ç¬¦éè¿åªå
æ¬ TABLE1 å TABLE2 ä¸é½æçè¡å¹¶æ¶é¤ææéå¤è¡èæ´¾çåºä¸ä¸ªç»æ表ãå½ ALL é INTERSECT ä¸èµ·ä½¿ç¨æ¶
(INTERSECT ALL)ï¼ä¸æ¶é¤éå¤è¡ã
注ï¼ä½¿ç¨è¿ç®è¯çå 个æ¥è¯¢ç»æè¡å¿
é¡»æ¯ä¸è´çã
12ã说æï¼ä½¿ç¨å¤è¿æ¥
Aãleft outer joinï¼
å·¦å¤è¿æ¥ï¼å·¦è¿æ¥ï¼ï¼ç»æéå å
æ¬è¿æ¥è¡¨çå¹é
è¡ï¼ä¹å
æ¬å·¦è¿æ¥è¡¨çææè¡ã
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
Bï¼right outer join:
å³å¤è¿æ¥(å³è¿æ¥)ï¼ç»æéæ¢å
æ¬è¿æ¥è¡¨çå¹é
è¿æ¥è¡ï¼ä¹å
æ¬å³è¿æ¥è¡¨çææè¡ã
Cï¼full outer joinï¼
å
¨å¤è¿æ¥ï¼ä¸ä»
å
æ¬ç¬¦å·è¿æ¥è¡¨çå¹é
è¡ï¼è¿å
æ¬ä¸¤ä¸ªè¿æ¥è¡¨ä¸çææè®°å½ã
äºãæå
1ã说æï¼å¤å¶è¡¨(åªå¤å¶ç»æ,æºè¡¨åï¼a æ°è¡¨åï¼b) (Accesså¯ç¨)
æ³ä¸ï¼select * into b from a where 1<>1
æ³äºï¼select top 0 * into b from a
2ã说æï¼æ·è´è¡¨(æ·è´æ°æ®,æºè¡¨åï¼a ç®æ 表åï¼b) (Accesså¯ç¨)
insert into b(a, b, c) select d,e,f from b;
3ã说æï¼è·¨æ°æ®åºä¹é´è¡¨çæ·è´(å
·ä½æ°æ®ä½¿ç¨ç»å¯¹è·¯å¾) (Accesså¯ç¨)
insert into b(a, b, c) select d,e,f from b in âå
·ä½æ°æ®åºâ where æ¡ä»¶
ä¾åï¼..from b in '"&Server.MapPath("."&"\data.mdb" &"' where..
4ã说æï¼åæ¥è¯¢(表å1ï¼a 表å2ï¼b)
select a,b,c from a where a IN (select d from b æè
: select a,b,c from a where a IN (1,2,3)
5ã说æï¼æ¾ç¤ºæç« ãæ交人åæååå¤æ¶é´
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
6ã说æï¼å¤è¿æ¥æ¥è¯¢(表å1ï¼a 表å2ï¼b)
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
7ã说æï¼å¨çº¿è§å¾æ¥è¯¢(表å1ï¼a
select * from (Select a,b,c FROM a) T where t.a > 1;
8ã说æï¼betweençç¨æ³,betweenéå¶æ¥è¯¢æ°æ®èå´æ¶å
æ¬äºè¾¹çå¼,not betweenä¸å
æ¬
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between æ°å¼1 and æ°å¼2
9ã说æï¼in ç使ç¨æ¹æ³
select * from table1 where a [not] in (âå¼1â,âå¼2â,âå¼4â,âå¼6â)
10ã说æï¼ä¸¤å¼ å
³è表ï¼å é¤ä¸»è¡¨ä¸å·²ç»å¨å¯è¡¨ä¸æ²¡æçä¿¡æ¯
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1
11ã说æï¼å表èæ¥é®é¢ï¼
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
12ã说æï¼æ¥ç¨å®ææåäºåéæé
SQL: select * from æ¥ç¨å®æ where datediff('minute',få¼å§æ¶é´,getdate())>5
13ã说æï¼ä¸æ¡sql è¯å¥æå®æ°æ®åºå页
select top 10 b.* from (select top 20 主é®å段,æåºå段 from 表å order by æåºå段 desc) a,表å b where b.主é®å段 = a.主
é®å段 order by a.æåºå段
14ã说æï¼å10æ¡è®°å½
select top 10 * form table1 where èå´
15ã说æï¼éæ©å¨æ¯ä¸ç»bå¼ç¸åçæ°æ®ä¸å¯¹åºçaæ大çè®°å½çææä¿¡æ¯(类似è¿æ ·çç¨æ³å¯ä»¥ç¨äºè®ºåæ¯ææè¡æ¦,æ¯æçé产ååæ,æç§
ç®æ绩æå,çç.)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
16ã说æï¼å
æ¬ææå¨ TableA ä¸ä½ä¸å¨ TableBåTableC ä¸çè¡å¹¶æ¶é¤ææéå¤è¡èæ´¾çåºä¸ä¸ªç»æ表
(select a from tableA except (select a from tableB) except (select a from tableC)
17ã说æï¼éæºååº10æ¡æ°æ®
select top 10 * from tablename order by newid()
18ã说æï¼éæºéæ©è®°å½
select newid()
19ã说æï¼å é¤éå¤è®°å½
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
20ã说æï¼ååºæ°æ®åºéææç表å
select name from sysobjects where type='U'
21ã说æï¼ååºè¡¨éçææç
select name from syscolumns where id=object_id('TableName')
22ã说æï¼å示typeãvenderãpcså段ï¼ä»¥typeå段æåï¼caseå¯ä»¥æ¹ä¾¿å°å®ç°å¤ééæ©ï¼ç±»ä¼¼select ä¸çcaseã
select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender
when 'B' then pcs else 0 end) FROM tablename group by type
æ¾ç¤ºç»æï¼
type vender pcs
çµè A 1
çµè A 1
å
ç B 2
å
ç A 2
ææº B 3
ææº C 3
23ã说æï¼åå§å表table1
TRUNCATE TABLE table1
24ã说æï¼éæ©ä»10å°15çè®°å½
select top 5 * from (select top 15 * from table order by id asc) table_å«å order by id desc
ä¸ãæå·§
1ã1=1ï¼1=2ç使ç¨ï¼å¨SQLè¯å¥ç»åæ¶ç¨çè¾å¤
âwhere 1=1â æ¯è¡¨ç¤ºéæ©å
¨é¨ âwhere 1=2âå
¨é¨ä¸éï¼
å¦ï¼
if @strWhere !='
begin
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere
end
else
begin
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'
end
æ们å¯ä»¥ç´æ¥åæ
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 å®å® '+ @strWhere
2ãæ¶ç¼©æ°æ®åº
--é建索å¼
DBCC REINDEX
DBCC INDEXDEFRAG
--æ¶ç¼©æ°æ®åæ¥å¿
DBCC SHRINKDB
DBCC SHRINKFILE
3ãå缩æ°æ®åº
dbcc shrinkdatabase(dbname)
4ã转移æ°æ®åºç»æ°ç¨æ·ä»¥å·²åå¨ç¨æ·æé
exec sp_change_users_login 'update_one','newname','oldname'
go
5ãæ£æ¥å¤ä»½é
RESTORE VERIFYONLY from disk='E:\dvbbs.bak'
6ãä¿®å¤æ°æ®åº
Alter DATABASE [dvbbs] SET SINGLE_USER
GO
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK
GO
Alter DATABASE [dvbbs] SET MULTI_USER
GO
7ãæ¥å¿æ¸
é¤
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
USE tablename -- è¦æä½çæ°æ®åºå
Select @LogicalFileName = 'tablename_log', -- æ¥å¿æ件å
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 1 -- ä½ æ³è®¾å®çæ¥å¿æ件ç大å°(M)
-- Setup / initialize
DECLARE @OriginalSize int
Select @OriginalSize = size
FROM sysfiles
Where name = @LogicalFileName
Select 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
Where name = @LogicalFileName
Create TABLE DummyTrans
(DummyColumn char (8000) not null)
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
Select @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (Select size FROM sysfiles Where name = @LogicalFileName)
AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN -- Outer loop.
Select @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
Insert DummyTrans VALUES ('Fill Log')
Delete DummyTrans
Select @Counter = @Counter + 1
END
EXEC (@TruncLog)
END
Select 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
Where name = @LogicalFileName
Drop TABLE DummyTrans
SET NOCOUNT OFF
8ã说æï¼æ´æ¹æ个表
exec sp_changeobjectowner 'tablename','dbo'
9ãåå¨æ´æ¹å
¨é¨è¡¨
Create PROCEDURE dbo.User_ChangeObjectOwnerBatch
@OldOwner as NVARCHAR(128),
@NewOwner as NVARCHAR(128)
AS
DECLARE @Name as NVARCHAR(128)
DECLARE @Owner as NVARCHAR(128)
DECLARE @OwnerName as NVARCHAR(128)
DECLARE curObject CURSOR FOR
select 'Name' = name,
'Owner' = user_name(uid)
from sysobjects
where user_name(uid)=@OldOwner
order by name
OPEN curObject
FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0)
BEGIN
if @Owner=@OldOwner
begin
set @OwnerName = @OldOwner + '.' + rtrim(@Name)
exec sp_changeobjectowner @OwnerName, @NewOwner
end
-- select @name,@NewOwner,@OldOwner
FETCH NEXT FROM curObject INTO @Name, @Owner
END
close curObject
deallocate curObject
GO
10ãSQL SERVERä¸ç´æ¥å¾ªç¯åå
¥æ°æ®
declare @i int
set @i=1
while @i<30
begin
insert into test (userid) values(@i)
set @i=@i+1
end
温馨提示:答案为网友推荐,仅供参考