MYSQL数据库(七)-数据表的插入、删除

来源:转载

本章目录

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

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

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

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

五、删除列、删除并且添加(单行和多行同上,列之间逗号隔开)


本章语句

  • 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)

分享给朋友:
您可能感兴趣的文章:
随机阅读: