从两个或多个现有表中创建一个新表(MySQL) [英] Creating a new table from two or more existing tables (MySQL)

问题描述

是否可以通过两个或多个现有表在mySQL中创建一个表?

Is it possible to create a table in mySQL from two or more existing tables?

我可以这样创建一个表吗?

Can I create a table like so:

CREATE TABLE IF NOT EXISTS USERNAME ( AGE INT NOT NULL )
CREATE TABLE IF NOT EXISTS USERAGE ( NAME VARCHAR(100) NOT NULL )
CREATE TABLE IF NOT EXISTS USER LIKE USERNAME LIKE USERAGE;

我不能在参考手册:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }

结尾的(LIKE old_tbl_name)是否表示LIKE old_tbl_name出现1次或多次?

Does the trailing (LIKE old_tbl_name) mean 1 or more occurrences of LIKE old_tbl_name?

如果可能的话，同一个名称列或两个主要ID会怎样?

If it is possible, then what happens to same name columns, or two primary ids?

编辑:我试图使用现有表的架构来定义新表，不尝试使用现有表的内容填充新表. /p>

I am trying to use the schema of existing tables to define a new table, not trying to populate a new table with the contents of existing tables.

推荐答案

如何:

CREATE TABLE IF NOT EXISTS USER SELECT * FROM USERNAME, USERAGE WHERE FALSE;

您还可以在SELECT关键字之前在新表中指定所需的任何索引，或者像往常一样在SELECT右侧选择列重命名/选择一些子集:

You can furthermore specify any indexes you require in the new table prior to the SELECT keyword, or rename columns/select some subset as usual on the right of SELECT:

CREATE TABLE IF NOT EXISTS USER (PRIMARY KEY(UNAME)) SELECT NAME AS UNAME -- etc

如果要合并同名的所有列，只需在SELECT中使用NATURAL JOIN.

If you want to combine all columns of the same name, just use NATURAL JOINs in your SELECT.

有关更多信息，请参见 CREATE TABLE ... SELECT 信息.

See CREATE TABLE ... SELECT for more information.

这篇关于从两个或多个现有表中创建一个新表(MySQL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！