1 / 31

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ý)

ura
Download Presentation

Základní obeznámení s jazykem SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


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

  2. Ú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.

  3. 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

  4. 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

  5. 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.

  6. 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í

  7. 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

  8. 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

  9. 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í

  10. 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

  11. 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

  12. 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

  13. 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.

  14. 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.

  15. 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

  16. 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

  17. 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 )

  18. 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

  19. 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í];

  20. Ú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]

  21. 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;

  22. 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.

  23. 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

  24. 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 ...)

  25. 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

  26. 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‘)

  27. 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

  28. 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’;

  29. 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)

  30. 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’;

  31. 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;

More Related