MySQL数据库应用实战教程

任务1需求分析和数据结构设计任务1需求分析和数据结构设计2.1.1需求分析2.1.2数据结构设计2.1.3命名规范任务2理解MySQL的数据类型任务3创建数据库和数据表任务4操纵数据和查询数据任务5理解主键和外键2.1.1需求分析需求分析项目开发的第一步是对项目进行分析，看看这个项目有哪些需求，开发一个项目的最终目标就是满足这些需求开发的过程就是根据需求，设计规范的数据结构，并加以实施，以满足这些需求写一篇作文之前要审题，做一道数学题之前也要审题同样，在开发一个项目之前也要审题，这个审题的过程就是需求分析。项目概述名称：联系人项目数据库名称：contact需求概述：管理个人用的联系人信息，要求使用方便、容易查找、不易出错收集到的数据：见下表2.1.2数据结构设计方案一：简单但有缺陷的设计缺陷一：联系人信息中姓名是重复的，如果为一个已有的联系人增加一个联系方式时，是否可以避免重复输入姓名？缺陷二：联系人类型有更多的重复，并且联系人类型一般不会超过10种（如同学、同事和亲属等），是否可以把联系人类型固定下来，输入时只需要选择即可，这样还可以避免出现含义相同的类型（如亲属和亲戚是同义词）。2.1.2数据结构设计（续）方案二：复杂但完美的设计设计原则：消除重复数据解决办法：拆分表步骤一(a)：将电话数据拆分出来，消除了姓名的重复，但联系人类型还有重复人员表：保存姓名和联系人类型电话表：保存联系方式和说明每张表都有一个主键（类似于序号）2.1.2数据结构设计（续）步骤一(b)：再将联系人类型数据拆分出来，消除联系人类型的重复人员表：新的人员表，保存姓名类型表：保存联系人类型这时再也没有重复数据，拆分完毕2.1.2数据结构设计（续）步骤一的结果拆分成为3张表拆分后，不再出现重复的数据。例如“姓名”列和“联系人类型”列都不再有重复的数据，在输入数据时，不需要重复输入。拆分后，每张表都是独立的，具有独立的含义，简化了数据结构。例如联系人类型、联系人姓名和联系方式三者都具有独立的含义。2.1.2数据结构设计（续）通过拆分表，解决了前面提到的两个缺陷，但又出现了新问题：将联系人信息拆分为3张表（电话表、人员表和类型表）之后，如何将它们联系起来成为一个整体？解决的办法是建立它们之间的联系（Relationship）。建立联系时要考虑以下两个问题哪张表和哪张表有联系？联系的类型是什么？有联系的两张表如何关联起来？这是步骤二要解决的问题第1个问题：联系以及联系的类型现在一共有3张表：电话表、人员表和类型表。3张表的两两组合共有3种，下面分析每个组合的联系，这3种组合如下。类型表和人员表：有主从联系，人员是从属于类型的。类型表是主表，人员表是从表。一个人只属于一种联系人类型，一种联系人类型可以有多个人。在关系数据库理论中，这种联系称为一对多的联系，“一”的一方是类型表，是主表（也称为父表），“多”的一方是人员表，是从表（也称为子表）。人员表和电话表：有主从联系，电话是从属于人员的。人员表是主表，电话表是从表。一个人有多种联系方式，一种联系方式只属于一个人，也是一对多的联系，“一”的一方是人员表，是主表（父表），“多”的一方是电话表，是从表（子表）。类型表和电话表：没有直接的联系，两者需要通过人员表间接关联起来。第2个问题：表与表之间的关联先以人员表和类型表为例来讨论，讲解如何建立它们之间的多对一联系。对于人员表中的每个人，都应该有一个联系人类型，原来是用文字表示的，现在可以用类型表中的主键来表示，那么人员表（从表）就需要增加一列（或代替原来的“联系人类型”列），这个列的值是类型表中的主键的值。经过修改，人员表（见表2.5）和类型表（见表2.6）成为表2.7和表2.8所示的两张表。在关系数据库理论中，这个新增的列称为外键。外键的作用是建立表与表之间的联系，将从表与主表关联起来，从表的外键参照（也称为引用）主表的主键。外键与主键这里讲了一个重要的概念，在课本第31页第一次讲解外键参照另一张表的主键的值就是说它的值必须是另一张表的主键值中的一个就像每个儿子都有父亲一样参照reference也翻译为引用项目一讲了一个最重要的概念，在课本的第19页第一次讲解主键不能有重复的值，也不能为空就是说它必须有一个唯一的值，用来标识这一行例如数据库中，每一个人的信息可以用身份证号来唯一标识主键外键理解主键与外键张三的外键1引用类型表的主键1，因此他的类型是“常用联系人”李四的外键2引用类型表的主键2，因此他的类型是“朋友”王五的外键3引用类型表的主键3，因此他的类型是“同事”赵六的外键3引用类型表的主键3，因此他的类型也是“同事”2.1.2数据结构设计（续）按照同样的道理，可以建立电话表与人员表的联系电话表人员表2.1.2数据结构设计（续）这是步骤二的结果建立了联系的3张表与原始数据比较一下电话表人员表类型表2.1.2数据结构设计（续）根据前面的分析结果，可以得到下述数据结构设计的成果2.1.3命名规范本书的命名规范不同的公司对命名规范的要求会有所不同，应该严格按照规范进行命名任务2理解MySQL的数据类型任务1需求分析和数据结构设计任务2理解MySQL的数据类型2.2.1整型2.2.2浮点型和精确浮点型2.2.3日期和时间类型2.2.4字符串类型任务3创建数据库和数据表任务4操纵数据和查询数据任务5理解主键和外键2.2.1整型微整型tinyint、短整型smallint、中整型mediumint、整型int、和大整型bigint常用的是微整型tinyint和整型int两种微整型tinyint：通常用于保存范围很小的数值，取值范围是-128~127。整型int：取值范围大约是-20亿~20亿。如果要精确地保存全世界的人口数，则要用大整型bigint。定义整型时可以同时指定显示时的宽度，例如int(11)和tinyint(4)，指定显示宽度并不影响内部的存储格式。如果使用的是MySQL8.0，则不允许指定显示时的宽度（会引起出错）2.2.2浮点型和精确浮点型浮点型：有两种，可能有误差，不建议使用Float：单精度Double：双精度精确浮点型：建议使用Decimal：它有一个别名Numeric，两者同义如果使用的是MySQL8.0，float和double不允许指定显示时的宽度2.2.3日期和时间类型根据不同的需求，可以在5种与日期时间有关的类型中选择Datetime和Timestamp都含有日期和时间信息，两者的区别如下Datetime：不含时区信息，可表示的年份跨度大Timestamp：包含时区信息，可表示的年份跨度小一些2.2.4字符串类型通常只使用varchar(n)一种，只有对于定长的字符串，才会用char(n)不要使用各种text类型指定列类型长度时汉字和英文字母同等对待，例如varchar(10)的列可以保存10个字母或10个汉字但是字母（如'abc'）和汉字（'三个字'）占用的字节数是不同的，求长度时返回的长度值也不同MySQL没有字符类型，字符类型相当于char(1)任务3创建数据库和数据表任务1需求分析和数据结构设计任务2理解MySQL的数据类型任务3创建数据库和数据表2.3.1理解MySQL字符集2.3.2【实训2–1】创建数据库和数据表任务4操纵数据和查询数据任务5理解主键和外键2.3.1理解MySQL字符集字符集（CharacterSet，简写为Charset）字符编码中的所有字符，指定字符编码的同时也就指定了字符集。在中文里，常用的字符编码是utf8，这几乎是唯一应该使用的字符集。在MySQL8中，还细分为utf8mb3和utf8mb4，这时建议使用utf8mb4。如果字符集设置错误，将可能出现中文乱码的现象。校对（Collation）字符的排序规则例如英文的排序有两种规则区分大小写的排序不区分大小写的排序。中文乱码的原因当输入输出的字符集与数据库的字符集不同时，就会出现中文乱码数据库通常设置为utf8，当输入输出不是utf8时，就会出现乱码例如Windows操作系统的字符集是GBK，这时就会出现乱码解决办法是设置MySQL客户端，让客户端的连接对字符集进行转换例子见教材第13页“图1.21设置连接参数（自动选择字符集）”在MySQL客户端上可以用下述命令来解决中文乱码数据库输入（键盘）输出（显示器）连接setnames"GBK";2.3.2【实训2–1】创建数据库和数据表观看教材第37页的微课然后根据Jitor校验器的要求，在图形界面上一步一步完成下述任务1、创建数据库数据库名contact，注意选择正确的字符集2、创建数据表3张表：类型类、人员表、和电话表，注意主键和外键3、建立表与表之间的联系类型类和人员表：人员表的外键id_contact_type参照类型表的主键id_contact_type人员表和电话表：电话表的外键id_contact参照人员表的主键id_contact微课：2-1创建数据库和数据表【实训2-1】任务4操纵数据和查询数据任务1需求分析和数据结构设计任务2理解MySQL的数据类型任务3创建数据库和数据表任务4操纵数据和查询数据2.4.1【实训2–2】数据操纵2.4.2输入相关的常见问题2.4.3【实训2–3】数据查询任务5理解主键和外键2.4.1【实训2–2】数据操纵观看教材第41页的微课然后根据Jitor校验器的要求，在dbForge上输入下述联系人信息数据是分别输入到3张表中的，完成后应该如第42页图2.13、

2.14、2.15所示微课：2-2添加数据【实训2-2】2.4.2输入相关的常见问题只读模式和编辑模式在dbForge界面上，需要切换为编辑模式，才能输入或修改数据，见下图2.4.2输入相关的常见问题（续）空和空串（或数字0）空：没有数据空串：有数据，值为长度为0的字符串数字0：有数据，值为0改为空的办法从下拉框中选择null使用快捷键Ctrl+02.4.2输入相关的常见问题（续）修改主键值记住永远不要试图修改主键值教材上的例子只是证明修改主键值是非常麻烦和危险的操作修改主键值需要经过3个状态其中的中间状态是非常危险的2.4.3【实训2–3】数据查询观看教材第44页的微课然后根据Jitor校验器的要求，在dbForge上查询联系人信息微课：2-3查询数据【实训2-3】数据查询语句图2.24是自动生成的数据查询语句，是通过实训中的操作生成的将在“4.4.1【实训4-8】内连接查询”（第100-101页）详细讲解这里只需简单了解一下任务5理解主键和外键任务1需求分析和数据结构设计任务2理解MySQL的数据类型任务3创建数据库和数据表任务4操纵数据和查询数据任务5理解主键和外键2.5.1数据操纵与主键2.5.2数据操纵与外键2.5.3主键和外键的比较2.5.1数据操纵与主键每一张表都应该有一个主键，通常主键的数据类型是整型，自动增量主键的作用唯一标识一行作为一个可以被外键有效引用的标识增删改时插入时：主键的值是自动生成的更新时：不允许修改主键值删除时：如果主键值被其他表的外键参照，这时不允许删除微课：2-4主键和外键2.5.2数据操纵与外键外键参照主表的主键，外键的值只能取主表中主键的值之一外键的为空性不允许为空时：插入或更新数据时，不允许为空，例如学生必须有班级允许为空时：插入或更新数据时，可以为空，以后需要时再补充输入增删改时插入时：外键的值只能取主表中主键的值之一更新时：外键的值只能取主表中主键的值之一删除时：无需考虑2.5.3主键和外键的比较项目2认识数据库——联系人数据库任务1需求分析和数据结构设计

设计联系人数据库，重点是拆分表，消除了重复的数据

在有关联的表之间建立外键参照主键联系，将表关联起来任务2理解MySQL的数据类型

常用的有varchar(n)、int、tinyint、decimal(m,p)、date、datetime任务3创建数据库和数据表

采用utf8字符集，注意中文乱码问题

创建数据库，创建表，建立表与表之间的联系任务4操纵数据和查询数据

向多张表插入、更新数据

从多张表查询得到数据任务5理解主键和外键主键的值不允许修改

外键只能取主表中主键的值之一看思维导图，总结项目22.3目录操作命令（续）2.3.2pwd句法：pwd功能：打印当前工作目录的全路径名（绝对路径）。如果没有显示目录则属于符号链接。[bamboo@redhatbamboo]$pwd/home/bamboo2.3.3cd句法：cd[-PL][dir]功能：改变当前目录为dir。若dir缺省则改变当前目录为$HOME变量的值，其中$HOME变量存放着当前用户的主目录，即该用户刚登录后所在的目录。若dir为’..’，则当前目录改为当前目录的父目录。若dir以字符’/’开头，则dir指出的是绝对路径；否则dir指出的是相对路径，目录应改为从当前目录开始的dir目录。-P 使用物理目录结构，而不使用紧跟着的符号链接-L 强制使用后面的符号链接下一页2.3目录操作命令（续）2.3.4mkdir句法：mkdir[OPTION]DIRECTORY...功能：创建一个或多个目录。其中子目录名为最多255个任意字符（除了字符’/’）的字符串。-m,--mode=MODE设置许可模式(形式如chmod，而不是umask中的rwxrwxrwx)-p,--parents：noerrorifexisting,makeparentdirectoriesasneeded-v,--verbose：为每个创建的目录显示一条消息2.3.5rmdir句法：rmdir[OPTION]...DIRECTORY...功能：删除一个或多个空的目录。任何一个DIRECTORY参数必须是一个空目录，否则命令会指出这不是一个空目录。DIRECTORY参数支持通配符。OPTION参数：--ignore-fail-on-non-empty：如果一个DIRECTORY参数不是一个空目录，命令就忽略它，继续删除下一个DIRECTORY而不报错。-p或--parents：删除DIRECTORY之后，试图删除DIRECTORY的每一个分量。比如：`rmdir-pa/b/c'就相当于`rmdira/b/ca/ba'。返回本章首页2.4文件操作命令2.4.1ls(略)2.4.2cp句法：cp[OPTION]...SOURCEDESTcp[OPTION]...SOURCE...DIRECTORY功能：复制一个或多个文件或目录。若命令的最后一个参数名是一个已存在的目录，cp把每个SOURCE文件复制到DIRECTORY目录下(保持原来的文件名不变)。否则，如果在命令中只给出两个文件，则将第一个文件复制成第二个文件。如果命令的最后一个参数名不是一个目录，而且非选项参数不止两个，那么命令执行出错。该命令默认时不复制目录，复制目录要用-R或—recursive选项。2.4.3rm句法：rm[OPTION]...[FILE]...功能：删除命令中所列出的每个文件。默认时，该命令不删除目录。其中FILE是要删除的文件OPTION参数：-d或--directory用`unlink'而不是`rmdir'来删除在FILE参数中指定的目录,在unlink一个目录前并不要求它是空的。但这要求你必须有适当的权限，而且你的操作系统支持对目录`unlink'因为unlink一个目录会导致这个目录下的所有文件变为不被参照（或连接）的文件,所以在unlink你的目录后用`fsck'命令去检查一下你的文件系统是明智的。-f或--force：忽略不存在的文件而给用户任何提示。这个选项忽略已经设置的`--interactive'(`-i')选项。-i或--interactive：删除每个文件时提示用户是否要删除。如果用户的回答是以`y'或`Y'开头的字符串,那么这个文件就删除，否则不删除。这个选项忽略已经设置的`--force'(`-f')选项。-r或-R或--recursive：以递归方式删除目录中内容。-v或--verbose：在删除每个文件前打印文件名下一页2.4文件操作命令（续）2.4.4mv句法：mv[OPTION]...SOURCEDESTmv[OPTION]...SOURCE...DIRECTORY功能：移动或更名文件或目录。若命令的最后一个参数名是一个已存在的目录，则将在SOURCE中指定的文件移动到该目录下；否则，mv命令将SOURCE文件改名为DEST2.4.5cat句法：cat[OPTION][FILE]...功能：在屏幕上显示文件的内容。

下一页2.4文件操作命令（续）2.4.6chmod句法：chmod[OPTION]...{MODE|--reference=FILE}FILE...功能：改变文件（包括目录文件）的访问许可，但符号链接文件除外。因为符号链接文件本身的访问许可从不使用。但如果你在命令中出现了一个符号链接文件，那么改变的是该链接文件所指向的文件的访问许可。而另一方面，在以递归方式遍历目录时，chmod命令将忽略符号链接。MODE：指定新的访问许可。完整的MODE可以用4个八进制数或4组3位二进制数来表示。set-UID置位表示有设置进程userID的许可，在一个set-UID置位的可执行文件运行的时候，该进程的有效userID就是该文件的所有者；set-GID置位表示有设置进程groupID的许可，在一个set-GID置位的可执行文件运行的时候，该进程的有效groupID就是该文件所属的组；sticky-bit又称保持文本模式，它要求操作系统在内存中保留可执行程序的镜像，即使在程序退出后。这样能降低大型程序的启动时间。r为可读，w为可写，x为可执行。MODE的表示可以用4个八进制数（完整的许可）或3个八进制数（省略了第一个八进制数）来表示。通常绝大多数情况下用3个八进制数就够了。另外，还可以用访问字符串来表示访问许可模式。它的模式为：用户的类型:+/-：许可的种类[,用户的类型:+/-：许可的种类...]其中用户的类型有:u(文件主)、g（同组成员）、o（其他用户）、a（所有用户）；'+'表示增加相应的许可，而'-'表示增加相应的许可；许可类型有：r（读）、w（写）、x（执行）、s（set-UID或set-GID）、t（sticky-bit）比如：a+x,u+w表示对所有用户增加执行许可，对文件主增加写许可。OPTION参数：-v,--verbose：对每个文件的任何处理（动作或不动作）都给出描述--reference=FILE：将参照文件FILE的许可模式置为这里的MODE-R,--recursive：以递归方式来改变目录和目录下文件的许可模式。

___set-UID|___set-GID||___sticky-bit||||||usergroupother|||__|____|____|__|||||||||XXXXXXXXXXXX|||||||||rwxrwxrwxX=0或1，为1表示相应的位置位下一页2.4文件操作命令（续）2.4.7chown句法：chown[OPTION]...{NEW-OWNER|--reference=FILE}FILE...功能：直接将文件（包括目录文件）的用户以及/或者组的所有权改为NEW-OWNER；或者改为--reference=FILE所指的参照文件FILE的所有权。其中NEW-OWNER的模式为：[OWNER][[:][GROUP]]OWNER指定了文件的用户所有者，GROUP指定了文件的组所有者。这个模式的具体组合有一下几种形式：OWNER只改变文件的文件主为OWNER，而不改变文件的组所有者。OWNER:GROUP

改变文件的文件主为OWNER，文件组为GROUP。OWNER:

改变文件的文件主为OWNER，文件组为OWNER登录时所在的组。GROUP:

只改变文件的组所有者。这种情况就相当于命令chgrp。OPTION参数：--dereference：不对符号链接文件本身进行操作，而是对它们所指向的文件进行操作-h,--no-dereference：与-dereference选项正好相反。这也是该命令的缺省设置。--reference=FILE：将FILE的user和group设置为指定文件的NEW-OWNER。-R,--recursive：以递归方式来改变目录和目录下文件的所有权。下一页2.4文件操作命令（续）2.4.9ln句法：ln[OPTION]...TARGET[LINKNAME]ln[OPTION]...TARGET...DIRECTORY功能：在文件间建立链接。缺省情况下建立硬链接，如果有-s选项，则建立符号链接（或称“软链接”）。硬链接是已存在文件的别名，一旦链接生成，链接和原文件是无法区别的。但硬链接有一些局限性：硬链接不能和一个目录链接，也不能和其他文件系统的文件进行链接。而符号链接（或软链接）是一种特殊的文件类型，链接文件实际上指向了另一个文件。当大多数的操作