SQL学习笔记

---

计算字符的长度

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