《数据库原理》课程设计
原文件链接
所需sql文件
摘 要
数据库技术是现代信息科学与技术的重要组成部分，是计算机数据处理与信息管理系统的核心。数据库技术研究和解决了计算机信息处理过程中大量数据数据有效地组织和存储的问题，在系统数据库中减少数据存储冗余、实现数据共享、保障数据安全以及高效地检索和处理数据。它几乎涉及所有领域，从小型事务处理到大型信息系统，从小型超市的简单信息存储，到大型企业的庞大数据服务器信息存储，以及电子商务乃至地理信息系统等，都用到了数据库技术。
我所要做的智慧超市管理系统可以满足顾客、合作商、智慧超市管理人员等方面的需求。顾客用户需求具体体现在各种商品信息的提供、保存、更新和查询，这就要求数据库结构能充分满足各种信息的输入输出。
关键词：IBM DB2；数据库设计；智慧超市管理系统
目录
第一章 绪论 1
1.1课题简介 1
1.2设计目的 1
1.3设计内容 1
第二章 需求分析 3
2.1需求分析的任务 3
2.2需求分析的步骤 3
2.3需求分析处理对象 3
2.4数据字典 5
第三章 概念结构设计 9
3.1概念结构设计的方法与步骤 9
3.1.1 概念结构设计的方法 9
3.1.2 概念模型的主要特点 9
3.1.3 概念结构设计的步骤 9
3.2数据抽象与局部视图设计 9
第四章 逻辑结构设计 14
4.1 E-R图向关系模型的转换 14
4.2 数据模型的优化 14
4.3 数据库的结构 15
第五章 数据库的实施与运行 20
（一）数据库的创建和使用 20
（二）数据表的创建 20
（三）基本数据的添加 23
（四）视图的创建及使用 28
（五）安全性的定义实现 31
（六）存储过程的定义实现 31
（七）触发器的定义实现 33
（八）其他模块设计的操作语句 34
（九）数据库的备份与恢复 37
（十）设计总结及心得体会 38
第一章 绪论
1.1课题简介
随着现代科技水平的高速发展，庞大的数据需要更多的人力、更快的技术来保证系统数据的正常运转，传统手账数据处理模式的低效率逐渐出现弊端，而如何解决这样的弊端，并提高工作效率，就成为了一个非常重要的问题。而建立数据库管理系统就是一个很好的解决办法。
经过几年的学习，我们对计算机方面的知识有了很大提升，本着实践出真知，通过学校提供的这次数据库设计实践机会，在指导老师得帮助下，经历期末课程实践的规划以及总结，我进行了智慧超市管理系统数据库的设计，在下面的各章中，我将以这套智慧超市管理系统的数据库设计为例，谈谈其设计和开发过程和所涉及到的问题。
1.2设计目的
通过对数据库系统的理论学习，通过上机实践的方式以及联系实际问题给出合理的解决方案，并通过所学知识，将理论与实践相结合，巩固所学知识，并提升个人在处理问题上的能力。
理论的学习，让我对数据库系统有了基础的了解，对于数据库系统的建立、运行等有了比较深刻的认识，为了更加熟练掌握数据库系统的设计流程，以及对数据库系统建立的原理有更加深刻的认识，并且完成本次课程设计，我将通过建立一个智慧超市管理系统，来巩固在课堂教学中对于数据库的学习，以便于熟练掌握对于给定实际问题，建立关系数据库信息系统的各个流程步骤，包括系统调研、需求分析、结构设计、逻辑分析、物理设计、数据库实施以及运行和维护的一般过程，为毕业设计打下良好的基础。
1.3设计内容
智慧超市管理系统可以满足顾客、超市员工等方面的需要。顾客的需求具体体现在各种信息的提供、保存、更新和查询。
智慧超市管理系统其主要功能有以下几点：
设置员工信息功能：设置员工号，姓名，性别，年龄，入职日期和职位等。
设置商品信息功能：设置商品编号，商品名称，商品类别，商品单位，商品规格，生产厂商，生产日期等。
设置客户信息功能：设置会员编号，姓名，性别，出生日期，联系方式，级别等。
设置进货信息功能：进货单号，进货日期，进货地点，经办人编号，供货商编号，商品编号，商品进价，商品数量等。
设置仓库信息功能：库编号，库名，库地点，负责任编号，库存数量，备注，状态等。
设置销售信息功能：设置销售单号，销售日期，销售人编号，销售价格，销售数量，备注等；
设置供货商信息功能：设置供货商编号，供货商名称，联系方式，地址，备注等。
设置出库信息功能：设置出库单编号，出库日期，经办人编号，商品编号，商品数量等。
第二章 需求分析
2.1需求分析的任务
需求分析的任务是调查应用领域，对应用领域的各种信息需求进行详细的分析，形成分析文档，具体是调查、收集和分析用户在数据管理中的信息要求、处理要求、数据的安全性与完整性要求。
为了完成需求分析的任务，要详细调查数据库各个部门的情况，了解实际运行过程中的需求，工作原理，适当的在已有的需求上进行增减，并考虑日后可能增加和剔除的功能部分，将其总结形成分析文档，便于后面具体建立数据库打下基础。
2.2需求分析的步骤
进行需求分析首先是调查清楚用户的实际要求，与用户达成共识，然后分析和表达这些需求。调查用户具体需求的步骤如下：
（1）调查组织机构情况
（2）调查各部门的业务活动情况
（3）分析用户的业务需求
（4）确定新系统边界
常用的调查方法有：
（1）跟班作业
（2）开调查会
（3）请专人介绍
（4）询问
（5）设计调查表，请用户填写
（6）查阅记录
2.3需求分析处理对象
经过可行性分析以及初步的需求调查，经过一定的系统规划，在初步分析的基础上进行略微的调整，使其在处理具体问题中更加规范。
经过可行性分析和初步的需求调查，抽象出智慧超市管理系统业务流程图。
图 2 1 超市管理系统流程图
（1）调查用户需求
基于上述功能需求，通过进一步了解，超市管理业务规则如下：
（1）通过超市管理员可以录入员工信息包括但不限于员工入职日期等信息。
（2）超市管理员通过洽谈与供货商达成合作，对应职责员工负责对接进货信息等。
（3）超市管理员可以对对应员工信息进行查询、更新、增加、删除、修改等。
（4）商品信息的录入、更新、修改、删除、查询等，并对变动的情况进行记录和修改。
（5）对商品库存信息进行增加、更新、修改、删除，并可以在商品数量低于一定值时进行预警。
（6）按商品进货管理查询和对商品的变动以及商品的销售表的查询来查询商品的销售情况。
（7）通过对损坏商品信息的查询以及对损坏商品对应的供应商查询来了解对应的供应商信息，以及对应的负责任信息。
（8）会员用户通过累计的会员积分可以进行会员活动的参与。
（9）统计各商品的销售排行、销售金额、盈利等。
（2）处理对象
结合该实例具体情况，给出要处理的对象商品信息表、进货表、仓库表、销售表、供应商表、客户基本信息表、出库表、员工表等八个方面，各个对象包括的信息如下所示：
1.商品信息表：包括 商品编号、商品名称、商品类别、商品单位、商品规格、生产厂商、生产日期等信息。
2. 进货表：包括 进货单号、进货日期、进货地点、经办人编号、供货商编号、商品进价、库编号、商品数量等信息。
3.仓库表：包括 库编号、库名、库地点、负责任编号、商品编号、库存数量、备注等信息。
4.销售表：包括 销售单号、销售日期、销售人编号、商品编号、商品售价、所售商品数量、备注等信息。
5.供货商：包括 供货商编号、供货商名称、联系人、地址、备注等信息。
6.客户基本信息表：包括 会员编号、姓名、性别、生日、身份证号、联系方式、级别等信息。
7.出库表：包括 出库单编号、出库日期、经办人编号、仓库编号、商品编号、商品数量、状态等信息。
8.员工表：包括 员工号，姓名，性别，年龄，入职日期，职位等信息。
（3）处理功能要求
系统主要完成一下几个功能：
前台业务：
1.商品录入
2.商品扫描收银
3.商品价格等信息查询
4.会员积分活动查询
5.员工信息管理
后台业务：
1.进货管理
2.销售管理
3.库存管理
4.损坏商品处理
5.会员信息管理
6.仓库出入信息管理
2.4数据字典
采用自顶向下的结构化分析方法（SA方法）。首先，定义全局概念结构的框架。
图 2 2 用户需求系统总框架图
进一步细化各子系统。
图 2 3 前台信息系统细化
图 2 4 后台信息系统细化
图 2 5 商品信息系统细化
图 2 6 客户信息系统细化
图 2 7 销售信息系统细化
图 2 8 员工信息系统细化
图 2 9 进货信息系统细化
图 2 10 仓库信息系统细化
图 2 11 出库信息系统细化
图 2 12 供应商信息系统细化
经分析之后，本系统要用到八个基本表：商品信息表、进货表、仓库表、销售表、供应商表、客户基本信息表、出库表和员工表。但是经过考虑，避免数据过于密集造成冗余，对进货表、仓库表、销售表和出库表再进行细分，各分出一个子表，分别为：进货子表、仓库子表、销售子表以及出库子表。数据结构定义如下表所示。
表2-1 数据结构定义
数据结构名 含义说明 组成
商品信息表 定义了商品的基本信息 商品编号，商品名称，商品类别，商品单位，商品规格，生产厂商，生产日期
进货表 定义了进货的基本信息 进货单号，进货日期，进货地点，员工号，供货商编号
进货子表 定义了进货商品的基本信息 进货单号，商品编号，商品进价，库编号，商品数量
仓库表 定义了仓库的基本信息 库编号，库名，库地点，员工号，备注，状态
仓库子表 定义了仓库商品的基本信息 商品编号，库编号，库存数量
销售表 定义了销售的基本信息 销售单号，销售日期，员工号，备注，状态
销售子表 定义了销售商品的基本信息 销售单号，商品编号，商品售价，所售商品数量,会员编号
供货商 定义了供应商的基本信息 供货商编号，供货商名称，联系人，地址，备注，状态
客户基本信息表 定义了顾客的基本信息 会员编号，姓名，性别，出生日期，身份证号，联系方式，级别
出库表 定义了出库的基本信息 出库单编号，出库日期，员工号，仓库编号，状态
出库子表 定义了出库商品的基本信息 出库单编号，商品编号，商品数量
员工表 定义了员工的基本信息 员工号，姓名，性别，年龄，入职日期，职位
第三章 概念结构设计
3.1概念结构设计的方法与步骤
3.1.1 概念结构设计的方法
（1）自顶向下 即首先定义全局概念结构的框架，然后逐步细化。
（2）自底向上 即首先定义各局部应用的概念结构，然后将他们集合起来得到全局概念模型。
（3）逐渐扩张 首先定义最重要的核心概念结构，然后向外扩充，以滚雪球的方式逐步生成其他的概念结构，直至总体概念结构。
（4）混合策略 即将自顶向下和自底向上相结合，用自顶向下策略设计一个全局概念结构的框架，以它为骨架继承由自底向上策略中设计的各局部概念结构。
3.1.2 概念模型的主要特点
（1）能真实、充分的反映出现实世界，包括事物和事物之间的联系，能满足用户对数据的处理要求，是现实世界的一个真实模型。
（2）易于理解，可以用它和不熟悉计算机的用户之间进行交流，用户的积极参与是数据库设计成功的关键。
（3）易于更改，当应用环境和应用要求改变时，容易对概念模型进行修改和补充。
（4）易于向关系、网状、层次等各种数据模型转换。
3.1.3 概念结构设计的步骤
第一步是抽象数据并设计局部视图。
第二步是集成局部视图，得到全局的概念结构。
3.2数据抽象与局部视图设计
设计局部E-R图
（1）设计实体属性图
图 3-1 商品信息E-R图
图 3-2 进货信息E-R图
图 3-3 仓库信息E-R图
图 3-4 销售信息E-R图
图 3-5 客户信息E-R图
图 3-6 出库信息E-R图
图 3-7 供货信息E-R图
图 3-8 员工信息E-R图
（2）设计实体联系图
图 3-9 实体联系E-R图
（3）完整的E-R图
图 3-10 完整的E-R图
第四章 逻辑结构设计
4.1 E-R图向关系模型的转换
将完整的E-R图转化成关系模型。
商品信息表：{商品编号，商品名称，商品类别，商品单位，商品规格，生产厂商，生产日期；}
进货表：{进货单号，进货日期，进货地点，员工号，供货商编号，商品编号，商品进价，库编号，商品数量；}
仓库表：{库编号，库名，库地点，员工号，备注，状态，商品编号，库编号，库存数量；}
销售表：{销售单号，销售日期，员工号，备注，状态，商品编号，商品售价，所售商品数量；}
供货商：{供货商编号，供货商名称，联系人，地址，备注，状态；}
客户基本信息表：{会员编号，姓名，性别，出生日期，身份证号，联系方式，级别；}
出库表：{出库单编号，出库日期，员工号，仓库编号，状态，商品编号，商品数量；}
员工表：{员工号，姓名，性别，年龄，入职日期，职位；}
4.2 数据模型的优化
按总体结构E-R图转化的关系模型看出该模型最高达到第二范式，将转化的关系模式进行优化，最终达到三范式。
商品信息表：{商品编号，商品名称，商品类别，商品单位，商品规格，生产厂商，生产日期；}
进货表：{进货单号，进货日期，进货地点，员工号，供货商编号；}
进货子表：{进货单号，商品编号，商品进价，库编号，商品数量；}
仓库表：{库编号，库名，库地点，员工号，备注，状态；}
仓库子表：{商品编号，库编号，库存数量；}
销售表：{销售单号，销售日期，员工号，备注，状态；}
销售子表：{销售单号，商品编号，商品售价，所售商品数量，会员编号；}
供货商：{供货商编号，供货商名称，联系人，地址，备注，状态；}
客户基本信息表：{会员编号，姓名，性别，出生日期，身份证号，联系方式，级别；}
出库表：{出库单编号，出库日期，员工号，仓库编号，状态；}
出库子表：{出库单编号，商品编号，商品数量；}
员工表：{员工号，姓名，性别，年龄，入职日期，职位；}
4.3 数据库的结构
数据库基本表总体结构图：
图 4-1 数据库基本表总体结构图
根据总体结构图设计各表的结构。
表 4-1 商品信息表（goods_info）
字段名 字段中文说明 数据类型 备注
goods_id 商品编号 Varchar(20) 主键
goods_name 商品名称 Varchar(20)
goods_type 商品类别 Varchar(20)
goods_unit 商品单位 Varchar(20)
goods_specs 商品规格 Varchar(20)
goods_man 生产厂商 Varchar(20)
goods_produceDate 生产日期 DateTime
表 4-2 进货表 (ingoods)
字段名 字段中文说明 数据类型 备注
ingoods_id 进货单号 Varchar(20) 主键
ingoods_date 进货日期 DateTime
ingoods_address 进货地点 Varchar(20)
worker_id 员工号 Varchar(20)
man_id 供货商编号 Varchar(20)
表 4-3 进货子表 (ingoods_child)
字段名 字段中文说明 数据类型 备注
ingoods_id 进货单号 varchar(20) 主键
goods_id 商品编号 varchar(20)
goods_inPrice 商品进价 Money
base_id 库编号 varchar(20)
goods_number 商品数量 varchar(20)
表 4-4 仓库表 (base)
字段名 字段中文说明 数据类型 备注
base_id 库编号 varchar(20) 主键
base_name 库名 varchar(20)
base_address 库地点 varchar(30)
worker_id 员工号 varchar(20)
base_remarks 备注 varchar(20)
base_state 状态 varchar(20)
表 4-5 仓库子表 (base_child)
字段名 字段中文说明 数据类型 备注
goods_id 商品编号 varchar(20) 主键
base_id 库编号 varchar(20)
base_number 库存数量 varchar(20)
表 4-6 销售表 (sale)
字段名 字段中文说明 数据类型 备注
sale_id 销售单号 varchar(20) 主键
sale_date 销售日期 DateTime
worker_id 员工号 varchar(20)
sale_remarks 备注 varchar(20)
sale_state 状态 varchar(20)
表 4-7销售子表 ( sale_child)
字段名 字段中文说明 数据类型 备注
sale_id 销售单号 varchar(20) 主键
goods_id 商品编号 varchar(20)
goods_price 商品售价 Money
sale_number 所售商品数量 varchar(20)
vip_id 会员编号 varchar(20)
表 4-8 供货商 (man)
字段名 字段中文说明 数据类型 备注
man_id 供货商编号 varchar(20) 主键
man_name 供货商名称 varchar(20)
man _linkman 联系人 varchar(20)
man_address 地址 varchar(30)
man_remarks 备注 varchar(20)
man_state 状态 varchar(20)
表 4-9 客户基本信息表 ( vip_info )
字段名 字段中文说明 数据类型 备注
vip_id 会员编号 varchar(20) 主键
vip_name 姓名 varchar(20)
vip_sex 性别 varchar(20)
vip_birthday 出生日期 DateTime
vip_cardId 身份证号 varchar(18)
vip_phone 联系方式 varchar(11)
vip_post 级别 varchar(20)
表 4-10 出库表 ( outbase)
字段名 字段中文说明 数据类型 备注
outbase_id 出库单编号 varchar(20) 主键
outbase_date 出库日期 DateTime
worker_id 员工号 varchar(20)
base_id 仓库编号 varchar(20)
outbase_state 状态 varchar(20)
表 4-11 出库子表 ( outbase_child)
字段名 字段中文说明 数据类型 备注
outbase_id 出库单编号 varchar(20) 主键
goods_id 商品编号 varchar(20)
goods_number 商品数量 varchar(20)
表 4-12 员工表 ( worker)
字段名 字段中文说明 数据类型 备注
worker_id 员工号 varchar(20) 主键
worker_name 姓名 varchar(20)
worker_sex 性别 varchar(20)
worker_age 年龄 Varchar(3)
worker_date 入职日期 DateTime
worker_vip 职位 Varchar(20)
第五章 数据库的实施与运行
（一）数据库的创建和使用
create database Supermarket
use Supermarket
（二）数据表的创建
1.创建商品信息表（goods_info）
create table goods_info(
goods_id varchar(20) primary key ,
goods_name varchar(20),
goods_type varchar(20),
goods_unit varchar(20),
goods_specs varchar(20),
goods_man varchar(20),
goods_produceDate DateTime)
default charset=utf8;
2.创建进货表（ingoods）
create table ingoods(
ingoods_id varchar(20) primary key ,
ingoods_date DateTime ,
ingoods_address varchar(20),
worker_id varchar(20),
man_id varchar(20))
default charset=utf8;
3.创建进货子表（ingoods_child）
create table ingoods_child(
ingoods_id varchar(20) primary key ,
goods_id varchar(20),
goods_inPrice varchar(20),
base_id varchar(20),
goods_number varchar(20))
default charset=utf8;
4.创建仓库表（base）
create table base(
base_id varchar(20) primary key ,
base_name varchar(20),
base_address varchar(30),
worker_id varchar(20),
base_remarks varchar(20),
base_state varchar(20))
default charset=utf8;
5.创建仓库子表（base_child）
create table base_child(
goods_id varchar(20) primary key ,
base_id varchar(20),
base_number varchar(20))
default charset=utf8;
6.创建销售表（sale）
create table sale(
sale_id varchar(20) primary key ,
sale_date DateTime,
worker_id varchar(20),
sale_remarks varchar(20),
sale_state varchar(20))
default charset=utf8;
7.创建销售子表（sale_child）
create table sale_child(
sale_id varchar(20) primary key ,
goods_id varchar(20),
goods_price varchar(20) ,
sale_number varchar(20),
vip_id varchar(20))
default charset=utf8;
8.创建供货商表（man）
create table man(
man_id varchar(20) primary key ,
man_name varchar(30),
man_linkman varchar(20),
man_address varchar(30),
man_remarks varchar(20),
man_state varchar(20))
default charset=utf8;
9.创建客户基本表（vip_info）
create table vip_info(
vip_id varchar(20) primary key ,
vip_name varchar(20),
vip_sex varchar(20),
vip_birthday DateTime,
vip_cardId varchar(18),
vip_phone varchar(11),
vip_post varchar(20))
default charset=utf8;
10.创建出库表（outbase）
create table outbase(
outbase_id varchar(20) primary key ,
outbase_date DateTime ,
worker_id varchar(20),
base_id varchar(20),
outbase_state varchar(20))
default charset=utf8;
11.创建出库子表（outbase_child）
create table outbase_child(
outbase_id varchar(20) primary key ,
goods_id varchar(20),
goods_number varchar(20))
default charset=utf8;
12.创建员工表（worker）
create table worker(
worker_id varchar(20) primary key,
worker_name varchar(20),
worker_sex varchar(20),
worker_age varchar(20),
worker_date DateTime,
worker_vip varchar(20))
default charset=utf8;
（三）基本数据的添加
1.向goods_info表中添加数据
insert into goods_info(goods_id,goods_name,goods_type,goods_unit,goods_specs,goods_man, goods_produceDate)
values(‘SP00001’,‘苹果’,‘食品’,‘kg’,‘个’,‘北京水果批发市场’,‘2021/10/1’),
(‘SP00002’,‘香蕉’,‘食品’,‘kg’,‘个’,‘海南水果批发市场’,‘2021/10/1’),
(‘SP00003’,‘柠檬’,‘食品’,‘kg’,‘个’,‘江苏水果批发市场’,‘2021/10/1’),
(‘SP00004’,‘橙子’,‘食品’,‘kg’,‘个’,‘上海水果批发市场’,‘2021/10/1’),
(‘SP00005’,‘雨伞’,‘生活用品’,‘个’,‘个’,‘南京生活用品批发市场’,‘2021/10/1’),
(‘SP00006’,‘手帕纸’,‘生活用品’,‘包’,‘个’,‘北京生活用品批发市场’,‘2021/10/1’),
(‘SP00007’,‘凉席’,‘生活用品’,‘个’,‘个’,‘苏州生活用品批发市场’,‘2021/10/1’),
(‘SP00008’,‘茶杯’,‘生活用品’,‘个’,‘个’,‘苏州生活用品批发市场’,‘2021/10/1 12:00:00’)
2. 向ingoods表中添加数据
insert into ingoods(ingoods_id,ingoods_date,ingoods_address,worker_id,man_id)
values(‘JH00001’,‘2021/11/10’,‘北京水果批发市场’,‘YGH00007’,‘GHS00001’),
(‘JH00002’,‘2021/11/10’,‘海南水果批发市场’,‘YGH00007’,‘GHS00002’),
(‘JH00003’,‘2021/11/10’,‘江苏水果批发市场’,‘YGH00007’,‘GHS00003’),
(‘JH00004’,‘2021/11/10’,‘上海水果批发市场’,‘YGH00007’,‘GHS00004’),
(‘JH00005’,‘2021/11/10’,‘南京生活用品批发市场’,‘YGH00008’,‘GHS00005’),
(‘JH00006’,‘2021/11/10’,‘北京生活用品批发市场’,‘YGH00008’,‘GHS00006’),
(‘JH00007’,‘2021/11/10’,‘苏州生活用品批发市场’,‘YGH00008’,‘GHS00007’),
(‘JH00008’,‘2021/11/10’,‘苏州生活用品批发市场’,‘YGH00008’,‘GHS00008’)
3. 向ingoods_chils表中添加数据
insert into ingoods_child(ingoods_id,goods_id,goods_inPrice,base_id,goods_number)
values(‘JH00001’,‘SP00001’,‘2.5’,‘K1’,‘100’),
(‘JH00002’,‘SP00002’,‘1.5’,‘K1’,‘100’),
(‘JH00003’,‘SP00003’,‘2’,‘K1’,‘100’),
(‘JH00004’,‘SP00004’,‘1.3’,‘K1’,‘100’),
(‘JH00005’,‘SP00005’,‘12’,‘K1’,‘50’),
(‘JH00006’,‘SP00006’,‘0.2’,‘K1’,‘500’),
(‘JH00007’,‘SP00007’,‘1.5’,‘K1’,‘20’),
(‘JH00008’,‘SP00008’,‘5’,‘K1’,‘30’)
4. 向base表中添加数据
insert into base(base_id,base_name,base_address,worker_id,base_remarks,base_state)
values(‘K1’,‘弘文库’,‘河南1区’,‘YGH00007’,‘主库’,‘正常运行’),
(‘K2’,‘弘毅库’,‘河南2区’,‘YGH00006’,‘副库’,‘建设中’),
(‘K3’,‘弘新库’,‘河南3区’,‘YGH00008’,‘副库’,‘建设中’)
5. 向base_child表中添加数据
insert into base_child(goods_id,base_id,base_number)
values(‘SP00001’,‘K1’,‘500’),
(‘SP00002’,‘K1’,‘500’),
(‘SP00003’,‘K1’,‘500’),
(‘SP00004’,‘K1’,‘500’),
(‘SP00005’,‘K1’,‘100’),
(‘SP00006’,‘K1’,‘10000’),
(‘SP00007’,‘K1’,‘20’),
(‘SP00008’,‘K1’,‘30’)
6. 向sale表中添加数据
insert into sale(sale_id,sale_date,worker_id,sale_remarks,sale_state)
values(‘XS00001’,‘2019/10/10’,‘YGH00001’,‘无’,‘正常’),
(‘XS00002’,‘2019/10/10’,‘YGH00001’,‘无’,‘正常’),
(‘XS00003’,‘2019/10/10’,‘YGH00001’,‘无’,‘正常’),
(‘XS00004’,‘2019/10/10’,‘YGH00002’,‘无’,‘正常’),
(‘XS00005’,‘2019/10/10’,‘YGH00002’,‘无’,‘正常’),
(‘XS00006’,‘2019/10/10’,‘YGH00003’,‘无’,‘正常’),
(‘XS00007’,‘2019/10/10’,‘YGH00003’,‘无’,‘正常’),
(‘XS00008’,‘2019/10/10’,‘YGH00003’,‘无’,‘正常’)
7. 向sale_child表中添加数据
insert into sale_child(sale_id,goods_id,goods_price,sale_number,vip_id)
values(‘XS00001’,‘SP00001’,‘3.5’,‘0’,‘HY00001’),
(‘XS00002’,‘SP00002’,‘3’,‘20’,‘HY00001’),
(‘XS00003’,‘SP00003’,‘4’,‘10’,‘HY00003’),
(‘XS00004’,‘SP00004’,‘2.7’,‘30’,‘HY00003’),
(‘XS00005’,‘SP00005’,‘20’,‘5’,‘HY00006’),
(‘XS00006’,‘SP00006’,‘1’,‘35’,‘HY00006’),
(‘XS00007’,‘SP00007’,‘40’,‘0’,‘HY00006’),
(‘XS00008’,‘SP00008’,‘15’,‘0’,‘HY00002’)
8. 向man表中添加数据
insert into man(man_id,man_name,man_linkman,man_address,man_remarks,man_state)
values(‘GHS00001’,‘北京水果批发中心’,‘赵大’,‘北京一区1单元4楼1户’,‘VIP’,‘正常’),
(‘GHS00002’,‘海南水果批发中心’,‘钱二’,‘海南三区4单元3楼3户’,‘普通’,‘正常’),
(‘GHS00003’,‘江苏水果批发中心’,‘孙三’,‘江苏三区6单元2楼3户’,‘普通’,‘正常’),
(‘GHS00004’,‘上海水果批发中心’,‘李四’,‘上海八区10单元2楼3户’,‘普通’,‘正常’),
(‘GHS00005’,‘南京生活用品批发中心’,‘王五’,‘南京十四区2单元4楼3户’,‘VIP’,‘正常’),
(‘GHS00006’,‘北京生活用品批发中心’,‘马六’,‘北京七区5单元7楼2户’,‘普通’,‘正常’),
(‘GHS00007’,‘苏州生活用品批发中心’,‘冯七’,‘苏州三区4单元3楼1户’,‘普通’,‘正常’)
9. 向vip_info表中添加数据
insert into vip_info(vip_id,vip_name,vip_sex,vip_birthday,vip_cardId,vip_phone,vip_post)
values(‘HY00001’,‘赵金金’,‘男’,‘1980/10/10’,‘410426198001016010’,‘15516000000’,‘五星钻石会员’),
(‘HY00002’,‘钱木木’,‘男’,‘1975/2/2’,‘410426197502026011’,‘17812300000’,‘一星青铜会员’),
(‘HY00003’,‘孙水水’,‘男’,‘1969/1/8’,‘410426196901086012’,‘15612300000’,‘一星青铜会员’),
(‘HY00004’,‘李妍妍’,‘女’,‘1978/2/23’,‘410426197802236020’,‘18523500000’,‘一星青铜会员’),
(‘HY00005’,‘周圆圆’,‘男’,‘1984/5/8’,‘410426198405086012’,‘17925600000’,‘四星黄金会员’),
(‘HY00006’,‘吴晶晶’,‘女’,‘1986/12/25’,‘410426198612256021’,‘15624500000’,‘一星青铜会员’),
(‘HY00007’,‘李开开’,‘男’,‘2000/1/9’,‘410426200001096013’,‘14515600000’,‘一星青铜会员’),
(‘HY00008’,‘王媛媛’,‘女’,‘1999/1/7’,‘410426199901076025’,‘18945610000’,‘一星青铜会员’)
10. 向outbase表中添加数据
insert into outbase(outbase_id,outbase_date,worker_id,base_id,outbase_state)
values(‘CKD00001’,‘2019/10/30’,‘YGH00007’,‘K1’,‘正常’),
(‘CKD00002’,‘2019/10/30’,‘YGH00007’,‘K1’,‘正常’),
(‘CKD00003’,‘2019/10/30’,‘YGH00007’,‘K1’,‘正常’),
(‘CKD00004’,‘2019/10/30’,‘YGH00007’,‘K1’,‘正常’),
(‘CKD00005’,‘2019/10/30’,‘YGH00008’,‘K1’,‘正常’),
(‘CKD00006’,‘2019/10/30’,‘YGH00008’,‘K1’,‘正常’)
11. 向outbase_child表中添加数据
insert into outbase_child(outbase_id,goods_id,goods_number)
values(‘CKD00001’,‘SP00001’,‘50’),
(‘CKD00002’,‘SP00002’,‘50’),
(‘CKD00003’,‘SP00003’,‘30’),
(‘CKD00004’,‘SP00004’,‘50’),
(‘CKD00005’,‘SP00005’,‘20’),
(‘CKD00006’,‘SP00006’,‘100’)
12. 向worker表中添加数据
insert into worker(worker_id,worker_name,worker_sex,worker_age,worker_date,worker_vip)
values(‘YGH00001’,‘朱大’,‘男’,‘25’,‘2020/12/30’,‘销售’),
(‘YGH00002’,‘秦二’,‘女’,‘26’,‘2020/12/30’,‘销售’),
(‘YGH00003’,‘尤三’,‘男’,‘25’,‘2020/12/30’,‘销售’),
(‘YGH00004’,‘许四’,‘女’,‘26’,‘2020/12/30’,‘销售’),
(‘YGH00005’,‘何五’,‘男’,‘25’,‘2020/12/30’,‘销售’),
(‘YGH00006’,‘吕六’,‘女’,‘26’,‘2020/12/30’,‘仓库管理员’),
(‘YGH00007’,‘施七’,‘男’,‘25’,‘2020/12/30’,‘仓库管理员’),
(‘YGH00008’,‘张八’,‘女’,‘26’,‘2020/12/30’,‘仓库管理员’)
（四）视图的创建及使用
1.使用SQL语句CREATE VIEW建立一个名为goods_sale的视图，显示商品的商品编号、商品名称、销售表中的销售编号，并利用视图查询商品为商品编号为SP00001的销售情况。
create view goods_sale_shitu as select goods_info.goods_id,goods_name,sale_id from goods_info,sale_child where goods_info.goods_id=sale_child.goods_id;
select * from goods_sale_shitu where goods_id=‘SP00001’;
2．基于goods_info表、sale表和sale_child表，建立一个名为goods_sale_child_shitu的视图，视图包括所有销售商品的商品编号、商品名称、销售日期、商品售价。使用视图goods_sale_child_shitu查询商品编号为SP00002的商品的商品售价。
create view goods_sale_child_shitu as select goods_info.goods_id,goods_name,sale_date,goods_price from goods_info,sale,sale_child where goods_info.goods_id=sale_child.goods_id and sale.sale_id=sale_child.sale_id;
select * from goods_sale_child_shitu where goods_id=‘SP00002’;
3．使用SQL语句修改视图goods_sale_child_shitu，显示商品的商品编号、商品名称、商品类型。
alter view goods_sale_child_shitu as select goods_info.goods_id,goods_name,goods_type from goods_info;
4．利用视图goods_sale_child_shitu为goods_info表添加一行数据：商品编号为SP00009、商品名称为AD钙、商品类型为饮料。
insert into goods_sale_child_shitu values(‘SP00009’,‘AD
钙’,‘饮料’);
5．利用视图goods_sale_child_shitu删除商品编号为SP00009的商品记录。
delete from goods_sale_child_shitu where goods_id=‘SP00009’;
6．利用视图goods_sale_child_shitu修改商品名为香蕉的商品的销售价格为2.75。（提示：利用视图操作，不是直接使用视图操作）
alter view goods_sale_child_shitu as select goods_info.goods_id,goods_name,goods_type,goods_price from goods_info,sale_child where goods_info.goods_id=sale_child.goods_id;
update goods_sale_child_shitu set goods_price=‘2.75’ where goods_name=‘香蕉’;
select * from goods_sale_child_shitu where goods_name=‘香蕉’;
7．使用SQL语句删除视图v_stu_c和v_stu_g。
drop view goods_sale_shitu, goods_sale_child_shitu;
（五）安全性的定义实现
1.设置员工朱大对商品信息表,销售表和销售子表有查询权限。
grant select on goods_info,sale,sale_child to ‘朱大’;
2.设置员工秦二只对商品信息表,销售表和销售子表有添加和删除权限。
grant insert,delete on goods_info,sale,sale_child to 秦二;
3.设置每个员工对自己的信息有查询权限。
grant select on worker when user()=name to all;
4.设置员工尤三对员工表有查询权限，对员工级别有更新权限。
grant select,update(worker_vip) on worker to 尤三;
5.设置员工许四对商品信息表有修改结构的权利。
grant alert table on goods_info to 许四;
6.设置员工何五具有对销售表和销售子表的所有权限，并具有给其他员工授权的权限。
grant all priviliges on sale,sale_child to 何五 with grant option;
（六）存储过程的定义实现
1.创建存储过程goods_findid用于传入一个商品编号SP00002，查询该商品的信息。
delimiter
c
r
e
a
t
e
P
R
O
C
E
D
U
R
E
g
o
o
d
s
f
i
n
d
i
d
(
i
n
g
o
o
d
s
i
d
v
a
r
c
h
a
r
(
20
)
)
b
e
g
i
n
s
e
l
e
c
t
∗
f
r
o
m
g
o
o
d
s
i
n
f
o
w
h
e
r
e
g
o
o
d
s
i
d
=
g
o
o
d
s
i
d
;
e
n
d
create PROCEDURE goods_findid(in goodsid varchar(20)) begin select * from goods_info where goods_id=goodsid; end
createPROCEDUREgoodsfindid(ingoodsidvarchar(20))beginselect∗fromgoodsinfowheregoodsid=goodsid;end
call goods_findid(‘SP00002’);
delimiter;
2.创建存储过程，用于传入性别（男或女），显示对应客户的id,返回对应性别的人数。
#delimiter
c
r
e
a
t
e
p
r
o
c
e
d
u
r
e
v
i
p
s
e
x
n
u
m
(
i
n
s
e
x
v
a
r
c
h
a
r
(
2
)
c
h
a
r
a
c
t
e
r
s
e
t
u
t
f
8
,
o
u
t
n
u
m
i
n
t
)
b
e
g
i
n
s
e
l
e
c
t
∗
f
r
o
m
v
i
p
i
n
f
o
w
h
e
r
e
v
i
p
s
e
x
=
s
e
x
;
s
e
l
e
c
t
f
o
u
n
d
r
o
w
s
(
)
i
n
t
o
n
u
m
;
e
n
d
create procedure vip_sexnum(in sex varchar(2) character set utf8,out num int) begin select * from vip_info where vip_sex=sex; select found_rows() into num; end
createprocedurevipsexnum(insexvarchar(2)charactersetutf8,outnumint)beginselect∗fromvipinfowherevipsex=sex;selectfoundrows()intonum;end
#delimiter ;
call vip_sexnum(‘女’,@num);
select @num;
3.查看存储过程vip_sexnum的信息
show create procedure supermarket.vip_sexnum
4.删除存储过程vip_sexnum
drop procedure vip_sexnum;
（七）触发器的定义实现
1.创建触发器insert_or_update_ingoods，设置为当进货商品数量大于0且小于50的时候，设置为50，当进货商品数量小于0时，设置为0；
create trigger insert_or_update_ingoods
before insert on ingoods_child for each row
begin
if new.goods_number<50 and new.goods_number>0 then set new.goods_number=50;
elseif new.goods_number<0 then set new.goods_number=0;
end if;
end;
2.查看已经创建的触发器
show triggers;
3.向入库子表中添加两行输入，一个商品数量为22，一个商品为-12，观察触发器是否被激活并生效。
insert into ingoods_child(ingoods_id,goods_id,goods_inPrice,base_id,goods_number)
values(‘JH000010’,‘SP000010’,‘3.5’,‘K1’,‘22’),
(‘JH000011’,‘SP000011’,‘35’,‘K1’,‘-12’);
4.删除触发器
drop trigger insert_or_update_ingoods;
（八）其他模块设计的操作语句
- 查询出女客户购买了哪些商品? select t1.goods_name,t3.vip_name,t3.vip_sex from goods_info as t1 inner join sale_child as t2 on t1.goods_id=t2.goods_id inner join vip_info as t3 on t2.vip_id=t3.vip_id where t3.vip_sex=‘女’
- 查询出哪些被购买的商品中产地是北京的?intersect也可以用内连接(生产厂家表) select t1.goods_name,t1.goods_man,t2.sale_number from goods_info as t1 inner join sale_child as t2 on t1.goods_id=t2.goods_id inner join sale as t3 on t2.sale_id=t3.sale_id where t2.sale_number!=‘0’ and t1.goods_man like ‘%北京%’
- 查询出哪些人从来没购买过商品? select t2.vip_name,t1.goods_id from sale_child as t1 right outer join vip_info as t2 on t1.vip_id = t2.vip_id where t1.goods_id is null
- 查询出哪些商品从来没被购买过. select t1.goods_name,t2.sale_number from goods_info as t1 inner join sale_child as t2 on t1.goods_id=t2.goods_id where t2.sale_number=‘0’
- 查询出哪些厂家的商品从来没被购买过. select t1.goods_name,t2.sale_number,t1.goods_man from goods_info as t1 inner join sale_child as t2 on t1.goods_id=t2.goods_id where t2.sale_number=‘0’
- 统计出所有商品库存情况. select t3.goods_name,t2.base_number from base as t1 inner join base_child as t2 on t1.base_id=t2.base_id inner join goods_info as t3 on t2.goods_id=t3.goods_id
- 查询出食品类中哪些商品没被购买过.
（九）数据库的备份与恢复
- 使用mysqldump命令备份数据库supermarket的所有表，存于D:\下，文件名为all_supermarket.sql。 mysqldump -u root -h localhost -p supermarket > d:\all_table_supermarket.sql
- 在MySQL服务器上创建数据库student1，使用mysql命令将备份文件all_tables.sql恢复到数据库student1中。 create database supermarket1; 再在CMD命令提示符窗口中执行命令。 mysql -u root -p supermarket1 < d:\all_table_supermarket.sql （十）设计总结及心得体会 对于已经学过数据库基础理论后再来做这样一个课程设计，是比较有意义的，毕竟将理论运用到实践，将虚拟的文字教学，像是放到现实世界里来，将那种虚拟缥缈的数字立体化呈现在我们的脑海中，经过这样一个从理论设计，到创建数据库，以及设计数据库相关对象表，还有基础数据的添加，以及对于视图，触发器的运用，这些实践，虽然在实际操作的时候，有很大的困难，比如画E-R图，需要考虑清楚每个实体对应到的属性，以及各个实体之间的相互联系，都是需要考虑全面的，这些对于数据库其本身的数据正确性，相溶性都是非常重要的。 在经历了几天的斟酌，设计，实践，还有对于数据的揣摩，设计，终于完成了本次课程设计，虽然我觉得自己设计的数据库仍然不是非常完美，但我还是非常高兴，毕竟自己亲手做了，困难虽然有，但现在看来，那些，都是值得的。 希望在以后的学习中能够更好的成长，学无止境，一起加油！
版权归原作者 倾心凝望 所有， 如有侵权，请联系我们删除。