SQL Server 查询语句

查看 SQL Server 中所有数据库的信息
select * from sysdatabases
查看当前使用的数据库中所有表信息
use Northwind select * 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
CategoryNamefrom Categories order by CategoryID asc --编号正序 select CategoryName
from Categories order by CategoryID desc --编号倒序 select * from OrderDetails where
OrderIDin(10248,10249) order by OrderID asc,ProductID asc --按多列排序1 select *
from OrderDetails where OrderID in(10248,10249) order by OrderID asc,ProductID
desc --按多列排序2 select * from OrderDetails where OrderID in(10248,10249) order by
OrderIDdesc,ProductID asc --按多列排序3 select * 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
CategoryIDdesc --倒数两行数据
分页查询

每页显示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
CategoryIDdesc --分页第三页，每页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
) CategoryIDfrom Categories order by CategoryID) order by CategoryID select top
3 * from Categories where CategoryID not in(select top (1*3) CategoryID from
Categoriesorder by CategoryID) order by CategoryID select top 3 * from
Categorieswhere 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)
CategoryIDfrom Categories order by CategoryID desc) order by CategoryID desc
select top 3 * from Categories where CategoryID not in(select top (1*3)
CategoryIDfrom Categories order by CategoryID desc) order by CategoryID desc
select top 3 * from Categories where CategoryID not in(select top (2*3)
CategoryIDfrom Categories order by CategoryID desc) order by CategoryID desc
查询字段指定别名
select CategoryID,CategoryName from Categories --查询指定列 select
CategoryID,CategoryNameas 种类名称 from Categories --指定列别名1 select
CategoryID,CategoryName 种类名称from Categories --指定列别名2 select
CategoryID,种类名称=CategoryNamefrom 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
Categorieswhere len(PictureFile)=7 --根据字段长度查询
条件查询
select * from Categories where CategoryID=2 select * from Categories where
CategoryID<>2 select * from Categories where CategoryID!=2 select * from
Categorieswhere CategoryID in(2,4,6) select * from Categories where CategoryID
not in(2,4,6) select * from Categories where CategoryID>3 select * from
Categorieswhere CategoryID>=3 and CategoryID<6 select * from Categories where
CategoryID>=3 and CategoryID<6 and CategoryID<>4 select * from Categories where
CategoryID<3 or CategoryID>6 select * from Categories where CategoryID<3 or
CategoryID>6 or CategoryID=5 select * from Categories where CategoryID between 3
and 5 select * from Categories where CategoryID not between 3 and 5 select *
from Categories where CategoryID not between 3 and 5 and CategoryID not in(1,2)
select * from Suppliers where Fax is null select * from Suppliers where Fax is
not null select * from Categories where CategoryName='谷类/麦片' select * from
Categorieswhere CategoryName like '[谷,米]类/麦片' select * from Categories where
CategoryNamelike '^[谷,米]类/麦片' select * from Categories where CategoryName like
'_类/麦片' select * from Categories where CategoryName like '__类/麦片' select * from
Categorieswhere CategoryName like '%/麦片' select * from Categories where
CategoryNamelike '谷类/%' 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<'1997-01-01' select * from Orders where
OrderDate between'1996-01-01' and '1996-12-31 23:59:59'
分组查询
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 between3 and 5 select * from #TempTab1 drop table #TempTab1
子查询
select * from Products where SupplierID in(select SupplierID from Suppliers
where City='上海') select Tab1.CompanyName from (select * from Suppliers where
City='上海') as Tab1 select 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
.CategoryID select 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,CategoryNamefrom Categories where CategoryID>4 --将两个或两个以上的查询结果合并
逻辑查询case
select LastName+FirstName as 姓名,TitleOfCourtesy as 称谓 from Employees select
LastName+FirstName 姓名,case Gender when 0 then '女' when 1 then '男' end as 性别 from
Employeesselect LastName+FirstName 姓名,case TitleOfCourtesy when '女士' then '女孩'
when '先生' then '男孩' else '未知' end as 称谓 from Employees
select 与 print
print 123 select 123 select 123 as Result select 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) end else print '不存在 '+@name--可用select if(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=1 while 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.CategoryID select * from
Categories_Products --查询视图exec sp_helptext Categories_Products --查询视图的创建语句 exec
sp_help Categories_Products --查看视图结构create view Categories_Products with
encryptionas 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 --删除视图
用视图修改数据表的数据

* 若视图字段来自表达式或常量，则只能进行delete操作；
* 若视图字段来自集合函数，则不允许修改操作；
* 若视图定义中含group by子句，则不允许修改操作；
* 若视图定义中含有distinct短语，则不允许修改操作；
* 在一个不允许修改操作视图上定义的视图，不允许修改操作。 update Categories_Products set ProductName='牛奶2'
where ProductID=2 update Categories_Products set ProductName='牛奶' where
ProductID=2
修改与删除数据

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

• « 上一篇：最好用的lua编辑器--------emmylua使用汇总
• » 下一篇：音视频学习笔记-OpenGL概念理解