MySQL数据库项目式教程621

MySQL数据库构建与管理

之数据库设计概述2课程安排学时分配：

32学时（上课）+32学时（上机）考核：平时成绩=作业20%+实践30%+考勤50%总成绩=平时成绩60%+考试成绩40%123本章抛开MySQL讲解关系数据库设计的相关知识，以“选课系统”为例，讲解“选课系统”数据库的设计流程。4内容一览数据库设计的相关知识数据库概述E-R图关系数据库设计123简单地说：数据库（Database或DB）是存储、管理数据的容器；严格地说：数据库是“按照某种数据结构对数据进行组织、存储和管理的容器”。总结：数据永远是数据库的核心。关系数据库关系数据库管理系统结构化查询语言SQL1.1数据库概述添加标题基于“关系模型”的数据库管理系统称为关系数据库管理系统（简称为RDBMS）。随着关系数据库管理系统的日臻完善，目前关系数据库管理系统已占据主导地位。1.1.1关系数据库管理系统/blog/jdd-2013-what-we-found-out-about-databases2014年2月数据库流行度排行榜截至目前，MySQL已经成功逆袭！1.1.1关系数据库管理系统2014年3月数据库流行度排行榜1.1.1关系数据库管理系统2014年4月数据库流行度排行榜1.1.1关系数据库管理系统开源MySQL源代码免费下载简单MySQL体积小，便于安装MySQL性能足够与商业数据库媲美功能强大MySQL提供的功能足够与商业数据库媲美逆袭原因性能优越1.1.1关系数据库管理系统1.1.1关系数据库管理系统通过“数据库管理系统”，数据库用户可以轻松地实现数据库容器中各种数据库对象的访问（增、删、改、查等操作），并可以轻松地完成数据库的维护工作（备份、恢复、修复等操作）数据库用户无法直接通过操作系统获取数据库文件中的具体内容；数据库管理系统通过调用操作系统的进程管理、内存管理、设备管理以及文件管理等服务，为数据库用户提供管理、控制数据库容器中各种数据库对象、数据库文件的接口。1.1.1关系数据库管理系统添加标题添加标题数据加工：1、现实世界2、信息世界3、计算机世界数据模型：1、实体模型：E-R模型（概念设计）2、概念模型：关系模型（逻辑设计）3、数据模型：规范化数据库结构层次模型面向对象模型网状模型关系模型数据库管理系统常用的数学模型

关系数据库中所谓的“关系”，实质上是一张二维表。请看下面的课程表。字段名（列名）第1个字段（列）第1条记录第2条记录第3条记录第4条记录第5条记录第3个字段（列）第2个字段（列）第4个字段（列）1.1.2关系数据库作为数据库中最为重要的数据库对象，数据库表的设计过程并非一蹴而就，上述课程表根本无法满足“选课系统”的功能需求。事实上，数据库表的设计过程并非如此简单，本章的重点就是讨论如何设计结构良好的数据库表。1.1.2关系数据库结构化查询语言（StructuredQueryLanguage或简称为SQL）是一种应用最为广泛的关系数据库语言，该语言定义了操作关系数据库的标准语法，几乎所有的关系数据库管理系统都支持SQL。1.1.3结构化查询语言SQL学习重点123数据库设计是一个“系统工程”，要求数据库开发人员：熟悉“商业领域”的商业知识。利用“管理学”的知识与其他开发人员进行有效沟通。掌握一些数据库设计辅助工具。数据库设计辅助工具商业知识和沟通技能“选课系统”概述45定义问题域编码规范1.2数据库设计的相关知识数据库技术解决的是“商业领域”的“商业问题”。数据库开发人员有必要成为该“商业领域”的专家，与其他开发人员（包括最终用户）一起工作，继而使用数据库技术解决该“商业领域”的“商业问题”。1.2.1商业知识和沟通技能1.2.2数据库设计辅助工具

工具模型技术业务模型面向对象分析和设计技术结构化分析和设计技术软件测试技术关系数据库设计技术ErwinPowerDesignerRationalRoseVisioVC++6.0VisualstudioEclipseNetBeans单元测试工具功能测试工具性能测试工具程序流程图DFD时序图状态图CASE工具IDE工具测试工具数据模型类图E-R图限于篇幅，在不影响“选课系统”核心功能的基础上，适当地对该系统进行“定制”、“扩展”以及“瘦身”。

1.2.3“选课系统”概述定义问题域是数据库设计过程中重要的活动，它的目标是准确定义要解决的商业问题。“选课系统”亟需解决的“商业”问题有哪些？1.2.4定义问题域在编程的时候会考虑代码的可读性吗？你觉得代码可读性是需要考虑的问题吗？1.代码不仅要自己能读懂，还要别人也能看懂？2.尽量做到可读，但时间紧任务重的时候就顾不上了？3.代码只要自己能读懂就可以了？4.代码写完就完了，不用管以后是否能读懂？5.不知道，没有想过这个问题？1.2.5编码规范请记住：尽最大努力把方便留给别人和将来的自己。建议大家遵循本书罗列的常用编程规范！123关系实体和属性E-R图的设计原则E-R图设计的质量直接决定了关系数据库设计的质量。1.3E-R图

实体不是某一个具体事物，而是某一种类别所有事物的统称。属性通常用于表示实体的某种特征，也可以使用属性表示实体间关系的特征实体属性属性1.3.1实体和属性E-R图中的关系用于表示实体间存在的联系，在E-R图中，实体间的关系通常使用一条线段表示。E-R图中实体间的关系是双向的。关系关系1.3.2关系

基数表示一个实体到另一个实体之间关联的数目，基数是针对关系之间的某个方向提出的概念，基数可以是一个取值范围，也可以是某个具体数值基数可选基数强制1.3.2关系

元表示关系所关联的实体个数。二元关系二元关系一元关系1.3.2关系

使用关联（Association）表示实体间关系的属性。关联关联1.3.2关系123实体是一个单独的个体，不能存在于另一个实体中，成为另一个实体的属性。属性应该存在于、且只存在于某一个地方（实体或者关联）。同一个实体在同一个E-R图内，仅出现一次。

数据库开发人员通常采用“一事一地”的原则从系统的功能描述中抽象出来E-R图。1.3.3E-R图的设计原则问题：1、唯一标识：ISBN；2、属性多个值怎么处理；3、一个实体提取多少个属性，实体与实体之间的联系1．为E-R图中的每个实体建立一张表。2．为每张表定义一个主键（如果需要，可以向表添加一个没有实际意义的字段作为该表的主键）3．增加外键表示一对多关系。4．建立新表表示多对多关系。5．为字段选择合适的数据类型。6．定义约束条件（如果需要）。7．评价关系的质量，并进行必要的改进。1.4关系数据库设计student(student_no,student_name,student_contact）course(course_name,up_limit,description,status)teacher(teacher_no,teacher_name,teacher_contact)classes(class_name,department_name)1.4.1为每个实体建立一张数据库表关键字（key）：用以唯一标识表中的每行记录。主键（PrimaryKey）：在所有的关键字中选择一个关键字，作为该表的主关键字，简称主键。1.4.2为每张表定义一个主键主键有以下两个特征：（1）表的主键可以是一个字段，也可以是多个字段的组合（这种情况称为复合主键）。（2）表中主键的值具有唯一性且不能取空值（NULL）；当表中的主键由多个字段构成时，每个字段的值都不能取NULL。主键设计技巧，请参看本书内容。1.4.2为每张表定义一个主键student(student_no,student_name,student_contact）course(course_no,course_name,up_limit,description,status)teacher(teacher_no,teacher_name,teacher_contact)classes(class_no,class_name,department_name)1.4.2为每张表定义一个主键外键（ForeignKey）：如果表A中的一个字段a对应于表B的主键b，则字段a称为表A的外键，此时存储在表A中字段a的值，要么是NULL，要么是来自于表B主键b的值。1.4.3增加外键表示一对多关系情形一：如果实体间的关系为一对多关系，则需要将“一”端实体的主键放到“多”端实体中，然后作为“多”端实体的外键，通过该外键即可表示实体间的一对多关系。1.4.3增加外键表示一对多关系让学生记住所在班级，远比班级“记住”所有学生容易的多。1.4.3增加外键表示一对多关系情形二：实体间的一对一关系，可以看成一种特殊的一对多关系：将“一”端实体的主键放到另“一”端的实体中，并作为另“一”端的实体的外键，然后将外键定义为唯一性约束（UniqueConstraint）。1.4.3增加外键表示一对多关系PK1.4.3增加外键表示一对多关系PK后的方案：student(student_no,student_name,student_contact,class_no)course(course_no,course_name,up_limit,description,status,teacher_no)teacher(teacher_no,teacher_name,teacher_contact)classes(class_no,class_name,department_name)1.4.3增加外键表示一对多关系情形三：如果两个实体间的关系为多对多关系，则需要添加新表表示该多对多关系，然后将该关系涉及到的实体的“主键”分别放入到新表中（作为新表的外键），并将关系自身的属性放入到新表中作为新表的字段。1.4.4建立新表表示多对多关系teacher(teacher_no,teacher_name,teacher_contact)classes(class_no,class_name,department_name)course(course_no,course_name,up_limit,description,status,teacher_no)student(student_no,student_name,student_contact,class_no)choose(choose_no,student_no,course_no,score,choose_time)1.4.4建立新表表示多对多关系1.4.4建立新表表示多对多关系数据模型的要素和种类

数据模型是严格定义的一组概念的集合，这些概念精确地描述了系统的静态特征(数据结构)、动态特征(数据操作)和数据约束条件，这是数据模型的三要素。数据结构:一张二维表、数据类型数据操作：sql语言增删改查数据约束条件：1.4.5为字段选择合适的数据类型常用的约束条件有6种：主键（PrimaryKey）约束外键（ForeignKey）约束唯一性（Unique）约束默认值（Default）约束非空（NotNULL）约束检查（Check）约束。1.4.6定义约束（Constraint）条件设计数据库时，有两个不争的事实。 数据库中冗余的数据需要额外的维护，因此质量好的一套表应该尽量“减少冗余数据”。 数据库中经常发生变化的数据需要额外的维护，因此质量好的一套表应该尽量“避免数据经常发生变化”。1.4.7评价数据库表设计的质量冗余的数据需要额外的维护，并且容易导致“数据不一致”、“插入异常”以及“删除异常”等问题的发生。1.4.8使用规范化减少数据冗余1.4.8使用规范化减少数据冗余规范化是通过最小化数据冗余来提升数据库设计质量的过程，规范化是基于函数依赖以及一系列范式定义的，最为常用的是第一范式（1NF）、第二范式（2NF）和第三范式（3NF）。1.4.8使用规范化减少数据冗余函数依赖：一张表内两个字段值之间的一一对应关系称为函数依赖。第一范式：如果一张表内同类字段不重复出现，并且属性不可再分，该表就满足第一范式的要求。1.4.8使用规范化减少数据冗余第一范式1.4.8使用规范化减少数据冗余第二范式：一张表在满足第一范式的基础上，如果每个“非关键字”字段“仅仅”函数依赖于主键，那么该表满足第二范式的要求。1.4.8使用规范化减少数据冗余场景一：插入异常。场景二：修改复杂。场景三：删除异常。第二范式1.4.8使用规范化减少数据冗余第三范式：如果一张表满足第二范式的要求，并且不存在“非关键字”字段函数依赖于任何其他“非关键字”字段，那么该表满足第三范式的要求。1.4.8使用规范化减少数据冗余第三范式1.4.8使用规范化减少数据冗余统计学生的个人资料时，如果读者是一名数据库开发人员，应该让学生上报年龄信息，还是让学生上报出生日期？如何确保每一门课程选报学生的人数，不超过人数上限？1.4.9避免数据经常发生变化方案一：course(course_no,course_no,course_name,up_limit,description,status,teacher_no,available)方案二：数据库表无需进行任何更改。1.4.9避免数据经常发生变化任务1：描述“选课系统”的问题域。任务2：将本书图1-7中“团购网站、顾客以及房源之间的三元关系表示方法

”E-R图转化为关系（模式）。任务3：使用笔纸或者word绘图工具，绘制“选课系统”的E-R图。任务4：将“选课系统”的E-R图转化为关系（模式）。任务布置Thanks郭水泉MySQL数据库基础与实例教程

之MySQL基础知识123通过本章的学习，读者可以掌握一些常用的MySQL命令，从而对MySQL数据库进行一些简单的管理。本章知识点较为繁杂，希望保持一份儿耐心。4字符集以及字符序设置MySQL概述MySQL数据库管理MySQL表管理5系统变量内容一览123MySQL由瑞典MySQLAB公司开发。2008年1月MySQL被美国的SUN公司收购。2009年4月SUN公司又被美国的甲骨文（Oracle）公司收购。MySQL服务的安装MySQL的特点MySQL服务的配置45启动与停止MySQL服务MySQL配置文件2.1MySQL概述67MySQL由瑞典MySQLAB公司开发。2008年1月MySQL被美国的SUN公司收购。2009年4月SUN公司又被美国的甲骨文（Oracle）公司收购。连接MySQL服务器关系数据库MySQL客户机2.1MySQL概述2.1.1数据与数据库1.数据2.数据处理3.数据管理4.数据库:集合、可共享、最小冗余5.数据库技术：完整性、安全性、并发控制2.1.2数据库的发展1.人工管理阶段:数据不保存；没有专门的数据管理软件；数据面向应用；基本没有文件的概念。2.文件系统阶段数据冗余大，缺乏独立性，无法集中管理，文件之间缺乏联系3.数据库系统阶段：dbms

数据的结构化；数据共享和独立性好；数据存取冗余度小；数据库管理系统对数据进行统一的管理和控制；为用户提供有好的接口。数据库管理系统：1、数据库安全型控制2、数据库完整性控制功能3、并发控制4、恢复功能数据库相关概念数据库数据库管理系统数据库应用系统数据库系统数据库相关概念数据库数据库(DataBase，DB)是长期存放在计算机内、有组织的、可共享的相关数据的集合它将数据按一定的数据模型组织、描述和存储，具有较小的冗余度、较高的数据独立性和易扩展性、可被各类用户共享等特点。数据库相关概念数据库管理系统数据库管理系统(DataBaseManagementSystem，DBMS)是位于用户与操作系统(OS)之间的一层数据管理软件，它为用户或应用程序提供访问数据库的方法，包括数据库的创建、查询、更新及各种数据控制，它是数据库系统的核心。数据库管理系统一般由计算机软件公司提供，目前比较流行的DBMS有VisualFoxPro、Access、SQLServer、MySQL、Oracle等。数据库相关概念数据库管理系统的主要功能数据定义功能。DBMS提供数据定义语言(DataDefinitionLanguage，DDL)，用户通过它可以方便地对数据库中的对象进行定义。数据操纵功能。DBMS提供数据操纵语言(DataManipulationLanguage，DML)，用户可以使用DML操纵数据实现对数据库的基本操作，如查询、插入、删除和修改等。数据库的运行管理。数据库在创建、运用和维护时由DBMS统一管理、统一控制，以保证数据的安全性、完整性、多用户对数据的并发使用及发生故障后的系统恢复。数据库的创建和维护功能。包括数据库初始数据的输入、转换功能，数据库的转储、恢复功能，数据库的组织功能和性能监视、分析功能等。这些功能通常由一些应用程序完成的。数据库相关概念数据库应用系统凡使用数据库技术管理其数据的系统都称为数据库应用系统(DataBaseApplicationSystem)。数据库应用系统的应用非常广泛，它可以用于事务管理、计算机辅助设计、计算机图形分析和处理及人工智能等系统中。数据库相关概念数据库系统数据库系统(DataBaseSystem，DBS)是指在计算机系统中引入数据库后的系统它由计算机硬件、数据库、数据库管理系统(及其开发工具)、数据库应用系统、数据库用户构成。1、数据共享2、数据独立3、减少数据冗余度4、避免数据不一致5、加强数据保护数据库相关概念数据库系统数据库用户包括数据库管理员、系统分析员、数据库设计人员及应用程序开发人员和终端用户。数据库系统的体系结构数据库的三级模式结构是数据3个抽象级别，它把数据的具体组织留给DBMS去处理，用户只要抽象地处理数据，而不必关心数据在计算机中的表示和存储，这样就减轻了用户使用系统的负担。三级结构之间差别往往很大，为了实现这3个抽象级别的联系和转换，DBMS在三级结构之间提供了两级映像(Mapping)：外模式/模式映像，模式/内模式映像。正是这两级映像保证了数据库系统中的数据能够具有较高的逻辑独立性和物理独立性。

数据库系统的体系结构1.模式模式(Schema)也称概念模式(ConceptualSchema)或逻辑模式，是对数据库中全部数据的逻辑结构和特征的描述，是所有用户的公共数据视图。模式实际上是数据库数据在概念级上的视图，一个数据库只有一个模式。数据库管理系统提供模式描述语言(SchemaDataDefinitionLanguage，模式DDL)来定义模式。数据库系统的体系结构2.外模式外模式(ExternalSchema)也称子模式(Subschema)或用户模式，它是对数据库用户(包括程序员和最终用户)能够看见和使用的局部数据的逻辑结构和特征的描述，即个别用户涉及的数据的逻辑结构。外模式通常是模式的子集，一个数据库可以有多个外模式。外模式是保证数据库安全性的一个有效措施。数据库管理系统提供外模式描述语言(外模式DDL)来定义外模式。数据库系统的体系结构3.内模式内模式(InternalSchema)也称存储模式(StorageSchema)或物理模式，一个数据库只有一个内模式。内模式是对数据物理结构和存储方式的描述，是数据在数据库内部的表示方式。内模式的设计目标是将系统的模式(全局逻辑结构)组织成最优的物理模式，以提高数据的存取效率，改善系统的性能指标。数据库管理系统提供内模式描述语言(内模式DDL)来定义内模式。

数据库系统的体系结构4.外模式/模式映像模式描述的是数据的全局逻辑结构，外模式描述的是数据的局部逻辑结构对应于同一个模式可以有任意多个外模式对于每个外模式，数据库系统都有一个外模式/模式映像，它定义了该外模式与模式之间的对应关系。这些映像定义通常包含在各自外模式的描述中。

数据库系统的体系结构5.模式/内模式映像数据库中只有一个模式，也只有一个内模式，所以模式/内模式映像是唯一的，它定义了数据库全局逻辑结构与存储结构之间的对应关系。例如，说明逻辑记录和字段在内部是如何表示的。该映像定义通常包含在模式描述中。数据库系统的体系结构6.两级数据独立性数据独立性(DataIndependence)是指应用程序和数据库的数据结构之间相互独立，不受影响逻辑数据独立性物理数据独立性数据库系统的体系结构逻辑数据独立性当模式改变时(如增加新的关系、新的属性、改变属性的数据类型等)，由数据库管理员对各个外模式/模式映像作相应改变，可以使外模式保持不变。应用程序是依据数据的外模式编写的，因而应用程序不必修改，保证了数据与程序的逻辑独立性，简称逻辑数据独立性。数据库系统的体系结构物理数据独立性当数据库的存储结构改变了(如选用了另一种存储结构)，由数据库管理员对模式/内模式映像作相应改变，可以保证模式保持不变，因而应用程序也不必改变。保证了数据与程序的物理独立性，简称物理数据独立性。数据库系统的体系结构特定的应用程序是在外模式描述的数据结构上编制的，它依赖于特定的外模式，与数据库的模式和存储结构相独立。不同的应用程序可以共用同一外模式。数据库的两级映像保证了数据库外模式的稳定性，从而从底层保证了应用程序的稳定性，除非应用需求本身发生变化，否则应用程序一般不需要修改。1.1什么是数据

数据库系统的体系结构数据与程序之间的独立性，使数据的定义和描述可以从应用程序中分离出去。另外，由于数据的存取由DBMS管理，用户不必考虑存取路径等细节，从而简化了应用程序的编写，大大减少了对应用程序的维护及修改工作。2.1.5常见的关系型数据库管理系统1.oracle2.sqlserver3.mysql4.access2.1.6结构化查询语言1、综合统一2、高度非过程化3、面向集合的操作方式4、以同一种语法结构提供两种使用方式5、语言简洁，易学易用

MySQL是一个单进程多线程、支持多用户、基于客户机/服务器（Client/Server简称C/S）的关系数据库管理系统。性能高效跨平台支持简单易用开源支持多用户优化的sql查询算法2.1.1MySQL特点MySQL的使用流程

2.1.1MySQL特点MySQL其他概念：MySQL服务MySQL服务实例MySQL服务器端口号2.1.1MySQL特点MySQL服务的安装（请参看本书内容）。建议自学，并上机操作。注意：本书使用的MySQL为5.6版本。可以到本书指定的网址下载MySQL图形化安装包mysql-5.6.5-m8-win32.msi。

2.1.2MySQL服务的安装MySQL服务的配置（请参看本书内容）。建议自学，并上机操作。配置过程中的其他知识点：OLAP与OLTP

EnableStrictMode选项字符集/字符序MySQL超级管理员root账户my.ini配置文件中2.1.3MySQL服务的配置MySQL服务的启动与停止（请参看本书内容）。建议自学，并上机操作。Bin目录下的mysqld2.1.4启动与停止MySQL服务my.ini配置文件包含了多种参数选项组，每个参数选项组通过“[]”指定，每个参数选项组可以配置多个参数信息。通常情况下，每个参数遵循“参数名=参数值”这种配置格式，参数名一般是小写字母，参数名大小写敏感。常用的参数选项组有“[client]”、“[mysql]”以及“[mysqld]”参数选项组。2.1.5MySQL配置文件[client]参数选项组：配置了MySQL自带的MySQL5.6命令行窗口可以读取的参数信息。

常用的参数是port（默认值是3306）。修改该port值会导致新打开的MySQL5.6命令行窗口无法连接MySQL服务器。2.1.5MySQL配置文件[mysql]参数选项组：配置了MySQL客户机程序mysql.exe可以读取的参数信息。

常用的参数有“prompt”、“default-character-set=gbk”。

修改“[mysql]”参数选项组中的参数值，将直接影响新打开的MySQL客户机。2.1.5MySQL配置文件[mysqld]参数选项组：配置了MySQL服务程序mysqld.exe可以读取的参数信息，mysqld.exe启动时，将[mysqld]参数选项组的参数信息加载到服务器内存，继而生成MySQL服务实例。2.1.5MySQL配置文件[mysqld]参数选项组：常用的参数有“port”、“basedir”、“datadir”、“character-set-server”、“sql_mode”、“max_connections”以及“default_storage_engine”等。2.1.5MySQL配置文件[mysqld]参数选项组：修改“[mysqld]”参数选项组的参数值，只有重新启动MySQL服务，将修改后的配置文件参数信息加载到服务器内存后，新配置文件才会在新的MySQL服务实例中生效。

如果“[mysqld]”参数选项组的参数信息出现错误，将会导致MySQL服务无法启动。2.1.5MySQL配置文件MySQL客户机（本书使用前两个）：

MySQL5.6命令行窗口

CMD命令提示符窗口

WEB浏览器（例如phpMyAdmin）第三方客户机程序（例如MySQL-Front、MySQL

ManagerforMySQL等）2.1.6MySQL客户机phpMyAdmin2.1.6MySQL客户机MySQL-Front2.1.6MySQL客户机MySQL

ManagerforMySQL2.1.6MySQL客户机2.1.6MySQL客户机HeidiSQLMySQL客户机连接MySQL服务器须提供：合法的登录主机：解决“from”的问题。

合法的账户名以及密码：解决“who”的问题。MySQL服务器主机名（或IP地址）：解决“to”的问题。

端口号：解决“多卡多待”的问题。MySQL客户机与MySQL服务器是同一台主机时，主机名可以使用localhost（或者）。2.1.7连接MySQL服务器当MySQL客户机与MySQL服务器是同一台主机时，打开命令提示符窗口，输入mysql-h-P3306-uroot–proot或者mysql-hlocalhost-P3306-uroot–proot然后回车（注意-p后面紧跟密码root），即可实现本地MySQL客户机与本地MySQL服务器之间的成功连接。2.1.7连接MySQL服务器2.1.7连接MySQL服务器123MySQL由瑞典MySQLAB公司开发，默认情况下MySQL使用的是latin1字符集。由此可能导致MySQL数据库不够支持中文字符串查询或者发生中文字符串乱码等问题。

MySQL字符集与字符序字符集及字符序概念MySQL的字符集转换过程45MySQL字符集的设置SQL脚本文件2.2字符集以及字符序设置字符（Character）是人类语言最小的表义符号，例如‘A’、‘B’等。给定一系列字符，对每个字符赋予一个数值，用数值来代表对应的字符，这个数值就是字符的编码（CharacterEncoding）。给定一系列字符并赋予对应的编码后，所有这些“字符和编码对”组成的集合就是字符集(CharacterSet)。

2.2.1字符集及字符序概念2.2.1字符集及字符序概念字符序(Collation)是指在同一字符集内字符之间的比较规则。一个字符集包含多种字符序，每个字符序唯一对应一种字符集。MySQL字符序命名规则是：以字符序对应的字符集名称开头，以国家名居中（或以general居中），以ci、cs或bin结尾。ci表示大小写不敏感，cs表示大小写敏感，bin表示按二进制编码值比较。2.2.1字符集及字符序概念使用MySQL命令showcharacterset;即可查看当前MySQL服务实例支持的字符集、字符集默认的字符序以及字符集占用的最大字节长度等信息latin1支持西欧字符、希腊字符等gbk支持中文简体字符big5支持中文繁体字符utf8几乎支持世界所有国家的字符。2.2.2MySQL字符集及字符序使用MySQL命令showvariableslike'character%';即可查看当前MySQL服务实例使用的字符集。2.2.2MySQL字符集及字符序

character_set_client：MySQL客户机字符集。character_set_connection：数据通信链路字符集，当MySQL客户机向服务器发送请求时，请求数据以该字符集进行编码。character_set_database：数据库字符集。character_set_filesystem：MySQL服务器文件系统字符集，该值是固定的binary。2.2.2MySQL字符集及字符序character_set_results：结果集的字符集，MySQL服务器向MySQL客户机返回执行结果时，执行结果以该字符集进行编码。character_set_server：MySQL服务实例字符集。character_set_system：元数据(字段名、表名、数据库名等)的字符集，默认值为utf8。2.2.2MySQL字符集及字符序使用MySQL命令showvariableslike'character%';setcharacter_set_client=gbk;setnamesgbk;登陆:Mysql–h–uroot–p123456;CdC:\ProgramFiles\MYSQL\mysql5.6\binMysqld–install服务名–defaults-file=d：路径\my.iniNETSTARTMYSQLNETSTOPMYSQL使用MySQL命令“showcollation;”即可查看当前MySQL服务实例支持的字符序。2.2.2MySQL字符集及字符序2.2.3MySQL字符集的转换过程方法1：修改my.ini配置文件，可修改MySQL默认的字符集。方法2：MySQL提供下列MySQL命令可以“临时地”修改MySQL“当前会话的”字符集以及字符序。2.2.4MySQL字符集的设置setcharacter_set_client=gbk;setcharacter_set_connection=gbk;setcharacter_set_database=gbk;setcharacter_set_results=gbk;setcharacter_set_server=gbk;setcollation_connection=gbk_chinese_ci;setcollation_database=gbk_chinese_ci;setcollation_server=gbk_chinese_ci;2.2.4MySQL字符集的设置方法3：使用MySQL命令“setnamesgbk;”可以“临时一次性地”设置character_set_client、character_set_connection以及character_set_results的字符集为gbk方法4：连接MySQL服务器时指定字符集mysql--default-character-set=字符集

-h服务器IP地址-u账户名–p密码2.2.4MySQL字符集的设置SQL基本的执行方法（两种）：

\.C:\mysql\init.sqlsourceC:\mysql\init.sql连接与断开服务器：1、cmd2、client3、断开服务器2.2.5SQL脚本文件解压版安装1、解压2、考到c:\programfiles\mysql\3、我的电脑-属性-高级-环境变量path：c:\programfiles\mysql\mysql56\bin4、修改配置文件#basedir=C:\ProgramFiles\MYSQL\mysql5.6\bin#datadir=C:\ProgramFiles\MYSQL\mysql5.6\bin\data5、以管理员身份运行cmdCdC:\ProgramFiles\MYSQL\mysql5.6\binMysqld–install服务名–defaults-file=d：路径\my.inimysql6、登录：Mysql–uroot-p123

数据库是存储数据库对象的容器。MySQL数据库的管理主要包括数据库的创建、选择当前操作的数据库、显示数据库结构以及删除数据库等操作

。查看数据库创建数据库显示数据库结构45选择当前操作的数据库删除数据库2.3MySQL数据库管理

createdatabasechoose;成功创建choose数据库后，数据库根目录下会自动创建数据库目录。2.3.1创建数据库

使用MySQL命令showdatabases;即可查看MySQL服务实例上所有的数据库

2.3.2查看数据库information_schema其中保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。Mysql:账户信息，权限信息，存储过程，event,时区等信息。performance_schema:主要用于收集数据库服务器性能参数。

提供以下功能：

1.提供进程等待的详细信息，包括锁、互斥变量、文件信息；

2.保存历史的事件汇总信息，为提供MySQL服务器性能做出详细的判断；

3.对于新增和删除监控事件点都非常容易，并可以随意改变mysql服务器的监控周期.

使用MySQL命令showcreatedatabasechoose;可以查看choose数据库的相关信息（例如MySQL版本ID号、默认字符集等信息）。2.3.3显示数据库结构

执行“usechoose;”命令后，后续的MySQL命令以及SQL语句将自动操作choose数据库中所有数据库对象。

修改数据库：Alterdatabasechoose2.3.4选择当前操作的数据库删除student数据库，使用SQL语句dropdatabasestudent;字符集：字符序：比较规则Showcharacterset；cicsBinCreatedatabasechooseDefaultcharactersetgb2312Defaultcollategb2312_chinese_ci;2.3.5删除数据库123表是数据库中最为重要的数据库对象

设置默认的存储引擎MyISAM和InnoDB存储引擎创建数据库表45显示表结构表记录的管理2.4MySQL表管理67表是数据库中最为重要的数据库对象

删除表InnoDB表空间2.4MySQL表管理MySQL提供了插件式（Pluggable）的存储引擎，存储引擎是基于表的，同一个数据库，不同的表，存储引擎可以不同。甚至同一个数据库表，在不同的场合可以应用不同的存储引擎。

2.4.1MyISAM和InnoDB存储引擎使用MySQL命令“showengines;”，即可查看MySQL服务实例支持的存储引擎。2.4.1MyISAM和InnoDB存储引擎MyISAM是MySQL官方提供的存储引擎，其特点是不支持事务,支持表锁、全文索引、高速存储，对于一些联机分析处理（OLAP）系统，操作速度快。每个MyISAM在磁盘上存储成三个文件。文件名都和表名相同，扩展名分别是.frm（存储表定义）、.MYD(MYData，存储数据)、.MYI(MYIndex，存储索引)。这里特别要注意的是MyISAM不缓存数据文件，只缓存索引文件。InnoDB存储引擎支持事务，主要面向OLTP方面的应用，其特点是行锁设置、支持外键，并支持类似于Oracle的非锁定读，即默认情况下读不产生锁。InnoDB将数据放在一个逻辑表空间中（类似Oracle）。InnoDB通过多版本并发控制来获得高并发性，实现了ANSI标准的4种隔离级别，默认为Repeatable，使用一种被称为next-keylocking的策略避免幻读。对于表中数据的存储，InnoDB采用类似Oracle索引组织表Clustered的方式进行存储。InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比Myisam的存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。1．InnoDB存储引擎的特点支持外键（ForeignKey）支持事务（Transaction）：如果某张表主要提供OLTP支持，需要执行大量的增、删、改操作（insert、delete、update语句），出于事务安全方面的考虑，InnoDB存储引擎是更好的选择。最新版本的MySQL已经开始支持全文检索。oltp面向交易的处理系统，其基本特征是顾客的原始数据可以立即传送到计算中心进行处理，并在很短的时间内给出处理结果。2.4.1MyISAM和InnoDB存储引擎2．MyISAM存储引擎的特点MyISAM具有检查和修复表的大多数工具。MyISAM表可以被压缩

MyISAM表最早支持全文索引

但MyISAM表不支持事务

但MyISAM表不支持外键（ForeignKey）。如果需要执行大量的select语句，出于性能方面的考虑，MyISAM存储引擎是更好的选择。2.4.1MyISAM和InnoDB存储引擎MySQL5.6默认的存储引擎是InnoDB。使用MySQL命令setdefault_storage_engine=MyISAM;可以“临时地”将MySQL“当前会话的”存储引擎设置为MyISAM，使用MySQL命令“showengines;”可以查看当前MySQL服务实例默认的存储引擎。2.4.2设置默认的存储引擎使用SQL语句“createtable表名”即可创建一个数据库表。例如：

usechoose;setdefault_storage_engine=InnoDB;createtablemy_table(todaydatetime,namechar(20));2.4.3创建数据库表成功创建InnoDB存储引擎的my_table表后，MySQL服务实例会在数据库目录choose中自动创建一个名字为表名、后缀名为frm的文件。my_table.frm。2.4.3创建数据库表将my_table表的存储引擎修改为MyISAM。usechoose;altertablemy_tableengine=MyISAM;2.4.3创建数据库表使用MySQL命令“desctable_name;”即可查看表名为table_name的表结构。2.4.4显示表结构使用MySQL命令“showcreatetabletable_name;”，查看名为table_name表的详细信息。2.4.4显示表结构usechoose;insertintomy_tablevalues(now(),'a');insertintomy_tablevalues(now(),'a');insertintomy_tablevalues(now(),NULL);insertintomy_tablevalues(now(),'');select*frommy_table;

2.4.5表记录的管理任务布置1：完成本书场景描述1：MyISAM表记录的管理的任务要求。任务布置2：完成本书场景描述2：InnoDB表记录的管理的任务要求。2.4.5表记录的管理

1.共享表空间：MySQL服务实例承载的所有数据库的所有InnoDB表的数据信息、索引信息、各种元数据信息以及事务的回滚（UNDO）信息，全部存放在共享表空间文件中。默认情况下该文件位于数据库根目录下，文件名是ibdata1，且文件的初始大小为10M。可以使用MySQL命令“showvariableslike‘innodb_data_file_path’;”查看该文件的的属性。

2.4.6InnoDB表空间2.4.6InnoDB表空间元数据（Metadata），又称中介数据、中继数据，为描述数据的数据（dataaboutdata），主要是描述数据属性（property）的信息，用来支持如指示存储位置、历史数据、资源查找、文件记录等功能。元数据算是一种电子式目录

2.独享表空间：如果将全局系统变量innodb_file_per_table的值设置为ON（innodb_file_per_table的默认值为OFF），那么之后再创建InnoDB存储引擎的新表，这些表的数据信息、索引信息都将保存到独享表空间文件。2.4.6InnoDB表空间独享表空间的设置2.4.6InnoDB表空间usechoose;altertablemy_tableengine=InnoDB;createtablesecond_table(todaydatetime,namechar(20));2.4.6InnoDB表空间2.4.6InnoDB表空间ibd是MySQL数据文件、索引文件，无法直接读取。

frm是表结构文件，可以直接打开。

任务布置3：完成本书场景描述3的任务要求。2.4.6InnoDB表空间使用SQL语句droptabletable_name;即可删除名为table_name的表。删除表后，MySQL服务实例会自动删除该表结构定义文件（例如second_table.frm文件），以及数据、索引信息。该命令慎用！

2.4.7删除表123MySQL数据库中，变量分为系统变量（以@@开头）以及用户自定义变量（以@开头）。查看系统变量的值全局系统变量与会话系统变量设置系统变量的值2.5系统变量每一个MySQL客户机成功连接MySQL服务器后，都会产生与之对应的会话。会话期间，MySQL服务实例会在MySQL服务器内存中生成与该会话对应的会话系统变量，这些会话系统变量的初始值是全局系统变量值的拷贝。

2.5.1全局系统变量与会话系统变量由于各会话在会话期间所做的操作不尽相同，为了标记各个会话，会话系统变量又新增了12个变量。2.5.1全局系统变量与会话系统变量2.5.1全局系统变量与会话系统变量

使用“showglobalvariables;”命令即可查看MySQL服务器内存中所有的全局系统变量信息（有393项之多）。使用“showsessionvariables;”命令即可查看与当前会话相关的所有会话系统变量以及所有的全局系统变量（有405项之多），此处session关键字可以省略。

2.5.2查看系统变量的值

MySQL中有一些系统变量仅仅是全局系统变量，例如innodb_data_file_path。showglobalvariableslike'innodb_data_file_path';showsessionvariableslike'innodb_data_file_path';showvariableslike'innodb_data_file_path';2.5.2查看系统变量的值

MySQL中有一些系统变量仅仅是会话系统变量，例如MySQL连接ID会话系统变量pseudo_thread_id。showsessionvariableslike'pseudo_thread_id';showvariableslike'pseudo_thread_id';2.5.2查看系统变量的值MySQL中有一些系统变量既是全局系统变量，又是会话系统变量，例如系统变量character_set_client既是全局系统变量，又是会话系统变量。2.5.2查看系统变量的值

此时查看会话系统变量的方法：showsessionvariableslike'character_set_client';showvariableslike'character_set_client';此时查看全局系统变量的方法：

showglobalvariableslike'character_set_client';2.5.2查看系统变量的值作为MySQL编码规范，MySQL中的系统变量以两个“@”开头。@@global仅仅用于标记全局系统变量；@@session仅仅用于标记会话系统变量；@@首先标记会话系统变量，如果会话系统变量不存在，则标记全局系统变量。

2.5.2查看系统变量的值方法一：修改MySQL源代码，然后对MySQL源代码重新编译（该方法适用于MySQL高级用户，这里不作阐述）。方法二：最为简单的方法是通过修改MySQL配置文件，继而修改MySQL系统变量的值（此方法需要重启MySQL服务）。方法三：在MySQL服务运行期间，使用“set”命令重新设置系统变量的值。

2.5.2查看系统变量的值设置全局系统变量的值的方法：set@@global.innodb_file_per_table=default;set@@global.innodb_file_per_table=ON;setglobalinnodb_file_per_table=ON;2.5.2查看系统变量的值

设置会话系统变量的值的方法：set@@session.pseudo_thread_id=5;setsessionpseudo_thread_id=5;set@@pseudo_thread_id=5;setpseudo_thread_id=5;Setglobalquery_cache_type=1启用查询缓存Setglobalquery_cache_size=1024*1024*16还有内存、日志、及其它方面的变量：临时用表、结果排序、sql缓存，slow_query_log慢日志，sql_mode2.5.2查看系统变量的值Setglobalquery_cache_type=1启用查询缓存Setglobalquery_cache_size=1024*1024*16Setglobalinnodb_file_per_table=ON独享表空间altertablemy_tableengine=MyISAM;setdefault_storage_engine=InnoDB;showengines;

MySQL中还有一些特殊的全局系统变量（例如log_bin、tmpdir、version、datadir），在MySQL服务实例运行期间它们的值不能动态修改，不能使用“set”命令进行重新设置，这种变量称为静态变量。数据库管理员可以使用方法一或者方法二对静态变量的值重新设置。

2.5.2查看系统变量的值步骤1：准备工作方法一：停止MySQL服务方法二：使用MySQL命令“flushtableswithreadlock;”将服务器内存中的数据“刷新”到数据库文件中，同时锁定所有表，以保证备份期间不会有新的数据写入。2.6MySQL数据库备份和恢复步骤2：备份文件的选取如果数据库中全部是MyISAM存储引擎的表，最为简单的数据库备份方法就是直接“备份”整个数据库目录。如果某个数据库中还存在InnoDB存储引擎的表，此时不仅需要“备份”整个数据库目录，还需要备份ibdata1表空间文件以及重做日志文件ib_logfile0与ib_logfile1。2.6MySQL数据库备份和恢复步骤2：备份文件的选取数据库备份时，建议将MySQL配置文件（例如my.ini配置文件）一并进行备份。2.6MySQL数据库备份和恢复步骤3：数据库恢复首先停止MySQL服务；然后将整个数据库目录、MySQL配置文件（例如my.ini配置文件）、ibdata1共享表空间文件以及重做日志文件ib_logfile0与ib_logfile1复制到新MySQL服务器对应的路径，即可恢复数据库中的数据。2.6MySQL数据库备份和恢复Thanks郭水泉MySQL数据库基础与实例教程

之MySQL表结构的管理任务：为petstore数据库表选择合适的数据类型1.用户表account用户编号、用户名、密码、性别、住址、邮箱、电话2.商品分类表category类别编号、分类名称、类别描述3.商品表product商品编号、类别编号、商品名、商品介绍、市场价格、成本价格、数量4.订单表orders订单号、用户编号、订单日期、商品介绍、订单总价、订单状态5.订单明细表lineitem订单号、商品编号、数量、单价123本章详细讲解“选课系统”数据库各个表的实施过程，通过本章的学习，读者可以掌握表结构管理的相关知识。

4创建表MySQL数据类型修改表结构删除表5索引内容一览123MySQL提供的数据类型包括数值类型（整数类型和小数类型）、字符串类型、日期类型、复合类型（复合类型包括enum类型和set类型）以及二进制类型。MySQL小数类型MySQL整数类型MySQL字符串类型45MySQL日期类型MySQL复合类型3.1MySQL数据类型67为字段选择合适的数据类型MySQL二进制类型MySQL提供的数据类型包括数值类型（整数类型和小数类型）、字符串类型、日期类型、复合类型（复合类型包括enum类型和set类型）以及二进制类型。3.1MySQL数据类型3.1.1MySQL整数类型整数类型的数，默认情况下既可以表示正整数又可以表示负整数（此时称为有符号数）。如果只希望表示零和正整数，可以使用无符号关键字“unsigned”对整数类型进行修饰（此时称为无符号整数）。

例如：scoretinyintunsigned3.1.1MySQL整数类型3.1.1MySQL整数类型3.1.2MySQL小数类型decimal(length,precision)用于表示精度确定（小数点后数字的位数确定）的小数类型，length决定了该小数的最大位数，precision用于设置精度（小数点后数字的位数）。例如：decimal(5,2)表示小数取值范围：-999.99～999.99decimal(5,0)表示：

-99999～99999的整数。3.1.2MySQL小数类型3.1.2MySQL小数类型字符串类型的数据外观上使用单引号括起来，例如学生姓名'张三'、课程名'java程序设计'等。3.1.3MySQL字符串char()与varchar():存储或检索过程中不进行大小写转换。对于简体中文字符集gbk的字符串而言，varchar(255)表示可以存储255个汉字，而每个汉字占用两个字节的存储空间。假如这个字符串没有那么多汉字，例如仅仅包含一个‘中’字，那么varchar(255)仅仅占用1个字符（两个字节）的储存空间，另加一个字节记录长度.varchar(1000)需要多大空间?而char(255)则必须占用255个字符长度的存储空间，哪怕里面只存储一个汉字。Char、archar，当存储或检索尾部带空格的字符串时有什么区别。3.1.3MySQL字符串类型date表示日期，默认格式为‘YYYY-MM-DD’；time表示时间，格式为‘HH:mm:ss’；year表示年份；datetime与timestamp是日期和时间的混合类型，格式为'YYYY-MM-DDHH:mm:ss'3.1.4MySQL日期类型datetime（8字节）与timestamp（4字节）都是日期和时间的混合类型，区别在于：表示的取值范围不同，datetime的取值范围远远大于timestamp的取值范围。datetime保存的时间范围较大，年的数字在：1000---9999之间timestamp保存的时间较小，年的数字在：1970--2037之间将NULL插入timestamp字段后，该字段的值实际上是MySQL服务器当前的日期和时间。同一个timestamp类型的日期或时间，不同的时区，显示结果不同。

3.1.4MySQL日期类型任务布置1：理解datetime与timestamp之间的区别。上机操作，完成本书场景描述1以及场景描述2的任务要求，理解datetime与timestamp之间的区别。学会使用now()函数。注意：now()函数用于获得MySQL服务器的当前时间，该时间与时区的设置密切相关。

3.1.4MySQL日期类型MySQL支持两种复合数据类型：enum枚举类型和set集合类型。

enum类型的字段类似于单选按钮的功能，一个enum类型的数据最多可以包含65535个元素。

set类型的字段类似于复选框的功能，一个set类型的数据最多可以包含64个元素。3.1.5MySQL复合类型字符-数字映射表任务布置2：上机操作，完成本书场景描述3的任务要求，理解复合类型的用法。注意：MySQL模式对复合类型数据的影响。3.1.5MySQL复合类型二进制类型的字段主要用于存储由‘0’和‘1’组成的字符串，因此从某种意义上将，二进制类型的数据是一种特殊格式的字符串。二进制类型与字符串类型的区别在于：字符串类型的数据按字符为单位进行存储，因此存在多种字符集、多种字符序；而二进制类型的数据按字节为单位进行存储，仅存在二进制字符集binary。

3.1.6MySQL二进制类型3.1.6MySQL二进制类型Blob和text类型1.blob二进制大数据存储：声音、图像、图片、视频2.Text字符主要是文字方面的数据存储:简介、说明等3.存储或检索过程中不进行大小写转换，排序和索引使用前几个字符：max_short_length4.最大值由数据类型确定，客户端和服务器之间传递的最大值由可用内存和通信缓存区大小确定：max_allowd_packet修改

选择合适的数据类型，不仅可以节省储存空间，还可以有效地提升数据的计算性能。（1）更小的通常更好：在符合应用要求（取值范围、精度）的前提下，尽量使用“短”数据类型（2）简单就好：数据类型越简单越好（3）在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储日期和时间。3.1.7选择合适的数据类型（4）尽量采用精确小数类型（例如decimal），而不采用浮点数类型。使用精确小数类型不仅能够保证数据计算更为精确，还可以节省储存空间，例如百分比使用decimal(4,2)即可。计算时转化为double（5）尽量避免NULL字段，建议将字段指定为NOTNULL约束。Null字段很难优化，使得索引、索引统计和值比较都更复杂。（6）从存储和计算来说int（1）和int（20）是相同的，但显示不同3.1.7选择合适的数据类型（7）位数据类型Bit、set（可以用整数代替）（8）标识列的选择整型是最好的选择Char为什么差些？标识列与其它有关联的列的类型相同Ip地址如何存储123createtable表名(字段名1数据类型[约束条件],…[其他约束条件],[其他约束条件])其他选项（例如存储引擎、字符集等选项）设置自增型字段设置约束其他选项的设置45创建“选课系统”数据库表复制一个表结构3.2创建表1．设置主键（PrimaryKey）约束（1）如果一个表的主键是单个字段，直接在该字段的数据类型或者其他约束条件后加上“primarykey”关键字，即可将该字段设置为主键约束，语法规则如下。字段名数据类型[其他约束条件]primarykey例如：useridchar(6)primarykey，3.2.1设置约束1．设置主键（PrimaryKey）约束（2）如果一个表的主键是多个字段的组合（例如字段名1与字段名2共同组成主键），定义完所有的字段后，使用下面的语法规则将（字段名1,字段名2）设置为复合主键。primarykey(字段名1,字段名2)2.设置外键FOREIGNKEY(`rootid`)REFERENCESroottb(`id`)ONDELETE/updateCASCADE3.2.1设置约束

CASCADE删除包含与已删除键值有参照关系的所有记录

SETNULL修改包含与已删除键值有参照关系的所有记录，使用NULL值替换（只能用于已标记为NOTNUL