Naknadne izmene struktura tabela (Any subsequent changes to the structure of table)

Osim azuriranja redova, ponekad cete zeleti da izmenite strukturu tabela u bazi podataka. To mozete postici uz pomoc iskaza alter table. 

Sintaksa:

alter table [ignore] tabela izmena [, izmena ...]

Napomena: u ANSI SQL-u je moguca samo jedna izmena po iskazu alter table, dok je u MySQL-u mozete da zadate neogranicen broj izmena u istom iskazu. 

Ako upotrebite odredbu ignore, a izmena koju uvodite duplira neku vrednost u koloni primarnog kljuca, red u kome se nalazi prva takva vrednost ostace ce sacuvan u izmenjenoj tabeli, dok se drugi takvi redovi brisu. Ako ne zadate odredbu ignore, izmena nece biti uneta u tabelu.

U sledecem clanku je tabela - izmene koje omogucava iskaz alter table. 

Ako u koloni ne mogu da stanu podaci koje treba da sadrzi, npr., predvideli ste da u tabeli kupci imena kupaca budu max. duzine 50 znakova. 


Resenje: izmenite tip podatka u koloni tako da ona prihvata imena max. duzine 70 znakova.

alter table kupci

modify ime char(70) not null;

Ovaj fajl sacuvajte kao naknadne-izmene.sql.

Ako zelite tabeli da dodate jos jednu kolonu, npr., PDV koji se obracunava na ukupan iznos porudzbine, a koji treba da cuvate u zasebnoj koloni. 

Resenje: tabeli porudzbine dodacete kolonu pdv. 

alter table porudzbine

add pdv float(6,2) after iznos;

1. Pokrenite XAMPP. Kliknite na Admin u liniji MySQL-a ili ukucajte u address bar http://localhost/phpmyadmin/
2. Sa leve strane izberite bazu podataka volimmotore. 
3. Izaberite karticu SQL i unesite kod iz fajla naknadne-izmene.sql.

4. Klik na dugme "Go". 

5. Kliknite na karticu SQL i dodajte drugi primer. Klik na dugme "Go".

In addition to updating the rows, sometimes you may wish to change the structure of the tables in the database. You can accomplish this with the help of the statement  ALTER TABLE.

Syntax:

ALTER TABLE [IGNORE] table changes [, change ...]

Note: in ANSI SQL is possible only one change to the statement ALTER TABLE, while in MySQL you may specify an unlimited number of changes in the same statement.

If you use a clause IGNORE, and changes you introduce duplicates a value in the primary key column, the first row with such value will remain intact in the modified table, while others such rows will be deleted. If you do not specify the clause IGNORE, changes will not be entered in the table.

In next article is a table - changes for statement ALTER TABLE.

If the column can not fit the data which should contain, for example, you predicted that in the table the names of customers are max. length of 50 characters.

Solution: change the data type in the column so that it accepts the name max. length of 70 characters.

ALTER TABLE customers

modify name CHAR(70) NOT NULL;

Save this file as subsequent-changes.sql.

If you want to add another column to the table, eg., tax, which is calculated on the total amount of order, which should be kept in a separate column.

Solution: to table orders add column tax.

ALTER TABLE orders

ADD tax FLOAT(6,2) AFTER amount;

1. Start XAMPP. Click on the Admin in line of MySQL or type in the address bar http://localhost/phpmyadmin/
2. On the left side, select a database lovemotobikes.
3. Select the tab SQL and enter the code from the file subsequent-changes.sql.

4. Click on the "Go".

5. Click on tab SQL, add the code from 2nd example and click "Go".