SQL语句精妙集合

 1一、基础

  3 1、说明：创建数据库
  4 Create  DATABASE  database -name
  5
  6 2、说明：删除数据库
  7 drop  database dbname
  8
  9 3、说明：备份sql server
 10 -- - 创建 备份数据的 device
 11 USE master
 12 EXEC sp_addumpdevice  disk, testBack, c:\mssql7backup\MyNwind_1.dat
 13 -- - 开始 备份
 14 BACKUP  DATABASE pubs  TO testBack
 15
 16 4、说明：创建新表
 17 create  table tabname(col1 type1  [ not null ]  [ primary key ],col2 type2  [ not null ],..)
 18 根据已有的表创建新表：
 19 A： create  table tab_new  like tab_old (使用旧表创建新表)
 20 B： create  table tab_new  as  select col1,col2…  from tab_old definition  only
 21
 22 5、说明：删除新表
 23 drop  table tabname
 24
 25 6、说明：增加一个列
 26 Alter  table tabname  add  column col type
 27 注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。
 28
 29 7、说明：添加主键：  Alter  table tabname  add  primary  key(col)
 30 说明：删除主键：  Alter  table tabname  drop  primary  key(col)
 31
 32 8、说明：创建索引： create  [ unique ]  index idxname  on tabname(col….)
 33 删除索引： drop  index idxname
 34 注：索引是不可更改的，想更改必须删除重新建。
 35
 36 9、说明：创建视图： create  view viewname  as  select statement
 37 删除视图： drop  view viewname
 38
 39 10、说明：几个简单的基本的sql语句
 40 选择： select  *  from table1  where 范围
 41 插入： insert  into table1(field1,field2)  values(value1,value2)
 42 删除： delete  from table1  where 范围
 43 更新： update table1  set field1 =value1  where 范围
 44 查找： select  *  from table1  where field1  like ’ %value1 %’  -- -like的语法很精妙，查资料!
 45 排序： select  *  from table1  order  by field1,field2  [ desc ]
 46 总数： select  count  as totalcount  from table1
 47 求和： select  sum(field1)  as sumvalue  from table1
 48 平均： select  avg(field1)  as avgvalue  from table1
 49 最大： select  max(field1)  as maxvalue  from table1
 50 最小： select  min(field1)  as minvalue  from table1
 51
 52 11、说明：几个高级查询运算词
 53 A：  UNION 运算符
 54 UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当  ALL 随  UNION 一起使用时（即  UNION  ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
 55 B：  EXCEPT 运算符
 56 EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当  ALL 随  EXCEPT 一起使用时 ( EXCEPT  ALL)，不消除重复行。
 57 C：  INTERSECT 运算符
 58 INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当  ALL 随  INTERSECT 一起使用时 ( INTERSECT  ALL)，不消除重复行。
 59 注：使用运算词的几个查询结果行必须是一致的。
 60
 61 12、说明：使用外连接
 62 A、 left  outer  join：
 63 左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。
 64 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
 65 B： right  outer  join:
 66 右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。
 67 C： full  outer  join：
 68 全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。
 69
 70 二、提升
 71
 72 1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)
 73 法一： select  *  into b  from a  where  1 <> 1
 74 法二： select  top  0  *  into b  from a
 75
 76 2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)
 77 insert  into b(a, b, c)  select d,e,f  from b;
 78
 79 3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
 80 insert  into b(a, b, c)  select d,e,f  from b  in ‘具体数据库’  where 条件
 81 例子：.. from b  in  &Server.MapPath(.) &\data.mdb  &  where..
 82
 83 4、说明：子查询(表名1：a 表名2：b)
 84 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)
 85
 86 5、说明：显示文章、提交人和最后回复时间
 87 select a.title,a.username,b.adddate  from  table a,( select  max(adddate) adddate  from  table  where  table.title =a.title) b
 88
 89 6、说明：外连接查询(表名1：a 表名2：b)
 90 select a.a, a.b, a.c, b.c, b.d, b.f  from a  LEFT OUT  JOIN b  ON a.a  = b.c
 91
 92 7、说明：在线视图查询(表名1：a )
 93 select  *  from ( Select a,b,c  FROM a) T  where t.a  >  1;
 94
 95 8、说明：between的用法,between限制查询数据范围时包括了边界值, not between不包括
 96 select  *  from table1  where time  between time1  and time2
 97 select a,b,c,  from table1  where a  not  between 数值1  and 数值2
 98
 99 9、说明： in 的使用方法
100 select  *  from table1  where a  [ not ]  in (‘值1’,’值2’,’值4’,’值6’)
101
102 10、说明：两张关联表，删除主表中已经在副表中没有的信息
103 delete  from table1  where  not  exists (  select  *  from table2  where table1.field1 =table2.field1 )
104
105 11、说明：四表联查问题：
106 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  ..
107
108 12、说明：日程安排提前五分钟提醒
109 SQL:  select  *  from 日程安排  where  datediff(minute,f开始时间, getdate()) > 5
110
111 13、说明：一条sql 语句搞定数据库分页
112 select  top  10 b. *  from ( select  top  20 主键字段,排序字段  from 表名  order  by 排序字段  desc) a,表名 b  where b.主键字段  = a.主键字段  order  by a.排序字段
113
114 14、说明：前10条记录
115 select  top  10  * form table1  where 范围
116
117 15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
118 select a,b,c  from tablename ta  where a =( select  max(a)  from tablename tb  where tb.b =ta.b)
119
120 16、说明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表
121 ( select a  from tableA )  except ( select a  from tableB)  except ( select a  from tableC)
122
123 17、说明：随机取出10条数据
124 select  top  10  *  from tablename  order  by  newid()
125
126 18、说明：随机选择记录
127 select  newid()
128
129 19、说明：删除重复记录
130 Delete  from tablename  where id  not  in ( select  max(id)  from tablename  group  by col1,col2, )
131
132 20、说明：列出数据库里所有的表名
133 select name  from sysobjects  where type =U
134
135 21、说明：列出表里的所有的
136 select name  from syscolumns  where id = object_id(TableName)
137
138 22、说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。
139 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
140 显示结果：
141 type vender pcs
142 电脑 A  1
143 电脑 A  1
144 光盘 B  2
145 光盘 A  2
146 手机 B  3
147 手机 C  3
148
149 23、说明：初始化表table1
150
151 TRUNCATE  TABLE table1
152
153 24、说明：选择从10到15的记录
154 select  top  5  *  from ( select  top  15  *  from  table  order  by id  asc) table_别名  order  by id  desc
155 三、技巧
156
157 1、 1 = 1， 1 =2的使用，在SQL语句组合时用的较多
158
159 “ where  1 = 1” 是表示选择全部 “ where  1 = 2”全部不选，
160 如：
161 if  @strWhere  != '' 
162 begin
163 set  @strSQL  =  ' select count(*) as Total from [ '  +  @tblName  +  ' ] where  '  +  @strWhere 
164 end
165 else 
166 begin
167 set  @strSQL  =  ' select count(*) as Total from [ '  +  @tblName  +  ' ] ' 
168 end 
169
170 我们可以直接写成
171 set  @strSQL  =  ' select count(*) as Total from [ '  +  @tblName  +  ' ] where 1=1 安定  ' +  @strWhere 
172
173 2、收缩数据库
174 -- 重建索引
175 DBCC REINDEX
176 DBCC INDEXDEFRAG
177 -- 收缩数据和日志
178 DBCC SHRINKDB
179 DBCC SHRINKFILE
180
181 3、压缩数据库
182 dbcc shrinkdatabase(dbname)
183
184 4、转移数据库给新用户以已存在用户权限
185 exec sp_change_users_login  ' update_one ', ' newname ', ' oldname '
186 go
187
188 5、检查备份集
189 RESTORE VERIFYONLY  from  disk = ' E:\dvbbs.bak '
190
191 6、修复数据库
192 Alter  DATABASE  [ dvbbs ]  SET SINGLE_USER
193 GO
194 DBCC CHECKDB( ' dvbbs ',repair_allow_data_loss)  WITH TABLOCK
195 GO
196 Alter  DATABASE  [ dvbbs ]  SET MULTI_USER
197 GO
198
199 7、日志清除
200 SET NOCOUNT  ON
201 DECLARE  @LogicalFileName sysname,
202 @MaxMinutes  INT,
203 @NewSize  INT
204
205
206 USE tablename  --  要操作的数据库名
207 Select  @LogicalFileName  =  ' tablename_log ',  --  日志文件名
208 @MaxMinutes  =  10,  --  Limit on time allowed to wrap log.
209 @NewSize  =  1  --  你想设定的日志文件的大小(M)
210
211 --  Setup / initialize
212 DECLARE  @OriginalSize  int
213 Select  @OriginalSize  = size 
214 FROM sysfiles
215 Where name  =  @LogicalFileName
216 Select  ' Original Size of  '  +  db_name()  +  '  LOG is  '  + 
217 CONVERT( VARCHAR( 30), @OriginalSize)  +  '  8K pages or  '  + 
218 CONVERT( VARCHAR( 30),( @OriginalSize * 8 / 1024))  +  ' MB '
219 FROM sysfiles
220 Where name  =  @LogicalFileName
221 Create  TABLE DummyTrans
222 (DummyColumn  char ( 8000)  not  null)
223
224
225 DECLARE  @Counter  INT,
226 @StartTime  DATETIME,
227 @TruncLog  VARCHAR( 255)
228 Select  @StartTime  =  GETDATE(),
229 @TruncLog  =  ' BACKUP LOG  '  +  db_name()  +  '  WITH TRUNCATE_ONLY '
230
231 DBCC SHRINKFILE ( @LogicalFileName,  @NewSize)
232 EXEC ( @TruncLog)
233 --  Wrap the log if necessary.
234 WHILE  @MaxMinutes  >  DATEDIFF (mi,  @StartTime,  GETDATE())  --  time has not expired
235 AND  @OriginalSize  = ( Select size  FROM sysfiles  Where name  =  @LogicalFileName) 
236 AND ( @OriginalSize  *  8  / 1024)  >  @NewSize 
237 BEGIN  --  Outer loop.
238 Select  @Counter  =  0
239 WHILE (( @Counter  <  @OriginalSize  /  16)  AND ( @Counter  <  50000))
240 BEGIN  --  update
241 Insert DummyTrans  VALUES ( ' Fill Log ') 
242 Delete DummyTrans
243 Select  @Counter  =  @Counter  +  1
244 END 
245 EXEC ( @TruncLog) 
246 END 
247 Select  ' Final Size of  '  +  db_name()  +  '  LOG is  '  +
248 CONVERT( VARCHAR( 30),size)  +  '  8K pages or  '  + 
249 CONVERT( VARCHAR( 30),(size * 8 / 1024))  +  ' MB '
250 FROM sysfiles 
251 Where name  =  @LogicalFileName
252 Drop  TABLE DummyTrans
253 SET NOCOUNT  OFF 
254
255 8、说明：更改某个表
256 exec sp_changeobjectowner  ' tablename ', ' dbo '
257
258 9、存储更改全部表
259
260 Create  PROCEDURE dbo.User_ChangeObjectOwnerBatch
261 @OldOwner  as  NVARCHAR( 128),
262 @NewOwner  as  NVARCHAR( 128)
263 AS
264
265 DECLARE  @Name  as  NVARCHAR( 128)
266 DECLARE  @Owner  as  NVARCHAR( 128)
267 DECLARE  @OwnerName  as  NVARCHAR( 128)
268
269 DECLARE curObject  CURSOR  FOR 
270 select  ' Name '  = name,
271 ' Owner '  =  user_name(uid)
272 from sysobjects
273 where  user_name(uid) = @OldOwner
274 order  by name
275
276 OPEN curObject
277 FETCH  NEXT  FROM curObject  INTO  @Name,  @Owner
278 WHILE( @@FETCH_STATUS = 0)
279 BEGIN 
280 if  @Owner = @OldOwner 
281 begin
282 set  @OwnerName  =  @OldOwner  +  ' . '  +  rtrim( @Name)
283 exec sp_changeobjectowner  @OwnerName,  @NewOwner
284 end
285 --  select @name,@NewOwner,@OldOwner
286
287 FETCH  NEXT  FROM curObject  INTO  @Name,  @Owner
288 END
289
290 close curObject
291 deallocate curObject
292 GO
293
294
295 10、SQL SERVER中直接循环写入数据
296 declare  @i  int
297 set  @i = 1
298 while  @i < 30
299 begin
300 insert  into test (userid)  values( @i)
301 set  @i = @i + 1
302 end 
303