赞
踩
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 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,CategoryName from Categories where CategoryID>4 --将两个或两个以上的查询结果合并
select LastName+FirstName as 姓名,TitleOfCourtesy as 称谓 from Employees
select LastName+FirstName 姓名,case Gender
when 0 then '女'
when 1 then '男'
end as 性别 from Employees
select LastName+FirstName 姓名,case TitleOfCourtesy
when '女士' then '女孩'
when '先生' then '男孩'
else '未知'
end as 称谓 from Employees
print 123
select 123
select 123 as Result
select 123 Result
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 delay '00:00:03'--等待3秒
select '11'
waitfor time '17:44:03'--等待到具体时间
select '22'
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())
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 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 --删除视图
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):速度快,无删除记录,不可恢复,不可删除有外键的表
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。