☆软件工程师☆必备2——数据库基础之电子商城购物系统源码

create database EBuy on primary (name=N'EBuy', filename=N'E:\F04 潘小贵\s1303\EBuy\EBuy.mdf' ) log on (name=N'EBuy_log', filename=N'E:\F04 潘小贵\s1303\EBuy\EBuy_log.ldf' ) create table 商品类别表 (商品类别号 int not null , 商品类别名称 varchar(30) not null, primary key(商品类别号) ) create table 商品信息表 (商品号 int, 商品类别号 int not null, 商品名称 varchar(30) not null, 商品单价 decimal(10,2) not null, 批发价 decimal(10,2) not null, 商品库存量 decimal(10,2) not null, 商品详情 varchar(1000) not null, primary key(商品号), foreign key (商品类别号) references 商品类别表(商品类别号) ) create table 客户信息表 (客户帐号 varchar(20), 客户密码 varchar(6) not null, 客户姓名 varchar(30) not null, 客户性别 varchar(1) not null, 电子邮箱 varchar(30) not null, 联系电话 varchar(12) not null, 地址 varchar(80) not null, 邮政编码 varchar(6) not null, 身份证号 varchar(18) not null, primary key(客户帐号) ) create table 订单信息表 (订单号 int not null, 客户号 varchar(20) not null, 商品号 int not null, 数量 int not null, 付款金额 decimal(10,2) not null, 付款方式 varchar(50) not null, 日期 datetime not null, 是否确认 varchar(1), 是否派货 varchar(1), primary key(订单号) ) create table 销售流水信息表 (销售流水号 int not null, 订单号 int not null, 客户号 varchar(20) not null, 商品号 int not null, 商品数量 int not null, 日期 datetime not null, 附送金额 decimal(10,2) not null, 送货地址 varchar(100) not null, primary key(销售流水号) ) create table dept (Deptno varchar(4) not null primary key, Dname varchar(30) not null, Deptloc varchar(60) not null, ) create table Emp (Empno int not null primary key, Ename varchar(30) not null, Deptno varchar(4) not null, Hiredate datetime not null, Salary decimal(8,2) not null, Comm decimal(8,2), Mgr int, foreign key (Deptno) references dept (Deptno) ) alter table Emp alter column Mgr varchar(10) insert into 商品类别表 values('1','家具') insert into 商品类别表 values('2','电器') insert into 商品类别表 values('3','化妆品') insert into 商品类别表 values('4','运动器材') insert into 商品类别表 values('5','食品') insert into 商品信息表 values('2001','1','剃须刀','75.00','50.00','200.00','made in China') insert into 商品信息表 values('2002','4','哑铃','105.00','70.00','180.00','10公斤') insert into 商品信息表 values('2003','1','沙发','1075.00','850.00','10.00','全友家俬') insert into 商品信息表 values('2004','5','薯片','5.00','4.50','200.00','上好佳') insert into 商品信息表 values('2005','2','空调','3075.00','2850.00','10.00','格力') insert into 商品信息表 values('2006','3','香水','775.00','350.00','150.00','香奈儿') insert into 商品信息表 values('2007','4','篮球','105.00','80.00','100.00','斯伯丁') insert into 商品信息表 values('2008','2','电视机','2275.00','1950.00','110.00','TCL') insert into 商品信息表 values('2009','3','SOD蛋白蜜','7.00','6.50','100.00','大宝系列') insert into 商品信息表 values('2010','5','緑箭','1.50','1.20','200.00','口香糖系列') insert into 订单信息表 values('2008110401','1001','2003','1','1075.00','分期付款','2008-11-04','Y','N') insert into 订单信息表 values('2008110102','1002','2006','3','2325.00','支票付款','2008-11-01','Y','Y') insert into 订单信息表 values('2008110203','1005','2005','1','3075.00','现金付款','2008-11-02','Y','Y') insert into 订单信息表 values('2008110404','1004','2004','4','20.00','现金付款','2008-11-04','Y','Y') insert into 订单信息表 values('2008110405','1007','2008','2','4500.00','分期付款','2008-11-04','Y','N') insert into 订单信息表 values('2008101506','1008','2009','5','35.00','现金付款','2008-10-15','Y','Y') insert into 订单信息表 values('2008110407','1009','2010','10','15.00','现金付款','2008-11-04','Y','Y') insert into 订单信息表 values('2008110408','1001','2001','1','75.00','现金付款','2008-11-04','Y','Y') insert into 订单信息表 values('2008110409','1010','2002','2','210.00','现金付款','2008-11-04','Y','Y') insert into 订单信息表 values('2008110410','1011','2003','1','775.00','首期付款','2008-11-04','Y','N') insert into 客户信息表 values('9001','123456','贾宝玉','1','[email protected]','123456789','贾府','100000','123456789101213789') insert into 客户信息表 values('9002','123456','孙悟空','1','[email protected]','223456789','西游记','100001','023456789101213789') insert into 客户信息表 values('9003','123456','辰南','1','[email protected]','323456789','神墓','200000','023456789101213780') insert into 客户信息表 values('9004','123456','武松','1','[email protected]','123456789','汴梁城','300000','923456789101213789') insert into 客户信息表 values('9005','123456','母夜叉','0','[email protected]','523456789','梁山','900000','192345678910121378') insert into 客户信息表 values('9006','123456','漩涡鸣人','1','[email protected]','0023456789','日本','990000','623456789101213789') insert into 客户信息表 values('9007','123456','佐助','1','[email protected]','123456789','日本','600000','782345678910121378') insert into 客户信息表 values('9008','123456','赵子龙','1','[email protected]','113456789','巴蜀','500000','683456789101213789') insert into 客户信息表 values('9009','888888','张小凡','1','[email protected]','123456789','小竹峰','000000','000456789101213789') insert into 客户信息表 values('9010','666666','陆雪琪','0','[email protected]','12340000','飘渺峰','100009','1234567801012137') insert into 销售流水信息表 values('1001','2008110401','9001','2001','1','2008-11-04','10.00','石家庄') insert into 销售流水信息表 values('1002','2008110402','9002','2002','3','2008-11-04','5.00','武汉') insert into 销售流水信息表 values('1003','2008110403','9003','2004','1','2008-11-04','5.00','哈尔滨') insert into 销售流水信息表 values('1004','2008110404','9004','2005','2','2008-11-04','10.00','长沙') insert into 销售流水信息表 values('1005','2008110405','9005','2003','1','2008-11-04','20.00','沈阳') insert into 销售流水信息表 values('1006','2008110406','9006','2008','5','2008-11-04','100.00','长春') insert into 销售流水信息表 values('1007','2008110407','9007','2009','1','2008-11-04','10.00','西安') insert into 销售流水信息表 values('1008','2008110408','9008','2007','7','2008-11-04','10.00','上海') insert into 销售流水信息表 values('1009','2008110409','9009','2006','1','2008-11-04','10.00','广州') insert into 销售流水信息表 values('1010','2008110410','9010','2010','1','2008-11-04','15.00','郑州') insert into dept values('01','总裁办','北京通天大厦1801室') insert into dept values('02','开发部','上海牛人大厦1802室') insert into dept values('03','财务部','北京子进程广场1803室') insert into dept values('04','行政部','北京奉孝大厦1804室') insert into dept values('05','市场部','上海海豚大厦1805室') insert into Emp values('01001','梨花','01','2001-06-18','8050.01','100.00',' ') insert into Emp values('03001','桃花','03','2001-05-18','5000.00','500.00',' ') insert into Emp values('02001','精明','02','2001-04-20','7000.00','600.00',' ') insert into Emp values('02002','能干','02','2001-04-21','5000.00','300.00','02001') insert into Emp values('02003','赶巧','02','2002-10-10','3000.00','600.00','02001') insert into Emp values('04001','狠狠','04','2002-03-01','1000.00','800.00',' ') insert into Emp values('05001','卫星','05','2003-04-01','3000.00',null,' ') insert into Emp values('05002','跑腿','05','2003-04-15','2500.0',null,'05001') sp_rename dept,部门表 sp_rename 部门表,dept select top 50 percent * from dept update dept set Deptloc='北京紫禁城广场1803室' where Deptno ='03';