Post

MYSQL

πŸ’‘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;
This post is licensed under CC BY 4.0 by the author.