Základní obeznámení s jazykem SQL

Základní obeznámení s jazykem SQL Databázové systémy

Úvod • Structured Query Language • standardní jazyk pro přístup k relačním databázím • původně snaha o co nejpřirozenější formulace DB požadavků (např. příkaz SELECT je to v podstatě „věta“ – proto je tak složitý) • je zároveň jazykem pro • definici dat (DDL) • vytváření/modifikace schémat, resp. tabulek • manipulaci s daty (DML) • dotazování • vkládání, aktualizace, mazání dat • definici integritních omezení • moduly (programovací jazyk) • řízení transakcí • atd.

Historie 70-ta léta – vznikl z výzkumného projektu o relačních databázích vedeného v firmou IBM - Sequel 1986 – byl přijat jako oficiální standard ANSI (označován SQL/86) 1989 – integritní dodatek (označován SQL/89) 1992 – opravená verze označována jako SQL/92 - je standardem dodnes. – další standardy X_OPEN SQL (Unix), SAA-SQL (IBM) 1996 – dodatek pro uložené moduly 1999 – poslední sada dokumentů SQL/99 • objektově-relační rozšíření • typy STRING, BOOLEAN, REF, ARRAY, typy pro full-text, obrázky, prostorová data, • triggery, role, programovací jazyk, regulární výrazy, rekurzivní dotazy, atd... 2003 – další rozšíření, např. XML management, autočísla, std. sekvence, nicméně zmizel např. typ BIT

Dotazy v SQL • dotaz v SQL vs. kalkuly a algebra • příkaz SELECT sdílí prvky obou aparátů • rozšířený DRK (práce se sloupci, kvantifikátory, agregační funkce) • algebra (některé operace – projekce, selekce, spojení, kart.součin, množinové operace) • na rozdíl od striktní formulace relačního modelu jsou povoleny duplikátní řádky a nulové hodnoty atributů • validátor syntaxe pro SQL 92, 1999, 2003 • umožnuje zkontrolovat dotaz (nebo jiný SQL příkaz) podle normy • http://developer.mimer.com/validator/index.htm

SQL patři do kategorie tzv. deklarativních programovacích jazyků - kód jazyka SQL nepíšeme v žádném samostatném programu, ale vkládáme jej do jiného programovacího jazyka, který je již procedurální. Se samotným jazykem SQL můžeme pracovat pouze v případě, že se terminálem připojíme na SQL server a na příkazový řádek zadáváme přímo příkazy jazyka SQL. Pro řešení problémů různých syntaxi SQL (používaných před zavedením standardu), Microsoft formalizoval CLI – Common Language Interface pro pracovní stanice a oznámil, že jeho produkty by měli používat toto rozhraní – nazývá ho ODBC – Open Databaze Connectivity.

Kontexty použití jazyka SQL • 􀂾 přímý (direct) SQL • 􀂾 hostitelská verze (embedded) SQL • 􀂾 jazyk modulů • Hlavní kategorie příkazů • 􀂾 definice dat a pohledů (DDL – Data Definition Language) • 􀂾 manipulace s daty (DML – Data Manipulation Language) • 􀂃 - pro přímý SQL • 􀂃 - pro hostitelskou verzi • 􀂾 autorizace (řízení přístupových práv) • 􀂾 integrita dat • 􀂾 řízení transakcí

Dotazy v SQL • pro vysvětlení použijeme syntaxi SQL 86 pomocí diagramů • orientovaný graf - automat rozpoznávající SQL • rozlišování termů v diagramu • malá písmena, podtržení – podvýraz v rámci dané konstrukce • velká písmena – klíčové slovo SQL • malá písmena, kurzíva – jméno (tabulky/sloupce/...) • místo atribut/doména, relace budeme používat označení sloupec, tabulka

CREATE TABLE – základní konstrukce • vytvoření schématu a prázdné tabulky (pokud již není tabulka s daným jménem založena) • tabulka má definovány jednak sloupce (atributy a související atributová IO) a jednak tabulková IO

CREATE TABLE – definice sloupce • každý sloupec má vždy přiřazen jednak datový typ (data_type) • nepovinně je možno specifikovat • implicitní hodnotu v nově vytvořeném záznamu (DEFAULT NULL | value) • sloupcové integritní omezení

CREATE TABLE – integritní omezení sloupce (lokální) • sloupcové IO umožňuje omezit množinu platných hodnot daného atributu v rámci záznamu (nového nebo modifikovaného) • pojmenované CREATE TABLE ... (..., CONSTRAINT constraint ...) • nepojmenované • 5 typů omezení na platnou hodnotu atributu • NOT NULL – hodnota musí být nenulová • UNIQUE – hodnota musí být unikátní (v rámci všech řádků v tabulce) • PRIMARY KEY – definuje primární klíč (totéž jako NOT NULL+UNIQUE) • REFERENCES – definuje jednoatributový cizí klíč (oba atributy musí být kompatibilní) • CHECK – obecná podmínka • vyhodnotí se pouze na vkládaném řádku (řádcích) • při vyhodnocení podmínky na TRUE je hodnota atributu platná • při pokusu o aktualizaciřádků s neplatnou hodnotou atributu se aktualizace celého řádkuneprovede

CREATE TABLE – integritní omezení tabulky (globální) • zobecnění atributových IO pro kombinace hodnot více sloupců • kromě NOT NULL, to má smysl pouze u jednotlivých atributů • UNIQUE – n-tice hodnot je unikátní • FOREIGN KEY – stejně jako REFERENCES u atributu • CHECK

Referenční integrita • při aktualizaci tabulky referující (ta, pro kterou je IO definováno) nebo referované může nastat porušení integrity cizích klíčů • pokus o vložení/aktualizaci záznamu s hodnotou cizího klíče, která se nevyskytuje v sloupci referované tabulky • pokus o smazání záznamu z referované tabulky, když pro mazanou hodnotu klíče existuje reference • při porušení integrity cizích klíčů může nastat: • hlášení chyby aktualizace, pokud není definována referenční akce (SQL 89) • vykonání referenční akce referential_action(SQL 92) • ON UPDATE, ON DELETE – podmínka spuštění akce • při modifikaci referované hodnoty nebo smazání řádku v referované tabulce • CASCADE – záznam s referující hodnotou se také smaženebo aktualizuje novou hodnotou • SET NULL – hodnota dotčeného záznamu se nastaví na NULL • SET DEFAULT – hodnota dotčeného záznamu se nastaví na implicitní hodnotu definovanou v CREATE TABLE • NO ACTION – implicitní, neprovede se nic, resp. SŘBD ohlásí chybu, tj. chování v rámci SQL 89

Modifikátory • AUTO_INCREMENT – (automatické číslo) definuje datový typ pole, který při přidání nového záznamu do tabulky automaticky uloží jedinečné číslo o jedničku větší než předchozí. Nedovolí uložit už jednou použitou hodnotu, i když záznam s touto hodnotou byl již z databáze odstraněn. Modifikátor AUTO_INCREMENT lze použít pouze u polí s celočíselnými hodnotami. • ZEROFILL – používá se k zobrazení vedoucích nul u čísel, která jsou založena na šířce zobrazení, tj. vyžadují určitý počet číslic. Chceme-li číslo 23 zobrazit jako 000023, musíme pole deklarovat jako INT(6) ZEROFILL. • BINARY – umožňuje ukládání textových informací jako binárních řetězců. Řetězce uložené v binárním formátu rozlišují velikost písmen. To se projeví také na třídění těchto polí. • DEFAULT – umožňuje určit hodnotu pole, jehož hodnota nebude při uložení záznamu (řádku) známá. Výchozí hodnotou používanou v systému MySQL je NULL. Hodnota NULL je dosazována do všech prázdných polí, kromě polí typu ENUM.

Vytvoření bázové tabulky CREATE TABLE název_tabulky (definice_sloupce, … [definice_integritních_omezení_tabulky] ); - příkaz vytvoří novou, prázdnou tabulku s definovaným počtem sloupců a popis uloží do katalogu Definice sloupce jméno_sloupce TYP [impl_hodnota][integritní omezení_sloupce] Integritní omezení jsou omezení kladená na hodnoty ve sloupcích tabulky, aby nedošlo k porušení integrity dat.

Integritní omezení celé tabulky PRIMARY KEY (jm_sloupce, …) UNIQUE (jm_sloupce, …) FOREIGN KEY (jm_sloupce, …) REFERENCES tabulka [(jm_sloupce, …)] [událost ref_akce] CHECK (podmíněný_výraz) Integritní omezení sloupce NULL, resp NOT NULL CHECK (podmíněný_výraz) PRIMARY KEY UNIQUE FOREIGN KEY REFERENCES tabulka [(jm_sloupce)] [událost ref_akce] Referenční akce - CASCADE, SET DEFAULT, SET NULL

Příklad: Spořitelna – tabulky s globálními IO KLIENT: r_cislojmeno prijmeni ulice mesto 601012/1224Jan Nový Jánská 25 Zlín 826111/5267 Petra NovákováPoštová 128 Lešná 580506/4891 Pavel Nováček Nezvalova 697 Zlín 795302/2459 Ivana Novotná Pod mlýnem 12 Lukov UCET:POBOCKA: c_uctu stav r_cislopobockac_p nazev jmeni 4568517 42000 826111/5267 2 1 Nám. Míru 12 5000000 6585485 75000 580506/4891 1 2 Zarámí 325 8000000 3256151 25000 826111/5267 1 TRANSAKCE: c_uctu c_transakce castka datum 6585485 1 -2100 11.09.2007 4568517 1 -4560 11.09.2007 6585485 2 10000 12.09.2007

CREATE TABLEKlient (r_cislo VARCHAR(11) NOT NULL, jmeno VARCHAR(20)NOT NULL, prijmeni VARCHAR(30)NOT NULL, ulice VARCHAR(30) NOT NULL, mesto VARCHAR(30) NOT NULL, CONSTRAINT pk PRIMARY KEY (r_cislo)); CREATE TABLEPobocka (c_p INT NOT NULL, nazev VARCHAR(10), jmeniDECIMAL(10,2), CONSTRAINT pk PRIMARY KEY (c_p)); CREATE TABLE Ucet (c_uctu DECIMAL(7,0) NOT NULL, stav DECIMAL(10,2), r_cislo VARCHAR(11) NOT NULL, pobocka VARCHAR(20) NOT NULL, CONSTRAINT pk PRIMARY KEY (c_uctu), CONSTRAINT fk1 FOREIGN KEY (r_cislo) REFERENCES Klient, CONSTRAINT fk2 FOREIGN KEY (pobocka) REFERENCES Pobocka )

ALTER TABLE • změna definice schématu • atributy – přidání/odebrání atributu, změna DEFAULT hodnoty • IO – přidání/odebrání IO • pozor, v tabulce už mohou být data, která nedovolí změnit IO (např. zavést IO primární klíč) ALTER TABLE table-name ... ADD [COLUMN] column-name column-definition ... ADD constraint-definition ... ALTER [COLUMN] column-name SET ... ALTER [COLUMN] column-name DROP ... DROPCOLUMN column-name ... DROPCONSTRAINT constraint-name

Změna struktury tabulky Modifikuje tabulku a změní informace v katalogu ALTER TABLE název_tabulkyaktualizace; Přidání sloupců do tabulky V případě, že v tabulce již nějaká data jsou, lze použít následující příkaz: ALTER TABLE název_tabulky ADD COLUMN jméno_sloupce typ_sloupce [integritní omezení];

Úprava definice sloupců Základní příkaz: ALTER TABLE název_tabulky CHANGE COLUMN staré_jméno_sloupce nové_jméno_sloupce typ_sloupce [integritní mezení]; Mazání sloupců Máme-li nějaký sloupec, jehož hodnoty už opravdu nepotřebujeme, jednoduše jej smažeme následujícím příkazem: ALTER TABLE název_tabulky DROP COLUMN jméno_sloupce [CASCADE]

Přejmenování sloupce První možnost je rychlejší a využijeme v ní již známý příkaz: ALTER TABLE název_tabulky CHANGE COLUMN staré_jméno_sloupce nové_jméno_sloupce typ_sloupce [integritní omezení]; Druhá možnost je obecnější je potřeba provést ji ve více krocích: ALTER TABLE název_tabulky ADD COLUMN jméno_sloupce typ_sloupce [integritní omezení]; UPDATE název_tabulky SET staré_jméno_sloupce = nové_jméno_sloupce; ALTER TABLE název_tabulky DROP staré_jméno_sloupce;

DROP TABLE • DROP TABLEtable • komplementární k příkazu CREATE TABLE table • smaže se jak obsah tabulky, tak i schéma tabulky • pokud chceme vymazat pouze obsah tabulky, použijeme příkaz DELETE FROMtable Odstranění tabulky DROP TABLE název_tabulky; Pokud provedeme příkaz DROP TABLE, tak přijdeme o všechna data, která v mazané tabulce byla uložena.

Modifikace dat • SQL obsahuje kromě SELECT tři příkazy pro manipulaci s daty • INSERT INTO – vložení řádků • DELETE FROM – vymazání řádků • UPDATE – aktualizace hodnot v řádcích

INSERT INTO • vkládání řádku výčtem hodnot, dvě možnosti • INSERT INTO table VALUES (h1, h2, h3, h4) • INSERT INTO table (s1, s2, s4) VALUES (h1, h2, h4) • vkládání více řádků výčtem hodnot • INSERT INTO table VALUES (h11, h12, h13),(h21, h22, h23) • vkládání více řádků, jejichž hodnoty vzniknou jako výsledek dotazu • INSERT INTO tabulka | (výčet atributů) | (SELECT ... FROM ...)

INSERT INTO název_tabulky [(náz_sloupce]] zdroj Zdroje pro vkládaní dat: Řádek implicitních hodnot – z příkazu CREATE TABLE DEFAULT_VALUES Řádek zadaných hodnot VALUES (skalarní_výraz|NULL|DEFAULT,…) Výsledek poddotazu tabulkový_výraz

Příklad INSERT INTO Klient VALUES (‘601012/1224‘,‘Jan‘,‘Nový‘,‘Jánská 25‘,‘Zlín‘) INSERT INTO Klient (r_cislo, jmeno, prijmeni, ulice, mesto) VALUES (‘826111/5267‘,‘Petra‘,‘Nováková‘,‘Poštová 128‘,‘Lešná‘), (‘580506/4891‘,‘Pavel‘,‘Nováček‘,‘Nezvalova 697‘,‘Zlín‘)

UPDATE • aktualizace záznamů splňujících podmínku • hodnoty zvolených atributů vybraných záznamů jsou nastaveny na • NULL • hodnotu expression (např. konstanta) • výsledek dotazu

Aktualizace dat UPDATE název_tabulky SET sloupec = ’hodnota’; Pro změnu více hodnot se používá zápis: UPDATE název_tabulky SET sloupec1 = ’hodnota1’, sloupec2 = ’hodnota2’,…; Použití klauzule WHERE: UPDATE název_tabulky SET sloupec1 = ’hodnota1’ WHERE sloupec2 = ’hodnota2’;

Příklad UPDATE Klient SET jmeno =‘Jakub’ WHERE r_cislo = ‘601012/1224’ UPDATE Ucet AS u SET stav = stav+ (SELECTcastkaFROMTransakce AS t WHERE u.c_uctu = t.c_uctu)

DELETE FROM • vymaže záznamy, které splňují podmínku • bez nadefinování podmínky vymaže všechny záznamy DELETE FROM název_tabulky WHERE sloupec = ’hodnota’;

Odstraňování dat POZOR! – po odstranění záznamu už není možné záznam obnovit. Je dobré mít data pro jistotu zálohovaná. Při odstraňování dat je nutné používat podmínku WHERE, jinak se může stát, že z tabulky odstraníte všechna data! Chcete-li zajistit větší bezpečnost před nechtěným odstraněním záznamů, můžete použít klauzuli LIMIT, která omezí počet odstraňovaných záznamů: DELETE FROM název_tabulky WHERE sloupec = ’hodnota’LIMIT 1;

Základní obeznámení s jazykem SQL