alter是非常強大的一個功能我們可以利用alter來修改數(shù)據(jù)表表名字體名及一些其它的操作了，下面一起來看看mysql中alter數(shù)據(jù)表中增加、刪除字段與表名修改的一個例子．

修改刪除mysql數(shù)據(jù)庫中的數(shù)據(jù)內(nèi)容：

[root@hk ~]# /usr/local/mysql/bin/mysql -uroot -p'admin' #進入mysql

mysql> create database gbk default character set gbk collate gbk_chinese_ci; #建立一個名字叫做gbk的數(shù)據(jù)庫

mysql> use gbk

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| gbk |

+--------------------+

mysql> show tables;

Empty set (0.00 sec)

mysql> create table test( #建立一個叫做test的數(shù)據(jù)表

-> 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'); #插入部分內(nèi)容

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數(shù)據(jù)庫

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)

接上上面，修改數(shù)據(jù)庫中表名，表中增加、刪除字段。

mysql> use gbk #進入gbk數(shù)據(jù)庫

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)

更多信息請查看IT技術(shù)專欄