Detailed description of MySQL table structure modification

Detailed description of MySQL table structure modification

MySQL alter table syntax
======================================
Add column [add column name]
======================================
① Alter table name add column name column type column parameter [add column at the end of the table]
Example: alter table test add username char (20) not null default '';
Alter table test add birth date not null default '2017-00-00 ';

② Alter table name add column name column type column parameter after a column [add a new column after a column]
Example: alter table test add gender char (1) not null default ''after username;

③ Alter table name add column name column type column parameter first [add new column to the beginning]
For example, alter table test add pid int not null default 0 first;

======================================
Delete a column [drop column name]
======================================
① Alter table Name drop column name
Example: alter table test drop pid;

======================================
Modify column [modife column name]
======================================
① Alter table name modify column name new type new parameter [modify column type]
Example: alter table test modify gender char (4) not null default '';
② Alter table name change old column name new type new parameter [Modify column name and column type]
Example: alter table test change pid uid int unsigned not null default 0;

======================================
Query Columns
======================================
① Desc table name [query all columns]
Example: desc test;
Mysql> desc department;
+ ------- + ------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ------- + ------------- + ------ + ----- + --------- + ------- +
| DId | int (11) | NO | PRI |
| DName | varchar (32) | YES | NULL |
+ ------- + ------------- + ------ + ----- + --------- + ------- +

② Show columns from table name [same effect as desc]
Mysql> show columns from department;
+ ------- + ------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ------- + ------------- + ------ + ----- + --------- + ------- +
| DId | int (11) | NO | PRI |
| DName | varchar (32) | YES | NULL |
+ ------- + ------------- + ------ + ----- + --------- + ------- +

③ Show create table name [view the table creation Code]
Mysql> show create table department;
Create table 'department '(
'Did' int (11) not null,
'Dname' varchar (32) default null,
Primary key ('did ')
) ENGINE = InnoDB default charset = utf8

How to avoid the problem that the table cannot be used when MySQL modifies the table structure

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

This article permanently updates the link address: