Example of adding and deleting fields and table names in the alter datasheet in MySQL

Alter is a very powerful feature. We can use ALTER to modify the data table name and some other operations, and let's look at an example of adding, deleting, and modifying the table name in the ALTER datasheet in MySQL.

   

Modify the data content in the MySQL database to delete:

[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 (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)

On top, modify the table name in the database, add and remove fields from the table.

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