www.9778.com 6

【www.9778.com】sqlserver中操作主从关系表的sql语句

sqlserver中操作主从关系表的sql语句,重点讲解,科目信息传递过后怎么处理。假设从表Tb_StudentSubject
存在StudentId,SubjectId两个字段。那么代码如下: 复制代码 代码如下:

典型的方法就是对于主表的各数据字段进行更新或新增,对于从表一般都会先删除相关的所有记录,然后再进行插入。也就是说每次保存时,从表的数据都要重新执行一遍删除再插入的过程。这是一个不错的方法,在使用数据库事务后,不会存在删除后又没有插入导致数据不一致的情况。
ok,原理是这样,那么到实际的操作中,大家如何操作的呢?有人通过在程序中,通过多次数据库操作完成如上的要求,这种方法可能存在这数据的不一致性,因为无法保证在两个数据语句操作之间出现意外。那么我们就合并到一个存储过程中去完成吧,复杂的参数和接收后处理,让这个存储过程变的困难。下边我就结合实际应用说说此过程吧。应用场景,在学生管理中,添加一条学生记录,对应的要
添加学生对应的课程。即每次更新和新增学生记录,对应其课程也要跟着变化。
那么这个过程,就符合上边的逻辑。
通用的参数传递,在此不再详说,重点讲解,科目信息传递过后怎么处理。假设从表Tb_StudentSubject
存在StudentId,SubjectId两个字段。那么代码如下: 复制代码 代码如下: Create proc SaveInfo (
—参数列表不再逐一书写 @subjectList varchar(400)) as begin tran
–新增或修改主表 省略 DECLARE @subjectID int, @subjectIDsLen
int,–科目长度 @subjectIDLen int,–单个科目长度 @nStart INT , —
记录查找的起始位置 @nLast INT — 记录‘,’的位置 select
@subjectIDsLen=len(@SubjectIDs),@nStart=1,@nLast=0 DELETE
Tb_StudentSubject WHERE StudentId=@sId –删除相关的全部记录
IF(@subjectIDsLen0) BEGIN while(@nStart@subjectIDsLen) BEGIN set
@nLast=charindex(‘,’,@SubjectIDs,@nStart)–从第@nStart个位置开始记录逗号的位置
set @subjectIDLen=@nLast-@nStart –记录每个subjectID的长度 set
@subjectID=substring(@SubjectIDs,@nStart,@subjectIDLen)–获取一个subjectID
Insert Into
Tb_StudentSubject(StudentId,SubjectID)values(@sId,@subjectID) set
@nStart=@nLast+1–设置下次查找的起始位置 END END if @@Error=0 commit
transaction else rollback transaction End
在这边代码中,科目列表通过“,”进行分割,上边的存储过程带注释,重点说明两个系统自带的函数
CHARINDEX ( expression1 ,expression2 [ , start_location ] )
expression1 :其中包含要查找的字符的序列 expression2
:通常是一个为指定序列搜索的列 start_location :开始在 expression2 中搜索
expression1 时的字符位置。如果 start_location 未被指定、是一个负数或零
另一个函数就是:SUBSTRING ( expression ,start , length
)从指定的数据源串中从指定位置截取指定长度的串 其中 expression
:是字符串、二进制字符串,要操作的数据源串
start:要截取的开始位置,注意此方法和C#自带的位置不一样,数据库中strat从1开始,程序中从0开始
length:截取内容的长度 另外一个就len(expres)
这个就比较简单了,获取指定字符的长度
通过以上代码,就可以实现一个完整存储过程实现主从表数据的处理了,在数据一致性和操作便捷性得到保证,而且相同通用。
注:在涉及类似主从关系表中,建议不要在从表中设置自动编号的主键,因为频繁的删除和添加,导致主键不断的累增,而且毫无使用此主键的意义。
以上内容是个人见解,希望大家多交流相应的方法。

 

在这边代码中,科目列表通过,进行分割,上边的存储过程带注释,重点说明两个系统自带的函数
CHARINDEX ( expression1 ,expression2 [ , start_location ] )
expression1 :其中包含要查找的字符的序列 expression2
:通常是一个为指定序列搜索的列 start_location :开始在 expression2 中搜索
expression1 时的字符位置。如果 start_location 未被指定、是一个负数或零
另一个函数就是:SUBSTRING ( expression ,start , length
)从指定的数据源串中从指定位置截取指定长度的串 其中 expression
:是字符串、二进制字符串,要操作的数据源串
start:要截取的开始位置,注意此方法和C#自带的位置不一样,数据库教程中strat从1开始,程序中从0开始
length:截取内容的长度 另外一个就len(expres)
这个就比较简单了,获取指定字符的长度
通过以上代码,就可以实现一个完整存储过程实现主从表数据的处理了,在数据一致性和操作便捷性得到保证,而且相同通用。
注:在涉及类似主从关系表中,建议不要在从表中设置自动编号的主键,因为频繁的删除和添加,导致主键不断的累增,而且毫无使用此主键的意义。

【www.9778.com】sqlserver中操作主从关系表的sql语句。初步断定不可见字符在右边,因为第三个的查询结果不是0而是空(可能是字符串空吧”)

Create proc SaveInfo ( —参数列表不再逐一书写 @subjectList
varchar(400)) as begin tran –新增或修改主表 省略 DECLARE @subjectID
int, @subjectIDsLen int,–科目长度 @subjectIDLen int,–单个科目长度
@nStart INT , — 记录查找的起始位置 @nLast INT — 记录,’的位置 select
@subjectIDsLen=len(@SubjectIDs),@nStart=1,@nLast=0 DELETE
Tb_StudentSubject WHERE StudentId=@sId –删除相关的全部记录
IF(@subjectIDsLen0) BEGIN while(@nStart@subjectIDsLen) BEGIN set
@nLast=charindex(‘,’,@SubjectIDs,@nStart)–从第@nStart个位置开始记录逗号的位置
set @subjectIDLen=@nLast-@nStart –记录每个subjectID的长度 set
@subjectID=substring(@SubjectIDs,@nStart,@subjectIDLen)–获取一个subjectID
Insert Into
Tb_StudentSubject(StudentId,SubjectID)values(@sId,@subjectID) set
@nStart=@nLast+1–设置下次查找的起始位置 END END if @@Error=0 commit
transaction else rollback transaction End

  char(integer_expression)

      5、获取字符串长度LEN

www.9778.com 1

   语法结构:

  参数
  string_expression1:要搜索的字符串表达式。string_expression1
可以是字符或二进制数据类型。
  string_expression2:要查找的子字符串。string_expression2
可以是字符或二进制数据类型。
  string_expression3:替换字符串。string_expression3
可以是字符或二进制数据类型。

      start_location:开始在expression2中搜索expression1是的字符位置。如果start_location未被指定、是一个负数或零,则将从expression2的开头开始搜索。      start_location可以是bingint类型。

  SELECT Unicode('飞')  -- 输出 39134

    参数说明:

    示例:

www.9778.com, 

  select len('天下之大,无奇不有')  -- 输出 9

 

    语法结构:

   示例:

 前言 

www.9778.com 2

  语法如下:

    charindex函数用于在指定的字符串中搜索特定的字符串,并可以指定开始搜索的位置,返回第一次找到目标字符串的字符数。

      expression2:一个字符串数据类型的表达式,通常是一个为指定序列搜索的列。

    返回值:unicode字符

  len( string_expression )

     示例:

      string_expression:要计算长度的字符串。

  REPLACE ( string_expression1 , string_expression2 , string_expression3 )

   注意如果是多个字符的字符串,也只是返回第一个字符的ASCII码值。

loopUPDATE TblName SET ColName = LEFT(ColName,LEN(ColName)-1)   WHERE ASCII(RIGHT( ColName ,1))=0 end loop
 问题处理 

既然这样,那就用截取字符串的方法把需要的信息拿出来,框架大概如下,套个循环。当然也有可能有更好的方法,仅供参考

    select charindex(‘456′,’123456789’) — 输出 4
从123456789中检索456出现的位置
    select charindex(‘456′,’123456789’,4) — 输出 4

    示例:

      expression1:一个字符串数据类型的表达式,其中包含要查找的字符的序列。

  select char(102)  -- 输出f

贴几个常用函数 

 原来如此,charindex都找不到它,非得right一下,这个”空”+其它字符后都不认识它的爹爹了。

  charindex ( expression1 , expression2 [ , start_location] )
    参数说明:

 

 基础知识准备 

    返回值:如果expression2的数据类型为varchar(max)、nvarchar(max)或varbinary(max),则为bigint,否则为int。

转载注明出处:

查看ascii表(我是大自然的搬用工),ASCII 非打印控制字符:ASCII
表上的数字 0–31 分配给了控制字符,用于控制像打印机等一些外围设备。

  nchar(integer_expression)

接着查看它的ascii编码值

    返回值:int数据类型。

   这里的ncharacter_expression是nchar或nvarchar类型的表达式。函数仅会返回第一个字符的unicode编码。

    ASCII码是对字符的标准编码。要获取字符的ASCII码就可以通过调用ASCII函数来实现。

    nchar能够根据unicode标准的定义,返回具有指定的整数代码的unicode字符。

    返回值:expression数据类型为varchar(max)、nvarchar(max)或varbinary(max),则为bigint。否则为int。

继续追踪原因

得到结论1:右边第一个不可见字符是个控制字符(对应char(0),为什么是char(0)上面贴的基础知识有讲)

一般到这里就结束了,查到了是什么字符,然后用replace函数替换成字符串空完事,下面将其处理掉时发现失败

转载注明出处:

    语法结构:

    返回值类型:char型字符

 1、获取字符的ASCII码 ASCII

  SELECT nchar(39134)    -- 输出 飞

  4、获取unicode编码对应的字符nchar

转载注明出处:

   

   返回值:int类型数据

  ASCII(espression)

 

如下图所示 golds字段 看上去5个字符,长度则为44
,可判断有特殊字符存在此列中。

先查看左右边字符和肉眼所见是否吻合结果如下图

   这里的expression是一个返回char或varchar数据类型的表达式,ASCII函数仅对表达式最左侧的字符返回ASCII码值。–这个是重点

上面查到了是char(0)在作祟,而且我循环输出golds字段右边的字符发现有39个char(0),方便理解,简化问题,改为下图对比着看

初步断定在@a里没找到

   unicode函数可以获得字符的unicode编码。

www.9778.com 3

www.9778.com 4

    语法结构:

  2、获取ASCII码对应的字符 Char

SET NOCOUNT ON DECLARE @TblName VARCHAR(100) DECLARE @UpdateString NVARCHAR(1000) DECLARE @SelectString NVARCHAR(1000) DECLARE @COlName VARCHAR(100) DECLARE @COUNT INT SET @TblName = '表名称'--指定想要修改的表名 --定义游标取出指定表内的数据类型是VARCHAR,char,nVARCHAR的字段名称 DECLARE cur_ColName CURSOR FOR SELECT col.name FROM syscolumns AS col inner join sysobjects AS obj ON col.ID = obj.ID INNER join systypes AS typ ON col.xtype = typ.xtype WHERE obj.xtype ='U' AND obj.name = @TblName AND typ.name IN ('VARCHAR','CHAR','NVARCHAR','NCHAR') FOR READ ONLY --打开游标 OPEN cur_ColName FETCH NEXT FROM cur_ColName INTO @ColName IF @@FETCH_STATUS<>0 BEGIN PRINT '没有对应表或字段,'PRINT '请确认当前数据库内有' + @TblName + '表,' PRINT '或该表内有VARCHAR、CHAR、NVARCHAR、NCHAR类型的字段!' GOTO LABCLOSE END--循环修改 WHILE @@FETCH_STATUS=0 BEGIN --拼修改字符串  --去掉左边的不可见字符  SET @SelectString = 'SELECT @COU=COUNT(*)   FROM ' + @TblName +'   WHERE ASCII(LEFT(' + @ColName +',1))<32 AND '+ @ColName + ' IS NOT NULL' EXEC sp_executesql @SelectString,N'@COU INT OUTPUT', @COUNT OUTPUT WHILE @COUNT>0 BEGIN  SET @UpdateString =  ' UPDATE ' + @TblName +  ' SET ' + @ColName + '=RIGHT(' + @ColName + ',LEN(' + @ColName + ')-1)   WHERE ASCII(LEFT(' + @ColName + ',1))<32   AND ' + @ColName + ' IS NOT NULL'  EXEC sp_executesql @UpdateString  EXEC sp_executesql @SelectString,N'@COU INT OUTPUT',@COUNT OUTPUT END --去掉右边的不可见字符  SET @SelectString = 'SELECT @COU=COUNT(*)   FROM ' + @TblName +'   WHERE ASCII(RIGHT(' + @ColName +',1))<32   AND '+ @ColName + ' IS NOT NULL' EXEC sp_executesql @SelectString,N'@COU INT OUTPUT', @COUNT OUTPUT WHILE @COUNT>0 BEGIN  SET @UpdateString =  ' UPDATE ' + @TblName +  ' SET ' + @ColName + '=LEFT(' + @ColName + ',LEN(' + @ColName + ')-1)   WHERE ASCII(RIGHT(' + @ColName + ',1))<32   AND ' + @ColName + ' IS NOT NULL'  EXEC SP_EXECUTESQL @UpdateString  EXEC sp_executesql @SelectString,N'@COU INT OUTPUT', @COUNT OUTPUT END PRINT 'column: ' + @ColName + '---ok' FETCH NEXT FROM cur_ColName INTO @ColName END --关闭、释放游标 LABCLOSE: CLOSE cur_ColName  DEALLOCATE cur_ColName GO

 下面共享个通用处理指定表中所有列中所有不可见字符的处理脚本,实测可用。

    len函数用于获取字符串的长度(字符数),但不包括右边的空格。左边的空格和右边的空格计算在内。

以前言中描述的例子为例(个人习惯处理方式供参考,如果你的肉眼自带反隐功能可略过下文)

    示例:

   7 、指定位置搜索字符串中的内容CHARINDEX

  用另一个字符串值替换出现的所有指定字符串值。

 

    示例:

    语法结构:

www.9778.com 5

  select ASCII('f')  --输出 102

问题描述:在表列里有肉眼不可见字符,导致一些更新或插入失败。
几年前第一次碰见这种问题是在读取考勤机人员信息时碰见的,折腾了一点时间,现在又碰到了还有点新发现就顺便一起记录下。

  unicode('ncharacter_expression')
  SELECT REPLACE('abcde','abc','xxx')  --xxxde

   6、REPLACE

www.9778.com 6

  3、获取字符的unicode编码 Unicode

    语法结构: