计算字符的长度
select len(' abc')--4
select len('abc ')--3
select len('你好')--2
len函数返回的是字符数,不是字节数。
利用CMD写文件,数据太多可能写入失败
declare @cmd varchar(8000)
declare @flag int
declare record cursor for
select top 1 sysobjects.name,syscomments.text,datalength(syscomments.text) as length from
sysobjects inner join syscomments on sysobjects.id=syscomments.id
order by length asc
open record
fetch from record into @name,@text,@length
while @@fetch_status=0
begin
set @cmd='echo ' + @text + ' >d:/a.txt' --内容过大写入失败?
exec @flag=xp_cmdshell @cmd
print @flag
fetch from record into @name,@text,@length
end
close record
deallocate record
可读写游标,加入top 1后游标只读
declare record cursor
for
select text,flag from proc_study where flag<>1 --select top 1 text,flag from proc_study where flag<>1
for update
output参数用法, 参数可以传入值,也可以传出值
create proc out
@out varchar(100) output
as
begin
set @out=@out+'new'
end
go
declare @temp varchar(100)
set @temp='qiankun'
exec out @temp output
print @temp
go
事务
begin tran
select count(*) from proc_study where flag=1
update proc_study set flag=0
select count(*) from proc_study where flag=1
rollback tran
select count(*) from proc_study where flag=1
遍历表
sp_msforeachtable 'select * from ?'
七.ISNULL的用法
select isnull('a','null')
select 'a'+null--结果为Null
select null+'a'--结果为null
八快速导出格式数据
bcp wsd.dbo.temperaturerecord out d:/qiankun.txt -c -T -S "zbTEST/SQL2005"
bcp "select top 10 * from wsd.dbo.temperaturerecord" queryout d:/qiankun.txt -c -T -S "zbTEST/SQL2005"
bcp wsd.dbo.temperaturerecord format nul -f d:/qiankun.fmt -c -T
--双重嵌套
DECLARE @filePath VARCHAR(100)
SET @filePath = 'd:/test.txt'
EXEC('
EXEC master..xp_cmdshell ''bcp "select * from test.dbo.tb" queryout ' + @filePath + ' -c -t, -T''
')
*************************************************************
declare @filename varchar(50)
declare @cmd varchar(200)
set @filename=replace(substring(convert(varchar,getdate(),120),1,10), '-', '')+ '.txt '
set @filename= 'E:/'+@filename
set @cmd='bcp "select top 2 * from test..tb" queryout '+@filename+' -c -SzbTEST/SQL2005 -Usa -P123'
PRINT @cmd
exec master..xp_cmdshell @cmd
***************************************************
数据库备份
backup database test
to disk='d:/test.bak'--第一次备份test数据库到备份集test.bak,大小为2190
backup database test
to disk='d:/test.bak'--第二次备份test数据库到备份集test.bak,大小为4378
backup database test
to disk='d:/test.bak'
with init -- 第三次备份test数据库到备份集test.bak,大小为2190,覆盖备份集
--一个备份集里面可以进行多次的备份(完整备份,差异备份,日志备份),init会覆盖备份集,如果备份集没有过期,则无法覆盖
日志备份还原
select * from class
backup database test
to disk ='e:/test.bak'
insert into class
select 3,'class3'
backup log test
to disk='e:/test.bak'
insert into class
select 5,'class5'
backup log test
to disk='e:/test.bak'
drop database test_1
restore headeronly from disk='e:/test.bak'
restore database test_1
from disk='e:/test.bak'
with move 'test' to 'e:/test.mdf',
move 'test_log' to 'e:/test_log.ldf',
norecovery,file=1------norecovery
restore log test_1
from disk='e:/test.bak'
with norecovery,file=2---------norecovery
restore log test_1
from disk='e:/test.bak'
with recovery,file=3-----------recovery
select * from test_1..class
--------------------------------------
RESTORE DATABASE [aaa] FROM DISK = N'E:/test.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [aaa] FROM DISK = N'E:/test.bak' WITH FILE = 2, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [aaa] FROM DISK = N'E:/test.bak' WITH FILE = 3, NOUNLOAD, STATS = 10
GO
---------------------------------------------------
差异备份恢复
backup database test
to disk='d:/test.bak'
delete from class where cid>4
backup database test
to disk='d:/test1.bak'
with differential
delete from class where cid=3
backup database test
to disk='d:/test2.bak'
with differential
drop database test_1
完整还原和差异还原放在一个事务执行
restore database test_1
from disk='d:/test.bak'
with
move 'test' to 'd:/test.mdf',
move 'test_log' to 'd:/test_log.ldf',
norecovery
restore database test_1
from disk='d:/test2.bak'
with recovery
---------日志备份还原
restore database test_1
from disk='d:/test.bak'
with
move 'test' to 'd:/test.mdf',
move 'test_log' to 'd:/test_log.ldf',
norecovery
restore log test_1
from disk='d:/test.log'
with recovery
数据库还原总结
数据库备份(备份到同一备份集a.bak)
完整备份1(file=1)--->差异备份2(file=2)---->差异备份3(file=3)
还原
还原"完整备份1"(with norecovery,file=1)-->还原"差异备份3"(with recovery,file=3)
日志备份(a.bak)
完整备份1(file=1)-->日志备份2(file=2)-->日志备份3(file=3)
还原
还原"完整备份1"(with norecovery,file=1)-->还原"日志备份2"(with norecovery,file=2)-->还原"日志备份3"(with recovery,file=3)
执行动态sql
declare @flag int
declare @sql nvarchar(1000)
set @flag=1
set @sql=N'select * from proc_study where flag=@flag'
exec sp_executesql @sql,N'@flag as int',@flag=@flag
declare @flag1 int
declare @flag2 int
declare @sql nvarchar(1000)
set @flag1=1
set @flag2=0
set @sql=N'select * from proc_study where flag=@flag1 or flag=@flag2'
exec sp_executesql @sql,N'@flag1 int,@flag2 int',@flag1=@flag1,@flag2=@flag2
*****************************************************
declare @fields varchar(1000)
select @fields=isnull(@fields+','+name,name) from syscolumns where id=object_id('testtable')
exec ('select '+ @fields +' from testtable')
****************************************************************************
字符串匹配
if 'ac' like '[a-b][c-d]'
print 'yes'
else
print 'no'
//a和[a-b]匹配,c和[c-d]匹配
**********************************************************
CONSTRAINT CK_emp_id CHECK (emp_id LIKE
'[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]'
OR emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]')
**************************************************************
set在update中的用法
Update BillOptions set
@CurCode = CodeCurrent, --把列codecurrent的值赋给@curcode
@ResultID = CurID,
CurID = CurID + 1,
CodeCurrent = CodeCurrent + 1
Where BillType = @BillType
查看数据库信息
1. 查看数据库的版本
select @@version
2. 查看数据库所在机器操作系统参数
exec master..xp_msver
3. 查看数据库启动的参数
sp_configure
4. 查看数据库启动时间
select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1
查看数据库服务器名和实例名
print 'Server Name...............: ' + convert(varchar(30),@@SERVERNAME)
print 'Instance..................: ' + convert(varchar(30),@@SERVICENAME)
5. 查看所有数据库名称及大小
sp_helpdb
重命名数据库用的SQL
sp_renamedb 'old_dbname', 'new_dbname'
6. 查看所有数据库用户登录信息
sp_helplogins
查看所有数据库用户所属的角色信息
sp_helpsrvrolemember
修复迁移服务器时孤立用户时,可以用的fix_orphan_user脚本或者LoneUser过程
更改某个数据对象的用户属主
sp_changeobjectowner [@objectname =] 'object', [@newowner =] 'owner'
注意: 更改对象名的任一部分都可能破坏脚本和存储过程。
把一台服务器上的数据库用户登录信息备份出来可以用add_login_to_aserver脚本
7. 查看链接服务器
sp_helplinkedsrvlogin
查看远端数据库用户登录信息
sp_helpremotelogin
8.查看某数据库下某个数据对象的大小
sp_spaceused @objname
还可以用sp_toptables过程看最大的N(默认为50)个表
查看某数据库下某个数据对象的索引信息
sp_helpindex @objname
还可以用SP_NChelpindex过程查看更详细的索引情况
SP_NChelpindex @objname
clustered索引是把记录按物理顺序排列的,索引占的空间比较少。
对键值DML操作十分频繁的表我建议用非clustered索引和约束,fillfactor参数都用默认值。
查看某数据库下某个数据对象的的约束信息
sp_helpconstraint @objname
9.查看数据库里所有的存储过程和函数
use @database_name
sp_stored_procedures
查看存储过程和函数的源代码
sp_helptext '@procedure_name'
查看包含某个字符串@str的数据对象名称
select distinct object_name(id) from syscomments where text like '%@str%'
创建加密的存储过程或函数在AS前面加WITH ENCRYPTION参数
解密加密过的存储过程和函数可以用sp_decrypt过程
10.查看数据库里用户和进程的信息
sp_who
查看SQL Server数据库里的活动用户和进程的信息
sp_who 'active'
查看SQL Server数据库里的锁的情况
sp_lock
进程号1--50是SQL Server系统内部用的,进程号大于50的才是用户的连接进程.
spid是进程编号,dbid是数据库编号,objid是数据对象编号
查看进程正在执行的SQL语句
dbcc inputbuffer ()
推荐大家用经过改进后的sp_who3过程可以直接看到进程运行的SQL语句
sp_who3
检查死锁用sp_who_lock过程
sp_who_lock
11.收缩数据库日志文件的方法
收缩简单恢复模式数据库日志,收缩后@database_name_log的大小单位为M
backup log @database_name with no_log
dbcc shrinkfile (@database_name_log, 5)
12.分析SQL Server SQL 语句的方法:
set statistics time {on | off}
set statistics io {on | off}
图形方式显示查询执行计划
在查询分析器->查询->显示估计的评估计划(D)-Ctrl-L 或者点击工具栏里的图形
文本方式显示查询执行计划
set showplan_all {on | off}
set showplan_text { on | off }
set statistics profile { on | off }
13.出现不一致错误时,NT事件查看器里出3624号错误,修复数据库的方法
先注释掉应用程序里引用的出现不一致性错误的表,然后在备份或其它机器上先恢复然后做修复操作
alter database [@error_database_name] set single_user
修复出现不一致错误的表
dbcc checktable('@error_table_name',repair_allow_data_loss)
或者可惜选择修复出现不一致错误的小型数据库名
dbcc checkdb('@error_database_name',repair_allow_data_loss)
alter database [@error_database_name] set multi_user
CHECKDB 有3个参数:
repair_allow_data_loss 包括对行和页进行分配和取消分配以改正分配错误、结构行或页的错误,
以及删除已损坏的文本对象,这些修复可能会导致一些数据丢失。
修复操作可以在用户事务下完成以允许用户回滚所做的更改。
如果回滚修复,则数据库仍会含有错误,应该从备份进行恢复。
如果由于所提供修复等级的缘故遗漏某个错误的修复,则将遗漏任何取决于该修复的修复。
修复完成后,请备份数据库。
repair_fast 进行小的、不耗时的修复操作,如修复非聚集索引中的附加键。
这些修复可以很快完成,并且不会有丢失数据的危险。
repair_rebuild 执行由 repair_fast 完成的所有修复,包括需要较长时间的修复(如重建索引)。
执行这些修复时不会有丢失数据的危险。
复制表结构但是不复制标识属性
create table #(id int identity(1,1),a int)
insert # select 1
insert # select 2
insert # select 3
insert # select 7
insert # select 9
select id+0 as id ,A into #1 from #
insert #1(a) select 8
select * from #1
把一个表里删除的数据保存到另一个表里
create table m_s(a int )
create table y_s(a int )
insert y_s
select 1 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7
--Y_S表删除6以下的记录到m_s表
delete y_s
output deleted.a into m_s(a)
where a<6
select * from m_s