Ograniczanie i sortowanie danych

Podstawowe możliwości instrukcji SELECT to Zaznaczanie, Projekcja i Łączenie. Wyświetlanie określonych kolumn z tabeli jest nazywane operacją projektu. Skoncentrujemy się teraz na wyświetlaniu określonych wierszy wyników. Nazywa się to operacją wyboru. Określone wiersze można wybrać, dodając klauzulę WHERE do zapytania SELECT. W rzeczywistości klauzula WHERE pojawia się tuż po klauzuli FROM w hierarchii zapytań SELECT. Sekwencja musi być zachowana we wszystkich scenariuszach. W przypadku naruszenia Oracle zgłasza wyjątek.

Składnia:

SELECT *|{[DISTINCT] column| expression [alias],..}
FROM table
[WHERE condition(s)]

W składni

• WHERE klauzula jest słowem kluczowym

• [warunek] zawiera nazwy kolumn, wyrażenia, stałe, literały i operator porównania.

Załóżmy, że Twój kierownik pracuje nad kwartalnym budżetem Twojej organizacji. W ramach tego działania konieczne jest sporządzenie listy najważniejszych danych każdego pracownika, ale tylko w przypadku pracowników, którym zarabia co najmniej 25 000 USD rocznie. Poniższe zapytanie SQL wykonuje to zadanie. Zwróć uwagę na użycie klauzuli WHERE zaznaczonej pogrubioną czcionką.

SELECT Employee_ID, Last_Name, First_Name, Salary
FROM employees
WHERE Salary >= 25000;  

EMPLOYEE_ID  LAST_NAME        FIRST_NAME       SALARY
----------   ---------------  ---------------  -----------
88303        Jones            Quincey          $30,550.00
88404        Barlow           William          $27,500.00 88505 Smith Susan $32,500.00

3 rows selected

Punkty, na które należy zwrócić uwagę -

• Klauzula SELECT może zawierać tylko jedną klauzulę WHERE. Jednak do klauzuli WHERE można dołączyć wiele warunków filtru przy użyciu operatora AND lub OR.

• Kolumny, literały lub wyrażenia w klauzuli predykatu muszą mieć podobne lub wzajemnie konwertowalne typy danych.

• W klauzuli WHERE nie można używać aliasu kolumny.

• Literały znakowe muszą być ujęte w pojedyncze cudzysłowy i uwzględniają wielkość liter.

• Literały daty muszą być ujęte w pojedyncze cudzysłowy i uwzględniają format. Format domyślny toDD-MON-RR.

Operatory porównania

Operatory porównania są używane w predykatach do porównywania jednego terminu lub operandu z innym terminem. SQL oferuje kompleksowy zestaw operatorów równości, nierówności i różnych operatorów. Mogą być używane w zależności od logiki warunku danych i filtru w zapytaniu SELECT. Gdy używasz operatorów porównania w klauzuli WHERE, argumenty (obiekty lub wartości, które porównujesz) po obu stronach operatora muszą być albo nazwą kolumny, albo określoną wartością. Jeśli używana jest określona wartość, musi to być wartość liczbowa lub ciąg literału. Jeśli wartość jest ciągiem znaków lub datą, należy wprowadzić wartość w apostrofach („”).

Oracle ma dziewięć operatorów porównania, których można używać w warunkach równości lub nierówności.

Operator  Meaning
=         equal to 
<         less than
>         greater than
>=        greater than or equal to
<=        less than or equal to
!=        not equal to
<>        not equal to

Inne operatory Oracle to BETWEEN..AND, IN, LIKE i IS NULL.

Operator BETWEEN

Operator BETWEEN może służyć do porównywania wartości kolumny w określonym zakresie. Określony zakres musi mieć dolną i górną granicę, gdy oba są uwzględniające podczas porównania. Jego użycie jest podobne do złożonego operatora nierówności (<= i> =). Może być używany z wartościami liczbowymi, znakowymi i datowymi.

Na przykład warunek WHERE SALARY BETWEEN 1500 AND 2500 w zapytaniu SELECT wyświetli tych pracowników, których wynagrodzenie mieści się w przedziale od 1500 do 2500.

Operator IN

Operator IN służy do testowania wartości kolumny w zadanym zestawie wartości. Jeśli kolumnę można przyrównać do dowolnej wartości z podanego zestawu, warunek jest sprawdzany. Warunek zdefiniowany za pomocą operatora IN jest również nazywany warunkiem członkostwa.

Na przykład warunek WHERE SALARY IN (1500, 3000, 2500) w zapytaniu SELECT ograniczy wiersze, w których pensja wynosi 1500, 3000 lub 2500.

Operator LIKE

Operator LIKE służy do dopasowywania wzorców i wyszukiwania z użyciem symboli wieloznacznych w zapytaniu SELECT. Jeśli część wartości kolumny jest nieznana, do zastąpienia nieznanej części można użyć symbolu wieloznacznego. Używa operatorów symboli wieloznacznych do tworzenia ciągu wyszukiwania, dlatego wyszukiwanie jest znane jako wyszukiwanie z użyciem symboli wieloznacznych. Te dwa operatory to Percentyl („%”) i Podkreślenie („_”). Podkreślenie („_”) zastępuje pojedynczy znak, a percentyl („%”) zastępuje więcej niż jeden znak. Mogą być również używane w połączeniu.

Na przykład poniższe zapytanie SELECT wyświetla imiona tych pracowników, których nazwisko zaczyna się od „SA”.

SELECT first_name
FROM employees
WHERE last_name LIKE 'SA%';

Warunki są (NIE) NULL

Należy zauważyć, że wartości NULL nie można testować przy użyciu operatora równości. Dzieje się tak, ponieważ wartości NULL są nieznane i nieprzypisane, podczas gdy operator równości testuje określoną wartość. Operator IS NULL służy jako operator równości do sprawdzania wartości NULL w kolumnie.

Na przykład warunek WHERE COMMISSION_PCT IS NULL w zapytaniu SELECT wyświetli listę pracowników, którzy nie mają procentu prowizji.

Operatory logiczne

Do predykatu klauzuli WHERE można dodać wiele warunków filtru. Więcej niż jeden warunek można połączyć razem za pomocą operatorów logicznych AND, OR i NOT.

• AND: łączy dwa lub więcej warunków i zwraca wyniki tylko wtedy, gdy wszystkie warunki są spełnione.

• LUB: łączy dwa lub więcej warunków i zwraca wyniki, jeśli którykolwiek z warunków jest spełniony.

• NOT: neguje wyrażenie, które po nim występuje.

Operator AND łączy dwa lub więcej warunków w klauzuli WHERE i zwraca wartość TRUE tylko wtedy, gdy wszystkie warunki są spełnione. Załóżmy, że kierownik potrzebuje listy pracownic. Ponadto lista powinna zawierać tylko pracowników z nazwiskami zaczynającymi się na literę „E” lub późniejszymi w alfabecie. Dodatkowo tabelę wyników należy posortować według nazwiska pracownika. Trzeba spełnić dwa proste warunki. Klauzulę WHERE można zapisać jako: WHERE Gender = 'F' AND last_name> 'E'.

SELECT last_name "Last Name", first_name "First Name", Gender "Gender"
FROM employees
WHERE Gender = 'F' AND last_name > 'E'
ORDER BY last_name;

Operator OR łączy więcej niż jeden warunek w klauzuli WHERE i zwraca wartość TRUE, jeśli którykolwiek z warunków zwraca prawdę. Załóżmy, że wymagania menedżera organizacji nieco się zmienią. Potrzebna jest inna lista pracowników, ale na tej liście pracownicy powinni: (1) być kobietą lub (2) mieć nazwisko zaczynające się na literę „T” lub literę, która występuje później w alfabecie. Tabela wyników powinna być posortowana według nazwiska pracownika. W tej sytuacji jeden z dwóch warunków może być spełniony w celu spełnienia zapytania. Pracownice płci żeńskiej powinny być wymienione wraz z pracownikami posiadającymi nazwisko, które spełnia drugi warunek.

Operator NOT służy do zanegowania wyrażenia lub warunku.

Klauzula ORDER BY

Gdy wyświetlasz tylko kilka wierszy danych, sortowanie danych wyjściowych może być niepotrzebne; Jednak gdy wyświetlasz wiele wierszy, menedżerowie mogą być wspomagani w podejmowaniu decyzji poprzez sortowanie informacji. Dane wyjściowe instrukcji SELECT można sortować za pomocą opcjonalnej klauzuli ORDER BY. W przypadku użycia klauzuli ORDER BY nazwa kolumny, dla której zamawiasz, musi być również nazwą kolumny określoną w klauzuli SELECT.

Poniższe zapytanie SQL używa klauzuli ORDER BY do sortowania tabeli wyników według kolumny last_name w porządku rosnącym. Kolejność rosnąca jest domyślną kolejnością sortowania.

SELECT last_name, first_name
FROM employees
WHERE last_name >= 'J'
ORDER BY last_name;

last_name        first_name
---------------  ---------------
Jones            Quincey
Klepper          Robert
Quattromani      Toni
Schultheis       Robert

Sortowanie może również opierać się na wartościach liczbowych i datach. Sortowanie można również przeprowadzić na podstawie wielu kolumn.

Domyślnie klauzula ORDER BY posortuje wiersze wyjściowe w tabeli wynikowej w porządku rosnącym. Możemy użyć słowa kluczowego DESC (skrót od descending), aby włączyć sortowanie malejące. Alternatywną wartością domyślną jest ASC, które sortuje w kolejności rosnącej, ale słowo kluczowe ASC jest rzadko używane, ponieważ jest to ustawienie domyślne. Gdy używane jest opcjonalne słowo kluczowe ASC lub DESC, musi ono występować po nazwie kolumny, według której odbywa się sortowanie, w klauzuli WHERE.

Positional Sorting - Numeryczną pozycję kolumny na liście wybranych kolumn można podać w klauzuli ORDER BY zamiast nazwy kolumny. Jest używany głównie w zapytaniach UNION (omówionych później). Zapytanie porządkuje wynik według wynagrodzenia, ponieważ pojawia się on jako drugi na liście kolumn.

SELECT  first_name, salary
FROM employees
ORDER BY 2;

Zmienne substytucyjne

Gdy zapytanie SQL musi być wykonane więcej niż jeden raz dla innego zestawu danych wejściowych, można użyć zmiennych zastępczych. Zmienne podstawienia mogą służyć do monitowania o wprowadzenie danych przez użytkownika przed wykonaniem zapytania. Są szeroko stosowane w generowaniu raportów na podstawie zapytań, które pobiera zakres danych od użytkowników jako dane wejściowe do warunkowego filtrowania i wyświetlania danych. Zmienne zastępcze są poprzedzone pojedynczym symbolem ampersand (&) w celu tymczasowego przechowywania wartości. Na przykład,

SELECT EMPLOYEE_ID, LAST_NAME, SALARY
FROM employees
WHERE LAST_NAME = &last_name
OR EMPLOYEE_ID = &EMPNO;

Kiedy powyższe zapytanie SELECT jest wykonywane, oracle identyfikuje „&” jako zmienną zastępczą. Prosi użytkownika o wprowadzenie wartości dla „last_name” i „EMPNO”, jak poniżej.

Enter value for last_name:
Enter value for empno:

Gdy użytkownik wprowadzi dane wejściowe do obu zmiennych, wartości są podstawiane, a zapytanie jest weryfikowane i wykonywane.

Punkty, na które należy zwrócić uwagę -

• Jeśli zmienna ma zastępować znak lub wartość daty, literał musi być ujęty w pojedyncze cudzysłowy. Użyteczną techniką jest umieszczenie zmiennej podstawiania ampersand w pojedyncze cudzysłowy, gdy mamy do czynienia z wartościami znaków i dat.

• Zarówno SQL Developer, jak i SQL * Plus obsługują zmienne podstawienia i polecenia DEFINE / UNDEFINE. Chociaż SQL Developer lub SQL * Plus nie obsługuje sprawdzania poprawności (z wyjątkiem typu danych) na danych wejściowych użytkownika.

• Zmiennych podstawiania można używać nie tylko w klauzuli WHERE instrukcji SQL, ale także jako substytucji nazw kolumn, wyrażeń lub tekstu.

Korzystanie ze zmiennej podwójnej Ampers i substytucji

Gdy ta sama zmienna podstawienia jest używana w więcej niż jednym miejscu, aby uniknąć ponownego wprowadzania tych samych danych, używamy podwójnego ampersand podstawienia. W takich przypadkach wartość zmiennej podstawiania, raz wprowadzona, zostanie podstawiona we wszystkich momentach użycia.

SELECT first_name, HIRE_DATE, SEPARATION_DATE
FROM employees
WHERE HIRE_DATE LIKE '%&DT%' AND SEPARATION_DATE '%&&DT%'

Zauważ, że ta sama wartość & DT jest podstawiana dwukrotnie w powyższym zapytaniu. Czyli jego wartość raz podana przez użytkownika zostanie zastąpiona w dwóch miejscach.

Polecenia DEFINIUJ i WERYFIKUJ

Definicję zmiennych w sesji określa funkcja DEFINE programu SQL * Plus. Zmienne można zdefiniować w sesji, aby uniknąć zatrzymania podczas wykonywania zapytania. Oracle odczytuje tę samą zmienną za każdym razem, gdy zostanie napotkana w zapytaniu SQL. Domyślnie jest włączony. Za pomocą klauzuli DEFINE można zadeklarować zmienną w linii poleceń przed wykonaniem zapytania jakoDEFINE variable=value;.

Polecenie Verify weryfikuje powyższe podstawienie wyświetlane jako instrukcja OLD i NEW. Domyślnie jest wyłączone i można je ustawić na ON za pomocą polecenia SET.

SQL> SET DEFINE ON
SQL> SET VERIFY ON
SQL> DEFINE NAME = MARTIN'
SQL> SELECT first_name, SALARY 
FROM employees
WHERE first_name = '&NAME';
OLD   1: select first_name, sal from employee where first_name = '&first_name'
new   1: select first_name, sal from employee where first_name = 'MARTIN'

first_name     SALARY
-------        -------
MARTIN         5000