mysql 连表修改数据库_【一步一步学习mysql】修改数据表

添加单列

ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST|AFTER col_name]

mysql> alter table users add age tinyint unsigned not null default 10;

mysql> show columns from users;

+-------+----------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+----------------------+------+-----+---------+----------------+

| id | smallint(6) | NO | PRI | NULL | auto_increment |

| name | varchar(20) | NO | | NULL | |

| pid | smallint(5) unsigned | YES | MUL | NULL | |

| age | tinyint(3) unsigned | NO | | 10 | |

+-------+----------------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)

mysql> alter table users add password varchar(20) not null after name;

mysql> show columns from users;

+----------+----------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+----------+----------------------+------+-----+---------+----------------+

| id | smallint(6) | NO | PRI | NULL | auto_increment |

| name | varchar(20) | NO | | NULL | |

| password | varchar(20) | NO | | NULL | |

| pid | smallint(5) unsigned | YES | MUL | NULL | |

| age | tinyint(3) unsigned | NO | | 10 | |

+----------+----------------------+------+-----+---------+----------------+

5 rows in set (0.00 sec)

添加多列

ALTER TABLE tbl_name ADD [COLUMN] (col_name column_definition, ...)

mysql> alter table users add (weight tinyint unsigned not null, height tinyint unsigned);

mysql> show columns from users;

+----------+----------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+----------+----------------------+------+-----+---------+----------------+

| id | smallint(6) | NO | PRI | NULL | auto_increment |

| name | varchar(20) | NO | | NULL | |

| password | varchar(20) | NO | | NULL | |

| pid | smallint(5) unsigned | YES | MUL | NULL | |

| age | tinyint(3) unsigned | NO | | 10 | |

| weight | tinyint(3) unsigned | NO | | NULL | |

| height | tinyint(3) unsigned | YES | | NULL | |

+----------+----------------------+------+-----+---------+----------------+

7 rows in set (0.00 sec)

删除列

ALTER TABLE tbl_name DROP [COLUMN] col_name

mysql> ALTER TABLE users DROP weight, DROP height;

Query OK, 0 rows affected (0.40 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> show columns from users;

+----------+----------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+----------+----------------------+------+-----+---------+----------------+

| id | smallint(6) | NO | PRI | NULL | auto_increment |

| name | varchar(20) | NO | | NULL | |

| password | varchar(20) | NO | | NULL | |

| pid | smallint(5) unsigned | YES | MUL | NULL | |

| age | tinyint(3) unsigned | NO | | 10 | |

+----------+----------------------+------+-----+---------+----------------+

5 rows in set (0.00 sec)

添加主键约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name)

# 创建新表

mysql> CREATE TABLE users3 (id SMALLINT, name VARCHAR(20), pid SMALLINT UNSIGNED);

Query OK, 0 rows affected (0.23 sec)

mysql> SHOW CO