ALTER TABLE nßm umo₧≥uje meni¥ Ütrukt·ru u₧ existuj·cej tabu╛ky. Okrem toho pomocou tohto prφkazu m⌠₧eme pridßva¥, maza¥ jednotlivΘ stσpce,
vytvßra¥ a maza¥ indexy, meni¥ typy a vlastnosti existuj·cich stσpcov (polo₧iek). Je dovolenΘ pomocou tohto prφkazu tie₧ premenovßva¥ polo₧ky
v tabu╛kßch a samozrejme i celΘ tabu╛ky.
Najsk⌠r si ukß₧me ako vypadß syntax prφkazu ALTER TABLE.
ALTER [IGNORE] TABLE tbl_name alter_specifikacia [, alter_spec ...]
alter_specifikacia:
ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
alebo ADD [COLUMN] (create_definition, create_definition,...)
alebo ADD INDEX [index_name] (index_col_name,...)
alebo ADD PRIMARY KEY (index_col_name,...)
alebo ADD UNIQUE [index_name] (index_col_name,...)
alebo ADD FULLTEXT [index_name] (index_col_name,...)
alebo ADD [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)
[reference_definition]
alebo ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
alebo CHANGE [COLUMN] old_col_name create_definition
alebo MODIFY [COLUMN] create_definition
alebo DROP [COLUMN] col_name
alebo DROP PRIMARY KEY
alebo DROP INDEX index_name
alebo RENAME [TO] new_tbl_name
alebo ORDER BY col
alebo table_options
Ukß₧me si teraz na reßlnych prφkladoch pou₧itie ALTER TABLE a zaΦnime s vytvorenφm jednoduchej tabu╛ky, na ktorej si ukß₧eme vyu₧itie tohto
prφkazu.
mysql> CREATE TABLE table_A (meno CHAR(10), vek INTEGER);
Query OK, 0 rows affected (0.04 sec)
Tabu╛ka bude potom vypada¥ nasledovne:
mysql> DESCRIBE table_A;
+-------+---------+------+-----+---------+-----+---------------------+
| Field | Type | Null | Key | Default |Extra| Privileges |
+-------+---------+------+-----+---------+-----+---------------------+
| meno | char(10)| YES | | NULL | | select,insert,update|
| vek | int(11) | YES | | NULL | | select,insert,update|
+-------+---------+------+-----+---------+-----+---------------------+
2 rows in set (0.00 sec)
V prvom rade si sk·sme premenova¥ tabu╛ku table_A na tabu╛ku table_B:
mysql> ALTER TABLE table_A RENAME table_B;
Teraz sa sk·sme pohra¥ priamo s polo₧kami tabu╛ky. St╛pec "meno" premenujeme na "prve_meno" a zmenime jeho typ z CHAR(10) na CHAR(22). Okrem
toho zmenime typ polo₧ky "vek" na TINYINT NOT NULL bez zmeny nßzvu polo₧ky.
mysql> ALTER TABLE table_B MODIFY vek TINYINT NOT NULL,
CHANGE meno prve_meno CHAR(22);
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
Sk·sme si teraz prida¥ do naÜej tabu╛ky "table_B" jeden nov² st┼pec s nßzvom "datum_nar" a typom DATE.
mysql> ALTER TABLE table_B ADD datum_nar DATE;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
Pridanie prφznaku "PRIMARY KEY" na polo₧ku "prve_meno" urobime nasledovne (pred t²m musφme samozrejme zmeni¥ typ polo₧ky na NOT NULL, preto₧e
inak jej nepridßme prφznak primßrnΘho k╛·Φa):
mysql> ALTER TABLE table_B MODIFY prve_meno CHAR(22) NOT NULL;
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0
A teraz u₧ m⌠₧eme prida¥ prφznak primßrneho k╛·Φa na polo₧ku "prve_meno":
mysql> ALTER TABLE table_B ADD PRIMARY KEY (prve_meno);
Na zßver si sk·sme pomocou ALTER TABLE odobra¥ z tabu╛ky "table_B" stσpec "datum_nar":
mysql> ALTER TABLE table_B DROP COLUMN datum_nar;
To by sme teda mali, naÜa v²slednß tabu╛ka mß len mßlo spoloΦnΘ s naÜou p⌠vodnou tabu╛kou, ktor· sme si vytvorili na zaΦiatku tohto Φlßnku
:-)
mysql> DESCRIBE table_B;
+-----------+-----------+----+---+--------+------+---------------------+
| Field | Type |Null|Key| Default|Extra | Privileges |
+-----------+-----------+----+---+--------+------+---------------------+
| prve_meno | char(22) | |PRI| | | select,insert,update|
| vek | tinyint(4)| | | 0 | | select,insert,update|
+-----------+-----------+----+---+--------+------+---------------------+
2 rows in set (0.00 sec)
