数据库建表及初始化

drop database if exists sqltest
create database sqltest
use sqltest;
/*建立抢险工程表*/
drop table if exists  saving ;
create table saving(
prj_id int primary key,
prj_name varchar(20) not null,
start_time datetime,
end_time datetime
)
/*修改表*/
alter table saving add prj_status bit; 

/*建立物料表*/
create table stock(
stock_id int primary key,
stock_name varchar(20) null,
stock_num int
)
alter table stock add speci varchar(20) not null;
alter table stock add warehouse varchar(20);
alter table stock alter column warehouse varchar(50) not null;
alter table stock add unit decimal(18,2);
alter table stock add total as ([stock_num]*unit);
/*物料支出表*/
create table out_stock(
prj_id int ,
stock_id int,
out_num int,
 primary key(prj_id,stock_id),
 foreign key(prj_id) references saving(prj_id),
 foreign key(stock_id) references stock(stock_id)
)
alter table out_stock add get_date datetime;
alter table out_stock add department varchar(100);
--插入数据
select * from saving;
insert into saving values(0001,'雅安地震','2013-5-20','2013-5-20','true');
insert into saving values(0002,'汶川地震','2011-4-20','2011-4-20','true');
insert into saving values(0003,'南方雪灾','2010-6-12','2010-6-13','false');
insert into saving values(0004,'商洛洪灾','2008-8-12','2008-9-10','false');

select * from stock;
insert into stock values(000001,'帐篷',3000,'v1','救灾1#',200);
insert into stock values(000002,'矿泉水',10000,'w1','救灾2#',1);
insert into stock values(000003,'毛巾','5000','p1','救灾1#',4);
insert into stock values(000004,'车量',3000,'b1','救灾2#',40000);


select * from out_stock;
insert into out_stock values(0001,000001,300,getdate(),'省级');
insert into out_stock values(0001,000002,1000,getdate(),'省级');
insert into out_stock values(0001,000003,500,getdate(),null);
insert into out_stock values(0001,000004,1000,getdate(),'省级');
insert into out_stock values(0002,000001,500,getdate(),'省级1部');
insert into out_stock values(0002,000002,2000,getdate(),'省级1部');
insert into out_stock values(0002,000003,700,getdate(),null);
insert into out_stock values(0002,000004,2000,getdate(),'省级2部');
insert into out_stock values(0003,000001,200,getdate(),'省级2部');
insert into out_stock values(0003,000002,900,getdate(),'省级2部');
insert into out_stock values(0003,000003,400,getdate(),null);
insert into out_stock values(0003,000004,1000,getdate(),'省级2部');

--查询操作
select stock_id,stock_name,speci from stock;
--datediff(day,start_time,end_time)函数
select prj_name,start_time,end_time,datediff(day,start_time,end_time) as '抢修天数' from saving;
select department from saving;
--distinct去重
select distinct department from out_stock;
select stock_name from stock where unit not between 0 and 80;
select * from stock where warehouse  like ('%1_')
--escape转为一般字符
select * from stock where warehouse like '救灾\_1号' escape '\';
select * from out_stock where department is null;
select stock_id,unit from stock order by unit desc;
--先在仓库降序，在按库存量升序
select * from stock order by warehouse desc,stock_num;
--top 2和top 50 percent
select  top 50 percent * from stock order by stock_num desc
select  count(distinct prj_id) from out_stock 
select max(out_num) as 最大值,min(out_num) as 最小值,avg(out_num) as 平均值 from out_stock where stock_id=000001
--查询每个抢险项目使用的物资种类.执行顺序：group by子句,,count(*) 函数，， having子句
 select prj_id ,count(*) 数 from out_stock group by prj_id having count(stock_id)>2
 select saving.* ,stock.* from saving,stock where saving.prj_id=stock.prj_id