MySQL getting started (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 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.