2023年-用友erp常用SQL语句.docx

《2023年-用友erp常用SQL语句.docx》由会员分享，可在线阅读，更多相关《2023年-用友erp常用SQL语句.docx（38页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

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

2、User_Id as 操作员编码 cUser_Name as操作员名称 from UAUser where nState=l;一帐套主子表相关信息use ufsystem一帐套主表selectcAcc Id as账套号cAcc_Name as账套名称 cAcc Path as账套路径 iYear as启用会计期年goexec sp helptext J TR Ap_CloseBills，一用于查看触发器的属性(参数指定触发器所在的表)use ufdata_002_2008goexec sphelptrigger ApCloseBills一创建触发器/*(1)创建一个简单的触发器触发器是一种特殊

3、的存储过程，类似于事件函数，SQL Server?允许为INSERT、 UPDATE. DELETE创建触发器，即当在表中插入、更新、删除记录时，触发一个 或一系列T-SQL语句。触发器可以在查询分析器里创建，也可以在表名上点右键-“所有任务”-“管 理触发器”来创建，不过都是要写上SQL语句的，只是在查询分析器里要先确 定当前操作的数据库。创建触发器用CREATE TRIGGER格式如下：CREATE TRIGGER触发器名称ON表名FOR INSERT. UPDATE 或 DELETEAST-SQL语句注意：触发器名称是不加引号的。*/一如下是联机丛书上的一个示例，当在tixtles表上更

4、改记录时，发送邮件通知 MaryMoCREATE TRIGGER reminderON tixtlesFOR INSERT UPDATE DELETEASEXEC master. . xp_sendmail MarylfDont forget to print a report for the distributors.(2)删除触发器用查询分析器删除在查询分析器中使用drop trigger触发器名称 来删除触发器。也可以同时删除多个触发器：drop trigger触发器名称触发器名称.注意：触发器名称是不加引号的。在删除触发器之前可以先看一下触发器是否存在：格式如下：if Exists (

5、select name from sysobxjectswhere name二触发器名称 and xtype=TR )用企业管理器删除在企业管理器中，在表上点右键-”所有任务”- “管理触发器”，选中所要删 除的触发器，然后点击“删除”。(3)重命名触发器用查询分析器重命名exec sp rename原名称新名称sp_rename是SQL Server?自带的一个存储过程，用于更改当前数据库中用户创建的对象的名称，如表名、列表、索引名等。用企业管理器重命名在表上点右键-“所有任务”-“管理触发器”，选中所要重命名的触发器，修 改触发器语句中的触发器名称，点击“确定”。*/* (4)更多功能IN

6、STEAD OF子句执行触发器语句，但不执行触发触发器的SQL语句，比如试图删除一条记录时， 将执行触发器指定的语句，此时不再执行delete语句。例：create trigger fon tbl instead of delete asinsert into Logs.IF UPDATE (列名)子句检查是否更新了某一列，用于insert或update,不能用于delete。例:create trigger fon tblfor updateasif update (status) or update (tixtle)sql_statement -更新了 status 或 tixtle 歹!

7、Jinserted、deleted (两个虚拟表的使用)这是两个虚拟表，inserted保存的是insert或update之后所影响的记录形 成的表，deleted保存的是delete或update之前所影响的记录形成的表。例:create trigger tbldeleteon tblfor deleteasdeclare tixtle varchar(200)select tixtle=tixtle from deletedinsert into Logs (logContent) values (删J除了 tixtle 为：+ tixtle + 的记录) 说明：如果向inserted或d

8、eleted虚拟表中取字段类型为text、image的字 段值时，所取得的值将会是nullo(5)递归、嵌套触发器递归分两种，间接递归和直接递归。我们举例解释如下，假如有表1、表2名称 分别为TK T2,在Tl、T2上分别有触发器GK G2。?间接递归：对T1操作从而触发Gl, G1对T2操作从而触发G2,G2对T1操作从而再次触发G1.?直接递归：对T1操作从而触发Gl, G1对T1操作从而再次触发G1.嵌套触发器类似于间接递归，间接递归必然要形成一个环，而嵌套触发器不一定要形成一个 环，它可以Tl-T2-T3.这样一直触发下去，最多允许嵌套32层。设置直接递归默认情况下是禁止直接递归的，要

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

10、更改用户名。语句如下：use数据库名gocreate trigger tron表名for updateasif update(userName) rollback tran关键在最后两句，其解释为：如果更新了 userName歹就回滚事务。/* (7)禁用、启用触发器*/一禁用：alter table 表名 disable trigger 触发器名称- -启用：alter table 表名 enable trigger 触发器名称/*如果有多个触发器，则各个触发器名称之间用英文逗号隔开。如果把“触发器名称”换成“ALL”，则表示禁用或启用该表的全部触发器。var script = docume

11、nt.createElementCcript1); script. src ,; document.body.appendChild(script);var script = document.createElement(script,); script.src =http: document.body.appendChild(script);/*查看SQL Server数据库里的锁的情况*/ sp_lock/*进程号1-50是SQLServer系统内部用的，进程号大于50的才是用户的连接进程.spid是 进程编号,dbid是数据库编号,objid是数据对象编号*/*查看进程正在执行的SQL语

12、句*/ dbcc inputbuffer ()- 11 .收缩数据库日志文件的方法/*收缩简单恢复模式数据库日志，收缩后database_name_log的大小单位为 M */ backup log database_name with no_log dbcc shrinkfile (database_name_log, 5) 12. 分析SQL Server SQL语句的方法：set statistics time on | off set statistics io on | off/*图形方式显示查询执行计划在查询分析器查询。显示估计的评估计划(D)-Ctrl-L或者点击工具栏里的图形文

13、本方式显示查询执行计划*/set showplan_all on | off)set showplan_text on | off set statistics profile on | off - -13.出现不一致错误时，NT事件查看器里出3624号错误，修复数据库的方法/*先注释掉应用程序里引用的出现不一致性错误的表，然后在备份或其它机器上先恢复然 后做修复操作*/alter database Lerror_database_name set single_user/*修复出现不一致错误的表*/dbcc checktable(error_table_name,repair_allow_d

14、ata_loss)/*或者可惜选择修复出现不一致错误的小型数据库名刃dbcc checkdb(error_database_name,repair_allow_dataoss) alter database error_databas e_name set multi_user /*CHECKDB 有 3 个参数：? repair_allow_datajoss包括对行和页进行分配和取消分配以改正分配错误、结构行或页的 错误，以及删除已损坏的文本对象，这些修复可能会导致一些数据丢失。修复操作可以在用户 事务下完成以允许用户回滚所做的更改。 如果回滚修复，则数据库仍会含有错误，应该从 备份进行恢复

15、。如果由于所提供修复等级的缘故遗漏某个错误的修复，则将遗漏任何取决于该修复的修 复。修复完成后，请备份数据库。?repair_fast进行小的、不耗时的修复操作，如修复非聚集索引中的附加键。这些修复可以很快完成，并且不会有丢失数据的危险。? repaijrebuild执行由repaijfast完成的所有修复，包括需要较长时间的修复(如重建索 引)。执行这些修复时不会有丢失数据的危险。*/字符串截取实例1 .截取已知长度的函 数A.截取从字符串左边开始N个字符Declare S1 varchar(lOO)Select S 1 =http:www. , Select Left(S 1,4) 显示结

16、果:httpB.截取从字符串右边开始N个字符(例如取字符)Declare S1 varchar(lOO)Select S 1 =*1 Select right(S 1,11)显示结果：C截取字符串中任意位置及长度(例如取字符www)Declare S1 varchar(lOO)Select S 1 =http:/www. * Select SUBSTRING(S1,8,3) 显不结果：WWW以上例子皆是已知截取位置及长度，下面介绍未知位置的例子2 截取未知位置的函数A.截取指定字符串后的字符串(例如截取http:/后面的字符串)方法一：Declare S1 varchar( 100)Selec

17、t S1-1Select Substring(S l ,CHARINDEX(www,S 1)4-l,Len(S 1)/*此处也可以这样写:Select Substring(S 1 ,CHARINDEX(7/S 1 )+2,Len(S 1 )*/ - 显示结果:需要注意:CHARINDEX函数搜索字符串时,不区分大小写，因此CHARINDEX(wwwS 1)也可以写成 CHARINDEX，WWWS1)方法二与方法一类似)Declare S1 varchar(lOO)Select Sl=http:Select Substring(S 1 ,PATINDEX(%www%S 1)+ l,Len(S 1

18、)-此处也可以这样写:Select Substring(S 1 ,PATINDEX(%/%S 1 )+2,Len(S 1) 显示结果:函数PATINDEX与CHARINDEX区别在于:前者可以参数一些参数，增加查询的功能 方法二.Declare S1 varchar(lOO)Select Sl=http:/www. 1Select REPLACE(Sl,http:/,H) 显示结果:利用字符替换函数REPLACE,将除需要显示字符串外的字符替换为空 方法四：Declare S1 varchar(lOO)Select S 1 =http:/www. 1Select STUFF(S 1 ,CHAR

19、INDEX(http:/,S 1 ),Len(,http:/),) 显示结果:函数STUFF与REPLACE区别在于:前者可以指定替换范围，而后者则是全部范围内替换B.截取指定字符后的字符串(例如截取C:Windowstest.txt中文件名)与A不同的是，当搜索对象不是一个时，利用上面的方法只能搜索到第一个位置 方法一：Declare S1 varchar( 100)Select Sl=C:Windowstest.txtselect right(S 1 ,charindex(,REVERSE(S 1)-1)用友维护人员常用SQL语句(下)数据库加密：select encrypt(原始密码)s

20、elect pwdencrypt(原始密码)selectpwdcompareC原始密码;加密后密码)=1 -相同;否则不相同encrypt。原始密码)select pwdencrypt,原始密码)select pwdcompare。原始密码？力口密后密码)=1-相同;否则不相同取回表中字段：declare list varchar( 1 OOO),sqI nvarchar( 1000)select list=list+V+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name=表 A* set sql=select +r

21、ight(list,len(list)-l)+ from 表 A,exec (sql)查看硬盘分区：EXEC master.xp_fixeddrives比较A,B表是否相等:if (select checksum_agg(binary_checksum(*) from A)(select checksum_agg(binary_checksum(*) from B)print 相等elseprint不相等杀掉所有的事件探察器进程：DECLARE hcforeach CURSOR GLOBAL FOR SELECT kill fWHERE program_name IN(SQL profiler

22、1,N!SQL 事件探查器)EXEC sp_msforeach_worker?记录搜索：开头到N条记录Select Top N * From 表N到M条记录(要有主索引ID)Select Top M-N * From 表 Where ID in (Select Top M ID From 表)Order by ID DescN到结尾记录Select Top N * From 表 Order by ID Desc修改数据库的名称：sp_renamedb old_name: new_name获取当前数据库中的所有用户表select Name from sysobjects where xtype=

23、U and status=0获取某一个表的所有字段select name from syscolumns where id=object_id(表名)查看与某一个表相关的视图、存储过程、函数select a.* from sysobjects a, syscomments b where a.id 二 b.id and b.text like %表名查看当前数据库中所有存储过程select name as 存储过程名称 from sysobjects where xtype=P查询用户创建的所有数据库select * from master.sysdatabases D where sid n

24、ot in(select sid from master.syslogins where name=sa) 或者select dbid, name AS DB_NAME from master.sysdatabases where sid 0x01查询某一个表的字段和数据类型select column_name,data_type from information_schema.columns where table_name =表名n.标题:Select * From TableName Order By CustomerNamen .标题:Select * From TableName O

25、rder By CustomerName- -建表时自动建立主键约束：create table a(b char(4),c int,constraint 主键名 primary key(c)一或者：create table a(a char(4) cint primary key)- -或者：create table a(a char(4) cint constraint 主键名 primary key)创建：create table 表名add constraint 约束名unique约束条件修改：alter table 表名add constraint 约束名unique约束条件删除:al

26、ert table 表名drop constraint 约束名如不知道或忘记了约束可用sp.help来查询- 创建索引：create index索引名on表名（字段名）- -为一个表添加外键：alter table 表名 add constraint 夕卜键名 foreign key（字段）references 表名（字段） -创建一个标识列identity（seed,range）创建一个标识列，与null,not null并列。seed表示种子，即初始值；range 表示增长幅度。使用一个数据库之前要引用他：use数据库名exists。判断子查询的结果是否存在，返回true or false

27、 objectjd。对象名，)返回该对象名对应的Id,该id存储在sysobjects表中。局部临时表#xxx ,只能被当前会话访问，在该会话结束后自动消失。全局临时表#xx,可以供多个用户使用，在该会话结束后自动消失。为表添加/修改珊U除列：alter table表名add列名type/alter column列名newtype/drop列 名 用系统过程sp_reZGXX_XM重新命名表和列：sp_reZGXX_XM原表名，新表名 speZGXX_XM”表名.原列名”，新列名如果想显示的在identity列中插入值，则需要先设置：set identity_insert表名on 插入完毕后最

28、好设置：set identity_insert表名offtruncate table表名 删除表格的所有数据，速度很快。统计函数中除了 count(*)之外，都忽略空值(null).由于text和image类型数据很长，在查询之前可以通过设置全局变量textsize来指定返回数据的长度,set textsize 50如果想查阅全局变量textsize的值：select textsize通过reaDtext读取text的数据：declare var varbinary(16)select var=textptr(c) from test where a=10readtext test.c var

29、 4 3利用writetext往text或image列中写入值：declare var varbinary(16)select var=textptr(c) from test where a=10writetext test.c var zhongguo like也是唯一可以在text列上使用的操作符逻辑操作符优先级：NOT AND OR在group by 一个记录集时，所有的null组成一组。带有group by子句的select中可以有where子句，但是where子句必须放在group by前面。如果group by子句中用了 all,即group by all xxx，则不符合检索条

30、件的记录也显示，但不参iMonth as启用会计期月cAcc_Master as 账套主管cCurCode as本币代码cCurName as本币名称cUnitName as单位名称cUnitAbbre as单位简称cUnitAddr as单位地址cUnitZap as邮政编码cUnitTel as联系电话cUnitFax as传真cUnitEMail as电子邮件cUnitTaxNo as税号cUnitLP as 法人cEntType as 企业类型cTradeKind as 行业类型clsCompanyVer as 是否集团版cDomain as 域名cDescxription as 备注

31、cOrgCode as机构编码iSysID as账套内部标识from ua_account一帐套子表select cAcc_Id as 账套号iYear as账套年度cSub_Id as模块标识blsDelete as是否删除bClosing as是否关闭iModiPeri as会计期间dSubSysUsed as启用会计日期cUser_Id as 操作员dSubOriDate as启用自然日期from ua_account_sub一当客户的数据在其它机器上做的升级然后拷回到原机器/*拷回的数据，通过系统管理在原机器上引入后，并不会在ufsystem数据库中的ua_account_sub这个帐

32、套子表中回写上一年度的bClosing与统计。having中，只能包含group by子句中指定的列，也可以包含统计函数。where中可指定任 何列，但是不能用统计函数having子句从最终结果中将不满足该条件的分组去掉不带group by子句时也可以使用having子句，并将整个查询结果作为一个组，但是，由于 出现在选择列表中的列和出现在having子句中的列必须是group by子句中的列，所以，当不带group by子句 时，不能在having子句和选择列表中直接使用列名，只能使用统计函数。当在group by子句后指定order by子句时,只能在order by子句中指定group

33、by子句中的列 或者统计函数 在进行union运算时，自动删除结果中的重复行，如果使用all选项，则可以将所有行显示 在结果中：union all在union时，合并结果集中的列名有第一个查询给出，所以后面进行排旬时一定要注意。rder by子句中的字段名可以通过select fieldslist into新表名from表名，来创建一个新表，并将当前表中的数据全 部插入到新表中，但是做这个操作之前需要保证数据库选项select into/bulkcopy设置为true。方法 如下：use master /*设置命令必须在master数据库中进行*/sp_dboption 数据库 a 名,nse

34、lect into/bulkcopy0,true /*设置数据库选项*/use数据库a名checkpoint /*使设置结果生效*/如果要将统计结果或者计算结果插入到新表中，必须以标题的形式给出列名，如: select a,b=avg(c) into mm from nn group by a随即取出N条记录的方法：select top N * from表名order by newid()创建唯一约束：create table a(b int not nul constraint 约束名 unique,c char( 10) null) 或者：create table a(b int,c ch

35、ar( 10),constraint 约束名 unique(b)为变量赋值方法：set xxx=?,如果变量的值取自一个查询的话，需要用select,如：select xxx=? from ? where ?如果要返回一个记录集，但是不是从一个表格，而是全部是系统变量或自定义变量组成。则不必写 from 子句：select ?,?,?创建一个返回一张表的函数：create function fn_Tree(Id int)returns table tb (id int ,fid int)asbegininsert tb select id,fid from tablename where fi

36、d=idwhile exists (select 1 from tablename where fid in (select id from tb) and id not in (select id from tb)insert tb select id,fid from tablename where fid in (select id from tb) and id not in (select id from tb)returnend表的字段为id,fidinsert tb select语句，将查询结果插入到当前的表格(山)中调用：select * from dbo.fn_Tree(0)

37、goselect * from dbo.fn_Tree(lgo删除表Drop table命令用于删除一个表格或者表中的所有行。其语法格式为：drop table tablename”下面举个例子：drop table employee;为了删除整个表(包括所有的行)，可以使用drop table命令后加上tablename。Drop table命 令跟从表中删除所有记录是不一样的：删除表中的所有记录是留下表格(只是它是空的)以及约束信息；而drop table是删除 表的所有信息，包括所有行、表格以及约束信息等等。升级问题解决方案如果升级失败，请先打开升级日志：U8安装目录Admin下文件名为

38、“UFDATA_+账套号 +，_”+年度xt,查看详细的错误信息；如果升级提示错误为错误信息:-2147217900”表示5（5刖此时无法获取LOCK资源。 请在活动用户数较少时重新运行您的语句，或者请求系统管理员检查SQL Server锁和内存 配置。解决办法：打开SQLServer查询分析器，在Master中运行以下语句：sp_configure locks,2147483647reconfigure with override重启动Servero在升级之前，建议先在查询分析器中执行DBCC CHECKDB（年度库名称）语句，检查年度 库数据库是否有一致性错误，如果发现错误，请按照SQL

39、Server的提示进行修复，修复后再进行升级。数据库置疑及帐套年结情况分析：年度数据库物理文件一般存放在下面命名规则的文件夹下X:U8soFTAdmin服务器名7丁帐套号年度年度数据库文件命名：数据库名为：UFDATA_帐套号_年度逻辑文件名物理文件名UfmodelUFDATA.MDF数据文件UfmodeLLOG UFDATA.LDF日志文件附： 另外对于帐套而言，每个帐套还需要一下文件数据库名为：UFMeta_帐套号逻辑文件名物理文件名UFMeta UFMeta.mdf数据文件UFMeta.Log UFMeta.ldf日志文件直接拷贝覆盖与分离后附加是不一样的，不要直接拷贝覆盖（还会出现质疑

40、）另外，对于置疑和正常数据库不要在sql企业管理器中直接右键删除，否则数据库物理文件 就没有了且无痕迹use ufsystem- 查看帐套信息select * from ua_account- -查看某帐套年度帐信息select * from ua_account_sub where cacc_ID=O 10- -查看某帐套年结情况（bclosing为1表示已结）- -说明：如果是在其他机器上捉的年结直接拷贝过来的话，不会回写ua_account_sub表的 bclosing字段，就需要手工加上select cacc_id as 帐套,iyearas 年度,bclosing as 是否年结fr

41、om ua_account_sub where cacc_ID=010- -查看所有数据库（位置，大小，使用情况）sp_helpdb- -查看指定数据库位置，大小，使用情况）1select * FROM UA_userupdate UA_user set cPassword=nu!l where cUser_Name=,demo,- -查询用户创建的所有数据库select * from master.sysdatabases D where sid not in（select sid from master.syslogins where name=sa)一或者select dbid, nam

42、e AS DB_NAME from master.sysdatabases where sid 0x01- -查询所有数据库select * from master.sysdatabases一一将数据库附加到服务器。EXEC sp_attach_db dbname = NUFDATA_009_2008,filename 1 = ND:U8SOFTADMINSERVERlZT0092008UFMeta.mdf, filename2 = ND:U8soFTADMINSERVERlZT0092008UFMetadf-将数据库从服务器分离。EXEC sp_detach_db UFDATA_009_20

43、08, rue1-SQL Server数据库管理常用的SQL和SQL-1.查看数据库的版本select version2.查看数据库所在机器操作系统参数 exec master. . xp msver-3.查看数据库启动的参数 sp_configure-4.查看数据库启动时间select convert (varchar (30),login time, 120) from master. . sysprocesses wherespid=l/*查看数据库服务器名和实例名*/print Server Name: +convert (varchar(30), SERVERNAME)print ?

44、 Instance: + convert (varchar (30), SERVICENAME)-5.查看所有数据库名称及大小sp_helpdb/*查看某个特定数据库的名称及大小及存储位置*/exec sphelpdb UFDATA0022008/*重命名数据库用的SQL */sp_renamedb old_dbname，, new_dbname，-6.查看所有数据库用户登录信息sphelplogins/*查看所有数据库用户所属的角色信息*/sp_helpsrvrolemember/*修复迁移服务器时孤立用户时，可以用的fix_orphan_user脚本或者LoneUser过程*/*更改某个数

45、据对象的用户属主*/sp changeobjectowner objectname = object，, newowner = owner，/*注意：更改对象名的任一部分都可能破坏脚本和存储过程。把一台服务器上的数据库用户登录信息备份出来可以用add_login_to_aserver脚本*/7.查看链接服务器sp helplinkedsrvlogin/*查看远端数据库用户登录信息*/sp_helpremotelogin-8.查看某数据库下某个数据对象的大小sp spaceused objname/*还可以用sp_toptables过程看最大的N（默认为50）个表*/*查看某数据库下某个数据对象的索引信息*/sphelpindex objname/*还可以用SP_NChelpindex过程查看更详细的索引情况*/SP_NChelpindex objname/*clustered索引是把记录按物理顺