医药销售管理系统SQL语句.doc

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, MedicineName 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 KEY, 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 char(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), SaleDate DateTime, Amount Money)create table sellChild/*创建医药销售子表*/ (SaleNo int constraint SC_PRIM PRIMARY KEY, MedicineCode char(6) FOREIGN KEY REFERENCES MedInfor(MedicineCode), MedicineName 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 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 Datetimeasinsert into MedInfor(MedicineCode ,MedicineName,MedKindeCode,Price,ListPrice,Number,Supplicer,Userfulllife) values(MedicineCode,MedicineName,MedKindeCode,Price,ListPrice,Number,FirmCode,Userfulllife)exec MedInfor_proc 1001,板蓝根,0002,5,3,100,014,2010-12-5exec MedInfor_proc 2002,四季感康,0002,14,10.5,150,051,2010-12-12exec MedInfor_proc 2003,银黄颗粒,0002,12,8.8, 120 ,014,2012-10-6exec MedInfor_proc 2004,感冒清热软胶囊,0002,17,12, 150,015, 2011-11-1exec MedInfor_proc 3001,阿斯匹林,0003,15,11,100,014,2010-12-1exec MedInfor_proc 3002,布洛芬,0003,21,17.5,120,051,2010-6-5exec MedInfor_proc 4001,泻利挺,0004,25,20,120,015,2012-10-2exec MedInfor_proc 4002,诺氟沙星胶囊,0004,15,12,100,015,2012-9-16exec MedInfor_proc 5001,碘酒,0005,5,2.5,50 ,051,2012-10-12exec MedInfor_proc 5002,创口贴,0005,2,1,250,014,2015-5-1create 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,zhangsan,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,dongyangcreate 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_proc 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)values(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_procSaleNo int,WorkNo char(10),SaleDate DateTime,Amount Moneyasinsert 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,075120,2009-1-1,1200alter 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)exec sellChild_proc 13,1001,板蓝根,5,20,bao,100exec sellChild_proc 15,2002,四季感康,14,15,he,210exec sellChild_proc 20,3001,阿斯匹林,15,20,he,300/*删除数据的存储过程*/ create proc MedID_delete_procMedKindeCode char(10)asdelete from MedId where MedKindeCode=MedKindeCodeexec MedID_delete_proc 0002create proc MedInfor_delete_procMedicineName varchar(8)asdelete from MedInfor where MedicineName=MedicineNamecreate proc GueInfor_delete_procGuestCode char(10) asdelete from GueInforwhere GuestCode=GuestCodecreate proc FirmInfor_delete_procFirmCode char(10)asdelete from FirmInforwhere FirmCode=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_update_procMedKindeCode char(10),KindExplanation varchar(12),MedKindeCode1 char(10)asupdate MedID set MedKindeCode=MedKindeCode,KindExplanation=KindExplanation where MedKindeCode=MedKindeCode1exec MedID_update_proc 0002,感冒,0001create proc MedInfor_update_procMedicineCode1 char(6),MedicineName varchar(8),MedKindeCode char(10),Price money,ListPrice money,Number int,FirmCode char(10),Userfulllife Datetime,MedicineCode char(6)asupdate MedInforset MedicineCode=MedicineCode1,MedicineName=MedicineName,MedKindeCode=MedKindeCode,Price=Price,ListPrice=ListPrice,Number=Number,FirmCode=FirmCode,Userfulllife=Userfulllife,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 GueInforset GuestCode=GuestCode1,GuestName=GuestName,GLink=GLink,GLinkTell=GLinkTell,City=Citywhere GuestCode=GuestCodecreate proc FirmInfor_update_procFirmCode1 char(10),FirmName varchar(16),Link varchar(12),LinkTell varchar(11),City varchar(8),FirmCode char(10)asupdate FirmInforset FirmCode=FirmCode1,FirmName=FirmName,Link=Link,LinkTell=LinkTell,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 WorkInforset WorkNo=WorkNo1,Name=Name,UserRegName=UserRegName,Password=Password,Position=Position,Power=Powerwhere WorkNo=WorkNocreate proc sellMain_update_procSaleNo1 int,WorkNo char(10),SaleDate DateTime,Amount Money,SaleNo intasupdate sellMainset SaleNo=SaleNo1,WorkNo=WorkNo,SaleDate=SaleDate,Amount=Amountwhere SaleNo=SaleNocreate proc sellChild_update_procSaleNo1 int,MedicineCode char(6),MedicineName varchar(32),Price Money,Number Int,Uint char(8),Amount Money,SaleNo intasupdate sellChildset SaleNo=SaleNo1,MedicineCode=MedicineCode,MedicineName=MedicineName,Price=Price,Number=Number,Amount=Amountwhere SaleNo=SaleNo/*建立存储过程实现单表查询*/ /*建立名为“单表查询1”的存储过程，用来查询某种药品的信息*/ create proc 单表查询1MedicineCode char(6)ASselect *from MedInfor where MedicineCode=MedicineCode /*建立名为“单表查询2”的存储过程，用来查询某个客户的信息*/ create proc 单表查询2GuestCode char(10)ASselect *from GueInfor where GuestCode=GuestCode /*建立名为“单表查询3”的存储过程，用来查询某个员工的信息*/ create proc 单表查询3WorkNo char(10)ASselect *from WorkInfor where WorkNo=WorkNo /*建立名为“单表查询4”的存储过程，用来查询某个供应商的信息*/ create proc 单表查询4FirmCode char(10)ASselect *from FirmInfor where FirmCode=FirmCode /*建立名为“单表查询5”的存储过程，用来查询某个药品代码对应的药品类型的信息*/ create proc 单表查询5MedKindeCode char(10)ASselect *from MedID where MedKindeCode=MedKindeCode/*建立存储过程实现连接查询*/ /*建立名为“连接查询1”的存储过程，用来查询某个药品名称对应的药品类型的信息*/ create proc 连接查询1MedicineName varchar(8)asselect MedicineName ,KindExplanationfrom MedInfor,MedIDwhere MedID.MedKindeCode=MedInfor.MedKindeCode and MedicineName=MedicineName/*建立名为“连接查询2”的存储过程，用来查询某个供应商提供的药品类型*/ create proc 连接查询2FirmName varchar(16)asselect FirmName,KindExplanationfrom MedInfor,MedID,FirmInforwhere MedID.MedKindeCode=MedInfor.MedKindeCode and MedInfor.FirmCode=FirmInfor.FirmCode and FirmName=FirmName/*建立名为“连接查询3”的存储过程，用来查询某个销售员销售某种药品的数量*/create proc 连接查询3Name varchar(12),MedicineName varchar(8)asselect name ,MedInfor.MedicineName,sellChild.Numberfrom WorkInfor,sellChild,MedInfor,sellMainwhere WorkInfor.WorkNo=sellMain.WorkNo and sellMain.SaleNo=sellChild.SaleNo and sellChild.MedicineCode=MedInfor.MedicineCode and name=Name and MedInfor.MedicineName=MedicineName/*建立名为“连接查询4”的存储过程，用来查询某类药品的销售量*/create proc 连接查询4KindExplanation varchar(12)asselect KindExplanation,sellChild.Numberfrom sellChild,MedID,MedInfor where MedID.MedKindeCode=MedInfor.MedKindeCode and MedInfor.MedicineCode=sellChild.MedicineCode and KindExplanation=KindExplanation/*建立名为“连接查询5”的存储过程，用来查询某个员工销售的药品类型*/create proc 连接查询5Name varchar(12)asselect Name,KindExplanationfrom sellChild,MedID,MedInfor,sellMain,WorkInfor where MedInfor.MedicineCode=sellChild.MedicineCode and MedID.MedKindeCode=MedInfor.MedKindeCode and WorkInfor.WorkNo=sellMain.WorkNo and sellMain.SaleNo=sellChild.SaleNo and Name=Name建立存储过程实现嵌套查询/*建立名为“嵌套查询1”的存储过错，用来查询某类药品的销售量*/create proc 嵌套查询1KindExplanation varchar(12)asselect Number from sellChild where MedicineCode In(select MedicineCode from MedID where KindExplanation=KindExplanation)/*建立名为“嵌套查询2”的存储过错，用来查询某个供应商提供的商品*/create proc 嵌套查询2FirmName varchar(16)asselect MedicineName from MedInfor where FirmCode In (select FirmCode from FirmInfor where FirmName=FirmName)/*建立存储过程实现集合查询*/*建立名为“集合查询1”的存储过错，用来查询提供某类商品的供应商数*/create proc 集合查询1MedicineName varchar(8)asselect