Multi-table operation and batch processing in MySQL detailed introduction

Multi-table operations

In a database, there may be multiple tables, all of which are interrelated. We continue to use the previous example. The table previously established contains some basic information about the employee, such as name, sex, date of birth, place of birth. We'll create a table that describes the articles published by the employee, including the author's name, the title of the article, and the date of publication.

1, view the contents of the first table mytable:

Mysql> select * FROM MyTable;

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

| name | sex | Birth | birthaddr |

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

| Abccs | f | 1977-07-07 | Our |

| Mary | f | 1978-12-12 | USA |

| Tom | m | 1970-09-02 | USA |

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

2, create a second table title (including author, article title, Publication date):

Mysql> CREATE TABLE title (writer varchar) NOT NULL,

-> title varchar NOT NULL,

-> senddate date);

Add a record to the table, and the final table reads as follows:

Mysql>

SELECT * from title;

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

| Writer | Title | Senddate |

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

| Abccs | A1 | 2000-01-23 |

| Mary | B1 | 1998-03-21 |

| Abccs | A2 | 2000-12-04 |

| Tom | C1 | 1992-05-16 |

| Tom | C2 | 1999-12-12 |

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

5 rows in Set (0.00SEC)

3. Multi-Table Query

Now we have two tables: MyTable and title. Using these two tables we can make a combination query: for example, we want to query the author Abccs's name, sex, article:

Mysql> SELECT Name,sex,title from Mytable,title

-> WHERE Name=writer and name=′abccs′;

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

| name | sex | Title |

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

| Abccs | f | A1 |

| Abccs | f | A2 |

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

In the example above, because the author's name, sex, and article are recorded in two different tables, you must use a combination to query. You must specify how records in one table match the records in other tables.

Note: If the writer column in the second table title is also named name (the same as the Name column in the MyTable table) rather than writer, it must be represented by Mytable.name and Title.name as a distinction.

        Note : More wonderful articles please pay attention to the triple programming Tutorial column.