青少年编程与数学 02-011 MySQL数据库应用 07课题、表的操作

课题摘要: 本文介绍了MySQL数据库中表的操作，包括创建、修改、删除表，以及插入、查询、更新和删除数据等基本操作。详细解释了CREATE TABLE、ALTER TABLE、DROP TABLE、TRUNCATE TABLE、RENAME TABLE等语句的使用方法和示例。同时，强调了在创建表时选择合适的数据类型的重要性，并提供了数值类型、字符串类型、日期和时间类型等常见数据类型的详细介绍。文章还提供了创建表的示例，展示了如何定义字段、数据类型和完整性约束条件。此外，还介绍了字段命名规则和选择合适数据类型的指导原则，帮助用户优化数据库性能和存储效率。

---

一、数据库表（Table）

在MySQL数据库中，针对表可以执行多种操作，这些操作允许你创建、修改、查询和删除数据。以下是一些常见的表操作：

1. CREATE TABLE：

   • 用于创建一个新的数据库表。
   • 示例：CREATE TABLE table_name (column1 datatype, column2 datatype, ...);

2. ALTER TABLE：

   • 用于更改现有的表结构，比如添加或删除列、修改列的类型等。
   • 示例：ALTER TABLE table_name ADD column_name datatype;
   • 示例：ALTER TABLE table_name DROP COLUMN column_name;

3. DROP TABLE：

   • 用于删除一个已有的表及其所有数据。
   • 示例：DROP TABLE table_name;

4. TRUNCATE TABLE：

   • 用于删除表中的所有数据行，但保留表结构。
   • 示例：TRUNCATE TABLE table_name;

5. RENAME TABLE：

   • 用于重命名一个已存在的表。
   • 示例：RENAME TABLE old_table_name TO new_table_name;

6. INSERT INTO：

   • 用于向表中插入新的数据行。
   • 示例：INSERT INTO table_name (column1, column2) VALUES (value1, value2);

7. SELECT：

   • 用于从表中查询数据。
   • 示例：SELECT column1, column2 FROM table_name WHERE condition;

8. UPDATE：

   • 用于更新表中已有的数据行。
   • 示例：UPDATE table_name SET column1 = value1 WHERE condition;

9. DELETE FROM：

   • 用于从表中删除数据行。
   • 示例：DELETE FROM table_name WHERE condition;

10. SHOW CREATE TABLE：

    • 显示创建表时使用的语句。
    • 示例：SHOW CREATE TABLE table_name;

11. DESCRIBE 或 DESC：

    • 显示表的结构信息。
    • 示例：DESCRIBE table_name; 或 DESC table_name;

12. EXPLAIN：

    • 用来分析SQL查询，显示MySQL如何执行查询计划。
    • 示例：EXPLAIN SELECT * FROM table_name;

以上只是MySQL中一些基本的表操作。根据具体的需求，还可能涉及到索引、视图、存储过程、触发器等更高级的操作。每个命令都有其特定的语法和使用场景，因此在实际应用时需要根据具体情况来选择合适的命令。

二、创建表

在MySQL数据库中，创建表通常使用CREATE TABLE语句，以下是具体的介绍：

语法格式

CREATE TABLE 表名 (字段1 数据类型 [完整性约束条件],字段2 数据类型 [完整性约束条件],...字段n 数据类型 [完整性约束条件]
) [表级完整性约束条件];

示例

假设我们要创建一个名为students的表，用于存储学生信息，包含学号、姓名、性别、年龄、入学日期等字段，具体SQL语句如下：

CREATE TABLE students (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL,gender ENUM('男', '女') NOT NULL,age INT CHECK (age BETWEEN 16 AND 30),enrollment_date DATE NOT NULL
);

• id字段是整型，设置为主键（PRIMARY KEY），并且开启自动增长（AUTO_INCREMENT），用于唯一标识每个学生。
• name字段是长度为50的可变长字符串类型（VARCHAR(50)），且不能为空（NOT NULL）。
• gender字段是枚举类型（ENUM），只能取“男”或“女”，且不能为空。
• age字段是整型，通过检查约束（CHECK）限制年龄在16到30岁之间。
• enrollment_date字段是日期类型（DATE），且不能为空，用于存储入学日期。

注意事项

• 数据类型选择：要根据字段实际存储的数据类型来选择合适的MySQL数据类型，如整型（INT、TINYINT等）、浮点型（FLOAT、DOUBLE等）、字符串类型（CHAR、VARCHAR等）、日期时间类型（DATE、DATETIME等）等。
• 完整性约束：
  • 主键约束（PRIMARY KEY）：用于唯一标识表中的每一行记录，一个表只能有一个主键，主键字段的值不能重复且不能为空。
  • 唯一约束（UNIQUE）：确保字段中的值唯一，但可以有多个唯一约束，且允许有空值。
  • 非空约束（NOT NULL）：限制字段的值不能为空。
  • 默认值约束（DEFAULT）：为字段指定一个默认值，当插入记录时未明确指定该字段的值，则自动填充默认值。
  • 外键约束（FOREIGN KEY）：用于建立两个表之间的关联关系，确保数据的参照完整性，但创建外键时需要先确保被引用表和字段已存在，且数据类型和约束条件等匹配。
  • 检查约束（CHECK）：用于限制字段的值必须满足某个条件，但MySQL早期版本对检查约束的支持有限，部分版本可能需要通过其他方式（如触发器等）来实现类似功能。
• 字符编码：在创建表时，还可以指定表的字符编码，如CHARACTER SET utf8mb4，以支持存储多种语言的字符，特别是对于存储中文等非英文字符的字段，合适的字符编码很重要。
• 存储引擎：可以指定表的存储引擎，如ENGINE=InnoDB，不同的存储引擎有不同的特点和适用场景，InnoDB支持事务、行级锁等特性，而MyISAM则不支持事务但读取速度相对较快等。

三、字段的命名规则

在MySQL中，字段命名需要遵循一定的规则和规范，以下是详细的介绍：

基本规则

• 字符组成：字段名可以由字母（A-Z、a-z）、数字（0-9）、下划线（_）等字符组成。不能包含空格、特殊符号（如!、@、#等）。
• 首字符限制：字段名的首字符不能是数字，必须是字母或下划线。例如，1stname是非法的字段名，而firstname或_1stname是合法的。
• 长度限制：MySQL对字段名的长度也有一定限制，通常最大长度为64个字符。过长的字段名会影响数据库的可读性和性能。

命名规范建议

• 见名知意：字段名应尽量简洁明了且具有描述性，能够直观地反映字段所存储的数据内容或含义。例如，存储学生姓名的字段命名为student_name，而不是sn或nm等模糊的缩写。
• 使用小写字母：建议使用小写字母来命名字段，这样可以避免在不同操作系统（如Linux对大小写敏感，Windows对大小写不敏感）下可能出现的大小写问题，保证数据库的兼容性和一致性。
• 避免使用保留字：MySQL有许多保留字，如SELECT、INSERT、UPDATE、DELETE、TABLE、COLUMN等，这些保留字在SQL语句中有特殊的语法意义，不能直接用作字段名。如果非要使用保留字作为字段名，需要用反引号（```）将其括起来，但这样做会增加SQL语句的复杂性和可读性，所以最好避免使用保留字。
• 统一命名风格：在同一个数据库项目中，应保持字段命名风格的一致性。常见的命名风格有：
  • 下划线分隔命名法：使用下划线将多个单词分隔开，如user_id、order_number等。这种命名风格在MySQL中比较常见，清晰易读。
  • 驼峰命名法：将多个单词组合在一起，从第二个单词开始每个单词的首字母大写，如userId、orderNumber等。这种命名风格在一些编程语言中较为常见，但在MySQL中使用时需要注意保持一致性，且在SQL语句中可能会与一些函数或保留字产生混淆。

示例

假设我们要创建一个存储员工信息的表，以下是一些符合命名规则和规范的字段名示例：

• employee_id：用于存储员工编号，采用下划线分隔命名法，清晰明了。
• first_name：存储员工名字，同样使用下划线分隔，易于理解。
• last_name：存储员工姓氏，命名风格与first_name保持一致。
• email_address：存储员工邮箱地址，详细描述了字段内容。
• hire_date：存储员工入职日期，简洁且具有描述性。

而以下是一些不符合命名规则或规范的字段名示例：

• 1st_name：首字符为数字，违反了基本规则。
• email@address：包含特殊符号@，不符合字符组成规则。
• EmployeeName：采用驼峰命名法，虽然在某些情况下可读性尚可，但在MySQL中使用时可能与其他命名风格不一致，建议谨慎使用。
• select：使用了MySQL的保留字，直接使用会导致语法错误，需用反引号括起来才能使用，如select，但最好避免使用保留字。

四、字段数据类型

MySQL提供了丰富多样的数据类型，用于满足不同场景下数据存储的需求，以下是一些常见的数据类型分类及详细介绍：

数值类型

• 整数类型
  • TINYINT：范围为-128到127（有符号）或0到255（无符号），占用1个字节。
  • SMALLINT：范围为-32768到32767（有符号）或0到65535（无符号），占用2个字节。
  • MEDIUMINT：范围为-8388608到8388607（有符号）或0到16777215（无符号），占用3个字节。
  • INT或INTEGER：范围为-2147483648到2147483647（有符号）或0到4294967295（无符号），占用4个字节。
  • BIGINT：范围为-9223372036854775808到9223372036854775807（有符号）或0到18446744073709551615（无符号），占用8个字节。适用于存储大整数值，如用户ID、订单号等。
• 定点数类型
  • DECIMAL(M,D)或NUMERIC(M,D)：用于存储精确的小数值，其中M表示数字的最大位数，D表示小数点后的位数。例如DECIMAL(5,2)可以存储像123.45这样的数值，适用于财务数据等对精度要求较高的场景。
• 浮点数类型
  • FLOAT(M,D)：单精度浮点数，M表示数字的最大位数，D表示小数点后的位数。其存储范围和精度相对有限，但占用空间较小。
  • DOUBLE(M,D)或DOUBLE PRECISION(M,D)：双精度浮点数，同样M和D含义与FLOAT相同，但具有更大的存储范围和更高的精度，适用于科学计算等领域。

字符串类型

• 定长字符串类型
  • CHAR(N)：固定长度的字符串，N表示字符的最大长度，范围为0到255。当存储的字符串长度不足N时，会用空格填充至N个字符。适用于存储长度固定的数据，如性别（用M或F表示）等。
• 变长字符串类型
  • VARCHAR(N)：可变长度的字符串，N表示最大字符数，范围为0到65535。实际占用的空间为字符串的实际长度加上1或2个字节（用于存储长度信息）。适用于存储长度不固定的数据，如姓名、地址等。
  • TINYTEXT：最大长度为255个字符的文本数据，适合存储较短的文本信息。
  • TEXT：最大长度为65535个字符的文本数据，可用于存储较长的文本，如文章内容、评论等。
  • MEDIUMTEXT：最大长度为16777215个字符的文本数据，适用于存储非常长的文本，如书籍内容等。
  • LONGTEXT：最大长度为4294967295个字符的文本数据，是MySQL中最大的文本类型，可用于存储超大文本数据。
• 二进制字符串类型
  • BINARY(N)：固定长度的二进制字符串，与CHAR(N)类似，但存储的是二进制数据。
  • VARBINARY(N)：可变长度的二进制字符串，与VARCHAR(N)类似，用于存储可变长度的二进制数据。
  • TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB：分别对应TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT，用于存储二进制大对象数据，如图片、音频、视频等文件内容。

日期和时间类型

• DATE：用于存储日期值，格式为YYYY-MM-DD，范围为1000-01-01到9999-12-31，适用于存储生日、入职日期等仅包含日期的数据。
• TIME：用于存储时间值，格式为HH:MM:SS，范围为-838:59:59到838:59:59，可以用于存储时间段、营业时间等。
• DATETIME：用于存储日期和时间的组合值，格式为YYYY-MM-DD HH:MM:SS，范围为1000-01-01 00:00:00到9999-12-31 23:59:59，是存储具体时间点（如订单创建时间、文章发布时间等）的常用类型。
• TIMESTAMP：也用于存储日期和时间，格式与DATETIME相同，但其范围为1970-01-01 00:00:01到2038-01-19 03:14:07（UTC时间），并且具有自动更新的特性（当表中的其他字段值发生变化时，TIMESTAMP字段可以自动更新为当前时间戳），适用于需要记录数据最后修改时间等场景。
• YEAR：用于存储年份值，可以是两位或四位数字，范围为1901到2155和0000，适用于存储毕业年份、产品生产年份等仅包含年份的数据。

其他类型

• 枚举类型（ENUM）：允许在创建表时定义一组固定的字符串值，字段的值必须是这个集合中的一个。例如ENUM('男','女')，适用于性别、状态等具有固定选项的字段。
• 集合类型（SET）：与ENUM类似，但可以同时选择多个值，其值是集合中值的组合。例如SET('篮球','足球','乒乓球')，可以存储用户喜欢的多个运动项目。
• 位字段类型（BIT）：用于存储位字段值，可以存储二进制数据，长度范围为1到64位，适用于存储一些特定的二进制标志或位掩码等数据。

在实际使用中，选择合适的数据类型对于优化数据库性能、节省存储空间以及确保数据的准确性和完整性都至关重要。需要根据字段所存储数据的特点、范围、精度要求等因素综合考虑来选择最恰当的数据类型。

五、选择合适的数据类型

在MySQL中创建表时，为字段选择合适的数据类型是一个关键步骤，这不仅影响数据的存储效率，还关系到查询性能和数据完整性。以下是一些选择数据类型的指导原则和建议：

1. 基于数据的性质选择

• 数值数据：
  • 整数：如果数据是整数且范围较小（如性别用0和1表示），可以选择TINYINT；如果范围较大（如用户ID、订单号等），则根据实际范围选择SMALLINT、MEDIUMINT、INT或BIGINT。
  • 小数：对于需要精确计算的小数（如财务数据），应使用DECIMAL类型，指定精度和小数位数，以避免浮点数计算的精度问题。对于科学计算等对精度要求不是特别严格但需要较大范围或更高效计算的场景，可以选择FLOAT或DOUBLE。
• 文本数据：
  • 短文本：如姓名、地址等较短的文本信息，使用VARCHAR类型，并根据实际最大长度设置合适的长度参数，避免浪费空间。
  • 长文本：对于文章内容、描述等较长的文本，根据长度选择TEXT、MEDIUMTEXT或LONGTEXT。注意，过长的文本可能会影响查询性能，应谨慎使用。
  • 固定长度文本：如果文本长度固定，如电话号码格式，可以使用CHAR类型，这样可以提高比较和排序的效率。
• 日期和时间数据：
  • 仅日期：如果只需要记录日期（如生日、入职日期等），使用DATE类型。
  • 仅时间：记录时间段、营业时间等仅与时间相关的信息时，使用TIME类型。
  • 日期和时间：对于需要同时记录日期和时间的场景（如订单创建时间、日志时间等），优先选择DATETIME类型。如果需要记录数据的创建或修改时间，并且希望自动更新时间戳，可以使用TIMESTAMP类型。
  • 年份：仅记录年份信息时，使用YEAR类型，节省空间且查询效率高。

2. 考虑数据的范围和精度

• 在选择数值类型时，要明确数据可能出现的最大值和最小值，确保所选类型能够覆盖这个范围。例如，对于一个存储年龄的字段，TINYINT通常就足够了（假设年龄范围在0到127岁之间）；而对于存储全球人口数量的字段，则需要BIGINT。
• 对于小数类型，要根据实际业务需求确定精度（即数值的总位数）和小数位数。比如，存储商品价格时，如果价格精确到分，且最大价格不超过99999.99元，可以选择DECIMAL(7,2)。

3. 基于性能和存储效率考虑

• 存储空间：选择数据类型时，应考虑其占用的存储空间。例如，TINYINT占用1个字节，而INT占用4个字节。在能够满足数据存储需求的前提下，选择占用空间较小的数据类型可以节省存储资源，提高存储效率。
• 查询性能：较小的数据类型通常在查询时速度更快。例如，INT类型的比较和排序操作通常比BIGINT类型更快。此外，对于频繁查询的字段，选择合适的数据类型可以提高查询性能。比如，使用ENUM类型存储具有固定选项的字段（如性别、状态等），在查询时可以更高效地进行比较和筛选操作。

4. 遵循业务规则和约束

• 数据完整性：根据业务需求，为字段选择合适的数据类型有助于维护数据的完整性。例如，使用ENUM类型可以限制字段的值只能是预定义的几个选项之一，从而避免非法数据的插入。
• 业务逻辑：考虑字段在业务逻辑中的用途。例如，对于需要进行数学运算的数值字段，应避免使用字符串类型存储，以防止在运算时出现类型转换错误或性能问题。

示例

假设我们要创建一个存储图书信息的表，包含图书ID、书名、作者、出版日期、价格等字段，字段数据类型的选择可以如下：

CREATE TABLE books (book_id INT PRIMARY KEY AUTO_INCREMENT,  -- 图书ID，使用INT类型，范围足够且可自动增长title VARCHAR(255) NOT NULL,  -- 书名，使用VARCHAR类型，长度设置为255，可存储较长的书名author VARCHAR(100) NOT NULL,  -- 作者，使用VARCHAR类型，长度100足够存储常见的作者名publish_date DATE NOT NULL,  -- 出版日期，使用DATE类型，仅需记录日期price DECIMAL(10,2) NOT NULL  -- 价格，使用DECIMAL类型，总位数10位，其中2位小数，可存储较大范围的价格且保证精度
);

通过以上步骤和原则，可以为MySQL表中的字段选择最合适的数据类型，从而优化数据库的性能和存储效率，同时确保数据的准确性和完整性。

六、表定义示例

以下是一个示例脚本，用于创建一个名为employees的表，该表用于存储员工信息，包括员工ID、姓名、性别、年龄、入职日期、部门ID和工资等字段：

CREATE TABLE employees (employee_id INT PRIMARY KEY AUTO_INCREMENT,  -- 员工ID，主键，自动增长name VARCHAR(100) NOT NULL,  -- 员工姓名，最大长度100，不能为空gender ENUM('男', '女') NOT NULL,  -- 员工性别，只能是“男”或“女”，不能为空age INT CHECK (age BETWEEN 18 AND 60),  -- 员工年龄，范围限制在18到60岁之间hire_date DATE NOT NULL,  -- 入职日期，不能为空department_id INT NOT NULL,  -- 部门ID，假设与部门表的部门ID关联，不能为空salary DECIMAL(10, 2) CHECK (salary > 0)  -- 工资，总位数10位，其中2位小数，且工资必须大于0
);

字段说明

• employee_id：员工的唯一标识，使用INT类型作为主键，并开启自动增长功能，这样在插入新员工记录时，MySQL会自动为employee_id生成一个唯一的值。
• name：员工的姓名，使用VARCHAR(100)类型，最大长度为100个字符，且不能为空（NOT NULL），确保每条员工记录都有姓名信息。
• gender：员工的性别，使用ENUM('男', '女')类型，这样性别字段的值只能是“男”或“女”，且不能为空，有助于保证数据的准确性和一致性。
• age：员工的年龄，使用INT类型，并通过检查约束（CHECK (age BETWEEN 18 AND 60)）限制年龄在18到60岁之间，确保年龄数据的合理性。
• hire_date：员工的入职日期，使用DATE类型，记录格式为YYYY-MM-DD，且不能为空，用于存储员工入职的具体日期。
• department_id：员工所属部门的ID，假设与另一个存储部门信息的表（如departments表）中的部门ID字段关联，使用INT类型，且不能为空，表示每个员工都必须属于某个部门。
• salary：员工的工资，使用DECIMAL(10, 2)类型，总位数为10位，其中2位小数，这样可以精确地存储工资金额，并且通过检查约束（CHECK (salary > 0)）确保工资大于0，避免出现不合理的工资数据。

这个表定义示例脚本涵盖了多种常见的数据类型和约束条件，可以根据实际的业务需求进行调整和扩展。例如，如果需要存储员工的联系方式，可以添加一个phone_number字段，类型为VARCHAR；如果需要记录员工的离职日期，可以添加一个leave_date DATE字段等。