TSQL入门(一) - 查询

2023-12-05 13:49:52

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    正确

//当根据多个字段进行分组时,并非从左到右进行一步一步分组,而是把2个字段组合在一起进行一次性分组
 select OrderDate, CustomerIDfrom 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的纪录

执行顺序

1	`from` `->` `where` `->` `group` `by` `->` `having` `->` `order` `by` `->` `top`

select top 1 OrderID from Orders where OrderID=10004 
        　　　　 group by OrderID having OrderID=10004 order by OrderID

TSQL入门(一) - 查询,布布扣,bubuko.com