关于MSSQL的方面的书籍,网上有很多,现实生活中也有很多纸版教材,有很多相当不错的书, 但大都含盖了MSSQL技术的各个方面. 不少朋友有相当的基础,或丰富的数据库管理经验,但在开发上面经常一些数据操作却无法完成. 笔者从事MSSQL开发5年,总结了一下自己在数据检索上的心得,与大家共享.
本文不讨论数据库设计,不讨论性能优化,也不讨论存储过程(用好了语言套上特定语法那它就是存储过程),触发器,函数之类的.本文也不讲游标,循环(因为理论上,只要游标能实现的在前台程序中使用数据访问组件如ADO,ADO.NET等等都可以实现,并且借助应用程序语言所提供的条件,您拥有更灵活处理方式. 同时笔者也建议若没有特殊要求,建议您权衡某些功能的实现是否必须要放在数据库端完成)
为突出主题,本文只讲查询,并尽量试着用笔者认为效率最高的查询语句来完成,因为笔者水平有限并不确定使用的就是最有效的.
本文适合有一定基础并且希望能提高自己数据检索水平的朋友（您的水平不错的话，可以点右上角的X了.）. 本文旨在阐述数据检索心得,而不作为手册,所以不会面面俱到,阅读本文同时,请确保您有一本手册或联机丛书在傍边.
由于水平有限,文中难免有错误的地方, 欢迎指正.Let’s go
(文中错误语句以绿色标识，有任何问题也可以一起讨论
MSN:fcuandy@163.net，http://Forum.devms.com,http://www.idotnet.org)
1,基础
(1) MSSQL为关系数据库,为此您必须了解一些概念
a.关系模型
二维表，记录（行），字段（列，属性），表之间的关系
ID GroupID UserName PassWord
1 3 James Doyouknowmypass
2 1 Merry Merry1984
3 1 Andy MyLove
以上是一个二维表 UserInfo, 请确认您已经确认它们在关系模型中的名称.
b.语言
数据定义 DDL CREATE,DROP,ALTER
数据操纵 DML SELECT,INSERT,UPDATE,DELETE
数据控制 DCL GRANT,REVOKE
T-SQL
文本讨论数据检索,所以主要会用到 DDL,DML,T-SQL这些. 请确认您已常握了常用的基础语法.能说出下面的语句各有什么功能.
SELECT * FROM UserInfo WHERE ID=1
SELECT UserName FROM UserInfo ORDER BY ID DESC
UPDATE UserInfo SET PassWord=’xxxxxx’ WHERE UserName=’James’
DELETE FROM UserInfo WHERE ID=2
DROP TABLE UserInfo
TRUNCATE TABLE UserInfo
针对您对二维表的理解,想象一下,为什么下面这个语句会出错
SELECT UserName,GroupID FROM UserInfo GROUP BY GroupID
您可能会说,这是语法报错.然而,严格的说在语法上这句是没有错误的. 原因是,当按GroupID分组时, GroupID与UserName成了一对多的关系,系统无法确定在GroupID=1的结果行中将UserName 取为Andy还是Merry. 实事上,系统不管您的数据表中的某个GroupID是否会对应多个UserName,均会一律对待,因为它不打算冒这个险.^^
(2)函数
您应该牢记一些函数 MIN,MAX,COUNT,RTRIM,GETDATE(),SUBSTRING, ISNULL,LEFT,CHARINDEX,REPLACE,ISNUMERIC,ISDATE等等.
您应该对一些函数至少有印象,知道他们是做什么用的,具体参数可以查手册,用的次数多后就会记下来. 当然有精力去记下它们更好.如
DATEDIFF,DATEADD,CONVERT,REPLICATE,STUFF,PATINDEX等等
(3)另外一些系统函数
虽然在本文中我们可能不会用到它,但是在您的实际工用中,它们并不可少如:@@rowcount,@@identity,object_id,NEWID,DATALENGTH等等
(4)一些语句或关健字或语句或系统存储过程用法
… WHERE UserName IS NULL
… WHERE UserName LIKE ‘Me%’
COLLATE ..
Sp_ExecuteSql
CHECKSUM
LEFT( OUTER) JOIN,INNER JOIN,CROSS JOIN等等,它们的区别是什么
等等. 这些语句的意思,或关健字或过程的用法在联机丛书上都有说明,如果您对某些的用法并不熟悉,请对照联机丛书的说明掌握它们的用法.
(5)一些需要注意的地方
SELECT TOP 2 ID FROM t1 ORDER BY ID
UNION ALL
SELECT TOP 2 ID FROM t2 ORDER BY ID
这句报错的原因在于 使用UNION运算符时, ORDER BY 对多个 SELECT Statement结果集的合集有效,因此 ORDER BY 不能出现在某个 SELECT Statement之后(最后的那个除外)
实现上述语句功能可以用下面的方法代替
1-5.1
SELECT * FROM (SELECT TOP 2 ID FROM t1 ORDER BY ID)
UNION ALL
SELECT * FROM (SELECT TOP 2 ID FROM t2 ORDER BY ID)
或者
1-5.2
SELECT * FROM
(SELECT ID,frm=’t1’ FROM t1
UNION ALL
SELECT ID,frm=’t2’ FROM t2) a
WHERE 2>(SELECT COUNT(1) FROM
(SELECT ID,frm=’t1’ FROM t1
UNION ALL
SELECT ID,frm=’t2’ FROM t2) b
WHERE a.frm=b.frm AND b.id<a.id
)
但从语句上来看,二者效率明显有差别. 但请注意,看起来效率低的实际运行中并不一定效率低,有些时候系统会进行语句优化成更有效的语句,您可以看执行计划来分析. 当然,这个例子确实效率有差别. 效率问题不再累赘.
(语句中的frm书面语称”别名”,我喜欢叫它虚字段,虚例,同样,子查询我喜欢称它为虚表.尽管叫法不够科学,但这样更有助于理解和描述.)
至于第二种写法思路缘何而来,在第二部分会有详细介绍
DECLARE @i INT,@s VARCHAR(100)
SET @s=’Me’
EXEC(‘SELECT @i=Count(*) FROM UserInfo WHERE UserName LIKE ’’’ + @s + ‘%’’’)
在EXEC外部定义的变量@i报错:变量未声明. EXEC内外部语句执行级别不同,编译不在同一空间,所以报错. 这里应该使用sp_executesql获取结果
还有一些其它要注意的问题,在些不一一列出.总之在您写查询语句被提示报错后,请冷静思考
a. 二维表的意义
b. 一些语句或操作内在需要注意的地方
c. 一些语句碰到意外值
d. 其它
本文不讨论数据库设计,不讨论性能优化,也不讨论存储过程(用好了语言套上特定语法那它就是存储过程),触发器,函数之类的.本文也不讲游标,循环(因为理论上,只要游标能实现的在前台程序中使用数据访问组件如ADO,ADO.NET等等都可以实现,并且借助应用程序语言所提供的条件,您拥有更灵活处理方式. 同时笔者也建议若没有特殊要求,建议您权衡某些功能的实现是否必须要放在数据库端完成)
为突出主题,本文只讲查询,并尽量试着用笔者认为效率最高的查询语句来完成,因为笔者水平有限并不确定使用的就是最有效的.
本文适合有一定基础并且希望能提高自己数据检索水平的朋友（您的水平不错的话，可以点右上角的X了.）. 本文旨在阐述数据检索心得,而不作为手册,所以不会面面俱到,阅读本文同时,请确保您有一本手册或联机丛书在傍边.
由于水平有限,文中难免有错误的地方, 欢迎指正.Let’s go
(文中错误语句以绿色标识，有任何问题也可以一起讨论
MSN:fcuandy@163.net，http://Forum.devms.com,http://www.idotnet.org)
1,基础
(1) MSSQL为关系数据库,为此您必须了解一些概念
a.关系模型
二维表，记录（行），字段（列，属性），表之间的关系
ID GroupID UserName PassWord
1 3 James Doyouknowmypass
2 1 Merry Merry1984
3 1 Andy MyLove
以上是一个二维表 UserInfo, 请确认您已经确认它们在关系模型中的名称.
b.语言
数据定义 DDL CREATE,DROP,ALTER
数据操纵 DML SELECT,INSERT,UPDATE,DELETE
数据控制 DCL GRANT,REVOKE
T-SQL
文本讨论数据检索,所以主要会用到 DDL,DML,T-SQL这些. 请确认您已常握了常用的基础语法.能说出下面的语句各有什么功能.
SELECT * FROM UserInfo WHERE ID=1
SELECT UserName FROM UserInfo ORDER BY ID DESC
UPDATE UserInfo SET PassWord=’xxxxxx’ WHERE UserName=’James’
DELETE FROM UserInfo WHERE ID=2
DROP TABLE UserInfo
TRUNCATE TABLE UserInfo
针对您对二维表的理解,想象一下,为什么下面这个语句会出错
SELECT UserName,GroupID FROM UserInfo GROUP BY GroupID
您可能会说,这是语法报错.然而,严格的说在语法上这句是没有错误的. 原因是,当按GroupID分组时, GroupID与UserName成了一对多的关系,系统无法确定在GroupID=1的结果行中将UserName 取为Andy还是Merry. 实事上,系统不管您的数据表中的某个GroupID是否会对应多个UserName,均会一律对待,因为它不打算冒这个险.^^
(2)函数
您应该牢记一些函数 MIN,MAX,COUNT,RTRIM,GETDATE(),SUBSTRING, ISNULL,LEFT,CHARINDEX,REPLACE,ISNUMERIC,ISDATE等等.
您应该对一些函数至少有印象,知道他们是做什么用的,具体参数可以查手册,用的次数多后就会记下来. 当然有精力去记下它们更好.如
DATEDIFF,DATEADD,CONVERT,REPLICATE,STUFF,PATINDEX等等
(3)另外一些系统函数
虽然在本文中我们可能不会用到它,但是在您的实际工用中,它们并不可少如:@@rowcount,@@identity,object_id,NEWID,DATALENGTH等等
(4)一些语句或关健字或语句或系统存储过程用法
… WHERE UserName IS NULL
… WHERE UserName LIKE ‘Me%’
COLLATE ..
Sp_ExecuteSql
CHECKSUM
LEFT( OUTER) JOIN,INNER JOIN,CROSS JOIN等等,它们的区别是什么
等等. 这些语句的意思,或关健字或过程的用法在联机丛书上都有说明,如果您对某些的用法并不熟悉,请对照联机丛书的说明掌握它们的用法.
(5)一些需要注意的地方
SELECT TOP 2 ID FROM t1 ORDER BY ID
UNION ALL
SELECT TOP 2 ID FROM t2 ORDER BY ID
这句报错的原因在于 使用UNION运算符时, ORDER BY 对多个 SELECT Statement结果集的合集有效,因此 ORDER BY 不能出现在某个 SELECT Statement之后(最后的那个除外)
实现上述语句功能可以用下面的方法代替
1-5.1
SELECT * FROM (SELECT TOP 2 ID FROM t1 ORDER BY ID)
UNION ALL
SELECT * FROM (SELECT TOP 2 ID FROM t2 ORDER BY ID)
或者
1-5.2
SELECT * FROM
(SELECT ID,frm=’t1’ FROM t1
UNION ALL
SELECT ID,frm=’t2’ FROM t2) a
WHERE 2>(SELECT COUNT(1) FROM
(SELECT ID,frm=’t1’ FROM t1
UNION ALL
SELECT ID,frm=’t2’ FROM t2) b
WHERE a.frm=b.frm AND b.id<a.id
)
但从语句上来看,二者效率明显有差别. 但请注意,看起来效率低的实际运行中并不一定效率低,有些时候系统会进行语句优化成更有效的语句,您可以看执行计划来分析. 当然,这个例子确实效率有差别. 效率问题不再累赘.
(语句中的frm书面语称”别名”,我喜欢叫它虚字段,虚例,同样,子查询我喜欢称它为虚表.尽管叫法不够科学,但这样更有助于理解和描述.)
至于第二种写法思路缘何而来,在第二部分会有详细介绍
DECLARE @i INT,@s VARCHAR(100)
SET @s=’Me’
EXEC(‘SELECT @i=Count(*) FROM UserInfo WHERE UserName LIKE ’’’ + @s + ‘%’’’)
在EXEC外部定义的变量@i报错:变量未声明. EXEC内外部语句执行级别不同,编译不在同一空间,所以报错. 这里应该使用sp_executesql获取结果
还有一些其它要注意的问题,在些不一一列出.总之在您写查询语句被提示报错后,请冷静思考
a. 二维表的意义
b. 一些语句或操作内在需要注意的地方
c. 一些语句碰到意外值
d. 其它
浙公网安备 33010602011771号