TSQL基础(一)

1.查询一张表(orders)的所以纪录

  select   *   from  Orders

2.查询一张表(orders)某字段的所有记录

  select  OrderID,OrderDate from Orders

如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句

  select  * from Orders where OrderID=10004

分组查询，根据某些字段进行分组查询，查询返回的字段只能是跟在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的作用和where一样是条件筛选，但是having是对分组后的集合进行筛选，和Group by一起使用

select   OrderID from Orders   group by OrderID having OrderID=10004

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 子句用于规定要返回的记录的数目

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