数据库

数据库

常用主键规范

• FID：常用于单据头表的主键
• FENTRYID：常用于单据体表或者子单据头表的主键
• FDETAILID：常用于子单据体表的主键
• FPKID：常用于多语言表的主键
• FMASTERID：常用于基础资料主表中，用于分配型基础资料的唯一标识（基础资料分配到不同组织下，主键不同，FMASTERID相同）
• ID：平台内部会用Id作为单据头数据包的主键字段的属性名，例如，取单据主键：this.Model.DataObject["Id"]，因此，这个Id有特殊含义，请勿使用

所有基础资料的主键是全局唯一的，种子来自Z_BAS_ITEM表

查询业务领域

SELECT B.FNAME FROM T_META_TOPCLASS A LEFT JOIN T_META_TOPCLASS_L B ON A.FTOPCLASSID = B.FTOPCLASSID WHERE B.FLOCALEID = 2052 ORDER BY A.FSEQ;

查询子系统

SELECT B.FNAME FROM T_META_SUBSYSTEM A LEFT JOIN T_META_SUBSYSTEM_L B ON A.FID = B.FID WHERE B.FLOCALEID=2052 ORDER BY A.FSEQ;

查询业务对象

SELECT B.FNAME FROM T_META_CONSOLEDETAIL A LEFT JOIN T_META_CONSOLEDETAIL_L B ON A.FDETAILFUNCID = B.FDETAILFUNCID WHERE B.FLOCALEID = 2052 ORDER BY A.FSEQ;

查询数据表，通过表T_META_OBJECTTYPE的FKERNNELXML字段进行xml查找

select * from
(
select t1.*,convert(varchar(4000),t1.FKERNELXML.query('//TableName')) as Item,t2.FNAME from T_META_OBJECTTYPE t1
left join T_META_OBJECTTYPE_L t2 on t1.FID=t2.FID where t1.fid='SAL_SaleOrder'
) as t
where t.Item<>''

库存状态列表

select t1.FSTOCKSTATUSID,t1.FNUMBER,t2.FNAME from T_BD_STOCKSTATUS t1 left join T_BD_STOCKSTATUS_L t2 on t1.FSTOCKSTATUSID=t2.FSTOCKSTATUSID

单据类型

select t1.FBILLTYPEID,t1.FNUMBER,t2.FNAME from T_BAS_BILLTYPE t1 left join T_BAS_BILLTYPE_L t2 on t1.FBILLTYPEID=t2.FBILLTYPEID
where t1.FBILLFORMID='QM_InspectBill' --单据类型--检验单

基础资料

• 组织表：T_ORG_ORGANIZATIONS
• 组织表：T_ORG_ORGANIZATIONS_L
• 辅助资料分类：T_BAS_ASSISTANTDATA_L
• 辅助资料列表：T_BAS_ASSISTANTDATAENTRY
• 辅助资料列表：T_BAS_ASSISTANTDATAENTRY_L
• 账簿多语言：t_bd_accountbook_l
• 账簿：t_bd_accountbook
• 物料：t_bd_material
• 物料多语言：t_bd_material_l
• 业务员：T_BD_OPERATOR
• 业务员多语言：T_BD_OPERATOR_L
• 员工表：T_BD_STAFF
• 仓库：t_BD_Stock
• 供应商：t_bd_supplier

财务

• 应付单表头：t_AP_payable
• 付款单：T_AP_PAYBILL
• 应收单表头：t_AR_receivable
• 应收单表头：t_AR_receivable
• 凭证：t_gl_voucher
• 采购发票：T_IV_PURCHASEIC
• 销售发票：T_IV_SALESIC

供应链

• 采购订单：T_PUR_POORDER
• 价目表：T_PUR_PRICELIST
• 采购收料单：T_PUR_RECEIVE
• 采购申请：T_PUR_REQUISITION
• 销售发货通知单：T_SAL_DELIVERYNOTICE
• 销售订单：T_SAL_ORDER
• 销售出库单：T_SAL_OUTSTOCK
• 销售退货单：T_SAL_RETURNSTOCK
• 入库单：T_STK_INSTOCK
• 库存余额表：T_STK_InvBal
• 即时库存表：T_STK_INVENTORY

制造

• 物料清单：T_ENG_BOM
• 生产入库单：T_PRD_INSTOCK
• 生产订单：T_PRD_MO
• 生产汇报单：T_PRD_MORPT
• 生产领料单：T_PRD_PICKMTRL
• 生产用料清单：T_PRD_PPBOM
• 生产备料单据头：T_PRD_PREPAREMTRL
• 生产退料单：T_PRD_RETURNMTRL
• 委外补料单：T_SUB_FEEDMTRL
• 委外领料单：T_SUB_PICKMTRL
• 委外用料清单：T_SUB_PPBOM
• 委外订单：T_SUB_REQORDER
• 委外退料单：T_SUB_RETURNMTRL

物料名称

select m.fmaterialid,m.fmasterid, fname,m.fnumber from t_bd_material m join t_bd_material_l l on l.fmaterialid=m.fmaterialid
where 1=1
--and m.fmaterialid=147002
and m.fnumber='XACPWM0067'
--and l.fname like '%扯面%'
order by m.fmaterialid desc

组织

select V_SCM_OWNERORG_L.FNAME,* from V_SCM_OWNERORG left join V_SCM_OWNERORG_L on
V_SCM_OWNERORG.FORGID=V_SCM_OWNERORG_L.FORGID

select l.FNAME,* from T_ORG_ORGANIZATIONS o join T_ORG_ORGANIZATIONS_L l
on l.FORGID=o.FORGID

元数据

select * from t_meta_objecttype_l ml,t_meta_objecttype m where fname like '%工序汇报%' and ml.fid=m.fid

部门

select b.FNAME,* from T_BD_DEPARTMENT a join T_BD_DEPARTMENT_L b on a.FDEPTID=b.FDEPTID

单据转换

select * from T_META_CONVERTRULE where fid='PlanOrder_PPBom'

通过元数据唯一id查询菜单

select * from T_META_CONSOLEDETAIL where FOBJECTID like '%stk_%'

反写规则

select rl.FNAME, * from T_BF_WRITEBACKRULE r
left join T_BF_WRITEBACKRULE_L rl on rl.FID=r.FID and rl.FLOCALEID=2052
left join T_BF_WRITEBACKRULECUST rc on r.FID=rc.FID
where (rl.FNAME like '%生产线生产%' or rl.FID like '%sfc_%') and rc.FFORBIDSTATUS='A'

select * from T_BF_WRITEBACKRULE where fsourceformid like '%SFC_%' and ftargetformid like '%SFC_%'

转换规则

select * from T_META_CONVERTRULE where FID like '%rem_%'

参数

select * from t_bas_sysparameter

枚举值

select * from T_META_FORMENUM e
join T_META_FORMENUM_L el on el.FID=e.FID where FNAME like '%生产线领料单%'

select * from T_META_FORMENUM_L where fname ='REM_业务类型'

select * from t_Meta_Formenumitem where fid='c4a9508f-7af8-4edb-bb2f-fc21c866ee41'

单据类型

select * from T_BAS_BILLTYPE where FBILLFORMID like '%REM_INSTOCK%'

菜单操作对应类名

select * from T_MDL_FORMOPERATIONTYPE

操作里面的服务

select * from T_MDL_FORMBUSINESS_L where FDESC like '%更新即时库存%'

select * from T_MDL_FORMBUSINESS where FACTIONID=45

保存界面布局

select * from T_BAS_FormParameter
where FPARAOBJID like '%SFC_OperationPlanning%'

用户

select * from T_SEC_USER

用户参数

select * FROM T_BAS_UserParameter where FPARAMETEROBJID like '%rem_%'

IDE函数

select fl.FNAME,f.FAPPEARANCECLASS,f.FELEMENTCLASS, * from T_MDL_ELEMENTTYPE f join T_MDL_ELEMENTTYPE_L fl on f.FID=fl.FID
where FELEMENTCLASS like '%OPERATIONSTATUS%' or FAPPEARANCECLASS like '%OPERATIONSTATUS%'

权限对象，权限项

select * from T_SEC_PERMISSIONOBJECT

select * from T_SEC_PERMISSIONOBJECTENTRY

通过菜单查找元数据对象，查找表名(在xml中搜t_)

select * from T_META_OBJECTTYPE h join T_META_OBJECTTYPE_L l on l.FID=h.FID where FNAME like '%运算日志%'

单位

select l.FNAME, * from T_BD_UNIT h join T_BD_UNIT_L l on h.FUNITID=l.FUNITID where l.FNAME like '%千克%'

单据转换、业务流程、反写规则

SELECT * FROM T_BF_DEFVERSION;
SELECT * FROM T_BF_DEFVERSION_L;
SELECT * FROM T_BF_DEFVERSIONLOOKUP;
SELECT * FROM T_BF_INSTANCE;
SELECT * FROM T_BF_INSTANCEAMOUNT;
SELECT * FROM T_BF_INSTANCEAMOUNTHIS;
SELECT * FROM T_BF_INSTANCEENTRY;
SELECT * FROM T_BF_INSTANCEENTRYHIS;
SELECT * FROM T_BF_INSTANCEHIS;
SELECT * FROM T_BF_INSTANCESNAP;
SELECT * FROM T_BF_INSTANCESNAPHIS;
SELECT * FROM T_BF_INSTANCETRACK;
SELECT * FROM T_BF_MYFAVOURITE;
SELECT * FROM T_BF_PROCDEF;
SELECT * FROM T_BF_PROCDEF_L;
SELECT * FROM T_BF_PROCESSTYPE;
SELECT * FROM T_BF_PROCESSTYPE_L;
SELECT * FROM T_BF_PUBLISH;
SELECT * FROM T_BF_PUBLISH_L;
SELECT * FROM T_BF_PUBLISHENTRY;
SELECT * FROM T_BF_TABLEDEFINE;
SELECT * FROM T_BF_TRACKBACKUPLOG;
SELECT * FROM T_BF_WRITEBACKRULE;
SELECT * FROM T_BF_WRITEBACKRULE_L;
SELECT * FROM T_BF_WRITEBACKRULECUST;
SELECT * FROM T_META_CONVERTRULE;
SELECT * FROM T_META_CONVERTRULE_L;

操作日志查询

SELECT
    FNAME,
    FDATETIME,
    BOL.FDESCRIPTION 
FROM
    T_BAS_OPERATELOGbk BOL
    INNER JOIN T_SEC_USER SU ON BOL.FUSERID = SU.FUSERID 
WHERE
    BOL.FDESCRIPTION LIKE '%xxx%' UNION ALL
SELECT
    FNAME,
    FDATETIME,
    BOL.FDESCRIPTION 
FROM
    T_BAS_OPERATELOG BOL
    INNER JOIN T_SEC_USER SU ON BOL.FUSERID = SU.FUSERID 
WHERE
    BOL.FDESCRIPTION LIKE '%xxx%' 
ORDER BY
    FDATETIME ASC;

清理金蝶临时表

--SQLserver手工删临时表的方法（可在业务期间运行，推荐使用该方法删除临时表）：
--第一步：删除登记表中的可删除的临时表登记记录
delete from T_BAS_TEMPORARYTABLENAME where FPROCESSTYPE=1 or FCREATEDATE<DATEADD(hh,-24, GETDATE())

--第二步：删除临时表，每次删除50个临时表释放一次资源，可在业务运行期间执行语句
if object_id('temptb','table')>0 drop table temptb;
 declare @sql varchar(max)
 declare @icount int
 declare @I int
 set @sql='drop table '
 set @i=1
 select name,IDENTITY(int,1,1) id into temptb from sys.tables t where name like 'tmp%' and len(name)=30 and name not like 'tmp[_]%' 
 and not exists(select 1 from T_BAS_TEMPORARYTABLENAME where FTABLENAME=t.name) and create_date<=DATEADD(hh,-24, GETDATE())
 select @icount=@@ROWCOUNT
 while @i<@icount
 begin
   select @sql=@sql+name+',' from temptb where id between @i and @i+49
   if @@ROWCOUNT>0
       set @sql=substring(@sql,1,len(@sql)-1)+';'
   set @i=@i+50
   exec(@sql)
   set @sql='drop table '
 end
 if object_id('temptb','table')>0 drop table temptb;

SQL Server 权限设置

取消public角色查看所有数据库

• 安全性 -> 服务器角色 -> 双击public -> 服务器 -> 查看所有数据库 -> 取消勾选

新增high_public角色并设置权限（只读所有库）

• 端点 -> 连接 -> 勾选
• 服务器 -> 查看所有数据库 -> 勾选

新增common_user用户并设置权限（只读所有库）

• 服务器角色 -> 勾选high_public角色（同时public角色也会被选中）

新增test_user用户可以操作test库（看不到其他库）

• 服务器角色 -> 勾选public角色（默认选中）
• 用户映射 -> 勾选test库 -> 并在下方勾选库权限（如：db.owner）

给 low_user 用户增加 t_ar_receivable 表的权限

grant select on t_ar_receivable to low_user;

查看 low_user 用户的表权限

exec sp_helprotect null,low_user

禁用 low_user 用户的 t_ar_receivable 表权限

DENY SELECT ON t_ar_receivable to low_user;

删除 low_user 用户的 t_ar_receivable 表权限

REVOKE SELECT ON t_ar_receivable to low_user;

来宾账户

假设有一个需求可以让用户只读数据库的dbo模式，但是要建表、建视图等场景的话，只允许在guest模式下进行

确认guest用户在数据库中存在并启用

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'guest')
BEGIN
    CREATE USER guest;
END

允许guest连接权限

GRANT CONNECT TO guest

创建用户，指定默认库（只要不在 Sql Server Management Studio 中勾选用户映射，默认就是guest用户）

create login guest_user with password='guest_user123$', default_database=db_name

给来宾用户授权，这样guest_user就可以在guest模式下有创建表和视图的权限并且可以查看、引用dbo的数据

-- 授权查询后可以查询dbo模式下的表，guest模式下的视图也能引用dbo模式下的表
grant select to guest;
-- 授权创建表权限后，可以在guest模式下创建表，但是不能在dbo模式下创建表（因为没有勾选用户映射）
grant create table to guest;
-- 授权创建视图权限后，可以在guest模式下创建视图，但是不能在dbo模式下创建视图（因为没有勾选用户映射）
grant create view to guest;

最新总结

USE [数据库名];

-- 授予用户在guest模式下创建表的权限
GRANT CREATE TABLE TO [登录用户名];
GRANT ALTER ON SCHEMA::guest TO [登录用户名];

-- 确认或更改默认模式为guest
ALTER USER [登录用户名] WITH DEFAULT_SCHEMA = guest;

-- 授予创建视图的权限
GRANT CREATE VIEW TO [登录用户名];

-- 授予用户在guest模式下对表的增删改查权限
GRANT SELECT ON SCHEMA::guest TO [登录用户名];
GRANT INSERT ON SCHEMA::guest TO [登录用户名];
GRANT UPDATE ON SCHEMA::guest TO [登录用户名];
GRANT DELETE ON SCHEMA::guest TO [登录用户名];

-- 确保用户在dbo模式下只有只读权限
REVOKE INSERT, UPDATE, DELETE, ALTER ON SCHEMA::dbo FROM [登录用户名];
GRANT SELECT ON SCHEMA::dbo TO [登录用户名];

查看运行中的进程

SELECT
SPID = er.session_id
,Status = ses.status
,[Login] = ses.login_name
,Host = ses.host_name
,BlkBy = er.blocking_session_id
,DBName = DB_Name(er.database_id)
,CommandType = er.command
,SQLStatement = st.text
,ObjectName = OBJECT_NAME(st.objectid)
,ElapsedMS = er.total_elapsed_time
,CPUTime = er.cpu_time
,IOReads = er.logical_reads + er.reads
,IOWrites = er.writes
,LastWaitType = er.last_wait_type
,StartTime = er.start_time
,Protocol = con.net_transport
,ConnectionWrites = con.num_writes
,ConnectionReads = con.num_reads
,ClientAddress = con.client_net_address
,Authentication = con.auth_scheme
FROM sys.dm_exec_requests er
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id
WHERE er.session_id > 50
ORDER BY
er.blocking_session_id DESC
,er.session_id

查看慢查询

declare @beginTime varchar(23),
        @endTime varchar(23)
set @beginTime='2020-12-21 01:35:00.000'
set @endTime='2020-12-22 15:00:00.000'
SELECT
(total_elapsed_time / execution_count)/1000 N'平均时间ms'
,total_elapsed_time/1000 N'总花费时间ms'
,total_worker_time/1000 N'所用的CPU总时间ms'
,total_physical_reads N'物理读取总次数'
,total_logical_reads/execution_count N'每次逻辑读次数'
,total_logical_reads N'逻辑读取总次数'
,total_logical_writes N'逻辑写入总次数'
,execution_count N'执行次数'
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) N'执行语句'
,creation_time N'语句编译时间'
,last_execution_time N'上次执行时间'
FROM
sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
WHERE
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) not like '%fetch%'
and creation_time between @beginTime and @endTime
ORDER BY 
total_elapsed_time / execution_count DESC;

查看进行中的事务

select * from sys.dm_tran_session_transactions;
go

查询表名/字段名

• F = FOREIGN KEY 约束
• L = 日志
• FN = 标量函数
• IF = 内嵌表函数
• P = 存储过程
• PK = PRIMARY KEY 约束（类型是 K）
• RF = 复制筛选存储过程
• S = 系统表
• TF = 表函数
• TR = 触发器
• U = 用户表
• UQ = UNIQUE 约束（类型是 K）
• V = 视图
• X = 扩展存储过程

# 查询数据库下的所有用户自建表
SELECT * FROM 数据库名..SysObjects Where XType='U' ORDER BY Name 

# 查询数据表下的所有字段
SELECT * FROM SysColumns WHERE id=Object_Id('表名')

数据库复制

使用sqlserver自带的 复制 -> 本地发布、本地订阅

删除本地发布

• EXEC sp_removedbreplication 'DATABASE_NAME';

sqlserver服务器字体很小问题

• win + "+" 放大镜（增大）
• win + "-" 放大镜（缩小）
• win + "esc" 退出放大镜

发布步骤（部分）

• 使用事务发布
• 勾选，立即创建快照并使快照保持可用状态，以初始化订阅
• 安全设置，勾选在 SQL Server 代理服务账户下运行
• 完成后，右键发布的具体对象，查看快照代理状态（一定要等到进度100%再进行订阅，否则可能订阅失败）

订阅步骤（部分）

• 勾选，在分发服务器 xxx 上运行所有代理
• 其他参照发布步骤（部分）

报错处理

增加发布表

python操作数据库

文档

安装sqlserver驱动

安装pymssql

pip install pymssql

import pymssql

connect = pymssql.connect('127.0.0.1:1433','sa','123456','db_test')

cursor = connect.cursor(as_dict=True)

sql = "SELECT [客户编码],[物料编码],CONVERT (CHAR (7),日期,120) AS [年月],SUM (数量) AS [数量] FROM [V_SAL_OUTSTOCK] GROUP BY [客户编码],[物料编码],CONVERT (CHAR (7),日期,120);"

cursor.execute(sql)

# 迭代输出
# for row in cursor:
#     print(row)

# 输出全部
print(cursor.fetchall())

cursor.close()
connect.close()

批量操作

def executeMany(params):
    connect = pymssql.connect(config.SqlServerHost, config.SqlServerUser, config.SqlServerPassword,
                              config.SqlServerDatabase, timeout=60)
    cursor = connect.cursor()

    sql_header = "UPDATE table_header SET field1 = %s,field2 = %s WHERE id = %s"
    sql_detail = "UPDATE table_detail SET field1 = %s,field2 = %s WHERE id = %s"

    update_header = []
    update_detail = []

    for k, data in params.items():
        update_header.append((data['header']['field1'], data['header']['field2'], data['header']['id']))
        for detail in data['detail']:
            update_detail.append((detail['field1'], detail['field2'], detail['id']))

    # 使用 executemany 执行多个更新操作
    cursor.executemany(sql_header, update_header)
    cursor.executemany(sql_detail, update_detail)

    # 提交更改
    connect.commit()

    # 关闭游标和数据库连接
    cursor.close()
    connect.close()
    return True

分页

SELECT * FROM T_AR_RECEIVABLE ORDER BY FID OFFSET (1-1)*10 ROWS FETCH NEXT 10 ROWS ONLY;

连表更新，和MySQL略有区别

UPDATE a SET a.name = b.name
FROM tb_a a LEFT JOIN tb_b b ON a.id = b.id
WHERE b.id IS NOT NULL;