Use the MySQL command to modify the table and related table Structures

In practice, we sometimes encounter deletion and modification of database tables and databases. The following articles provide solutions to this problem, that is, use the MySQL command to correctly modify some tables and modify the table structure. Use the MySQL command to modify the table and the table structure: 1. Add a column, as shown in my

In practice, we sometimes encounter deletion and modification of database tables and databases. The following articles provide solutions to this problem, that is, use the MySQL command to correctly modify some tables and modify the table structure. Use the MySQL command to modify the table and the table structure: 1. Add a column, as shown in my

In practice, we sometimes encounter deletion and modification of database tables and databases. The following articles provide solutions to this problem, that is, use the MySQL command to correctly modify some tables and modify the table structure.

Use the MySQL command to modify the table and its structure:

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

3. delete records

Run the following MySQL 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 MySQL 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 MySQL command:

 
 

  
  
MySQL < mytest.sql | more 
 
 

We can also output the result to a file:

 
 

  
  
MySQL < mytest.sql > mytest.out 
 
 

Add index 549830479

 
 

  
  
MySQL> alter table tablename change depno int (5) not null;
  
  
MySQL> alter table tablename add index name (field name 1 [, field name 2…]);
  
  
MySQL> alter table tablename add index emp_name (name );
 
 

Index with primary keyword 549830479

 
 

  
  
MySQL> alter table tablename add primary key(id); 
 
 

Add an index of 549830479 with unique restrictions

 
 

  
  
MySQL> alter table tablename add unique emp_name2(cardnumber); 
 
 

Delete An index 549830479

 
 

  
  
MySQL>alter table tablename drop index emp_name; 
 
 

Modify Table: 549830479

Add field: 549830479

 
 

  
  
MySQL> ALTER TABLE table_name ADD field_name field_type; 
 
 

Modify the original field name and type: 549830479

 
 

  
  
MySQL> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type; 
 
 

Delete field: 549830479

 
 

  
  
MySQL> ALTER TABLE table_name DROP field_name; 
 
 

PS; Modify Field attributes

 
 

  
  
ALTER TABLE tableName MODIFY cloumnName DATETIME; 
 
 

The above content is an introduction to table modification and table structure modification using the MySQL command. I hope you will get some benefits.