数据库-建立数据库,建立数据表和录入数据

本关任务：完成代理商、客户、产品和彼此之间的订单数据库。

建数据库、建数据表、录入数据、备份还原

1、需求分析

代理商、客户、产品和彼此之间的关系说明如下：
1．对于代理商，需要存储：代理商编号、姓名、地址、提成金额以及提成的比例。
2．每个客户需要存储：客户编号、姓名、地址、收支差额以及贷款的限额。另外还要存储客户通过的代理编号及姓名。（注：每个代理商可以代理多个客户，而每个客户只能通过一个代理商）。
3．对于产品，需要存储：产品的编号、描述信息、库存量、类别、仓库的编号以及价格。
4．每份订单需要存储：订单编号、订货日期、客户编号、姓名以及地址、通过的代理的编号。而且对于订单的每一个订货项，需要存储产品编号、描述信息、订货的数量以及协商的价钱。
说明：
（1）每份订单的客户详细信息都已经存储在客户文件中。
（2）每份订单只限于一个客户。
（3）每种产品在一份订单中最多出现一次。
（4）协商后的价钱可能和产品管理文件中的相同，但也可能不同。即允许把同一种产品按不同的价格卖给不同的客户，同时也允许修改产品的基本价格，而无须改变文件中的订货信息。

2、数据库设计

根据上面的需求分析

关系模型如下： 1、代理商（代理商编号、姓名、地址、邮政编码、提成金额、提成比例） 其中代理商编号为primary key（主码）

字段名	数据类型	备注
代理商编号	char(4)	primary key
姓名	nvarchar(10)
地址	nvarchar(20)
邮政编码	char(6)
提成金额	smallmoney
提成比例	tinyint

2、客户（客户编号、姓名、地址、邮政编码、收支差额、贷款限额、代理商编号） 其中客户编号为primary key（主码）

字段名	数据类型	备注
客户编号	char(4)	primary key
姓名	nvarchar(10)
地址	nvarchar(20)
邮政编码	char(6)
收支差额	smallmoney
贷款限额	smallmoney
代理商编号	char(4)	foreign key

3．产品（产品编号、描述信息、库存量、类别、仓库的编号、价格） 其中产品的编号为primary key（主码）

字段名	数据类型	备注
产品编号	char(4)	primary key
描述信息	nvarchar(20)
库存量	int
类别	tinyint
仓库编号	char(4)
价格	smallmoney

4．订单（订单编号、订货日期、客户编号）

字段名	数据类型	备注
订单编号	char(4)	primary key
订货日期	Smalldatetime
客户编号	char(4)	foreign key

5．订货项目（订单编号、产品编号、订购数量、订购单价）

字段名	数据类型	备注
订单编号	char(4)	primary key ， foreign key
产品编号	char(4)	primary key， foreign key
订购数量	smallint
订购单价	smallmoney

注意：这里不是有两个主键，没有数据表会有两个主键。这里是联合主键，也就是说通过字段订单编号和产品编号来确认一条记录的唯一性。primary key(订单编号,产品编号)

sql语句

/***********注意：只是在适当的位置根据提示填写代码，请不要删除现有代码**********/

/***********BEGIN:删除现存订单数据库**********/
use master
go
if exists(select * from sysdatabases WHERE name = 'orderdb')
drop database orderdb
go
/***********END:删除现存订单数据库**********/


/*****TODO: 请在下面创建名为orderdb的订单数据库*******************/
/*   该订单数据库的数据文件具体描述如下
     name='orderdb_data',  -- 主数据文件的逻辑名称
     filename='/home/orderdb_data.mdf', -- 主数据文件的物理名称
     size=5mb, --主数据文件的初始大小
     maxsize=100mb, -- 主数据文件增长的最大值
     filegrowth=15%--主数据文件的增长率

	 该订单数据库的日志文件具体描述为
	 name='orderdb_log',
     filename='/home/orderdb_log.ldf',
     size=2mb,
     filegrowth=1mb
*/
/*******************BEGIN:创建订单数据库**********************/
CREATE DATABASE orderdb
on primary(
	name='orderdb_data',
	filename='/home/orderdb_data.mdf',
	size=5mb,
	maxsize=100mb,
	filegrowth=15%
)
log on
(
	name='orderdb_log',
	filename='/home/orderdb_log.ldf',
	size=2mb,
	filegrowth=1mb
)
go


/*********************END:订单数据库orderdb创建结束 *******************/

/********************  支持中文 **********************/
ALTER DATABASE orderdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go
ALTER DATABASE orderdb COLLATE Chinese_PRC_90_CI_AS
go
ALTER DATABASE orderdb SET MULTI_USER
go


/********使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息*********/
SET NOCOUNT ON
go

/************************使用orderdb*********************/
USE orderdb
go

/***************TODO: 请在下面创建5张数据表************************/
/*
例子1：订单表
create table  订单
(
订单编号 char(4) ,
订货日期 smalldatetime ,
客户编号 char(4),
primary key(订单编号),--在表级定义实体完整性
foreign key(客户编号) references 客户(客户编号)--在表级定义参照完整性
)
go
*/
/**************BEGIN:创建数据表**************/
/*TODO:1、创建名为代理商的数据表*/
CREATE TABLE 代理商(
	代理商编号 char(4),
	姓名 nvarchar(10),
	地址 nvarchar(20),
	邮政编码 char(6),
	提成金额 smallmoney,
	提成比例 tinyint,
	primary key(代理商编号)
)
go


/*TODO:2、创建名为客户的数据表*/
CREATE TABLE 客户(
	客户编号 char(4),
	姓名 nvarchar(10),
	地址 nvarchar(20),
	邮政编码 char(6),
	收支差额 smallmoney,
	贷款限额 smallmoney,
	代理商编号 char(4),
	primary key(客户编号),
	foreign key(代理商编号) references 代理商(代理商编号)
)
go

/*TODO:3、创建名为产品的数据表*/
CREATE TABLE 产品(
	产品编号 char(4),
	描述信息 nvarchar(20),
	库存量 int,
	类别 tinyint,
	仓库编号 char(4),
	价格 smallmoney,
	primary key(产品编号)
)
go

/*TODO:4、创建名为订单的数据表*/

CREATE TABLE 订单(
	订单编号 char(4),
	订货日期 smalldatetime,
	客户编号 char(4),
	primary key(订单编号),
	foreign key(客户编号) references 客户(客户编号)
)
go


/*TODO:5、创建名为订货项目的数据表*/
CREATE TABLE 订货项目(
	订单编号 char(4),
	产品编号 char(4),
	订购数量 smallint,
	订购单价 smallmoney,
	CONSTRAINT orderdb_key primary key(订单编号,产品编号),
	foreign key(订单编号) references 订单(订单编号),
	foreign key(产品编号) references 产品(产品编号)
)
go


/**************	END:创建数据表结束 ****************/

/***************TODO: 请在下面给表中插入数据************************/
/*
例子1，插入一条记录：
insert into  订货项目
VALUES 
('444','0044',1000,1900.00)
go
例子2，插入多条记录：
insert into  订货项目
VALUES 
('555','0055',5500,950.00),
('444','0044',1000,1900.00)
go
*/
/**************BEGIN:插入数据**************/
/*TODO:1、插入数据到名为代理商的数据表中*/
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

/*TODO:2、插入数据到名为客户的数据表中*/
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
/*TODO:3、插入数据到名为产品的数据表中*/
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
/*TODO:4、插入数据到名为订单的数据表中*/
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
/*TODO:5、插入数据到名为订货项目的数据表中*/
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
/****************END:插入数据结束**********************/

/****************TODO：请在下面写SQL语句备份orderdb数据库***********************/
backup database orderdb to disk = '/home/mybackupdb.bak'
go
/****************END：完成订单数据库备份********************************/

/**************TODO：请在下面写SQL语句还原orderdb数据库为mybackup*************/
restore database mybackup
from disk='/home/mybackupdb.bak'
 WITH move 'orderdb_data'
 to '/home/orderdb_data_backup.mdf',
 move 'orderdb_log'
 to '/home/orderdb_log_backup.ldf'
 go
/****************END：完成还原数据库备份********************************/

/***************  测试 ，请不要删除下面代码********/
SELECT name FROM sys.databases WHERE name='mybackup'
go
USE mybackup
go
select * from 代理商
go
select * from 客户
go
select * from 产品
go
select * from 订单
go
select * from 订货项目
go