做人第一 学习第二

数据库基本----SQL语句大全

 据库基本SQL语句大全

 

  一、基础

 

  1、说明:创建数据库

 

  CreateDATABASEdatabase-name

 

  2、说明:删除数据库

 

  dropdatabasedbname

 

  3、说明:备份sqlserver

 

  ---创建备份数据的device

 

  USEmaster

 

  EXECsp_addumpdevice'disk','testBack','c:/mssql7backup/MyNwind_1.dat'

 

  ---开始备份

 

  BACKUPDATABASEpubsTOtestBack

 

  4、说明:创建新表

 

  createtabletabname(col1type1[notnull][primarykey],col2type2[notnull],..)

 

  根据已有的表创建新表:

 

  A:createtabletab_newliketab_old(使用旧表创建新表)

 

  B:createtabletab_newasselectcol1,col2…fromtab_olddefinitiononly

 

  5、说明:删除新表

 

  droptabletabname

 

  6、说明:增加一个列

 

  Altertabletabnameaddcolumncoltype

 

  注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。

 

  7、说明:添加主键:Altertabletabnameaddprimarykey(col)

 

  说明:删除主键:Altertabletabnamedropprimarykey(col)

 

  8、说明:创建索引:create[unique]indexidxnameontabname(col….)

 

  删除索引:dropindexidxname

 

  注:索引是不可更改的,想更改必须删除重新建。

 

  9、说明:创建视图:createviewviewnameasselectstatement

 

  删除视图:dropviewviewname

 

  10、说明:几个简单的基本的sql语句

 

  选择:select*fromtable1where范围

 

  插入:insertintotable1(field1,field2)values(value1,value2)

 

  删除:deletefromtable1where范围

 

  更新:updatetable1setfield1=value1where范围

 

  查找:select*fromtable1wherefield1like’%value1%’---like的语法很精妙,查资料!

 

  排序:select*fromtable1orderbyfield1,field2[desc]

 

  总数:selectcountastotalcountfromtable1

 

  求和:selectsum(field1)assumvaluefromtable1

 

  平均:selectavg(field1)asavgvaluefromtable1

 

  最大:selectmax(field1)asmaxvaluefromtable1

 

  最小:selectmin(field1)asminvaluefromtable1

 

  11、说明:几个高级查询运算词

 

  A:UNION运算符

 

  UNION运算符通过组合其他两个结果表(例如TABLE1和TABLE2)并消去表中任何重复行而派生出一个结果表。当ALL随UNION一起使用时(即UNIONALL),不消除重复行。两种情况下,派生表的每一行不是来自TABLE1就是来自TABLE2。

 

  B:EXCEPT运算符

 

  EXCEPT运算符通过包括所有在TABLE1中但不在TABLE2中的行并消除所有重复行而派生出一个结果表。当ALL随EXCEPT一起使用时(EXCEPTALL),不消除重复行。

 

  C:INTERSECT运算符

 

  INTERSECT运算符通过只包括TABLE1和TABLE2中都有的行并消除所有重复行而派生出一个结果表。当ALL随INTERSECT一起使用时(INTERSECTALL),不消除重复

 

  行。注:使用运算词的几个查询结果行必须是一致的。

 

  12、说明:使用外连接

 

  A、leftouterjoin:

 

  左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。

 

  SQL:selecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTJOINbONa.a=b.c

 

  B:rightouterjoin:

 

  右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。

 

  C:fullouterjoin:

 

  全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。

 

  二、提升

 

  1、说明:复制表(只复制结构,源表名:a新表名:b)(Access可用)

 

  法一:select*intobfromawhere1<>1

 

  法二:selecttop0*intobfroma

 

  2、说明:拷贝表(拷贝数据,源表名:a目标表名:b)(Access可用)

 

  insertintob(a,b,c)selectd,e,ffromb;

 

  3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径)(Access可用)

 

  insertintob(a,b,c)selectd,e,ffrombin‘具体数据库’where条件

 

  例子:..frombin'"&Server.MapPath("."&"/data.mdb"&"'where..

 

  4、说明:子查询(表名1:a表名2:b)

 

  selecta,b,cfromawhereaIN(selectdfromb或者:selecta,b,cfromawhereaIN(1,2,3)

 

  5、说明:显示文章、提交人和最后回复时间

 

  selecta.title,a.username,b.adddatefromtablea,(selectmax(adddate)adddatefromtablewheretable.title=a.title)b

 

  6、说明:外连接查询(表名1:a表名2:b)

 

  selecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTJOINbONa.a=b.c

 

  7、说明:在线视图查询(表名1:a

 

  select*from(Selecta,b,cFROMa)Twheret.a>1;

 

  8、说明:between的用法,between限制查询数据范围时包括了边界值,notbetween不包括

 

  select*fromtable1wheretimebetweentime1andtime2

 

  selecta,b,c,fromtable1whereanotbetween数值1and数值2

 

  9、说明:in的使用方法

 

  select*fromtable1wherea[not]in(‘值1’,’值2’,’值4’,’值6’)

 

  10、说明:两张关联表,删除主表中已经在副表中没有的信息

 

  deletefromtable1wherenotexists(select*fromtable2wheretable1.field1=table2.field1

 

  11、说明:四表联查问题:

 

  select*fromaleftinnerjoinbona.a=b.brightinnerjoincona.a=c.cinnerjoindona.a=d.dwhere..

 

  12、说明:日程安排提前五分钟提醒

 

  select*from日程安排wheredatediff('minute',f开始时间,getdate())>5

 

  13、说明:一条sql语句搞定数据库分页

 

  selecttop10b.*from(selecttop20主键字段,排序字段from表名orderby排序字段desc)a,表名bwhereb.主键字段=a.主键字段orderbya.排序字段

 

  14、说明:前10条记录

 

  selecttop10*formtable1where范围

 

  15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)

 

  selecta,b,cfromtablenametawherea=(selectmax(a)fromtablenametbwheretb.b=ta.b)

 

  16、说明:包括所有在TableA中但不在TableB和TableC中的行并消除所有重复行而派生出一个结果表

 

  (selectafromtableAexcept(selectafromtableB)except(selectafromtableC)

 

  17、说明:随机取出10条数据

 

  selecttop10*fromtablenameorderbynewid()

 

  18、说明:随机选择记录

 

  selectnewid()

 

  19、说明:删除重复记录

 

  Deletefromtablenamewhereidnotin(selectmax(id)fromtablenamegroupbycol1,col2,)

 

  20、说明:列出数据库里所有的表名

 

  selectnamefromsysobjectswheretype='U'

 

  21、说明:列出表里的所有的

 

  selectnamefromsyscolumnswhereid=object_id('TableName')

 

  22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select中的case。

 

  selecttype,sum(casevenderwhen'A'thenpcselse0end),sum(casevenderwhen'C'thenpcselse0end),sum(casevenderwhen'B'thenpcselse0end)

 

  FROMtablenamegroupbytype

 

  显示结果:

 

  typevenderpcs

 

  电脑A1

 

  电脑A1

 

  光盘B2

 

  光盘A2

 

  手机B3

 

  手机C3

 

  23、说明:初始化表table1

 

  TRUNCATETABLEtable1

 

  24、说明:选择从10到15的记录

 

  selecttop5*from(selecttop15*fromtableorderbyidasc)table_别名orderbyiddesc

 

  三、技巧

 

  1、1=1,1=2的使用,在SQL语句组合时用的较多

 

  “where1=1”是表示选择全部“where1=2”全部不选,

 

  如:

  if@strWhere!='
 
  begin
 
  set@strSQL='selectcount(*)asTotalfrom['+@tblName+']where'+@strWhere
 
  end
 
  else
 
  begin
 
  set@strSQL='selectcount(*)asTotalfrom['+@tblName+']'
 
  end

 

 

  我们可以直接写成

 

  set@strSQL='selectcount(*)asTotalfrom['+@tblName+']where1=1安定'+@strWhere

 

  2、收缩数据库

 

  --重建索引

 

  DBCCREINDEX

 

  DBCCINDEXDEFRAG

 

  --收缩数据和日志

 

  DBCCSHRINKDB

 

  DBCCSHRINKFILE

 

  3、压缩数据库

 

  dbccshrinkdatabase(dbname)

 

  4、转移数据库给新用户以已存在用户权限

 

  execsp_change_users_login'update_one','newname','oldname'

 

  Go

 

  5、检查备份集

  RESTOREVERIFYONLYfromdisk='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、说明:更改某个表

  execsp_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、SQLSERVER中直接循环写入数据

    declare @i int
    set @i=1
    while @i<30
    begin
       insert into test (userid) values(@i)
       set @i=@i+1
    end

此文如未标注转载均为本站原创,自由转载请表明出处 宋子宪博客 » 数据库基本----SQL语句大全

发表评论

游客 表情
送你一朵小花花~

帅人已评:(0)