TSQL基础(一) - 查询
select
1.查询一张表(orders)的所以纪录
select * from Orders
2.查询一张表(orders)某字段的所有记录
select OrderID,OrderDate from Orders
where
如需有条件地从表中选取数据,可将 WHERE 子句添加到 SELECT 语句
select * from Orders where OrderID=10004
group by
分组查询,根据某些字段进行分组查询,查询返回的字段只能是跟在grooup by后面的分组字段
select * from Orders group by OrderID 错误 select OrderID from Orders group by OrderID 正确 //当根据多个字段进行分组时,从左到右进行一步一步分组
select OrderDate, CustomerID from Orders group by OrderDate,CustomerID
having
having的作用和where一样是条件筛选,但是having是对分组后的集合进行筛选,和Group by一起使用
select OrderID from Orders group by OrderID having OrderID=10004
order by
order by 语句用于对结果集进行排序 order by column asc/desc //asc--升序(默认), desc--降序
select OrderID from Orders order by OrderID //按OrderID进行升序排列
select OrderID from Orders order by OrderID asc //按OrderID进行升序排列
select OrderID from Orders order by OrderID desc //按OrderID进行降序排列
top
TOP 子句用于规定要返回的记录的数目
select top 10 OrderID from Orders //返回前10条记录
select top 10 percent OrderID from Orders //返回前百分之10的纪录
执行顺序
from -> where -> group by -> having -> select -> order by -> top
select top 1 OrderID from Orders where OrderID=10004 group by OrderID having OrderID=10004 order by OrderID