Databázové systémy. Cvičení 6: SQL

Databázové systémy

Cvičení 6: SQL

Co je SQL? ●

SQL = Structured Query Language

●

SQL je standardním (ANSI, ISO) textovým počítačovým jazykem

●

SQL umožňuje jednoduchým způsobem –

přistupovat k datům v databázi

–

vznášet dotazy na data (agregační funkce apod.),

–

vyzvedávat data z databáze (dataset → text, tabulka, XML,...),

–

vkládat nové záznamy do databáze,

–

mazat záznamy z databáze,

–

aktualizovat záznamy v databázi,

–

měnit strukturu databáze (struktura záznamů, indexy, ...)

… standardní počítačový jazyk pro přístup a manipulaci s databází.

SQL je standard, ale... ●

●

●

„Standardní“ SQL nalezneme v mnoha DB prostředích (MS Access, DB2, Informix, MS SQL Server, Oracle, Sybase, ...) Všechna tato prostředí obsahují různě zpracované interpretery SQL, základní operace stanovené ANSI (např. příkazy SELECT, UPDATE, DELETE, INSERT, klauzule WHERE, …) však obsahují všechny. Nejčastěji diskutované rozdíly v různých interpretacích SQL spočívají v proprietárních rozšířeních původního standardu.

Data ke kterým přistupujeme SQL ●

●

●

Databáze je zpravidla tvořena jednou nebo více tabulkami. Každá tabulka je jednoznačně identifikovatelná svým jménem (např. Adresář, Objednávky..). Tabulky obsahují záznamy/struktury s daty, např. Jméno: Alois Kryšpín Felix Tadeáš

Příjmení: Vocásek Jelítko Bůček Jelítko

Adresa: Severní 10 Jižní 11 Západní 3 Východní 8

Město: Brno Praha Ostrava Plzeň

PSČ: 606 00 116 00 825 00 322 00

SQL: Jazyk pro manipulaci s daty (DML) ●

SQL (Structured Query Language) je syntaktický předpis pro vykonávání dotazů na data: –

●

SELECT

Příjmení FROM Adresář Where PSČ LIKE '6?? ??'

Obsahuje však i syntaktické struktury pro manipulace se záznamy: –

UPDATE – aktualizace dat v určeném záznamu dané tabulky

–

DELETE – smazání vybraných záznamů z konkrétní tabulky

–

INSERT INTO – vložení nového záznamu do dané tabulky

SQL: SELECT ●

Základní syntax: SELECT column_name(s) FROM table_name; –

Výběr všech sloupců zvolené tabulky: symbol „*“ SELECT * FROM table_name;

–

Příklad výběru zvolených sloupců: SELECT Příjmení, Jméno FROM Adresář; → Příjmení: Jméno: Vocásek Alois Jelítko Kryšpín Bůček Felix Jelítko Tadeáš

–

Výběr jen unikátních záznamů: Prefix „DISTINCT“ SELECT DISTINCT Příjmení FROM Adresář;

SQL: SELECT ●

Klauzule aliAS: Aliasy mohou být využity pro tabulky i sloupce: –

SELECT column AS column_alias FROM table; (typicky pro vlastní pojmenování sloupců na výstupu)

–

SELECT column FROM table AS table_alias;

(typicky pro substituci jmen při definici relací uvnitř jedné tabulky) ●

Klauzule OrderBy: Předpis pro třídění řádek výstupu, např.: –

SELECT Jméno, Příjmení FROM Adresář ORDER BY Příjmení, Jméno;

–

Třídění v opačném pořadí: suffix DESC SELECT Distinct Příjmení FROM Adresář ORDER BY Příjmení DESC;

SQL: SELECT ●

Klauzule Where: Definice pravidel pro výběr záznamů: SELECT <sloupce> FROM Where ; –

Jednotlivé podmínky je možné logicky spojovat pomocí And/Or

–

K definici vyhodnocovacích podmínek je možné použít ●

●

●

●

Porovnávání ordinárního obsahu polí: < , >, =, < > (nebo !=) , > =, < = SELECT ... WHERE PSČ < '600 00'; Test přítomnosti řetězce ve výrazu: LIKE <řetězec> SELECT ... WHERE Jméno Like 'x'; Test zda hodnota výrazu leží mezi dvěma mezemi: Between .. And .. SELECT ... WHERE Příjmení BETWEEN 'D' AND 'T'; Test zda výraz nabývá něktré z hodnot: IN (hodnota1, hodnota2,..) SELECT ... WHERE Příjmení IN ('Vocásek','Buřt');

SQL: SELECT ●

Formátování výstupů dotazu: –

Group By: Seskupování (agregace) dat dle určitého kritéria: SELECT Partner, SUM(platba) FROM PenezniDenik GROUP BY Partner;

–

Having: Dodatečné filtrování výstupních (typicky agregovaných) dat SELECT Partner, SUM(platba) FROM PenezniDenik GROUP BY Partner Having SUM(platba)>1000000;

SQL: SELECT ●

Agregační funkce (výběr): –

AVG(sloupec) :

Aritmetický průměr agregovaných hodnot

–

COUNT(sloupec) : Počet neprázdných řádek v daném sloupci

–

COUNT(*) :

Počet vybraných řádek

–

FIRST(sloupec) :

První nalezená hodnota v daném sloupci

–

LAST(sloupec) :

Poslední nalezená hodnota v daném sloupci

–

MAX(sloupec) :

Nejvyšší nalezená hodnota v daném sloupci

–

MIN(sloupec) :

Nejmenší nalezená hodnota v daném sloupci

–

SUM(sloupec) :

Součet nalezených hodnot v daném sloupci

SQL: SELECT ●

Skalární funkce: Funkce na úrovni jednotlivých hodnot –

UCASE (c) :

Převod výrazu c na velká písmena

–

LCASE (c) :

Převod výrazu c na malá písmena

–

MID (c, první[,poslední]) : Výběr zvoleného počtu znaků z textového výrazu c

–

LEN (c) :

Délka textového výrazu c

–

INSTR (c) :

Numerická pozice substringu ve výrazu c

–

LEFT (c, n) :

Výběr prvních n znaků v textovém výrazu c

–

RIGHT (c, n):

Výběr posledních n znaků v textovém výrazu c

–

ROUND (c, místa):

Zaokrouhlení na daný počet desetinných míst

–

MOD (x, y) :

Vrací výsledek x mod y (zbytek po dělení)

–

NOW () :

Vrací aktuální systémové datum (bez parametru)

–

FORMAT (c, format) :

Změna formátu zobrazení pole

–

DATEDIFF (d, datum1, datum2) : Datové kalkulace

SQL: SELECT Propojování tabulek je v SQL realizováno pomocí klíčů tabulek a konstrukcí ●

●

●

●

WHERE: (výpis všech řádků na kterých je shoda v „levé“ i „pravé“ tabulce) SELECT Zam.Jmeno, Obj.Produkt FROM Zam, Obj WHERE Zam.ID=Obj.Vystavil_ID INNER JOIN: (dtto) SELECT Zam.Jmeno, Obj.Produkt FROM Zam INNER JOIN Obj ON Zam.ID=Obj.Vystavil_ID LEFT JOIN: (výpis všech záznamů „levé“ tabulky, doplněných o data z Obj) SELECT Zam.Jmeno, Obj.Produkt FROM Zam LEFT JOIN Obj ON Zam.ID=Obj.Vystavil_ID RIGHT JOIN (výpis všech záznamů „pravé“ tabulky, doplněných o data Zam) SELECT Zam.Name, Obj.Produkt FROM Zam RIGHT JOIN Obj ON Zam.ID=Obj.Vystavil_ID

SQL: Poddotazy ●

●

Při tvorbě výrazů v SQL dotazech můžeme místo odkazů na hodnoty ve „fyzických“ sloupcích použít i odkazy na „virtuální“ tabulky, vytvářené dynamicky pomocí tzv. „vnořených“ SQL dotazů Příklad: SELECT jméno, příjmení FROM platy WHERE plat > (SELECT AVG(plat) FROM platy)

SQL : UNION ●

Sloučení výstupů více dotazů: Spojka UNION SQLquery1 UNION SQLquery2;

SQL: UPDATE, INSERT INTO, DELETE Modifikace dat v tabulkách je možná příkazy: ●

●

●

INSERT INTO (<seznam sloupců>) VALUES (<seznam hodnot>); DELETE FROM WHERE <podmínky pro výběr řádek> UPDATE SET <sloupce> = WHERE <podmínky pro výběr řádek>;

SQL: Jazyk pro definici dat (DDL) ●

CREATE TABLE – vytvoření nové tabulky

●

ALTER TABLE – změna struktury tabulky

●

DROP TABLE – zrušení dané tabulky

●

CREATE INDEX – vytvoření indexu (klíče)

●

DROP INDEX – zrušení daného indexu

SQL: CREATE ●

CREATE TABLE table_name( NázevSloupce1 ,

NázevSloupce2 ,...)

Používané typy:

●

–

integer(n), int(n), smallint(n), tinyint(n): Celočíselné, n je max. počet míst

–

decimal(n,d), numeric(n,d): Desetinná čísla, d je počet desetinných míst.

–

char(délka): Řetězec s pevně danou délkou.

–

varchar(max_délka): Řetězec s danou maximální délkou.

–

date: Datum

CREATE UNIQUE INDEX jméno_indexu ON table_name (<seznam sloupců zahrnutých v indexu>)

SQL: TRUNCATE a DROP Smazání dat v tabulce, zachování struktury: ●

TRUNCATE TABLE table_name

Změna struktury: ●

ALTER TABLE table_name ADD jméno_odstavce DROP COLUMN <jména rušených odstavců>

Odstranění stávajících struktur: ●

DROP INDEX table_name.index_name

●

DROP TABLE table_name

●

DROP DATABASE database_name