snippet
大约 6 分钟sqlserver
DDL
--=================================================== 备份相关 ====================================================
-- 附加数据库
exec sp_attach_db 'CJ_XSZFZServer','D:\数据库\CJ_XSZFZServer.mdf','D:\数据库\CJ_XSZFZServer_log.ldf'
-- 2005之前设置默认备份路径
EXEC master..xp_regwrite
@rootkey='HKEY_LOCAL_MACHINE',
@key='Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer',
@value_name='BackupDirectory',
@type='REG_SZ',
@value='D:\SQL2005\DBBak'
-- 2012及以后设置默认备份路径
EXEC master..xp_regwrite
@rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQLServer',
@value_name='BackupDirectory',
@type='REG_SZ',
@value='D:\'
--=================================================== 序号字段 ====================================================
ROW_NUMBER() OVER (Partition By 字段 ORDER BY P.B_ID) as B_XHAuto
ROW_NUMBER() OVER (ORDER BY Getdate()) as B_XHAuto --根据当前复杂的排序方式获取序号
括号内的：partition By
意思是按照哪个字段进行分组排序，字段可以是多个字段，以逗号间隔
--=========================== 查询指定表字段为字符串，将表字段以指定的间隔符号拼接起来 ==============================
SELECT ',' + cols.name
FROM syscolumns cols
LEFT OUTER JOIN sysobjects obj
ON cols.id = obj.id
WHERE obj.name = 'G_DraftBillYarn'
FOR XML PATH('')
--=================================================== 自增种子 ====================================================
-- 重置自增种子
--- 删除原表数据,并重置自增列
truncate table tablename --truncate方式也可以重置自增字段
-- 重置表的自增字段,重置后第一行数据的编号是0
-- 如果要从1开始，要将下面的0修改为1
DBCC CHECKIDENT (tablename,reseed,0)
-- 设置允许显式插入自增列
SET IDENTITY_INSERT tablename ON
-- 当然插入完毕记得要设置不允许显式插入自增列
SET IDENTITY_INSERT tablename Off
--=================================================== 字段注释 ====================================================
-- 为字段添加注释
EXECUTE sp_addextendedproperty N'MS_Description', '这里填写对字段的注释', N'user', N'dbo', N'table', N'你的表名', N'column', N'表中的字段名称';
-- 修改字段注释
execute sp_updateextendedproperty 'MS_Description', '你要的修改后的字段注释','user','dbo','table','<tablename>','column','<column name>';
-- 删除注释
execute sp_dropextendedproperty 'MS_Description','user','dbo','table','<tablename>','column','<column name>';
--=================================================== 数据库操作 ====================================================
-- 新建数据库
create database ABC
on primary
(
/*--数据文件的具体描�?-*/
name='ABC', -- 主数据文件的逻辑名称
filename='D:\数据库\ABC.mdf', -- 主数据文件的物理名称
size=5mb, --主数据文件的初始大小
maxsize=100mb, -- 主数据文件增长的最大�?
filegrowth=15%--主数据文件的增长�?
)
log on
(
/*--日志文件的具体描�?各参数含义同�?-*/
name='ABC_log',
filename='D:\数据库\ABC_log.ldf',
size=2mb,
filegrowth=1mb
)
-- 恢复数据库
RESTORE DATABASE CJ_KSCKZKServer
FROM DISK = 'E:\客户列表\新达\数据库\新达2019.bak'
with REPLACE,
--备份文件中的数据文件的逻辑文件
MOVE 'TA_JDServer' TO 'D:\数据库\CJ_KSCKZKServer.mdf',
--备份文件中的日志文件的逻辑文件
MOVE 'TA_JDServer_log' TO 'D:\数据库\CJ_KSCKZKServer_log.ldf'
DML
多表联合修改
UPDATE G_Goods SET B_DefaultWX='222037,222038' FROM G_Goods
WHERE B_GoodsName IN (SELECT B_GoodsName from dev_processgoods)
cte操作
-- 删除前1W条数据
with cte as(select top 10000 * from G_Orders)
delete from cte WHERE 1=1;
存储过程
查询赋值变量
CREATE PROC usp_finishPurchaseOrderDtl8InID
(@ItemID INT)
AS
/**
* 创建时间：2024年11月22日 08:06:53
* 功能简介：传入材料采购入库明细主键，对比所属的采购订单的需求量，并设置该采购订单明细已完工状态
* 传入参数：@ItemID是订单材料入库单明细主键
*/
BEGIN
DECLARE @orderCode VARCHAR(100) -- 采购订单
DECLARE @ProductCode VARCHAR(100) -- 存货编码
DECLARE @orderDemand FLOAT -- 采购订单需求数量
DECLARE @inputQty float -- 材料入库合计数量
DECLARE @finished INT -- 完工标识
-- 从订单材料入库单明细中获取必要的数据
select @orderCode = B_OrderID,@ProductCode = B_GoodsID,
@orderDemand = B_OrderDemand
from G_BillDetailYarn
where B_ItemID = @ItemID
-- 统计采购订单号下指定存货编码的累计入库数量
select a.B_OrderID,a.B_GoodsID,@inputQty = sum(isnull(a.B_Qty,0))
from G_BillDetailYarn a
left outer join G_BillYarn b
on a.B_ID = b.B_ID
where b.B_ObjectID='12E016' -- 订单材料入库单
and a.B_OrderID=@orderCode and a.B_GoodsID=@ProductCode
GROUP BY a.B_OrderID,a.B_GoodsID
-- 比较并设置采购订单明细为完工状态
if @inputQty >= @orderDemand
set @finished = 1
else
set @finished = 0
-- 设置采购订单明细完工状态
Update G_BillDetailYarn set B_Inputed = @finished
from G_BillDetailYarn
left outer join G_BillYarn on G_BillDetailYarn.B_ID = G_BillYarn.B_ID
where G_BillYarn.B_OrderID = @orderCode
and G_BillDetailYarn.B_GoodsID = @ProductCode
END
数字类型相关
// 下面使用cast 确定小数位数，numeric 前面的8表示数值的总长度（包括小数点），后面的2表示小数点后的位数
select cast(e11/e9 as numeric(8,2)) from table
外键约束
// 获取关闭外键约束的sql
select 'ALTER TABLE [' + b.name + '] NOCHECK CONSTRAINT ' + a.name +';' as 禁用约束
from sysobjects a ,sysobjects b
where a.xtype ='f' and a.parent_obj = b.id
// 获取启用外键约束的sql
select 'ALTER TABLE [' + b.name + '] CHECK CONSTRAINT ' + a.name +';' as 禁用约束
from sysobjects a ,sysobjects b
where a.xtype ='f' and a.parent_obj = b.id
// 关闭启用指定表的所有外键
alter table [表名] NOCHECK constraint all;
alter table [表名] CHECK constraint all;
// 查看所有外键约束
select fk.name fkname , ftable.name ftablename, cn.name fkcol, rtable.name ftablename,
ObjectProperty(fk.id,'CnstIsUpdateCascade') updatCase,ObjectProperty(fk.id,'CnstIsDeleteCascade') deletCase from sysforeignkeys
join sysobjects fk
on sysforeignkeys.constid = fk.id
join sysobjects ftable
on sysforeignkeys.fkeyid = ftable.id
join sysobjects rtable
on sysforeignkeys.rkeyid = rtable.id
join syscolumns cn
on sysforeignkeys.fkeyid = cn.id and sysforeignkeys.fkey = cn.colid
字符串操作
删除回车符、换行符
# 删除回车符
SELECT *, REPLACE(detail, CHAR(13) , '') AS 显示替换后的内容 FROM loginfo
# 删除换行符
SELECT *, REPLACE(detail, CHAR(10), '<br>') AS 显示替换后的内容 FROM loginfo
其他
计算EAN13的最后一个校验位
传入前面12位长度的字符串，返回一个数字，是EAN13的最后一位数字
CREATE FUNCTION dbo.udf_GetGS1EAN13CheckDigit
(
@ACode AS VARCHAR(12)
)
RETURNS SMALLINT
AS BEGIN
/*
Author: Sergio Govoni
Notes: Calculate the check-digit of a GS1 EAN13 code
Version: 1.0
*/
DECLARE
@tmpCode AS VARCHAR(12)
,@tmpMulSup AS VARCHAR(8000)
,@tmp AS VARCHAR(8000)
,@i AS INT
,@j AS INT
,@z AS INT
,@SumDEven AS INT
,@SumDOdd AS INT
,@List AS VARCHAR(8000)
,@tmpList AS VARCHAR(8000)
,@CheckSum AS SMALLINT
SET @SumDEven = 0
SET @SumDOdd = 0
SET @List = ''
SET @tmpList = ''
SET @tmp = ''
SET @tmpCode = @ACode
/* 0. List builder */
SET @j = LEN(@tmpCode) + 1
SET @i = 1
WHILE (@i <= LEN(@tmpCode)) BEGIN SET @List = @List + '|' + LTRIM(RTRIM(STR(@j))) + ';' + SUBSTRING(@tmpCode, @i, 1) SET @j = (@j - 1) SET @i = (@i + 1) END /* 1. Add up the digits in even position */ SET @i = 1 SET @tmpList = @List WHILE (CHARINDEX('|', @tmpList) > 0)
BEGIN
SET @j = CHARINDEX('|', @tmpList)
SET @z = CHARINDEX(';', @tmpList)
IF (CAST(SUBSTRING(@tmpList, (@j + 1), (@z - (@j + 1))) AS INTEGER) % 2) = 0
BEGIN
SET @SumDEven = @SumDEven + CAST(SUBSTRING(@tmpList, (@z + 1), 1) AS INTEGER)
END
SET @tmpList = SUBSTRING(@tmpList, (@z + 2), LEN(@tmpList))
END
/* 2. Multiply the result of the previous step (the first step) to 3 (three) */
SET @SumDEven = (@SumDEven * 3)
/* 3. Add up the digits in the odd positions */
SET @i = 1
SET @tmpList = @List
WHILE (CHARINDEX('|', @tmpList) > 0)
BEGIN
SET @j = CHARINDEX('|', @tmpList)
SET @z = CHARINDEX(';', @tmpList)
IF (CAST(SUBSTRING(@tmpList, (@j + 1), (@z - (@j + 1))) AS INTEGER) % 2) <> 0
BEGIN
SET @SumDOdd = @SumDOdd + CAST(SUBSTRING(@tmpList, (@z + 1), 1) AS INTEGER)
END
SET @tmpList = SUBSTRING(@tmpList, (@z + 2), LEN(@tmpList))
END
/* 4. Add up the results obtained in steps two and three */
SET @CheckSum = (@SumDEven + @SumDOdd)
/* 5. Subtract the upper multiple of 10 from the result obtained in step four */
IF ((@CheckSum % 10) = 0)
BEGIN
/* If the result of the four step is a multiple of Ten (10), like
Twenty, Thirty, Forty and so on,
the check-digit will be equal to zero, otherwise the check-digit will be
the result of the fifth step
*/
SET @CheckSum = 0
END
ELSE BEGIN
SET @tmpMulSup = LTRIM(RTRIM(STR(@CheckSum)))
SET @i = 0
WHILE @i <= (LEN(@tmpMulSup) - 1)
BEGIN
SET @tmp = @tmp + SUBSTRING(@tmpMulSup, @i, 1)
IF (@i = LEN(@tmpMulSup) - 1)
BEGIN
SET @tmp = LTRIM(RTRIM(STR(CAST(@tmp AS INTEGER) + 1)))
SET @tmp = @tmp + '0'
END
SET @i = (@i + 1)
END
SET @CheckSum = CAST(@tmp AS INTEGER) - @CheckSum
END
RETURN @CheckSum
END;