Úprava tabulky

V úvodu minulé kapitoly jsem psal, že už budete téměř schopni používat databáze v reálném životě. Tak teď už to bude bez toho téměř.
Nejdřív vám ukážu šikovnou podmínku WHERE (česky kde), pak příkazy na úpravu dat v tabulce DELETE a UPDATE a nakonec představím příkaz na úpravu samotné definice tabulky ALTER TABLE.

Příprava
Podmínka WHERE
- AND
- OR
- NOT
- BETWEEN
DELETE FROM
UPDATE
ALTER TABLE
MySQL/MariaDB
SQLite
Oracle

Příprava

V této kapitole budu příkazy vysvětlovat na nové tabulce. Nejdřív ji tedy musíte vytvořit a vložit do ní pár řádků. K tomu využijte již známé příkazy CREATE TABLE a INSERT INTO. Přihlašte se k vaší databázi rimmer1 (nebo jak jste si ji pojmenovali) a zadejte následující příkazy:

CREATE TABLE dluznici (
jmeno VARCHAR(10),
prijmeni VARCHAR(15),
dluh_kc NUMERIC(8,1),
zadluzen DATE
);

Vytvoří se tabulka se jménem dluznici, která obsahuje sloupce se jménem a příjmením dlužníka, výší dluhu a datum, kdy se dlužník zadlužil. Jak vidíte, dluh je číslo s jedním desetinným místem (halíře) a maximální dluh může být 9 999 999,9. Můžete dokonce zadat i dluh záporný (to když si od někoho půjčíte). Věřím, že nebudete nikomu půjčovat více jak 10 miliónů :-). Pokud mou víru nesdílíte, můžete si rozšířit velikost datového typu NUMERIC u sloupečku dluh_kc dle libosti.

Naplňte tabulku těmito hodnotami:

INSERT INTO dluznici VALUES ('Martin', 'Doktor', 5000, '2002-09-20');
INSERT INTO dluznici VALUES ('Linus', 'Torvalds', 6030, '2002-09-21');
INSERT INTO dluznici VALUES ('Jan Jakub', 'Ryba', 10.5, '2002-09-21');
INSERT INTO dluznici VALUES ('Martin', 'Ryba', 1000, '2002-09-21');

Chcete-li si hodnoty v tabulce prohlédnout (že tam opravdu jsou), použijte příkaz SELECT * FROM dluznici;.

Podmínka WHERE

Podmínka WHERE slouží k omezení řádků, na které se aplikuje SQL příkaz. U kterých všech SQL příkazů se podmínka WHERE dá použít se dozvíte časem. Zatím ji ukážu na příkazu SELECT.

Řekněme, že chci vypsat jen ty dlužníky, kteří mi dluží 5000 Kč a více. Na konec příkazu SELECT přidám podmínku WHERE:

Jestli napíšete podmínku ve tvaru sloupec >= hodnota nebo hodnota <= sloupec je jedno, ale to, kde napíšete příkaz WHERE, již jedno není. Nemůžete napsat WHERE dluh_kc >= 5000 SELECT * FROM dluznici;.

Jsem trochu v pokušení vám napsat, abyste se podívali na metapříkaz \h SELECT, abyste viděli co a v jakém pořadí se v tomto příkazu dá použít. Jenomže SELECT toho umí tolik, o čem jste ještě neslyšeli, že by vám z toho šla akorát hlava kolem.

Podmínky použitelné v klauzuli WHERE
Podmínka	Užití	Význam
=	sloupec = hodnota	Vyberou se ty řádky, které mají v sloupci zadanou hodnotu
<>	sloupec <> hodnota	Vyberou se ty řádky, které nemají v sloupci zadanou hodnotu
!=	sloupec != hodnota	To samé jako <>, jen jinak zapsáno.
<	sloupec < hodnota	Vyberou se ty řádky, které mají v sloupci menší hodnotu, než je hodnota v podmínce.
>	sloupec > hodnota	Vyberou se ty řádky, které mají v sloupci větší hodnotu, než je hodnota v podmínce.
<=	sloupec <= hodnota	Vyberou se ty řádky, které mají v sloupci menší nebo stejnou hodnotu, jako je hodnota v podmínce.
>=	sloupec >= hodnota	Vyberou se ty řádky, které mají v sloupci větší nebo stejnou hodnotu, jako je hodnota v podmínce.
IS NULL	sloupec IS NULL	Vyberou se ty řádky, které mají v sloupci hodnotu NULL
IS NOT NULL	sloupec IS NOT NULL	Vyberou se ty řádky, které nemají v sloupci hodnotu NULL
LIKE	sloupec LIKE 'retezec'	Vyberou se ty řádky, kde ve sloupci (s datovým typem řetězec) odpovídá hodnota řetězci za klíčovým slovem LIKE. Tento řetězec může obsahovat dva zástupné znaky: `%` (procento) zastupuje řetězec libovolné délky (i délky 0) s libovolnými znaky. `_` (podtržítko) zastupuje právě jeden libovolný znak. Příklad: podmínka `WHERE sloupec LIKE '_%A'` vybere ty sloupce, kde je řetězec končící písmenem velké A a který má před ním minimálně jeden (libovolný) znak.
IN (ALL, ANY…)	sloupec IN (množina hodnot)	Tyto podmínky již nejsou tak triviální a budou vysvětleny v kapitole SELECT IV - poddotazy. Zatím se jimi nemusíte zabývat.

Pro pořádek připomínám, že hodnota NULL se ničemu nerovná. Proto jsou potřeba podmínky IS NULL a IS NOT NULL).

AND

Chcete-li pro výběr řádků použít více podmínek najednou, spojte je klíčovým slovem AND. Vyberou se jen ty řádky, které splňují obě podmínky.

Příklad: Vyberte všechny záznamy o dluzích všech Jakubů Rybů, které přesahují částku 10 Kč.

Kontrolní otázka: Proč jsem použil v podmínce jmeno LIKE '%Jakub%' a ne jmeno = 'Jakub'?

OR

Pomocí klíčového slova OR vyberete ty řádky, které splňují alespoň jednu z podmínek spojených tímto klíčovým slovem. Spojení OR a AND můžete používat spolu. Jejich prioritu je nejjistější určit závorkami.

Příklad: Vyberte všechny záznamy Rybů a Linuse Torvaldse, kteří mají dluhy vyšší než 500 Kč.

Kontrolní otázka: Co by se stalo, kdybych odstranil vnější závorky z SQL z příkladu? (Nápověda: AND má vyšší prioritu než OR a pokud nejsou závorky, rozhoduje priorita …)
Kontrolní otázka: Stalo by se něco, kdybych odstranil vnitřní závoky z SQL z příkladu?

NOT

NOT neguje logický výraz. Má ještě větší prioritu než AND.

Příklad: Vyberte všechny záznamy o dluzích všech Rybů, kteří se nejmenují Jakub, a které přesahují částku 10 Kč.

SELECT * FROM dluznici WHERE jmeno NOT LIKE '%Jakub%' AND
prijmeni = 'Ryba' AND dluh_kc > 10;

Příklad: Vyberte všechny záznamy, kde je dluh mezi 1000 a 5000 Kč včetně.

SELECT * FROM dluznici WHERE dluh_kc >= 1000 AND dluh_kc <= 5000;
SELECT * FROM dluznici WHERE NOT dluh_kc < 1000 AND NOT dluh_kc > 5000;
SELECT * FROM dluznici WHERE NOT (dluh_kc < 1000 OR dluh_kc > 5000);

BETWEEN

Podmínka BETWEEN x AND y je snad samovysvětlující.

Příklad: Vyberte všechny záznamy, kde je dluh mezi 1000 a 5000 Kč včetně.

SELECT * FROM dluznici WHERE dluh_kc BETWEEN 1000 and 5000;

Jak jste mohli vidět z posledních dvou příkladů, v SQL můžete dojít k jednomu výsledku mnoha cestami. BETWEEN je tu jen pro to, aby se podmínka WHERE mohla zapsat trošilinku přehledněji.

Jedna dobrá rada na závěr: Pokud používáte v klauzuli WHERE více logických výrazů s AND, OR či NOT, určujte prioritu pomocí závorek. Nejen že tím zamezíte nechtěným chybám, ale také se to lépe čte a tak i lépe upravuje (když je potřeba).

DELETE FROM

Jistě jste si již položili otázku, jak řádky z tabulky smazat. Smazat celou tabulku a pak tam vložit zase všechno, kromě toho co tam nechceme mít, není úplně nejpohodlnější cesta.

K mazání řádků slouží příkaz DELETE. Za klíčovými slovy DELETE FROM následuje název tabulky, klíčové slovo WHERE s podmínkami. Podmínky určí, které řádky se smažou.

Pokud zadáte příkaz bez podmínky, tj. jen

DELETE FROM
nazev_tabulky;

, smažou se všechny řádky! Bacha na to! (Tabulka však existuje dále, tu zrušíte jen příkazem DROP TABLE).

Příklad: Odpusťte dluh všem, kteří vám dluží méně jak 10 Kč.

rimmer1=> DELETE FROM dluznici WHERE dluh_kc < 10;
DELETE 0

Při úspěšném provedení příkazu DELETE se vám ukáže na obrazovce DELETE n, kde n je počet smazaných řádků.
Jelikož v tabulce nebyl nikdo s dluhem menším než 10 Kč, nebyl smazán žádný řádek.

Teď už pro vás určitě nebude problém smazat všechny dluhy Martina Ryby.

rimmer1=> DELETE FROM dluznici WHERE jmeno = 'Martin'
AND prijmeni = 'Ryba';
DELETE 1

UPDATE

Řádky můžete nejenom vytvářet (příkazem INSERT) nebo mazat (příkazem DELETE), ale také měnit hodnoty ve sloupcích řádku (nebo několika řádek najednou). K tomu slouží příkaz UPDATE.

Za klíčovým slovem UPDATE následuje název tabulky, klíčové slovo SET a názvy sloupců = hodnota. Potom můžete určit podmínkou WHERE kterým řádkům se mají hodnoty ve sloupcích změnit. Pokud podmínku WHERE neuvedete, změny se provedou ve všech řádcích tabulky. (Vidíte, už umíte třetí SQL příkaz, který používá WHERE. Toho byste se ještě dneska ráno nenadáli, že?).

Vaše tabulka dlužníci by měla momentálně vypadat takhle:

Řekněme že Vám Martin Doktor vrátil 2200 Kč a dluží Vám tedy jen 3000 (úroky jsou sviňa). Hodnotu jeho dlužné částky změníte takto:

UPDATE dluznici SET dluh_kc = 3000 WHERE prijmeni = 'Doktor';

Pozor! Pokud byste měli více záznamů o dluhu Martina Doktora, nastavili byste je všechny na 3000! Proto by bylo ještě rozumné přidat podmínku:
… AND zadluzen = '2002-09-20'; nebo … AND dluh_kc = 5000; (nebo obě najednou).

Pokud byste z nějakého důvodu uložili do tabulky několik dluhů jednoho člověka v jeden den, a několik těchto dluhů by bylo 5000 kč, už byste byli nahraný! Jakou podmínkou určit správný řádek?
Takový problém se dá řešit buď zavedením primárho klíče nebo sloupcovým omezením UNIQUE (oboje proberu později).

SQL umí řešit i jednoduché matematické výrazy. Chcete-li například odpustit všem 20 Kč dluh, pak nejdříve smažete všechny řádky kde je dluh menší než 20Kč, ale větší než 0 (své dluhy, které si označujete zápornou hodnotou dluhu, nemůžete odpustit). Poté dosadíte do sloupce dluh_kc hodnotu dluh_kc-20 pro řádky, kde je dluh větší než 0.
Zní to složitě, ale příklad je jednoduchý:

DELETE FROM dluznici WHERE dluh_kc < 20 AND dluh_kc > 0;
UPDATE dluznici SET dluh_kc = dluh_kc-20 WHERE dluh_kc > 0;

Následující příklad není příliš smysluplný, je tu jen pro ukázku změny více sloupců najednou. Co tento příkaz provede si jistě domyslíte.

UPDATE dluznici SET dluh_kc = dluh_kc/2+100, zadluzen = '2002-09-22';

ALTER TABLE

Příkaz ALTER TABLE slouží k úpravě definice tabulky (mění její název, název sloupečků, přidává nebo maže sloupečky atd.). Upravovat definici tabulky asi nebudete často, ale někdy se to hodí. Pamatujte, že u tabulek s velkým množstvím záznamů (milióny a milióny) může ALTER TABLE trvat dlouho. DBMS musí někdy kvůli změně definice tabulky přesouvat všechny data na disku.

Teď vám ukážu nejzajímavější možnosti využití tohoto příkazu, které se vám mohou hodit. Později se k ALTER TABLE ještě vrátím.

Změna jména tabulky

Přejmenování tabulky je jednoduché:

ALTER TABLE dluznici RENAME TO dluhy;

Tabulka dluznici se přejmenovala na dluhy.

Změna jména sloupce

V tabulce se jménem dluhy změním jméno sloupce dluh_kc na jméno dluh.

ALTER TABLE dluhy RENAME dluh_kc TO dluh;

Vytvoření nového sloupce

Řekněme, že se například rozhodnete dát svým dlužníkům časový limit, do kdy vám mají dluh vrátit. Vytvoříte tedy nový sloupec s názvem navrat_dluhu. Tabulka dluhy by teď měla vypadat takto:

Přidám nový sloupec navrat_dluhu, který bude typu DATE.

Jak vidíte, přibyl nám nový sloupec, který je však celý prázdný (přesněji řečeno, obsahuje pro všechny řádky hodnotu NULL).

Příkazem UPDATE dám všem dlužníkům čas 30 dní na splacení dluhu:

Všimněte si, jak jsem použil hodnoty z jednoho sloupce pro nastavení hodnot v jiném sloupci. Samá kouzla, samá magie …

Poznámka pro pokročilé: pokud chcte upravit datum o jeden měsíc (ne každý měsíc má 30 dní), můžete to v PostgreSQL udělat takto:

UPDATE dluhy SET navrat_dluhu = zadluzen + interval '1 month';

Aritmetiku s časem proberu v některé z dalších kapitol.

Odstranění sloupce

Poslední, co vám chybí ke štěstí, je příkaz na odstranění sloupce z tabulky. Tím se také nenávratně ztratí všechna data ve sloupci obsažená).

ALTER TABLE dluhy DROP COLUMN navrat_dluhu;

Další informace o ALTER TABLE najdete v kapitole Úprava tabulky - pro pokročilé.

MySQL/MariaDB

V MySQL je všchno stejné, krom přejmenování sloupečku a práce s datem.

Sloupec se nedá jednoduše přejmenovat, můžete jej jen „znovuvytvořit“ (změnit jeho definici včetně jména). A pokud při jeho znovuvytvoření změníte pouze jméno …

ALTER TABLE dluhy CHANGE COLUMN dluh_kc dluh decimal(8,1);

V první části SQL příkazu se říká, že chcete změnit sloupeček dluh_kc a v druhé části na co ho chcete změnit (dluh decimal(8,1);). Změněný sloupeček si ponechá své hodnoty, pokud je to možné. (Pokud změníte typ sloupce z čísla ne text tak to možné je, opačně je to už horší …)

A ano, tušíte správně, můžete takto změnit typ sloupečku (třeba na decimal(10, 1)), ale to už předbíhám o pár lekcí dopředu.

Když se pokusíte k DATE přičíst číslo, MySQL ho nechápe jako počet dní, jako PostgreSQL. Musíte na to jít trochu jinak:

UPDATE dluhy SET navrat_dluhu = DATE_ADD(zadluzen, INTERVAL +30 DAY);

INTERVAL 30 day by taky šlo, ale INTERVAL +30 DAYS už je chyba.

SQLite

U SQLite platí všechno stejně jako u PostgreSQL, krom úpravy datumu a příkazu ALTER TABLE.

Přičtení čísla k datumu na rozdíl od PostgreSQL nepřičte 30 dní. SQLite typ DATE je totiž ve skutečnosti TEXT. SQLite se při sčítání pokusí převést text na číslo, sečíst a výsledek uložit.

Pro aritmetiku s daty se v SQLite používá funkce date.

sqlite> select zadluzen + 30, date(zadluzen,'+30 days') FROM dluhy;
zadluzen + 30 date(zadluzen,'+30 days')
------------- -------------------------
2032 2002-10-22
2032 2002-10-22

UPDATE dluhy SET navrat_dluhu = date(zadluzen, '+1 month');

Při letmém pohledu do dokumentace SQLite k ALTER TABLE zjistíte, že ALTER TABLE umí jen přejmenovat tabulku a přidat sloupec. (Zkuste se zamyslet nad tím obrázkem v dokumentaci, není to tak těžké pochopit.)

Přejmenování i smazání sloupece jde udělat, jen je na to potřeba takový malý trik. Ukážu vám novou verzi INSERT statemetu, a to INSERT INTO … SELECT, který vloží do jedné tabulky data z jiné tabulky.
Postup bude následující:

Vytvořte novou tabulku, která bude mít požadované sloupce.
Zkopírujte data z původní tabulky do té nové pomocí INSERT INTO … SELECT
Smaže původní tabulku
Přejmenujte novou tabulku na jméno původní tabulky.

CREATE TABLE dluhy_new (
jmeno VARCHAR(10),
prijmeni VARCHAR(15),
dluh NUMERIC(8,1),
zadluzen DATE,
navrat_dluhu DATE
);
INSERT INTO dluhy_new SELECT jmeno, prijmeni, dluh_kc, zadluzen, NULL FROM dluhy;
UPDATE dluhy_new SET navrat_dluhu = date(zadluzen, '+1 month'); -- (krok navic)
DROP TABLE dluhy;
ALTER TABLE dluhy_new RENAME TO dluhy;

Ve výrazu INSERT INTO … SELECT musí být v SELECTu vyjmenované všechny sloupečky a ve stejném pořadí, jako jsou definované v tabulce, do které se INSERTuje. Do nového (posledního) sloupečku se proto insertuje NULL.

Poznámka: INSERT a UPDATE by šli nahradit jedním INSERTem:

INSERT INTO dluhy_new SELECT jmeno, prijmeni, dluh_kc, zadluzen, date(zadluzen, '+1 month') FROM dluhy;

Oracle

V Oracle musíte pro práci s časem používat vám už známou funkci TO_DATE.

INSERT INTO dluznici VALUES ('Martin', 'Doktor', 5000, TO_DATE('2002-09-20','YYYY-MM-DD'));
INSERT INTO dluznici VALUES ('Linus', 'Torvalds', 6030, TO_DATE('2002-09-21','YYYY-MM-DD'));
INSERT INTO dluznici VALUES ('Jan Jakub', 'Ryba', 10.5, TO_DATE('2002-09-21','YYYY-MM-DD'));
INSERT INTO dluznici VALUES ('Martin', 'Ryba', 1000, TO_DATE('2002-09-21','YYYY-MM-DD'));

UPDATE dluznici SET dluh_kc = dluh_kc/2+100, zadluzen = to_date('2002-09-22','YYYY-MM-DD');

Přejmenování sloupce vyžaduje použití RENAME COLUMN.

ALTER TABLE dluhy RENAME COLUMN dluh_kc TO dluh;

Jak vidíte, syntaxe pro přejmenování je stejná jako v Postgresu, jen je tam navíc slovíčko COLUMN. Ale to můžete použít i v PostgreSQL! Rozdíl mezi Oracle a PostgreSQL je v tom, že Oracle slovíčko COLUMN vyžaduje, zatímco v Postgresu je nepovinné. Doporučení zní: používejte RENAME COLUMN (rázem se stane SQL příkaz přenositelnější mezi oběma DBMS).

Přičtení čísla k typu DATE se chová v Oracle stejně jako v PostgreSQL – přičte se to jako počet dní.

Pro přičtení měsíců má Oracle funkci ADD_MONTHS:

UPDATE dluhy SET navrat_dluhu = add_months(zadluzen, 1);