Use SQL statements in MySQL to modify field names, field lengths, and so on

In the reconstruction of the site, the data structure is usually modified, so add, delete, add the MySQL table field is unavoidable, and sometimes for convenience, but also increase the modification of the table or field comments, the same field attributes to the same. These actions can be done in phpMyAdmin or other MySQL management tools, but we sometimes choose to write SQL statements for easier management.

1. Modify the length of the field

Grammar:

ALTER Table name MODIFY COLUMN field name data type (modified length)

Example:

Change the length of the field from 10 to 20

ALTER TABLE attence MODIFY COLUMN ID INT (20)

2. Modify the name of the field

Grammar:

ALTER TABLE < table name > change < Field name > < field new name > < field type >.

Example:

Change field Attence_name to name

ALTER TABLE attence Change attence_name name VARCHAR (20)

3. New fields

Grammar:

Add a field that is empty by default
ALTER Table name ADD COLUMN field FirstName type DEFAULT NULL;
Add a field that is not empty
ALTER Table name Add COLUMN field FirstName type not NULL;

Example:
ALTER TABLE attence ADD COLUMN attence_name VARCHAR () DEFAULT NULL;

ALTER TABLE attence ADD COLUMN Age VARCHAR (a) not NULL;

4. Delete a field

Grammar:

ALTER Table name DROP COLUMN field name;

Example:

ALTER TABLE attence DROP COLUMN age;

5. Adding fields in bulk

Method One
can use transactions

Grammar:

Begin Transaction start
ALTER TABLE table name add field name segment type (length);
ALTER TABLE table name add field name segment type (length);
ALTER TABLE table name add field name segment type (length);
ALTER TABLE table name add field name segment type (length);
Commit

Example:

Begin Transaction start
ALTER TABLE em_day_data add F_DAY_HOUSE7 int (11);
ALTER TABLE em_day_data add F_day_house8 int (11);
ALTER TABLE em_day_data add F_DAY_HOUSE9 int (11);
ALTER TABLE em_day_data add F_DAY_HOUSE10 int (11);
Commit

Method Two

ALTER TABLE name Add (field 1 type (length), field 2 type (length), field 3 type (length));

ALTER TABLE Em_day_data Add (f_day_house11 int (one), F_day_house12 Int (one), F_day_house13 Int (11));

6. Batch modification of field names

Grammar:

ALTER TABLE table change before field names modified field name int (one) not NULL,
Change before field names modified field name int (one) not NULL,
Change before field names modified field name int (one) not NULL,
Change before field names modified field name int (one) not NULL,
Change before field names modified field name int (one) NOT NULL

Example:

ALTER TABLE em_day_data change f_day_house11 f_day_hour11 Int (one) not NULL,
Change F_day_house12 f_day_hour12 Int (one) not NULL,
Change F_day_house13 F_day_hour13 Int (one) not NULL,
Change F_day_house14 f_day_hour14 Int (one) not NULL,
Change F_day_house15 f_day_hour15 Int (one) not NULL,
Change F_day_house16 f_day_hour16 Int (one) not NULL,
Change F_day_house17 f_day_hour17 Int (one) NOT NULL

Use SQL statements in MySQL to modify field names, field lengths, and so on