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

Source: Internet
Author: User
Tags character set table name create database mysql database

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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.