精品医药销售系统sql语句.docx
《精品医药销售系统sql语句.docx》由会员分享,可在线阅读,更多相关《精品医药销售系统sql语句.docx(19页珍藏版)》请在冰点文库上搜索。
精品医药销售系统sql语句
医药销售管理系统SQL语句
createdatabaseMedicalManagerSystem/*创建医药销售管理系统*/
useMedicalManagerSystem
createtableMedID/*创建药品类别索引信息*/
(MedKindeCodechar(10)constraintMI_PRIPRIMARYKEY,
KindExplanationvarchar(12)NOTNULL)
createtableMedInfor/*创建药品信息表*/
(MedicineCodechar(6)constraintM_PRIMPRIMARYKEY,
MedicineNamevarchar(8)NOTNULL,
MedKindeCodechar(10)FOREIGNKEYREFERENCESMedID(MedKindeCode),
PriceMoney,
ListPriceMoney,
NumberInt,
FirmCodechar(10)FOREIGNKEYREFERENCESFirmInfor(FirmCode),
UserfulllifeDatetime)
createtableGueInfor/*创建客户信息表*/
(GuestCodechar(10)constraintG_PRIMPRIMARYKEY,
GuestNamevarchar(16)NOTNULl,
GLinkvarchar(12),
GLinkTellvarchar(11),
Cityvarchar(8))
createtableFirmInfor/*创建供应商信息表*/
(FirmCodechar(10)constraintF_PRIMPRIMARYKEY,
FirmNamevarchar(16)NOTNULL,
Linkvarchar(12),
LinkTellvarchar(11),
Cityvarchar(8))
createtableWorkInfor/*创建员工信息表*/
(WorkNochar(10)constraintW_PRIMPRIMARYKEY,
Namevarchar(12),
UserRegNamechar(6)NOTNULL,
Passwordchar(10)NOTNULL,
Positionchar(10),
PowerInt)
createtablesellMain/*创建医药销售主表*/
(SaleNointconstraintSM_PRIMPRIMARYKEY,
WorkNochar(10)FOREIGNKEYREFERENCESWorkInfor(WorkNo),
SaleDateDateTime,
AmountMoney)
createtablesellChild/*创建医药销售子表*/
(SaleNointconstraintSC_PRIMPRIMARYKEY,
MedicineCodechar(6)FOREIGNKEYREFERENCESMedInfor(MedicineCode),
MedicineNamevarchar(32)NOTNULL,
PriceMoney,
NumberInt,
Uintchar(8),
AmountMoney)
/*插入数据的存储过程*/
createprocMedID_proc
@MedKindeCodechar(10),@KindExplanationvarchar(12)
as
insertintoMedID(MedKindeCode,KindExplanation)values(@MedKindeCode,@KindExplanation)
execMedID_proc'0001','口腔溃疡'
execMedID_proc'0002','感冒'
execMedID_proc'0003','发烧'
execMedID_proc'0004','拉肚子'
execMedID_proc'0005','外伤'
createprocMedInfor_proc
@MedicineCodechar(6),@MedicineNamevarchar(8),@MedKindeCodechar(10),@Pricemoney,@ListPricemoney,
@Numberint,@FirmCodechar(10),@UserfulllifeDatetime
as
insertintoMedInfor(MedicineCode,MedicineName,MedKindeCode,Price,ListPrice,
Number,Supplicer,Userfulllife)values(@MedicineCode,@MedicineName,@MedKindeCode,@Price,@ListPrice,
@Number,@FirmCode,@Userfulllife)
execMedInfor_proc'1001','板蓝根','0002',5,3,'100','014','2010-12-5'
execMedInfor_proc'2002','四季感康','0002',14,10.5,'150','051','2010-12-12'
execMedInfor_proc'2003','银黄颗粒','0002',12,8.8,'120','014','2012-10-6'
execMedInfor_proc'2004','感冒清热软胶囊','0002',17,12,'150','015','2011-11-1'
execMedInfor_proc'3001','阿斯匹林','0003',15,11,'100','014','2010-12-1'
execMedInfor_proc'3002','布洛芬','0003',21,17.5,'120','051','2010-6-5'
execMedInfor_proc'4001','泻利挺','0004',25,20,'120','015','2012-10-2'
execMedInfor_proc'4002','诺氟沙星胶囊','0004',15,12,'100','015','2012-9-16'
execMedInfor_proc'5001','碘酒','0005',5,2.5,'50','051','2012-10-12'
execMedInfor_proc'5002','创口贴','0005',2,1,'250','014','2015-5-1'
createprocGueInfor_proc
@GuestCodechar(10),@GuestNamevarchar(16),@GLinkvarchar(12),@GLinkTellvarchar(11),
@Cityvarchar(8)
as
insertintoGueInfor(GuestCode,GuestName,GLink,GLinkTell,
City)values(@GuestCode,@GuestName,@GLink,@GLinkTell,@City)
execGueInfor_proc'015112','zhangsan','xiaozhang','668401','jiaxing'
execGueInfor_proc'065114','lisi','xiaofang','614425','yuyao'
execGueInfor_proc'052114','wangwu','xiaowu','659024','wenzhou'
execGueInfor_proc'043115','zhaoliu','xiaowu','615874','shangyu'
execGueInfor_proc'014221','awu','xiaozhang','651283','linan'
execGueInfor_proc'025471','asha','xiaofang','691472','dongyang'
createprocFirmInfor_proc
@FirmCodechar(10),@FirmNamevarchar(16),@Linkvarchar(12),@LinkTellvarchar(11),@Cityvarchar(8)
as
insertintoFirmInfor(FirmCode,FirmName,Link,LinkTell,City)
values(@FirmCode,@FirmName,@Link,@LinkTell,@City)
execFirmInfor_proc'015','yangshengtang','xiaotai','681472','huzhou'
execFirmInfor_proc'014','baozhilin','zhangqing','658421','deqing'
execFirmInfor_proc'051','pinmingdayaofang','oudan','65417','xiangshan'
createprocWorkInfor_proc
@WorkNochar(10),@Namevarchar(12),@UserRegNamechar(6),@Passwordchar(10),@Positionchar(10),@PowerInt
as
insertintoWorkInfor(WorkNo,Name,UserRegName,Password,Position,Power)
values(@WorkNo,@Name,@UserRegName,@Password,@Position,@Power)
execWorkInfor_proc'075101','ZKL','zkl01','456789','jingli',''
execWorkInfor_proc'075201','ZJM','zjm01','123789','dongshi',''
execWorkInfor_proc'075215','WMX','wmx05','147258','xiaomi',''
execWorkInfor_proc'075120','ZZW','zzm20','123456','buzhang',''
createprocsellMain_proc
@SaleNoint,@WorkNochar(10),@SaleDateDateTime,@AmountMoney
as
insertintosellMain(SaleNo,WorkNo,SaleDate,Amount)
values(@SaleNo,@WorkNo,@SaleDate,@Amount)
execsellMain_proc'12','075101','2009-1-1',1000
execsellMain_proc'13','075201','2009-1-1',1500
execsellMain_proc'15','075215','2009-1-1',800
execsellMain_proc'20','075120','2009-1-1',1200
alterprocsellChild_proc
@SaleNoint,@MedicineCodechar(6),@MedicineNamevarchar(32),@PriceMoney,@NumberInt,@Uintchar(8),@AmountMoney
as
insertintosellChild(SaleNo,MedicineCode,MedicineName,Price,Number,Uint,Amount)
values(@SaleNo,@MedicineCode,@MedicineName,@Price,@Number,@Uint,@Amount)
execsellChild_proc'13','1001','板蓝根',5,'20','bao',100
execsellChild_proc'15','2002','四季感康',14,'15','he',210
execsellChild_proc'20','3001','阿斯匹林',15,'20','he',300
/*删除数据的存储过程*/
createprocMedID_delete_proc
@MedKindeCodechar(10)
as
deletefromMedId
whereMedKindeCode=@MedKindeCode
execMedID_delete_proc'0002'
createprocMedInfor_delete_proc
@MedicineNamevarchar(8)
as
deletefromMedInfor
whereMedicineName=@MedicineName
createprocGueInfor_delete_proc
@GuestCodechar(10)
as
deletefromGueInfor
whereGuestCode=@GuestCode
createprocFirmInfor_delete_proc
@FirmCodechar(10)
as
deletefromFirmInfor
whereFirmCode=@FirmCode
createprocWorkInfor_delete_proc
@WorkNochar(10)
as
deletefromWorkInfor
whereWorkNo=@WorkNo
createprocsellMain_delete_proc
@SaleNoint
as
deletefromsellMain
whereSaleNo=@SaleNo
createprocsellChild_delete_proc
@SaleNoint
as
deletefromsellChild
whereSaleNo=@SaleNo
/*修改数据的存储过程*/
createprocMedID_update_proc
@MedKindeCodechar(10),@KindExplanationvarchar(12),@MedKindeCode1char(10)
as
updateMedID
setMedKindeCode=@MedKindeCode,KindExplanation=@KindExplanation
whereMedKindeCode=@MedKindeCode1
execMedID_update_proc'0002','感冒','0001'
createprocMedInfor_update_proc
@MedicineCode1char(6),@MedicineNamevarchar(8),@MedKindeCodechar(10),@Pricemoney,@ListPricemoney,
@Numberint,@FirmCodechar(10),@UserfulllifeDatetime,@MedicineCodechar(6)
as
updateMedInfor
setMedicineCode=@MedicineCode1,MedicineName=@MedicineName,MedKindeCode=@MedKindeCode,Price=@Price,
ListPrice=@ListPrice,Number=@Number,FirmCode=@FirmCode,Userfulllife=@Userfulllife,MedicineCode=@MedicineCode
whereMedKindeCode=@MedKindeCode
createprocGueInfor_update_proc
@GuestCode1char(10),@GuestNamevarchar(16),@GLinkvarchar(12),@GLinkTellvarchar(11),
@Cityvarchar(8),@GuestCodechar(10)
as
updateGueInfor
setGuestCode=@GuestCode1,GuestName=@GuestName,GLink=@GLink,GLinkTell=@GLinkTell,
City=@City
whereGuestCode=@GuestCode
createprocFirmInfor_update_proc
@FirmCode1char(10),@FirmNamevarchar(16),@Linkvarchar(12),@LinkTellvarchar(11),@Cityvarchar(8),
@FirmCodechar(10)
as
updateFirmInfor
setFirmCode=@FirmCode1,FirmName=@FirmName,Link=@Link,LinkTell=@LinkTell,City=@City
whereFirmCode=@FirmCode
createprocWorkInfor_update_proc
@WorkNo1char(10),@Namevarchar(12),@UserRegNamechar(6),@Passwordchar(10),@Positionchar(10),@PowerInt,
@WorkNochar(10)
as
updateWorkInfor
setWorkNo=@WorkNo1,Name=@Name,UserRegName=@UserRegName,Password=@Password,Position=@Position,Power=@Power
whereWorkNo=@WorkNo
createprocsellMain_update_proc
@SaleNo1int,@WorkNochar(10),@SaleDateDateTime,@AmountMoney,@SaleNoint
as
updatesellMain
setSaleNo=@SaleNo1,WorkNo=@WorkNo,SaleDate=@SaleDate,Amount=@Amount
whereSaleNo=@SaleNo
createprocsellChild_update_proc
@SaleNo1int,@MedicineCodechar(6),@MedicineNamevarchar(32),@PriceMoney,@NumberInt,@Uintchar(8),@AmountMoney,
@SaleNoint
as
updatesellChild
setSaleNo=@SaleNo1,MedicineCode=@MedicineCode,MedicineName=@MedicineName,Price=@Price,Number=@Number,Amount=@Amount
whereSaleNo=@SaleNo
/*建立存储过程实现单表查询*/
/*建立名为“单表查询1”的存储过程,用来查询某种药品的信息*/
createproc单表查询1
@MedicineCodechar(6)
AS
select*
fromMedInfor
whereMedicineCode=@MedicineCode
/*建立名为“单表查询2”的存储过程,用来查询某个客户的信息*/
createproc单表查询2
@GuestCodechar(10)
AS
select*
fromGueInfor
whereGuestCode=@GuestCode
/*建立名为“单表查询3”的存储过程,用来查询某个员工的信息*/
createproc单表查询3
@WorkNochar(10)
AS
select*
fromWorkInfor
whereWorkNo=@WorkNo
/*建立名为“单表查询4”的存储过程,用来查询某个供应商的信息*/
createproc单表查询4
@FirmCodechar(10)
AS
select*
fromFirmInfor
whereFirmCode=@FirmCode
/*建立名为“单表查询5”的存储过程,用来查询某个药品代码对应的药品类型的信息*/
createproc单表查询5
@MedKindeCodechar(10)
AS
select*
fromMedID
whereMedKindeCode=@MedKindeCode
/*建立存储过程实现连接查询*/
/*建立名为“连接查询1”的存储过程,用来查询某个药品名称对应的药品类型的信息*/
createproc连接查询1
@MedicineNamevarchar(8)
as
selectMedicineName,KindExplanation
fromMedInfor,MedID
whereMedID.MedKindeCode=MedInfor.MedKindeCodeand
MedicineName=@MedicineName
/*建立名为“连接查询2”的存储过程,用来查询某个供应商提供的药品类型*/
createproc连接查询2
@FirmNamevarchar(16)
as
selectFirmName,KindExplanation
fromMedInfor,MedID,FirmInfor
whereMedID.MedKindeCode=MedInfor.MedKindeCodeand
MedInfor.FirmCode=FirmInfor.FirmCodeand
FirmName=@FirmName
/*建立名为“连接查询3”的存储过程,用来查询某个销售员销售某种药品的数量*/
createproc连接查询3
@Namevarchar(12),@MedicineNamevarchar(8)
as
selectname,MedInfor.MedicineName,sellChild.Number
fromWo