Język SQL w Delphi

Język SQL w Delphi
1. Kwerendy
Kwerenda jest zleceniem dla programu bazy danych, mającym na celu odszukanie rekordów
spełniających określone kryteria. Przykładem kwerendy może być zlecenie odszukania rekordu
zawierającego dane osoby o podanym nazwisku lub odszukaniu wszystkich osób pochodzących
z jednego miasta. Wyniki obydwu zapytań są różne. W pierwszym przypadku uzyskujemy jeden rekord,
a w drugim całą ich grupę.
Język SQL (ang. strucured query language) został zaprojektowany przez firmę IBM w celu sprawnej
realizacji kwerend w relacyjnych bazach danych.
Do wykonania kwerendy w Delphi służy komponent TQuery. Jej podstawowe właściwości:
❏
❏
❏
❏
SQL — Zawiera tekst komendy języka SQL, która zostanie wykonana.
DatabaseName — Określa nazwę bazy danych, z którą skojarzony został zbiór danych.
Params — Zawiera parametry przekazywane do komendy SQL.
Active — Określa czy zbiór danych jest otwarty, czy nie.
1.1. Przygotowanie do ćwiczenia
Aby wykonać ćwiczenie związane z zastosowaniem komponentu TQuery, należy utworzyć nową
aplikacjię i dodać do formularza komponent TQuery oraz TDataSource (znajdują się one w zakładce
Data Access palety komponentów) oraz komponent TDBGrid (zakładka Data Controls).
W komponencie TQuery należy ustawić właściwość DatabaseName na odpowiedni alias bądź ścieżkę
dostępu do katalogu, w którym znajduje się baza danych. Daje to dostęp do wszystkich tabel
umieszczonych w katalogu bazy danych. W odróżnieniu od komponentu tabeli, który pozwala na
dostęp tylko do jednej tabeli, komponent zapytania TQuery umożliwia dostęp do całej bazy, czyli
do wszystkich tabel. Oczywiście zastosowanie komponentu TTable oraz TQuery jest inne. Następnie
należy połączyć komponent TQuery ze źródłem danych TDataSource poprzez właściwość DataSet
oraz komponent TDBGrid z komponentem TDataSource poprzez właściwość DataSource. W trakcie
ćwiczenia należy modyfikować działanie komponentu TQuery poprzez ustalanie wartość właściwości
SQL, a następnie ustalać wartość właściwości Active na True.
1.2. Komenda SELECT
Komenda SELECT jest podstawową komendą języka SQL, służącą do zadawania zapytań. Jej
podstawową składnią jest:
SELECT co FROM skąd
Przykład:
SELECT firma, miasto, rczn zakup FROM klienci
Dodanie klauzuli WHERE <Wyrażenie logiczne> pozwala na umieszczenie w zapytaniu tylko tych
rekordów, dla których warunek przyjmuje wartość TRUE. Klauzula ORDER BY <wyr> powoduje zmianę
porządku wyświetlanych danych.
2
1. Kwerendy
SELECT firma, miasto, rczn_zakup FROM klienci
WHERE rczn_zakup > 1000 ORDER BY miasto, firma
Do zapytania domyślnie kierowane są wszystkie rekordy. Dodanie klauzuli DISTINCT zaraz za
słowem SELECT powoduje wykluczenie powtarzających się wierszy.
SELECT
WHERE
SELECT
WHERE
biura.nr_biura FROM biura, klienci
biura.nr_biura = klienci.nr_biura
DISTINCT biura.nr_biura FROM biura, klienci
biura.nr_biura = klienci.nr_biura
Domyślnie wszystkie kolumny wyników zapytań przyjmują nazwę pola, z którego pochodzą dane,
ale można nadać im inną nazwę. Nazwa ta jednak musi spełniać wszystkie wymogi nazw pól tabeli.
SELECT nr_klienta AS Numer, firma AS Nazwa FROM klienci
W przypadku, gdy trzeba wybrać wszystkie pola z tabeli, można ich listę zastąpić symbolem *.
SELECT klienci.*, biura.miasto AS Biuro
FROM klienci, biura
WHERE klienci.nr_biura = biura.nr_biura ORDER BY firma
Elementy wybrane w zapytaniach mogą być także funkcjami. Oto kilka funkcji, których można
używać z elementem wyboru będącym polem lub wyrażeniem zawierającym nazwy pól:
AVG(<element wyb>)
COUNT(<element wyb>)
MIN(<element wyb>)
MAX(<element wyb>)
SUM(<element wyb>)
SELECT
SELECT
SELECT
SELECT
SELECT
Uśrednianie kolumny danych numerycznych,
Zlicza elementy wyboru w kolumnie,
Wyznacza najmniejszą wartość elementu kolumny,
Wyznacza największą wartość elementu kolumny,
Sumuje kolumny danych numerycznych.
AVG(rczn_zakup) AS ŚREDNIA FROM klienci
MIN(rczn_zakup) AS MINIMUM FROM klienci
MAX(rczn_zakup) AS MAKSIMUM FROM klienci
COUNT(rczn_zakup) AS LICZBA FROM klienci
SUM(rczn_zakup) AS SUMA FROM klienci
Jeżeli tworzymy zapytanie z kilku tabel to istnieje możliwość, że nazwy pól w tych tabelach pokryją
się. W celu rozróżnienia tych pól można umieścić zaraz za nazwą pola lokalny alias, który jest aktualny
tylko w czasie wykonywania zapytania. Aliasy lokalne stosowane są również w sytuacji, gdy potrzebne
jest uproszczenie nazwy tabeli, bądź jej skrócenie w celu zwiększenia czytelności.
SELECT a.nr_klienta, a.firma, a.miasto, b.miasto AS Biuro
FROM klienci a, biura b
WHERE a.nr_biura = b.nr_biura ORDER BY a.firma
1.2. Komenda SELECT
3
Kryterium wyboru umieszczone za klauzulą WHERE można budować z wielu pojedynczych wyrażeń
logicznych używając słów OR lub AND. Można tam umieścić zarówno warunki łączenia tabel jak i
odfiltrowywania rekordów. Do budowania pojedynczych wyrażeń logicznych należy stosować
następujących operatorów:
=
równe,
<> różne,
>
większe,
<
mniejsze,
>= większe równe,
<= mniejsze równe,
a także operatorów ALL(<zapytanie wtórne>), ANY | SOME(<zapytanie wtórne>), klauzuli [NOT]
BETWEEN <minimum> AND <maksimum>, [NOT] EXIST(<zapytanie wtórne>) oraz [NOT] IN
<zbiór wartości> i [NOT] LIKE <wzorzec>. Zapytanie wtórne to polecenie SELECT wewnątrz
głównego polecenia SELECT. W klauzuli WHERE można użyć kilku zapytań wtórnych na tym samym
poziomie (nie zagnieżdżonych). W ostatniej klauzuli dopuszcza się występowanie symboli
wieloznacznych takich jak:
dowolny znak,
% sekwencja dowolnych znaków.
SELECT nr_klienta, firma, miasto, nr_biura FROM klienci
WHERE nr_biura != "4" ORDER BY firma
SELECT firma, miasto FROM klienci WHERE rczn_zakup > ALL
(SELECT rczn_zakup FROM klienci WHERE miasto = "New York")
SELECT firma, miasto FROM klienci WHERE rczn_zakup > ANY
(SELECT rczn_zakup FROM klienci WHERE miasto = "New York")
SELECT * FROM klienci WHERE rczn_zakup BETWEEN 1000 and 2000
SELECT * FROM biura WHERE NOT EXIST (SELECT * FROM klienci, biura
WHERE klienci.nr_biura = biura.nr_biura)
SELECT adres, miasto FROM biura WHERE nr_biura IN ("1","2","3")
SELECT * FROM klienci WHERE miasto LIKE ’N%’
SELECT * FROM klienci WHERE stan LIKE ’_A’
Dodanie klauzuli GROUP BY <kolumna grupowania> powoduje grupowanie wierszy w
wygenerowanym wyniku zapytania na podstawie wartości kolumny (kolumn). Pozwala to naprzykład
na podsumowanie wartości sprzedaży w filiach firmy, położonych w różnych firmach.
Użycie klauzuli HAVING <warunek filtrujacy> pozwala na określenie warunku filtrującego, który
muszą spełniać grupy, aby zostały włączone do wyników kwerendy. Klauzula HAVING powinna zostać
użyta z klauzulą GROUP BY. Może ona zostać użyta z dowolną liczbą warunków filtrujących,
połączonych operatorami AND i OR. Można również używać operatora NOT, zmieniającego wartość
logiczną wyrażeń na przeciwną. Parametr WarunekFiltrujący nie może zawierać kwerendy wtórnej.
W klauzuli HAVING wolno używać lokalnych aliasów i funkcji pól. W wypadku, gdy klauzula HAVING
nie zawiera funkcji pól, należy używać klauzuli WHERE, z uwagi na większą szybkość wykonania.
4
1. Kwerendy
SELECT firma, miasto, rczn_zakup, nr_biura FROM klienci ORDER BY
firma GROUP BY miasto HAVING nr_biura > "2"
Klauzula UNION [ALL] <polecenie select> powoduje połączenie wyników zapytania z wynikiem
innego polecenia zapytania. Standardowo klauzula ta sprawdza połączone wyniki i eliminuje
powtarzające się wiersze. Dodanie klauzuli ALL spowoduje pominięcie tej eliminacji.
SELECT * FROM klienci WHERE nr_biura = "2" UNION
SELECT * FROM klienci WHERE nr_biura = "4"
Klauzula ORDER BY <elementy porządkujące> uporządkowuje wyniki zapytania. Standardowo
wyniki zapytania wyświetlane są w kolejności rosnącej. Dodanie klauzuli DESC umożliwia sortowanie
w kolejności malejącej.
SELECT klienci.firma, klienci.miasto, biura.miasto FROM klienci, biura
WHERE klienci.nr_biura = biura.nr_biura
ORDER BY biura.miasto, klienci.firma DESC
Istnieje możliwość utworzenia sparametryzowanych zapytań (np. w kryterium wyboru). W tym
celu należy, przed uruchomieniem kwerendy, określić wartość parametru Params. Należy wykonać to
programowo, umieszczając przed wykonaniem zapytania kod programu
Procedure TForm1.Button1Click(Sender: TObject);
begin
With Query1 Do
begin
Close;
Unprepare;
Try
ParamByName(’MIASTO’).AsString := Edit1.Text;
Prepare;
Open;
Except
On EDBEngineError Do
ShowMessage(’Bledna skladnia zapytania SQL.’);
On EDatabaseError Do
ShowMessage(’Bledny parametr lub zapytanie.’)
end
end
end;
W powyższym przypadku komenda zapytania może mieć następującą postać:
SELECT * FROM klienci WHERE UPPER(miasto) = UPPER(:MIASTO)
1.3. „Żywe” kwerendy
Domyślnie wynik wykonania zapytania jest zbiorem danych tylko do odczytu. Zbiór danych, który
otrzymuje się z zapytania, można wyświetlać w obiektach kontrolnych, lecz użytkownicy nie mogą
1.3. „Żywe” kwerendy
5
tych danych edytować. Aby umożliwić edycję danych zapytania, należy sprawić, aby zwrócony zbiór
danych był „żywy” — możliwy do edycji.
Można żądać, aby zapytanie zwróciło „żywy” zbiór danych. Umożliwia to właściwość RequestLive
komponentu TQuery. Aby zbiór danych zwrócony przez zapytanie mógł być edytowany, przez
użytkownika, należy ją ustawić na wartość True. Niestety nie zawsze ma się gwarancję na to,
że zwrócony zbiór danych będzie edytowalny. Jednak kiedy tylko to możliwe, system BDE usiłuje
spełniać oczekiwania projektanta. Istnieją pewne ograniczenia, które decydują o tym, czy wynik
zapytania będzie można edytować. Głównie zależy to od tego, czy zapytanie jest przetwarzane przez
system BDE, czy też przez narzędzia serwera baz danych. Niejednorodne złączenia oraz zapytania
realizowane w bazach lokalnych, takich jak np. Paradox, są wykonywane przez BDE. Zapytania
dotyczące oddalonej bazy danych serwera SQL są analizowane przez sam serwer.
To, czy zbiór danych będący wynikiem zapytania może być edytowany, określa właściwość
CanModify, która w przypadku gdy możliwa jest edycja zapytania, zwraca wartość True.
Jeśli projektant żąda, aby wynik zapytania mógł być edytowany, ale składnia wyrażenia SELECT
nie pozwala na to, BDE zwróci:
❏ wynik tylko do odczytu w przypadku zapytań wykonywanych na bazach lokalnych typu Paradox
lub dBASE.
❏ kod błędu w przypadku zapytań wykonywanych na oddalonym serwerze SQL.
1.3.1. Wymagania i ograniczenia dla „żywych” wyników zapytania
W przypadku zapytań, które używają lokalnego SQL, BDE oferuje rozszerzone wsparcie
edytowalnych, żywych wyników zapytań zarówno opartych na jednej, jak i wielu tabelach. Local SQL
jest używany, kiedy zapytanie jest wykonywane dla jednej lub więcej tabel lokalnych typu Paradox
lub dBase, albo dla jednej lub wiecej tabel zdalnego serwera, kiedy nazwy tych tabel w zapytaniu są
poprzedzone nazwą aliasu bazy danych.
Żywy wynik zapytania wykonanego na pojedynczej tabeli lub widoku jest zwracany, gdy zapytanie
to nie zawiera następujących elementów:
❏
❏
❏
❏
❏
❏
klauzuli DISTINCT w wyrażeniu SELECT,
złąceń typu INNER, OUTER lub UNION,
funkcji agregujących, z klauzulą lub bez klauzuli GROUP BY lub HAVING,
podzapytań,
klauzul ORDER BY nie bazujących na indeksie
gdy zapytanie bazuje na tabelach lub widokach, które nie dają się aktualizować
W przypadku zapytań wykonywanych na oddalonym serwerze SQL, które zawierają wszystkie
zapytania wykonane wyłącznie na zdalnym serwerze bazy danych, żywy wynik zapytania jest
okraniczony do standardu zdefiniowanego przez SQL-92 oraz przez dodatkowe ograniczenia narzucone
przez rodzaj serwera.
W tym przypadku, żywy wynik zapytania wykonanego na pojedynczej tabeli lub widoku jest
zwracany, gdy zapytanie to nie zawiera następujących elemetnów:
❏
❏
❏
❏
klauzuli DISTINCT w wyrażeniu SELECT,
funcji agregujących z klauzulą lub bez klauzuli GROUP BY lub HAVING,
odwołań do więcej niż jednej tabeli bazowej lub widoków, które mogą być uaktualniane,
podzapytań, które odwołują się do tabeli w klauzuli FROM lub innych tabel.
1.3.2. Aktualizacja wyników zapytania tylko do odczytu
Aplikacja może aktualizować dane zwrócone w zbiorze tylko do odczytu, jeżeli zastosowano tryb
buforowania zmian. W celu zaktualizowania zbioru danych tylko do odczytu, należy:
6
1. Kwerendy
❏ dodać komponent TUpdateSQL do modułu danych lub formularza, który daje mołiwość aktualizacji
takiego wyniku zapytania,
❏ wprowadzic aktualizujące wyrażenie SQL dla właściwości ModifySQL, InsertSQL lub DeleteSQL,
❏ dla komponentu TQuery ustawić właściwość CachedUpdate na wartość True.