您的位置:新葡亰496net > 网络数据库 > 新葡亰496net精妙SQL语句收罗,常用优秀SQL语句大

新葡亰496net精妙SQL语句收罗,常用优秀SQL语句大

发布时间:2019-09-08 03:40编辑:网络数据库浏览(92)

    三、技巧
    1、1=1,1=2 的使用,在 SQL 语句组合时用的相当多
    “where 1=1” 是表示采用一切
    “where 1=2”全体不选,
    如:
    if @strWhere !=''
    begin set @strSQL = 'select count(*) as Total from [' @tblName '] where ' @strWhere
    end
    else
    begin
    set @strSQL = 'select count(*) as Total from [' @tblName ']'
    end

    三、技巧

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

    “where 1=1” 是意味接纳一切    “where 1=2”整体不选,

    如:

    if

    @strWhere !=''

    begin

    set @strSQL = 'select count(*) as Total from [' @tblName '] where ' @strWhere

    end

    else

    begin

    set @strSQL = 'select count(*) as Total from [' @tblName ']'

    end

    咱俩得以一向写成

    谬误!未找到目录项。

    set @strSQL = 'select count(*) as Total from [' @tblName '] where 1=1 安定 ' @strWhere

    2、裁减数据库

    --重新建构索引

    DBCC REINDEX DBCC INDEXDEFRAG

    --减弱数据和日志

    DBCC SHRINKDB DBCC SHRINKFILE

    3、压缩数据库

    dbccshrinkdatabase(dbname)

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

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

    5、检查备份集

    RESTORE VERIFYONLY fromdisk='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 notexpired  AND @OriginalSize = (SELECT size FROMsysfiles 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 pagesor '  CONVERT(VARCHAR(30),(size*8/1024)) 'MB'  FROM sysfiles  WHERE name = @LogicalFileName DROP TABLE DummyTrans SET NOCOUNT OFF

    8、表达:更换有些表

    exec sp_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、SQL SE汉兰达VE帕杰罗中一贯循环写入数据

    declare @i int set @i=1 while @i<30 begin    

    insert into test (userid) values(@i)    

    set @i=@i 1 end

    案例: 有如下表,须要就裱中负有沒有及格的成績,在每一次增長0.1的基礎上,使他們剛好及格:

        Name     score

        Zhangshan   80

        Lishi       59

        Wangwu      50

        Songquan    69

    while((select min(score) from tb_table)<60)

    begin

    update tb_table set score =score*1.01

    where score<60

    if (select min(score) from tb_table)>60

     break

     else

       continue

    end

    一、基础

    SQL语句先前写的时候,很轻巧把一部分非同小可的用法忘记,笔者故意整理了一晃SQL语句操作,方便本人写SQL时有助于一点,想贴上来,一齐探望,同一时候希望大家能一齐多多提意见,也给自己留部分更加好的清词丽句,整理一份《精妙SQL速查手册》,不吝赐教!

    作者们得以平昔写成
    set @strSQL = 'select count(*) as Total from [' @tblName ']
    where 1=1 ' @strWhere

    1、创制数据库

    一、基础

    2、减少数据库
    --重新建构索引 DBCC REINDEX DBCC INDEXDEFRAG
    --缩小数据和日志 DBCC SH中华VINKDB DBCC SH奥迪Q3INKFILE

        

    1、表达:创设数据库
    CREATE DATABASE database-name
    2、表明:删除数据库
    drop database dbname
    3、说明:备份sql server
    --- 成立 备份数据的 device
    USE master
    EXEC sp_addumpdevice 'disk', 'testBack', 'c:mssql7backupMyNwind_1.dat'
    --- 开始 备份
    BACKUP DATABASE pubs TO testBack
    4、表明:成立新表
    create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
    依据已部分表成立新表:
    A:create table tab_new like tab_old (使用旧表创建新表)
    B:create table tab_new as select col1,col2… from tab_old definition only
    5、表达:删除新表
    drop table tabname
    6、表达:扩大一个列
    Alter table tabname add column col type
    注:列扩展后将不能去除。DB第22中学列加上后数据类型也不能够改动,独一能退换的是加多varchar类型的长度。
    7、表明:增加主键: Alter table tabname add primary key(col)
    评释:删除主键: Alter table tabname drop primary key(col)
    8、表达:创立索引:create [unique] index idxname on tabname(col….)
    删去索引:drop index idxname
    注:索引是不行改造的,想退换必得删除重新建。
    9、表明:创造视图:create view viewname as select statement
    去除视图:drop view viewname
    10、说明:多少个简单的基本的sql语句
    选择:select * from table1 where 范围
    插入:insert into table1(field1,field2) values(value1,value2)
    新葡亰496net,删除:delete from table1 where 范围
    更新:update table1 set field1=value1 where 范围
    查找:select * from table1 where 田野先生1 like ’%value1%’ ---like的语法很精美,查资料!
    排序:select * from table1 order by field1,field2 [desc]
    总数:select count as totalcount from table1
    求和:select sum(field1) as sumvalue from table1
    平均:select avg(field1) as avgvalue from table1
    最大:select max(field1) as maxvalue from table1
    最小:select min(field1) as minvalue from table1
    11、表明:多少个高等查询运算词
    A: UNION 运算符
    UNION 运算符通过整合别的七个结实表(比方 TABLE1 和 TABLE2)并消去表中别的重复行而派生出叁个结实表。当 ALL 随 UNION 一同利用时(即 UNION ALL),不排除重复行。三种景况下,派生表的每一行不是根源 TABLE1 正是根源 TABLE2。
    B: EXCEPT 运算符
    EXCEPT 运算符通过包涵持有在 TABLE1 中但不在 TABLE第22中学的行并化解全数重复行而派生出叁个结出表。当 ALL 随 EXCEPT 一起行使时 (EXCEPT ALL),不排除重复行。
    C: INTERSECT 运算符
    INTECR-VSECT 运算符通过只包蕴 TABLE1 和 TABLE2中都一些行并化解所有重复行而派生出三个结果表。当 ALL 随 INTE福特ExplorerSECT 一齐利用时 (INTE路虎极光SECT ALL),不免除重复行。
    注:使用运算词的多少个查询结果行必得是同等的。
    12、表达:使用外接连
    A、left outer join:
    左外连接(左连接):结果集几满含连接表的匹配行,也包罗左连接表的保有行。
    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
    B:right outer join:
    右外连接(右连接):结果集既包蕴连接表的相配连接行,也囊括右连接表的富有行。
    C:full outer join:
    全外连接:不仅仅满含符号连接表的匹配行,还满含三个延续表中的全数记录。

    3、压缩数据库 dbcc shrinkdatabase(dbname)

    CREATE DATABASE database-name
    

    二、提升

    4、转移数据库给新客户以已存在客户权限
    exec sp_change_users_login 'update_one','newname','oldname' go

     

    1、表明:复制表(只复制结构,源表名:a 新表名:b) (Access可用)
    法一:select * into b from a where 1<>1
    法二:select top 0 * into b from a

    5、检查备份集 RESTORE VE大切诺基IFYONLY from disk='E:dvbbs.bak'

    2、删除数据库

    2、表达:拷贝表(拷贝数据,源表名:a 指标表名:b) (Access可用)
    insert into b(a, b, c) select d,e,f from b;

    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

        

    3、表明:跨数据库之间表的正片(具体数量利用绝对路线) (Access可用)
    insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
    例子:..from b in '"&Server.MapPath(".")&"data.mdb" &"' where..

    7、日志清除
    SET NOCOUNT ON
    DECLARE @LogicalFileName sysname,
    @MaxMinutes INT,
    @NewSize INT

    drop database dbname
    

    4、说明:子查询(表名1:a 表名2:b)
    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)

    USE tablename -- 要操作的多寡库名
    SELECT @LogicalFileName = 'tablename_log', -- 日志文件名
    @MaxMinutes = 10, -- Limit on time allowed to wrap log.
    @NewSize = 1 -- 你想设定的日记文件的深浅(M)

     

    5、表明:展现文章、提交人和尾声回复时间
    select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b

    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)

    3、备份数据库

    6、表达:外接连查询(表名1:a 表名2:b)
    select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

    DECLARE @Counter INT,
    @StartTime DATETIME,
    @TruncLog VARCHAR(255)
    SELECT @StartTime = GETDATE(),
    @TruncLog = 'BACKUP LOG ' db_name() ' WITH TRUNCATE_ONLY'

    --- 创建 备份数据的 device
    
    use master
    
    
    exec sp_addumpdevice 'disk','testBack', 'c:mssql7backupMyNwind_1.dat'
    
    
    --- 开始 备份
    BACKUP DATABASE pubs TO testBack
    

    7、表明:在线视图查询(表名1:a )
    select * from (SELECT a,b,c FROM a) T where t.a > 1;

    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 = @Logical FileName)
    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

     

    8、表明:between的用法,between限制查询数据范围时包含了边界值,not between不包涵
    select * from table1 where time between time1 and time2
    select a,b,c, from table1 where a not between 数值1 and 数值2

    SELECT @Counter = @Counter 1
    END
    EXEC (@TruncLog)
    END
    ELECT '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

     

    9、表达:in 的运用方法
    select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)

    8、表明:改换某些表
    exec sp_changeobjectowner 'tablename','dbo'

    4、成立新表

    10、表明:两张关联表,删除主表中已经在副表中从未的新闻
    delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )

    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)

    create table tablename(col1 type1 [not null] [primary key],col2 type2 [not null],..)
    

    11、表明:四表联合检查难题:
    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 .....

    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

     

    12、表明:日程陈设超前五分钟提示
    SQL: select * from 日程布署 where datediff('minute',f开头时间,getdate())>5

    FETCH NEXT FROM curObject INTO @Name, @Owner
    END
    close curObject
    deallocate curObject
    GO

    基于已部分表创建新表:

    13、表明:一条sql 语句消除数据库分页
    select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段

    10、SQL SEENCOREVE锐界 中一贯循环写入数据
    declare @i int
    set @i=1
    while @i<30
    begin
    insert into test (userid) values(@i)
    set @i=@i 1
    end
    案例:
    有如下表,需要就裱中有所沒有及格的成績,在历次增長 0.1 的根底上,使他们恰恰及格:
    Name score
    Zhangshan 80
    Lishi 59
    Wangwu 50
    Songquan 69

    A:create table tab_new like tab_old (使用旧表创建新表)
    B:create table tab_new as select col1,col2… from tab_old definition only
    

    14、说明:前10条记录
    select top 10 * form table1 where 范围

    while((select min(score) from tb_table)<60)
    begin
    update tb_table set score =score*1.01
    where score<60
    if (select min(score) from tb_table)>60
    break
    else
    continue
    end

     

    15、表明:选择在每一组b值同样的数目中对应的a最大的记录的具备新闻(类似那样的用法可以用来论坛每月排名的榜单,每月热销产品解析,按学科战绩排名,等等.)
    select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)

    5、删除新表

    16、表明:包蕴具备在 TableA 中但不在 TableB和TableC 中的行并消除全部重复行而派生出二个结实表
    (select a from tableA ) except (select a from tableB) except (select a from tableC)

    drop table tabname 
    

    17、表达:随机收取10条数据
    select top 10 * from tablename order by newid()

     

    18、表达:随机接纳记录
    select newid()

    6、扩展多少个列

    19、表达:删除重复记录
    Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)

    Alter table tabname add column colname type
    

    20、表明:列出数据Curry存有的表名
    select name from sysobjects where type='U'

    注:列扩大后将不可能去除。DB第22中学列加上后数据类型也无法更动,唯一能改换的是扩张varchar类型的尺寸。

    21、表达:列出表里的持有的
    select name from syscolumns where id=object_id('TableName')

    7、加多主键

    22、表达:列示type、vender、pcs字段,以type字段排列,case能够低价地贯彻多种选拔,类似select 中的case。
    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
    来得结果:
    type vender pcs
    电脑 A 1
    电脑 A 1
    光盘 B 2
    光盘 A 2
    手机 B 3
    手机 C 3

    Alter table tabname add primary key(col) 
    

    23、表明:先河化表table1

    除去主键:

    TRUNCATE TABLE table1

    Alter table tabname drop primary key(col)
    

    24、说明:选择从10到15的记录
    select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc

     

    三、技巧

    8、成立索引

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

    create [unique] index idxname on tabname(col….) 
    

    “where 1=1” 是表示选择一切   “where 1=2”全部不选,
    如:
    if @strWhere !=''
    begin
    set @strSQL = 'select count(*) as Total from [' @tblName '] where ' @strWhere end
    else
    begin
    set @strSQL = 'select count(*) as Total from [' @tblName ']'
    end

     

    作者们能够一向写成
    set @strSQL = 'select count(*) as Total from [' @tblName '] where 1=1 安定 ' @strWhere
    2、收缩数据库
    --重新建立索引
    DBCC REINDEX
    DBCC INDEXDEFRAG
    --收缩数据和日志
    DBCC SHRINKDB
    DBCC SHRINKFILE

       删除索引

    3、压缩数据库
    dbcc shrinkdatabase(dbname)

    drop index idxname
    

    4、转移数据库给新客户以已存在客户权限
    exec sp_change_users_login 'update_one','newname','oldname'
    go

    注:索引是不可退换的,想改动必需删除重新建。

    5、检查备份集
    RESTORE VERIFYONLY from disk='E:dvbbs.bak'

    9、创造视图

    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

    create view viewname as select statement
    

    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)

    drop view viewname
    

    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

    10、多少个轻巧的中坚的sql语句

    8、表明:更换某些表
    exec sp_changeobjectowner 'tablename','dbo'

    选择:select * from table1 where 范围
    插入:insert into table1(field1,field2) values(value1,value2)
    删除:delete from table1 where 范围
    更新:update table1 set field1=value1 where 范围
    查找:select * from table1 where field1 like ’%value1%’ ---like的语法很精妙,查资料!
    排序:select * from table1 order by field1,field2 [desc]
    总数:select count as totalcount from table1
    求和:select sum(field1) as sumvalue from table1
    平均:select avg(field1) as avgvalue from table1
    最大:select max(field1) as maxvalue from table1
    最小:select min(field1) as minvalue from table1
    

    9、存款和储蓄更换全数表

     

    CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch
     @OldOwner as NVARCHAR(128),
     @NewOwner as NVARCHAR(128)
    AS

    11、多少个高档查询运算词

    DECLARE @Name   as NVARCHAR(128)
    DECLARE @Owner  as NVARCHAR(128)
    DECLARE @OwnerName  as NVARCHAR(128)

    A: UNION 运算符
    新葡亰496net精妙SQL语句收罗,常用优秀SQL语句大全。UNION 运算符通过结合别的三个结果表(举例 TABLE1 和 TABLE2)并消去表中别的重复行而派生出二个结出表。当 ALL 随 UNION 一同使用时(即 UNION ALL),不化解重复行。三种情况下,派生表的每一行不是缘于 TABLE1 正是缘于 TABLE2。
    B: EXCEPT 运算符
    EXCEPT 运算符通过包蕴具备在 TABLE1 中但不在 TABLE第22中学的行并消除全数重复行而派生出四个结果表。当 ALL 随 EXCEPT 一齐使用时 (EXCEPT ALL),不解决重复行。
    C: INTERSECT 运算符
    INTE福特ExplorerSECT 运算符通过只包含 TABLE1 和 TABLE2中都部分行并消除全部重复行而派生出二个结实表。当 ALL 随 INTEENCORESECT 一同行使时 (INTE索罗德SECT ALL),不清除重复行。
    注:使用运算词的多少个查询结果行必须是平等的。

    DECLARE curObject CURSOR FOR
     select 'Name'   = name,
      'Owner'   = user_name(uid)
     from sysobjects
     where user_name(uid)=@OldOwner
     order by name

    12、表明:使用外接连
    A、left outer join:
    左外连接(左连接):结果集几囊括连接表的相称行,也囊括左连接表的保有行。
    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
    B:right outer join:
    右外连接(右连接):结果集既包罗连接表的匹配连接行,也包括右连接表的具有行。
    C:full outer join:
    全外连接:不仅仅囊括符号连接表的相配行,还包罗三个三番五次表中的全体记录。

    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

    1、表明:复制表(只复制结构,源表名:a 新表名:b) (Access可用)
    法一:select * into b from a where 1<>1
    法二:select top 0 * into b from a

    close curObject
    deallocate curObject
    GO

    2、表达:拷贝表(拷贝数据,源表名:a 目的表名:b) (Access可用)
    insert into b(a, b, c) select d,e,f from b;

    10、SQL SEEscortVE大切诺基中向来循环写入数据
    declare @i int
    set @i=1
    while @i<30
    begin
       insert into test (userid) values(@i)
       set @i=@i 1
    end

    3、表达:跨数据库之间表的正片(具体数额利用相对路线) (Access可用)
    insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
    例子:..from b in '"&Server.MapPath(".")&"data.mdb" &"' where..

    4、说明:子查询(表名1:a 表名2:b)
    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)

    5、表明:呈现文章、提交人和最终回复时间
    select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b

    6、表明:外接连查询(表名1:a 表名2:b)
    select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

    7、表明:在线视图查询(表名1:a )
    select * from (SELECT a,b,c FROM a) T where t.a > 1;

    8、表达:between的用法,between限制查询数据范围时包罗了边界值,not between不包罗
    select * from table1 where time between time1 and time2
    select a,b,c, from table1 where a not between 数值1 and 数值2

    9、表达:in 的应用办法
    select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)

    10、表明:两张关联表,删除主表中曾在副表中从不的音信
    delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )

    11、表达:四表联查难题:
    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 .....

    12、表明:日程安顿提前五分钟提示
    SQL: select * from 日程安插 where datediff('minute',f开端时间,getdate())>5

    13、表达:一条sql 语句化解数据库分页
    select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段

    14、说明:前10条记录
    select top 10 * form table1 where 范围

    15、表达:选取在每一组b值同样的数目中对应的a最大的笔录的兼具新闻(类似那样的用法能够用于论坛每月排行的榜单,每月热销产品深入分析,按学科战绩排行,等等.)
    select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)

    16、表明:富含富有在 TableA 中但不在 TableB和TableC 中的行并化解全体重复行而派生出一个结实表
    (select a from tableA ) except (select a from tableB) except (select a from tableC)

    17、表达:随机抽出10条数据
    select top 10 * from tablename order by newid()

    18、表达:随机选取记录
    select newid()

    19、表明:删除重复记录
    Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)

    20、表达:列出数据Curry装有的表名
    select name from sysobjects where type='U'

    21、表明:列出表里的有所的
    select name from syscolumns where id=object_id('TableName')

    22、表达:列示type、vender、pcs字段,以type字段排列,case能够方便地贯彻多重选择,类似select 中的case。
    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
    展现结果:
    type vender pcs
    电脑 A 1
    电脑 A 1
    光盘 B 2
    光盘 A 2
    手机 B 3
    手机 C 3

    23、表达:伊始化表table1

    TRUNCATE TABLE table1

    24、说明:选择从10到15的记录
    select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc

    111111111

    三、技巧

    1、1=1,1=2的施用,在SQL语句组合时用的相当多

    “where 1=1” 是意味着选拔一切   “where 1=2”全部不选,
    如:
    if @strWhere !=''
    begin
    set @strSQL = 'select count(*) as Total from [' @tblName '] where ' @strWhere
    end
    else
    begin
    set @strSQL = 'select count(*) as Total from [' @tblName ']'
    end

    作者们能够平素写成
    set @strSQL = 'select count(*) as Total from [' @tblName '] where 1=1 安定 ' @strWhere

    2、裁减数据库
    --重新营造索引
    DBCC REINDEX
    DBCC INDEXDEFRAG
    --减弱数据和日志
    DBCC SHRINKDB
    DBCC SHRINKFILE

    3、压缩数据库
    dbcc shrinkdatabase(dbname)

    4、转移数据库给新客商以已存在客商权限
    exec sp_change_users_login 'update_one','newname','oldname'
    go

    5、检查备份集
    RESTORE VERIFYONLY from disk='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、表明:更动某些表
    exec sp_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、SQL SE奥迪Q5VE凯雷德中央直属机关接循环写入数据
    declare @i int
    set @i=1
    while @i<30
    begin
       insert into test (userid) values(@i)
       set @i=@i 1
    end

    本文由新葡亰496net发布于网络数据库,转载请注明出处:新葡亰496net精妙SQL语句收罗,常用优秀SQL语句大

    关键词:

上一篇:新葡亰496net:SQL复杂查询和视图

下一篇:没有了