MySQL数据库教程（第2版）-课件全套 第1-10章数据库基础知识和 MySQL 的安装与配置 - -数据安全

第1章数据库基础知识和MySQL的安装与配置学习目标了解数据管理技术的发展历程。掌握数据库系统的组成和内部体系结构。掌握关系数据库的基础知识。掌握MySQL的安装与配置方法。掌握登录与退出MySQL的方法。1.1数据库概述日常生活和工作中的很多场景或业务活动都离不开数据库，如学校的学生成绩管理、图书馆借还书管理、银行业务、证券市场业务、飞机票/火车票的订票业务、超市业务和电子商务等。数据库可以理解为存放数据的仓库，但数据库中的数据并不是随意存放的，必须满足一定的规则，否则会影响查询效率。研究数据库的根本目的是研究如何科学地组织和管理数据，以提供可共享的、安全可靠的数据。1.1.1数据管理技术的发展历程自从世界上第一台电子数字计算机诞生以来，数据管理技术经历了人工管理、文件系统和数据库系统3个阶段。人工管理文件系统数据库系统1．人工管理阶段时间：20世纪50年代中期以前。背景：计算机主要用于科学计算。硬件：外部存储设备只有磁带、卡片和纸带。软件：没有可进行数据管理的软件。特点：数据不单独保存；一组数据只对应一个应用程序。计算机没有数据管理功能，应用程序之间不能共享数据，数据冗余度很大。人工管理例如，要求分别编写C语言程序求出10个整数中的最大值和最小值。从这个例子可以看出，在人工管理阶段，程序和数据是不可分割的整体。每个程序都有自己的数据，而且数据不独立，完全依赖于程序，根本无法实现数据共享，数据冗余严重。1．人工管理阶段2．文件系统阶段时间：20世纪60年代中期。背景：计算机已大量用于信息处理。硬件：有了可直接存取的存储设备（如磁盘）。软件：操作系统。特点：数据可长期保存；应用程序与数据具有一定的独立性。数据以文件形式存储，由操作系统统一管理，使得程序与数据具有一定的独立性。实现了数据以文件为单位的共享。文件系统2．文件系统阶段例如，同样要求分别编写C语音程序求出10个整数中的最大值和最小值从这个例子可以看出，文件中只保存了数据，并未存储数据的结构信息，因此读取文件数据的操作必须在程序中实现，这说明程序与数据的独立性仍存在局限性，数据不能完全脱离程序。3．数据库系统阶段时间：20世纪60年代后期背景：计算机广泛应用于各个领域，数据规模越来越大，数据共享要求越来越强。特点：将与多个应用程序相关的数据存放在同一个数据库中，实现数据共享。数据库中保存了数据及其结构信息，应用程序通过数据库管理系统获取数据，真正实现了数据的独立和共享。数据库系统3．数据库系统阶段例如，同样是求10个整数中的最大值和最小值。求最大值的SQL语句为:SELECTMAX(num)FROMdata求最小值的SQL语句为:SELECTMIN(num)FROMdata从这个例子可以看出，在数据库系统阶段，数据库中不仅保存了数据，还保存了数据表的结构信息（如列名称num），数据的存取工作完全由数据库管理系统完成。真正实现了数据独立和数据共享。1.1.2数据库系统数据库系统（DataBaseSystem，DBS）是指引入数据库技术后的计算机系统。1．数据库系统的组成数据库系统实际上是一个集合体，除了计算机硬件系统和操作系统，还包括：数据库数据库管理系统应用程序相关人员：数据库管理员、开发人员和最终用户1.1.2数据库系统2．数据库系统的特点（1）数据集成性强。将与多个应用程序相关的数据存放在同一个数据库中，从而实现数据的集成管理，可以避免数据之间的不相容与不一致。（2）数据共享性高且冗余度低。数据库中的数据可以被多个用户、多个应用程序共享使用，大大降低了数据冗余度，节省了存储空间。（3）数据独立性高。数据独立性是指数据和应用程序相互独立。把数据从程序中分离出去，并且数据的存取由数据库管理系统来负责，从而大大减少应用程序的维护和修改工作量。数据独立性包括逻辑独立性和物理独立性。（4）数据由数据库管理系统统一管理和控制。数据库管理系统提供了一套有效的数据管理和控制手段，以保证数据库中数据的安全、可靠和正确有效。1.1.2数据库系统3．数据库系统的内部体系结构三级模式：

外模式

概念模式

内模式二级映射：

外模式到概念模式的映射

概念模式到内模式的映射1.1.2数据库系统3．数据库系统的内部体系结构三级模式：（1）外模式。也称为子模式或用户模式，它是对开发人员和最终用户能够看见和使用的局部数据的逻辑结构的描述，是与某一应用程序相关的数据的逻辑表示。（2）概念模式。也称为逻辑模式，它是对数据的全局逻辑结构的描述，是所有用户（或应用程序）的公共数据视图。它不涉及具体的硬件环境与平台，也与具体的软件环境无关。针对不同的用户需求，一个概念模式可以有若干个外模式。（3）内模式。也称为存储模式或物理模式，它是对数据的物理存储结构和存储方法的描述，是数据在存储介质上的具体表现形式。其中，内模式处于底层，它反映了数据在计算机中的实际存储方法；概念模式处于中间层，它反映了设计人员对数据的全局逻辑结构的描述；外模式处于最高层，它反映了最终用户对数据的要求。1.1.2数据库系统3．数据库系统的内部体系结构二级映射：（1）外模式到概念模式的映射是指外模式与概念模式之间的对应关系。外模式是面向最终用户的局部模式，概念模式是全局模式，当概念模式发生改变时，数据库管理员负责改变相应的映射关系，使外模式保持不变，这样就不需要修改应用程序，从而保证了数据的逻辑独立性。（2）概念模式到内模式的映射是数据的全局逻辑结构与物理存储结构的对应关系。当数据的物理存储结构发生改变时，数据库管理员负责改变相应的映射关系，使数据的全局逻辑结构保持不变，从而保证了数据的物理独立性。1.1.3数据模型数据模型是数据库系统的核心，按不同的数据库设计阶段可分为3类。1.概念模型是对现实世界的第一层抽象，通常采用图形化（如E-R图）的方式来表示，方便数据库设计人员与用户进行交流以明确数据需求。2.逻辑模型是概念模型数据化的结果，它提供了表示和组织数据的方法，逻辑模型与具体的数据库管理系统密切相关。3.物理模型是对数据最底层的抽象，它描述了数据在计算机系统内部的表示方式和存取方法。从概念模型到逻辑模型的转换由数据库设计人员完成，从逻辑模型到物理模型的转换主要由数据库管理系统完成。1．概念模型概念模型以用户的视角来描述数据，仅考虑某领域内的实体、属性和联系，而不涉及具体的数据库管理系统和存储细节。概念模型中的几个基本概念，（1）实体（2）属性（3）实体集（4）实体之间的联系（5）E-R图（1）实体客观存在并可相互区分的事物称为实体。实体可以是具体的人、事、物，也可以是抽象的概念。例如，一名学生、一名教师、一门课程、一本书等。（2）属性描述实体的特性称为属性。一个实体可以有若干个属性，如学生实体有学号、姓名、性别、出生日期和班级等属性。属性的具体取值称为属性值。例如，某一名男学生实体的性别属性的属性值是“男”。（3）实体集同类型实体的集合称为实体集。例如，对学生实体来说，全体学生就是一个实体集；对课程实体来说，学校开设的所有课程也是一个实体集（4）实体之间的联系实体之间的联系是指两个不同实体集之间的联系。①一对一联系（1∶1）。实体集A中的一个实体最多与实体集B中的一个实体相对应，实体集B中的一个实体也最多与实体集A中的一个实体相对应。例如，一个班级只有一位班长，一位班长也只能管理一个班级，班级和班长两个实体集是一对一联系。②一对多联系（1∶n）。实体集A中的一个实体可以与实体集B中的多个实体相对应，但实体集B中的一个实体最多与实体集A中的一个实体相对应。例如，一个班级可以包含多名学生，而一名学生只能属于一个班级，班级和学生两个实体集是一对多联系。③多对多联系（m∶n）。实体集A中的一个实体可以与实体集B中的多个实体相对应，实体集B中的一个实体也可以与实体集A中的多个实体相对应。例如，一名学生可以选修多门课程，一门课程也可以被多名学生选修，学生和课程两个实体集是多对多联系。（5）E-R图实体联系（Entity-Relationship，E-R）方法是使用最广泛的概念模型表示方法，该方法使用E-R图来描述现实世界中实体集、属性及实体集之间的联系。实体集：用矩形表示。属性：用椭圆表示，并用实线将其与对应的实体集连接起来。联系：用菱形表示，并用实线将其与有关的实体集连接起来，同时标注联系的类型。2．逻辑模型逻辑模型与数据库管理系统密切相关，但不涉及具体的存储细节。逻辑模型的具体形式主要有：（1）层次模型（2）网状模型（3）关系模型（1）层次模型按照层次结构组织数据，用树形结构表示实体集之间的联系。

有且仅有一个根节点（即没有父节点的节点），

除根节点之外的其他节点有且只有一个父节点。层次模型能够反映实体集之间的一对多联系，但节点之间没有横向的关联，很难表示含有多对多联系的复杂结构，目前较少使用。（2）网状模型按照网状结构组织数据，易于表示实体集之间的多对多联系。不仅允许一个以上的节点没有父节点；而且允许一个节点有多个父节点。网状模型能更好地描述现实世界中实体集之间的联系，但其结构复杂，不容易维护，目前较少使用。（3）关系模型关系模型是主流的逻辑模型，用二维表格（数据表）来表示实体集以及实体集之间的联系。1.1.4关系数据库基于关系模型建立的数据库称为关系数据库。关系数据库实际上是由若干张二维表格组成的集合。1．关系数据库中的基本术语（1）关系（2）属性（字段）（3）元组（记录）（4）分量（5）域（6）主关键字（7）外部关键字（1）关系一个关系就是一张二维表格。对关系的描述称为关系模式，关系模式的一般格式为：关系名（属性名1，属性名2，……，属性名n）

学生实体集的关系模式为：学生表(学号,姓名,性别,出生日期,班级)

（1）关系一个关系就是一张二维表格。对关系的描述称为关系模式，关系模式的一般格式为：关系名（属性名1，属性名2，……，属性名n）课程实体集的关系模式为：课程表(课程编号,课程名称,学分)

（1）关系一个关系就是一张二维表格。对关系的描述称为关系模式，关系模式的一般格式为：关系名（属性名1，属性名2，……，属性名n）选修多对多联系的关系模式为：选修成绩表(学号,课程编号,成绩)

（2）属性（字段）二维表格中的一列称为一个属性，每个属性都有一个属性名。在关系数据库中，一列称为一个字段，每个字段都有字段名称。（3）元组（记录）二维表格中的一行称为一个元组，在关系数据库中称为一条记录。（4）分量一条记录中某个字段的具体取值称为一个分量，也称为数据项、字段值或属性值。（5）域字段的取值范围。例如，表1-2中的学分字段只能存储数值数据。（6）主关键字关系中能够唯一标识一条记录的字段集（一个字段或几个字段的组合），也称为主键或主码。学生表：学号课程表：课程编号选修成绩表：学号+课程编号（7）外部关键字如果一个字段集不是所在关系的主键，而是另一个关系的主键，则该字段集称为外部关键字，也称为外键或外码。选修成绩表有两个外键：学号课程编号2．关系的基本性质一个关系就是一张二维表格，但并不是所有的二维表格都是关系，一个关系应具有以下7个性质。（1）元组（记录）个数有限。（2）元组（记录）各不相同。（3）元组（记录）的次序可以交换。（4）元组（记录）的分量是不可再分割的基本数据项。（5）属性（字段）名各不相同。（6）属性（字段）的次序可以交换。（7）属性（字段）的分量具有与该属性相同的值域。由关系的基本性质可知，二维表格的每一行都是唯一的，而且同一列的分量的数据类型都是相同的。3．专门的关系运算关系数据库是由若干张二维表格（关系）组成的集合，关系运算的结果也是一个关系。关系数据库中专门的关系运算有（1）选择（2）投影（3）连接（4）除这些运算是实现数据查询与数据处理的基础。（1）选择运算一元运算，执行该运算会从指定关系中选出满足给定条件的元组组成一个新的关系。

班级=“英语2401”(喜欢唱歌的学生R)（2）投影运算一元运算，执行该运算会从指定关系中选出某些属性组成一个新的关系。

∏学号，姓名(喜欢唱歌的学生R)（3）连接运算二元运算，执行该运算会把两个关系中满足连接条件的元组组合成一个新的关系，通常记作：R⋈S。内连接①等值连接②非等值连接③自然连接外连接④左外连接⑤右外连接⑥

全外连接①等值连接等值连接就是从关系R和关系S的广义笛卡儿积中选取满足等值条件的元组组成一个新的关系。假设关系R有m个属性、i个元组，关系S有n个属性、j个元组。则两个关系的广义笛卡儿积可以记作R×S，运算结果是一个具有m+n个属性、i×j个元组的关系。例如，将学生R与选修成绩S两个关系进行广义笛卡儿积运算，将得到一个具有7个属性（含重复属性），9个元组的关系。①等值连接学生R与选修成绩S两个关系进行广义笛卡儿积运算，实际上得到的是一个错误的学生选修成绩单（成绩不符合实际情况）。①等值连接等值连接是在广义笛卡儿积的基础上，只保留满足等值条件的元组。例如，将学生R与选修成绩S两个关系进行等值连接，等值条件为“R.学号=S.学号”。①等值连接等值连接是在广义笛卡儿积的基础上，只保留满足等值条件的元组。例如，将学生R与选修成绩S两个关系进行等值连接，等值条件为“R.学号=S.学号”。②非等值连接从关系R和关系S的广义笛卡儿积中选出满足非等值条件的元组组成一个新的关系。可以使用的比较运算符（>、>=、<=、<、<>），运算结果同样包含两个关系的所有属性，也包括重复的属性。例如，将学生R与选修成绩S两个关系进行非等值连接，非等值条件为“R.学号>S.学号”②非等值连接从关系R和关系S的广义笛卡儿积中选出满足非等值条件的元组组成一个新的关系。可以使用的比较运算符（>、>=、<=、<、<>），运算结果同样包含两个关系的所有属性，也包括重复的属性。例如，将学生R与选修成绩S两个关系进行等值连接，非等值条件为“R.学号>S.学号”③自然连接按照公共属性值相等的条件进行连接，要求两个关系中必须有相同的属性，并且在结果中消除重复的属性。自然连接实际上就是在等值连接的基础上去掉重复的属性。将学生R与选修成绩S两个关系进行自然连接，结果中只有一个学号属性。④左外连接在等值连接的基础上，保留左边关系R中要舍弃的元组，同时将右边关系S对应的属性值用NULL代替。左外连接能够保证包含左边关系R中的所有元组。例如，将学生R与选修成绩S两个关系进行左外连接。⑤右外连接在等值连接的基础上，保留右边关系S中要舍弃的元组，同时将左边关系R对应的属性值用NULL代替。右外连接能够保证包含右边关系S中的所有元组。例如，将学生R与选修成绩S两个关系进行右外连接。⑥全外连接在等值连接的基础上，同时保留关系R和关系S中要舍弃的元组，但将其他属性值用NULL代替。全外连接能够保证包含关系R和关系S中的所有元组。（4）除运算关系R和关系S的除运算（记作R÷S）要求关系S的属性必须全部包含在关系R中，且关系R中存在关系S中没有的属性，运算结果的属性由R中除去S中的属性后剩下的属性组成，元组由关系R与关系S中在所有相同属性上有相等值的那些元组组成。（4）除运算例如，将学生选课表R与所有课程S进行除运算，目的是找出选修了所有课程的学生。1.1.5大数据时代的数据库大数据时代的数据库根据所使用的语言不同可以分为三大类：1.SQL类数据库。使用SQL作为其标准查询语言的关系数据库，其核心优势在于能够确保数据的一致性，典型代表有Oracle、MySQL、SQLServer等。2.NoSQL类数据库。泛指非关系数据库，这类数据库通常具备分布式特性，采用键值对、文档、列族或图形等灵活模型，旨在解决关系数据库面临的高并发、海量数据和高扩展性问题，典型代表有Redis、MongoDB、HBase、Neo4j等。3.NewSQL类数据库。对各种新型可扩展、高性能数据库的简称。NewSQL旨在结合SQL的强一致性和NoSQL的高扩展性优势，典型代表有GoogleSpanner、TiDB、OceanBase、CockroachDB等。1.2MySQL的安装与配置

MySQL是一种开源的关系数据库管理系统，支持多种平台。

MySQL社区版（Community）是供个人用户免费使用的版本。

个人用户可以直接进入MySQL官网下载相应版本的安装程序，然后进行安装与配置。1.2.1安装与配置MySQLWindows平台中MySQLServer8.0的安装与配置过程中，需要注意以下4个地方。①在ChoosingaSetupType界面进行安装类型的选择时，应选择Serveronly。②在AccountsandRoles界面进行账号和角色的配置时，设置root账号的登录密码为123456。③在TypeandNetworking界面进行产品类型和网络的配置时，采用默认的DevelopmentComputer类型和TCP/IP端口3306。④在WindowsService界面进行Windows服务的配置时，服务名称采用默认的MySQL80。1.2.2登录与退出MySQLMySQL的安装和配置完成之后，必须启动MySQL才能进行登录（连接）。1．启动或停止MySQL启动MySQL实际上就是启动MySQL80服务。默认情况下，在安装与配置MySQL全部完成后会自动启动MySQL80服务，用户也可以手动启动该服务。单击“开始”菜单“Windows管理工具”下的“服务”，打开“服务”窗口。2.使用超级管理员身份登录（连接）MySQL（1）通过MySQL8.0CommandLineClient窗口登录MySQL在安装MySQL时，MySQL8.0CommandLineClient程序会被自动配置到计算机上，方便用户登录和管理MySQL数据库。在窗口中输入root账号的密码123456并回车，登录成功后显示“mysql>”

命令提示符。2.使用超级管理员身份登录（连接）MySQL（2）通过Windows命令提示符窗口登录MySQL在Windows命令提示符窗口中，首先使用“cdC:\ProgramFiles\MySQL\MySQLServer8.0\bin”命令切换到MySQL安装目录的bin文件夹。然后使用“mysql-uroot–p”命令以root账号及密码123456登录，登录成功后显示“mysql>”命令提示符。3.退出MySQL在“mysql>”命令提示符后输入“QUIT”或“EXIT”命令并回车。第2章数据库设计学习目标了解数据库设计步骤。掌握E-R图的绘制方法。掌握关系模式的设计方法。了解关系模式规范化的相关范式。2.1数据库设计步骤数据库设计大多采用生命周期法，将整个数据库设计步骤分解为6个阶段。1.需求分析2.概念结构设计3.逻辑结构设计4.物理结构设计5.数据库实施6.数据库运行和维护1.需求分析需求分析是根据用户的需求收集并分析数据。通过调查和分析用户的业务活动和数据使用情况，分析所用数据的类型、范围、数量，以及它们在业务活动中的应用情况，确定用户对数据库系统的使用要求和各种约束等。2.概念结构设计概念结构设计是整个数据库设计的关键，通过对需求分析阶段收集的数据进行综合、归纳与抽象，形成一个不依赖任何数据库管理系统的概念模型。概念结构设计最常用的方法之一是绘制E-R图。3.逻辑结构设计逻辑结构设计的任务是将概念模型转换为选用的数据库管理系统所支持的逻辑模型，即将用E-R图表示的实体集以及实体集之间的联系转换为所选择的数据库管理系统支持的数据模型，然后对其进行规范化。4.物理结构设计物理结构设计的主要目标是为所设计的逻辑模型选择合适的数据存储结构和存取方法，以提高数据库的访问速度和存储空间的利用率。目前，大量关系数据库屏蔽了数据库内部的存储结构，因此需要设计人员参与物理结构设计的任务很少。5.数据库实施在数据库实施阶段，设计人员运用数据库管理系统提供的数据库语言及实用程序，根据逻辑结构设计和物理结构设计的结果建立数据库，编写与调试应用程序，组织数据入库，并进行试运行。

6.数据库运行和维护数据库正式投入运行后，就进入数据库运行和维护阶段。在这个阶段，必须不断地对数据库进行评估、调整及备份。

本章主要介绍前3个阶段，即需求分析、概念结构设计和逻辑结构设计。2.2需求分析需求分析是整个数据库设计的基础。需求分析的充分程度和准确程度决定了在其基础上构建数据库的速度与质量。2.2.1需求分析的任务和重点1.任务详尽地调查现实世界中要处理的对象，充分了解该对象当前使用的数据管理系统的工作概况，尽可能多地收集数据，明确用户的各种需求，然后在此基础上确定新系统的功能。2.

重点调查、收集与分析用户对于数据管理的信息要求、处理要求、安全性与数据完整性要求。2.2.2需求分析的方法1.调查用户需求2.分析和表达用户需求3.得到用户的认可1.调查用户需求调查用户需求的具体步骤如下。①调查要处理的对象的基本情况，包括其部门组成情况，以及各个部门的职能等。②调查各个部门的业务活动情况，包括各个部门输入和使用什么数据、如何处理这些数据、输出什么格式的数据、数据输出到什么部门等。③协助用户明确对于数据管理的各种要求，包括信息要求、处理要求、安全性与数据完整性要求。④确定新系统的边界，确定哪些功能由计算机完成或将来准备让计算机完成、哪些功能由人工完成。2.分析和表达用户需求结构化分析方法是最简单、最实用的分析和表达用户需求的方法之一。结构化分析方法采用自顶向下、逐层分解的方式分析需求，并建立需求的处理流程。3.得到用户的认可对用户需求进行分析与表达后，必须将结果提交给用户，得到用户的认可后，需要数据库存储的具体数据和新系统的功能就确定了。2.3概念结构设计概念结构设计的任务是将需求分析阶段收集的数据抽象为概念模型，它的最终结果是整个系统的E-R图。按照自顶向下、逐层分解的需求分析示意，概念结构设计可以采用自底向上、逐层设计与合并的方法，先设计底层的局部概念结构（局部E-R图），然后将它们逐层合并，最终形成完整的全局概念结构（全局E-R图）。2.3.1局部E-R图设计需求分析阶段收集了每个局部需求所涉及的数据，局部E-R图设计旨在将这些数据抽象为实体集、属性，以及实体集之间的联系。2.3.1局部E-R图设计【例2-1】设有如下4个实体集及其属性，请分析这4个实体集之间的联系并绘制局部E-R图。（1）学生实体集（学号，姓名，性别，班级，院系，绩点）6个属性，主键是学号。（2）课程实体集（课程编号，课程名称，学时，学分，开课院系）5个属性，主键是课程编号。（3）教师实体集（教工号，教师姓名，性别，职称，所属部门）5个属性，主键是教工号。（4）院系实体集（院系代，院系名称，负责人）

3个属性，主键是院系代码。这4个实体集之间存在如下联系。学生实体集和课程实体集之间是多对多联系；教师实体集和课程实体集之间是多对多联系；

院系实体集和学生实体集之间是一对多联系；院系实体集和教师实体集之间是一对多联系；院系实体集和课程实体集之间是一对多联系。院系学生选修课程的局部E-R图

学生实体集（学号，姓名，性别，班级，院系，绩点）课程实体集（课程编号，课程名称，学时，学分，开课院系）院系实体集（院系代码，院系名称，负责人）学生在各个学年和学期选修课程后将得到该课程的成绩。院系教师讲授课程的局部E-R图课程实体集（课程编号、课程名称、学时、学分、开课院系）教师实体集（教工号、姓名、性别、职称、所属部门）院系实体集（院系代码、院系名称、负责人）教师讲授课程必须面向某个班级2.3.2全局E-R图设计各个局部E-R图绘制好后，需要将它们合并，形成完整的概念模型，即全局E-R图。全局E-R图的设计主要分为合并和优化两个步骤。1．合并合并不是简单地将各个局部E-R图拼接在一起，而是在拼接的同时消除各个局部E-R图之间的冲突。局部E-R图之间的冲突主要有3种：（1）属性冲突：分为属性值域冲突和属性的取值单位冲突。（2）命名冲突：实体集名、属性名或联系名之间存在的同名异义或异名同义。（3）结构冲突：有3种情况。第1种是同一个对象在不同的局部E-R图中有不同的抽象，如一个抽象为实体集，另一个抽象为属性；第2种是同一个实体集的属性组成在不同的局部E-R图中不同，如同一个实体集的属性个数不同；第3种是实体集之间的联系类型不同，如一个是一对一联系，另一个是多对多联系。消除合并过程中的冲突存在命名冲突：院系实体集中的“院系名称”、学生实体集中的“院系”、课程实体集中的“开课院系”、教师实体集中的“所属部门”实际上是同样的含义，即异名同义。存在结构冲突：学生实体集中的“院系”是属性，同时有院系实体集。2．优化优化的目的是消除不必要的冗余。冗余的属性：指可由基本属性推导或计算得到的属性。冗余的实体集之间的联系：指可由其他联系推导出的联系。2．优化冗余的属性：学生实体集的“院系”“绩点”；课程实体集的“学时”“开课院系”；教师实体集的“所属部门”，“讲授”联系中的“班级”。冗余的实体集之间的联系：课程实体集与院系实体集之间的“开设”。合并与优化后得到的全局E-R图考虑到数据库的查询性能，采用通过空间换时间的设计策略，保留了“讲授”联系中的“班级”属性，这样可避免查询该属性时跨其他实体集或者联系所产生的开销。2.4逻辑结构设计逻辑结构设计的任务是将概念模型转换为选用的数据库管理系统所支持的逻辑模型。由于MySQL采用的逻辑模型是关系模型，所以逻辑结构设计就是将E-R图中的各个实体集以及实体集之间的联系转换为一组关系模式。2.4.1关系模式设计将E-R图中各个实体集以及实体集之间的联系转换为一组关系模式时，需要遵循以下原则。1.实体集的转换原则一个实体集转换为一个独立的关系模式。实体集的属性就是关系的属性，实体集的主键就是关系的主键。2.

实体集之间的联系的转换原则根据不同的联系类型做不同的处理，有4种情况。2.4.1关系模式设计2.实体集之间的联系的转换原则（1）一对一联系：转换为一个独立的关系模式，或者与任意一端的关系模式合并。（2）一对多联系：转换为一个独立的关系模式，或者与n端的关系模式合并。（3）多对多联系：转换为一个独立的关系模式。（4）具有相同主键的关系模式可以合并：将其中一个关系模式的全部属性加入另一个关系模式中，然后去掉其中的同义属性。关系模式设计举例【例2-2】将合并与优化后得到的全局E-R图中各个实体集以及实体集之间的联系转换为一组关系模式。关系模式设计举例方案1：4个实体集（院系、学生、课程和教师）和4个联系（属于、拥有、选修、讲授）分别转换成8个独立的关系模式。①院系表（院系代码,院系名称,负责人）②学生表（学号,姓名,性别,班级）③课程表（课程编号,课程名称,学分）④教师表（教工号,教师姓名,性别,职称）⑤属于表（学号,院系代码）⑥拥有表（教工号,院系代码）⑦选修成绩表（学号,课程编号,学年,学期,成绩）⑧讲授安排表（班级,教工号,课程编号）关系模式设计举例方案2：将“属于”和“拥有”一对多联系分别合并到n端对应的关系模式中。①院系表（院系代码,院系名称,负责人）②学生表（学号,姓名,性别,班级,院系代码）③课程表（课程编号,课程名称,学分）④教师表（教工号,教师姓名,性别,职称,院系代码）⑤选修成绩表（学号,课程编号,学年,学期,成绩）⑥讲授安排表（班级,教工号,课程编号）建议选用关系模式较少的方案2。2.4.2关系模式的规范化为了提升数据库的性能，应该对关系模式进行规范化。关系模式的规范化通常以关系数据库范式（NormalForm，NF）理论为指导，可以将范式分为6种：第一范式（1NF）第二范式（2NF）第三范式（3NF）BC范式（BCNF）第四范式（4NF）第五范式（5NF）在实际的数据库设计中，通常只需要用到前3种范式。1.第一范式（1NF）1NF要求关系（数据表）中的每一个分量（属性值）都是不可再分割的数据项，即一个属性不能有多个值。2.第二范式（2NF）2NF建立在1NF的基础上，即要想满足2NF，必须先满足1NF。2NF要求数据表中的每条记录都是唯一的，而且每个属性完全依赖主键。完全依赖主键是指不能仅依赖主键中的一部分属性。如果存在不完全依赖主键的属性，就应该将其分离出来形成一个新的数据表。2.第二范式（2NF）分离为3张表,且增加了课程编号属性以便对课程表进行维护3.第三范式（3NF）3NF建立在2NF的基础上，即要想满足3NF，必须先满足2NF。3NF要求数据表不存在非主属性对任意主属性的传递函数依赖。主属性是指能够唯一标识一条记录的所有属性（一张数据表可以有多个主属性，通常指定某个主属性作为主键）。传递函数依赖是指如果存在主属性A决定非主属性B，而非主属性B决定非主属性C，则称非主属性C传递函数依赖主属性A。3.第三范式（3NF）分离为2张表,并且增加了院系代码属性以便进行维护3.第三范式（3NF）另外，3NF还要求不在数据表中存储可以通过简单计算得出的数据。学时和学分只需要保留其中的一个。关系模式的规范化举例【例2-3】检验【例2-2】方案1和方案2中的关系模式是否满足3NF的规范化要求。从1NF开始，逐步进行规范化检验。（1）判断是否满足1NF。由于每个关系模式中的所有属性值都是不可再分割的数据项，因此满足1NF。（2）判断是否满足2NF。以学生表为例，学号为主键，其余的所有属性都完全依赖学号。其他关系模式都如此，因此满足2NF。（3）判断是否满足3NF。由于所有模式都不存在非主属性对主属性的传递函数依赖，因此满足3NF。综上，【例2-2】方案1和方案2中的关系模式满足3NF的规范化要求。2.5课堂案例：学生成绩管理数据库设计1．需求分析通过与教务管理人员进行交流，确定了以下需求。（1）数据库要存储学生、院系、教师、课程，以及学生所选修的各门课程的成绩等核心数据。（2）支持基本的插入、删除、修改和查询等数据操作。如插入（增加）新入学的学生信息，删除已经毕业的学生信息，查询学生的学号、班级、院系和课程成绩等。（3）可以实现各种数据统计。如统计学生人数、平均成绩、已修学分、绩点等。（4）为了降低系统复杂度，暂不考虑课程重修的问题，但要考虑未来的扩展性，未来要实现课程重修功能时，对现有数据库进行的修改越少越好。2.5课堂案例：学生成绩管理数据库设计2．概念结构设计对需求分析得到的信息进行抽象，得出如下系统包含的4个实体集及其属性如下。①学生实体集：学号、姓名、性别、出生日期、班级、院系名称、入学总分、绩点、奖惩情况。②课程实体集：课程编号、课程名称、学时、学分、开课院系、任课教师。③教师实体集：教工号、教师姓名、性别、职称、院系名称。④院系实体集：院系代码、院系名称、负责人。其中，学生实体集和课程实体集之间是多对多联系；教师实体集和课程实体集之间是多对多联系；院系实体集和学生实体集之间是一对多联系；院系实体集和教师实体集之间是一对多联系；院系实体集和课程实体集之间是一对多联系。2.5课堂案例：学生成绩管理数据库设计为“选修”多对多联系单独设立了选修记录编号作为主键，目的是考虑未来的扩展性。同一名学生重复选修同一门课程时，由于每次选修时产生的选修记录编号值不同，完全可以区分不同的记录。2.5课堂案例：学生成绩管理数据库设计3．逻辑结构设计①院系表(院系代码,院系名称,负责人)②学生表(学号,姓名,性别,出生日期,班级,

院系代码,入学总分,奖惩情况)③课程表(课程编号,课程名称,学分)④教师表(教工号,教师姓名,性别,职称,院系代码)⑤选修成绩表(选修记录编号,学号,课程编号,

学年,学期,成绩)⑥讲授安排表(班级,教工号,课程编号)第3章数据定义学习目标掌握创建和管理数据库的相关语句。掌握创建和管理数据表的相关语句。掌握数据完整性约束的作用。掌握创建数据完整性约束的方法。3.1创建和管理数据库创建数据库是数据库实施阶段的第一步，管理数据库是为了有效地维护数据库，以确保数据库系统高效、稳定地运行。MySQL中的数据库可以用命令语句或者通过图形化的数据库工具（如NavicatforMySQL）来创建和管理。3.1.1创建数据库1．创建数据库的语句CREATE{DATABASE|SCHEMA}[IFNOTEXISTS]数据库名[[DEFAULT]CHARACTERSET字符集][[DEFAULT]COLLATE字符集的校对规则];（1）{}为必选项；[]可选项；|用于分隔各个选项，表示只能选择其中一个。（2）IFNOTEXISTS：在创建数据库前进行判断，只有在不存在该数据库时才创建。（3）DEFAULT：指定数据库的默认字符集或校对规则。（4）CHARACTERSET子句：指定数据库的字符集。（5）COLLATE子句：指定字符集的校对规则。（6）语句以英文分号“;”结束。3.1.1创建数据库2．MySQL数据库中的字符集和校对规则字符集是一套符号和编码，用于定义数据的存储格式。例如，ASCII字符集定义了英文字符的存储格式。校对规则是在字符集上用于比较字符大小的一套规则。例如，英文字符在比较大小时是否区分大小写等。mysql>SHOWCHARACTERSET;+----------+---------------------------------+---------------------+--------+|Charset|Description|Defaultcollation|Maxlen|+----------+---------------------------------+---------------------+--------+|ASCII|USASCII|ASCII_GENERAL_CI|1||GB2312|GB2312SimplifiedChinese|GB2312_CHINESE_CI|2||GBK|GBKSimplifiedChinese|GBK_CHINESE_CI|2||UTF8MB3|UTF-8Unicode|UTF8MB3_GENERAL_CI|3||UTF8MB4|UTF-8Unicode|UTF8MB4_0900_AI_CI|4|+----------+---------------------------------+---------------------+--------+创建数据库举例【例3-1】创建一个名为scoredb的学生成绩管理数据库，采用MySQL数据库默认的字符集和校对规则。创建数据库的语句：CREATE{DATABASE|SCHEMA}[IFNOTEXISTS]数据库名[[DEFAULT]CHARACTERSET字符集][[DEFAULT]COLLATE字符集的校对规则];CREATEDATABASEscoredb;mysql>CREATEDATABASEscoredb;QueryOK,1rowaffected(0.01sec)创建数据库举例【例3-2】创建一个名为mytestdb的数据库，采用字符集GB2312和校对规则GB2312_CHINESE_CI。创建数据库的语句：CREATE{DATABASE|SCHEMA}[IFNOTEXISTS]数据库名[[DEFAULT]CHARACTERSET字符集][[DEFAULT]COLLATE字符集的校对规则];CREATEDATABASEmytestdbCHARACTERSETGB2312COLLATEGB2312_CHINESE_CI;mysql>CREATEDATABASEmytestdb->CHARACTERSETGB2312->COLLATEGB2312_CHINESE_CI;QueryOK,1rowaffected(0.01sec)创建数据库举例【例3-3】如果不存在数据库mytestdb，则创建该数据库。创建数据库的语句：CREATE{DATABASE|SCHEMA}[IFNOTEXISTS]数据库名CREATEDATABASEIFNOTEXISTSmytestdb;mysql>CREATEDATABASEmytestdb;ERROR1007(HY000):Can’tcreatedatabase'mytestdb';databaseexistsmysql>CREATEDATABASEIFNOTEXISTSmytestdb;QueryOK,1rowaffected,1warning(0.01sec)mysql>SHOWWARNINGS;+-------+------+---------------------------------------------------+|Level|Code|Message|+-------+------+---------------------------------------------------+|Note|1007|Can'tcreatedatabase'mytestdb';databaseexists|+-------+------+---------------------------------------------------+3.1.2管理数据库1.显示数据库2.选择数据库3.修改数据库4.删除数据库1．显示数据库显示MySQL中的所有数据库的语句：SHOWDATABASES;4个系统数据库（1）information_schema：保存MySQL的元数据信息。（2）mysql：存储MySQL的用户及其访问权限等信息。（3）performance_schema：收集MySQL的性能数据。（4）sys：包含一系列的存储过程、存储函数和视图，主要作用是展示MySQL的各类性能指标。mysql>SHOWDATABASES;+--------------------+|Database|+--------------------+|information_schema||mysql||mytestdb||performance_schema||scoredb||sys|+--------------------+2.选择数据库在多数据库环境中，可以使用USE语句选择数据库，目的是告诉MySQL要使用哪个数据库，使其成为当前数据库。选择数据库的语句：USE数据库名;【例3-5】选择数据库scoredb作为当前数据库。

USEscoredb;mysql>USEscoredb;Databasechanged3.修改数据库修改数据库的语句：ALTER{DATABASE|SCHEMA}[数据库名][[DEFAULT]CHARACTERSET字符集][[DEFAULT]COLLATE字符集的校对规则];【例3-6】修改数据库mytestdb的字符集为GBK，校对规则为GBK_CHINESE_CI。ALTERDATABASEmytestdbCHARACTERSETGBKCOLLATEGBK_CHINESE_CI;mysql>ALTERDATABASEmytestdb->CHARACTERSETGBK->COLLATEgbk_chinese_ci;QueryOK,1rowaffected(0.01

sec)4.删除数据库删除数据库的语句：DROP{DATABASE|SCHEMA}[IFEXISTS]数据库名;【例3-7】删除数据库mytestdb。

DROPDATABASEmytestdb;mysql>DROPDATABASEmytestdb;QueryOK,0rowaffected(0.02sec)3.2创建和管理数据表数据表是数据库中最重要和最基本的对象，是数据库中组织和存储数据的基本单位。建立数据库后，即可在数据库中创建数据表。3.2.1创建数据表1．MySQL支持的数据类型2．设计数据表3．创建数据表1．MySQL支持的数据类型MySQL支持的数据类型非常丰富，这里主要介绍常用的3种：（1）数值型（2）日期和时间型（3）字符串型（1）数值型数值型数据可以分为整数和实数两类。整数主要有TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT，默认情况下，整数都是有符号数，无符号数必须使用UNSIGNED显式地进行说明，如INTUNSIGNED。（1）数值型实数主要有FLOAT、DOUBLE和DECIMAL。其中，定点数的m表示总位数，d表示小数位数，且“总位数m=整数位数+小数位数d”。定点数适合对精度要求极高的场景。（2）日期和时间型日期和时间型数据主要有DATE、TIME、YEAR、DATETIME和TIMESTAMP。不同类型表示的日期和时间内容不同，取值范围不同，而且存储长度也可能不同。（3）字符串型有文本字符串和二进制字符串。文本字符串主要包括CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。其中，n表示可存储字符的个数，不区分中英文。CHAR是固定长度，在不指定n时，默认是CHAR(1)。VARCHAR是可变长度，必须指定n，n只是限制最多能存储的字符数，如果实际字符数小于n，则按实际存储。（3）字符串型二进制字符串主要包括BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。其中，m表示可存储的字节数。BINARY是固定长度，在不指定m时，默认占用1字节。VARBINARY是可变长度，必须指定m。m只是限制最多能存储的字节数，如果实际存储的字节数小于m，则按实际存储。2．设计数据表（1）确定数据表的名称。（2）设计数据表的结构。确定各个字段的字段名称、数据类型、是否允许空值、键和默认值等。3．创建数据表创建数据表的语句：CREATETABLE[IFNOTEXISTS]表名(字段名称1数据类型[约束][,字段名称2

数据类型[约束]…]);其中，约束包括是否允许空值、默认值、自增属性、主键、唯一约束等，具体参数如下。[NULL|NOTNULL][DEFAULT默认值][AUTO_INCREMENT][PRIMARYKEY][UNIQUE]（1）NULL|NOTNULL：指定该字段是否允许空值。如果不指定，默认允许空值。（2）DEFAULT默认值：指定该字段的默认值。如果不指定，默认值为NULL。（3）AUTO_INCREMENT：指定该字段为自增字段。每插入一条记录，该字段的值自动增加1。（4）PRIMARYKEY：指定该字段为主键。主键既不允许空值，也不允许重复。（5）UNIQUE：指定该字段为唯一约束。唯一约束允许空值，但不允许重复。创建数据表举例【例3-8】在数据库scoredb中创建院系表department。创建数据表的语句：CREATETABLE[IFNOTEXISTS]表名(字段名称1数据类型[约束][,字段名称2数据类型[约束]…]);CREATETABLEdepartment(deptnoCHAR(3)NOTNULLPRIMARYKEY,deptnameVARCHAR(50)UNIQUE,directorVARCHAR(50)DEFAULT‘郑明'

);mysql>USEscoredb;Databasechangedmysql>CREATETABLEdepartment

->(->deptnoCHAR(3)NOTNULLPRIMARYKEY,->deptnameVARCHAR(50)UNIQUE,->directorVARCHAR(50)DEFAULT'郑明'->);QueryOK,0rowsaffected(0.01sec)创建数据表举例【例3-9】创建学生家庭情况表familyinfo。

mysql>CREATETABLEfamilyinfo->(->stunoCHAR(11)PRIMARYKEY,->stunameVARCHAR(50),->addressVARCHAR(50),->telephoneCHAR(11)->);QueryOK,0rowsaffected(0.01sec)CREATETABLEfamilyinfo(stunoCHAR(11)PRIMARYKEY,stunameVARCHAR(50),addressVARCHAR(50),telephoneCHAR(11));3.2.2管理数据表1.查看数据表2.修改数据表3.复制数据表4.删除数据表1.查看数据表（1）查看数据表的名称（2）查看数据表的基本结构（3）查看数据表的详细结构（1）查看数据表的名称语句格式：SHOWTABLES[{FROM|IN}数据库名];【例3-10】在数据库scoredb中查看已经创建好的数据表的名称。

SHOWTABLESFROMscoredb;或SHOWTABLES;mysql>SHOWTABLES;+-------------------+|Tables_in_scoredb|+-------------------+|department||familyinfo|+-------------------+（2）查看数据表的基本结构SHOWCOLUMNS语句：SHOWCOLUMNS{FROM|IN}表名[{FROM|IN}数据库名];DESCRIBE语句：{DESCRIBE|DESC}表名;【例3-11】查看院系表department的基本结构。SHOWCOLUMNSFROMdepartment;或DESCdepartment;

mysql>DESCdepartment;+----------+-------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+----------+-------------+------+-----+---------+-------+|deptno|char(3)|NO|PRI|NULL|||deptname|varchar(50)|YES|UNI|NULL|||director|varchar(50)|YES||郑明||+----------+-------------+------+-----+---------+-------+3rowsinset(0.01sec)（3）查看数据表的详细结构语句格式：SHOWCREATETABLE表名;【例3-12】查看院系表department的详细结构。SHOWCREATETABLEdepartment;mysql>SHOWCREATETABLEdepartment;+------------+--------------------------------------------------------+|Table|CreateTable|+------------+--------------------------------------------------------+|department|CREATETABLE`department`(`deptno`char(3)NOTNULL,`deptname`varchar(50)DEFAULTNULL,`director`varchar(50)DEFAULT'郑明',PRIMARYKEY(`deptno`),UNIQUEKEY`deptname`(`deptname`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci|+------------+--------------------------------------------------------+1rowinset(0.02sec)2.修改数据表（1）修改数据表的名称（2）修改字段的数据类型（3）修改字段名称和数据类型（4）添加字段（5）删除字段（1）修改数据表的名称语句格式：ALTERTABLE原表名RENAME[TO]新表名;【例3-13】将学生家庭情况表familyinfo的名称修改为sfamily。

ALTERTABLEfamilyinfoRENAMEsfamily;mysql>ALTERTABLEfamilyinfoRENAMEsfamily;QueryOK,0rowsaffected(0.01sec)mysql>SHOWTABLES;+-------------------+|Tables_in_scoredb|+-------------------+|department||sfamily|+-------------------+2rowsinset(0.04sec)（2）修改字段的数据类型语句格式：ALTERTABLE表名MODIFY[COLUMN]字段名称新的数据类型;【例3-14】将学生家庭情况表sfamily中联系电话telephone字段的数据类型由CHAR(11)修改为VARCHAR(20)。ALTERTABLEsfamilyMODIFYtelephoneVARCHAR(20);mysql>ALTERTABLEsfamilyMODIFYtelephoneVARCHAR(20);mysql>DESCsfamily;+-----------+-------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-----------+-------------+------+-----+---------+-------+|stuno|char(11)|NO|PRI|NULL|||stuname|varchar(50)|YES||NULL|||address|varchar(50)|YES||NULL|||telephone|varchar(20)|YES||NULL||+-----------+-------------+------+-----+---------+-------+（3）修改字段的名称和数据类型语句格式：ALTERTABLE表名CHANGE[COLUMN原字段名称新字段名称新数据类型;【例3-15】将学生家庭情况表sfamily中的联系电话字段名称由telephone修改为cellphone、数据类型修改回CHAR(11)。。

ALTERTABLEsfamilyCHANGEtelephonecellphoneCHAR(11);mysql>ALTERTABLEsfamilyCHANGEtelephonecellphoneCHAR(11);mysql>DESCsfamily;+-----------+-------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-----------+-------------+------+-----+---------+-------+|stuno|char(11)|NO|PRI|NULL|||stuname|varchar(50)|YES||NULL|||address|varchar(50)|YES||NULL|||cellphone|char(11)|YES||NULL||+-----------+-------------+------+-----+---------+-------+（4）添加字段语句格式：ALTERTABLE表名ADD[COLUMN]新字段名称数据类型[约束][FIRST|AFTER已存在的字段名称];默认