MySQL数据库

MySQL 表操作详解

---

1. 创建表（CREATE TABLE）

基本语法

CREATE TABLE tablename (
    FieldName1 DataType,
    FieldName2 DataType
);

从查询结果创建表

可以通过查询语句创建一个新表，新表的字段类型与查询结果中的字段类型相同。

示例：

CREATE TABLE LearnHindi
SELECT 
    english.tag, 
    english.Inenglish AS english, 
    hindi.Inhindi AS hindi
FROM 
    english, hindi
WHERE 
    english.tag = hindi.tag;

表大小限制

• 表的大小通常受文件系统限制，通常为 2TB。

自增主键（AUTO_INCREMENT）

• 使用 AUTO_INCREMENT 确保主键字段值的自动递增。
• 如果表被截断，可以通过以下方式重置计数器：

  ALTER TABLE tablename AUTO_INCREMENT = 1;
  

---

2. 复制表

复制表结构

CREATE TABLE `new1` LIKE `old1`;

复制表数据

将旧表的所有记录插入到新表中：

INSERT INTO `new1` SELECT * FROM `old1`;

---

3. 临时表（Temporary Tables）

创建临时表

临时表是只在当前会话有效的表，会在会话结束时自动删除。

示例：

1. 基于现有表创建临时表：

   CREATE TEMPORARY TABLE IF NOT EXISTS MyTempTable1 AS (SELECT * FROM MyTable1);
   

2. 创建带指定字段的临时表：

   CREATE TEMPORARY TABLE IF NOT EXISTS MyTempTable1 (id INT) AS (SELECT id FROM MyTable1);
   

注意事项

• 如果临时表的列名与源表字段名不匹配，MySQL 会为源表字段添加额外的列。 示例：

  CREATE TEMPORARY TABLE IF NOT EXISTS MyTempTable1(id1 INT) AS (SELECT id FROM MyTable1);
  SHOW FIELDS FROM MyTempTable1;
  

  结果中会显示两个字段：id1 和 id。
• 所有临时表会在创建它们的 MySQL 会话结束时被删除。

---

4. 修改表（ALTER TABLE）

增加列

ALTER TABLE awards ADD COLUMN AwardCode INT(2);

修改列

1. 修改字段名称和类型：

   ALTER TABLE awards CHANGE COLUMN AwardCode VARCHAR(2) NOT NULL;
   

2. 仅修改字段类型：

   ALTER TABLE awards MODIFY COLUMN AwardCode VARCHAR(2) NOT NULL;
   

删除列

ALTER TABLE awards DROP COLUMN AwardCode;

重新排序记录

按特定字段重新排序记录（仅支持部分存储引擎）：

ALTER TABLE awards ORDER BY id;

---

5. 重命名表（RENAME TABLE）

单个表重命名

RENAME TABLE `old_name` TO `new_name`;

多个表重命名

RENAME TABLE `old1` TO `new1`, `old2` TO `new2`;

使用 ALTER TABLE 重命名

ALTER TABLE `old` RENAME `new`;

注意：ALTER TABLE 每次只能重命名一个表，但它是重命名临时表的唯一方式。

---

6. 删除表（DROP TABLE）

删除单个表

DROP TABLE `awards`;

删除多个表

DROP TABLE `table1`, `table2`;

选项关键字

• 删除临时表：

  DROP TEMPORARY TABLE `table`;
  

• 如果表不存在且不想引发错误：

  DROP TABLE `table` IF EXISTS;
  

---

参考资料

• MySQL 表大小限制
• MySQL 临时表教程