MySQL getting started (5) Multi-Table operations

Multi-Table operations

We are familiar with the basic operations of databases and database tables. Now let's take a look at how to operate multiple tables.

Multiple tables may exist in a database, which are associated with each other. We will continue to use the previous example. The preceding table contains basic information about an employee, such as name, gender, date of birth, and place of birth. Create another table to describe the articles published by employees, including the author's name, article title, and publication date.

1. view the content of mytable in the first table:
Mysql> select * from mytable;
+ ---------- + ------ + ------------ + ----------- +
| Name | sex | birth | birthaddr |
+ ---------- + ------ + ------------ + ----------- +
| Abccs | f | 1977-07-07 | china |
| Mary | f | 1978-12-12 | usa |
| Tom | m | 1970-09-02 | usa |
+ ---------- + ------ + ------------ + ----------- +

2. Create the second table title, including the author, article title, and publication date ):
Mysql> create table title (writer varchar (20) not null,
-> Title varchar (40) not null,
-> Senddate date );

Add a record to the table. The contents of the last table are 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 | maid |
+ -------- + ------- + ------------ +
5 rows in set (0.00sec)

3. Multi-Table query
Now we have two tables: mytable and title. Using these two tables, we can perform combined queries:
For example, we want to query the name, gender, and Article of the author abccs:
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 above example, because the author's name, gender, and Article record are in two different tables, you must use a combination for query. You must specify how records in a table match those in other tables.
Note: if the name of the writer column in the title of the second table is the same as that in the mytable Table), instead of the writer column, you must use mytable. name and title. name to indicate the difference.

Another example is used to query the author, place of birth, and date of birth of article a2:
Mysql> select title, writer, birthaddr, birth from mytable, title
-> Where mytable. name = title. writer and title = 'a2 ';
+ ------- + -------- + ----------- + ------------ +
| Title | writer | birthaddr | birth |
+ ------- + -------- + ----------- + ------------ +
| A2 | abccs | china | 1977-07-07 |
+ ------- + -------- + ----------- + ------------ +

Welcome to http://abccs.oso.com.cn to visit my home page and make comments, please keep the author's name and home address When referencing this article.