mysql - 在 mysql View 中插入给出错误的输出

Question

我是 mysql View 的新手，刚刚遇到这个问题我有3张 table

personal(name,age,village)
office(id,company,location)
entertainment(sport,team,music)

现在我使用以下语法创建一个 View

mysql> CREATE VIEW person AS
    ->  SELECT personal.name,personal.age,personal.village,office.id,office.comp
any,office.location,entertainment.sport,entertainment.team,entertainment.music
    -> FROM personal,office,entertainment;

接下来我插入人物 View

 INSERT INTO person(name,age,village) VALUES ('jay','40','Pune');
 INSERT INTO person(id,company,location) VALUES ('36234','AZcD','Mumbai');
 INSERT INTO person(sport,team,music) VALUES ('football','KKR','POP');

我正确地得到了输出。(我必须单独插入，否则它会给我一个错误)

 +------+------+---------+-------+---------+----------+----------+------+-------+

| name | age  | village | id    | company | location | sport    | team | music |

+------+------+---------+-------+---------+----------+----------+------+-------+

| jay  |   40 | Pune    | 36234 | AZcD    | Mumbai   | football | KKR  | POP   |

+------+------+---------+-------+---------+----------+----------+------+-------+

现在问题出现了，当我尝试使用不同的值再次插入相同的插入查询时，我得到以下输出

+-------+------+---------+-------+---------+----------+------------+------+-----
------+
| name  | age  | village | id    | company | location | sport      | team | musi
c     |
+-------+------+---------+-------+---------+----------+------------+------+-----
------+
| jay   |   40 | Pune    | 36234 | AZcD    | Mumbai   | football   | KKR  | POP
      |
| Rohit |   42 | Goa     | 36234 | AZcD    | Mumbai   | football   | KKR  | POP
      |
| jay   |   40 | Pune    | 86234 | YZcD    | Kolkata  | football   | KKR  | POP
      |
| Rohit |   42 | Goa     | 86234 | YZcD    | Kolkata  | football   | KKR  | POP
      |
| jay   |   40 | Pune    | 36234 | AZcD    | Mumbai   | basketball | CSK  | Boll
ywood |
| Rohit |   42 | Goa     | 36234 | AZcD    | Mumbai   | basketball | CSK  | Boll
ywood |
| jay   |   40 | Pune    | 86234 | YZcD    | Kolkata  | basketball | CSK  | Boll
ywood |
| Rohit |   42 | Goa     | 86234 | YZcD    | Kolkata  | basketball | CSK  | Boll
ywood |
+-------+------+---------+-------+---------+----------+------------+------+-----
------+

我得到了 7 条新记录，而不是 1 条。如果你仔细观察最后一条记录是正确的记录，我指的是我插入的记录。我做错了什么？经过上述查询后，表格是正确的:

mysql> select * from personal;
+-------+------+---------+
| name  | age  | village |
+-------+------+---------+
| jay   |   40 | Pune    |
| Rohit |   42 | Goa     |
+-------+------+---------+
2 rows in set (0.00 sec)

mysql> select * from office;
+-------+---------+----------+
| id    | company | location |
+-------+---------+----------+
| 36234 | AZcD    | Mumbai   |
| 86234 | YZcD    | Kolkata  |
+-------+---------+----------+
2 rows in set (0.00 sec)

mysql> select * from entertainment;
+------------+------+-----------+
| sport      | team | music     |
+------------+------+-----------+
| football   | KKR  | POP       |
| basketball | CSK  | Bollywood |
+------------+------+-----------+
2 rows in set (0.00 sec)

请帮忙。

Answer 1

我认为您的数据库存在概念问题。

表之间没有关系。它们包含所有不同的数据，不引用其他表。例如，我认为personal表应该包含一个office_id，它将是office表的外键(即引用)。

例如:

mysql> select * from personal;
+-------+------+---------+-----------+
| name  | age  | village | office_id |
+-------+------+---------+-----------+
| jay   |   40 | Pune    | 36234     |
| Rohit |   42 | Goa     | 86234     |
+-------+------+---------+-----------+

然后，您可以向其相应办公室的人员提出此请求:

SELECT personal.name,personal.age,personal.village,office.id,office.company,office.location
    FROM personal,office WHERE personal.office_id = office.id;

OR(使用真正的 JOIN):

SELECT personal.name,personal.age,personal.village,office.id,office.company,office.location
    FROM personal JOIN office ON personal.office_id = office.id;

您应该重新考虑您的数据库结构，然后通过在表之间添加联接来调整您的 View 。

要小心使用 View 的 insert 语句:您可能会出现奇怪的行为。我总是更喜欢将 insert 语句直接插入表中。