sql 常用语句积累

<script>function StorePage(){d=document;t=d.selection?(d.selection.type!='None'?d.selection.createRange().text:''):(d.getSelection?d.getSelection():'');void(keyit=window.open('http://www.365key.com/storeit.aspx?t='+escape(d.title)+'&u='+escape(d.location.href)+'&c='+escape(t),'keyit','scrollbars=no,width=475,height=575,left=75,top=20,status=no,resizable=yes'));keyit.focus();}</script>

1．如何删除表中的重复记录？(这里指记录的每个字段都要相同)
select  distinct  *  into  #temp  from  tab  
delete  tab  
insert  tab  select  *  from  #temp  
drop  table  #temp

 

 

2．怎样返回数据库中用户表的表单名
select  name  from  sysobjects  where  xtype=&apos;U&apos;  
select  name  from  sysobjects  where  xtype  =  &apos;u&apos;  and  status  >=0

 

 

3．返回两个表中共有的所有记录
select * from testTable as a inner join TestTableChild as b on a.id = b.parentid

 

 

4．返回两个表里共有的记录,且不重复

select a.id,a.name,b.name from testTable as a inner join TestTableChild as b on a.id = b.parentid group by a.id,a.name,b.name

 

 

5．向一个表A中插入记录,并且插入的记录在A中不存在(通过一个字段来判断)

insert into trace_users

(tracekey,muteSMS,CreateTime,traceuser,tracetime,traceSlot,traceduration)

 Select &apos;TRACE_TIMER&apos;,0,getdate(),mobileid,getdate(),&apos;30&apos;,&apos;0&apos; from Epm_EmployeeList where corpid = 10001 and not exists (select traceuser from trace_users ) and mobileid like &apos;13%&apos; and len(mobileid) = 11

 

 

6、根据出生日期，算出年龄

DATEDIFF(month, T.Birthday, GETDATE()) AS MONTHS //得到月份
MONTHS /12 取整就是年龄

 

7、等待时间再执行语句
waitfor delay &apos;00:00:05&apos;
select * from studentinfo

waitfor time ’23:08:00
select * from employee

 

8、指定值的范围查询

stockname like &apos;[a-zA-Z]%&apos; --------- ([]指定值的范围)
 
stockname like &apos;[^F-M]%&apos; --------- (^排除指定范围)

 

 

9、从表中获取值并插入另一张表中

insert into table2 (a) select a from table1

 

 

10、备份与恢复数据库

backup database SCardDB to disk = &apos;F:/SCardDB. 2006 年10 月10 日 (105748).bak&apos;

restore   database   kangda   from   disk=&apos;d:/backup.bak‘

 

 

11、对查询结果随机排序
SELECT * FROM Northwind..Orders ORDER BY NEWID()

 

 

12、按姓氏笔画排序
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as

 

 

13获取某一个表的所有字段
select name from syscolumns where id=object_id(&apos;表名&apos;)

 

 

14、记录转换

select *,case Type when 1 then &apos;移动&apos; when 2 then &apos;联通&apos; when 3 then &apos;小灵通&apos; end as TypeName from abc

 

 

15、按拼音首字母排序

select * from 表名 order by 列名 Collate Chinese_PRC_CS_AS_KS_WS