mysql中alter数据表中增加删除字段与表名修改示例
发布时间：2022-06-29 21:04:14 所属栏目：MySql教程 来源：互联网
导读：alter是非常强大的一个功能我们可以利用alter来修改数据表表名字体名及一些其它的操作了,下面一起来看看mysql中alter数据表中增加、删除字段与表名修改的一个例子. 修改删除mysql数据库中的数据内容: [root@hk ~]# /usr/local/mysql/bin/mysql -uroot -pad
|
alter是非常强大的一个功能我们可以利用alter来修改数据表表名字体名及一些其它的操作了,下面一起来看看mysql中alter数据表中增加、删除字段与表名修改的一个例子. 修改删除mysql数据库中的数据内容: [root@hk ~]# /usr/local/mysql/bin/mysql -uroot -p'admin' #进入mysql mysql> create database gbk default character set gbk collate gbk_chinese_ci; #建立一个名字叫做gbk的数据库 mysql> use gbk mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | gbk | +--------------------+ mysql> show tables; Empty set (0.00 sec) mysql> create table test( #建立一个叫做test的数据表 -> id int(4) not null primary key auto_increment, -> name char(20) not null -> ); Query OK, 0 rows affected (0.13 sec) mysql> show tables; +---------------+ | Tables_in_gbk | +---------------+ | test | +---------------+ 1 row in set (0.00 sec) mysql> insert into test(id,name) values(1,'zy'); #插入部分内容 mysql> insert into test(id,name) values(2,'binghe'); mysql> insert into test(id,name) values(3,'zilong'); mysql> insert into test(id,name) values(4,'feng'); mysql> select * from test; #检索整个test表 +----+--------+ | id | name | +----+--------+ | 1 | zy | | 2 | binghe | | 3 | zilong | | 4 | feng | +----+--------+ 4 rows in set (0.00 sec) [root@hk ~]# /usr/local/mysql/bin/mysqldump -uroot -p'admin' -B gbk >/tmp/gbk.sql #备份gbk数据库 mysql> update test set name = 'zy' ; #未定义 mysql> select * from test; # +----+------+ | id | name | +----+------+ | 1 | zy | | 2 | zy | | 3 | zy | | 4 | zy | +----+------+ [root@hk ~]# /usr/local/mysql/bin/mysql -uroot -p'admin' mysql> use gbk --phpfensi.com mysql> select * from test; +----+--------+ | id | name | +----+--------+ | 1 | zy | | 2 | binghe | | 3 | zilong | | 4 | feng | +----+--------+ mysql> update test set name = 'yadianna' where id =1; mysql> select * from test; +----+----------+ | id | name | +----+----------+ | 1 | yadianna | | 2 | binghe | | 3 | zilong | | 4 | feng | +----+----------+ mysql> update test set id = 999 where name ='yadianna'; mysql> select * from test; +-----+----------+ | id | name | +-----+----------+ | 2 | binghe | | 3 | zilong | | 4 | feng | | 999 | yadianna | +-----+----------+ mysql> delete from test where id =999; mysql> select * from test; +----+--------+ | id | name | +----+--------+ | 2 | binghe | | 3 | zilong | | 4 | feng | +----+--------+ mysql> delete from test where id <4; #以条件删除 mysql> truncate table test; #删除all mysql> select * from test; Empty set (0.00 sec) 接上上面,修改数据库中表名,表中增加、删除字段. mysql> use gbk #进入gbk数据库 mysql> desc test; +-------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | name | char(20) | NO | | NULL | | +-------+----------+------+-----+---------+----------------+ mysql> alter table test add gender char(4); #增加gender mysql> desc test; +--------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+----------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | name | char(20) | NO | | NULL | | | gender | char(4) | YES | | NULL | | +--------+----------+------+-----+---------+----------------+ mysql> alter table test add age int(4) after name; mysql> desc test; +--------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+----------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | name | char(20) | NO | | NULL | | | age | int(4) | YES | | NULL | | | gender | char(4) | YES | | NULL | | +--------+----------+------+-----+---------+----------------+ mysql> show tables; +---------------+ | Tables_in_gbk | +---------------+ | test | +---------------+ mysql> rename table test to hello; mysql> show tables; +---------------+ | Tables_in_gbk | +---------------+ | hello | +---------------+ mysql> alter table hello rename to world; mysql> show tables; +---------------+ | Tables_in_gbk | +---------------+ | world | +---------------+ mysql> alter table world drop age; mysql> desc world; +--------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+----------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | name | char(20) | NO | | NULL | | | gender | char(4) | YES | | NULL | | +--------+----------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)。 （编辑：云计算网_宿迁站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐