2. blok část A Jazyk SQL, datové typy

2. blok – část A

Jazyk SQL, datové typy

Studijní cíl Tento blok je věnován jazyku SQL, jeho vývoji, standardizaci a problémy s přenositelností. Dále je zde uveden přehled datových typů dle standardu SQL a v databázové platformě Oracle. Doba nutná k nastudování

2 - 3 hodiny

Průvodce studiem Při studiu tohoto bloku se předpokládá, že čtenář je obeznámen se základy relačních databází. 1. Jazyk SQL Jazyk SQL je standardem v oblasti počítačových databázových jazyků. Je podporován stovkami databázových produktů a provozován jak na střediskových počítačích, tak i osobních počítačích či kapesních zařízeních. Naprostá většina informačních systémů pro velké podniky používá ke správě dat SQL. Jazyk SQL je základem databázových produktů největších softwarových firem jako Microsoft, Oracle či IBM, ale je hojně využíván i v „open-source“ řešeních. V této kapitole najdeme odpovědi na otázky:   

Co je jazyk SQL? Jak je to se standardizací SQL a rozdílností databázových produktů? Jak se liší jednotlivé produkty postavené na jazyce SQL?

1.1. Jazyk SQL a jeho historie Jazyk SQL je nástroj pro organizování, správu a získávání dat uložených v počítačové databázi. Zkratka SQL znamená Structured Query Language – tedy strukturovaný dotazovací jazyk, který je určen pro práci s relačními databázemi. Jazyk SQL navazuje na jazyk SEQUEL (Structured English Query Language), který byl vytvořen v 70. letech 20. století ve firmě IBM, jako sada příkazů pro práci s relačními databázemi. Tyto příkazy se měly syntakticky podobat přirozenému jazyku (samozřejmě v angličtině). K vývoji jazyka se následně přidaly i další firmy. V roce 1979 představila firma Relational Software, Inc. (dnešní Oracle Corporation) svoji relační databázovou David Žák, Jiří Zechmeister, Tomáš Váňa IDAS1/2A – Jazyk SQL, datové typy

1

platformu Oracle Database. IBM uvedla v roce 1981 nový systém SQL/DS a v roce 1983 systém DB2. Dalšími systémy byly např. Progres, Informix a SyBase. Ve všech těchto systémech se používala varianta jazyka SEQUEL, který byl následně přejmenován na SQL. Význam relačních databází se rychle zvyšoval, stejně jako počty databázových produktů a proto bylo nezbytné dotazovací jazyk standardizovat. 1.2. Standardizace Americký institut ANSI chtěl původně vydat jako standard zcela nový jazyk RDL. Rychlý nárůst popularity SQL způsobil, že byl v roce 1986 Americkým národním standardizačním institutem (ANSI) a v roce 1987 Mezinárodní standardizační organizací (ISO) vydán jako standard. Rok

Název

Alias

Poznámka

1986 SQL-86

SQL-87

První verze ANSI.

1989 SQL-89

FIPS 127-1

Menší revize, která byla přijata jako FIPS 127-1.

1992 SQL-92

SQL2, FIPS 127-2

Zásadní revize (ISO 9075), základní úroveň SQL-92 přijata jako FIPS 127-2.

1999 SQL:1999 SQL3

Přidána práce s regulárními výrazy, rekurzivní dotazy, triggery, podpora procedur, neskalární datové typy a některé objektově orientované rysy.

2003 SQL:2003 SQL 2003

Představeny XML vlastnosti, „window“ funkce, standardizovány sekvence a sloupce s automaticky generovanými hodnotami.

2006 SQL:2006 SQL 2006

ISO / IEC 9075-14:2006 definuje způsoby, jak SQL jazyk může být použit ve spojení s XML, importy a ukládání XML dat v databázi, práce s XML daty a konverze mezi SQL a XML formou. Využití XQuery, XML Query Language vydané World Wide Web Consortium (W3C).

2008 SQL:2008 SQL 2008

Legalizuje klauzuli ORDER BY mimo definici kurzoru. Instead of triggery. Příkaz TRUNCATE.

David Žák, Jiří Zechmeister, Tomáš Váňa IDAS1/2A – Jazyk SQL, datové typy

2

1.3. Přenositelnost Existuje několik důvodů, které komplikují přenositelnost SQL kódu mezi databázovými systémy:



SQL standard je složitý a tak ne všichni implementátoři podporují celý standard.



Norma nespecifikuje chování databáze v několika důležitých oblastech (např. indexy, ukládání souborů ...), takže implementátoři se mohou rozhodnout, jak tyto činnosti implementovat.



SQL standard přesně specifikuje syntaxi, kterou musí kompatibilní databáze splnit. Standardní specifikace pro sémantiku jazyka je méně dobře definovaná, což vede k nejasnostem.



Mnoho prodejců databázových systémů má velké existující zákaznické základny, kde SQL standard se střetává s předchozím chováním databáze a prodejce může být neochotný ztratit zpětnou kompatibilitu.



V některých případech mají prodejci zájem vytvořit či zachovat nekompatibility s jinými produkty, protože zajišťují silnou motivaci pro stávající uživatele zůstat loajální.

Obecně je třeba počítat s tím, že aplikace musí být modifikována, aby mohla být přenesena na jinou SQL databázi. 2. Architektura databází Při vytváření databází je potřeba zvolit, jaký typ architektury je pro dané účely nejvhodnější s přihlédnutím k technologickým a finančním možnostem. 2.1. Jednovrstvé (centrální) systémy Jde se o zastaralý model s použitím centrálního počítače. Jednotliví uživatelé používali pouze terminál, na který se přenášely informace o rozložení obrazovky obsahující zobrazovaná data. S tímto modelem se stále můžeme setkat obzvláště u některých velkých firem. 2.2. Dvouvrstvá architektura (klient/server) Zde hovoříme o dvou podtypech těchto architektur: Architektura s výkonem soustředěným u klienta - veškeré aplikační a uživatelské služby se zpracovávají u klienta (tlustý klient). Velkou slabinou této architektury byly potřebné přenosové kapacity, kdy mezi klientem a serverem musí probíhat velký počet datových přenosů. David Žák, Jiří Zechmeister, Tomáš Váňa IDAS1/2A – Jazyk SQL, datové typy

3

Obrázek 1 - Architektura s výkonem soustředěným u klienta (zdroj: http://www.fs.vsb.cz/books/MSSQLServer/MSSQL_soubory/SQL_index3.htm) Architektura s výkonem soustředěným na serveru - ke klientu se přesouvají pouze uživatelské služby a dostává pouze požadované informace (tenký klient). Aplikační a datové služby probíhají na serveru.

Obrázek 2 - Architektura s výkonem soustředěným na serveru (zdroj: http://www.fs.vsb.cz/books/MSSQLServer/MSSQL_soubory/SQL_index3.htm) 2.3. Třívrstvá architektura U třívrstvé architektury lze shledat určitou podobnost s již uvedeným modelem architektury soustředěné na serveru. Klient pracuje pouze s uživatelským rozhraním, avšak datové a aplikační služby jsou od sebe odděleny do samostatných logických modelů, které mohou být umístěny buď na stejném serveru, nebo na dvou různých serverech. Třívrstvý model nám umožňuje získat vyšší úroveň stability, jelikož provozní zátěž může běžet na dvou serverech. Jedním z příkladů třívrstvé architektury jsou i internetové služby. Klientskou vrstvu zajišťuje webový prohlížeč, aplikační vrstvu pak webový a aplikační server, který komunikuje s databázovou vrstvou databázovým serverem prostřednictvím SQL příkazů.


4

Obrázek 3 - Třívrstvá architektura (zdroj: http://www.fs.vsb.cz/books/MSSQLServer/MSSQL_soubory/SQL_index3.htm)

3. Skupiny příkazů SQL jazyka Základem jazyka SQL je přibližně 40 příkazů. Standardní příkazy SQL jazyka dělíme do skupin:    

Příkazy pro definici dat Příkazy pro manipulaci s daty Příkazy pro řízení dat Ostatní příkazy

(DDL) (DML) (DCL)

3.1. Příkazy pro definici dat Těmito příkazy se vytvářejí struktury databáze – tabulky, indexy, pohledy a další objekty. Vytvořené struktury lze také upravovat, doplňovat a mazat. Tato skupina příkazů se nazývá zkráceně DDL – Data Definition Language („jazyk pro definici dat“). CREATE

– vytváření nových objektů například CREATE TABLE, CREATE VIEW, CREATE INDEX …

ALTER

– změny struktury existujících objektů například ALTER TABLE

DROP

– odstraňování objektů například DROP TABLE, DROP VIEW, DROP INDEX …


5

3.2. Příkazy pro manipulaci s daty Příkazy pro získání dat z databáze a pro jejich úpravy se označují zkráceně DML (data Manipulation Language). SELECT – vyhledá data z databáze INSERT – vkládá do databáze nová data (řádky) UPDATE – modifikuje data v databázi (řádky). DELETE – odstraňuje data (řádky) z databáze. EXPLAIN PLAN FOR – speciální příkaz, který zobrazuje postup zpracování SQL příkazu. Pomáhá uživateli optimalizovat příkazy tak, aby byly rychlejší.

3.3. Příkazy pro řízení dat Do této skupiny patří příkazy pro nastavování přístupových práv, řízení provozu a údržby databáze transakcí. Označují se jako DCL – Data Control Language („jazyk pro ovládání dat“), GRANT – příkaz pro přidělení oprávnění uživateli k určitým objektům. REVOKE – příkaz pro odnětí práv uživateli. ALTER USER DROP USER

3.4. Příkazy pro řízení dat – skupina příkazů pro řízení transakcí Označují se TCC – Transaction Control Commands („jazyk pro ovládání transakcí"). COMMIT – potvrzení transakce. ROLLBACK – zrušení transakce, návrat do původního stavu. SAVEPOINT SET TRANSACTION

3.5. Ostatní příkazy Do této skupiny patří příkazy pro správu databáze. Pomocí nich lze nastavovat systémové parametry (kódování znaků, způsob řazení, formáty data a času apod.). Tato skupina není standardizována a konkrétní syntaxe příkazů je závislá na databázovém systému.


6

4. Standardní datové typy v jazyce SQL Každý sloupec v SQL tabulce deklaruje typ hodnot, které může sloupec obsahovat. ANSI SQL obsahuje následující typy dat. 4.1. Řetězce  

CHARACTER(n) nebo CHAR(n) — řetězec znaků pevné délky (n) znaků CHARACTER VARYING(n) nebo VARCHAR(n) — řetězec znaků proměnné

délky maximálně (n) znaků 

NATIONAL CHARACTER(n) nebo NCHAR(n) — řetězec národních znaků

pevné délky (n) znaků 

NATIONAL CHARACTER VARYING(n) nebo NVARCHAR(n) — řetězec

znaků proměnné délky maximálně (n) znaků 4.2. Řetězce bitů  

BIT(n) — řetězec bitů s pevnou délkou (n) bitů BIT VARYING(n) — řetězec bitů s proměnnou délkou maximálně (n) bitů

4.3. Čísla     

INTEGER nebo INT – celá čísla SMALLINT – malá celá čísla FLOAT(přesnost) – čísla s plovoucí desetinnou tečkou REAL – čísla s plovoucí desetinnou tečkou s nízkou přesností DOUBLE PRECISION - čísla s plovoucí desetinnou tečkou s vysokou

přesností 

NUMERIC(přesnost, měřítko)nebo DECIMAL(přesnost, měřítko) nebo DECT(přesnost, měřítko) – desetinná čísla

4.4. Datum a čas      

DATE — datum (např. 2011-05-03) TIME(přesnost) — čas (např. 15:51:36). TIME WITH TIME ZONE nebo TIMETZ — stejně jako TIME, ale obsahující

ještě informaci o časové zóně TIMESTAMP(přesnost) — Datum a Čas s vyšší přesností TIMESTAMP(přesnost) WITH TIME ZONE nebo TIMESTAMPTZ — stejně jako TIMESTAMP, ale obsahující ještě informaci o časové zóně INTERVAL – časové intervaly


7

5. Datové typy v Oracle Datové typy používané v Oracle databázích se mírně liší od datových typů popsaných v standardech SQL. Mezi základními odlišnostmi můžeme zmínit používání VARCHAR2 místo VARCHAR, typu NUMBER pro všechny číselné typy, typ DATE obsahuje nejen datum, ale i čas. Navíc některé další datové typy jsou přidány. Datový typ

Délka

Popis

VARCHAR2(size)

4000 B

Řetězec znaků proměnné délky

NVARCHAR2(size)

4000 B

Řetězec národních znaků proměnné délky

VARCHAR

Zastaralý (pouze pro zpětnou kompatibilitu)

CHAR(size)

2000 B, defaultně 1 B

Řetězec znaků pevné délky

NCHAR(size)

2000 B, defaultně 1 B

Řetězec národních znaků pevné délky

NUMBER přesnost,měřítko)

Přesnost od 1 do 38. Měřítko od -84 do 127.

Čísla s udanou přesností a měřítkem

PLS_INTEGER

Pouze v kódu PL/SQL

Označená číselná hodnota PLS_INTEGER hodnoty jsou úspornější a poskytnou lepší výkon než číselné hodnoty Označená číselná hodnota (starší pomalejší verze PLS_INTEGER)

BINARY_INTEGER

LONG

2 GB

Znakový řetězec proměnné délky (větší verze VARCHAR2)

DATE

Od 1.1.4712 př.n.l. do 31.12.9999

Platný datum a čas s přesností na celé sekundy

TIMESTAMP (přesnost_sekund)

přesnost_sekund 0 až 9 (defaultně 6)

Datum a čas s přesností na uvedený počet desetinných míst sekundy

TIMESTAMP (přesnost_sekund) WITH {LOCAL} TIMEZONE

přesnost_sekund 0 až 9 (defaultně 6)

Jako TIMESTAMP, ale s hodnotou časového posunu

INTERVAL YEAR (přesnost_let) TO MONTH

přesnost_let 0 až 9 (defaultně 2)

Časový interval v letech a měsících, kde přesnost_let je počet číslic v poli YEAR (ROK).

INTERVAL DAY (přesnost_dní) TO SECOND (přesnost_sekund)

přesnost_dní 0 až 9 (defaultně 2) přesnost_sekund 0 až 9 (defaultně 6)


Časový interval ve dnech, hodinách, minutách a vteřinách, přesnost_dní je maximální počet číslic udávajících počet dní, přesnost_sekund je maximální počet

8

desetinných míst sekundy. RAW(size)

2000 B

Nezpracovaná binární data v bytech

LONG RAW

2 GB

Nezpracovaná binární data proměnné délky

ROWID

10 B

Hexadecimální řetězec reprezentující unikátní adresu řádku v tabulce. (především určeno pro hodnoty vrácené pseudosloupcem ROWID)

UROWID

Maximum i defaultně 4000 B

Hexadecimální řetězec reprezentující logickou adresu řádků v indexověorganizované tabulce

CLOB

8 TB až 128 TB

Objemná data ve znakové podobě

NCLOB

8 TB až 128 TB

Objemná data v podobě národních znaků

BLOB

8 TB až 128 TB

Binární objemná data

BFILE

Dle limitu OS

Ukazatel na binární soubor na disku (mimo databázi)

XMLType

4 GB

XML data

Pojmy k zapamatování Příkazy a funkce: DCL, TCC

Jazyk

SQL,

skupiny

příkazů

DLL,

DML,

Problém: struktura jazyka SQL, rozdělení příkazů do skupin, datové typy dle standardu jazyka SQL a datové typy v Oracle

Shrnutí V této lekci jste se seznámili s jazykem SQL v roli standardního jazyka pro práci s relačními databázemi.  Jazyk SQL je popsán několika standardy.  Míra kompatibility se standardy se v různých databázových produktech liší.  Databázové produkty se liší i v míře implementace standardů SQL jazyka, proto je přenositelnost SQL kódu omezená.  SQL jazyk je velice rozšířeným standardem.


9

Otázky na procvičení 1. 2. 3. 4. 5.

Charakterizujte jazyk SQL? Uveďte přehled standardizace jazyka SQL? Jak je to přenositelností SQL kódu mezi různými databázovými platformami? Vysvětlete rozdíl mezi datovým typem CHAR(x) a VARCHAR(x)? Popište datový typ v Oracle, který se používá pro ukládání čísel?

Odkazy a další studijní prameny    

http://www.techonthenet.com/oracle (syntaxe příkazů SQL jazyka a funkcí) http://www.sqlcourse.com (interaktivni SQL trénink) http://www.oracle.com/technetwork/database/enterpriseedition/documentation (dokumentace k databázové platformě Oracle) http://krokodata.vse.cz/SQL/SQL (Krokodýlovy databáze – o jazyce SQL)

Odkazy a další studijní prameny   

GROFF, J.R., WEINBERG, P.N. SQL - kompletní průvodce. Praha: Computer Press, 2005. ISBN 80-251-0369-2. ŠIMŮNEK, M. SQL, kompletní kapesní průvodce. Grada Publishing, 1999. ISBN 80-7169-692-7. STEPHENS, K.R., PLEW, R.R. Naučte se SQL za 21 dní. Praha: Computer Press, 2004. ISBN 80-7226-870-8.


10

2. blok část A Jazyk SQL, datové typy

Recommend Documents