operate.sql

课程设计了

use csmag
go

/*=====================================员工部分==================================*/

/*找出员工信息*/
SELECT WorkersList.WorkersListId, 
WorkKindList.WorkKinds,
 WorkersList.WorkerName, 
WorkersList.WorkerPwd, WorkersList.Salary, 
WorkersList.WorkerPhone,
 WorkersList.WorkerAddress,
 WorkersList.WorkerMoreInfo,
 WorkKindList.WorkKindListId 
FROM WorkersList 
INNER JOIN WorkKindList ON WorkersList.WorkKindListId = WorkKindList.WorkKindListId

select * from WorkersList where WorkerName='邓秋'
/*插入员工信息*/
insert into WorkersList
 (WorkersList.WorkersListId,
WorkersList.WorkKindListId,
 WorkersList.WorkerName, 
WorkersList.WorkerPwd, 
WorkersList.Salary, 
WorkersList.WorkerPhone,
 WorkersList.WorkerAddress,
 WorkersList.WorkerMoreInfo
 )
values 
(3,2,'邓秋','123456789','6000','456789123456','北京东燕郊206','负责系统管理最高权限')


/*插入工作类型*/
insert into WorkKindLis
(WorkKindName,WorkMoreInfo)
values
('系统管理员','负责最高权限的系统管理')

insert into WorkKindLis
(WorkKindName,WorkMoreInfo)
values
('仓库管理员','负责仓库进出管理')


/*更新员工信息*/
update  WorkersList
set 


WorkersList.WorkKindListId=2,
 WorkersList.WorkerName='张', 
WorkersList.WorkerPwd='123456', 
WorkersList.Salary='3000', 
WorkersList.WorkerPhone='123456789',
 WorkersList.WorkerAddress='河北廊坊',
 WorkersList.WorkerMoreInfo='仓库管理张先生'
where

WorkersList.WorkersListId=2


 
/*删除员工*/

delete  from 
WorkersList 
where WorkersListId=3

/*======================================仓库部分===========================*/
/*===列出每个库房的货物信息*/
/*表套嵌显示，每个仓库分别显示，用仓库做参数*/
SELECT dbo.ProducesKind.ProducesKinds, dbo.Produces.ProducesName, 
  
      dbo.Produces.ProducesGG, dbo.Produces.ProducesCompany, 
      dbo.Produces.ProducesMoreInfo
FROM dbo.Produces CROSS JOIN
    
      dbo.ProducesKind
WHERE (dbo.Produces.ProducesId IN
          (SELECT ProducesId
         FROM SupplyList
         WHERE CkId IN
                   (SELECT CkId
                  FROM Cklist
where CkId=1001
)))


/*最终使用过程*/


/*插入库房资料*/

INSERT INTO 
[Cklist] 
([CkName], [WorkKindListId], [CkPhone], [CkAddress], [CkMoreInfo]) 
VALUES ('饮料库','2','4567891258','河北廊坊','在河北廊坊有库，存放烟酒类物资')





/*==================================客户管理=====================*/
/*================================帐目管理================*/
/*==============================每日报帐=============*/
/*============================商品管理==================*/

/*==========================销售管理================*/

use csmag
go

update
Cklist
set
Cklist.CkName='饮料',
Cklist.WorkersListId='10001',
Cklist.CkPhone='4564654',
Cklist.CkAddress='fsfefefef',
Cklist.CkMoreInfo='gdgfdgfdgdsfges'
where 
Cklist.CkId=1002

select * from Cklist
use csmag
go
select WorkersList.WorkersListId,WorkersList.WorkerName from WorkersList,WorkKindList where WorkersList.WorkKindListId=WorkKindList.WorkKIndListId and WorkKindList.WorkKinds ='管理员'\



select * from Produces,ProducesKind where Produces.ProducesId like '%100001%' 
select * from Produces,ProducesKind  where Produces.ProducesKindId=ProducesKind.ProducesKindId 
select * from Produces

update Produces set Produces.ProducesId='" + ((TextBox)(GridView1.Rows[e.RowIndex].Cells[0].Controls[0])).Text.ToString().Trim() + "',Produces.producesKindId='" + produceskind.SelectedValue.ToString() + "', Produces.ProducesName='" + ((TextBox)(GridView1.Rows[e.RowIndex].Cells[1].Controls[0])).Text.ToString().Trim() + "',Produceslist.ProducesPhone='" + ((TextBox)(GridView1.Rows[e.RowIndex].Cells[3].Controls[0])).Text.ToString().Trim() + "',Produceslist.ProducesAddress='" + ((TextBox)(GridView1.Rows[e.RowIndex].Cells[4].Controls[0])).Text.ToString().Trim() + "',Produces.ProducesMoreInfo='" + ((TextBox)(GridView1.Rows[e.RowIndex].Cells[5].Controls[0])).Text.ToString().Trim() + "' where Produces.ProducesId='" + GridView1.DataKeys[e.RowIndex].Value.ToString() + "'


UPDATE [Produces] SET [ProducesId]='" + ((TextBox)(GridView1.Rows[e.RowIndex].Cells[0].Controls[0])).Text.ToString().Trim() + "',[ProducesKindId] ='" + produceskind.SelectedValue.ToString() + "', [ProducesName] = '" + ((TextBox)(GridView1.Rows[e.RowIndex].Cells[2].Controls[0])).Text.ToString().Trim() + "', [ProducesGG] = '" + ((TextBox)(GridView1.Rows[e.RowIndex].Cells[3].Controls[0])).Text.ToString().Trim() + "', [ProducesJinJia] = '" + ((TextBox)(GridView1.Rows[e.RowIndex].Cells[4].Controls[0])).Text.ToString().Trim() + "', [Discount] ='" + ((TextBox)(GridView1.Rows[e.RowIndex].Cells[5].Controls[0])).Text.ToString().Trim() + "', [ProducesPrices] = '" + ((TextBox)(GridView1.Rows[e.RowIndex].Cells[6].Controls[0])).Text.ToString().Trim() + "', [ProducesCompany] = '" + ((TextBox)(GridView1.Rows[e.RowIndex].Cells[7].Controls[0])).Text.ToString().Trim() + "', [ProducesMoreInfo] = '" + ((TextBox)(GridView1.Rows[e.RowIndex].Cells[8].Controls[0])).Text.ToString().Trim() + "' WHERE [ProducesId] = '" + GridView1.DataKeys[e.RowIndex].Value.ToString() + "'




SELECT  Cklist.CkName, SupplyList.ListId, ProducesKind.ProducesKinds, Produces.ProducesName, SupplyList.NeedNum, SupplyList.ListDateTime, SupplyList.AffordMoney, 
               SupplyList.TruelyAfford, WorkersList.WorkerName, SupplyList.ListMoreInfo, SupplyCompany.SupplyCompanyName
FROM     ProducesKind INNER JOIN
               Produces ON ProducesKind.ProducesKindId = Produces.ProducesKindId INNER JOIN
               SupplyList ON Produces.ProducesId = SupplyList.ProducesId INNER JOIN
               Cklist ON SupplyList.CkId = Cklist.CkId INNER JOIN
               SupplyCompany ON SupplyList.SupplyId = SupplyCompany.SupplyId INNER JOIN
               WorkersList ON SupplyList.WorkersListId = WorkersList.WorkersListId AND Cklist.WorkersListId = WorkersList.WorkersListId


INSERT INTO 
[SupplyList] 
([CkId], [SupplyId], [ProducesId], [NeedNum], 
[ListDateTime], [AffordMoney], [TruelyAfford], [WorkersListId], 
[ListMoreInfo]) 

VALUES ('','1001', @SupplyId,
 @ProducesId, @NeedNum, @ListDateTime,
 @AffordMoney, @TruelyAfford, @WorkersListId,
@ListMoreInfo)

select * from Cklist
select * from SupplyCompany

select * from CustomsKind

select sum(NeedNum) from SupplyList where ProducesId='100003'
select * from Produces

select sum(SellNum) as sellnumshow from SellList where ProducesId='100001'
select sum(NeedNum) as totalnum from SupplyList where ProducesId='100002'
select * from SupplyList
select * from SellList
INSERT INTO
 [WorkersList] 
([WorkersListId], [WorkKindListId],
 [WorkerName], [WorkerPwd], [Salary], 
[WorkerPhone], [WorkerAddress], 
[WorkerMoreInfo]) 
VALUES 
('10002', 2,
 '李死','45+6123' , '2000', 
@WorkerPhone, @WorkerAddress, 
@WorkerMoreInfo)"

select * from WorkersList
select * from WorkKindList
insert into WorkKindList values('仓库管理人员','负责仓库的管理和调配')

/*"UPDATE 
[WorkersList] 
SET 
[WorkKindListId] = " + WorkKind.SelectedValue.ToString() + ", 
[WorkerName] = " + ((TextBox)(GridView2.Rows[e.RowIndex].Cells[2].Controls[0])).Text.ToString().Trim() + ",
 [WorkerPwd] = " + ((TextBox)(GridView2.Rows[e.RowIndex].Cells[3].Controls[0])).Text.ToString().Trim() + ",
 [Salary] =" + ((TextBox)(GridView2.Rows[e.RowIndex].Cells[4].Controls[0])).Text.ToString().Trim() + ",
 [WorkerPhone] = " + ((TextBox)(GridView2.Rows[e.RowIndex].Cells[5].Controls[0])).Text.ToString().Trim() + ", 
[WorkerAddress] = " + ((TextBox)(GridView2.Rows[e.RowIndex].Cells[6].Controls[0])).Text.ToString().Trim() + ", 
[WorkerMoreInfo] =" + ((TextBox)(GridView2.Rows[e.RowIndex].Cells[7].Controls[0])).Text.ToString().Trim() + "
WHERE [WorkersListId] = " + GridView2.DataKeys[(int)e.RowIndex].Value.ToString()+"'";

" DELETE FROM [WorkersList] WHERE [WorkersListId] = " + GridView1.DataKeys[(int)e.RowIndex].Value.ToString();

*/
select * from Produces
select * from SellList
insert into SellList (ProducesId,SellNum,SellDateTime) values('"+producesid+"','"+sellnum+"','"+selltime+"')
SELECT 
SellList.SellListId, 
ProducesKind.ProducesKinds, 
Produces.ProducesName, 
Produces.Discount,
Produces.ProducesPrices, 
 SellList.SellNum,
 Produces.ProducesId 
FROM SellList 
INNER JOIN Produces 
ON SellList.ProducesId = Produces.ProducesId 
 
INNER JOIN ProducesKind 
ON Produces.ProducesKindId = ProducesKind.ProducesKindId 

/*=======每日报帐============*/

/*SELECT SUM(dbo.SupplyList.NeedNum) AS totalnum
FROM dbo.SupplyList CROSS JOIN
      dbo.Produces
WHERE (dbo.SupplyList.ProducesId = 100002)*/

SELECT 
MoneyKind.MoneyKindS, 
MoneyMag.MoneyChang, 
WorkersList.WorkerName, 
MoneyMag.MoneyMagMoreInfo, 
MoneyMag.MoneyMagDate
FROM MoneyMag 
INNER JOIN MoneyKind ON 
MoneyMag.MoneyKindId = MoneyKind.MoneyKindId INNER JOIN WorkersList ON MoneyMag.WorkersListId = WorkersList.WorkersListId

SELECT MoneyKind.MoneyKindS,MoneyMag.JinChu, MoneyMag.MoneyChang, WorkersList.WorkerName, MoneyMag.MoneyMagMoreInfo, MoneyMag.MoneyMagDate FROM MoneyMag INNER JOIN MoneyKind ON MoneyMag.MoneyKindId = MoneyKind.MoneyKindId INNER JOIN WorkersList ON MoneyMag.WorkersListId = WorkersList.WorkersListId


SELECT * 
FROM SellLIst 
WHERE (GetTime() BETWEEN '2001-1-01' AND '2007-6-31')

SELECT * FROM SellLIst WHERE CONVERT(VARCHAR(20),SellDateTime,120)>='2002-01-01' AND CONVERT(VARCHAR(10),SellDateTime,120)<='2007-06-31' 

select *
from SellList 
where convert(varchar(20),cast(SellDateTime as datetime),112)='20060601' 

select * from SellList

SELECT SupplyCompany.SupplyCompanyName, SupplyList.ListId, Cklist.CkName, SupplyList.NeedNum, SupplyList.ListDateTime, SupplyList.AffordMoney, SupplyList.TruelyAfford, WorkersList.WorkerName, SupplyList.ListMoreInfo FROM SupplyCompany INNER JOIN SupplyList ON SupplyCompany.SupplyId = SupplyList.SupplyId INNER JOIN WorkersList ON SupplyList.WorkersListId = WorkersList.WorkersListId INNER JOIN Cklist ON SupplyList.CkId = Cklist.CkId AND WorkersList.WorkersListId = Cklist.WorkersListId WHERE (CONVERT (VARCHAR(20), ListDateTime, 120) >= '2006-05-06') AND (CONVERT (VARCHAR(10), ListDateTime, 120) <='2007-10-31')

SELECT Produces.ProducesName, Produces.ProducesName AS Expr2, WorkersList.WorkerName, SellList.SellListId, SellList.SellNum, SellList.SellDateTime FROM Produces INNER JOIN SellList ON Produces.ProducesId = SellList.ProducesId INNER JOIN WorkersList ON SellList.WorkersListId = WorkersList.WorkersListId WHERE (CONVERT (VARCHAR(20), SellDateTime, 120) >= '2006-05-06') AND (CONVERT (VARCHAR(10), SellDateTime, 120) <='2007-10-31')



SELECT 
*
FROM SupplyCompany
, SupplyList
, Cklist
, WorkersList 
 WHERE (CONVERT (VARCHAR(20), ListDateTime, 120) >= '2006-08-08')
 AND (CONVERT (VARCHAR(10), ListDateTime, 120) <='2007-07-31')

select *
from SellList,Produces,ProducesKind,WorkersList
 WHERE (CONVERT (VARCHAR(20), SellList.SellDateTime, 120) >= '2006-08-08')
 AND (CONVERT (VARCHAR(10), SellList.SellDateTime, 120) <='2007-07-31')
SELECT Produces.ProducesName, Produces.ProducesName AS Expr2, WorkersList.WorkerName, SellList.SellListId, SellList.SellNum, SellList.SellDateTime FROM Produces INNER JOIN SellList ON Produces.ProducesId = SellList.ProducesId INNER JOIN WorkersList ON SellList.WorkersListId = WorkersList.WorkersListId WHERE (CONVERT (VARCHAR(20), SellList.SellDateTime, 120) >= '2006-08-08')
 AND (CONVERT (VARCHAR(10), SellList.SellDateTime, 120) <='2007-07-31')


SELECT SupplyCompany.SupplyCompanyName, SupplyList.ListId, Cklist.CkName, SupplyList.NeedNum, SupplyList.ListDateTime, SupplyList.AffordMoney, SupplyList.TruelyAfford, WorkersList.WorkerName, SupplyList.ListMoreInfo FROM SupplyCompany INNER JOIN SupplyList ON SupplyCompany.SupplyId = SupplyList.SupplyId INNER JOIN WorkersList ON SupplyList.WorkersListId = WorkersList.WorkersListId INNER JOIN Cklist ON SupplyList.CkId = Cklist.CkId AND WorkersList.WorkersListId = Cklist.WorkersListId WHERE (CONVERT (VARCHAR(20), ListDateTime, 120) >='2006-07-08') AND (CONVERT (VARCHAR(10), ListDateTime, 120) <= '2007-06-31')

SELECT 
MoneyMag.MoneyMagId,
 WorkersList.WorkerName, 
MoneyKind.MoneyKindS, 
MoneyMag.MoneyChang, MoneyMag.JinChu,
 MoneyMag.MoneyMagMoreInfo, MoneyMag.MoneyMagDate
 FROM MoneyMag INNER JOIN MoneyKind 
ON MoneyMag.MoneyKindId = MoneyKind.MoneyKindId 
INNER JOIN WorkersList ON MoneyMag.WorkersListId = 
WorkersList.WorkersListId WHERE (CONVERT (VARCHAR(20), 
MoneyMag.MoneyMagDate, 120) >= '2006-08-08') AND (CONVERT (VARCHAR(10),
 MoneyMag.MoneyMagDate, 120) <= '2007-07-31') AND (MoneyMag.JinChu = '进款')

select sum(NeedNum) from SupplyList 
where
(CONVERT (VARCHAR(20), 
ListDateTime, 120) >= '2006-08-08') AND (CONVERT (VARCHAR(10),
ListDateTime, 120) <= '2007-07-31')

select * from MoneyMag



SELECT 
sum(AffordMoney) as ziduan 
FROM SupplyList 

where
(CONVERT (VARCHAR(20), 
ListDateTime, 120) >= '2006-08-08') AND (CONVERT (VARCHAR(10),
ListDateTime, 120) <= '2007-09-31')

SELECT 
sum(AffordMoney) 
FROM SupplyList 
where (CONVERT (VARCHAR(20), ListDateTime, 120) >= '2007-09-10 11:12:44')
 AND (CONVERT (VARCHAR(10),ListDateTime, 120) <= '2007-09-10 11:12:44')


           select ProducesJinJia from Produces where ProducesId='100002'

SELECT 
sum(SellNum) 
FROM SellList 
where (CONVERT (VARCHAR(20), SellDateTime, 120) >= '2006-08-08')
 AND (CONVERT (VARCHAR(10),SellDateTime, 120) <= '2007-09-31')

SELECT *
 FROM Produces,SellList,WorkersList
where 
SellList.WorkersListId = WorkersList.WorkersListId 
and Produces.ProducesId = SellList.ProducesId 
and(CONVERT (VARCHAR(20), SellDateTime, 120) >= '2006-07-09') 
AND (CONVERT (VARCHAR(10), SellDateTime, 120) <= '2007-09-31')


select * from Selllist

insert into supplylist
values
('','1001','1','10001','5','2007-09-04','40.00','100001','beuzhu 信息')

insert into Supplycompany
values
('','北京事业公司','张五','北京潮阳区','fjeijfewjejfwejfwj备注信息')

insert into ProducesKind
values
('','烟酒')

insert into moneymag
values
('','10001','进款','50.00','1','备注信息','2007-09-08')

insert into moneykind
values
('','银行存款')