医药销售管理系统SQL语句

1、个人收集整理 勿做商业用途create database MedicalManagerSystem/*创建医药销售管理系统 */use MedicalManagerSystemcreate table MedID/*创建药品类别索引信息 */(MedKindeCode char(10) constraint MI_PRI PRIMARY KEY, KindExplanation varchar(12) NOT NULL)create table MedInfor/*创建药品信息表 */(MedicineCode char(6) constraint M_PRIM PRIMARY KEY,Med

2、icineName varchar(8) NOT NULL,MedKindeCode char(10) FOREIGN KEY REFERENCES MedID(MedKindeCode), Price Money,ListPrice Money,Number Int,FirmCode char(10) FOREIGN KEY REFERENCES FirmInfor(FirmCode), Userfulllife Datetime)create table GueInfor/*创建客户信息表 */(GuestCode char(10) constraint G_PRIM PRIMARY KE

3、Y,GuestName varchar(16) NOT NULl,GLink varchar(12),GLinkTell varchar(11),City varchar(8)create table FirmInfor/*创建供应商信息表 */(FirmCode char(10) constraint F_PRIM PRIMARY KEY,FirmName varchar(16) NOT NULL,Link varchar(12),LinkTell varchar(11),City varchar(8)create table WorkInfor/*创建员工信息表 */(WorkNo cha

4、r(10) constraint W_PRIM PRIMARY KEY, Name varchar(12),UserRegName char(6) NOT NULL,Password char(10) NOT NULL,Position char(10),Power Int)create table sellMain/*创建医药销售主表 */(SaleNo int constraint SM_PRIM PRIMARY KEY,WorkNo char(10) FOREIGN KEY REFERENCES WorkInfor(WorkNo),1 / 11个人收集整理 勿做商业用途SaleDate

5、DateTime,Amount Money)create table sellChild/*创建医药销售子表 */exec MedInfor_proc 1001,exec MedInfor_proc 2002,exec MedInfor_proc 2003,exec MedInfor_proc 2004,感 冒 清 热 软 胶 囊 ,0002,17,12,150,015,(SaleNo int constraint SC_PRIM PRIMARY KEY,MedicineCode char(6) FOREIGN KEY REFERENCES MedInfor(MedicineCode), Me

6、dicineName varchar(32) NOT NULL,Price Money, Number Int,Uint char(8),Amount Money) /* 插入数据地存储过程 */create proc MedID_procMedKindeCode char(10),KindExplanation varchar(12) asinsert into MedID (MedKindeCode,KindExplanation) values(MedKindeCode ,KindExplanation )exec MedID_proc 0001,口腔溃疡 exec MedID_proc

7、 0002,感冒 exec MedID_proc 0003,发烧 exec MedID_proc 0004,拉肚子 exec MedID_proc 0005 ,外伤 create proc MedInfor_procMedicineCode char(6),MedicineName varchar(8),MedKindeCode char(10),Price money,ListPrice money,Number int,FirmCode char(10),Userfulllife Datetime asinsert into MedInfor(MedicineCode ,MedicineN

8、ame,MedKindeCode,Price,ListPrice, Number,Supplicer,Userfulllife) values(MedicineCode,MedicineName,MedKindeCode,Price,ListPrice, Number,FirmCode,Userfulllife)板蓝根 ,0002,5,3,100,014,2010-12-5 四季感康 ,0002,14,10.5,150,051,2010-12-12 银黄颗粒 ,0002,12,8.8, 120 ,014,2012-10-6exec MedInfor_proc 3001,exec MedInfo

9、r_proc 3002,exec MedInfor_proc 4001,2011-11-1阿斯匹林 ,0003,15,11,100,014,2010-12-1布洛芬 ,0003,21,17.5,120,051,2010-6-5泻利挺 ,0004,25,20,120,015,2012-10-22 / 11个人收集整理 勿做商业用途exec MedInfor_proc 4002,exec MedInfor_proc 5001,exec MedInfor_proc 5002,诺氟沙星胶囊 ,0004,15,12,100,015,2012-9-16碘酒 ,0005,5,2.5,50 ,051,2012

10、-10-12创口贴 ,0005,2,1,250,014,2015-5-1 create proc GueInfor_procGuestCode char(10),GuestName varchar(16),GLink varchar(12),GLinkTell varchar(11),City varchar(8)asinsert into GueInfor(GuestCode,GuestName,GLink,GLinkTell,City) values(GuestCode,GuestName,GLink,GLinkTell,City)exec GueInfor_proc 015112,zha

11、ngsan,xiaozhang,668401,jiaxingexec GueInfor_proc 065114,lisi,xiaofang,614425,yuyaoexec GueInfor_proc 052114,wangwu,xiaowu,659024,wenzhouexec GueInfor_proc 043115,zhaoliu,xiaowu,615874,shangyuexec GueInfor_proc 014221,awu,xiaozhang,651283,linanexec GueInfor_proc 025471,asha,xiaofang,691472,dongyangcr

12、eate proc FirmInfor_procFirmCode char(10),FirmName varchar(16),Link varchar(12),LinkTell varchar(11),City varchar(8)asinsert into FirmInfor(FirmCode,FirmName,Link,LinkTell,City) values(FirmCode,FirmName,Link,LinkTell,City) exec FirmInfor_proc 015,yangshengtang,xiaotai,681472,huzhouexec FirmInfor_pro

13、c 014,baozhilin,zhangqing,658421,deqingexec FirmInfor_proc 051,pinmingdayaofang,oudan,65417,xiangshancreate proc WorkInfor_procWorkNo char(10),Name varchar(12),UserRegName char(6),Password char(10),Position char(10),Power Intasinsert into WorkInfor(WorkNo,Name,UserRegName,Password,Position,Power) va

14、lues(WorkNo,Name,UserRegName,Password,Position,Power) exec WorkInfor_proc 075101,ZKL,zkl01,456789,jingli,exec WorkInfor_proc 075201,ZJM,zjm01,123789,dongshi,exec WorkInfor_proc 075215,WMX,wmx05,147258,xiaomi,exec WorkInfor_proc 075120,ZZW,zzm20,123456,buzhang, create proc sellMain_proc3 / 11个人收集整理 勿

15、做商业用途SaleNo int,WorkNo char(10),SaleDate DateTime,Amount Money asinsert into sellMain(SaleNo,WorkNo,SaleDate,Amount) values(SaleNo,WorkNo,SaleDate,Amount)exec sellMain_proc 12,075101,2009-1-1,1000exec sellMain_proc 13,075201,2009-1-1,1500exec sellMain_proc 15,075215,2009-1-1,800exec sellMain_proc 20

16、,075120,2009-1-1,1200 alter proc sellChild_procSaleNo int,MedicineCode char(6),MedicineName varchar(32),Price Money,Number Int,Uint char(8),Amount Moneyasinsert into sellChild(SaleNo,MedicineCode,MedicineName,Price,Number,Uint,Amount) values(SaleNo,MedicineCode,MedicineName,Price,Number,Uint,Amount)

17、板蓝根 ,5,20,bao,100 四季感康 ,14,15,he,210 阿斯匹林 ,15,20,he,300exec sellChild_proc 13,1001,exec sellChild_proc 15,2002,exec sellChild_proc 20,3001,/* 删除数据地存储过程 */ create proc MedID_delete_proc MedKindeCode char(10) as delete from MedIdwhere MedKindeCode=MedKindeCode exec MedID_delete_proc 0002 create proc M

18、edInfor_delete_procMedicineName varchar(8) asdelete from MedInforwhere MedicineName=MedicineName create proc GueInfor_delete_procGuestCode char(10)asdelete from GueInfor4 / 11个人收集整理 勿做商业用途where GuestCode=GuestCodecreate proc FirmInfor_delete_procFirmCode char(10)asdelete from FirmInforwhere FirmCode

19、=FirmCodecreate proc WorkInfor_delete_procWorkNo char(10)asdelete from WorkInforwhere WorkNo=WorkNocreate proc sellMain_delete_procSaleNo intasdelete from sellMainwhere SaleNo=SaleNocreate proc sellChild_delete_procSaleNo intasdelete from sellChildwhere SaleNo=SaleNo/* 修改数据地存储过程 */create proc MedID_

20、update_procMedKindeCode char(10),KindExplanation varchar(12),MedKindeCode1 char(10) asupdate MedIDset MedKindeCode=MedKindeCode,KindExplanation=KindExplanationwhere MedKindeCode=MedKindeCode1exec MedID_update_proc 0002, 感冒 ,0001create proc MedInfor_update_procMedicineCode1 char(6),MedicineName varch

21、ar(8),MedKindeCode char(10),Price money,ListPrice money,Number int,FirmCode char(10),Userfulllife Datetime,MedicineCode char(6) as5 / 11个人收集整理 勿做商业用途update MedInforsetMedicineCode=MedicineCode1,MedicineName=MedicineName,MedKindeCode=MedKindeCod e,Price=Price,ListPrice=ListPrice,Number=Number,FirmCod

22、e=FirmCode,Userfulllife=Userfulllif e,MedicineCode=MedicineCodewhere MedKindeCode=MedKindeCode create proc GueInfor_update_procGuestCode1 char(10),GuestName varchar(16),GLink varchar(12),GLinkTell varchar(11),City varchar(8),GuestCode char(10)asupdate GueInforsetGuestCode=GuestCode1,GuestName=GuestN

23、ame,GLink=GLink,GLinkTell=GLinkTell,City=Citywhere GuestCode=GuestCode create proc FirmInfor_update_procFirmCode1 char(10),FirmName varchar(16),Link varchar(12),LinkTell varchar(11),City varchar(8),FirmCode char(10)asupdate FirmInforsetFirmCode=FirmCode1,FirmName=FirmName,Link=Link,LinkTell=LinkTell

24、,City=Citywhere FirmCode=FirmCodecreate proc WorkInfor_update_procWorkNo1 char(10),Name varchar(12),UserRegName char(6),Password char(10),Position char(10),Power Int,WorkNo char(10)asupdate WorkInforsetWorkNo=WorkNo1,Name=Name,UserRegName=UserRegName,Password=Password,Position= Position,Power=Power

25、where WorkNo=WorkNo6 / 11个人收集整理 勿做商业用途create proc sellMain_update_procSaleNo1 int,WorkNo char(10),SaleDate DateTime,Amount Money,SaleNo int asupdate sellMainset SaleNo=SaleNo1,WorkNo=WorkNo,SaleDate=SaleDate,Amount=Amount where SaleNo=SaleNo create proc sellChild_update_procSaleNo1 int,MedicineCode

26、char(6),MedicineName varchar(32),Price Money,NumberInt,Uint char(8),Amount Money, SaleNo intasupdate sellChildsetSaleNo=SaleNo1,MedicineCode=MedicineCode,MedicineName=MedicineName,Price=Pri ce,Number=Number,Amount=Amountwhere SaleNo=SaleNo/* 建立存储过程实现单表查询 */* 建立名为“单表查询 1”地存储过程 , 用来查询某种药品地信息 */ create

27、 proc 单表查询 1MedicineCode char(6)ASselect *from MedInforwhere MedicineCode=MedicineCode/* 建立名为“单表查询 2”地存储过程 , 用来查询某个客户地信息 */ create proc 单表查询 2GuestCode char(10)ASselect *from GueInforwhere GuestCode=GuestCode/* 建立名为“单表查询 3”地存储过程 , 用来查询某个员工地信息 */ create proc 单表查询 37 / 11个人收集整理 勿做商业用途WorkNo char(10)AS

28、select *from WorkInforwhere WorkNo=WorkNo/* 建立名为“单表查询 4”地存储过程 , 用来查询某个供应商地信息 */ create proc 单表查询 4FirmCode char(10)ASselect *from FirmInforwhere FirmCode=FirmCode/* 建立名为“单表查询 5”地存储过程 , 用来查询某个药品代码对应地药品类型地信息*/create proc 单表查询 5MedKindeCode char(10)ASselect *from MedIDwhere MedKindeCode=MedKindeCode/*

29、建立存储过程实现连接查询 */* 建立名为“连接查询 1”地存储过程 , 用来查询某个药品名称对应地药品类型地信息*/create proc 连接查询 1MedicineName varchar(8) asselect MedicineName ,KindExplanationfrom MedInfor,MedIDwhere MedID.MedKindeCode=MedInfor.MedKindeCode and MedicineName=MedicineName/* 建立名为“连接查询 2”地存储过程 , 用来查询某个供应商提供地药品类型*/create proc 连接查询 2FirmNam

30、e varchar(16) asselect FirmName,KindExplanationfrom MedInfor,MedID,FirmInforwhere MedID.MedKindeCode=MedInfor.MedKindeCode andMedInfor.FirmCode=FirmInfor.FirmCode and FirmName=FirmName/* 建立名为“连接查询 3”地存储过程 , 用来查询某个销售员销售某种药品地数量*/8 / 11个人收集整理 勿做商业用途create proc 连接查询 3 Name varchar(12),MedicineName varch

31、ar(8) as select name ,MedInfor.MedicineName,sellChild.Number from WorkInfor,sellChild,MedInfor,sellMain where WorkInfor.WorkNo=sellMain.WorkNo and sellMain.SaleNo=sellChild.SaleNo and sellChild.MedicineCode=MedInfor.MedicineCode and name=Name andMedInfor.MedicineName=MedicineName/* 建立名为“连接查询 4”地存储过程

32、 , 用来查询某类药品地销售量 */ create proc 连接查询 4 KindExplanation varchar(12)asselect KindExplanation,sellChild.Number from sellChild,MedID,MedInforwhere MedID.MedKindeCode=MedInfor.MedKindeCode and MedInfor.MedicineCode=sellChild.MedicineCode and KindExplanation=KindExplanation/* 建立名为“连接查询 5”地存储过程 , 用来查询某个员工销售

33、地药品类型 */ create proc 连接查询 5Name varchar(12) as select Name,KindExplanation from sellChild,MedID,MedInfor,sellMain,WorkInfor where MedInfor.MedicineCode=sellChild.MedicineCode andMedID.MedKindeCode=MedInfor.MedKindeCode and WorkInfor.WorkNo=sellMain.WorkNo and sellMain.SaleNo=sellChild.SaleNo andName

34、=Name 建立存储过程实现嵌套查询 /* 建立名为“嵌套查询 1”地存储过错 , 用来查询某类药品地销售量 */ create proc 嵌套查询 1 KindExplanation varchar(12) as select Number from sellChild where MedicineCode In(select MedicineCodefrom MedID where KindExplanation=KindExplanation) /* 建立名为“嵌套查询 2”地存储过错 , 用来查询某个供应商提供地商品 */ create proc 嵌套查询 2 FirmName varchar(16)9 / 11个人收集整理 勿做商业用途asselect MedicineNamefrom MedInforwhere FirmCode In (select FirmCodefrom FirmInforwhere FirmName=FirmName)/* 建立存储过程实现集合查询 */* 建立名为“集合查询 1”地存储过错 , 用来查询提供某类商品地供应商数 */ create proc 集合查询 1MedicineName varchar(8) asselect avg(FirmCode)from FrimInforwhere FirmCode in(sel