业务-字符大小写转换
Lower(字段名) ,Upper(字段名) 业务-字符替换
select replace('abc','b','x'); --不分大小写 业务-转义字符用法
select * from TBUser where UEmial like '%\_%' escape '\'; 业务-datetime fromat
select 100,convert(char(100),getdate(),100) select 101,convert(char(100),getdate(),101) select 102,convert(char(100),getdate(),102) select 103,convert(char(100),getdate(),103) select 104,convert(char(100),getdate(),104) select 105,convert(char(100),getdate(),105) select 106,convert(char(100),getdate(),106) select 107,convert(char(100),getdate(),107) select 108,convert(char(100),getdate(),108) select 109,convert(char(100),getdate(),109) select 110,convert(char(100),getdate(),110) select 111,convert(char(100),getdate(),111) select 112,convert(char(100),getdate(),112) select 113,convert(char(100),getdate(),113) select 114,convert(char(100),getdate(),114) select 120,convert(char(100),getdate(),120) select 121,convert(char(100),getdate(),121) select datepart(yy,getdate()) --year select datepart(mm,getdate()) --month select datepart(dd,getdate()) --day select datepart(hh,getdate()) --hour select datepart(mi,getdate()) --min select datepart(ss,getdate()) --sec select datepart(ms,getdate()) --ms 业务-cursor
declare @idTemp nvarchar(max), @nameTemp nvarchar(max) begin DECLARE MyCursor CURSOR FOR SELECT personid,personname FROM StoreManView OPEN MyCursor FETCH NEXT FROM MyCursor INTO @idTemp,@nameTemp WHILE @@FETCH_STATUS =0 BEGIN print(@idTemp+' '+@nameTemp); FETCH NEXT FROM MyCursor INTO @idTemp,@nameTemp END CLOSE MyCursor DEALLOCATE MyCursor end 业务-while
declare @ADBegin datetime,@ADEnd datetime,@i datetime set @ADBegin=getdate(); set @ADEnd=dateadd(day,10,@ADBegin); set @i=@ADBegin; while @i<@ADEnd begin print(@i); set @i=dateadd(day,1,@i) end 业务-transaction
ALTER PROCEDURE TEST_PRO AS begin TRANSACTION DECLARE @TEST_TEMP INT BEGIN insert into test_table values(1,'a'); insert into test_table values('a','a'); insert into test_table values(1,'a'); END IF @@ERROR>0 ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION 业务-占用CPU最高的语句
SELECT TOP 10 total_worker_time/execution_count AS avg_cpu_cost, plan_handle, execution_count, (SELECT SUBSTRING(text, statement_start_offset/2 + 1, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset END - statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text FROM sys.dm_exec_query_stats ORDER BY [avg_cpu_cost] DESC 列-修改类型
ALTER TABLE tableName ALTER COLUMN columnName nvarchar(50) null 列-添加、删除、重命名
exec sp_rename 'ta.[old]','new','column' --rename column name ALTER TABLE 表名 DROP COLUMN 列名 --delete column ALTER TABLE 表名 ADD 列名 VARCHAR(20) null default '' with values --add column 列-查询所有列
select * from syscolumns where id in (select id from sysobjects where xtype='u' and name='TBB001_Record') 表-获取总数量
select COUNT(*) from sysobjects where xtype='U' 表-判断存在
if(select * from sysobjects where name='objectname') drop table/procedure/... 'objectname' 表-根据大小排序
SELECT a.name, b.rows FROM sysobjects AS a INNER JOIN sysindexes AS b ON a.id = b.id WHERE (a.type = 'u') AND (b.indid IN (0, 1)) ORDER BY b.rows DESC 表-排名次
select (select count(1)+1 from student where a.sno>sno) as 排名,* from student a 表-row to column
create database testdb; go create table testTable ( id int identity(1,1), name varchar(50), course varchar(50), score int ) insert into testTable values('小张','语文',97); insert into testTable values('小张','数学',98); insert into testTable values('小张','英语',99); insert into testTable values('小李','语文',81); insert into testTable values('小李','数学',82); insert into testTable values('小李','英语',83); select name,语文=sum(case when[course]='语文' then score else 0 end), 数学=sum(case when[course]='数学' then score else 0 end), 英语=sum(case when[course]='英语' then score else 0 end) from testTable group by name; 表-random
select top 5 * from test order by newid(); 表-快速去重
with temp as ( select row_number() over(partition by SUrl order by sid) as num from TBShop ) delete from temp where num != 1 表-SQL判断包含中文字、英文、数字
1.包含中文字符: select * from 表名 where 列名 like '%[吖-座]%' 2.包含英文字符: select * from 表名 where 列名 like '%[a-z]%' 3.包含纯数字: select * from 表名 where 列名 like '%[0-9]%' 对象-查看所有库、查看所有表、重命名表
select * from sys.sysdatabases select * from sysobjects where xtype='U' EXEC sp_rename 'old','new' 对象-删除约束
exec('alter table TBServiceFeeDetail drop constraint DF__TBService__SFDFe__1F98B2C1'); 对象-建索引
CREATE INDEX IX_TBC003_Code ON TBC003_Code (CCODE); 库-收缩
DBCC SHRINKDATABASE(库名) 库-判断存在
select * from master.dbo.sysdatabases WHERE name = 'Yuyan' 库-跨库
select * into TBCity from HYAmp..TBZoneCity 库-清空所有表
declare @sql varchar(8000) while (select count(*) from sysobjects where type='U')>0 begin SELECT @sql='drop table ' + name FROM sysobjects WHERE (type = 'U') ORDER BY 'drop table ' + name exec(@sql) end 库-备份
BACKUP DATABASE [YuyanDemo] TO DISK = N'D:\Project\YuyanDemo\YuyanDemoSolution\Web\AD\YuyanDemo.bak' WITH NOFORMAT, NOINIT, NAME = N'备注', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO /* 备份 */ 2 backup database Test to disk='D:/Test.bak' 3 /* 还原 */ 4 restore database Test from disk='D:/Test.bak' Sql Server 的基本备份方法 数据库完整备份 backup database 数据库名 to disk='数据库备份地址' with noinit,name='备份名' 数据库差异备份 backup database 数据库名 to disk='数据库备份地址' with differential noinit,name='备份名' 日志备份 backup log 数据库名 to disk='日志备份地址' with noinit,name='日志名' 数据库恢复 restore database 数据库名 from disk='数据库备份地址' 说明： with init：覆盖原有的档案，默认为附加（with noinit）至原有档案 库-查询备份模式
SELECT recovery_model,recovery_model_desc FROM sys.databases WHERE name ='数据库名' 库-SQL2005清空日志
DUMP TRANSACTION [Yuyan] WITH NO_LOG 库-SQL2008清空日志
--第一步获取日志文件名 USE [DBName] GO SELECT name FROM SYS.database_files WHERE type_desc='LOG' --第二步执行清空代码 USE[master] GO ALTER DATABASE [DBName] SET RECOVERY SIMPLE WITH NO_WAIT GO ALTER DATABASE [DBName] SET RECOVERY SIMPLE --简单模式 GO USE [DBName] GO DBCC SHRINKFILE (N'DBName_log' , 11, TRUNCATEONLY) GO USE[master] GO ALTER DATABASE [DBName] SET RECOVERY FULL WITH NO_WAIT GO ALTER DATABASE [DBName] SET RECOVERY FULL --还原为完全模式 GO 服务器-connStr
server=112.124.124.13\SQL2008;database=BCP;uid=sa;pwd=XXXXXX server=.;database=BCP;integrated security=true 服务器-跨服务器
exec sp_addlinkedserver 'ServerName','','SQLOLEDB','192.168.1.131' exec sp_addlinkedsrvlogin 'ServerName','false',null,'sa','888888' go select * into ood_VWDCSalesHistory from ServerName.vwdc.dbo.ood_VWDCSalesHistory