倒序查询_干货：SQL Server 查询语句

查看 SQL Server 中所有数据库的信息

select * from sysdatabases

查看当前使用的数据库中所有表信息

use Northwindselect * from sysobjects where type='U'select * from sysobjects where type not in('U','S','SQ','IT','D')  --当前使用的数据库中所有表约束exec sp_help Categories  --查看指定表结构

查询表的所有数据

select * from Categories --商品种类select * from Suppliers --供应厂商select * from Products --商品信息select * from Customers --客户信息select * from Employees --员工信息select * from Shippers --货运公司select * from Orders --订单信息select * from OrderDetails --订单详情--delete from OrderDetails --备份测试用select * from Reports --报表配置

查询结果排序

select CategoryName from Categories --默认按首字段值的首字母排序(与MySQL不同，MySQL默认是主键排序)select CategoryName from Categories order by CategoryID --默认编号正序select CategoryName from Categories order by CategoryID asc --编号正序select CategoryName from Categories order by CategoryID desc --编号倒序select * from OrderDetails where OrderID in(10248,10249) order by OrderID asc,ProductID asc --按多列排序1select * from OrderDetails where OrderID in(10248,10249) order by OrderID asc,ProductID desc --按多列排序2select * from OrderDetails where OrderID in(10248,10249) order by OrderID desc,ProductID asc --按多列排序3select * from OrderDetails where OrderID in(10248,10249) order by OrderID desc,ProductID desc --按多列排序4

指定条数查询

select top 2 * from Categories order by CategoryID --头两行数据(排序必要)select top 2 * from Categories where CategoryID not in(select top 2 CategoryID from Categories) order by CategoryID --第二行后两行数据select top 2 CategoryID from Categories order by CategoryID desc --倒数两行数据

分页查询

每页显示3条。

想法一

select COUNT(*) from Categories -- 8/3=2···2，最后一页余2条数据select top 3 * from (select top (1*3) * from Categories order by CategoryID) Tab order by CategoryID desc --分页第一页，每页3条select top 3 * from (select top (2*3) * from Categories order by CategoryID) Tab order by CategoryID desc --分页第二页，每页3条select top (8%3) * from (select top (3*3) * from Categories order by CategoryID) Tab order by CategoryID desc --分页第三页，每页3条

想法二(正序)

--select top PerPage * from Categories where CategoryID not in(select top ((NowPage-1)*PerPage) CategoryID from Categories order by CategoryID) order by CategoryIDselect top 3 * from Categories where CategoryID not in(select top (0*3) CategoryID from Categories order by CategoryID) order by CategoryIDselect top 3 * from Categories where CategoryID not in(select top (1*3) CategoryID from Categories order by CategoryID) order by CategoryIDselect top 3 * from Categories where CategoryID not in(select top (2*3) CategoryID from Categories order by CategoryID) order by CategoryID

想法二(倒序)

select top 3 * from Categories where CategoryID not in(select top (0*3) CategoryID from Categories order by CategoryID desc) order by CategoryID descselect top 3 * from Categories where CategoryID not in(select top (1*3) CategoryID from Categories order by CategoryID desc) order by CategoryID descselect top 3 * from Categories where CategoryID not in(select top (2*3) CategoryID from Categories order by CategoryID desc) order by CategoryID desc

查询字段指定别名

select CategoryID,CategoryName from Categories --查询指定列select CategoryID,CategoryName as 种类名称 from Categories --指定列别名1select CategoryID,CategoryName 种类名称 from Categories --指定列别名2select CategoryID,种类名称=CategoryName from Categories --指定列别名3

集合函数

select count(*) 记录总数 from Categories --计算总数select UnitPrice,UnitPrice+10 结果值 from OrderDetails --查询结果计算select max(CategoryID) from Categories --求一列的最大值select min(CategoryID) from Categories --求一列的最大值select avg(UnitPrice) 平均价格 from Products --求所有商品的平均价格select * from Products --求所有商品的平均价格--select UnitPrice from Products where ProductID<=3 --查询指定商品的价格select avg(UnitPrice) from Products where ProductID<=3 --求指定商品的平均价格

函数查询

select * from Categories where len(CategoryName)=3 --根据字段长度查询select * from Categories where len(PictureFile)=7 --根据字段长度查询

条件查询

select * from Categories where CategoryID=2select * from Categories where CategoryID<>2select * from Categories where CategoryID!=2select * from Categories where CategoryID in(2,4,6)select * from Categories where CategoryID not in(2,4,6)select * from Categories where CategoryID>3select * from Categories where CategoryID>=3 and CategoryID<6select * from Categories where CategoryID>=3 and CategoryID<6 and CategoryID<>4select * from Categories where CategoryID<3 or CategoryID>6select * from Categories where CategoryID<3 or CategoryID>6 or CategoryID=5select * from Categories where CategoryID between 3 and 5select * from Categories where CategoryID not between 3 and 5select * from Categories where CategoryID not between 3 and 5 and CategoryID not in(1,2)select * from Suppliers where Fax is nullselect * from Suppliers where Fax is not nullselect * from Categories where CategoryName='谷类/麦片'select * from Categories where CategoryName like '[谷,米]类/麦片'select * from Categories where CategoryName like '^[谷,米]类/麦片'select * from Categories where CategoryName like '_类/麦片'select * from Categories where CategoryName like '__类/麦片'select * from Categories where CategoryName like '%/麦片'select * from Categories where CategoryName like '谷类/%'select * from Categories where CategoryName like '%/%'

通配符：
1. %，包含0个或多个字符的任意字符；
2. _，任何单个字符；
3. []，指定范围([a-f])或集合([abcd])的任何单个字符；
4. [^]，不属于指定范围([a-f])或集合([abcd])的任何单个字符。

日期查询

select * from Orders where OrderDate='1996-07-04'select * from Orders where OrderDate>='1996-01-01' and OrderDate

分组查询

select distinct ProductID from OrderDetails --出现过的ProductID(查询结果不会有重复的值)select ProductID,count(ProductID) 订单数量,sum(Quantity) 该类总量 from OrderDetails group by ProductID --按ProductID分组，并求得每种的出现次数，与该种类的数量总和select ProductID,count(ProductID) 订单数量,sum(Quantity) 该类总量 from OrderDetails group by ProductID having sum(Quantity)<200 --在上面分组查询的基础上添加新的条件select ProductID,count(ProductID) 订单数量,sum(Quantity) 该类总量 from OrderDetails group by ProductID having sum(Quantity)<200 and ProductID<>15 --在上面分组查询的基础上添加新的条件

临时表

select CategoryID,CategoryName,Description into #TempTab1 from Categories where CategoryID between 3 and 5select * from #TempTab1drop table #TempTab1

子查询

select * from Products where SupplierID in(select SupplierID from Suppliers where City='上海')select Tab1.CompanyName from (select * from Suppliers where City='上海') as Tab1select CompanyName from (select * from Suppliers where City='上海') as Tab1

联表查询

select P.CategoryID,C.CategoryName,P.ProductID,P.ProductName,P.QuantityPerUnit,P.UnitPrice,P.UnitsInStock from Products P join Categories C on P.CategoryID=C.CategoryIDselect C.CategoryName,P.ProductID,P.ProductName,P.QuantityPerUnit,P.UnitPrice,P.UnitsInStock from Products P join Categories C on P.CategoryID=C.CategoryID

即使查询字段里不存在两表的 CategoryID，仍可用两表的 CategoryID 联表。

连表方式：

1. 交叉连接(cross join)：将两个表不加任何约束地组合起来，在实际应用中一般没有意义；
2. 内连接(自然连接)([inner] join)：将交叉连接按照连接条件进行过滤，匹配的才能出现在结果集，通常采用主键=外键的形式；
3. 外连接：和内连接的不同是，不匹配条件的行也能出现在结果集，对应的空位会被填上NULL，左外连接(left join, left outer join)是对左表不加限制，右外连接(right join, right outer join)是对右表不加限制，全外连接(full join, full outer join)是对左右两表都不加限制。

合并查询

select CategoryID,CategoryName from Categories where CategoryID<=4 union select CategoryID,CategoryName from Categories where CategoryID>4 --将两个或两个以上的查询结果合并

逻辑查询case

select LastName+FirstName as 姓名,TitleOfCourtesy as 称谓 from Employeesselect LastName+FirstName 姓名,case Genderwhen 0 then '女' when 1 then '男' end as 性别 from Employeesselect LastName+FirstName 姓名,case TitleOfCourtesywhen '女士' then '女孩' when '先生' then '男孩' else '未知' end as 称谓 from Employees

select 与 print

print 123select 123select 123 as Resultselect 123 Result

逻辑查询 ifelse，convert 类型转换

declare @name nvarchar(10) set @name='点心'if exists(select CategoryName from Categories where CategoryName=@name)    begin     print '存在 '+@name --可用select    declare @id int    select @id=CategoryID from Categories where CategoryName=@name    print 'ID: '+convert(varchar,@id)    endelse print '不存在 '+@name--可用selectif(select CategoryID from Categories where CategoryName='点心')=3 print 'Right' else print 'Wrong'

时间控制 waitfor

waitfor delay '00:00:03'--等待3秒select '11'waitfor time '17:44:03'--等待到具体时间select '22'

获取时间 getdate, datename

select getdate()select datename(year,getdate())select datename(month,getdate())select datename(day,getdate())select datename(hour,getdate())select datename(minute,getdate())select datename(second,getdate())select datename(millisecond,getdate())select datename(year,getdate())+'-'+ datename(month,getdate())+'-'+datename(day,getdate())

循环控制 while

declare @i int set @i=1while 1=1    begin    if @i<10         begin        print @i         set @i=@i+1        end    else break    end

视图查询

create view Categories_Products as select P.CategoryID,C.CategoryName,P.ProductID,P.ProductName,P.QuantityPerUnit,P.UnitPrice,P.UnitsInStock from Products P join Categories C on P.CategoryID=C.CategoryIDselect * from Categories_Products --查询视图exec sp_helptext Categories_Products --查询视图的创建语句exec sp_help Categories_Products --查看视图结构create view Categories_Products with encryption as select P.CategoryID,C.CategoryName,P.ProductID,P.ProductName,P.QuantityPerUnit,P.UnitPrice,P.UnitsInStock from Products P join Categories C on P.CategoryID=C.CategoryID --创建视图并加密，加密后不能使用 exec sp_helptext 查看它的创建语句alter view Categories_Products with encryption as select P.CategoryID,C.CategoryName,P.ProductID,P.ProductName,P.QuantityPerUnit,P.UnitPrice,P.UnitsInStock from Products P join Categories C on P.CategoryID=C.CategoryID --加密视图drop view Categories_Products --删除视图

用视图修改数据表的数据

1. 若视图字段来自表达式或常量，则只能进行delete操作；
2. 若视图字段来自集合函数，则不允许修改操作；
3. 若视图定义中含group by子句，则不允许修改操作；
4. 若视图定义中含有distinct短语，则不允许修改操作；
5. 在一个不允许修改操作视图上定义的视图，不允许修改操作。

update Categories_Products set ProductName='牛奶2' where ProductID=2update Categories_Products set ProductName='牛奶' where ProductID=2  

修改与删除数据

修改与删除的 where 条件与条件查询的语法相同。

select * from Categoriesupdate Categories set CategoryName='牛奶2' where CategoryID=2update Categories set CategoryName='牛奶2',Description='暂无描述' where CategoryID=2delete from Categories where CategoryID=2delete from Categories --删除指定表内全部数据：有删除记录，可恢复truncate table Categories --删除指定表内全部数据(能重置主键ID的递增起始数为1)：速度快，无删除记录，不可恢复，不可删除有外键的表