2.2 MySQL数据库基本操作

2.2.1 MySQL数据库模型

1．数据库模型概述

数据库模型描述了在数据库中结构化和操纵数据的方法，模型的结构部分规定了数据如何被描述（例如树、表等）。模型的操纵部分规定了数据的添加、删除、显示、维护、打印、查找、选择、排序和更新等操作。

数据库的类型是根据数据模型来划分的，而任何一个DBMS也是根据数据模型有针对性地设计出来的，这就意味着必须把数据库组织成符合DBMS规定的数据模型。

数据模型是数据库系统的核心与基础，是关于数据与数据之间的联系、数据的语义、数据一致性约束的概念性工具的集合，它包括数据结构、数据操作、完整性约束规则三部分。DBMS支持的基本数据模型主要包括层次模型、网状模型和关系模型。

（1）层次模型

层次模型的数据结构是树状结构，只有一个根节点（没有父节点），根节点以外的节点有且只有一个父节点。层次模型中的记录只能组织成有向树的集合，根结点在最上端，层次最高，子结点在下，逐层排列，每个节点表示一个记录类型对应于实体的概念，每个记录类型有且只有一条从父节点通向自身的路径，使得层次数据库系统只能直接处理一对多的实体关系。以层次模型建立的数据库系统的典型代表是20世纪60年代末，IBM公司推出的IMS（Information Management System）信息管理系统。

（2）网状模型

网状模型的数据结构是有向图结构，它允许一个以上的节点没有父节点，一个节点可以有多个父节点。每个节点表示一个记录型（实体），每个记录型可包含若干个字段（实体的属性），节点间用连线表示记录型（实体）间的父子关系。这样，网状模型中的数据就用记录的集合表示，数据间的联系用链接（指针）表示，数据库中的记录可以被组织成任意图的集合。网状数据模型的典型代表是DBTG（Data Base Task Group）系统，也称CODASYL（Conference/Committee On Data Systems Languages）系统。1969年10月，美国数据系统语言委员会（CODASYL）下属的数据库任务组（DBTG）发布了网状数据库模型的第一个语言规范，该模型被称为CODASYL模型或DBTG模型，根据该模型实现的系统一般称为DBTG系统或CODASYL系统。DBTG系统是典型的三级体系结构，即子模式、模式、存储模式，相应的数据定义语言分别称为子模式定义语言SSDDL、模式定义语言SDDL、设备介质控制语言DMCL，另外还有数据操纵语言DML。网状数据模型可以很方便地表示现实世界中的多种复杂关系，避免了数据的重复性，但同时数据结构也比较复杂，应用程序在访问数据时，不仅要说明对数据做些什么，还要说明所操作记录的路径，数据的插入、删除牵涉的相关数据较多，不利于数据库的维护与重建。

（3）关系模型

关系模型使用二维表的集合描述数据间的联系，表中不能有完全相同的行和列，主表（父表）与相关表（子表）之间实施参照完整性约束。1970年，IBM公司研究员E. F. Codd首次提出数据库系统的关系模型，1977年IBM公司研制的System R作为关系数据库的代表开始运行，其后经过不断改进与扩充，出现了基于System R的数据库系统。关系数据库已成为目前应用最广泛的数据库系统，包括MySQL在内的主流数据库，都属于关系模型数据库产品。

2．MySQL关系数据库模型

MySQL是一种关系型数据库管理系统，MySQL数据库按照“服务器→用户→数据库→数据表→数据项”五级模式存储数据。用户需登录MySQL服务器，才能在拥有的权限内访问数据库中的数据表和数据表中的数据项。管理员用户或根用户（root）拥有MySQL数据库的全部操作权限，可以根据需要来创建普通用户并授予操作数据库的相关权限。用户只能在各自的操作权限内访问MySQL数据库。

在MySQL中，一个数据库中允许有若干个数据表，一个数据库对应一个文件夹，一个数据表对应若干文件；每个数据表都是一个由行和列构成的二维表，二维表的一行称为一条记录，一列称为一个字段（即数据项），每个字段都具有特定的字段名、数据类型、宽度、小数位数、取值范围等，而且，数据表中没有完全相同的两行或两列数据存在；另外，表示实体的数据表（主表或父表）中存在主键，表示联系的数据表（相关表或子表）中存在外键，数据表之间按照主键和外键值相等的原则建立关联。

2.2.2 MySQL数据库操作方式

MySQL数据库有两种操作方式：一是利用MySQL命令行客户端在命令界面使用MySQL命令操作数据库；二是利用phpMyAdmin软件在图形界面以可视化的形式管理数据库的数据。

1．命令界面操作方式

操作MySQL数据库，用户首先要登录MySQL服务器，然后才能在MySQL命令行客户端输入相关命令进行操作。用户分为管理员用户和普通用户，管理员用户具有使用MySQL数据库的最高操作权限，而普通用户由管理员用户创建，普通用户的用户名、操作密码以及数据库操作权限也由管理员用户指定，普通用户只能在指定的权限内操作数据库。安装MySQL软件后，MySQL服务器默认的管理员用户名是root（也被称为root用户），其登录密码为安装MySQL时设定的密码。登录MySQL服务器和执行MySQL命令的操作方法如下。

1）执行命令方式登录MySQL服务器。选择Windows桌面的“开始”→“所有程序”→“附件”→“命令提示符”，在出现的DOS命令提示符窗口输入正确的登录命令，如图2-1所示。然后按〈Enter〉键执行输入的登录命令，即可进入MySQL命令行客户端界面，屏幕出现“mysql>”提示符，表明正确登录了MySQL服务器，如图2-2所示。

登录MySQL服务器的命令格式是：mysql-u＜用户名＞-p＜密码＞。例如，用户名为root、密码为12345678，则登录MySQL服务器的命令是：mysql-uroot-p12345678。

注意：字母u和p必须为小写，-u后面可以有空格，也可以无空格；紧接着是＜用户名＞，输入用户名时两端不要带格式中的尖括号；＜用户名＞后至少有一个空格，之后是“-p＜密码＞”；-p与＜密码＞之间不能有空格，输入密码时要忽略格式中的尖括号。

2）管理员用户利用MySQL命令行客户端进行登录。root用户还可以以管理员身份登录MySQL服务器。选择Windows桌面的“开始”→“所有程序”→“AppServ”→“MySQL Command Line Client”，会出现图1-21所示的MySQL命令行客户端界面（要求输入管理员用户的密码），输入正确的密码（输入安装MySQL时设置的root的密码12345678），按〈Enter〉键后出现“mysql>”提示符，就以管理员身份登录了MySQL服务器。这种方式登录时的用户名默认为root，所以，需要输入安装MySQL时设置的密码（本书的范例均按12345678作为root用户的登录密码），才能成功登录，登录后具有操作数据库的最高权限。

3）执行MySQL命令。登录MySQL服务器之后，就可以在MySQL命令行客户端操作界面，在“mysql>”提示符后输入正确的MySQL命令，再按〈Enter〉键即可执行命令并返回结果。例如，执行“show variables like 'time_zone';”命令，如图2-3所示。

说明：MySQL命令行必须以分号“;”或字符“\g”结束。输入MySQL命令时，一行输不完可按〈Enter〉键换行后继续输入（注意一个单词不能拆成两行书写），也可不按〈Enter〉键让命令自动换行后继续输入，直到以分号“;”或“\g”结束，命令才算输完，再按〈Enter〉键才能执行命令。另外，要取消正在输入的命令行，可在未结束的命令行后直接输入“\c”（不含引号），再按〈Enter〉键即可；要退出MySQL命令行客户端，执行“exit;”命令即可。MySQL命令行后可用“#”进行注释（单行注释）；多行注释以“/*”开始，以“*/”结束。

2．图形界面操作方式

图形界面操作方式是指利用phpMyAdmin软件管理数据库的数据，加工数据时采用菜单方式在可视化图形界面进行操作，用户不需要记住MySQL命令即可操作数据库。

phpMyAdmin软件安装成功后，可在浏览器地址栏输入“http://网址或域名:端口号/phpMyAdmin”格式的网址（例如，http://127.0.0.1:8080/phpMyAdmin或http://localhost:8080/phpMyAdmin，端口号默认80可省略），再按〈Enter〉键，会出现图1-24所示的phpMyAdmin登录窗口，输入正确的用户名、密码，然后单击“执行”按钮，则出现图1-25所示的数据库管理器窗口。如果是以root用户的身份登录至图1-25所示的图形界面，就可以利用各菜单项执行建库、建表、删表、删库、浏览与修改表结构的操作，以及执行数据的增加、浏览、查询、修改、删除等各种操作；如果是以普通用户的身份登录至phpMyAdmin的数据库管理界面，便可以在普通用户所赋予的操作权限内使用数据库了。由于是图形操作界面，所以具体操作也比较简单、方便，容易理解，这里不再赘述。

2.2.3 MySQL客户端命令格式与应用

如前所述，在MySQL命令行客户端，在“mysql>”提示符后按照正确的命令格式输入MySQL命令（命令行以分号“;”或“\g”结束），按〈Enter〉键即可执行该命令并返回执行结果（参见图2-3）。

1．查看与设置系统变量

（1）查看系统变量的值

1）查看MySQL服务器中所有全局系统变量的信息。命令格式为：show global variables；

2）查看与当前会话相关的所有会话系统变量及所有全局系统变量的信息。命令格式为：

● show session variables；

● show variables；

3）说明。查看指定系统变量的值，可在上述命令格式后添加“like '模式串'”，其中，模式串可包含百分号“%”或下划线“_”通配符，一个百分号“%”匹配0个或多个字符，一个下划线“_”匹配一个字符。

4）举例。

● 查看指定的全局系统变量：show global variables like 'innodb_data_file_path'；

● 查看指定的会话系统变量：show session variables like 'character_set_client'；

● 查看包含“char”的系统变量：show variables like '%char%'；

（2）设置系统变量的值

1）重新设置全局系统变量的值。命令格式为：

● set@@global.全局系统变量=值；

● set global全局系统变量=值；

2）重新设置会话系统变量的值。命令格式为：

● set@@session.会话系统变量=值；

● set session会话系统变量=值；

● set@@会话系统变量=值；

● set会话系统变量=值；

3）说明。

● 具备super权限的账户才能设置全局系统变量；

● 将一个系统变量的值设置为MySQL默认值，可使用default关键字；

● set命令不会导致my.ini配置文件的内容发生变化；

● 大部分系统变量的值，可以在MySQL服务运行期间使用set命令重新设置，但一些特殊的全局系统变量（例如tmpdir、datadir、version、log_bin）属于静态变量，不能在MySQL命令行客户端使用set命令重新设置。

4）举例。

● 将全局系统变量innodb_file_per_table设置为MySQL默认值：set@@global.innodb_file_per_table=default；

● 将全局系统变量innodb_file_per_table设置为“ON”：set global innodb_file_per_table=ON；

● 将会话系统变量pseudo_thread_id的值设置为“5”：set pseudo_thread_id=5；

● 将MySQL当前会话的存储引擎设置为“MyISAM”：set default_storage_engine=MyISAM；

（3）set names命令

1）将character_set_client、character_set_connection、character_set_results统一设置为指定的字符集。命令格式为：set names '字符集名称'；

2）举例。

● 将字符集设置为gb2312：set names 'gb2312'；

● 将字符集设置为utf8：set names 'utf8'；

● 将字符集设置为gbk：set names 'gbk'；

3）说明。

● “set names '字符集名称'”命令可以将character_set_client（客户端的语句使用的字符集）、character_set_connection（客户端与服务器端连接采用的字符集）、character_set_results（向客户端返回查询结果使用的字符集）三者统一，“临时一次性地”将它们设置成相同的编码字符集。

● 此命令中字符集名称两端的引号可以省略，例如，“set names 'gb2312';”可以写成“set names gb2312;”。

2．管理MySQL数据库

（1）显示数据库

显示MySQL中所有数据库的名称。命令格式为：show databases；

（2）建立数据库

1）建立指定名称的数据库。命令格式为：create database数据库名；

2）如果指定数据库不存在，则建立该数据库。命令格式为：create database if not exists数据库名；

3）举例。如果booklending数据库不存在，则建立booklending数据库：create database if not exists booklending；

4）说明。创建数据库后，会在MySQL数据库根目录下以“数据库名”建立一个文件夹，该文件夹下也会自动建立一个名为db.opt的文件。db.opt文件的主要功能是记录当前数据库的默认字符集及字符序等信息。之后为该数据库建立的数据表，将以文件形式，保存在该文件夹下。

（3）打开或选择数据库

1）打开或选择待使用的数据库，使待选数据库成为当前数据库。命令格式为：use数据库名；

2）举例。打开或选择booklending数据库：use booklending；

（4）删除数据库

1）删除指定数据库。命令格式为：drop database数据库名；

2）如果指定数据库存在，则删除该数据库。命令格式为：drop database if exists数据库名；

3）举例。如果booklending数据库存在，则删除booklending数据库：drop database if exists booklending；

3．管理MySQL数据表

（1）建立数据表

1）在数据库内建立指定的数据库表。命令格式为：create table数据表名（字段名数据类型（宽度）约束条件，…，字段名数据类型（宽度）约束条件，其他约束条件）其他选项；

2）说明

● 要建立数据表，必须提供数据表名、字段名、字段的数据类型（参见2.1.2节MySQL数据类型），并根据数据类型的需要来指定宽度，可以设置主键、外键、默认值、非空、唯一性、是否自增型字段等约束条件，允许设置数据表的存储引擎类型、字符集类型等其他选项；如果一些约束条件或选项缺省，则采用MySQL默认的设置；

● 主键、外键、默认值、非空、唯一性分别使用关键字primary key、foreign key、default、not null、unique进行约束；

● 自增型字段用auto_increment约束，且该字段必须为整型，主键才能设置为自增型，设置格式为“字段名int auto_increment primary key”；MySQL自增型字段的值默认从1开始递增，且步长为1；

● 如果主键由至少两个字段构成，可作为“其他约束条件”，使用“primary key （字段名，…，字段名）”设置为复合主键；设置为主键的字段会自动设置为非空属性字段；

● 外键约束也属于“其他约束条件”，设置格式是“constraint约束名foreign key （子表字段名或字段名列表） references父表名（字段名或字段名列表） on delete级联选项on update级联选项;”，其中级联选项可以是cascade、set null、no action、restrict，“cascade”表示在父表中执行删除（delete）或修改（update）操作时，子表中对应的相关记录会自动执行级联删除或级联更新操作（子表中对应的相关记录是指子表中外键值与父表的主键值相同的所有记录；级联删除是指在父表中删除一条记录时，子表中的所有相关记录自动被删除；级联更新是指在父表中将一条记录的主键值修改为新值时，子表中所有相关记录的相应外键值自动被更新为这个新值）；“set null”表示在父表中执行删除（delete）或修改（update）操作时，子表中对应的相关记录的外键值会自动设置为null值；“no action”表示如果子表中存在相关记录，则不允许在父表中删除（delete）相应的记录或修改（update）相应记录的主键值；“restrict”与no action功能相同，且为级联选项的默认值；

● 其他选项包括设置数据表的存储引擎类型和字符集类型等，设置格式分别是“engine=存储引擎类型”“default charset=字符集类型”，例如：engine=InnoDB、default charset=gb2312；

● 使用MySQL命令“show engines;”即可查看MySQL服务支持的存储引擎，其中常用的存储引擎有InnoDB以及MyISAM存储引擎。相对于其他存储引擎，InnoDB存储引擎是事务安全的，且支持外键，而MyISAM存储引擎主要支持OLAP，但不是事务安全的，也不支持外键。MySQL 5.5以上版本默认的存储引擎为InnoDB。本书默认采用InnoDB引擎和gb2312字符集；

● 创建表时，如果存储引擎是InnoDB，则在数据库目录下对应建立两个数据表文件，主文件名为表名，扩展名分别为“.frm”（表结构定义文件）和“.ibd”（独享表空间文件，保存表的数据、索引以及该表的事务回滚等信息），同时，表的元数据信息存储在共享表空间文件ibdata1中，重做日志信息采用轮循策略依次记录在重做日志文件ib_logfile0和ib_logfile1中，共享表空间文件和重做日志文件均存储在数据库根目录下；如果存储引擎是MyISAM，则在数据库目录下对应建立3个数据表文件，主文件名为表名，扩展名分别为“.frm”（表结构定义文件）、“.MYD”（数据文件）和“.MYI”（索引文件）；

● 字段名中不能出现+、-、=、/、&等非法字符。

3）举例1。

根据图1-2所示的读者借阅系统E-R模型及分析所得到的借书关系模型，可将前述booklending数据库作为借书数据库，并在booklending数据库中建立读者、图书、借阅、留言对应的数据表。假设读者表、图书表、借阅表、留言表的表名分别为reader、book、borrow、note，那么，要创建这些数据表，只需要在MySQL命令行客户端，在“mysql>”提示符后执行“use booklending;”命令后，再分别输入和执行以下命令即可：

● 创建读者表。

create table reader（读者编号varchar（5） not null primary key，姓名varchar（20） not null，性别varchar（1），出生日期datetime，单位varchar（30），是否学生varchar（1），会员类别varchar（2），电话号码varchar（13），Email varchar（30），密码varchar（16）） ENGINE=InnoDB DEFAULT CHARSET=gb2312；

● 创建图书表。

create table book（图书编号varchar（5） not null primary key，图书名称varchar（40） not null，内容提要mediumtext，作者varchar（20） not null，出版社varchar（40），定价float，类别varchar（6），ISBN varchar（35），版次varchar（20），库存数int，在库数int，在架位置varchar（12）） ENGINE=InnoDB DEFAULT CHARSET=gb2312；

● 创建借阅表。

create table borrow（读者编号varchar（5） not null，图书编号varchar（5） not null，借阅日期datetime，归还日期datetime，还书标记varchar（1），constraint borrow_reader_fk foreign key（读者编号） references reader（读者编号），constraint borrow_book_fk foreign key（图书编号） references book（图书编号）） ENGINE=InnoDB DEFAULT CHARSET=gb2312；

● 创建留言表。

create table note（留言标题varchar（20），留言内容varchar（100），留言时间datetime，留言状态varchar（2），留言人读者编号varchar（5），回复人读者编号varchar（5），回复内容varchar（100），回复时间datetime，primary key（留言标题，留言内容，留言时间），constraint note_reader_fk foreign key （留言人读者编号） references reader（读者编号） on delete cascade on update cascade） ENGINE=InnoDB DEFAULT CHARSET=gb2312；

执行上述命令后，booklending数据库中的数据表及各表的结构分别如图2-4至图2-8所示。其中，“show tables”命令用于显示当前（或指定）数据库中所有数据表的名称，“desc数据表名”命令用于显示指定数据表的结构，“show create table数据表名”命令用于显示指定数据表的详细建表信息和结构（详见下文）。

4）举例2

假设已建立名为tongxunlu的数据库，要在tongxunlu数据库中建立一个通讯录数据表，表名为txl，则可用以下MySQL语句（参见图2-9）：create table tongxunlu.txl （编号int not null primary key auto_increment，姓名varchar（20） not null，性别varchar（1），职务varchar（5），职称varchar（5），联系地址varchar（50），邮政编码varchar（6），手机号varchar（11），办公电话varchar（12））；

（2）显示数据表名

1）显示当前（或指定）数据库中所有数据表的名称。命令格式为：show tables；或show tables from数据库名；

2）举例。

● 显示当前数据库中所有数据表的名称：show tables；

● 显示booklending数据库中所有数据表的名称：show tables from booklending；

（3）显示数据表结构

1）显示数据库表的结构。命令格式为：describe数据表名；desc数据表名；show create table数据表名；

2）说明。

● 指定数据表名时，应提供所在数据库的名称，格式是“数据库名.数据表名”，如果所指定的数据表属于当前数据库，可缺省数据库名。例如，要使用booklending数据库中的reader表，可以用“booklending.reader”；

● 格式一、格式二显示的结果是完全一样的，格式三显示的表结构信息更详细。

3）举例。

● 显示当前数据库中reader表的结构（图2-5）：desc reader；

● 显示booklending数据库中reader表的详细建表信息和结构（图2-10）：show create table booklending.reader；

（4）修改数据表字段信息

1）在数据表中增加字段、修改字段、删除字段。命令格式为：alter table数据表名add字段名字段类型（宽度）约束条件，change字段名新字段名新字段类型（宽度）约束条件，drop字段名；

2）说明。

● 可根据需要分别用若干个add子句增加字段，用若干个change子句修改字段，用若干个drop子句删除字段；

● add子句可再加上“first”（或“after字段名”）约束，使新增加的字段成为第一个字段（或增加在指定的字段名之后）。

● 子句与子句之间用逗号隔开，子句顺序无关紧要；

● 不增加字段则缺省格式中的add子句，不修改字段则缺省格式中的change子句，不删除字段则缺省格式中的drop子句。

3）举例。

● 在reader表中增加邮政编码、通讯地址字段，修改单位字段，删除Email字段：

● 在reader表中删除邮政编码、通讯地址字段，修改单位字段，增加Email字段：

（5）修改数据表约束条件与其他选项

1）添加和删除约束条件，修改存储引擎、默认字符集、自增字段初始值等其他选项。命令格式为：alter table数据表名 约束条件；

2）说明。

● 约束条件为“add constraint约束名约束类型（字段名）”表示向指定的字段添加约束条件（为数据表添加约束条件时，表的已有记录需要满足新约束条件的要求）；

● 约束条件为“drop primary key”表示删除表的主键约束；

● 约束条件为“drop foreign key约束名”表示删除表的外键约束；

● 约束条件为“drop index唯一索引名”表示删除字段的唯一性约束；

● 约束条件为“engine=新的存储引擎类型”“default charset=新的字符集”“auto_increment=新的初始值”分别表示修改数据表的存储引擎类型、默认字符集、自增字段初始值。

3）举例。

● 向reader表的“姓名”字段添加约束名为“name_unique”的唯一性约束：alter table booklending.reader add constraint name_unique unique （姓名）；

● 删除reader表中名为“name_unique”的唯一性约束：alter table booklending.reader drop index name_unique；

● 删除borrow表中名为“borrow_reader_fk”的外键约束：alter table booklending.borrow drop foreign key borrow_reader_fk；

● 删除note表中名为“note_reader_fk”的外键约束：alter table booklending.note drop foreign key note_reader_fk；

● 删除reader表的主键约束：alter table booklending.reader drop primary key；

● 将reader表的存储引擎修改为MyISAM：alter table booklending.reader engine=MyISAM；

● 将reader表的默认字符集修改为GBK：alter table booklending.reader default charset=gbk；

● 将通讯录数据库tongxunlu中txl表的自增字段初始值修改为11：alter table tongxunlu.txl auto_increment=11；

（6）数据表更名

1）修改数据表名。命令格式为：rename table原数据表名to新数据表名；或alter table表名rename新表名；

2）说明。将原来的数据表名称修改成新的数据表名称。

3）举例。将通讯录数据库tongxunlu中的txl数据表名改为“contact”，可使用以下任意一条MySQL语句：rename table txl to contact；或alter table txl rename contact；

（7）删除数据表

1）删除指定的数据表。命令格式为：drop table数据表名；或drop table if exists数据表名；

2）说明。

● 格式一用于直接执行删除数据表的操作，格式二用于当数据表存在时执行删除操作；

● 删除父表前，必须先删除子表与父表之间的外键约束条件（即解除“父子”关系）。

3）举例。

● 删除通讯录数据库tongxunlu中的contact数据表：

● 如果通讯录数据库tongxunlu中存在contact数据表，则删除contact数据表：

4．管理MySQL数据表的记录

（1）增加记录

1）向指定的数据表中增加一条记录。命令格式为：

● insert into数据表名（字段名1，…，字段名n） values（值1，…，值n）；

● insert into数据表名values（值1，…，值n）；

● insert into数据表名（字段名列表） values（值列表1），…， （值列表m）；

2）说明。

● 第一种格式用于向指定数据表中的指定字段插入数据，其中“值n”是“字段名n”的值，值的类型与相应字段的数据类型一致，没有插入值的字段取NULL或默认值；

● 第二种格式用于为数据表的所有字段插入数据，“值n”是数据表中第n个字段的值，有n个字段就要提供n个值，值的数据类型与对应字段的数据类型一致；

● 第三种格式用于一次性向数据表批量插入多条记录，“字段名列表”格式为“字段名1，…，字段名n”，“值列表m”的格式为“值m1，…，值mn”，字段名列表缺省时表示向所有字段插入数据；

● 对于数值类型的数据，可直接写成整数或小数；对于字符串类型、日期时间类型、enum枚举类型、set集合类型的数据，值的两端需用引号括住；自增型字段的值建议用NULL，以便向自增型字段插入下一个编号；默认约束字段的值可以使用default，表示插入的是该字段的默认值；如果日期时间类型的字段取值为系统的当前日期时间，则可使用MySQL的now()函数来表示其值；

● 插入新记录时，如果表之间有外键约束关系，原则上应当先为父表插入数据，再为子表插入数据。

3）举例。利用以下MySQL语句为booklending数据库中的reader、book、borrow、note数据表添加记录。

● insert into booklending.reader（读者编号，姓名，性别，出生日期，单位，是否学生，会员类别，电话号码，Email，密码） values（"D0001"， “张三”， “男”， "1991-1-1"， “管理工程学院”， "N"， "01"，"0371-67780001"，"zhangsan@163.com"，"111111"）， （"D0003"，“杨八妹”，“女”，"2003-1-1"，“信息管理学院”，"Y"，"02"，"13837121001"，"y8m@163.com"，"333333"）， （"D0002"，“欧阳一一”，“女”，"2001-1-1"，“管理工程学院”，"Y"，"02"，"13511112222"，"oy11@zzu.edu.cn"，"123456"）；

● insert into booklending.book values（”T0001”，“Web数据库技术及应用”，”本书在介绍……”，“李国红”，“清华大学出版社”， 39.00，“计算机”，”978-7-302-46903-2”，“2017年7月第2版”， 20， 10，”02-A-01-0001”）；

● insert into booklending.book values（”T0002”，“管理信息系统”，”管理信息系统是一个由……”，“李国红”， “郑州大学出版社”， 39.80，“管理”，“978-7-5645-3797-3”，“2017年1月第1版”， 10， 6，“02-B-01-0001”）， （”T0003”，“会计信息系统”，“本书依托……”，“徐晓鹏”， “清华大学出版社”， 39，”会计”，”978-7-302-35784-1”，”2014年5月第1版”， 5， 3，”02-C-01-0101”）；

● insert into booklending.borrow（读者编号，图书编号，借阅日期，归还日期，还书标记） values（"D0001"，"T0001"，"2019-5-15 10:45:46"，NULL，"0"），（"D0003"，"T0001"，"2019-6-5 14:30:46"，"2019-6-14 8:49:11"，"1"），（"D0001"，"T0002"，"2019-6-13 16:49:30"，NULL，"0"），（"D0002"，"T0001"，"2019-6-18 11:05:00"，"2019-6-28 16:30:31"，"1"）; /*日期时间型数据若指定为NULL，则存储为NULL，若指定为空字符串或非正确日期时间值，则存储为0000-00-00 00:00:00 */

● insert into booklending.note（留言人读者编号，留言标题，留言内容，留言时间，留言状态，回复人读者编号，回复内容，回复时间） values（”D0002”，“《数据库》到了没？”，“请问我预定的《Web数据库技术及应用》到了没？”，“2019-9-6 15:38:25”，“保密”，“D0001”，“到了”，”2019-9-6 16:17:58”）；

● insert into booklending.note（留言人读者编号，留言标题，留言内容，留言时间，留言状态） values（”D0003”，“借书多久必须归还？”，“请问，学生所借图书在多长时间内必须归还？”， now()，”公开”）;#now()返回系统当前日期时间

（2）浏览与查询记录

1）按指定的要求来查找与显示数据表中满足条件的记录。命令格式为：select字段列表from数据源where条件group by分组字段having分组条件order by字段1 asc，字段2 desc，…，字段n；

2）说明。

● 字段列表采用星号“*”表示在浏览或查询结果中包含数据表的全部字段，采用“字段1，字段2，…，字段i”表示结果中依次显示各指定字段的信息，多表查询时字段名若出现在不同数据表中，则字段采用“数据表名.字段名”的形式，采用“数据表名.*”表示多表查询时显示指定数据表的全部字段；

● 多表查询时，数据源采用“数据表1，…，数据表j”的形式，表示“字段列表”中的字段来自这些数据表；

● where子句指定查询结果应满足的条件，条件可采用类似“字段=值”“字段between起始值and终止值”“字段in （值1，值2，…，值k）”“字段like带通配符%或_的模式串”“字段is null”等的简单条件，条件中值的类型应与字段的类型一致；条件可用not或有时用感叹号“！”表示逻辑非运算使条件反转，也可用and、or分别表示逻辑与、逻辑或运算将简单条件连接成复合条件；“字段like带通配符%或_的模式串”中的百分号“%”表示0个或多个字符，下划线“_”表示1个字符；单表查询时where子句可以缺省，表示数据表的全部记录都满足条件；多表查询时可用“where主表.主键字段名=子表.外键字段名”将子表与主表连接起来，再用not、and、or与其他条件构成复合条件；

● group by子句用于对查询的数据按字段进行分组，这时select之后的“字段列表”中包含带有聚合函数的表达式或其他的表达式，聚合函数主要包括count（*）或count（字段）、sum（数值型字段）、avg（数值型字段）、max（字段）、min（字段），分别用于在各组内统计记录的行数、求字段值的和、求字段值的平均值、求字段的最大值、求字段的最小值；缺省group by子句表示不分组；select之后的表达式往往采用“表达式as列名”格式表示；

● having子句通常与group by子句一起使用，用来过滤分组后的统计信息，缺省having子句表示分组统计后结果不再进行过滤；

● order by子句用于对结果按字段进行排序，先按字段1排序，字段1的值相同的记录再按字段2排序，…，最后按字段n排序；asc和desc需要同order by子句一起使用，字段后带asc表示该字段按升序排序，带desc表示该字段按降序排序，同时缺省asc和desc表示排序字段默认按升序排序；缺省order by子句表示结果不进行排序；

● 单表查询且同时缺省where子句、group by子句和having子句时，格式变为“select字段列表from数据表order by字段1 asc，字段2 desc，…，字段n;”，表示显示数据表中的全部记录，即浏览记录。

3）举例。

【例1】浏览（或查询）book表中的全部记录：select*from booklending.book；

【例2】查询book表的各记录的图书编号、图书名称、作者、出版社、在库数：select图书编号，图书名称，作者，出版社，在库数from booklending.book；

【例3】查询book表中出版社是清华大学出版社的图书的图书编号、图书名称、作者、定价、出版社信息：select图书编号，图书名称，作者，定价，出版社from booklending.book where出版社='清华大学出版社'；

【例4】查询book表中出版社是清华大学出版社的图书的图书编号、图书名称、作者、定价、出版社信息，按图书名称降序排序，图书名称相同的再按定价升序排序：select图书编号，图书名称，作者，定价，出版社from booklending.book where出版社='清华大学出版社' order by图书名称desc，定价asc；

【例5】查询booklending数据库borrow数据表中所有借阅人对应的读者编号、图书编号、图书名称、作者、出版社、借阅日期、还书标记的信息：select读者编号，borrow.图书编号，图书名称，作者，出版社，借阅日期，还书标记from booklending.borrow，booklending.book where borrow.图书编号=book.图书编号；

【例6】查询booklending数据库中借阅了图书编号是“T0001”的图书的所有读者的信息：select reader.* from booklending.reader， booklending.borrow where reader.读者编号=borrow.读者编号and图书编号="T0001"；

【例7】查询booklending数据库中姓名是“张三”的读者的读者编号、姓名及其所借书的图书编号、图书名称、作者、定价、借阅日期、还书标记的信息：select reader.读者编号，姓名，book.图书编号，图书名称，作者，定价，借阅日期，还书标记from booklending.reader，booklending.borrow，booklending.book where reader.读者编号=borrow.读者编号and borrow.图书编号=book.图书编号and姓名=“张三”；

【例8】查询借书人的读者编号、姓名、图书名称、定价、借阅日期、还书标记的信息，按姓名排序：select reader.读者编号，姓名，图书名称，定价，借阅日期，还书标记from booklending.reader，booklending.borrow，booklending.book where reader.读者编号=borrow.读者编号and borrow.图书编号=book.图书编号order by姓名；

【例9】查询reader表中姓“张”的读者的读者编号、姓名、电话号码、E-mail：select读者编号，姓名，电话号码，Email from booklending.reader where姓名like"张%"；

【例10】查询reader表中所有不姓“张”的读者的读者编号、姓名、电话号码、E-mail：select读者编号，姓名，电话号码，Email from booklending.reader where姓名not like"张%"；

【例11】查询reader表中姓名为“张三”“欧阳一一”的读者的读者编号、姓名、E-mail、电话号码：select读者编号，姓名，Email，电话号码from booklending.reader where姓名in （“张三”，“欧阳一一”）；

【例12】查询reader表中除“张三”“欧阳一一”之外的读者的读者编号、姓名、E-mail、电话号码：select读者编号，姓名，Email，电话号码from booklending.reader where姓名not in （“张三”，“欧阳一一”）；

【例13】查询所有未被回复的留言的留言标题、留言内容、留言时间、留言状态及留言人的读者编号和姓名，并按留言时间降序排序：select读者编号，姓名，留言标题，留言内容，留言时间，留言状态from booklending.reader， booklending.note where读者编号=留言人读者编号and （回复内容is null or回复内容=""） order by留言时间desc；

【例14】统计reader表中读者的人数：select count（*） as读者人数from booklending.reader；

【例15】统计reader表中姓“张”的读者人数：select count（读者编号） as姓张的读者人数from booklending.reader where姓名like"张%"；

【例16】统计所有未归还图书的图书编号、图书名称、作者、出版社、借阅人的读者编号、姓名、借阅日期、已借阅天数：select book.图书编号，图书名称，作者，出版社，reader.读者编号，姓名，借阅日期，date（now()）-date（借阅日期） as已借阅天数from booklending.book，booklending.borrow，booklending.reader where book.图书编号=borrow.图书编号and borrow.读者编号=reader.读者编号and （归还日期is null and还书标记="0"）;#date()函数将日期时间转换为日期

【例17】统计reader表中年龄最大的读者所借图书的总数：select borrow.读者编号，姓名， count（*） as年龄最大读者的借书总数from booklending.borrow， booklending.reader where borrow.读者编号=reader.读者编号and reader.读者编号in （select读者编号from booklending.reader where出生日期=（select min（出生日期） from booklending.reader）） group by borrow.读者编号；

【例18】统计reader表中年龄最大与最小的读者所借图书的总数：select borrow.读者编号，姓名， year（now()）-year（出生日期） as年龄， count（*） as借书总数from booklending.borrow， booklending.reader where borrow.读者编号=reader.读者编号and reader.读者编号in （select读者编号from booklending.reader where出生日期=（select min（出生日期） from booklending.reader） or出生日期=（select max（出生日期） from booklending.reader）） group by borrow.读者编号；

【例19】统计borrow表中借书的人数：select count（distinct读者编号） as借阅图书的人数from booklending.borrow; /*distinct表示用于返回唯一不同的值，或表示不重复统计相同的值*/

（3）修改记录

1）修改数据表中满足条件的记录。命令格式为：update数据表名set字段名1=值1 ，…，字段n=值n where条件；

2）说明。

● 值n的数据类型必须与相应的字段n的数据类型保持一致；

● 当用where子句指定条件时，所有满足条件的记录的指定字段都将修改为新的值，如果没有用where子句指定条件，则将数据表中所有记录的指定字段修改成新的值；

● 修改数据表记录时，需要注意数据表的唯一性约束、表之间的外键约束关系和级联选项的设置。

3）举例。

● 将book表中图书编号为“T0001”的图书信息的在库数增加1：update booklending.book set在库数=在库数+1 where图书编号="T0001"；

● 将borrow表中读者编号为“D0001”、图书编号为“T0001”的借书记录的归还日期修改为当前日期时间，还书标记修改为“1”：update booklending.borrow set归还日期=now()，还书标记="1"where读者编号="D0001"and图书编号="T0001"；

（4）删除与清空记录

1）删除数据表中满足条件的记录。命令格式为：delete from数据表名where条件；

2）清空数据表中的记录。命令格式为：

● delete from数据表名；

● truncate table数据表名；

● truncate数据表名；

3）说明。

● delete语句如果不用where子句指定条件，则删除数据表中的全部记录；

● 删除数据表记录时，需要注意表之间的外键约束关系和级联选项的设置；

● 用delete语句清空数据表记录时，不会修改自增型字段的起点，用truncate语句清除数据表的所有记录后，数据表的自增型字段的起点将重置为1（即重新设置自增型字段的计数器）；

● 如果清空记录的数据表是父表，则truncate命令将永远执行失败，truncate table语句不支持事务的回滚，并且不会触发触发器程序的运行。

4）举例。

● delete from booklending.borrow where读者编号="D0001"；

● truncate table booklending.borrow；

● truncate booklending.note；

● delete from booklending.borrow；

5．复制MySQL数据表

（1）复制表结构

1）利用create table语句，将一个已存在的数据表的结构复制到新的数据表中。命令格式为：create table新表名like源表；

2）说明。这种方法复制的表结构无法完全复制表的约束条件，例如，无法复制表之间的外键约束关系。如需要复制完整的表结构，可借助mysqldump工具。

3）举例。将booklending数据库中的reader表的结构复制到“tongxunlu”数据库，新表的名称为“reader1”：create table tongxunlu.reader1 like booklending.reader；

（2）复制表的结构与数据到新表

1）将源表的指定字段和源表中满足条件的记录复制到新表中。命令格式为：create table新表名select语句；

2）说明。

● 上述格式的“select语句”中，如果用“select * from源表”，则将源表的全部字段和相关记录复制到新表，如果用“select字段1，…，字段n from源表”，则将指定的这些字段和相关记录复制到新表；

● “select语句”如不用where子句指定条件，则将源表的结构和所有记录复制到新表，如用where子句指定的条件不成立（例如“where 1=2”），则只复制表结构；

● 这种方法复制的表结构无法完全复制表的约束条件。

3）举例。

● 将booklending数据库中的reader表的结构和全部记录复制到“tongxunlu”数据库，新表的名称为“reader2”：

● 将booklending.reader表的读者编号、姓名、性别、电话号码字段，以及姓名是“张三”的相关信息，复制到新表tongxunlu.reader3中：

● 将booklending数据库中的读者编号、姓名、图书编号、图书名称、借阅日期、归还日期、还书标记字段的信息保存在新表tongxunlu.jieyue中：

（3）复制表的记录到已存在的结构相同的数据表

1）把源表中满足条件的记录复制到结构相同的目标数据表中。命令格式为：

● insert into目标数据表select*from源表where条件；

● insert into目标数据表（字段列表1） select字段列表2 from源表where条件；

2）说明。

● 格式一是将源表中满足条件的记录全部复制到结构相同的目标数据表，格式二是将源表中满足条件的记录的指定字段的值复制到目标数据表指定的字段（字段列表1和字段列表2的字段名之间用逗号隔开，其字段个数、对应的数据类型、宽度等必须一致）；

● 如果不用where子句指定条件，则复制的是源表的全部记录；

● 注意目标数据表若存在主键，则主键的值不能重复。

3）举例。

● 将booklending.reader表中姓名为“张三”的记录插入到tongxunlu.reader1表中：

● 将booklending.reader表中姓名为“欧阳一一”的记录的读者编号、姓名、单位插入到tongxunlu.reader1表的相应字段。

（4）将select语句的查询结果替换（或复制）到已存在的数据表

1）把源表中满足条件的记录的相关字段的值，复制到结构相同的目标数据表的相应字段；其中，若目标数据表有与待插入新记录的主键值或唯一性约束的字段值相同的旧记录，则旧记录先被删除，再插入新记录。命令格式为：replace into目标数据表（字段列表1） select字段列表2 from源表where条件；

2）说明。

● 格式中的“字段列表1”与“字段列表2”的字段个数、对应的数据类型、宽度一致，字段之间用逗号隔开；

● 如果目标数据表没有主键约束和唯一性约束，那么，格式中select子句的执行结果，将作为记录全部插入到目标数据表的指定字段，字段列表缺省时表示插入全部字段的数据；

● 当目标数据表有主键约束（或唯一性约束）时，如果存在与待插入记录的主键值相同的记录（或存在与待插入记录的唯一性约束的字段值相同的记录），则目标数据表中这些与待插入记录的主键值（或唯一性约束的字段值）相同的记录先被删除，然后才插入新记录；

● 上述replace into语句的功能可简单看成是，将select子句的查询结果替换或复制到目标数据表，或目标数据表的指定字段。

3）举例。

将booklending.reader表中的所有记录复制到tongxunlu.reader1表；其中，如果reader表的一条记录的主键值与reader1表的某条记录的主键值相同，则以reader表的这条记录更新（或覆盖）reader1表中主键值相同的那条记录；如果reader表的一条记录的主键值在reader1表中找不到主键值相同的记录，则将reader表的这条记录复制到reader1表：

6．索引

（1）索引的基本概念

索引是将关键字数据以某种数据结构的方式存储到外存，用于提升数据检索性能的一种方法。在数据库中为数据表建立索引的主要目的，就是提高数据的查询效率。对于MySQL数据库，主键约束、唯一性约束、外键约束是基于索引实现的。建立数据库表时，若设置主键约束，则MySQL会自动为主键创建一个主索引（索引名为“PRIMARY”）；若设置唯一性约束，则MySQL自动创建一个唯一性索引（索引名与唯一性约束的字段名相同）；若设置外键约束，MySQL自动创建一个普通索引（索引名与外键约束名相同）。如果删除了唯一性索引，对应的唯一性约束将会自动删除。

如果数据库表的存储引擎是MyISAM，则创建主键约束时，MySQL自动创建主索引，用户可以根据需要建立普通索引，如图2-11所示。如果数据库表的存储引擎是InnoDB，则创建主键约束时，MySQL自动创建聚簇索引（InnoDB表的“主索引”关键字的顺序必须与InnoDB表记录主键值的顺序一致，这种主索引称为聚簇索引；MySQL会为没有主键的InnoDB表自动创建一个“隐式”的主键；InnoDB表必须有一个聚簇索引，且只能有一个聚簇索引），非聚簇索引统称辅助索引，辅助索引的表记录指针称为书签（实际是主键值），如图2-12所示。

（2）创建索引

1）在已有指定表上建立索引，或者在创建表的同时建立索引。命令格式为：

● create索引类型索引名on数据表名（字段名（长度））；

● alter table数据表名add索引类型索引名（字段名（长度））；

● create table数据表名（字段名数据类型（宽度），…，索引类型索引名（字段名（长度））） engine=存储引擎类型default charset=字符集；

2）说明。

● 第一和第二种格式用于在已有表上创建索引，第三种格式是创建表的同时创建索引，可以一次性为表创建多个索引；

● 索引类型是指index（普通索引或复合索引）、unique index（唯一性索引）或fulltext index（全文索引），索引中所有关键字的值均以升序存储；

● 长度表示索引中关键字的字符长度，关键字的值可以是数据库表中字段值的一部分，这种索引称为前缀索引；

● 字符集是指一系列字符及其编码组成的集合，MySQL提供了latin1、gb2312、big5、gbk、utf8等多种字符集，其中latin1支持西欧字符、希腊字符等，gb2312支持简体中文，big5支持繁体中文，gbk支持简体和繁体中文，utf8几乎支持世界上所有国家的字符。

3）举例。

● 为booklending.reader表的“姓名”字段创建普通索引name_index（图2-13和图2-14）：create index name_index on booklending.reader（姓名）；

● 向booklending.reader表的“出生日期”字段添加普通索引date_index，并为“单位”和“姓名”字段创建复合索引complex_idnex（图2-13和图2-14）：

● 在booklending数据库中创建一个存储引擎为MyISAM、默认字符集为gb2312的“book1”数据表，主键为“图书编号”，其余字段自定，同时创建唯一性索引isbn_unique、普通索引name_index、全文索引brief_fulltext和复合索引complex_index：

（3）查看索引

1）查看数据表中创建的索引。命令格式为：show index from数据表名；或show keys from数据表名；

2）举例。显示book1表中已创建的索引：show index from book1；

（4）查看索引生效情况

1）查看索引是否生效。命令格式为：explain select语句；

2）举例。查看查询reader表的相关数据时索引是否生效：explain select读者编号，姓名，性别，出生日期，单位from booklending.reader where姓名=“张三”；

（5）删除索引

1）删除不必要的索引。命令格式为：

● drop index索引名on数据表名；

● alter table数据表名drop index索引名；

● 删除主键约束：alter table数据表名drop primary key；

3）举例。

● 删除book1表的全文索引brief_fulltext：drop index brief_fulltext on booklending.book1；

● 删除唯一性索引isbn_unique：alter table booklending.book1 drop index isbn_unique；

● 删除主键约束：alter table booklending.book1 drop primary key；

7．show命令的其他用法与作用

（1）显示数据表的结构

1）显示数据表中各列的名称（字段名）、数据类型、是否允许空值、键、默认值等。命令格式为：show columns from数据表名from数据库名；或show columns from数据库名.数据表名；

2）举例

● show columns from reader from booklending；

● show columns from booklending.reader；

（2）显示数据库的详细创建信息

1）显示指定数据库的详细建库信息（包括数据库使用的字符编码）。命令格式为：show create database数据库名；

2）举例。显示booklending数据库的详细创建信息：show create database booklending；

（3）显示可用的存储引擎和默认引擎

显示安装MySQL以后可用的存储引擎和默认引擎。命令格式为：show engines；show storage engines；

（4）显示正在运行的所有进程

显示系统中正在运行的所有进程，即当前正在执行的查询。命令格式为：show processlist；

（5）显示数据表的信息

显示当前数据库中每个数据表的信息，信息包括数据表类型和最新更新时间。命令格式为：show table status；

（6）显示服务器支持的权限

显示服务器所支持的不同权限。命令格式为：show privileges；

（7）其他

● 显示一些系统特定资源的信息，例如，正在运行的线程数量。命令格式为：show status；

● 显示innoDB存储引擎的状态。命令格式为：show engine InnoDB status；

● 显示最后一个执行的语句所产生的错误、警告和通知。命令格式为：show warnings；

● 显示最后一个执行语句所产生的错误。命令格式为：show errors；

2.2.4 MySQL用户管理

1．用户管理与mysql.user数据表概述

用户管理主要指数据库管理员（root用户）执行增加新用户、设置用户权限、修改用户密码和删除用户等的操作。MySQL服务器中有一个名为“mysql”的数据库，mysql数据库中包含一个专门用于保存用户信息的“user”数据表，user表中登记了服务器名、用户名、登录密码以及用户的各种操作权限，其中服务器名默认为localhost，操作权限值为“y”时表示具有该权限，权限值为“空”或“n”时表示不具有此权限。mysql.user表的常用字段及其作用如表2-1所示，其他字段请参考相关书籍或在网上查看。

增加用户、修改用户密码或权限、删除用户就是对mysql.user表执行增加、修改、删除记录的操作，按照前述insert into语句、update语句、delete语句的格式即可完成操作。值得注意的是，MySQL 5.7的mysql.user表中存储密码的字段是“authentication_string”，之前旧版本的MySQL的mysql.user表中存储密码的字段是“password”。

2．用户管理操作

（1）增加用户

1）增加一个用户，指定登录的服务器名、用户名、登录密码，设置用户的相关权限。命令格式为：insert into mysql.user（host，user，authentication_string，权限字段，…） values（服务器名，用户名，密码，操作权限值，…）；

2）说明。如果使用MySQL 5.7之前的版本，上述格式中的“authentication_string”应变成“password”；“密码”的值需用password()函数加密。

3）举例。在localhost服务器增加用户名是“user1”、密码为“12345678”的用户，拥有建立数据库和数据表的权限与查询记录的权限，使用以下两个语句（图2-15）：

（2）修改用户的密码和权限

1）设置（或修改）用户的密码和操作权限。命令格式为：update mysql.user set authentication_string=新值，权限字段=操作权限值，… where user=”用户名”；

2）说明。如果使用MySQL 5.7之前的版本，上述格式中的“authentication_string”应变成“password”；“authentication_string=新值”中的“新值”需用password()函数加密。

3）举例。将用户名为“user1”的用户的密码修改为“87654321”，并使用户user1拥有增加、修改、删除记录的权限，可依次使用以下两个语句：

（3）删除用户

1）删除用户信息。命令格式为：delete from mysql.user where user=”用户名”；

2）举例。删除用户名是“user1”的用户，可依次使用以下两个语句：