sql2008 t-sql
单表查询
TOP选项
1. (结合order by)返回的是有固定顺序的游标
2. 可以使用
percent关键字
3. 附加属性
tiebreaker:
在没有给定所有列的order by次序时,top语句的返回列可能是不确定的,sql server将按照物理顺序返回结果。
tiebreaker是一个用以确定唯一结果顺序的field或field列表。
4.
WITH TIES选项 (相对于tiebreaker)
除了使用tiebreaker,还可以使用该选项返回具有所有相同结果的行。
例如 select top(3) field1, field2, field3 from xtable,可能会返回a,b,c行或a,b,d行
而select top(3) with ties field1, field2, field3 from xtable将返回所有的可能结果行: a,b,c,d
OVER子句
over子句为行定义一个窗口(window),以进行窗口函数(window function, 又分为
ranking和
aggregate)计算。
传统的聚合(aggregate)函数以group by查询作为操作上下文,每个每组仅返回一个值
开窗聚合函数使用over子句为上下文,没有数据分组,可以在返回记录中同时使用from, where, group by中的可用字段。
Ranking function: ROW_NUMBER, RANK, DENSE_RANK, NTITLE
select ROW_NUMBER() over(order by SalesOrderID) as RowNumber
, RANK() over (order by SalesOrderID) as Rank
, SalesOrderID
, ProductID
, OrderQty
, SUM(OrderQty) over(partition by SalesOrderID) as Total
from SalesLT.SalesOrderDetail as A
查询元数据
Sql server提供了一系列视图,用于获取数据库对象的元数据信息的工具:
目录视图 (Booksonline: catalog views): 提供数据库中各个对象的信息
信息架构视图 (Booksonline: INFORMATION_SCHEMA views): 基于ANSI SQL标准的形式提供元数据信息,不包含Sql server所特有的内容
系统存储过程和函数 (Booksonline: system stored procedures, system functions):
联接查询
1. 左联应用中非常有用的数字序列表,可以用来构造一个连续的结果集作为左联基础:
set nocount on
if OBJECT_ID('dbo.Nums', 'U') is not null drop table dbo.Nums
create table dbo.Nums(n int not null primary key)
declare @i as int = 1 -- SQL 2008 新语法
begin tran
while @i <=10000
begin
insert into dbo.Nums values(@i)
set @i = @i +1
end
commit tran
set nocount off
select DATEADD(day, n-1, '20060101') as orderdate
from dbo.Nums
where n <= DATEDIFF(day, '20060106', '20081231') + 1
order by orderdate
2. 左联的抵消
- 如果在左联的where子句中使用了非保留列的表达式作为条件,那么左联效果会被抵消,结果集会变成内联的效果。
- 因为该非保留列的表达式永远为: NULL<运算符><值>, 其值永远为unkown
3. 多表连接中使用外联容易出现的逻辑错误。下边是2.3点的例子
-- 1. 错误逻辑的例子
select C.CustomerID, O.SalesOrderID, OD.ProductID, OD.OrderQty
from SalesLT.Customer as C
left join SalesLT.SalesOrderHeader as O
on C.CustomerID = O.CustomerID
join SalesLT.SalesOrderDetail as OD
on O.SalesOrderID = OD.SalesOrderID -- 使用了非保留列作条件,将会抵消左联
-- 2. 正解1,连续左联
select C.CustomerID, O.SalesOrderID, OD.ProductID, OD.OrderQty
from SalesLT.Customer as C
left join SalesLT.SalesOrderHeader as O
on C.CustomerID = O.CustomerID
left join SalesLT.SalesOrderDetail as OD -- 继续左联
on O.SalesOrderID = OD.SalesOrderID
-- 3. 正解2,先内联再左联
select C.CustomerID, O.SalesOrderID, OD.ProductID, OD.OrderQty
from SalesLT.SalesOrderHeader as O
join SalesLT.SalesOrderDetail as OD -- 先内联
on O.SalesOrderID = OD.SalesOrderID
right join SalesLT.Customer as C -- 再右联
on O.CustomerID = C.CustomerID
-- 4. 正解3,类似正解2,使用括号,进行逻辑嵌套即可,代码省略
4. 表关联是的运算顺序为自左至右
5. 在外联查询中使用count应注意它会将外部行业纳入统计,因为它只根据行数进行统计
子查询
- 分为独立子查询(self-contained subquery),相关子查询(correlated subquery,子查询中引用了外部查询的表)。
- 查询结果可以返回单值(标量 scalar),多值(multi-valued)或表(table)
-- 返回前一条记录的子查询
-- 注意,这里是最简单的一个例子,效率会比较低
-- 可以把条件转移到where子句以提高效率
-- 或者使用错位联接,效率应该更高
select SalesOrderID, OrderDate
, (select MAX(O2.SalesOrderID)
from SalesLT.SalesOrderHeader as O2
where O2.SalesOrderID < O1.SalesOrderID) as prevorderid
from SalesLT.SalesOrderHeader as O1
-- 连续聚合 (running aggregate)
-- 通常使用连续聚合时应该至少有一个时间这样类型的列来表达时间连续的概念
select orderyear, qty
, (select SUM(O2.qty)
from SalesLT.OrderTotalByYear as O2
where O2.orderyear <= O1.orderyear) as runqty
from SalesLT.OrderTotalByYear as O1
order by orderyear
-- 行为不当的子查询1,返回没下过单的客户
-- 如果子查询的返回有null,则该查询结果永远为空,因为where子句中的条件结果为unknown
select CustomerID, CompanyName
from SalesLT.Customer as C
where CustomerID not in (select CustomerID
from SalesLT.SalesOrderHeader) -- 没有考虑CustomerID为空的情况
-- 最佳实践: 1.必要时设置field为not null。2.时刻注意SQL的三值逻辑中null的处理
-- 行为不当的子查询2,相近名称造成的子查询混淆
-- 造成这个错误一方面由于名字相近
-- 另一方面是由于sql server的搜索策略:当内查询不存在指定的field时,继续搜索外部查询
-- 这种行为无意中将一个独立子查询转换成了相关子查询
create table SalesLT.MyShippers (
shipper_id int not null primary key,
companyname nvarchar(40) not null
)
create table SalesLT.Orders (
custid int not null primary key,
shipperid int not null
)
-- 返回把订单发货给43号客户的发货人
select shipper_id, companyname
from SalesLT.MyShippers
where shipper_id in ( select shipper_id -- 注意这里不是 shipperid,
from SalesLT.Orders
where custid = 43)
-- 最佳实践:
-- 1. 长远角度应该考虑统一、一致的命名方案
-- 2. 短期角度则应该在子查询的field前指定来源表
分享到:
相关推荐
《Microsoft SQL Server 2008技术内幕:T-SQL查询》全面深入地介绍了Microsoft SQL Server 2008中高级T-SQL查询、性能优化等方面的内容,以及SQL Server 2008新增加的一些特性。主要内容包括SQL的基础理论、查询优化...
《Microsoft SQL Server 2008技术内幕:T-SQL查询》是一本深入探讨SQL Server 2008中T-SQL(Transact-SQL)查询的权威书籍。T-SQL是SQL Server数据库管理系统的核心语言,用于数据操作、查询、存储过程编写以及...
不过,我可以根据标题和描述提供的信息,以及对SQL Server 2008和T-SQL语言基础知识的理解,为您构建一份关于Microsoft SQL Server 2008中T-SQL语言基础知识的知识点概要。 Microsoft SQL Server 2008是微软公司...
《Microsoft SQL Server 2008技术内幕:T-SQL查询》是一本深入探讨SQL Server 2008中T-SQL(Transact-SQL)查询的权威指南。T-SQL是微软SQL Server数据库管理系统的核心语言,用于数据操作、查询、存储过程编写以及...
本书全面深入地介绍了Microsoft SQL Server 2008中高级T-SQL查询、性能优化等方面的内容,以及SQL Server 2008新增加的一些特性。主要内容包括SQL的基础理论、查询优化、查询算法及复杂度,以及在使用子查询、表...
《Microsoft SQL Server 2005技术内幕:T-SQL查询的源代码》是一本深入探讨SQL Server 2005中T-SQL查询技术的专业书籍。T-SQL(Transact-SQL)是Microsoft SQL Server数据库管理系统中用于数据操作、查询、存储过程...
作为一本讲述T-SQL高级查询的专业图书,《Microsoft SQL Server 2008技术内幕:T-SQL查询》旨在结合实践中的各种常见问题,教读者如何用SQL作为语言工具来思考问题,揭示基于集合查询的强大威力。《Microsoft SQL ...
Microsoft SQL Server 2005技术内幕:T-SQL查询.part1
Microsoft SQL Server 2008技术内幕:T-SQL语言基础的示例数据库无法再国外的网址上下载,这个是已经下载好的
《Microsoft SQL Server 2008技术内幕:T-SQL语言基础》是Microsoft SQL Server 2008系列中的一本。书中全面深入地介绍了T-SQL的基本元素,以及SQL Server 2008中新增加的一些特性。主要包括SQL的基础理论、逻辑...
《Microsoft SQL Server 2008技术内幕:T-SQL语言基础》是针对数据库开发者和管理员的一本深入学习SQL Server 2008 T-SQL(Transact-SQL)语言的专业指南。T-SQL是微软SQL Server的核心语言,用于执行查询、数据操作...
《Microsoft SQL Server 2008技术内幕:T-SQL查询》是一本深入探讨SQL Server 2008中T-SQL(Transact-SQL)查询的权威书籍。T-SQL是微软SQL Server的主要编程语言,用于管理和操作数据库,执行查询、数据更新以及...
《Microsoft SQL Server 2008技术内幕:T-SQL查询》一书的源代码及附录A内容。
《Microsoft SQL Server 2008技术内幕:T-SQL语言基础》是Microsoft SQL Server 2008系列中的一本。书中全面深入地介绍了T-SQL的基本元素,以及SQL Server 2008中新增加的一些特性。主要包括SQL的基础理论、逻辑查询...
《Microsoft SQL Server 2005技术内幕:T-SQL查询》 《Microsoft SQL Server 2005技术内幕:T-SQL程序设计》 《Microsoft SQL Server 2005技术内幕:存储引擎》 《Microsoft SQL Server 2005技术内幕:查询、调整...
《SQLServer2008技术内幕:T-SQL查询》这本书深入探讨了SQL Server 2008中的Transact-SQL(T-SQL)查询语言,这是SQL Server数据库管理系统的核心部分,用于数据检索、更新、插入和删除。T-SQL不仅包含标准的SQL语法...
《Microsoft SQL Server 2005技术内幕全套(三):T-SQL查询》是一部深入探讨SQL Server 2005数据库管理系统中Transact-SQL(T-SQL)查询技术的专业著作。本部分主要聚焦于如何高效、准确地在SQL Server 2005环境中...
Microsoft SQL Server 2005技术内幕:T-SQL查询 中文
《Microsoft SQL Server 2008技术内幕:T-SQL语言基础》是关于数据库管理和开发的重要参考资料,专注于T-SQL(Transact-SQL)这一SQL Server的核心编程语言。T-SQL是微软SQL Server中用于数据查询、更新、插入和删除...
Microsoft SQL Server 2005技术内幕:T-SQL查询,经典书籍,上传受限,所以分开两个文件