Použití jazyka SQL

Abstrakt

Velmi stručný úvod do problematiky jazyka SQL – spíše z praktické stránky jeho použití pro definici dat a manipulaci s nimi. Příklady možno testovat na databázi demo (iData_Demo).

Obsah

Abstrakt 1

Obsah 1

1. Definice dat............................................................................................................................ 2

1.1. Vytváření tabulek - CREATE TABLE.................................................................................... 2

1.2. Změna tabulek - ALTER TABLE.......................................................................................... 3

1.3. Vymazání tabulky - DROP TABLE....................................................................................... 3

1.4. Vytvoření indexu - CREATE INDEX..................................................................................... 3

1.5. Smazání indexu - DROP INDEX.......................................................................................... 3

1.6. Práce s pohledy................................................................................................................ 3

2. Manipulace s daty................................................................................................................... 4

2.1. Vložení hodnot do tabulky – INSERT.................................................................................. 4

2.2. Aktualizace hodnot tabulky – UPDATE................................................................................ 4

2.3. Rušení dat – DELETE........................................................................................................ 4

2.4. Výběr dat z tabulek - SELECT............................................................................................ 4

2.5. Strukturované dotazy – vnořené subdotazy........................................................................ 7

3. Přílohy.................................................................................................................................... 8

3.1. Datové typy SQL serveru................................................................................................... 8

3.1.1. Čísla.......................................................................................................................... 8

3.1.2. Ukládáme datum a čas............................................................................................... 8

3.1.3. Ukládáme řetězce...................................................................................................... 9

3.1.4. Ukládáme binární data............................................................................................... 9

3.1.5. Ostatní...................................................................................................................... 9

3.2. Užitečné funkce SQL serveru........................................................................................... 10

4. Datová základna IMES........................................................................................................... 11

5. Užitečné nástroje vývoje........................................................................................................ 11

1. Definice dat

1.1. Vytváření tabulek - CREATE TABLE

· základní údaje: jméno, sloupce a jejich datové typy

Př. 1 – základní založení

CREATE TABLE [BARVYGRID](

[TAB_STAV] [tinyint],

[POPREDI] [int],

[POZADI] [int],

[FONTSTYLE] [varchar](5),

[POZNAMKA] [varchar](50))

Př. 2 – null a not null

(povolení nebo zakázání nulových hodnot)

CREATE TABLE [BARVYGRID](

[TAB_STAV] [tinyint] NOT NULL,

[POPREDI] [int] NULL,

[POZADI] [int] NULL,

[FONTSTYLE] [varchar](5) NULL,

[POZNAMKA] [varchar](50) NULL,

CONSTRAINT [PK_ BARVYGRID] PRIMARY KEY CLUSTERED

(

[TAB_STAV] ASC)

)

· SQL server považuje implicitně (neuvede-li se nul nebo not null) sloupce za not null

Př. 3 – Primary key

CREATE TABLE [BARVYGRID](

[TAB_STAV] [tinyint] NOT NULL,

[POPREDI] [int] NULL,

[POZADI] [int] NULL,

[FONTSTYLE] [varchar](5) NULL,

[POZNAMKA] [varchar](50) NULL,

CONSTRAINT [PK_ BARVYGRID] PRIMARY KEY CLUSTERED

(

[TAB_STAV] ASC)

)

Př. 5 – počítadlo - IDENTITY

· vlastnost sloupce, nikoliv datový typ

· lze deklarovat na libovolném celočíselném typu

· v každé tabulce může být pouze jeden sloupec s vlastností IDENTITY

· lze stanovit počáteční číslo a inkrement nebo dekrement (pokud se nespecifikuje, je to 1 a 1)

CREATE TABLE [BARVY](

[BARVY_ID] [int] IDENTITY(1,1) NOT NULL,

[TAB_STAV] [tinyint] NOT NULL,

[POPREDI] [int] NULL,

[POZADI] [int] NULL,

[FONTSTYLE] [varchar](5) NULL,

[POZNAMKA] [varchar](50) NULL,

CONSTRAINT [PK_BARVY] PRIMARY KEY CLUSTERED

(

[TAB_STAV] ASC)

)

· generování hodnot lze dočasně pozastavit volbou SET IDENTITY_INSERT [TABULKA] OFF/ON, po opětném spuštění je další inkrementovaná hodnota odvozena z nejvyšší hodnoty zvětšené o inkrement, po vypnutí lze sloupec updatovat

· dále: DEFAULT, UNIQUE, IDENTITY …

· pořadí kontrol integrity:

· Aplikace implicitních hodnot

· Narušení NOT NULL

· Omezení CHECK

· Podmínky FOREIGN KEY – kontroly odkazujících se tabulek

· Podmínky FOREIGN KEY – kontroly odkazovaných tabulek

· Podmínky UNIQUE a PRIMARY KEY

· Vyvolání triggerů

1.2. Změna tabulek - ALTER TABLE

Př. 1 – přidání sloupce do tabulky BARVYGRID

ALTER TABLE BARVYGRID

ADD COLUMN POPIS varchar (MAX) NULL

Př. 2 – změna vlastnosti sloupce POPIS

ALTER TABLE BARVYGRID

ALTER COLUMN POPIS varchar (100)

Př. 3 – vymazání sloupce

ALTER TABLE BARVYGRID

DROP COLUMN POPIS

1.3. Vymazání tabulky - DROP TABLE

Př. 1

DROP TABLE BARVYGRID

1.4. Vytvoření indexu - CREATE INDEX

CREATE [UNIQUE] [CLUSTERED] [NONCLUSTERED] INDEX jméno_indexu ON tabulka(jménosloupce,[jménosloupce], …)

· CLUSTERED index by měl být vytvořen bouze jeden na tabulacem protože zajišťuje fyzické uskupovaní stánek, na které se index odkazuje

1.5. Smazání indexu - DROP INDEX

DROP INDEX názevindexu

1.6. Práce s pohledy

CREATE VIEW

ALTER VIEW

DROP VIEW

2. Manipulace s daty

2.1. Vložení hodnot do tabulky – INSERT

INSERT INTO názevtabulky [(výčetatributů)]

{VALUES (výčethodnotatributů) | SELECT-příkaz}

Př. 1 – vložení řádku do tabulky C0042

INSERT INTO [C0042]([STREDISKO],[VYKON],[DOPLN])

VALUES ('910','032',1)

Př. 2 – vložení řádků do tabulky na základě výběru pomocí SELECT

INSERT INTO [C0042]([STREDISKO],[VYKON],[DOPLN])

(select c0001.[STREDISKO],c0002.[VYKON],'1'

from c0001, c0002

where not exists (select 1 from c0042 where c0042.stredisko = c0001.stredisko and c0042.vykon = c0002.vykon))

Př. 3 – vložení řádků do tabulky z jiní databáze

INSERT INTO [C0042]([STREDISKO],[VYKON],[DOPLN])

(select [STREDISKO],[VYKON],DOPLN from idata_demo_test..C0042)

Př. 4 – odvození nové relace na základě výběru

Select STREDISKO,VYKON,DOPLN

into #PRAC

from C0042

select * from # PRAC

2.2. Aktualizace hodnot tabulky – UPDATE

UPDATE názevtabulky

SET názevatributu = výraz [, …]

[WHERE podmínky]

Př. 1 – prostá aktualizace všech řádek

UPDATE C0204

SET ZEME = 'CZ'

Př. 2 – aktualizace za podmínek

UPDATE C0204

SET ZEME = 'CZ'

WHERE ISNULL(ZEME,'') = ''

Př. 3 – aktualizace tabulek chráněných triggerem

update t81sal set mena = 'CZK', akt = 'SALDO'

where porcislof = '8887744' and radaf = '1544' and MENA <> 'CZK'

2.3. Rušení dat – DELETE

Př. 1 – rušení za podmínek

DELETE FROM C0204

WHERE ZEME = 'JP'

Př. 2 – rušení z tabulek chráněných triggerem

update t81sal set akt = 'SALDOD'

where porcislof = '8887744' and radaf = '1544'

2.4. Výběr dat z tabulek - SELECT

· obecné možnosti:

SELECT [DISTINCT] <výčet sloupců>

FROM <jména tabulek>

[JOIN] <v případě více tabulek deklarace způsobu jejich provázání>

[WHERE] <kritéria, která má splňovat řádek, aby byl vybrán>

[GROUP BY] <sloupce pro seskupení agregačních funkcí>

[HAVING] <kritéria, která musí být splněna v agregační funkci>

[ORDER BY] <určení způsobu třídění výsledků dotazu>

Př. 1 – jednoduchý výběr

SELECT *

FROM T20KFH

SELECT rada, skupdokl,PORCISLO, PARTNER, PARTNERN, OBDOBIZUCT, CASTKAUHR

FROM T20KFH

Př. 2 – výběr s přejmenováním sloupce

SELECT SD = skupdokl,Faktura = PORCISLO, Obdobi = OBDOBIZUCT

FROM T20KFH

Př. 3 – výběr za podmínek – WHERE

SELECT rada, skupdokl,PORCISLO, PARTNER, PARTNERN, OBDOBIZUCT, CASTKAUHR

FROM T20KFH

WHERE OBDOBIZUCT > 201112 AND CASTKAUHR > 500

SELECT rada, skupdokl,PORCISLO, PARTNER, PARTNERN, OBDOBIZUCT, CASTKAUHR

FROM T20KFH

WHERE (OBDOBIZUCT BETWEEN 201112 AND 201212 AND PARTNER = '0000001002')

OR PARTNER = '0000001114'

Př. 4 – výběr z více tabulek – pomocí JOIN

SELECT T20KFH.rada, T20KFH.skupdokl,T20KFH.PORCISLO, T20KFH.PARTNER, T20KFH.PARTNERN, T20KFH.OBDOBIZUCT, T20KFH.CASTKAUHR, t20kfh.vykon, C0002.nazevvykonu

FROM T20KFH

LEFT JOIN C0002 ON C0002.vykon = T20KFH.vykon

· left join vypisuje všechny řádky z první tabulky i když jim neodpovídají (neexistují) hodnoty z druhé tabulky

SELECT T20KFH.rada, T20KFH.skupdokl,T20KFH.PORCISLO, T20KFH.PARTNER, T20KFH.PARTNERN, T20KFH.OBDOBIZUCT, T20KFH.CASTKAUHR,t20kfh.vykon,C0002.nazevvykonu

FROM T20KFH

inner JOIN C0002 ON C0002.vykon = T20KFH.vykon

· Inner join vypisuje jen takové řádky z první tabulky ke kterým existuje věta z druhé tabulky

Př. 6 – výběr z více tabulek a jejich přejmenování pro pohodlnější práci

SELECT T.rada, T.skupdokl,T.PORCISLO, T.PARTNER, T.PARTNERN, T.OBDOBIZUCT, T.CASTKAUHR,t.vykon,C2.nazevvykonu

FROM T20KFH T

inner JOIN C0002 C2 ON C2.vykon = T.vykon

Př. 7 – omezení počtu řádků klauzulí DISTINCT – potlačení duplicitních výstupních hodnot

SELECT DISTINCT RADA FROM t20kfh

• vypíše řady (výpis nebude obsahovat 2 shodné řádky)

Př. 8 – řazení - ORDER BY

SELECT T.skupdokl,T.rada, T.PORCISLO, T.PARTNER, T.PARTNERN, T.OBDOBIZUCT, T.CASTKAUHR,t.vykon,C2.nazevvykonu

FROM T20KFH T

left JOIN C0002 C2 ON C2.vykon = T.vykon

order by T.rada asc ,T.PORCISLO desc

· bez udání ASC (vzestupně) nebo DESC (sestupně) je standardně ASC

· název sloupce, podle kterého se má třídit, je možné nahradit číslem, které označuje jeho pořadí za slovem SELECT

Př. 9 – agregační funkce SUM, AVG, MIN, MAX

· hodnota agregační funkce může být odvozena

· ze všech řádek relace

· z výběru řádek, který je specifikovaný podmínkou WHERE

· ze skupin řádků definovaných kluzulí GROUP BY

· výsledkem je v prvém druhém případě jedna hodnota, ve třetím tolik hodnot, kolik je vytvořených skupin řádků

· použije-li se agregační funkce, nelze použít běžný výpis sloupce

· možnosti použití

SELECT SUM(CASTKAUHR), partner, max(partnern) FROM t20kfh

group by partner

· odvození průměrné hodnoty – AVG

SELECT avg(CASTKAUHR) FROM t20kfh

· odvození extrémů – MAX a MIN

SELECT MAX(CASTKAUHR), MIN(CASTKAUHR) FROM t20kfh

· odvození počtu hodnot – COUNT

SELECT COUNT(*) FROM t20kfh WHERE OBDOBIZUCT > 201112

Př. 10 – odvození souhrnných hodnot ze skupin údajů - GROUP BY

· GROUP BY mění rozsah aplikace agregačních funkcí z celé relace na skupiny řádků

· Skupina je množina řádků relace, které mají stejnou hodnotu určeného atributu; tyto atributy jsou určeny v klauzuli GROUP BY

· Každé skupině pak na výstupu odpovídá právě jeden řádek výsledných hodnot

SELECT SUM(CASTKAUHR), partner, max(partnern) FROM t20kfh

group by partner

· v příkladu je vidět, že v klauzuli SELECT je možné uvést pouze agregační funkce nad jednotlivými skupinami, nebo atributy, podle kterých jsou skupiny vytvořeny

· skupina může být vytvořena podle více skupinovacích atributů

SELECT partner,rada, obdobizuct,CASTKAUHR = SUM(CASTKAUHR), NAZEVPARTNERA = max(partnern) FROM t20kfh

group by partner, rada, obdobizuct

Př. 11 – podmíněný výstup souhrných údajů – HAVING

· počet skupin, které jsou vytvořeny na základě klauzule GROUP BY je možné redukovat pomocí klauzule HAVING

· podmínka za HAVING vyjadřuje podmínku, kterou musí splňovat všechny řádky výsledného dotazu, z toho též vyplývá, že nemůže být sestavena z jiných funkcí a sloupců než těch, které mohou být použity v dotazu v klauzuli SELECT

SELECT partner,rada, obdobizuct,CASTKAUHR = SUM(CASTKAUHR), NAZEVPARTNERA = max(partnern) FROM t20kfh

group by partner, rada, obdobizuct

having SUM(CASTKAUHR) > 500

Př. 12 – kombinace WHERE – GROUP BY – HAVING – ORDER BY)

· v tomto pořadí jsou vyhodnocovány jednotlivé podmínky

SELECT partner,rada, obdobizuct,CASTKAUHR = SUM(CASTKAUHR), NAZEVPARTNERA = max(partnern)

FROM t20kfh

where rada = '2100'

group by partner, rada, obdobizuct

having SUM(CASTKAUHR) > 500

order by max(partnern)

2.5. Strukturované dotazy – vnořené subdotazy

· subdotazy je možné rozlišit:

· vracející množinu hodnot (uvozené vždy slovem IN nebo některým z relačních operátorů s klíčovým slovem ALL, ANY, SOME

· subdotazy vracející pouze jednu hodnotu uvozené relačním operátorem bez modifikace

· subdotazy fungující jako test existence (uvozené sloupcem EXISTS)

· subdotaz, který vrací jednu hodnotu lze použít i za klauzulí SELECT

· v podmínkách subdotazů lze použít hodnoty z vnějšího dotazu (viz příklad 4)

Př. 1 – subdotaz vracející jednu hodnotu (všechny řádky, která jsou pro výkon s názvem 'Výroba 1')

SELECT T20KFH.rada, T20KFH.skupdokl,T20KFH.PORCISLO, T20KFH.PARTNER, T20KFH.PARTNERN, T20KFH.OBDOBIZUCT, T20KFH.CASTKAUHR,

t20kfh.vykon,NAZEVVYKONU = (select NAZEVVYKONU from c0002 where C0002.vykon = t20kfh.vykon)

FROM T20KFH

Př. 2 – subdotaz vracející množinu hodnot (všichni partneři, na které je nějaký účetní doklad v hlavní knize a přitom se zde nejedná o uhrazovaný doklad)

SELECT Partner, Nazevpartnera

FROM c0204

WHERE Partner IN (SELECT Partner FROM T10URT WHERE SkupdoklH IS NULL)

Př. 3 – to samé jako 2 přes exists

SELECT Partner, Nazevpartnera

FROM c0204

WHERE exists (SELECT 1 FROM T10URT WHERE t10urt.partner = c0204.partner and t10urt.podpartner = c0204.podpartner)

3. Přílohy

3.1. Datové typy SQL serveru

3.1.1. Čísla

· Základním typem pro celá čísla je int. Pokud nám z nějakého důvodu nestačí, můžeme použít bigint. Naopak není-li třeba ukládat tak velké hodnoty, jsou k dispozici smallint a tinyint.

· Specialitou je bit pro ukládání hodnot pravda/nepravda. SQL Server navíc tabulku optimalizuje tak, že více polí tohoto typu ukládá do bajtů (tedy po osmi najednou).

· Decimal(p, s) a numeric (p, s) jsou funkčně totožné a slouží k uložení desetinných čísel se specifikovanou přesností (p, precision) a měřítkem (s, scale). Precision udává nejvyšší počet číslic, které budou uloženy, a to jak vpravo, tak vlevo od desetinné čárky. Výchozí hodnota je 18, minimum 1 a maximum 38. Scale je nejvyšší možný počet číslic vpravo od desetinné čárky. Výchozí hodnotou je 0, minimální taky 0, maximální p. Na volbě parametrů závisí výsledná velikost dat. Použití těchto typů má smysl pro data pocházející z výpočtů.

· Zvláštní variantou typu decimal jsou money a smallmoney, určené pro ukládání hodnot měny. Odpovídají v podstatě decimal s nastavenou přesností na čtyři desetinná místa.

Datový typ	Délka	Popis
bigint	8	Celé číslo od -2^63 (-9 223 372 036 854 775 808) do 2^63-1 (9 223 372 036 854 775 807).
int	4	Celé číslo od -2^31 (-2 147 483 648) do 2^31-1 (2 147 483 647).
smallint	2	Celé číslo od -2^15 (-32 768) do 2^15-1 (32 767).
tinyint	1	Celé číslo od 0 do 255.
bit	1 bit	Celé číslo 0 nebo 1.
decimal(precision, scale)	5-17	Číselný datový typ s pevnou přesností a měřítkem (přesnost 1-38, implicitní 18 a měřítko 0-p, implicitní 0).
numeric	5-17	Stejný jako datový typ 'decimal'.
money	8	Peněžní datový typ od -2^63 (-922 337 203 685 477.5808) do 2^63-1 (922 337 203 685 477.5807) s přesností jedné desetitisíciny jednotky.
smallmoney	4	Peněžní datový typ od -2^31 (-214 748.3648) do 2^31-1 (214 748.3647) s přesností jedné desetitisíciny jednotky.
float(n)	4-8	Číselný datový typ s plovoucí přesností, kde n je počet bitů mantisy (1-24, přesnost 7 číslic, velikost 4 byty a 25-53, přesnost 15 číslic a velikost 8 bytů).
real	4	Číselný datový typ s plovoucí přesností, který je definovaný jako float(24).

3.1.2. Ukládáme datum a čas

· SQL Server 2008 doplnil klasický datetime o čtyři nové typy: date, time, datetime2 a datetimeoffset. Díky tomu je možné definovat sloupce přesně odpovídající potřebám dat. Nové typy rozšiřují rozsahy, některé dovolují stanovit přesnost (při deklaraci) a jiné přinášejí práci s časovými zónami.

Datový typ	Délka	Popis
date	3	0001-01-01 až 9999-12-31 (YYYY-MM-DD)
datetime	8	Datový typ vyjadřující datum a čas od 1.1.1753 do 31.12.9999 s přesností asi 3ms. Hodnoty jsou zaokrouhleny na .000, .003 a .007.
datetime2	6-8	d0001-01-01 00:00:00.0000000 až 9999-12-31 23:59:59.9999999 (YYYY-MM-DD hh:mm:ss[.nnnnnnn])
datetimeoffset	8-10	Vyžaduje zadání časové zóny (posunu), dokáže tedy pracovat s mezinárodním časem. Syntaxe zadávání dat odpovídá jednomu ze dvou formátů ISO 8601: · YYYY-MM-DDThh:mm:ss[.nnnnnnn][{+\|-}hh:mm] · YYYY-MM-DDThh:mm:ss[.nnnnnnn]Z (pro UTC)
smalldatetime	4	Datový typ vyjadřující datum a čas od 1.1.1900 do 6.6.2079 s přesností 1m. Hodnoty do 29.998 jsou zaokrouhleny dolů a hodnoty od 29.999 jsou zaokrouhleny nahoru na nejbližší minutu.
time	3-5	Time se ukládá v 24h formátu a představuje místní čas (nemá představu o časové zóně). Přesnost ovlivní, kolik místa zabere. 00:00:00.0000000 až 23:59:59.9999999 (hh:mm:ss[.nnnnnnn])

3.1.3. Ukládáme řetězce

· Nejběžnější pro ukládání řetězců jsou char a varchar. U obou se stanovuje délka, liší se ve způsobu uložení dat. Char se používá pro řetězce s alespoň přibližně konstantní délkou a zabírá přesně tolik místa, kolik je určeno při deklaraci (n). Velikost typu varchar se naproti tomu mění podle uložených dat a zabírá v paměti aktuální délku dat (m) + 2 B. Není-li délka stanovena, je rovna 1.

· Datový typ text je zastaralý (do budoucna se sním nepočítá), je doporučeno místo něj používatvarchar(max).

· K ukládání znaků ze sady Unicode slouží analogie výše zmíněných typů. Jsou jimi: nchar, nvarchar a ntext(který je opět doporučeno nepoužívat). Platí pro ně stejná pravidla, pouze rozsahy jsou poloviční (zabírají v paměti dvojnásobek místa).

Datový typ	Délka	Popis
char	n	Znakový řetězec pevné délky a max. délce 8000 znaků.
varchar	n	Znakový řetězec proměnlivé délky a max. délce 8000 znaků.
varchar(max)	n	Znakový řetězec proměnlivé délky a max. délce 2^31-1 (2 147 483 647) znaků. Do Sql 2005 obdobný datový typ text.
nchar	2*n	Znakový Unicode řetězec pevné délky a max. délce 4000 znaků.
nvarchar	2*n	Znakový Unicode řetězec proměnlivé délky a max. délce 4000 znaků.
nvarchar(max)	2*n	Znakový Unicode řetězec proměnlivé délky a max. délce 2^30-1 (1 073 741 823) znaků.

3.1.4. Ukládáme binární data

· Pole pro binární data (obrázky, zvuky...) jsou podobná polím pro znakové řetězce. Datové typy pro ně jsou tři - binary, varbinary a image. Deklarují se s parametrem, který určuje velikost (délku) uložitelných informací.

· Podobně jako text u znakových řetězců není doporučeno nadále používat image, protože se s ním do budoucna nepočítá.

Datový typ	Délka	Popis
binary	n+4	Binární data pevné délky a max. délce 8000 bytů.
varbinary	n+4	Binární data proměnlivé délky a max. délce 8000 bytů.
varbinary(max)	n	Binární data proměnlivé délky a max. délce 2^31-1 (2 147 483 647) bytů.

3.1.5. Ostatní

Datový typ	Délka	Popis
cursor		Pro uložení reference na kurzory v proměnné nebo uložené proceduře (ne v CREATE TABLE).
sql_variant		Pro uložení hodnoty jiného typu (ne text, ntext, image, timestamp, sql_variant) o max. délce do 8016 bytů. ODBC nepodporuje plně tento datový typ.
table		Pro uložení výsledku dotazu pro pozdější zpracování.
rowversion	8+4	Dříve timestamp. Datový typ automaticky generující binární čísla, jedinečná v dané databázi, používané většinou k identifikaci řádků. V tabulce smí být pouze jediný sloupec tohoto datového typu.
uniqueidentifier		Datový typ pro uložení GUID (nového pomocí NEWID funkce nebo existujícího z řetězce ve tvaru xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, např. 6F9619FF-8B86-D011-B42D-00C04FC964FF).
geography		Od SQL Serveru 2008. Používá se, pokud pracujeme se zeměpisnou šířkou (latitude) a délkou (longitude), protože počítá se zakřivením zemského povrchu.
geometry		Používá se u rozměrů, kde zakřivení Země není třeba brát v úvahu – vzdálenosti, velikosti
hierarchyid		Ke stanovení hierarchických vztahů mezi daty. Umožňuje v databázi sestavovat a spravovat stromovou strukturu.
xml		slouží k ukládání objektů typu XML