常用的数据统计Sql 总结

最近刚在搞一个BI的项目,里面需要大量的sql 数据统计相关运用,加深了我又对SQL的理解与使用。

 

所以,分享几个数据统计时常用的sql 语句总结:

 

1. 统计各个条件下的数据

select
BatchId,sum(CardSum) 总金额,
sum(case when Status=1 then CardSum else 0 end) as 已使用,
sum(case when Status=2 then CardSum else 0 end) as 已冻结 
from GiftCard 
group by BatchId

 

2. 统计每日,每月,每年的数据

select year(AddTime) 年,month(AddTime) 月,day(AddTime) 日,COUNT(1) 数量,sum(CardSum) 销售合计
from GiftCard
group by year(AddTime),month(AddTime),day(AddTime)

 

3. 某列去重统计

select COUNT(BatchId),COUNT(distinct BatchId),COUNT(distinct BatchName)
from GiftCard

 

4. 行转列

SELECT *
FROM (
    SELECT 
        BatchName, 
        CardSum as TotAmount 
    FROM GiftCard

) as s
PIVOT
(    
    SUM(TotAmount)
    FOR BatchName IN (zx测试商品, test新人优惠券,测试高考大放送)
)AS MyPivot

 

5. 得到表中最小的未使用的ID号

SELECT 
(CASE WHEN EXISTS(SELECT * FROM GiftCard b WHERE b.Id = 1) THEN MIN(Id) + 1 ELSE 1 END) as Id 
FROM GiftCard

WHERE NOT Id IN (SELECT a.Id - 1 FROM GiftCard a)

 

6. 查询某一列数据不重复的数量

select * 
from GiftCard a 
where not exists(select 1 from GiftCard where BatchName=a.BatchName and ID<a.ID)

 

7. 按年统计1月到12个月的销量

select year(AddTime) as '',
SUM(case when MONTH(AddTime)=1 then CardSum else 0 end ) as '一月',
SUM(case when MONTH(AddTime)=2 then CardSum else 0 end ) as '二月',
SUM(case when MONTH(AddTime)=3 then CardSum else 0 end ) as '三月',
SUM(case when MONTH(AddTime)=4 then CardSum else 0 end ) as '四月',
SUM(case when MONTH(AddTime)=5 then CardSum else 0 end ) as '五月',
SUM(case when MONTH(AddTime)=6 then CardSum else 0 end ) as '六月',
SUM(case when MONTH(AddTime)=7 then CardSum else 0 end ) as '七月',
SUM(case when MONTH(AddTime)=8 then CardSum else 0 end ) as '八月',
SUM(case when MONTH(AddTime)=9 then CardSum else 0 end ) as '九月',
SUM(case when MONTH(AddTime)=10 then CardSum else 0 end ) as '十月',
SUM(case when MONTH(AddTime)=11 then CardSum else 0 end ) as '十一月',
SUM(case when MONTH(AddTime)=12 then CardSum else 0 end ) as '十二月'

from GiftCard

group by year(AddTime)

 

数据库性能优化常用sql脚本总结

 

最近闲来无事,正好抽出时间,来总结总结 sql性能优化方面的一下小技巧,小工具。虽然都是些很杂的东西,但是我个人觉得,如果真的清楚了里面的一下指标,或许真的能抵半个DBA。

有些时候,找不到DBA或者根本就没有DBA的时候,程序员就只能靠自己想办法了解决。久而久之,久病成医,说不定就成了半个DBA了。 这里面的一些脚本,有自己总结的,也有网上找的。希望能给程序员在性能优化方面一些帮助。(PS: 这些脚本,都是SQL Server 下的)。

 

 1. 当前连接的Session 有多少

SELECT login_name

    ,[program_name]

    ,COUNT(session_id) AS [session_count]

FROM sys.dm_exec_sessions WITH (NOLOCK)

GROUP BY login_name,[program_name]

ORDER BY COUNT(session_id) desc;

 

2. 每个数据库上的Session 数量是多少

SELECT DB_NAME(dbid) AS DBName

    ,COUNT(dbid) AS NumberOfConnections

    ,loginame AS LoginName

FROM sys.sysprocesses

WHERE dbid > 0 

GROUP BY dbid,loginame

 

3. 查看阻塞

SELECT

    SPID                = er.session_id

    ,STATUS             = ses.STATUS

    ,[LOGIN]            = ses.login_name

    ,HOST               = ses.host_name

    ,BlkBy              = er.blocking_session_id

    ,DBName             = DB_NAME(er.database_id)

    ,CommandType        = er.command

    ,SQLStatement       = st.text

    ,BlockingText     = bst.text

    ,ObjectName         = OBJECT_NAME(st.objectid)

    ,ElapsedMS          = er.total_elapsed_time

    ,CPUTime            = er.cpu_time

    ,IOReads            = er.logical_reads + er.reads

    ,IOWrites           = er.writes

    ,LastWaitType       = er.last_wait_type

    ,StartTime          = er.start_time

    ,Protocol           = con.net_transport

    ,ConnectionWrites   = con.num_writes

    ,ConnectionReads    = con.num_reads

    ,ClientAddress      = con.client_net_address

    ,Authentication     = con.auth_scheme

FROM sys.dm_exec_requests er

OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st

LEFT JOIN sys.dm_exec_sessions ses

ON ses.session_id = er.session_id

LEFT JOIN sys.dm_exec_connections con

ON con.session_id = ses.session_id

LEFT JOIN sys.dm_exec_requests ber

ON er.blocking_session_id=ber.session_id

OUTER APPLY sys.dm_exec_sql_text(ber.sql_handle) bst

WHERE er.session_id > 50

ORDER BY er.blocking_session_id DESC,er.session_id

 

4. 找出哪些表的Index 需要改进

SELECT CONVERT(DECIMAL(18, 2), user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)) AS [index_advantage]

    ,migs.last_user_seek

    ,mid.[statement] AS [Database.Schema.Table]

    ,mid.equality_columns

    ,mid.inequality_columns

    ,mid.included_columns

    ,migs.unique_compiles

    ,migs.user_seeks

    ,migs.avg_total_user_cost

    ,migs.avg_user_impact

FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)

INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK) ON migs.group_handle = mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK) ON mig.index_handle = mid.index_handle

ORDER BY index_advantage desc

 

 5. 查看Index 的Statistics 最后更新时间

SELECT SCHEMA_NAME(o.[schema_id]) + N'.' + o.[name] AS [Object Name]

    ,o.type_desc AS [Object Type]

    ,i.[name] AS [Index Name]

    ,STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date]

    ,s.auto_created

    ,s.no_recompute

    ,s.user_created

    ,st.row_count

    ,st.used_page_count

FROM sys.objects AS o WITH (NOLOCK)

INNER JOIN sys.indexes AS i WITH (NOLOCK) ON o.[object_id] = i.[object_id]INNER JOIN sys.stats AS s WITH (NOLOCK) ON i.[object_id] = s.[object_id]

    AND i.index_id = s.stats_id

INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK) ON o.[object_id] = st.[object_id]

    AND i.[index_id] = st.[index_id]WHERE o.[type] IN ('U','V')

    AND st.row_count > 0

ORDER BY STATS_DATE(i.[object_id], i.index_id) desc;

 

6. 查看Index 碎片化指数

SELECT DB_NAME(ps.database_id) AS [Database Name]

    ,OBJECT_NAME(ps.[object_id]) AS [Object Name]

    ,i.[name] AS [Index Name]

    ,ps.index_id

    ,ps.index_type_desc

    ,ps.avg_fragmentation_in_percent

    ,ps.fragment_count

    ,ps.page_count

    ,i.fill_factor

    ,i.has_filter

    ,i.filter_definition

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, N'LIMITED') AS ps

INNER JOIN sys.indexes AS i WITH (NOLOCK) ON ps.[object_id] = i.[object_id]

    AND ps.index_id = i.index_id

WHERE ps.database_id = DB_ID()

    AND ps.page_count > 2500

ORDER BY ps.avg_fragmentation_in_percent desc;

 

 7. 查询前 10 个可能是性能最差的 SQL 语句

SELECT TOP 10 TEXT AS 'SQL Statement'
    ,last_execution_time AS 'Last Execution Time'
    ,(total_logical_reads + total_physical_reads + total_logical_writes) / execution_count AS [Average IO]
    ,(total_worker_time / execution_count) / 1000000.0 AS [Average CPU Time (sec)]
    ,(total_elapsed_time / execution_count) / 1000000.0 AS [Average Elapsed Time (sec)]
    ,execution_count AS "Execution Count"
    ,qp.query_plan AS "Query Plan"
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_elapsed_time / execution_count DESC

 

CTE公用表表达式和With用法总结

CTE(Common Table Expression) 公用表表达式,它是在单个语句的执行范围内定义的临时结果集,只在查询期间有效。它可以自引用,也可在同一查询中多次引用,实现了代码段的重复利用。

CTE最大的好处是提升T-Sql代码的可读性,可以更加优雅简洁的方式实现递归等复杂的查询。

CTE可用于:
  ⒈ 创建递归查询,这个应该是CTE最好用的地方
  ⒉ 在同一语句中多次引用生成的表
  3. 减少子查询和表变量,提高执行效率

CTE优点:
  1. 使用 CTE 可以获得提高可读性和轻松维护复杂查询的优点。同时,CTE要比表变量的效率高得多。
  2. 可以用来定义一个SQL片断,该SQL片断会被整个SQL语句所用到。有的时候,是为了让SQL语句的可读性更高些,也有可能是在UNION ALL的不同部分,作为提供数据的部分。
  3. 查询可以分为单独块、简单块、逻辑生成块。之后,这些简单块可用于生成更复杂的临时 CTE,直到生成最终结果集。

下面是CTE的语法:

WITH cte_name ( column_name [,...n] )
AS
(
    CTE_query_definition –- Anchor member is defined.
) 

 

使用示例
1. 查询临时结果集

WITH cte(CategoryID,CategoryName,ParentID,CategoryLevel)
AS (
  SELECT CategoryID
      ,CategoryName
      ,ParentID
      ,CategoryLevel
  FROM Category(NOLOCK)
  WHERE Status = 1 and parentid = 23
)
select * from cte;

注意: 1.使用CTE的SQL语句应紧跟在相关的CTE后面。
    2.多重CTE中间用逗号,分隔。
    3.可以被紧跟着的一条SQL语句所使用多次,但不能被紧跟着的多条SQL语句使用。

2. 创建递归查询

WITH cte(CategoryID ,CategoryName,ParentID,CategoryLevel)
AS (
  SELECT CategoryID
      ,CategoryName
      ,ParentID
      ,CategoryLevel
      FROM Category(NOLOCK)
  WHERE Status= 1 and parentid in (21,22,23,25,26)
  UNION ALL
  SELECT t.CategoryID
      ,t.CategoryName
      ,t.ParentID
      ,t.CategoryLevel
  FROM Category(NOLOCK) AS t
  INNER JOIN cte AS c ON t.parentid = c.CategoryID where Status= 1
)
select * from cte;

 

3. cte结果集和数据表关联

WITH cte(CategoryID,CategoryName,ParentID,CategoryLevel)
AS (
  SELECT CategoryID
      ,CategoryName
      ,ParentID
      ,CategoryLevel
  FROM Category(NOLOCK)
  WHERE Status = 1 and parentid = 23
)
select p.ProductId,p.ProductName,c.CategoryID,c.CategoryName,c.CategoryLevel 
from product p(NOLOCK)
inner join cte c(NOLOCK) on p.CategoryId=c.CategoryID

 

以上三种,应该是cte表达式,最常见的用法,如果还有其他用法,非常乐意咱们一起探讨。

 

记录数据库中索引失效的问题

  昨天碰到一个很有意思的问题,一个sql 语句,加上 SoftUseLine like ‘%OQC%’ 之后,速度就特别慢。去掉该条件之后,速度就快起来了。

  查看sql 语句的执行情况,发现加上那个查询条件之后,SoftWareDetailInfo表的逻辑读取变成了1300374 次,

      

  可是这个查询字段明明已经加上Index_SoftWareDetail索引了,怎么会逻辑读取还这么大呢。查看sql profile 的详细信息,发现sql 语句,没有走那个索引。

      

 

  于是,加上WITH(INDEX(Index_SoftWareDetail)) ,让sql 必须走这个索引试试。Sql查询立马变快了。sql profile 显示,已经走了Index_SoftWareDetail索引。看来是索引创建的有问题,导致查询计划,没有走该索引,而是进行全表扫描。所以导致查询速度变慢。

      

  于是把所有索引重新调整下。Ok了。

  数据库中索引什么时候会失效呢?

  总结下数据库中索引失效的问题,不过有些没有经过测试。这里仅供自己参考。

  首先,所谓失效。并不真的就是这个索引被删除了。而是在某些情况下,DBMS不会检索索引列表了。执行速度和没有这个索引时的速度一样。但是再执行另外的一条语句。同样的索引又正常起作用。所以索引的失效是针对某条sql语句,某个查询条件的,而不是针对索引本身的。

  哪类语句执行时索引不起作用呢。总结如下:

  1. 索引字段进行判空查询时。也就是对索引字段判断是否为NULL时。语句为is null 或is not null。

  比如:select * from SoftWareDetailInfo where CreateTime is null 此时就不检索time字段上的索引表了。也就是索引在这条语句执行时失效了。

  接着再执行

  select * from SoftWareDetailInfo where CreateTime = ‘2015-04-11 00:00:00’ 此时就会检索索引表了。索引又起作用了。

 

  2. 对索引字段进行like查询时。比如:select * from SoftWareDetailInfo where SoftUseLine like ‘%OQC%’。不过网上有的例子说like ‘xx%’索引起作用。我没试过。

 

  3. 判断索引列是否不等于某个值时。‘!=’操作符。比如:select * from SoftWareDetailInfo where SoftUseLine != 0

 

  4. 对索引列进行运算。这里运算包括+-*/等运算。也包括使用函数。比如:

  select * from SoftWareDetailInfo where SoftUseLine +0= 0

  此时索引不起作用。

  select * from SoftWareDetailInfo where count(SoftUseLine) = 0

  此时索引也不起作用。

  也就是说如果不是直接判断索引字段列,而是判断运算或其它函数处理后的索引列索引均不起作用。

 

  5. 复合索引中的前导列没有被作为查询条件。比如:Index_SoftWareDetail索引包含(a,b,c) 三列,但是查询条件里面,没有a,b 列,只有c 列,那么 Index_SoftWareDetail索引也不起作用。

 

记录一次阻塞引发的系统超时

 前些天一个棘手的问题,早上上班刚到公司不久,就发现系统里面出现了大量的查询超时。
 
一开始以为是某个语句锁住了,感觉查查,看看系统里面是否有语句死锁了,,
  select 0 , blocked      
 
 from ( select * from master..sysprocesses where blocked>0 )a     
 
 where not exists( select * from  master..sysprocesses where a.blocked =spid and blocked >0)      
 
 union select spid ,blocked from  master ..sysprocesses where blocked >0   
 
在数据库执行这个语句,发现没有死锁的问题,
 
 
系统还一直在超时的错误,突然想起来,是不是某个语句阻塞了,导致数据库其他查询都被阻塞了。于是赶紧问牛逼的 文佳哥。
 
给了我一段查询系统阻塞的sql 语句,
–查看阻塞
SELECT
    SPID                = er.session_id
    ,STATUS             = ses.STATUS
    ,[LOGIN]            = ses.login_name
    ,HOST               = ses.host_name
    ,BlkBy              = er.blocking_session_id
    ,DBName             = DB_NAME(er.database_id)
    ,CommandType        = er.command
    ,SQLStatement       = st.text
    ,BlockingText     = bst.text
    ,ObjectName         = OBJECT_NAME(st.objectid)
    ,ElapsedMS          = er.total_elapsed_time
    ,CPUTime            = er.cpu_time
    ,IOReads            = er.logical_reads + er.reads
    ,IOWrites           = er.writes
    ,LastWaitType       = er.last_wait_type
    ,StartTime          = er.start_time
    ,Protocol           = con.net_transport
    ,ConnectionWrites   = con.num_writes
    ,ConnectionReads    = con.num_reads
    ,ClientAddress      = con.client_net_address
    ,Authentication     = con.auth_scheme
FROM sys.dm_exec_requests er
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id
LEFT JOIN sys.dm_exec_requests ber
ON er.blocking_session_id=ber.session_id
OUTER APPLY sys.dm_exec_sql_text(ber.sql_handle) bst
WHERE er.session_id > 50
ORDER BY er.blocking_session_id DESC,er.session_id
 
通过这个语句一查,还真有几条语句处于阻塞的状态,一直在等待,但是等待的语句太多,也不知道是因为那个阻塞的,通过阻塞时间,和其他的参数也看不出来,也没办只能把所有阻塞的语句都kill 掉。kill  SPID                
 
经过一阵折腾,系统总算是恢复正常了。但是还是没有找到罪魁祸首,没办法有加了一些日子,果然,没过多次时间,又被阻塞了,于是分析相关的日志,终于找到相关的语句了。
 
 
 
 
 
 
 
 
 

存储过程的优缺点

  前段时间,在维护公司的几年前的一个项目,虽说不上是历史悠久,但也有些年头了,采用三层架构,加上存储过程的这么一个老的架构,本来这个架构也没什么问题,大部分系统都是这么干的,但是由于这个系统存储过程被滥用的问题,后期维护的时候,麻烦事就来了,任何涉及到数据库的调整,都得存储过程,实体类,SQL操作等相关的地方,都得改一通。可以说被存储过程伤的很深啊,所以今天就来总结下存储过程的优缺点。

  优点

  1. 运行速度:对于很简单的sql,存储过程没有什么优势。对于复杂的业务逻辑,因为在存储过程创建的时候,数据库已经对其进行了一次解析和优化。存储过程一旦执行,在内存中就会保留一份这个存储过程,这样下次再执行同样的存储过程时,可以从内存中直接调用,所以执行速度会比普通sql快。    

  2.  减少网络传输:存储过程直接就在数据库服务器上跑,所有的数据访问都在数据库服务器内部进行,不需要传输数据到其它服务器,所以会减少一定的网络传输。但是在存储过程中没有多次数据交互,那么实际上网络传输量和直接sql是一样的。而且我们的应用服务器通常与数据库是在同一内网,大数据的访问的瓶颈会是硬盘的速度,而不是网速。

  3. 可维护性:的存储过程有些时候比程序更容易维护,这是因为可以实时更新DB端的存储过程。  有些bug,直接改存储过程里的业务逻辑,就搞定了。 

  4. 增强安全性:提高代码安全,防止 SQL注入。这一点sql语句也可以做到。

  5. 可扩展性:应用程序和数据库操作分开,独立进行,而不是相互在一起。方便以后的扩展和DBA维护优化。

  缺点  

   1. SQL本身是一种结构化查询语言,但不是面向对象的的,本质上还是过程化的语言,面对复杂的业务逻辑,过程化的处理会很吃力。同时SQL擅长的是数据查询而非业务逻辑的处理,如果如果把业务逻辑全放在存储过程里面,违背了这一原则。

   2. 如果需要对输入存储过程的参数进行更改,或者要更改由其返回的数据,则您仍需要更新程序集中的代码以添加参数、更新调用,等等,这时候估计会比较繁琐了。

   3. 开发调试复杂,由于IDE的问题,存储过程的开发调试要比一般程序困难。     

   4. 没办法应用缓存。虽然有全局临时表之类的方法可以做缓存,但同样加重了数据库的负担。如果缓存并发严重,经常要加锁,那效率实在堪忧。

   5. 不支持群集,数据库服务器无法水平扩展,或者数据库的切割(水平或垂直切割)。数据库切割之后,存储过程并不清楚数据存储在哪个数据库中。

  总结

   1. 适当的使用存储过程,能够提高我们SQL查询的性能,

   2. 存储过程不应该大规模使用,滥用。

   3. 随着众多ORM 的出现,存储过程很多优势已经不明显。

   4. SQL最大的缺点还是SQL语言本身的局限性——SQL本身是一种结构化查询语言,我们不应该用存储过程处理复杂的业务逻辑——让SQL回归它“结构化查询语言”的功用。复杂的业务逻辑,还是交给代码去处理吧。

 

 

一个 Sql语句优化的问题- STATISTICS 统计信息

  前段时间,同事遇到一个 Sql语句的问题,一个列表分页功能响应在30 s以上,看数据库里面的数据条数,数据量也不大,相关字段的一些索引也都有,可就是慢。于是找出具体的sql 语句出来分析,分页功能主要有个sql 语句,select 查询和 count 两条语句。 select 查询字段的时候,速度挺快,执行时间在1 s以内 ,但是执行count(1)  的时候,速度巨慢,执行时间增加到10 s以上。奇怪的是count 语句为什么会比select 语句还慢呢。总之可以确定的就是count语句导致的。定位到具体的语句之后,查看具体的执行计划。发现select 语句的查询计划和count(1)的查询计划,有一些不同。

Select 语句 的执行计划

 

count(*)的查询计划

 

以为是索引的问题。于是往各个表里面都加上的相关的索引,情况依旧,于是判断可能不是索引的问题。

然后猜测是IO的问题。

于是在两条sql 前后 加上SET STATISTICS IO ON; 查看IO情况。

 

Select 语句的IO 输出

 

Count(1) 语句的IO 输出

 

对比后发现,ChannelBussinessInfo 这个表的逻辑读取,从2028次猛增到了631722 次,

估计就是ChannelBussinessInfo 这个表的问题,于是尝试着给这个表加了一些相关的索引,但是依然没有效果。

 

没有办法了,于是猜测是不是统计信息有问题,因为统计信息会影响 执行计划和io读取。

顺着这个思路,尝试着把ChannelBussinessInfo这个表统计信息更新了,

UPDATE STATISTICS ChannelBussinessInfo;

 

果然,sql 执行时间降低到1秒。IO 读取降到2028次。

 

关于UPDATE STATISTICS 的相关说明:

  http://technet.microsoft.com/zh-cn/ms187348

常用的Sql 函数

常用的Sql 函数

 

1: replace 函数,替换字符。

语法

replace (original-string, search-string, replace-string )

第一个参数你的字符串,第二个参数你想替换的部分,第三个参数你要替换成什么

 

select replace(‘helloword’,’h’,’a’)

输出:aelloword

 

2: substring函数,截取字符串。

语法

SUBSTRING ( expression, start, length )

第一个参数你的字符串,第二个是开始截取位置,第三个结束截取位置

 

select substring(‘helloword’,0,3);

输出:he

 

3:charindex 函数,返回字符或者字符串在另一个字符串中的起始位置

语法

charindex (expression1 , expression2)

第一个参数你要查找的char,第二个参数你被查找的字符串,返回参数一在参数二的位置

 

select charindex(‘e’,’helloworld’)

输出:2

 

4:str 函数,将数值型转换成指定长度的字符串

语法

STR (float_expression [ , length [ , decimal ] ] )

 

select STR(12345.633,7,1)

输出:12345.6

 

5:stuff 函数,将字符串插入另一字符串。它在第一个字符串中从开始位置删除指定长度的字符;然后将第二个字符串插入第一个字符串的开始位置。

语法

STUFF ( character_expression , start , length ,character_expression )

 

SELECT stuff(‘helloworld’,2,3,’hello’)

输出:hhellooworld

 

6:left 函数,返回最左边N个字符,

语法

left(character_expression, integer_expression)

 

select left(‘helloworld’,4)

输出:hell

 

7: right函数,返回最右边N个字符,由参数决定

语法

right(character_expression, integer_expression)

 

select right(‘helloworld’,4)

输出:orld

 

8:replicate 函数,以指定的次数重复字符表达式。

语法

replicate ( string_expression ,integer_expression )

 

select replicate(‘helloworld’,4)

输出:helloworldhelloworldhelloworld

 

 

9:len函数,返回参数长度

语法

len ( string_expression )

 

select len(‘helloworld’)

输出:10

 

10:reverse函数,反转字符串

语法

reverse ( string_expression )

 

select reverse(‘helloworld’)

输出:dlrowolleh

 

11:lowerupper函数,参数大小写转化

语法

lower( string_expression ) , upper( string_expression )

 

select lower(‘HELLOWORLD’) + upper(‘helloworld’)

输出:helloworldHELLOWORLD

 

12:ltrimrtrim函数,删除左边空格和右面空格

语法

ltrim( string_expression ) , rtrim( string_expression )

 

select ltrim(‘    helloworld                 ‘)

输出:helloworld                                 

select rtrim(‘    helloworld                ‘)

输出:     helloworld

 

13. PATINDEX 函数,返回模式在指定表达式中第一次出现的起始位置;如果在所有有效的文本和字符数据类型中都找不到该模式,则返回零。

语法

PATINDEX ( ‘%pattern%’ , expression )

 

select PATINDEX ( ‘%wor%’ , ‘helloworld’)

输出:6

 

14.DATEADD 函数,通过向指定日期添加间隔,返回新的 datetime 值。

语法

DATEADD (datepart ,number,date )

 

SELECT OrderId,DATEADD(day,2,’2012-1-1′)

输出:2012-01-03 00:00:00.000

 

15.DATEDIFF 函数,返回跨越两个指定日期的日期和时间边界的数目。

语法

DATEDIFF ( datepart , startdate , enddate )

 

SELECT DATEDIFF(day,’2012-1-1 00:00:00.000′,’2012-1-3 00:00:00.000′)

输出:2

 

16. cast  函数,将某种数据类型的表达式显式转换为另一种数据类型。

语法

cast ( expression AS data_type )

select cast(11 as varchar(10))

输出:11

 

17. convert 函数,将某种数据类型的表达式显式转换为另一种数据类型。

语法

convert (data_type(length),data_to_be_converted,style)

select convert(VARCHAR(10),GETDATE(),110)

输出:06-09-2014

 

18:ASCII 函数,返回字符表达式中最左侧的字符的 ASCII 代码值。

语法

ASCII ( character_expression )

 

select ASCII(‘helloworld’)

输出:104

 

19:nchar 函数,根据 Unicode 标准的定义,返回具有指定的整数代码的 Unicode 字符。

参数是介于 0 与 65535 之间的正整数。如果指定了超出此范围的值,将返回 NULL。

语法

nchar ( integer_expression )

 

select nchar(3213)

输出: unicode字符

 

20:soundex 函数,返回一个soundex字符串。两个字符串应该有几乎是相同的同音字符串。

语法

SOUNDEX ( character_expression )

 

SELECT SOUNDEX (‘helloworld’)

输出:H120