SQL Basics: Viewing and modifying data tables

1. View the table's basic structure

abbreviated DESC table name;

Null: null value is available for storage

Key: Whether the column is indexed. The PRI indicates that the column is part of the primary key of the table; Uni indicates that the column is part of a unique index; Mul indicates that a given value in a column allows multiple occurrences

Default: Whether the column has a defaults value, how many

Extra: Gets the attachment information about the given column, such as: property value self-increment

2. View table Detail Structure

-------------------------------------------------

1. Modify the table name

ALTER TABLE (old table name) rename to (new table name);

To is optional, use or not affect the result

2. Modifying the data type of a field

ALTER TABLE (table name) modify (field name) (data type);

The name in TB_DEPT1 is changed from varchar (22) to varchar (30)

3. Modify the field name

ALTER TABLE (table name) change (old field name) (new field name) (data type);

The data type here cannot be empty, even if it is not modified, before the data type is filled in

Change the Location field name to LOC and the data type remains unchanged

Change can also only modify the data type, implement and modify the same effect, just set the old field name and the new field name to the same name

Because different types of data are stored in the machine in different ways and lengths, modifying the data type may affect the data records that are already in the table, and it is best not to modify the data type when there is data in the table

4. Add a field

ALTER TABLE (table name) Add (new field name) (data type) (constraint) (first|after field name already exists);

(First|after already exists field name) to specify the position of the new field in the table, if not set, the new field defaults to the last column

4.1 Adding a field without integrity constraints

4.2 Adding a field with integrity constraints

4.3 Add a field to the first column of a table

4.4 Add a field after the specified column in the table

5. Delete a field

ALTER TABLE (table name) drop (field name);

6. Modify the position of the field

ALTER TABLE (table name) modify (field 1) (data type) first|after (field 2);

First refers to a field that is modified to be a table, after is inserted after the field 2

6.1 Modify fields as the first field of a table

6.2 After you modify a table field to a specified column

7. Change the storage engine for a table

ALTER TABLE (table name) engine= (changed storage engine name);

Modify the default InnoDB of the table to MyISAM

8. Delete a foreign KEY constraint for a table

Once the foreign key is deleted, the correlation between the master and slave tables will be lifted

ALTER TABLE (table name) Drop foreing key (foreign KEY constraint name);

SQL Basics: Viewing and modifying data tables