查看 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 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 --按多列排序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 OrderID desc,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 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 CategoryID
select top 3 * from Categories where CategoryID not in(select top (0*3) CategoryID from Categories order by CategoryID) order by CategoryID
select top 3 * from Categories where CategoryID not in(select top (1*3) CategoryID from Categories order by CategoryID) order by CategoryID
select 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 desc
select top 3 * from Categories where CategoryID not in(select top (1*3) CategoryID from Categories order by CategoryID desc) order by CategoryID desc
select 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 --指定列别名1
select CategoryID,CategoryName 种类名称 from Categories --指定列别名2
select 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=2
select * from Categories where CategoryID<>2
select * from Categories where CategoryID!=2
select * from Categories where CategoryID in(2,4,6)
select * from Categories where CategoryID not in(2,4,6)
select * from Categories where CategoryID>3
select * from Categories where 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 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<'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 between 3 and 5select * 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.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 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 --可用selectdeclare @id intselect @id=CategoryID from Categories where CategoryName=@nameprint 'ID: '+convert(varchar,@id)end
else 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=1
while 1=1beginif @i<10 beginprint @i set @i=@i+1endelse breakend

视图查询

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=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)：速度快，无删除记录，不可恢复，不可删除有外键的表