MYSQL数据库（七）-数据表的插入、删除

来源：互联网发布：lrc编辑器 mac 编辑：程序博客网时间：2024/05/16 07:12

本章目录

一、单列插入，位置默认列底

二、单列插入，位于某列之后

三、单列插入，位于列顶端

四、多列插入，不能指定位置，只能位于列底

五、删除列、删除并且添加（单行和多行同上，列之间逗号隔开）

本章语句

alter table tbl_name add[column] col_name column_definition[first|after col_name] 数据表删除或增加语句
解释：更改表格某某，添加，某某列名，列定义，【顶部|某某之后】

数据表的修改操作案例：

一、创建一个数据表city1，并添加了约束

mysql> create table city1(    -> id smallint unsigned primary key auto_increment,    -> usename varchar(20) not null,    -> pid smallint unsigned,    -> foreign key(pid) references sheng(id) on delete cascade)//查看表结构：mysql> show columns from city1+---------+----------------------+------+-----+---------+----------------+| Field   | Type                 | Null | Key | Default | Extra          |+---------+----------------------+------+-----+---------+----------------+| id      | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment || usename | varchar(20)          | NO   |     | NULL    |                || pid     | smallint(5) unsigned | YES  | MUL | NULL    |                |+---------+----------------------+------+-----+---------+----------------+

一、单列插入

语句：alter table city1 add age tinyint UNSIGNED NOT NULL DEFAULT 10;
解释：更改表格 city1，添加参数age ，类型：tinyint ，无符号，非空，默认值10

//更改表格city1,添加age列，类型：tinyint ，无符号类，非空，默认值：10mysql> alter table city1 add age tinyint UNSIGNED NOT NULL DEFAULT 10;Query OK, 2 rows affected (0.53 sec)//插入成功//查看插入结果，会发现我们数据表中增加了一列，默认值为10.mysql> SHOW COLUMNS FROM CITY1;//不指定位置默认放在列末尾//插入前不存在age列+---------+----------------------+------+-----+---------+----------------+| Field   | Type                 | Null | Key | Default | Extra          |+---------+----------------------+------+-----+---------+----------------+| id      | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment || usename | varchar(20)          | NO   |     | NULL    |                || pid     | smallint(5) unsigned | YES  | MUL | NULL    |                |+---------+----------------------+------+-----+---------+----------------+//插入后存在age列，插入成功+---------+----------------------+------+-----+---------+----------------+| Field   | Type                 | Null | Key | Default | Extra          |+---------+----------------------+------+-----+---------+----------------+| id      | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment || usename | varchar(20)          | NO   |     | NULL    |                || pid     | smallint(5) unsigned | YES  | MUL | NULL    |                || age     | tinyint(3) unsigned  | NO   |     | 10      |                |+---------+----------------------+------+-----+---------+----------------+

二、单列插入，位于某列之后

语句：alter table city1 add password varchar(10) NOT NULL AFTER usename;
解释：更改表格 city1 添加参数password 字符类型位置位于usename后

注意：这里我们指定了某列之后：AFTER usename，就是位置位于usename下方

mysql> alter table city1 add password varchar(10) NOT NULL AFTER usename;Query OK, 2 rows affected (0.32 sec)//插入成功mysql> show columns from city1//插入前，查看city1表的结构不存在password列+---------+----------------------+------+-----+---------+----------------+| Field   | Type                 | Null | Key | Default | Extra          |+---------+----------------------+------+-----+---------+----------------+| id      | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment || usename | varchar(20)          | NO   |     | NULL    |                || pid     | smallint(5) unsigned | YES  | MUL | NULL    |                || age     | tinyint(3) unsigned  | NO   |     | 10      |                |+---------+----------------------+------+-----+---------+----------------+//插入后，查看city1表的结构，我们插入的password列位于usename 之后+----------+----------------------+------+-----+---------+----------------+| Field    | Type                 | Null | Key | Default | Extra          |+----------+----------------------+------+-----+---------+----------------+| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment || usename  | varchar(20)          | NO   |     | NULL    |                || password | varchar(10)          | NO   |     | NULL    |                || pid      | smallint(5) unsigned | YES  | MUL | NULL    |                || age      | tinyint(3) unsigned  | NO   |     | 10      |                |+----------+----------------------+------+-----+---------+----------------+

三、单列插入，位于列顶端

语句：alter table city1 add tname varchar(22) NOT NULL FIRST;
解释：更改表格 city1 添加参数tname 字符类型不能为空位于顶部

mysql> alter table city1 add tname varchar(22) NOT NULL FIRST;Query OK, 2 rows affected (0.25 sec)//插入成功过mysql> SHOW COLUMNS FROM CITY1;//查看city1表的结构，我们插入的tname列位于列的顶端//插入前，查询列表，暂无tname列+----------+----------------------+------+-----+---------+----------------+| Field    | Type                 | Null | Key | Default | Extra          |+----------+----------------------+------+-----+---------+----------------+| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment || usename  | varchar(20)          | NO   |     | NULL    |                || password | varchar(10)          | NO   |     | NULL    |                || pid      | smallint(5) unsigned | YES  | MUL | NULL    |                || age      | tinyint(3) unsigned  | NO   |     | 10      |                |+----------+----------------------+------+-----+---------+----------------+//插入后，查询列表，tname列位于列表的顶端+----------+----------------------+------+-----+---------+----------------+| Field    | Type                 | Null | Key | Default | Extra          |+----------+----------------------+------+-----+---------+----------------+| tname    | varchar(22)          | NO   |     | NULL    |                || id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment || usename  | varchar(20)          | NO   |     | NULL    |                || password | varchar(10)          | NO   |     | NULL    |                || pid      | smallint(5) unsigned | YES  | MUL | NULL    |                || age      | tinyint(3) unsigned  | NO   |     | 10      |                |+----------+----------------------+------+-----+---------+----------------+6 rows in set (0.01 sec)

四、多列插入，不能指定位置，只能位于列底

语句：alter table t_name add[column](col_name column_definition,……)
解释：更改表格某某添加【列】（某列，某列，……）

mysql> alter table city1 add (    -> ttt varchar(20) NOT NULL,    -> uuu varchar(20) NOT NULL);Query OK, 2 rows affected (0.33 sec)//添加成功mysql> show columns from city1;//添加的两列ttt和uuu位于列的最下方//插入前+----------+----------------------+------+-----+---------+----------------+| Field    | Type                 | Null | Key | Default | Extra          |+----------+----------------------+------+-----+---------+----------------+| tname    | varchar(22)          | NO   |     | NULL    |                || id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment || usename  | varchar(20)          | NO   |     | NULL    |                || password | varchar(10)          | NO   |     | NULL    |                || pid      | smallint(5) unsigned | YES  | MUL | NULL    |                || age      | tinyint(3) unsigned  | NO   |     | 10      |                |+----------+----------------------+------+-----+---------+----------------+//插入后，ttt和uuu两列插入成功+----------+----------------------+------+-----+---------+----------------+| Field    | Type                 | Null | Key | Default | Extra          |+----------+----------------------+------+-----+---------+----------------+| tname    | varchar(22)          | NO   |     | NULL    |                || id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment || usename  | varchar(20)          | NO   |     | NULL    |                || password | varchar(10)          | NO   |     | NULL    |                || pid      | smallint(5) unsigned | YES  | MUL | NULL    |                || age      | tinyint(3) unsigned  | NO   |     | 10      |                || ttt      | varchar(20)          | NO   |     | NULL    |                || uuu      | varchar(20)          | NO   |     | NULL    |                |+----------+----------------------+------+-----+---------+----------------+8 rows in set (0.01 sec)

五、删除列、删除并且添加（单行和多行同上，列之间逗号隔开）

语句：alter table tal_name drop [column] col_name
解释：更改表格表名册删除【列】列名册

1、单行删除

//单列删除age，删除成功mysql> alter table city1 drop age; //翻译：更改表格city1 删除age列mysql> show columns from city1;//查看city1表结构//删除前，age列存在+----------+----------------------+------+-----+---------+----------------+| Field    | Type                 | Null | Key | Default | Extra          |+----------+----------------------+------+-----+---------+----------------+| tname    | varchar(22)          | NO   |     | NULL    |                || id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment || usename  | varchar(20)          | NO   |     | NULL    |                || password | varchar(10)          | NO   |     | NULL    |                || pid      | smallint(5) unsigned | YES  | MUL | NULL    |                || age      | tinyint(3) unsigned  | NO   |     | 10      |                || ttt      | varchar(20)          | NO   |     | NULL    |                || uuu      | varchar(20)          | NO   |     | NULL    |                |+----------+----------------------+------+-----+---------+----------------+//删除后，age删除成功+----------+----------------------+------+-----+---------+----------------+| Field    | Type                 | Null | Key | Default | Extra          |+----------+----------------------+------+-----+---------+----------------+| tname    | varchar(22)          | NO   |     | NULL    |                || id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment || usename  | varchar(20)          | NO   |     | NULL    |                || password | varchar(10)          | NO   |     | NULL    |                || pid      | smallint(5) unsigned | YES  | MUL | NULL    |                || ttt      | varchar(20)          | NO   |     | NULL    |                || uuu      | varchar(20)          | NO   |     | NULL    |                |+----------+----------------------+------+-----+---------+----------------+

2、多列删除

2、多列删除，同时删除ttt和uuu列，（注意多行删除，删除列需要用逗号隔开，如：drop ttt,drop uuu）//更改表格city1,删除ttt,uuu两列mysql> alter table city1 drop ttt,drop uuu;mysql> show columns from city1;//查看//删除前+----------+----------------------+------+-----+---------+----------------+| Field    | Type                 | Null | Key | Default | Extra          |+----------+----------------------+------+-----+---------+----------------+| tname    | varchar(22)          | NO   |     | NULL    |                || id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment || usename  | varchar(20)          | NO   |     | NULL    |                || password | varchar(10)          | NO   |     | NULL    |                || pid      | smallint(5) unsigned | YES  | MUL | NULL    |                || ttt      | varchar(20)          | NO   |     | NULL    |                || uuu      | varchar(20)          | NO   |     | NULL    |                |+----------+----------------------+------+-----+---------+----------------+//删除后+----------+----------------------+------+-----+---------+----------------+| Field    | Type                 | Null | Key | Default | Extra          |+----------+----------------------+------+-----+---------+----------------+| tname    | varchar(22)          | NO   |     | NULL    |                || id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment || usename  | varchar(20)          | NO   |     | NULL    |                || password | varchar(10)          | NO   |     | NULL    |                || pid      | smallint(5) unsigned | YES  | MUL | NULL    |                |+----------+----------------------+------+-----+---------+----------------+

3、删除和添加并用

//更改表格city1,删除tname列，添加vvv列mysql> alter table city1 drop tname,add vvv varchar(21) NOT NULL;Query OK, 2 rows affected (0.27 sec)mysql> show columns from city1;//查看效果//删除前+----------+----------------------+------+-----+---------+----------------+| Field    | Type                 | Null | Key | Default | Extra          |+----------+----------------------+------+-----+---------+----------------+| tname    | varchar(22)          | NO   |     | NULL    |                || id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment || usename  | varchar(20)          | NO   |     | NULL    |                || password | varchar(10)          | NO   |     | NULL    |                || pid      | smallint(5) unsigned | YES  | MUL | NULL    |                |+----------+----------------------+------+-----+---------+----------------+//删除后tname列消失，最下面出现vvv列+----------+----------------------+------+-----+---------+----------------+| Field    | Type                 | Null | Key | Default | Extra          |+----------+----------------------+------+-----+---------+----------------+| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment || usename  | varchar(20)          | NO   |     | NULL    |                || password | varchar(10)          | NO   |     | NULL    |                || pid      | smallint(5) unsigned | YES  | MUL | NULL    |                || vvv      | varchar(21)          | NO   |     | NULL    |                |+----------+----------------------+------+-----+---------+----------------+5 rows in set (0.01 sec)

阅读全文

0 0