数据库和表的基本操作

Transcription

1 第 2 章数据库基本操作 学习目标 掌握数据库的创建 查看 选择与删除操作 掌握数据表的创建 查看 修改与删除操作 掌握数据的添加 查询 修改与删除操作 在 MySQL 数据库的学习中, 数据库 数据表和数据的操作, 是每个初学者必须掌握的内容, 同时也是学习后续课程的基础 为了让初学者能够快速体验与掌握数据库的基本操作, 本章将对这些基本操作进行详细讲解 2.1 数据库操作 创建数据库 MySQL 服务器中的数据库可以有多个, 分别存储不同的数据 要想将数据存储到数据库中, 首先需要创建一个数据库 创建数据库就是在数据库系统中划分一块存储数据的空间, 基本语法格式如下 CREATE DATABASE 数据库名称 [ 库选项 ]; 在上述语法中, CREATE DATABASE 表示创建数据库 ; 数据库名称 可以是字母 数字和下划线组成的任意字符串 ; 库选项 用于设置此数据库的相关特性, 如字符集 CHARSET, 校对集 COLLATE 其中, 语法内使用 [] 括起来的选项表示可选参数 具体库选项的设置及相关注意事项会在后面的章节中讲解, 此处读者了解即可 下面创建一个名称为 mydb 的数据库, 具体 SQL 语句与执行结果如下 mysql> CREATE DATABASE mydb; Query OK, 1 row affected (0.00 sec) 在创建数据库后,MySQL 会在存储数据的 data 目录中创建一个与数据库同名的子目录 ( 即 mydb), 同时会在 mydb 目录下生成一个 db.opt 文件, 保存数据库选项 打开 data\mydb\db.opt 文件, 如下所示 default-character-set=latin1

2 default-collation=latin1_swedish_ci 上述内容表示 mydb 数据库的默认字符集为 latin1, 校对集为 latin1_swedish_ci 值得一提的是, 如果创建的数据库已存在, 则程序会报错 为了防止这种情况的发生, 在创建数据库时可以在 数据库名称 前添加 IF NOT EXISTS, 表示指定的数据库不存在时执行创建操作, 否则忽略此操作 例如, 再次创建一个名称为 mydb 的数据库, 具体 SQL 语句如下 mysql> CREATE DATABASE IF NOT EXISTS mydb; Query OK, 1 row affected, 1 warning (0.00 sec) 从以上结果可以看出, 创建数据库时添加 IF NOT EXISTS 后, 再次创建 mydb 就不会发生错误, 但是服务器返回了一条警告信息 下面可通过 SHOW WARNINGS 查看错误信息, 具体 SQL 语句如下 mysql> SHOW WARNINGS; Level Code Message Note 1007 Can't create database 'mydb'; database exists row in set (0.00 sec) 从以上结果可知,MySQL 提示名为 mydb 的数据库已经存在, 不能再重复创建 小提示 : 从前面的 SQL 语句操作可以看出, 创建数据库就是在存储数据的文件夹 data 下生成一个与数据库同名的目录, 用于保存此数据库相关的内容 因此, 在 MySQL 中还可以通过在 data 下创建目录的方式完成数据库的创建 查看数据库 数据库创建完成后, 若要查看该数据库的信息, 或查看 MySQL 服务器当前都有哪些数据库, 可以根据不同的需求选择以下的方式进行查看 1. 查看 MySQL 服务器下所有数据库当需要查看 MySQL 服务器中已经存在的数据库时, 基本语法格式如下 SHOW DATABASES; 执行上述 SQL 语句后, 运行效果如下所示 mysql> SHOW DATABASES; Database

3 information_schema mysql performance_schema mydb sys rows in set (0.00 sec) 在以上输出的结果中,MySQL 服务器已有 5 个数据库, 除 mydb 是手动创建的数据库外, 其他数据库都是 MySQL 安装时自动创建的 information_schema 和 performance_schema 数据库分别是 MySQL 服务器的数据字典 ( 保存所有数据表和库的结构信息 ) 和性能字典 ( 保存全局变量等的设置 ); mysql 数据库主要负责 MySQL 服务器自己需要使用的控制和管理信息, 如用户的权限关系等 ; sys 是系统数据库, 包括了存储过程 自定义函数等信息 对于初学者来说, 建议不要随意的删除和修改这些数据库, 避免造成服务器故障 2. 查看指定数据库的创建信息在完成创建数据库后, 若要查看创建该数据库的信息, 基本语法格式如下 SHOW CREATE DATABASE 数据库名称 ; 接下来查看前面创建的数据库 mydb, 具体 SQL 语句与执行结果如下 mysql> SHOW CREATE DATABASE mydb; Database Create Database mydb CREATE DATABASE `mydb` /*!40100 DEFAULT CHARACTER SET latin1 */ row in set (0.00 sec) 以上输出结果显示了创建 mydb 数据库的 SQL 语句, 以及数据库的默认字符集 选择数据库 由于 MySQL 服务器中的数据需要存储到数据表中, 而数据表需要存储到对应的数据库下, 并且 MySQL 服务器中又可以同时存在多个数据库 因此, 在对数据和数据表进行操作前, 首先需要选择数据库 基本语法格式如下 USE 数据库名称 ; 接下来选择数据库 mydb 进行操作, 具体 SQL 语句如下与执行结果如下 mysql> USE mydb; Database changed

4 多学一招 : 登录 MySQL 服务器时选择数据库数据库的选择除了可以使用 USE 关键字外, 在用户登录 MySQL 服务器时也可以直接选择要操作的数据库, 基本语法格式如下 mysql -u 用户名 -p 密码数据库名上述语法中, 在用户登录服务器的密码后添加要选择的数据库名称, 按下回车 Enter 键后,MySQL 会在登录服务器后自动选择要操作的数据库 例如, 密码为 的 root 用户登录后要直接选择 mydb 数据库进行操作, 具体 SQL 语句如下 # 方式 1, 在登录时显示用户密码, 选择数据库 mysql -uroot -p mydb # 方式 2, 在登录时隐藏用户密码, 选择数据库 mysql -uroot -p mydb Enter password: ****** 删除数据库 在 MySQL 中如若要清除数据库中的所有数据, 回收为数据库分配的存储空间, 则可以执行删除数据库的操作, 基本语法格式如下 DROP DATABASE 数据库名称 ; 在上述语法中,DROP DATABASE 表示删除数据库, 数据库名称 是待删除的数据库名称 下面以删除一个名称为 mydb 的数据库为例进行演示, 具体 SQL 语句与执行结果如下 mysql> DROP DATABASE mydb; Query OK, 0 rows affected (0.01 sec) 需要注意的是, 在使用 DROP DATABASE 删除数据库时, 若待删除数据库 ( 如 mydb) 不存在,MySQL 服务器会报错 因此, 可以在删除数据库时, 使用 IF EXISTS, 具体 SQL 语句与执行结果如下 mysql> DROP DATABASE IF EXISTS mydb; Query OK, 0 rows affected, 1 warning (0.00 sec) 上述 SQL 语句表示, 若 MySQL 服务器中存在数据库 mydb, 则删除该数据库, 否则不执行删除数据库 mydb 的操作 这与创建一个已存在的数据库相同,MySQL 服务器也会返回 1 条警告信息用于提示, 读者可通过 SHOW WARNINGS 查看, 这里不再演示 值得一提的是, 在执行删除数据库操作前, 一定要备份需要保留的数据, 做好相关的安全操作, 确保数据的安全, 避免误操作造成严重的后果

5 多学一招 : 注释 MySQL 中的注释通常可以分为两类, 一类是将注释内容添加到表结构中 ( 此处了解即可, 具体会在创建表时详细讲解 ); 另外一类则会在服务器实际运行时被忽略, 有单行注释和多行注释之分, 下面对这两种注释详细讲解 MySQL 中单行注释以 # 开始标识, 也支持标准 SQL 中 -- 单行注释 但是为了防止 -- 与 SQL 语句中负数减法运算的混淆, 在第二个短横线后必须添加至少一个控制字符 ( 如空格 制表符 换行符等 ) 将其标识为单行注释符号 示例如下 # 此处填写单行注释内容, 如 : 若服务器中没有 mydb 数据库, 则创建, 否则忽略此 SQL CREATE DATABASE IF NOT EXISTS mydb; -- 此处填写单行注释内容, 如 : 若服务器中存在 mydb 数据库, 则删除, 否则忽略此 SQL DROP DATABASE IF EXISTS mydb; 同样的,MySQL 也支持标准 SQL 中的多行注释 /* 此处填写注释内容 */, 它的开始符号为 /*, 结束符号为 */, 中间的内容就是要编写的注释 示例如下 /* 此处填写多行注释内容如 : 利用以下 SQL 查看当前服务器中的所有数据库 */ SHOW DATABASES; 在开发中编写的 SQL 语句, 建议合理的添加单行或多行注释, 方便阅读与理解 脚下留心在 MySQL 使用的过程中, 它相关的基本语法有以下几点需要注意的地方 (1) 换行 缩进与结尾分隔符 MySQL 中的 SQL 语句可以单行或多行书写, 多行书写时可以使用回车 Enter 换行, 每行中的 SQL 语句可以使用空格和缩进增强语句的可读性, 在 SQL 语句完成时通常情况下使用分号 (;) 结尾, 在命令行窗口中也可使用 \g 结尾, 效果与分号相同 另外, 在命令行窗口中, 还可以使用 \G 结尾, 将显示结果以每条记录 ( 一行数据 ) 为一组, 将所有的字段纵向排列展示 (2) 大小写问题 MySQL 的关键字在使用时不区分大小写, 如 SHOW DATABASES 与 show databases 都表示获取当前 MySQL 服务器中有哪些数据库 另外,MySQL 中的所有数据库名称 数据表名称 字段名称默认情况下在 Windows 系统下都忽略大小写, 在 Linux 系统下数据库与数据表名称则区分大小写, 通常开发时推荐都使用小写 本书为了读者便于理解所有 MySQL 关键字均采用大写形式出现, 其他自定义的名称 ( 如数据库名 ) 均以小写形式出现 (3) 反引号的使用

6 在项目开发中, 为了避免用户自定义的名称与系统中的命令 ( 如关键字 ) 冲突, 最好 使用反引号 (` `) 包裹数据库名称 字段名称和数据表名称 其中, 反引号 (`) 在键盘中 左上角 Tab 键的上方, 读者只需将输入法切换到英文, 按下此键即可输入反引号 (`) 2.2 数据表操作 在 MySQL 数据库中, 所有的数据都存储在数据表中, 若要对数据执行添加 查看 修改 删除等操作, 首先需要在指定的数据库中准备一张数据表 下面将详细地讲解如何 在 MySQL 中创建 查看 修改以及删除数据表 创建数据表 创建数据表指的是在已存在的数据库中建立新表 MySQL 既可以根据开发需求创建新的表, 又可以根据已有的表复制相同的表结构 其中依据已有的表创建相同结构的新表方式会在后面的章节中讲解, 此处仅讲解如何根据需求创建一个简单的新表 在 MySQL 数据库中, 使用 CREATE TABLE 语句可以完成数据表的创建, 基本语法格式如下 CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 表名 ( 字段名字段类型 [ 字段属性 ] ) [ 表选项 ] 在上述语法中, 可选项 TEMPORARY 表示临时表, 仅在当前会话中可见, 并且在会话关闭时自动删除 字段名 指的是数据表的列名 ; 字段类型 设置字段中保存的数据类型, 如时间日期类型等 ; 可选项 字段属性 指的是字段的某些特殊约束条件 可选的 表选项 用于设置表的相关特性, 如存储引擎 (ENGINE) 字符集(CHARSET) 和校对集 (COLLATE) 其中, 字段类型 字段属性以及表选项的设置和相关注意事项会在后面的章节中讲解, 此处读者了解即可 需要注意的是, 在操作数据表之前, 应该使用 USE 数据库名 指定操作是在哪个数据库中进行, 否则会抛出 No database selected 错误 下面在 mydb 数据库中, 创建一个名称为 goods 的数据表, 保存商品信息, 具体 SQL 语句及执行结果如下 # 1 创建 mydb 数据库 mysql> CREATE DATABASE mydb; Query OK, 1 row affected (0.00 sec) # 2 选择 mydb 数据库 mysql> USE mydb; Database changed

7 # 3 创建 goods 数据表 mysql> CREATE TABLE goods ( -> id INT COMMENT ' 编号 ', -> name VARCHAR(32) COMMENT ' 商品名 ', -> price INT COMMENT ' 价格 ', -> description VARCHAR(255) COMMENT ' 商品描述 ' -> ); Query OK, 0 rows affected (0.01 sec) 上述 SQL 语句中,INT 用于设置字段数据类型是整型 ;VARCHAR(L) 表示可变长度 的字符串,L 表示字符数, 如 VARCHAR(32) 表示可变的字符数是 32;COMMENT 用于在 创建表时添加注释内容, 并将其保存到表结构中 值得一提的是, 在操作数据表时, 可以不使用 USE 选择数据库 的方式选择数据 库, 直接将表名的位置改为 数据库. 表名 的形式, 就可以在任何数据库下访问其他数据 库中的表 例如, 省略以上第 2 步的操作, 将第 3 步创建 goods 数据表的语句修改成如下 形式 CREATE TABLE mydb.goods ( 此处省略字段的定义 ); 上述语句中, mydb.goods 表示 mydb 数据库中的 goods 数据表 注意 : 项目开发中, 同一个数据库可能被多个项目使用, 因此为了避免数据表重复, 通常为数据表添加前缀用于区分不同的项目 前缀一般选取数据库的前几个字母, 并添加 一个下划线 (_) 例如, mydb_goods 若是一个表名, 则 mydb_ 就是表前缀 查看数据表 MySQL 中提供了专门的 SQL 语句, 用于查看某数据库中存在的所有数据表 指定模式的数据表或数据表的相关信息 下面分别对其进行详细地讲解 1. 查看数据表选择数据库后, 可以通过 MySQL 提供的 SQL 语句进行查看, 基本语法格式如下 SHOW TABLES [LIKE 匹配模式 ]; 上述语法中, 若不添加可选项 LIKE 匹配模式, 表示查看当前数据库中的所有数据表 ; 若添加则按照 匹配模式 查看数据表 其中, 匹配模式符有两种, 分别为 % 和 _ 前者表示匹配一个或多个字符, 代表任意长度的字符串, 长度也可以为 0, 后者仅可以匹配一个字符 下面为了读者更好的理解, 以 mydb 数据库中数据表的查询为例进行演示 首先为 mydb 数据库再添加一张数据表 new_goods, 方便读者对以下示例的理解 new_goods 表的创建语句如下 mysql> CREATE TABLE new_goods (

8 -> id INT COMMENT ' 编号 ', -> name VARCHAR(32) COMMENT ' 商品名 ', -> price INT COMMENT ' 价格 ', -> description VARCHAR(255) COMMENT ' 商品描述 ' -> ); Query OK, 0 rows affected (0.01 sec) 数据表准备完成后, 接下来分别查看 mydb 数据表中的所有数据表和名称中含有 new 的数据表 具体 SQL 语句如下 # 1 查看所有数据表 mysql> SHOW TABLES; Tables_in_mydb goods rows in set (0.00 sec) # 2 查看名称中含有 new 的数据表 mysql> SHOW TABLES LIKE '%new%'; Tables_in_mydb (%new%) new_goods row in set (0.00 sec) 从以上输出结果可以看出,mydb 数据库中一共有两个数据表, 而名字中含有 new 的数据表仅有一个 需要注意的是,LIKE 后的匹配模式必须使用引号包裹 2. 查看数据表的相关信息除了查看数据库下有哪些数据表外, 还可以利用 MySQL 提供的 SQL 语句查看数据表的相关信息, 如数据表的名称 存储引擎 创建时间等, 基本语法格式如下 SHOW TABLE STATUS [FROM 数据库名 ] [LIKE 匹配模式 ]; 下面查看 mydb 数据库下含有 new 的数据表的详细信息, 具体 SQL 语句如下 mysql> SHOW TABLE STATUS FROM mydb LIKE '%new%'\g *************************** 1. row *************************** Name: new_goods Engine: InnoDB Version: 10 Row_format: Dynamic

9 Rows: 0 Avg_row_length: 0 Data_length: Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: :58:05 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) 上述 SQL 语句中, \G 是 MySQL 客户端可以使用的结束符中的一种, 用于将显示结果纵向排列, 适合字段非常多的情况 输出结果中含有值的字段含义如表 2-1 所示, 其他字段的含义读者可参考 MySQL 手册查看, 此处不再赘述 表 2-1 数据表的相关信息字段名称描述 Name 数据表的名称 Engine 数据表的存储引擎 Version 数据表的结构文件 ( 如 lib_user_temp.frm) 版本号 Row_format 记录的存储格式,Dynamic 表示动态数据文件的长度 (MyISAM 存储引擎 ) 或为集群索引分配的内存 Data_length (InnoDB 存储引擎 ), 均以字节为单位 Create_time 数据表的创建时间 Collation 数据表的校对集在表 2-1 中,Row_format 字段的值除 Dynamic 外, 还有 Fixed( 固定 ) Compressed ( 压缩 ) Redundant( 冗余 ) 和 Compact( 紧凑 ) 修改数据表 在实际开发时, 若创建的数据表不符合当前项目的开发要求时, 可以通过修改数据表 来实现 如修改数据表的名称和表选项 下面将分别讲解如何修改数据表 1. 修改数据表名称

10 在 MySQL 中, 提供了两种修改数据表名称的方式, 基本语法格式如下 # 语法格式 1 ALTER TABLE 旧表名 RENAME [TO AS] 新表名 ; # 语法格式 2 RENAME TABLE 旧表名 1 TO 新表名 1[, 旧表名 2 TO 新表名 2]... 在上述语法中,ALTER TABLE 修改数据表名称时, 可以直接使用 RENAME 或在其后添加 TO 或 AS 而 RENAME TABLE 则必须使用 TO, 另外此语法可以同时修改多个数据表的名称 下面使用 RENAME TABLE 将 new_goods 表的名称修改为 my_goods, 具体 SQL 语句与执行结果如下 mysql> RENAME TABLE new_goods TO my_goods; Query OK, 0 rows affected (0.01 sec) mysql> SHOW TABLES; Tables_in_mydb goods my_goods rows in set (0.00 sec) 执行上述 SQL 语句后, 使用 SHOW TABLES; 可查看修改后的数据表 2. 修改表选项数据表中的表选项字符集 存储引擎以及校对集也可以通过 ALTER TABLE 修改, 基本语法格式如下 ALTER TABLE 表名表选项 [=] 值 ; 下面以修改 my_goods 数据表的字符集为例进行演示, 具体 SQL 语句如下 # 1 将 my_goods 数据表的字符集改为 utf8 mysql> ALTER TABLE my_goods CHARSET = utf8; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 # 2 查看修改结果 mysql> SHOW CREATE TABLE my_goods \G *************************** 1. row *************************** Table: my_goods Create Table: CREATE TABLE `my_goods` ( `id` int(11) DEFAULT NULL COMMENT ' 编号 ', `name` varchar(32) CHARACTER SET latin1 DEFAULT NULL COMMENT ' 商品名 ',

11 `price` int(11) DEFAULT NULL COMMENT ' 价格 ', `description` varchar(255) CHARACTER SET latin1 DEFAULT NULL COMMENT ' 商品描述 ' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) 在上述 SQL 语句中, 第 2 步使用 SHOW CREATE TABLE my_goods\g 查看表的字符集, 该语句会在下一节中详细讲解, 此处读者会使用即可 查看表结构 1. 查看数据表的字段信息 MySQL 提供的 DESCRIBE 语句可以查看数据表中所有字段或指定字段的信息, 包括字段名 字段类型等 其中,DESCRIBE 语句以简写成 DESC 基本语法格式如下 # 语法格式 1: 查看所有字段的信息 { DESCRIBE DESC } 数据表名 ; # 语法格式 2: 查看指定字段的信息 { DESCRIBE DESC } 数据表名字段名 ; 下面以查看数据表 my_goods 中的所有字段和指定字段 name 为例演示 具体 SQL 语句及执行结果如下 # 1 所有字段 mysql> DESC my_goods; Field Type Null Key Default Extra id int(11) YES NULL name varchar(32) YES NULL price int(11) YES NULL description varchar(255) YES NULL rows in set (0.00 sec) # 2 name 字段 mysql> DESC my_goods name; Field Type Null Key Default Extra name varchar(32) YES NULL

12 1 row in set (0.00 sec) 在上述执行结果中,Field 表示字段名称,Type 表示字段的数据类型,Null 表示该字段是否可以为空,Key 表示该字段是否已设置了索引,Default 表示该字段是否有默认值, Extra 表示获取到的与该字段相关的附加信息 2. 查看数据表的创建语句若想要查看创建数据表的具体 SQL 语句以及表的字符编码时, 可以使用以下的 SQL 语句, 基本语法格式如下 SHOW CREATE TABLE 表名 ; 接下来查看 my_goods 数据表的创建语句, 具体 SQL 语句及执行结果如下 mysql> SHOW CREATE TABLE my_goods \G *************************** 1. row *************************** Table: my_goods Create Table: CREATE TABLE `my_goods` ( `id` int(11) DEFAULT NULL COMMENT ' 编号 ', `name` varchar(32) CHARACTER SET latin1 DEFAULT NULL COMMENT ' 商品名 ', `price` int(11) DEFAULT NULL COMMENT ' 价格 ', `description` varchar(255) CHARACTER SET latin1 DEFAULT NULL COMMENT ' 商品描述 ' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) 在上述执行结果中,Table 表示查询的表名称,Create Table 表示创建该数据表的 SQL 语句 在 SQL 语句中, 包含了字段信息 COMMENT( 注释 ) ENGINE( 存储引擎 ) 以及 DEFAULT CHARSET( 字符集 ) 等内容 3. 查看数据表结构 MySQL 数据库中的 SHOW COLUMNS 语句也可以查看表结构, 基本语法格式如下 # 语法格式 1 SHOW [FULL] COLUMNS FROM 数据表名 [FROM 数据库名 ]; # 语法格式 2 SHOW [FULL] COLUMNS FROM 数据库名. 数据表名 ; 在上述语法格式中, 可选项 FULL 表示显示详细内容, 在不添加的情况下查询结果与 DESC 的结果相同 ; 在添加 FULL 选项时此语句不仅可以查看到 DESC 语句查看的信息, 还可以查看到字段的权限 COMMENT 字段的注释信息等 另外, 在 SQL 语句中可以通过 FROM 数据库名 或 数据库名. 数据表名 的方式查看任意数据库下的数据表结构信息 下面查看 my_goods 数据表结构的详细信息, 具体 SQL 语句如下 mysql> SHOW FULL COLUMNS FROM my_goods;

13 Field Type Collation Null Key id int(11) NULL YES name varchar(32) latin1_swedish_ci YES price int(11) NULL YES description varchar(255) latin1_swedish_ci YES Default Extra Privileges Comment NULL select,insert,update,references 编号 NULL select,insert,update,references 商品名 NULL select,insert,update,references 价格 NULL select,insert,update,references 商品描述 rows in set (0.00 sec) 从上述执行结果可以看出,SHOW FULL COLUMNS 语句除与 DESC 语句查询出的相 同字段外, 还包括 Collation( 校对集 ) 字段 Privileges( 权限 ) 字段和 Comment( 注释 ) 字段 修改表结构 在创建完数据表后, 除了可以修改数据表的名称及表选项外, 还可以利用 MySQL 提供的 ALTER TABLE 语法对字段名称 类型 位置等进行修改 增加或删除 下面分别讲解几种常用的使用方式 1. 修改字段名在 MySQL 中仅修改数据表中的字段名称, 使用 CHANGE 实现, 基本语法格式如下 ALTER TABLE 数据表名 CHANGE [COLUMN] 旧字段名新字段名字段类型 [ 字段属性 ]; 在上述语法中, 旧字段名 指的是字段修改前的名称, 新字段名 指的是字段修改后的名称 数据类型 表示新字段名的数据类型, 不能为空, 即使与旧字段的数据类型相同, 也必须重新设置 下面将 my_goods 数据表中名为 description 的字段修改为 des, 具体 SQL 语句如下 mysql> ALTER TABLE my_goods CHANGE description des VARCHAR(255); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 执行上述 SQL 语句后, 查看字段名的修改情况, 具体结果如下 mysql> DESC my_goods;

14 Field Type Null Key Default Extra id int(11) YES NULL name varchar(32) YES NULL price int(11) YES NULL des varchar(255) YES NULL rows in set (0.00 sec) 2. 修改字段类型在 MySQL 中仅修改数据表中的字段类型, 通常使用 MODIFY 实现, 基本语法格式如下 ALTER TABLE 数据表名 MODIFY [COLUMN] 字段名新类型 [ 字段属性 ]; 下面修改 my_goods 数据表中 des 字段的数据类型, 将 VARCHAR (255) 修改为 CHAR(255), 具体 SQL 语句如下 mysql> ALTER TABLE my_goods MODIFY des CHAR(255); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 执行上述 SQL 语句后, 查看字段类型的修改情况, 具体结果如下 mysql> DESC my_goods des; Field Type Null Key Default Extra des char(255) YES NULL row in set (0.00 sec) 3. 修改字段的位置数据表在创建时, 字段编写的先后顺序就是其在数据库中存储的顺序, 若需要调整某个字段的位置, 也可以使用 MODIFY 实现, 基本语法格式如下 ALTER TABLE 数据表名 MODIFY [COLUMN] 字段名 1 数据类型 [ 字段属性 ] [FIRST AFTER 字段名 2]; 从上述语法可知, 修改字段的位置就是在修改字段类型的后面添加 FIRST 或 AFTER 字段名 2 前者表示将 字段名 1 调整为数据表的第 1 个字段, 后者表示将 字段名 1 插入到 字段名 2 的后面 下面将 my_goods 表中最后一个字段 des 移动到 name 字段后, 具体 SQL 语句如下 mysql> ALTER TABLE my_goods MODIFY des VARCHAR(255) AFTER name; Query OK, 0 rows affected (0.03 sec)

15 Records: 0 Duplicates: 0 Warnings: 0 执行上述 SQL 语句后, 查看字段位置的修改结果, 具体结果如下 mysql> DESC my_goods; Field Type Null Key Default Extra id int(11) YES NULL name varchar(32) YES NULL des varchar(255) YES NULL price int(11) YES NULL rows in set (0.00 sec) 4. 新增字段对于已经创建好的数据表, 也可以根据业务需求利用 ADD 新增字段, 基本语法格式如下 # 语法格式 1: 新增一个字段, 并可指定其位置 ALTER TABLE 数据表名 ADD [COLUMN] 新字段名字段类型 [FIRST AFTER 字段名 ] # 语法格式 2: 同时新增多个字段 ALTER TABLE 数据表名 ADD [COLUMN] ( 新字段名 1 字段类型 1, 新字段名 2 字段类型 2,...) 在上述语法中, 在不指定位置的情况下, 新增的字段默认添加到表的最后 另外, 同时新增多个字段时不能指定字段的位置 下面在 my_goods 数据表中字段 name 后新增一个 num 字段, 表示商品的数量, 具体 SQL 语句如下 mysql> ALTER TABLE my_goods ADD num INT AFTER name; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 执行上述 SQL 语句后, 查看新增的字段, 具体结果如下 mysql> DESC my_goods; Field Type Null Key Default Extra id int(11) YES NULL name varchar(32) YES NULL num int(11) YES NULL des varchar(255) YES NULL

16 price int(11) YES NULL rows in set (0.00 sec) 5. 删除字段删除字段指的是将某个字段从数据表中删除,MySQL 中可以通过 DROP 完成 基本语法格式如下 ALTER TABLE 数据表名 DROP [COLUMN] 字段名 ; 下面以删除 my_goods 数据表中 num 字段为例演示, 具体 SQL 语句如下 mysql> ALTER TABLE my_goods DROP num; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 执行上述 SQL 语句后, 查看删除 num 字段后数据表中的字段, 具体结果如下 mysql> DESC my_goods; Field Type Null Key Default Extra id int(11) YES NULL name varchar(32) YES NULL des varchar(255) YES NULL price int(11) YES NULL rows in set (0.00 sec) 删除数据表 删除数据表操作指的是删除指定数据库中已经存在的表 另外, 在删除数据表的同时, 存储在数据表中的数据都将被删除, 基本语法格式如下 DROP [TEMPORARY] TABLE [IF EXISTS] 数据表 1 [, 数据表 2] ; 从上述语法可知, 删除数据表时, 可同时删除多个数据表, 多个数据表之间使用逗号分隔 可选项 IF EXISTS 用于在删除一个不存在的数据表时, 防止产生错误 下面以删除数据表 my_goods 为例进行演示, 具体 SQL 语句及执行结果如下 mysql> DROP TABLE IF EXISTS my_goods; Query OK, 0 rows affected (0.01 sec) 值得一提的是, 在开发时应谨慎使用数据表删除操作, 因为数据表一旦删除, 表中的所有数据都将被清除

17 2.3 数据操作 添加数据 通常情况下, 要想操作数据表中的数据, 首先要保证数据表中存在数据 MySQL 中使用 INSERT 语句向数据表中添加数据 根据操作的不同目的一般可以分为两种, 一种是为所有字段添加数据, 另外一种是为部分字段添加数据 下面将对这两种操作进行详细地讲解 1. 为所有字段添加数据在 MySQL 中, 为所有字段插入记录时, 可以省略字段名称, 严格按照数据表结构 ( 字段的位置 ) 插入对应的值, 基本语法格式如下 INSERT [INTO] 数据表名 {VALUES VALUE}( 值 1[, 值 2] ); 从上述语法可知, 关键字 INTO 是可选项,VALUES 和 VALUE 可以任选一种, 通常情况下使用 VALUES 值列表 值 1 [, 值 2] 中多个值之间使用逗号分隔 下面为 goods 表添加一条商品记录, 编号为 1, 商品名为 notebook, 售价是 4998 元, 描述信息为 High cost performance, 具体 SQL 语句如下 mysql> INSERT INTO goods -> VALUES (1, 'notebook', 4998, 'High cost performance'); Query OK, 1 row affected (0.00 sec) 在上述 SQL 语句中, 插入的数据顺序与创建数据表时对应的字段顺序相同, 分别表示商品编号 商品名 价格和商品描述 脚下留心在 MySQL 中, 若创建的数据表未指定字符集, 则数据表及表中的字段将使用默认的字符集 latin1 因此, 若用户插入的数据中含有中文, 则会出现错误提示 例如, 向 goods 表中输入含有中文的数据, 具体 SQL 语句及执行结果如下 mysql> INSERT INTO goods -> VALUES(2, ' 笔记本 ', 9998, ' 续航时间超过 10 个小时 '); ERROR 1366 (HY000): Incorrect string value: '\xb1\xca\xbc\xc7\xb1\xbe' for column 'name' at row 1 为了解决以上中文插入的问题, 通常在创建数据表的时候添加表选项, 设置数据表的字符集, 如下所所示 CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 表名 ( 字段名字段类型 [ 字段属性 ] ) [DEFAULT] {CHARACTER SET CHARSET} [=] utf8;

18 在上述语法中,CHARACTER SET 与 CHARSET 是同义词, 设置字符集时选取其一即可 其中,utf8 字符集支持世界上大多数国家的字符, 通常推荐使用此字符集 另外, 对于已经添加数据的数据表, 则可以通过 ALTER TABLE CHANGE/MODIFY 完成对表字段字符集的设置, 在使用时需要注意它们语法的不同 下面以修改 goods 表中 name 和 description 字段的字符集为例进行演示 具体 SQL 语句及执行结果如下 mysql> ALTER TABLE goods -> MODIFY name VARCHAR(32) CHARACTER SET utf8, -> MODIFY description VARCHAR(255) CHARACTER SET utf8; Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 上述 SQL 语句中, 在同时修改多个字段时, 使用逗号 (,) 分隔 修改完成后, 可以再次向 goods 表中插入以上含有中文的数据, 可以看到 Query OK 成功插入的提示 2. 为部分字段添加数据除了为数据表中所有字段添加数据外, 还可以通过指定字段名的方式增加数据 其中指定的字段名可以是数据表中全部的字段, 也可以是部分的字段 基本语法格式如下 INSERT [INTO] 数据表名 ( 字段名 1 [, 字段名 2] ) {VALUES VALUE} ( 值 1[, 值 2] ); 在上述语法中, ( 字段名 1 [, 字段名 2] ) 字段列表中, 多个字段名之间使用逗号分隔, 且字段名的编写顺序可与表结构 ( 字段位置 ) 不同, 只需保证值列表 ( 值 1[, 值 2] ) 中的数据与其相对应即可 下面将编号为 3 的 Mobile phone 插入到 goods 数据表中 具体 SQL 语句及执行结果如下 mysql> INSERT INTO goods (id, name) VALUES (3, 'Mobile phone'); Query OK, 1 row affected (0.00 sec) 上述 SQL 语句中, 字段的名称在使用时不需要使用引号包裹 另外, 未添加数据的字段系统会自动为该字段添加默认值 NULL( 空的 ) 除此之外,MySQL 中还提供了另外一种使用 INSERT 语句为指定字段添加数据的方式 基本语法格式如下 INSERT [INTO] 数据表名 SET 字段名 1 = 值 1 [, 字段名 2 = 值 2] ; 在上述语法中, 字段名 1 字段名 2 表示待添加数据的字段名称, 值 1 值 2 表示添加的数据 若在 SET 关键字后, 为表中多个字段添加数据, 每对 字段名 = 值 之间使用逗号 (,) 分隔即可 例如, 使用 INSERT SET 语法实现上述示例的功能, 具体 SQL 语句如下 INSERT INTO goods SET id = 3, name = 'Mobile phone'; 需要注意的是, 最后一个字段赋值后不需要添加逗号 3. 一次添加多行数据

19 在实际开发时, 向一张数据表中同时插入多条记录时, 重复的书写以上的 INSERT 指令, 操作不仅烦琐, 又不便于阅读 因此, 可以使用 MySQL 提供的另外一种插入数据的语法, 完成多数据插入 基本语法格式如下 INSERT [INTO] 数据表名 [( 字段列表 )] {VALUES VALUE} ( 值列表 ) [, ( 值列表 )] ; 在上述语法中, 多个 值列表 之间使用逗号 (,) 分割 其中, 字段列表 在省略时, 插入的数据需严格按照数据表创建的顺序插入, 否则 值列表 插入的数据仅需与字段列表中的字段相对应即可 例如, 将以上一次插入一条记录的操作修改成以下形式, 完成一次添加多行数据 mysql> INSERT INTO goods VALUES -> (1, 'notebook', 4998, 'High cost performance'), -> (2, ' 笔记本 ', 9998, ' 续航时间超过 10 个小时 '), -> (3, 'Mobile phone', NULL, NULL); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 需要注意的是, 在多数据插入时, 若一条数据插入失败, 则整个插入语句都会失败 查询数据 数据的查询操作是 MySQL 中最常用 也是最重要的功能之一 下面介绍 3 种最基本的数据查询方式, 其他更复杂的操作会在本书的其他章节中详细地讲解 1. 查询表中全部数据查询数据表中所有字段的数据, 可以使用星号 * 通配符代替数据表中的所有字段名, 基本语法格式如下 SELECT * FROM 数据表名 ; 下面利用以上语法查看 goods 表中插入的全部数据 具体 SQL 语句如下 mysql> SELECT * FROM goods; id name price description notebook 4998 High cost performance 2 笔记本 9998 续航时间超过 10 个小时 3 Mobile phone NULL NULL rows in set (0.00 sec) 2. 查询表中部分字段

20 查询数据时, 可在 SELECT 语句的字段列表中指定要查询的字段 基本语法格式如下 SELECT { 字段名 1, 字段名 2, 字段名 3, } FROM 数据表名 ; 上述语法中, 字段列表 字段名 1, 字段名 2, 字段名 3, 中, 若列出数据表中所有的字段名, 则表示查询表中全部数据 下面仅查看 goods 表中 id 和 name 字段, 具体 SQL 语句及执行结果如下 mysql> SELECT id, name FROM goods; id name notebook 2 笔记本 3 Mobile phone rows in set (0.00 sec) 3. 简单条件查询数据在查询数据时, 若想要查询出符合条件的相关数据记录时, 可以使用 WHERE 实现 基本语法格式如下 SELECT * { 字段名 1, 字段名 2, 字段名 3, } FROM 数据表名 WHERE 字段名 = 值 ; 上述语法表示获取 字段名 等于指定 值 的数据记录, 数据的内容中可以是表的部分字段或全部字段 下面获取 goods 表中 id 等于 1 的全部商品信息 具体 SQL 语句及执行结果如下 mysql> SELECT * FROM goods WHERE id = 1; id name price description notebook 4998 High cost performance rows in set (0.00 sec) 修改数据 修改数据是数据库中常见的操作, 通常用于对表中的部分记录进行修改 例如, 商品在做活动时, 需要在原价的基础上打折, 此时就需要对商品价格的数据进行修改 MySQL 提供了 UPDATE 语句修改数据 基本语法格式如下 UPDATE 数据表名

21 SET 字段名 1 = 值 1 [, 字段名 2 = 值 2, ] [WHERE 条件表达式 ] 上述语法中, 若实际使用时没有添加 WHERE 条件, 那么表中所有对应的字段都会被修改成统一的值, 因此读者在修改数据时, 请谨慎操作 下面将 goods 表中编号为 2 的商品价格由 9998 元修改为 5899 元 具体 SQL 语句及执行结果如下 mysql> UPDATE goods SET price = 5899 WHERE id = 2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM goods WHERE id = 2; id name price description 笔记本 5899 续航时间超过 10 个小时 rows in set (0.00 sec) 执行完上述 SQL 语句后, 使用 SELECT 查看编号为 2 的商品价格修改情况 删除数据 删除数据是指对表中存在的记录进行删除 例如, 商品停产后, 可以删除商品表中的相关数据 MySQL 中使用 DELETE 语句删除表中的记录, 基本语法格式如下 DELETE FROM 数据表名 [WHERE 条件表达式 ]; 在上述语法中, 数据表名 指定要执行删除操作的表,WHERE 条件为可选参数, 用于设置删除的条件, 满足条件的记录会被删除 下面删除 goods 表中编号等于 3 的商品数据 具体 SQL 语句如下 mysql> DELETE FROM goods WHERE id = 3; Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM goods; id name price description notebook 4998 High cost performance rows in set (0.00 sec)

22 执行上述 SQL 语句后, 使用 SELECT 查询 goods 表中记录的变化 需要注意的是, 在删除数据时若未指定 WHERE 条件, 系统就会自动删除该表中所有的记录, 因此读者在 操作时需要慎重 动手实践 : 电子杂志订阅表的操作 数据库的学习在于多看 多学 多想 多动手, 只有将理论与实际相结合, 才能够体现出数据开发与管理的重要性, 展现知识学习的价值与力量 接下来请结合本章所学的知识完成电子杂志订阅表的操作 实践目标 此实践的目标就是能够根据文字提示, 完成对应数据表的创建, 并可以对数据表中的数据进行简单的增 删 改 查操作 实践需求 (1) 在 mydb 数据库中创建一张电子杂志订阅表 (subscribe) (2) 电子杂志订阅表中要包含 4 个字段, 分别为编号 (id) 订阅邮件的邮箱地址 ( ) 用户是否确认订阅(status, 使用数字表示,1 表示已确认,0 表示未确 认 ) 邮箱确认的验证码(code) (3) 为电子杂志订阅表添加 5 条测试数据, 如表 2-2 所示 表 2-2 测试数据信息 编号 邮箱地址 是否确认的状态 邮箱确认验证码 1 tom123@163.com 1 TRBXPO 2 lucy123@163.com 1 LOICPE 3 lily123@163.com 0 JIXDAMI 4 jimmy123@163.com 0 QKOLPH 5 joy123@163.com 1 JSMWNL (4) 查看已经通过邮箱确认的电子杂志订阅信息 (5) 将编号等于 4 的订阅确认状态设置为 已确认 (6) 删除编号等于 5 的电子杂志订阅信息 动手实践 1. 选择数据库当 MySQL 服务器中不存在 mydb 数据库时, 创建此数据库, 否则忽略 在 mydb 数据库已经存在后, 选择 mydb 数据库 具体 SQL 语句如下 mysql> CREATE DATABASE IF NOT EXISTS mydb;

23 Query OK, 1 row affected, 1 warning (0.00 sec) mysql> USE mydb; Database changed 2. 创建电子杂志订阅表根据 实践需求 第 (2) 条需求, 创建电子杂志订阅表, 具体 SQL 语句及执行结果如下 mysql> CREATE TABLE subscribe ( -> id INT COMMENT ' 编号 ', -> VARCHAR(60) COMMENT ' 邮件订阅的邮箱地址 ', -> status INT COMMENT ' 是否确认,0 未确认,1 已确认 ', -> code VARCHAR(10) COMMENT ' 邮箱确认的验证码 ' -> ) DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.01 sec) 3. 操作电子杂志订阅表的数据 (1) 添加数据根据 实践需求 第 (3) 条给出的信息完成电子杂志订阅数据的新增, 具体 SQL 语句及执行结果如下 mysql> INSERT INTO subscribe VALUES -> (1, 'tom123@163.com', 1, 'TRBXPO'), -> (2, 'lucy123@163.com', 1, 'LOICPE'), -> (3, 'lily123@163.com', 0, 'JIXDAMI'), -> (4, 'jimmy123@163.com', 0, 'QKOLPH'), -> (5, 'joy123@163.com', 1, 'JSMWNL'); Query OK, 5 row affected (0.00 sec) mysql> SELECT * FROM subscribe; id status code tom123@163.com 1 TRBXPO 2 lucy123@163.com 1 LOICPE 3 lily123@163.com 0 JIXDAMI 4 jimmy123@163.com 0 QKOLPH 5 joy123@163.com 1 JSMWNL rows in set (0.00 sec) 执行上述 SQL 语句后, 使用 SELECT 查询表中的所有数据 (2) 查询数据

24 查看已经通过邮箱确认的电子杂志订阅信息 具体 SQL 语句及执行结果如下 mysql> SELECT * FROM subscribe WHERE status = 1; id status code tom123@163.com 1 TRBXPO 2 lucy123@163.com 1 LOICPE 5 joy123@163.com 1 JSMWNL rows in set (0.00 sec) 以上操作成功查询出了已经确认的邮箱 (3) 修改数据将编号等于 4 的确认状态设置为已确认 具体 SQL 语句及执行结果如下 mysql> UPDATE subscribe SET status = 1 WHERE id = 4; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM subscribe WHERE id = 4; id status code jimmy123@163.com 1 QKOLPH row in set (0.00 sec) 执行上述 SQL 语句后, 使用 SELECT 可查询出表中 id 等于 4 的记录 status 已修改为 1 (4) 删除数据删除编号等于 5 的电子杂志订阅信息, 具体 SQL 语句及执行结果如下 mysql> DELETE FROM subscribe WHERE id = 5; Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM subscribe; id status code tom123@163.com 1 TRBXPO 2 lucy123@163.com 1 LOICPE 3 lily123@163.com 0 JIXDAMI 4 jimmy123@163.com 1 QKOLPH rows in set (0.00 sec)

25 了 从上述结果可以看出,SELECT 查询结果中,id 为 5 的记录不存在, 说明已经被删除 本章小结 本章主要讲解了数据库的创建 查看 选择与删除, 数据表和数据的基础操作 通过 本章的学习希望读者能够掌握 MySQL 的数据库 数据表以及数据的基本操作, 为以后的 学习和开发奠定夯实的基础 课后练习 一 填空题 1. 添加 可在创建的数据库已存在时防止程序报错 2. MySQL 提供的 可查看指定数据库的创建信息 3. 和 可在 MySQL 中添加注释内容, 且在服务器运行时会被忽略 4. 语句 可同时修改多个数据表名 5. 查询数据时, 通配符 可表示数据表中的所有字段 二 判断题 1. 临时表仅在当前会话可见, 会话关闭时会自动删除 ( ) 2. 仅修改数据表中的字段名称时, 通常使用 ALTER TABLE...MODIFY 实现 ( ) 3. 修改数据时若未带 WHERE 条件, 则表中对应字段都会被改为统一的值 ( ) 4. 数据库目录中的 db.opt 用于保存该数据库下的所有数据表信息 ( ) 5. 插入数据前必须使用 USE 选择操作的数据库 ( ) 三 选择题 1. 下列选项中,( ) 语句可查看数据表的创建时间 A.SHOW TABLES B.DESC 数据表名 C.SHOW TABLE STATUS D.SHOW CREATE TABLE 数据表名 2. 若数据库中存在以下数据表, 语句 SHOW TABLES LIKE 'sh_' 的结果为 ( ) A.fish B.mydb C.she D. unshift 3. 下面语法不能实现新增数据的是 ( ) A.INSERT 表名 VALUE( 值列表 ) B.INSERT INTO 表名 VALUE( 值列表 ) C.INSERT INTO 表名 VALUES( 值列表 ) D.INSERT INTO 表名 ( 值列表 )

26 4. 语句 ( ) 可以删除数据表中指定条件的数据 A.DELETE B. DROP C.ALTER TABLE D. 以上答案全部正确 5. 语句 ALTER TABLE...MODIFY 添加 ( ) 可将字段调整为数据表的第 1 个字段 A.FIRST 字段名 B. FIRST C.AFTER 字段名 D. AFTER 四 实训题 1. 按下列表结构, 利用 SQL 语句在 mydb 数据库中创建 topic 表 字段名 数据类型 备注 id INT 专题编号 title VARCHAR(255) 专题名称 intro VARCHAR(255) 专题介绍 start_time INT 专题开始时间 end_time INT 专题结束时间 2. 为 mydb.goods 表新增 total( 库存量 ) 和 add_time( 发布时间 ) 字段