请选择 进入手机版 | 继续访问电脑版
搜索
查看: 232|回复: 4

[数据库] 简单实用SQL脚本

[复制链接]
  • TA的每日心情
    郁闷
    2018-7-31 18:12
  • 签到天数: 5 天

    [LV.2]偶尔看看I

    发表于 2018-7-26 14:53:28 | 显示全部楼层 |阅读模式 <
    行列互转
    create table test(id int,name varchar(20),quarter int,profile int)
    insert into test values(1,'a',1,1000)
    insert into test values(1,'a',2,2000)
    insert into test values(1,'a',3,4000)
    insert into test values(1,'a',4,5000)
    insert into test values(2,'b',1,3000)
    insert into test values(2,'b',2,3500)
    insert into test values(2,'b',3,4200)
    insert into test values(2,'b',4,5500)
    select * from test

    --行转列
    select id,name,
    [1] as "一季度",
    [2] as "二季度",
    [3] as "三季度",
    [4] as "四季度",
    [5] as "5"
    from
    test
    pivot
    (
    sum(profile)
    for quarter in
    (
    [1],[2],[3],[4],[5])
    )
    as pvt


    create table test2(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int)
    insert into test2 values(1,'a',1000,2000,4000,5000)
    insert into test2 values(2,'b',3000,3500,4200,5500)
    select * from test2

    --列转行
    select id,name,quarter,profile
    from
    test2
    unpivot
    (
    profile
    for quarter in
    (
    [Q1],[Q2],[Q3],[Q4])
    )
    as unpvt


    sql替换字符串 substring replace
    --例子1
    update tbPersonalInfo set TrueName = replace(TrueName,substring(TrueName,2,4),'**') where ID = 1

    --例子2
    update tbPersonalInfo set Mobile = replace(Mobile,substring(Mobile,4,11),'********') where ID = 1

    --例子3
    update tbPersonalInfo set Email = replace(Email,'chinamobile','******') where ID = 1


    SQL查询一个表内相同纪录having
    如果一个ID可以区分的话,可以这么写
    select * from where ID in (
    select ID from group by ID having sum(1)>1)
    如果几个ID才能区分的话,可以这么写
    select * from where ID1 ID2 ID3 in
    (
    select ID1 ID2 ID3 from group by ID1,ID2,ID3 having sum(1)>1)
    其他回答:数据表是zy_bho,想找出ZYH字段名相同的记录
    --方法1   
    SELECT  *FROM  zy_bho  a  WHERE  EXISTS
      (
    SELECT  1  FROM  zy_bho  WHERE  [PK]  <>  a.[PK]  AND  ZYH  =  a.ZYH)
       
    --方法2
    select  a.*  from  zy_bho  a  join  zy_bho  b
          
    on  (a.[pk]<>b.[pk]  and  a.zyh=b.zyh)
       
    --方法3
    select  *  from  zy_bbo  where  zyh  in
    (
    select  zyh  from  zy_bbo  group  by  zyh  having  count(zyh)>1)
    --其中pk是主键或是 unique的字段。


    把多行SQL数据变成一条多列数据,即新增列
    Select
    DeptName
    =O.OUName,
    '9G'=Sum(Case When PersonalGrade=9 Then 1 Else 0 End),
    '8G'=Sum(Case When PersonalGrade=8 Then 1 Else 0 End),
    '7G4'=Sum(Case When PersonalGrade=7 AND JobGrade =4 Then 1 Else 0 End),
    '7G3'=Sum(Case When PersonalGrade=7 AND JobGrade =3 Then 1 Else 0 End),
    '6G'=Sum(Case When PersonalGrade=6 Then 1 Else 0 End),
    '5G3'=Sum(Case When PersonalGrade=5 AND JobGrade =3 Then 1 Else 0 End),
    '5G2'=Sum(Case When PersonalGrade=5 AND JobGrade =2 Then 1 Else 0 End),
    '4G'=Sum(Case When PersonalGrade=4 Then 1 Else 0 End),
    '3G2'=Sum(Case When PersonalGrade=3 AND JobGrade =2 Then 1 Else 0 End),
    '3G1'=Sum(Case When PersonalGrade=3 AND JobGrade =1 Then 1 Else 0 End),
    '2G'=Sum(Case When PersonalGrade=2 Then 1 Else 0 End),
    '1G'=Sum(Case When PersonalGrade=1 Then 1 Else 0 End),
    --' 未定级'=Sum(Case When PersonalGrade=NULL Then 1 Else 0 End)


    表复制
    insert into PhoneChange_Num ([IMSI],Num)
    SELECT [IMSI]
          ,
    count([IMEI]) as num
      
    FROM [Test].[dbo].[PhoneChange] group by [IMSI] order by num desc
    语法1Insert INTOtable(field1,field2,...) values(value1,value2,...)
    语法2Insert intoTable2(field1,field2,...) select value1,value2,... from Table1(要求目标表Table2必须存在,由于目标表Table2已经存在,所以我们除了插入源表Table1的字段外,还可以插入常量。)
    语法3SELECT vale1,value2 into Table2 from Table1(要求目标表Table2不存在,因为在插入时会自动创建表Table2,并将Table1中指定字段数据复制到Table2中。)
    语法4:使用导入导出功能进行全表复制。如果是使用【编写查询以指定要传输的数据】,那么在大数据表的复制就会有问题?因为复制到一定程度就不再动了,内存爆了?它也没有写入到表中。而使用上面3种语法直接执行是会马上刷新到数据库表中的,你刷新一下mdf文件就知道了。


    利用带关联子查询Update语句更新数据
    --方法1
    Update Table1 set c = (select c from Table2 where a = Table1.a) where c is null

    --方法2
    update  A
    set  newqiantity=B.qiantity
    from  A,B
    where  A.bnum=B.bnum

    --方法3
    update
        (
    select A.bnum ,A.newqiantity,B.qiantity from A left join B on A.bnum=B.bnum) AS C
    set C.newqiantity = C.qiantity
    where C.bnum =XX


    连接远程服务器
    --方法1
    select *  from openrowset('SQLOLEDB','server=192.168.0.67;uid=sa;pwd=password','SELECT * FROM BCM2.dbo.tbAppl')

    --方法2
    select *  from openrowset('SQLOLEDB','192.168.0.67';'sa';'password','SELECT * FROM BCM2.dbo.tbAppl')  
    Date Time 样式
      
    不带世纪数位 (yy) (1)
      
      
    带世纪数位 (yyyy)
      
      
    标准
      
      
    输入/输出 (3)
      
      
    -
      
      
    0 100 (1,2)
      
      
      
      
    mon dd yyyy  hh:miAM(或 PM
      
      
    1
      
      
    101
      
      
      
      
    mm/dd/yyyy
      
      
    2
      
      
    102
      
      
    ANSI
      
      
    yy.mm.dd
      
      
    3
      
      
    103
      
      
    /法国
      
      
    dd/mm/yyyy
      
      
    4
      
      
    104
      
      
      
      
    dd.mm.yy
      
      
    5
      
      
    105
      
      
    大利
      
      
    dd-mm-yy
      
      
    6
      
      
    106(1)
      
      
    -
      
      
    dd mon yy
      
      
    7
      
      
    107(1)
      
      
    -
      
      
    mon dd, yy
      
      
    8
      
      
    108
      
      
    -
      
      
    hh:mi:ss
      
      
    -
      
      
    9 109 (1,2)
      
      
    认设置 毫秒
      
      
    mon dd yyyy  hh:mi:ss:mmmAM(或 PM
      
      
    10
      
      
    110
      
      
      
      
    mm-dd-yy
      
      
    11
      
      
    111
      
      
      
      
    yy/mm/dd
      
      
    12
      
      
    112
      
      
    ISO
      
      
    yymmdd
      
    yyyymmdd
      
      
    -
      
      
    13 113 (1,2)
      
      
    洲默认设置 毫秒
      
      
    dd mon yyyy  hh:mi:ss:mmm(24h)
      
      
    14
      
      
    114
      
      
    -
      
      
    hh:mi:ss:mmm(24h)
      
      
    -
      
      
    20 120 (2)
      
      
    ODBC 规范
      
      
    yyyy-mm-dd  hh:mi:ss(24h)
      
      
    -
      
      
    21 121 (2)
      
      
    ODBC 规范(带毫秒)
      
      
    yyyy-mm-dd  hh:mi:ss.mmm(24h)
      
      
    -
      
      
    126 (4)
      
      
    ISO8601
      
      
    yyyy-  mm-ddThh:mi:ss.mmm(无空格)
      
      
    -
      
      
    127(6, 7)
      
      
    带时区 Z ISO8601
      
      
    yyyy-mm-ddThh:mi:ss.mmmZ
      
    (无 空格)
      
      
    -
      
      
    130 (1,2)
      
      
    回历 (5)
      
      
    dd mon yyyy  hh:mi:ss:mmmAM
      
      
    -
      
      
    131 (2)
      
      
    回历 (5)
      
      
    dd/mm/yy  hh:mi:ss:mmmAM
      
      
    --语句及查询结果:
    SELECT CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM
    SELECT CONVERT(varchar(100), GETDATE(), 1): 05/16/06
    SELECT CONVERT(varchar(100), GETDATE(), 2): 06.05.16
    SELECT CONVERT(varchar(100), GETDATE(), 3): 16/05/06
    SELECT CONVERT(varchar(100), GETDATE(), 4): 16.05.06
    SELECT CONVERT(varchar(100), GETDATE(), 5): 16-05-06
    SELECT CONVERT(varchar(100), GETDATE(), 6): 16 05 06
    SELECT CONVERT(varchar(100), GETDATE(), 7): 05 16, 06
    SELECT CONVERT(varchar(100), GETDATE(), 8): 10:57:46
    SELECT CONVERT(varchar(100), GETDATE(), 9): 05 16 2006 10:57:46:827AM
    SELECT CONVERT(varchar(100), GETDATE(), 10): 05-16-06
    SELECT CONVERT(varchar(100), GETDATE(), 11): 06/05/16
    SELECT CONVERT(varchar(100), GETDATE(), 12): 060516
    SELECT CONVERT(varchar(100), GETDATE(), 13): 16 05 2006 10:57:46:937
    SELECT CONVERT(varchar(100), GETDATE(), 14): 10:57:46:967
    SELECT CONVERT(varchar(100), GETDATE(), 20): 2006-05-16 10:57:47
    SELECT CONVERT(varchar(100), GETDATE(), 21): 2006-05-16 10:57:47.157
    SELECT CONVERT(varchar(100), GETDATE(), 22): 05/16/06 10:57:47 AM
    SELECT CONVERT(varchar(100), GETDATE(), 23): 2006-05-16
    SELECT CONVERT(varchar(100), GETDATE(), 24): 10:57:47
    SELECT CONVERT(varchar(100), GETDATE(), 25): 2006-05-16 10:57:47.250
    SELECT CONVERT(varchar(100), GETDATE(), 100): 05 16 2006 10:57AM
    SELECT CONVERT(varchar(100), GETDATE(), 101): 05/16/2006
    SELECT CONVERT(varchar(100), GETDATE(), 102): 2006.05.16
    SELECT CONVERT(varchar(100), GETDATE(), 103): 16/05/2006
    SELECT CONVERT(varchar(100), GETDATE(), 104): 16.05.2006
    SELECT CONVERT(varchar(100), GETDATE(), 105): 16-05-2006
    SELECT CONVERT(varchar(100), GETDATE(), 106): 16 05 2006
    SELECT CONVERT(varchar(100), GETDATE(), 107): 05 16, 2006
    SELECT CONVERT(varchar(100), GETDATE(), 108): 10:57:49
    SELECT CONVERT(varchar(100), GETDATE(), 109): 05 16 2006 10:57:49:437AM
    SELECT CONVERT(varchar(100), GETDATE(), 110): 05-16-2006
    SELECT CONVERT(varchar(100), GETDATE(), 111): 2006/05/16
    SELECT CONVERT(varchar(100), GETDATE(), 112): 20060516
    SELECT CONVERT(varchar(100), GETDATE(), 113): 16 05 2006 10:57:49:513
    SELECT CONVERT(varchar(100), GETDATE(), 114): 10:57:49:547
    SELECT CONVERT(varchar(100), GETDATE(), 120): 2006-05-16 10:57:49
    SELECT CONVERT(varchar(100), GETDATE(), 121): 2006
    对上面进行动态生成字符串:
    declare @sql1 nvarchar(200),@sql2 nvarchar(200)
    declare @count nvarchar(100);
    set @sql1 = 'SELECT CONVERT(varchar(100), GETDATE(), 0)'
    set @sql2 = 'SELECT @count = CONVERT(varchar(100), GETDATE(), 0)'
    exec sp_executesql  @sql2,N'@count nvarchar(50) out',@count out
    print @sql1 '' @count
    --SQL Server 仅保证往返转换(即从原始数据类型进行转换后又返回原始数据类型的转换)在各版本间产生相同值。
    DECLARE @myval decimal (5, 2)
    SET @myval = 193.57
    SELECT CAST(CAST(@myval AS varbinary(20)) AS decimal(10,5))
    -- Or, using CONVERT
    SELECT CONVERT(decimal(10,5), CONVERT(varbinary(20), @myval))
    --输出193.57000
    --
    输出193.57000
    --bigint数据类型的字段截取(其它类型也一样)
    select substring(CONVERT(varchar(15),字段名),11,9) from 表名
    select substring(cast(字段名 as varchar(50),6,9)) from 表名


    SQL中的相除
    --SQL中的相除
    SELECT CASE WHEN ISNULL(A B,0)<>0 THEN
    LTRIM(CONVERT(DEC(18,2),A*100.0/(A B))) '%'  ELSE '' END AS '百分数'
    FROM TB
    --百分比的不同格式
    select
    LTRIM(CONVERT(DEC(18,2),42*100.0/96)) '%' AS '百分数string' ,--DEC=decimal
    CONVERT(decimal(10,2),42*100.0/96) AS '百分数dec', --100 100.0是不一样的
    CONVERT(decimal(10,2),42*100/96) AS '没有保留到小数点'
    --from Tb
    order by 百分数dec desc
    --43.75%    43.75    43.00

    --方法二:
    Select (Convert(varchar(50),Round(42*100.0/96,3)) '%') as 百分比 --from A
    --43.750000%


    四舍五入
    /*ROUND ( numeric_expression , length [ ,function ] )
    function
    必须为 tinyintsmallint   int
    如果省略 function 或其值为 0(默认值),则将舍入 numeric_expression
    如果指定了0以外的值,则将截断 numeric_expression*/
    SELECT ROUND(150.45648, 2);--保留小数点后两位,需要四舍五入
    SELECT ROUND(150.45648, 2, 0);--保留小数点后两位,0为默认值,表示进行四舍五入
    SELECT ROUND(150.45648, 2, 1);--保留小数点后两位,不需要四舍五入,这里除0以外都是有同样的效果
    SELECT ROUND(150.45648, 2, 2);--保留小数点后两位,不需要四舍五入,这里除0以外都是有同样的效果
    --150.46000
    --150.45000
    --150.45000
    --150.45000
      

    对字段出现NULL值的处理
    --判断某些字段是否为空
    --case
    select case when '字段名' is null then '\N' else convert(varchar(20),'字段名') end as 'NewName'
    select case when null is null then '\N' else convert(varchar(20),null) end as 'NewName'

    --SQL Server 2005coalesce
    select coalesce('字符串类型字段','\N') as 'NewName'
    select coalesce(convert(varchar(20),'非字符串类型字段'),'\N') as 'NewName'
    select coalesce(convert(varchar(20),null),'\N') as 'NewName'

    --coalesce,返回其参数中的第一个非空表达式
    select Coalesce(null,null,1,2,null)union
    select Coalesce(null,11,12,13,null)union
    select Coalesce(111,112,113,114,null)


    count的几种情况
    --第一种
    select count(*) from tablename

    --第二种
    select count(ID) from tablename

    --第三种,1换成其它值也是可以的
    select count(1) from tablename

    /*
    --
    第四种,这个不存在性能问题
    idint
    ID(如果 indid = 0 255)。否则为索引所属表的ID
    Indid smallint
    索引ID
    0
    =表
    1
    =聚簇索引
    >1
    =非聚簇索引
    255
    =具有textimage数据的表条目。
    rows int
    基于indid=0 indid=1地数据级行数,该值对于indid>1重复。如果indid=255rows设置为0
    当表没有聚簇索引时,Indid = 0 否则为 1
    */

    select rows,indid from sysindexes where id = object_id('tablename') and indid in (0,1)


    Union all
    --把两个相同结构的表union后插入到一个新表中
    select *
    into table_now
    from table_1
    union all select * from table_2
    --Truncate table table_now


    查看数据库缓存的SQL
    --适用MSSQL2000MSSQL2005
    use master
    declare @dbid int
    Select @dbid = dbid from sysdatabases where name = 'Test'--修改成数据库的名称
    select
    dbid,UseCounts ,RefCounts,CacheObjtype,ObjType,
    DB_Name(dbid) as DatabaseName,SQL
    from syscacheobjects
    where dbid=@dbid
    order by dbid,useCounts desc,objtype


    删除计划缓存
    --删除整个数据库的计划缓存
    DBCC FREEPROCCACHE

    --删除某个数据库的计划缓存
    USE master
    DECLARE @dbid INT
    SELECT @dbid=dbid FROM sysdatabases WHERE NAME = '表名'
    DBCC FLUSHPROCINDB (@dbid)


    导出时加入特殊字符
    情况一:全部字段都需要加字符,在这里设置【文本限定符】就可以了。
    file:///C:/Users/ADMINI~1/AppData/Local/Temp/msohtmlclip1/01/clip_image004.gif
    --查看内存状态
    dbcc memorystatus

    --查看哪个引起的阻塞,blk
    EXEC sp_who active

    --查看锁住了那个资源idobjid
    EXEC sp_lock

    --当前查询分析器SPID
    --
    查询分析器的状态栏中可以看到
    --
    比如sa(57),这就表示当前查询分析器SPID57,这样在使用profile的时候就可以指定当前窗体进行监控
    --获取脚本的执行时间
    declare @timediff datetime
    select @timediff=getdate()
    select * from tablename
    print '1耗时:' convert(varchar(10),datediff(ms,@timediff,getdate()))

    该用户从未签到

    发表于 2018-8-5 00:08:21 | 显示全部楼层
    大家顶啊,这么好的帖子,楼主辛苦了

    回复

    使用道具 举报

    该用户从未签到

    发表于 2018-8-20 10:08:10 | 显示全部楼层
    艾特资源有你更精彩!
    回复

    使用道具 举报

    该用户从未签到

    发表于 2019-1-8 12:35:31 | 显示全部楼层
    天龙私服全套|奇迹Mu私服全套|魔兽私服全套|魔域私服全套|墨香私服全套
    天堂2私服全套|传奇3私服全套|英雄王座私服全套|千年私服全套|征途私服全套
    新魔界私服全套|骑士私服全套|烈焰私服全套|破天私服全套|决战私服全套
    美丽世界私服全套|乱勇OL私服全套|倚天2私服全套|完美世界私服全套|征服私服全套
    天堂私服全套|传世私服全套|真封神私服全套|劲舞团私服全套|天上碑私服全套
    永恒之塔私服全套|仙境RO私服全套|诛仙私服全套|神泣私服全套|石器私服全套
    冒险岛私服全套|惊天动地私服全套|热血江湖私服全套|问道私服全套|密传私服全套
    火线任务(Heat Project)私服全套|飞飞OL私服全套|洛汗私服全套|天之炼狱私服全套
    丝路传说私服全套|大话西游私服全套|蜀门私服全套|机战私服全套|剑侠情缘私服全套
    绝对女神私服全套|传说OL私服全套|刀剑私服全套|弹弹堂私服全套|科洛斯私服全套
    魔力宝贝私服全套|武林外传私服全套|网页游戏私服全套|页游私服全套|希望OL私服全套
    成吉思汗私服全套|剑侠世界私服全套|全民奇迹私服全套|挑战OL私服全套
    红月私服全套|十二之天(江湖OL)私服全套|倚天私服全套|dnf私服全套
    玩游戏不如开游戏,自己当GM卖游戏道具赚钱!游戏赚钱两不误的发家工具!
    最低几百就能成为暴利游戏老板!一切技术我们服务,一切辛苦我们扛住!
    开sf游戏就到(45ur.com)私服一条龙,成全您的暴富梦想!!!
    私服开区一条龙服务(网络游戏私服开服制作)-主机IDC租用-网站模版
    广告宣传-游戏版本-开区套餐
    官方网站45ur.com
    客服咨询QQ1207542352(企鹅扣扣)
    Email:1207542352@qq.com
    45ur.com服务端商业版本下载地址(已修复不能下载问题):
    45ur.com/soft/index.html
    魔兽sf一条龙服务端_45ur.com 魔域私服一条龙服务端_45ur.com 完美世界开服一条龙制作_45ur.com












    墨香私服一条龙 决战sf一条龙 永恒之塔开服一条龙
    回复

    使用道具 举报

    该用户从未签到

    发表于 2019-2-22 08:23:40 | 显示全部楼层
    谢谢楼主,真是太好了












    抖音点赞
    刷赞软件
    免费刷赞
    代刷网
    qq代刷网
    QQ代刷网
    刷赞
    代刷网
    名片赞
    刷赞网站
    qq名片赞
    刷赞平台
    QQ代刷网
    代刷
    卡盟平台
    刷qq名片赞
    qq说说刷赞
    qq刷赞软件
    qq刷赞
    刷赞平台
    回复

    使用道具 举报

    您需要登录后才可以回帖 登录 | 立即注册

    本版积分规则

    艾特资源分享

    艾特资源分享,一个分享各种资源的部落,提供优质互联网资源!

    扫描并关注

    联系我们

    • QQ:2129911300
    • Email:gly@it155.cn

    申请友链|Archiver|手机版|艾特资源分享 ( 粤ICP备18077912号 )

    Powered by Discuz! X3.4 © 2001-2013 Comsenz Inc.

    快速回复 返回顶部 返回列表