Na začátku samotného návrhu většinou nemáme jasnou představu o budoucí podobě tabulek a jejich návazností. Někdy je dokonce zapotřebí upravit existující tabulku dost podstatně včetně názvů sloupců a typu proměnných. Toto všechno je možné provádět a nenechat kámen na kameni.
Základním příkazem, se kterým se budeme setkávat, je ALTER. Jeho možnosti jsou natolik široké, že úplný popis by vydal alespoň na dva články. Úplné znění syntaxe najdete v manuálu k MySQL. Záměrně říkám MySQL, poněvadž u jiných serverů se syntaxe může mírně lišit, základy jsou ale stejné.
Na konci minulého dílu jsme měli tabulku s několika lidmi. Doufám že jste ji nesmazali, budeme na ní provádět experimenty. Měla by vypadat asi nějak takto:
+-------------+------------+-------+---------+-------------+ | rc | prijmeni | jmeno | pohlavi | telefon | +-------------+------------+-------+---------+-------------+ | 790303/0041 | Vonásek | Alois | m | 0611-585555 | | 250808/0087 | Dlabačka | Karel | m | 02-88888888 | | 480202/5895 | Houžvička | Karel | m | 0971-123456 | | 795331/0589 | Klokánková | Jana | z | 0600-784546 | +-------------+------------+-------+---------+-------------+ -------------+----------+-------+ ulice | mesto | psc | -------------+----------+-------+ Ružová 15 | Praha 1 | 11000 | Kolínská 1 | Praha 8 | 18000 | Jabloňová 8 | Praha 10 | 10100 | Školská 487 | Praha 2 | 12000 | -------------+----------+-------+
Poslední dva občany jsem ještě přidal, ženu kvůli tomu, aby se na mě nesesypaly feministky :-). Zkusme do tabulky přidat sloupec mobil. Jeho význam je zřejmý. Proměnná bude typu CHAR (nebo VARCHAR, výběr ponechám na Vás) z důvodu toho, že číslo na mobilu lze vytočit i jako kombinaci znaků. Dále víme, že sloupec může být prázdný, ale my chceme, aby se při neexistenci mobilu vypisovalo „není“. Někdo přece ještě mobilní není (jako já). Všechno shrneme do jednoho příkazového řádku:
ALTER TABLE lide ADD COLUMN mobil varchar(12) default „není“;
+-------------+------------+-------+---------+ | rc | prijmeni | jmeno | pohlavi | +-------------+------------+-------+---------+ | 790303/0041 | Vonásek | Alois | m | | 250808/0087 | Dlabačka | Karel | m | | 480202/5895 | Houžvička | Karel | m | | 795331/0589 | Klokánková | Jana | z | +-------------+------------+-------+---------+ -------------+-------------+----------+-------+-------+ telefon | ulice | mesto | psc | mobil | -------------+-------------+----------+-------+-------+ 0611-585555 | Ružová 15 | Praha 1 | 11000 | není | 02-88888888 | Kolínská 1 | Praha 8 | 18000 | není | 0971-123456 | Jabloňová 8 | Praha 10 | 10100 | není | 0600-784546 | Školská 487 | Praha 2 | 12000 | není | -------------+-------------+----------+-------+-------+
Jak vidíme, povedlo se. Defaultní neboli výchozí hodnotu můžeme uvést u každého sloupce, datový typ výchozí hodnoty musí být stejný jako datový typ sloupce, což je asi logické. Pokud máme takto přidaný sloupec, můžeme do něj začít podle libosti vkládat čísla mobilů. Jak to jsme si již ukazovali, ale pro připomenutí příkazem UPDATE s podmínkou pokud známe rodné číslo (hmmm asi ne):
UPDATE lide SET mobil=„0604–555555“ where rc=„790303/0041“;
a nebo pokud víme jméno a alespoň kus příjmení či město, v němž osoba bydlí:
UPDATE lide SET mobil=„0604–555555“ where jmeno like „A%“ and prijmeni like „V%“ and mesto like „Praha 1“;
Ještě snad jen k podmínce. Pokud nevíme, jaké přesně má ona osoba příjmení, použijeme „prijmeni like neco“. Přeložíme-li si to z angličtiny, je to „příjmení vypadá jako …“. A přesně to také podmínka dělá. Jistě jste si už každý všiml, že DML a DDL je slovosledem velice podobný anglické gramatice. Výsledný řádek tabulky pak vypadá takto:
+-------------+------------+-------+---------+ | rc | prijmeni | jmeno | pohlavi | +-------------+------------+-------+---------+ | 790303/0041 | Vonásek | Alois | m | +-------------+------------+-------+---------+ -------------+-------------+----------+-------+-------------+ telefon | ulice | mesto | psc | mobil | -------------+-------------+----------+-------+-------------+ 0611-585555 | Ružová 15 | Praha 1 | 11000 | 0604-555555 | -------------+-------------+----------+-------+-------------+
A co když náhle zjistíme, že máme telefonní čísla patřící jedné osobě, identifikované podle rodného čísla, uvedeny v jiné tabulce (číselníku), skládajícího se pouze ze třech sloupců. Jimi jsou sloupce rodné_číslo, mobil a pevná. Z naší rozsáhlé tabulky můžeme vypustit sloupce telefon a mobil. Jsou zbytečné a mohly by díky nim vznikat dohady a problémy, pokud by v číselníku bylo jiné číslo než v hlavní tabulce. Vytvořme nyní číselník tak, abychom nepřišli o údaje. K tomu založíme standardním způsobem tabulku telefony:
CREATE TABLE telefony (rc varchar(11) not null unique, mobil varchar(11), pevna varchar(25));
Tabulka je připravena. Rodné číslo bude muset být nenulové a unikátní. Ale jak nyní vložit hodnoty z tabulky lidé do tabulky telefony? SQL příkazy se dají hromadit jeden do druhého. Použijeme standardní příkaz INSERT INTO:
INSERT INTO telefony SELECT rc,mobil,telefon FROM lide;
Do tabulky telefony se nám vyberou hodnoty ve sloupcích uvedených za SELECT. Šetří to nejen čas, ale i práci. Mohli bychom přidat třeba ještě podmínku, že chceme jen muže, nebo jen ženy nebo lidi z jednoho města… Fantazii se meze nekladou. Příkaz by vypadal asi takhle:
INSERT INTO telefony SELECT rc,mobil,telefon FROM lide where mesto like „Praha%“;
Tak jsme se zbavili dvou sloupců a nezbývá nám nic jiného než to, že je v klidu zahodíme. Schválně říkám zahodíme. Ano, zase ta angličtina.
ALTER TABLE lide DROP COLUMN telefon;
ALTER TABLE lide DROP COLUMN mobil;
Tabulka se nám poněkud zhubla, ale to nevadí. Má stejnou informační hodnotu jako před změnami. Vysvětlení přichází. Pokud o člověku víte alespoň něco (jméno, na co začínalo příjmení, městskou část), jistě dokážete z tabulky lidé vybrat alespoň jednu osobu splňující podmínky. A pak samozřejmě víte i rodné číslo. A víte-li rodné číslo, máte otevřenou cestu k číselníku s telefonními čísly. A najdete. Opačně to jde samozřejmě také úplně opačným postupem. A ještě aby jste netápali, číselník námi vytvořený vypadá takto:
+-------------+-------------+-------------+ | rc | mobil | pevna | +-------------+-------------+-------------+ | 790303/0041 | 0604-555555 | 0611-585555 | | 250808/0087 | není | 02-88888888 | | 480202/5895 | není | 0971-123456 | | 795331/0589 | není | 0600-784546 | +-------------+-------------+-------------+
Příkaz ALTER umožňuje také jiné věci, například změnu datového typu ve sloupci. Nestačí Vám CHAR? Nedivím se, má jen 255 znaků. Lepší je TEXT, má jich o hodně víc (nechce se mi plácat, ale víc jak 32768 určitě) a hlavně je to velký VARCHAR, takže si alokuje jen tolik, kolik potřebuje a ani o kus víc (oproti INT nebo REAL, ty zaberou celý rozsah). Změňme v naší tabulce lidé cvičně sloupec lide na proměnnou typu text:
ALTER TABLE lide modify prijmeni text NOT NULL;
Změna je provedena. Není ale pravděpodobné, že má někdo na světě tak dlouhé jméno. Pokud konvertujeme textový datový typ (CHAR, CHAR, TEXT a jejich odnože) na jiný textový, data nezmizí, což je i celkem pochopitelné. Stejně tak i při INT a jeho odnožích. Jen pozor na to, když převádíte sloupec do nižších datových typů (třeba CHAR nebo TINYINT). Zde je možná ztráta pravděpodobná.
Příkaz ALTER umožňuje vkládat i sloupce do libovolného místa, ne jen na konec tabulky, ale umí je i vsunout mezi nebo až na začátek. Dejme tomu že chceme v tabulce lidé každému člověku přiřadit zákaznické unikátní číslo, podle kterého ho budeme identifikovat na daňových dokladech. Syntaxe je následující:
ALTER TABLE lide ADD COLUMN id INT NOT NULL UNIQUE AUTO_INCREMENT FIRST;
Výsledek je ten, že sloupec id je první. Dali jsme mu do vínku nenulovost, unikátnost a autoinkrementaci.
+----+-------------+------------+-------+ | id | rc | prijmeni | jmeno | +----+-------------+------------+-------+ | 1 | 790303/0041 | Vonásek | Alois | | 2 | 250808/0087 | Dlabačka | Karel | | 3 | 480202/5895 | Houžvička | Karel | | 4 | 795331/0589 | Klokánková | Jana | +----+-------------+------------+-------+ ---------+-------------+----------+-------+ pohlavi | ulice | mesto | psc | ---------+-------------+----------+-------+ m | Ružová 15 | Praha 1 | 11000 | m | Kolínská 1 | Praha 8 | 18000 | m | Jabloňová 8 | Praha 10 | 10100 | z | Školská 487 | Praha 2 | 12000 | ---------+-------------+----------+-------+
Ještě bychom mohli přidat mezi sloupce jmeno a pohlavi sloupec třeba plat. Bude typu REAL (je reálný, na virtuálním kontě ve virtuální bance) a žádná omezení na něj nebudou kladena:
ALTER TABLE lide ADD COLUMN plat REAL AFTER jmeno;
Výsledek má následující podobu:
+----+-------------+------------+-------+------+ | id | rc | prijmeni | jmeno | plat | +----+-------------+------------+-------+------+ | 1 | 790303/0041 | Vonásek | Alois | NULL | | 2 | 250808/0087 | Dlabačka | Karel | NULL | | 3 | 480202/5895 | Houžvička | Karel | NULL | | 4 | 795331/0589 | Klokánková | Jana | NULL | +----+-------------+------------+-------+------+ ---------+-------------+----------+-------+ pohlavi | ulice | mesto | psc | ---------+-------------+----------+-------+ m | Ružová 15 | Praha 1 | 11000 | m | Kolínská 1 | Praha 8 | 18000 | m | Jabloňová 8 | Praha 10 | 10100 | z | Školská 487 | Praha 2 | 12000 | ---------+-------------+----------+-------+
Český překlad příkazu by mohl znít asi jako věta „rozšiř tabulku lide přidej sloupec plat typu REAL za sloupec jmeno“. Kdo umí anglicky a dá si tu práci, aby se pošťoural v návodu k MySQL (a že je obsáhlý), dá dohromady i složité dotazy obcházející jistá omezení MySQL.
Pomocí příkazu alter lze také přidávat klíče. Syntaxe je stejná jako při přidávání sloupců. Sestává se z:
ALTER TABLE jmeno_tabulky ADD INDEX (PRIMARY KEY, UNIQUE) jmeno_klice (sloupec);
Sloupec udává nad jakým sloupcem chcete klíč rozvinout. Jeho volba je na Vás, nemusí to být jen jeden sloupec, ale klidně dva nebo tři. Měli byste mít ale na paměti, že klíč má jistá pravidla a nejde udělat všude.
Modifikace tabulek není složitá, pokud možno si všechno otestujte na malé tabulce, než se pustíte do operací s databázemi klientů nějaké banky. Pak se stává to, že na účtu máte nulu nebo dokonce mínus. A pokud do SQL databáze vstupujete z nějakého skriptu (PHP, CGI atd.), pamatujte že fungují a existují Murphyho zákony… a to tak že dokonale.
