简单实用SQL脚本

最新推荐文章于 2024-10-05 00:24:42 发布

pdn2000

最新推荐文章于 2024-10-05 00:24:42 发布

阅读量764

点赞数

文章标签： sql insert table 数据库 null function

行列互转

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

语法1：Insert INTO table(field1,field2,...) values(value1,value2,...)

语法2：Insert into Table2(field1,field2,...) select value1,value2,... from Table1（要求目标表Table2必须存在，由于目标表Table2已经存在，所以我们除了插入源表Table1的字段外，还可以插入常量。）

语法3：SELECT 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 必须为 tinyint、smallint 或 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 2005：coalesce
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＝具有text或image数据的表条目。
rows int 基于indid=0 和 indid=1地数据级行数，该值对于indid>1重复。如果indid=255，rows设置为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

-- 适用MSSQL2000、MSSQL2005
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 )

导出时加入特殊字符

情况一：全部字段都需要加字符，在这里设置【文本限定符】就可以了。

情况二：

-- 某些特殊的字段需要加单引号（或者其它符号）
SELECT
       [ ID ]
      , '''' + convert ( varchar ( 25 ), [ ts ] , 121 ) + '''' as [ ts ]
      , '''' + convert ( varchar ( 25 ), [ otherParty ] , 121 ) + '''' as [ otherParty ]
      , '''' + convert ( varchar ( 25 ), [ StartTime ] , 121 ) + '''' as [ StartTime ]
      , [ CcCause ]
      , [ RrCause ]
   FROM [ 表 ]

效果：74983006,'2010-03-09 23:59:10.000'

newid()的妙用

-- 生成测试数据
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [ dbo ] . [ test ] (
     [ name ] [ varchar ] ( 20 ) NULL ,
     [ quarter ] [ int ] NULL ,
     [ profile ] [ int ] NULL ,
     [ dates ] [ smallint ] NULL ,
     [ ID ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
CONSTRAINT [ PK_test ] PRIMARY KEY CLUSTERED
(
     [ ID ] ASC
) WITH (PAD_INDEX   = OFF , STATISTICS_NORECOMPUTE   = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS   = ON , ALLOW_PAGE_LOCKS   = ON ) ON [ PRIMARY ]
) ON [ PRIMARY ]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [ dbo ] . [ test ] ON
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' a ' , 1 , 1000 , 421 , 1 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' Aa ' , 2 , 2000 , 421 , 2 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' AA ' , 3 , 4000 , 421 , 3 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' a ' , 4 , 5000 , 421 , 4 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' b ' , 1 , 3000 , 421 , 5 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' b ' , 2 , 3500 , 421 , 6 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' b ' , 3 , 4200 , 421 , 7 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' b ' , 4 , 5500 , 421 , 8 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' a ' , 1 , 1000 , 421 , 9 )
SET IDENTITY_INSERT [ dbo ] . [ test ] OFF

--从数据库中随机取出N条记录的方法:newid()

select top 5 * from test
select top 5 * from test order by newid ()

（图：效果图）

查询时区分大小写

 
  -- 
  生成测试数据 
  
  
  SET 
   ANSI_NULLS  
  ON 
  
  
  GO 
  
  
  SET 
   QUOTED_IDENTIFIER  
  ON 
  
  
  GO 
  
  
  SET 
   ANSI_PADDING  
  ON 
  
  
  GO 
  
  
  CREATE 
    
  TABLE 
    
  [ 
  dbo 
  ] 
  . 
  [ 
  test 
  ] 
  (
      
  [ 
  name 
  ] 
    
  [ 
  varchar 
  ] 
  ( 
  20 
  )  
  NULL 
  ,
      
  [ 
  quarter 
  ] 
    
  [ 
  int 
  ] 
    
  NULL 
  ,
      
  [ 
  profile 
  ] 
    
  [ 
  int 
  ] 
    
  NULL 
  ,
      
  [ 
  dates 
  ] 
    
  [ 
  smallint 
  ] 
    
  NULL 
  ,
      
  [ 
  ID 
  ] 
    
  [ 
  int 
  ] 
    
  IDENTITY 
  ( 
  1 
  , 
  1 
  )  
  NOT 
    
  NULL 
  ,
   
  CONSTRAINT 
    
  [ 
  PK_test 
  ] 
    
  PRIMARY 
    
  KEY 
    
  CLUSTERED 
   
 (
      
  [ 
  ID 
  ] 
    
  ASC 
  
 ) 
  WITH 
   (PAD_INDEX   
  = 
    
  OFF 
  , STATISTICS_NORECOMPUTE   
  = 
    
  OFF 
  , IGNORE_DUP_KEY  
  = 
    
  OFF 
  , ALLOW_ROW_LOCKS   
  = 
    
  ON 
  , ALLOW_PAGE_LOCKS   
  = 
    
  ON 
  )  
  ON 
    
  [ 
  PRIMARY 
  ] 
  
 )  
  ON 
    
  [ 
  PRIMARY 
  ] 
  
  
  GO 
  
  
  SET 
   ANSI_PADDING  
  OFF 
  
  
  GO 
  
  
  SET 
    
  IDENTITY_INSERT 
    
  [ 
  dbo 
  ] 
  . 
  [ 
  test 
  ] 
    
  ON 
  
  
  INSERT 
    
  [ 
  dbo 
  ] 
  . 
  [ 
  test 
  ] 
   ( 
  [ 
  name 
  ] 
  ,  
  [ 
  quarter 
  ] 
  ,  
  [ 
  profile 
  ] 
  ,  
  [ 
  dates 
  ] 
  ,  
  [ 
  ID 
  ] 
  )  
  VALUES 
   (N 
  ' 
  a 
  ' 
  ,  
  1 
  ,  
  1000 
  ,  
  421 
  ,  
  1 
  )
  
  INSERT 
    
  [ 
  dbo 
  ] 
  . 
  [ 
  test 
  ] 
   ( 
  [ 
  name 
  ] 
  ,  
  [ 
  quarter 
  ] 
  ,  
  [ 
  profile 
  ] 
  ,  
  [ 
  dates 
  ] 
  ,  
  [ 
  ID 
  ] 
  )  
  VALUES 
   (N 
  ' 
  Aa 
  ' 
  ,  
  2 
  ,  
  2000 
  ,  
  421 
  ,  
  2 
  )
  
  INSERT 
    
  [ 
  dbo 
  ] 
  . 
  [ 
  test 
  ] 
   ( 
  [ 
  name 
  ] 
  ,  
  [ 
  quarter 
  ] 
  ,  
  [ 
  profile 
  ] 
  ,  
  [ 
  dates 
  ] 
  ,  
  [ 
  ID 
  ] 
  )  
  VALUES 
   (N 
  ' 
  AA 
  ' 
  ,  
  3 
  ,  
  4000 
  ,  
  421 
  ,  
  3 
  )
  
  INSERT 
    
  [ 
  dbo 
  ] 
  . 
  [ 
  test 
  ] 
   ( 
  [ 
  name 
  ] 
  ,  
  [ 
  quarter 
  ] 
  ,  
  [ 
  profile 
  ] 
  ,  
  [ 
  dates 
  ] 
  ,  
  [ 
  ID 
  ] 
  )  
  VALUES 
   (N 
  ' 
  A 
  ' 
  ,  
  4 
  ,  
  5000 
  ,  
  421 
  ,  
  4 
  )
  
  INSERT 
    
  [ 
  dbo 
  ] 
  . 
  [ 
  test 
  ] 
   ( 
  [ 
  name 
  ] 
  ,  
  [ 
  quarter 
  ] 
  ,  
  [ 
  profile 
  ] 
  ,  
  [ 
  dates 
  ] 
  ,  
  [ 
  ID 
  ] 
  )  
  VALUES 
   (N 
  ' 
  b 
  ' 
  ,  
  1 
  ,  
  3000 
  ,  
  421 
  ,  
  5 
  )
  
  INSERT 
    
  [ 
  dbo 
  ] 
  . 
  [ 
  test 
  ] 
   ( 
  [ 
  name 
  ] 
  ,  
  [ 
  quarter 
  ] 
  ,  
  [ 
  profile 
  ] 
  ,  
  [ 
  dates 
  ] 
  ,  
  [ 
  ID 
  ] 
  )  
  VALUES 
   (N 
  ' 
  b 
  ' 
  ,  
  2 
  ,  
  3500 
  ,  
  421 
  ,  
  6 
  )
  
  INSERT 
    
  [ 
  dbo 
  ] 
  . 
  [ 
  test 
  ] 
   ( 
  [ 
  name 
  ] 
  ,  
  [ 
  quarter 
  ] 
  ,  
  [ 
  profile 
  ] 
  ,  
  [ 
  dates 
  ] 
  ,  
  [ 
  ID 
  ] 
  )  
  VALUES 
   (N 
  ' 
  b 
  ' 
  ,  
  3 
  ,  
  4200 
  ,  
  421 
  ,  
  7 
  )
  
  INSERT 
    
  [ 
  dbo 
  ] 
  . 
  [ 
  test 
  ] 
   ( 
  [ 
  name 
  ] 
  ,  
  [ 
  quarter 
  ] 
  ,  
  [ 
  profile 
  ] 
  ,  
  [ 
  dates 
  ] 
  ,  
  [ 
  ID 
  ] 
  )  
  VALUES 
   (N 
  ' 
  b 
  ' 
  ,  
  4 
  ,  
  5500 
  ,  
  421 
  ,  
  8 
  )
  
  INSERT 
    
  [ 
  dbo 
  ] 
  . 
  [ 
  test 
  ] 
   ( 
  [ 
  name 
  ] 
  ,  
  [ 
  quarter 
  ] 
  ,  
  [ 
  profile 
  ] 
  ,  
  [ 
  dates 
  ] 
  ,  
  [ 
  ID 
  ] 
  )  
  VALUES 
   (N 
  ' 
  A 
  ' 
  ,  
  1 
  ,  
  1000 
  ,  
  421 
  ,  
  9 
  )
  
  SET 
    
  IDENTITY_INSERT 
    
  [ 
  dbo 
  ] 
  . 
  [ 
  test 
  ] 
    
  OFF 
  
  
 

-- sql server 查询区分大小写 collate Chinese_PRC_CS_AS_WS
select * from test where [ name ] = ' A ' collate Chinese_PRC_CS_AI
select * from test where ascii (name) = ascii ( ' A ' )

（图：效果图）

SQL换行

/*
SQL的换行
制表符 CHAR(9)
换行符 CHAR(10)
回车 CHAR(13)
*/
PRINT ' Test ' + CHAR ( 13 ) + ' Name '
PRINT ' Test ' + CHAR ( 10 ) + ' Age '
PRINT ' Test ' + CHAR ( 9 ) + ' Tel '

-- 以文本格式显示结果
SELECT ' AAA ' + CHAR ( 10 ) + ' BBB ' -- AAA  BBB
SELECT ' AAA ' + CHAR ( 13 ) + ' BBB ' -- AAA  BBB
SELECT ' AAA ' + CHAR ( 10 ) + CHAR ( 13 ) + ' BBB ' -- AAA  BBB

TRUNCATE TABLE [Table Name]

下面是对Truncate语句在MSSQLServer2000中用法和原理的说明：

Truncate是SQL中的一个删除数据表内容的语句，用法是：

Truncate table 表名速度快,而且效率高,因为:
TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。
DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。
对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。
TRUNCATE TABLE 不能用于参与了索引视图的表。

其它

-- 查看内存状态
dbcc memorystatus

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

-- 查看锁住了那个资源id，objid
EXEC sp_lock

-- 当前查询分析器SPID
-- 查询分析器的状态栏中可以看到
-- 比如sa(57),这就表示当前查询分析器SPID为57,这样在使用profile的时候就可以指定当前窗体进行监控

-- 获取脚本的执行时间
declare @timediff datetime
select @timediff = getdate ()
select * from tablename
print ' 1耗时: ' + convert ( varchar ( 10 ), datediff (ms, @timediff , getdate ()))

/*sql 语法学习*/

/*函数的学习－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－*/

获取当前时间（时/分/秒）：select convert(varchar(10),getdate(),8)
获取当前年月日：select convert(varchar(10),getdate(),120)
获取当前年月：select convert(varchar(7),getdate(),120)
获取当前年月：select convert(varchar(10),year(getdate())) + '-' + convert(varchar(10),month(getDate()))

select cast(b as integer) as bb from table1 where b = '11'

select a,case b when '11' then '细细' when '22' then '呵呵' else '哈哈' end as 转换,c from table1

select a,b,case when c = '111' then '细细' when c = '222' then '呵呵' else '哈哈' end as 转换1 from table1

获取当前时间：print current_timestamp

/*－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－*/

-----------------将sql查询输出到txt文本文件中-------------------------------------------
EXEC master..xp_cmdshell 'bcp 数据库名.dbo.表名 out d:\1.txt -c -q -U"sa" -P"password"'

---------------------------------------------------------------------------------------

---------------------------round的用法beigin------------------------------
declare @s float
set @s = 0.1566134
print round(@s,3)
---------------------------round的用法end---------------------------------

--------------------------------自动收缩数据库begin-----------------------------

EXEC [master]..sp_dboption [Database Name], 'autoshrink', 'TRUE'

--------------------------------自动收缩数据库end-----------------------------

-------------------------------去除首尾无效的字符begin--------------------------
declare @s varchar(20)
set @s=',,,1->1,'
while(left(@s,1)=',')
set @s=stuff(@s,1,1,'')
while(right(@s,1)=',')
set @s=stuff(reverse(@s),1,1,'')
select @s
-------------------------------去除首尾无效的字符end--------------------------

------------删除数据库中的重复记录（且仅保留一条有效记录）示例-----------------
create table A
(
userID int identity(1,1),
userName varchar(20),
userPwd varchar(20),
userEmail varchar(50)
)
insert into A(userName,userpwd) select 'qin','qin' union all select 'qin','qin1' union all select 'qin','qin1'
select * from A

--method one
delete from A where userid not in(select min(userid) as userid from A group by username ,userpwd)

--method two
delete from A where exists (select * from A b where a.username = b.username and a.userpwd = b.userpwd and a.userid < b.userid)

--method three
delete from a where userid not in(select min(userid) from A b where a.username = b.username and a.userpwd = b.userpwd and a.userid > b.userID)

select * from A
drop table A

------------删除数据库中的重复记录（且仅保留一条有效记录）示例-----------------

-------------------------------迭归的应用（找起点和终点之间的路径-----------------------------
create table t
(st varchar(20),ed varchar(20),km int)
go
insert t values ('A','B',1000)
insert t values ('A','C',1100)
insert t values ('A','D',900)
insert t values ('A','E',400)
insert t values ('B','D',300)
insert t values ('D','F',600)
insert t values ('E','A',400)
insert t values ('F','G',1000)
insert t values ('C','B',600)
go
--显示插入值
select * from t
go

--创建函数
--函数返回一个表，根据实际情况的不同一层一层的插入，可以充分利用生成的表
create function f_go(@col varchar(10))
returns @t table(col varchar(30),st varchar(20),ed varchar(20),km int,level int)
as
begin
declare @i int
set @i=1
insert @t select st+'-'+ed,*,@i from t where st=@col
while exists (select * from t a,@t b where
b.ed=a.st and b.level=@i and b.ed<>@col )
begin
set @i=@i+1
insert @t
select b.col+'-'+a.ed,a.st,a.ed,b.km+a.km,@i from t a,@t b
where b.level=@i-1 and b.ed=a.st and b.ed<>@col
end
return
end
go

--调用
--select * from dbo.f_go('A')
select col,km from dbo.f_go('a')

--删除环境
drop function f_go
drop table t

-------------------------------迭归的应用（找起点和终点之间的路径-----------------------------

--------按类别去最新的前N条记录,把同一类的放在一起,统计同一类的项的个数等-------------
create table t
(
ClassName varchar(50),
ClassCode varchar(10),
ClassID int identity(1,1)
)
insert into t
select 'cccc1','002' union all
select 'aaaa','001' union all
select 'bbbb','001' union all
select 'aaaa1','002' union all
select 'cccc','001' union all
select 'dddd','001' union all
select 'bbbb1','002' union all
select 'dddd1','002'
select * from t
select ClassCode = (case when exists(select 1 from t t1 where classCode = t1.ClassCode
and ClassID < t1.ClassID)
then '' else ClassCode end),ClassName from t order by ClassCode,ClassID desc

select count(*),classCode from (select top 100 percent ClassCode = (case when exists(select 1 from t t1 where classCode = t1.ClassCode
and ClassID < t1.ClassID)
then '' else ClassCode end),ClassName from t order by ClassCode,ClassID desc)a group by classcode

select classCode,className from t order by classCode,classID desc
drop table t

--------按类别去最新的前N条记录,把同一类的放在一起,统计同一类的项的个数等-------------

-------------同上,按类别进行统计,把同一类的项的其他内容进行相加并发在一个字段中------------------
create table tb(ProductID varchar(10),PositionID varchar(10))
insert into tb
select '10001','A1'
union all select '10001','B2'
union all select '10002','C3'
union all select '10002','D4'
union all select '10002','E5'
go

create function dbo.fc_str(@ProductID varchar(10))
returns varchar(100)
as
begin
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+','+cast(PositionID as varchar(20)) from tb where ProductID=@ProductID
return stuff(@sql,1,1,'')
end
go

select ProductID,dbo.fc_str(ProductID) as PositionID from tb group by ProductID

drop table tb

drop function dbo.fc_str

-------------按类别进行统计,把同一类的项的其他内容进行相加并发在一个字段中------------------

--取各个类的前n条记录(每个类都取top n条)
--如果有数据库中有多个类,现在要取每个类的前n条记录,可用以下语句
Create Table TEST
(ID Int Identity(1,1),
h_id Int)
Insert TEST Select 100
Union All Select 100
Union All Select 100
Union All Select 101
Union All Select 101
Union All Select 101
Union All Select 100
GO
--方法一：
Select * From TEST A Where Id In(Select TOP 3 ID From TEST Where h_id=A.h_id)
--方法二：
Select * From TEST A Where Not Exists (Select 1 From TEST Where h_id=A.h_id And ID<A.ID Having Count(*)>2)
--方法三：
Select * From TEST A Where (Select Count(*) From TEST Where h_id=A.h_id And ID<A.ID)<3
GO
Drop Table TEST
GO

--分组统计,统计每个段中数据的个数
--一般成绩统计可以用到这个
declare @t table(id int,weight int)
insert into @t select 1, 20
insert into @t select 2, 15
insert into @t select 3, 5
insert into @t select 4, 60
insert into @t select 5, 12
insert into @t select 6, 33
insert into @t select 7, 45
insert into @t select 8, 59
insert into @t select 9, 89
insert into @t select 10,110

declare @p int
set @p=10
select
    rtrim(p*@p)+'-'+rtrim((p+1)*@p">p*@p)+'-'+rtrim((p+1)*@p) as p,
    num
from
    (select (weight/@p">weight/@p) as p,count(*) as num from @t where weight between 10 and 100 group by (weight/@p">weight/@p)) a

----------------------------在in语句中只用自定义排序begin--------------------------------
declare @t table(id int,weight int)
insert into @t select 1, 20
insert into @t select 2, 15
insert into @t select 3, 5
insert into @t select 4, 60
insert into @t select 5, 12
insert into @t select 6, 33
insert into @t select 7, 45
insert into @t select 8, 59
insert into @t select 9, 89
insert into @t select 10,110
--默认in语句中sql会按照id进行排序
select * from @t where id in(2,4,3)
--用此方法可以按照我们传入的id顺序进行显示数据
select * from @t where id in(2,4,3) order by charindex(rtrim(id),',2,4,3,')

----------------------------在in语句中只用自定义排序end--------------------------------