经典收藏SQL语句合集（来自大家的智慧）2

n久前整理过的一些最基本的,随意看看.
1.Insert (添加数据)
.Inert Into tb1(field1,field2) Values(11,'aa')
.Inert tb1 Values(11,"aa")
.Inert tb1(field1,field2) Select field3,'s'+field4 From tb2 Where field3<22
.Inert tb1 Select * From tb2 Where field3<22

2.Update (修改数据)
.Update tb1 Set field1=field1+1,field2='zz' Where field1<1
.Update tb1 Set field1=field1+1,field2='zz' From (Select * From tb2 Where field3>1) As a Where a.field3=table1.field2
.Update tb1 Set field1=field1+1,field2='zz' From table2 As a,table1 as b Where a.field4=table1.field2

3.Delete (删除数据)
.Delete table1 Where field2 Like 'a%'
.Delete table1 From (Select * From table2 Where field3<1) As a Where a.field4=table1.field2
.Delete table1 As a,table2 As b Where a.field2=b.field4 And b.field3<1

4.Creat (在本机上新建msde数据库.如需安装在始动路径,改绝对路径为Application.StartupPath)
   .if object_id('msde1') is null CREATE DATABASE msde1 ON (Name=msde1_dat,filename='C:\\msde1.mdf',size=10,maxsize=50,filegrowth=10%) log on (name=msde1_log,filename='c:\\msde1.ldf',size=3,maxsize=20,filegrowth=1)

5.Drop (删除表)
.Drop table1
.if object_id('table1') is not null DROP TABLE table1 (删除之前可先判断一下.)
.Drop table1 Truncate Table table2 (如table1 参照 table2,需要先删除 table1)

6.If,Else,Begin
If Exists(Select field2 From table1 Where field2='aa')
     Begin
Delect table1 Where field2='aa'
        Print 'aa is deleted.'
     End
Else
        Print 'aa is not found.'

7.Declare,GOTO
Declare @s int,@time int
Select @s=1,@time=1
label1:
Select @s=@s*@time
Select @time=@time+1
If @time<=10
GOTO label1
Print '10 n='+str(@s)

8.While,Break,Continue
While Boolean   //Boolean: 1=1
{SQL}
Break
{SQL}
Continue
{SQL}

9.WaitFor
Begin
WaitFor Time '10:00:00' //(系统时间)
Select * From Table1
End

Begin
WaitFor Delay '1:00:00' //(1小时后)
Select * From Table1
End

10.Return
   Create Procedure check_contact @para varchare(40)
AS
If (Select contract From authors Where au_lname=@para)=1
Return 1
Else
Return 2

11.Case
   Select Name=Convert(varchar(15),au_lname),
Contract=Case contract
When 0 Then '0'
When 1 Then '1'
Else '2'
End
   From authors

   Select Name=Convert(varchar(15),au_lname),
Contract=Case
When contract=0 Then '0'
When contract<>0 Then '1'
End
   From authors

12.DataLength //返回表达式中数据的实际长度(如为字段名,则返回字段中数据的长度,非字段定义长度.)
   Select Name=field1,DataLength=DataLength(field1) From table1

13.Current_TimeStamp //返回系统的当前日期和时间
   Use pubs
   GO
   Creat Table books
   {
book_id char(3) Not NULL,
pub_date datetime Not NULL Default Current_TimeStamp,
price money
   }
   GO
   Insert books(book_id,price)
Values('A01',$55.0)

14.FormatMessage//格式化指定错误号的错误信息
   Declare @Err123 nvarchar(64)
   Set @Err123=FormatMessage(123,50)
   Print @Err123

15.GetAnsiNULL//返回数据库的默认空值设置
   Select GetAnsiNULL('pubs')//return 1:notNull; return 0: NULL

16.IsNULL //替换空值项(注意替换项和替换内容的类型需一致)
   Select title=field2,num=IsNULL(field1,0) From table1
   Select title=field2,num=IsNULL(str(field1),'Nothing') From table1

17.Coalescs//返回参数中的第一个非空(NULL)表达式
   Select title=field2,numMark=Coalescs(field1,field2,'Nothing') From table1
The Result: If field1 is null And field2 is not null Then return field2.value.
       If field1 is null And field2 is null Then return 'Nothing'.

18.Permissions//返回用户的语句和对象权限.
   Declare @state_p int,@object_p int
   Set @state_p=Permissions()
If (@sate_p & 2)=2
Print '你可以在当前数据库中创建表'
If (@sate_p & 4)=4
Print '你可以在当前数据库中创建存储过程'
If (@sate_p &=8
Print '你可以在当前数据库中创建视图'
Set @object_p=Permissions(Object_id('pubs..jobs'))
If (@object_p & 1)=1
Print '你可以检索jobs表中的数据'
If (@object_p & 2)=2
Print '你可以修改jobs表中的数据'
If (@object_p &=8
Print '你可以向jobs表中添加数据'
If (@object_p & 16)=16
Print '你可以删除jobs表中的数据'

19.Convert//转换函数(从一种系统数据类型转换为另外一种)
   Select thetime1=Convert(char,Current_TimeStamp,120),thetime2=Convert(char,Current_TimeStamp,108)
//The Result: thetime1:2006-03-13 15:50:00; thetime2:03:50:00
   Select s0=Convert(char,$123456789.9876,0),s1=Convert(char,$123456789.9876,1),s2=Convert(char,$123456789.9876,2)
//The Result: s0:123456789.99; s1:123,456,789.99; s2:123456789.9876
   Select n0=Convert(char,123456789.9876,0),n1=Convert(char,$123456789.9876,1),n2=Convert(char,$123456789.9876,2)
//The Result: n0:123457; n1:1.2345679e+005; n2:1.234567890000000e+005

20.Cast//转换函数(转换格式控制没有Convert函数灵活)
   Select title_id,theDate=Cast(pubdate As Char(11)) From titles Where title_id Like 'b%'
//The Result: title_id: BU1032; theDate: Jun 12 1991

****************************
一些日期元素的取值范围:yy(1753~9999),qq(1~4),mm(1~12),dy(1~366),dd(1~31),wk(0~51),hh(0~23),mi(1~59),ss(1~59),ms(0~999)
****************************
21.DateAdd//返回指定时间间隔后的日期.
   Select NowDate=GetDate(),LateDate=DateAdd(dy,60,GetDate())

22.DateDiff       //返回时间间隔.
   Select TheDate=pubdate,NowDate=GetDate(),NY=DateDiff(yy,pubdate,GetDate()),NM=DateDiff(mm,pubdate,GetDate()) From titles Where type='business'

23.DateName,DatePart//返回日期中指定部分对应的字符串,整数值
   Select YN=DateName(yy,GetDate())   The Result:'2006'
   Select YI=DatePart(yy,GetDate())   The Result:2006

24.Char,Ascii
   Print 'A==>ASCII value is:'+Char(13)+Str(Ascii('A'),2,0)//Enter key ASCII is 13.
//The Result:A==>ASCII value is:
                 65

25.Stuff //删除字符串中指定位置的字符串,然后在删除位置插入字符串.
   Print Stuff('abcdefghijk',3,5,'ABCD')
//The Result:abABCDhijk

26.Str
   Print Str(10.1234,10,6)
//The Result:10.123400

27.Replicate,Replace//按指定次数复制字符串,替换字符串中的内容.
   Select Replace(Replicate('ABC',2),'BC','bc')
//The Result:AbcAbc

28.sin,cos,tan,ctan,Radians
   Select sin30du=Sin(Radians(30.0)),ctan30du=cot(Radians(30.0))

29.Round(近似值)
   Select r1=Round(918.2736,3),r2=Round(918.2736,-2),r3=Round(918.2736,3,1),r4=Round(918.2736,-2,1)
//The Result: r1:918.2740; r2:900.0000; r3:918.2730; r4:900.0000;


经过对SQLServer2000系统表的分析，写出了以下两个SQL语句。可以把这两个语句分别建为两个“视图”，方便查看用户数据表和字段的信息。

1、列出所有的用户数据表：

SELECT TOP 100 PERCENT o.name AS 表名
FROM dbo.syscolumns c INNER JOIN
      dbo.sysobjects o ON o.id = c.id AND objectproperty(o.id, N'IsUserTable') = 1 AND
      o.name <> 'dtproperties' LEFT OUTER JOIN
      dbo.sysproperties m ON m.id = o.id AND m.smallid = c.colorder
WHERE (c.colid = 1)
ORDER BY o.name, c.colid

2、列出所有的用户数据表及其字段信息：

SELECT TOP 100 PERCENT c.colid AS 序号, o.name AS 表名, c.name AS 列名,
      t.name AS 类型, c.length AS 长度, c.isnullable AS 允许空,
      CAST(m.[value] AS Varchar(100)) AS 说明
FROM dbo.syscolumns c INNER JOIN
      dbo.sysobjects o ON o.id = c.id AND objectproperty(o.id, N'IsUserTable') = 1 AND
      o.name <> 'dtproperties' INNER JOIN
      dbo.systypes t ON t.xusertype = c.xusertype LEFT OUTER JOIN
      dbo.sysproperties m ON m.id = o.id AND m.smallid = c.colorder
ORDER BY o.name, c.colid

你如果有更好的方法，请交流一下:)

----作者：夏春涛　Email：xChuntao@163.com 　QQ:23106676 ----

From:　http://www.cnblogs.com/bluesky521/archive/2007/03/23/685783.html
CREATE PROCEDURE sp_page
@CurrentPage int, @PageSize int,@Field_Info varchar(500),@Table_info varchar(20),@Field_id varchar(10),@intOrder int,@otherwhere varchar(50),@RecordCount int output,@PageCount int output
--@CurrentPage为显示那一页,@PageSize为每一页显示几行,@Field_info为要显示的字段可以 为*,@Table_info为要查询的表或视图,@field_id为按这个字段排序,@intorder0为升序排1为降序 排,@otherwhere为条件,@RecordCount为总行数,@PageCount为总页数
AS
DECLARE @MinPage int, @MaxPage int
declare @sql varchar(1000)
declare @sqlt nvarchar(300)
declare @order varchar(4)
set @sqlt = 'SELECT @RecordCount = COUNT(' + @Field_id + ') FROM ' + @Table_Info
exec sp_executesql @sqlt,N'@RecordCount int output',@RecordCount output     --如何将exec执行结果放入变量中,如果是字符串就要用N,N后面的变量一定要和@sqlt里面的变量同名
IF @PageSize <= 0
begin
set @PageSize = 10
end
else if @PageSize > @RecordCount
begin
set @pageSize = @RecordCount
end
set @pagecount = @RecordCount / @PageSize
if ((@recordcount % @pagesize) != 0)--如果除不尽则加一页
begin
set @PageCount = @RecordCount / @PageSize
set @PageCount = @pagecount + 1
end
else
begin
set @pagecount = @recordcount /@PageSize
end
IF @CurrentPage <= 0
begin
set @CurrentPage = 1
end
else if @CurrentPage > @pagecount
begin
set @currentpage = @pagecount--如果输入页数大于总页数则符最后一页
end
SET @MinPage = (@CurrentPage - 1) * @PageSize + 1
SET @MaxPage = @MinPage + @PageSize - 1

BEGIN
if @intorder = 0
set @order = 'asc'
else
set @order = 'desc'
if @Field_Info like ''
set @field_Info = '*'
if @otherwhere like ''
set @sql = 'select ' + @Field_Info + ' from (select ' + @Field_Info + ' , row_number() over(order by ' + @Field_id + ' ' + @Order + ') as rownumber from ' + @Table_info + ') as a where rownumber between ' + convert(varchar(10),@minpage) + ' and ' + convert(varchar(10),@maxpage)
else
set @sql = 'select ' + @Field_Info + ' from (select ' + @Field_Info + ' , row_number() over(order by ' + @Field_id + ' ' + @Order + ') as rownumber from ' + @Table_info + ') as a where rownumber between ' + convert(varchar(10),@minpage) + ' and ' + convert(varchar(10),@maxpage) + ' and ' + @otherwhere
exec(@sql)
END


declare @rcon int
declare @pcon int
exec sp_page 8,73,'','user_info','id',0,'',@rcon output,@pcon output


这是我修改别人的一个数据库分页的存储过程
--清除企业管理器的备份历史记录


use msdb

declare @db varchar(100)

set @db = '数据库名'

--查看SQL Server企业管理器的备份数据库历史记录
select f.device_type, f.physical_device_name, f.logical_device_name, b.database_name
from backupmediafamily f, backupset b
where b.database_name = @db and b.backup_finish_date in (select backup_finish_date from backupmediafamily INNER JOIN backupset ON backupmediafamily.media_set_id=backupset.media_set_id where backupset.database_name = @db and (backupmediafamily.device_type=2 or backupmediafamily.device_type=102)) and b.media_set_id = f.media_set_id

--删除SQL Server企业管理器的备份数据库历史记录
delete backupmediafamily
from backupmediafamily f, backupset b
where b.database_name = @db and b.backup_finish_date in (select backup_finish_date from backupmediafamily INNER JOIN backupset ON backupmediafamily.media_set_id=backupset.media_set_id where backupset.database_name = @db and (backupmediafamily.device_type=2 or backupmediafamily.device_type=102)) and b.media_set_id = f.media_set_id
随机选择数据库记录的方法（使用Randomize函数，通过SQL语句实现）

对存储在数据库中的数据来说，随机数特性能给出上面的效果，但它们可能太慢了些。你不能要求ASP“找个随机数”然后打印出来。实际上常见的解决方案是建立如下所示的循环：

Randomize
RNumber = Int(Rnd*499) +1

While Not objRec.EOF
If objRec("ID") = RNumber THEN
这里是执行脚本
end if
objRec.MoveNext
Wend

这很容易理解。首先，你取出1到500范围之内的一个随机数（假设500就是数据库内记录的总数）。然后，你遍历每一记录来测试ID 的值、检查其是否匹配RNumber。满足条件的话就执行由THEN 关键字开始的那一块代码。假如你的RNumber 等于495，那么要循环一遍数据库花的时间可就长了。虽然500这个数字看起来大了些，但相比更为稳固的企业解决方案这还是个小型数据库了，后者通常在一 个数据库内就包含了成千上万条记录。这时候不就死定了？

采用SQL，你就可以很快地找出准确的记录并且打开一个只包含该记录的recordset，如下所示：

Randomize
RNumber = Int(Rnd*499) + 1

SQL = "SELECT * FROM Customers WHERE ID = " & RNumber

set objRec = ObjConn.Execute(SQL)
Response.WriteRNumber & " = " & objRec("ID") & " " & objRec("c_email")

不必写出RNumber 和ID，你只需要检查匹配情况即可。只要你对以上代码的工作满意，你自可按需操作“随机”记录。Recordset没有包含其他内容，因此你很快就能找到你需要的记录这样就大大降低了处理时间。

再谈随机数

现在你下定决心要榨干Random 函数的最后一滴油，那么你可能会一次取出多条随机记录或者想采用一定随机范围内的记录。把上面的标准Random 示例扩展一下就可以用SQL应对上面两种情况了。

为了取出几条随机选择的记录并存放在同一recordset内，你可以存储三个随机数，然后查询数据库获得匹配这些数字的记录：

SQL = "SELECT * FROM Customers WHERE ID = " & RNumber & " OR ID = " & RNumber2 & " OR ID = " & RNumber3

假如你想选出10条记录（也许是每次页面装载时的10条链接的列表），你可以用BETWEEN 或者数学等式选出第一条记录和适当数量的递增记录。这一操作可以通过好几种方式来完成，但是 SELECT 语句只显示一种可能（这里的ID 是自动生成的号码）：
SQL = "SELECT * FROM Customers WHERE ID BETWEEN " & RNumber & " AND " & RNumber & "+ 9"

注意：以上代码的执行目的不是检查数据库内是否有9条并发记录。

随机读取若干条记录，测试过

Access语法：SELECT top 10 * From 表名 ORDER BY Rnd(id)
Sql server:select top n * from 表名 order by newid()
mysql select * From 表名 Order By rand() Limit n

Access左连接语法(最近开发要用左连接,Access帮助什么都没有,网上没有Access的SQL说明,只有自己测试, 现在记下以备后查)

语法 select table1.fd1,table1,fd2,table2.fd2 From table1 left join table2 on table1.fd1,table2.fd1 where

使用SQL语句 用代替过长的字符串显示

语法：

SQL数据库：select case when len(field)>10 then left(field,10)+'' else field end as news_name,news_id from tablename
Access数据库：SELECT iif(len(field)>2,left(field,2)+'',field) FROM tablename;

Conn.Execute说明

Execute方法

该方法用于执行SQL语句。根据SQL语句执行后是否返回记录集，该方法的使用格式分为以下两种：

1．执行SQL查询语句时，将返回查询得到的记录集。用法为：

Set 对象变量名=连接对象.Execute("SQL 查询语言")

Execute方法调用后，会自动创建记录集对象，并将查询结果存储在该记录对象中，通过Set方法，将记录集赋给指定的对象保存，以后对象变量就代表了该记录集对象。

2．执行SQL的操作性语言时，没有记录集的返回。此时用法为：

连接对象.Execute "SQL 操作性语句" [, RecordAffected][, Option]

·RecordAffected 为可选项，此出可放置一个变量，SQL语句执行后，所生效的记录数会自动保存到该变量中。通过访问该变量，就可知道SQL语句队多少条记录进行了操作。

·Option 可选项，该参数的取值通常为adCMDText，它用于告诉ADO，应该将Execute方法之后的第一个字符解释为命令文本。通过指定该参数，可使执行更高效。

·BeginTrans、RollbackTrans、CommitTrans方法

这三个方法是连接对象提供的用于事务处理的方法。BeginTrans用于开始一个事物；RollbackTrans用于回滚事务；CommitTrans用于提交所有的事务处理结果，即确认事务的处理。

事务处理可以将一组操作视为一个整体，只有全部语句都成功执行后，事务处理才算成功；若其中有一个语句执行失败，则整个处理就算失败，并恢复到处里前的状态。

BeginTrans和CommitTrans用于标记事务的开始和结束，在这两个之间的语句，就是作为事务处理的语句。判断事务处理是否成 功，可通过连接对象的Error集合来实现，若Error集合的成员个数不为0，则说明有错误发生，事务处理失败。Error集合中的每一个Error对 象，代表一个错误信息。
SELECT *, UPPER(master.dbo.fn_varbintohexstr(TermAddress)) AS TermAddress_Hex
FROM TerminalPrm
WHERE (master.dbo.fn_varbintohexstr(TermAddress) LIKE '0%46%')