View data table structure

After the data table is created using the SQL statement, you can view the definition of the table structure to determine whether the definition of the table is correct.In MySQL, the DESCRIBE and SHOW CREATE TABLE statements can be used to view the table structure.

1.View table basic structure statement DESCRIBE

 

DESCRIBE/DESC statement can view the field information of the table, including: field name.Field data type, whether it is the primary key, and whether there is a default value.Grammar rules:

DESCRIBE table name;

The latter is abbreviated as:

DESC table name;

The meaning of each field is:

NULL: Indicates whether the column can store NULL pointers

Key: Indicates whether the column is indexed.PRI indicates that the column is part of the primary key of the table; UNI indicates that the column is part of the UNIQUE index; MUL indicates that a given value in the column is allowed to appear multiple times.

Default: Indicates whether the column has a default value.If so, how much.

Extra: Indicates additional information about a given column that can be obtained, such as AUTO_INCREMENT, etc.

 

 

2.View the detailed structure statement of the table SHOW CREATE TABLE

The SHOW CREATE TABLE statement is used to display the CREATE TABLE statement when creating a table.The syntax is as follows:

SHOW CREATE TABLE<tablenameG>;

Using the SHOW CREATE TABLE statement, you can not only view the detailed statement at the time of creation, but also view the storage engine and character encoding

PS:

If the "G" parameter is not good, the displayed results may be very confusing.After adding the parameter "G", the displayed results can be more intuitive and easy to observe.

 

 

Modify data table

1.Modify the table name

 

MySQL uses the ALTER TABLE statement to modify the table name.The specific grammar rules are as follows:

ALTER TABLE <old table name> RENAME TO <new table name>;

Where TO is an optional parameter, whether it is used or not will not affect the result.

Example:

Rename the data table department to dept

alter table department rename dept;

You can use DESC to view the structure of the two tables before and after the modification.Modifying the table name does not modify the table structure, so the table structure after the name modification is exactly the same as the table structure before the modification.

 

2.Modify the data type of the field

 

Modifying the data type of a field is to convert the data type of the field to another data type.The syntax rules for modifying field data types in MySQL are as follows:

ATTER TABLE <tablename>MODIFY <fieldname> <datatype>

Example:

Change the data type of the name field in the data table department2 from VARCHAR(22) to VARCHAR(30).

alter table department2 modify name varchar(30);

 

3.Modify the field name

 

The syntax rules for modifying field names in MySQL are as follows:

ALTER TABLE <table name> CHANGE <old field name> <new field name> <new data type>

Wherein, "old field name" refers to the field name before modification; "new field name" refers to the modified field name; "new data type" refers to the modified data type; if you do not need to modify the field You can set the new data type to be the same as the original data type, but the data type cannot be empty.

Example:

Rename the data table department field location to local, the data type remains unchanged, the SQL statement is as follows:

alter table department change location local varchar(50);

 

 

4.Add fields

Add field syntax is as follows:

ALTER TABLE <table name> ADD <new field name> <data type> [constraint] [FIRST | AFTER existing field name] ;

The name of the new field is the name of the field to be added; "FIRST" is an optional parameter, its role is to set the newly added field as the first field of the table; "AFTER" is an optional parameter, its role is to The newly added field is added after the specified "existing field name".If there are no two parameters "FIRST" and "AFTER" in the SQL statement, this field is placed in the last column of the data table by default.

A.Add fields without integrity constraints

Example: Add a field managerId (department manager ID) of INT type without adding integrity constraints to the data table department.The SQL statement is as follows:

ALTER TABLE department ADD managerId INT(10);

 

B.Add fields with integrity constraints

Example: Add a non-nullable VARCHAR(25) field column1 to the data table department, the SQL statement is as follows:

ALTER TABLE department ADD column1 VARCHAR(12) NOT NULL;

 

 

C.Add a field to the first column of the table

Example:
Add a field column2 of type INT to the data table department, the SQL statement is as follows

alter table department add column2 int(10) first;

 

D.Add a field after the specified column in the table

Example:

Add a field of bad INT type column3 after the column2 column in the data table department.

alter table department add column3 int(10) after column2;

 

 

5, delete field

Deleting a field is to delete a field in the data table from the table, the syntax format is:

ALTER TABLE <tablename> DROP <fieldname>

Example:

Delete the column2 field in the data table department

alter table department drop column2;

 

 

6.Modify the arrangement of fields

Syntax:

ALTER TABLE <tablename>MODIFY <field1> <datatype> FIRST|AFTER <field2>

"Field 1" value is the field of the position to be modified, "Data Type" value is the data type of "Field 1", "FIRST" is an optional parameter, which means that "Field 1" is modified as the first field of the table, " AFTER Field 2" refers to inserting "Field 1" after "Field 2".

A.Modify the field to the first field of the table

Example:

Modify the column1 field in the data table department to the first field of the table, SQL statement:

alter table department modify column1 varchar(12) first;

 

 

 

B, after modifying the field to the specified column of the table

Example:

Modify the column1 field in the data table department to the back of the managerId field of the table.The SQL statement is as follows:

alter table department modify column1 varchar(12) after managerId;

 

 

7.Modify the storage engine of the table

 

Storage engines are different technical implementations used when data in MySQL is stored in files or in memory.The main storage engines in MySQL are: MyISAM, InnoDB, MEMORY, BDB, FEDERATED, etc.You can use the SHOW ENGINES; statement to view the storage engines supported by the system.

The syntax for changing the storage engine of the table is as follows:

ALTER TABLE <table name> ENGINE=<changed storage engine name>;

Example:

Change the storage engine of the data table student to MyISAM.

 

 

 

8, delete the foreign key constraint of the table

Syntax to delete a foreign key:

ALTER TABLE <table name> DROP FOREIGN KEY <foreign key constraint name>

Example:

Create employee9

 

create table employee9

(

id INT(11) primary key ,

name varchar(20),

deptId INT(11),

salary float,

constraint fk_emp_dept foreign key(deptId) references department(id)

);

 

Delete foreign key

alter table employee9 drop foreign key fk_emp_dept;

 

 

 

Delete data table

1.Delete unrelated tables

In MySQL, use DROP TABLE to delete one or more data tables that are not associated with other tables at a time.Syntax:

DROP TABLE [IF EXISTS] table1, table2,...tablen;

 

 

 

2.Delete the main table associated with other tables

 

If there is a foreign key association between data tables, if you delete the parent table directly, the result will show failure.The reason is that direct deletion will destroy the referential integrity of the table.

If you must delete, you can delete the associated child table first, and then delete the parent table, just delete the data in the two tables at the same time.If you want to keep the child table, this is to delete the parent table separately, just cancel the foreign key constraint of the associated table, and then delete the parent table

Example:

Create dept table

create table dept(id INT(11) primary key ,name varchar(10),location varchar(30));

 

Create emp table

create table emp

(

id INT(10) primary key,

name varchar(20),

deptId INT(10),

constraint fk_emp_dept foreign key(deptId) references dept(id)

);

 

Modify foreign key association

alter table emp drop foreign key fk_emp_dept;

 

Dept table

drop dept;