SQL ， ， SQL 。
、
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
， ，
Dateadd(wk,datediff(wk,0,getdate()),-1)
Dateadd(wk,datediff(wk,0,getdate()),6)
Dateadd(mm,datediff(mm,0,getdate()),0)
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0))
Dateadd(yy,datediff(yy,0,getdate()),0)
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))
SQL
Dateadd(wk,datediff(wk,0,getdate()),-1)
Dateadd(wk,datediff(wk,0,getdate()),6)
.
SQL , :
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6)
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1)
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6)