SQL – vkládání a aktualizace dat v tabulce

V předchozích dílech jste se seznámili s tvorbou databázových tabulek a jejich správou a údržbou. Dnes si řekneme, jak tyto databázové tabulky naplnit daty. Dále si povíme o mazání řádků v tabulce a jejich aktualizaci. Uvedu, jaké zásady a postupy při manipulaci s daty v tabulkách musíme dodržovat, aby námi prováděné operace proběhly úspěšně a bez chybových hlášek SQL serveru. Dříve, než budu pokračovat ve výkladu jazyka SQL, vysvětlím pojem SQL serveru.

SQL server

Databáze pracující s SQL jsou založeny na modelu klient – server. Na server lze pohlížet ze dvou úhlů. Server je vybraný stroj v naší firmě, na kterém je nainstalovaný databázový systém a na jeho discích jsou uložena naše data. Na druhou stranu je server proces (program), který běží na zvoleném počítači a který obsluhuje jednotlivé požadavky klientů. Klienti zadávají SQL příkazy a server tyto příkazy nad databází vykonává. Klientem pak může být konkrétní databázová aplikace, nebo také řádkový terminál, ve kterém můžeme SQL příkazy zadávat přímo.

Většinou se setkáme s lidmi, kteří budou pracovat s databázovými aplikacemi, např. pro vedení skladového hospodářství. Tito uživatelé samozřejmě žádné SQL příkazy nezadávají, ti pracují s jednotlivými dialog boxy a okny v aplikaci, vyplňují data do různých formulářů, apod. Takové naprogramované aplikace mají ve svém kódu již SQL příkazy napsány (vzpomeňte si, jazyk SQL je deklarativní a tudíž se vkládá do jazyka, ve kterém se programuje aplikace). Uživatelská data se „dosadí“ do těl SQL příkazů ve zdrojovém kódu aplikace a následně se zavolají jako požadavek na SQL server ke zpracování. To znamená, že běžný uživatel v tomto případě nepotřebuje vůbec mít tušení, že existuje nějaký jazyk SQL. Ten potřebuje dobře znát programátor takové databázové aplikace. Než začnete programovat aplikaci, je užitečné jednotlivé SQL příkazy si zkoušet „ručně“ na řádkovém terminálu připojeného k SQL serveru. Tento způsob přístupu je vhodný také pro ladění databázových aplikací. Nyní uvedu pár příkladů databázových systémů (=SQL serverů), ke kterým se můžete připojit, a ve kterých můžete přímo zkoušet příklady uvedené na této stránce.

Oracle

Pod operačním systémem Unix na příkazový řádek vašeho shellu se na databázi Oracle napojíte tímto způsobem:

module add oracle sqlplus vaše_přihlašovací_jméno

Nejprve budete dotázání na heslo, po zadání správného hesla se objeví prompt příkazového řádku SQL>. Sezení ukončíte příkazem EXIT, nápovědu vypíšete příkazem HELP. Oracle existuje i pro platformu Windows NT, ale s tou jsem se nesetkal.

PostgreSQL

Pod operačním systémem Unix v terminálu na příkazový řádek zadáte následující příkazy pro spuštění klienta:

module add postgres psql -H jméno_serveru jméno_vaší_databáze

MySQL

MySQL existuje pro platformy Unix i Windows, v Unixu napíšete pro spuštění příkazy:

module add mysql mysql -h jméno_serveru -p jméno_vaší_databáze

Na platformě Windows si po té, co si nainstalujete MySQL a spustíte MySQL server (příkazem mysql-shareware), spustíte klienta v okně Command:

mysql -u uživatelské_jméno -pheslo jméno_vaší_databáze

MS Access a WinBase602

Tyto dva databázové systémy najdeme na platformách Windows. Od těch předchozích se liší především v tom, že se spouští grafické rozhraní (i když pro výše uvedené existují také dodatečná grafická rozhraní). V těchto systémech máme možnost vytvářet databázi, vykonávat dotazy, aktualizovat data přes klikání a zadávání dat do formulářů. V menší míře můžeme zadávat SQL příkazy přímo. Oba dva systémy, dle mých informací, podporují vzdálené připojení z databázové aplikace nebo CGI skriptů, v jejimž kódu jsou SQL příkazy zapsány. Tyto systémy spustíte většinou z nabídky Start, nebo kliknutím na ikonu na ploše.

Vkládání dat do tabulky

Nyní se vraťme k jazyku SQL. Máme-li kompletně vytvořenou strukturu databázových tabulek, můžeme začít s jejich naplněním daty. Pro vkládání jednoho řádku dat do tabulky se používá příkaz INSERT, jehož syntaxe je následující:

INSERT INTO jméno_tabulky [(jména sloupců)] VALUES (seznam hodnot)

Za klíčovými slovy INSERT INTO se udává jméno tabulky, do které chceme data vkládat (je-li tabulka v jiném databázovém schématu, použijeme tvar jméno_schématu.jméno_tabulky). Seznam sloupců uvedený v závorkách je nepovinný, použijeme jej jenom ve speciálním případě. Za klíčovým slovem je uveden seznam hodnot, které vkládáme. Pořadí hodnot musí přesně odpovídat pořadí sloupců v tabulce. Pokud toto pořadí hodnot nechceme dodržet, pak musíme uvést část jména_sloupců za klíčovým slovem INTO, ve které budeme vlastně specifikovat, v jakém pořadí vkládáme jednotlivé hodnoty. Pořadí sloupců tedy musí odpovídat pořadí hodnot.

Vkládání dat do tabulky bez cizích klíčů

Mějme tabulku ZAMĚSTNANEC. Ta bude obsahovat jednoduché sloupce a nebude mít žádný cizí klíč. Tabulku vytvoříme následujícím příkazem:

CREATE TABLE zaměstnanec (číslo INTEGER PRIMARY KEY, jméno VARCHAR(10), příjmení VARCHAR(20) NOT NULL, počet_hodin INTEGER, vzdělání VARCHAR(12) DEFAULT ‚neuvedeno‘, řidičský_p CHAR(1) DEFAULT ‚N‘)

Pro úspěšné vložení řádku do tabulky musíme dodržet několik zásad, které se týkají uvedených integritních omezeních. V prvé řadě musíme zajistit, aby vkládáná hodnota sloupce ČÍSLO, která je primárním klíčem tabulky, byla jedinečná. Dále musí být vždy zadána hodnota PŘÍJMENÍ. A samozřejmě musí platit, že typ zadávaných hodnot bude totožný (nebo i kompatibilní, např. celé číslo lze uložit do položky reálného čísla, ale ne naopak) s typem sloupců, do kterých hodnoty vkládáme. Následuje výčet příkazů INSERT, které se provedou úspěšně. Předpokládejme, že v tabulce ZAMĚSTNANEC nemáme ještě žádná data.

INSERT INTO zaměstnanec VALUES (1, ‚jan‘, ‚novák‘, 60, ‚SŠ‘, ‚A‘) INSERT INTO zaměstnanec (číslo, jméno, příjmení, počet_hodin) VALUES (2, ‚petr‘, ‚nový‘, 50) INSERT INTO zaměstnanec (číslo, příjmení, jméno, řidičský_p) VALUES (7, ‚nováček‘, ‚jan‘, ‚A‘) INSERT INTO zaměstnanec (číslo, příjmení) VALUES (4, ‚novotný‘)

Pár komentářů k uvedeným příkazům. První je jeho uvedení v základním tvaru, pořadí hodnot odpovídá přesně pořadí sloupců, jak jsme je vytvořili v tabulce. V druhém příkazu vkládáme další řádek do tabulky, vložíme hodnoty položek ČÍSLO, JMÉNO, PŘÍJMENÍ a POČET_HODIN, do položky VZDĚLÁNÍ se uloží implicitní hodnota ‚neuvedeno‘, a do položky ŘIDIČSKÝ_P se vloží předdefinovaná hodnota ‚N‘. Třetí INSERT zanechá položku POČET_HODIN jako nedefinovanou, ostatní budou mít hodnotu zadanou, nebo defaultní. Poslední příkaz nám vkládá hodnoty do sloupců, které z  definice tabulky nesmí zůstat prázdné, ostatní položky buď budou mít defaultní hodnotu, nebo nedefinovanou. Většina databázových systémů neumožňuje nezadat všechny sloupce, jak je tomu u druhého až čtvrtého příkazu. Tuto vlastnost podporuje např. systém Oracle. Tabulka po provedení těchto čtyř příkazů bude mít následující obsah:

ČÍSLO JMÉNO PŘÍJMENÍ POČET_HODIN VZDĚLÁNÍ ŘIDIČSKÝ_P 1 jan novák 60 SŠ A 2 petr nový 50 neuvedeno N 7 jan nováček neuvedeno A 4 novotný neuvedeno N

Uveďme si nyní pár dalších příkazů, u nichž SQL server nahlásí chybu: (sami si zkuste odvodit, proč k chybě dojde)

INSERT INTO zaměstnanec VALUES (3, ‚jiří‘, ‚dvořák‘, ’42‘, ‚VŠ‘, ‚A‘) INSERT INTO zaměstnanec (číslo, příjmení, jméno, řidičský_p, vzdělání, počet_hodin) VALUES (9, ‚nováková‘, ‚jana‘, ‚N‘, 55, ‚SŠ‘) INSERT INTO zaměstnanec (číslo, příjmení) VALUES (4, ‚dvořáček‘)

Vkládání dat do tabulky s cizím klíčem

Pokud se rozhodneme vkládat data do tabulky, jejíž některý ze sloupců je cizím klíčem do jiné tabulky, musíme dodržovat jednu zásadu. Konkrétní hodnota, kterou budeme chtít vložit do sloupce, který je cizím klíčem, musí nejdříve existovat v  odkazované tabulce (v jejím primárním klíči). Vysvětlím na příkladu. Mějme tabulku ZAMĚSTNANEC, kterou si rozšíříme o sloupce ČÍS_FUN, jehož hodnoty budou odkazovat do tabulky FUNKCE. Obě tabulky vytvoříme následujícími příkazy:

CREATE TABLE funkce (číslo INTEGER, název VARCHAR(10), PRIMARY KEY (číslo)) CREATE TABLE zaměstnanec (číslo INTEGER, jméno VARCHAR(10), příjmení VARCHAR(20) NOT NULL, počet_hodin INTEGER, vzdělání VARCHAR(12) DEFAULT ‚neuvedeno‘, řidičský_p CHAR(1) DEFAULT ‚N‘, čís_fun INTEGER, PRIMARY KEY (číslo), FOREIGN KEY (čís_fun) REFERENCES funkce (číslo))

Předpokládejme, že tabulku ZAMĚSTNANEC máme prázdnou a tabulku FUNKCE naplníme následujícími daty:

INSERT INTO funkce VALUES (121, ‚vedoucí‘) INSERT INTO funkce VALUES (127, ‚technik‘) INSERT INTO funkce VALUES (156, ‚správce‘)

Správně uvedený příkaz INSERT je následující:

INSERT INTO zaměstnanec VALUES (1, ‚jan‘, ‚novák‘, 60, ‚SŠ‘, ‚A‘, 127)

Další příkaz je chybný, neboť číslo funkce 29 v tabulce FUNKCE zatím neexistuje:

INSERT INTO zaměstnanec VALUES (2, ‚petr‘, ‚nový‘, 50, ‚ZŠ‘, ‚A‘, 29)

Pokud bychom chtěli, aby se výše uvedený příkaz provedl úspěšně, museli bychom nejprve do tabulky FUNKCE vložit nový řádek s číslem funkce 29:

INSERT INTO funkce VALUES (29, ‚vrátný‘)

Aktualizace dat v tabulce

Aktualizací dat v tabulce rozumíme editaci existujících hodnot v řádku, nebo mazání celých řádků. U obou dvou případů můžeme provést aktualizaci všech řádků v tabulce, nebo můžeme specifikovat určité řádky, u kterých se má aktualizace dat provést. Specifikace spočívá v uvedení podmínek, které daný řádek musí splňovat. Aktualizace hodnot bude provedena pouze u těch řádků, které splňují zadané podmínky v příkazech pro aktualizaci. Nejprve se podívejme na editaci hodnot.

Editace hodnot v řádcích

Pro změnu hodnot položek v již existujících řádcích použijeme příkaz:

UPDATE jméno_tabulky SET sloupec = hodnota, … [WHERE podmínky]

Nejjednodušší použití příkazu UPDATE je jeho zavolání bez části WHERE. Tím říkáme, že se mají změnit hodnoty všech uvedených sloupců ve všech řádcích tabulky. Vezměme si tabulku FUNKCE, kterou si doplníme o sloupec PLAT, příkazem ALTER TABLE:

ALTER TABLE funkce ADD (plat NUMBER(9,2) DEFAULT 10000)

Předpokládejme, že tabulka je prázdná. Naplníme ji základními daty:

INSERT INTO funkce VALUES (121, ‚vedoucí‘, 21500) INSERT INTO funkce VALUES (127, ‚technik‘, 15000) INSERT INTO funkce VALUES (156, ‚správce‘, 17500) INSERT INTO funkce VALUES (29, ‚vrátný‘, 10000)

Jako novou hodnotu můžeme uvést konkrétní hodnotu odpovídajícího datového typu, nebo se můžeme odkazovat na některou z hodnot ve sloupci ve stejném řádku. Následují dva příklady. První v tabulce FUNKCE nastaví všechny platy na částku 20000 Kč, druhý příklad vezme všechny platy a jejich hodnoty zvýší o 25%. Příkazy UPDATE se v těchto příkladech provedou pro všechny řádky v tabulce FUNKCE:

UPDATE funkce SET plat = 20000 UPDATE funkce SET plat = 1.25 * plat

Většinou ale potřebujeme zeditovat hodnoty pouze na těch řádcích, které splňují určitou podmínku. Např. chceme ve firmě zvýšit plat pouze vedoucím, nebo chceme zvýšit plat jen těm zaměstnancům, kteří mají méně, než 16000 Kč. Tyto upřesňující podmínky uvedeme v části WHERE. Následující příklady ukazují zvýšení platu všem vedoucím o 10% a další zvýšení platu o 500 Kč všem, kteří mají plat nižší než 16000 Kč, vyjma vrátného:

UPDATE funkce SET plat = plat + (0.1 * plat) WHERE název = ‚vedoucí‘ UPDATE funkce SET plat = plat + 500 WHERE plat < 16000 AND NOT název = ‚vrátný‘

Podmínka v části WHERE je řetězec, který se může skládat z několika logických výrazů (výrazy s operátory <, >, <=, >=, =, apod) oddělených logickými spojkami AND a OR. Lze použít i negaci NOT a závorky. Více o sestavování podmínek se dovíte v příštím díle v souvislosti s vyhledáváním informací v tabulkách (pomocí příkazu SELECT, který má také část WHERE, jejiž syntaxe je stejná). Přejděme nyní k mazání řádků v tabulce.

Mazání řádků

Příkaz pro mazání řádků v tabulce je syntakticky velmi podobný předchozímu příkazu:

DELETE FROM jméno_tabulky [WHERE podmínky]

Základní použití příkazu DELETE je opět bez části WHERE. Takové zavolání příkazu způsobí vymazání všech řádků v uvedené tabulce. Mnohem praktičtější je ovšem smazání jen některých řádků. Takové řádky opět specifikujeme podmínkami v části WHERE. Smazány budou výhradně jen ty, které budou splňovat uvedené podmínky. Jednoduchý příklad, demonstrující situaci, že bychom v naší firmě dali výpověď všem vratným, a vrátnici bychom zrušili, by mohl vypadat takto:

DELETE FROM zaměstnanec WHERE čís_fun = 29 DELETE FROM funkce WHERE název = ‚vrátný‘

Všimněte si, že jsme nejprve smazali všechny relevantní položky z tabulky ZAMĚSTNANEC a teprve potom z tabulky FUNKCE. S tím, co zatím o SQL již víme, by každý z nás měl být schopen najít odůvodnění, proč nemůžeme nejprve smazat řádky z tabulky FUNKCE.

Pokročilé operace s daty v tabulkách

Příkazy pro manipulaci s daty INSERT, UPDATE i DELETE umožňují do svých těl vnořovat SQL dotazy. To znamená, že aktualizace dat se provede na základě výsledků vnořených dotazů. To může být někdy užitečné. Vnořování dotazů patří ale k pokročilým znalostem jazyka SQL, ke kterým se dostanu v jednom z příštích dílů. Zde vás o této možnosti jen informuji.

Starší komentáře ke článku

Pokud máte zájem o starší komentáře k tomuto článku, naleznete je zde.