6. Modification, backup, and Batch Processing

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', '2017-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 |
+ ---------- + ------ + ------------ + ----------- + -------- +

3. 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 |
+ ---------- + ------ + ------------ + ----------- + -------- +

4. delete a table:
Mysql> drop table ***** (name of table 1), and *** name of table 2;
You can delete one or more tables with caution.

5. delete a database:
Mysql> drop database database name;
Use it with caution.

6. 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.

7. 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 result to a file:
Mysql <mytest. SQL> mytest. Out

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.