医药销售管理系统sql语句.doc
《医药销售管理系统sql语句.doc》由会员分享,可在线阅读,更多相关《医药销售管理系统sql语句.doc(11页珍藏版)》请在冰豆网上搜索。
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),@SaleDat