MySQL数据库技术（第3版）课件全套 周德伟 单元1-10 数据模型的规划与设计- 数据库应用

单元1数据模型的规划与设计项目知识要点与目标项目知识要点知识能力目标学时任务1设计数据库关系模型1数据模型2概念模型3E-R图的设计4数据库关系模型的建立任务2数据库设计规范化1关系型数据库范式理论

2数据库规范化实例3综合实例PetStore数据库的设计【典型工作任务】

对数据库系统进行分析，建立E-R图，关系模型【技能要求】

1

设计数据库关系模型2

数据库规范化【知识要求技能训练】1

掌握数据库设计的相关知识2

运用E-R图等工具设计数据库4项目实训创建“员工管理系统”的数据模型2修建茅屋需要设计吗？修建大厦需要设计吗？结论：当数据库比较复杂时我们需要设计数据库为什么需要设计数据库

数据库设计就是将数据库中的数据对象以及这些数据对象之间关系进行规划和结构化的过程任务一设计数据库关系模型为什么需要设计数据库良好的数据库设计节省数据的存储空间能够保证数据的完整性方便进行数据库应用系统的开发糟糕的数据库设计：数据冗余、存储空间浪费内存空间浪费数据更新和插入的异常数据冗余编号姓名年龄民族部门部门位置1王涛33汉族人事管理部行政楼3012李梅27汉族人事管理部行政楼301存在冗余为减少数据查找的麻烦，允许数据有一定的冗余数据冗余：指数据重复的现象1127李梅21133王涛1部门编码民族编码年龄姓名编号回族2汉族1民族民族编码市场营销部2人事管理部1部门部门编码行政楼402行政楼301部门位置数据完整性存在不正确、不准确的数据，数据库“失去了完整性”编码姓名地址销售代码11阿兰海淀黄庄菜园子AC09112东方求败海淀龙王庙鬼楼AC04315张无忌中关村大街3-201AC01113菜鸟特注海淀龙王庙树林AC04114赵可海淀龙王庙树林AC01215张无忌中关村大街2-101AC011姓名不同的地址!数据完整性：指数据的准确性

设计数据库关系模型概念模型:把现实世界转换为信息世界的模型,E-R模型实施模型:把信息世界转化为数据世界使用的模型,关系模型现实世界建模信息世界模型转换规范化数据库世界数据库数据库事物类事物性质实体集实体属性文件记录数据项实体模型概念模型数据模型实体模型现实世界客观存在的、可以被描述事物都是“实体”层次模型按层次模型组织的数据示例网状模型按网状模型组织的数据示例关系模型按关系模型组织的数据示例关系模型特点关系模型中基本数据结构是二维数据表，且必须满足相应的要求：（1）表说明的是关系模型中某一特定的方面或部分的对象及其属性（2）表中的行通常叫做记录或元组，代表具有相同属性的对象中的一个（3）表中的列通常叫做字段或属性，代表存储对象的共有的属性（4）数据表之间的关联通过“键”来实现的，键分为主键和外键两种。（5）表必须符合某些特定条件①信息原则：每个单元只能存贮一条数据；②列有唯一性的名称，贮存在列下的数据必须具有相同数据类型；列没有顺序；③每行数据是唯一的；行没有顺序；④实体完整性原则，即主键不能为空；⑤引用完整性原则，即外键不能为空；概念模型建模步骤：收集信息：与该系统有关人员进行交流、坐谈，充分理解数据库需要完成的任务标识对象（实体－Entity）标识数据库要管理的关键对象或实体标识每个实体的属性（Attribute）标识对象之间的关系（Relationship）建模：把现实世界转换为信息世界的模型,E-R模型建模实例：学生选课系统建模张三选修李四王五计算机基础英语MySQL数据库个体学号总学分专业名出生日期性别姓名共性学生实体抽象抽象实体共性课程课程号学分学时开课学期类别课程名关联选课成绩个体主码（关键字）

主码，也叫关键字实体集中的属性或最小属性组合的值，能唯一标识其对应实体，则将该属性或属性组合称为主码。对于每一个实体集，可指定一个码为主码。学生和课程实体集属性描述概念结构设计概念结构设计-E-R图方法实体关系图：简记E-R图，是指以实体、关系、属性三个基本概念概括数据的基本结构，从而描述静态数据结构的概念模式。实体型(Entity)：具有相同的特征和性质的集合体，用实体名及其属性名来抽象和刻画同类实体;在E-R图中用矩形表示，矩形框内写明实体名；比如学生张三、学生李四都是实体属性(Attribute)：实体所具有的某一特性，一个实体可由若干个属性来刻画。在E-R图中用椭圆形表示，并用无向边将其与相应的实体连接起来；比如学生的姓名、学号、性别、都是属性。联系(Relationship)：数据对象彼此之间相互连接的方式称为联系，也称为关系。E-R模型E-R图的组成要素及其画法实体名联系名属性名实体集表示属性表示联系表示实体集及其属性表示绘制E-R图映射基数一对一XXXXYYYYXXXXYYYY一对多XXXXY

YY多对一XXXXYYYY多对多客户订单产品

MN

1N一对一联系(1:1)实体间不同联系情况的E-R图表示法一对一联系(1:1)：1主任系管理1姓名年龄学历编号系编号系名任职时间例：每个系只有一个主任。则主任和系之间是一对一的关系。主任负责管理系的工作，主任和系之间是一个管理关系主任与系之间的一对一的联系一对多的联系（1：m）：例：在某仓库管理系统中，有两个实体集：仓库和商品。仓库用来存放商品，且规定一类商品只能存放在一个仓库中，一个仓库可以存放多件商品。当商品存放在仓库时，存放联系中要反映出存放商品的数量。1仓库商品存放n地点面积仓库号商品号价格数量商品名一对多的联系（1：m）仓库和商品之间一对多的联系多对多联系（m:n)：例：在某教务管理系统中，一个教师可以上多门课，一门课也可以由多个老师去上。教师和课程之间的多对多联系，教师通过讲授课程与课程发生联系，在“讲授”联系中应能反映出教师的授课质量。m教师课程讲授n教师名职称教师号课程号班级质量课程名多对多联系（m:n)教师和课程之间的多对多联系

E-R图设计实例【例1.1】网络图书销售系统处理会员图书销售。简化的业务处理过程为：网络销售的图书信息包括：图书编号，图书类别，书名，作者，出版社，出版时间，单价，数量，折扣，封面图片等；用户需要购买图书必须先注册为会员，提供身份证号，会员姓名，密码，性别，联系电话，注册时间等信息；系统根据会员的购买订单形成销售信息，包括订单号，身份证号，图书编号，订购册数，订购时间，是否发货，是否收货，是否结清。

请画出网络图书销售数据库E-R图。【例2.1】E-R图设计步骤1.确定实体集：网络图书销售系统中有两个实体集：图书和会员2.确定实体集属性及主码：（1）实体集会员属性有：身份证号，会员姓名，性别，联系电话，注册时间，密码。

会员实体集中可用身份证号来唯一标识各会员，所以主码为身份证号。（2）实体集图书属性有：图书编号，图书类别，书名，作者，出版社，出版时间，单价，数量，折扣，封面图片。

图书实体集中可用图书编号来唯一标识图书，所以主码为图书编号。3.确定实体集之间的联系：

图书销售给会员时图书与会员建立关联，联系“销售”的属性有：订购册数，订购时间，是否发货，是否收货，是否结清。为了更方便标识销售记录，可添加订单号作为该联系的主码。4.确定联系关系：

因为一个会员可以购买多种图书，一种图书可销售各多个会员，所以这是一种多对多（m:n）的联系。

根据以上分析画出的网络图书销售数据库E-R图网络图书销售数据库E-R图E-R图设计步骤

对于复杂的系统，E-R图设计通常都应经过以下两个阶段：

（1）针对每一用户画出该用户信息的局部E—R图，确定该用户视图的实体、属性和联系。需注意的是：能作为属性的就不要作为实体，这有利于E—R图的简化。

（2）综合局部E—R图，生成总体E—R图。在综合过程中，同名实体只能出现一次，还要去掉不必要的联系，以便消除冗余。一般来说，从总体E—R图必须能导出原来的所有局部视图，包括实体、属性和联系。复杂E-R图设计实例

【例2.2】工厂物流管理中的涉及雇员、部门、供应商、原材料、成品和仓库等实体，并且存在以下关联：（1）一个雇员只能在一个部门工作，一个部门可以有多个雇员。（2）每一个部门可以生产多种成品，但一种成品只能由一个部门生产。（3）一个供应商可以供应多种原材料，一种原材料也可以由多个供应商供货。（4）购买的原材料放在仓库中，成品也放在仓库中。一个仓库可以存放多种产品，一种产品也可以存放在不同的仓库中。（5）各部门从仓库中提取原料，并将成品放在仓库中。一个仓库可以存放多个部门的产品，一个部门的产品也可以存放在不同的仓库中。

画出简单的工厂物流管理系统E-R模型。局部E-R图1、找出工厂物流管理系统实体集：分别是雇员、部门、成品、供应商、原材料和仓库。2.从生产的角度，根据（1）和（2）画出雇员、部门和成品三个实体间的初步联系3.从供应的角度，根据（3）画出供应商和原材料两个实体间的初步联系，4.为是问题简化，实体的属性先不画出。全局E-R图5.从仓储的角度，根据（4）和（5）画出仓库与各实体之间的联系，最终得到工厂物流管理系统的E-R图6.实体联系中有多个数量属性，分别用数量1，数量2…以区分E-R图设计中常见问题分析【例2.3】画出出版社和图书的E-R图。1.实体与联系分析：一个出版社可以出版多本图书，一本图书只能由一个出版社出版，出版社和图书之间就是一对多的关系。2.属性与主码：出版社实体有社名、地址、邮编、网址、联系电话等属性。为了建立出版社与图书实体一对多的联系，还应该有一个出版社代码来唯一标识出版社；

图书实体有出版社、书名、作者、价格等属性。为了唯一标识图书，还应设置书号属性。

根据以上分析，画出出版社与图书的E-R图如下：图书出版E-R图案例分析

出版社和图书的E-R图分析：（1）出版社和图书的关系：出版社通过出版与图书建立关联。一个出版社可以出版多本图书，一本图书一般由一个出版社出版，出版社和图书之间就是一对多的关系。（2）出版社实体有社名、地址、邮编、网址、联系电话等属性，为了建立出版社与图书实体一对多的联系，还应该有一个出版社代码来唯一标识出版社（3）图书实体有出版社代码、书名、作者、价格等属性。为了唯一标识图书，还应设置书号属性。图书出版E-R图问题思考（1）1、怎么标识书号（选取关键字）？国际上规定：全世界的每本书都应该有唯一的编号，这个号码叫做ISBN号，俗称书号。ISBN号码又分为几个子域，每个域的代码表示不同的含义，从而保证了图书书号的全球唯一性。因此，用ISBN书号作为图书的唯一标识似乎是非常合理的。在实际管理过程中，如果有些书或者资料还没有书号怎么处理？一般不能以ISBN书号唯一标识图书实体，而应该自己定义唯一标识图书实体的属性，如定义内部的图书编号规则。在现实世界中，类似这样的问题有很多，通常需要为实体集定义额外的关键字段。图书出版E-R图问题思考（2）2、有些属性的值如果有多个，该怎么办？出版社实体有电话属性。但一个出版社一般不止一部电话，怎么处理？一种方法是仍使用一个电话属性，采用只保存一部电话或几部电话写在一起，这种方法适合于小单位。第二种方法是将电话属性独立出来，建立一个新的电话实体集，通过出版社代码属性，建立和出版社的一对多联系。图书出版E-R图问题思考（3）3、如何提取实体的属性？实体的属性可以说是无穷无尽，到底应提取哪些属性，要结合具体应用系统考虑。比如，对图书实体，一般属性有书号、社代码、书名、作者、价格、版次等，如果开发的是书店管理系统，这些属性一般够用了，但如果要开发印刷厂管理系统，还需要增加图书大小（32开还是16开，或具体数字）、印刷纸张规格、是否彩印、彩印规格、印刷数量、交货日期等属性。所以，提取一个实体的属性也要具体问题具体分析。通过出版社—图书的例子引出的三个问题，说明建立在现实世界基础上的E-R模型并不只有唯一答案。面向不同的应用、使用不同的方法，可以设计出不同的E-R模型。E—R模型到关系模型的转换

把E-R图转换为关系模型可遵循如下原则:

（1）对于E—R图中每个实体集，都应转换为一个关系，该关系应包括对应实体的全部属性，并应根据关系所表达的语义确定哪个属性或哪几个属性组作为“主关键字”，主关键字用来标识实体。（2）对于E—R图中的联系，情况比较复杂，要根据实体联系方式的不同，采取不同的手段加以实现。（1:1）联系的E-R图转换（1）联系单独对应一关系模式，则由联系属性、参与联系的各实体集的主码属性构成关系模式，其主码可选参与联系的实体集的任一方的主码。BJ（班级编号，院系，专业名，人数）BZ（学号，姓名）SY（学号，班级编号）（2）联系不单独对应一关系模式，联系的属性及一方的主码加入另一方实体集对应的关系模式中。BJ（班级编号，院系，专业名，人数）BZ（学号，姓名，班级编号）或者BJ（班级编号，院系，专业名，人数，学号）BZ（学号，姓名）（1:n）联系的E-R图转换（1）联系单独对应一关系模式，则由联系的属性、参与联系的各实体集的主码属性构成关系模式，n端的主码作为该关系模式的主码。BJ（班级编号，院系，专业名，人数）XS(学号，姓名，专业名，性别，出生时间，总学分，备注)SY（学号，班级编号）（2）联系不单独对应一个关系模式，则将联系的属性及1端的主码加入n端实体集对应的关系模式中，主码仍为n端的主码

BJ（班级编号，院系，专业名，人数）XS(学号，姓名，专业名，性别，出生时间，总学分，备注，班级编号)（m:n）联系的E-R图转换对于（m:n）的联系，单独对应一关系模式，该关系模式包括联系的属性、参与联系的各实体集的主码属性，该关系模式的主码由各实体集的主码属性共同组成。XS(学号，姓名，专业名，性别，出生时间，总学分，备注)KC（课程号，课程名称，类别，开课学期，学时，学分）XS_KC(学号，课程号，成绩)假设A实体集与B实体集是1:1的联系，联系的转换有三种方法：①把A实体集的主关键字加入到B实体集对应的关系中，如果联系有属性也一并加入；②把B实体集的主关键字加入到A实体集对应的关系中，如果联系有属性也一并加入；③建立第三个关系，关系中包含两个实体集的主关键字，如果联系有属性也一并加入。两实体集间1:n联系

两实体集间1:n联系，可将“一方”实体的主关键字纳入“n方”实体集对应的关系中作为“外部关键字”，同时把联系的属性也一并纳入“n方”对应的关系中。两实体集间m:n联系

对于两实体集间m:n联系，必须对“联系”单独建立一个关系，用来联系双方实体集。该关系的属性中至少要包括被它所联系的双方实体集的“主关键字”，并且如果联系有属性，也要归入这个关系中。E-R模型到关系模型的转换总结任务二数据库设计规范化仅有好的RDBMS并不足以避免数据冗余，必须在数据库的设计中创建好的表结构DrE.F.codd最初定义了规范化的三个级别，范式是具有最小冗余的表结构。这些范式是：第一范式(1stNF－FirstNormalFromate)第二范式(2ndNF－SecondNormalFromate)第三范式(3rdNF－ThirdNormalFromate)关系数据库范式理论是在数据库设计过程中将要依据的准则，数据库结构必须要满足这些准则，才能确保数据的准确性和可靠性。这些准则则被称为规范化形式，即范式。第一范式(1stNF)BuyerIDCountryCity1342中国英国日本美国北京伦敦东京纽约………BuyerIDAddress1234中国北京市美国纽约市英国伦敦日本东京市……第一范式的目标是确保每列的原子性如果每列都是不可再分的最小数据单元（也称为最小的原子单元），则满足第一范式（1NF）第二范式(2ndNF)如果一个关系满足1NF，并且除了主键以外的其他列，都依赖与该主键，则满足第二范式（2NF）第二范式要求每个表只描述一件事情Orders字段例子订单编号产品编号订购日期价格001A0012000-2-3$29.00……Orders字段例子订单编号订购日期0012000-2-3Products字段例子产品编号价格A001$29.00第三范式(3rdNF)如果一个关系满足2NF，并且除了主键以外的其他列都不传递依赖于主键列，则满足第三范式（3NF）金额=单价*数量，是可以通过其它列计算得到，存在函数依赖关系，不满足第三范式Orders字段例子订单编号产品编号单价001AB00130数量50金额

1500Orders字段例子订单编号产品编号单价001AB00130数量

50规范化实例分析（1）假设某建筑公司要设计一个数据库。公司的业务规则概括说明如下：公司承担多个工程项目，每一项工程有：工程号、工程名称、施工人员等公司有多名职工，每一名职工有：职工号、姓名、性别、职务（工程师、技术员）等公司按照工时和小时工资率支付工资，小时工资率由职工的职务决定（例如，技术员的小时工资率与工程师不同）公司定期制定一个工资报表，如图-1所示规范化实例分析（2）工程号工程名称职工号姓名职务小时工资率工时实发工资A1花园大厦1001齐光明工程师6513845.001002李思岐技术员6016960.001004葛宇宏律师60191140.00小计2945.00A2立交桥1001齐光明工程师6515975.001003鞠明亮工人5517935.00小计1910.00A3临江饭店1002李思岐技术员60181080.001004葛宇洪技术员6014840.00小计1920.00工资发放表规范化实例分析（3）工程号工程名称职工号姓名职务小时工资率工时A1花园大厦1001齐光明工程师6513A1花园大厦1002李思岐技术员6016A1花园大厦1001齐光明工程师6513A1花园大厦1003鞠明亮工人5517A3临江饭店1002李思岐技术员6018A3临江饭店1004葛宇洪技术员6014项目工时表规范化实例分析（4）表中包含大量的冗余，可能会导致数据异常：更新异常

例如，修改职工号=1001的职务，则必须修改所有职工号=1001的行添加异常

若要增加一个新的职工时，首先必须给这名职工分配一个工程。或者为了添加一名新职工的数据，先给这名职工分配一个虚拟的工程。（因为主关键字不能为空）删除异常

例如，1001号职工要辞职，则必须删除所有职工号＝1001的数据行。这样的删除操作，很可能丢失了其它有用的数据采用这种方法设计表的结构，虽然很容易产生工资报表，但是每当一名职工分配一个工程时，都要重复输入大量的数据。这种重复的输入操作，很可能导致数据的不一致性。一张表描述了多件事情，如下图所示。规范化实例分析（5）工程号工程名称职工号姓名职务小时工资率工时工程信息员工信息项目工时信息应用第二范式规范化工程号工程名称职工号姓名职务小时工资率工程号职工号工时工程表员工表项目工时表满足第三范式吗？应用第三范式规范化工程号工程名称职工号姓名职务职务小时工资率工程号职工号工时工程表员工表职务表工程表规范化和性能的关系为满足某种商业目标，数据库性能比规范化数据库更重要通过在给定的表中添加额外的字段，以大量减少需要从中搜索信息所需的时间通过在给定的表中插入计算列（如成绩总分），以方便查询进行规范化的同时，还需要综合考虑数据库的性能。总结在需求分析阶段，设计数据库的一般步骤为：收集信息标识对象标识每个对象的属性标识对象之间的关系在概要设计阶段和详细设计阶段，设计数据库的步骤为：绘制E-R图将E-R图转换为表格应用三大范式规范化表格为了设计结构良好的数据库，需要遵守一些专门的规则，称为数据库的设计范式。第一范式（1NF）的目标：确保每列的原子性。第二范式（2NF）的目标：确保表中的每列，都和主键相关。第三范式（3NF）的目标：确保每列都和主键列直接相关，而不是间接相关。单元2数据库管理环境的建立项目知识要点与目标项目知识要点知识能力目标学时任务1了解数据库的基础知识1数据与数据库2数据库技术的发展3数据库管理系统（DBMS）4数据库系统的组成5结构化查询语言SQL6常见的关系型数据库管理系统任务2安装与配置MySQL数据库1MySQL服务器安装与配置2连接与断开服务器【典型工作任务】安装与配置MySQL数据库【技能要求】：1

MySQL的安装和配置2

MySQL运行环境测试【知识要求】：1数据库管理系统组成2服务器连接、启动和运行

2项目实训安装MySQL数据库2数据库基础知识数据狭义：数值数据广义：文字、声音、图形等一切能被计算机接收且能被处理的符号大量、海量数据库存储大量数据，方便检索和访问保持数据信息的一致、完整共享和安全通过组合分析，产生新的有用信息特点数据库管理系统（软件）管理大量的、持久的、可靠的、共享的数据的工具数据管理技术的发展阶段

人工管理阶段-〉文件系统阶段-〉数据库管理数据管理技术的发展阶段

-没有专用的软件对数据进行管理。-数据的组织方式必须由程序员自行设计与安排。-只有程序的概念而没有文件的概念、数据面向应用，也就是一组数据只能对应一个程序。数据管理技术的发展阶段

文件系统的三个缺陷：数据冗余性、数据不一致性、数据联系弱。数据管理技术的发展阶段

数据库…

订单

客户

产品

产品数据库表数据库就是“数据”的“仓库”数据库由表、关系以及操作对象组成数据存放在表中数据表存储过程视图….所谓数据库(Database,DB)，是将数据按一定的数据模型组织、描述和存储，具有较小的冗余度，较高的数据独立性和易扩展性，并可为各种用户共享的数据集合。列行(记录)关系型数据库关系型数据库关系模型使用的存储结构是多个二维表格，表中每一行称为一条记录，用来描述一个对象的信息；每一列称为一个字段，用来描述对象的一个属性。数据表于数据库之间存在相应的关联，这些关联将用来查询相关的数据。数据库管理系统马兰A004杨可A003刘开泰B008李新爱B007数据库检索数据插入数据更新数据删除数据数据库管理系统-DBMS

数据库管理系统（DBMS-DataBaseManagementSystem

）对收集到的大量数据进行整理、加工、归并、分类、计算、存储等处理，产生新的数据，以便反映事物或现象的本质和特征及其内在联系。数据库系统的基本概念数据库服务器响应和提供数据应用程序客户端操作和查询数据库应用程序作用：响应操作并显示结果、向数据库请求数据要求：美观、操作简单方便数据库作用：存储数据、检索数据、生成新的数据要求：统一、安全、性能等数据库应用系统数据库系统数据库系统(DatabaseSystem,DBS)一般由数据库、数据库管理系统(及其开发工具)、应用系统、数据库管理员和用户结构组成大数据时代的数据库管理系统当前主要的OldSQL（关系数据库）、NoSQL和NewSQL类数据库产品分类大数据时代的数据库管理系统数据库流行排行榜DB-EnginesRanking大数据时代的数据库管理系统数据库趋势流行度排名DB-EnginesRanking时下流行的DBMS简介(1)OracleOracle公司的产品，世界上最好的数据库系统“关系-对象”型数据库支持70多种操作系统，配置、管理和维护复杂主要满足对银行、金融、保险等企业、事业开发大型数据库需求SQLServerMicrosoft公司的产品，针对不同用户群体的多个版本要求在Windows操作系统平台上运行易用性好时下流行的DBMS简介（2）MySQL瑞典MySQLAB公司开发，被SUN公司收购，后Oracle收购Sun，现在MySQL并入了Oracle旗下。体积小、速度快、成本低、开放源码广泛地应用在Internet上的中小型网站中注意：招聘时通常要求“熟练使用SQLServer/Oracle/MySQL一种或多种数据库”结构化查询语言SQL2．结构化查询语言SQL（StructuredQueryLanguage）SQL语言是用于关系数据库查询的结构化语言，集数据查询（dataquery）、数据操纵（datamanipulation）、数据定义（datadefinition）和数据控制（datacontrol）功能于一体，充分体现了关系数据语言的特点和优点。主要特点包括：（1）综合统一（2）高度非过程化（3）面向集合的操作方式（4）以同一种语法结构提供两种使用方式（5）语言简洁，易学易用任务二MySQL的安装与配置

选用MySQL数据库有以下几方面的优势。

技术趋势

成本因素

跨平台性

性价比高

集群功能MySQL8.0的部分新特性。

更简便的NoSQL支持

更好的索引

安全和账户管理

InnoDB的变化

字符集支持MySQL的安装下载地址：/downloads/--下载MySQL社区版MySQL的安装MySQL社区版是免费的，有MSI和zip两种安装方式：下载前先选择安装平台（windows）安装文件方式（MSI或Zip)MySQL的安装安装向导--产品类型选择：Serveronly（服务器）MySQL的安装配置向导--类型和网络配置：ServerComputerMySQL的安装配置向导--账号和角色配置：为MySQL的超级用户root设置密码连接服务器“开始”→“程序”→MySQL→“MySQLServer8.0”→“MySQL8.0CommandLineClient”。在客户端窗口输入安装时为root用户设置的密码。窗口中出现MySQL命令行提示符“mysql>“，安装成功。MySQL图形化管理工具

NavicatforMySQL是一个桌面版MySQL数据库管理和开发工具，易学易用，很受大家的欢迎。本课程将以NavicatforMySQL为例介绍MySQL数据库管理工具的使用。单元3数据库和表的创建与管理项目知识要点知识能力目标学时任务1创建与管理数据库1创建数据库2管理数据库任务2创建与管理数据库表1创建数据库表2管理数据库表任务3数据完整性约束1主键约束2替代键约束3参照完整性约束4CHECK完整性约束【典型工作任务】：

学会创建与管理数据库和表【技能要求】：1掌握创建与管理数据库的方法2掌握创建与管理数据库表的方法3掌握建立数据完整性约束的方法【知识要求】：1数据库和数据库表的结构2数据完整性约束3数据库及表操作的SQL语句4项目实训创建“员工管理系统”数据库4项目知识要点与目标网络图书销售系统设计实例1、网络图书销售系统简化的业务逻辑网络销售的图书信息包括：图书实体：图书编号，图书类别，书名，作者，出版社，出版时间，单价，数量，折扣等；用户需要购买图书必须先注册为会员会员实体：身份证号，会员姓名，密码，性别，联系电话，注册时间等；系统根据会员的购买订单形成销售信息订单信息：订单号，身份证号，图书编号，订购册数，订购时间，是否发货，是否收货，是否结清

网络图书销售系统设计实例2.网络图书销售数据库E-R图网络图书销售系统设计实例3.将E-R图转换为关系模型：Bookstore（数据库）①Book(

图书编号,图书类别,书名,作者,出版社,出版时间,单价,数量,折扣)②Members(用户号,姓名,性别,密码,联系电话,注册时间)③ell(订单号,用户号,图书编号,订购册数,订购单价,订购时间,是否发货,是否收货,是否结清)网络图书销售系统设计实例4.由关系模型到数据库基本表Book表：Members表：Sell表：MySQL数据库模型在数据库服务器中可以存储多个数据库文件，所以建立数据库时要设定数据库的文件名，每个数据库有惟一的数据库文件名作为与其它数据库区别的标识。数据库文件：数据库是由相关数据表组成，一个数据库包括多个数据表。数据库文件用于记录数据库中数据表构成的信息。数据库只能由得到授权的用户访问，这样保证了数据库的安全。成绩数据库xscj网络图书库bookstore雇员管理库YGGL……….库MySQL服务器数据库数据库和表数据库可以看成是一个存储数据对象的容器，这些数据对象包括表、视图、触发器、存储过程等，其中，表是最基本的数据对象，用以存放数据库的数据的。必须首先创建数据库，然后才能创建数据库的数据对象。MySQL可以采用两种方式创建、操作数据库和数据对象（1）命令行方式（2）图形界面工具方式数据表视图存储过程DELIMITER$$CREATEPROCEDUREDELETE_STUDENT(INXHCHAR(6))BEGIN

创建数据库MySQL安装后，系统自动地创建information_scema和MySQL数据库，MySQL把有关数据库的信息存储在这两个数据库中。如果删除了这些数据库，MySQL就不能正常工作。

对于用户的数据，需要创建新的数据库来存放。

语法格式：CREATE{DATABASE|SCHEMA}[IFNOTEXISTS]数据库名[[DEFAULT]CHARACTERSET字符集名|[DEFAULT]COLLATE校对规则名]说明：语句中“[]”内为可选项。{|}表示二选一。创建数据库

命令行方式创建数据库

在SQL语言创建数据库命令createdatabase中，如果省略语句中“[]”中的所有可选项，其结构形式如下：

CREATEDATABASE数据库名;数据库名表示被创建数据库名，数据库名必须符合以下规则：数据库名必须唯一；名称内不能含有“/”及“.”等非法字符；最大不能超过64字节。【例3.1】创建一个名为Bookstore的数据库。CREATEDATABASEBookstore;显示数据库显示服务器中已建立的数据库，使用SHOWDATABASES命令。语法格式：SHOWDATABASES此命令没有用户变量，执行“SHOWDATABASES；”。注意：在MySQL中，每一条SQL语句都以“;”作为结束标志。显示数据库命令创建数据库时使用IFNOTEXISTS如果在服务器已有test1数据库的情况下，再创建一个名为test1的数据库。因为MySQL不允许两个数据库使用相同的名字，所以会出错。使用IFNOTEXISTS从句可以不显示错误信息，例如：CREATEDATABASEIFNOTEXISTStest1;为了表达问题简单，在以后的示例中单独描述命令而不需要界面结果时，在命令前省略“mysql>”提示符。因为MySQL服务器中有多个数据库，可以使用USE命令可指定当前数据库。语法格式：

USE数据库名说明：这个语句也可以用来从一个数据库“跳转”到另一个数据库，在用CREATEDATABASE语句创建了数据库之后，该数据库不会自动成为当前数据库，需要用这条USE语句来指定。【例】将数据库test1设置为当前数据库。

Usetest1;打开数据库MySQL中的字符集和校对规则字符集是一套符号和编码。校对规则是在字符集内用于比较字符的一套规则。ASCII字符集中四个字母：‘A’、‘B’、‘a’、‘b’，每个字母赋予一个数值：‘A’=65，‘B’=66，‘a’=113，‘b’=114。字母‘A’是一个符号，数字65是‘A’的编码，字母和它们的编码组合在一起是一个字符集。假设我们希望比较两个字符串的值：‘A’和‘b’。比较的最简单的方法是查找编码：‘A’为65，‘b’为114。因为65小于114，我们可以说‘A’小于‘b’。我们做的仅仅是在我们的字符集上应用了一个校对规则。MySQL字符集和校对规则应用MySQL支持40多种字符集的70多种校对规则。在同一台服务器、同一个数据库或甚至在同一个表中使用不同字符集或校对规则来混合字符串。

两个不同的字符集不能有相同的校对规则。每个字符集有一个默认校对规则。可用SHOWCHARACTERSET命令查看。ASCII码字符集：使用7位二进制数来表示所有的大写和小写字母，数字0到9、标点符号，以及在美式英语中使用的特殊控制字符GB2312是中国国家标准的简体中文字符集。GB2312收录简化汉字及一般符号、序号、数字、拉丁字母、日文假名、希腊字母、俄文字母、汉语拼音符号、汉语注音字母，共7445个图形字符。【例】创建一个名为Bookstore的数据库，采用字符集gb2312和校对规则gb2312_chinese_ci。CREATEDATABASEBookstore

DEFAULTCHARACTERSETgb2312COLLATEgb2312_chinese_ci;修改数据库数据库创建后，如果需要修改数据库的参数，可以使用ALTERDATABASE命令。

语法格式：ALTER{DATABASE|SCHEMA}[数据库名][[DEFAULT]CHARACTERSET字符集名|[DEFAULT]COLLATE校对规则名]

【例】修改数据库Pet的默认字符集为latin1，校对规则为latin1_swedish_ci。ALTERDATABASEPetDEFAULTCHARACTERSETlatin1DEFAULTCOLLATElatin1_swedish_ci;删除数据库已经创建的数据库需要删除，使用DROPDATABASE命令。

语法格式：DROPDATABASE[IFEXISTS]数据库名数据库test2命令如下：

DROPDATABASEtest2;删显示服务器中已建立的数据库，

使用SHOWDATABASES命令。

语法格式：

SHOWDATABASES；任务二

数据库表的操作

图书目录表的表结构分析字段名图书编号书名出版时间单价数量…字段值的表示方法用10个字符编号用40个字符表示yyyy-mm-dd表示用带有2位小数的5位数字表示用整数表示数据类型Char(10)Varchar(40)dateFloat(5，2)Int图书编号书名出版时间单价数量…7-115-12683-6计算机应用基础2022-06-0145.50

7-301-06342-3网络数据库2020-08-0149.5031

7-302-05701-7PHP高级语言2021-02-0136.505

图书目录表（Book）数据类型（1）数值类型MySQL支持所有标准SQL数值数据类型。

严格数值数据类型：INTEGER或INT，SMALLINT，DECIMAL或DEC，NUMERIC

近似数值数据类型：FLOAT，REAL，PRECISIONMySQL支持选择在该类型关键字后面的括号内指定整数值的显示宽度(例如，INT(4))。对于浮点列类型，在MySQL中单精度值使用4个字节，双精度值使用8个字节。MySQL允许使用FLOAT(M,D)或REAL(M,D)或DOUBLEPRECISION(M,D)格式。“(M,D)”表示该值一共显示M位整数，其中D位位于小数点后面。例如，定义为FLOAT(7,4)的一个列可以显示为-999.9999。MySQL保存值时进行四舍五入，因此如果在FLOAT(7,4)列内插入999.00009，近似结果是999.0001。字符串类型（2）字符串类型：字符串类型的数据主要是由字母、汉字、数字符号、特殊符号构成的数据对象。按照字符个数多少的不同分为以下几类：CHAR：列的长度固定为创建表时声明的长度。长度可以为从0到255的任何值。当保存CHAR值时，在它们的右边填充空格以达到指定的长度。

例如,在学生情况表中，如果设定“namechar(8)”，表示“name”是8个字符长度的字符串，可以做赋值引用即：name=｀张三｀，这里name的值包括8个字符，其中2个汉字（按照4个字符处理）和4个空格。name右侧会有若干个空格占位。这样浪费了磁盘的存储空间。字符串类型VARCHAR：列中的值为可变长字符串。长度可以指定为0到65,535之间的值。(VARCHAR的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是65,532字节）。同CHAR对比，VARCHAR值保存时只保存需要的字符数。

例如,在学生情况表中，如果设定“namevarchar(8)”，可以做赋值引用即：name=｀张三｀，这里姓名的值包括5个字符，其中2个汉字（按照4个字符处理）和1个空格。name右侧只有一个空格占位。BLOB和TEXT类型BLOB：二进制字符串(字节字符串)。

BLOB列没有字符集，并且排序和比较基于列值字节的数值值。这种类型数据用于存储声音、视频、图像等数据。

例如，图书数据处理中的图书封面、会员照片可以设定成为blob类型。TEXT：非二进制字符串(字符字符串)。TEXT列有一个字符集，并且根据字符集的校对规则对值进行排序和比较。在实际应用中像个人履历、奖惩情况、职业说明、内容简介等设定为text的数据类型。

例如，图书数据处理中的内容简介可以设定为text类型。BLOB和TEXT列不能有默认值。BLOB或TEXT对象的最大大小由其类型确定，但在客户端和服务器之间实际可以传递的最大值由可用内存数量和通信缓存区大小确定。你可以通过更改max_allowed_packet变量的值更改消息缓存区的大小，但必须同时修改服务器和客户端程序。日期和时间类型（3）日期和时间类型date：表示日期，输入数据的格式是：yyyy-mm-dd。支持的范围是'1000-01-01'到'9999-12-31'。time：表示时间，输入数据的格式是：hh:mm:ss。TIME值的范围可以从'-838:59:59'到'838:59:59'。小时部分会因此大的原因是TIME类型不仅可以用于表示一天的时间(必须小于24小时)，还可能为某个事件过去的时间或两个事件之间的时间间隔(可以大于24小时，或者甚至为负)。datetime:表示日期时间，格式是：yyyy-mm-ddhh:mm:ss。支持的范围为'1000-01-0100:00:00'到'9999-12-3123:59:59'。

例如，在图书销售信息管理中注册时间、订购时间可以设定成为datetime类型。CREATETABLE命令

为数据库创建数据表，可以使用SQL命令CREATETABLE来完成。此语句的完整语法是相当复杂的，因为存在很多的可选子句创建表使用CREATETABLE命令。

语法格式：CREATETABLE[IFNOTEXISTS]表名

（列名

数据类型[NOTNULL|NULL][DEFAULT列默认值]…）ENGINE=存储引擎

CREATETABLE命令的基本格式：

CREATETABLEtbl_name

（列名1数据类型1[NOTNULL|NULL],列名2数据类型2[NOTNULL|NULL],…..）

案例分析-创建学生基本情况表字段名字段类型宽度说明图书编号char10字符,非NULL，主键书名varchar40字符,非NULL出版时间date20日期,非NULL单价Float(5,2)两位小数，非NULL数量int

3位整数,非NULL…图书目录表如下

，在数据库Bookstore中创建图书目录表Book。

数据表结构分析：图书编号书名出版时间单价数量…7-115-12683-6计算机应用基础2022-06-0145.50

7-301-06342-3网络数据库2020-08-0149.5031

7-302-05701-7PHP高级语言2021-02-0136.505

案例分析-创建Book表命令CREATETABLEbook(

图书编号char(20)NOTNULLPRIMARYKEY,

图书类别varchar(20)NOTNULLDEFAULT'计算机',

书名varchar(40)NOTNULL,

作者char(10)NOTNULL,

出版社varchar(20)NOTNULL,

出版时间dateNOTNULL,

单价float(5,2)NOTNULL,

数量int(5),

折扣float(3,2),

封面图片blob)ENGINE=InnoDB;命令代码：显示数据表信息（1）显示数据表文件名

语法格式：

SHOWTABLES

【例3.11】显示Bookstore数据库建立的数据表文件:

USEBookstoreSHOWTABLES;（2）显示数据表结构

语法格式：{DESCRIBE|DESC}表名[列名|通配符]DESC是DESCRIBE的简写，二者用法相同。【例】用DESCRIBE语句查看Book表的列的信息。

USEBookstoreDESCRIBEBook;【例】查看Book

表图书编号列的信息。

USEBookstoreDESCBook图书编号;修改表

ALTERTABLE用于更改原有表的结构。例如，可以增加或删减列，创建或取消索引，更改原有列的类型，重新命名列或表，还可以更改表的评注和表的类型。语法格式：ALTERTABLE表名ADD[COLUMN]列定义[FIRST|AFTER列名]/*添加列*/ |ALTER[COLUMN]列名

{SETDEFAULT默认值|DROPDEFAULT}/*修改默认值*/|CHANGE[COLUMN]旧列名

列定义 /*对列重命名*/[FIRST|AFTER列名] |MODIFY[COLUMN]列定义

[FIRST|AFTER列名]/*修改列类型*/|DROP[COLUMN]列名 /*删除列*/|RENAME[TO]新表名

/*重命名该表*/修改表举例

【例】假设已经在数据库Bookstore中创建了表book，表中存在“书名”列。在表book中增加“浏览次数”列并将表中的“书名”列删除。

ALTERTABLEbookADD浏览次数tinyintNULL,DROPCOLUMN书名;【例】假设数据库Bookstore中已经存在table1表，将table1表重命名为student。

ALTERTABLEtable1RENAMETOstudent;

修改表名除了上面的ALTERTABLE命令，还可以直接用RENAMETABLE语句来更改表的名字。

语法格式：RENAMETABLE旧表名1TO新表名1[,旧表名2TO新表名2]...【例】假设数据库BookStore中已经存在table2表和table3表，将table2表重命名为orders，table3表重命名为orderlist。

RENAMETABLEtable2TOorders,Table3TOorderlist;复制表语法格式：CREATETABLE[IFNOTEXISTS]新表名[LIKE参照表名]|[AS(select语句)]

使用LIKE关键字创建一个与old_table_name表相同结构的新表，列名、数据类型、空指定和索引也将复制，但是表的内容不会复制，因此创建的新表是一个空表。

使用AS关键字可以复制表的内容，但索引和完整性约束是不会复制的。【例】假设数据库BookStore中有一个表Book，创建Book表的一个名为book_copy1的拷贝。

CREATETABLEbook_copy1LIKEBook;【例】创建表Book的一个名为book_copy2的拷贝，并且复制其内容。

CREATETABLEbook_copy2AS(SELECT*FROMBook);删除表需要删除一个表时可以使用DROPTABLE语句。语法格式：DROPTABLE[IFEXISTS]表名1[,表名2

]...这个命令将表的描述、表的完整性约束、索引及和表相关的权限等都全部删除。【例】删除表test。

USEBookstore；DROPTABLEIFEXISTStest;单元4数据操纵项目知识要点与目标项目知识要点知识能力目标学时

任务1插入表数据任务2修改表数据任务3删除表数据【典型工作任务】数据的添加、修改、删除操作【技能要求】1数据的插入操作2数据的修改操作3数据的删除操作【知识要求】1理解常用数据类型2掌握数据操纵的SQL语句4项目实训“员工管理系统”数据表操作4数据插入一旦创建了数据库和表，下一步就是向表里插入数据。通过INSERT或REPLACE语句可以向表中插入一行或多行数据。语法格式：

INSERT[IGNORE][INTO]表名[(列名,...)]VALUES({表达式|DEFAULT},..),(...),...|SET列名={表达式|DEFAULT},...如果要给全部列插入数据，列名可以省略。如果只给表的部分列插入数据，需要指定这些列。对于没有指出的列，它们的值根据列默认值或有关属性来确定，MySQL处理的原则是：（1）具有自动递增属性的列，系统生成序号值来唯一标志列。（2）具有默认值的列，其值为默认值。（3）没有默认值的列，若允许为空值，则其值为空值；若不允许为空值，则出错。（4）类型为timestamp的列，系统自动赋值。●VALUES子句：包含各列需要插入的数据清单，数据的顺序要与列的顺序相对应。若tb1_name后不给出列名，则在VALUES子句中要给出每一列（除IDENTITY和timestamp类型的列）的值，如果列值为空，则值必须置为NULL，否则会出错。VALUES子句中的值：（1）表达式：可以是一个常量、变量或一个表达式，也可以是空值NULL，其值的数据类型要与列的数据类型一致。例如，列的数据类型为int，插入的数据是‘aaa’就会出错。当数据为字符型时要用单引号括起。（2）DEFAULT：指定为该列的默认值。前提是该列原先已经指定了默认值。如果列清单和VALUES清单都为空，则INSERT会创建一行，每个列都设置成默认值。插入语句最常用格式：

INSERTINTO表名(列名,...)VALUES(表达式,...)

INSERT命令

【例】向Bookstore数据库中的表Book中插入如下的一行数据：

USEBookstore;INSERTINTObook

VALUES('6625','计算机','Dreamwearer8',‘2010-08-16’,33.25,NULL,NULL);INSERT命令举例

图书编号图书类别书名出版日期单价数量封面6625计算机Dreamwearer82015-08-1633.25【例】若表Book中图书类别的默认值为“计算机”，数量默认值为NULL，插入上例那行数据。INSERTINTOBook(图书编号,书名,出版日期,单价)VALUES('6626','网站制作','2010-08-16',33.25);与下列命令效果相同：INSERTINTOBookSET图书编号=‘6627’,书名=‘网页设计',图书类别=DEFAULT,出版日期='2010-08-16',单价=33.25;INSERT命令举例

如果记录已经插入了，再执行插入该记录的SQL代码，系统提示1062错误，这是因为两条记录的图书编号相同，而图书编号是Book表的主键，要求唯一。当插入第二条相同编号的记录时，系统提示错误，意为表中已有图书编号为6625的记录，第二条记录不能插入。可以使用REPLACE语句，用第二条记录替换第一条记录：REPLACEINTOBookVALUES('6625','计算机','PHP网站制作','2011-10-16',23.5,30);INSERT命令举例

【例】向Book表中插入一行数据：其中，封面图片的照片存储路径为D:\IMAGE\picture.jpg。方法一：以路径的形式来存储INSERTINTOBookVALUES('6628','计算机','计算机基础','2011-10-16',45.5,40,'D:/IMAGE/picture.jpg');方法二、使用LOAD_FILE()函数直接存储图片INSERTINTOBookVALUES('6628','计算机','计算机基础','2011-10-16',45.5,45,LOAD_FILE('D:/IMAGE/picture.jpg'));INSERT命令举例

图书编号图书类别书名出版日期单价数量封面6628计算机计算机基础2011-10-1645.540在一个单独的INSERT语句中使用多个VALUES（）子句一次插入多条记录。INSERTINTOBookVALUES('6629','计算机','数据库基础','2016-02-16',25.5,40,null),('6630','科学','数学','2016-02-16',35.5,40,null);从上面的SQL代码可以看出，当一次插入多条记录时，每条记录的数据要用（）括起来，记录与记录之间用逗号分开。INSERT命令举例

图书编号图书类别书名出版日期单价数量封面6629计算机数据库基础2016-02-1625.5406630科学数学2016-02-1635.540数据修改要修改表中的一行数据，可以使用UPDATE语句，UPDATE可以用来修改一个表，也可以修改多个表。修改单个表，语法格式：UPDATE[IGNORE]表名SET列名1=表达式1[,列名2=表达式2...][WHERE条件]说明： ●SET子句：根据WHERE子句中指定的条件对符合条件的数据行进行修改。若语句中不设定WHERE子句，则更新所有行。●可以同时修改所在数据行的多个列值，中间用逗号隔开。修改表数据命令

【例】将Bookstore数据库中Book表的所有书籍数量都增加10。将Members表中姓名为“张三”的员工的联系电话改为，密码改为“111111”。UPDATEBookSET数量=数量+10;UPDATEMembersSET联系电话=,密码='111111'

WHERE姓名='张三';任务二修改表数据

多表修改语法格式：UPDATE[IGNORE]

表名列表

SET

列名1=表达式1[,列名2=表达式2...][WHERE

条件]表名列表：包含了多个表的联合，各表之间用逗号隔开。多表修改语法的其他部分与单表修改语法相同。【例】订单号为16的客户因某种原因退回4本图书，请在sell表中修改订购册数，同时书退回后，book表中该图书的数量要增加4本。UPDATEsell,bookSETsell.订购册数=订购册数-4,book.数量=数量+4WHEREsell.图书编号=book.图书编号andsell.订单号='16';当用UPDATE修改多个表时，要修改的表名之间用逗号分开，字段名因为涉及到多个表，用“表名.字段名”表示,多表连接条件须在WHERE子句中指定。数据删除1.使用DELETE语句删除数据从单个表中删除，语法格式：DELETE[IGNORE]FROM表名[WHERE条件]如果省略WHERE子句则删除该表的所有行。【例】将Bookstore数据库的Members表中姓名为“张三”的员工的记录删除。USEBookstore;DELETEFROMMembersWHERE姓名='张三';【例】将Book表中数量小于5的所有行删除。USEBookstore;DELETEFROMBookWHERE数量<5;删除表数据命令从多个表中删除行，语法格式：DELETE[IGNORE]表名1[.*][,表名2[.*]...]FROM表名列表[WHERE条件]或：DELETE[IGNORE]FROM表名1[.*][,表名2[.*]...] USING表名列表 [WHERE条件]【例】用户号为D1963的客户注销了，请在members表中将该用户记录删除，同时将其在sell表中的记录也删除。DELETEsell,membersFROMsell,membersWHEREsell.用户号=members.用户号ANDmembers.用户号='D1963';或：DELETEFROMsell,membersUSINGsell,membersWHEREsell.用户号=members.用户号ANDmembers.用户号='D1963';TRUNCATE命令2.使用TRUNCATETABLE语句删除表数据使用TRUNCATETABLE语句将删除指定表中的所有数据，因此也称其为清除表数据语句。语法格式：TRUNCATETABLE表名说明：由于TRUNCATETABLE语句将删除表中的所有数据，且无法恢复，因此使用时必须十分小心。TRUNCATETABLE在功能上与不带WHERE子句的DELETE语句（如DELETEFROMXS）相同，二者均删除表中的全部行。但TRUNCATETABLE比DELETE速度快，且使用的系统和事务日志资源少。DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项。而TRUNCATETABLE通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。使用TRUNCATE