实验一 完成订单数据库
1.创建订单数据库
educoder上
create database orderdb
on primary
(
name='orderdb_data',
filename='/home/orderdb_data.mdf',
size=4mb,
maxsize=100mb,
filegrowth=15%
)
log on
(
name='orderdb_log',
filename='/home/orderdb_log.ldf',
size=2mb,
filegrowth=1mb
)
go
本地上
create database orderdb1800310111
on primary
(
name='orderdb_data',
filename='F:giao-sourcemysqltestorderdb_data.mdf',
size=4mb,
maxsize=100mb,
filegrowth=15%
)
log on
(
name='orderdb_log',
filename='F:giao-sourcemysqltestorderdb_log.ldf',
size=2mb,
filegrowth=1mb
)
go
2.创建数据库表
创建代理商表
create table 代理商
(
代理商编号 char(4) ,
姓名 nvarchar(10) ,
地址 nvarchar(20) ,
邮政编码 char(6) ,
提成金额 smallmoney ,
提成比例 tinyint ,
primary key(代理商编号)
)
go
创建客户表
create table 客户
(
客户编号 char(4) ,
姓名 nvarchar(10) ,
地址 nvarchar(20) ,
邮政编码 char(6) ,
收支差额 smallmoney ,
贷款限额 smallmoney ,
代理商编号 char(4) ,
primary key(客户编号),
foreign key(代理商编号)
references 代理商(代理商编号)
ON DELETE CASCADE
)
go
创建产品表
create table 产品
(
产品编号 char(4) ,
描述信息 nvarchar(20) ,
库存量 int ,
类别 tinyint ,
仓库编号 char(4) ,
价格 smallmoney ,
primary key(产品编号)
)
go
创建订单表
create table 订单
(
订单编号 char(4) ,
订货日期 smalldatetime ,
客户编号 char(4) ,
primary key(订单编号),
foreign key(客户编号) references 客户(客户编号) ON DELETE CASCADE
)
Go
创建订货项目
create table 订货项目
(
订单编号 char(4) ,
产品编号 char(4) ,
订购数量 smallint ,
订购单价 smallmoney ,
primary key(订单编号,产品编号),
foreign key(订单编号) references 订单(订单编号) ON DELETE CASCADE,
foreign key(产品编号) references 产品(产品编号) ON DELETE CASCADE
)
go
3.插入原始记录
插入代理商数据
insert into 代理商
VALUES
('01','联邦','东环路1号','541001',30000.00,40),
('02','惠普','东环路2号','541002',4000.00,10),
('03','三洋','东环路3号','541003',10000.00,30),
('04','联想','东环路4号','541004',100000.00,60),
('05','海尔','东环路5号','541005',200000.00,60)
go
插入客户数据
insert into 客户
VALUES
('100','张三','西环路1号','100001',10.00,100.00,'02'),
('200','李四','西环路2号','100001',-10.00,10.00,'04'),
('300','王五','西环路3号','100001',100.00,1000.00,'02'),
('400','赵六','西环路4号','100001',600.00,2000.00,'01'),
('500','洪七','西环路5号','100001',300.00,900.00,'05'),
('600','李明','西环路6号','100001',20.00,300.00,'03'),
('700','张进','西环路7号','100001',400.00,1000.00,'03')
go
插入产品数据
insert into 产品
VALUES
('0011','药物,单位(瓶)',1000,12,'1001',40.00),
('0022','机器,单位(件)',300,3,'1002',50000.00),
('0033','中药,单位(包)',800,12,'1001',300.00),
('0044','软件,单位(套)',1500,10,'1003',2000.00),
('0055','家具,单位(件)',6000,3,'1002',1000.00),
('0066','小型机,单位(台)',10000,3,'1002',200000.00)
go
插入订单数据
insert into 订单
VALUES
('111','2000-10-01','200'),
('222','2000-09-01','200'),
('333','2001-01-01','500'),
('444','2002-02-02','300'),
('555','2003-03-03','100')
go
插入订货项目数据
insert into 订货项目
VALUES
('111','0033',200,280.00),
('222','0066',6000,150000.00),
('333','0033',100,280.00),
('444','0011',300,39.00),
('555','0055',5500,950.00),
('444','0044',1000,1900.00)
go
3.数据库备份
educoder
backup database orderdb to disk = '/home/mybackupdb.bak'
go
本地上
backup database orderdb1800310111 to disk = 'F:giao-sourcemysqltestmybackupdb.bak'
go
4.数据库还原
educoder
restore database mybackup
from disk = '/home/mybackupdb.bak'
with move 'orderdb_data' to '/home/mybackup_data.mdf',
move 'orderdb_log' to '/home/mybackup_log.ldf'
go
本地上
restore database mybackup
from disk = 'F:giao-sourcemysqltestmybackupdb.bak'
with move 'orderdb_data' to 'F:giao-sourcemysqltestmybackup_data.mdf',
move 'orderdb_log' to 'F:giao-sourcemysqltestmybackup_log.ldf'
go