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
