1.随机读取10条记录
select top 10
[id],
[title],
from [table1]
order by newid()
newid()返回类型为uniqueidentifier
uniqueidentifier是全局唯一标识符 (guid):D22EB737-984B-4252-8A0D-1EE428853110
sql server联机帮助中的例子:
declare @myid uniqueidentifier
set @myid=newid()
print 'value of @myid is '+cast(@myid as varchar(255))
每次运行以上程序返回不同的uniqueidentifier
2. 批量删除所有表的数据
![](/Images/OutliningIndicators/None.gif)
--禁用所有外键约束
exec sp_msforeachtable 'alter table ? nocheck constraint all'
--清空数据
exec sp_msforEachTable 'truncate table ? '
--再启外键约束
exec sp_msforeachtable 'alter table ? check constraint all '
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
这里使用了ms没有公开的存储过程sp_msforeachtable。这个过程可以批量的处理表,其中的?号就代表所有表的意思。这个过程还是满好用的,应用也很广泛,例如批量修改所有者为dbo,为每个表都加上个字段等。
--修改ower为dbo
exec sp_msforeachtable 'sp_changeobjectowner ''?'', ''dbo'''
3.重设identity种子
DBCC CHECKIDENT (‘TableName', RESEED, 1) --1为种子参数,如果你要种子从10开始就改为10
![](/Images/OutliningIndicators/None.gif)
DBCC CHECKIDENT('TableName',NOSEED)--查看种子
4.常用字符串函数
--ASCII(character_expression) 字符值-》ASCII 值
select ascii('A'),ascii('a')
go
![](/Images/OutliningIndicators/None.gif)
--CHAR(integer_expression)ASCII 值-》字符值
select char(65),char(97)
go
![](/Images/OutliningIndicators/None.gif)
--UNICODE(ncharacter_expression) 字符值-》Unicode值
select unicode('爱')
go
![](/Images/OutliningIndicators/None.gif)
--NCHAR(integer_expression) Unicode值-》字符值
select nchar(29233)
go
![](/Images/OutliningIndicators/None.gif)
--LOWER(character_expression) 小写
select lower('I Like CnBlogs')
go
![](/Images/OutliningIndicators/None.gif)
----UPPER(character_expression) 大写
select upper('i like cnblogs')
go
![](/Images/OutliningIndicators/None.gif)
--LTRIM(character_expression) 截断左端英文空格
select ltrim(' 左部空格沒了')
go
![](/Images/OutliningIndicators/None.gif)
--RTRIM(character_expression) 截断右端英文空格
select rtrim('右边的空格沒了 ')
go
![](/Images/OutliningIndicators/None.gif)
--LEN(string_expression) 字符个数,不包含尾随的英文空格
select len('123456789')
go
![](/Images/OutliningIndicators/None.gif)
--LEFT(character_expression, integer_expression) 取左边相应个数字符
select left('我爱园子',2)
go
![](/Images/OutliningIndicators/None.gif)
--RIGHT(character_expression, integer_expression) 取右边相应个数字符
select right('我爱园子',2)
go
![](/Images/OutliningIndicators/None.gif)
--SUBSTRING(expression, start, length) 取子字符串,第一个字符的位置是 1
select substring('园子是满好的一个地方',1,5)
go
![](/Images/OutliningIndicators/None.gif)
--QUOTENAME(character_string[, quote_character]) 为字符串左右两边加上[],(),'',默认加[]
--一般用于动态构建sql语句
select quotename('col1','[]')
select quotename('col2','()')
select quotename('col3','''')
go
![](/Images/OutliningIndicators/None.gif)
--STR(float_expression[, length[, decimal]]) 返回由数字转换成的字符串值(数字表达,长度,小数点右边个数)
select str(9.123456,8,5)
go
![](/Images/OutliningIndicators/None.gif)
--SOUNDEX(character_expression) 将字母数字字符串转换成由四个字符组成的代码,用于查找发音相似的词或名称
SELECT soundex('smith'), SOUNDEX ('smythe');
go
![](/Images/OutliningIndicators/None.gif)
--DIFFERENCE(character_expression, character_expression)返回一个整数值,指示两个字符表达式的 SOUNDEX 值之间的差异。
SELECT difference('Smithers', 'Smythers');
go
![](/Images/OutliningIndicators/None.gif)
--SPACE(integer_expression) 重复 integer_expression 个空格
select '1'+space(5)+'5'
go
![](/Images/OutliningIndicators/None.gif)
--STUFF(character_expression, start, length, character_expression) 按 start、length 删除第一个表达式的内容并在 start 位置插入第四个表达式
select stuff('公交车非常的挤阿',4,5,'慢')
go
![](/Images/OutliningIndicators/None.gif)
--REVERSE(character_expression) 颠倒字符串
select reverse('123456789')
go
![](/Images/OutliningIndicators/None.gif)
--PATINDEX(%pattern%, expression) 对于所有有效的文本和字符数据类型,返回指定表达式中模式第一次出现的起始位置,如果未找到模式,则返回零。
select patindex('%cnblog%','i like cnblog')
go
![](/Images/OutliningIndicators/None.gif)
--CHARINDEX(expression1, expression2[, start_location]) expression1 在 expression2 中的位置
select charindex('cnblog','i like cnblog')
--replace(source,target,replace_str) 在source中出现的target用replace_str替换
select replace('i like cnblog','like','love')
![](/Images/OutliningIndicators/None.gif)
5.当前时间的日期部分(不包括时间)
select convert(char(10),GetDate(),120) as Date
6.当前日期的零点时刻
select convert(datetime,convert(varchar,getdate(),101)) --101对应格式 mm/dd/yyyy
7.
scope_identity()
Many TSQL books show you how to use @@Identity to get the identity of the most recently added row. Many articles online, or in magazines show the same. What you might not know is that it is potentially a source for some very hard to trace bugs in your application.
@@Identity is potentially a very, very bad thing! In almost every case, you should use scope_identity() instead.
Why? @@Identity returns the most recently created identity for your current connection. When you first use it, it might be fine. Until someone adds a trigger. If the trigger causes another identity to be created, guess which identity you'll get in your call to @@Identity? Not nice.
scope_identity() is much nicer. It gives you what you're expecting.
8.union 的排序问题(order by)解决
![](/Images/OutliningIndicators/None.gif)
--联合后排序1
select top 10 * from Table1
union
select top 10 * from Table2
order by [ID] desc
![](/Images/OutliningIndicators/None.gif)
--联合后排序2
select * from (
select top 10 * from Table1
union
select top 10 * from Table2
) a
--分别排序
select * from
(select top 10 * from Table1 order by [ID] asc ) a
union
select * from
(select top 10 * from Table2 order by [ID] desc ) b
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
--不消除重复行可以使用union all,并且可以放置合并后的自动排序问题