MySQL- 7. MySQL 테이블 수정 및 칼럼 추가

MySQL- 7. MySQL 테이블 수정 및 칼럼 추가

 

 

 

1. 테이블 수정

 

mysql> alter table ccna modify UserID varchar(30);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

 

 

mysql> alter table ccna modify UserPass varchar(30);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

 

 

mysql> alter table ccna modify UserNAME varchar(30);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

 

mysql> desc ccna;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| UserID   | varchar(30) | YES  |     | NULL    |       |
| UserPass | varchar(30) | YES  |     | NULL    |       |
| UserNAME | varchar(30) | YES  |     | NULL    |       |
| age      | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

 

 

2. 컬럼 맨 앞에 추가

 

mysql> alter table ccna add Birthday datetime first;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

 

 

mysql> desc ccna;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| Birthday | datetime    | YES  |     | NULL    |       |
| UserID   | varchar(30) | YES  |     | NULL    |       |
| UserPass | varchar(30) | YES  |     | NULL    |       |
| UserNAME | varchar(30) | YES  |     | NULL    |       |
| age      | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

 

 

3. 컬럼 자율 추가

 

mysql> alter table ccna add PhoneNumber int(20) after UserName;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

 

 

mysql> select * from ccna;
+----------+--------+----------+--------------+-------------+------+
| Birthday | UserID | UserPass | UserNAME     | PhoneNumber | age  |
+----------+--------+----------+--------------+-------------+------+
| NULL     | ccna1  | ccna1111 | kim jung woo |        NULL |   23 |
| NULL     | ccna2  | ccna2222 | kim jung woo |        NULL |   26 |
| NULL     | ccna3  | ccna3333 | kim jung woo |        NULL |   29 |
+----------+--------+----------+--------------+-------------+------+
3 rows in set (0.00 sec)

 

 

4. 컬럼 순서 변경

 

mysql> alter table ccna modify Birthday datetime after PhoneNumber;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

 

 

mysql> select * from ccna;
+--------+----------+--------------+-------------+----------+------+
| UserID | UserPass | UserNAME     | PhoneNumber | Birthday | age  |
+--------+----------+--------------+-------------+----------+------+
| ccna1  | ccna1111 | kim jung woo |        NULL | NULL     |   23 |
| ccna2  | ccna2222 | kim jung woo |        NULL | NULL     |   26 |
| ccna3  | ccna3333 | kim jung woo |        NULL | NULL     |   29 |
+--------+----------+--------------+-------------+----------+------+
3 rows in set (0.00 sec)

 

 

5. 컬럼 이름 및 Type 수정

 

mysql> alter table ccna change Birthday Birthdate date;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

 

 

mysql> select * from ccna;
+--------+----------+--------------+-------------+-----------+------+
| UserID | UserPass | UserNAME     | PhoneNumner | Birthdate | age  |
+--------+----------+--------------+-------------+-----------+------+
| ccna1  | ccna1111 | kim jung woo |        NULL | NULL      |   23 |
| ccna2  | ccna2222 | kim jung woo |        NULL | NULL      |   26 |
| ccna3  | ccna3333 | kim jung woo |        NULL | NULL      |   29 |
+--------+----------+--------------+-------------+-----------+------+

 

 

6. 컬럼 삭제

 

mysql> alter table ccna drop Birthdate;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

 

 

mysql> select * from ccna;
+--------+----------+--------------+-------------+------+
| UserID | UserPass | UserNAME     | PhoneNumner | age  |
+--------+----------+--------------+-------------+------+
| ccna1  | ccna1111 | kim jung woo |        NULL |   23 |
| ccna2  | ccna2222 | kim jung woo |        NULL |   26 |
| ccna3  | ccna3333 | kim jung woo |        NULL |   29 |
+--------+----------+--------------+-------------+------+
3 rows in set (0.00 sec)

 

mysql> desc ccna;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| UserID      | varchar(30) | YES  |     | NULL    |       |
| UserPass    | varchar(30) | YES  |     | NULL    |       |
| UserNAME    | varchar(30) | YES  |     | NULL    |       |
| PhoneNumner | int(20)     | YES  |     | NULL    |       |
| age         | int(11)     | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)