MySQL数据库项目实例教程 课件 项目三：电商零售系统数据库

MySQL数据库项目三：电商零售系统数据库设计数据库数据库的基础知识MYSQL数据库物理设计逻辑设计概念设计目录需求分析数据库实施创建数据表需求分析通过调查、收集与分析，获得用户对数据库的基本要求本次课程的目的是设计一个电商平台。网上购物已成了现在我们生活中的主要购物方式。网上购物的过程当中，数据库是如何实现的？主要的工作主要发生在哪些过程之间？网上购物的核心是什么？登录用户系统用户需求商户注

册售卖店铺的内容会发布在对应的域名上审核发布访问店铺浏览商品下单支付成功查看订单跟踪物流确认收货顾客注册登录店铺浏览商品提交订单支付成功商家发货购物流程图根据需求分析的结果数据库店铺顾客商品订单创建数据表实体“顾客”的属性顾客收货地址昵称手机号码顾客ID注册时间密码创建数据表实体“店铺”的属性店铺更新时间店铺ID创建时间店铺名称店铺法人客服信息联系电话商品库存价格商品描述条形码商品名称商品ID销量创建时间更新时间实体“商品”的属性创建时间数量商品ID订单名称支付时间快递单号订单ID顾客ID实体“订单”的属性订单顾客购买订单1n1:n关系顾客浏览店铺n1n:1关系店铺管理商品1n1:n关系订单包含商品mnm:n关系顾客购买订单1n1:n关系顾客可以生成多个订单每个订单只属于一个顾客顾客浏览店铺n1n:1关系店铺管理商品1n1:n关系订单包含商品mn1:n关系完整的基本ER图顾客商品订单店铺购买浏览包含管理nnn111nm收货地址注册时间密码昵称手机ID（省略其他实体的属性）完整的基本ER图顾客商品订单店铺购买浏览包含管理nnn111nm收货地址注册时间密码昵称手机ID（省略其他实体的属性）下面通过逻辑设计、物理设计来继续完成电商购物系统的数据库。创建数据表逻辑结构设计根据按照ER图到关系结构的转换规则，把er图转换成关系模式表也就是说，将每个实体类型转换成一个实体的关系模式创建数据表实体的属性转换的过程当中关系的属性实体的标志符关系模式的码对关系表进行第3范式的规范化顾客信息表顾客收货地址注册时间密码昵称手机号码ID用户昵称用户ID密码手机号码注册时间收货地址顾客收货地址注册时间密码用户昵称手机号码用户ID顾客信息表订单ID订单名称商品ID数量创建时间顾客ID支付时间快递单号订单顾客ID创建时间数量品ID订单ID订单名称支付时间快递单号订单信息表商品ID订单ID顾客ID店铺信息表店铺ID店铺名称法人信息客服信息联系电话创建时间更新时间商品信息表商品ID商品名称条形码商品描述价格库存销量创建时间更新时间创建数据表熟练掌握了第三范式的规范，在设计ER图的时候就有下意识的直接将ER图设计成符合第三范式规范的关系表第三范式规范这个步骤会在潜意识里直接在概念设计时一并完成创建数据表根据数据库的存储和方法表上有哪些属性？选取合适的数据类型这些属性需要用什么样的数据格式来存储?顾客信息表字段名称数据类型字段说明顾客昵称varchar(30)用户名顾客IDchar(6)用户唯一ID，主键密码char(32)保存加密后的签名手机号码char(11)手机号注册时间DATETIME时间戳收货地址DATETIME时间戳店铺信息表字段名称数据类型字段说明店铺IDchar(11)店铺唯一ID，主键店铺名称varchar(30)店铺名法人信息varchar(30)店主名客服信息varchar(64)联系电话char(11)创建时间DATETIME时间戳更新时间DATETIME时间戳商品信息表字段名称数据类型字段说明商品IDChar(6)商品唯一ID，主键商品名称varchar(30)商品名条形码varchar(60)图像URL商品描述varchar(60)文字描述价格Int(32)数字库存Int(32)数字销量Int(32)数字创建时间DATETIME时间戳更新时间DATETIME时间戳订单信息表字段名称数据类型字段说明订单IDchar(6)店铺唯一ID，主键订单名称varchar(30)店铺名商品IDchar(6)商品唯一ID数量int(32)数字创建时间DATETIME顾客IDchar(6)用户唯一ID支付时间DATETIME支付时间快递IDchar(15)快递唯一ID数据库安全从其根本来说就是对信息进行保护，其整体的覆盖层面非常广，通常包括防火墙、入侵防御、风险评价等各个方面的技术手段，而通常储存数据信息主要是通过一些数据库实现的，但是一般数据库自身的安全性经常被忽视。由于社会的快速发展，我国经济水平的不断提升使得我国计算机的相关应用也越来越广泛，数据库系统当中有存储着大量的信息，包括个人信息、企业信息甚至国家信息等，这也就意味着，一旦数据库出现问题，其中的庞大的信息就会逐渐被破坏掉，对国家和社会都会造成非常大的损失。由于社会的快速发展，我国经济水平的不断提升使得我国计算机的相关应用也越来越广泛，数据库系统当中有存储着大量的信息，包括个人信息、企业信息甚至国家信息等，这也就意味着，一旦数据库出现问题，其中的庞大的信息就会逐渐被破坏掉，对国家和社会都会造成非常大的损失。数据库安全从其根本来说就是对信息进行保护，其整体的覆盖层面非常广，通常包括防火墙、入侵防御、风险评价等各个方面的技术手段，而通常储存数据信息主要是通过一些数据库实现的，但是一般数据库自身的安全性经常被忽视。由于社会的快速发展，我国经济水平的不断提升使得我国计算机的相关应用也越来越广泛，数据库系统当中有存储着大量的信息，包括个人信息、企业信息甚至国家信息等，这也就意味着，一旦数据库出现问题，其中的庞大的信息就会逐渐被破坏掉，对国家和社会都会造成非常大的损失。网络数据库的隐私和安全的需要通过数据本身的查询隐私以及机密性等两个角度出发。

通常数据的机密性主要指的是在对一些信息和隐私进行设计的过程中，数据库需要实施更加安全的机制，从而保证整体的数据信息自身的安全性，在没有任何授权的情况之下所有经过处理的信息和数据是无法被外界访问的。

而关于隐私保护的查询，主要指的是在对某一种东西实施查询的过程当中不会被其他人掌控。在现阶段的社会背景之下，数据库的隐私和安全对人们日常生活的各种方面都有着一定程度的影响，就企业来说，对于一个数据库的整体信息的应用和掌握的能力可以决定企业自身能够产生的利润程度，以及可以获得重视消费者的数量，但是就一些比较普通的消费者来说，在很多的情况之下，消费者都是进行间接的接触的，但是对于这一类的消费者来说，消费者自身也会有自己信息被泄露的情况出现。感谢观看MySQL数据库项目三：电商零售系统数据库创•建•数•据•库根据物理设计的结果，将在Sqlyog中完成数据库的基本操作数据库的操作:查看数据库创建数据库选择数据库修改数据库删除数据库对象资源管理器information_schema数据库performance_schema数据库mysql数据库sys数据库实例数据库world实例数据库Sakilainformation_schema数据库保存了MySQL服务中数据库的所有信息包括具体MySQL服务有多少个数据库各个数据库有哪些表各个表中的字段是什么数据类型各个表中有哪些索引各个数据库要什么权限才能访问Mysql数据库是mysql服务的核心数据库类似于sqlserver中的master表Mysql数据库主要负责存储数据库的用户、权限设置、关键字等包含了关于数据库对象元数据（metadata）的数据字典表和系统表performance_schema数据库主要用于收集MySQL服务的性能参数并且库里表的存储引擎均为performance_schema

，而用户是不能创建存储引擎为performance_schema的表在较低级别监控MySQL服务器执行的功能sys数据库MySQL从MySQL5.7开始引入了sys数据库其中包含了一系列的视图，可以方便为DBA和开发人员解释performance_schema性能数据库中收集的数据实例数据库world和sakilaSakila

实例数据库是一个在线DVD出租商店数据库为各种MySQL文档、书籍、教程、文章、示例等提供了一个标准数据库模式实例数据库world和sakila同时，它还可以用于演示MySQL的其他功能特性例如视图、存储过程和触发器实例数据库world和sakilaworld实例数据库提供了一组包含世界各国和城市信息的表，对基本查询很有用但缺少测试MySQL5及更高版本中MySQL特定功能和特性的结构修改数据库:在MySQL5.5之后，MySQL就不允许数据库改名了创建数据库时关于数据库的名字一定要慎重后期必须改名，只能通过创建一个新的数据库将现有数据库中所有数据全部复制过去的替代做法实现删除数据库删除数据库是将已经存在的数据库从磁盘空间上清除清除之后，数据库中的所有数据和关联对象将被永久删除，并且无法撤消应该谨慎使用，一旦执行该操作，没有恢复的可能，除非数据库有备份删除数据库MySQL中的数据用各种不同的技术存储在文件（或者内存）中，每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供各不相同的功能这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎（也称作表类型）MySQL提供了多个不同的存储引擎：处理事务安全表的引擎处理非事务安全表的引擎可以使用以下语句查看系统所支持的引擎类型，代码如下：SHOWENGINES;使用SQLyog图形管理工具查看数据库创建数据库选择数据库修改数据库和删除数据库数据库引擎课堂小结感谢观看MySQL数据库项目三：电商零售系统数据库创•建•数•据•表数据表是一种很重要的数据库对象是组成数据库的基本元素由若干个字段组成主要用来实现存储数据记录创建数据库的目的:存储管理查询数据数据表数据表是存储数据最重要的载体数据表是MySQL数据库中最重要的数据库对象也是构建高性能数据库的基础数据表设计的优劣有哪些影响：影响磁盘空间的使用效率数据处理时内存的利用率据的查询效率注意数据表结构的规范化数据类型的正确使用数据库和数据表字符集的统一问题数据表的数据完整性规则保证表中数据的正确性，精确性和可靠性的关键表的操作包含:创建表查看表删除表修改表是数据库对象的表管理中最基本、最重要的操作数据表是数据库最重要的组成部分之一数据库数据库只是一个框架，数据表才是其实质内容任务利用SQLyog图形化管理工具创建数据表一、创建数据表表是包含数据库中所有数据的数据库对象数据在表中的组织方式与在电子表格中相似，都是按行和列的格式组织的其中每一行代表一条唯一

的记录，

每列代表记录中的一个字段，表中的数据库对象包含栏位索引也称为列（Columns）：或称属性列数据表中的每一列称为一个字段表是由其包含的各种字段定义的每个字段描述了它所含有的数据的意义栏

位数据表的设计实际上就是对字段的设计

在具体创建表时，必须指定列的名字和数据类型索引(Indexes)在数据之外，数据库系统还维护着满足特定查找算法的数据结构这些数据结构以某种方式引用数据，这样就可以在这些数据结构上实现高级查找算法帮助MySQL高效的查询出数据的数据结构叫做索引索引是帮助MySQL高效获取数据的数据结构【案例】在数据库ess中创建数据表“用户信息表”字段名称数据类型字段说明用户IDCHAR(6)用户唯一ID，主键用户昵称VARCHAR(30)用户名密码CHAR(6)六位固定长度的密码手机号码CHAR(11)用户的手机号注册时间DATETIME用户的注册时间收货地址VARCHAR(60)用户的收货地址本课小结数据表的概念如何使用SQLyog图形化管理工具创建数据表用户信息表字段名称数据类型字段说明用户IDCHAR(6)用户唯一ID，主键用户昵称VARCHAR(30)用户名密码CHAR(6)六位固定长度的密码手机号码CHAR(11)用户的手机号注册时间DATETIME用户的注册时间收货地址VARCHAR(60)用户的收货地址商品信息表字段名称数据类型字段说明用户IDCHAR(6)用户唯一ID，主键商品名称VARCHAR(30)用户名条形码CHAR(13)条形码数字商品描述VARCHAR(60)文字描述价格INT数字库存INT数字销量INT数字创建时间DATETIME商品入库时间更新时间DATETIME商品情况更新时间订单信息表字段名称数据类型字段说明用户IDCHAR(6)订单唯一ID，主键订单名称VARCHAR(30)店铺名创建时间DATETIME订单创建时间用户IDCHAR(6)用户唯一ID支付时间DATETIME用户支付完成时间快递IDCHAR(15)快递ID订单详情表字段名称数据类型字段说明订单详情IDCHAR(6)订单详情唯一ID，主键订单IDCHAR(6)订单唯一ID商品IDCHAR(6)商品唯一ID数量INT数字感谢观看MySQL数据库项目三：电商零售系统数据库数•据•的•完•整•性•约•束课程回顾如何创建数据库如何创建数据表对于已经创建好的表，虽然字段的数据类型决定了所能存储的数据类型但是表中所存储的数据是否合法并没有进行检查什么叫做数据的完整性约束？MySQL数据库具体使用MySQL数据库时，需要针对表中的数据做一些完整性检查操作MySQL数据库管理系统采用多种方法来保证数据完整性，提供了一种机制来检查数据库表中的数据是否满足规定的条件，以保证数据库表中数据的准确性和一致性对已经创建好的表定义完整性约束创建新表时就进行完整性约束的定义数据的完整性约束MySQL中的数据支持不同的数据类型来处理不同的操作实际生活中字段的取值通常有一个范围如果这些字段被插入不合法的数据，将给系统带来麻烦数据完整性是信息安全的三个基本要点之一传输存储信息存储数据确保不被未授权的篡改篡改后能够被迅速发现数据完整性是指数据的精确性和可靠性为防止数据库中存在不符合语义规定的数据防止因错误信息的输入输出造成无效操作或错误信息而提出的数据完整性分为四类：实体完整性域完整性参照完整性用户自定义完整性实体完整性实体完整性要求每个数据表都必须有主键而作为主键的所有字段，其属性必须是唯一及非空值域完整性域完整性是指一个列的输入有效性，是否允许为空值强制域完整性的方法有：限制类型格式或可能值的范围如：人的年龄不能小于0，人的性别只能是“男”或“女”参照完整性是指保证主关键字和外部关键字之间的参照关系它涉及两个或两个以上表数据的一致性维护如：商品销售系统中用户订单中的商品代码必须是商品库存表中已有的商品代码外键值引用表此外键的记录被引用表中主键与外键相匹配的记录关联用户自定义完整性用户自定义完整性是针对某一具体关系数据库的约束条件它反映某一具体应用所涉及的数据必须满足的语义要求约束就是用来限定表中数据:准确性、完整性、一致性、联动性的一套规则MySql中约束保存在information_schema数据库的table_constraints表中，可以通过该表查询约束信息主键约束非空约束主键约束（PRIMARYKEY）在MySQL中，主键全称“主键约束”，是一个列或多列的组合其值能唯一地标识表中的每一行，通过它可强制表的实体完整性在SQLyog界面上用“主键”表示，如图所示主键的作用是确定该数据的唯一性，主要是用于和其他表的外键关联，以及本记录的修改与删除可能会重复人身份证号姓名不会重复，可以作为主键在数据表中可以通过身份证号确定准确人选，而姓名可能会出现同名据库的单个数据表中只能有一个主键

，不可能有多个主键如果一张表选中多个主键，我们称之为联合主键联合主键就是用多个字段一起作为一张表的主键在创建数据表的时候没有指定主键InnoDB会自动帮创建一个不可见的、长度为6字节的字段row_id由于row_id的长度限制，为了避免这种隐患，每个表在创建时都需要一个主键在SQLyog界面上，创建表的时候直接创建主键新建表的时候，图上列名是“主键”的即为主键列设置方法是在设置好字段名和数据类型后，在主键复选框上选中即可主键是列的唯一标志主键不能为空，在选中主键时将默认选中非空约束设置好后，还可以在右键单击表后弹出界面中选择“改变表”取消或修改主键也可以修改其他的约束，在设置完成后单击“保存”按钮即可执行约束的修改非空约束（NOTNULL）MySQL中非空约束是最简单的约束，用来限制数据填写的完整性被设置了非空约束的字段，在添加数据时必须要有数据，不能够省略如果用户输入记录时，该字段为空值，则系统会报错非空约束使用NOTNULL语句，而允许为空的字段可以使用NULL关键字非空约束限制该字段中的内容不能为空，但可以是空白字符串或0非空约束与其他约束都用来限制数据，但其作用和使用方式大不相同一个表只能够设置一个主键(或主键组)，但非空约束不受限制，而且每个字段的非空约束之间互不影响字段是否非空的两种可能要么允许添加数据为空要么不允许添加数据为空无论字段是否为空，都需要在创建时指出若允许为空则使用NULL否则使用NOTNULL来定义SQLyog图形界面操作非空约束的创建非空约束的修改操作方法和主键约束是一样的课程任务默认值约束自增约束唯一约束外键约束默认值约束（DEFAULT）在MySQL中默认值约束用来给某个字段指定默认值负担一旦设置了默认值约束之后，再插入数据时如果此字段没有显式赋值，则赋值为默认值默认值的使用可以有效降低数据添加的负担例如某校女同学较多，性别就可以默认为“女”如果插入一条新的记录时没有为这个字段赋值那么系统会自动为这个字段赋值为“女”是直接选中复选框创建默认值约束可参考主键约束其他约束是为字段设置一个默认的取值，因此是文本框的形式，将字段的默认取值写入“默认”文本框即可默认值约束默认值可以设置为固定值，也可以设置为通过计算获得的数据系统在添加字段默认值设置时，将根据字段的数据类型提供几个常用的默认值自增约束（AUTO_INCREMENT）自增约束是当给定某个字段该属性之后该列的数据在没有提供确定数据的时候系统会根据之前已经存在的数据进行自动增加填充数据的约束如学生信息表中的学生学号字段可以设置为自增约束字段当添加学生信息时，系统会默认对学号字段值增1自增约束字段由系统自动填入数据，在节省用户工作量和时间的同时，避免因疏忽而加入错误数据（1）自增约束是数据列的一种属性，只适用于整数类型数据列在使用自增约束时，应注意以下几点：（2）自增约束数据列必须有唯一约束，以避免序号重复（3）MySQL表中只能有一个自增约束字段（4）自增字段必须创建索引，如主键索引、唯一索引等（5）有着外键约束的字段不能够设置为自增字段（6）自增约束数据列必须具备NOT

NULL属性自增约束的创建和修改都可以使用SQLyog图形界面操作，其操作方法和主键约束是一样的唯一约束（UNIQUE）MySQL的唯一约束是指所有记录中字段的值不能重复出现为id字段加上唯一性约束后，每条记录的id值都是唯一的，不能出现重复的情况如果其中一条记录的

id

值为0001，那么该表中就不能出现另一条记录的id值也为0001唯一约束与主键约束相似的是它们都可以确保列的唯一性在一个表中可有多个，并且设置唯一约束的列允许有空值，但是由于两个空值也相当于重复，所以只能有一个空值唯一约束在一个表中只能有一个，且不允许有空值。比如，在用户信息表中，为了避免表中用户名重名，可以把用户名设置为唯一约束主键约束外键约束（FOREIGNKEY）完整性约束都是在单表中进行外键约束是要保证多个表（一般是两个表）之间的参照完整性，用来构建两个表中的两个字段之间的约束关系外键约束是表的一个特殊字段，经常与主键约束一起使用外键约束主键所在的表就是主表（也称父表）外键所在的表就是从表（也称子表）对于两个具有关联关系的表而言主表：商品信息表从表：订单详情表定义外键时，需要遵守下列规则：则主表与从表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性主表必须已经存在于数据库中，或者是当前正在创建的表必须为主表定义主键只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的主键不能包含空值，但允许在外键中出现空值主表的表名后面指定列名或列名的组合这个列或列的组合必须是主表的主键或候选键外键中列的数目必须和主表的主键中列的数目相同外键中列的数据类型必须和主表主键中对应列的数据类型相同使用SQLyog图形界面操作创建和修改外键约束的方法：（1）在操作界面上选择“3外部键”，然后在栏位上首先在“约束名”填入外键约束名外键约束的命名为了方便查阅一般规则是：“fk_+从表名+_+主表名”可以自定义名称不自定义名称系统会按照mysql规则自动生成一个名称在左侧“引用列”上通过下拉菜单选择从表上指定的外键列在“引用数据库”和“引用表”中依次指定数据库和主表右侧“引用列”上通过下拉菜单选择主表中的参照约束字段（一般是主键）在“订单详情表”的“商品ID”列上创建外键约束“fk_订单详情_商品信息”外键约束“fk_订单详情_商品信息”的参考主表是“商品信息表”，主表中主键约束字段是“商品ID”数据的约束与人的道德规范数据库中的数据必须遵守数据的完整性约束，才能成为有效的信息木受绳则直，金就砺则利无规矩不成方圆作为数据库的设计者应该:遵守法律和社会的公序良俗必须做到不违反法律不损害社会公共利益不损害他人的合法权益本课小结讲解了数据完整性约束中的主键约束和非空约束、默认值约束、自增约束、唯一约束和外键约束外键约束创建时必须参照主键感谢观看MySQL数据库项目三：电商零售系统数据库操•作•数•据•表数据表数据表是数据库中最基本、最重要的组成元素在表中包含多个数据，对表中数据的操作是最重要的操作表中数据的操作主要包含插入、修改和删除任务如何操作数据表一、插入数据插入数据是数据表中最常见的操作该操作可以向数据表中添加新的数据记录通过SQLyog图形化管理工具在MySQL数据库管理系统中向表中插入数据记录的操作步骤如下：连接MySQL服务“对象资源管理器”窗口数据库ess中表对象“商品信息表”右键单击弹出的菜单中“打开表”命令在打开的表数据窗口中，可以直接输入表数据，依次在对应的字段商品ID商品名称商品信息价格库存销量条形码创建时间更新时间按照字段规定的数据类型和约束条件输入数据插入数据完成后单击下一行即可完成数据输入如果输入的数据不符合数据类型或约束条件，则会出现错误提示错误提示是“商品信息”字段的数据长度超出限制错误提示是新输入的数据主键“500001”跟原有数据主键“500001”发生冲突，违反主键约束二、修改数据修改数据是数据表中最常见的操作，该操作可以修改数据表中原有数据记录通过SQLyog图形化管理工具在MySQL数据库管理系统中修改数据记录的操作步骤如下：连接MySQL服务“对象资源管理器”窗口数据库ess中表对象“商品信息表”右键弹出的菜单中“打开表”命令在打开的表数据窗口中，会显示该表所包含的所有数据记录，如图所示查找字段“价格”该数据记录中字段为“38”的表格同时修改该表格内容为“22”双击值为“38”的数据记录处于编辑状态最后随意单击其他单元格或空白区域即可实现该数据记录值的更新如果输入的数据不符合数据类型或约束条件，则会出现错误提示根据错误提示修改数据后即可完成数据插入错误提示是“商品信息”字段的数据类型不符合要求三、删除数据删除数据是数据表中常见的操作，该操作可以删除数据表中原有数据记录通过SQLyog图形化管理工具在MySQL数据库管理系统中删除数据记录的操作步骤如下：连接MySQL服务“对象资源管理器”窗口数据库ess中表对象“商品信息表”右键弹出的菜单中“打开表”命令在打开的表数据窗口中，会显示该表所包含的所有数据记录，如图所示本课小结使用SQLyog图形化管理工具操作数据表完成数据的插入、修改和删除感谢观看MySQL数据库项目三：电商零售系统数据库MySQL数据库3.3.1使用SQLyog图形管理工具管理用户通过输入账号及密码进行登录后使用思考不同的账号可能会拥有不同的操作权限比如在学生成绩管理系统中，教师账号和学生账号的权限就明显不同那么这些账号式如何管理的呢？MySQL数据库网络课程任务添加用户1删除用户2修改用户密码3在使用应用程序时，注册的账号通常被称为普通用户而负责审核和分配账号等管理操作的账号，被称为管理员用户MySQL数据库网络课程用户分类在MySQL8.0中为了方便对用户信息和权限的管理，用户被分为：root用户超级管理员，对所有数据库拥有所有的权限包括创建用户、删除用户和修改用户的密码等管理权限并且可以创建普通用户普通用户只具有创建该