作者：孔祥盛
出版社：人民邮电出版社
格式: AZW3, DOCX, EPUB, MOBI, PDF, TXT
MySQL核心技术与最佳实践试读：
前言
本书的写作背景
目前成熟的关系数据库管理系统主要源自欧美数据库厂商，典型的有美国微软公司的SQL Server、美国IBM公司的DB2 和Informix、德国SAP公司的Sybase、美国甲骨文公司的Oracle以及MySQL，这些数据库管理系统（MySQL除外）都是商业数据库，价格昂贵。截至目前国产数据库管理系统还不成熟，国内很多大型企业，也不乏核心部门（例如金融银行、移动通信、石油行业、12306 铁路订票等），不得不依赖进口，“享受着”国外数据库厂商提供服务的同时，不得不将核心部门的信息交由国外数据库管理，企业的核心数据、核心部门的机密数据的不安全因素剧增。
另外，自2001年中国加入世界贸易组织（WTO）以来，中美、中欧之间的贸易摩擦、尤其是软件版权纠纷不断加剧。考虑到国家信息安全战略因素以及知识产权保护因素，为了大力发展、培养自己的软件企业，中国政府、高校越来越重视数据安全、版权制度建设以及知识产权的保护。学习、使用、开发、研究开放源代码的软件（简称为开源软件），不仅可以有效缩短国内软件与国外软件之间的差距，还可以减少经费的投入。人们所熟知的Android操作系统、Linux 操作系统、MySQL 数据库管理系统、Apache 应用服务器软件都是开源软件。有些开源软件（例如 Android）不仅改变了人们的生活，甚至改变了企业的命运。开源软件的学习、使用、开发、研究势在必行。
作为世界上最受欢迎的开源数据库管理系统，MySQL 数据库管理系统具有成本低廉、体积小、便于安装、性能优越、功能强大等特点，很多企业将MySQL 作为首选数据库管理系统。目前淘宝、百度、新浪微博等越来越多的应用已经将部分业务数据迁移到MySQL数据库中，MySQL的应用前景可观。
然而相对于 SQL Sever、Oracle 等商业数据库管理系统而言，市场上适合学习、使用、研究MySQL的书籍还比较少，好的MySQL书籍更是凤毛麟角，这也是编写本书的初衷，希望本书能够为各类读者提供一条学习、使用、研究MySQL 的捷径，更希望本书能够帮助更多的数据库爱好者走进数据库开发的殿堂。
本书的写作思路
学习的过程好比导演拍摄电影，不仅需要演员（读者）的配合，还需要挑选好的剧本（书籍）。好的剧本可以让演员（读者）更容易被剧情所吸引，以便更顺利地融入剧情，不仅可以缩短拍摄周期，节省拍摄成本，还可以让演员（读者）真正地成为“剧情”中的主角。曾经看到一种极端的说法：中国不缺好的导演、好的演员，而是缺少好的剧本，对于学习亦是如此。
作为世界上最受欢迎的开源数据库管理系统，MySQL 受到了广大自由软件爱好者甚至商业软件用户的青睐。MySQL爱好者学习MySQL知识，需要一部这样的“剧本”：让读者快速地融入“剧情”，变读者“被动学习”为“主动学习”的一部“剧本”。然而，目前国内市场讲述MySQL知识的书籍还比较少，好的MySQL书籍更是凤毛麟角。
数据库技术主要用于解决“商业问题”，面对MySQL繁而杂的数据库知识，很多MySQL爱好者感到束手无策。如果再将“商业知识”掺入其中，学好MySQL核心技术，MySQL爱好者只能望洋兴叹。为了便于初学者专心地学习MySQL核心技术，本书精心设计了简单易懂的案例，以便读者可以巧妙地避开商业领域有关商业知识的学习。通过该案例，读者可以在短时间内掌握MySQL几乎所有的核心技术，从而能够快速地、轻松地学习MySQL繁而杂的数据库知识。除此之外，为了便于MySQL爱好者快速上手，本书选用Windows操作系统作为开发平台部署MySQL服务器。
本书的内容结构
本书从关系数据库设计、MySQL 基础、MySQL 编程、MySQL 管理及维护4个角度，深入浅出地对MySQL的核心技术进行了详细的介绍，并通过综合实训提供了MySQL核心技术的最佳实践。
关系数据库设计：旨在强调关系数据库设计的重要性。数据库开发人员通常存在致命的误区：重开发，轻设计。开发出来的数据库往往成了倒立的金字塔，头重脚轻。真正的数据库开发，首先强调的是设计，其次是开发。正因为如此，本书将有关软件工程思想以及关系数据库设计方法的内容前置到第1章，并将这些内容融入到数据库开发过程中，并对数据库的开发过程，进行了详细讲解。针对本书的案例，提出多种数据库设计解决方案，并对这些解决方案进行了分析、比较。
MySQL 基础：MySQL 开发人员不仅需要致力于 SQL 语法的学习，还需要全面了解MySQL的特性，例如存储引擎、MySQL字符集、MySQL系统变量、MySQL数据类型等。本部分通过具体案例，对这些知识点进行通俗易懂、步骤详细的讲解。除此之外，本书还对MySQL开发人员很容易忽视的数据库重要概念——索引，进行了详尽的诠释。
MySQL编程：旨在讲解MySQL语言结构。通过本部分的学习，可以使读者快速跃身为MySQL中级用户。本部分详细讲解MySQL常量与变量、MySQL语言结构、自定义函数、系统函数、触发器、临时表、派生表、存储过程、游标、MySQL错误处理、事务机制和锁机制等MySQL核心技术。由于这部分内容繁而杂，本书尽量将抽象问题形象化、具体化，复杂问题图形化、简单化，以便读者快速掌握 MySQL 核心技术。本部分通过具体案例，提供了 MySQL核心技术的最佳实践。
综合实训：做到了项目实训与MySQL知识的完美融合。本部分详细讲解如何使用PHP编程语言实现具体案例。本部分实现了编程语言与全文检索、存储过程、触发器、函数、事务、锁等MySQL核心知识的高度融合，以便读者更清楚地意识到数据库在应用程序开发过程中占据的举足轻重的地位。
MySQL管理及维护：适用于MySQL高级用户，旨在全面讲解MySQL日志与事件、MySQL备份与恢复、MySQL安全管理、MySQL优化以及MySQL Cluster等MySQL核心技术，以便读者能够胜任MySQL数据库管理员的角色，顺利地实现MySQL数据库的备份、恢复、优化、安全管理以及数据迁移。
本书的写作特点
笔者坚持“理论知识够用、专业知识实用、专业技能会用”的原则，通过多年数据库开发经验的积累和总结，编排了本书的篇章结构。本书突出自学和模仿的特点，读者只要按照章节顺序上机操作，即可掌握MySQL核心技术。本书具有如下特色。
1．强调MySQL核心技术的最佳实践
MySQL 核心技术包括：存储引擎、MySQL 字符集、MySQL 系统变量、MySQL 数据类型、MySQL 变量、索引以及全文索引、MySQL 语言结构、自定义函数、系统函数、触发器、临时表、派生表、存储过程、游标、MySQL错误处理、事务机制和锁机制、MySQL 日志与事件、MySQL 备份与恢复、MySQL 安全管理、MySQL 优化以及 MySQL Cluster 等。本书以讲解 MySQL核心技术为目标，以案例的实现为载体，以不同的章节完成不同的任务为理念，采用软件工程的思想实现具体案例，强调MySQL核心技术的最佳实践。
2．入门门槛低，讲解细腻
本书尽量将抽象问题形象化，复杂问题图形化、简单化。即便读者没有任何数据库基础，也丝毫不会影响MySQL核心技术的学习。为了向读者还原真实的开发环境，本书在内容组织上保留了一定数量的截图显示执行结果，有些截图至关重要，读者甚至可以从截图中得到一些结论。当然这些截图无疑增加了本书的版面，希望能够得到读者的理解。
3．案例虽小，五脏俱全
本书选择的案例大小适中，且易于理解。本书不仅将全文检索、存储过程、触发器、函数、事务、锁等MySQL核心技术融入其中，而且做到了商业问题与MySQL知识的完美融合，项目实训与MySQL知识的完美融合。读者无需太多技术基础，就可以非常轻松地掌握数据库设计技术、MySQL核心技术以及应用程序开发相关技术，不知不觉中掌握使用数据库技术解决商业问题的能力。
4．选用尽可能少的数据库表讲解MySQL核心技术
本书选用尽可能少的数据库表讲解MySQL尽可能多的知识，以便读者能够将所有精力集中在MySQL知识点的学习上。本书所使用的数据库表不超过10张，经常使用的数据库表不超过5张，使用5张表讲解MySQL几乎所有的知识点，很大程度上可以减轻读者的负担。
5．内容丰富、严谨
本书内容丰富，几乎囊括MySQL所有核心技术。本书的内容编排一气呵成，且遵循知识的学习曲线，并尽量做到不留死角。本书对MySQL内容的选取非常严谨，一环扣一环，从一个知识点过渡到另一个知识点非常顺畅和自然，章节之间循序渐进，内容不冲突、不重复、不矛盾。
6．强调数据库开发人员、数据库管理人员工作中的必备知识和技能
本书注重数据库开发人员、数据库管理人员工作中必备知识和技能的讲解，强调软件工程以及数据库设计方法，并且全面讲解MySQL日志与事件、MySQL 备份与恢复、MySQL 安全管理、MySQL 优化以及 MySQL Cluster 等高级知识，以便读者更快速地胜任数据库开发人员、数据库管理人员的工作环境，顺利地实现MySQL数据库的开发、备份、恢复、优化、安全管理以及数据迁移。
鉴于上述特点，笔者相信：本书能够成为一本读者自学MySQL的好“剧本”，是一部面向广大MySQL爱好者的一本实用参考书。
本书由孔祥盛任主编，潘红任主审，孟现勇、张天伍、龚立雄、吴华稳、陈淼超和万润泽任副主编，参加编写工作的还有张珂、欧阳艾嘉、李东平、李敏、孙婧和孙大鹏。其中陈淼超编写第2章，吴华稳编写第3章，龚立雄编写第4章和第7章，孙大鹏编写第5章，孟现勇编写第6章，李东平编写第8章和第9章，万润泽编写第10章，张珂编写第11章，李敏编写第12章，欧阳艾嘉编写第13章，张天伍编写第14章，孙婧编写第15章，并对全书的代码进行了测试，孔祥盛编写其余内容，并对本书的案例以及组织架构进行了设计。
由于本书涉及面广，加之笔者经验有限，书中难免存在不妥之处，敬请广大读者批评指正。未经许可，不得以任何方式复制或抄袭本书之部分或全部内容。版权所有，侵权必究。编者2014年2月第一篇关系数据库设计
数据库设计概述
数据库概述
数据库设计的相关知识
E-R图
关系数据库设计第1章数据库设计概述
数据库是信息系统的核心，在信息社会中占据着举足轻重的地位。数据库技术主要研究如何科学地组织、存储和管理数据库中的数据。采用科学的方法开发、设计一个结构良好的数据库，是所有数据库开发人员应该掌握的最基本技能。本章抛开MySQL讲解关系数据库设计的相关知识，以“选课系统”为例，讲解“选课系统”数据库的设计流程。通过本章的学习，读者将具备一定的数据库设计能力。1.1 数据库概述
简单地说，数据库（Database，DB）是存储、管理数据的容器；严格地说，数据库是“按照某种数据结构对数据进行组织、存储和管理的容器”。无论哪一种说法，数据永远是数据库的核心。1.1.1 关系数据库管理系统
数据是数据库的核心。数据库容器通常包含诸多数据库对象，如表、视图、索引、函数、存储过程、触发器、事件等，这些数据库对象最终都是以文件的形式存储在外存（例如硬盘）上。数据库用户如何能够访问到数据库容器中的数据库对象呢？事实上，通过“数据库管理系统”，数据库用户可以轻松地实现数据库容器中各种数据库对象的访问（增、删、改、查等操作），并可以轻松地完成数据库的维护工作（备份、恢复、修复等操作），如图1-1所示。▲图1-1 数据库管理系统与操作系统之间的关系
数据库管理系统（Database Management System， DBMS）安装于操作系统之上，是一个管理、控制数据库容器中各种数据库对象的系统软件。可以这样理解：数据库用户无法直接通过操作系统获取数据库文件中的具体内容；数据库管理系统通过调用操作系统的进程管理、内存管理、设备管理以及文件管理等服务，为数据库用户提供管理、控制数据库容器中各种数据库对象、数据库文件的接口。
数据库管理系统通常会选择某种“数学模型”存储、组织、管理数据库中的数据，常用的数学模型包括“层次模型”、“网状模型”、“关系模型”以及“面向对象模型”等。基于“关系模型”的数据库管理系统称为关系数据库管理系统（Relational Database Management System，RDBMS）。随着关系数据库管理系统的日臻完善，目前关系数据库管理系统已占据主导地位。
通过关系数据库管理系统，数据库开发人员可以轻而易举地创建关系数据库容器，并在该数据库容器中创建各种数据库对象（表、索引、视图、存储过程、触发器、函数等）以及维护各种数据库对象。目前成熟的关系数据库管理系统主要源自欧美数据库厂商，典型的有美国微软公司的SQL Server、美国IBM公司的DB2和Informix、德国SAP公司的Sybase、美国甲骨文公司的Oracle，然而这些数据库都是商业数据库，且价格昂贵。截至目前，国产数据库管理系统还不成熟，国内很多大型企业，也不乏核心部门（例如金融银行、移动通信、石油行业、12306铁路订票等）不得不依赖进口，“享受着”国外数据库厂商提供服务的同时，不得不将核心部门的信息交由国外数据库管理，企业的核心数据、核心部门的机密数据的不安全因素剧增。
另外，2001 年中国加入世界贸易组织（WTO）以来，中美、中欧之间的贸易摩擦，尤其是软件版权纠纷不断加剧。考虑到国家信息安全战略因素以及知识产权保护因素，为了大力发展、培养自己的软件企业，中国政府、高校越来越重视数据安全、版权制度建设以及知识产权的保护。学习、使用、开发、研究开放源代码的软件（简称开源软件），不仅可以有效缩短国内软件与国外软件之间的差距，还可以减少经费的投入。大家所熟知的Android操作系统、Linux操作系统、MySQL数据库管理系统、Apache服务器软件都是开源软件，有些开源软件（例如Android操作系统）不仅改变了人们的生活，而且改变了企业的命运。开源软件的学习，使用、开发、研究势在必行。
与上述商业化的数据库管理系统相比，MySQL 具有开源、免费、体积小、便于安装，而且功能强大等特点。很多企业将MySQL作为首选数据库管理系统，MySQL成为了全球最受欢迎的数据库管理系统之一。目前，淘宝、百度、新浪微博已经将部分业务数据迁移到MySQL数据库中，MySQL的应用前景可观。
考虑到 MySQL 成本低廉、开源、免费、易于安装、性能高效、功能齐全等特点，因此 MySQL非常适合教学。从2013年下半年开始，全国计算机等级考试二级中新增了“MySQL数据程序设计”考试科目，足以看到教育部门对开源软件学习、使用、开发、研究的重视程度。目前越来越多的高校相继开设了MySQL 课程，然而相对SQL Sever、Oracle等商业数据库管理系统而言，市场上适合中国读者学习的MySQL书籍少之又少，这也是本书编写的初衷，希望本书能够为各类读者提供一条学习MySQL的捷径，更希望本书能够帮助更多的读者走进数据库开发的殿堂。
说明
本章主要讲解关系数据库设计的相关知识，本书从第2章开始，才会讲解MySQL的相关知识。1.1.2 关系数据库
目前主流的数据库使用的“数学模型”是“关系”数据模型（简称关系模型），使用关系模型对数据进行组织、存储和管理的数据库称为关系数据库。关系数据库中所谓的“关系”，实质上是一张二维表。
以“选课系统”为例，教师申报课程相关信息（其中包括课程名、人数上限、任课教师及课程描述等信息），并将课程信息录入到“选课系统”数据库的课程表（二维表）中，如表1-1所示。管理员从课程表（二维表）中获取课程信息，并对课程进行审核（修改课程表中课程的状态信息）；接着学生可以从课程表（二维表）中获取已经审核的课程信息进行浏览，然后选修自己感兴趣的课程，填入选课表中（二维表）；期末考试结束后，任课教师把学生的考试成绩录入到选课表（二维表）中……越来越多的二维表就构成了“选课系统”数据库。可以看出，一个数据库通常包含多个二维表（称为数据库表，或者简称为表），从而实现某个应用各类信息的存储和维护。
数据库表是由列和行构成的，表中的每一列（也叫字段）都由一个列名（也叫字段名）进行标记；除了字段名那一行，表中的每一行称为一条记录。表1-1 所示的课程表共有5个字段以及 5 条记录。外观上，关系数据库中的一个数据库表和一个不存在“合并单元格”的电子表格（例如Excel）相同。与电子表格不同的是：同一个数据库表的字段名不能重复。为了优化存储空间以及便于数据排序，数据库表的每一列必须指定某种数据类型。当然，数据库表与电子表格的区别并不局限于此，随着学习的深入，读者可以了解它们之间更多的区别。表1-1 课程表（二维表）
需要注意的是，作为数据库中最为重要的数据库对象，数据库表的设计过程并非一蹴而就，表1-1所示的课程表根本无法满足“选课系统”的功能需求（甚至该表就是一个设计失败的数据库表）。事实上，数据库表的设计过程并非如此简单，本章的重点就是讨论如何设计结构良好的数据库表。数据库中还包含其他数据库对象，如触发器、存储过程、视图、索引、函数等，这些知识将在后续章节进行详细讲解。1.1.3 结构化查询语言SQL
结构化查询语言（Structured Query Language，SQL）是一种应用最为广泛的关系数据库语言，该语言定义了操作关系数据库的标准语法，几乎所有的关系数据库管理系统都支持SQL。使用SQL可以轻松地创建、管理关系数据库的各种数据库对象以及维护数据库中的各种数据，例如删除“选课系统”中课程表（course）的所有记录，使用结构化查询语言“delete from course”语句可以轻松地实现。
SQL 仅仅提供了一套标准语法，为了实现更为强大的功能，各个关系数据库管理系统都对 SQL标准进行了扩展，典型的有Oracle 的PL/SQL，SQL Server 的T-SQL。MySQL 也对SQL 标准进行了扩展（虽然至今没有命名），例如，MySQL命令“show databases;”用于查询当前MySQL 服务实例所有的数据库名，该命令是MySQL的特有命令，并不是SQL标准中定义的SQL语句，该命令在其他数据库管理系统中运行时将报错，例如，在 SQL Server 中运行该命令时，显示“未能找到存储过程'show'”错误信息。这些扩展命令导致了各个数据库产品之间的差异，这种差异为同一个数据库在不同数据库产品之间的移植带来诸多不便。
说明
为了区分SQL扩展以及SQL标准，本书将符合SQL标准的代码称为“SQL语句”，将SQL扩展部分的代码称为“MySQL命令”或者“MySQL语句”。例如“delete from course”是SQL语句；“show databases;”是MySQL命令或者MySQL语句。
SQL 并不是一种功能完善的程序设计语言，例如，不能使用 SQL 构建人性化的图形用户界面（Graphical User Interface，GUI），程序员需要借助 Java、VC++等面向对象程序设计语言或者 HTML的FORM表单构建图形用户界面（GUI）。如果选用FORM表单构建GUI，程序员还需要使用JSP、PHP或者.NET编写Web应用程序，处理FORM表单中的数据以及数据库中的数据，这些知识将在“网上选课系统的开发”章节中进行详细讲解。1.2 数据库设计的相关知识
数据库设计是一个“系统工程”，要求数据库开发人员：
● 熟悉“商业领域”的商业知识，甚至是该商业领域的专家。
● 利用“管理学”的知识与其他开发人员进行有效沟通。
● 掌握一些数据库设计辅助工具。
说明
本书提到的数据库开发人员指的是能够从事各种应用系统的数据库开发工作的相关人员，主要包括能够从事需求分析、数据库建模、数据库设计、数据库实施及编写函数、存储过程或者触发器等数据库开发工作的相关人员。限于篇幅，本书将选择一个大小合适、认知度合适的案例展现数据库设计、开发的所有流程，并对该案例的应用程序使用软件工程的思想进行开发。1.2.1 商业知识和沟通技能
数据库中存储的数据是“商业领域”的信息，使用数据库技术可以解决“商业领域”的“商业问题”。对于数据库开发人员而言，商业知识和沟通技巧永远是避不开的话题。数据库开发人员必须熟悉某种商业领域的商业知识，甚至是该商业领域的专家，才能使用数据库技术解决商业问题。试想一个不熟悉、不了解金融服务业（或者制造业、零售业等行业）运作流程的数据库开发人员，即便掌握了数据库开发的所有技能，也不可能设计一个结构良好的金融服务业（或者制造业、零售业等行业）数据库。
设计数据库时，数据库开发人员经常与其他开发人员（包括最终用户）一起工作，并且需要使用“管理学”的知识与其他开发人员进行有效沟通，获取所需商业信息，从而解决商业问题。因此，对于数据库开发人员而言，沟通的技巧也不能小觑。
熟悉一种“商业领域”的商业知识需要花费大量的时间，很多数据库开发人员用毕生精力研究某个特定行业，从而成为该“商业领域”的专家，继而可以成功地设计该“商业领域”的数据库。同样对于读者而言，必须了解某一“商业领域”的商业知识，才能将数据库技术应用到该“商业领域”，解决该“商业领域”的“商业问题”，进而才能更有效地学习数据库的相关知识。
鉴于多数读者有过“网上选课”的经历，限于篇幅，本书选用“选课系统”作为案例，尽量避开“商业领域”和“管理学”相关知识的讲解，着重讲解数据库设计、开发过程中使用到的各种数据库技术。通过该案例的讲解，读者能够在最短的时间内具备一定的数据库设计、开发能力，继而能够尽快地掌握使用数据库技术解决“商业问题”的能力。1.2.2 数据库设计辅助工具
数据库开发是软件开发过程中一个非常重要的环节，甚至是一个核心环节。软件开发过程中，软件开发人员经常使用一些辅助工具提高软件开发的速度与质量，典型的辅助工具包括模型、工具和技术。这些辅助工具由软件开发专业人员根据自身经验提炼而成，且日益成熟。开发数据库时，数据库开发人员同样也需要使用一些数据库设计的辅助工具，从而提高数据库的开发速度与质量。
1．模型
软件开发时经常使用到一些模型，模型是现实世界中事物特征与事物行为的抽象。模型包括数学模型（例如数学公式）、描述模型（例如报表、列表、备忘录等）和图形模型（例如E-R图、数据流程图DFD、类图等）。对事物的特征进行抽象的过程称为数据建模。软件开发过程中通过数据建模，可以得到软件系统的E-R图或者类图等数据模型。对事物的行为进行抽象的过程称为业务建模。软件开发过程中通过业务建模，可以得到软件系统的程序流程图、数据流程图DFD、时序图、状态图等业务模型。
一般而言，数据库设计更侧重于数据建模，程序设计更侧重于业务建模。然而在真实的软件开发环境中，数据建模与业务建模两者相辅相成，不可或缺。E-R图是关系数据库数据建模过程中经常使用的数据模型。本章将对E-R图进行详细讲解，其他模型的相关知识请读者参考软件工程类的书籍，限于篇幅，本书不再赘述。
2．工具
软件开发时经常使用到一些工具，这些工具为创建模型或其他组[1]件提供了软件支持。例如在系统规划阶段 ，需求分析人员经常使用软件项目管理工具为任务分配资源、跟踪进度以及管理预算，常用的软件项目管理工具是美国微软公司的Project。在系统分析与设计阶段，需求分析人员经常使用计算机辅助系统工程工具（Computer Aided Software Engineering，CASE）进行数据建模以及业务建模，常用的CASE工具有ERwin、PowerDesigner、Rational Rose以及Visio 等。在系统实施阶段，编程人员经常使用集成开发环境（Integrated Development Environment，简称IDE 工具）进行软件编码、编译、调试等工作，常用的IDE工具有VC++6.0、Visual Studio、Eclipse以及NetBeans 等。在测试阶段，测试人员经常使用测试工具进行单元测试、功能测试以及性能测试，常用测试工具有 Junit 单元测试工具、QuickTest Professional功能测试工具以及LoadRunner 性能测试工具等。
在关系数据库数据建模时，数据库开发人员经常使用ERwin、PowerDesigner、Visio等CASE工具创建E-R图，甚至使用ERwin、PowerDesigner、Visio等工具直接创建数据库（例如MySQL数据库）或者直接生成SQL脚本文件（例如MySQL的SQL脚本文件）。
3．技术
软件开发时使用的技术是一组方法，常用的技术包括：面向对象分析和设计技术、结构化分析和设计技术、软件测试技术和关系数据库设计技术等。其中，关系数据库设计技术决定了关系数据库设计的质量，这也是本章着重讲解的内容。关系数据库设计技术包含E-R图绘制以及关系数据库设计两方面的内容，这两方面的内容稍后进行详细讲解。
注意：数据建模制作E-R图的过程中，本章使用的CASE工具是PowerDesigner。部分读者可能没有使用过PowerDesigner，但笔者认为软件开发（包括数据库开发）是一种高级脑力劳动，工具代替不了软件开发人员以及数据库开发人员的“智慧”及“思想”，掌握这些“智慧”、“思想”对于数据库开发人员至关重要，这也是本书着重阐述的内容。读者在学习本章内容时，可以使用笔、纸或者绘图工具（例如 Word 绘图）设计 E-R 图，掌握本章的知识后，有精力的读者可以学习一下 ERwin、PowerDesigner或者Visio工具的使用。1.2.3 “选课系统”概述
相信大多数读者有过网上选课的经历，熟悉“选课系统”的基本操作流程，多数读者可以称得上是“选课”领域的“专家”，这为设计一个结构良好的“选课系统”数据库奠定了坚实的基础。为了将“选课系统”案例融入到数据库设计以及MySQL的各个知识点，限于篇幅，本书在不影响“选课系统”核心功能的基础上，适当地对该系统进行“定制”、“扩展”以及“瘦身”，“选课系统”的操作流程如图 1-2 所示。“选课系统”操作流程的文字描述如下。本书后续所有章节的内容，全部围绕该“描述”设计，开发“选课系统”的数据库表、索引、视图、函数、存储过程、触发器等数据库对象。▲图1-2 选课系统操作流程
● 游客用户只能浏览已经审核的课程信息，不能享受“选课系统”提供的其他服务。游客用户注册成为学生或者教师，成功登录“选课系统”后，才能享受“选课系统”提供的其他服务。
● 教师成功登录“选课系统”后，可以申报选修课程，要求选修课程面向全校学生。
● 为保证教学质量，每一位教师只能申报一门选修课程。由于很多课程需要在教室内完成教学，因此课程的人数上限受到教室座位数量的限制（共有60座位、150座位和230座位3种教室）。教师申报选修课程时需提供课程的详细信息，其中包括课程名、工号、教师姓名、人数上限、教师联系方式以及课程详细描述等。
● 教师申报课程信息后，经管理员审核通过才能供学生选修。
● 学生成功登录“选课系统”后，才可以进行选课。学生选课时，每位学生可以浏览所有已审核的课程信息，并进行选修。为保证学习效果，限制每位学生最多选修两门课程。学生选课时需提供学号、姓名、班级名、所属院系名和联系方式等信息，由系统自动记录选择课程的时间。
● 选课结束前，学生可以退课、调课。
● 选课结束后，当某一门课程的选修人数少于30人时，为避免教师、教室资源浪费，管理员有权删除该课程信息。某一门的课程信息删除后，选择该课程的学生需要重新选修其他课程。
● 管理员负责审核课程，添加班级信息（且班级名不能重复），以及维护用户信息。
● 教师可以查看本人申报课程的学生信息列表，管理员可以查看所有申报课程的学生信息列表。
说明
本书第 1～9 章介绍“选课系统”数据库开发流程，第 10 章介绍“选课系统”应用程序开发的相关知识，前10章内容循序渐进，章节之间知识衔接非常紧密，并且章节之间尽量避免知识重复和交叉，建议读者按照本书章节的顺序学习前10章的内容。1.2.4 定义问题域
定义问题域是数据库设计过程中重要的活动，它的目标是准确定义要解决的商业问题。使用数据库技术可以解决“选课系统”存在的诸多“商业”问题，其中包括以下内容。
● 如何存储以及维护课程、学生、教师以及班级的详细信息？
● 不同教师申报的课程名能否相同？如果允许课程名相同，如何区分课程？
● 如何控制每位教师只能申报一门选修课程？
● 如何控制每门课程的人数上限在（60、150、230）中取值？
● 如何控制每一门课程的选课学生总人数不超过该课程的人数上限？
● 如何实现学生选课功能、退选功能以及调课功能？
● 如何控制每位学生最多可以选修两门课程，且两门课程不能相同？
● 系统如何自动记录学生的选课时间？
● 如何统计每一门课程还可以供多少学生选修？
● 如何统计人数已经报满的课程？
● 如何统计某一个教师已经申报了哪些课程？
● 如何统计某一个学生已经选修了多少门课程，是哪些课程？
● 如何统计选修人数少于30人的所有课程信息？
● 如何统计选修每一门课程的所有学生信息？
● 课程信息删除后，如何保证选择该课程的学生可以选修其他课程？
● 如何通过搜索关键字检索自己感兴趣的课程信息？
上述所有“商业”问题，都可以通过数据库技术找到答案，并可以在本书找到解决方案。有些“商业”问题可以使用数据库设计知识在本章进行解答；有些“商业”问题需借助具体的数据库管理系统（例如MySQL）的知识，可以在其他章节中找到答案。1.2.5 编码规范
结构化查询语言SQL是本书重点讲解的内容。一方面，数据库开发人员需要使用SQL编写部分业务逻辑代码（如触发器、存储过程、函数、事件等）完成部分业务功能。另一方面，程序开发人员需要在应用程序中构造 SQL 语句，实现应用程序与数据库的交互。为了保证数据库能够在不同的操作系统平台上进行移植，甚至为了保证应用程序能够在不同的数据库管理系统之间进行移植，数据库开发人员以及程序开发人员在书写 SQL 语句时需要遵循一些基本的编程原则，这些原则称为数据库编码规范。下面介绍一些常用的数据库编码规范，这些规范对任何一个追求高质量代码的人来说是必需的。
1．命名规范
良好的命名方式是重要的编程习惯，描述性强的名称让代码更加容易阅读、理解和维护。命名遵循的基本原则是：以标准计算机英文为蓝本，杜绝一切拼音或拼音英文混杂的命名方式，建议使用语义化英语的方式命名。为了保证软件代码具有良好的可读性，一般要求在同一个软件系统中，命名原则必须统一。
常用的命名原则有两种。第一种：第一个单词首字母小写，其余单词首字母大写（驼峰标记法），如studentNo、studentName。第二种：单词所有字母小写，单词间用下划线“_”分隔，如student_no、student_name。本书使用第二种命名规则定义“选课系统”E-R图中的实体名、属性名以及MySQL数据库中的数据库名、表名和字段名等各个数据库对象名称。本书使用的其他数据库命名原则包括：函数名使用“_fun”后缀；存储过程名使用“_proc”后缀；视图名使用“_view”后缀；触发器名使用“_trig”后缀；索引名使用“_index”后缀；外键约束名使用“_fk”后缀等。
说明
在MySQL数据库中，命名时应尽量避免使用关键字，例如table、database、limit等。
2．注释
软件开发是一种高级脑力劳动，精妙算法的背后往往伴随着难以理解的代码。对于不经常维护的代码，时过境迁，开发者本人也会忘记编写的初衷，因此，要为代码添加注释，增强代码的可读性和可维护性。有时添加注释和编写代码一样难，但养成这样的习惯是必要的。请记住：尽最大努力把方便留给别人和将来的自己。
说明
MySQL 代码单行注释以“#”开始，或者以用两个短划线和一个空格（“--”）开始。多行注释以“/*”开始，以“*/”结束。
3．书写规范
每个缩进的单位约定是一个Tab（制表符）。MySQL中begin-end语句块中的第一条语句需要缩进，同一个语句块内的所有语句上下对齐。
4．其他
在MySQL 数据库中，关键字是不区分大小写的，例如SQL语句“delete from course”中的“delete”与“from”为关键字，因此该SQL语句等效于“DELETE FROM course”。为了便于读者阅读，本书将涉及的SQL关键字书写为小写。
但这不意味着表名“course”等效于表名“COURSE”，“course”并不是MySQL的关键字。事实上，如果将MySQL部署在Windows操作系统中，表名以及数据库名是大小写不敏感的（不区分大小写的）；如果将MySQL部署在Linux操作系统中，表名以及数据库名是大小写敏感的（区分大小写的）。考虑到数据库可能在不同操作系统之间进行移植，数据库开发人员应该尽量规范数据库的命名。1.3 E-R图
关系数据库的设计一般要从数据模型 E-R 图（Entity-Relationship Diagram，E-R 图）设计开始。E-R图设计的质量直接决定了表结构设计的质量，而表是数据库中最为重要的数据库对象，可以这样说：E-R 图设计的质量直接决定了关系数据库设计的质量。E-R 图既可以表示现实世界中的事物，又可以表示事物与事物之间的关系，它描述了软件系统的数据存储需求，其中E表示实体，R表示关系，所以E-R图也称为实体-关系图。E-R图由实体、属性和关系3个要素构成。1.3.1 实体和属性
E-R图中的实体用于表示现实世界具有相同属性描述的事物的集合，它不是某一个具体事物，而是某一种类别所有事物的统称。E-R图中的实体通常使用矩形表示，如图1-3所示。数据库开发人员在设计E-R图时，一个E-R图中通常包含多个实体，每个实体由实体名唯一标记。开发数据库时，每个实体对应于数据库中的一张数据库表，每个实体的具体取值对应于数据库表中的一条记录。例如“选课系统”中，“课程”是一个实体，“课程”实体应该对应于“课程”数据库表；“课程名”为数学，“人数上限”为 230 的课程是课程实体的具体取值，对应于“课程”数据库表中的一条记录。▲图1-3 课程实体及属性
E-R图中的属性通常用于表示实体的某种特征，也可以使用属性表示实体间关系的特征（稍后举例）。一个实体通常包含多个属性，每个属性由属性名唯一标记，所有属性画在实体矩形的内部，如图1-3所示。E-R图中实体的属性对应于数据库表的字段。例如“选课系统”中课程实体具有课程名、人数上限等属性，这些属性对应于课程数据库表的课程名字段以及人数上限字段。
注意：在E-R图中，属性是一个不可再分的最小单元，如果属性能够再分，则可以考虑将该属性进行细分，或者可以考虑将该属性“升格”为另一个实体。例如假设（注意这里仅仅是“假设”）学生实体中的联系方式属性可以细分为Email、QQ、固定电话、手机等联系方式，则可以将联系方式属性拆分为Email、QQ、固定电话、手机4个联系方式属性；也可以将联系方式属性“升格”成“联系方式”实体，该实体有Email、QQ、固定电话、手机4个属性。这两种设计方案没有正确、错误之分，只有合适与不合适之分。1.3.2 关系
E-R图中的关系用于表示实体间存在的联系，在E-R图中，实体间的关系通常使用一条线段表示。需要注意的是，E-R图中实体间的关系是双向的，例如，在班级实体与学生实体之间的双向关系中，“一个班级包含若干名学生”描述的是“班级学生”的“单向”关系，“一个学生只能属于一个班级”描述的是“学生班级”的“单向”关系，两个“单向”关系共同构成了班级实体与学生实体之间的双向关系，最终构成了班级实体与学生实体之间的一对多（1：m）关系（稍后介绍）。
理解关系的双向性至关重要，因为设计数据库时，有时“从一个方向记录关系”比“从另一个方向记录关系”容易得多。例如，在班级实体与学生实体之间的关系中，让学生记住所在班级，远比班级“记住”所有学生容易得多。这就好比“让学生记住校长，远比校长记住所有学生容易得多”。
在E-R图中，实体间的关系有3个重要概念：基数、元以及关联。
1．基数
在E-R图中，基数表示一个实体到另一个实体之间关联的数目。基数是针对关系之间的某个方向提出的概念，基数可以是一个取值范围，也可以是某个具体数值。当基数的最小值为1时，表示一种强制关系（mandatory），强制关系对应于本章即将讲到的非空约束（Not NULL Constraint）。例如，选修课程必须由一名教师申报后才存在，言外之意“对于选修课程而言，任课教师必须存在”，如图1-4所示。当基数的最小值为0时，表示一种可选关系（optional），例如，一名教师只能申报一门课程，言外之意，“对教师而言，无需必须申报课程”，如图1-4所示（注意强制关系与可选关系的表示方法不同）。
说明
数据库开发人员为了区分各种关系，也可以为实体间的关系命名，如图1-4所示。▲图1-4 教师实体与课程实体之间的关系
从基数的角度可以将关系分为一对一（1∶1）、一对多（1∶m）、多对多（m∶n）关系。例如，在“选课系统”中，一名教师只能申报一门课程，而一门课程必须由一名教师申报，实体间双向关系的基数都是1，此时教师实体和课程实体之间是一对一关系。一个班级包含若干名学生（基数为m），而一名学生只能属于一个班级（基数为 1），此时班级实体与学生实体之间是一对多（1∶m）关系。一名学生可以选修两门课程（基数为m<=2），一门课程可以被多名学生选修（基数为n<=课程的人数上限），此时学生实体与课程实体之间是多对多（m∶n）关系。
2．元
在E-R图中，元表示关系所关联的实体个数，上面叙述的每个关系都牵涉到两个实体，它们都是二元关系，E-R图中二元关系最为常用。有时实体间可能存在一元关系（也称为回归关系），例如在“婚姻”关系中，人实体与人实体之间存在的“夫妻”关系就是典型的一元关系，表示方法如图1-5所示。实体间的多元关系（例如三元关系）稍后举例。
3．关联
有时关系本身可能存在自身属性，例如“夫妻”关系中存在“登记时间”属性。使用一条线段可以表示人实体与人实体之间存在的“夫妻”关系，却无法表示“夫妻”关系中存在的“登记时间”属性。对于这种关系，不再使用一条线段表示，可以使用关联（association）表示实体间关系的属性，表示方法如图1-6所示（注意可选关系optional在图1-5以及图1-6中表示方法的区别）。[2]
关联（association）也是一种实体间的连接。在Merise模型 方法学理论中，关联经常用于表示两个实体间发生的某种“事件”，这种事件通过实体往往不能明确表达。伴随着事件的发生，通常还会产生“事件”的一些属性（例如事件的状态、事件发生的时间、地点等），此时可以使用关联表示实体间发生的事件。▲图1-5 一元关系▲图1-6 使用关联表示“夫妻”关系中存在的“登记时间”属性
有时实体间可能存在多元关系（例如三元关系）。数据库开发人员经常使用关联表示多元关系。设想如下场景：很多团购网站在网上对房源进行出租。一名顾客可以在多个团购网站上寻找房源，一个网站可以为多名顾客提供房源；一个房源可以在多个团购网站上进行出租，一个网站可以出租多个房源；一名顾客每次可以订购多个房源（订购房源时需提供入住时间和入住天数），一个房源又可以出租给多名顾客。在该场景描述中，团购网站、顾客以及房源3者之间的关系为三元关系，可以使用关联表示这种多元关系，表示方法如图1-7所示。▲图1-7 团购网站、顾客以及房源之间的三元关系表示方法
技巧：如果两个实体间的关系（relationship）存在自身的属性，可以使用关联（association）表示实体间的这种关系（relationship）。如果实体间存在多元关系（例如三元关系），同样可以使用关联表示实体间的多元关系。1.3.3 E-R图的设计原则
数据库开发人员设计的E-R图必须确保能够解决某个“商业领域”的所有“商业问题”，这样才能够保证由E-R图生成的数据库能够解决该商业领域的所有商业问题。数据库开发人员通常采用“一事一地”的原则从系统的功能描述中抽象出E-R图。所谓“一事一地”原则，可以从属性、实体两个方面进行解读。
● 属性应该存在于且只存在于某一个地方（实体或者关联），反映在数据库中，这句话确保了数据库中的某个数据只存储于某个数据库表中（避免同一数据存储于多个数据库表），避免了数据冗余。表1-2所示的学生表出现了大量的数据冗余，而数据冗余是导致插入异常、删除异常、修改复杂等一系列问题的罪魁祸首（稍后介绍）。
● 实体是一个单独的个体，不能存在于另一个实体中成为其属性。反映在数据库中，这句话确保了一个数据库表中不能包含另一个数据库表，即不能出现“表中套表”的现象。表1-2所示的学生表出现了“表中套表”的现象，而“表中套表”的现象通常也会伴随着数据冗余问题的发生。表1-2 存在大量冗余数据的学生表
例如：在“选课系统”的功能描述中曾经提到，学生选课时，需要提供学号、姓名、班级名、所属院系名和联系方式等信息。学号、姓名以及联系方式理应作为学生实体的属性，那么，班级名和院系名是不是也可以作为学生实体的属性呢？事实上，如果将班级名和院系名也作为学生实体的属性，此时学生实体存在（学号、姓名、联系方式、班级名、院系名）5 个属性，学生实体中出现了“表中套表”的现象，反而违背了“一事一地”的原则。原因在于，班级名和院系名联系紧密（班级属于院系，院系通常包含多个班级），应该将“班级名属性”与“院系名属性”“抽取”出来放入“班级”实体中，将一个“大”实体分解成两个“小”实体，然后建立班级实体与学生实体之间的一对多关系，这样就得到了“选课系统”的“部分”E-R图，如图1-8所示。▲图1-8 E-R图中尽量避免“表中套表”的现象
● 同一个实体在同一个E-R图内仅出现一次。例如同一个E-R图内，两个实体间存在多种关系时，为了表示实体间的多种关系，尽量不要让同一个实体出现多次。
以中国移动提供的 10086人工服务为例，移动用户拨打 10086申请客服人员服务；客服人员为手机用户提供服务后，手机用户可以对该客服人员进行评价打分。那么客服人员与手机用户之间就存在“服务-被服务”、“评价-被评价”等多种关系。由于客服人员可以为多个手机用户提供服务，手机用户可以享受多个客服人员提供的服务；手机用户可以为多个客服人员进行评价，客服人员可以接受多个手机用户的评价。因此，客服人员与手机用户之间的关系可以使用图 1-9 所示的E-R图或者图1-10所示的E-R图进行描述。手机用户实体与客服人员实体仅仅在E-R图中出现一次。▲图1-9 E-R图：客服人员与手机用户之间的关系（1）▲图1-10 E-R图：客服人员与手机用户之间的关系（2）
说明
上述两种E-R图都可以描述客服人员与手机用户之间的关系，数据库开发人员可以根据项目的具体要求，选择其中一种进行项目实施。如果每一次的人工服务必须伴随一次评价，那么数据库开发人员可以选择第二个E-R图描述客服人员与手机用户之间的关系；如果每一次的人工服务不一定有评价，那么数据库开发人员可以选择第一个E-R图描述客服人员与手机用户之间的关系。可以看出，E-R图的设计没有正确、错误之分，只有合适与不合适之分，更多时候考验的是数据库开发人员的经验、智慧。
基于“一事一地”的原则，逐句分析“选课系统”的功能描述，可以得到所有的“部分”E-R图，然后将其合并成“选课系统”的E-R图。“选课系统”的E-R图共抽象出4个实体，分别是教师、课程、学生和班级，每个实体包含的属性以及实体间的关系如图1-11所示。
E-R图中的实体名、属性名以及关系名尽量使用语义化的英语。例如学生实体可以命名为student，学号属性可以命名为 student_no，选修关系可以命名为 choose（本书命名方法为所有单词字母小写，单词间用下划线分隔）。语义化英语后的E-R图如图1-12所示。
说明
班级表名classes使用的是语义化英语class的复数形式classes，目的是避免与面向对象编程中的“类”关键字class混淆。类似地，用户表推荐使用语义化英语user的复数形式users，目的是为了避免与数据库管理系统中的user关键字混淆。▲图1-11 “选课系统”的E-R图▲图1-12 语义化英语后的“选课系统”E-R图1.4 关系数据库设计
数据库表是数据库中最为重要的数据库对象，采用“一事一地”的原则绘制出E-R图后，可以采用如下几个步骤由E-R图生成数据库表。（1）为E-R图中的每个实体建立一张表。（2）为每张表定义一个主键（如果需要，可以向表添加一个没有实际意义的字段作为该表的主键）。（3）增加外键表示一对多关系。（4）建立新表表示多对多关系。（5）为字段选择合适的数据类型。（6）定义约束条件（如果需要）。（7）评价关系的质量，并进行必要的改进。
结合“选课系统”的E-R图，下面将详细讨论每个步骤并介绍关系数据库的相关知识。1.4.1 为每个实体建立一张数据库表“选课系统”的E-R图共涉及4个实体，每个实体将对应于数据库中的一张表，实体名对应于表名，属性名对应于字段名。经此步骤，得到“选课系统”的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.2 为每张表定义一个主键
关系数据库中的表是由列和行构成的，和电子表格不同的是，数据库表要求表中的每一行记录都必须是唯一的，即在同一张数据库表中不允许出现完全相同的两条记录。关系数据库中的表必须存在关键字（key），用以唯一标识表中的每行记录，关键字实际上是能够唯一标识表记录的字段或字段组合。例如，在学生 student 表中，由于学号不允许重复且学号不允许取空值（NULL），学号可以作为学生student表的关键字。假设（注意这里仅仅是“假设”）学生student表中还存在身份证号字段，且身份证号不允许取空值（NULL），那么身份证号字段也可以作为学生student表的关键字。
设计数据库时，为每个实体建立一张数据库表后，数据库开发人员最为普遍、最为推荐的做法是：在所有的关键字中选择一个关键字作为该表的主关键字，简称主键（primary key）。数据库表中的主键有以下两个特征。（1）表的主键可以是一个字段，也可以是多个字段的组合（这种情况称为复合主键）。（2）表中主键的值具有唯一性且不能取空值（NULL）。当表中的主键由多个字段构成时，每个字段的值都不能取NULL。例如在电话号码中，区号和地方号码的组合才能标识一个电话号码，如果区号和地方号码共同构成电话号码的主键，那么对于“电话号码”而言，区号和地方号码都不能取NULL。
说明
NULL 表示值不确定或者不存在。例如-∞（负无穷大）是一个不确定的值；除零操作的结果是NULL；一个刚出生孩子的姓名是一个不确定的值（与空格字符“以及零长度的空字符”的意义不同）；学生选课后，只要课程没有考试，该生该门课程的成绩就是NULL（与零的意义不同，与缺考、作弊的意义也不同）。
说明
主键和关键字的不同之处在于，一张表可以有多个关键字，但一张表只能有一个主键，且主键肯定属于关键字。
为表定义主键时，有几个常用的技巧需要读者了解。
技巧1：推荐取值简单的关键字为主键。例如，假设（注意这里仅仅是“假设”）学生student表存在学号以及身份证号两个字段，虽然学号或者身份证号都能够唯一标记一个学生，但数据库开发人员通常会选择学号作为学生表的主键，毕竟学号的取值要比身份证号的取值简单得多。另外，在“选课系统”中，由于班级名的取值不能为NULL，也不允许重复，因此，班级classes表的班级名字段可以作为该表的关键字，但班级名字段不适合作为班级classes表的主键，原因在于，有些班级的班级名（例如“2012级计算机科学与技术1班”）取值较为复杂。读者可以参看“技巧3”为班级classes表添加主键。
技巧 2：在设计数据库表时，复合主键会给表的维护带来不便，因此不建议使用复合主键。对于存在复合主键的数据库表，读者可以参看“技巧3”为该表添加主键。
技巧 3：数据库开发人员如果不能从已有的字段（或者字段组合）中选择一个主键，那么可以向数据库表中添加一个没有实际意义的字段作为该表的主键。例如，在课程course表中，考虑到课程名可能重复，课程course表没有关键字，此时数据库开发人员可以在课程course表中添加一个没有实际意义的字段（例如课程号course_no）作为该表的主键。向表添加一个没有实际意义的字段作为该表的主键，这样做有以下两个优点。
● 可以避免“复合主键”情况的发生，同时可以确保数据库表满足第二范式的要求（范式的概念稍后介绍）。
● 可以避免“意义更改”导致主键数据被“业务逻辑”修改。这里举个反例，假设（注意这里仅仅是“假设”）课程名能够唯一标记课程（即课程名course_name是课程course表的关键字），并将课程名course_name选作课程course表的主键。如果某一门课程的课程名course_name因为某些特殊原因需要更正，那么选修该课程的所有学生选课信息将受到影响。一般而言，主键数据改动的概率很小（但却不可避免），主键数据一旦修改将会导致“牵一发而动全身”，不利于信息的维护。
技巧 4：数据库开发人员如果向数据库表中添加一个没有实际意义的字段作为该表的主键，建议该主键的值由数据库管理系统（例
试读结束[说明：试读内容隐藏了图片]