实验一 完成订单数据库

实验一 完成订单数据库

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