用友erp常用SQL语句

1、-查询用友版本号use ufsystemgoselect * from UA_Versiongo- 查看系统用户信息表use ufsystemselectcUser_Id as操作员编码cUser_Name as操作员名称nState as是否停用iAdmin as 是否帐套主管理cDept as 所属部门cBelongGrp as 所在组nState as是否停用from UA_User- 查看具有帐套主管身份的操作员selectcUser_Id as操作员编码cUser_Name as操作员名称from UA_User where iAdmin=1;- 查看被停用的操作员selectcUs

2、er_Id as操作员编码cUser_Name as操作员名称from UA_User where nState=1;- 帐套主子表相关信息use ufsystem- 帐套主表selectcAcc_Id as 账套号cAcc_Name as账套名称cAcc_Path as 账套路径iYear as启用会计期年iMonth as 启用会计期月cAcc_Master as 账套主管cCurCode as 本币代码cCurName as本币名称cUnitName as 单位名称cUnitAbbre as单位简称cUnitAddr as单位地址cUnitZap as邮政编码cUnitTel as联系c

3、UnitFax as传真cUnitEMail as电子cUnitTaxNo as税号cUnitLP as法人cEntType as企业类型cTradeKind as行业类型cIsCompanyVer as是否集团版cDomain as域名cDescxription as备注cOrgCode as 机构编码iSysID as账套部标识from ua_account- 帐套子表select cAcc_Id as账套号iYear as账套年度cSub_Id as 模块标识bIsDelete as是否删除bClosing as是否关闭iModiPeri as会计期间dSubSysUsed as启用会计

4、日期cUser_Id as 操作员dSubOriDate as 启用自然日期from ua_account_sub- 当客户的数据在其它机器上做的升级然后拷回到原机器/* 拷回的数据，通过系统管理在原机器上引入后，并不会在ufsystem 数据库中的 ua_account_sub 这个帐套子表中回写上一年度的bClosing字段来关闭上一年度*/- 比如 002 帐套结转后年度为 2010 则用于关闭上一 (2009) 年度的 sql 如下：select * from ua_account_sub where cAcc_Id='002' and iYear=2008 updat

5、e ua_account_sub set bclosing=0 where cAcc_Id='002' and iYear=2008- 清除异常任务及单据锁定use ufsystemdelete from ua_taskdelete from ua_taskloggodelete from ufsystem.ua_taskdelete from ufsystem.ua_taskloggoSelect *From ua_taskWhere(cacc_id='*')- 注：（ *为账套号）- 科目锁定的解决/*XX 科目已经被用户 XX 锁定”或“科目 (xxxxx

6、x) 正在被机器 (xxxx) 上的用户 (xxx) 进行 (xxxx) 操作锁定请稍候再试”。*/use UFDATA_002_2008selectccode as 科目编码cauth as 功能名称cuser as 用户名cmachine as 机器名from GL_mccontroldelete from GL_mccontrol- 如何取得一个数据表的所有列名/*方法如下：先从SYSTEMobxject系统表中取得数据表的SYSTEMID然后再SYSCOLUMN表中取得该数据表的所有列名。SQL语句如下： */*( 方法一 */select * from ufsystem.ua_acc

7、ountselect * from syscolumns where id=obxject_id('ua_account')declare objid intobjname char(40)set objname = 'ua_account'select objid = id from sysobxjects where id = obxject_id(objname)select'Column_name' = namefrom syscolumns where id = objidorder by colid/*( 方法二（邹建） */- 跟踪

8、程序的运行就可以了 ./*开始 - 程序 -MSSQLSERVER- 事件探察器 (SQLProfiler)- 文件- 新建 - 跟踪 .- 设置要跟踪的服务器的信息 ( 连接服务器 )- 确定- 设置跟踪的项目 .- 然后数据库的调用情况就会显示出来在跟踪项目设置中如果不熟悉的话一般用默认设置筛选项目有几个可以注意一下:1.DatabaxseName 同于你要监测的数据库名 ( 不过这个好像不起作用我的电脑上设置无效 )用用2.DatabaxseIDselect3.obxjectName4.obxjectIDselect5.Error同于你要检测的数据库的dbid 可以db_id(N'

9、;你要监测的库名 ') 得到 dbid同于你要监测的对象名例如表名视图名等同于你要监测的对象的id 可以obxject_id(N'你要监测的对象名 ') 得到 id同于错误如果经常出现某个编号的错误则针对此错误号6.Seccess同于0失败1 成功如果是排错就过滤掉成功的处理*/* 方法三： */- 如果直接查询可以参考我的这段代码 :ifexists(select*fromdbo.sysobxjectswhereid=obxject_id(N'dbo.p_search')andobxjectPROPERTY(idN'IsProcedure

10、9;)=1)dropproceduredbo.p_searchGO/*-搜索某个字符串在那个表的那个字段中- 邹建2004.10( 引用请保留此信息 )-*/*-调用示例usepubsexecp_searchN'l'-*/createprocp_searchstrNvarchar(1000)- 要搜索的字符串asifstrisnullreturndeclaresNvarchar(4000)createtable#t(表名sysname 字段名sysname)declareselect''')+'printfromtbcursorlocalfors

11、='ifexists(select1from'+replace('where'++'likeN''%'+str+'%'' )''所在的表及字段 :'++'.'++''''syscolumnsajoinsysobxjectsbona.id=b.idwhereandb.xtype='U' and a.xtype in(31167)a.status>=0opentbfetc

12、hnextfromtbintoswhilefetch_status=0beginexec(s)fetchnextfromtbintosendclosetbdeallocatetbgo- 通过 SQL语句来更改用户的密码/* 修改别人的需要 sysadminrole*/EXECsp_passwordNULL'newpassword''User'/* 如果为 SA执行 */EXECsp_passwordNULL'newpassword'sa- 通怎么判断出一个表的哪些字段不允许为空？select COLUMN_NAMEfrom INFORMATION

13、_SCHEMA.COLUMNSwhere IS _NULLABLE='NO' and TABLE_NAME='ua_account'- 如何在数据库里找到含有相同字段的表？- a. 查已知列名的情况SELECTasTableNasFromsyscolumnsaINNERJOINONa.id=b.idANDb.type='U'AND='cacc_id'- 本例如： cacc_id 列columnname sysobxjectsb- b. 未知列名查所有在不同表出现过的列名Selecto.na

14、meAAsFromsyscolumnss1sysobxjectsoWheres1.id=o.idAndo.type='U'AndExists(columnnameSelect1Fromsyscolumnss2W=Ands1.id<>s2.id)- 查询第 xxx 行数据- 假设 id 是主键：select*fromwherefrom(selecttopxxx*fromyourtable)notexists(select1from(selectyourtable)bbwhereaa.id=bb.id

15、)aatopxxx-1*- 如果使用游标也是可以的fetchabsolutenumberfromcursor_name- 行数为绝对行数- SQL Server 日期计算/*a.一个月的第一天 */SELECTDATEADD(mm DATEDIFF(mm0getdate()0)/*b.本周的星期一 */SELECTDATEADD(wkDATEDIFF(wk0getdate()0)/*c.一年的第一天 */SELECTDATEADD(yyDATEDIFF(yy0getdate()0)/*d.季度的第一天 */SELECTDATEADD(qqDATEDIFF(qq0getdate()0)/*e.上

16、个月的最后一天*/SELECTdateadd(ms-3DATEADD(mm DATEDIFF(mm0getdate()0)/*f.去年的最后一天 */SELECTdateadd(ms-3DATEADD(yy DATEDIFF(yy0getdate()0)/*g.本月的最后一天 */SELECTdateadd(ms-3DATEADD(mm DATEDIFF(m0getdate()+10)/*h.本月的第一个星期一 */selectDATEADD(wkDATEDIFF(wk0dateadd(dd6-datepart(daygetdate()getdate()0)/*i.本年的最后一天 */SELE

17、CT dateadd(ms-3DATEADD(yy DATEDIFF(yy0getdate()+10)1. 显示本月第一天SELECT DATEADD(mmDATEDIFF(mm0getdate()0)select convert(datetimeconvert(varchar(8)getdate()120)+'01'120)2. 显示本月最后一天selectdateadd(day-1convert(datetimeconvert(varchar(8)dateadd(month1getdate()120)+'01'120)SELECT dateadd(ms-3D

18、ATEADD(mmDATEDIFF(m0getdate()+10)3. 上个月的最后一天SELECT dateadd(ms-3DATEADD(mmDATEDIFF(mm0getdate()0)4. 本月的第一个星期一select DATEADD(wkDATEDIFF(wk0dateadd(dd6-datepart(daygetdate()getdate()0)5. 本年的第一天SELECT DATEADD(yyDATEDIFF(yy0getdate()0)6. 本年的最后一天SELECT dateadd(ms-3DATEADD(yyDATEDIFF(yy0getdate()+10)7. 去年的

19、最后一天SELECT dateadd(ms-3DATEADD(yyDATEDIFF(yy0getdate()0)8. 本季度的第一天SELECT DATEADD(qqDATEDIFF(qq0getdate()0)9. 本周的星期一SELECT DATEADD(wkDATEDIFF(wk0getdate()0)10. 查询本月的记录select * from tableName where DATEPART(mm theDate) = DATEPART(mm GETDATE() and DATEPART(yy theDate) = DATEPART(yy GETDATE()11. 查询本周的记录

20、select * from tableName where DATEPART(wk theDate) = DATEPART(wk GETDATE() and DATEPART(yy theDate) = DATEPART(yy GETDATE()12. 查询本季的记录select * from tableName where DATEPART(qq theDate) = DATEPART(qqGETDATE() and DATEPART(yy theDate) = DATEPART(yy GETDATE()其中 :GETDATE()是获得系统时间的函数。13. 获取当月总天数 :select

21、DATEDIFF(ddgetdate()DATEADD(mm 1 getdate()select datediff(daydateadd(mm datediff(mm''getdate() '')dateadd(mm datediff(mm''getdate() '1900-02-01')14. 获取当前为星期几DATENAME(weekday getdate()-/* 查询数据库的所有用户表*/use ufdata_002_2008selectnamefromsysobxjectswheretype='U'- 查

22、看数据库中所有的触发器use ufdata_002_2008goselect * from sysobxjects where xtype='TR'- 查询特定数据库中某一不知归属表的触发器/*查询某一个触发器TR_Ap_CloseBills所归属的表*/use ufdata_002_2008declareparent_obj_idint- 定义父对象id变量- 先找出父对象（所在表）的 id( 在触发器不重复归属于多个表的情况下 ) select parent_obj_id=parent_objfrom sysobxjects where name='TR_Ap_Cl

23、oseBills'and xtype='TR'print '所在父对象（表）的ID 是： '+str(parent_obj_id)- 接下来找出父对象（表）的名称select name as触发器所在表为from sysobxjects wheretype='U' andid=parent_obj_id- 查看触发器容use ufdata_002_2008goexec sp_helptext 'TR_Ap_CloseBills'- 用于查看触发器的属性（参数指定触发器所在的表）use ufdata_002_2008goex

24、ec sp_helptrigger Ap_CloseBills- 创建触发器/*（ 1）创建一个简单的触发器触发器是一种特殊的存储过程，类似于事件函数，SQL Server? 允许为 INSERT、UPDATE、 DELETE创建触发器，即当在表中插入、更新、删除记录时，触发一个或一系列T-SQL语句。触发器可以在查询分析器里创建， 也可以在表名上点右键- >“所有任务” - >“管理触发器”来创建，不过都是要写 T-SQL语句的，只是在查询分析器里要先确定当前操作的数据库。创建触发器用 CREATE TRIGGER格式如下：-CREATE TRIGGER触发器名称ON表名FOR

25、INSERT、UPDATE或DELETEAST-SQL语句-注意：触发器名称是不加引号的。*/- 如下是联机丛书上的一个示例，当在tixtles表上更改记录时，发送通知 MaryM。CREATE TRIGGER reminderON tixtlesFOR INSERT UPDATE DELETEASEXEC master.xp_sendmail 'MaryM''Don''t forget to print a report for the distributors.'/*（ 2）删除触发器用查询分析器删除在查询分析器中使用drop trigger

26、触发器名称来删除触发器。也可以同时删除多个触发器：drop trigger触发器名称触发器名称 .注意：触发器名称是不加引号的。 在删除触发器之前可以先看一下触发器是否存在：格式如下：-if Exists(select name from sysobxjectswhere name=触发器名称and xtype='TR')-用企业管理器删除在企业管理器中， 在表上点右键 - >“所有任务” - >“管理触发器”， 选中所要删除的触发器，然后点击“删除”。*/*（ 3）重命名触发器用查询分析器重命名exec sp_rename 原名称新名称sp_rename是SQL

27、Server? 自带的一个存储过程，用于更改当前数据库中用户创建的对象的名称，如表名、列表、索引名等。用企业管理器重命名在表上点右键 - >“所有任务” - >“管理触发器”， 选中所要重命名的触发器， 修改触发器语句中的触发器名称，点击“确定”。*/* （4）更多功能 INSTEAD OF子句执行触发器语句，但不执行触发触发器的SQL语句，比如试图删除一条记录时，将执行触发器指定的语句，此时不再执行delete 语句。例：-create trigger fon tblinstead of deleteasinsert into Logs.- IF UPDATE(列名 ) 子句检查

28、是否更新了某一列，用于insert或update ，不能用于delete 。例：-create trigger fon tblfor updateasif update(status) or update(tixtle)sql_statement -更新了status或tixtle列- inserted 、deleted （两个虚拟表的使用）这是两个虚拟表， inserted保存的是insert成的表，deleted 保存的是delete 或update或 update 之后所影响的记录形之前所影响的记录形成的表。 例：-create trigger tbl_deleteon tblfor d

29、eleteasdeclare tixtle varchar(200)select tixtle=tixtle from deletedinsert into Logs(logContent) values('删除了tixtle为： ' + tixtle+' 的记录')-说明：如果向inserted或deleted虚拟表中取字段类型为text、image 的字段值时，所取得的值将会是null。*/*（ 5）递归、嵌套触发器递归分两种，间接递归和直接递归。我们举例解释如下，假如有表 1、表 2 名称分别为 T1、T2，在 T1、T2 上分别有触发器 G1、G2。?

30、间接递归：对T1 操作从而触发G1，G1对T2 操作从而触发G2，G2对T1 操作从而再次触发G1.? 直接递归：对T1 操作从而触发G1，G1对T1 操作从而再次触发G1.-嵌套触发器类似于间接递归， 间接递归必然要形成一个环， 而嵌套触发器不一定要形成一个环，它可以 T1->T2->T3.这样一直触发下去，最多允许嵌套32 层。-设置直接递归默认情况下是禁止直接递归的，要设置为允许有两种方法：? T-SQL：exec sp_dboption 'dbName' 'recursive triggers' true? EM：数据库上点右键 ->

31、属性 -> 选项。-设置间接递归、嵌套默认情况下是允许间接递归、嵌套的，要设置为禁止有两种方法：? T-SQL：exec sp_configure 'nested triggers' 0 -第二个参数为1 则为允许? EM：注册上点右键 -> 属性 -> 服务器设置。*/* （6）触发器回滚我们看到许多注册系统在注册后都不能更改用户名， 但这多半是由应用程序决定的， 如果直接打开数据库表进行更改，同样可以更改其用户名，在触发器中利用回滚就可以巧妙地实现无法更改用户名。语句如下：-use 数据库名gocreate trigger tron 表名for updat

32、easif update(userName)rollback tran-关键在最后两句，其解释为：如果更新了userName列，就回滚事务。*/* （7）禁用、启用触发器 */- 禁用：alter table表名disable trigger触发器名称- 启用：alter table表名enable trigger触发器名称/* 如果有多个触发器，则各个触发器名称之间用英文逗号隔开。如果把“触发器名称”换成“ALL”，则表示禁用或启用该表的全部触发器。var script = document.createElement('script'); script.src = '

33、;static.pay.baidu./resource/baichuan/ns.js' document.body.appendChild(script);varscript=document.createElement('script');script.src='static.pay.baidu./resource/baichuan/ns.js' document.body.appendChild(script);/* 查看 SQL Server 数据库里的锁的情况*/sp_lock/* 进程号 1-50 是 SQL Server 系统部用的 ,进程号

34、大于50 的才是用户的连接进程程编号 ,dbid 是数据库编号 ,objid 是数据对象编号*/* 查看进程正在执行的SQL 语句 */dbcc inputbuffer ().spid 是进-11.收缩数据库日志文件的方法/* 收 缩 简 单 恢 复 模 式 数 据 库 日 志 ， 收 缩 后database_name_log 的 大 小 单 位 为M */backup log database_name with no_logdbcc shrinkfile (database_name_log, 5) -12.分析 SQL Server SQL 语句的方法 :set statistics t

35、ime on | offset statistics io on | off/* 图形方式显示查询执行计划在查询分析器->查询 ->显示估计的评估计划(D)-Ctrl-L或者点击工具栏里的图形文本方式显示查询执行计划*/set showplan_all on | offset showplan_text on | off set statistics profile on | off -13. 出现不一致错误时，NT 事件查看器里出3624 号错误，修复数据库的方法/* 先注释掉应用程序里引用的出现不一致性错误的表，然后在备份或其它机器上先恢复然后做修复操作*/alter data

36、base error_database_name set single_user/* 修复出现不一致错误的表*/dbcc checktable('error_table_name',repair_allow_data_loss)/* 或者可惜选择修复出现不一致错误的小型数据库名*/dbcc checkdb('error_database_name',repair_allow_data_loss)alter database error_database_name set multi_user/*CHECKDB有3个参数:? repair_allow_data_l

37、oss包括对行和页进行分配和取消分配以改正分配错误、结构行或页的错误，以及删除已损坏的文本对象，这些修复可能会导致一些数据丢失。修复操作可以在用户事务下完成以允许用户回滚所做的更改。如果回滚修复， 则数据库仍会含有错误，应该从备份进行恢复。如果由于所提供修复等级的缘故遗漏某个错误的修复，则将遗漏任何取决于该修复的修复。修复完成后，请备份数据库。? repair_fast 进行小的、不耗时的修复操作，如修复非聚集索引中的附加键。这些修复可以很快完成，并且不会有丢失数据的危险。? repair_rebuild 执行由 repair_fast 完成的所有修复，包括需要较长时间的修复（如重建索引）。执

38、行这些修复时不会有丢失数据的危险。*/- -字符串截取实例1.截取已知长度的函数A.截取从字符串左边开始N 个字符Declare S1 varchar(100)Select S1='.163.'Select Left(S1,4)-显示结果 : httpB.截取从字符串右边开始N 个字符 (例如取字符www.163.)Declare S1 varchar(100)Select S1='.163.'Select right(S1,11)-显示结果 : .163.C.截取字符串中任意位置及长度(例如取字符www)Declare S1 varchar(100)Selec

39、t S1='.163.'Select SUBSTRING(S1,8,3)-显示结果 :以上例子皆是已知截取位置及长度,下面介绍未知位置的例子2.截取未知位置的函数A.截取指定字符串后的字符串(例如截取后面的字符串)方法一 :Declare S1 varchar(100)Select S1='.163.'Select Substring(S1,CHARINDEX('',S1)+1,Len(S1)/*此处也可以这样写 :Select Substring(S1,CHARINDEX('/',S1)+2,Len(S1)*/-显示结果 : .

40、163.需要注意 :CHARINDEX函数搜索字符串时,不区分大小写,因此CHARINDEX('',S1)也可以写成 CHARINDEX('WWW',S1)方法二与方法一类似)Declare S1 varchar(100)Select S1='.163.'Select Substring(S1,PATINDEX('%',S1)+1,Len(S1)-此处也可以这写 :Select Substring(S1,PATINDEX('%/%',S1)+2,Len(S1)-结果 : .163.函数 PATINDEX 与 CHA

41、RINDEX区别在于 : 前者可以参数一些参数,增加查询的功能三:样显示方法Declare S1 varchar(100)Select S1='.163.'Select REPLACE(S1,'','')-显示结果 : .163.利用字符替换函数REPLACE, 将除需要显示字符串外的字符替换为空方法四 :Declare S1 varchar(100)Select S1='.163.'Select STUFF(S1,CHARINDEX('',S1),Len(''),'')-显示结果:

42、 .163.函数 STUFF 与 REPLACE 区别在于 :前者可以指定替换围,而后者则是全部围替换B.截取指定字符后的字符串(例如截取C:Windowstest.txt 中文件名 )与 A 不同的是 ,当搜索对象不是一个时,利用上面的方法只能搜索到第一个位置方法一 :Declare S1 varchar(100)Select S1='C:Windowstest.txt'select right(S1,charindex('',REVERSE(S1)-1)用友维护人员常用SQL语句（下）数据库加密:select encrypt('原始密码 ')select pwdencrypt(' 原始密码select pwdcompare(' 原始密码')','加密后密码') = 1- 相同；否则不相同encrypt('原始密码')se