| 标题 | mysql中alter数据表中增加、删除字段与表名修改例子 |
| 内容 | 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 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) |
| 随便看 |
|
在线学习网考试资料包含高考、自考、专升本考试、人事考试、公务员考试、大学生村官考试、特岗教师招聘考试、事业单位招聘考试、企业人才招聘、银行招聘、教师招聘、农村信用社招聘、各类资格证书考试等各类考试资料。