Shulou(Shulou.com)06/01 Report--

1. Create two new data tables offices and employees in the test_tb2 database, as follows:

Mysql > DESC offices

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | officeCode | int (10) | NO | PRI | NULL |

| | city | varchar (50) | NO | | NULL |

| | address | varchar (50) | YES | | NULL |

| | country | varchar (50) | NO | | NULL |

| | postlCode | varchar (15) | YES | | NULL |

+-+ +

Mysql > DESC employees

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | employeeNumber | int (11) | NO | PRI | NULL | auto_increment |

| | lastName | varchar (50) | NO | | NULL |

| | firstName | varchar (50) | NO | | NULL |

| | mobile | varchar (25) | YES | UNI | NULL |

| | officeCode | int (10) | NO | | NULL |

| | jobTitle | varchar (50) | NO | | NULL |

| | birth | datetime | NO | | NULL |

| | note | varchar (255) | YES | | NULL |

| | sex | varchar (5) | YES | | NULL |

+-+ +

2. Modify the location of the field:

Mysql > ALTER TABLE employees MODIFY mobile VARCHAR (25) after officeCode

3. Modify the name of the field:

Mysql > ALTER TABLE employees CHANGE birth employee_birth DATETIME

4. Modify the data type and non-null constraint of the field.

Mysql > ALTER TABLE employees MODIFY sex VARCHAR (2) NOT NULL

5. Delete a field

Mysql > ALTER TABLE employees DROP note

6. Add field name

Mysql > ALTER TABLE employees ADD favoriate_activity VARCHAR

7. Delete the table

(1) mysql > DROP TABLE offices

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constrai

Nt fails

Directly delete the data table, there will be an error, because the foreign key constraint of the child table employees is associated with the parent table offices, to delete the parent table must first disassociate the relationship with the child table.

(2) undo the relationship between parent and child tables

Mysql > ALTER TABLE employees DROP FOREIGN KEY fk_emp_off (foreign key name)

(3) Delete again

Mysql > DROP TABLE offices

Query OK, 0 rows affected (0.01 sec)

(4) the table delete operation is to delete the definition of the table together with the data in the table, and there is no prompt for confirmation when performing the delete operation, so you should be cautious when performing the delete operation.

8. Summary: the basic operation of the fields of the table is to add (ADD), delete (DROP), change: modify (MODIFY) and change CHANGE)

CHANGE changes the name, data type, and comments of the field.

MODIFY cannot change the name of a field

So: (1) change both the column name and the type, using CHANGE

(2) modify only the type and use MODIFY

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.