MySQL modifies the table structure and its functions of adding, deleting and modifying fields

MySQL modify table structure add delete modify fields

Create database

CREATE DATABASE database_name

Create table

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Delete table

DROP TABLE IF EXISTS `user`;

Add field:

"ALTER TABLE  `user` ADD  `id` int(11) NOT NULL DEFAULT '0'  COMMENT 'ID'"
ALTER  TABLE  `user`  ADD  `name` VARCHAR( 20  )  CHARACTER  SET utf8 COLLATE utf8_general_ci NULL  DEFAULT NULL  COMMENT  '姓名'

Delete field

ALTER TABLE  `user` DROP column name

Rename

ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;

Modify type

alter table t1 change b b bigint not null;  
alter table infos change list list tinyint not null default '0';

Add index

alter table t1 rename t2;
mysql> alter table tablename change depno depno int(5) not null;  
mysql> alter table tablename add index 索引名 (字段名1[，字段名2 …]);  
mysql> alter table tablename add index emp_name (name);加主关键字的索引  
mysql> alter table tablename add primary key(id);加唯一限制条件的索引  
mysql> alter table tablename add unique emp_name2(cardnumber);删除某个索引  
mysql>alter table tablename drop index emp_name;修改表：

Add fields in Thinkphp3.2, such as:

M('admin')->execute("ALTER TABLE  `admin` ADD  `id` int(11) NOT NULL DEFAULT '0'  COMMENT 'ID'");
M('admin')->execute("ALTER TABLE  `admin` ADD  `name` varchar(20) DEFAULT NULL  COMMENT '姓名'");

Recommended tutorial: "Mysql"

The above is the detailed content of MySQL modifies the table structure and its functions of adding, deleting and modifying fields. For more information, please follow other related articles on the PHP Chinese website!