SQL SERVER 经验总结 [未完待续..]
/***********************************************************************************************************************************/
在安装Sql或sp补丁的时候系统提示之前有挂起的安装操作，要求重启，这里往往重启无用，解决办法：
到HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager
删除PendingFileRenameOperations
***********************/
/*************************************************************************
***********************SQL语句加密字段函数
************/
Create table #t_user
(uname varchar(20),
Pwd varbinary(256))
go
/************************************************
加密函数pwdencrypt(varbinary value)
**********/
insert into #t_user values('a',pwdencrypt('123'))
insert into #t_user values('b',pwdencrypt('321'))
go
select * from #t_user
/************************************************
检查明文是否与加密字段相等函数pwdcompare(varbinary value)
**********/
select pwdcompare('123',(select Pwd from #t_user where uname='a'))
/************************************************
**********************列转行
***********/
create table score
(id int identity(1,1) primary key,
sid int,
course nvarchar(10),
result int
)
go
insert into score values('2005001','语文','80')
insert into score values('2005001','数学','90')
insert into score values('2005001','英语','85')
insert into score values('2005002','语文','89')
insert into score values('2005002','数学','87')
insert into score values('2005002','英语','70')
go
select * from score
go
select sid,语文=isnull(sum(case course when '语文' then result end),0),
数学=isnull(sum(case course when '数学' then result end),0),
英语=isnull(sum(case course when '英语' then result end),0)
from score
group by sid
/********************************************************
**********************************行转列
*********/
drop table score
go
create table score
(
id int identity(1,1) primary key ,
sid int,
Chinese int,
math int,
english int
)
go
insert into score values('2005001','80','90','85')
insert into score values('2005002','89','87','70')
go
select * from score
select id,sid, Chinese '语文-英语-数学' from score
union
select id,sid, math from score
union
select id,sid, english from score
go
/*****************************************************************
*************************SQL SERVER支持的所有排序规则
****************/
select * from :: fn_helpcollations()
go
/****************************************************
*************************按姓氏笔画排序
************/
drop table #t_user
go
Create table #t_user(
id int identity(1,1) primary key,
uname varchar(20)
)
go
insert into #t_user values('张三')
insert into #t_user values('李四')
insert into #t_user values('王五')
go
select * from #t_user
select * from #t_user order by uname collate chinese_prc_ci_as
go
/******************************************************
**************************随即取出10条数据
****************/
select top 10 * from #t_user order by newid()
/******************************************************
*************************复制T2表结构到新表
***************/
select top 0 * into T1 from T2
/****************************************************************************
**********************************************批量删除
*****************/
delete from #t_user where charindex(cast([id] as varchar),'5,6,8,')>0
select * from #t_user where charindex(cast([id] as varchar),('6,2,3'),0)>0
/**************************************************************************************
*******************************************删除某表重复数据
***********************************/
Delete from #t_user where id not in (select max(id) from #t_user group by uname)
最新评论