MySQL (III) _ MySQL

After creating a data table using SQL statements, you can view the table structure definition to check whether the table definition is correct. In MySQL, you can use the DESCRIBE and SHOWCREATETABLE statements to view the table structure. 1. view the basic table structure statement DESCRIBEDES

After creating a data table using SQL statements, you can view the table structure definition to confirm whether the table definition is correct. In MySQL, DESCRIBE and show create table statements can be used to view the TABLE structure.

1. view the basic table structure statement DESCRIBE

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

DESCRIBE table name;

The latter is abbreviated:

DESC table name;

The meanings of each field are as follows:

NULL: indicates whether the column can store NULL

Key: indicates whether the column has been indexed. PRI indicates that this column is part of the table's primary key; UNI indicates that this column is part of the UNIQUE index; MUL indicates that a given value in this column can appear multiple times.

Default: indicates whether the column has a Default value. If yes, what is it.

Extra: additional information related to a given column that can be obtained, such as AUTO_INCREMENT.

2. view the detailed TABLE structure statement SHOW CREATE TABLE

The show create table statement is used to display the create table statement when a TABLE is created. the syntax format is as follows:

SHOW CREATE TABLE <表名\g> ;

You can use the show create table statement to view not only the detailed statements at the time of creation, but also the storage engine and character encoding.

PS:

If the \ G parameter is not good, the displayed results may be very confusing. after the \ G parameter is added, the displayed results are more intuitive and easy to observe.

Modify data table 1 and Table name

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

ALTER TABLE <旧表名> RENAME <新表名> ;

Here, "TO" is an optional parameter. whether TO use it or not does 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 modification. modifying the table name does not modify the table structure. Therefore, the table structure after modification is identical with that before modification.

2. modify the data type of a field

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

ATTER TABLE <表名> MODIFY <字段名> <数据类型>

Example:

Change the data type of the name field in data table department2 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 <表名> CHANGE <旧字段名> <新字段名> <新数据类型>

Here, "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 data type of a field, you can set the new data type to be the same as the original one, but the data type cannot be blank.

Example:

Change the location of the department field of the data table to local. The data type remains unchanged. the SQL statement is as follows:

Alter table department change location local varchar (50 );

4. add fields

Syntax:

ALTER TABLE <表名> ADD <新字段名> <数据类型> [Constraints] [FIRST | an existing field name AFTER];

The new field name is the name of the field to be added; "FIRST" is an optional parameter, which is used to set the newly added field to the FIRST field of the table; "AFTER" is an optional parameter, add the newly added field to the end of the specified "existing field name. If the SQL statement does not contain the "FIRST" or "AFTER" parameters, this field is placed in the last column of the data table by default.

A. add fields without integrity constraints

Example: add a managerId (department Manager number) field of the INT type without 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 field column1 of the VARCHAR (25) type that cannot be empty to the data table department. the SQL statement is as follows:

Alter table department ADD column1 VARCHAR (12) not null;

C. add a field in the first column of the table.

Example:
Add an INT field column2 to the department of the data table. 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 the evil INT type column3 to the department of the data table after column2.

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

5. delete fields

Deleting a field is to delete a field from the table. the syntax format is:

ALTER TABLE <表名> DROP <字段名>

Example:

Delete column2 field in data table department

Alter table department drop column2;

6. modify the field arrangement position

Syntax format:

ALTER TABLE <表名> MODIFY <字段1> <数据类型> FIRST | AFTER <字段2>

The field where "field 1" is to be modified, the data type of "data type" value "field 1", and "FIRST" is an optional parameter, "Field 1" is changed to the first field in the table. "AFTER field 2" is used to insert "field 1" to "field 2.

A. change the field to the first field in the table.

Example:

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

Alter table department modify column1 varchar (12) first;

B. After modifying a field to a specified column in the table

Example:

Modify the column1 field in the data table department to the end 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 table storage engine

The storage engine uses different technologies to store MySQL data in files or memory. MySQL's main storage engines include MyISAM, InnoDB, MEMORY, BDB, and FEDERATED. You can use the show engines; statement to view the storage ENGINES supported by the system.

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

ALTER TABLE <表名> ENGINE = <更改后的存储引擎名> ;

Example:

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

8. delete the foreign key constraint of the table

Syntax for deleting foreign keys:

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>

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 a data table 1. delete a table that is not associated

In MySQL, you can use drop table to delete one or more data tables that are not associated with other tables at a time. Syntax format:

Drop table [if exists] TABLE 1, TABLE 2,... TABLE n;

2. delete the primary table associated with another table

A foreign key association exists between data tables. if you delete the parent table directly, the result will fail. The reason is that direct deletion will damage the integrity of the table's reference.

If you must delete the table, you can first delete the child table associated with it and delete the parent table, but in this way, the data in both tables is deleted. If you want to retain the child table, you only need to cancel the foreign key constraints of the associated table and then delete the parent table.

Example:

Create a dept table

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

Create an 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;

Delete a dept table

Drop dept;