详解与实例,SQL操作收集

  在做报表的时候,平常会遇上要把询问出的数据做为列头呈现,可是查询出来的数据会依照法则的差别查询出的数目也就不一致(动态列),那么列头也不一致。

1.行列转变

   本文的源地址:  
    [1]

本条时候就须求把询问出多少的行转变到列。

原表(这里其实能够是个视图V_AnswerList)的数据结构,如下图所示:

**    [2]

 图片 1Q78ZU{_DKEQ`CHLH1ND.png)

图片 2

    [3

  一、将查询出来的数额转变到列头

编写制定期存款款和储蓄进程:

**

  declare @Str varchar(500);
  Create Table #t_Data(ID int identity(1,1),DeptID int,DeptName varchar(50),TotalAmountYS money,TotalAmountZY             money,TotalAmountZX money);      
      Set @str=(Select Stuff((SELECT ',' ltrim(a.YWTypeName) ' money'
      From
      (select YWTypeName,GKYWTypeID from tbgYearSourceGKSplitList where SplitYears=2016 group by YWTypeName,GKYWTypeID)
      a
  For XML  Path('')),1,1,''));
  Set @str='Alter Table #t_Data Add ' @str; 
  Exec(@Str);
 select * from #t_Data

CREATE PROCEDURE [dbo].[pro_Sum_1]
AS
BEGIN
DECLARE @sql varchar(8000)
SET              @sql = 'select d_Name as 单位,s_Name as 专门的职业职员'
                          SELECT     @sql = @sql   ', (case q_No when '''   q_No   ''' then a_Answer else '''   ''   ''' end) ['   q_No   ']'
                           FROM         (SELECT DISTINCT q_No
                                                  FROM          V_AnswerList) AS V_AnswerList
                                                      SELECT     @sql = @sql   'from V_AnswerList'  EXEC (@sql)
END

    1 只复制七个表结构,不复制数据

二、这里的列是华语的,你能够凑合成匈牙利(Magyarország)语的,就看你想要什么名字了。

 

    select top 0 * into [t1] from [t2]

 图片 3

落到实处后的职能:

    2 到手数据库中有个别对象的创导脚本

三、这里是用的是有的时候表查询数据,那么数据库查询出的数据肯定要加到不常表中,方法是用游标。

图片 4

    (1) 先用上面包车型大巴本子创设贰个函数

 

图片 5if exists(select 1 from sysobjects where id=object_id('fgetscript') and objectproperty(id,'IsInlineFunction')=0)
图片 6  drop function fgetscript
图片 7go
图片 8
图片 9create function fgetscript(
图片 10@servername varchar(50)     --服务器名
图片 11 ,@userid varchar(50)='sa'    --顾客名,假诺为nt验证格局,则为空
图片 12 ,@password varchar(50)=''    --密码
图片 13 ,@databasename varchar(50)    --数据库名称
图片 14 ,@objectname varchar(250)    --对象名
图片 15) returns varchar(8000)
图片 16as
图片 17begin
图片 18 declare @re varchar(八千)        --重临脚本
图片 19 declare @srvid int,@dbsid int       --定义服务器、数据库集id
图片 20 declare @dbid int,@tbid int        --数据库、表id
图片 21 declare @err int,@src varchar(255), @desc varchar(255) --错误管理变量
图片 22
图片 23--创建sqldmo对象
图片 24 exec @err=sp_oacreate 'sqldmo.sqlserver',@srvid output
图片 25 if @err<>0 goto lberr
图片 26
图片 27--连接服务器
图片 28 if isnull(@userid,'')='' --要是是 Nt验证格局
图片 29 begin
图片 30  exec @err=sp_oasetproperty @srvid,'loginsecure',1
图片 31  if @err<>0 goto lberr
图片 32
图片 33  exec @err=sp_oamethod @srvid,'connect',null,@servername
图片 34 end
图片 35 else
图片 36  exec @err=sp_oamethod @srvid,'connect',null,@servername,@userid,@password
图片 37
图片 38 if @err<>0 goto lberr
图片 39
图片 40--得到数据库集
图片 41 exec @err=sp_oagetproperty @srvid,'databases',@dbsid output
图片 42 if @err<>0 goto lberr
图片 43
图片 44--获得要收获脚本的数据库id
图片 45 exec @err=sp_oamethod @dbsid,'item',@dbid output,@databasename
图片 46 if @err<>0 goto lberr
图片 47
图片 48--拿到要获得脚本的对象id
图片 49 exec @err=sp_oamethod @dbid,'getobjectbyname',@tbid output,@objectname
图片 50 if @err<>0 goto lberr
图片 51
图片 52--获得脚本
图片 53 exec @err=sp_oamethod @tbid,'script',@re output
图片 54 if @err<>0 goto lberr
图片 55
图片 56 --print @re
图片 57 return(@re)
图片 58
图片 59lberr:
图片 60 exec sp_oageterrorinfo NULL, @src out, @desc out 
图片 61 declare @errb varbinary(4)
图片 62 set @errb=cast(@err as varbinary(4))
图片 63 exec master..xp_varbintohexstr @errb,@re out
图片 64 set @re='错误号: ' @re
图片 65    char(13) '错误源: ' @src
图片 66    char(13) '错误描述: ' @desc
图片 67 return(@re)
图片 68end
图片 69go
图片 70

 

此处因为a_Answer为字符串类型,笔者不笑得使用非常聚合函数将其总是;纵然a_Answer为int 型,在存款和储蓄进程中你可以如此Sum(case q_No when ....)。

    (2)用法如下:

别的二个带参数的积累进程:

图片 71print dbo.fgetscript('服务器名','顾客名','密码','数据库名','表名或任何对象名')

供参考:

    (3)假诺要拿走Curry全数指标的本子,如如下情势

CREATE PROCEDURE [dbo].[pro_Sum_2]
(
@s_No varchar(20)
)
AS
BEGIN

图片 72declare @name varchar(250)
图片 73declare #aa cursor for
图片 74select name from sysobjects where xtype not in('S','PK','D','X','L')
图片 75open #aa
图片 76fetch next from #aa into @name
图片 77while @@fetch_status=0
图片 78begin
图片 79 print dbo.fgetscript('onlytiancai','sa','sa','database',@name)
图片 80 fetch next from #aa into @name
图片 81end
图片 82close #aa
图片 83deallocate #aa
图片 84

DECLARE @s_No_1 varchar(20);
DECLARE @sql varchar(8000);

 **   3 分隔字符串

set
@s_No_1 = @s_No;
SET
@sql = 'select d_Name as 单位,s_Name as 职业人士'
                          SELECT     @sql = @sql   ', (case q_No when '''   q_No   ''' then a_Answer else '''   ''   ''' end) ['   q_No   ']'
                           FROM         (SELECT DISTINCT q_No
                                                  FROM          V_AnswerList) AS V_AnswerList
                                                      SELECT     @sql = @sql   'from V_AnswerList where s_No='   @s_No_1
EXEC (@sql)

   ** 即便有一个用逗号分割开的字符串,比方说"a,b,c,d,1,2,3,4",怎样用t-sql获取这几个字符串有多少个成分,获取第几个因素的值是某个啊?因为t-sql里不曾split函数,也绝非数组的定义,所以不得不自己写几个函数了。

END

    (1) 获取成分个数的函数

 

 

1.--行列调换 另例
原表:   姓名     科目   成绩
           张三     语文    80
           张三     数学    90
           张三     物理    85
           李四     语文    85
           李四     物理    82
           李四     英语    90
           李四     政治    70
           王五     英语    90

图片 85create function getstrarrlength (@str varchar(8000))
图片 86returns int
图片 87as
图片 88begin
图片 89  declare @int_return int
图片 90  declare @start int
图片 91  declare @next int
图片 92  declare @location int
图片 93  select @str =','  @str  ','
图片 94  select @str=replace(@str,',,',',')
图片 95  select @start =1
图片 96  select @next =1 
图片 97  select @location = charindex(',',@str,@start)
图片 98  while (@location <>0)
图片 99  begin
图片 100    select @start = @location  1
图片 101    select @location = charindex(',',@str,@start)
图片 102    select @next =@next  1
图片 103  end
图片 104 select @int_return = @next-2
图片 105 return @int_return
图片 106end
图片 107

改变后的表:  姓名       数学    物理     加泰罗尼亚语    语文    政治 
                       李四         0         82        90      85       70
                       王五         0          0         90       0         0
                       张三        90        85         0       80        0

    (2) 获取钦赐索引的值的函数

实例:
create table cj  --创建表cj
(
    ID       Int IDENTITY (1,1)     not null, --创设列ID,并且每回新增加一条记下就能够加1
    Name     Varchar(50),  
    Subject  Varchar(50),
    Result   Int, 
    primary key (ID)      --定义ID为表cj的主键     
);
--Truncate table cj
--Select * from cj
Insert into cj
Select '张三','语文',80 union all
Select '张三','数学',90 union all
Select '张三','物理',85 union all
Select '李四','语文',85 union all
Select '李四','物理',82 union all
Select '李四','英语',90 union all
Select '李四','政治',70 union all
Select '王五','英语',90
--行列调换
Declare @sql varchar(8000)
Set @sql = 'Select Name as 姓名'
Select @sql = @sql ',sum(case Subject when ''' Subject ''' then Result else 0 end) [' Subject ']'
from (select distinct Subject from cj) as cj  --把具有独一的学科的称呼都列举出来
Select @sql = @sql ' from cj group by name'
Exec (@sql)

 

  1. 队列调换--合併
    原表:   班级    学号    
                1          1  
                1          2
                1          3
                2          1
                2          2
                3          1
    改造后的表:  班级  学号           
                           1   1,2,3
                           2   1,2
                           3   1  

图片 108create function getstrofindex (@str varchar(8000),@index int =0)
图片 109returns varchar(8000)
图片 110as
图片 111begin
图片 112  declare @str_return varchar(8000)
图片 113  declare @start int
图片 114  declare @next int
图片 115  declare @location int
图片 116  select @start =1
图片 117  select @next =1 --就算习贯从0开端则select @next =0
图片 118  select @location = charindex(',',@str,@start)
图片 119  while (@location <>0 and @index > @next )
图片 120  begin
图片 121    select @start = @location  1
图片 122    select @location = charindex(',',@str,@start)
图片 123    select @next =@next  1
图片 124  end
图片 125  if @location =0 select @location =len(@str) 1 --借使是因为未有逗号退出,则以为逗号在字符串后
图片 126  select @str_return = substring(@str,@start,@location -@start) --@start料定是逗号之后的职位依旧就是初步值1
图片 127  if (@index <> @next ) select @str_return = '' --若是两个不等于,则是因为逗号太少,可能@index小于@next的开头值1。
图片 128  return @str_return
图片 129end
图片 130

实例:
Create table ClassNo  --创建表ClassNo
(
    ID Int IDENTITY(1,1)  not null,  --创制列ID,何况每回新扩充一条记下就能够加1
    Class  Varchar(50),    --班级列
    Number Varchar(50),    --学号列
    Primary Key(ID)        --定义ID为表ClassNo的主键
);
--Truncate Table ClassNo
--Select * from ClassNo
Insert Into ClassNo
Select 1,1 Union all
Select 1,2 Union all
Select 1,3 Union all
Select 2,1 Union all
Select 2,2 Union all
Select 3,1

    (3) 用法如下:

始建一个统一的函数
--Drop Function KFReturn
Create Function KFReturn(@Class Varchar(50))
Returns Varchar(8000)
as
Begin
Declare @str Varchar(8000)
Set @str = ''
Select @str = @str cast(Number as Varchar(50))  ',' from ClassNo Where Class = @Class
Set @str = SubString(@str,1,len(@str)-1)
Return(@str)
End

图片 131SELECT [dbo].[getstrarrlength]('1,2,3,4,a,b,c,d')
图片 132SELECT [dbo].[getstrofindex]('1,2,3,4,a,b,c,d',5)

--调用自定义函数获得结果
Select Distinct Class,dbo.KFReturn(Class) From ClassNo

    4  一条语句推行高出若干个数据库
    自家要在一条语句里操作差别的服务器上的两样的数据库里的两样的表,如何做呢?
    方法一:

3:列转行
--Drop Table ColumnToRow
Create table ColumnToRow
(
   ID Int IDENTITY(1,1)  not null,  --创造列ID,并且每一回新扩充一条记下就能加1
   a  int,
   b  int,
   c  int,
   d  int,
   e  int,
   f  int,
   g  int,
   h  int,
   Primary Key(ID)        --定义ID为表ColumnToRow的主键     
);
--Truncate Table ColumnToRow
--Select * from ColumnToRow
Insert Into ColumnToRow
Select 15,9,1,0,1,2,4,2 Union all
Select 22,34,44,5,6,7,8,7 Union all
Select 33,44,55,66,77,88,99,12

图片 133select * from OPENDATASOURCE('SQLOLEDB','Data Source=远程ip;User ID=sa;Password=密码').库名.dbo.表名

Declare @sql Varchar(8000)
Set @sql = ''
Select @sql = @sql rtrim(name) ' from ColumnToRow union all Select ' from SysColumns Where id = object_id('ColumnToRow')
Set @sql = SubString(@sql,1,len(@sql)-70)
--70的长度正是其一字符串'from ColumnToRow union all Select ID from ColumnToRow union all Select ',因为它会把ID这一列的值也算进去,所以要把它截掉
Exec ('Select ' @sql ' from ColumnToRow')

    方法二:
    先使用联结服务器:

  1. 怎样获取三个数据表的兼具列名
    办法如下:先从sysobjects系统表中获得数据表的systemid,然后再syscolumns表中得到该数据表的具备列名。
    SQL语句如下:
    Declare @objid int,@objname char(40)
    set @objname = 'ColumnToRow'
    --第1种方法
    select @objid = id from sysobjects where id = object_id(@objname)
    select 'Column_name' = name from syscolumns where id = @objid order by colid
    --或也足以写成
    select name as 'Column_name' from syscolumns where id = @objid order by colid
    --第2种方法:
    Select name as 'Column_Name' from SysColumns where id = object_id(@objname) Order by colid

图片 134EXEC sp_addlinkedserver '别名','','MSDASQL',NULL,NULL,'DRIVER={SQL Server};SERVER=远程名;UID=用户;PWD=密码;'
图片 135exec sp_addlinkedsrvlogin  @rmtsrvname='别名',@useself='false',@locallogin='sa',@rmtuser='sa',@rmtpassword='密码'
图片 136GO
图片 137

 

    然后:

  1. 透过SQL语句来更改客商的密码
    修改外人的,要求sysadmin role
    Exec Sp_password '原始密码','改变后密码','账号'
    Exec sp_password null,ok,sa

图片 138select * from 别名.库名.dbo.表名
图片 139insert 库名.dbo.表名 select * from 别名.库名.dbo.表名
图片 140select * into 库名.dbo.新表名 from 别名.库名.dbo.表名
图片 141go

 

    5 获取多个表中全数的字段音讯

  1. 怎么决断出多少个表的怎样字段不允许为空?
    Declare @objname Varchar(50)
    set @objname = 'ColumnToRow'
    Select Column_Name from information_schema.Columns where is_nullable = 'No' and Table_Name = @objname

    先创制两个视图

 

图片 142Create view fielddesc    
图片 143as
图片 144select o.name as table_name,c.name as field_name,t.name as type,c.length as 
图片 145
图片 146length,c.isnullable as isnullable,convert(varchar(30),p.value) as desp 
图片 147from syscolumns c  
图片 148join systypes t on c.xtype = t.xusertype
图片 149join sysobjects o on o.id=c.id 
图片 150left join    sysproperties p on p.smallid=c.colid and p.id=o.id    
图片 151where o.xtype='U'

7. 如何在数据Curry找到含有同样字段的表?
a. 查已知列名的景观
Select a.name as Columnname,b.name as tablename from SysColumns a inner join sysobjects b on a.id = b.id
and b.type = 'U' and a.name = '您要探索的字段名'
b. 未知列名查全数在不相同表出现过的列名
Select s.name as tablename,s1.name as columnname from SysColumns s1,Sysobjects s
Where s1.id = s.id and s.Type = 'U' and Exists (Select 1 from syscolumns s2 where s1.name = s2.name and s1.id <> s2.id)

 

 

    查询时:

8.查询第N行数据
假设id是主键:
select *
from (select top N * from 表) aa
where not exists(select 1 from (select top N-1 * from 表) bb where aa.id=bb.id)

图片 152Select * from fielddesc where table_name = '你的表名' 

 

 

  1. SQL Server日期总括
    a. 一个月的首后天
    SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
    b. 本周的星期三
    SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
    c. 一年的率后天
    SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
    d. 季度的首后天
    SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
    e. 方今的尾声一天
    SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
    f. 二〇一八年的终极一天
    SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
    g. 前段时期的末尾一天
    SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate()) 1, 0))
    h. 前段日子的首先个星期二
    select DATEADD(wk, DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0)
    i. 下季度的最终一天
    SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()) 1, 0))

    还应该有个越来越强的言辞,是邹建写的,也写出来吗

图片 153SELECT 
图片 154 (case when a.colorder=1 then d.name else '' end) N'表名',
图片 155 a.colorder N'字段序号',
图片 156 a.name N'字段名',
图片 157 (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'标识',
图片 158 (case when (SELECT count(*)
图片 159 FROM sysobjects
图片 160 WHERE (name in
图片 161           (SELECT name
图片 162          FROM sysindexes
图片 163          WHERE (id = a.id) AND (indid in
图片 164                    (SELECT indid
图片 165                   FROM sysindexkeys
图片 166                   WHERE (id = a.id) AND (colid in
图片 167                             (SELECT colid
图片 168                            FROM syscolumns
图片 169                            WHERE (id = a.id) AND (name = a.name))))))) AND
图片 170        (xtype = 'PK'))>0 then '√' else '' end) N'主键',
图片 171 b.name N'类型',
图片 172 a.length N'占用字节数',
图片 173 COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',
图片 174 isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',
图片 175 (case when a.isnullable=1 then '√'else '' end) N'允许空',
图片 176 isnull(e.text,'') N'默认值',
图片 177 isnull(g.[value],'') AS N'字段表明'
图片 178--into ##tx
图片 179
图片 180FROM  syscolumns  a left join systypes b 
图片 181on  a.xtype=b.xusertype
图片 182inner join sysobjects d 
图片 183on a.id=d.id  and  d.xtype='U' and  d.name<>'dtproperties'
图片 184left join syscomments e
图片 185on a.cdefault=e.id
图片 186left join sysproperties g
图片 187on a.id=g.id AND a.colid = g.smallid  
图片 188order by object_name(a.id),a.colorder
图片 189
图片 190

    6  时间格式转变难点

    因为新开荒的软件供给用一些旧软件生成的有的数量,在时光格式上不统一,只可以手工业调换,研究了一晌午写了三条语句,在此从前没怎么用过convert函数和case语句,还应该有" "操作符在不一致上下文景况也会起到差异的功用,把作者搞晕了要,但是以往看来是大半弄好了。

    (1) 把具备"70.07.06"那样的值变成"一九六八-07-06"

图片 191UPDATE lvshi
图片 192SET shengri = '19'   REPLACE(shengri, '.', '-')
图片 193WHERE (zhiyezheng = '139770070153')

 

    (2)在"1970-07-06"里提取"70","07","06"

图片 194SELECT SUBSTRING(shengri, 3, 2) AS year, SUBSTRING(shengri, 6, 2) AS month, 
图片 195      SUBSTRING(shengri, 9, 2) AS day
图片 196FROM lvshi
图片 197WHERE (zhiyezheng = '139770070153')

 

    (3)把叁个时光项目字段调换来"一九六六-07-06"  

图片 198UPDATE lvshi
图片 199SET shenling = CONVERT(varchar(4), YEAR(shenling)) 
图片 200        '-'   CASE WHEN LEN(MONTH(shenling)) = 1 THEN '0'   CONVERT(varchar(2), 
图片 201      month(shenling)) ELSE CONVERT(varchar(2), month(shenling)) 
图片 202      END   '-'   CASE WHEN LEN(day(shenling)) = 1 THEN '0'   CONVERT(char(2), 
图片 203      day(shenling)) ELSE CONVERT(varchar(2), day(shenling)) END
图片 204WHERE (zhiyezheng = '139770070153')

 

**    7 分区视图

**    分区视图是升高查询质量的一个很好的格局

 

图片 205--看上面包车型客车身体力行
图片 206
图片 207--示例表
图片 208create table tempdb.dbo.t_10(
图片 209id int primary key check(id between 1 and 10),name varchar(10))
图片 210
图片 211create table pubs.dbo.t_20(
图片 212id int primary key check(id between 11 and 20),name varchar(10))
图片 213
图片 214create table northwind.dbo.t_30(
图片 215id int primary key check(id between 21 and 30),name varchar(10))
图片 216go
图片 217
图片 218--分区视图
图片 219create view v_t
图片 220as
图片 221select * from tempdb.dbo.t_10
图片 222union all
图片 223select * from pubs.dbo.t_20
图片 224union all
图片 225select * from northwind.dbo.t_30
图片 226go
图片 227
图片 228--插入数据
图片 229insert v_t select 1 ,'aa'
图片 230union  all select 2 ,'bb'
图片 231union  all select 11,'cc'
图片 232union  all select 12,'dd'
图片 233union  all select 21,'ee'
图片 234union  all select 22,'ff'
图片 235
图片 236--更新数据
图片 237update v_t set name=name '_更新' where right(id,1)=1
图片 238
图片 239--删除测验
图片 240delete from v_t where right(id,1)=2
图片 241
图片 242--展现结果
图片 243select * from v_t
图片 244go
图片 245
图片 246--删除测验
图片 247drop table northwind.dbo.t_30,pubs.dbo.t_20,tempdb.dbo.t_10
图片 248drop view v_t
图片 249
图片 250图片 251/**//**//**//*--测量检验结果
图片 252
图片 253id          name       
图片 254----------- ---------- 
图片 2551           aa_更新
图片 25611          cc_更新
图片 25721          ee_更新
图片 258
图片 259(所影响的行数为 3 行)
图片 260==*/

 

**    8 树型的兑现

** 

图片 261
图片 262--参考
图片 263
图片 264--树形数据查询示例
图片 265--作者: 邹建
图片 266
图片 267--示例数据
图片 268create table [tb]([id] int identity(1,1),[pid] int,name varchar(20))
图片 269insert [tb] select 0,'中国'
图片 270union  all  select 0,'美国'
图片 271union  all  select 0,'加拿大'
图片 272union  all  select 1,'北京'
图片 273union  all  select 1,'上海'
图片 274union  all  select 1,'江苏'
图片 275union  all  select 6,'苏州'
图片 276union  all  select 7,'常熟'
图片 277union  all  select 6,'南京'
图片 278union  all  select 6,'无锡'
图片 279union  all  select 2,'纽约'
图片 280union  all  select 2,'旧金山'
图片 281go
图片 282
图片 283--查询内定id的全数子
图片 284create function f_cid(
图片 285@id int
图片 286)returns @re table([id] int,[level] int)
图片 287as
图片 288begin
图片 289 declare @l int
图片 290 set @l=0
图片 291 insert @re select @id,@l
图片 292 while @@rowcount>0
图片 293 begin
图片 294  set @l=@l 1
图片 295  insert @re select a.[id],@l
图片 296  from [tb] a,@re b
图片 297  where a.[pid]=b.[id] and b.[level]=@l-1
图片 298 end
图片 299图片 300/**//**//**//**//**//**//**//*--如若只体现最细心的子(下边未有子),则拉长那几个删除
图片 301 delete a from @re a
图片 302 where exists(
图片 303  select 1 from [tb] where [pid]=a.[id])
图片 304--*/
图片 305 return
图片 306end
图片 307go
图片 308
图片 309--调用(查询全数的子)
图片 310select a.*,层次=b.[level] from [tb] a,f_cid(2)b where a.[id]=b.[id]
图片 311go
图片 312
图片 313--删除测量试验
图片 314drop table [tb]
图片 315drop function f_cid
图片 316go
图片 317
图片 318

 

    9 排序难点

 数据库里有1,2,3,4,5 共5条记下,要用一条sql语句让其排序,使它排列成4,5,1,2,3,怎么写?

图片 319CREATE TABLE [t] (
图片 320 [id] [int] IDENTITY (1, 1) NOT NULL ,
图片 321 [GUID] [uniqueidentifier] NULL 
图片 322) ON [PRIMARY]
图片 323GO

    上边那句实行5次

图片 324insert t values (newid())

 

    翻开实行结果

图片 325select * from t

    (1) 第一种

图片 326select * from t
图片 327 order by case id when 4 then 1
图片 328                  when 5 then 2
图片 329                  when 1 then 3
图片 330                  when 2 then 4
图片 331                  when 3 then 5 end

    (2) 第二种

图片 332select * from t order by (id 2)%6

    (3) 第三种

图片 333select * from t order by charindex(cast(id as varchar),'45123')

    (4) 第四种

图片 334select * from t
图片 335WHERE id between 0 and 5
图片 336order by charindex(cast(id as varchar),'45123')

    (5) 第五种

图片 337select * from t order by case when id >3 then id-5 else id end

    (6) 第六种

图片 338select * from t order by id / 4 desc,id asc

    10 一条语句删除一群记录

    先是id列是int标志类类型,然后删除ID值为5,6,8,9,10,11的列,这里的cast函数不能够用convert函数替代,并且转移的项目必得是varchar,而无法是char,不然就能奉行出您不期望的结果,这里的"5,6,8,9,10,11"能够是您在页面上收获的二个chkboxlist创设形成的值,然后用下边包车型地铁一句就全数删减了,比循环用多条语句高效吧应该。

图片 339delete from [fujian] where charindex(',' cast([id] as varchar) ',',',' '5,6,8,9,10,11,' ',')>0

    再有一种正是

图片 340delete from table1 where id in(1,2,3,4 )

    11 获取子表内的一列数据的构成字符串

    上面那些函数获取05年已经登记了的某部所的辩白律师,唯一二个参数正是事务所的名号,然后回到zhuce字段里包涵05字样的有着律师。 

图片 341CREATE   FUNCTION fn_Get05LvshiNameBySuo  (@p_suo Nvarchar(50))
图片 342RETURNS Nvarchar(2000)
图片 343AS
图片 344BEGIN  
图片 345 DECLARE @LvshiNames varchar(2000), @name varchar(50)
图片 346 select @LvshiNames=''
图片 347 DECLARE lvshi_cursor CURSOR FOR

    12 让0变成1,1变成0 

图片 348declare @a int
图片 349set @a =0 --初始为0
图片 350select @a
图片 351set @a = @a^1 --把0变成1
图片 352select @a
图片 353set @a = @a^1 --把1变成0
图片 354select @a

     13  二种方法取表里n到m条纪录
    (1) 第一种** **

图片 355 若果tablename里未有另外identity列,那么:

 

图片 356select top m * into 有时表(或表变量) from tablename order by columnname -- 将top m笔插入
图片 357**set rowcount n
图片 358select * from 表变量 order by columnname desc**

   (2) 第二种 

 

图片 359select top n * from (select top m * from tablename order by columnname) a order by columnname desc

   (3) 第三种

图片 360 select identity(int) id0,* into #temp from tablename

图片 361 取n到m条的话语为:

图片 362 select * from #temp where id0 >=n and id0 <= m

图片 363 倘若你在实践 select identity(int) id0,* into #temp from tablename那条语句的时候报错,那是因为你的DB中间的select into/bulkcopy属性未有张开要先试行:
图片 364 exec sp_dboption 你的DB名字,'select into/bulkcopy',true

**图片 365  **(4) 第四种
图片 366
 一旦表里有identity属性,那么简单:图片 367 **

图片 368select * from tablename where identitycol between n and m 

图片 369  14 快速得到表test的笔录总量

图片 370select rows from sysindexes where id = object_id('test') and indid in (0,1)

图片 371 ** **15 提取数据库内全数表的字段详细表达的SQL语句

图片 372SELECT 
图片 373(case when a.colorder=1 then d.name else '' end) N'表名', 
图片 374a.colorder N'字段序号', 
图片 375a.name N'字段名', 
图片 376(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' 
图片 377end) N'标识', 
图片 378(case when (SELECT count(*) 
图片 379FROM sysobjects 
图片 380WHERE (name in 
图片 381(SELECT name 
图片 382FROM sysindexes 
图片 383WHERE (id = a.id) AND (indid in 
图片 384(SELECT indid 
图片 385FROM sysindexkeys 
图片 386WHERE (id = a.id) AND (colid in 
图片 387(SELECT colid 
图片 388FROM syscolumns 
图片 389WHERE (id = a.id) AND (name = a.name))))))) AND 
图片 390(xtype = 'PK'))>0 then '√' else '' end) N'主键', 
图片 391b.name N'类型', 
图片 392a.length N'占用字节数', 
图片 393COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度', 
图片 394isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数', 
图片 395(case when a.isnullable=1 then '√'else '' end) N'允许空', 
图片 396isnull(e.text,'') N'默认值', 
图片 397isnull(g.[value],'') AS N'字段表达' 
图片 398FROM syscolumns a 
图片 399left join systypes b 
图片 400on a.xtype=b.xusertype 
图片 401inner join sysobjects d 
图片 402on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' 
图片 403left join syscomments e 
图片 404on a.cdefault=e.id 
图片 405left join sysproperties g 
图片 406on a.id=g.id AND a.colid = g.smallid 
图片 407order by object_name(a.id),a.colorder
图片 408收获表结构[把 'sysobjects' 替换 成 'tablename' 即可] 
图片 409
图片 410SELECT CASE IsNull(I.name, '') 
图片 411When '' Then '' 
图片 412Else '*' 
图片 413End as IsPK, 
图片 414Object_Name(A.id) as t_name, 
图片 415A.name as c_name, 
图片 416IsNull(SubString(M.text, 1, 254), '') as pbc_init, 
图片 417T.name as F_DataType, 
图片 418CASE IsNull(TYPEPROPERTY(T.name, 'Scale'), '') 
图片 419WHEN '' Then Cast(A.prec as varchar) 
图片 420ELSE Cast(A.prec as varchar)   ','   Cast(A.scale as varchar) 
图片 421END as F_Scale, 
图片 422A.isnullable as F_isNullAble 
图片 423FROM Syscolumns as A 
图片 424JOIN Systypes as T 
图片 425ON (A.xType = T.xUserType AND A.Id = Object_id('sysobjects') ) 
图片 426LEFT JOIN ( SysIndexes as I 
图片 427JOIN Syscolumns as A1 
图片 428ON ( I.id = A1.id and A1.id = object_id('sysobjects') and (I.status & 0x800) = 0x800 AND A1.colid <= I.keycnt) ) 
图片 429ON ( A.id = I.id AND A.name = index_col('sysobjects', I.indid, A1.colid) ) 
图片 430LEFT JOIN SysComments as M 
图片 431ON ( M.id = A.cdefault and ObjectProperty(A.cdefault, 'IsConstraint') = 1 ) 
图片 432ORDER BY A.Colid ASC
图片 433

图片 434 ** 16 SQL Server中剔除重复数据的多少个章程
图片 435
 
** 图片 436  数据库的应用进程中出于程序方面的主题素材不经常会碰着重复数据,重复数据形成了数据库部分装置不可能正确安装。

图片 437 ** (1) 方法一**

图片 438declare @max integer,@id integer
图片 439declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) > 1
图片 440open cur_rows
图片 441fetch cur_rows into @id,@max
图片 442while @@fetch_status=0
图片 443begin
图片 444select @max = @max -1
图片 445set rowcount @max
图片 446delete from 表名 where 主字段 = @id
图片 447fetch cur_rows into @id,@max
图片 448end
图片 449close cur_rows
图片 450set rowcount 0 

图片 451 ** (2) 方法二**

图片 452 ** **有多少个意思上的重复记录,一是截然重复的笔录,也即具备字段均再一次的记录,二是局部重大字段重复的笔录,举个例子Name字段重复,而其余字段不自然再次或都再度能够忽视。

图片 453 ** **〈1〉 对于第一种重复,相比易于化解,使用

图片 454select distinct * from tableName 

图片 455 ** **就足以赢得无重复记录的结果集。

图片 456 ** **假若该表必要删除重复的笔录(重复记录保留1条),可以按以下方法删除

图片 457select distinct * into #Tmp from tableName
图片 458drop table tableName
图片 459select * into tableName from #Tmp
图片 460drop table #Tmp 

图片 461 ** **发生这种重新的缘由是表设计不周发生的,扩张独一索引列就能够缓和。

图片 462 〈2〉 那类重复难点普通必要保存重复记录中的第一条记下,操作方法如下

图片 463 若是有重复的字段为Name, Address,需要获取这四个字段独一的结果集

图片 464select identity(int,1,1) as autoID, * into #Tmp from tableName
图片 465select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID
图片 466select * from #Tmp where autoID in(select autoID from #tmp2) 

图片 467 末段一个select即获得了Name,Address不另行的结果集(但多了贰个autoID字段,实际写时得以写在select子句中节约此列)

图片 468 ** **17 查询分析器不可能单步调节和测量检验的的原故

图片 469 ** 具体步骤如下:
图片 470
 ** 1、将服务器【身份验证】属性设置成【混合方式】(window与sql身份验证)
图片 471 ** 2、在【调控面板】中开荒【服务】将【MSSQLSE途睿欧VE大切诺基】服务张开【属性】,选拔【登陆】页面,将登陆身份设置成服务器本地帐号和该帐号密码,如administrator,密码123;
图片 472
 ** 3、重新启航sqlserver服务,此时的劳动指的是【SQL服务管理器】中的SQL SEENCOREVE库罗德服务;要是【帐号】设置为administrator
图片 473 ** 那时完结的功效是:服务器当地帐号administrator与客商端上的administrator(何况该帐号的密码要与服务器密码同样)能够由此【查询解析器】进行调度;
图片 474
 ** 要是想让【别的帐号】也能够调治,那么还亟需如下设置:
图片 475 ** 1、在【服务器】上运行dcomcnfg.exe;
图片 476
 ** 2、在【暗许安全部制】中【暗中认可访谈权限】侧边点击【编辑默许值】接纳允许调节和测量试验的帐号类型,如users顾客类型,sample帐号有隐含users组;
图片 477 ** 3、重新启航sqlserver服务;
图片 478
 ** 3、在客商端上创办与劳务帐号密码一样的顾客,如sample;
图片 479 ** 姣好那步就足以由此询问解析器的调和功用实行单步调节和测量试验了。
图片 480
 ** 注:第二步更换“运转服务帐户”,在率先次登入以前,必得改变顾客密码。
图片 481 ** 不然,event log:
图片 482
 ** 以当下密码登陆的品尝因下列错误而发表退步:
图片 483 ** **在第二回登陆从前,必得改变客商密码。

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:详解与实例,SQL操作收集

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。