Kurz Databáze Zpracování dat Doc. Ing. Radim Farana, CSc.
Obsah Druhy dotazů, tvorba dotazu, prostředí QBE
(Query by Example). Realizace základních relačních operací – selekce,
projekce a spojení.
Agregace dat, virtuální pole, parametry. Výkonné dotazy. SQL dotazy. Poddotazy. Dotazovací jazyk SQL (Structured Query
Language).
strana 1
Dotazy Jaké jsou druhy dotazů a k čemu slouží? výběrové dotazy - výběr požadovaných dat (selekce, projekce a spojení), virtuální položky, řazení, agregované výpočty, výkonné dotazy - vytvoření tabulky, hromadná změna dat, přidání a rušení záznamů, SQL dotazy - vše co umí jazyk SQL, speciálně UNION.
Spojení dat z více tabulek Projekce – výběr zobrazených polí
Selekce - výběr zobrazených záznamů
strana 2
1
Výběrové dotazy Spojení dat z více tabulek
Projekce - výběr zobrazených polí
Selekce - výběr zobrazených záznamů
Řazení záznamů
strana 3
Parametry dotazů
Datový typ
Parametr
Zadání hodnoty včetně kontroly
strana 4
Virtuální pole Formát pole
PARAMETERS [Fill in the machine number] Long; SELECT tblOperations.opeID, tblOperations.opeWorker, tblOperations.opeMachine, IIf(IsNull([opeStop]),Now(),[opeStop])-[opeStart] AS opeLength FROM tblOperations WHERE (((tblOperations.opeMachine)=[Fill in the machine number])) Pojmenování pole ORDER BY tblOperations.opeID;
strana 5
2
Agregační dotazy T OP n (T OP n PERCENT ) DIST INCT DIST INCT ROW
Pojmenování položky
Definice skupin
Operace
WITH OWNERACCESS OPTION
IN
Podmínka
strana 6
Agregační funkce Volba Sum Avg Min Max Count
StDev
Var First Last
Výsledek Součet hodnot v poli Průměr hodnot v poli Nejnižší hodnota v poli Nejvyšší hodnota v poli Počet hodnot v poli, hodnoty Null (prázdné) se nepočítají Směrodatná odchylka hodnot v poli Rozptyl hodnot v poli První ze skupiny Poslední ze skupiny
Datový typ pole Číslo, Datum/Čas, Měna a Automatické číslo Číslo, Datum/Čas, Měna a Automatické číslo Text, Číslo, Datum/Čas, Měna a Automatické číslo Text, Číslo, Datum/Čas, Měna a Automatické číslo Text, Memo, Číslo, Datum/Čas, Měna a Automatické číslo, Ano/Ne a objekt OLE Číslo, Datum/Čas, Měna a Automatické číslo Číslo, Datum/Čas, Měna a Automatické číslo
Volba Výsledek Group By Definice skupin, s nimiž se provádějí výpočty. Expression Vytvoření vypočítaného pole, které zahrnuje agregační funkci ve svém výrazu. Vypočítané pole se používá obvykle tehdy, když má být ve výrazu několik funkcí. Zadání kritérií pro pole, která se nepoužívají k definici Where seskupení.
strana 7
Křížový dotaz
TRANSFORM Sum(qryWorkersWithExpensiveSteps.stpPrice) AS SumOfstpPrice SELECT qryWorkersWithExpensiveSteps.wrkPN, qryWorkersWithExpensiveSteps.wrkSurname, qryWorkersWithExpensiveSteps.wrkName, Sum(qryWorkersWithExpensiveSteps.stpPrice) AS TotalOfstpPrice FROM qryWorkersWithExpensiveSteps GROUP BY qryWorkersWithExpensiveSteps.wrkPN, qryWorkersWithExpensiveSteps.wrkSurname, qryWorkersWithExpensiveSteps.wrkName PIVOT qryWorkersWithExpensiveSteps.opeMachine;
strana 8
3
Vytvářecí dotaz
SELECT tblOperations.*, qryOperationsFinished.stpPriceTotal INTO tblOperationsFinished FROM qryOperationsFinished INNER JOIN tblOperations ON qryOperationsFinished.opeID = tblOperations.opeID;
strana 9
Aktualizační dotaz Způsob zobrazení
Druh dotazu
Provedení dotazu Přidání zdroje Vlastnosti dotazu UPDATE tblSteps SET tblSteps.stpName = [New step name] WHERE (((tblSteps.stpName)=[Previous step name]));
strana 10
Přidávací dotaz
INSERT INTO tblOperationsFinished ( stpPriceTotal ) SELECT tblOperations.*, qryOperationsFinished.stpPriceTotal FROM tblOperations INNER JOIN qryOperationsFinished ON tblOperations.opeID = qryOperationsFinished.opeID;
strana 11
4
Odstraňovací dotaz
DELETE tblOperations.opeStop FROM tblOperations WHERE (((tblOperations.opeStop) Is Not Null));
strana 12
Sjednocovací dotaz
TABLE tblOperationsFinished UNION SELECT tblOperations.*, qryOperationsFinished.stpPriceTotal FROM tblOperations INNER JOIN qryOperationsFinished ON tblOperations.opeID = qryOperationsFinished.opeID ORDER BY opeWorker;
Způsob řazení
strana 13
Definiční dotazy Vytvoření tabulky: CREATE TABLE NewTableName (Prijmeni TEXT (50) NOT NULL, Jmeno TEXT (50), Datum DATETIME, Pocet INTEGER CONSTRAINT JednoduchyKlic PRIMARY KEY, Noname TEXT); Vytvoření indexu (složeného): CREATE INDEX NewIndex ON NewTableName (Prijmeni ASC, Jmeno ASC, Plat DESC) WITH DISALLOW NULL;
Vytvoření primárního klíče
Určení způsobu řazení Změna definice tabulky (přidání položky): ALTER TABLE NewTableName ADD COLUMN Plat CURRENCY NOT NULL; Změna definice tabulky (přidání indexu): ALTER TABLE NewTableName ADD CONSTRAINT SlozenyKey UNIQUE (Prijmeni, Jmeno); Změna definice tabulky (zrušení položky): ALTER TABLE NewTableName DROP COLUMN Plat; Změna definice tabulky (zrušení indexu): ALTER TABLE NewTableName DROP CONSTRAINT SlozenyKey; Zrušení indexu: DROP INDEX NewIndex ON NewTableName; Zrušení tabulky: DROP TABLE NewTableName;
strana 14
5
Průvodce tvorbou dotazů Křížový dotaz Hledání duplicit Hledání chybějících podřízených záznamů
strana 15
Chybějící podřízené záznamy
SELECT tblOperations.* FROM tblOperations LEFT JOIN tblSteps ON tblOperations.opeID = tblSteps.stpOperation WHERE (((tblSteps.stpID) Is Null)) ORDER BY tblOperations.opeWorker;
strana 16
Duplicitní záznamy
strana 17
6
...
SELECT tblSteps.stpOperation, tblSteps.stpID, tblSteps.stpName, tblSteps.stpPrice FROM tblSteps WHERE (((tblSteps.stpOperation) In (SELECT [stpOperation] FROM [tblSteps] As Tmp GROUP BY [stpOperation] HAVING Count(*)>1 ))) ORDER BY tblSteps.stpOperation;
strana 18
Poddotazy
Průměrná cena všech kroků (skalární hodnota) SELECT tblSteps.* FROM tblSteps WHERE (((tblSteps.stpPrice)>(SELECT Avg(stpPrice) as stpPriceAvg FROM tblSteps;)));
strana 19
Propojení poddotazu
SELECT tblSteps.* FROM tblSteps WHERE (((tblSteps.stpPrice)> (SELECT Avg(stpPrice) as stpPriceAvg FROM tblSteps as Pom WHERE tblSteps.stpOperation=Pom.stpOperation;)));
Přejmenování zdroje v poddotazu
Propojení s hlavním dotazem
strana 20
7
Testování více hodnot Testování existence v seznamu (EXISTS, NOT EXISTS). Porovnání hodnoty se seznamem vrácených hodnot: IN - nachází se v seznamu, ANY - podmínka platí alespoň pro jednu hodnotu v seznamu, ALL - podmínka platí pro všechny hodnoty v seznamu. WHERE Adresy.DatumNarozeni IN (SELECT Adresy.DatumNarozeni FROM Adresy WHERE Adresy.Prijmeni=“Novak“) vrátí všechny osoby, které se narodily stejný den jako některý z Nováků WHERE Adresy.DatumNarozeni
strana 21
Dotaz jako součást výrazu
SELECT tblSteps.*, [stpPrice]/(SELECT Sum(stpPrice) FROM tblSteps;) AS stpPerc FROM tblSteps;
Dotaz vrací skalární hodnotu (jeden záznam s jednou položkou)
strana 22
Dotazovací jazyk SQL Structured Query Language neprocedurální jazyk 1984 - SEQUEL ANSI standard 1986 ISO standard 1987 rozšíření ISO 1989 ANSI standard 1992 (SQL92) jazyk pro definici dat interaktivní jazyk pro manipulaci s daty
DDL (Data Definition Language),
jazyk pro manipulaci s daty v hostitelské verzi
DML (Data Manipulation Language),
možnost definice pohledů (virtuální tabulka)
DCL (Data Control Language)
možnost definice přístupových práv možnost definice integritních omezení jazyk modulů řízení transakcí
strana 23
8
Datové typy SQL Typ dat:
Popis:
Celá čísla v délce až šesti číslic (včetně znaménka). Rozpětí povolených hodnot je od -32,768 do 32,767. Velikost: 2 Byte. Celá čísla v délce až 11 číslic (včetně znaménka). Rozpětí povolených hodnot je interval od 2,147,483,648 až do 2,147,483,647. Velikost: 4 Byte. Čísla s pevnou řádovou čárkou se znaménkem celkem s x číslicemi a y desetinnými místy, x je implicitně DECIMAL(x,y) 30 a y se implicitně rovná 6. Velikost: proměnná. Obdobně jako DECIMAL(x,y). Velikost: proměnná. NUMERIC(x,y) Čísla v pohyblivé řádové čárce. Rozpětí čísel sahá od 2.22507385850720160*10-308 až k FLOAT(x) 1.79769313486231560*10+308. Velikost: 8 Byte. Znakové řetězce v délce n znaků. Povolené rozpětí pro n je interval od 1 do 32767. Bez určení n je CHAR(n) (CHARAKTER (n)) implicitně rovno 1. Velikost : n znaků. Umožňuje používat řetězců proměnné délky. Dovoluje ukládat řetězce různé délky v jednotlivých VARCHAR(n) řádcích. Stanovuje se horní maximálně možná délka řetězce. Jinak podobně jako u CHAR(n). Velikost: n (VARYING znaků. CHARAKTER) LONG VARCHAR Řetězec libovolného počtu znaků. Maximální velikost je omezena maximálně dovolenou velikostí databázového souboru. Datum ve formátu dle nastavení: yyyy/mm/dd. Velikost: 4 Byte. DATE Stejně jako datum plus hodina, minuta, vteřina a zlomek vteřiny (na 6 míst): yyyy/mm/dd hh-mmTIMESTAMP ss.ffffff. Velikost: 8 Byte. Časový okamžik dne. Ve formátu hodina/minuta/vteřina. Velikost: 4 Byte. TIME Obdobně jako FLOAT. Velikost: 8 Byte. DOUBLE Reálná čísla s vědeckotechnickým formátem. Rozpětí čísel sahá od 1.175494351*10-38 až do REAL 3.402823466*1038. Velikost: 4 Byte. Binární data celkové délky ‘n’ Byte. Implicitně je size = 1 Byte. Max. délka je 32767 Byte. Velikost: n BINARY(n) Byte. Binární datový typ libovolné délky. Omezení jako u LONG VARCHAR. LONG BINARY SMALLINT INTEGER
strana 24
Příkazy SQL Příkazy pro definici datových struktur (DDL)
Příkazy pro kontrolu přístupu dat (DCL)
Klauzule CREATE DATABASE Klauzule CREATE TABLE Klauzule DROP TABLE Klauzule ALTER TABLE Klauzule CREATE VIEW Klauzule DROP VIEW Klauzule ALTER DOMAIN Klauzule DROP DOMAIN
Klauzule GRANT Klauzule REVOKE Klauzule CONNECT
Příkazy pro získávání dat
Příkazy pro řízení transakcí Klauzule COMMIT Klauzule ROLLBACK
Příkazy pro specifikaci nastavení Klauzule SET NAMES Klauzule SET TRANSACTION
Klauzule SELECT a FROM Příkaz DECLARE CURSOR Příkaz OPEN Příkaz FETCH Příkaz CLOSE
Příkazy pro manipulaci s daty (DML) Klauzule INSERT Klauzule DELETE Klauzule UPDATE
strana 25
http://www.fs.vsb.cz/books/SQLReference/Index.htm
strana 26
9
