π‘ALTERπ‘
π‘ALTERλ?
ALTER λ¬Έμ SQLμμ λ°μ΄ν°λ² μ΄μ€μ ꡬ쑰λ₯Ό λ³κ²½νλ λ° μ¬μ©λλ λͺ
λ Ήλ¬Έμ
λλ€. λ°μ΄ν°λ² μ΄μ€ ꡬ쑰λ ν
μ΄λΈ, μ΄, μ μ½ μ‘°κ±΄ λ±κ³Ό κ°μ μμλ‘ κ΅¬μ±λ©λλ€. ALTER λ¬Έμ μ¬μ©νλ©΄ μ΄λ¬ν ꡬ쑰λ₯Ό μμ ν μ μμ΅λλ€.
κ°μ₯ νν μ¬μ© μ¬λ‘ μ€ νλλ μλ‘μ΄ μ΄μ ν
μ΄λΈμ μΆκ°νλ κ²μ
λλ€. μλ₯Ό λ€μ΄, μλ‘μ΄ μ 보λ₯Ό μ μ₯ν΄μΌ ν λ κΈ°μ‘΄ ν
μ΄λΈμ μλ‘μ΄ μ΄μ μΆκ°ν μ μμ΅λλ€. λλ λ°μ΄ν° μ νμ λ³κ²½νκ±°λ κΈ°μ‘΄ μ΄μ μ μ½ μ‘°κ±΄μ μμ νλ λ±μ μμ
λ ν μ μμ΅λλ€.
λ λ€λ₯Έ μ¬μ© μ¬λ‘λ ν
μ΄λΈμ μ΄λ¦μ λ³κ²½νκ±°λ μ μ½ μ‘°κ±΄μ μΆκ° λλ μ κ±°νλ κ²μ
λλ€. μλ₯Ό λ€μ΄, λ μ΄μ νμνμ§ μμ μ μ½ μ‘°κ±΄μ μ κ±°νκ±°λ μλ‘μ΄ μ μ½ μ‘°κ±΄μ μΆκ°νμ¬ λ°μ΄ν°μ 무결μ±μ 보μ₯ν μ μμ΅λλ€.
λ°μ΄ν°λ² μ΄μ€ ꡬ쑰λ₯Ό λ³κ²½νλ κ²μ μ£Όμ κΉκ² μ²λ¦¬ν΄μΌ ν©λλ€. ALTER λ¬Έμ μ¬μ©ν λλ λ°μ΄ν°μ 무결μ±κ³Ό μΌκ΄μ±μ μ μ§νλ κ²μ΄ μ€μν©λλ€. μλͺ»λ λ³κ²½μ λ°μ΄ν°λ² μ΄μ€μ μΌκ΄μ±μ κΉ¨λ¨λ¦΄ μ μμΌλ―λ‘ μ μ€νκ² μ²λ¦¬ν΄μΌ ν©λλ€.
μμ½νμλ©΄, ALTER λ¬Έμ λ°μ΄ν°λ² μ΄μ€ ꡬ쑰λ₯Ό λ³κ²½νκ³ μ μ§ κ΄λ¦¬νλ λ° μ¬μ©λλ κ°λ ₯ν SQL λͺ
λ Ήλ¬Έμ
λλ€. λ°μ΄ν°λ² μ΄μ€λ₯Ό μ μ°νκ² μ‘°μ νκ³ λ³κ²½ν΄μΌ ν λ μ¬μ©λ©λλ€.
π‘νλ μΆκ°νκΈ°
1
| ALTER TABLE [ν
μ΄λΈ μ΄λ¦] ADD [μΆκ°ν νλλͺ
] AFTER [νλ μμΉ]
|
π‘λ©€λ² ν
μ΄λΈμ νΈλν° λ²νΈ νλ μΆκ°
1
2
3
| mysql> ALTER TABLE members ADD youPhone varchar(20) NOT NULL;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
|
π‘λ©€λ² ν
μ΄λΈμ μ£Όμλ₯Ό μμΌ λ€μ μΆκ°
1
2
3
| mysql> ALTER TABLE members ADD youAddress varchar(100) NOT NULL AFTER youBirth;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
|
π‘νλ μμ νκΈ°
1
| ALTER TABLE [ν
μ΄λΈ μ΄λ¦] MODIFY [μμ ν νλλͺ
]
|
π‘νΈλν° νλ μ ν μμ νκΈ°
1
2
3
| mysql> ALTER TABLE members MODIFY youPhone int(20) NOT NULL;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
|
π‘νλ μμ νκΈ°
1
| ALTER TABLE [ν
μ΄λΈ μ΄λ¦] DROP [μμ ν νλλͺ
]
|
π‘μ£Όμ νλ μμ νκΈ°
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| mysql> ALTER TABLE members DROP youAddress;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc members;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| memberID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| youName | varchar(10) | NO | | NULL | |
| youPass | varchar(10) | NO | | NULL | |
| youAge | int(5) | NO | | NULL | |
| youBirth | int(5) | NO | | NULL | |
| regTime | int(30) | NO | | NULL | |
+----------+------------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)
|
π‘ADD COLUMN: μλ‘μ΄ μ΄μ ν
μ΄λΈμ μΆκ°ν λ μ¬μ©λ©λλ€.
1
| ALTER TABLE table_name ADD column_name data_type;
|
π‘ALTER COLUMN: μ΄μ λ°μ΄ν° μ νμ μμ νκ±°λ ν¬κΈ°λ₯Ό μ‘°μ ν λ μ¬μ©λ©λλ€.
1
| ALTER TABLE table_name ALTER COLUMN column_name new_data_type;
|
π‘DROP COLUMN: λΆνμν μ΄μ ν
μ΄λΈμμ μμ ν λ μ¬μ©λ©λλ€.
1
| ALTER TABLE table_name DROP COLUMN column_name;
|
π‘RENAME COLUMN: μ΄μ μ΄λ¦μ λ³κ²½ν λ μ¬μ©λ©λλ€.
1
| ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;
|
π‘ADD INDEX: μλ‘μ΄ μΈλ±μ€λ₯Ό μ΄μ μΆκ°ν λ μ¬μ©λ©λλ€.
1
| ALTER TABLE table_name ADD INDEX index_name (column_name);
|
π‘DROP INDEX: ν
μ΄λΈμμ μΈλ±μ€λ₯Ό μμ ν λ μ¬μ©λ©λλ€.
1
| ALTER TABLE table_name DROP INDEX index_name;
|
π‘RENAME TABLE: ν
μ΄λΈμ μ΄λ¦μ λ³κ²½ν©λλ€.
1
| ALTER TABLE old_table_name RENAME TO new_table_name;
|
π‘ADD CONSTRAINT: μλ‘μ΄ μ μ½ μ‘°κ±΄μ ν
μ΄λΈμ μΆκ°ν©λλ€.
1
| ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_definition;
|
π‘DROP CONSTRAINT: ν
μ΄λΈμμ μ μ½ μ‘°κ±΄μ μμ ν©λλ€.
1
| ALTER TABLE table_name DROP CONSTRAINT constraint_name;
|
π‘ENABLE/DISABLE TRIGGER: νΈλ¦¬κ±°λ₯Ό νμ±ν λλ λΉνμ±νν©λλ€.
1
2
| ALTER TABLE table_name ENABLE TRIGGER trigger_name;
ALTER TABLE table_name DISABLE TRIGGER trigger_name;
|
π‘MODIFY COLUMN: μ΄μ μμ±μ λ³κ²½ν©λλ€. (μ£Όλ‘ λ°μ΄ν° μ ν λ° NULL μ μ½ μ‘°κ±΄ λ³κ²½)
1
| ALTER TABLE table_name MODIFY COLUMN column_name new_data_type;
|