Sql语句积累

最近在给一家客户做系统维护时，需要查看数据库表的大小，相关的sql如下：

1. exec sp_spaceused '表名' --（SQL统计数据，大量事务操作后可能不准）
2. exec sp_spaceused '表名', true --(准确的表空间大小，但可能会花些统计时间）
3. exec sp_spaceused -- (数据库大小查询）
4. exec sp_MSforeachtable "exec sp_spaceused '?'" --(所有用户表空间表小，SQL统计数据，，大量事务操作后可能不准）
5. exec sp_MSforeachtable "exec sp_spaceused '?',true" --(所有用户表空间表小，大数据库慎用）

数据库创建完后，且正在使用。但还需要添加字段。如下：

6.
--判断是否存在字段
if exists (Select * From syscolumns Where Id=object_id('表') And Name='字段')
print
'PersonID Exists'
else print
'PersonID not Exists'
7.Alter Table TableName Add 字段Varchar(20)          --添加字段
8. Alter Table TableName Drop Column 字段             --删除字段
9. Alter Table TableName Alter Column 字段 类型       --修改字段类型

Alter Table TableName drop column id                     --修改为自增
Alter Table TableName add id int  identity(1,1)

10.

Declare @path varchar(500)
set @path='E:\路径\'+CONVERT(VARCHAR, GETDATE(), 105)+'.Bak'
select @path

BACKUP DATABASE 数据库

TO DISK = @path
WITH FORMAT,
MEDIANAME = 'SQLServerBackups',
NAME = '备份';
GO

11.复制表

法一：select * into b from a where 1<>1
法二：select top 0 * into b from a

12.DATEDIFF(返回两个日期之间间隔)

SELECT DATEDIFF(day,'2012-12-12','2012-12-13') AS DiffDate  返回两个日期之间的天数

SELECT DATEDIFF(HOUR,'2012-12-12','2012-12-13') AS DiffDate  返回两个日期之间的小时

SELECT DATEDIFF(minute,'2012-12-12','2012-12-13') AS DiffDate  返回两个日期之间的分钟

12. ms sql里的控制字符列表

print replace(@msg,'|',char(10))
--Tab 　  char(9)
--换行　　char(10)
--回车　　char(13)
--单引号　char(39)
--双引号　char(34)

13. exists

if exists (select * from sys.databases where name = 'databasename')  判断库是否存在

if exists (select * from sysobjects where id = object_id('tablsename') and OBJECTPROPERTY(id, 'IsUserTable') = 1)  判断表是否存在

简写if exists(select * from sysobjects where name='[表]')  drop table 关系表 

if exists (select * from sysobjects where id = object_id('procname') and OBJECTPROPERTY(id, 'IsProcedure') = 1) 判断存储过程

14.删除重复保留一条

delete table where id in (
select  max(id) from table group by 重复 having count(重复) > 1)

16.將字串值重複指定的次數。  
SELECT REPLICATE ( 'K' ,5 ) --KKKKK

 
16.统计有多少个汉字  
select datalength('kk中国123')-len('kk中国123')  
 
select nullif('kk','kk') --相等为null，否则取第一个
select isnull(null,'kk') --第一个值不为空取第一个,否则为第二个  
select coalesce(null,null,'kk','中国') --返回第一个非空值  

17.小数取整
select CEILING(12.7)    --[13];取大于12.7的最小整数
select CEILING(12.2)    --[13];取大于12.2的最小整数
select FLOOR(12.7)    --[12];取小于12.7的最大整数
select FLOOR(12.2)    --[12];取小于12.2的最大整数
select round(12.77,0)    --[13.00];四舍五入,0位小数
select round(12.24,1)    --[12.20];四舍五入,1位小数

18.按位置替换
select STUFF ( 'ABCDEFG' , 2 , 0 ,'-' )    --[A-BCDEFG];第二个位置,取字符长度为0,替换为-
select STUFF ( 'ABCDEFG' , 2 , 1 ,'b' )    --[AbCDEFG];第二个位置,取字符长度为1,替换为b
select STUFF ( 'ABCDEFG' , 2 , 2 ,'*' )    --[A*DEFG];第二个位置,取字符长度为2,替换为*

19.按相同字符替换
select REPLACE('ABCDEFG','B','b')    --[AbCDEFG];将所有B对应替换为b
select REPLACE('ABCDEFG-Bc','BC','*')    --[A*DEFG-*];将所有BC对应替换为一个*,不区分大小写

20.判断某字符存在
select CHARINDEX('456','123456789')
select CHARINDEX('1','235694526')    --[0];判断1是否存在
select CHARINDEX('1','12314510215985')    --[1];1出现的位置
select CHARINDEX('1','12314510215985',8)    --[10];从第八个字符查找,1在字符串中出现的位置

21.起始位置,支持匹配表达式
select patindex('456', '123456789')    --[0];
select patindex('456%', '123456789')    --[0];
select patindex('%456%', '123456789')    --[4];
select patindex('12%', '123456789')    --[1];
select patindex('__3%', '123456789')    --[1];
select patindex('%[js]%','hsdjgjsrgsdgfjt')--返回j或s中第一个字符出现的位置
select patindex('%[^js]%','ssjjgjsrgsdgfjt')--返回不是j和s外第一个字符出现的位置

22.修改数据表名称

exec sp_rename table_1,table_2;

23.修改数据库名称

alter database 'database_1’  MODIFY NAME = 'database_2';

24.存储过程返回条数

DECLARE
@count INT
select @count=   COUNT(*)  from table
if(@count>1)
SELECT (@count)
else
SELECT 'aaa'

25.sql语句耗时1

set statistics time on;
sql
set statistics time off;
26.sql语句耗时2

declare @q datetime
set @q = getdate()
SELECT *FROM Table
select [耗费时间(毫秒)]=datediff(ms,@q,getdate())

27. select xtype 类型,prec 长度 from where syscolumns Id= object_id('表明') 

28.数值对应字段类型

34 image
35 text
36 uniqueidentifier
48 tinyint
52 smallint
56 int
58 smalldatetime
59 real
60 money
61 datetime
62 float
98 sql_variant
99 ntext
104 bit
106 decimal
108 numeric
122 smallmoney
127 bigint
165 varbinary
167 varchar
173 binary
175 char
189 timestamp
231 sysname
231 nvarchar
239 nchar