MySQL ALTER Tutorial

MySQL ALTER TABLE komandu smo koristili par puta, ali sada cemo je posmatrati u svetlu dodavanja, brisanja i promene kolona u tabeli.

Dodavanje nove kolone u tabelu ide ovako:

ALTER TABLE ime_tabele
ADD naziv_kolone tip_podatka

Brisanje kolone iz tabele:

ALTER TABLE ime_tabele
DROP COLUMN naziv_kolone

Promena tipa podataka za kolonu:

ALTER TABLE ime_tabele
ALTER COLUMN naziv_kolone tip_podatka

Primer SQL dodavanja Nove Kolone u Postojecu Tabelu

Koristicemo tabelu "prodavnice", sledeceg sadrzaja:

mysql> SELECT * FROM prodavnice;
+------+-----------+------------+-----------+-------------+------+
| p_id | gradovi   | prodavnice | proizvod  | raspolozivo | cena |
+------+-----------+------------+-----------+-------------+------+
|    1 | Beograd   | prbg1      | Kafa      |         121 |  150 |
|    2 | Beograd   | prbg2      | Cigare    |          50 |  300 |
|    3 | Subotica  | prsub1     | Hleb      |          43 |   45 |
|    4 | Nis       | prnis1     | Hleb      |          23 |   55 |
|    5 | Kraljevo  | prkrl1     | Pavlaka   |         155 |   50 |
|    6 | Uzice     | pruzi1     | Mleko     |          68 |   90 |
|    7 | Majdanpek | prmaj1     | Mleko     |          91 |   87 |
|    8 | Bor       | prbor1     | Pavlaka   |         155 |   50 |
|    9 | Kikinda   | prkik1     | Ajzaklija |          38 |  650 |
|   10 | Obrenovac | prob1      | Jabuke    |          35 |  120 |
|   11 | Smederevo | prsm1      | Ajvar     |          28 |  270 |
|   12 | Zrenjanin | przr1      | Somun     |          10 |   59 |
|   13 | Jagodina  | prjg1      | Bakalar   |          29 |  400 |
+------+-----------+------------+-----------+-------------+------+
13 rows in set (0.00 sec)

mysql>

Nakon sledece komande tabela "prodavnice" bi trebala da ima kolonu "menadzer":

ALTER TABLE prodavnice
ADD menadzer VARCHAR(255)

mysql> ALTER TABLE prodavnice
    -> ADD menadzer VARCHAR(255);
Query OK, 13 rows affected (0.25 sec)
Records: 13  Duplicates: 0  Warnings: 0

mysql>

mysql> SELECT * FROM prodavnice;
+------+-----------+------------+-----------+-------------+------+----------+
| p_id | gradovi   | prodavnice | proizvod  | raspolozivo | cena | menadzer |
+------+-----------+------------+-----------+-------------+------+----------+
|    1 | Beograd   | prbg1      | Kafa      |         121 |  150 | NULL     |
|    2 | Beograd   | prbg2      | Cigare    |          50 |  300 | NULL     |
|    3 | Subotica  | prsub1     | Hleb      |          43 |   45 | NULL     |
|    4 | Nis       | prnis1     | Hleb      |          23 |   55 | NULL     |
|    5 | Kraljevo  | prkrl1     | Pavlaka   |         155 |   50 | NULL     |
|    6 | Uzice     | pruzi1     | Mleko     |          68 |   90 | NULL     |
|    7 | Majdanpek | prmaj1     | Mleko     |          91 |   87 | NULL     |
|    8 | Bor       | prbor1     | Pavlaka   |         155 |   50 | NULL     |
|    9 | Kikinda   | prkik1     | Ajzaklija |          38 |  650 | NULL     |
|   10 | Obrenovac | prob1      | Jabuke    |          35 |  120 | NULL     |
|   11 | Smederevo | prsm1      | Ajvar     |          28 |  270 | NULL     |
|   12 | Zrenjanin | przr1      | Somun     |          10 |   59 | NULL     |
|   13 | Jagodina  | prjg1      | Bakalar   |          29 |  400 | NULL     |
+------+-----------+------------+-----------+-------------+------+----------+
13 rows in set (0.00 sec)

mysql>