医药销售管理系统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
update MedInfor
set
MedicineCode=@MedicineCode1,MedicineName=@MedicineName,MedKindeCode=@MedKindeCod e,Price=@Price,
ListPrice=@ListPrice,Number=@Number,FirmCode=@FirmCode,Userfulllife=@Userfulllif
e,MedicineCode=@MedicineCode
where MedKindeCode=@MedKindeCode
create proc GueInfor_update_proc
@GuestCode1 char(10),@GuestName varchar(16),@GLink varchar(12),@GLinkTell varchar(11),
@City varchar(8),@GuestCode char(10)
as
update GueInfor
set
GuestCode=@GuestCode1,GuestName=@GuestName,GLink=@GLink,GLinkTell=@GLinkTell,
City=@City
where GuestCode=@GuestCode
create proc FirmInfor_update_proc
@FirmCode1 char(10),@FirmName varchar(16),@Link varchar(12),@LinkTell varchar(11),@City varchar(8),
@FirmCode char(10)
as
update FirmInfor
set
FirmCode=@FirmCode1,FirmName=@FirmName,Link=@Link,LinkTell=@LinkTell,City=@City
where FirmCode=@FirmCode
create proc WorkInfor_update_proc
@WorkNo1 char(10),@Name varchar(12),@UserRegName char(6),@Password char(10),@Position char(10),@Power Int,
@WorkNo char(10)
as
update WorkInfor
set
WorkNo=@WorkNo1,Name=@Name,UserRegName=@UserRegName,Password=@Password,Position= @Position,Power=@Power
where WorkNo=@WorkNo
create proc sellMain_update_proc
@SaleNo1 int,@WorkNo char(10),@SaleDate DateTime,@Amount Money,@SaleNo int
as
update sellMain
set SaleNo=@SaleNo1,WorkNo=@WorkNo,SaleDate=@SaleDate,Amount=@Amount
where SaleNo=@SaleNo
create proc sellChild_update_proc
@SaleNo1 i nt,@MedicineCode char(6),@MedicineName varchar(32),@Price Money,@Number Int,@Uint char(8),@Amount Money,
@SaleNo int
as
update sellChild
set
SaleNo=@SaleNo1,MedicineCode=@MedicineCode,MedicineName=@MedicineName,Price=@Pri ce,Number=@Number,Amount=@Amount
where SaleNo=@SaleNo
/*建立存储过程实现单表查询*/
/*建立名为“单表查询1”地存储过程,用来查询某种药品地信息*/
create proc 单表查询 1
@MedicineCode char(6)
AS
select *
from MedInfor
where MedicineCode=@MedicineCode
/*建立名为“单表查询2”地存储过程,用来查询某个客户地信息*/
create proc 单表查询 2
@GuestCode char(10)
AS
select *
from GueInfor
where GuestCode=@GuestCode
/*建立名为“单表查询3”地存储过程,用来查询某个员工地信息*/
create proc 单表查询 3
@WorkNo char(10)
AS
select *
from WorkInfor
where WorkNo=@WorkNo
/*建立名为“单表查询4”地存储过程,用来查询某个供应商地信息*/
create proc 单表查询 4
@FirmCode char(10)
AS
select *
from FirmInfor
where FirmCode=@FirmCode
/*建立名为“单表查询5”地存储过程,用来查询某个药品代码对应地药品类型地信息*/ create proc 单表查询 5
@MedKindeCode char(10)
AS
select *
from MedID
where MedKindeCode=@MedKindeCode
/*建立存储过程实现连接查询*/
/*建立名为“连接查询1”地存储过程,用来查询某个药品名称对应地药品类型地信息*/ create proc 连接查询 1
@MedicineName varchar(8)
as
select MedicineName ,KindExplanation
from MedInfor,MedID
where MedID.MedKindeCode=MedInfor.MedKindeCode and
MedicineName=@MedicineName
/*建立名为“连接查询2”地存储过程,用来查询某个供应商提供地药品类型*/
create proc 连接查询 2
@FirmName varchar(16)
as
select FirmName,KindExplanation
from MedInfor,MedID,FirmInfor
where MedID.MedKindeCode=MedInfor.MedKindeCode and
MedInfor.FirmCode=FirmInfor.FirmCode and
FirmName=@FirmName
/*建立名为“连接查询3”地存储过程,用来查询某个销售员销售某种药品地数量*/
create proc 连接查询 3
@Name varchar(12),@MedicineName varchar(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 and
MedInfor.MedicineName=@MedicineName
/*建立名为“连接查询4”地存储过程,用来查询某类药品地销售量*/ create proc 连接查询 4
@KindExplanation varchar(12)
as
select KindExplanation,sellChild.Number
from sellChild,MedID,MedInfor
where MedID.MedKindeCode=MedInfor.MedKindeCode and
MedInfor.MedicineCode=sellChild.MedicineCode and
KindExplanation=@KindExplanation
/*建立名为“连接查询5”地存储过程,用来查询某个员工销售地药品类型*/ create proc 连接查询 5
@Name varchar(12)
as
select Name,KindExplanation
from 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 嵌套查询 1
@KindExplanation varchar(12)
as
select Number
from sellChild
where MedicineCode In(select MedicineCode
from MedID
where KindExplanation=@KindExplanation)
/*建立名为“嵌套查询2”地存储过错,用来查询某个供应商提供地商品*/ create proc 嵌套查询 2
@FirmName varchar(16)
as
select MedicineName
from MedInfor
where FirmCode In (select FirmCode
from FirmInfor
where FirmName=@FirmName)
/*建立存储过程实现集合查询*/
/*建立名为“集合查询1”地存储过错,用来查询提供某类商品地供应商数*/
create proc 集合查询 1
@MedicineName varchar(8)
as
select avg(FirmCode)
from FrimInfor
where FirmCode in(select FirmCode
from MedInfor,FrimInfor
where MedInfor.FirmCode=FrimInfor.FirmCode and
MedicineName=@MedicineName)
/*建立名为“集合查询2”地存储过错,用来查询药品种类数*/
create proc 集合查询 2
as
select count(MedKindeCode)
from MedID
/*视图建立*/
/*1,由药品类别表“MedID”建立一个视图,该视图由药品类别表地所有列构成*/
create view 药品类别(药品类别代码,类别说明)
as
select* from MedID
/*2,由客户信息表“GueInfor”建立一个视图,该视图由客户信息表地所有列构成*/ create view 客户信息(客户编码,客户名称,联系人,联系电话,所在城市)
as
select* from GueInfor
/*3,由供应商信息表“FirmInfor”建立一个视图,该视图由供应商信息表地所有列构成*/ create view 供应商信息(供应商编码,供应商名称,联系人,联系电话,所在城市)
as
select* from FirmInfor
/*建立INSERT触发器*/
create trigger MedID_insert on MedID
for insert
as if(select count(*)
from MedID_med,inserted
where MedID_med.MedKindeCode=inserted.MedKindeCode)=0 rollback transaction
/*建立DELETE触发器*/
create trigger delete_MedID on MedID
for delete
as
select* from MedID
declare @MedKindeCode char(10)
select @MedKindeCode=MedKindeCode from deleted
delete from MedID
where MedKindeCode=@MedKindeCode
select*from MedID
*/建立UPDATE触发器*/
create trigger MedID_update on MedID
for update
as
if update(MedKindeCode)
begin
raiserror('you can not modify this column',16,1)
rollback transaction
end