醫(yī)藥銷售管理系統(tǒng)SQL語句

1、create database MedicalManagerSystem/*創(chuàng)建醫(yī)藥銷售管理系統(tǒng)*/use MedicalManagerSystemcreate table MedID/*創(chuàng)建藥品類別索引信息*/ (MedKindeCode char(10) constraint MI_PRI PRIMARY KEY, KindExplanation varchar(12) NOT NULL)create table MedInfor/*創(chuàng)建藥品信息表*/ (MedicineCode char(6) constraint M_PRIM PRIMARY KEY, MedicineName var

2、char(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/*創(chuàng)建客戶信息表*/ (GuestCode char(10) constraint G_PRIM PRIMARY KEY, GuestN

3、ame varchar(16) NOT NULl, GLink varchar(12), GLinkTell varchar(11), City varchar(8)create table FirmInfor/*創(chuàng)建供應商信息表*/ (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/*創(chuàng)建員工信息表*/ (WorkNo char

4、(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/*創(chuàng)建醫(yī)藥銷售主表*/ (SaleNo int constraint SM_PRIM PRIMARY KEY, WorkNo char(10) FOREIGN KEY REFERENCES WorkInfor(WorkNo), SaleDate DateTime, Amo

5、unt Money)create table sellChild/*創(chuàng)建醫(yī)藥銷售子表*/ (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) /*插入數(shù)據(jù)的存儲過程 */create proc MedID_procMedKindeCode char(1

6、0),KindExplanation varchar(12)asinsert into MedID (MedKindeCode,KindExplanation) values(MedKindeCode ,KindExplanation )exec MedID_proc '0001','口腔潰瘍'exec MedID_proc '0002','感冒'exec MedID_proc '0003','發(fā)燒'exec MedID_proc '0004','拉肚子'exec M

7、edID_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,Suppli

8、cer,Userfulllife) values(MedicineCode,MedicineName,MedKindeCode,Price,ListPrice,Number,FirmCode,Userfulllife)exec MedInfor_proc '1001','板藍根','0002',5,3,'100','014','2010-12-5'exec MedInfor_proc '2002','四季感康','0002',14,10.5,'

9、150','051','2010-12-12'exec MedInfor_proc '2003','銀黃顆粒','0002',12,8.8, '120 ','014','2012-10-6'exec MedInfor_proc '2004','感冒清熱軟膠囊','0002',17,12, '150','015', '2011-11-1'exec MedInfor_p

10、roc '3001','阿斯匹林','0003',15,11,'100','014','2010-12-1'exec MedInfor_proc '3002','布洛芬','0003',21,17.5,'120','051','2010-6-5'exec MedInfor_proc '4001','瀉利挺','0004',25,20,'120'

11、;,'015','2012-10-2'exec MedInfor_proc '4002','諾氟沙星膠囊','0004',15,12,'100','015','2012-9-16'exec MedInfor_proc '5001','碘酒','0005',5,2.5,'50' ,'051','2012-10-12'exec MedInfor_proc '5002&#

12、39;,'創(chuàng)口貼','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,GLin

13、k,GLinkTell,City)exec GueInfor_proc '015112','zhangsan','xiaozhang','668401','jiaxing'exec GueInfor_proc '065114','lisi','xiaofang','614425','yuyao'exec GueInfor_proc '052114','wangwu','xiaowu',&#

14、39;659024','wenzhou'exec GueInfor_proc '043115','zhaoliu','xiaowu','615874','shangyu'exec GueInfor_proc '014221','awu','xiaozhang','651283','linan'exec GueInfor_proc '025471','asha','xiaof

15、ang','691472','dongyang'create 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'

16、,'yangshengtang','xiaotai','681472','huzhou'exec FirmInfor_proc '014','baozhilin','zhangqing','658421','deqing'exec FirmInfor_proc '051','pinmingdayaofang','oudan','65417','xiangshan'creat

17、e 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',

18、9;zkl01','456789','jingli',''exec WorkInfor_proc '075201','ZJM','zjm01','123789','dongshi',''exec WorkInfor_proc '075215','WMX','wmx05','147258','xiaomi',''exec WorkInfor_proc

19、'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','

20、;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

21、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&

22、#39;,'bao',100exec sellChild_proc '15','2002','四季感康',14,'15','he',210exec sellChild_proc '20','3001','阿斯匹林',15,'20','he',300/*刪除數(shù)據(jù)的存儲過程*/ create proc MedID_delete_procMedKindeCode char(10)asdelete from MedId wher

23、e MedKindeCode=MedKindeCodeexec MedID_delete_proc '0002'create 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_

24、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 sel

25、lChildwhere SaleNo=SaleNo/*修改數(shù)據(jù)的存儲過程*/ 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','感冒',

26、'0001'create 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=

27、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

28、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 FirmInforse

29、t 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,UserRe

30、gName=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 sel

31、lChild_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/*建立存儲過程實現(xiàn)單表查詢*/ /

32、*建立名為“單表查詢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

33、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=MedKindeCo

34、de/*建立存儲過程實現(xiàn)連接查詢*/ /*建立名為“連接查詢1”的存儲過程，用來查詢某個藥品名稱對應的藥品類型的信息*/ create proc 連接查詢1MedicineName varchar(8)asselect MedicineName ,KindExplanationfrom MedInfor,MedIDwhere MedID.MedKindeCode=MedInfor.MedKindeCode and MedicineName=MedicineName/*建立名為“連接查詢2”的存儲過程，用來查詢某個供應商提供的藥品類型*/ create proc 連接查詢2FirmName va

35、rchar(16)asselect FirmName,KindExplanationfrom MedInfor,MedID,FirmInforwhere MedID.MedKindeCode=MedInfor.MedKindeCode and MedInfor.FirmCode=FirmInfor.FirmCode and FirmName=FirmName/*建立名為“連接查詢3”的存儲過程，用來查詢某個銷售員銷售某種藥品的數(shù)量*/create proc 連接查詢3Name varchar(12),MedicineName varchar(8)asselect name ,MedInfor.

36、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 連接查詢4Ki

37、ndExplanation 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)asse

38、lect 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建立存儲過程實現(xiàn)嵌套查詢/*建立名為“嵌套查詢1”的存儲過錯，用來查詢某類藥品的銷售量

39、*/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 (selec

40、t FirmCode from FirmInfor where FirmName=FirmName)/*建立存儲過程實現(xiàn)集合查詢*/*建立名為“集合查詢1”的存儲過錯，用來查詢提供某類商品的供應商數(shù)*/create proc 集合查詢1MedicineName varchar(8)asselect avg(FirmCode) from FrimInfor where FirmCode in(select FirmCode from MedInfor,FrimInfor where MedInfor.FirmCode=FrimInfor.FirmCode and MedicineName=Med

41、icineName)/*建立名為“集合查詢2”的存儲過錯，用來查詢藥品種類數(shù)*/create proc 集合查詢2asselect count(MedKindeCode) from MedID/*視圖建立*/*1，由藥品類別表“MedID”建立一個視圖，該視圖由藥品類別表的所有列構(gòu)成*/create view 藥品類別(藥品類別代碼,類別說明)asselect* from MedID/*2，由客戶信息表“GueInfor”建立一個視圖，該視圖由客戶信息表的所有列構(gòu)成*/create view 客戶信息(客戶編碼,客戶名稱,聯(lián)系人,聯(lián)系電話,所在城市)asselect* from GueInfor/*3，由供應商信息表“FirmInfor”建立一個視圖，該視圖由供應商信息表的所有列構(gòu)成*/create view 供應商信息(供