数据库课程设计——医药销售管理系统.doc

use mastergo -drop database MedicalManagerSystemcreate database MedicalManagerSystem/*创建医药销售管理系统*/on(name=M_data, filename=e:20094051227M_data.mdf, size=10MB, maxsize=300MB, filegrowth=10%)log on(name=M_log, filename=e:20094051227M_data.ldf, size=5MB, maxsize=300MB, filegrowth=2MB)use MedicalManagerSystemgocreate table FirmInfor /*创建供应商信息表*/ (FirmCode char(10) PRIMARY KEY, FirmName varchar(16) NOT NULL, Link varchar(12), /*联系人*/ LinkTell varchar(11), City varchar(8); create table MedInfor/*创建药品信息表*/ (MedicineCode char(6) PRIMARY KEY, MedicineName varchar(32) NOT NULL, Price Money, ListPrice Money, /*进价*/ Number Int, /*库存*/ Userfulllife Datetime, FirmCode char(10), /*供应商，外键*/ FOREIGN KEY(FirmCode) REFERENCES FirmInfor(FirmCode); create table GueInfor/*创建客户信息表*/ (GuestCode char(10) PRIMARY KEY, GuestName varchar(16) NOT NULl, Gage int, GLinkTell varchar(11); create table WorkInfor/*创建员工信息表*/ (WorkNo char(10) PRIMARY KEY, Name varchar(12),Age Int, UserRegName char(6) NOT NULL, /*用户登录名*/ Password char(10) NOT NULL ); /*密码*/create table ManagerInfor/*创建经理信息表*/ (ManagerName varchar(12) PRIMARY KEY,UserRegName char(6) NOT NULL,PassWord char(10) NOT NULL );create table sell/*创建医药销售表*/(SaleNo int PRIMARY KEY, MedicineCode char(6), WorkNo char(10), GuestCode char(10), MedicineName varchar(32) NOT NULL, SaleDate DateTime, Price Money, Number Int, FOREIGN KEY(GuestCode) REFERENCES GueInfor(GuestCode), FOREIGN KEY(MedicineCode) REFERENCES MedInfor(MedicineCode), FOREIGN KEY(WorkNo) REFERENCES WorkInfor(WorkNo); create table serve/*供应药品表*/(FirmCode char(10) NOT NULL, MedicineCode char(6) NOT NULL, Serveday DateTime, Servenumber int, PRIMARY KEY(FirmCode,MedicineCode), FOREIGN KEY(MedicineCode) REFERENCES MedInfor(MedicineCode), FOREIGN KEY(FirmCode) REFERENCES FirmInfor(FirmCode); /*插入数据*/insert into FirmInfor(FirmCode,FirmName,Link,LinkTell,City)values(015,养生堂,小泰,681472,湖州);insert into FirmInfor(FirmCode,FirmName,Link,LinkTell,City)values(014,宝芝林,张清,658421,德州);insert into FirmInfor(FirmCode,FirmName,Link,LinkTell,City)values(051,同仁堂,周丹,654217,北京); insert into MedInforvalues(1001,板蓝根,5,3,100,2010.12.5,014);insert into MedInfor(MedicineCode ,MedicineName,Price,ListPrice,Number,FirmCode,Userfulllife) values(2002,四季感康,14,10.5,150,051,2010-12-12);insert into MedInfor(MedicineCode ,MedicineName,Price,ListPrice,Number,FirmCode,Userfulllife) values(2003,银黄颗粒,12,8.8, 120 ,014,2012-10-6);insert into MedInfor(MedicineCode ,MedicineName,Price,ListPrice,Number,FirmCode,Userfulllife) values(2004,感冒清热软胶囊,17,12,150,015, 2011-11-1);insert into MedInfor(MedicineCode ,MedicineName,Price,ListPrice,Number,FirmCode,Userfulllife) values(3001 ,阿斯匹林, 15,11,100,014,2010-12-1);insert into GueInforvalues(00123,刘刚,45;insert into GueInforvalues(00124,张华,30;insert into GueInforvalues(00125,王明,60,3015191);insert into WorkInforvalues(9127,zhangling,28,009127,0000000000);insert into WorkInforvalues(9128,liuqing,35,009128,0000000000);insert into WorkInforvalues(9129,tianhua,22,009129,0000000000);insert into ManagerInforvalues(张小西,012345,0123456789);insert into sell values(101,2002,9128,00123,四季感康,2011-6-10,14,2);insert into sell values(102,1001,9129,00124,板蓝根,2011-6-11,4.5,20);insert into sell values(103,2003,9129,00123,银黄颗粒,2011-6-13,12,4);insert into sell values(104,3001,9127,00125,阿斯匹林,2011-6-17,15,1);insert into serve values(015,2003,2011-3-20,100);insert into serve values(051,3001,2011-3-10,200);insert into serve values(015,1001,2011-5-10,1000);/*测试语句select * from sell;select * from MedInfor;delete from sellwhere SaleNo=101;*/ /*创建索引*/create UNIQUE index FirmIndex on FirmInfor(FirmCode); /*供应商编号*/create UNIQUE index MedicineIndex on MedInfor(MedicineCode); /*药品编号*/create UNIQUE index GuestIndex on GueInfor(GuestCode); /*客户编号*/create UNIQUE index WorkIndex on WorkInfor(WorkNo); /*职工编号*/create UNIQUE index sellIndex on sell(SaleNo); /*销售编号*/create UNIQUE index MedNameIndex on MedInfor(MedicineName); /*药品名称*/*创建视图*/create view 供应商所在城市(FirmCode,FirmName,City)asselect FirmCode,FirmName,CityFrom FirmInfor;create view 供应商联系方式(FirmCode,FirmName,Link,LinkTell)asselect FirmCode,FirmName,Link,LinkTellFrom FirmInfor;create view 职工业绩(WorkNo,Name,Age,SaleNo,MedicineCode,SaleDate,Price,Number)asselect WorkInfor.WorkNo,Name,Age,SaleNo,MedicineCode,SaleDate,Price,NumberFrom WorkInfor,sellWHERE WorkInfor.WorkNo=sell.WorkNo;create view 药品库存量(MedicineCode,MedicineName,Number)asselect MedicineCode,MedicineName,NumberFrom MedInfor;/*测试视图语句*/select *from 供应商联系方式select * from 职工业绩where WorkNo=9129;/*创建触发器*/create trigger 售货减少库存量 on sellfor insertasdeclare Number int,MedicineCode char(6)select Number=Number from insertedselect MedicineCode=MedicineCode from insertedupdate MedInfor set Number=Number-Numberwhere MedicineCode=MedicineCodego create trigger 退货增加库存量 on sellfor deleteasdeclare Number int,MedicineCode char(6)select Number=Number from deletedselect MedicineCode=MedicineCode from deletedupdate MedInfor set Number=Number+Numberwhere MedicineCode=MedicineCodegocreate trigger 进货增加库存量 on servefor insertasdeclare Servenumber int,MedicineCode char(6)select Servenumber=Servenumber from inser