sqlserver字符串拆分(split)方法汇总

核心提示:经常可以遇到这种情况:用不同的SQL写法可以达到同样的目的

www.9778.com 1www.9778.com 2代码

sqlserver字符串拆分(split)方法汇总

 转载

–方法0:动态SQL法 declare @s varchar(100),@sql varchar(1000)
set @s=’1,2,3,4,5,6,7,8,9,10′
set @sql=’select col=”’+ replace(@s,’,’,”’ union all select
”’)+””
PRINT @sql
exec (@sql)

 

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[f_splitSTR]’) and xtype in (N’FN’, N’IF’,
N’TF’))
drop function [dbo].[f_splitSTR]
GO
–方法1:循环截取法 CREATE FUNCTION f_splitSTR(
@s   varchar(8000),   –待分拆的字符串
@split varchar(10)     –数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
 DECLARE @splitlen int
 SET @splitlen=LEN(@split+’a’)-2
 WHILE CHARINDEX(@split,@s)>0
 BEGIN
  INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
  SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,”)
 END
 INSERT @re VALUES(@s)
 RETURN
END
GO

 

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[f_splitSTR]’) and xtype in (N’FN’, N’IF’,
N’TF’))
drop function [dbo].[f_splitSTR]
GO
–方法2:使用临时性分拆辅助表法
CREATE FUNCTION f_splitSTR(
@s   varchar(8000),  –待分拆的字符串
@split varchar(10)     –数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
 –创建分拆处理的辅助表(用户定义函数中只能操作表变量)
 DECLARE @t TABLE(ID int IDENTITY,b bit)
 INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b

 INSERT @re SELECT SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID)
 FROM @t
 WHERE ID<=LEN(@s+’a’)
  AND CHARINDEX(@split,@split+@s,ID)=ID
 RETURN
END
GO

 

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[f_splitSTR]’) and xtype in (N’FN’, N’IF’,
N’TF’))
drop function [dbo].[f_splitSTR]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tb_splitSTR]’) and
objectproperty(id,N’IsUserTable’)=1)
drop table [dbo].[tb_splitSTR]
GO
–方法3:使用永久性分拆辅助表法 –字符串分拆辅助表
SELECT TOP 8000 ID=IDENTITY(int,1,1) INTO dbo.tb_splitSTR
FROM syscolumns a,syscolumns b
GO
–字符串分拆处理函数
CREATE FUNCTION f_splitSTR(
@s     varchar(8000),  –待分拆的字符串
www.9778.com,@split  varchar(10)     –数据分隔符
)RETURNS TABLE
AS
RETURN(
 SELECT col=CAST(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID) as
varchar(100))
 FROM tb_splitSTR
 WHERE ID<=LEN(@s+’a’)
  AND CHARINDEX(@split,@split+@s,ID)=ID)
GO

 

–方法4:利用sql server2005的OUTER APPLY

CREATE FUNCTION [dbo].[ufn_SplitStringToTable]
(
  @str VARCHAR(MAX) ,
  @split VARCHAR(10)
)
RETURNS TABLE
    AS
RETURN
    ( SELECT    B.id
      FROM      ( SELECT    [value] = CONVERT(XML , ‘<v>’ +
REPLACE(@str , @split , ‘</v><v>’)
                            + ‘</v>’)
                ) A
      OUTER APPLY ( SELECT  id = N.v.value(‘.’ , ‘varchar(100)’)
                    FROM    A.[value].nodes(‘/v’) N ( v )
                  ) B
    )

 

备注说明:

方法4必须在sql server2005下才可以运行

Mysql数据库笔记,mysql数据库

出错记录:
1、mysql服务启动不了,进程意外终止 1067
   错误显示:can not connect to mysql server on local hosts(1061)
   解决方法:原来是我傻逼把原来的MySQL数据库给删掉了

2、服务已经启动,但是输入密码时 进不去
错误显示:ERROR 1045 <28000>:Access denied for user’root’@’locahost'<using password:YES>
解决方法:

3、

默认端口号:3306

把mysql的bin目录添加到环境变量中就可以在任意目录下都可以打开bin目录了。

之前看到的[email protected]这些是因为别人改了提示符:
mysql>prompt [email protected] h d>
u表示当前用户 h表示服务器名称 d表示当前数据库

MySQL语句规范:

关键字和函数名称全部大写;
数据库名称、表的名称、字段的名称全部小写;
SQL语句必须以分好结尾。

加中括号表示可以省略  
显示当前版本;
mysql>SELECT VERSION();
显示当前时间;
mysql>SELECT NOW();
显示当前用户;
mysql>SELECT USER();

修改原始密码:

打开命令提示符界面, 执行命令: mysqladmin -u root -p password 新密码
执行后提示输入旧密码完成密码修改, 当旧密码为空时直接按回车键确认即可。

开始:

//创建数据库:
MySQL>CREATE DATABASE (IF NOT EXISTS) case;
//显示已经存在的数据库;
MySQL>SH0W DATABASES;
//重命名数据库名称
先关闭数据库,然后找到文件夹所在目录,更改文件夹名称。
//显示某个数据库;
MySQL>SHOW CREATE DATABASE case;

//更改数据库编码为utf8;
MySQL>ALTER DATABASE case CHATACTER SET=utf8;

//删除数据库:
MySQL>DROP DATABASE case;

修改和删除

修改默认值: ALTER TABLE TB_NAME ALTER 字段名 SET DEFAULT 默认值; ALTER
TABLE TB_NAME ALTER 字段名 DROP DEFAULT ;
修改表名
ALTER TABLE 表名 RENAME TO 新名;
修改字段名
ALTER TABLE 表名 CHANGE 旧字段 新字段 新字段数据类型
修改字段数据类型
ALTER 表名 MODIFY 属性名 数据类型
增加字段
ALTER TABLE 表名 ADD 字段1 字段1的条件 [FIRST | AFTER 字段2];
删除字段
ALTER TABLE 表名 DROP 字段;
修改字段的排列位置:
ALTER TABLE 表名 MODIFY 字段1 字段1数据类型 FIRST|AFTER 字段2;
更改表的存储引擎:
ALTER TABLE 表名 ENGINE=引擎名
添加主键约束: ALTER TABLE 表名 ADD PRIMARY KEY (外键名) 删除外键约束:
ALTER TABLE 表名 DROP FOREIGN KEY (外键别名) 修改数据表的名称:
ALTER  TABLE table_name RENAME TO new_table_name
删除记录:
delete from users where id=1;
修改记录:
update 表名 set 字段=新值 where 条件;
update users set id=1 whers sex=1;

表字段的修改:
增加字段
ALTER table tb_name ADD column_name 属性 位置;        
  //增加一个字段,默认为空
alter table user add COLUMN new2 VARCHAR(20) NOT NULL;  //增加一个字段,默认不能为空
删除字段
alter table user DROP COLUMN new2;                //删除一个字段

alter table user DROP column1,column2;                        //删除多列

修改一个字段
alter table user MODIFY new1 VARCHAR(10);            //修改一个字段的类型
alter table user CHANGE new1 new4 int;              //修改一个字段的名称,此时一定要重新指定该字段的类型

第二章: 1、数据类型: 整型:
TINYINT -2^7->2^7-1
SMALLINT -2^15->2^15-1
MEDIUMINT -2^23->2^23-1
INT -2^31->2^31-1
BIGINT -2^63->2^63-1

浮点型:
FLOAT[(M,D)] m代表总位数,d代表小数点后位数
DOUBLE[(M,D)]

时间日期型:(了解)
YEAR 1个字节
TIME 3
DATE 3
DATETIME 8
TIMESTAMP 4

字符型:
CHAR(M) 0<=M<=255
VARCHAR(M)
TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT
ENUM(‘value1,’value2’…)
SET(‘value1′,’value2’,,,)

2、数据表的操作
打开数据库:
USE test(数据库名称)  
创建数据表:
CREATE TABLE (IF NOT EXISTS) table_name<数据表名字>(column_name<根据项目大小确定的列名>data_type<数据类型>,..)

mysql>USE TEST;
>CREATE TABLE tb1(
>username VARCHAR(20),
>age TINYINT UNSIGNED,<unsigned意思是不要负数>
>salary FLOAT(8,2) UNSIGNED <float(8,2)的意思是总共有8位数,其中小数点后有2位)>
>);

查看数据表列表
SHOW TABLES FROM test;

查看数据表结构
SHOW COLUMNS FROM TB1;

插入记录INSERT
INSERT [INTO] tb1_name [(col_name,,,)] VALUES(val,,,)
INSERT TB1 (username ,salary) VALUES (‘tom’,26,919.3);
插入的也可以是算式比如:33-2 或者函数式:MD5(‘342’)
也可以一次插入多条记录,记录间用,分开就行。

记录查找SELECT
SELECT * FROM TB1_NAME

3、空值与非空

NULL 字段值可以为空  NOT NULL 字段禁止为空

创建表,设定某些量空与非空
>CREATE TABLE TB2(
>username VARCHAR(20) NOT NULL,
>age TINYINT UNSIGNED NULL,
>);

>INSERT TB2 VALUES(NULL,20);
<将报错说username不可为空>

4、自动编号(AUTO_INCREMENT)不能用char类型

自动编号,且需与主键组合使用
默认情况下,起始值为1,每次的增量为1;

5、主键约束(PRIMARY KEY)

每张数据表只存在一个主键
主键保证记录的唯一性
主键自动为not null

多个字段联合主键:
PRIMARY KEY(username,age);

>CREATE TABLE TB2(
>id SMALLINT UNSIGNED  PRIMARY KEY AUTO_INCREMENT,

AUTO_INCREMENT(自动递增)必须和PRIMARY KRY一起使用,而PRIMARY KEY则不一定要和AUTO_INCREMENT一起使用

>username VARCHAR(20) NOT NULL,
>);

>SHOW COLUMNS FROM TB3;

6、唯一约束(UNIQUE KEY)

唯一约束
唯一约束可以保证记录的唯一性
唯一约束的字段可以为NULL
每张数据表可以存在多个唯一约束
与主键的区别:一张数据表只能有一个主键,而UNIQUE KEY可以有多个可以NULL
>CREATE TABLE TB4(
>id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,<自动编号字段>
>username VARCHAR(20) NOT NULL UNIQUE KEY,
>age TINYINT UNSIGNED
>);

插入记录:INSERT TB4(username,age) VALUES(‘TOM’,23);
当再次写入同样的记录时,将提示错误,因为username用了unique约束。可想而知这个约束在数据表里可以有多个。

7、默认约束(DEFAULT)

默认值
当插入记录时,如果没有明确为字段赋值,则自动赋予默认值。
>CREATE TABLE TB5(
>id SAMLLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
>username VARCHAR(20) NOT NULL UNIQUE KEY,
>sex ENUM(‘1′,’2′,’3’) DEFAULT ‘3’
>);

验证:
INSERT TB5(username)VALUES(‘TOM’);
将发现自动给sex赋值3了。

8、外键约束

要求:
表与表之间的链接
父表和子表必须使用相同的储存引擎(InnoDB),而禁止使用临时表:
外键列和参照列必须具有相识的数据类型。其中数字的长度或是否有符号位必须相同;而字符     的长度则可以不同
外键列和参照列必须创建索引。如果外键列不存在索引的话,MySQL将自动创建索引。

CREATE TABLE provinces(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(20) NOT NULL
);
查看数据表的引擎:show create table provinces;
CREATE TABLE users(
id SMALLINT UNSIGNED PRIMARU KEY AUTO_INCREMENT,
username VARCHAR(10) NOT NULL,
//添加省份的话可以不用添加字段,只要添加关系表省份的编号就行
pid SMALLINT UNSIGNED, 
FOREIGN KEY(pid)REFERENCES provinces(id)
);

查看索引:SHOW INDEXES FROM provincesG;
SHOW INDEXES FROM usersG;

外键约束的参数:

CASCADE:从父表删除或更新且自动删除或更新字表中匹配的行
SET NULL:从父表删除或更新行,并设置子表中的外键列为NULL,如果使用该选项,必须保证
子表列没有指定NOT NULL
TESTRICT(约束、限制):拒绝对父表的删除或更新操作
NO ACTION:标准的SQL的关键字,在MySQL中RESTRICT相同。

为自动编号的字段赋值

可以书写成default 或者null
创建表:
CREATE TABLE users(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password VARCHAR(32) NOT NULL DEFAULT 123,
age TINYINT UNSIGNED NOT NULL,
sex BOOLEAN
);
插入记录:
INSERT users VALUES(NULL,’Jack’,159357,20,1);

9、

www.9778.com 3

www.9778.com 4

出错记录:
1、mysql服务启动不了,进程意外终止1067
错误显示:cannotconnecttomysqlserveronlocalhosts(1061)
解决方法:…

经常可以遇到这种情况:用不同的SQL写法可以达到同样的目的。爱钻牛角尖的人就想搞明白,诚然结果一样,但到底孰优孰劣?下面是我列出的一些,请兄弟们赐教。也请兄弟们将你们遇到的类似问题一并列出。

SQLServer 中的回车符与换行符
制表符:CHAR(9)  换行符:CHAR(10)  回车符:CHAR(13)
1、将 varchar 值作为条件,查询Where Id in 列表的值 
declare @a varchar ( 100 )
set @a = ‘56,58’   
— 正确的: 
select * from tb where charindex ( ‘,’ + ltrim (id) + ‘,’ , ‘,’ + @a + ‘,’ ) > 0 
— 错误的: 
select * from tb where id in + @a 

(1)一次插入多条数据时:CREATE TABLE tb(ID int, 名称 NVARCHAR(30), 备注
NVARCHAR(1000))INSERT tbSELECT 1,DDD,1UNIONALLSELECT
1,5100,DUNIONALLSELECT 1,5200,E

2、找出与某id相近的四条记录
Create table tb(id int ,cName char ( 10 ))
DECLARE @ID INT         
SET @ID = 7 
SELECT * FROM TB A WHERE id in 
( SELECT TOP 4 id FROM TB ORDER BY ABS (id – @id ))
ORDER BY ID 

也可以这样:CREATE TABLE tb1(ID int, 名称 NVARCHAR(30), 备注
NVARCHAR(1000))INSERT TB1 (ID,名称,备注)VALUES(1,DDD,1)INSERT TB1
(ID,名称,备注)VALUES(1,5100,D)INSERT TB1
(ID,名称,备注)VALUES(1,5200,E)_________________________________上面两种方法,哪种方法效率高?

3、按名称,规格分组,将单价数据合并成一行,并计算数量
Create table [tb] (tName varchar(4), [tSize] varchar (7), [ tPrice ] int , [ tQty ] int )
insert [ tb ] 
select ‘高瓦’ , ‘880*110’  , 22 , 1 union all select ‘高瓦’ , ‘880*110’ , 25 , 1 union all 
select ‘高瓦’ , ‘880*110’ , 22 , 1 union all select ‘高瓦’ , ‘880*120’ , 22 , 1 

(2)赋值时:SELECT @a=NaaSET
@a=Naa_________________________________上面两种方法,哪种方法效率高?

select   tName, tSize,
  tPrice = stuff (( select ‘,’ + ltrim (tPrice) from tb  
     where tName = t.tName and tSize = t.tSize FOR XML PATH(”)),1,1,”), sum(tQty)
as tQty
from   tb t group by   tName, tSize 
—结果—————————– 
tName   tSize     tPrice     tQty
高瓦    880*110    22,25,22  3
高瓦    880*120    22        1 

(3)取前几条数据时set ROWCOUNT 2 select * from tb order by fdselect Top
2 * from tb order by
fd_________________________________上面两种方法,哪种方法效率高?

4、根据出生日期,计算出准确的年龄 
SET @A = ‘2008-08-12’ 
DECLARE @A DATETIME 
SELECT 年龄 = 
case when datediff(day,dateadd(year,datediff(year,@A,getdate()),@A), getdate())>= 0 
      then datediff(year,@A,getdate()) else datediff(YY,@A,getdate())- 1 end 

(4)条件判断时where 0(select count(*) from tb where ……)where
exists(select * from tb where ……)
_________________________________上面两种方法,哪种方法效率高?

5、找出某目录列表中所有下级目录,包括自己

(5)NULLIF的使用—–同理它的反函数ISNULL的使用update tb set fd=case when
fd=1 then null else fd endupdate tb set
fd=nullif(fd,1)_________________________________上面两种方法,哪种方法效率高?

www.9778.com 5www.9778.com 6代码

(6)从字符串中取子字符串时substring(abcdefg,1,3)left(abcderg,3)_________________________________上面两种方法,哪种方法效率高?

DECLARE @FolderList varchar ( 800 )
SET @FolderList = ‘1’ 
SET NOCOUNT ON 
    CREATE TABLE # Temp (FolderId int )    
    INSERT # Temp 
    SELECT FolderId FROM Doc_Folder
    WHERE CHARINDEX ( ‘,’ + LTRIM (FolderId) + ‘,’ , ‘,’ + @FolderList + ‘,’ ) > 0     
    WHILE @@Rowcount > 0 
    BEGIN 
        INSERT # Temp SELECT FolderId FROM Doc_Folder AS A WHERE 
            EXISTS ( SELECT 1 FROM # Temp AS B WHERE B. [ FolderId ] = A.ParentFolderId)
            AND NOT EXISTS(SELECT 1 FROM # Temp AS B WHERE B. [FolderId] = A.[FolderId])
    END 

(7)EXCEPT和Not in的区别?

 

(8)INTERSECT和UNION的区别?下面是邹老大的回答:

6、简单静态游标

(1)一次插入多条数据时:

www.9778.com 7www.9778.com 8代码

第1种好一些, 但也得有个, 因为第1种的union all是做为一个语句整体,
查询优化器会尝试做优化, 同时, 也要先算出这个结果再插入的.

DECLARE product_cursor CURSOR STATIC FOR 
SELECT cName FROM Product 
OPEN product_cursor
FETCH NEXT FROM product_cursor INTO @product
WHILE @@FETCH_STATUS = 0
BEGIN
 SELECT @message = ‘ ‘ + @product
 PRINT @message
 FETCH NEXT FROM product_cursor INTO @product
END
CLOSE product_cursor
DEALLOCATE product_cursor

  1. 如果是单个赋值, 没有什么好比较的话.不过, 如果是为多个变量赋值,
    我测试过, SELECT 一次性赋值, 比用SET 逐个赋值效率好.

  2. SET ROWCOUNT和TOP 是一样的, 包括执行的计划等都是一样的

  3. 这个一般是exists快, 当然, 具体还要看你后面的子查询的条件,
    是否会引用外层查询中的对象的列.exists检查到有值就返回, 而且不返回结果集,
    count需要统计出所有满足条件的, 再返回一个结果集, 所以一般情况下exists快.

  4. 应该是一样的

  5. 基本上是一样的

  6. except会去重复, not in
    不会(除非你在select中显式指定)except用于比较的列是所有列,
    除非写子查询限制列, not in 没有这种情况8.
    intersect是两个查询都有的非重复值(交集),
    union是两个查询结果的所有不重复值(并集)

 

7、要求是取得每个ID对应postId的前三条

www.9778.com 9www.9778.com 10代码

CREATE TABLE [tb] (Id INT ,postId INT )
INSERT INTO [tb] 
SELECT 2788 , 45753530 UNION ALL 
SELECT 6417 , 46862065 UNION ALL 
SELECT 61773 , 47407456 UNION ALL
SELECT 61773 , 47436468 UNION ALL
SELECT 61773 , 47448259 UNION ALL 
SELECT 61773 , 47474393 UNION ALL 
SELECT 83604 , 41671947 UNION ALL 
SELECT 83604 , 45858681 UNION ALL
select id, postid from ( select * ,cid = row_number() over (partition by id order by id) from tb ) as t
where t.cid <= 3

 

8、实现编号自动增长

www.9778.com 11www.9778.com 12代码

–下面的代码生成长度为8的编号,编号以BH开头,其余6位为流水号。
— 得到新编号的函数 
CREATE FUNCTION f_NextBH()
RETURNS char ( 8 )
AS 
BEGIN 
RETURN (SELECT ‘BH’ +RIGHT(1000001 + ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK))
END 
GO 
— 在表中应用函数 
CREATE TABLE tb(
BH char (8) PRIMARY KEY DEFAULT dbo.f_NextBH(),col int)
— 插入资料 
BEGIN TRAN 
INSERT tb(col) VALUES ( 1 )
INSERT tb(col) VALUES ( 2 )
INSERT tb(col) VALUES ( 4 )
INSERT tb(BH,col) VALUES (dbo.f_NextBH(), 14 )
COMMIT TRAN 

9、表Age_AssignedNum中的字段CallRecordId,其值有三种:K、N、null,
   按PlanId分组统计出这三种值各有多少行

www.9778.com 13www.9778.com 14代码

SELECT PlanId,sK=count(CASE CallRecordId WHEN ‘K’ then CallRecordId end),
    sN=count(CASE CallRecordId WHEN ‘N’ then CallRecordId end),
    sNull=count( CASE isnull(CallRecordId,”) WHEN ” then ‘1’ end)
FROM Age_AssignedNum GROUP BY PlanId