MySQL multi-Table query Implementation Analysis

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:
<Ccid_nobr>
<Table width = "400" border = "1" cellspacing = "0" cellpadding = "2"
Bordercolorlight = "black" bordercolordark = "# FFFFFF" align = "center">
<Tr>
<Td bgcolor = "e6e6e6" "font-size: 9pt">
<Pre> <ccid_code> 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:

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 writer column in the second table title is named name (the same as the name column in mytable) rather than write r, you must use mytable. name and title. name indicates 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 |
+ ------- + -------- + ----------- + ------------ +

Modification and backup, batch processing

Sometimes we need to modify and delete database tables and databases, which can be implemented as follows:

1. Add a column:

For example, in the mytable table in the previous example, adding a column to indicate whether the table is single or not:

Mysql> alter table mytable add column single char (1 );

2. Modify records

Modify the single record of abccs to "y ":

Mysql> update mytable set single = 'y' where name = 'abccs '; now let's see what happened:

Mysql> select * from mytable;
+ ---------- + ------ + ------------ + ----------- + -------- +
| Name | sex | birth | birthaddr | single |
+ ---------- + ------ + ------------ + ----------- + -------- +
| Abccs | f | 1977-07-07 | china | y |
| Mary | f | 1978-12-12 | usa | NULL |
| Tom | m | 1970-09-02 | usa | NULL |
+ ---------- + ------ + ------------ + ----------- + -------- +

3. Add records

We have already discussed how to add a record to repeat this record for ease of viewing:

Mysql> insert into mytable
-> Values ('abc', 'F', '1966-08-17', 'China', 'n ′);
Query OK, 1 row affected (0.05 sec)

Check it out:

Mysql> select * from mytable;
+ ---------- + ------ + ------------ + ----------- + -------- +
| Name | sex | birth | birthaddr | single |
+ ---------- + ------ + ------------ + ----------- + -------- +
| Abccs | f | 1977-07-07 | china | y |
| Mary | f | 1978-12-12 | usa | NULL |
| Tom | m | 1970-09-02 | usa | NULL |
| Abc | f | 1966-08-17 | china | n |
+ ---------- + ------ + ------------ + ----------- + -------- +

4. delete records

Run the following command to delete a record in the Table: mysql> delete from mytable where name = 'abc ′;

DELETE: DELETE a record from the table that meets the conditions given by where. The result is displayed as follows:

Mysql> select * from mytable;
+ ---------- + ------ + ------------ + ----------- + -------- +
| Name | sex | birth | birthaddr | single |
+ ---------- + ------ + ------------ + ----------- + -------- +
| Abccs | f | 1977-07-07 | china | y |
| Mary | f | 1978-12-12 | usa | NULL |
| Tom | m | 1970-09-02 | usa | NULL |
+ ---------- + ------ + ------------ + ----------- + -------- +

5. delete a table:

Mysql> drop table ***** (the name of table 1), and **** the name of table 2. You can delete one or more tables and use them with caution.

6. delete a database:

Mysql> drop database name; use it with caution.

7. Database Backup:

Return to DOS:

Mysql> quit

D: \ mysqlbin

Use the following command to back up the database abccs:

Mysqldump -- opt abccs> abccs. dbb

Abccs. dbb is the backup file of your database abccs.

8. Use MySQL in batches:

First, create a batch file mytest. SQL with the following content:

Use abccs;
Select * from mytable;
Select name, sex from mytable where name = 'abccs ′;

Run the following command in DOS: d: mysqlbin mysql <mytest. SQL

The execution result is displayed on the screen.

If you want to view the results, but there are many output results, you can use the following command: mysql <mytest. SQL | more

We can also output the results to a file: mysql <mytest. SQL> mytest. out