医药销售管理系统SQL语句
医药销售管理系统SQL语句
————————————————————————————————作者:————————————————————————————————日期:
create database MedicalManagerSystem/*创建医药销售管理系统*/
use MedicalManagerSystem
create 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_proc
@MedKindeCode char(10),@KindExplanation varchar(12)
as
insert 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_proc
@MedicineCode char(6),@MedicineName varchar(8),@MedKindeCode char(10),@Price money,@ListPrice money,
@Number int,@FirmCode char(10),@Userfulllife Datetime
as
insert 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-5'
exec MedInfor_proc '2002','四季感康','0002',14,10.5,'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_proc '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','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','创口贴','0005',2,1,'250','014','2015-5-1'
create proc GueInfor_proc
@GuestCode char(10),@GuestName varchar(16),@GLink varchar(12),@GLinkTell varchar(11),
@City varchar(8)
as
insert into GueInfor(GuestCode,GuestName,GLink,GLinkTell,
City) values(@GuestCode,@GuestName,@GLink,@GLinkTell,@City)
exec GueInfor_proc '015112','zhangsan','xiaozhang','668401','jiaxing'
exec GueInfor_proc '065114','lisi','xiaofang','614425','yuyao'
exec GueInfor_proc '052114','wangwu','xiaowu','659024','wenzhou'
exec GueInfor_proc '043115','zhaoliu','xiaowu','615874','shangyu'
exec GueInfor_proc '014221','awu','xiaozhang','651283','linan'
exec GueInfor_proc '025471','asha','xiaofang','691472','dongyang'
create proc FirmInfor_proc
@FirmCode char(10),@FirmName varchar(16),@Link varchar(12),@LinkTell varchar(11),@City varchar(8)
as
insert into FirmInfor(FirmCode,FirmName,Link,LinkTell,City)
values(@FirmCode,@FirmName,@Link,@LinkTell,@City)
exec FirmInfor_proc '015','yangshengtang','xiaotai','681472','huzhou'
exec FirmInfor_proc '014','baozhilin','zhangqing','658421','deqing'
exec FirmInfor_proc '051','pinmingdayaofang','oudan','65417','xiangshan'
create proc WorkInfor_proc
@WorkNo char(10),@Name varchar(12),@UserRegName char(6),@Password char(10),@Position char(10),@Power Int
as
insert 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_proc
@SaleNo int,@WorkNo char(10),@SaleDate DateTime,@Amount Money
as
insert into sellMain(SaleNo,WorkNo,SaleDate,Amount)
values(@SaleNo,@WorkNo,@SaleDate,@Amount)
exec sellMain_proc '12','075101','2009-1-1',1000
exec sellMain_proc '13','075201','2009-1-1',1500
exec sellMain_proc '15','075215','2009-1-1',800
exec sellMain_proc '20','075120','2009-1-1',1200
alter proc sellChild_proc
@SaleNo int,@MedicineCode char(6),@MedicineName varchar(32),@Price Money,@Number Int,@Uint char(8),@Amount Money
as
insert 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',100
exec sellChild_proc '15','2002','四季感康',14,'15','he',210
exec sellChild_proc '20','3001','阿斯匹林',15,'20','he',300
/*删除数据的存储过程*/
create proc MedID_delete_proc
@MedKindeCode char(10)
as
delete from MedId
where MedKindeCode=@MedKindeCode
exec MedID_delete_proc '0002'
create proc MedInfor_delete_proc
@MedicineName varchar(8)
as
delete from MedInfor
where MedicineName=@MedicineName
create proc GueInfor_delete_proc
@GuestCode char(10)
as
delete from GueInfor
where GuestCode=@GuestCode
create proc FirmInfor_delete_proc
@FirmCode char(10)
as
delete from FirmInfor
where FirmCode=@FirmCode
create proc WorkInfor_delete_proc
@WorkNo char(10)
as
delete from WorkInfor
where WorkNo=@WorkNo
create proc sellMain_delete_proc
@SaleNo int
as
delete from sellMain
where SaleNo=@SaleNo
create proc sellChild_delete_proc
@SaleNo int
as
delete from sellChild
where SaleNo=@SaleNo
/*修改数据的存储过程*/
create proc MedID_update_proc
@MedKindeCode char(10),@KindExplanation varchar(12),@MedKindeCode1 char(10)
as
update MedID
set MedKindeCode=@MedKindeCode,KindExplanation=@KindExplanation
where MedKindeCode=@MedKindeCode1
exec MedID_update_proc '0002','感冒','0001'
create proc MedInfor_update_proc
@MedicineCode1 char(6),@MedicineName varchar(8),@MedKindeCode char(10),@Price money,@ListPrice money,
@Number int,@FirmCode char(10),@Userfulllife Datetime,@MedicineCode char(6)
as