1.실습-칼럼추가와 삭제
1).칼럼추가
mysql> alter table orders2 add column many int not null;
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from orders2;
+---------+------------+--------+---------------------+------+
I OrderID I CustomerID I Amount I oDate I many I
+---------+------------+--------+---------------------+------+
I 1 I 2 I 12500 I 2005-09-01 12:00:00 I 0 I
I 2 I 3 I 35000 I 2005-09-01 13:00:00 I 0 I
I 3 I 5 I 23000 I 2005-09-03 17:00:00 I 0 I
I 4 I 6 I 42500 I 2005-09-06 19:00:00 I 0 I
I 5 I 2 I 33000 I 2005-09-13 13:00:00 I 0 I
+---------+------------+--------+---------------------+------+
5 rows in set (0.00 sec)
2). 칼럼 삭제
mysql> alter table orders2 drop column many;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> desc orders2;
+------------+------------------+------+-----+---------+-------+
I Field I Type I Null I Key I Default I Extra I
+------------+------------------+------+-----+---------+-------+
I OrderID I int(10) unsigned I I I 0 I I
I CustomerID I int(10) unsigned I I I 0 I I
I Amount I int(11) I I I 0 I I
I oDate I datetime I YES I I NULL I I
+------------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
3). 열들의 앞부분에 새컬럼 추가
mysql> alter table orders2 add column many int not null first;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> desc orders2;
+------------+------------------+------+-----+---------+-------+
I Field I Type I Null I Key I Default I Extra I
+------------+------------------+------+-----+---------+-------+
I many I int(11) I I I 0 I I
I OrderID I int(10) unsigned I I I 0 I I
I CustomerID I int(10) unsigned I I I 0 I I
I Amount I int(11) I I I 0 I I
I oDate I datetime I YES I I NULL I I
+------------+------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
4). 특정 칼럼뒤에 새 칼럼 삽입
mysql> alter table orders2 add column many int not null after Amount;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> desc orders2;
+------------+------------------+------+-----+---------+-------+
I Field I Type I Null I Key I Default I Extra I
+------------+------------------+------+-----+---------+-------+
I OrderID I int(10) unsigned I I I 0 I I
I CustomerID I int(10) unsigned I I I 0 I I
I Amount I int(11) I I I 0 I I
I many I int(11) I I I 0 I I
I oDate I datetime I YES I I NULL I I
+------------+------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
=======================================================================================================
1. 칼럼 구조변경
mysql> alter table orders2 modify column many smallint;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> desc orders2;
+------------+------------------+------+-----+---------+-------+
I Field I Type I Null I Key I Default I Extra I
+------------+------------------+------+-----+---------+-------+
I OrderID I int(10) unsigned I I I 0 I I
I CustomerID I int(10) unsigned I I I 0 I I
I Amount I int(11) I I I 0 I I
I many I smallint(6) I YES I I NULL I I
I oDate I datetime I YES I I NULL I I
+------------+------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
2). 칼럼의 이름과 자료형을 변경함.
mysql> alter table orders2 change column many mmany char(10);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> desc orders2;
+------------+------------------+------+-----+---------+-------+
I Field I Type I Null I Key I Default I Extra I
+------------+------------------+------+-----+---------+-------+
I OrderID I int(10) unsigned I I I 0 I I
I CustomerID I int(10) unsigned I I I 0 I I
I Amount I int(11) I I I 0 I I
I mmany I char(10) I YES I I NULL I I
I oDate I datetime I YES I I NULL I I
+------------+------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
3). 기본키 추가
mysql> alter table orders2 add primary key(OrderID, CustomerID);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> desc orders2;
+------------+------------------+------+-----+---------+-------+
I Field I Type I Null I Key I Default I Extra I
+------------+------------------+------+-----+---------+-------+
I OrderID I int(10) unsigned I I PRI I 0 I I
I CustomerID I int(10) unsigned I I PRI I 0 I I
I Amount I int(11) I I I 0 I I
I mmany I char(10) I YES I I NULL I I
I oDate I datetime I YES I I NULL I I
+------------+------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
4) 기본키 삭제
mysql> alter table orders2 drop primary key;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> desc orders2;
+------------+------------------+------+-----+---------+-------+
I Field I Type I Null I Key I Default I Extra I
+------------+------------------+------+-----+---------+-------+
I OrderID I int(10) unsigned I I I 0 I I
I CustomerID I int(10) unsigned I I I 0 I I
I Amount I int(11) I I I 0 I I
I mmany I char(10) I YES I I NULL I I
I oDate I datetime I YES I I NULL I I
+------------+------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
5). 인덱스 만들기
mysql> alter table orders2 add index order_idx (CustomerID, OrderID);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> desc orders2;
+------------+------------------+------+-----+---------+-------+
I Field I Type I Null I Key I Default I Extra I
+------------+------------------+------+-----+---------+-------+
I OrderID I int(10) unsigned I I I 0 I I
I CustomerID I int(10) unsigned I I MUL I 0 I I
I Amount I int(11) I I I 0 I I
I mmany I char(10) I YES I I NULL I I
I oDate I datetime I YES I I NULL I I
+------------+------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
인덱스 확인
mysql> show index from orders2;
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
I Table I Non_unique I Key_name I Seq_in_index I Column_name I Collation I Cardinality I Sub_part I Packed I Null I Index_type I Comment I
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
I orders2 I 1 I order_idx I 1 I CustomerID I A I NULL I NULL I NULL I I BTREE I I
I orders2 I 1 I order_idx I 2 I OrderID I A I NULL I NULL I NULL I I BTREE I I
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)
6). 인덱스 삭제
mysql> alter table orders2 drop index order_idx;
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> desc orders2;
+------------+------------------+------+-----+---------+-------+
I Field I Type I Null I Key I Default I Extra I
+------------+------------------+------+-----+---------+-------+
I OrderID I int(10) unsigned I I I 0 I I
I CustomerID I int(10) unsigned I I I 0 I I
I Amount I int(11) I I I 0 I I
I mmany I char(10) I YES I I NULL I I
I oDate I datetime I YES I I NULL I I
+------------+------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
7.테이블 이름 바꾸기
mysql> alter table orders2 RENAME AS orderTest;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
|
|
|
△