常用SQL语句备忘

1. --建数据库  
   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