What is the sql statement to modify the table structure?

In mysql, you can use the "ALTER TABLE" statement to modify the table structure. You only need to use the "ALTER TABLE table name add/drop/alter field" statement. The "ALTER TABLE" statement is used to add, modify, or delete columns from an existing table.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

ALTER TABLE statement

The ALTER TABLE statement is used to add, modify, or delete columns in an existing table.

SQL ALTER TABLE syntax

To add a column to the table, use the following syntax:

ALTER TABLE table_name
ADD column_name datatype

To delete a column in the table, use the following syntax:

ALTER TABLE table_name 
DROP COLUMN column_name

Note: Some database systems do not allow this method of deleting columns in database tables (DROP COLUMN column_name).

To change the data type of a column in the table, please use the following syntax:

ALTER TABLE table_name
ALTER COLUMN column_name datatype

Example:

CREATE TABLE `login_user` (
  `id` int(32) NOT NULL AUTO_INCREMENT,
  `name` varchar(225) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '名字',
  `password` varchar(26) DEFAULT NULL COMMENT '密码3',
  `type` varchar(32) DEFAULT NULL,
  `state` varchar(32) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `password5` varchar(26) DEFAULT NULL COMMENT '密码5',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

1. Modify fields: generally modify attributes and data types

alter table login_user modify password varchar(25) DEFAULT NULL COMMENT '密码2'

2. Rename fields: alter table table name change old field new field data type [attribute] [location];

alter table login_user change password2  password varchar(26) DEFAULT NULL COMMENT '密码3'

3. Add new field: alter table table name add [column] field Name Data type [Column attribute][Position]

Position: The field can be stored anywhere in the table;

first:The first position;

after:In After which field; defaults to after the last field.

--Add to the end

alter  table  login_user  add   password3  varchar(26) DEFAULT NULL COMMENT '密码4'

--Add after the specified field alter table table name add field field type to be added after field name to follow

alter  table  login_user  add   password6   varchar(26)  DEFAULT NULL COMMENT '密码6'  after password

4 .Delete fields: alter table table name drop field name;

alter  table  login_user  drop   password5

Related learning recommendations: mysql tutorial(video)

The above is the detailed content of What is the sql statement to modify the table structure?. For more information, please follow other related articles on the PHP Chinese website!