第3章 MySQL管理表记录

表是数据库中存储数据的基本单位，它由一个或多个字段组成，每个字段需要有对应的数据类型。例如，年龄对应整数类型，姓名对应字符串类型，生日对应日期类型等。因此，在创建表时必须为表中的每个字段指定正确的数据类型及可能的数据长度。数据表创建成功后，就可以使用SQL语句完成记录的增添、修改和删除。本章将详细介绍MySQL中提供的各种数据类型、运算符和字符集，以及数据表中记录的插入、修改和删除。

3.1 MySQL的基本数据类型

在创建表时，表中的每个字段都有数据类型，用来指定数据的存储格式、约束和有效范围。选择合适的数据类型可以有效地节省存储空间，同时可以提升数据的计算性能。MySQL提供了多种数据类型，主要包括数值类型（包括整数类型和小数类型）、字符串类型、日期时间类型、复合类型和二进制类型。

3.1.1 整数类型

MySQL中的整数类型有TINYINT、SMALLINT、MEDIUMINT、INT（INTEGER）和BIGINT。每种整数类型所占用的字节数及表示的整数范围如表3-1所示。

表3-1 整数类型的字节数其取值范围

默认情况下，整数类型既可以表示正整数，也可以表示负整数。如果只希望表示正整数，则可以使用关键字unsigned来进行修饰。例如，将学生表中的学生年龄字段定义为无符号整数，可以使用SQL语句age tinyint unsigned来实现。

对于整数类型还可以指定其显示宽度，如int（8）表示当数值宽度小于8位时在数字前面填满宽度。如果在数字位数不够时需要用0填充时，则可以使用关键字zerofill。但是在插入的整数位数大于指定的显示宽度时，将按照整数的实际值进行存储。

【例3-1】 整数类型的定义及使用。

1）在数据库type_test中创建表int_test，表中包括两个int类型字段int_field1和int_field2，字段的显示宽度分别为6和4，然后输出表结构。SQL语句如下。

create database type_test；

use type_test；

create table int_test（int_field1 int（6），int_field2 int（4））；

desc int_test；

SQL语句运行结果如图3-1所示。

图3-1 整数类型定义

2）在上面的int_test表中插入一条记录，使得两个整数字段的值都为5，SQL语句如下。

insert into int_test values（5，5）；

SQL语句运行结果如图3-2所示。

图3-2 插入整数值并输出

3）将int_test表中的两个字段的定义都加上关键字zerofill，然后再输出表中的记录并查看结果。SQL语句如下。

alter table int_test modify int_field1 int（6）zerofill；

alter table int_test modify int_field2 int（4）zerofill；

由于整数5的宽度小于字段的显示宽度6和4，所以在5的前面用0来填充。SQL语句运行结果如图3-3所示。

4）在上面的int_test表中插入1条记录，使得两个整数字段的值都为123456789，SQL语句如下。

insert into int_test values（123456789，123456789）；

由于整数值123456789大于指定的显示宽度，所以按照整数的实际值进行存储。SQL语句运行结果如图3-4所示。

图3-3 整数位数不够宽度时用0填充

图3-4 整数位数大于显示宽度时按照实际值存储

整数类型还有一个属性：AUTO_INCREMENT。在需要产生唯一标识符或顺序值时，可以利用此属性，该属性只适用于整数类型。一个表中最多只能有一个AUTO_INCREMENT字段，该字段应该为NOT NULL，并且定义为PRIMARY KEY或UNIQUE。AUTO_INCREMENT字段值从1开始，每行记录其值增加1。当插入NULL值到一个AUTO_INCREMENT字段时，插入的值为该字段中当前最大值加1。

3.1.2 小数类型

MySQL中的小数类型有两种：浮点数和定点数。浮点数包括单精度浮点数FLOAT类型和双精度浮点数DOUBLE类型，定点数为DECIMAL类型。定点数在MySQL内部以字符串形式存放，比浮点数更精确，适合用来表示货币等精度高的数据。

浮点数和定点数都可以在类型后面加上（M，D）来表示，M表示该数值一共可显示M位数字，D表示该数值小数点后的位数。当在类型后面指定（M，D）时，小数点后面的数值需要按照D来进行四舍五入。当不指定（M，D）时，浮点数将按照实际值来存储，而DECIMAL默认的整数位数为10，小数位数为0。

由于浮点数存在误差问题，所以如货币等对于精度敏感的数据应该使用定点数来表示和存储。

【例3-2】 小数类型的定义及使用。

1）在数据库type_test中创建表number_test，表中包括3个字段：float_field、double_field和decimal_field，字段的类型分别为float、double和decimal，然后输出表结构。SQL语句如下。

create table number_test（float_fieldfloat，double_field double，decimal_field decimal）；

desc number_test；

SQL语句运行结果如图3-5所示，从运行结果中可以看出DECIMAL默认的整数位数为10，小数位数为0。

图3-5 小数类型定义

2）在上面的number_test表中插入两条记录，使得3个小数字段的值都为1234.56789和1.234，SQL语句如下。

insert into number_test values（1234.56789，1234.56789，1234.56789）；

insert into number_test values（1.234，1.234，1.234）；

由于DECIMAL类型默认为DECIMAL（10，0），所以插入decimal_field字段的值四舍五入到整数值后插入到表中。SQL语句运行结果如图3-6所示。

图3-6 3个不同类型字段中插入同一个值

3）将number_test表中的3个字段类型分别修改为float（5，1）、double（5，1）和decimal（5，1），并将记录输出。SQL语句如下。

alter table number_test modify float_field float（5，1）；

alter table number_test modify double_field double（5，1）；

alter table number_test modify decimal_field decimal（5，1）；

将表中的3个字段类型分别修改为float（5，1）、double（5，1）和decimal（5，1）后，数据在存储时将小数部分四舍五入并保留1位小数。SQL语句运行结果如图3-7所示。

图3-7 修改字段类型后的输出记录

3.1.3 字符串类型

MySQL支持的字符串类型主要有CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。

CHAR与VARCHAR都是用来保存MySQL中较短的字符串的，二者的主要区别在于存储方式不同。CHAR（n）为定长字符串类型，n的取值范围为0～255；VARCHAR（n）为变长字符串类型，n的取值范围为0～255（5.0.3版本以前）或0～65535（5.0.3版本以后）。CHAR（n）类型的数据在存储时会删除尾部空格，而VARCHAR（n）在存储数据时则会保留尾部空格。

除了VARCHAR（n）是变长类型字符串外，TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT类型也都是变长字符串类型。各种字符串类型及其存储长度范围如表3-2所示。

表3-2 字符串类型及其存储长度范围

【例3-3】CHAR（n）与VARCHAR（n）类型的定义及使用。

在数据库type_test中创建表string_test，表中包括两个字段：char_field和varchar_field，字段的类型分别为char（8）和varchar（8），然后在两个字段中都插入字符串“test”，并给两个字段值再追加字符串“+”，显示追加后两个字段的值。SQL语句如下。

SQL语句运行结果如图3-8所示。

图3-8 CHAR（n）与VARCHAR（n）类型的定义及使用

从运行结果中可以看出，CHAR（n）类型的数据在存储时会删除尾部空格，追加字符串“+”后新的字符串为“test+”；而VARCHAR（n）在存储数据时会保留尾部空格后再追加字符串“+”，所以新的字符串为“test+”。

3.1.4 日期时间类型

日期时间类型包括DATE、TIME、DATETIME、TIMESTAMP和YEAR。DATE表示日期，默认格式为YYYY-MM-DD；TIME表示时间，默认格式为HH：MM：SS；DATETIME和TIMESTAMP表示日期和时间，默认格式为YYYY-MM-DDHH：MM：SS；YEAR表示年份。日期时间类型及其取值范围如表3-3所示。

表3-3 日期时间类型及其取值范围

在YEAR类型中，年份值可以为2位或4位，默认为4位。在4位格式中，允许值的范围为1901～2155。在2位格式中，取值范围为70～99时，表示从1970年～1999年；取值范围为01～69时，表示从2001年～2069年。

DATETIME与TIMESTAMP都包括日期和时间两部分，但TIMESTAMP类型与时区相关，而DATETIME则与时区无关。如果在一个表中定义了两个类型为TIMESTAMP的字段，则表中第一个类型为TIMESTAMP的字段其默认值为CURRENT_TIMESTAMP，第二个TIMESTAMP字段的默认值为0000-00-0000：00：00。

【例3-4】 日期时间类型的定义及使用。

1）在数据库type_test中创建表year_test，在表中定义year_field字段为YEAR类型，在表中插入年份值2155和69并查看记录输出结果。SQL语句如下。

create table year_test（year_field year）；

insert into year_test values（2155）；

insert into year_test values（69）；

select^*from year_test；

SQL语句运行结果如图3-9所示。

图3-9 YEAR类型的定义及使用

2）在数据库type_test中创建表date_test，在表中定义date_field字段为DATE类型，在表中插入日期值9999-12-31和1000/01/01并查看记录输出结果。SQL语句如下。

create table date_test（date_field date）；

insert into date_test values（"9999-12-31"）；

insert into date_test values（'1000/01/01'）；

select^*from date_test；

SQL语句运行结果如图3-10所示。

图3-10 DATE类型的定义及使用

3）在数据库type_test中创建表datetime_test，在表中定义datetime_field字段为DATETIME类型，timestamp_field1和timestamp_field2字段为TIMESTAMP类型，并查看表结构。在表中插入两条记录，第一条记录的所有字段值都为当前日期值，第2条记录只有第一个字段为当前日期值其他两个字段为空，然后查看记录输出结果。SQL语句如下。

SQL语句运行结果如图3-11所示。

图3-11 DATETIME及TIMESTAMP类型的定义及使用

从上面的运行结果可以看出，表中第一个类型为TIMESTAMP的字段其默认值为CURRENT_TIMESTAMP，第二个TIMESTAMP字段的默认值为0000-00-0000：00：00。

4）查看数据库服务器的当前时区，并将当前时区修改为东十时区，然后查看上面表中记录输出结果与时区的关系。SQL语句如下。

show variables like′time_zone′；

set time_zone=′+10：00′；

select^*from datetime_test；

SQL语句运行结果如图3-12所示。

图3-12 DATETIME及TIMESTAMP类型与时区的关系

从上面的运行结果可以看出，当前的时区值为SYSTEM，这个SYSTEM值表示时区与主机的时区相同，实际值为东八区（+8：00）。将时区设置为东十区后，对照图3-11可以发现，TIMESTAMP类型值与时区相关，而DATETIME类型值则与时区无关。

3.1.5 复合类型

MySQL中的复合数据类型包括ENUM枚举类型和SET集合类型。ENUM类型只允许从集合中取得某一个值，SET类型允许从集合中取得多个值。ENUM类型的数据最多可以包含65535个元素，SET类型的数据最多可以包含64个元素。

【例3-5】 复合类型的定义及使用。

1）在数据库type_test中创建表enum_test，在表中定义sex字段为ENUM（'男'，'女'）类型，在表中插入3条记录，其值分别为“男”“女”和NULL，然后查看记录输出结果。SQL语句如下。

create table enum_test（sex enum（′男′，′女′））；

insert into enum_test values（′女′）；

insert into enum_test values（′男′）；

insert into enum_test values（NULL）；

select^*from enum_test；

SQL语句运行结果如图3-13所示。

图3-13 ENUM类型的定义及使用

2）在数据库type_test中创建表set_test，在表中定义hobby字段为SET（'旅游'，'听音乐'，'看电影'，'上网'，'购物'）类型，在表中插入3条记录，其值分别为“看电影，听音乐”“上网”和NULL，然后查看记录输出结果。SQL语句如下。

create table set_test（hobby set（′旅游′，′听音乐′，′看电影′，′上网′，′购物′））；

insert into set_test values（′看电影，听音乐′）；

insert into set_test values（′上网′）；

insert into set_test values（NULL）；

select^*from set_test；

SQL语句运行结果如图3-14所示。

图3-14 SET类型的定义及使用

复合数据类型ENUM和SET存储的仍然是字符串类型数据，只是数据的取值范围受到某种约束。

3.1.6 二进制类型

MySQL中的二进制类型有7种，分别为BINARY、VARBINARY、BIT、TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。BIT数据类型按位为单位进行存储，而其他二进制类型的数据以字节为单位进行存储。各种二进制类型及其存储长度范围如表3-4所示。

表3-4 二进制类型及其存储长度范围

3.2 MySQL运算符

MySQL支持多种类型的运算符，主要包括算术运算符、比较运算符、逻辑运算符和位运算符。

3.2.1 算术运算符

MySQL中的算术运算符包括加、减、乘、除和取余运算，这些算术运算符及其作用如表3-5所示。

表3-5 MySQL中的算术运算符及其作用

【例3-6】 算术运算符的使用。

在数据库type_test中创建表arithmetic_test，表中的字段int_field为int类型，往表中分别插入数值34，123，1，0，NULL，对这些数值完成算术运算。SQL语句如下。

create table arithmetic_test（int_field int）；

insert into arithmetic_test values（34）；

insert into arithmetic_test values（123）；

insert into arithmetic_test values（1）；

insert into arithmetic_test values（0）；

insert into arithmetic_test values（NULL）；

select int_field，int_field+10，int_field-15，int_field^*3，int_field/2，int_field%3from arithmetic_test；

SQL语句运行结果如图3-15所示。

图3-15 算术运算符的使用

3.2.2 比较运算符

比较运算符是对表达式左右两边的操作数进行比较，如果比较结果为真则返回值为1，为假则返回0，当比较结果不确定时则返回NULL。MySQL中的各种比较运算符及其作用如表3-6所示。

表3-6 MySQL中的比较运算符及其作用

【例3-7】 比较运算符的使用。

在数据库type_test中创建表comparison_test，表中的字段int_field为int类型，字段varchar_field为varchar类型。往表中插入的记录分别为：（17，′Mr Li′）和（NULL，′Mrs Li′），对这些数值完成比较运算。SQL语句如下。

在上面的SQL语句中，varchar_field like′%Li′表示当varchar_field中的字符串以Li结尾时，返回值为1，否则返回值为0。varchar_field regexp′^Mr′表示当varchar_field中的字符串以Mr开头时，返回值为1，否则返回值为0。varchar_fieldregexp ′Li$′表示当varchar_field中的字符串以Li结尾时，返回值为1，否则返回值为0。

SQL语句运行结果如图3-16所示。

图3-16 比较运算符的使用

3.2.3 逻辑运算符

逻辑运算符又称为布尔运算符，在MySQL中支持4种逻辑运算符：逻辑非（NOT或！）、逻辑与（AND或＆＆）、逻辑或（OR或||）和逻辑异或（XOR）。

● 逻辑非（NOT或！）：当操作数为假时，则取非的结果为1；否则结果为0。NOT NULL的返回值为NULL。

● 逻辑与（AND或＆＆）：当操作数中有一个值为NULL时，则逻辑与操作结果为NULL。当操作数不为NULL，并且值为非零值时，逻辑与操作结果为1；否则有一个操作数为0时，逻辑与结果为0。

● 逻辑或（OR或||）：当两个操作数均为非NULL值时，如果一个操作数为非0值，则逻辑或结果为1；否则逻辑或结果为0。当有一个操作数为NULL值时，如果另一个操作数为非0值，则逻辑或结果为1；否则逻辑或结果为0。如果两个操作都为NULL时，则逻辑或结果为NULL。

● 逻辑异或（XOR）：当任意一个操作数为NULL时，逻辑异或的返回值为NULL。对于非NULL操作数，如果两个操作数的逻辑真假值相异，则返回结果为1；否则返回值为0。【例3-8】 逻辑运算符的使用。

select（not0），（not-5），（！null）；

select（null and null），（null ＆＆ 1），（-2 ＆＆ -5），（1 and 0）；

select（null or null），（null or 1），（null||0），（-8 or 0）；

select（null xor null），（null xor 1），（0 xor 0），（-8 xor 0），（1 xor 1）；

上面SQL语句的运行结果如图3-17所示。

图3-17 逻辑运算符的使用

3.2.4 位运算符

位运算是指对每一个二进制位进行的操作，包括位逻辑运算和移位运算。在MySQL中，位逻辑运算包括按位与（＆）、按位或（|）、按位取反（～）和按位异或（^）。操作数在进行位运算时，是将操作数在内存中的二进制补码按位进行操作。

● 按位与（＆）：如果两个操作数的二进制位同时为1，则按位与（＆）的结果为1；否则按位与（＆）的结果为0。

● 按位或（|）：如果两个操作数的二进制位同时为0，则按位或（|）的结果为0；否则按位或（|）的结果为1。

● 按位取反（～）：如果操作数的二进制位为1，则按位取反（～）的结果为0；否则按位取反（～）的结果为1。

● 按位异或（^）：如果两个操作数的二进制位相同，则按位异或（^）的结果为0；否则按位异或（^）的结果为1。

移位运算是指将整型数据向左或向右移动指定的位数，移位运算包括左移（＜＜）和右移（＞＞）。

● 左移（＜＜）：将整型数据在内存中的二进制补码向左移出指定的位数，向左移出的位数丢弃，右侧添0补位。

● 右移（＞＞）：将整型数据在内存中的二进制补码向右移出指定的位数，向右移出的位数丢弃，左侧添0补位。

【例3-9】 位运算符的使用。

select 5＆2，5|2，～（-5），2^3，5＜＜3，（-5）＞＞63；

上面SQL语句的运行结果如图3-18所示。

图3-18 位运算符的使用

在MySQL中，整数常量用8个字节来表示，所以-5在向右移动63位后就剩最高位1，然后左端空出的63位都添0，所以（-5）＞＞63的结果为1。

3.2.5 运算符的优先级

在一个表达式中往往有多种运算符，要先进行哪一种运算呢？这就涉及运算符优先级的问题。优先级高的运算符先执行，优先级低的运算符后执行，同一优先级别的运算符则按照其结合性依次计算。MySQL中各运算符的优先级如表3-7所示。

表3-7 MySQL中运算符的优先级

3.3 字符集设置

默认情况下，MySQL使用的字符集为latin1（西欧ISO_8859_1字符集的别名）。由于latin1字符集是单字节编码，而汉字是双字节编码，由此可能导致MySQL数据库不支持中文字符查询或中文字符乱码等问题。为了避免此类问题，需要对字符集及字符排序规则进行设置。

3.3.1 MySQL字符集与字符排序规则

给定一系列字符并赋予对应的编码后，所有这些字符和编码对组成的集合就是字符集（character set）。MySQL中提供了latin1、utf8、gbk和big5等多种字符集。字符排序规则（collation）是指在同一字符集内字符之间的比较规则，一个字符集可以包含多种字符排序规则，每个字符集会有一个默认的字符排序规则。MySQL中的字符排序规则命名方法为：以字符排序规则对应的字符集开头，中间是国家名（或general），以ci、cs或bin结尾。以ci结尾的字符排序规则表示大小写不敏感；以cs结尾的字符排序规则表示大小写敏感；以bin结尾的字符排序规则表示按二进制编码值进行比较。

使用MySQL命令“show character set；”即可查看当前MySQL服务实例支持的字符集、字符集的默认排序规则，以及字符集占用的最大字节长度等信息。MySQL中支持的字符集信息如图3-19所示。

图3-19 MySQL中支持的字符集

使用MySQL命令“show variables like′character%′；”可以查看当前服务实例使用的字符集信息，如图3-20所示。

图3-20中各参数信息的说明如下。

● character_set_client：MySQL客户机的字符集，默认安装MySQL时，该值为latin1。

图3-20 当前MySQL服务实例使用的字符集

● character_set_connection：数据通信链路的字符集，当MySQL客户机向服务器发送请求时，请求数据以该字符集进行编码。默认安装MySQL时，该值为latin1。

● character_set_database：数据库字符集，默认安装MySQL时，该值为latin1。

● character_set_filesystem：MySQL服务器文件系统的字符集，该值固定为binary。

● character_set_results：结果集的字符集，MySQL服务器向MySQL客户机返回执行结果时，执行结果以该字符集进行编码。默认安装MySQL时，该值为latin1。

● character_set_server：MySQL服务实例字符集，默认安装MySQL时，该值为latin1。

● character_set_system：元数据（字段名、表名和数据库名等）的字符集，默认值为utf8。

使用MySQL命令“show variables like′collation%′；”可以查看当前服务实例使用的字符排序规则，如图3-21所示。

图3-21 当前MySQL服务实例使用的字符排序规则

3.3.2 MySQL字符集的设置

当启动MySQL服务并生成服务实例后，MySQL服务实例的字符集character_set_server将使用my.ini配置文件中[mysqld]选项组中的character_set_server参数的值。character_set_client、character_set_connection及character_set_results的字符值将使用my.ini配置文件中[mysqld]选项组中的default_character_set参数的值。可以使用下面4种方法来修改MySQL的默认字符集。

1.修改my.ini配置文件

将my.ini配置文件中[mysqld]选项组中的default_character_set参数的值修改为utf8后，则character_set_client、character_set_connection及character_set_results的参数值都被修改为utf8。将my.ini配置文件中[mysqld]选项组中的character_set_server参数的值修改为utf8后，character_set_server和character_set_database的参数值都被修改为utf8。保存修改后的my.ini配置文件，重新启动MySQL服务器，这些字符集将在新的MySQL实例中生效。

修改字符集后只会影响数据库中的新数据，并不会影响数据库中的原有数据。

2.使用set命令设置相应的字符集

可以使用命令set character_set_database=utf8将数据库的字符集设置为utf8，但这种设置只在当前的MySQL服务器连接内有效。当打开新的MySQL客户机时，字符集将恢复为my.ini配置文件中的默认值。

3.使用setnames命令设置字符集

使用set name sutf8可以一次性地将character_set_client、character_set_connection及character_set_results的参数值都设置为utf8，但这种设置也只在当前的MySQL服务器连接内有效。

4.连接MySQL服务器时指定字符集

当使用命令mysql--default-character-set=utf8 -h 127.0.0.1-u root-p连接MySQL服务器时，相当于连接服务器后执行命令set names=utf8。

3.4 增添表记录

一旦创建了数据库和表，就可以向表中增添记录，使用INSERT语句和REPLACE语句可以向表中增添一条或多条记录。

3.4.1 INSERT语句

使用INSERT语句可以将一条或多条记录插入表中，也可以将另一个表中的结果集插入到当前表中。

INSERT语句的语法格式如下。

对INSERT语句的说明如下。

● table_name：表示进行插入操作的表名。

● column_name：表示需要插入数据的字段名。当省略字段名时，表示给全部字段插入数据。如果只给表中的部分字段插入数据，则需要指出字段名。对于没有指定的字段，其值根据字段的默认值或相关属性来确定。相关规则如下：具有默认值的字段，其值为默认值；没有默认值的字段，若允许为空值，则其值为空值，否则出错；向自增型auto_increment字段插入数据时，系统自动生成下一个编号并插入；类型为timestamp的字段，其值系统自动填充为当前系统日期和时间。

● VALUES子句：包含各字段需要插入的数据清单，数据的顺序要与字段的顺序相对应。如果省略字段名，则要给出每一个字段值。字段值可以为常量、变量或者表达式，也可以为NULL，并且数据类型要与字段的数据类型相一致。

● SET子句：用于给指定字段赋值。

使用INSERT语句可以向表中插入一行记录或多行记录，插入的记录可以给出每个字段的值或给出部分字段值，还可以插入其他表中的数据。

使用INSERT INTO…SELECT…可以从一个表或多个表中向目标表中插入记录。SELECT语句中返回的是一个查询到的结果集，INSERT语句将这个结果集插入到目标表中，结果集中记录的字段数和字段的数据类型要与目标表完全一致。INSERT INTO…SELECT…的语法格式如下。

【例3-10】 INSERT语句的使用。

1）使用INSERT语句向表中的所有字段插入数据。在数据库teacher_course中创建教师表teacher，教师表中包括教师编号、教师姓名和联系电话3个字段，然后向教师表中增添3个教师信息。具体的SQL语句如下。

SQL语句运行结果如图3-22所示。

图3-22 使用INSERT语句向表中的所有字段插入数据

当插入的数据为字符串和日期类型时，字段值要用引号括起来。

2）使用INSERT语句向表中的部分字段插入数据。在数据库teacher_course中创建课程表course，课程表中包括课程编号、课程名称、课程学时数和任课教师编号4个字段。其中课程编号为整数，从1开始依次递增；课程学时数的默认值为72；teacher表与course表之间存在外键约束，向课程表中增添课程信息。具体的SQL语句如下。

SQL语句运行结果如图3-23所示。

图3-23 使用INSERT语句向表中的部分字段插入数据

在上面的INSERT语句中，当向自增型auto_increment字段插入数据时，可以插入NULL值或省略该字段，此时插入的值为该字段的下一个自增值。当向默认值约束字段插入数据时，字段值可以使用default关键字或省略该字段，此时插入的值为该字段的默认值。

由于course表与teacher表之间存在着外键约束关系，所以course表中的teacher_id字段值要来自于表teacher中teacher_id字段，否则会产生错误。如下面的SQL语句所示。

insert into course values（NULL，'组成原理'，75，′0412893408′）；

其运行结果如图3-24所示。

图3-24 插入值不符合外键约束关系时的出错信息

3）使用INSERT语句一次向表中插入多条记录。在上面的course表中将原有的课程信息删除，使用一条INSERT语句重新插入3门课程信息。具体的SQL语句如下。

SQL语句运行结果如图3-25所示。

图3-25 使用INSERT语句一次向表中插入多条记录

4）使用INSERT语句将一个表中的查询结果集添加到目标表中。将上面的course表中学时数为72学时的课程信息添加到表course_hours_72中。具体的SQL语句如下。

SQL语句运行结果如图3-26所示。

图3-26 使用INSERT语句将一个表中的查询结果集添加到目标表中

3.4.2 REPLACE语句

使用REPLACE语句也可以将一条或多条记录插入表中，或者将一个表中的结果集插入到目标表中。

REPLACE语句的语法格式1如下。

REPLACE语句的语法格式2如下。

从上面的语法格式中可以看出，INSERT语句与REPLACE语句的功能基本相同。不同之处在于：使用REPLACE语句添加记录时，如果新记录的主键值或者唯一性约束的字段值与已有记录相同，则已有记录被删除后再添加新记录。

【例3-11】 REPLACE语句与INSERT语句的区别。

在教师表teacher中，使用REPLACE语句插入教师记录：′0412893404′，′唐明明′，′13401234567′。然后再分别使用INSERT和REPLACE语句插入教师记录：′0412893404′，′张明明′，′18701234567′，并查看记录插入情况，具体的SQL语句如下。

SQL语句运行结果如图3-27所示。

图3-27 REPLACE语句与INSERT语句的区别

当使用INSERT语句插入教师记录：′0412893404′，′张明明′，′18701234567′，由于表中已经存在主键为′0412893404′的教师记录，所以插入失败。当使用REPLACE语句插入时，会先将主键为'0412893404'的教师记录删除后，再插入新的教师记录。

3.5 修改表记录

当记录插入后，可以使用UPDATE语句对表中的记录进行修改。UPDATE语句的语法格式如下。

在上面的UPDATE语句中，where子句用于指出表中哪些记录需要修改。如果省略了where子句，则表示表中的所有记录都需要修改。set子句用于指出记录中需要修改的字段及其取值。

【例3-12】 UPDATE语句的使用。

在课程表course中，将课程名为“C程序设计”的课程的学时数改为90学时，具体的SQL语句如下。

update course set course_hours=90 where course_name=′C程序设计′；

select^*from course；

SQL语句运行结果如图3-28所示。

图3-28 UPDATE语句的使用

3.6 删除表记录

当不再使用表中的记录时，可以使用DELETE或TRUNCATE语句将其删除。

3.6.1 DELETE删除表记录

DELETE语句的语法格式如下。

在上面的DELETE语句中，如果没有指定WHERE子句，则表中的所有记录都将被删除，但表结构仍然存在。

【例3-13】 DELETE语句的使用。

1）使用DELETE语句删除课程表course中课程名为“C程序设计”的课程信息。具体的SQL语句如下。

delete from course where course_name=′C程序设计′；

select^*from course；

SQL语句运行结果如图3-29所示。

图3-29 DELETE语句的使用

2）使用DELETE语句删除教师表teacher中教师编号为“0412893401”的教师信息。由于教师表teacher与课程表course之间的外键约束关系，所以要先删除课程表course中教师编号为“0412893401”的课程信息，然后再删除教师表teacher中教师编号为“0412893401”的教师信息。具体的SQL语句如下。

delete from teacher where teacher_id=′0412893401′；

delete from course where teacher_id=′0412893401′；

delete from teacher where teacher_id=′0412893401′；

select^*from teacher where teacher_id=′0412893401′；

SQL语句运行结果如图3-30所示。

图3-30 使用DELETE语句删除具有外键约束关系的记录

3.6.2 TRUNCATE清空表记录

除了使用DELETE语句删除表中的记录外，还可以使用TRUNCATE语句清空表记录。TRUNCATE语句的语法格式如下。

TRUNCATE[table]table_name

从上面的语法格式中可以看出，TRUNCATE语句的功能与DELETE from table_name语句的功能相同。但使用TRUNCATE table语句清空表记录后会重新设置自增型字段的计数起始值，但DELETE语句不会。

【例3-14】 TRUNCATE语句与DELETE语句的区别。

1）将课程表course中的所有记录复制到新表course_copy中，然后使用TRUNCATE语句清空course_copy中的所有记录。插入课程记录：′C程序设计′，72，′0412893402′，并注意课程编号值。具体的SQL语句如下。

create table course_copy like course；

insert into course_copy select^*from course；

select^*from course_copy；

truncate course_copy；

select auto_increment from information_schema.tables where table_name=′course_copy′；

insert into course_copy values（NULL，′C程序设计′，default，′0412893402′）；

select^*from course_copy；

SQL语句运行结果如图3-31所示。

图3-31 使用TRUNCATE语句清空表记录

2）使用DELETE语句清空course_copy中的所有记录，然后插入课程记录：′C程序设计′，72，′0412893402′，并注意课程编号值。具体的SQL语句如下。

delete from course_copy；

select^*from course_copy；

select auto_increment from information_schema.tableswhere table_name=′course_copy′；

insert into course_copy values（NULL，'C程序设计'，default，′0412893402′）；

select^*from course_copy；

SQL语句运行结果如图3-32所示。

图3-32 使用DELETE语句清空表记录

“select auto_increment from information_schema.tables where table_name=′course_copy′；”语句的作用是查询course_copy表中自增字段的起始值。从上面的结果中可以看出：使用TRUNCATE table语句清空表记录后会重新设置自增型字段的计数起始值为1；而使用DELETE语句删除记录后自增字段的值并没有设置为起始值，而是依次递增。

3.7 案例：图书管理系统中表记录的操作

1.图书管理系统中数据库及表的创建

在图书管理系统中首先创建数据库library，在library数据库中主要创建4个表：图书信息表book、读者信息表reader、图书借阅信息表borrow和图书归还信息表giveback。图书信息表中的主要字段有图书ID、书名、作者、出版社、价格、录入时间和是否删除。创建数据库及图书信息表book的SQL语句如下。

读者信息表中的主要字段有读者ID、姓名、性别、证件号码、电话和登记日期。创建读者信息表reader的SQL语句如下。

图书借阅信息表中的主要字段有图书借阅ID、读者ID、图书ID、借书日期、应还日期和是否归还。创建图书借阅信息表borrow的SQL语句如下。

图书归还信息表中的主要字段有图书归还ID、读者ID、图书ID和归还日期。创建图书归还信息表giveback的SQL语句如下。

2.在图书信息表和读者信息表中插入表记录

在图书信息表book中插入4本图书信息。具体的SQL语句如下。

SQL语句运行结果如图3-33所示。

图3-33 使用INSERT语句在book表中插入图书信息

在读者信息表reader中插入两个读者信息。具体的SQL语句如下。

SQL语句运行结果如图3-34所示。

图3-34 使用INSERT语句在reader表中插入读者信息

3.读者借书

如果第一个读者要借第三、四本书，则在图书借阅信息表borrow中插入借书记录，默认借书时间为60天。具体的SQL语句如下。

SQL语句运行结果如图3-35所示。

图3-35 使用INSERT语句在borrow表中插入读者借阅信息

4.读者还书

如果第一个读者10天后归还第三、四本书，则将读者归还图书信息添加到表giveback中，并将图书借阅信息表borrow中的相应字段“是否归还”修改为1，表示图书已经归还。具体的SQL语句如下。

insert into giveback values（null，′1′，′3′，′2016-07-12′）；

insert into giveback values（null，′1′，′4′，′2016-07-12′）；

update borrow set isback=1 where readerid=′1′andbookid=′3′；

update borrow set isback=1 where readerid=′1′andbookid=′4′；

SQL语句运行结果如图3-36所示。

图3-36 读者还书信息设置

5.删除读者

第一个读者已将所借书归还，并且退还借书卡后，可以将第一个读者信息删除。具体的SQL语句如下。

delete from reader where readerid=′1′；

SQL语句运行结果如图3-37所示。

图书借阅信息表和图书归还信息表中的外键约束定义了在删除时都为级联删除，所以在删除读者信息时会同时删除上面两个表中的相应记录。图书借阅信息表中的外键约束定义为：constraint borrow_reader_fk foreign key（readerid）references reader（readerid）on delete cascade。图书归还信息表中的外键约束定义为：constraintgiveback_reader_fkforeign key（readerid）references reader（readerid）on delete cascade。

图3-37 删者读者信息

本章总结

在创建表时，表中的每个字段都有数据类型，用来指定一定的存储格式、约束和有效范围。MySQL提供了多种数据类型，主要包括数值类型（包括整数类型和小数类型）、字符串类型、日期时间类型、复合类型和二进制类型。MySQL中的整数类型有TINYINT、SMALLINT、MEDIUMINT、INT（INTEGER）和BIGINT。MySQL中的小数类型有两种：浮点数和定点数。浮点数包括单精度浮点数FLOAT类型、双精度浮点数DOUBLE类型和定点数DECIMAL类型。定点数在MySQL内部以字符串形式存放，比浮点数更精确，适合用来表示货币等精度高的数据。MySQL支持的字符串类型主要有CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。CHAR与VARCHAR都是用来保存MySQL中较短的字符串的，二者的主要区别在于存储方式不同。日期时间类型包括DATE、TIME、DATETIME、TIMESTAMP和YEAR。DATE表示日期，默认格式为YYYY-MM-DD；TIME表示时间，默认格式为HH：MM：SS；DATETIME和TIMESTAMP表示日期和时间，默认格式为YYYY-MM-DDHH：MM：SS；YEAR表示年份。MySQL中的复合数据类型包括ENUM枚举类型和SET集合类型。ENUM类型只允许从集合中取得某一个值，SET类型允许从集合中取得多个值。ENUM类型的数据最多可以包含65535个元素，SET类型的数据最多可以包含64个元素。MySQL中的二进制类型有7种，分别为BINARY、VARBINARY、BIT、TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。BIT数据类型按位为单位进行存储，而其他二进制类型的数据以字节为单位进行存储。

一旦创建了数据库和表，就可以向表中增添记录，使用INSERT语句和REPLACE语句可以向表中增添一条或多条记录。插入记录后，可以使用UPDATE语句对表中的记录进行修改。当表中的记录不再使用时，可以使用DELETE或TRUNCATE语句将其删除。

实践与练习

1.选择题

（1）要快速完全清空一个表，可以使用语句（ ）。

A.truncate table_name； B.delete table_name；

C.drop table_name； D.clear table_name；

（2）使用DELETE语句删除数据时，会有一个返回值，其含义是（ ）。

A.被删除的记录数目 B.删除操作所针对的表名

C.删除是否成功执行 D.以上均不正确

（3）在MySQL中，与表达式“仓库号NOT IN（"wh1"，"wh2"）”功能相同的表达式是（ ）。

A.仓库号="wh1"AND仓库号="wh2"

B.仓库号！="wh1"OR仓库号！="wh2"

C.仓库号="wh1"OR仓库号="wh2"

D.仓库号！="wh1"AND仓库号！="wh2"

（4）要显示数字时，要想使用0作为填充符，可以使用哪一个关键字（ ）。

A.ZEROFILL； B.ZEROFULL； C.FILLZERO； D.FULLZERO；

（5）DATETIME类型支持的最大年份为哪一年（ ）。

A.2070 B.9999 C.3000 D.2099

2.概念题

（1）MySQL中整数类型有几种？每种类型所占用的字节数为多少？

（2）MySQL中日期类型的种类及其取值范围是什么？

（3）MySQL中复合数据类型有几种？

（4）使用什么命令可以查看MySQL服务器实例支持的字符集信息？

（5）使用什么命令可以查看MySQL服务器实例使用的字符集信息？

3.操作题

在数据库employees_test中有雇员信息表employees，雇员信息表中的数据如表3-8所示。

表3-8 employees表数据

使用SQL语句完成以下操作。

（1）创建数据库及数据表，并在表中插入雇员信息。

（2）修改“开发部”雇员的薪水，修改后其薪水增加20%。

（3）将雇员表中性别为“男”的所有雇员信息复制到employee_copy表中。

（4）将employee_copy表中的所有记录清空。

实验指导

在创建表时，表中的每个字段都有数据类型，用来指定数据的存储格式、约束和有效范围。因此，在创建表时必须为表中的每个字段指定正确的数据类型及可能的数据长度。默认情况下，MySQL使用的字符集为latin1（西欧ISO_8859_1字符集的别名）。由于latin1字符集是单字节编码，而汉字是双字节编码，由此可能导致MySQL数据库不支持中文字符查询或中文字符乱码等问题。为了避免此类问题，需要对字符集及字符排序规则进行设置。数据表创建成功后，就可以使用SQL语句完成记录的增添、修改和删除。

实验目的和要求

● 掌握MySQL中的基本数据类型。

● 掌握MySQL运算符。

● 掌握MySQL的字符集设置。

● 掌握MySQL中表记录的增添、修改和删除。

实验1 MySQL中字符集的设置

MySQL中有4种方法可以修改服务实例的默认字符集：修改my.ini配置文件、使用set命令设置相应的字符集、使用set names命令设置字符集，以及连接MySQL服务器时指定字符集。

题目 在MySQL中设置字符集使其支持中文

1.任务描述

在MySQL数据库表中插入记录时使其支持中文。

2.任务要求

（1）设置服务实例的默认字符集为latin1。

（2）在数据表中插入中文后查询结果是否出现乱码。

（3）设置服务实例的默认字符集为gbk。

（4）在数据表中插入中文后查询结果是否出现乱码。

3.知识点提示

本任务主要用到以下知识点。

（1）参数character_set_client表示MySQL客户机的字符集。

（2）参数character_set_connection表示数据通信链路的字符集，当MySQL客户机向服务器发送请求时，请求数据以该字符集进行编码。

（3）参数character_set_database表示数据库字符集。

（4）参数character_set_results表示结果集的字符集，MySQL服务器向MySQL客户机返回执行结果时，执行结果以该字符集进行编码。

（5）参数character_set_server表示MySQL服务实例字符集。

4.操作步骤提示

（1）使用set character_set_client=latin1将character_set_client字符集设置为latin1。

（2）在library数据库图书信息表book中插入图书信息，SQL语句为：insert into book values（null，′数据库基础与SQL Server′，′徐孝凯′，′清华大学出版社′，35.50，default，default）。

（3）使用select^*from book语句查看刚插入的记录是否为乱码。

（4）使用set character_set_client=gbk将character_set_client字符集设置为gbk。

（5）再使用SQL语句insert into book values（null，′数据库基础与SQLServer′，′徐孝凯′，′清华大学出版社′，35.50，default，default）插入记录，然后查看记录是否为乱码。

实验2 数据表中记录的插入、修改和删除

一旦创建了数据库和表，就可以向表中增添记录，使用INSERT语句和REPLACE语句可以向表中增添一条或多条记录。当插入记录后，可以使用UPDATE语句对表中的记录进行修改。当不再使用表中的记录时，可以使用DELETE或TRUNCATE语句将其删除。

题目 学生成绩管理系统中表记录的操作

1.任务描述

创建学生成绩管理数据库student_score，在学生成绩管理数据库中创建学生表student、课程表course和学生成绩表score，然后在表中完成记录的插入、修改和删除。

2.任务要求

（1）创建数据库和表。

（2）使用INSERT语句或REPLACE语句向表中增添记录。

（3）使用UPDATE语句对表中的记录进行修改。

（4）使用DELETE或TRUNCATE语句删除表中的记录。

3.知识点提示

本任务主要用到以下知识点。

（1）INSERT和REPLACE语句的使用。

（2）UPDATE语句的使用。

（3）DELETE和TURNCATE语句的使用。

4.操作步骤提示

（1）创建学生成绩管理数据库student_score。

（2）在学生成绩管理数据库中创建学生表student，学生表中的主要字段为学号、姓名和性别，并在学生表student中插入学生信息，如表3-9所示。

表3-9 学生信息

（3）在学生成绩管理数据库中创建课程表course，课程表中的主要字段为课程号、课程名称和学分，并在课程表course中插入课程信息，如表3-10所示。

表3-10 课程信息

（4）在学生成绩管理数据库中创建学生成绩表score，学生成绩表中的主要字段为学号、课程号和成绩。在学生成绩表中设置两个参照完整性约束，一个名为score_student_fk，约束表score的学号参照引用表student中的学号，UPDATE的处理方式为级联，DELETE的处理方式为禁止；另一个约束名为score_course_fk，约束表score中的课程号参照引用表course中的课程号，UPDATE的处理方式为级联，DELETE的处理方式为禁止。在学生成绩表score中插入学生成绩信息，如表3-11所示。

表3-11 学生成绩信息

（5）将学生表中学生学号为“2014013601”的记录改为“2014013611”，然后查看student表与score表的相关信息。

（6）将学生成绩表中学号为“2014013602”的学生成绩信息删除。