OraLessons-I / Les-02-Restricting and Sorting data

Ограничение и сортировка выходных данных.

Цели: После освоение материала данной лекции, студенты должны быть в состоянии выполнить следующие действия:

1. Ограничить строки, которые извлекаются с помощью SQL запроса;

2. Произвести сортировку строк, которые извлекаются с помощью SQL запроса;

3. Использовать символ подстановки «&» для ограничения и упорядочивания строк во время выполнения запроса.

1. Ограничение количества выбираемых строк путём отбора

Исходные данные:

EMPLOYEES

EMPLOYEE_ID	LAST_NAME	JOB_ID	DEPARTMENT_ID
100	King	AD_PRES	90
101	Kochhar	AD_VP	90
102	De Haan	AD_VP	90
103	Hunold	IT_PROG	60
104	Ernst	IT_PROG	60
107	Lorentz	IT_PROG	60
124	Mourgos	ST_MAN	50

Задача: Выбрать всех служащих отдела 90.

Результат:

EMPLOYEE_ID	LAST_NAME	JOB_ID	DEPARTMENT_ID
100	King	AD_PRES	90
101	Kochhar	AD_VP	90
102	De Haan	AD_VP	90

1.1 Использование предложения WHERE

SELECT * | {[DISTINCT] столбец | выражение [псевдоним], ...}

FROM таблица

[WHERE условие(я)] ;

Примечания:

1. Для ограничения выбираемых запросом строк необходимо использовать предложение WHERE.

2. Предложение WHERE всегда следует за предложением FROM.

3. Предложение WHERE может сравнивать значения в столбцах, литералы, арифметические выражения и функции.

4. Псевдонимы не могут использоваться в предложении WHERE.

Пример

SELECT employee_id, last_name, job_id, department_id

FROM employees

WHERE department_id = 90 ;

1.2 Символьные строки и даты

1. Символьные строки и даты заключаются в апострофы (‘ ‘).

2. В символьных значениях различаются регистры символов, а в датах - форматы.

3. Формат дат по умолчанию: DD-MON-RR (число-месяц-год).

Примечание

СУБД Oracle хранит даты во внутреннем числовом формате, представляющем столетие, год, месяц, число, часы, минуты и секунды.

Пример

SELECT last_name, job_id, department_id

FROM employees

WHERE last_name = 'Whalen' ;

1.3 Операторы сравнения

Оператор	Значение
=	Равно
>	Больше, чем
>=	Больше или равно
<	Меньше, чем
<=	Меньше или равно
<> или !=	Не равно
BETWEEN . . . AND . . .	Между двумя значениями (включительно)
IN (set)	Совпадает с любым из списка значений
LIKE	Совпадает с символьным шаблоном (образцом)
IS NULL	Является неопределённым значением NULL

Пример 1

SELECT last_name, salary

FROM employees

WHERE salary <= 3000 ;

Пример 2

SELECT last_name, salary, hire_date

FROM employees

WHERE hire_date >= ’01-JAN-95’ ;

1.4 Использование условия BETWEEN

Условие BETWEEN используется для выборки строк на основе диапазона значений, например:

SELECT last_name, salary

FROM employees

WHERE salary BETWEEN 2500 AND 3500 ;

нижняя верхняя

граница граница

1.5 Использование условия IN

Условие принадлежности IN используется для проверки на вхождение значений в список, например:

SELECT employee_id, last_name, salary, manager_id

FROM employees

WHERE manager_id IN (100, 101, 201);

Другой пример:

SELECT employee_id, manager_id, department_id

FROM employees

WHERE last_name in (‘King’, ‘Ernst’)

1.6 Использование условия LIKE

1. Условие LIKE используется для поиска символьных значений по шаблону с метасимволами.

2. Условия поиска могут включать алфавитные и цифровые символы.

3. Метасимволы:

«%» - обозначает любую последовательность из нуля или более символов;

«_» - обозначает любой одиночный символ.

Пример 1

SELECT first_name

FROM employees

WHERE first_name LIKE 'S%' ;

Пример 2

SELECT last_name, hire_date

FROM employees

WHERE hire_date LIKE ‘%95’ ;

Примечание 1

Метасимволы можно комбинировать.

Пример

SELECT last_name

FROM employees

WHERE last_name LIKE '_o%' ;

Примечание 2

Для поиска символов «%» и «_» можно использовать опцию ESCAPE. Эта опция задаёт символ, присутствие которого превращает метасимвол в обычный символ.

Пример

SELECT employee_id, last_name, job_id

FROM employees

WHERE job_id LIKE ‘%SA\_%’ ESCAPE ‘\’ ;

1.7 Использование условия NULL

1. Проверка на неопределённые значения проводится с помощью оператора IS NULL.

2. Возможен и другой вариант использования: IS NOT NULL.

Пример 1

SELECT last_name, manager_id

FROM employees

WHERE manager_id IS NULL ;

Пример 2

SELECT last_name, job_id, commission_pct

FROM employees

WHERE commission_pct IS NOT NULL ;

1.8 Логические условия

Оператор	Значение
AND	Возвращает значение TRUE, если выполняются оба условия.
OR	Возвращает значение TRUE, если выполняется любое из условий.
NOT	Возвращает значение TRUE, если следующее условие не выполняется.

С помощью операторов AND и OR можно задавать несколько условий в одном предложении WHERE.

1.9 Использование оператора AND

Оператор AND требует выполнения обоих условий.

SELECT employee_id, last_name, job_id, salary

FROM employees

WHERE salary >= 10000

AND job_id LIKE '%MAN%' ;

Таблица истинности для оператора AND

AND	TRUE	FALSE	NULL
TRUE	TRUE	FALSE	NULL
FALSE	FALSE	FALSE	FALSE
NULL	NULL	FALSE	NULL

1.10 Использование оператора OR

Оператор OR требует выполнения любого из условий.

SELECT employee_id, last_name, job_id, salary

FROM employees

WHERE salary >= 10000

OR job_id LIKE '%MAN%' ;

Таблица истинности для оператора OR

OR	TRUE	FALSE	NULL
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	NULL
NULL	TRUE	NULL	NULL

1.11 Использование оператора NOT

SELECT last_name, job_id

FROM employees

WHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP') ;

Таблица истинности для оператора NOT

NOT	TRUE	FALSE	NULL
	FALSE	TRUE	NULL

Примечание

Оператор NOT обычно используется с операторами BETWEEN, IN, LIKE, а также в операторе IS NOT NULL.

1.12 Приоритеты операторов

Порядок вычисления	Оператор
1	Арифметические операторы
2	Оператор конкатенации
3	Операторы сравнения
4	IS [NOT] NULL, LIKE, [NOT] IN
5	NOT BEYWEEN
6	NOT
7	AND
8	OR

Изменить стандартную последовательность вычислений можно с помощью круглых скобок.

Пример 1

SELECT last_name, job_id, salary

FROM employees

WHERE job_id = 'SA_REP'

OR job_id = 'AD_PRES'

AND salary > 15000;

Пример 2

SELECT last_name, job_id, salary

FROM employees

WHERE (job_id = 'SA_REP'

OR job_id = 'AD_PRES')

AND salary > 15000;

2. Использование предложения ORDER BY

Последовательность строк, возвращаемых запросом, не определенна. Отсортировать строки можно с помощью предложения ORDER BY.

Правила:

1. В команде SELECT предложение ORDER BY указывается последним;

2. Для сортировки можно задать выражения, псевдонимы или столбцы;

3. Режимы упорядочивания:

• ASC – в порядке возрастания значений (по умолчанию);

• DESC – в порядке убывания значений.

Синтаксис:

SELECT * | {[DISTINCT] столбец | выражение [псевдоним], ...}

FROM таблица

[WHERE условие(я)]

[ORDER BY {выражение | столбец | псевдоним} [ASC | DESC]] ;

2.1 Сортировка по возрастанию

SELECT last_name, job_id, hire_date

FROM employees

ORDER BY last_name ;

2.2 Сортировка по убыванию

SELECT last_name, job_id, department_id, hire_date

FROM employees

ORDER BY hire_date DESC ;

2.3 Сортировка по псевдонимам столбцов

SELECT employee_id, last_name, salary*12 AS annsal

FROM employees

ORDER BY annsal ;

2.4 Сортировка по нескольким столбцам

SELECT last_name, department_id, salary

FROM employees

ORDER BY department_id, salary DESC ;

Неопределённые значения при сортировке по возрастанию выводятся последними, а при сортировке по убыванию – первыми.

8