MSSQL – ITJOY.NET

2020年9月22日2020年9月22日

小数乘法除法练习题批量生成

批量生成3位长度的小数与2位长度小数乘法题目
方便广大数学老师或学生家长打印练习。
SQL SERVER 代码

–1.生成随机数
select top 5000
ceiling(rand(checksum(NEWID())) *1000) as x –生成3位以内随机整数
,ceiling(rand(checksum(NEWID())) *100) as y –生成2位以内随机整数
–cast(cast(rand(rand(number)100000000)10000000000000 as bigint)%1000 as dec(10,4)) as x –生成3位以内随机整数
–,cast(cast(rand(rand(number)100000000)1000000000000000 as bigint)%100 as dec(10,4)) as y –生成2位以内随机整数
–,number,rand(number)100000000,rand(rand(number)100000000)
,cast(0 as dec(28,6))as z
into #temp01
from master..spt_values a,master..spt_values b
where 1=1

–将整数转换为随机位小数
update a set
x=x/(case when cast(rand(x)100000000 as int)%4=1 then 1000 when cast(rand(x)100000000 as int)%4=2 then 100 when cast(rand(x)100000000 as int)%4=3 then 10 else 1 end), –将3位整数随机转换为小数 y=y/(case when cast(rand(y)100000000 as int)%3=1 then 100 when cast(rand(y)*100000000 as int)%3=2 then 10 else 1 end )–将2位整数随机转换为小数
from #temp01 a

–2.清理数据
update #temp01 set z=x*y –计算乘积得数

–去除得数为0的计算
delete from #temp01 where z=0

–去除全整数计算
delete from #temp01 where (x-cast(x as int)=0 and y-cast(y as int)=0)

–去除两个乘数都是小数的计算
delete from #temp01 where cast(x as int)=0 and cast(y as int)=0

–去除有一个乘数是1的计算
delete from #temp01 where x=1 or y=1

–3.输出结果
–select * from #temp01
–输出小数乘法题
select cast(x as float)as 乘数,’x’ 乘,cast(y as float)as 乘数,’=’as 等于,cast(z as float)as 积 from #temp01 order by NEWID()
–输出小数除法题
select cast(z as float)as 被除数,’÷’ 除,cast(y as float)as 除数,’=’as 等于,cast(x as float)as 商 from #temp01 order by NEWID()

输出结果：

乘数乘乘数等于积
27.9 x 0.8 = 22.32
33.6 x 9.2 = 309.12
0.18 x 14 = 2.52
0.425 x 4.2 = 1.785

被除数除除数等于商
3312.4 ÷ 5.2 = 637
72.416 ÷ 7.3 = 9.92
7.5287 ÷ 0.79 = 9.53
37.037 ÷ 77 = 0.481

代码下载：小数计算题随机生成.sql

5000道题目下载：小数计算题一万道

2019年5月23日

SQL Server 备份检查及监控

对数据库定期备份进行检查,并监控容量增长.可以用以下SQL查询并推送消息。

select database_name,name,backup_start_date,CAST(backup_size/(1024*1024*1024) AS DEC(28,2))AS [备份大小(GB)],CAST(compressed_backup_size /(1024*1024*1024) AS DEC(28,2)) AS [压缩后(GB)]
from msdb..backupset 
where DATEDIFF(day,backup_start_date,GETDATE())=0 --当天的备份

select database_name,name,backup_start_date,CAST(backup_size/(1024*1024*1024) AS DEC(28,2))AS [备份大小(GB)],CAST(compressed_backup_size /(1024*1024*1024) AS DEC(28,2)) AS [压缩后(GB)]

from msdb..backupset

where DATEDIFF(day,backup_start_date,GETDATE())=0 --当天的备份

2019年5月23日2019年5月23日

WebApi+SQLServer定时任务方案

工作中有很多定期执行、定时同步的需求，最适合的方案是SqlServer+Topshelf+Quartznet ，用SqlServer做任务池，用Quartznet做调度计划，用Topshelf将项目部署为系统服务后台运行。

但是整个项目比较庞大，一时半会做不出来。结合手工已经在用的组件，设计了WebApi+SQLServer 的方案。在WebApi里写工作任务，利用SQLServer的维护计划做调度，需要有安装IIS和SQL数据库。

1.首先是写一个可以http调用的api程序。以下程序调用路径是 /api.aspx?act=GO

当然也可以写一个规范的MVC api程序。

using System;
using System.Reflection;
using System.Web.UI;
using Newtonsoft.Json;

namespace MIS.范例
{

    public partial class api : Page
    {
        private string errcode;
        private string errmsg;
        private string info;
        protected void Page_Load(object sender, EventArgs e)
        {
            this.errcode = "0";
            this.errmsg = "";
            this.info = "";

            string act = Request.QueryString["act"];
            if (!string.IsNullOrEmpty(act))
            {
                try
                {
                    //执行act动作
                    var result = GetType().GetMethod(act, BindingFlags.Instance | BindingFlags.Public).Invoke(this, null) as string;
                    if (!string.IsNullOrEmpty(result))
                    {
                        Context.Response.ContentType = "text/plain";
                        Response.Write(result);
                    }
                }
                catch (Exception ex)
                {
                    //错误
                    Context.Response.ContentType = "text/plain";
                    Response.Write(JsonConvert.SerializeObject(new { this.errcode, errmsg = ex.ToString(), this.info }));
                }
                Response.End();
            }
        }
        //任务 /act=GO
        public string GO()
        {
            //执行动作
            //返回JSON结果
            return JsonConvert.SerializeObject(new { this.errcode, this.errmsg, this.info });
        }
    }
}

using System;

using System.Reflection;

using System.Web.UI;

using Newtonsoft.Json;

namespace MIS.范例

{

public partial class api : Page

{

private string errcode;

private string errmsg;

private string info;

protected void Page_Load(object sender, EventArgs e)

{

this.errcode = "0";

this.errmsg = "";

this.info = "";

string act = Request.QueryString["act"];

if (!string.IsNullOrEmpty(act))

{

try

{

//执行act动作

var result = GetType().GetMethod(act, BindingFlags.Instance | BindingFlags.Public).Invoke(this, null) as string;

if (!string.IsNullOrEmpty(result))

{

Context.Response.ContentType = "text/plain";

Response.Write(result);

}

catch (Exception ex)

{

//错误

Context.Response.ContentType = "text/plain";

Response.Write(JsonConvert.SerializeObject(new { this.errcode, errmsg = ex.ToString(), this.info }));

}

Response.End();

}

//任务 /act=GO

public string GO()

{

//执行动作

//返回JSON结果

return JsonConvert.SerializeObject(new { this.errcode, this.errmsg, this.info });

}

2. 在SQLServer维护计划里添加T-SQL语句任务，并设置按周期执行。

use DATABASE
declare @url as Varchar(200) ,@PostData Varchar(10),@ResponseText as Varchar(8000)
declare @errcode nvarchar(20),@errmsg nvarchar(2000)
set @url='http://10.0.0.1:8080/api?act=GO'

exec P_Url_SendRequest @url ,@PostData,@ResponseText OUTPUT
select @errcode=StringValue from parsejson(@ResponseText) where NAME='errcode'
select @errmsg='考勤同步失败,'+StringValue from parsejson(@ResponseText) where NAME='errmsg'	--errcode  errmsg  info

if @errcode<>'0'
begin
	exec  P_Send_TextMsg '企业微信消息','wxuser1,wxuser2',@errmsg
end

use DATABASE

declare @url as Varchar(200) ,@PostData Varchar(10),@ResponseText as Varchar(8000)

declare @errcode nvarchar(20),@errmsg nvarchar(2000)

set @url='http://10.0.0.1:8080/api?act=GO'

exec P_Url_SendRequest @url ,@PostData,@ResponseText OUTPUT

select @errcode=StringValue from parsejson(@ResponseText) where NAME='errcode'

select @errmsg='考勤同步失败,'+StringValue from parsejson(@ResponseText) where NAME='errmsg' --errcode errmsg info

if @errcode<>'0'

begin

exec P_Send_TextMsg '企业微信消息','wxuser1,wxuser2',@errmsg

end

3. 以上SQL里用到的函数组件

P_Url_SendRequest 连接URL，支持POST\GET 支持返回值

MSSQL访问WebServer接口代码 – P_Url_SendRequest

parsejson JSON解析函数

MSSQL JSON函数-parseJSON

P_Send_TextMsg 企业微信消息推送执行结果

MSSQL调用企业微信发送消息接口封装-P_Send_TextMsg

2019年4月23日2019年4月28日

SQL Server 智能重建所有表索引

一般使用 dbcc dbreindex(@TABLENAME,”,90) 来重建表索引，但数据库中表较多需要批量全部重建，有的碎片程度不高或数据行数不多，不需要重建时，可以参考使用如下批处理。

继续阅读“SQL Server 智能重建所有表索引”

2018年12月11日

SQL Server 防注入参数化之 Where in

程序中执行SQL时，可以使用参数化防注入，如：

select * from tablenmae where id = @a  --@a=123

1	select * from tablenmae where id = @a --@a=123

但是where id形式不可以直接使用参数化：

select * from tablenmae where id in('0,1,2,3')

1	select * from tablenmae where id in('0,1,2,3')

参考：https://www.cnblogs.com/shenyixin/archive/2011/08/26/2154115.html

将逗号分隔的字符串转换为参数table，再使用参数化调用，如：

declare str vchar(100)
set str='1,2,3'
select * from tablename where id in (select toint from StrTointTable(@str,',') )

declare str vchar(100)

set str='1,2,3'

select * from tablename where id in (select toint from StrTointTable(@str,',') )

where in 字符串：

declare str vchar(100)
set str='abc,bbb,ccc'
select * from tablename where name in (select string from StrToTable(@str,',')

declare str vchar(100)

set str='abc,bbb,ccc'

select * from tablename where name in (select string from StrToTable(@str,',')

字符串转换参数(string类型)table函数如下：

IF OBJECT_ID('dbo.StrToTable', 'TF') IS NOT NULL
    DROP FUNCTION dbo.StrToTable;
GO
CREATE Function StrToTable(@str nvarchar(2000),@split nvarchar(100))
Returns @tableName Table
(
   string varchar(50)
)
As
--该函数用于把一个用逗号分隔的多个数据字符串变成一个表的一列，例如字符串'1,2,3,4,5' 将变成一个表
Begin
	set @str = @str+@split
	declare @splen int	--分割符长度
	set @split=ltrim(rtrim(@split))
	set @splen=len(@split)
	Declare @insertStr nvarchar(50) --截取后的第一个字符串
	Declare @newstr nvarchar(2000) --截取第一个字符串后剩余的字符串
	set @insertStr = left(@str,charindex(@split,@str)-1)
	set @newstr = stuff(@str,1,charindex(@split,@str)-1+@splen,'')
	Insert @tableName Values(@insertStr)
	while(len(@newstr)>0 and charindex(@split,@newstr)>0)
	begin
	   set @insertStr = left(@newstr,charindex(@split,@newstr)-1)
	   Insert @tableName Values(@insertStr)
	   set @newstr = stuff(@newstr,1,charindex(@split,@newstr)-1+@splen,'')
	end

	if(len(@newstr)>0)
	begin
	   Insert @tableName Values(@newstr)
	end
	delete from @tableName where isnull(string,'')=''
	Return
End

IF OBJECT_ID('dbo.StrToTable', 'TF') IS NOT NULL

DROP FUNCTION dbo.StrToTable;

CREATE Function StrToTable(@str nvarchar(2000),@split nvarchar(100))

Returns @tableName Table

(

string varchar(50)

)

--该函数用于把一个用逗号分隔的多个数据字符串变成一个表的一列，例如字符串'1,2,3,4,5' 将变成一个表

Begin

set @str = @str+@split

declare @splen int --分割符长度

set @split=ltrim(rtrim(@split))

set @splen=len(@split)

Declare @insertStr nvarchar(50) --截取后的第一个字符串

Declare @newstr nvarchar(2000) --截取第一个字符串后剩余的字符串

set @insertStr = left(@str,charindex(@split,@str)-1)

set @newstr = stuff(@str,1,charindex(@split,@str)-1+@splen,'')

Insert @tableName Values(@insertStr)

while(len(@newstr)>0 and charindex(@split,@newstr)>0)

begin

set @insertStr = left(@newstr,charindex(@split,@newstr)-1)

Insert @tableName Values(@insertStr)

set @newstr = stuff(@newstr,1,charindex(@split,@newstr)-1+@splen,'')

end

if(len(@newstr)>0)

begin

Insert @tableName Values(@newstr)

end

delete from @tableName where isnull(string,'')=''

Return

End

字符串转换参数(int类型)table函数如下：

IF OBJECT_ID('dbo.StrToIntTable', 'TF') IS NOT NULL
    DROP FUNCTION dbo.StrToIntTable;
GO
create Function StrToIntTable(@str nvarchar(2000),@split nvarchar(100))
Returns @tableName Table
(
   string varchar(50),
   toint int
)
As
--该函数用于把一个用逗号分隔的多个数据字符串变成一个表的一列，例如字符串'1,2,3,4,5' 将变成一个表
Begin
	set @str = @str+@split
	declare @splen int	--分割符长度
	set @split=ltrim(rtrim(@split))
	set @splen=len(@split)
	Declare @insertStr nvarchar(50) --截取后的第一个字符串
	Declare @newstr nvarchar(2000) --截取第一个字符串后剩余的字符串
	set @insertStr = left(@str,charindex(@split,@str)-1)
	set @newstr = stuff(@str,1,charindex(@split,@str)-1+@splen,'')
	Insert @tableName Values(@insertStr,null)
	while(len(@newstr)>0 and charindex(@split,@newstr)>0)
	begin
	   set @insertStr = left(@newstr,charindex(@split,@newstr)-1)
	   Insert @tableName Values(@insertStr,null)
	   set @newstr = stuff(@newstr,1,charindex(@split,@newstr)-1+@splen,'')
	end

	if(len(@newstr)>0)
	begin
	   Insert @tableName Values(@newstr,null)
	end

	update @tableName set toint=cast(string as int)
	where PATINDEX('%[^0-9]%', string)=0
	and  PATINDEX('%[0-9]%', string)>0

	delete from @tableName where toint is null
	Return
End