Multi-table operation and backup processing of MySQL database

mysql| Backup | data | database

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 |   The US |   |   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.

Another example is used to query the author, birthplace, and date of birth of the 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 |   1977-07-07 | +-------+--------+-----------+------------+
   
    

Modification and backup, batch processing

Sometimes we have to modify and delete database tables and databases, which can be implemented in the following ways:

1. Add one column:

Add a column in the MyTable table in the previous example to indicate whether single singles:

   
    
     
    Mysql> ALTER TABLE mytable add column single char (1);
   
    

2, modify the record

Modify the Abccs single record to "Y":

   
    
     
    mysql> Update mytable set Single=′y′where name=′abccs′;
   
    

Now let's see what's going on:

   
    
     
    Mysql> select * FROM MyTable; +----------+------+------------+-----------+--------+ |   Name   |  sex |    Birth   | birthaddr | single | +----------+------+------------+-----------+--------+ |   Abccs  |   F  | 1977-07-07 |   Our   |    Y   | |    Mary  |   F  | 1978-12-12 |    USA    |  NULL  | |    Tom   |   M  | 1970-09-02 |    USA    |  NULL  | +----------+------+------------+-----------+--------+
   
    

3, increase the record

I've already talked about how to add a record to make it easier to see and repeat with this:

   
    
     
    mysql> INSERT INTO MyTable-> values (′abc′,′f′,′1966-08-17′,′china′,′n′); Query OK, 1 row affected (0.05 sec)
   
    

Check this out:

   
    
     
    Mysql> SELECT * from mytable;+----------+------+------------+-----------+--------+ |    Name  |  sex |    Birth   | birthaddr | single | +----------+------+------------+-----------+--------+ |   Abccs  |   F  | 1977-07-07 |   Our   |    Y   | |    Mary  |   F  | 1978-12-12 |    USA    |   NULL | |     Tom  |   M  | 1970-09-02 |    USA    |   NULL | |     ABC  |   F  | 1966-08-17 |   Our   |    N   | +----------+------+------------+-----------+--------+
   
    

4, delete the record

Delete a record in the table with the following command:

   
    
     
    Mysql> Delete from mytable where name=′abc′;
   
    

Delete Deletes a record from the table that satisfies the condition given by the where.

Show the results again:

   
    
     
    Mysql> select * FROM MyTable; +----------+------+------------+-----------+--------+ |    Name  |  sex |    Birth   | birthaddr | single | +----------+------+------------+-----------+--------+ |   Abccs  |   F  | 1977-07-07 |   Our   |    Y   | |    Mary  |   F  | 1978-12-12 |    USA    |   NULL | |    Tom   |   M  | 1970-09-02 |    USA    |   NULL | +----------+------+------------+-----------+--------+
   
    

5, delete the table:

   
    
     
    mysql> DROP TABLE * * * * * * (table 1 name), * * * Table 2 name;
   
    

You can delete one or more tables and use them carefully.

6, the deletion of the database:

   
    
     
    mysql> drop database name;
   
    

Use caution.

7, the database backup:

Back to Dos:

   
    
     
    Mysql> Quitd:mysqlbin
   
    

Use the following command to back up the database Abccs:

   
    
     
    Mysqldump--opt ABCCS>ABCCS.DBB
   
    

ABCCS.DBB is the backup file for your database Abccs.

8. Use MySQL in batch processing mode:

First, create a batch file Mytest.sql, which reads as follows:

   
    
     
    Use Abccs; SELECT * FROM MyTable; Select Name,sex from mytable where name=′abccs′;
   
    

Run the following command under DOS:

   
    
     
    D:mysqlbin MySQL < Mytest.sql
   
    

The execution results are displayed on the screen.

If you want to see the results and have a lot of output, you can use this command:

   
    
     
    MySQL < Mytest.sql | More
   
    

We can also output the results to a file:

   
    
     
    MySQL < mytest.sql > mytest.out