sqlserver 数据库查询技巧

概述一、基础 …… 二、提升  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

一、基础

……

二、提升 

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,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,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,c FROM a) T where t.a > 1;  8、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括  select * from table1 where time between time1 and time2  select a,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,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,getdate()),-1)  Dateadd(wk,6)  Dateadd(mm,datediff(mm,0)  Dateadd(ms,-3,dateadd(mm,datediff(m,getdate())+1,0))  Dateadd(yy,datediff(yy,DATEADD(yy, DATEDIFF(yy, 0))  上面的sql代码只是一个时间段  Dateadd(wk,6)  就是表示本周时间段.  下面的sql的条件部分,就是查询时间段在本周范围内的:  Where Time BETWEEN Dateadd(wk,-1) AND Dateadd(wk,6)  而在存储过程中  select @begintime = Dateadd(wk,-1)  select @endtime = Dateadd(wk,6)  最后，再补充一些： 分组group 　　常用于统计时，如分组查总数： select gender,count(sno)  from students group by gender (查看男女学生各有多少) 　　注意：从哪种角度分组就从哪列"group by" 　　对于多重分组，只需将分组规则罗列。比如查询各届各专业的男女同学人数 ，那么分组规则有：届别(grade)、专业(mno)和性别(gender)，所以有"group by grade, mno, gender" select grade, gender, count(*) from students group by grade, gender 　　通常group还和having联用，比如查询1门课以上不及格的学生，则按学号(sno)分类有： select sno,count(*) from grades  where mark<60 group by sno having count(*)>1  　　6.UNION联合 　　合并查询结果，如： SELECT * FROM students WHERE name like ‘张%’ UNION [ALL] SELECT * FROM students WHERE name like ‘李%’ 　　7.多表查询 　　a.内连接 select g.sno,s.name,c.coursename  from grades g JOIN students s ON g.sno=s.sno JOIN courses c ON g.cno=c.cno (注意可以引用别名) b.外连接 b1.左连接 select courses.cno,max(coursename),count(sno)  from courses left JOIN grades ON courses.cno=grades.cno  group by courses.cno 　　左连接特点：显示全部左边表中的所有项目，即使其中有些项中的数据未填写完全。 　　左外连接返回那些存在于左表而右表中却没有的行，再加上内连接的行。 　　b2.右连接 　　与左连接类似 　　b3.全连接 select sno,name,major  from students FulL JOIN majors ON students.mno=majors.mno 　　两边表中的内容全部显示 　　c.自身连接 select c1.cno,c1.coursename,c1.pno,c2.coursename  from courses c1,courses c2 where c1.pno=c2.cno 　　采用别名解决问题。 　　d.交叉连接 select lastname+firstname from lastname CROSS JOIN firstanme 　　相当于做笛卡儿积 总结

以上是内存溢出为你收集整理的sqlserver 数据库查询技巧全部内容，希望文章能够帮你解决sqlserver 数据库查询技巧所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。