MySQL ALTER

From w3cyberlearnings

1 Create table
2 desc to describe about the table
3 rename table to allprofile
4 rename column p_id to ap_id
5 change column type but not the column name
6 add another column after a specific column
7 add multiple columns
8 define default value
9 add unique to the column
10 delete a column
11 drop column, add new column, and change column type at once
12 add full-text search
13 reorder table column
14 convert to a different character set
15 change the mysql table engine type
16 Change the Unicode format (UTF-8) using Alter

Create table

CREATE TABLE tb_myprofile
( p_id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(20),
  sex enum('boy','girl'),
  PRIMARY KEY(p_id)
);

desc to describe about the table

mysql> desc tb_myprofile;
+-------+--------------------+------+-----+---------+----------------+
| Field | Type               | Null | Key | Default | Extra          |
+-------+--------------------+------+-----+---------+----------------+
| p_id  | int(11)            | NO   | PRI | NULL    | auto_increment | 
| name  | varchar(20)        | YES  |     | NULL    |                | 
| sex   | enum('boy','girl') | YES  |     | NULL    |                | 
+-------+--------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

rename table to allprofile

mysql> ALTER TABLE tb_myprofile RENAME TO allprofile;
Query OK, 0 rows affected (0.00 sec)

rename column p_id to ap_id

mysql> ALTER TABLE allprofile CHANGE p_id allp_id INT NOT NULL AUTO_INCREMENT;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc allprofile;
+---------+--------------------+------+-----+---------+----------------+
| Field   | Type               | Null | Key | Default | Extra          |
+---------+--------------------+------+-----+---------+----------------+
| allp_id | int(11)            | NO   | PRI | NULL    | auto_increment | 
| name    | varchar(20)        | YES  |     | NULL    |                | 
| sex     | enum('boy','girl') | YES  |     | NULL    |                | 
+---------+--------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

change column type but not the column name

mysql> ALTER TABLE allprofile CHANGE allp_id allp_id BIGINT NOT NULL AUTO_INCREMENT;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc allprofile;
+---------+--------------------+------+-----+---------+----------------+
| Field   | Type               | Null | Key | Default | Extra          |
+---------+--------------------+------+-----+---------+----------------+
| allp_id | bigint(20)         | NO   | PRI | NULL    | auto_increment | 
| name    | varchar(20)        | YES  |     | NULL    |                | 
| sex     | enum('boy','girl') | YES  |     | NULL    |                | 
+---------+--------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

add another column after a specific column

mysql> ALTER TABLE allprofile ADD COLUMN location VARCHAR(30) AFTER allp_id;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc allprofile;
+----------+--------------------+------+-----+---------+----------------+
| Field    | Type               | Null | Key | Default | Extra          |
+----------+--------------------+------+-----+---------+----------------+
| allp_id  | bigint(20)         | NO   | PRI | NULL    | auto_increment | 
| location | varchar(30)        | YES  |     | NULL    |                | 
| name     | varchar(20)        | YES  |     | NULL    |                | 
| sex      | enum('boy','girl') | YES  |     | NULL    |                | 
+----------+--------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

add multiple columns

mysql> ALTER TABLE allprofile ADD COLUMN description VARCHAR(30) AFTER name,
                              ADD COLUMN age INT AFTER location,
                              ADD position VARCHAR(30) NOT NULL AFTER description;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc allprofile;
+-------------+--------------------+------+-----+---------+----------------+
| Field       | Type               | Null | Key | Default | Extra          |
+-------------+--------------------+------+-----+---------+----------------+
| allp_id     | bigint(20)         | NO   | PRI | NULL    | auto_increment | 
| location    | varchar(30)        | YES  |     | NULL    |                | 
| age         | int(11)            | YES  |     | NULL    |                | 
| name        | varchar(20)        | YES  |     | NULL    |                | 
| description | varchar(30)        | YES  |     | NULL    |                | 
| position    | varchar(30)        | NO   |     | NULL    |                | 
| sex         | enum('boy','girl') | YES  |     | NULL    |                | 
+-------------+--------------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)

define default value

mysql> ALTER TABLE allprofile CHANGE age age INT DEFAULT 24;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc allprofile;
+-------------+--------------------+------+-----+---------+----------------+
| Field       | Type               | Null | Key | Default | Extra          |
+-------------+--------------------+------+-----+---------+----------------+
| allp_id     | bigint(20)         | NO   | PRI | NULL    | auto_increment | 
| location    | varchar(30)        | YES  |     | NULL    |                | 
| age         | int(11)            | YES  |     | 24      |                | 
| name        | varchar(20)        | YES  |     | NULL    |                | 
| description | varchar(30)        | YES  |     | NULL    |                | 
| position    | varchar(30)        | NO   |     | NULL    |                | 
| sex         | enum('boy','girl') | YES  |     | NULL    |                | 
+-------------+--------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

add unique to the column


mysql> ALTER TABLE allprofile ADD UNIQUE(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc allprofile;
+-------------+--------------------+------+-----+---------+----------------+
| Field       | Type               | Null | Key | Default | Extra          |
+-------------+--------------------+------+-----+---------+----------------+
| allp_id     | bigint(20)         | NO   | PRI | NULL    | auto_increment | 
| location    | varchar(30)        | YES  |     | NULL    |                | 
| age         | int(11)            | YES  |     | 24      |                | 
| name        | varchar(20)        | YES  | UNI | NULL    |                | 
| description | varchar(30)        | YES  |     | NULL    |                | 
| position    | varchar(30)        | NO   |     | NULL    |                | 
| sex         | enum('boy','girl') | YES  |     | NULL    |                | 
+-------------+--------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

delete a column


mysql> ALTER TABLE allprofile DROP position;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc allprofile;
+-------------+--------------------+------+-----+---------+----------------+
| Field       | Type               | Null | Key | Default | Extra          |
+-------------+--------------------+------+-----+---------+----------------+
| allp_id     | bigint(20)         | NO   | PRI | NULL    | auto_increment | 
| location    | varchar(30)        | YES  |     | NULL    |                | 
| age         | int(11)            | YES  |     | 24      |                | 
| name        | varchar(20)        | YES  | UNI | NULL    |                | 
| description | varchar(30)        | YES  |     | NULL    |                | 
| sex         | enum('boy','girl') | YES  |     | NULL    |                | 
+-------------+--------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

drop column, add new column, and change column type at once

mysql> ALTER TABLE allprofile DROP location,
    ->                        ADD address VARCHAR(20) NOT NULL,
    ->                        CHANGE sex sex SMALLINT DEFAULT 1;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc allprofile;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| allp_id     | bigint(20)  | NO   | PRI | NULL    | auto_increment | 
| age         | int(11)     | YES  |     | 24      |                | 
| name        | varchar(20) | YES  | UNI | NULL    |                | 
| description | varchar(30) | YES  |     | NULL    |                | 
| sex         | smallint(6) | YES  |     | 1       |                | 
| address     | varchar(20) | NO   |     | NULL    |                | 
+-------------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

add full-text search

mysql> ALTER TABLE allprofile ADD FULLTEXT(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

reorder table column

mysql> ALTER TABLE allprofile CHANGE sex sex smallint AFTER age;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc allprofile;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| allp_id     | bigint(20)  | NO   | PRI | NULL    | auto_increment | 
| age         | int(11)     | YES  |     | 24      |                | 
| sex         | smallint(6) | YES  |     | NULL    |                | 
| name        | varchar(20) | YES  | UNI | NULL    |                | 
| description | varchar(30) | YES  |     | NULL    |                | 
| address     | varchar(20) | NO   |     | NULL    |                | 
+-------------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

convert to a different character set

mysql> ALTER TABLE allprofile CONVERT TO CHARSET utf8;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

change the mysql table engine type

mysql> ALTER TABLE allprofile ENGINE = myisam;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Change the Unicode format (UTF-8) using Alter

You can use the ALTER to change the unicode format.

mysql> ALTER DATABASE moodle2 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Query OK, 1 row affected (0.00 sec)

mysql>

Search

MySQL ALTER

Contents

Create table

desc to describe about the table

rename table to allprofile

rename column p_id to ap_id

change column type but not the column name

add another column after a specific column

add multiple columns

define default value

add unique to the column

delete a column

drop column, add new column, and change column type at once

add full-text search

reorder table column

convert to a different character set

change the mysql table engine type

Change the Unicode format (UTF-8) using Alter

Navigation

Web

SQL

MISC

References

Personal tools

Namespaces

Variants

Views

Actions