sqlserver 数据库查询技巧
发布时间：2020-12-12 14:46:30 所属栏目：MsSql教程 来源：网络整理
导读：一、基础 …… 二、提升? 1、说明：复制表(只复制结构,源表名：a?新表名：b)?(Access可用)? 法一：select?*?into?b?from?a?where?11? 法二：select?top?0?*?into?b?from?a? 2、说明：拷贝表(拷贝数据,源表名：a?目标表名：b)?(Access可用)? insert?into?b(a,
|
一、基础 …… 二、提升? 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 相当于做笛卡儿积（编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |