修改数据表 雨点打透心脏的1/2处 2022-05-19 00:07 204阅读 0赞 就是对数据表进行列的删除和增加,约束的添加和删除 1.添加数据列![70][] 查看users1的数据表结构 show columns from users1; mysql> show columns from users1; \+----------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | \+----------+----------------------+------+-----+---------+----------------+ | id | smallint(5) unsigned | NO | PRI | NULL | auto\_increment | | username | varchar(10) | NO | | NULL | | | password | varchar(32) | NO | | NULL | | | pid | smallint(5) unsigned | YES | MUL | NULL | | \+----------+----------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) 添加age在数据项的最后一列 alter table users1 add age tinyint unsigned not null default 10; mysql> show columns from users1; \+----------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | \+----------+----------------------+------+-----+---------+----------------+ | id | smallint(5) unsigned | NO | PRI | NULL | auto\_increment | | username | varchar(10) | NO | | NULL | | | password | varchar(32) | NO | | NULL | | | pid | smallint(5) unsigned | YES | MUL | NULL | | | age | tinyint(3) unsigned | NO | | 10 | | \+----------+----------------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) 在数据项的第一项添加 alter table users1 add truename varchar(20) unsigned first; 删除单行 alter table users drop truename; 删除多行 alter table users drop passward,drop age; 同时删除和添加 alter table users1 drop truename add age tinyint unsigned not null default 10; ![70 1][]添加主键,作用是数据项的唯一表示符,就像id mysql> show columns from users2; \+----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | \+----------+----------------------+------+-----+---------+-------+ | username | varchar(20) | NO | | NULL | | | pid | smallint(5) unsigned | YES | | NULL | | | id | smallint(5) unsigned | YES | | NULL | | \+----------+----------------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) alter table users2 add constraint pk\_user2\_id primary key (id); mysql> show columns from users2; \+----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | \+----------+----------------------+------+-----+---------+-------+ | username | varchar(20) | NO | | NULL | | | pid | smallint(5) unsigned | YES | | NULL | | | id | smallint(5) unsigned | NO | PRI | NULL | | \+----------+----------------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) 2.添加字段的字符约束 alter teable users2 add id smallint unsigned; 3.添加唯一约束 alter table users2 add unique (username); 4.添加外键列 alter table users2 add foreign key (pid) references provinces (id); 5.添加默认值 alter table users2 alter age set default 15; 6.删除默认值 alter table users2 alter age drop default ; 7.删除主键约束,一个表就一个主键约束,所以不需要添加id alter table users2 drop foreign key users2\_ibfk\_1; [70]: /images/20220519/b7e4f6e096714b548beefd041b2b17ce.png [70 1]: /images/20220519/cfd35d4c0cea473fab4efd25f8573be2.png
还没有评论,来说两句吧...