1、医药销售管理系统SQL语句医药销售管理系统SQL语句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 PRI
2、MARY 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
3、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 WorkInf
4、or/*创建员工信息表*/ (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(WorkN
5、o), 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 Med
6、ID_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_procMedicineCod
7、e 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,List
8、Price,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,阿斯匹林,
9、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
10、,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,jiaxi
11、ngexec 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_procFirm
12、Code 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,6584
13、21,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,Pa
14、ssword,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,
