MySQL入门_MySQL

 

本章主要介绍MySQL关系数据库管理系统（Relational Database Management System，RDBMS）和MySQL所使用的结构化查询语言（Structured Query Language，SQL）。其中，列出了应该掌握的基本术语和概念，描述了示例所要用到的样本数据库sampdb，并且提供了一个用于展示如何使用`MySQL创建数据库并与之进行交互操作的教程。
如果你对数据库系统还不甚了解，或者还不是很肯定自己是否需要学习它，甚至还不肯定是否需要使用它，那么请从本章开始。如果你对MySQL和SQL还一无所知，并且需要一个入门指南，那么也请从本章开始。对MySQL或其他数据库系统已有一定经验的读者，则可以略读一下本章的内容。不过，为了你能对全书所用那个数据库sampdb的目的和内容更熟悉，希望可以阅读一下1.2节。

1.1　MySQL的用途

本节描述的是MySQL数据库系统的用途，具体描述了“MySQL可以干什么”，以及“它如何才能给你提供帮助”。如果你已经明白数据库的用途（也许你正在思考某个问题，到这里来只是想要找出“如何使用MySQL来解决它”的答案），那么可以直接翻阅到1.2节。
数据库系统在本质上是一种高效的管理大量列表信息的办法。这些信息的来源可能多种多样。它可能是研究数据、业务记录、客户需求、体育统计、销售报告、个人信息、人事档案、bug报告或者学生成绩。发挥数据库系统强大作用的时机在于：想要组织和管理的信息非常庞大或复杂，以至于所有记录采用手工处理会变得异常繁重。对于每天处理上百万条事务的大公司来说，数据库是必不可少的。不过，即使只有一个人的小公司，也可能会维护很多的信息，甚至多到需要用一个数据库来管理它。假设有下面这些情况。

你在牙科诊所工作，在那里需要管理好患者的跟踪记录：何人何时到访、做了些什么、下次预约信息、保险信息等。

你收集了多年的研究数据，为了发表而需要对它们进行分析。你要从大量原始数据里提炼出摘要性的信息，并取出选中的观察子集进行详细的统计分析。

你是一名教师，需要跟踪学生的成绩和考勤。每次考试结束，你都需要记录每一个学生的分数。虽然将成绩记录到成绩册上很简单，但之后的成绩分析却很繁琐。你很想避免为了确定分数曲线，而对每次考试的分数进行排序；也真的很不愿意在期末时为了确定最终成绩，而把每个学生的成绩都加起来。统计每个学生的考勤也很无趣。

你在某个组织机构（可能是一个专业团体、一个俱乐部、一个交响乐团或者一个健身俱乐部）担任秘书一职，具体负责维护机构成员名录的工作。你每年都要为所有成员生成一份打印名录，名录是用文字处理软件管理的，每当成员资料有变化时你都得编辑更新。你非常厌倦这种维护名录的方式，因为它限制了你的发挥，主要表现在：很难对名录条目按不同方式进行排序；无法轻松地选到每一条目的指定部分，如列出人名和电话号码；更不能轻松地找出一组的成员，如需要尽快更新成员资格的成员。如果有办法，就可以省却每月通过翻阅这些条目来找出那些需要更新成员资格的成员的工作。你听说过“无纸化办公”，知道它是电子化记录发展的结果，但你还未见过它所带来的任何好处。虽然成员资格记录是电子化的，但具有讽刺意味的是，它们记录的形式除了能将名录打印成纸质的以外，很难用作他途！

“机构秘书”场景。该机构有一些特点：其成员对美国历史很感兴趣（因没有一个更好的名字，所以姑且称其为“美史联盟”）。所有成员都要定期缴纳一定的费用以维持其成员资格。缴纳的费用会用于一些正常开支，如出版通讯——《Chronicles of U.S. Past 》（美国编年史）。该联盟运营着一个小型网站，但还未被充分开发出来，而你很想要扭转这一局面。

“成绩考评”场景。你是一名教师，要负责考评期间的各种考试与测验，记录分数和打分。之后，你要确定出最终成绩，并把它们随同考勤情况一同上交到学校办公室。

你必须决定数据库里的哪些内容是你所想要的——即那些你要实现的目标。

你必须决定需要将哪些内容放入数据库——即你想要跟踪的数据是什么。

你想要以不同格式输出名录，同时按照不同的应用程序来定制信息。有一个目标是每年生成打印好的名录——这是联盟一直以来的一个需求，要继续实施下去。你可能还会想到名录信息的其他用途，例如向出席联盟年会的人员提供一份打印好的最新成员名单。这两个应用程序涉及的信息是不同的。打印名录程序需要用到每个成员条目的所有内容。而年会程序则只需要提取出成员的姓名即可（使用文字处理软件无法轻松完成这项工作）。

你想要在名录里搜索出满足不同条件成员。例如，你想要知道近期有哪些成员需要更新成员资格。你还需要另一个搜索相关的应用程序，用来维护每个成员各自的关键字列表。这些关键字描述了所有成员都特别感兴趣的一些美国历史时期，如南北战争（Civil War）、经济大萧条（Depression）、民权法案（civil right）、托马斯·杰斐逊（Thomas Jefferson）总统的生平事迹。有时，有些成员会要求你为他们提供一份与其志趣相投的其他成员名单，而你也很愿意满足这些需求。

你想要将名录发布到联盟网站上去。这样做能让你和所有成员都受益。如果你能通过某些自动化的过程将名录转换为Web页面，那么名录的在线版本将总能保持最新，而这是纸质形式无法做到的。如果在线名录能支持搜索功能，那么成员们便能轻松地自行查找信息。例如，如果某成员想要知道其他还有谁对“南北战争”感兴趣，那么他便可以自行查找，完全用不着等你帮他搜索，而你也不用抽时间去处理这件事情。

如果数据库中的信息能够以在线名录的形式放到网站上去，那么你也可以让这些信息以其他方式流转。例如，让成员能够在线修改他们自己的资料，并更新到数据库。这样，你就不用自己负责所有的编辑工作，而且这也能让名录里的信息更加准确。

如果你把电子邮件地址也存储到数据库里，那么你就可以利用它们来给成员发送电子邮件，提醒他们及时更新自己的资料。邮件内容可以显示出成员的当前资料，请成员们进行检查，并提示他们如何使用网站提供的功能完成必要的修改。

数据库还可以在很多方面拓展联盟网站的用途，而并不仅限于成员资格列表。联盟通讯《Chronicles of U.S. Past》有一个儿童专栏，其中每一期都会包含一个历史知识测验。最近几期的重点是美国总统的传记。联盟网站上也可以设置一个儿童专区，把那些测验题目放在上面。或许这个专区还可以弄成互动的，比如将做过的测验信息放入数据库，让Web服务器在数据库里查询问题的答案，然后呈现给访客。

是不是想太多了？实现起来工作量会很大吧？

MySQL能实现所有这些目标吗？

最后，还有一个很重要的问题：总计要花费多少钱？毕竟联盟的预算是有限的。

对于每一次测验或考试，你都要记录分数。如果是考试，你还需要对分数进行排序，以便查看它们并确定每一个字母成绩（包括A、B、C、D和F）所代表的界线。

在期末，你需要把计算出每一个学生的总分数，对这些总分数进行排序，并以此为基础确定出成绩。总分数可能需要加权计算，因为你可能需要让考试比测验的权重更大。

在期末，你还要向学校办公室提供学生的考勤情况。

数据库（DataBase，即RDBMS里的DB）是一个用来存储信息的仓库，它的结构简单、规则。

数据库里的数据集都被组织成表（table）。

每个表由多个行（row）和列（column）组成。

表中的每一行称为一条记录（record）。

记录可以包含多项信息；表里的每一列对应于其中的一项。

管理系统（Management System，即RDBMS里的MS）是一个软件，我们可以通过它来插入（insert）、检索（retrieve）、修改（modify）或删除（delete）记录。

关系（Relational，即RDBMS里的R）一词表示这是一种特殊的DBMS，其长处在于通过查找两个表里的共同元素，将分别存放于两个表里的信息联系（即匹配）起来。RDBMS的强大之处在于：它能方便地将这些表里的数据提取出来，并把相关表里的信息结合起来生成答案，回答那些只靠单个表无法回答的问题。（事实上，“关系”的正式定义与我在本书中用它的方式有所不同。为此，我先向那些纯粹主义者道歉。不过，我的定义更有助于表达出RDBMS的用途。）

服务器强制执行并发控制，可以防止两个用户同时修改同一条记录。所有客户端请求都要经过服务器，因此服务器会负责安排处理它们的先后顺序。即使出现多个客户端同时访问同一个表的情况，它们也不用先找到对方进行协商。它们只需把自己的请求发往服务器，然后由服务器来决定执行这些请求的顺序。

不是只有在数据库所在的那台机器上才能登录。MySQL工作在网络环境里，因此你可以在任意地方运行MySQL客户端程序，它都能够通过网络连接到服务器。距离不是问题！你可以在世界的任何地方访问服务器。例如，你的服务器位于澳大利亚，那么就算你带着笔记本电脑旅行到了冰岛，你也仍然可以访问自己的数据库。这是否意味着任何人都能够通过互联网看到你的数据呢？答案是“不能”。MySQL有一套灵活的安全机制，你可以设定只有得到授权的人才能访问。此外，你还可以进一步限制这些人的操作。例如，财务部的Sally应该有查看和更新（修改）记录的权限；而服务台的Phil却只应该有查看记录的权限。总之，你可以把这种访问权限控制细化到每一个人。如果你只想运行一个自属的系统，那么你完全可以把访问权限设置为只允许服务器上的客户端程序进行连接。

了解MySQL所能理解的SQL基础知识。（MySQL与其他RDBMS所使用的SQL有所不同，因此你最好也能快速浏览一下本节的内容，从而确认一下MySQL的SQL实现与你熟悉的版本是否存在差异。）

了解MySQL自带的标准客户端程序是如何与MySQL服务器进行通信的。前一节讲过，MySQL采用的是“客户端/服务器”体系结构。其中，服务器运行在数据库所在的机器上；而客户端则是通过网络连接到服务器。本教程主要依赖于客户端程序mysql，它首先读取你输入的SQL查询语句，把它们发送到服务器执行，然后把执行结果显示在你面前。客户端mysql可以在MySQL所支持的所有平台上运行，并且提供了与服务器进行交互的最直接的方式。不过根据需要，有些示例会使用mysqlimport或者mysqlshow来代替。

必须安装MySQL软件。

要有一个能够连接数据库服务器的MySQL账号。

要有一个用来操作的数据库。

-h host_name（另一种形式是：--host=host_name）

-u user_name（另一种形式是：--user=user_name）

-p（另一种形式是：--password）

president表。其中包含美国历任总统的描述性记录。我们需要用它来实现联盟网站上的在线小测验（对联盟通讯儿童专栏里出现的小测验进行交互式模拟）。

member表。用于保存联盟每位成员的最新个人资料。我们可以用它来创建成员名录的印刷版本和在线版本，用它来向到期成员自动发送提醒通知，还可以用它做很多其他事情。

姓名。在表里，表示姓名的方式有好几种，如使用单列包含整个名字，或用不同的列分别表示姓（last name）和名（first name）。使用单列来表示当然更简单一些，但这种做法不够灵活，存在一些限制。

出生地（城市和州）。与姓名的情况类似，它既可以用一列来表示，也可以用多列表示。采用单列来表示的做法显得更简单些；但与姓名的情况一样，使用多列能实现某些更复杂的操作。例如，若把州名与市名分开表示，那么像“找出出生在某个州的总统共有多少位”这种类似的操作便能轻易地实现。我们将使用两个单独的列来分别存放州名与市名。

出生日期和逝世日期。这里唯一需要特殊处理的事情是：因为有些总统依然健在，所以我们不能要求必须填上逝世日期。特殊值NULL的意思即表示“无值”，因此我们可以在逝世日期列里用它来表示该位总统“依然健在”。

姓名。我们将沿用与president表相同的3列表示法：姓、名和姓名后缀。

ID编号。这是一个唯一值，为每个首次加入的成员分配一个。联盟此前从未对成员编过号，但现在需要所有的记录都更系统化，因此这里需要这个值。（希望你能不断发现MySQL的好处，并找到更多将编号应用于联盟记录的方式。当想要将member表里的行，与你所创建的成员相关的其他表建立关联时，使用编号则会比使用姓名更容易实现。）

有效期。所有成员必须定期更新其成员资格，以避免过期。对于某些应用程序，可能还需要把最近一次资格更新后的起始日期存储起来，但“美史联盟”不需要这样做。成员资格的有效期是一个变值（通常有1年、2年、3年或者5年之分），而最近一次的资格更新日期也并不能说明该成员下一次的资格更新日期一定是在什么时候。因此，我们需要把成员资格的截止日期存储起来。此外，联盟还提供了终身成员资格。虽然我们可以用一个很遥远的日期来表示这种情况，但使用NULL会更合适，因为“无值”在逻辑上正好对应于“永不失效”。

电子邮件地址。公开电子邮件地址可以使兴趣相投的成员交流起来更方便。作为联盟的秘书，在有了这些地址之后，就可以用电子邮件来向成员发送成员资格更新通知，而不用邮寄信件了。与到邮局寄信相比，这种做法既方便又省钱。你还可以利用电子邮件把每位成员的个人最新资料发送给他们，让他们在必要时更新信息。

通信地址。当与那些没有电子邮件（或者长期没有回复你邮件）的成员进行联系时，你会需要这条信息。我们将使用多个列来分别存储街道地址、城市名、州名和邮政编码。

电话号码。与地址列相似，主要用于联系成员。

特殊兴趣关键字。联盟的每位成员对美国历史肯定都很感兴趣，但他们的兴趣却可能集中于某些特定的历史时期。此列便是用于记录这些兴趣。每位成员都可以利用这些信息来寻找与自己兴趣相投的其他成员。（严格来讲，建立一个独立的表可能会更好些，表中的每一行都由一个关键字和相关成员的ID组成。这点有些复杂，我们暂不作处理。）

INT。它表示该列用于存放整数（无小数部分的数值）。

UNSIGNED。它表示该值不能为负数。

NOT NULL。它表示该列必须要填值，这可以防止创建的成员没有ID号。

AUTO_INCREMENT。它是MySQL的一个特殊属性，表示该列存放的是序号。AUTO_INCREMENT的工作原理为：当往member表里添加新记录时，如果没有为member_id列提供值，那么MySQL将自动生成下一个编号，并将它赋给该列。如果你显式地将NULL赋给该列，结果也是一样的。AUTO_INCREMENT的这种特性使得为每一位成员分配一个唯一的ID变得很简单，因为MySQL会替我们生成这些值。

你需要把考试成绩记录在表格的每一个格里，这些格都要按学生姓名和考试日期进行排列（姓名由上往下排列，考试日期则由左往右排列）。这正是我刚才讲过的两组信息当中的一组，而它正好对应于score表里的内容。

你要如何才能知道各日期所对应的考试类型呢？你或许会这样做：在日期上方写一个T或Q。于是，你又在表格的顶部把考试日期与考试类型关联起来了。这正是我刚才讲过的两组信息当中的第二组，而它也正好对应于grade_``event表里的内容。

成绩册由行和列构成，电子表格也是如此。这使得它们在概念和外观上都很相似。

电子表格程序能够执行计算，因此可以使用计算字段来统计每个学生的分数。将测验分数和考试分数按不同权重来统计可能会有点棘手，但相信你能办到。

考试成绩与考试事件和学生都有关联：只有当与考试成绩相关联的学生ID和考试事件ID分别在student``表和grade_event表里存在时，才允许考试成绩进入score表。

类似地，考勤记录与学生有关联：只有与考勤相关联的学生ID在student表里存在时，才允许考勤情况进入absence表。

我们已将这两列的组合设置成了一个PRIMARY KEY。这样可以确保我们不会重复记录某位学生在某次考试或测验的分数。请注意，只有event_id和student_id的组合才具有唯一性。在score表里，这两个ID值自身都不具备唯一性。对于每一个event_id值（每位学生有一个）都会有多个分数行与之对应；对于每一个student_id值（每次考试或测验有一个）也会有多行记录相对应。

每一个ID列都需要用FOREIGN KEY子句来定义约束条件。此子句的REFERENCES部分表明这个ID列是与哪个表的哪一列相对应。event_id列的约束条件为：该列里的每一个值都必须与grade_event表里的某个event_id值相匹配。类似地，score表里的每一个student_id值都必须与student表里的某个student_id值相匹配。

score表依赖于grade_event表和student表，因此在创建score表之前必须先创建其依赖的表。类似地，adsence表依赖于student表，因此在创建adsence表之前，student表必须已存在。

在删除表时，必须把上面的顺序颠倒过来。如果不先删除score表，就无法删除grade_event表；如果不先删除score表和absence表，也无法删除student表。

按日期排序（我们已经操作过多次）。

搜索特定日期或日期范围。

从日期值里提取各组成部分，如年、月或日。

计算两个日期之间的时间差。

通过将一个日期加上或减去一个时间间隔，计算出另一个日期。

不用事先知道被统计列里有些什么值。

只需一个查询语句。

因为只用一个查询便能获得所有的结果，所以可以对输出进行排序。

FROM子句指定了多个表名，因为需要从多个表里检索信息：

ON子句指定了表grade_``event和score的连接条件，即这两个表的event_id值必须相互匹配：

FROM子句现在包含了student表，因为这条查询语句除了要用到grade_``event表和score表以外，还需要用到它。

在前一个查询里，student_id列不会产生二义性，因此在引用它时，既可以不限定表名（student_id），也可以限定表名（score.student_id）。但在这个查询里，因为score表和student表都有student_id列，所以肯定会出现二义性。于是，为了避免产生二义性，必须将它们分别限定为score.student_id和student.student_id。

ON子句里多了一个查询条件，用于指定score表里的行与student表里的行必须基于学生ID匹配在一起：

这个查询会显示出学生的姓名，而不显示学生的ID。（如果想要两者都显示，只需要在输出列的列表里加上student.student_id即可。）

它需要引用同一个表中的两个实例，因此我们必须为它创建两个别名（p1和p2），并用它们来将表中的同名列区别开来。由于列已有别名，所以在为表指定别名时， AS关键字就是可选项了。

每位总统的记录都与其本身相匹配，但这并不是我们想要的输出结果。在确保参与比较的总统名字都不相同的情况下， WHERE子句便能防止出现“行与其本身相匹配”的情况。

把连接参数存储在一个选项文件里。

利用shell的历史命令功能输入重复命令。

利用shell别名或脚本定义一个mysql命令行快捷方式。

利用mysql的输入行编辑功能。

利用复制和粘贴。

利用批处理运行mysql程序。