30条经典的SQL语句

久前整理过的一些最基本的,随意看看.
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's 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)=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)=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's 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;