1.加载驱动：
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
DriverManager.getConnection("jdbc:sqlserver://192.168.0.63:1433;DatabaseName=test","sa","hnzsfwq");
2.分页：
--top not in方式 select top 条数 * from tablename where Id not in (select top 条数*页数 Id from tablename) --ROW_NUMBER() OVER()方式 select * from ( select *, ROW_NUMBER() OVER(Order by Id ) AS RowNumber from tablename ) as b where RowNumber BETWEEN 当前页数-1*条数 and 页数*条数 --offset fetch next方式 --SQL2012以上的版本才支持 select * from tablename order by Id offset 页数 row fetch next 条数 row only
3.查询数据库有几张这张表：
SELECT COUNT(1) AS NUM FROM [INFORMATION_SCHEMA].[TABLES]WHERE TABLE_NAME= 'TABLENAME'
4.sql文件，动态判断表中字段是否重复：
IF NOT EXISTS(
SELECT *
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE [TABLE_NAME] = '$tableName$' AND COLUMN_NAME = '$template_ID$'
)
BEGIN
ALTER TABLE [$tableName$]
ADD [$template_ID$] [INT]
END
IF NOT EXISTS(
SELECT *
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE [TABLE_NAME] = '$tableName$' AND COLUMN_NAME = '$table_Name$'
)
BEGIN
ALTER TABLE [$tableName$]
ADD [$table_Name$] varchar(1000)
END
5. 多条件查询：
SELECT
SOURCE_TBL_NAME
FROM
[TBL_TABLE_MAPPING]
WHERE
TEMP_ID=1
AND TARGET_TBL_NAME !='query'
AND TABLEORSQL='sql'
AND DB_TYPE=0
6.循环：
Declare @ｉ INT
SET @i = 5001
WHILE @i <50000
Begin
INSERT INTO s_student (id)
VALUES
(@i)
SET@i=@i+1
END
7.查询某个表有多少列：
SELECT
COUNT(1)
FROM
SYSCOLUMNS
WHERE
ID=OBJECT_ID('表名')
8.查询当前时间：
SELECT GETDATE()
9.批量插入：
URL:
192.168.0.70:9898/SpagoBIETL/unitTemplateMapping/saveMapping.shtml?mappings[0].unitCode=110&mappings[0].templateID=111
前端传的是一个数组
Controller:
参数：实体类（包含 list<UnitTemplateMapping> mapping 属性）
list<UnitTemplateMapping> mapping
<insert id="insertBatch"> insert into TBL_UNIT_TEMAPLATE_MAPPING(UNIT_CODE,SOFTWARE_ID,TEMPLATE_ID) VALUES <foreach collection="list" item="mapping" separator=","> (#{mapping.unitCode},#{mapping.softwareId},#{mapping.templateId}) </foreach> </insert>
foreach的主要用在构建in条件中，它可以在SQL语句中进行迭代一个集合。foreach元素的属性主要有item，index，collection，open，separator，close。item表示集合中每一个元素进行迭代时的别名，index指定一个名字，用于表示在迭代过程中，每次迭代到的位置，open表示该语句以什么开始，separator表示在每次进行迭代之间以什么符号作为分隔符，close表示以什么结束，在使用foreach的时候最关键的也是最容易出错的就是collection属性，该属性是必须指定的，但是在不同情况下，该属性的值是不一样的，主要有一下3种情况：
- 如果传入的是单参数且参数类型是一个List的时候，collection属性值为list .
- 如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array .
- 如果传入的参数是多个的时候，我们就需要把它们封装成一个Map了，当然单参数也可以封装成map，实际上如果你在传入参数的时候，在MyBatis里面也是会把它封装成一个Map的，map的key就是参数名，所以这个时候collection属性值就是传入的List或array对象在自己封装的map里面的key.
<!--List:forech中的collection属性类型是List,collection的值必须是:list,item的值可以随意,Dao接口中参数名字随意 --> <select id="getEmployeesListParams" resultType="Employees"> select * from EMPLOYEES e where e.EMPLOYEE_ID in <foreach collection="list" item="employeeId" index="index" open="(" close=")" separator=","> #{employeeId} </foreach> </select> <!--Array:forech中的collection属性类型是array,collection的值必须是:list,item的值可以随意,Dao接口中参数名字随意 --> <select id="getEmployeesArrayParams" resultType="Employees"> select * from EMPLOYEES e where e.EMPLOYEE_ID in <foreach collection="array" item="employeeId" index="index" open="(" close=")" separator=","> #{employeeId} </foreach> </select> <!--Map:不单单forech中的collection属性是map.key,其它所有属性都是map.key,比如下面的departmentId --> <select id="getEmployeesMapParams" resultType="Employees"> select * from EMPLOYEES e <where> <if test="departmentId!=null and departmentId!=''"> e.DEPARTMENT_ID=#{departmentId} </if> <if test="employeeIdsArray!=null and employeeIdsArray.length!=0"> AND e.EMPLOYEE_ID in <foreach collection="employeeIdsArray" item="employeeId" index="index" open="(" close=")" separator=","> #{employeeId} </foreach> </if> </where> </select>
10.存储过程（游标）：
双层游标
USE [wqb_upgrade_test] GO /****** Object: StoredProcedure [dbo].[addFinancialBatchExecuteTask] Script Date: 2018/1/10 18:48:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[addFinancialBatchExecuteTask] AS BEGIN create table unitCodeAccountCode(unitCode varchar(100),accountCode varchar(100)); DECLARE @INTERMEDIATEDB_NAME varchar(1000) DECLARE @belong_unit_code varchar(1000) DECLARE @DATA_YEAR varchar(1000) DECLARE My_Cursor CURSOR FOR select distinct w.belong_unit_code as belongUnitCode, w.DATA_YEAR as dataYear,s.INTERMEDIATEDB_NAME as interMediatedb from Spagobietl.dbo.TBL_DATACOLLECTION as s, Spagobietl.dbo.TBL_ORIGINAL_DATA_REGISTER as w, ETL_COLLECTION as c where s.exec_stat=2 and s.uuid=w.uuid and s.INTERMEDIATEDB_NAME is not null and s.INTERMEDIATEDB_NAME in (select name from sys.databases) OPEN My_Cursor; FETCH NEXT FROM My_Cursor INTO @belong_unit_code,@DATA_YEAR,@INTERMEDIATEDB_NAME WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @fname varchar(1000) set @fname = @INTERMEDIATEDB_NAME DECLARE @s Nvarchar(1000) set @s = 'select 会计电子账簿编号 as accountCode ,单位代码 as unitCode from ['+@INTERMEDIATEDB_NAME+'].dbo.基础表_财务类_电子账簿信息表 '; DECLARE @Mx varchar(1000) set @Mx = 'DECLARE My_Cursor2 CURSOR '+ //此处须拼字符串，执行sql 'FOR ( '+@s+' )'+ // for的括号里不能用 exec 'OPEN My_Cursor2; '+ 'DECLARE @accountCode varchar(8000); '+ 'DECLARE @unitCode varchar(8000) '+ 'FETCH NEXT FROM My_Cursor2 INTO @accountCode ,@unitCode '+ ' WHILE @@FETCH_STATUS = 0 '+ 'BEGIN '+ 'FETCH NEXT FROM My_Cursor2 INTO @accountCode,@unitCode '+ 'insert into unitCodeAccountCode(unitCode,accountCode) values(@unitCode,@accountCode); '+ 'END '+ 'CLOSE My_Cursor2; '+ 'DEALLOCATE My_Cursor2;' execute(@Mx) FETCH NEXT FROM My_Cursor INTO @belong_unit_code,@DATA_YEAR,@INTERMEDIATEDB_NAME END CLOSE My_Cursor; DEALLOCATE My_Cursor; DECLARE @methodModelId int;--方法ID DECLARE @unitCode varchar(8000);--单位代码 DECLARE @year int;--年份 DECLARE @accountCode varchar(1000);--账套编码 DECLARE @methodInstanceId int;--方法实例ID DECLARE UnitCodeYearMethodIdCursor CURSOR FOR select methodModelId,belong_unit_code,DATA_YEAR,accountCode from ( SELECT methodModelID FROM auditMethodModel m JOIN auditItem i ON m.itemID = i.itemID JOIN dbo.auditCategory c ON c.CategoryID = i.CategoryID WHERE c.isEnable = 1 and realTime = 1 AND ( ( i.isEnable = 1 AND m.isEnable = 1 ) OR ( i.isEnable = 1 AND m.isEnable IS NULL ) OR ( i.isEnable IS NULL ) ) AND ( m.externalDataSource IS NULL OR LEN(RTRIM(m.externalDataSource)) = 0 ) AND ( m.methodType = 'model' OR m.methodType IS NULL ) AND ( m.autidType != '人工' ) AND ( m.isPass = 1 ) AND NOT EXISTS ( SELECT TOP 1 1 FROM paramModel p WHERE m.methodModelID = p.methodID AND p.paramName != '会计核算账套' AND p.paramName != '预算年度' AND p.paramName != '单位编码' ) AND EXISTS ( SELECT TOP 1 1 FROM paramModel p WHERE m.methodModelID = p.methodID AND p.paramName = '会计核算账套' ) ) as t_methodId cross join ( --查出年份和单位账号，paramInstance需要的 select distinct w.belong_unit_code as belong_unit_code, w.DATA_YEAR as DATA_YEAR,s.INTERMEDIATEDB_NAME as interMediatedb from Spagobietl.dbo.TBL_DATACOLLECTION as s, Spagobietl.dbo.TBL_ORIGINAL_DATA_REGISTER as w, ETL_COLLECTION as c where s.exec_stat=2 and s.uuid=w.uuid and s.INTERMEDIATEDB_NAME is not null and s.INTERMEDIATEDB_NAME in (select name from sys.databases) --and s.id not in (select distinct collid from dbo.ETL_COLLECTION where collid is not null) ) as t_unitcodeyear cross join ( SELECT distinct unitCode ,accountCode FROM unitCodeAccountCode ) as ucc --打开游标 OPEN UnitCodeYearMethodIdCursor; --遍历数据开始 FETCH NEXT FROM UnitCodeYearMethodIdCursor INTO @methodModelId,@unitCode,@year,@accountCode; WHILE @@FETCH_STATUS = 0 BEGIN --插入方法实例 insert into dbo.autidMethodInstance( methodModelID, executionState, isDeleted ) values( @methodModelId, 'queuing', 0 ) SELECT @methodInstanceId = @@IDENTITY; --插入批量任务表 insert into dbo.batchAudit( accountCode, modelId, unitCode, year, methodInstanceId, executionState, auditType, isEnable, addDate ) values( @accountCode, @methodModelId, @unitCode, @year, @methodInstanceId, 'queuing', 'business', 1, CONVERT(VARCHAR,GETDATE(),23) ) --添加参数实例 DECLARE @paramModelId INT; DECLARE @paramModelName VARCHAR(100); DECLARE paramModelCursor CURSOR FOR SELECT paramModelID , paramName FROM dbo.paramModel WHERE methodID = @methodModelId; OPEN paramModelCursor; FETCH NEXT FROM paramModelCursor INTO @paramModelId,@paramModelName; WHILE @@FETCH_STATUS = 0 BEGIN IF @paramModelName = '单位编码' BEGIN INSERT dbo.paramInstance ( methodInstaceID , paramModelID , paramValue , paramName ) VALUES ( @methodInstanceId , @paramModelId , @unitCode , '单位编码' ); END; ELSE IF @paramModelName = '预算年度' BEGIN INSERT dbo.paramInstance ( methodInstaceID , paramModelID , paramValue , paramName ) VALUES ( @methodInstanceId , @paramModelId , @year , '预算年度' ); END; FETCH NEXT FROM paramModelCursor INTO @paramModelId,@paramModelName; END; CLOSE paramModelCursor; DEALLOCATE paramModelCursor; FETCH NEXT FROM UnitCodeYearMethodIdCursor INTO @methodModelId,@unitCode,@year,@accountCode; END; --关闭游标 CLOSE UnitCodeYearMethodIdCursor; --释放游标 DEALLOCATE UnitCodeYearMethodIdCursor; insert into dbo.ETL_COLLECTION( COLLID, ADDDATE ) select distinct s.id,GETDATE() from Spagobietl.dbo.TBL_DATACOLLECTION s,Spagobietl.dbo.TBL_ORIGINAL_DATA_REGISTER w where s.status=3 and s.uuid=w.uuid and s.id not in (select distinct collid from dbo.ETL_COLLECTION where collid is not null) exec('drop table [dbo].[unitCodeAccountCode]') END
游标实例：
USE [wqb_upgrade_test] GO /****** Object: StoredProcedure [dbo].[addBusinessBatchExecuteTask] Script Date: 01/09/2018 21:13:56 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[addBusinessBatchExecuteTask] AS BEGIN DECLARE @methodModelId int;--方法ID DECLARE @unitCode varchar(8000);--单位代码 DECLARE @year int;--年份 DECLARE @methodInstanceId int;--方法实例ID DECLARE UnitCodeYearMethodIdCursor CURSOR FOR select methodModelId,belong_unit_code,DATA_YEAR from ( SELECT methodModelID FROM auditMethodModel m JOIN auditItem i ON m.itemID = i.itemID JOIN dbo.auditCategory c ON c.CategoryID = i.CategoryID WHERE c.isEnable = 1 AND realTime = 1 AND ( ( i.isEnable = 1 AND m.isEnable = 1 ) OR ( i.isEnable = 1 AND m.isEnable IS NULL ) OR ( i.isEnable IS NULL ) ) AND ( m.externalDataSource IS NULL OR LEN(RTRIM(m.externalDataSource)) = 0 ) AND ( m.methodType = 'model' OR m.methodType IS NULL ) AND ( m.autidType != '人工' ) AND ( m.isPass = 1 ) AND NOT EXISTS ( SELECT TOP 1 1 FROM paramModel p WHERE m.methodModelID = p.methodID AND p.paramName != '会计核算账套' AND p.paramName != '预算年度' AND p.paramName != '单位编码' ) AND EXISTS ( SELECT TOP 1 1 FROM paramModel p WHERE m.methodModelID = p.methodID AND p.paramName = '会计核算账套' ) ) as t_methodId cross join ( --查出年份和单位账号，paramInstance需要的 select distinct w.belong_unit_code as belong_unit_code,w.DATA_YEAR as DATA_YEAR from Spagobietl.dbo.TBL_DATACOLLECTION s,Spagobietl.dbo.TBL_ORIGINAL_DATA_REGISTER w where s.status=3 and s.uuid=w.uuid --and s.id not in (select distinct collid from dbo.ETL_COLLECTION where collid is not null) ) as t_unitcodeyear --打开游标 OPEN UnitCodeYearMethodIdCursor; --遍历数据开始 FETCH NEXT FROM UnitCodeYearMethodIdCursor INTO @methodModelId,@unitCode,@year; WHILE @@FETCH_STATUS = 0 BEGIN --插入方法实例 insert into dbo.autidMethodInstance( methodModelID, executionState, isDeleted ) values( @methodModelId, 'queuing', 0 ) SELECT @methodInstanceId = @@IDENTITY; --插入批量任务表 insert into dbo.batchAudit( modelId, unitCode, year, methodInstanceId, executionState, auditType, isEnable, addDate ) values( @methodModelId, @unitCode, @year, @methodInstanceId, 'queuing', 'business', 1, CONVERT(VARCHAR,GETDATE(),23) ) --添加参数实例 DECLARE @paramModelId INT; DECLARE @paramModelName VARCHAR(100); DECLARE paramModelCursor CURSOR FOR SELECT paramModelID , paramName FROM dbo.paramModel WHERE methodID = @methodModelId; OPEN paramModelCursor; FETCH NEXT FROM paramModelCursor INTO @paramModelId,@paramModelName; WHILE @@FETCH_STATUS = 0 BEGIN IF @paramModelName = '单位编码' BEGIN INSERT dbo.paramInstance ( methodInstaceID , paramModelID , paramValue , paramName ) VALUES ( @methodInstanceId , @paramModelId , @unitCode , '单位编码' ); END; ELSE IF @paramModelName = '预算年度' BEGIN INSERT dbo.paramInstance ( methodInstaceID , paramModelID , paramValue , paramName ) VALUES ( @methodInstanceId , @paramModelId , @year , '预算年度' ); END; FETCH NEXT FROM paramModelCursor INTO @paramModelId,@paramModelName; END; CLOSE paramModelCursor; DEALLOCATE paramModelCursor; FETCH NEXT FROM UnitCodeYearMethodIdCursor INTO @methodModelId,@unitCode,@year; END; --关闭游标 CLOSE UnitCodeYearMethodIdCursor; --释放游标 DEALLOCATE UnitCodeYearMethodIdCursor; insert into dbo.ETL_COLLECTION( COLLID, ADDDATE ) select distinct s.id,GETDATE() from Spagobietl.dbo.TBL_DATACOLLECTION s,Spagobietl.dbo.TBL_ORIGINAL_DATA_REGISTER w where s.status=3 and s.uuid=w.uuid and s.id not in (select distinct collid from dbo.ETL_COLLECTION where collid is not null) END
11.创建笛卡尔积向某张表插入数据：
insert into [dbo].[TBL_UNIT_FINANCIAL] SELECT * FROM ( SELECT UNIT_CODE FROM TBL_UNIT WHERE uk_number IS NOT NULL)B CROSS JOIN ( SELECT '财政专户账' AS A, '专户账' AS Z UNION SELECT '工会账' AS A , '工会账' AS Z UNION SELECT '机关(事业)财务账' AS A , '财务账' AS Z UNION SELECT '基建账' AS A , '基建账' AS Z UNION SELECT '其他' AS A , '其他' AS Z UNION SELECT '系统财务账' AS A, '系统账' AS Z )A
12.差集（查询A内，不包含B的数据）
union（联系结果集，自带去重）
13.java调用存储过程：
14.CASE when ，not EXISTS
select distinct t.belong_unit_code,t.belong_unit_name,t.FINANCIAL_DATD_TYPE, ( case when msg like'%还原失败%' then '还原失败' when msg like'%采集失败%' then '采集失败' when msg like'%服务器信息不完整%' then '服务器信息不完整,无法自动采集' when msg like'%模板匹配失败%' then '模板匹配失败:未匹配到模板' when msg like'%无法解析%' then '无法解析备份类型：通用软件备份' when msg like'%文件服务器上无此文件%' then '文件服务器上无此文件' when msg like'%服务器中断执行%' then '服务器中断执行' when msg like'%压缩文件不合法%' then '压缩文件不合法' when msg like'%上报客户端文件解密出错:解密失败%' then '上报客户端文件解密出错: 解密失败' end ) msg FROM [dbo].[TBL_ORIGINAL_DATA_REGISTER] t WHERE CONVERT(VARCHAR,t.submit_time,23) BETWEEN @startDate AND @endDate AND t.FINANCIAL_DATD_TYPE IS NOT NULL AND NOT EXISTS ( SELECT 1 FROM [dbo]. [TBL_ORIGINAL_DATA_REGISTER] b WHERE msg IS NULL AND t.submit_unit_code=b.belong_unit_code AND CONVERT(VARCHAR,b.submit_time,23) BETWEEN @startDate AND @endDate AND b.FINANCIAL_DATD_TYPE = t.FINANCIAL_DATD_TYPE )
15.游标抓取，修改数据，标准游标
标准游标：
DECLARE @cloumn varchar(8000); DECLARE @type varchar(8000); DECLARE @is varchar(8000); DECLARE classCursor CURSOR FOR SELECT 字段,类型,是否主键 FROM dbo.班级表 OPEN classCursor --将查询出来的数据放入声明的变量 FETCH NEXT FROM classCursor INTO @cloumn,@type,@is; WHILE @@FETCH_STATUS = 0 BEGIN SELECT @cloumn+',' --取出下一条数据 FETCH NEXT FROM classCursor INTO @cloumn,@type,@is; END --关闭游标 CLOSE classCursor --释放游标 DEALLOCATE classCursor ;
游标抓取，修改数据
DECLARE @dwszdxzqhdm_code varchar(100); DECLARE @dwszdxzqhmc_name varchar(100); DECLARE @unit_name varchar(100); DECLARE zz CURSOR FOR SELECT t1.单位所在地行政区划名称 AS dwszdxzqhmc_name,t1.单位所在地行政区划代码 AS dwszdxzqhdm_code,t2.unit_name FROM [czcw_gh].[dbo].[基础表_部门决算_单位信息表] t1,[Spagobietl].[dbo].[tbl_unit] t2 WHERE t1.单位名称 = t2.unit_name AND t2.uk_number IS NOT NULL AND LEN(t2.uk_serial_number)>0 OPEN zz; FETCH NEXT FROM zz INTO @dwszdxzqhdm_code,@dwszdxzqhmc_name,@unit_name; WHILE @@FETCH_STATUS = 0 BEGIN UPDATE [Spagobietl].[dbo].[tbl_unit] SET [dwszdxzqhdm_code]=@dwszdxzqhdm_code,[dwszdxzqhmc_name]=@dwszdxzqhmc_name WHERE [unit_name]=@unit_name FETCH NEXT FROM zz INTO @dwszdxzqhdm_code,@dwszdxzqhmc_name,@unit_name; END; CLOSE zz; DEALLOCATE zz;
16.stuff , replace
17.多行合并一行：
select stuff(
(select n.errorInfo+''
from(
SELECT info as errorInfo
FROM [Spagobietllog].[dbo].[TBL_LOG_DATACOLLECTION] ld
WHERE BIZID IN(
SELECT TOP 1 dc.ID
FROM spagobietl.dbo.TBL_DATACOLLECTION dc
WHERE dc.UUID = #{errorUUID, jdbcType=VARCHAR} AND dc.EXEC_STAT = 3
ORDER BY dc.ID
)
AND ld.LEVEL = 'ERROR' AND ld.INFO IS NOT NULL AND LEN(ld.INFO)>0
AND ld.INFO NOT LIKE '%被取消%'
) as n for xml path('')),1,1,'') as info
select id,valuelist=stuff((select ','+value from tb as b where b.ID=a.ID for xml path('')),1,1,'')
from tb as a
group by id
SELECT STUFF(( SELECT ',' + 字段 FROM dbo.班级表 AS b FOR XML PATH('')), 1, 1, '') valuelist
18.开窗，分区函数：
19.创建中间表，创建自增字段：
--#tableName 中间表 IDENTITY(INT,初始值，增加值) select RowNum=IDENTITY(INT,16,6),Name into #T from dbo.Test select * from #T DROP TABLE #T;
20.查询出表结构，游标拼创建表的sql
DECLARE @cloumn varchar(8000); DECLARE @type varchar(8000); DECLARE @is varchar(8000); DECLARE @allColumn varchar(8000); DECLARE @tempColumn varchar(8000); DECLARE @tempCore VARCHAR(8000) SET @tempCore = '' SET @tempColumn ='' SET @allColumn = 'create table aa(' DECLARE classCursor CURSOR FOR SELECT 字段,类型,是否主键 FROM dbo.班级表 OPEN classCursor FETCH NEXT FROM classCursor INTO @cloumn,@type,@is; WHILE @@FETCH_STATUS = 0 BEGIN IF(@is='是') begin set @tempCore ='primary key' END ELSE BEGIN set @tempCore ='' end SET @tempColumn = @tempColumn+@cloumn+' '+@type+' '+@tempCore+',' FETCH NEXT FROM classCursor INTO @cloumn,@type,@is; END CLOSE classCursor DEALLOCATE classCursor ; SELECT @allColumn+LEFT(@tempColumn,LEN(@tempColumn)-1)+')'
21. 获得当前用户和所在用户下的所有表名：
--获得当前用户和所在用户下的所有表名 SELECT o2.name 用户名,o.name 表名 FROM sys.sysobjects o LEFT JOIN sys.sysusers o2 ON o.uid = o2.uid WHERE o.type = 'U'
22.UNION联结结果集：（每列的个数必须一样且类型必须相同，UNION去重，UNION ALL不去重）
SELECT a.年份,SUM(a.[每月数据量大小（byte）])/1024.0/1024 每年数据量总大小, max(a.[每月数据量大小（byte）])/1024.0/1024 每月最大数据量, min(a.[每月数据量大小（byte）])/1024.0/1024 每月最小数据量, avg(a.[每月数据量大小（byte）])/1024.0/1024 平均每月数据量大小 FROM ( SELECT *, 't1' as 'tname' FROM [郑大二附院住院部].[dbo].[brryxx表$] union all select *, 't2' as 'tname'from [郑大二附院住院部].[dbo].[yje表$] union all select *, 't3' as 'tname'from [郑大二附院住院部].[dbo].[zyb_cybrjs表$] union all select *, 't4' as 'tname' from [郑大二附院住院部].[dbo].[zyb_sflsz_cybr表$] union all select *, 't5' as 'tname' from [郑大二附院门诊部].dbo.mz_tb_brjzxx表$ union all select *, 't6' as 'tname' from 郑大二附院门诊部.dbo.mzys_cfjbxx_fy表$ union all select *, 't7' as 'tname' from 郑大二附院门诊部.dbo.mzys_cfjbxx_zf表$ union all select *, 't8' as 'tname' from 郑大二附院门诊部.dbo.sflsz_mx表$ union all select *, 't8' as 'tname' from 郑大二附院药事部.dbo.ckd表$ union all select *, 't8' as 'tname' from 郑大二附院药事部.dbo.rkd表$ union all select *, 't8' as 'tname' from 郑大二附院药事部.dbo.yf_cfjbxx_fy表$ union all select *, 't8' as 'tname' from 郑大二附院药事部.dbo.yf_rkd表$ union all select *, 't8' as 'tname' from 郑大二附院药事部.dbo.ypcc表$ )A group by a.年份
23.双层游标定义创建sql语句：
DECLARE @tableName varchar(8000) DECLARE createCursor CURSOR FOR select table_name from INFORMATION_SCHEMA.TABLES OPEN createCursor FETCH NEXT FROM createCursor INTO @tableName; WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @str VARCHAR(8000); DECLARE @s Nvarchar(1000) set @s = 'SELECT 字段英文名,数据类型,是否主键 FROM '+@tableName ; --EXEC(@s) set @str = 'DECLARE @cloumn varchar(8000);'+ 'DECLARE @type varchar(8000);'+ 'DECLARE @is varchar(8000);'+ 'DECLARE @allColumn varchar(8000);'+ 'DECLARE @tempColumn varchar(8000);'+ 'DECLARE @tempCore VARCHAR(8000);'+ 'SET @tempCore = '''';'+ 'SET @tempColumn ='''';'+ 'SET @allColumn = ''create table '''''+@tableName+'''''('';'+ 'DECLARE classCursor CURSOR '+ 'FOR '+ '('+@s+')' + 'OPEN classCursor '+ 'FETCH NEXT FROM classCursor INTO @cloumn,@type,@is;'+ 'WHILE @@FETCH_STATUS = 0 '+ 'BEGIN '+ 'IF(@is=''是'')'+ 'begin '+ 'set @tempCore =''primary key auto_increment '';'+ 'END '+ 'ELSE '+ 'BEGIN '+ 'set @tempCore ='' '';'+ 'end '+ 'SET @tempColumn = @tempColumn+@cloumn+'' ''+@type+'' ''+@tempCore+'','''+ 'FETCH NEXT FROM classCursor INTO @cloumn,@type,@is;'+ 'END '+ 'CLOSE classCursor;'+ 'DEALLOCATE classCursor ;'+ 'SELECT replace(replace(@allColumn+LEFT(@tempColumn,LEN(@tempColumn)-1)+'')'',''$'',''''),'''''''','''');' exec(@str) FETCH NEXT FROM createCursor INTO @tableName; END CLOSE createCursor; DEALLOCATE createCursor ;
24.sqlserver定时作业
use master GO DECLARE @jobid UNIQUEIDENTIFIER --作业id DECLARE @jobname sysname --作业名称 DECLARE @dataBaseNmae VARCHAR(600) --数据库名称 DECLARE @savePath VARCHAR(600) --保存目录 DECLARE @freq_type_config INT --执行的频率周 DECLARE @freq_recurrence_factor_config INT --执行间隔两周 DECLARE @freq_interval_config INT --每天执行 DECLARE @freq_subday_type_config INT --，重复方式,0x1=在指定的时间,0x4=多少分钟,--重复方式,0x1=在指定的时间,0x4=多少分钟,0x8=多少小时执行一次。x1和@active_start_time一起使用，@active_start_time指定开始执行的时间，代表在@freq_type指定的频率间隔内只执行一次--若是x4或x8，只要指定@freq_subday_interval，@freq_subday_interval代表每多少分钟（当@freq_subday_type=0x4）或小时（当@freq_subday_type=0x8）执行的次数 DECLARE @freq_subday_interval_config INT --重复周期数,这里每小时执行一次 DECLARE @active_start_date_config INT --作业执行的开始日期,为NULL时表示当前日期,格式为YYYYMMDD DECLARE @active_end_date_config INT --作业执行的停止日期,默认为,格式为YYYYMMDD DECLARE @active_start_time_config INT --作业执行的开始时间,格式为HHMMSS DECLARE @active_end_time_config INT --作业执行的停止时间,格式为HHMMSS ------------------------------------------------------------------ --参数在此配置 ------------------------------------------------------------------ SET @jobname = N'testk20:17' --作业名称,格式：N'作业名称' SET @dataBaseNmae = 'test' --数据库名称 SET @savePath = 'E:/test/' --保存目录 set @freq_type_config = 4 --（执行的频率）1:一次，4：每天，8：每周，16：每月，32：每月，相对于frequency_interval,64:在sqlserver 代理服务器启动时运行，128：在计算机空闲时运行 set @freq_interval_config =1 --（执行的次数）1：一次，4：每天，8：每周 set @freq_subday_type_config =1 --0x1=在指定的时间,0x4=多少分钟,--重复方式,0x1=在指定的时间,0x4=多少分钟,0x8=多少小时执行一次。x1和@active_start_time一起使用，@active_start_time指定开始执行的时间，代表在@freq_type指定的频率间隔内只执行一次 --若是x4或x8，只要指定@freq_subday_interval，@freq_subday_interval代表每多少分钟（当@freq_subday_type=0x4）或小时（当@freq_subday_type=0x8）执行的次数 set @freq_recurrence_factor_config = 0 --作业的两次计划执行之间的间隔周或月，仅当frequency_type 设置为、或时，采使用，数据类型为int 默认值为 SET @freq_subday_interval_config =0 --执行作业的间隔，默认值为 set @active_start_date_config = 20180410 --作业执行的开始日期,为NULL时表示当前日期,格式为YYYYMMDD set @active_end_date_config = 99991231 --作业执行的停止日期,默认为,格式为YYYYMMDD set @active_start_time_config = 60000 --作业执行的开始时间,格式为HHMMSS set @active_end_time_config = 235959 --作业执行的停止时间,格式为HHMMSS ------------------------------------------------------------------ --以下均无需改动 ------------------------------------------------------------------ --定义创建作业 IF EXISTS(SELECT * FROM msdb.dbo.sysjobs WHERE name=@jobname) EXEC msdb.dbo.sp_delete_job @job_name=@jobname EXEC msdb.dbo.sp_add_job @job_name = @jobname, @job_id = @jobid OUTPUT --定义作业步骤 DECLARE @sql nvarchar(4000),@dbname sysname SELECT @dbname=DB_NAME(), --作业步骤在当前数据库中执行 @sql=N' DECLARE @date VARCHAR(66)=REPLACE(CONVERT(VARCHAR,GETDATE(),23),''-'','''') DECLARE @tempstr VARCHAR(666)='''+@savePath+@dataBaseNmae+'_''+@date+''.bak'' DECLARE @FILEPATHNAME NVARCHAR(100) DECLARE @result int SET @FILEPATHNAME= @tempstr CREATE TABLE #T2(a INT,b INT,c INT); INSERT INTO #T2 EXEC masteR..XP_FILEEXIST @FILEPATHNAME select @result =a from #T2 drop table #T2 if(@result=1) begin EXECUTE master.dbo.xp_delete_file 0,'+N'@tempstr end DECLARE @str VARCHAR(666)=''BACKUP DATABASE '+@dataBaseNmae+' TO DISK='''''+@savePath+@dataBaseNmae+'_''+@date+''.bak'''''' exec(@str)' --一般定义的是使用TSQL处理的作业,这里定义要执行的Transact-SQL语句 EXEC msdb.dbo.sp_add_jobstep @job_id = @jobid, @step_name = N'步骤一', @subsystem = 'TSQL', --步骤的类型,一般为TSQL @database_name=@dbname, @command = @sql --创建调度 EXEC msdb..sp_add_jobschedule @job_id = @jobid, @name = N'第一个调度', @freq_type=@freq_type_config, @freq_interval=@freq_interval_config, @freq_subday_type=@freq_subday_type_config, @freq_subday_interval=@freq_subday_interval_config, @active_start_date = @active_start_date_config, @active_end_date = @active_end_date_config, @active_start_time = @active_start_time_config, @active_end_time = @active_end_time_config, @freq_recurrence_factor = @freq_recurrence_factor_config EXEC msdb.dbo.sp_add_jobserver @job_id = @jobid, @server_name = N'(local)'
25.查询某个数据库中所有表总行数：
select SUM(rows) allrows from ( 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)) ) as zz
26.sql行转列：
27.sql列转行：
28.拼接还原数据库sql游标：
DECLARE @name varchar(1000) DECLARE @str varchar(1000) DECLARE @dwdm varchar(1000) set @name = '410000_yw_' DECLARE My_Cursor CURSOR FOR select dwdm from lwsjt.dbo.gx OPEN My_Cursor; FETCH NEXT FROM My_Cursor INTO @dwdm WHILE @@FETCH_STATUS = 0 BEGIN set @str = 'restore database ['+@name+@dwdm+'] from disk = ''D:Usersz.bak'' with replace,file=1,move ''410000_yw_115201'' to ''D:DataBaseSqlServerSqlServerDataBase'+@name+@dwdm+'.mdf'',' +'move ''410000_yw_115201_log'' to ''D:DataBaseSqlServerSqlServerLog'+@name+@dwdm+'_log.ldf''' select @str FETCH NEXT FROM My_Cursor INTO @dwdm END CLOSE My_Cursor; DEALLOCATE My_Cursor;
29.还原数据库语句：
RESTORE DATABASE [henandaxuehuaiheyiyuan506309cwyongyou_20180613100006_5054511020852556869] FROM DISK='F:/lwsj_file/merge_success/orig_name[CWYongYou_20180613],industry[医疗机构],unit[506309],sys_time[1528855218794].bak' WITH REPLACE, FILE=1, MOVE 'CW' TO 'F:DatabasesSqlserverSqlserverDatahenandaxuehuaiheyiyuan506309cwyongyou_20180613100006_5054511020852556869.mdf', MOVE 'CW_log' TO 'F:DatabasesSqlserverSqlserverDatahenandaxuehuaiheyiyuan506309cwyongyou_20180613100006_5054511020852556869_log.ldf';
30.公用表达式递归：
WITH COL_CTE(Id,Name,ParentId,tLevel ) AS ( --基本语句 SELECT Id,Name,ParentId,0 AS tLevel FROM Col WHERE ParentId = 0 UNION ALL --递归语句 SELECT c.Id,c.Name,c.ParentId,ce.tLevel+1 AS tLevel FROM COL as c INNER JOIN COL_CTE AS ce ON c.ParentId = ce.Id ) SELECT * FROM COL_CTE
31.merge合并：
create table test1 (id int,name varchar(20),dess varchar(20)) go create table test2 (id int,name varchar(20),dess varchar(20)) go insert into test1(id,name,dess) values(1,'boyi55','qqq'),(2,'56cto','qwe'),(3,'bbs','asd'),(4,'fengjicai','zxc'),(5,'alis','zxc') insert into test2(id,name,dess) values(1,'boyi','aaa'),(2,'test','qwe'),(3,'test','test') --------------------------- merge test2 t --要更新的目标表 using test1 s --源表 on t.id=s.id and t.name = s.name --更新条件（即主键） when matched --如果匹配，更新 then update set t.name=s.name when not matched
then insert values(id,name,dess); --如果两个条件都不匹配，插入。此语句必须以分号结束