--建數據庫??
CREATE DATABASE yongfa365_com ON?
( name = yongfa365_comt,??
? filename = 'd:\yongfa365_com.mdf',??
? size = 4,??
? maxsize = 10,??
? filegrowth = 1??
)??
?
?
--刪數據庫??
DROP DATABASE yongfa365_com??
?
--備份數據庫??
BACKUP DATABASE yongfa365_com TO DISK='d:\yongfa365_com.bak'?
?
--批量收縮所有數據庫? 適用SQL Server 2000/2005??
DECLARE cur CURSOR
FOR
? SELECT name
??? FROM Master..SysDatabases
??? WHERE name NOT IN ('master','model','msdb','Northwind','pubs','tempdb')??
DECLARE @tb SYSNAME??
?
OPEN cur??
FETCH NEXT FROM cur INTO @tb??
WHILE @@fetch_status=0
? BEGIN?
??? DUMP TRANSACTION? @tb? WITH NO_LOG??
??? BACKUP LOG? @tb WITH NO_LOG??
??? DBCC shrinkdatabase(@tb)??
??? FETCH NEXT FROM cur INTO @tb??
? END?
CLOSE cur??
DEALLOCATE cur???
?
?
--批量收縮所有數據庫? 適用所有SQL Server??
DECLARE cur CURSOR
FOR
? SELECT name
??? FROM Master..SysDatabases
??? WHERE name NOT IN ('master','model','msdb','Northwind','pubs','tempdb')??
DECLARE @tb SYSNAME??
?
OPEN cur??
FETCH NEXT FROM cur INTO @tb??
WHILE @@fetch_status=0
? BEGIN?
??? EXEC('ALTER DATABASE '+@tb+' SET RECOVERY SIMPLE;')??
??? DBCC shrinkdatabase (@tb) ;??
??? EXEC('ALTER DATABASE '+@tb+' SET RECOVERY FULL;')??
??????????
??? FETCH NEXT FROM cur INTO @tb??
? END?
CLOSE cur??
DEALLOCATE cur???
?
?
--刪除數據庫日志文件（有時能刪幾十G）??
DBCC ERRORLOG??
GO?????
?
----------------------------表操作----------------------------??
--刪除表??
DROP TABLE movies??
?
--SQL Server通用建表結構??
CREATE TABLE [test] ([Id] INT PRIMARY KEY
????????????????????????????? IDENTITY(1,1) ,--ID，主鍵，自動號??
???????????????????? [txtTitle] NVARCHAR(255) ,--標題??
???????????????????? [txtContent] NVARCHAR(MAX) ,--內容??
???????????????????? [Adder] NVARCHAR(20) ,--添加人??
???????????????????? [AddTime] DATETIME DEFAULT (GETDATE()) ,--提交時間??
???????????????????? [ModiTime] DATETIME DEFAULT (GETDATE()) ,--修改時間??
???????????????????? [Hits] INT DEFAULT (0) ,--點擊數??
???????????????????? [Flags] INT DEFAULT (0) ,--標識??
???????????????????? [SortID] INT DEFAULT (0)--排序號??
???????????????????? )??
?
?
--SQLite建表??
CREATE TABLE [test] ([Id] INTEGER PRIMARY KEY
????????????????????????????????? NOT NULL ,
???????????????????? [txtTitle] TEXT NULL ,--標題??
???????????????????? [txtContent] TEXT NULL ,--內容??
???????????????????? [Adder] TEXT NULL ,--添加人??
???????????????????? [AddTime] TEXT DEFAULT (datetime('now','localtime'))
??????????????????????????????????? NOT NULL ,--提交時間??
???????????????????? [ModiTime] TEXT DEFAULT (datetime('now','localtime'))
???????????????????????????????????? NOT NULL ,--修改時間??
???????????????????? [Hits] INTEGER DEFAULT (0)
??????????????????????????????????? NOT NULL ,--點擊數??
???????????????????? [Flags] INTEGER DEFAULT (0)
???????????????????????????????????? NOT NULL ,--標識??
???????????????????? [SortID] INTEGER DEFAULT (0)
????????????????????????????????????? NOT NULL--排序號??
???????????????????? )??
?
--ACCESS使用SQL語句建表??
CREATE TABLE 表名 ([Id] AutoIncrement NOT NULL ,
???????????????? [AddTime] DATE NULL ,
???????????????? [Hits] INTEGER NULL ,
???????????????? [Title] TEXT(255) NULL ,
???????????????? [Remark] Memo NULL ,
???????????????? [RMB] Currency NULL ,
???????????????? [Flags] BIT NULL ,
???????????????? PRIMARY KEY (ID))??
?
?
--重命名表??
EXEC sp_rename '表名','新表名','object'?
----------------------------表操作----------------------------??
?

---------------------------字段操作---------------------------??
--添加字段??
ALTER TABLE [表名] ADD [字段名] [varchar] (50) NOT NULL DEFAULT('默認')??
?
--刪除字段??
ALTER TABLE [表名] DROP COLUMN [字段名]??
?
--修改字段??
ALTER TABLE [表名] ALTER COLUMN [字段名] VARCHAR(50)??
?
--添加約束??
ALTER TABLE [表名] ADD CONSTRAINT [約束名] CHECK ([約束字段] <= '2009-1-1')??
?
--刪除約束??
ALTER TABLE [表名] DROP CONSTRAINT [約束名]??
?
--添加默認值??
ALTER TABLE [表名] ADD CONSTRAINT [默認值名] DEFAULT 'http://www.yongfa365.com/' FOR [字段名]??
?
--刪除默認值??
ALTER TABLE [表名] DROP CONSTRAINT [默認值名]??
?
--讓SQL Server 自動編號ID從1開始??
DBCC checkident('表名',reseed,0)??
?
---------------------------字段操作---------------------------??
?
----------------------表及字段描述信息------------------------??
?
--為表添加描述信息??
EXEC sp_addextendedproperty N'MS_Description',N'人員信息表',N'SCHEMA',N'dbo',
? N'TABLE',N'表名',NULL,NULL?
?
--為字段XingMing添加描述信息??
EXEC sp_addextendedproperty N'MS_Description',N'姓名',N'SCHEMA',N'dbo',N'TABLE',
? N'表名',N'COLUMN',N'XingMing'?
?
--更新表中列XingMing的描述屬性：??
EXEC sp_updateextendedproperty N'MS_Description',N'真實姓名',N'SCHEMA',N'dbo',
? 'TABLE','表名','COLUMN',N'XingMing'?
?
--刪除表中列XingMing的描述屬性：??
EXEC sp_dropextendedproperty N'MS_Description',N'SCHEMA',N'dbo','TABLE','表名',
? 'COLUMN',N'XingMing'?
?
----------------------表及字段描述信息------------------------??
?
?
---------------------------數據操作---------------------------??
--插入數據??
INSERT INTO [表名] (字段1,字段2)
? VALUES (100,'http://www.yongfa365.com/')??
?
--刪除數據??
DELETE FROM [表名]
? WHERE [字段名]>100??
?
--刪除重復記錄(效率低，適合少量數據操作,極不適合ACCESS使用)??
DELETE FROM [表名]
? WHERE id NOT IN (SELECT MAX(id)
??????????????????? FROM [表名]
??????????????????? GROUP BY txtTitle,txtContent)??
?
--NOT IN 效率太低，20000條數據都讓access死掉了。可行的方法：建個臨時表，在需要判斷是否重復的字段上加主鍵，用insert into InfoTemp select * from Info將原表的數據導入臨時表， 數據庫可以自動篩去重復數據，delete * from Info 清空原表，再用insert into Info select * from InfoTemp 將臨時表中數據導回原表??
?
--更新數據??
UPDATE [表名]
? SET [字段1]=200,[字段2]='http://www.yongfa365.com/'
? WHERE [字段三]='haiwa'?
?
--多表，用一個表更新另一個表(SQL Server版)??
UPDATE 表一
? SET 表一.a=表二.b
? FROM 表一 ,
??? 表二
? WHERE 表一.id=表二.id??
?
UPDATE 表一
? SET a=表二.b
? FROM 表二
? WHERE id=表二.id??
?
----多表，用一個表更新另一個表(ACCESS版)??
update 表一
inner join 表二??
on 表一.id = 表二.id??
set 表一.a = 表二.b??
?
--查詢結果存儲到新表??
select * into [新表表名] from [表名]??
?
--從table 表中取出從第 m 條到第 n 條的記錄：(not in 版本)??
select top n-m+1 * from [表名] where id not in (select top m-1 id from [表名])??
?
--例：取出第31到第40條記錄??
select top 10 * from [表名] where id not in (select top 30 id from [表名])??
?
--從table 表中取出從第 m 條到第 n 條的記錄：(ROW_NUMBER 版本)??
SELECT *
? FROM (SELECT ROW_NUMBER () OVER (ORDER BY id DESC) AS idx,*
??????????? FROM dbo.Articles) a
? WHERE a.idx BETWEEN m AND n??
?
--隨機取10條數據,newid()是SQL數據庫里的一個函數，跟數據庫里的ID沒關??
select top 10 * from [表名] order by newid()???
?
--隨機取10條數據,如果是ACCESS數據庫用order by rnd(數字字段)??
select top 10 * from [表名] order by rnd(id)??
?
--連接查詢left join??
select * from Article left join Category on Article.CategoryID=Category.ID??
?
?
?
--查詢結果插入到另一個表的相關字段里（可以插入一個表的字段，也可以是一個數字常量）??
insert into desttbl(fld1, fld2) select fld1, 5 from srctbl??
?
--把當前表里的數據再復制一份到這個表里??
insert into [表名] select * from [表名]??
?
--SQL 2008支持這種插入方法，使用,隔開各行??
DECLARE @Student TABLE (NO INT ,
??????????????????????? Name NVARCHAR(4) ,
??????????????????????? Sex NVARCHAR(2) ,
??????????????????????? Age INT ,
??????????????????????? Dept VARCHAR(2))??
INSERT INTO @Student
? VALUES? (95001,N'李勇',N'男',20,'CS'),
????????? (95002,N'劉晨',N'女',19,'IS'),
????????? (95003,N'王敏',N'女',18,'IS'),
????????? (95004,N'張立',N'男',19,'MA'),
????????? (96001,N'徐一',N'男',20,'IS'),
????????? (96002,N'張三',N'女',21,'CS'),
????????? (96003,N'李四',N'男',18,'IS')??
?
SELECT *
? FROM @Student??
?
--以逗號連接結果集所有行，使之變為一行??
DECLARE @result NVARCHAR(MAX)??
SET @result=N''?
SELECT @result=@result+N','+Name
? FROM @Student??
?
SELECT RIGHT(@result,LEN(@result)-1)??
?
?
--向數據庫中添加5000條數據??
declare @i int?
set @i=1??
while @i<=5000??
begin?
? insert into users(userid,username) values(@i,'username' convert(varchar(255),@i))??
? set @i=@i+1??
end?
go??
?
--統計SQL語句執行時間??
DECLARE @dt DATETIME??
SET @dt=GETDATE()??
--要執行的SQL語句??
SELECT [語句執行花費時間(毫秒)] = DATEDIFF(ms,@dt,GETDATE())??
?
--Case When語句??
SELECT UserName,sex = CASE WHEN sex='男' THEN '男人'
?????????????????????????? WHEN sex='女' THEN '女人'
?????????????????????????? ELSE '哈哈'
????????????????????? END,Age
? FROM Users??
?
--having使用方法??
--一個表中的UserName有很多重復,??
--只顯示重復項:???
select UserName,COUNT(*) from Users group by UserName having count(*)>1???
--不顯示重復項:???
select UserName,COUNT(*) from Users group by UserName having count(*)=1???
?