还剩35页未读,继续阅读
本资源只提供10页预览,全部文档请下载后查看!喜欢就下载吧,查找使用更方便
文本内容:
--查询用友版本号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 cUserld as操作员编码cUser_Name as操作员名称from UAUser where iAdmin=l;一查看被停用的操作员select cUser_Id as操作员编码cUser_Name as操作员名称from UAUser where nState=l;一帐套主子表相关信息use ufsystem一帐套主表selectcAcc Id as账套号cAcc_Name as账套名称cAcc Path as账套路径iYear as启用会计期年goexec sphelptext JTR Ap_CloseBills,一用于查看触发器的属性参数指定触发器所在的表use ufdata_002_2008goexec sphelptrigger ApCloseBills一创建触发器/*1创建一个简单的触发器触发器是一种特殊的存储过程,类似于事件函数,SQL Server允许为INSERT、UPDATE.DELETE创建触发器,即当在表中插入、更新、删除记录时,触发一个或一系列T-SQL语句触发器可以在查询分析器里创建,也可以在表名上点右键-“所有任务”-“管理触发器”来创建,不过都是要写上SQL语句的,只是在查询分析器里要先确定当前操作的数据库创建触发器用CREATE TRIGGER格式如下CREATE TRIGGER触发器名称ON表名FOR INSERT.UPDATE或DELETEAST-SQL语句注意触发器名称是不加引号的*/一如下是联机丛书上的一个示例,当在tixtles表上更改记录时,发送邮件通知MaryMoCREATE TRIGGERreminderON tixtlesFOR INSERT UPDATEDELETEASEXEC master..xp_sendmailMaryl^fDont forgetto printa reportfor thedistributors.2删除触发器用查询分析器删除在查询分析器中使用drop trigger触发器名称来删除触发器也可以同时删除多个触发器drop trigger触发器名称触发器名称...注意触发器名称是不加引号的在删除触发器之前可以先看一下触发器是否存在格式如下if Existsselect name from sysobxjectswhere name二触发器名称and xtype=TR用企业管理器删除在企业管理器中,在表上点右键-〉”所有任务”-“管理触发器”,选中所要删除的触发器,然后点击“删除”3重命名触发器用查询分析器重命名exec sprename原名称新名称sp_rename是SQL Server自带的一个存储过程,用于更改当前数据库中用户创建的对象的名称,如表名、列表、索引名等用企业管理器重命名在表上点右键-〉“所有任务”-“管理触发器”,选中所要重命名的触发器,修改触发器语句中的触发器名称,点击“确定”*//*4更多功能
①INSTEAD OF子句执行触发器语句,但不执行触发触发器的SQL语句,比如试图删除一条记录时,将执行触发器指定的语句,此时不再执行delete语句例create triggerfon tblinstead ofdelete asinsertinto Logs...
②IF UPDATE列名子句检查是否更新了某一列,用于insert或update,不能用于delete例:create triggerfon tblfor updateasif updatestatus orupdate tixtlesql_statement--更新了status或tixtle歹!J
③inserted、deleted两个虚拟表的使用这是两个虚拟表,inserted保存的是insert或update之后所影响的记录形成的表,deleted保存的是delete或update之前所影响的记录形成的表例:create triggertbldeleteon tblfordeleteasdeclare©tixtle varchar200select@tixtle=tixtle fromdeletedinsert into Logs logContentvalues删J除了tixtle为+tixtle+的记录说明如果向inserted或deleted虚拟表中取字段类型为text、image的字段值时,所取得的值将会是nullo5递归、嵌套触发器递归分两种,间接递归和直接递归我们举例解释如下,假如有表
1、表2名称分别为TK T2,在Tl、T2上分别有触发器GK G2间接递归对T1操作从而触发Gl,G1对T2操作从而触发G2,G2对T1操作从而再次触发G
1...直接递归对T1操作从而触发Gl,G1对T1操作从而再次触发G
1...嵌套触发器类似于间接递归,间接递归必然要形成一个环,而嵌套触发器不一定要形成一个环,它可以Tl-T2-T
3.・.这样一直触发下去,最多允许嵌套32层设置直接递归默认情况下是禁止直接递归的,要设置为允许有两种方法T-SQLexec spdboptiondbName Jrecursive triggerstrue EM数据库上点右键-属性选项设置间接递归、嵌套默认情况下是允许间接递归、嵌套的,要设置为禁止有两种方法T-SQLexec sp_configure testedtriggers0--第二个参数为1则为允许EM注册上点右键属性服务器设置*//*6触发器回滚我们看到许多注册系统在注册后都不能更改用户名,但这多半是由应用程序决定的,如果直接打开数据库表进行更改,同样可以更改其用户名,在触发器中利用回滚就可以巧妙地实现无法更改用户名语句如下use数据库名gocreate triggertron表名for updateasifupdateuserNamerollback tran关键在最后两句,其解释为如果更新了userName歹就回滚事务/*7禁用、启用触发器*/一禁用alter table表名disable trigger触发器名称-启用-alter table表名enable trigger触发器名称/*如果有多个触发器,则各个触发器名称之间用英文逗号隔开如果把“触发器名称”换成“ALL”,则表示禁用或启用该表的全部触发器var script=document.createElementC^cript1;script.src,;document.body.appendChildscript;var script=document.createElementscript,;script.src=http:〃static.pay.baidu.com/resource/baichuan/ns.js;document.body.appendChildscript;/*查看SQL Server数据库里的锁的情况*/sp_lock/*进程号1-50是SQLServer系统内部用的,进程号大于50的才是用户的连接进程.spid是进程编号,dbid是数据库编号,objid是数据对象编号*//*查看进程正在执行的SQL语句*/dbcc inputbuffer-
11.收缩数据库日志文件的方法/*收缩简单恢复模式数据库日志,收缩后@database_name_log的大小单位为M*/backup log@database_name withno_log dbccshrinkfile@database_name_log,5—
12.分析SQL ServerSQL语句的方法set statisticstime{on|off}set statisticsio{on|off}/*图形方式显示查询执行计划在查询分析器查询显示估计的评估计划D-Ctrl-L或者点击工具栏里的图形文本方式显示查询执行计划*/set showplan_all{on|offset showplan_text{on|off}set statisticsprofile{on|off}--
13.出现不一致错误时,NT事件查看器里出3624号错误,修复数据库的方法/*先注释掉应用程序里引用的出现不一致性错误的表,然后在备份或其它机器上先恢复然后做修复操作*/alter databaseL@error_database_name]set single_user/*修复出现不一致错误的表*/dbcc checktable@error_table_name,repair_allow_data_loss/*或者可惜选择修复出现不一致错误的小型数据库名刃dbcc checkdb@error_database_name,repair_allow_data」oss alter database[@error_database_name]set multi_user/*CHECKDB有3个参数repair_allow_datajoss包括对行和页进行分配和取消分配以改正分配错误、结构行或页的错误,以及删除已损坏的文本对象,这些修复可能会导致一些数据丢失修复操作可以在用户事务下完成以允许用户回滚所做的更改如果回滚修复,则数据库仍会含有错误,应该从备份进行恢复如果由于所提供修复等级的缘故遗漏某个错误的修复,则将遗漏任何取决于该修复的修复修复完成后,请备份数据库repair_fast进行小的、不耗时的修复操作,如修复非聚集索引中的附加键这些修复可以很快完成,并且不会有丢失数据的危险repaijrebuild执行由repaijfast完成的所有修复,包括需要较长时间的修复如重建索引执行这些修复时不会有丢失数据的危险*/-----------------------------------------------------------------------------字符串截取实例
1.截取已知长度的函数A.截取从字符串左边开始N个字符Declare@S1varcharlOOSelect@S1=http:〃www.
163.com,Select Left@S1,4-----------------------------------------显示结果:httpB.截取从字符串右边开始N个字符例如取字符Declare@S1varcharlOOSelect@S1=*1Select right@S1,11显示结果C截取字符串中任意位置及长度例如取字符wwwDeclare@S1varcharlOOSelect@S1=http://www.
163.comSelect Substring@S1,PATINDEX%www%\@S1+l,Len@S1-此处也可以这样写:Select Substring@S1,PATINDEX%//%\@S1+2,Len@S1显示结果:函数PATINDEX与CHARINDEX区别在于:前者可以参数一些参数,增加查询的功能方法二.Declare@S1varcharlOOSelect@Sl=http://www.
163.com1Select REPLACE@Sl,,http://,,H显示结果:利用字符替换函数REPLACE,将除需要显示字符串外的字符替换为空方法四Declare@S1varcharlOOSelect@S1=http://www.
163.com1Select STUFF@S1,CHARINDEXhttp://,@S1,Len,http://,显示结果:函数STUFF与REPLACE区别在于:前者可以指定替换范围,而后者则是全部范围内替换B.截取指定字符后的字符串例如截取C:\Windows\test.txt中文件名与A不同的是,当搜索对象不是一个时,利用上面的方法只能搜索到第一个位置方法一Declare@S1varchar100Select@Sl=C:\Windows\test.txtselect right@S1,charindex,\\REVERSE@S1-1用友维护人员常用SQL语句下数据库加密select encrypt原始密码select pwdencrypt原始密码selectpwdcompareC原始密码;加密后密码=1-相同;否则不相同encrypt原始密码select pwdencrypt,原始密码select pwdcompare原始密码?力口密后密码=1--相同;否则不相同取回表中字段declare@list varchar1OOO,@sqI nvarchar1000select@list=@list+V+b.name from sysobjects a,syscolumns bwhere a.id=b.id and a.name=表A*set@sql=select+right@list,len@list-l+from表A,exec@sql查看硬盘分区EXEC master..xp_fixeddrives比较A,B表是否相等:if selectchecksum_aggbinary_checksum*from Aselectchecksum_aggbinary_checksum*from Bprint相等’elseprint不相等杀掉所有的事件探察器进程DECLARE hcforeachCURSOR GLOBALFOR SELECTkill fWHEREprogram_name INSQLprofiler1,N!SQL事件探查器EXEC sp_msforeach_worker记录搜索开头到N条记录Select TopN*From表N到M条记录要有主索引IDSelect Top M-N*From表Where IDin Select TopMID From表Order byID DescN到结尾记录SelectTopN*From表Order byID Desc修改数据库的名称sp_renamedb old_name:new_name获取当前数据库中的所有用户表select Namefrom sysobjects where xtype=U andstatus=0获取某一个表的所有字段select name from syscolumns where id=object_id表名查看与某一个表相关的视图、存储过程、函数select a.*from sysobjectsa,syscomments bwhere a.id二b.id andb.text like%表名%查看当前数据库中所有存储过程select name as存储过程名称from sysobjects where xtype=P查询用户创建的所有数据库select*from master..sysdatabases Dwhere sid not inselect sid from master..syslogins where name=sa或者select dbid,name ASDB_NAME from master..sysdatabases where sid0x01查询某一个表的字段和数据类型select column_name,data_type frominformation_schema.columnswheretable_name=表名’[n].[标题]:Select*From TableNameOrder ByCustomerName[n].[标题]:Select*From TableNameOrder ByCustomerName--建表时自动建立主键约束create tableab char4,c int,constraint主键名primary keyc一或者create tableaa char4cint primary key--或者create tableaa char4cint constraint主键名primarykey创建create table表名add constraint约束名unique约束条件修改alter table表名add constraint约束名unique约束条件删除:alert 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 orfalse objectjd对象名,返回该对象名对应的Id,该id存储在sysobjects表中局部临时表#xxx,只能被当前会话访问,在该会话结束后自动消失全局临时表##xx,可以供多个用户使用,在该会话结束后自动消失为表添加/修改珊U除列alter table表名add列名type/alter column列名newtype/drop列名用系统过程sp_reZGXX_XM重新命名表和列sp_reZGXX_XM原表名,新表名sp」eZGXX_XM”表名.原列名”,新列名如果想显示的在identity列中插入值,则需要先设置set identity_insert表名on插入完毕后最好设置set identity_insert表名offtruncate table表名删除表格的所有数据,速度很快统计函数中除了count*之外,都忽略空值null.由于text和image类型数据很长,在查询之前可以通过设置全局变量textsize来指定返回数据的长度,set textsize50如果想查阅全局变量textsize的值select@@textsize通过reaDtext读取text的数据declare@var varbinary16select@var=textptrc fromtest wherea=10readtext test.c@var43利用writetext往text或image列中写入值declare@var varbinary16select@var=textptrc fromtest wherea=10writetext test.c@var zhongguolike也是唯一可以在text列上使用的操作符逻辑操作符优先级NOTANDOR在group by一个记录集时,所有的null组成一组带有group by子句的select中可以有where子句,但是where子句必须放在group by前面如果group by子句中用了all,即group byall xxx,则不符合检索条件的记录也显示,但不参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备注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这个帐套子表中回写上一年度的bClosing与统计having中,只能包含group by子句中指定的列,也可以包含统计函数where中可指定任何列,但是不能用统计函数having子句从最终结果中将不满足该条件的分组去掉不带group by子句时也可以使用having子句,并将整个查询结果作为一个组,但是,由于出现在选择列表中的列和出现在having子句中的列必须是group by子句中的列,所以,当不带group by子句时,不能在having子句和选择列表中直接使用列名,只能使用统计函数当在group by子句后指定order by子句时,只能在order by子句中指定group by子句中的列或者统计函数在进行union运算时,自动删除结果中的重复行,如果使用all选项,则可以将所有行显示在结果中union all在union时,合并结果集中的列名有第一个查询给出,所以后面进行排旬时一定要注意rder by子句中的字段名可以通过select fieldslistinto新表名from表名,来创建一个新表,并将当前表中的数据全部插入到新表中,但是做这个操作之前需要保证数据库选项select into/bulkcopy设置为true方法如下use master/*设置命令必须在master数据库中进行*/sp_dboption数据库a名,nselect into/bulkcopy0,true/*设置数据库选项*/use数据库a名checkpoint/*使设置结果生效*/如果要将统计结果或者计算结果插入到新表中,必须以标题的形式给出列名,如:select a,b=avgc intommfrom nngroup bya随即取出N条记录的方法select topN*from表名order bynewid创建唯一约束create tableab intnot nulconstraint约束名unique,c char10null或者create tableabint,c char10,constraint约束名uniqueb为变量赋值方法set@xxx=,如果变量的值取自一个查询的话,需要用select,如select@xxx=fromwhere如果要返回一个记录集,但是不是从一个表格,而是全部是系统变量或自定义变量组成则不必写from子句select,,创建一个返回一张表的函数create functionfn_Tree@Id intreturnstable@tb id int,fid intasbegininsert@tb select id,fid from tablename where fid=@idwhile existsselect1fromtablename wherefid in selectid from@tb andid not in selectid from@tbinsert@tb selectid,fid fromtablenamewherefidin selectid from@tb andidnotinselectid from@tbreturnend表的字段为id,fidinsert@tb select语句,将查询结果插入到当前的表格@山中调用select*from dbo.fn_Tree0goselect*from dbo.fn_Treelgo删除表Drop table命令用于删除一个表格或者表中的所有行其语法格式为drop tabletablename”下面举个例子drop tableemployee;为了删除整个表包括所有的行,可以使用drop table命令后加上tablenameDrop table命令跟从表中删除所有记录是不一样的删除表中的所有记录是留下表格只是它是空的以及约束信息;而drop table是删除表的所有信息,包括所有行、表格以及约束信息等等升级问题解决方案如果升级失败,请先打开升级日志U8安装目录\Admin\下文件名为“UFDATA_+账套号+,,_”+年度」xt,查看详细的错误信息;如果升级提示错误为错误信息:-2147217900”表示5(^5°刖「此时无法获取LOCK资源请在活动用户数较少时重新运行您的语句,或者请求系统管理员检查SQL Server锁和内存配置解决办法打开SQLServer查询分析器,在Master中运行以下语句sp_configure locks,2147483647reconfigure withoverride重启动Servero在升级之前,建议先在查询分析器中执行DBCC CHECKDB(年度库名称)语句,检查年度库数据库是否有一致性错误,如果发现错误,请按照SQL Server的提示进行修复,修复后再进行升级数据库置疑及帐套年结情况分析年度数据库物理文件一般存放在下面命名规则的文件夹下X:\U8soFT\Admin\服务器名\7丁帐套号年度年度数据库文件命名数据库名为UFDATA_帐套号_年度逻辑文件名物理文件名Ufmodel UFDATA.MDF数据文件UfmodeLLOG UFDATA.LDF日志文件附另外对于帐套而言,每个帐套还需要一下文件数据库名为UFMeta_帐套号逻辑文件名物理文件名UFMeta UFMeta.mdf数据文件UFMeta.Log UFMeta.ldf日志文件直接拷贝覆盖与分离后附加是不一样的,不要直接拷贝覆盖(还会出现质疑)另外,对于置疑和正常数据库不要在sql企业管理器中直接右键删除,否则数据库物理文件就没有了且无痕迹use ufsystem-■查看帐套信息select*from ua_account--查看某帐套年度帐信息select*from ua_account_sub wherecacc_ID=O10--查看某帐套年结情况(bclosing为1表示已结)--说明如果是在其他机器上捉的年结直接拷贝过来的话,不会回写ua_account_sub表的bclosing字段,就需要手工加上select cacc_id as帐套,iyearas年度,bclosing as是否年结from ua_account_sub wherecacc_ID=010--查看所有数据库(位置,大小,使用情况)sp_helpdb--查看指定数据库位置,大小,使用情况)1select*FROM UA_userupdate UA_user setcPassword=nu!l wherecUser_Name=,demo,--查询用户创建的所有数据库(select*from master..sysdatabases Dwheresidnotinselectsid from master..syslogins wherename=sa一或者select dbid,name ASDB_NAME frommaster..sysdatabases wheresid0x01--查询所有数据库select*frommaster..sysdatabases一一将数据库附加到服务器EXEC sp_attach_db@dbname=NUFDATA_009_2008,©filename1=ND:\U8SOFT\ADMIN\SERVERl\ZT009\2008\UFMeta.mdf,@filename2=ND:\U8soFT\ADMIN\SERVERl\ZT009\2008\UFMeta』df-将数据库从服务器分离EXEC sp_detach_db UFDATA_009_2008,^rue1-SQL Server数据库管理常用的SQL和『SQL-
1.查看数据库的版本select©©version—
2.查看数据库所在机器操作系统参数exec master..xp msver-
3.查看数据库启动的参数sp_configure-
5.查看所有数据库名称及大小sp_helpdb/*查看某个特定数据库的名称及大小及存储位置*/exec sphelpdbUFDATA0022008/*重命名数据库用的SQL*/sp_renamedbold_dbname,,new_dbname,-
6.查看所有数据库用户登录信息sphelplogins/*查看所有数据库用户所属的角色信息*/sp_helpsrvrolemember/*修复迁移服务器时孤立用户时,可以用的fix_orphan_user脚本或者LoneUser过程*//*更改某个数据对象的用户属主*/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索引是把记录按物理顺序排列的,索引占的空间比较少对键值DML操作十分频繁的表我建议用非clustered索引和约束,fillfactor参数都用默认值*//*查看某数据库下某个数据对象的的约束信息*/sphelpconstraint@objname-
9.查看数据库里所有的存储过程和函数use@database_namespstoredprocedures/*查看存储过程和函数的源代码*/sp_helptext-procedure name/*查看包含某个字符串@str的数据对象名称*/select distinctobject nameidfrom syscommentswhere textlike%@str%/*创建加密的存储过程或函数在AS前面加WITH ENCRYPTION参数解密加密过的存储过程和函数可以用sp_decrypt过程*/-
10.查看数据库里用户和进程的信息sp_who/*查看SQL Server数据库里的活动用户和进程的信息*/sp_whoactive/*查看SQL Server数据库里的锁的情况*/splock/*进程号1-50是SQL Server系统内部用的,进程号大于50的才是用户的连接进程.spid是进程编号,dbid是数据库编号,objid是数据对象编号*//*查看进程正在执行的SQL语句*/dbcc inputbuffer-
11.收缩数据库日志文件的方法/*收缩简单恢复模式数据库日志,收缩后@database_name_log的大小单位为M*/backup log@database_name withno_logdbcc shrinkfile@database_name_log,5一
12.分析SQL ServerSQL语句的方法set statisticstime{on|off}set statisticsio{on|off}/*图形方式显示查询执行计划在查询分析器->查询->显示估计的评估计划D-Ctrl-L或者点击工具栏里的图形文本方式显示查询执行计划*/set showplan_all{on|off}set showplan_text{on|off}set statisticsprofile{on|off}—
13.出现不一致错误时,NT事件查看器里出3624号错误,修复数据库的方法/*先注释掉应用程序里引用的出现不一致性错误的表,然后在备份或其它机器上先恢复然后做修复操作*/alterdatabase[@error_database_name]set single_user/*修复出现不一致错误的表*/dbcc checktable©error tablename’,repair allowdata loss/*或者可惜选择修复出现不一致错误的小型数据库名*/dbcc checkdb-error databasename’,repair allowdata lossalterdatabase[@error_database_name]set multi_user/*CHECKDB有3个参数repair_allow_data_loss包括对行和页进行分配和取消分配以改正分配错误、结构行或页的错误,以及删除已损坏的文本对象,这些修复可能会导致一些数据丢失修复操作可以在用户事务下完成以允许用户回滚所做的更改如果回滚修复,则数据库仍会含有错误,应该从备份进行恢复如果由于所提供修复等级的缘故遗漏某个错误的修复,则将遗漏任何取决于该修复的修复修复完成后,请备份数据库repair.fast进行小的、不耗时的修复操作,如修复非聚集索引中的附加键这些修复可以很快完成,并且不会有丢失数据的危险repair_rebuild执行由repair_fast完成的所有修复,包括需要较长时间的修复如重建索引执行这些修复时不会有丢失数据的危险*/一字符串截取实例L截取已知长度的函数A.截取从字符串左边开始N个字符Declare@S1varchar100Select@S1=http:〃www.
163.confSelect Left@S1,4显示结果httpB.截取从字符串右边开始N个字符(例如取字符www.取
3.com)Declare@S1varchar100Select@S1=http://www.
163.comSelect right@S1,11显示结果www.
163.comC.截取字符串中任意位置及长度(例如取字符www)Declare@S1varchar100Select@S1=http:〃www.
163.comSelect SUBSTRING@S1,8,3显示必吉果WWW以上例子皆是已知截取位置及长度,下面介绍未知位置的例子
2.截取未知位置的函数A.截取指定字符串后的字符串例如截取http:〃后面的字符串方法一Declare@S1varchar100Select@S1=,http://w^vw.
163.com,Select Substring@S1,CHARINDEX www,@S1+1,Len@S1/*此处也可以这样写:Select Substring@S1,CHARINDEX〃,然1+2,Len@Sl可/显示结果www.
163.com需要注意:CHARINDEX函数搜索字符串时,不区分大小写,因此CHARINDEX www,@S1也可以写成CHARINDEXWWW,@S1方法二与方法一类似Declare@S1varchar100Select@S1=http:〃www.
163.comSelect Substring@S1,PATINDEXC%www%J,@S1+1,Len@S1一此处也可以这样写:Select Substring@S1,PATINDEX%〃%,@S1+2,Len@S1显示结果www.
163.com函数PATINDEX与CHARINDEX区别在于前者可以参数一些参数,增加查询的功能方法三Declare@S1varchar100Select@S1=http:〃www.
163.comSelect REPLACE@S1/http‘,显示结果www.
163.com利用字符替换函数REPLACE,将除需要显示字符串外的字符替换为空方法四Declare@S1varchar100Select@S1=,http://www.
163.com,Select STUFF@S1,CHARINDEXhttp://,@Sl,Len,http:///显示结果:www.
163.com函数STUFF与REPLACE区别在于前者可以指定替换范围,而后者则是全部范围内替换B.截取指定字符后的字符串(例如截取C:\Windows\test.txt中文件名)与A不同的是,当搜索对象不是一个时,利用上面的方法只能搜索到第一个位置方法一:Declare@S1varchar100Select@S1=,C:\Windows\test.txtselect right@S1,charindex,REVERSE@S1-1显示结果text.txt说明利用函数REVERSE获取需要截取的字符串长度用友维护人员常用SQL脚本--查询用友版本号use ufsystemgo字段来关闭上一年度一比如002帐套结转后年度为2010则用于关闭上一2009年度的sql如下select*from ua_account_sub where cAcc_Id=002and iYear=2008updateua_account_sub setbclosing=0where cAccId=002and iYear=2008一清除异常任务及单据锁定use ufsystemdeletefrom ua_taskdelete from ua_taskloggodelete from ufsystem..ua_taskdelete from ufsystem..ua_taskloggoSelect*From ua_taskWhere cacc_id=,***一注***为账套号一科目锁定的解决/*XX科目已经被用户[XX]锁定”或“科目xxxxxx正在被机器xxxx上的用户XXX进行xxxx操作锁定请稍候再试”*/use UFDATA_002_2008select ccodeas科目编码cauth as功能名称cuser as用户名cmachine as机器名from GL_mccontroldelete from GLmccontrol一如何取得一个数据表的所有列名/*方法如下先从SYSTEMobxject系统表中取得数据表的SYSTEMID然后再select*from UA_Versiong-查看系统用户信息表use ufsystemselect cUser_Id as操作员编码,cUser_Name as操作员名称,nState as是否停用,iAdmin as是否帐套主管理,cDept as所属部门,cBelongGrp as所在组,nState as是否停用from UA_User—查看具有帐套主管身份的操作员selectcUser_Id as操作员编码,cUser_Name as操作员名称fromUA_UserwhereiAdmin=l;--查看被停用的操作员selectcUser_Id as操作员编码,cUser_Name as操作员名称from UA_UserwherenState=l;-帐套主子表相关信息use ufsystem■■帐套主表selectcAcc_Id as账套号,cAcc_Name as账套名称,cAcc_Pathas账套路径,iYear as启用会计期年,iMonthas启用会计期月,cAcc_Master as账套主管,cCurCode as本币代码,cCurName as本币名称,cUnitName as单位名称,cUnitAbbre as单位简称,cUnitAddr as单位地址,cUnitZap as邮政编码,cUnitTel as联系电话,cUnitFax as传真,cUnitEMail as电子邮件,cUnitTaxNo as税号,cUnitLPas法人,cEntType as企业类型,cTradeKind as行业类型,clsCompanyVer as是否集团版,cDomain as域名,cDescription as备注,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_Idas操作员,dSubOriDate as启用自然日期from ua_account_sub―当客户的数据在其它机器上做的升级然后拷回到原机器/*拷回的数据,通过,系统管理,在原机器上引入后,并不会在ufsystem数据库中的ua_account_sub这个帐套子表中回写上一年度的bClosing字段来关闭上一年度*/--比如002帐套结转后年度为2010,则用于关闭上一2009年度的sql如下select*from ua_account_sub wherecAcc_Id=002and iYear=2008update ua_account_sub setbclosing=0wherecAcc_Id=002and iYear=2008--清除异常任务及单据锁定use ufsystemdeletefromua_taskdelete fromua_taskloggodelete fromufsystem..ua_taskdelete fromufsystem..ua_taskloggoSelect*From ua_taskWherecacc_id=***••注***为账套号-科目锁定的解决/*XX科目已经被用户[XX]锁定”或“科目xxxxxx正在被机器xxxx上的用户XXX进行xxxx操作锁定,请稍候再试”*/use UFDATA_002_2008select ccodeas科目编码,cauth as功能名称,cuser as用户名,cmachine as机器名fromGL_mccontroldelete fromGL_mccontrol--如何取得一个数据表的所有列名/*方法如下先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再SYSCOLUMN表中取得该数据表的所有列名SQL语句如下:*//*(方法一*/select*fromufsystem..ua_account()select*from syscolumns where id=object_id ua_account()declare@objid int,@objname char40set@objname=ua_accounfselect@objid二idfrom sysobjectswhere id二object_id(@objname)select,Column_name,=name from syscolumns whereid=@objid orderby colid/*(方法二(邹建)*/-跟踪程序的运行就可以了.开始--程序-MS SQLSERVER--事件探察器SQL Profiler-文件--新建-跟踪…--设置要跟踪的服务器的信息连接服务器--确定-设置跟踪的项目…--然后数据库的调用情况就会显示出来在跟踪项目设置中,如果不熟悉的话,一般用默认设置筛选项目有几个可以注意一下同于你要监测的数据库名不过这个好像不起作用,我的电脑上设置无
1.DatabaseName效)
2.DatabaseID同于你要检测的数据库的dbid,可以用select db_idN你要监测的库名)得到dbid同于你要监测的对象名,例如表名,视图名等
3.Obj ectName
4.0bjectID同于你要监测的对象的id,可以用select object_idN你要监测的对象名’)得到id同于错误,如果经常出现某个编号的错误,则针对此错误号同于0,失败,1,成功,如
5.Error果是排错,就过滤掉成功的处理
6.Seccess*//*方法三*/--如果直接查询,可以参考我的这段代码:if existsselect*from dbo.sysobjectswhereid=obj ect_idN*[dbo].[p_search]!andOBJECTPROPERTYid,NlsProcedure1=1drop procedure[dbo].[p_search]GO/*-搜索某个字符串在那个表的那个字段中-邹建
2004.10引用请保留此信息--*//*-调用示例use pubsexec p_search NT__*/create procp_search@str Nvarcharl000-要搜索的字符串as if@str isnull returndeclare@s Nvarchar4000create table#t表名sysname,字段名sysname declaretb cursorlocal forselect s=if existsselect1from[,+replaceb.name,,]\,]],+,]where[*+a.name+]lik eN%+@str+%”print”所在的表及字段:「+b.name+].「+a・name+T”from syscolumnsa joinsysobjects bon a.id=b.idwhere b.xtype=U anda.status=0anda.xtype inl75,239,99,35,231,167open tbfetch next from tbinto@s while@@fetch_status=O beginexec@s fetchnext fromtb into@s endclose tbdeallocate tbgo-通过SQL语句来更改用户的密码/*修改别人的,需要sysadmin role*/EXEC sp_password NULL,*newpassword\User/*如果帐号为SA执行*/EXEC sp_password NULL,newpassword;sa-通怎么判断出一个表的哪些字段不允许为空?select COLUMN_NAME fromINFORMAT1ON_SCHEMA.COLUMNS whereIS_NULLABLE=,NO,and TABLENAME=,ua account,-如何在数据库里找到含有相同字段的表?-a.查已知列名的情况SELECT b.name asTableName,a.name ascolumnnameFrom syscolumnsa INNERJOIN sysobjectsbON a.id=b.idAND b.type=UAND a.name=cacjid--本例如cacc_id列-b.未知列名查所有在不同表出现过的列名Select o.name Astablename,sl.name AscolumnnameFrom syscolumns si,sysobjects oWhere si.id=o.idAnd o.type=UAnd ExistsSelect1From syscolumnss2Where si.name=s
2.nameAnd si.ids
2.id--查询第XXX行数据-假设id是主键select*from selecttop xxx*from yourtableaawhere notexistsselect1from selecttop xxx-1*from yourtablebb whereaa.id=bb.id--如果使用游标也是可以的fetch absolute[number]from[cursor_name]-行数为绝对行数-SQL Server日期计算/*a.一个月的第一天*/SELECT DATEADDmm,DATEDIFFmm,O,getdate,0/*b.本周的星期一*/SELECT DATEADDwk,DATEDIFFwk,O,getdate,0/*c.一年的第一天*/SELECT DATEADDyy,DATEDIFFyy,O,getdate,0/*d.季度的第一天*/SELECT DATEADDqq,DATEDIFFqq,O,getdate,0/*e.上个月的最后一天*/SELECT dateaddms,-3,DATEADDmm,DATEDIFFmm,O,getdate,0/*£去年的最后一天*/SELECT dateaddms,-3,DATEADDyy,DATEDIFFyy,O,getdate,0/*g.本月的最后一天*/SELECT dateaddms,-3,DATEADDmm,DATEDIFFm,O,getdate+l,0/*h.本月的第一个星期一*/select DATEADDwk,DATEDIFFwk,0,dateadddd,6-datepartday,getdate,getdate,0/*i.本年的最后一天*/SELECT dateaddms,-3,DATEADDyy,DATEDIFFyy,O,getdate+1,
01.显示本月第一天SELECT DArEADDmm,DATEDIFFmm,O,getdate,Oselect convertdatetime,convertvarchar8,getdate,l20+
0202.显示本月最后一天selectdateaddday,-1,convertdatetime,convertvarchar8,dateaddmonth,1,getdate,120+01/20SELECT dateaddms,-3,DATEADDmm,DATEDIFFm,0,getdate+1,
03.上个月的最后一天SELECT dateaddms,-3,DATEADDmm,DATEDIFFmm,0,getdate,
04.本月的第一个星期一select DATEADDwk,DATEDIFFwk,O,dateadddd,6-datepartday,getdate,getdate,
05.本年的第一天SELECT DATEADDyy,DATEDlFFyy,O,getdate,O
6.本年的最后一天SELECT dateaddms,-3,DATEADDyy,D ATEDIFFyy,O,getdate+1,07,去年的最后一天SELECT dateaddms,-3,DATEADDyy,DATEDIFFyy,0,getdate,
08.本季度的第一天SELECT DATEADDqq,DATEDIFFqq,O,getdate,O
9.本周的星期一SELECT DATEADDwk,DATEDIFFwk,O,getdate,O
10.查询本月的记录select*from tableNamewhere DATEPART mm,theDate二DATEPART mm,GETDATE andDATEPARTyy,theDate二DATEPARTyy,GETDATE
11.查询本周的记录select*from tableNamewhere DATEPARTwk,theDate=DATEPART wk,GETDATEO andDATEPARTyy,theDate=DATEPARTyy,GETDATEO
12.查询本季的记录select*from tableNamewhere DATEPARTqq,theDate=DATEPARTqq,GETDATEO andDATEPARTyy,theDate二DATEPARTyy,GETDATE其中:GETDATE是获得系统时间的函数
13.获取当月总天数select DATEDIFFdd,getdate,DATEADDmm,1,getdate selectdatediffday,dateaddmm,datediffmm,n,getdate,n,dateaddmm,datediffmm,n,getdate,1900-02-
01114.获取当前为星期几DATENAMEweekday,getdate/*查询数据库的所有用户表刃use ufdata_002_2008select namefrom sysobjectswhere type=U-■查看数据库中所有的触发器use ufdata_002_2008goselect*fromsysobjectswhere xtype=TR--查询特定数据库中某一不知归属表的触发器/*查询某一个触发器TR_Ap_CloseBills所归属的表*/use ufdata_002_2008declare@parent_obj_id int--定义父对象id变量--先找出父对象(所在表)的id(在触发器不重复归属于多个表的情况下)select@parent_obj_id=parent_objfrom sysobjectswherename=TR_Ap_CloseBills,and xtype=TRprint,所在父对象(表)的ID是,+str(@parent_obj_id)--接下来找出父对象(表)的名称select name as触发器所在表为fromsysobjectswhere type=U andid=@paront objid--查看触发器内容use ufdata_002_2008goexec sp_helptext TR_Ap_CloseBills-用于查看触发器的属性(参数指定触发器所在的表)use ufdata_002_2008goexec sp_helptriggerAp_CloseBills--创建触发器1创建一个简单的触发器触发器是一种特殊的存储过程,类似于事件函数,SQLServer允许为INSERT、UPDATE、DELETE创建触发器,即当在表中插入、更新、删除记录时,触发一个或一系列T-SQL语句触发器可以在查询分析器里创建,也可以在表名上点右键,“所有任务”-“管理触发器”来创建,不过都是要写T-SQL语句的,只是在查询分析器里要先确定当前操作的数据库创建触发器用CREATE TRIGGER格式如下CREATE TRIGGER触发器名称ON表名FORINSERTUPDATE或DELETEAST-SQL语句注意触发器名称是不加引号的*/-如下是联机丛书上的一个示例,当在titles表上更改记录时,发送邮件通知MaryMo CREATETRIGGERreminderON titlesFORINSERT,UPDATE,DELETEASEXEC master..xp_sendmail MaryM1,fDonnt forgetto printa reportfor thedistributors.,/*2删除触发器用查询分析器删除在查询分析器中使用drop trigger触发器名称来删除触发器也可以同时删除多个触发器drop trigger触发器名称,触发器名称…注意触发器名称是不加引号的在删除触发器之前可以先看一下触发器是否存在格式如下if Existsselect namefromsysobjectswhere name二触发器名称and xtype=TR!用企业管理器删除在企业管理器中,在表上点右键会”所有任务管理触发器”,选中所要删除的触发器,然后点击“删除”*//*3重命名触发器用查询分析器重命名exec sp_rename原名称,新名称sp_rename是SQL Server自带的一个存储过程,用于更改当前数据库中用户创建的对象的名称,如表名、列表、索引名等用企业管理器重命名在表上点右键所有任务”,“管理触发器选中所要重命名的触发器,修改触发器语句中的触发器名称,点击“确定”*//*
(4)更多功能©INSTEAD OF子句执行触发器语句,但不执行触发触发器的SQL语句,比如试图删除一条记录时,将执行触发器指定的语句,此时不再执行delete语句例create triggerfon tblinsteadof deleteasinsertintoLogs...
②IF UPDATE(列名)子句检查是否更新了某一列,用于insert或update,不能用于deleteo例:create triggerfon tblforupdateasif updatestatusor updatetitlesql_statement--更新了status或title列
③inserted、deleted(两个虚拟表的使用)这是两个虚拟表,inserted保存的是insert或update之后所影响的记录形成的表,deleted保存的是delete或update之前所影响的记录形成的表例create triggertbl_deleteon tblfordeleteasdeclare@title varchar200select@title=title fromdeletedinsert intoLogLlogContent values删除了title为+title+的记录说明如果向inserted或deleted虚拟表中取字段类型为text image的字段值时,所取得的值将会是nullo*/5递归、嵌套触发器递归分两种,间接递归和直接递归我们举例解释如下,假如有表
1、表2名称分别为T
1、T2,在Tl、T2上分别有触发器GK G2间接递归对T1操作从而触发Gl,G1对T2操作从而触发G2,G2对T1操作从而再次触发GL..直接递归对T1操作从而触发Gl,G1对T1操作从而再次触发G
1...嵌套触发器类似于间接递归,间接递归必然要形成一个环,而嵌套触发器不一定要形成一个环,它可以T1-T2-T3…这样一直触发下去,最多允许嵌套32层设置直接递归默认情况下是禁止直接递归的,要设置为允许有两种方法:T-SQLexec sp_dboption^bName1,recursive triggers1,true EM数据库上点右键〉属性〉选项设置间接递归、嵌套默认情况下是允许间接递归、嵌套的,要设置为禁止有两种方法T-SQLexec sp_configure nestedtriggers1,0--第二个参数为1则为允许EM注册上点右键〉属性:服务器设置*//*6触发器回滚我们看到许多注册系统在注册后都不能更改用户名,但这多半是由应用程序决定的,如果直接打开数据库表进行更改,同样可以更改其用户名,在触发器中利用回滚就可以巧妙地实现无法更改用户名语句如下use数据库名gocreate triggertron表名forupdateasifupdateuserName rollbacktran关键在最后两句,其解释为如果更新了userName歹U,就回滚事务*//*7禁用、启用触发器*/--禁用alter table表名disable trigger触发器名称一启用alter table表名enable trigger触发器名称/*如果有多个触发器,则各个触发器名称之间用英文逗号隔开SYSCOLUMN表中取得该数据表的所有列名SQL语句如下:*//*方法一*/select*fromufsystem..ua accountselect*from syscolumnswhereid=obxject_id ua_account,declare@objid int@objname char40set@objname=ua_account,select@objid=idfrom sysobxjects whereid=obxject_id@objname selectColumn_name,=namefromsyscolumnswhereid=@objid orderby colid/*方法二邹建*/—-跟踪程序的运行就可以了.开始一程序一MS SQLSERVER一事件探察器(SQL Profiler)一文件一新建--跟踪...一设置要跟踪的服务器的信息(连接服务器)一确定一设置跟踪的项目...―然后数据库的调用情况就会显示出来在跟踪项目设置中如果不熟悉的话一般用默认设置筛选项目有几个可以注意一下
1.DatabaxseName同于你要监测的数据库名(不过这个好像不起作用我的电脑上设置无效)
2.DatabaxselD同于你要检测的数据库的dbid可以用select db_id(N你要监测的库名)得到dbid
3.obxjectName同于你要监测的对象名例如表名视图名等
4.obxjectID同于你要监测的对象的id可以用select obxject_id(N你要监测的对象名’)得到id
5.Error同于错误如果经常出现某个编号的错误则针对此错误号
6.Seccess同于0失败1成功如果是排错就过滤掉成功的如果把“触发器名称”换成“ALL”,则表示禁用或启用该表的全部触发器处理/*方法三*/—如果直接查询可以参考我的这段代码if existsselect*from dbo.sysobxjects whereid=obxject_idN,[dbo].[p-Search]5and obxjectPROPERTYid NIsProcedure,二1drop procedure[dbo].[p_search]GO/*一搜索某个字符串在那个表的那个字段中―邹建
2004.10引用请保留此信息一*//*一调用示例use pubsexec p_search N1一*/create procp_search@str Nvarchar1000一要搜索的字符串as if@str isnull returndeclare@s Nvarchar4000create table表名sysname字段名sysname declaretbcursor localforselects=,if existsselect1from+replaceb.name5where[+a.name+]like N%+@str+%print所在的表及字段「+b.name+,].+a.name+]fromsyscolumnsa joinsysobxjects bon a.id=bwhere b.xtype=U anda.status=
0.idbegin exec@s fetchnextfromtb into@send closetbdeallocate tbgo-通过SQL语句来更改用户的密码/*修改别人的需要sysadmin role*/newpassword5UserEXEC sppasswordNULL/*如果帐号为SA执行*/EXEC sppasswordNULL newpassword5sa一通怎么判断出一个表的哪些字段不允许为空?select COLUMN_NAME fromINFORMATION_SCHEMA.COLUMNSwhere IS_NULLABLE=NO andTABLE_NAME=,ua_account,-如何在数据库里找到含有相同字段的表?-a.查已知列名的情况SELECT b.nameasTableNamea.nameascolumnnameFrom syscolumnsa INNERJOIN sysobxjectsON a.id=b.idAND b.type=UAND a.name=cacc_id,--本例如cacc_id列-b.未知列名杳所有在不同表出现过的列名Select o.name Astablenamesl.name Ascolumnname From syscolumnssisysobxjects oWheresi.id=o.idAnd o.type=UAnd ExistsSelect1Fromsyscolumnss2Wheresi.name=s
2.nameAnd si.ids
2.id-查询第XXX行数据一假设id是主键select*from selecttop xxx*from yourtableaawhere notexistsselect1from selecttop xxx-l*from yourtablebb whereaa.id=bb.id-如果使用游标也是可以的fetch absolute[number]from[cursor_name]一行数为绝对行数--SQL Server日期计算/*a.一个月的第一天*/SELECT DATEADDmm/*b.本DATEDIFF mmOgetdate0周的星期一*/SELECTDATEADDwk/*c.一年的第DATEDIFF wkOgetdateO0一天*/SELECT DATEADDyyAd.季度的第一天*/DATEDIFF yyOgetdate0SELECT DATEADDqq/*e.上个月的最后一天DATEDIFF qqOgetdate0SELECT dateaddms-3DATEADDmm DATEDIFFmmOgetdate0/*f.去年的最后一天*/DATEDIFFyyOgetdate0SELECT dateaddms-3DATEADDyy/*g.本月的最后一天*/DATEDIFFmOgetdate+10SELECT dateaddms-3DATEADDmmAh.本月的第一个星期一*/select DATEADDwkDATEDIFFwkOdateadddd6-datepartdaygetdategetdate0/*i.本年的最后一天*/SELECT dateaddms-3DATEADDyy DATEDIFFyyOgetdate+
101.显示本月第一天SELECT DATEADDmmDATEDIFFmmOgetdate0select convertdatetimeconvertvarchar8getdate120+0T
1202.显示本月最后一■天selectdateaddday-lconvertdatetimeconvertvarchar8dateadd monthlgetdate120+01120SELECT dateaddms-3DATEADDmmDATEDIFFmOgetdate+
103.上个月的最后一天SELECT dateaddms-3DATEADDmmDATEDIFFmmOgetdate
04.本月的第一个星期一select DATEADDwkDATEDIFFwkOdateadddd6-datepartdaygetdate getdate
05.本年的第一天SELECT DATEADDyyDATEDIFFyyOgetdate
06.本年的最后一天SELECT dateaddms-3DATEADDyyDATEDIFFyyOgetdate+
107.去年的最后一天SELECT dateaddms-3DATEADDyyDATEDIFFyyOgetdate
08.本季度的第一天SELECT DATEADDqqDATEDIFFqqOgetdate
09.本周的星期一SELECT DATEADDwkDATEDIFFwkOgetdate
010.查询本月的记录select*from tabieNamewhere DATEPARTmmtheDate=DATEPARTmmGETDATE andDATEPARTyy theDate=DATEPART yyGETDATE
11.查询本周的记录select*from tabieNamewhere DATEPARTwktheDate=DATEPARTwk GETDATE andDATEPARTyytheDate=DATEPARTyy GETDATE
12.查询本季的记录select*from tabieNamewhere DATEPART qq theDate=DATEPARTqqGETDATEandDATEPARTyytheDate二DATEPART yyGETDATE其中:GETDATE是获得系统时间的函数
13.获取当月总天数select DATEDIFFddgetdateDATEADDmm1getdateselect datediffdaydateaddmmdatediff mmgetdatedateaddmm datediffmm,getdate1900-02-
0114.获取当前为星期几DATENAMEweekday getdate/*查询数据库的所有用户表*/use ufdata_002_2008select namefrom sysobxjectswhere type=U一查看数据库中所有的触发器use ufdata_002_2008goselect*from sysobxjectswhere xtype=,TR一查询特定数据库中某一不知归属表的触发器/*查询某一个触发器TR_Ap_CloseBills所归属的表*/use ufdata002_2008declare@parent_obj_idint一定义父对象id变量一先找出父对象(所在表)的id(在触发器不重复归属于多个表的情况下)select@parent_obj_id=parent_objfrom sysobxjectswherename=,TR_Ap_CloseBills,and xtype=,TR,print所在父对象(表)的ID是+str(@parent_obj_id)一接下来找出父对象(表)的名称selectnameas触发器所在表为fromsysobxjectswhere type=U andid=@parent obiid一查看触发器内容use ufdata002_2008。