我怎样才能在MySQL
把一个数据库表分成两个表列的基础上,我有十列的表:我怎样才能在MySQL
name, id, colg, schol, add, no, subject, marks, surname, lectures
我可以拆分该表分为两个独立的表没有任何像这样的数据丢失?
表中的一个:
name, id, colg, schol, add, no
表二:
subject, marks, surname, lectures
CREATE TABLE old_table (name VARCHAR(255), id BIGINT, colg VARCHAR(255), schol VARCHAR(255), addit VARCHAR(255), no VARCHAR(255), subject VARCHAR(255), marks VARCHAR(255), surname VARCHAR(255), lectures VARCHAR(255));
INSERT INTO old_table VALUES("shaun", 1234, "DePaul University", "Computing and Digital Media", "something", "something", "some subject", "A", "Husain","no thank you");
mysql> SELECT * FROM old_table;
+-------+------+-------------------+-----------------------------+-----------+-----------+--------------+-------+---------+--------------+
| name | id | colg | schol | addit | no | subject | marks | surname | lectures |
+-------+------+-------------------+-----------------------------+-----------+-----------+--------------+-------+---------+--------------+
| shaun | 1234 | DePaul University | Computing and Digital Media | something | something | some subject | A | Husain | no thank you |
+-------+------+-------------------+-----------------------------+-----------+-----------+--------------+-------+---------+--------------+
1 row in set (0.00 sec)
CREATE TABLE table1 (name VARCHAR(255), id BIGINT PRIMARY KEY, colg VARCHAR(255), schol VARCHAR(255), addit VARCHAR(255), no VARCHAR(255));
CREATE TABLE table2 (id BIGINT PRIMARY KEY, subject VARCHAR(255), marks VARCHAR(255), surname VARCHAR(255), lectures VARCHAR(255));
INSERT INTO table1 (name,id,colg,schol,addit,no) SELECT name,id,colg,schol,addit,no from old_table;
INSERT INTO table2 (id,subject,marks,surname,lectures) SELECT id,subject,marks,surname,lectures from old_table;
mysql> select * from table1;
+-------+------+-------------------+-----------------------------+-----------+-----------+
| name | id | colg | schol | addit | no |
+-------+------+-------------------+-----------------------------+-----------+-----------+
| shaun | 1234 | DePaul University | Computing and Digital Media | something | something |
+-------+------+-------------------+-----------------------------+-----------+-----------+
1 row in set (0.00 sec)
mysql> select * from table2;
+------+--------------+-------+---------+--------------+
| id | subject | marks | surname | lectures |
+------+--------------+-------+---------+--------------+
| 1234 | some subject | A | Husain | no thank you |
+------+--------------+-------+---------+--------------+
1 row in set (0.00 sec)
mysql> select * from table1 LEFT JOIN table2 on (table1.id=table2.id);
+-------+------+-------------------+-----------------------------+-----------+-----------+------+--------------+-------+---------+--------------+
| name | id | colg | schol | addit | no | id | subject | marks | surname | lectures |
+-------+------+-------------------+-----------------------------+-----------+-----------+------+--------------+-------+---------+--------------+
| shaun | 1234 | DePaul University | Computing and Digital Media | something | something | 1234 | some subject | A | Husain | no thank you |
+-------+------+-------------------+-----------------------------+-----------+-----------+------+--------------+-------+---------+--------------+
1 row in set (0.00 sec)
是的,你只需要创建表之间有一个一对一的关系。您可以通过在每个表上创建一个唯一的主键来实现此目的,其中第二个表的主键也是第一个表的主键的外键。
表#1
PK_ID (primary key)
name
id
colg
schol
add
no
表#2
PK_ID (primary key, foreign key to Table #1)
subject
marks
surname
lectures
所以步骤将是简单地:
- 与结构创建上述两个新表) 条
- 插入从现有的表中的所有记录到表#1
- 插入从现有表到表#2的所有记录,有一个连接表#1拿到外键
(请注意,您还可以使用其中一列,现有表的主键,ID例如,只要它是独一无二的。)
这是真的我相信OP可能正在寻找如何使用SELECT可能INSERT INTO? – shaunhusain
dbasename我的问题是如何根据字段名将主记录划分为两个表。之后,我可以为你主键和外键概念。 – chetan
是shaunhusain .......我想问如何从主表中插入两个表中的记录.. – chetan
为什么你会想这样做?如果你这样做了,你需要两张表之间的链接,所以表2还需要一个id列,它需要与表1相对应。 –
你应该解释你为什么要这样做,以及什么你希望通过将它分成两个表来实现?另外,这些表格是否应该相互关联? –
是的,你可以轻松地做到这一点。一般来说,这就是所谓的分区。在你的具体情况下,它被称为垂直分区。 @shaunhusain的答案展示了这个的基本机制。 – pbarney