贝网博客

我的分类
流水记事
源码下载
Asp.net
其它
数据库
Javascript
.Net技术
我的相册
友情链接
博客园
CSDN博客
Start0
最新回复
fasdfasdf
[:..
lz这个东西好厉害,我..
哈哈,好照片
不错,以前一直用黄色..
终于找到支持ff的修正..
终于找到支持ff的修正..
新鲜性
看看,试试,好不好使。
好东西一起学习[:img0..
分类 =》数据库
SqlServer应用笔记
发表于:2010-09-06 15:56:42
更新于:2011-12-12 14:49:04

以前用了5,6年的Oracle,对SqlServer已经很生疏了,加个帖子记录日常应用SqlServer中的方法或技巧吧
收集了一些在SqlServer2005中的应用技巧,比如查看数据库版本、获取随机数、获取行号、触发器例子等

1、获取刚刚插入的表的自增ID值:
有3个方法
1、select @@identity :返回当前会话的所有作用域中的任何表最后生成的标识值(全局的,如果你插入表a,又插入表b,此时返回b表的id)
2、select SCOPE_IDENTITY() :返回当前会话和当前作用域中的任何表最后生成的标识值 (是当前会话的,如果你插入表a,又插入表b,此时返回b表的id)
3、select ident_current(‘表名'):返回特定表最后生成的标识值 (不受作用域和会话的限制)
在实际应用中,使用第3种方式在并发比较大的时候,可能会造成问题,例如下面的代码:
insert into table1(xxx) values(xxx); select ident_current('table1');
上面这句sql,在并发用户很多的时候,很有可能返回的并不是刚刚插入的那个id,而有可能是其它线程插入的id(因为
ident_current是跨所有会话的)

2、Update语法的其它格式
今天看到一个Update语句:UPDATE a SET a.name = 'a' FROM tb1 AS a INNER JOIN tb2 AS b ON b.id = a.id
一时非常困惑,后来查询资料才知道,这个语法类似于Oracle的:
UPDATE tb1 a SET a.name = 'a' WHERE EXISTS (SELECT 1 FROM tb2 b WHERE b.id = a.id)
还有DELETE i FROM tb1 as i, tb2 as j WHERE i.id = j.id

3、获取随机数
a.根据Guid作为种子来获取随机数:
declare @g uniqueidentifier
set @g = newid()
select rand(cast(cast(@g as varbinary) as int))
上面代码效果类似于C#里的:new Random(Guid.NewGuid().GetHashCode()).Next()
b.获取1到100之间的随机数(含1和100):
select cast(ceiling(rand() * 100) as int)
c.获取100到1000之间的随机数,在b的结果基础上操作:
declare @begin int
declare @end int
set @begin=100-1
set @end=200
select (cast(ceiling(rand() * (@end-@begin)) as int)+@begin)

4、获取类似于Oracle里的Rownum的行序号:Row_Number()
比如有学生表字段:学号 所属班级
直接按学号排序得到行号的Sql:select row_number() over(order by 学号), 学号, 所属班级 from 学生表
按所属班级分类获取行号的Sql:select row_number() over(partition by 所属班级 order by 学号), 学号, 所属班级 from 学生表

5、数据库报错:从 char 数据类型到 datetime 数据类型的转换导致 datetime 值越界
因为SqlServer支持的DateTime区间为:从 1753 年 1 月 1 日到 9999 年 12 月 31 日,而C#支持的最小值是1年1月1日,所以在用到sql里时,要判断C#的日期是否小于SqlServer的这个区间

6、把select出来的结果集另存到新表中
Select * into NewTableName from table1 where 条件;// 条件为1=2时,只复制表结构,注意:不含主键和索引等信息
在Oracle中是:
Create Table NewTableName as select * from table1 where 条件

7、Cross Apply 和 Outer Apply:
在实际应用中,经常有这种情况,A表和B表是1对多的关系,现在要根据A表从B表匹配出一条记录,比如下面的2个表
学生表:

ID Name
1 张三
2 李四
成绩表:

UserId Subject Score
 1  数学  90
 2  数学  80
 1  语文  95
 2  语文  75

现在要取出姓名和成绩最高的一门课程的SQL:
Select a.Name, c.Subject, c.Score From 学生表 a Outer Apply(Select Top 1 Subject,  Score From 成绩表 b Where b.Userid = a.Id Order By Score) c
当然上面的Sql也可以用Group By实现,但是有些情况下Group By是不好实现的,就可以用Apply实现
而Cross Apply类似于Inner Join,就是Apply后面如果没有记录出来,那么Apply前面的表记录也不显示
Outer Apply类似于Left Join,就是,Apply前面的表记录始终显示

8、触发器的示例:
CREATE TRIGGER [tr_trigName]
   ON  [table1]
   AFTER INSERT,DELETE,UPDATE
AS
BEGIN
 SET NOCOUNT ON;
 
 IF EXISTS(SELECT 1 FROM INSERTED) AND EXISTS(SELECT 1 FROM DELETED)
 BEGIN 
  -- PRINT 'UPDATE的操作'
  -- 因为SqlServer不支持行触发,所以要用CURSOR游标来循环增删改的数据,下面就是循环了
  DECLARE @ID INT, @VALUE VARCHAR(100)
  DECLARE CUR CURSOR LOCAL FORWARD_ONLY FOR SELECT id, value FROM DELETED
  OPEN CUR
  FETCH NEXT FROM CUR INTO @ID, @VALUE
  WHILE @@FETCH_STATUS = 0
  BEGIN 
   SELECT @ID, @VALUE
   FETCH NEXT FROM CUR INTO @ID, @VALUE
  END
  CLOSE CUR
  DEALLOCATE CUR
 END
 ELSE  IF EXISTS(SELECT 1 FROM INSERTED)
 BEGIN
  PRINT 'INSERT的操作'
 END
 ELSE
 BEGIN
  PRINT 'DELETE的操作'
  SELECT * FROM DELETED WHERE id > 100
 END
END

9、查看数据库版本信息:
--查看数据库版本信息 1
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY('edition')
--查看数据库版本信息 2
SELECT @@VERSION
--查看系统信息
exec master..xp_msver
下面是Oracle的查看数据库版本的SQL:
select version FROM Product_component_version Where SUBSTR(PRODUCT,1,6)='Oracle'

10、重建索引:
--查看索引碎片信息
DBCC SHOWCONFIG('表名')
--重建索引
DBCC DBREINDEX(表名, '索引名,空为所有索引', 70)--70为填充因子,可以为空
--重建数据库所有表索引
EXEC sp_msforeachtable 'DBCC DBREINDEX(''?'')'

11、清除Sql Server Management Studio里输入的密码历史记录:
a、清除Sql2008的密码历史:删除文件:C:\Documents and Settings\登录用户名\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin
b、清除Sql2005的密码历史:删除文件:C:\Documents and Settings\登录用户名\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\mru.dat
注意:某些设置可能丢失,请注意备份

12、快速查看表的记录总数(注:对记录总数要求不精确、且没有where条件时可以使用)
--查看单个表
select rows from sysindexes where id = object_id(tablename) and indid <= 1
--查看全部表
SELECT a.id, b.[name], a.ROWS FROM sysindexes a, sys.tables b WHERE a.id = b.[object_id] AND a.indid <=1

13、实现类似MySql里Group_Concat功能:
比如表如下:
1000010 10000
1000010 11000
1000010 11101
1000010 11102
1000010 11111
1000010 11112
下面的sql:
SELECT top 1 lf_id, STUFF((SELECT ',' + CAST(fw_id AS NVARCHAR) FROM SoftFrameworkMap b WHERE  b.lf_id = A.lf_id FOR XML PATH('')),1,1,'') aaa
FROM   SoftFrameworkMap a WHERE lf_id = 1000010
得到如下结果:
1000010 10000,11000,11101,11102,11111,11112
 

发表评论
名称(*):
邮箱:
正文:

©2008 Beinet.cn 版权所有