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_proc@MedKindeCode 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_proc@MedicineCode 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-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)asinsert 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)asinsert 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 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_proc@SaleNo 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_proc@SaleNo 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_proc@MedKindeCode char(10)asdelete from MedIdwhere MedKindeCode=@MedKindeCodeexec MedID_delete_proc '0002'create proc MedInfor_delete_proc@MedicineName varchar(8)asdelete from MedInforwhere MedicineName=@MedicineNamecreate proc GueInfor_delete_proc@GuestCode char(10)asdelete from GueInforwhere GuestCode=@GuestCodecreate proc FirmInfor_delete_proc@FirmCode char(10)asdelete from FirmInforwhere FirmCode=@FirmCodecreate proc WorkInfor_delete_proc@WorkNo char(10)asdelete from WorkInforwhere WorkNo=@WorkNocreate proc sellMain_delete_proc@SaleNo intasdelete from sellMainwhere SaleNo=@SaleNocreate proc sellChild_delete_proc@SaleNo intasdelete from sellChildwhere SaleNo=@SaleNo/*修改数据地存储过程*/create proc MedID_update_proc@MedKindeCode char(10),@KindExplanation varchar(12),@MedKindeCode1 char(10)asupdate MedIDset MedKindeCode=@MedKindeCode,KindExplanation=@KindExplanationwhere MedKindeCode=@MedKindeCode1exec 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)asupdate MedInforsetMedicineCode=@MedicineCode1,MedicineName=@MedicineName,MedKindeCode=@MedKindeCod e,Price=@Price,ListPrice=@ListPrice,Number=@Number,FirmCode=@FirmCode,Userfulllife=@Userfulllife,MedicineCode=@MedicineCodewhere MedKindeCode=@MedKindeCodecreate proc GueInfor_update_proc@GuestCode1 char(10),@GuestName varchar(16),@GLink varchar(12),@GLinkTell varchar(11),@City varchar(8),@GuestCode char(10)asupdate GueInforsetGuestCode=@GuestCode1,GuestName=@GuestName,GLink=@GLink,GLinkTell=@GLinkTell,City=@Citywhere GuestCode=@GuestCodecreate proc FirmInfor_update_proc@FirmCode1 char(10),@FirmName varchar(16),@Link varchar(12),@LinkTell varchar(11),@City varchar(8),@FirmCode char(10)asupdate FirmInforsetFirmCode=@FirmCode1,FirmName=@FirmName,Link=@Link,LinkTell=@LinkTell,City=@Citywhere FirmCode=@FirmCodecreate proc WorkInfor_update_proc@WorkNo1 char(10),@Name varchar(12),@UserRegName char(6),@Password char(10),@Position char(10),@Power Int,@WorkNo char(10)asupdate WorkInforsetWorkNo=@WorkNo1,Name=@Name,UserRegName=@UserRegName,Password=@Password,Position= @Position,Power=@Powerwhere WorkNo=@WorkNocreate proc sellMain_update_proc@SaleNo1 int,@WorkNo char(10),@SaleDate DateTime,@Amount Money,@SaleNo intasupdate sellMainset SaleNo=@SaleNo1,WorkNo=@WorkNo,SaleDate=@SaleDate,Amount=@Amountwhere SaleNo=@SaleNocreate proc sellChild_update_proc@SaleNo1 i nt,@MedicineCode char(6),@MedicineName varchar(32),@Price Money,@Number Int,@Uint char(8),@Amount Money,@SaleNo intasupdate sellChildsetSaleNo=@SaleNo1,MedicineCode=@MedicineCode,MedicineName=@MedicineName,Price=@Pri ce,Number=@Number,Amount=@Amountwhere SaleNo=@SaleNo/*建立存储过程实现单表查询*//*建立名为“单表查询1”地存储过程,用来查询某种药品地信息*/create proc 单表查询 1@MedicineCode char(6)ASselect *from MedInforwhere MedicineCode=@MedicineCode/*建立名为“单表查询2”地存储过程,用来查询某个客户地信息*/create proc 单表查询 2@GuestCode char(10)ASselect *from GueInforwhere GuestCode=@GuestCode/*建立名为“单表查询3”地存储过程,用来查询某个员工地信息*/create proc 单表查询 3@WorkNo char(10)ASselect *from WorkInforwhere WorkNo=@WorkNo/*建立名为“单表查询4”地存储过程,用来查询某个供应商地信息*/create proc 单表查询 4@FirmCode char(10)ASselect *from FirmInforwhere FirmCode=@FirmCode/*建立名为“单表查询5”地存储过程,用来查询某个药品代码对应地药品类型地信息*/ create proc 单表查询 5@MedKindeCode char(10)ASselect *from MedIDwhere MedKindeCode=@MedKindeCode/*建立存储过程实现连接查询*//*建立名为“连接查询1”地存储过程,用来查询某个药品名称对应地药品类型地信息*/ create proc 连接查询 1@MedicineName varchar(8)asselect MedicineName ,KindExplanationfrom MedInfor,MedIDwhere MedID.MedKindeCode=MedInfor.MedKindeCode andMedicineName=@MedicineName/*建立名为“连接查询2”地存储过程,用来查询某个供应商提供地药品类型*/create proc 连接查询 2@FirmName varchar(16)asselect FirmName,KindExplanationfrom MedInfor,MedID,FirmInforwhere MedID.MedKindeCode=MedInfor.MedKindeCode andMedInfor.FirmCode=FirmInfor.FirmCode andFirmName=@FirmName/*建立名为“连接查询3”地存储过程,用来查询某个销售员销售某种药品地数量*/create proc 连接查询 3@Name varchar(12),@MedicineName varchar(8)asselect name ,MedInfor.MedicineName,sellChild.Numberfrom WorkInfor,sellChild,MedInfor,sellMainwhere WorkInfor.WorkNo=sellMain.WorkNo andsellMain.SaleNo=sellChild.SaleNo andsellChild.MedicineCode=MedInfor.MedicineCode andname=@Name andMedInfor.MedicineName=@MedicineName/*建立名为“连接查询4”地存储过程,用来查询某类药品地销售量*/ create proc 连接查询 4@KindExplanation varchar(12)asselect KindExplanation,sellChild.Numberfrom sellChild,MedID,MedInforwhere MedID.MedKindeCode=MedInfor.MedKindeCode andMedInfor.MedicineCode=sellChild.MedicineCode andKindExplanation=@KindExplanation/*建立名为“连接查询5”地存储过程,用来查询某个员工销售地药品类型*/ create proc 连接查询 5@Name varchar(12)asselect Name,KindExplanationfrom sellChild,MedID,MedInfor,sellMain,WorkInforwhere MedInfor.MedicineCode=sellChild.MedicineCode andMedID.MedKindeCode=MedInfor.MedKindeCode andWorkInfor.WorkNo=sellMain.WorkNo andsellMain.SaleNo=sellChild.SaleNo andName=@Name建立存储过程实现嵌套查询/*建立名为“嵌套查询1”地存储过错,用来查询某类药品地销售量*/ create proc 嵌套查询 1@KindExplanation varchar(12)asselect Numberfrom sellChildwhere MedicineCode In(select MedicineCodefrom MedIDwhere KindExplanation=@KindExplanation)/*建立名为“嵌套查询2”地存储过错,用来查询某个供应商提供地商品*/ create proc 嵌套查询 2@FirmName varchar(16)asselect MedicineNamefrom MedInforwhere FirmCode In (select FirmCodefrom FirmInforwhere FirmName=@FirmName)/*建立存储过程实现集合查询*//*建立名为“集合查询1”地存储过错,用来查询提供某类商品地供应商数*/create proc 集合查询 1@MedicineName varchar(8)asselect avg(FirmCode)from FrimInforwhere FirmCode in(select FirmCodefrom MedInfor,FrimInforwhere MedInfor.FirmCode=FrimInfor.FirmCode andMedicineName=@MedicineName)/*建立名为“集合查询2”地存储过错,用来查询药品种类数*/create proc 集合查询 2asselect count(MedKindeCode)from MedID/*视图建立*//*1,由药品类别表“MedID”建立一个视图,该视图由药品类别表地所有列构成*/create view 药品类别(药品类别代码,类别说明)asselect* from MedID/*2,由客户信息表“GueInfor”建立一个视图,该视图由客户信息表地所有列构成*/ create view 客户信息(客户编码,客户名称,联系人,联系电话,所在城市)asselect* from GueInfor/*3,由供应商信息表“FirmInfor”建立一个视图,该视图由供应商信息表地所有列构成*/ create view 供应商信息(供应商编码,供应商名称,联系人,联系电话,所在城市)asselect* from FirmInfor/*建立INSERT触发器*/create trigger MedID_insert on MedIDfor insertas if(select count(*)个人收集整理勿做商业用途from MedID_med,insertedwhere MedID_med.MedKindeCode=inserted.MedKindeCode)=0 rollback transaction/*建立DELETE触发器*/create trigger delete_MedID on MedIDfor deleteasselect* from MedIDdeclare @MedKindeCode char(10)select @MedKindeCode=MedKindeCode from deleteddelete from MedIDwhere MedKindeCode=@MedKindeCodeselect*from MedID*/建立UPDATE触发器*/create trigger MedID_update on MedIDfor updateasif update(MedKindeCode)beginraiserror('you can not modify this column',16,1)rollback transactionend11 / 11。