- MySQL数据库基础与实践
- 夏辉 白萍 李晋 屈巍编著
- 13261字
- 2021-03-25 22:28:30
第2章 MySQL数据库概述
本章介绍MySQL相关的基础知识，主要包括MySQL的体系结构、安装与配置过程，以及MySQL数据库和数据表的基本操作。
2.1 认识MySQL数据库
MySQL是一种关系型数据库管理系统，关系型数据库将数据保存在不同的表中，而不是将所有数据存放在一个大仓库内，这样就提高了速度和灵活性。
2.1.1 MySQL简介
MySQL是一个开源的关系型数据库管理系统，由瑞典MySQL AB公司开发。MySQL在2008年1月被Sun公司收购，而2009年4月，Sun公司又被Oracle公司收购。由于MySQL具有体积小、运行速度快、成本低，尤其是开放源码这一特点，许多中小型网站为了降低网站总体成本而选择MySQL作为网站数据库。MySQL被设计为单进程多线程的架构，这一特性决定了MySQL属于轻量级的数据库。
MySQL具有以下系统特性。
● MySQL 可以在各种常见的操作系统中运行，包括UNIX、Linux、FreeBSD、Windows、Mac OS及OS/2等，可以方便地将数据库从一个操作系统转移到另一个操作系统。
● 为C、C++、Java、Perl、PHP、Python和Ruby等多种编程语言提供了API。
● 支持多线程，可充分利用CPU资源。
● 优化的SQL查询算法，能有效地提高查询速度。
● 支持多种存储引擎，提供了事务性和非事务性存储引擎。
● 提供用于管理、检查和优化数据库操作的管理工具。
● MySQL的开源性表明任何人都可以根据自身需要使用和修改软件，以满足自身的需求。
2.1.2 MySQL体系结构
MySQL的组成部分包括连接池组件、管理服务和工具组件、SQL接口组件、查询分析器组件、优化器组件、缓存组件、插件式存储引擎，以及物理文件。MySQL体系结构图如图2-1所示。
各部分说明如下。
1）Connectors：指的是不同语言中与SQL的交互。
2）Management Services ＆ Utilities：系统管理和控制工具。
图2-1 MySQL体系结构
3）Connection Pool：连接池，管理缓冲用户连接、线程处理等需要缓存的需求。
4）SQL Interface：SQL接口，接受用户的SQL命令，并且返回用户需要查询的结果，比如，SELECT语句就是调用SQLInterface。
5）Parser：解析器，SQL命令传递到解析器时会被解析器验证和解析。
6）Optimizer：查询优化器，SQL语句在查询之前会使用查询优化器对查询进行优化，使用的是“选取-投影-联接”策略进行查询。
7）Caches ＆ Buffers：查询缓存，如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。
8）Pluggable Storage Engines：插件式存储引擎，存储引擎是MySQL中与文件打交道的子系统。MySQL的存储引擎是插件式的，它根据文件访问层的一个抽象接口来定制一种文件访问机制。
2.2 MySQL 数据库的安装和配置
本节将介绍MySQL数据库的安装和配置过程。
2.2.1 MySQL的安装和配置服务
本书以Windows操作系统为开发平台，采用的是MySQL 5.0.22版本，读者可根据自己系统的要求下载相应的版本，官方网址为http：//www.mysql.com/。下载后的文件名称为MySql5.0_Setup.exe，双击该文件即可开始安装。具体安装步骤如下。
1）双击MySql5.0_Setup.exe文件，在弹出的安装向导对话框中，单击Next按钮，如图2-2所示。
2）进入选择安装类型的对话框，如图2-3所示。安装类型分为Typical（典型安装）、Complete（完全安装）及Custom（自定义安装）。默认为Typical，即只安装常用的MySQL组件并且不能修改安装目录，默认将MySQL安装在C：\Program Files\MySQL\ 5.0\目录中。
图2-2 安装向导对话框
图2-3 选择安装类型对话框
选择Custom单选按钮，单击Next按钮，进入自定义安装对话框，如图2-4所示。在自定义安装对话框中选择合适的安装组件，单击Change按钮，将安装路径更改为D：\MySQL Server5.0\，单击Next按钮。
图2-4 自定义安装对话框
3）进入准备安装程序对话框，如图2-5所示，单击Install按钮，开始安装。
图2-5 准备安装程序对话框
4）安装过程中会出现如图2-6所示的注册账号对话框，询问是否需要注册一个MySQL.com的账号，或是使用已有的账号登录MySQL.com，一般不需要，选择Skip Sign-Up单选按钮，单击Next按钮即可。
图2-6 注册账号对话框
5）安装完成后，出现如图2-7所示的安装完成对话框，选择“Configure th eMySQL Servernow”复选框，配置MySQL服务器，单击Finish按钮。
6）进入MySQL服务器实例配置向导对话框，如图2-8所示。通过MySQL服务器实例配置向导，MySQL安装程序可以自动创建my.ini配置文件，并通过图形化的方式将常用的配置信息写入my.ini配置文件中。
图2-7 安装完成对话框
图2-8 MySQL服务器实例配置向导对话框
7）单击Next按钮，进入选择配置方案对话框，如图2-9所示。MySQL提供了两种配置方案：Detailed Configuration（详细配置）和Standard Configuration（标准配置）。
8）选择Detailed Configuration单选按钮，单击Next按钮，进入选择服务器类型对话框，如图2-10所示。选择不同的服务器类型，将直接影响服务器的内存、硬盘及CPU等资源的使用情况。MySQL提供了3种服务器类型。
图2-9 配置方案对话框
图2-10 选择服务器类型对话框
● Developer Machine：作为开发服务器，适用于数据库开发阶段，MySQL服务器运行期间占用较少的内存资源。
● Server Machine：作为普通的服务器，MySQL服务器运行期间占用中等的内存资源。
●Dedicated MySQL Server Machine：作为专门的数据库服务器，MySQL服务器运行期间占用尽可能多的内存资源。
9）选择Developer Machine单选按钮，单击Next按钮，进入选择数据库使用情况对话框，如图2-11所示。通过数据库使用情况对话框，可以选择在创建MySQL表时所使用的存储引擎。
●Multifunctional Database（多功能数据库）：选择该单选按钮，则同时使用InnoDB和MyISAM存储引擎，并在两个引擎之间平均分配资源。建议经常使用两个存储引擎的用户选择该单选按钮。
● Transactional Database Only（只是事务处理数据库）：选择该单选按钮，将同时使用InnoDB和MyISAM储存引擎，但是将大多数服务器资源指派给InnoDB存储引擎。建议主要使用InnoDB只偶尔使用MyISAM的用户选择该单选按钮。
●Non-Transactional Database Only（只是非事务处理数据库）：选择该单选按钮，将完全禁用InnoDB存储引擎，将所有服务器资源指派给MyISAM存储引擎。建议不使用InnoDB的用户选择该单选按钮。
10）选择Multifunctional Database单选按钮，单击Next按钮，进入InnoDB表空间配置对话框，选择MySQL数据库的数据文件存放的位置，默认存放在MySQL的安装路径下。若要更改保存目录，单击
按钮，即可选择相应的目录，如图2-12所示。
图2-11 选择数据库使用情况对话框
图2-12 InnoDB表空间配置对话框
11）单击Next按钮，进入设置并发连接数对话框，限制与MySQL服务器之间的并发连接数量可以防止服务器资源被耗尽，如图2-13所示。
图2-13 设置并发连接数对话框
● Decision Support（DSS）/OLAP（决策支持）：最大连接数设置为100，平均并发连接数为20。
● Online Transaction Processing（OLTP）（联机事务处理）：最大连接数设置为500。
● Manual Setting（手动设置）：可以手动设置服务器并发连接的最大数目。
12）选择Decision Support（DSS）/OLAP单选按钮，单击Next按钮，进入网络配置对话框，如图2-14所示。默认选择Enable TCP/IP Networking复选框，表示可以通过TCP/IP协议远程连接MySQL服务器。Port Number的默认值为3306，表示MySQL服务器运行过程中占用3306端口。
图2-14 网络配置对话框
Enable Strict Mode（启用标准模式）：将MySQL设置为严格的SQL模式，这样可以尽量保证MySQL语法符合标准SQL语法，推荐选择该复选框。
13）单击Next按钮，进入设置默认字符集对话框，如图2-15所示。选择Best Support For Multilingualism单选按钮，表示设置UTF8字符集为MySQL默认的字符集，UTF8字符集支持所有国家的语言。
图2-15 设置默认字符集对话框
14）单击Next按钮，进入Windows选项设置对话框，如图2-16所示。
图2-16 Windows选项设置对话框
Install As Windows Service：该选项用于设置将MySQL服务器安装成一个系统服务。Service Name用于设置MySQL服务的名称，默认为MySQL。若要设置系统启动后自动启动MySQL服务，则选择Launch the My SQL Server automatically复选框。
Include Bin Directory in Windows PATH：用于将MySQL的bin目录添加到Windows操作系统的PATH环境变量中，这样就可以在CMD命令提示符窗口中直接打开MySQL客户机，而不需要切换到bin目录。
15）选中所有复选框后，单击Next按钮。进入安全设置对话框，如图2-17所示。如果要设置MySQL的超级用户root的密码（默认密码为空），则选择Modify Security Settings复选框，在New root password和Confirm两个文本框中输入重设的密码。如果允许远程机器使用root账号连接到当前MySQL服务器上，则选择Enable root access from remote machines复选框。若要创建一个匿名用户账户，则选择Create An Anonymous Account复选框，表示可以使用任何账号名连接MySQL服务器（默认密码为空），并且该匿名账户几乎拥有与超级管理员root相同的权限。创建匿名账户会降低服务器的安全，并造成登录和许可困难，因此不建议选择。
图2-17 安全设置对话框
16）单击Next按钮，进入准备执行配置对话框，如图2-18所示。单击Execute按钮，开始执行配置过程。配置完成后，单击Finish按钮，如图2-19所示。
图2-18 准备执行配置对话框
图2-19 配置完成对话框
2.2.2 启动和停止服务
在Windows操作系统中配置MySQL服务时，如果将MySQL服务注册为Windows操作系统的一个系统服务，则可以通过以下几种方法来启动和停止MySQL服务。
方法一：在桌面上右击“计算机”图标，在弹出的快捷菜单中选择“管理”命令。打开“计算机管理”窗口，展开左侧列表中的“服务和应用程序”选项，然后选择“服务”选项。在右侧的“服务”视图中找到MySQL服务，单击相应功能即可实现MySQL服务的启动、暂停、停止及重启动。
方法二：选择“开始”|“运行”命令，在弹出的“运行”对话框中输入services.msc命令，单击“确定”按钮，即可打开“服务”窗口，如图2-20所示。找到MySQL服务，即可完成相应的功能。
图2-20 服务窗口
方法三：选择“开始”|“运行”，在弹出的“运行”对话框中输入cmd命令，单击“确定”按钮，进入DOS命令窗口。在命令提示符后输入netstartmysql命令或netstopmysql命令，按【Enter】键，即可实现MySQL服务的启动与停止，如图2-21所示。
图2-21 启动与停止服务
2.2.3 MySQL加入环境变量
在Windows操作系统中配置MySQL服务时，若没有选择Include Bin Directory in Windows PATH复选框，也就是没有将MySQL服务的bin目录添加到环境变量PATH中。在这种情况下，选择“开始”|“运行”命令，在弹出的“运行”对话框中输入cmd命令，单击“确定”按钮，进入DOS命令窗口。在命令提示符后输入mysql-uroot-p命令，按下【Enter】键，系统将会输出“‘mysql’不是内部或外部命令，也不是可运行的程序或批处理文件”类似的出错信息，如图2-22所示，表明系统找不到相应的MySQL程序。
图2-22 出错信息
此时，可以通过设置环境变量PATH来解决这个问题。具体步骤如下。
1）右击“计算机”图标，在弹出的快捷菜单中选择“属性”命令。打开“系统”窗口，选择“高级系统设置”选择，在弹出的“系统属性”对话框中选择“高级”选项卡，然后单击“环境变量”按钮，将弹出“环境变量”对话框。
2）在系统变量列表中查看是否存在PATH变量（注意：不区分大小写）。如果不存在，则新建系统变量PATH；若存在，则选中该变量，单击“编辑”按钮，弹出“编辑系统变量”对话框。在“变量值”文本框中原有变量值的后面增加MySQL安装目录下bin目录的路径，本书中对应的路径是“D：\MySQL Server 5.0\bin；”，如图2-23所示。单击“确定”按钮，完成系统变量PATH的编辑。
图2-23 编辑PATH环境变量
此时重新打开一个新的DOS命令窗口，在命令提示符后输入mysql-uroot-p命令，按【Enter】键，如果系统运行结果如图2-24所示，则表明环境变量PATH配置成功。
图2-24 配置正确后的信息
2.2.4 连接MySQL服务器
MySQL服务启动以后，如果要访问MySQL服务器上的数据，必须先连接MySQL服务器。连接MySQL服务器的命令格式如下。mysql-h服务器主机名-u用户名p
各选项的含义如下。
● -h：指定所要连接的MySQL服务器主机，可以是IP地址，也可以是服务器域名。如果MySQL服务器与执行MySQL命令的机器是同一台主机，主机名可以使用localhost或使用IP地址127.0.0.0，也可以省略此选项。
● -u：指定连接MySQL服务器使用的用户名，如root为管理员用户，具有所有权限。
● -p：指定连接MySQL服务器使用的密码，在该参数后直接按【Enter】键，然后以密文的形式输入密码。
假设连接MySQL服务器的用户名和密码分别是root和123456，如果本机既是客户机又是服务器，则可以使用以下命令来连接MySQL服务器。
mysql-urootp
输入完上述命令之后，按【Enter】键，会显示要求输入密码的提示“Enter password：”，输入123456后按【Enter】键。如果成功连接MySQL服务器，则会显示出欢迎信息及mysql＞提示符，等待用户输入MySQL命令或者SQL语句。
注意：MySQL命令或者SQL语句使用“；”或者“\g”作为结束符号。
在连接到MySQL服务器后，可以随时输入quit、exit或\q命令来终止会话。连接及断开MySQL服务器的命令效果如图2-25所示。
图2-25 连接及断开MySQL服务器
另外一种进入MySQL控制台的方法为：单击“开始”按钮，选择MySQL|MySQL Server 5.0|MySQL Command Line Client命令，会出现控制台窗口，直接输入密码并按【Enter】键，即可连接MySQL服务器。
2.2.5 MySQL可视化操作工具
在管理MySQL数据库的可视化操作工具中，比较常用的有MySQL-Front、MySQL Workbench及Navicat for MySQL等。本节将介绍Navicat for MySQL的使用方法。
NavicatforMySQL的安装过程比较简单，下载相应版本直接进行安装即可，这里使用的是11.0版本。安装成功后，直接双击navicat.exe文件，进入操作界面。单击工具栏上的Connection按钮，将会打开设置连接属性的对话框，定义一个连接名称并输入正确的连接信息，如图2-26所示，单击Test Connection按钮，测试连接是否成功，然后单击OK按钮。
连接成功后，在左侧的导航窗口中会看到所连接服务器上所有的MySQL数据库，在本例中连接的本机服务器，其中灰色的图标表示没有打开数据库，绿色的图标就是已经被打开的数据库。
下面以创建一个学生管理系统的数据库为例进行介绍，创建数据库及表的过程如下。
1.创建数据库
右击MySQL连接，在弹出的快捷菜单中选择New Database命令，在打开的创建数据库对话框中输入数据库名称school，设置字符集为UTF8，如图2-27所示，然后单击OK按钮，完成数据库school的创建。
图2-26 连接属性对话框
图2-27 创建数据库对话框
2.创建数据表
双击school数据库图标或者右击school图标，在弹出的快捷菜单中选择Open Database命令，都可以打开数据库，即设置school数据库为当前数据库。下面要创建一个students表，右击Tables图标，在弹出的快捷菜单中选择New Table命令，在右侧窗口中设计表的结构，如图2-28所示。然后单击工具栏上的Save按钮，将表名保存为students。
图2-28 创建数据表结构
在左侧导航窗口中，双击students数据表图标或者右击students图标，在弹出的快捷菜单中选择Open Table命令，都可以打开数据表，然后向表中添加记录。由于ID字段设置了自动增量属性，ID字段的值可由系统自动填充，用户无须填写。添加完记录的students表如图2-29所示。
图2-29 students表记录
若要以SQL语句的形式对数据库及表进行操作，可单击工具栏上的Query按钮，然后再单击New Query按钮，即可在Query Editor选项卡下中输入SQL语句，如输入select*from students，然后单击工具栏上的Run按钮，即可执行SQL语句，结果如图2-30所示。
图2-30 查询表记录
2.3 MySQL数据库的基本操作
连接到MySQL服务器后，就可以使用数据定义语言（DDL）来定义和管理数据库对象，包括数据库、表、索引及视图等。本章以一个简单的网上书店的数据库管理为例，介绍数据库和表的创建，以及对数据库和表的各种操作。
2.3.1 创建数据库
在创建一个新的数据库之前，要先连接到MySQL服务器，然后执行CREATE DATABASE语句，并指定新的数据库名称。例如，创建网上书店的数据库bookstore，可在控制台中输入以下语句。
create database bookstore；
如果数据库创建成功，将会出现“Query OK，1 row a ffected”的提示信息。
注意：新数据库名不能和已有数据库重名。
数据库bookstore创建成功后，MySQL服务器会在其数据目录下创建一个目录，其名与数据库名相同，这个新目录被称为数据库目录。在本例中对应的数据库目录为D：\MySQL Server 5.0\data\bookstore\，在bookstore目录下还会创建一个名为db.opt的文件。
用记事本打开db.opt文件后，内容如下。
default-character-set=utf8
default-collation=utf8_general_ci
db.opt文件的主要功能是记录当前数据库的字符集和排序规则等信息。当在这个数据库中创建表时，如果表的定义里没有指定其默认的字符集和排序规则，那么数据库的默认设置就会成为该表的默认设置。若想修改某个数据库的字符集，可直接编辑该数据库对应的opt文件即可，也可以使用MySQL命令来设置数据库的字符集，命令如下。
alter database bookstore character set utf8；
该命令的修改结果将保存到opt文件中。
在my.ini配置文件中的[mysqld]选项组里，参数datadir设置了MySQL数据库文件存放的路径。在控制台中输入以下命令，可以查看参数datadir的值。
show variables like′datadir′；
2.3.2 查看数据库
使用以下MySQL命令可查看当前MySQL服务器上的数据库列表，命令执行结果如图2-31所示。
show databases；
在图2-31所示的数据库列表中，information_schema和mysql为系统数据库，test为测试数据库，bookstore为刚刚创建的数据库。information_schema是信息数据库，其中保存着MySQL服务器所维护的所有其他数据库的信息。mysql数据库存储了MySQL的账户信息及MySQL账户的访问权限，进而实现MySQL的账户的身份认证和权限验证，确保数据安全。test数据库是安装时创建的一个测试数据库，是一个空数据库，没有任何表，可以删除。
图2-31 查看数据库
2.3.3 显示数据库
数据库创建好之后，可以使用以下MySQL命令来查看数据库的相关信息，如默认字符集等。
show create database bookstore；命令执行结果如图2-32所示。
图2-32 显示数据库
2.3.4 选择当前数据库
在进行数据库操作之前，必须指定操作的是哪个数据库，即需要指定哪一个数据库为当前数据库。在使用CREATE DATABASE命令创建新的数据库后，新数据库并不会自动地成为当前数据库。使用SQL语句USE即可指定当前数据库，如要选择bookstore为当前数据库，可使用以下命令。
use bookstore；
如果选择数据库成功，将会出现Database changed的提示信息，以后在控制台中输入的MySQL命令及SQL语句都将默认操作bookstore数据库中的数据库对象。
2.3.5 删除数据库
如果要删除一个指定的数据库，如bookstore数据库，可在控制台中使用下面的SQL语句。
drop database bookstore；
如果数据库删除成功，将会出现“QueryOK，0rows affected”的提示信息。
注意：不要随意使用DROP DATABASE语句。这个操作将会删除指定数据库中的所有内容，包括该数据库中的表、视图和存储过程等各种信息，并且这是一个不可恢复的操作。
2.4 MySQL数据库表结构的操作
数据库中典型的数据库对象包括表、视图、索引、存储过程、函数和触发器等。其中，表是数据库中最重要的数据库对象，它的主要功能是存放数据库中的各种数据。本节将介绍如何创建、查看、修改和删除MySQL数据库表。
2.4.1 创建数据表
创建好数据库以后，就可以创建其所包含的数据表。在关系数据库中，数据是以二维表的形式存放的，每一行代表一条记录，每一列代表一个字段。创建数据表主要是定义数据表的结构，包括数据表的名称、字段名、字段类型、属性、约束及索引等。
使用CREATE TABLE语句创建表的基本语法格式如下。
CREATE TABLE[IF NOT EXISTS]表名
（字段名1数据类型[约束]]
[，字段名2数据类型[约束]]
…
[，字段名n数据类型[约束]]
[其他约束条件]
）[其他选项]；
其中，[]中为可选的内容，一个表可以由一个或多个字段组成，在字段名后面要定义该字段的数据类型。使用AUTO_INCREMENT、NOTNULL、DEFAULT、PRIMARYKEY、UNIQUE和FOREIGN KEY等属性来对字段进行约束限制。其他选项包括设置存储引擎、字符集等。设置存储引擎可以使用“ENGINE=存储引擎”语句来定义，存储引擎的名称不区分大小写，如果没有设置ENGINE选项，那么服务器将使用默认的存储引擎（InnoDB）来创建表。
例如，在bookstore数据库中创建一个名为users的用户表，代码如下。
use bookstore
create table users
（
uid int not null primary key auto_increment，
name varchar（20）not null unique，
pwd varchar（20）not null，
sex char（2）
）engine=InnoDB；
其中，use语句表示选择bookstore数据库为当前数据库。primary key属性定义uid字段为主键；auto_increment属性定义uid字段的第一行记录值为1，以后每一行的uid字段值在此基础上依次递增，增量为1；unique属性定义name字段的值不允许重复；notnull表示该字段的值不允许为空。
注意：新数据表名不能和已有数据表重名。
如果数据表创建成功，控制台中将会出现“Query OK，0 rows affected”的提示信息，并且在数据库目录bookstore中会自动创建一个表结构定义文件users.frm。提示：如果一条语句太长，可以根据需要，将这条语句分成多行进行输入，这时的提示符由mysql＞变成了−＞，mysql等待继续输入语句内容，直到遇到分号，mysql认为语句到此结束。
如果只想在一个表不存在时才创建新表，则应该在表名前面加上IF NOT EXISTS子句。这时系统不会检查已有表的结构是否与打算新创建的表的结构是否一致，系统只是查看表名是否存在，并且仅在表名不存在的情况下才创建新表。
可以使用SHOW TABLES命令来查看当前数据库中可用的表。
show tables；
下面介绍几种定义字段时常用的关键字。
1.AUTO_INCREMENT
该属性用于设置整数类型字段的自动增量属性，当数值类型的字段设置为自动增量时，每增加一条新记录，该字段的值就会自动加1，而且此字段的值不允许重复。AUTO_INCREMENT字段必须被索引，而且必须为NOT NULL。每个表最多只能有一个字段具有AUTO_INCREMENT属性。
2.DEFAULT
DEFAULT属性是指表中添加新行时给表中某一字段指定的默认值。使用DEFAULT定义，一是可以避免NOT NULL值的数据错误；二是可以加快用户的输入速度。
例如，设置users表中sex字段的默认值为F，在创建users表时可使用以下语句。
sex enum（′F′，′M′）default′F′
3.NOTNULL
指定NOT NULL属性的字段，不能有NULL值。当添加或修改数据时，设置了NOT NULL属性的字段值不允许为空，必须存在具体的值。
4.UNSIGNED
表示该值不能为负数。
5.PRIMARYKEY
表中唯一标识每一行的字段（可以是多个字段）可以定义为表的主键（PRIMARY KEY）。定义PRIMARY KEY时，该字段的空值属性必须定义为NOTNULL。一个表中只能有一个PRIMARY KEY。
有两种创建主键的方法（同样适用于UNIQUE），下面两条CREATETABLE语句是等价的。
create table test（
uid int not null primary key
）；
create table test（
uid int not null，
primary key（uid）
）；
6.UNIQUE
UNIQUE唯一索引通过在列中不输入重复值来保证一个字段或多个字段的数据完整性。与PRIMARY KEY不同的是，每个表可以创建多个UNIQUE列。
7.FOREIGNKEY
FOREIGNKEY定义了表之间的关系，主要用来维护两个表之间的数据一致性关系，是关系数据库中增强表与表之间参照完整性的主要机制。定义FOREIGN KEY约束时，要求在主键表中定义了PRIMARY KEY约束或UNIQUE约束。只有InnoDB存储引擎提供外键支持机制。
在子表里定义外键的语法格式如下。
[CONSTRAINT 约束名]FOREIGNKEY（字段名）
REFERENCES 父表名（字段名）
[ONDELETE 级联选项][ON UPDATE 级联选项]
例如，为订单表orders中的uid列创建外键约束，主键表（父表）为用户表users，创建订单表的代码如下。
create table orders（
oid int not null primary key，
uid int not null，
status int not null，
totalprice float not null，
constraint fk_orders_users foreign key（uid）references users（uid）
on delete cascade
on update cascade
）engine=InnoDB；
其中，ON DELETE CASCADE子句表示当从users表中删除某条记录时，MySQL应该自动从orders表中删除与uid值相匹配的行。ON UPDATE CASCADE子句表示如果更改了users表中某条记录的uid值，那么MySQL将自动把orders表中所有匹配到的uid值也更改为这个新值。
2.4.2 查看数据库表结构
可以使用DESCRIBE语句来查看数据表结构，代码如下。
describe users；
在控制台中输入上述语句后的执行结果如图2-33所示。
图2-33 查看数据表结构
另外，使用SHOW命令也能得到同样的结果。
show columns from users；
2.4.3 修改表结构
在实际应用中，当发现某个表的结构不满足要求时，可以使用ALTER TABLE语句来修改表的结构，包括修改表的名称、添加新的字段、删除原有的字段、修改字段类型、索引及约束，还可以修改存储引擎及字符集等。
修改表的语法格式如下。
ALTER TABLE表名ACTION[，ACTION]…；
其中，每个动作（ACTION）是指对表所做的修改，MySQL支持一条ALTERTABLE语句带多个动作，用逗号分隔。
下面将介绍几种常用的方式。
1.修改字段
（1）添加新字段
向表里添加新字段可以通过在ACTION语句中使用ADD关键字实现，语法格式如下。
ALTER TABLE表名ADD新字段名数据类型[约束条件][FIRST|AFTER字段名]；
向表中添加新字段时通常需要指定新字段在表中的位置，如果没有指定FIRST或AFTER关键字，则在表的末尾添加新字段，否则在指定位置添加新字段。
例如，为用户表users添加一个address字段，数据类型为varchar（50），非空约束，可以使用下面的SQL语句。
alter table users add address varchar（50）not null；
若要在users表中的sex字段后增加一个phone字段，数据类型为varchar（20），非空约束，则对应的SQL语句如下。
alter table users add phone varchar（20）not null after sex；
添加字段后的users表的结构如图2-34所示。
图2-34 添加字段后的users表的结构
（2）修改字段
如果只需要修改字段的数据类型，则使用CHANGE或MODIFY子句都可以，其语法格式如下。
ALTER TABLE表名CHANGE原字段名新字段名数据类型；
ALTER TABLE表名MODIFY字段名数据类型；
例如，要修改users表中的phone字段，将数据类型由varchar（20）改为int，并设置默认值为0，下面两条语句是等效的。
alter table users change phone phone int unsigned default 0；
alter table users modify phone int unsigned default 0；
如果需要修改字段的字段名（及数据类型），这时就只能使用CHANGE子句了。
例如，将users表中的phone字段修改为telephone字段，且数据类型修改为varchar（20），则可以使用下面的SQL语句。
alter table users change phone telephone varchar（20）；
（3）删除字段
删除表字段的语法格式如下。
ALTER TABLE表名DROP字段名；
例如，将users表的address字段删除，则可以使用以下SQL语句。
alter table users drop address；
2.修改约束条件
（1）添加约束条件
向表的某个字段添加约束条件的语法格式如下。
ALTER TABLE表名ADD CONSTRAINT约束名约束类型（字段名）；
例如，向用户表users的telephone字段添加唯一性约束，且约束名为phone_unique，可以使用下面的SQL语句。
alter table users add constraint phone_unique unique（telephone）；
添加约束条件后的users表的结构如图2-35所示。
图2-35 添加约束后的users表的结构
如果要向订单表orders的uid字段添加外键约束，且约束名为fk_orders_users，可以使用下面的SQL语句。
alter table users add constraint fk_orders_users foreign key（uid）references users（uid）；
注意：向表中添加约束条件时，要保证表中已有记录满足新的约束条件，否则会出现类似Duplicate
entry***forkey***的错误信息。
（2）删除约束条件
若要删除表的主键约束，其语法格式如下。
ALTER TABLE表名DROP PRIMARY KEY；
例如，要删除订单表orders的主键约束，可以使用以下代码。
alter table orders drop primary key；
若要删除表的外键约束，其语法格式如下。
ALTER TABLE表名DROP FOREIGN KEY外键约束名；
例如，要删除订单表orders的外键约束，可以使用以下代码。
alter table order sdrop foreign key fk_orders_users；
若要删除字段的唯一性约束，则只需删除该字段的唯一性索引即可，其语法格式如下。
ALTER TABLE表名DROP INDEX唯一索引名；
例如，要删除用户表users的telephone字段的唯一性索引，可以使用以下代码。
alter table users drop index phone_unique；
3.修改表的其他选项
修改表的其他选项，常用的操作如修改存储引擎、修改默认字符集等，其语法格式如下。
ALTER TABLE表名ENGINE=新的存储引擎类型；
ALTER TABLE表名DEFAULTCHARSET=新的字符集；
例如，将users表的存储引擎修改为MyISAM，默认字符集设置为utf8，可以使用以下代码。
alter table users engine=MyISAM；
alter table users default charset=utf8；
4.修改表名
修改表名的语法格式如下。
ALTER TABLE原表名RENAME TO新表名；
还可以使用RENAME TABLE语句，其语法格式如下。
RENAME TABLE原表名TO新表名；
例如，将users表的表名修改为tbl_users，可以使用以下代码。
alter table users rename to tbl_users；
或
rename table users to tbl_users；
2.4.4 删除数据库表
要删除数据库表，可以使用drop table语句实现，其语法格式如下。
DROP TABLE[IFEXISTS]表名1[，表名2，…]；
例如，删除users表可使用以下语句。
drop table users；
在默认情况下，当试图删除一个不存在的表时，系统将会报错。如试图删除订单表
orders（此时数据库中并不存在此表），执行下面的语句。
drop table orders；
在控制台中将会出现Unknown table"orders"的报错信息。若不想让系统报错，可在语句中加上if exists子句，执行下面的语句。
drop table if exists orders；
此时，控制台中将会出现“Query OK，0 rows affected，1 warning”的提示信息，表示当表不存在时，只是生成一条警告信息，可以使用SHOW WARNINGS来查看相关的警告信息。
2.5 MySQL存储引擎
MySQL数据库中典型的数据库对象包括表、视图、索引、存储过程、函数和触发器等，表是其中最为重要的数据库对象。使用SQL语句“create table表名”即可创建一个数据库表，在创建数据库表之前，必须首先明确该表的存储引擎。
存储引擎表示如何存储数据、如何为存储的数据建立索引，以及如何更新和查询数据。在关系数据库中，数据以表的形式存储，所以存储引擎也可以称为表类型。
MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术各自使用不同的存储机制、索引技巧和锁定水平，并且最终提供广泛的、不同的功能和能力。通过选择不同的技术，数据库开发人员可以获得额外的速度或者功能，从而改善所设计应用的整体功能。
例如，如果在研究大量的临时数据时，开发人员需要使用内存MySQL存储引擎。内存存储引擎能够在内存中存储所有的表格数据。
MySQL默认配置了许多不同的存储引擎，可以预先设置或者在MySQL服务器中启用。开发人员可以根据需要选择适用于服务器、数据库和表格的存储引擎，以便在选择如何存储信息、如何检索这些信息，以及需要数据结合什么性能和功能时为设计提供最大的灵活性。
与其他数据库管理系统不同，MySQL提供了插件式（pluggable）的存储引擎，存储引擎是基于表的。同一个数据库，不同的表，存储引擎可以不同；同一个数据库表在不同的场合可以应用于不同的存储引擎。
在Oracle和SQ LServer等数据库中只有一种存储引擎，所有的数据存储管理机制都一样，但是MySQL数据库提供了多种存储引擎，使用MySQL命令“show engines；”即可查看MySQL服务实例支持的存储引擎。每一种存储引擎都有各自的特点，对于不同业务类型的表，为了提升性能，数据库开发人员应该选用更合适的存储引擎。MySQL常用的存储引擎有InnoDB存储引擎和MyISAM存储引擎两种。
2.5.1 InnoDB存储引擎
与其他存储引擎相比，InnoDB存储引擎是事务（Transaction）安全的，并且支持外键。如果某张表主要提供联机事务处理（OLTP）支持，需要执行大量的增、删、改操作（即insert、delete、update语句），出于事务安全方面考虑，InnoDB存储引擎是较好的选择。对于支持事务的InnoDB表，影响速度的主要原因是打开了自动提交（autocommit）选项，或者程序没有显示调用“begin trasaction；”（开始事务）和“commit；”（提交事务），导致每条insert、delete或者update语句都自动开始事务和提交事务，严重影响了更新语句（insert、delete、update语句）的执行效率。让多条更新语句形成一个事务，可以大大提高更新操作的性能。从MySQL 5.6版本开始，InnoDB存储引擎的表已经支持全文索引，这大幅提升了InnoDB存储引擎的检索能力。由于选课系统的数据库表经常需要执行更新操作，因此需要将这些表设置为InnoDB存储引擎。
对于InnoDB存储引擎的数据库表而言，存在表空间的概念，InnoDB表空间分为共享表空间与独享表空间。
1.共享表空间
MySQL服务实例承载的所有数据库的所有InnoDB表的数据信息、索引信息、各种元数据信息，以及事务的回滚（UNDO）信息，全部存放在共享表空间文件中。默认情况下，该文件位于数据库的根目录下，文件名为ibdatal，且文件的初始大小为10MB。可以使用MySQL命令show variables like"innodb_data_file_path"查看该文件的属性，包括文件名、文件初始大小和自动增长等属性信息。
2.独享表空间
如果将全局系统变量innodb_file_per_table的值设为ON（innodb_file_per_table的默认值为OFF），则之后再创建InnoDB存储引擎的新表时，这些表的数据信息和索引信息将保存到独享表空间文件中。
2.5.2 MyISAM存储引擎
MyISAM存储引擎是基于传统的ISAM（Indexed Sequential Access Method，有索引的顺序访问方法）类型的，它是存储记录和文件的标注方法。与其他存储引擎相比，MyISAM具有检查和修复表格的大多数工具。MyISAM表格可以被压缩，且支持全文搜索。但它们不是事务安全的，且不支持外键。如果事物回滚将造成不完全回滚，不具有原子性。当执行大量的查询操作时，MyISAM是比较好的选择。
2.5.3 存储引擎的选择
选择存储引擎时，应根据应用特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行结合。
不需要事务支持、并发相对较低、数据修改相对较少、以读为主，以及数据一致性要求不高的场合，适合选用MyISAM存储引擎。
需要事务支持、行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成，数据更新较为频繁的场合，适合选用InnoDB存储引擎。
在采用InnoDB存储引擎时需要注意：主键应尽量小，避免给Secondary index带来过大的空间负担，避免全表扫描，因为使用表锁可能缓存所有的索引和数据，提供响应速度，在大批量小插入时合理设置innodb_flush_log_at_trx_commit参数值，尽量自己控制事务而不使用autocommit自动提交，不用过度追求安全性，避免主键更新，因为这会带来大量数据移动。
2.6 案例：网上书店系统
网上书店系统是一种具有交互功能的商业信息系统，它可以在网络上建立一个虚拟的网上书店，使购书过程变得轻松、便捷、方便。本节将继续介绍网上书店系统数据库的设计和实现过程。
本系统定义的数据库中主要包含以下几张表：用户表Users、图书类别表BookType、图书信息表BookInfo、订单信息表Orders和订单详情表OrderDetails。下面分别介绍这些表的结构。
1.用户表Users
Users表的结构如表2-1所示。
表2-1 Users表结构
参考代码如下。
2.图书类别表BookType
BookType表的结构如表2-2所示。
表2-2 BookType表结构
参考代码如下。
3.图书信息表BookInfo
BookInfo表的结构如表2-3所示。
表2-3 BookInfo表结构
参考代码如下。
4.订单信息表Orders
Orders表的结构如表2-4所示。
表2-4 Orders表结构
O_Status（订单状态）可以分为3个阶段：0表示图书还没有发送，1表示图书已发送但客户还没有收到，2表示图书已经交到客户手中，表示完成这份订单。
参考代码如下。
5.订单详情表OrderDetails
OrderDetails表的结构如表2-5所示。
表2-5 OrderDetails表结构
参考代码如下。
本章总结
本章首先简单地介绍了MySQL数据库及MySQL的体系结构，然后介绍了如何安装和配置MySQL服务。重点介绍了如何使用MySQL命令及SQL语句对MySQL数据库及数据表进行操作。最后，通过实现一个简单的案例，使读者能快速地掌握如何使用MySQL数据库。
实践与练习
1.选择题
（1）在MySQL中，通常使用（ ）语句来指定一个已有数据库作为当前工作数据库。
A.USING B.USED C.USES D.USE
（2）SQL语句中修改表结构的命令是（ ）。
A.MODIFY TABLE B.MODIFY STRUCTURE
C.ALTER TABLE D.ALTER STRUCTURE
（3）用SQL的ALTER TABLE语句修改基本表时，删除其中某个列的约束条件应使用的子句是（ ）。
A.ADD B.DELETE C.MODIFY D.DROP
（4）用SQL语句建立表时，将某字段定义为主关键字，应使用关键字（ ）。
A.CHECK B.PRIMARYKEY C.FREE D.UNIQUE
（5）启动MySQL服务所使用的命令是（ ）。
A.START B.NET START MYSQL
C.START MYSQL D.START NET MYSQL
2.填空题
（1）创建唯一性索引时，通常使用的关键字是____。
（2）在CREATE TABLE语句中，通常使用____关键字来指定主键。
（3）在MySQL的安装过程中，若选用“启用TCP/IP网络”，则MySQL默认选用的端口号是____。
（4）MySQL安装成功后，在系统中会默认建立一个____用户。
（5）在MySQL中，查看当前服务器上数据库列表所使用的命令为____。
实验指导：学生选课系统数据库设计
题目1 MySQL数据库的安装和配置
1.任务描述
掌握MySQL数据库的安装和配置。
2.任务要求
（1）下载相应版本的软件并安装，完成MySQL服务实例的配置。
（2）配置PATH环境变量。
（3）完成指定操作：启动服务、连接MySQL服务器和停止服务。
3.操作步骤提示
（1）安装完MySQL后，进行MySQL服务实例的配置。
（2）在环境变量PATH中，添加MySQL安装目录下的bin目录。
（3）输入相应的MySQL命令，完成指定操作。
题目2 数据库及数据表的基本操作
1.任务描述
完成学生选课系统数据库的设计。
2.任务要求
（1）设计学生选课系统的数据库。
（2）完成学生选课系统数据库的创建。
（3）完成学生选课系统数据表的创建。
3.操作步骤提示
（1）根据自己的理解设计学生选课系统的数据库。各表参考结构如下。
Students（Sno，Sname，Sex，Department），其中Sno为主键。
Courses（Cno，Cname，Credit，Semester，Period），其中Cno为主键。
SC（Sno，Cno，Grade），其中Sno和Cno为主键。
（2）在控制台中使用SQL语句创建数据库，参考代码如下。
create database StudentManage；
（3）在控制台中使用SQL语句创建数据表，参考代码如下。
题目3 使用Navicat完成数据库及数据表的操作
1.任务描述
使用Navicat完成学生选课系统数据库的设计。
2.任务要求
（1）连接MySQL服务器。
（2）创建StudentManage1数据库。
（3）创建学生表、课程表及成绩表。
（4）完成数据的输入。
3.操作步骤提示
（1）连接MySQL服务器。
（2）创建数据库。右击mysql连接，在弹出的快捷菜单中选择New Database命令，创建数据库StudentManage1，设置字符集为utf8，完成数据库的创建。
（3）创建数据表。双击StudentManage1数据库图标，然后右击Tables图标，在弹出的快捷菜单中选择New Table命令，在右侧窗口中设计表的结构。
（4）添加记录。在左侧导航窗口中双击students数据表图标，打开数据表，然后向表中添加记录。