SQL数据库代码

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'上海'

end

from stock

--------------插入一条出库记录，并显示出库表-------------------------------------- create procedure pro2

as

insert into out_stock values('20110003','m009',12,'2013-1-1','工程部')

select*from out_stock

execute pro2

-------------------------------------------------------

-----通过参数，插入一条出库记录，并显示出库表

create procedure pro3

--形参

@pnum char(8),@mnum char(8),@amt int,@gdate datetime,@dept varchar(100) as

insert into out_stock values(@pnum,@mnum,@amt,@gdate,@dept)

select*from out_stock

--调用时——需要给形参赋值——实参

execute pro3 '20110003','m007',17,'2013-1-1','工程部'

execute pro3 '20110003','m008',18,'2013-1-1','工程部'

--通过参数，插入一条出库记录，并显示出库表

create procedure pro3

--形参

@pnum char(8),@mnum char(8),@amt int,@gdate datetime,@dept varchar(100) as

insert into out_stock values(@pnum,@mnum,@amt,@gdate,@dept)

select*from out_stock

--调用时——需要给形参赋值——实参

execute pro3 '20110003','m007',17,'2013-1-1','工程部'

execute pro3 '20110003','m008',18,'2013-1-1','工程部'

----------------------------------------------------------

select*from stock

create procedure pro4

@house varchar(50)='供电局#仓库',@amt int=100

as

select*from stock where warehouse=@house and amount<@amt

exec pro4

exec pro4 '供电局#仓库'

exec pro4 @amt=50

--------------------------------------------

--获得-返回值——指定库房的库存总量

create procedure pro5

@house varchar(50),@sum int output

as

select @sum=sum(amount)from stock where warehouse=@house

--执行具有输出参数的过程

declare @s int

exec pro5 '供电局#仓库',@s output

print @s

---------------------------------触发器-------------------

---------创建insert触发器，显示插入的行数；

create trigger tri_stock1

on stock

for insert

as

begin

print'插入了：'+cast(@@rowcount as varchar(10))+'行'

select*from inserted

end

insert into stock (mat_num,mat_name,speci)values('m012','变压器','20kv')

select*from stock

drop trigger tri_stock1

--------创建触发器，要求检验插入记录的库存量不能小于，否则撤销

create trigger tri_stock2

on stock

for insert

as

begin

declare @amt int

select @amt = amount from inserted--从临时表获得插入的数据

if @amt<1

begin

--更新操作，系统会针对当前命令，自动加载事务

rollback transaction--回滚撤销

print'库存量不足，插入操作中断！'

end

else

print'插入了：'+cast(@@rowcount as varchar(10))+'行'

end

select*from stock

insert into stock values('m014','转换器','dd-2','供电局#仓库',7,45)

---------------------------------------------------------------------

--------建立delete触发器，删除stock物资，需要将出库表out_stock中的相关物资一并删除--------－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

create trigger tri_stock3

on stock

for delete

as

begin

begin transaction--当前删除触发器又包括了一条，delete，将两条delete组成事务

---删除stock记录时，同时删除out_stock表中，物资编号相同的记录

declare @mnum char(8)

select @mnum=mat_num from deleted

delete from out_stock where mat_num=@mnum

commit transaction

end

----------------------------------------

-----------------创建update触发器，当修改库存量为小于的值时，中断操作

create trigger tri_stock4

on stock

for update

as

begin

declare @amt int

select @amt=amount from inserted

if @amt<1

begin

rollback transaction

raiserror('库存过低，撤销操作！！！！',12,3)

end

end

---------windows验证打开的查询---------------------------

---------1.创建登录

----create login 登录名[with password=密码|from windows]

----创建登录：tlog1

-----------只能完成服务器连接，无法访问具体数据库

create login tlog1 with password='123'

---------default_database=数据库名

---------登录后默认访问的数据库

create login tlog2 with password='123',default_database=el_proj

--------登录出错：该登录tlog2,不能访问el_proj——因为没有用户

----------------------------------------------------------------------

----------2.创建用户

--------------格式：create user 用户名from login 登录名

----------------通常，用户来源于登录

use el_proj

create user tuser2 from login tlog2

-----注意：在创建用户时，需要指定当前数据库——在指定数据库中创建用户

-----如果登录具有指定数据库的用户，可以访问该数据库，但是，无法观察到任何对象

-----------------------------------------------------------------

---------3.授权

-----------------新创建的用户，没有任何授权。

------授权：给指定的用户，操作数据对象的能力——数据库的操作命令（create，insert。。。。）------格式：

/*

grant 权限——授予指定的操作能力——insert，select。。。。

on 表名|视图————针对哪个数据库对象授权

to 账号（用户、角色）————授权给谁

with grant option————允许分发授权*/

---授予tuser2 查询stock表的权限

grant select on stock to tuser2

grant select on out_stock to tuser2

---授予tuser2 查询salvaging表的权限，同时允许分发

grant select on salvaging to tuser2 with grant option

create login tlog3 with password='123',default_database=el_proj

create user tuser3 from login tlog3

--------------------- 建立索引

-------------------------------------------------------

create clustered index index1 on stock(warehouse)

--出错：stock表已经拥有聚集索引————主键

---查看索引：

sp_helpindex stock

create nonclustered index index1 on stock(warehouse)

drop index stock.index1