1. Kwerendy

Język SQL w Delphi

Kwerenda jest zleceniem dla programu bazy danych, mającym na celu odszukanie rekordówspełniających określone kryteria. Przykładem kwerendy może być zlecenie odszukania rekorduzawierającego dane osoby o podanym nazwisku lub odszukaniu wszystkich osób pochodzącychz 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ładceData 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 tabelumieszczonych w katalogu bazy danych. W odróżnieniu od komponentu tabeli, który pozwala nadostęp tylko do jednej tabeli, komponent zapytania TQuery umożliwia dostęp do całej bazy, czylido wszystkich tabel. Oczywiście zastosowanie komponentu TTable oraz TQuery jest inne. Następnienależy połączyć komponent TQuery ze źródłem danych TDataSource poprzez właściwość DataSetoraz 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ściSQL, 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ń. Jejpodstawową 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 tychrekordó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 klienciWHERE rczn_zakup > 1000 ORDER BY miasto, firma

Do zapytania domyślnie kierowane są wszystkie rekordy. Dodanie klauzuli DISTINCT zaraz zasłowem SELECT powoduje wykluczenie powtarzających się wierszy.

SELECT biura.nr_biura FROM biura, klienciWHERE biura.nr_biura = klienci.nr_biuraSELECT DISTINCT biura.nr_biura FROM biura, klienciWHERE 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 BiuroFROM klienci, biuraWHERE klienci.nr_biura = biura.nr_biura ORDER BY firma

Elementy wybrane w zapytaniach mogą być także funkcjami. Oto kilka funkcji, których możnaużywać z elementem wyboru będącym polem lub wyrażeniem zawierającym nazwy pól:

AVG(<element wyb>) Uśrednianie kolumny danych numerycznych,COUNT(<element wyb>) Zlicza elementy wyboru w kolumnie,MIN(<element wyb>) Wyznacza najmniejszą wartość elementu kolumny,MAX(<element wyb>) Wyznacza największą wartość elementu kolumny,SUM(<element wyb>) Sumuje kolumny danych numerycznych.

SELECT AVG(rczn_zakup) AS ŚREDNIA FROM klienciSELECT MIN(rczn_zakup) AS MINIMUM FROM klienciSELECT MAX(rczn_zakup) AS MAKSIMUM FROM klienciSELECT COUNT(rczn_zakup) AS LICZBA FROM klienciSELECT 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 aktualnytylko w czasie wykonywania zapytania. Aliasy lokalne stosowane są również w sytuacji, gdy potrzebnejest 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 BiuroFROM klienci a, biura bWHERE 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 iodfiltrowywania 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ątrzgłównego polecenia SELECT. W klauzuli WHERE można użyć kilku zapytań wtórnych na tym samympoziomie (nie zagnieżdżonych). W ostatniej klauzuli dopuszcza się występowanie symboliwieloznacznych takich jak:

dowolny znak,% sekwencja dowolnych znaków.

SELECT nr_klienta, firma, miasto, nr_biura FROM klienciWHERE 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, biuraWHERE 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 wwygenerowanym wyniku zapytania na podstawie wartości kolumny (kolumn). Pozwala to naprzykładna 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 HAVINGnie 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 BYfirma GROUP BY miasto HAVING nr_biura > "2"

Klauzula UNION [ALL] <polecenie select> powoduje połączenie wyników zapytania z wynikieminnego polecenia zapytania. Standardowo klauzula ta sprawdza połączone wyniki i eliminujepowtarzające się wiersze. Dodanie klauzuli ALL spowoduje pominięcie tej eliminacji.

SELECT * FROM klienci WHERE nr_biura = "2" UNIONSELECT * FROM klienci WHERE nr_biura = "4"

Klauzula ORDER BY <elementy porządkujące> uporządkowuje wyniki zapytania. Standardowowyniki zapytania wyświetlane są w kolejności rosnącej. Dodanie klauzuli DESC umożliwia sortowaniew kolejności malejącej.

SELECT klienci.firma, klienci.miasto, biura.miasto FROM klienci, biuraWHERE klienci.nr_biura = biura.nr_biuraORDER BY biura.miasto, klienci.firma DESC

Istnieje możliwość utworzenia sparametryzowanych zapytań (np. w kryterium wyboru). W tymcelu należy, przed uruchomieniem kwerendy, określić wartość parametru Params. Należy wykonać toprogramowo, umieszczając przed wykonaniem zapytania kod programu

Procedure TForm1.Button1Click(Sender: TObject);begin

With Query1 DobeginClose;Unprepare;TryParamByName(’MIASTO’).AsString := Edit1.Text;Prepare;Open;

ExceptOn EDBEngineError DoShowMessage(’Bledna skladnia zapytania SQL.’);

On EDatabaseError DoShowMessage(’Bledny parametr lub zapytanie.’)

endend

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óryotrzymuje 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órdanych 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, przezuż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łujespełniać oczekiwania projektanta. Istnieją pewne ograniczenia, które decydują o tym, czy wynikzapytania będzie można edytować. Głównie zależy to od tego, czy zapytanie jest przetwarzane przezsystem BDE, czy też przez narzędzia serwera baz danych. Niejednorodne złączenia oraz zapytaniarealizowane w bazach lokalnych, takich jak np. Paradox, są wykonywane przez BDE. Zapytaniadotyczą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 Paradoxlub 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 wsparcieedytowalnych, żywych wyników zapytań zarówno opartych na jednej, jak i wielu tabelach. Local SQLjest używany, kiedy zapytanie jest wykonywane dla jednej lub więcej tabel lokalnych typu Paradoxlub 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ą wszystkiezapytania wykonane wyłącznie na zdalnym serwerze bazy danych, żywy wynik zapytania jestokraniczony do standardu zdefiniowanego przez SQL-92 oraz przez dodatkowe ograniczenia narzuconeprzez 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 trybbuforowania 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ść aktualizacjitakiego 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.