mysql 动态创建表、添加数据

最新推荐文章于 2025-06-27 10:11:28 发布

原创最新推荐文章于 2025-06-27 10:11:28 发布 · 4.8k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #数据 #insert #create

数据库专栏收录该内容

17 篇文章

订阅专栏

本文介绍了如何在MySQL数据库中动态创建表并利用INSERT INTO...SELECT语句填充数据。讨论了查询字段与插入字段的对应关系，以及使用SELECT与CREATE TABLE结合时的限制和注意事项，包括不能使用ORDER BY，FROM子句不能包含目标表，以及字段类型和索引的自动选择。同时提出了优化建议，强调在SELECT查询中为字段设置别名的重要性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在数据库test中创建数据表

CREATE TABLE salestotal
    (
    user_id int(8) not null,
    name varchar(50),
    englishname varchar(50),
    totalsales decimal(5,2),
    PRIMARY KEY (user_id)
);

然后可以使用嵌套INSERT INTO… SELECT 在新表中插入数据:

INSERT INTO salestotal(user_id,name,englishname,totalsales)
    SELECT user.user_id,name,englishname,SUM(price)
    FROM user INNER JOIN items ON user.user_id = items.user_id
    GROUP BY items.user_id;

SELECT 的查询字段对应INSERT INTO的相应字段，并且 VALUES不能和 INSERT语句一同使用。

查询创建的表结构

SHOW COLUMNS FROM salestotal;
作用同上
DESC salestotal;

表结构如下：

这里写图片描述

限制：

查询不能包含ORDER BY
FROM子句不能包含INSERT INTO的目标表

SELECT 和CREATE TABLE 同时使用

CREATE TABLE salestotals
    SELECT user.user_id,name,englishname,SUM(price)
    FROM user INNER JOIN items ON user.user_id = items.user_id
    GROUP BY items.user_id;

结果和之前的SQL运行结果相同，差异在于无法控制字段名称和类型定义，也无法控制索引。字段名称由SELECT 语句复制得到，类型由mysql选择（通常与来源字段相同），索引需另外创建。但是名称为SUM(price)的字段不易应用，需手动修改。

ALTER TABLE salestotals INDEX ids(user_id);
//为字段user_id创建名称为ids的索引
ALTER TABLE salestotals DROP INDEX ids; 或者DROP INDEX ids ON salestotals
//移除字段user_id的索引
ALTER TABLE salestotals DROP user_id;
//移除字段user_id
ALTER TABLE salestotals MODIFY user_id smallint;
//修改user_id类型为smallint
ALTER TABLE salestotals CHANGE user_id id smallint;
//将user_id改为id
ALTER TABLE salestotals MODIFY name varchar(30);
//修改字段长度
ALTER TABLE salestotals RENAME sales;
//修改表名称

当然也可以对该语句进行优化：

CREATE TABLE salestotals(
                        user_id int(8) not null,
                        name varchar(50),
                        englishname varchar(50),
                        totalsales decimal(5,2),
                        PRIMARY KEY (user_id)
                    )
    SELECT user.user_id,name,englishname,SUM(price) as totalsales
    FROM user INNER JOIN items ON user.user_id = items.user_id
    GROUP BY items.user_id;

注：
在SELECT查询过程中必须为字段设置别名，否则查询的字段与表结构不相同的字段会被默认为新的字段插入到表中。
使用现有的表结构创建表：

CREATE TABLE salesuntilyesterday LIKE salestotal;