SQL数据库代码
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
select*from stock
select*from salvaging
select*from out_stock
select*from stock
select warehouse,mat_name,amount from stock//搜索列warehouse,mat_name,amount select warehouse,mat_name,amount as数量from stock //搜索列warehouse,mat_name,amount并改amount名为数量
select distinct warehouse from stock //取消重复的warehouse
select*from stock where warehouse='供电局1#仓库' //搜索stock表中warehouse为1#仓库select*from stock where speci='bvv-120' //搜索stock表中speci为bvv-120的项
select*from stock where amount<100 //搜索stock表中amount<100的项
select*from stock where amount>45 //搜索stock表中amount>45的项
select*from stock where amount>45 and amount<100 //搜索stock表中大于45小于100的项select*from salvaging where start_date>'2011/1/1' //搜索表中开始日期大于2011、1、1 select*from stock where amount between 45 and 100 //搜索表中45到100之间的项
select*from stock where amount not between 45 and 100 //搜索表中非45到100之间的项select*from stock where amount <45 or amount>100 //搜索stock表中小于45大于100的项select*from stock where warehouse='供电局1#仓库'or warehouse='供电局2#仓库' //1#和2# select*from stock where warehouse in('供电局#仓库','供电局#仓库') //1#和2#
select*from stock where speci like'b%' //搜索b开头的
select*from stock where mat_name like'%绝缘%' //搜索字符串中有绝缘二字的
select*from stock where mat_name not like'%绝缘%'
select*from stock WHERE amount =null //搜索空
select*from stock WHERE amount is null
select*from stock WHERE amount is not null //搜索不空
select*from stock order by amount //amount默认升序排列
select*from stock order by amount desc //amount降序排列
select top 3 *from stock order by amount desc /amount降序排列前三个
select top 30 percent*from stock order by amount desc /表中行数30%amount降序排列
-------------m009低于，报警“库存不足”
declare @cnt int
select @cnt=amount from stock where mat_num='m009'
if @cnt<50
begin
print'库存不足'
select*from stock where mat_num='m009'
end
else
print @cnt
--while 循环实现显式1~10
declare @a int
set @a=1
while @a<11
begin
print @a
set @a=@a+1
if @a>7
break
End
--while 循环实现显式1~10——goto
declare @a int
set @a=1
ed:print'end'
while @a<11
begin
print @a
set @a=@a+1
if @a>7
goto ed
End
--------------延迟查询时间----------------
select*from stock
waitfor delay '00:00:03'
select*from stock
waitfor time'15:12:43'
-------------当。。然后。。-----------------------
select*,
case warehouse
when'供电局#仓库'then'北京'
when'供电局#仓库'then'北京'
when'供电局#仓库'then'上海'
when'供电局#仓库'then'上海'
end
from stock
-----------------同上-------------------
select*,
case
when warehouse='供电局#仓库'or warehouse='供电局#仓库'then'北京'
when warehouse='供电局#仓库'or warehouse='供电局#仓库'then'上海'