mysql테이블 속성 및 구조변경 명령어

1. 데이타베이스 생성

mysql 접속 후, create database xxxx 도 가능하나 아래와 같은 방법도 있음

[root@mycent ~]# mysqladmin -uroot -ptkdlqj7 create abcde

 

 

2. 테이블 생성

[root@mycent ~]# mysql -u root -p abcde
Enter password: 
mysql> create table test_table(
    -> uid mediumint(4) unsigned DEFAULT '0' NOT NULL,
    -> name varchar(12) DEFAULT "" NOT NULL,
    -> email varchar(20) DEFAULT "" NOT NULL,
    -> PRIMARY KEY(uid)
    -> );
Query OK, 0 rows affected (0.14 sec)

mysql> show tables;
+-----------------+
| Tables_in_abcde |
+-----------------+
| test_table      | 
+-----------------+
1 row in set (0.01 sec)

mysql> desc test_table;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| uid   | mediumint(4) unsigned | NO   | PRI | 0       |       | 
| name  | varchar(12)              | NO   |        |         |       | 
| email | varchar(20)               | NO   |        |         |       | 
+-------+-----------------------+------+-----+---------+-------+

 

3. 기존 테이블에 새로운 필드 추가

mysql> alter table add column homepage varchar(30);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'add column homepage varchar(30)' at line 1
mysql> alter table test_table add column homepage varchar(30);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test_table;
+----------+-----------------------+------+-----+---------+-------+
| Field    | Type                  | Null | Key | Default | Extra |
+----------+-----------------------+------+-----+---------+-------+
| uid      | mediumint(4) unsigned | NO   | PRI | 0          |       | 
| name     | varchar(12)              | NO   |        |            |       | 
| email    | varchar(20)               | NO   |        |            |       | 
| homepage | varchar(30)           | YES  |       | NULL    |       | 
+----------+-----------------------+------+-----+---------+-------+

 

4. 기존의 필드 속성명 변경

mysql> alter table test_table change column homepage website varchar(50);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test_table;
+---------+-----------------------+------+-----+---------+-------+
| Field   | Type                  | Null | Key | Default | Extra |
+---------+-----------------------+------+-----+---------+-------+
| uid     | mediumint(4) unsigned | NO   | PRI | 0       |       | 
| name    | varchar(12)              | NO   |        |         |       | 
| email   | varchar(20)               | NO   |        |         |       | 
| website | varchar(50)             | YES  |       | NULL    |       | 
+---------+-----------------------+------+-----+---------+-------+

 

5. 기존의 필드 삭제

mysql> alter table test_table drop column website;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test_table;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| uid   | mediumint(4) unsigned | NO   | PRI | 0       |       | 
| name  | varchar(12)              | NO   |     |         |       | 
| email | varchar(20)               | NO   |     |         |       | 
+-------+-----------------------+------+-----+---------+-------+

 

6. 기존의 테이블 삭제

mysql> drop table test_table;

[출처] mysql테이블 속성 및 구조변경 명령어 (넷 ACK) |작성자 유수