Microsoft Word - MySQL-排版文件.doc

第 2 篇 核心应用 在了解 MySQL 的基本概念 基本应用之后 本篇将详细介绍 MySQL 的核心应用 包括数据表 视图 数据类型 数据运算符 函数 数据库查询 数据库索引 数据存储以及触发器等 通过本篇的学习 读 者将对使用 MySQL 数据库有更高的水平 第5章 第6章 第7章 第8章 第9章 第 10 章 第 11 章 第 12 章 数据表的基本操作 MySQL 视图 MySQL 的数据类型和运算符 MySQL 函数 MySQL 数据库查询语句详解 MySQL 数据库的数据与索引操作 存储过程与存储函数 使用 MySQL 触发器

第 5 章 数据表的基本操作 本章教学微视频 :20 个 41 分钟 学习指引 数据实际上存储在数据表中, 可见数据表是数据库中最重要 最基本的操作对象, 是数据存储的基本单位 本章将详细介绍数据表的基本操作, 主要包括创建数据表 查看数据表结构 修改数据表和删除数据表 重点导读 掌握创建数据表的方法 掌握查看数据表结构的方法 掌握修改数据表的方法 掌握数据表其他操作的方法 掌握删除数据表的方法 5.1 创建数据表 在创建数据库之后, 接下来就要在数据库中创建数据表 所谓创建数据表, 指的是在已经创建的数据库中建立新表 创建数据表的过程是规定数据列的属性的过程, 同时也是实施数据完整性 包括实体完整性 引用完整性和域完整性 ) 约束的过程 本节将介绍创建数据表的语法形式, 以及如何添加主键约束 外键约束 非空约束等 5.1.1 创建数据表的语法形式 数据表属于数据库, 在创建数据表之前应该使用语句 USE < 数据库名 > 指定操作在哪个数据库中进行 如果没有选择数据库, 直接创建数据表, 则系统会显示 No database selected 的错误 创建数据表的语句为 CREATE TABLE, 语法格式如下 : CREATE TABLE < 表名 >

第 5 章数据表的基本操作 字段名 1 数据类型 [ 列级别约束条件 ] [ 默认值 ], 字段名 2 数据类型 [ 列级别约束条件 ] [ 默认值 ], [ 表级别约束条件 ] 在使用 CREATE TABLE 创建表时必须指定以下信息 : 1) 要创建表的名称, 不区分大小写, 不能使用 SQL 语言中的关键字, 例如 DROP ALTER INSERT 等 2) 数据表中每一列 字段 ) 的名称和数据类型, 如果创建多列, 要用逗号隔开 5.1.2 创建数据表实例 下面以创建数据表 db_1 为例进行讲解 例 5-1 创建员工表 db_1, 结构如表 5-1 所示 表 5-1 员工表 db_1 字段名称数据类型备注 id INT11) 员工编号 name VARCHAR25) 员工名称 sex BOOLEAN 员工性别 salary FLOAT 员工工资 首先创建数据库, 然后选择数据库, CREATE DATABASE mytest; USE mytest; 开始创建数据表 db_1, CREATE TABLE db_1 id INT11), name VARCHAR25), sex BOOLEAN, salary FLOAT 语句执行结果如图 5-1 所示 图 5-1 创建数据表 077

MySQL 从入门到项目实践 超值版 ) 这里已经创建了一个名称为 db_1 的数据表, 使用 SHOW TABLES; 语句查看数据表是否创建成功, 执行结果如图 5-2 所示 图 5-2 查看数据表创建结果 可以看到, 数据表 db_1 创建成功, 数据库 mytest 中已经有了数据表 db_1 5.1.3 主键约束 主键又称主码, 是表中一列或多列的组合 主键约束 Primary Key Constraint) 要求主键列的数据唯一, 并且不允许为空 主键能够唯一标识表中的一条记录, 可以结合外键来定义不同数据表之间的关系, 并且可以加快数据库查询的速度 主键和记录之间的关系如同身份证号码和人之间的关系, 它们之间是一一对应的 主键分为两种类型, 即单字段主键和多字段主键 1. 单字段主键主键由一个字段组成, 设置主键的 SQL 语句格式分为两种情况 1) 在定义列的同时指定主键, 语法格式如下 : 字段名数据类型 PRIMARY KEY[ 默认值 ] 例 5-2 定义数据表 db_2, 其主键为 id CREATE TABLE db_2 id INT11) PRIMARY KEY, name VARCHAR25), sex BOOLEAN, salary FLOAT 2) 在定义完所有列之后指定主键, 语法格式如下 : [CONSTRAINT< 约束名 >] PRIMARY KEY [ 字段名 ] 例 5-3 定义数据表 db_3, 其主键为 id CREATE TABLE db_3 id INT11), 078

第 5 章数据表的基本操作 name sex salary PRIMARY VARCHAR25), BOOLEAN, FLOAT, KEYid) 上面两个例子执行后的结果是一样的, 都会在 id 字段上设置主键约束 2. 多字段主键主键由多个字段联合组成, 语法格式如下 : PRIMARY KEY[ 字段 1, 字段 2,, 字段 n] 例 5-4 定义数据表 db_4, 假设表中没有主键 id, 为了唯一确定一个员工, 可以把 name sex 联合起来作为主键 CREATE TABLE db_4 name VARCHAR25), sex BOOLEAN, salary FLOAT, PRIMARY KEYname,sex) 语句执行后便创建了一个名称为 db_4 的数据表,name 字段和 sex 字段组合在一起成为该数据表的多字段主键 5.1.4 外键约束 外键用来在两个表的数据之间建立连接, 它可以是一列或者多列 一个表可以有一个或者多个外键 外键对应的是参照完整性, 一个表的外键可以为空值, 若不为空值, 则每一个外键值必须等于另一个表中主键的某个值 外键首先是表中的一个字段, 它可以不是本表的主键, 但对应另外一个表的主键 外键的主要作用是保证数据引用的完整性, 在定义外键后不允许删除在另一个表中具有关联关系的行 外键还保证数据的一致性 完整性 例如部门表 tb_dept 的主键 id, 在员工表 db_5 中有一个键 deptid 与这个 id 关联 主表 父表 ): 对于两个具有关联关系的表而言, 相关联字段中主键所在的那个表即是主表 从表 子表 ): 对于两个具有关联关系的表而言, 相关联字段中外键所在的那个表即是从表 创建外键的语法格式如下 : [CONSTRAINT< 外键名 >]FOREIGN KEY 字段名 1[, 字段名 2, ] REFERENCES< 主表名 > 主键列 1[, 主键列 2, ] 外键名 为定义的外键约束的名称, 在一个表中不能有相同名称的外键 ; 字段名 表示子表需要添加外键约束的字段列 例 5-5 定义数据表 db_5, 并且在该表中创建外键约束 创建一个部门表 tb_dept1, 表结构如表 5-2 所示, CREATE TABLE tb_dept1 id INT11) PRIMARY KEY, name VARCHAR22) NOT NULL, location VARCHAR50) 079

MySQL 从入门到项目实践 超值版 ) 表 5-2 tb_dept1 表结构 字段名称 数据类型 备 注 id INT11) 部门编号 name VARCHAR22) 部门名称 location VARCHAR50) 部门位置 定义数据表 db_5, 让它的 deptid 字段作为外键关联到 tb_dept1 表的主键 id, CREATE TABLE db_5 id INT11) PRIMARY KEY, name VARCHAR25), deptid INT11), salary FLOAT, CONSTRAINT fk_emp_dept1 FOREIGN KEYdeptId) REFERENCES tb_dept1id) 以上语句成功执行后在 db_5 表上添加了名称为 fk_emp_dept1 的外键约束, 外键名称为 deptid, 其依赖 于 tb_dept1 表的主键 id 5.1.5 非空约束 非空约束 NOT NULL Constraint) 指字段的值不能为空, 对于使用了非空约束的字段, 如果用户在添加数据时没有指定值, 数据库系统会报错 非空约束的语法格式如下 : 字段名数据类型 NOT NULL 例 5-6 定义数据表 db_6, 指定员工的性别不能为空 CREATE TABLE db_6 id INT11) PRIMARY KEY, name VARCHAR25), sex BOOLEAN NOT NULL 执行后, 在 db_6 表中创建了一个 sex 字段, 其插入值不能为空 NOT NULL) 5.1.6 唯一性约束 唯一性约束 Unique Constraint) 要求某列唯一, 允许为空, 但只能出现一个空值 唯一性约束可以确保一列或者几列都不出现重复值 在定义完列之后指定唯一性约束, 语法格式如下 : 字段名数据类型 UNIQUE 例 5-7 定义数据表 db_7, 指定 name 字段唯一 CREATE TABLE db_7 id INT11) PRIMARY KEY, name VARCHAR22) UNIQUE, 080

第 5 章数据表的基本操作 sex age BOOLEAN NOT NULL, INT4) 5.1.7 默认约束 默认约束 Default Constraint) 指定某列的默认值 例如, 用户表中的北京人比较多, 就可以设置 city 字段的默认值为 北京 如果插入一条新的记录时没有为这个字段赋值, 那么系统会自动为这个字段赋值 北京 默认约束的语法格式如下 : 字段名数据类型 DEFAULT 默认值 例 5-8 定义数据表 db_7, 指定员工的城市默认值为 北京 CREATE TABLE db_7 id INT11) PRIMARY KEY, name VARCHAR25) NOT NULL, city VARCHAR20) DEFAULT ' 北京 ' 以上语句成功执行之后,db_7 表中的 city 字段拥有了一个默认值 ' 北京 ', 新插入的记录如果没有指定 city 的值, 则默认设置为 ' 北京 ' 5.1.8 自增属性 在 MySQL 数据库设计中会遇到需要系统自动生成字段的主键值的情况 例如用户表中需要 id 字段自增, 需要使用 AUTO_INCREMENT 关键字来实现 属性值自动增加的语法格式如下 : 字段名数据类型 AUTO_INCREMENT 例 5-9 定义数据表 db_8, 指定员工的编号自动增加 CREATE TABLE db_8 id INT11) PRIMARY KEY AUTO_INCREMENT, name VARCHAR25) NOT NULL, city VARCHAR20) 以上语句执行后会创建名称为 db_8 的数据表, 表中的 id 字段值在添加记录的时候会自动增加,id 字段值默认从 1 开始, 每次添加一条新记录, 该值自动加 1 5.2 查看数据表结构 在数据表创建完成之后即可查看表结构的定义, 以确认表的定义是否正确 本节将详细介绍查看数据表的方法 081

MySQL 从入门到项目实践 超值版 ) 5.2.1 查看数据表基本结构 使用 DESCRIBE/DESC 语句可以查看表字段信息, 包括字段名 字段数据类型 是否为主键 是否有默认值等, 其语法格式如下 : DESCRIBE< 表名 >; 或者简写为 : DESC < 表名 >; 例 5-10 分别使用 DESCRIBE 和 DESC 查看 db_2 表的结构 查看 db_2 表的结构, DESCRIBE db_2; 查看结果如图 5-3 所示 图 5-3 查看 db_2 表的结构图 5-3 中各个字段的含义分别如下 Null: 表示该列是否可以存储 Null 值 Key: 表示该列是否已编制索引 PRI 表示该列是表主键的一部分 ;UNI 表示该列是 UNIQUE 索引的一部分 ;MUL 表示在该列中某个给定值允许出现多次 Default: 表示该列是否有默认值, 如果有, 值是多少 Extra: 表示可以获取的与给定列有关的附加信息, 例如 AUTO_INCREMENT 等 5.2.2 查看数据表详细结构 SHOW CREATE TABLE 语句可以用来查看表的详细信息, 语法格式如下 : SHOW CREATE TABLE < 表名 \G>; 例 5-11 使用 SHOW CREATE TABLE 查看 db_1 表的详细信息 SHOW CREATE TABLE db_1\g 执行结果如图 5-4 所示 图 5-4 查看 db_1 表的详细信息 082

第 5 章数据表的基本操作 5.3 修改数据表 在数据库创建完成之后, 还可以根据实际工作的需要重新修改数据表的结构, 常用的修改表的操作有修改表名 修改字段数据类型或字段名 增加和删除字段 修改字段的排列位置 更改表的存储引擎 删除表的外键约束等 本节将对修改表的操作进行讲解 5.3.1 修改表名 MySQL 是通过 ALTER TABLE 语句来实现表名的修改的, 具体语法格式如下 : ALTER TABLE < 旧表名 >RENAME[TO]< 新表名 >; 其中,TO 为可选参数, 使用与否不影响结果 例 5-12 将数据表 db_1 改名为 tb_new 在执行修改表名操作之前使用 SHOW TABLES 查看数据库中的所有表 SHOW TABLES; 查询结果如图 5-5 所示 图 5-5 查看数据库中的所有表使用 ALTER TABLE 将 db_1 表改名为 tb_new, ALTER TABLE db_1 RENAME tb_new; 在语句执行之后检查 db_1 表是否改名成功 使用 SHOW TABLES 查看数据库中的表, 结果如图 5-6 所示 图 5-6 修改数据表的名称经过比较可以看到数据表列表中已经显示表名为 tb_new 5.3.2 修改字段数据类型 修改字段数据类型就是把字段的数据类型转换成另一种数据类型 在 MySQL 中修改字段数据类型的语法格式如下 : 083

MySQL 从入门到项目实践 超值版 ) ALTER TABLE < 表名 >MODIFY< 字段名 > < 数据类型 > 其中, 表名指要修改数据类型的字段所在表的名称, 字段名指需要修改的字段, 数据类型指修改后字段的新数据类型 例 5-13 将数据表 tb_new 中 name 字段的数据类型由 VARCHAR25) 修改成 VARCHAR28) 在执行修改表名的操作之前使用 DESC 查看 tb_new 表的结构, 结果如图 5-7 所示 图 5-7 查看 tb_new 表的结构可以看到现在 name 字段的数据类型为 VARCHAR25), 修改其数据类型, 输入以下 SQL 语句并执行 : ALTER TABLE tb_new MODIFY name VARCHAR28 再次使用 DESC 查看表, 结果如图 5-8 所示 图 5-8 修改数据类型为 VARCHAR28) 以上语句执行之后, 检查发现 tb_new 表中 name 字段的数据类型已经修改成 VARCHAR28), 修改成功 5.3.3 修改字段名 在 MySQL 中修改表字段名的语法格式如下 : ALTER TABLE < 表名 > CHANGE < 旧字段名 > < 新字段名 > < 新数据类型 >; 其中, 旧字段名 指修改前的字段名 ; 新字段名 指修改后的字段名 ; 新数据类型 指修改后的数据类型, 如果不需要修改字段的数据类型, 可以将新数据类型设置成与原来的一样, 但数据类型不能为空 例 5-14 将数据表 tb_new 中 name 字段的名称改为 newname ALTER TABLE tb_new CHANGE name newname VARCHAR28 使用 DESC 查看 tb_new 表, 发现字段名称已经修改成功, 结果如图 5-9 所示 084

第 5 章数据表的基本操作 图 5-9 修改数据表中字段的名称从结果可以看出,name 字段的名称已经修改为 newname 提示 : 由于不同类型的数据在计算机中存储的方式及长度并不相同, 修改数据类型可能会影响到数据表中已有的数据记录, 因此当数据库中已经有数据时不要轻易地修改数据类型 5.3.4 添加字段 添加字段的语法格式如下 : ALTER TABLE < 表名 > ADD < 新字段名 > < 数据类型 > [ 约束条件 ][FIRST AFTER 已存在字段名 ]; 其中, 新字段名为需要添加的字段名称 ;FIRST 为可选参数, 其作用是将新添加的字段设置为表的第一个字段 ;AFTER 为可选参数, 其作用是将新添加的字段添加到已存在字段名指定的字段后面 例 5-15 在数据表 tb_new 中添加一个字段 city ALTER TABLE tb_new ADD city VARCHAR20 使用 DESC 查看 tb_new 表, 发现在表的最后添加了一个名为 city 的字段, 结果如图 5-10 所示 图 5-10 添加字段从结果可以看出添加了一个字段 city, 在默认情况下, 该字段放在最后一列 读者可以在数据表的第一列添加一个字段 例 5-16 在数据表 tb_new 中添加一个 INT 类型的字段 newid ALTER TABLE tb_new ADD newid INT11) FIRST; 使用 DESC 查看 tb_new 表, 发现在表的第一列添加了一个名为 newid 的 INT11) 类型的字段, 结果如图 5-11 所示 085

MySQL 从入门到项目实践 超值版 ) 图 5-11 添加 INT 类型的字段 newid 另外, 读者还可以在表的指定列之后添加一个字段 例 5-17 在数据表 tb_new 中的 sex 列后添加一个 INT 类型的字段 ss ALTER TABLE tb_new ADD ss INT11) AFTER sex; 使用 DESC 查看 tb_new 表, 结果如图 5-12 所示 图 5-12 添加指定列字段从结果可以看出,tb_new 表中增加了一个名称为 ss 的字段, 其位置在指定的 sex 字段的后面, 添加字段成功 5.3.5 删除字段 删除字段是将数据表中的某个字段从表中移除, 其语法格式如下 : ALTER TABLE < 表名 > DROP < 字段名 >; 其中, 字段名 指需要从表中删除的字段的名称 例 5-18 删除数据表 tb_new 中的 ss 字段 ALTER TABLE tb_new DROP ss; 使用 DESC 查看 tb_new 表, 结果如图 5-13 所示 086

第 5 章数据表的基本操作 图 5-13 删除表字段 从结果可以看出,tb_new 表中已经不存在名称为 ss 的字段, 删除字段成功 5.4 数据表的其他操作 除了上述修改数据表的操作以外, 还有一些重要的操作, 包括修改字段排序 更改表的存储引擎和删除表的外键约束 5.4.1 修改字段排序 对于已经创建的数据表, 读者也可以根据实际工作的需要修改字段的排列顺序, 通常使用 ALTER TABLE 改变表中字段的排列顺序, 其语法格式如下 : ALTER TABLE < 表名 > MODIFY < 字段 1> < 数据类型 > FIRST AFTER < 字段 2>; 其中, 字段 1 指要修改位置的字段 ; 数据类型 指 字段 1 的数据类型 ; FIRST 为可选参数, 指将 字段 1 修改为表的第一个字段 ; AFTER 字段 2 指将 字段 1 插入到 字段 2 的后面 例 5-19 将数据表 tb_new 中的 id 字段修改为表的第一个字段 ALTER TABLE tb_new MODIFY id INT11) FIRST; 使用 DESC 查看 tb_new 表, 发现 id 字段已经被移至表的第一列, 结果如图 5-14 所示 图 5-14 修改字段的排列 087

MySQL 从入门到项目实践 超值版 ) 读者可以修改字段到列表的指定列之后 例 5-20 将数据表 tb_new 中的 newname 字段移动到 salary 字段的后面 ALTER TABLE tb_new MODIFY newname VARCHAR28) AFTER salary; 使用 DESC 查看 tb_new 表, 结果如图 5-15 所示 图 5-15 修改字段到指定位置 可以看到,tb_new 表中的 newname 字段已经被移至 salary 字段之后 5.4.2 更改表的存储引擎 更改表的存储引擎的语法格式如下 : ALTER TABLE < 表名 > ENGINE=< 更改后的存储引擎名 >; 例 5-21 将数据表 db_2 的存储引擎修改为 MyISAM 在修改存储引擎之前首先使用 SHOW CREATE TABLE 查看 db_2 表当前的存储引擎, 结果如图 5-16 所示 图 5-16 查看表的存储引擎从结果可以看出,db_2 表当前的存储引擎为 ENGINE=InnoDB, 接下来修改存储引擎类型, ALTER TABLE db_2 ENGINE=MyISAM; 使用 SHOW CREATE TABLE 再次查看 db_2 表的存储引擎, 发现 db_2 表的存储引擎已变为 MyISAM, 结果如图 5-17 所示 088

第 5 章数据表的基本操作 图 5-17 完成存储引擎的修改 5.4.3 删除表的外键约束 对于数据库中定义的外键, 如果不再需要, 可以将其删除 外键一旦删除, 就会解除主表和从表间的关联关系 MySQL 中删除外键的语法格式如下 : ALTER TABLE < 表名 > DROP FOREIGN KEY < 外键约束名 > 其中, 外键约束名 指在定义表时 CONSTRAINT 关键字后面的参数 例 5-22 删除数据表 db_5 中的外键约束 fk_emp_dept1 首先查看数据表 db_5 的结构, SHOW CREATE TABLE db_5\g 执行结果如图 5-18 所示 下面开始删除数据表 db_5 的外键 fk_emp_dept1, ALTER TABLE db_5 DROP FOREIGN KEY fk_emp_dept1; 执行完毕之后将删除 db_5 表的外键约束, 使用 SHOW CREATE TABLE 再次查看 db_5 表的结构, 结果如图 5-19 所示 图 5-18 查看数据表结构 图 5-19 删除表的外键约束 可以看到,db_5 表中已经不存在 FOREIGN KEY, 原有的名称为 fk_emp_dept1 的外键约束删除成功 5.5 删除数据表 对于不再需要的数据表, 可以将其从数据库中删除 本节将详细讲解数据库中数据表的删除方法 089

MySQL 从入门到项目实践 超值版 ) 5.5.1 删除没有被关联的表 在 MySQL 中使用 DROP TABLE 可以一次删除一个或多个没有被其他表关联的数据表, 语法格式如下 : DROP TABLE [IF EXISTS] 表 1, 表 2, 表 n; 其中, 表 n 指要删除的表的名称 用户可以同时删除多个表, 只需将要删除的表名依次写在后面, 相互之间用逗号隔开即可 例 5-23 删除数据表 db_2 DROP TABLE db_2; 在语句执行完毕之后, 使用 SHOW TABLES 查看当前数据库中所有的数据表, 查看结果如图 5-20 所示 图 5-20 删除数据表 从结果可以看出, 数据列表中已经不存在名称为 db_2 的数据表, 删除操作成功 5.5.2 删除被其他表关联的主表 在数据表之间存在外键关联的情况下, 如果直接删除父表, 结果会显示失败, 原因是直接删除将破坏表的参照完整性 如果必须删除, 可以先删除与它关联的子表, 再删除父表, 只是这样同时删除了两个表中的数据 在有些情况下可能要保留子表, 这时如果要单独删除父表, 只需将关联的表的外键约束条件取消, 然后就可以删除父表, 下面讲解这种方法 在数据库中创建两个关联表, 首先创建 tb_1 表, CREATE TABLE tb_1 id INT11) PRIMARY KEY, name VARCHAR22) 接下来创建 tb_2 表, CREATE TABLE tb_2 id INT11) PRIMARY KEY, name VARCHAR25), wwid INT11), CONSTRAINT fk_tb_dt FOREIGN KEY wwid) REFERENCES tb_1id) 使用 SHOW CREATE TABLE 命令查看 tb_2 表的外键约束, 090

第 5 章数据表的基本操作 SHOW CREATE TABLE tb_2\g 执行结果如图 5-21 所示 图 5-21 删除关联的主表 从结果可以看到, 在数据表 tb_2 上创建了一个名称为 fk_tb_dt 的外键约束 例 5-24 删除父表 tb_1 首先直接删除父表 tb_1, 输入删除语句如下 : DROP TABLE tb_1; 执行结果如图 5-22 所示 图 5-22 删除父表可以看到, 如前所述, 当存在外键约束时主表不能被直接删除 接下来解除关联子表 tb_2 的外键约束, ALTER TABLE tb_2 DROP FOREIGN KEY fk_tb_dt; 该语句成功执行后将取消 tb_1 和 tb_2 表之间的关联关系, 此时可以输入删除语句, 将原来的父表 tb_1 删除, DROP TABLE tb_1; 最后通过 SHOW TABLES 查看数据表列表, 结果如图 5-23 所示 图 5-23 解除关联子表 可以看到, 数据表列表中已经不存在名称为 tb_1 的表 091

MySQL 从入门到项目实践 超值版 ) 5.6 就业面试技巧与解析 面试官 : 带 AUTO_INCREMENT 约束的字段值都是从 1 开始的吗? 应聘者 : 在默认情况下,MySQL 中 AUTO_INCREMENT 的初始值是 1, 每新增一条记录, 字段值就自动加 1 在设置自增属性AUTO_INCREMENT) 的时候还可以指定第一条插入记录的自增字段的值, 这样新插入记录的自增字段值从初始开始递增 092