Статьи

Login Form



Глава 3: Условный оператор WHERE PDF Печать E-mail
Автор: Administrator   
25.04.2011 09:45

Условный оператор WHERE

 

Мы уже вместе с вами научились выбирать данные из таблицы. Выборка данных прочитанная в предыдущей главе — является лишь верхушкой айсберга. Самое интересное начинается лишь тогда, когда вы должны вытащить данные соответствующие тем или иным критериям. В данной главе мы изучим как:

 

Ограничить строки выдаваемые запросом

 

Следующая команда выдает всю информацию находящиюся в таблице EMP

 

SELECT * FROM emp;

 

EMPNO

ENAME

JOB

MGR

HIREDATE

SAL

COMM

DEPTNO

7 369

SMITH

CLERK

7 902

17.12.1980

800,00

 

20

7 499

ALLEN

SALESMAN

7 698

20.02.1981

1 600,00

300,00

30

7 521

WARD

SALESMAN

7 698

22.02.1981

1 250,00

500,00

30

7 566

JONES

MANAGER

7 839

02.04.1981

2 975,00

 

20

7 654

MARTIN

SALESMAN

7 698

28.09.1981

1 250,00

1400,00

30

7 698

BLAKE

MANAGER

7 839

01.05.1981

2 850,00

 

30

7 782

CLARK

MANAGER

7 839

09.06.1981

2 450,00

 

10

7 788

SCOTT

ANALYST

7 566

19.04.1987

3 000,00

 

20

7 839

KING

PRESIDENT

 

17.11.1981

5 000,00

 

10

7 844

TURNER

SALESMAN

7 698

08.09.1981

1 500,00

0,00

30

7 876

ADAMS

CLERK

7 788

23.05.1987

1 100,00

 

20

7 900

JAMES

CLERK

7 698

03.12.1981

950,00

 

30

7 902

FORD

ANALYST

7 566

03.12.1981

3 000,00

 

20

7 934

MILLER

CLERK

7 782

23.01.1982

1 300,00

 

10

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Такие запросы не носят в себя большой смысловой нагрузки. Так как в данном случаи у нас в таблице есть всего 14 строк и нам легко их проанализировать, но в реальных системах данные достигают более миллиона строк в одной таблице и подобный вывод нам ничего не даст.

 

В языке SQL есть условный оператор WHERE, который служит ограничителем выдаваемых строк в запросе.

 

Так например запрос соответствующей задаче вытащить данные только менеджеров из таблицы EMP будет выглядит следующем образом:

 

SELECT  *  FROM emp

WHERE  job = 'MANAGER'

 

EMPNO

ENAME

JOB

MGR

HIREDATE

SAL

COMM

DEPTNO

7 566

JONES

MANAGER

7 839

02.04.1981

2 975,00

 

20

7 698

BLAKE

MANAGER

7 839

01.05.1981

2 850,00

 

30

7 782

CLARK

MANAGER

7 839

09.06.1981

2 450,00

 

10

 

 

 

Общая схема запроса с условным оператором

 

Таким образом наша общая схема запроса дополняется с условным оператором WHERE.

 

1) SELECT * | {[DISTINCT] column | expression [[as] allias], ...}

2) FROM table_name

3) WHERE condition(s)

 

Как мы видим в общую схему добавилось лишь одна строка где:

  • WHERE - Ключевое слово, которое указывает на то, что необходимо вытащить строки соответствующее тем или иным критериям. При этом необходимо отметить, что строки будут возвращены лишь при условии, того что наши критерии будут истинными, т.е. будут соответствовать нашим условиям.

 

  • condition(s)  - это и есть те самые условие, которые должны быть истинными. В запросе может быть как одно условие, так и несколько. Условия могут состоять из имен столбцов, из арифметических, строковых и логических выражений, а также из функций.

 

  • [ ] - Квадратные скобки говорят о том, что условного оператора может и не быть, свидетелями чего мы уже были.

 

Давайте все-таки изучим, что же за чудесный запрос мы написали до этого:

 

SELECT  * FROM emp

WHERE  job = 'MANAGER'

 

Первая строка нам уже знакома. А вот вторая строка началась с ключевого слово WHERE, которое указывает, что необходимо вытащить данные соответствующие критериям. В  качестве критерия мы видим имя столбца  “job”, который нам говорит, что значения столбца  “job”  должны быть равным (именно знак равенства = и идет после название столбца) слову MANAGER (*).

 

Также стоит отметить, что все слова написанные внутри одинарных кавычек являются регистра чувствительными. Таким образом слово 'MANAGER', 'manager' и 'Manager' отличаются друг от друга.  А написание название столбца как “job” или “jOB”  одинаковы, так как в написание самих SQL конструкций, название таблиц, столбцов таблицы, функций - язык SQL не регистра чувствительный.

 

При запуске следующего запроса, получаем ровно ноль строк, так как не будет строк соответствующих нашим критериям.

 

 

 

(*) Для указаний выражений строкового типа используется одинарные кавычки в начале и в конце выражения.

SELECT  * FROM emp

WHERE  job = 'manager'

 

 

Но в тоже время следующий запрос  вернет знакомый нам результат.

 

select * frOm EMp

wHERE jOb = 'MANAGER'

 

 

Выражения сравнения

 

Рассмотрим схему выражений сравнений.

 

Оператор

Значение

 

 

=

равно

>

больше

<

меньше

>=

больше или равно

<=

меньше или равно

<>

не равно

!=

не равно

 

Таким образом в нашем запросе общая схема условного оператора примет следующий вид:

 

...WHERE expression operator value

 

Запрос возвращающий всех сотрудников у кого зарплата выше 2500, будет выглядеть следующем образом:

 

SELECT * FROM emp

WHERE sal > 2500

 

Запрос возвращающий всех сотрудников у кого зарплата не равно 0, будет выглядеть следующем образом:

 

SELECT * FROM emp

WHERE sal <> 0

 

Приступим к изучению более сложных операторов сравнения.

 

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

 

Оператор сравнения BETWEEN может быть полезен в случаи, когда  необходимо будет выбирать данные в определенном диапазоне, в котором будут точно указано минимальное значение и максимальное значение диапазона. Например, зарплаты сотрудников у кого она находиться в диапазоне 2500 — 3500. При использовании BETWEEN указанные значения также входят в условия поиска значений. Т.е. 2500 и 3500 входят в поиск, а вот и сам запрос:

 

SELECT *

FROM emp

WHERE sal between 2500 and 3500

 

Механизм довольно прост. Сперва указывается столбец, по которому идет поиск, затем оператор  BETWEEN после минимальное значение с какого следует начинать поиск. Логичский оператор and и значение указывающее конец диапазона.

 

Хотя, конечно же надо отметить верхний SQL c BETWEEN можно заменить, на следующий SQL:

 

SELECT *

FROM emp

WHERE sal >= 2500 and  sal <= 3500

 

 

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

 

У нас имеется список значений, который мы хотим сверить со значениями  одного из наших столбцов. В данном случаи может быть использован оператор IN.

 

К примеру, перед нами стоит задача получить полный список сотрудников которые работают на должности менеджера и аналиста:

 

SELECT  *  FROM emp

WHERE job IN ('MANAGER', 'ANALYST')

 

Как видно из примера, указывается оператор IN, а после в круглых скобках через запятую значения, которым мы хотим, чтобы соответствовала поле поиска. Таким образом, через запятую можно указывать довольно большое количество значений.

 

Отобразить всех у кого зарплата равна 500, 600, 700.

 

SELECT  *  FROM emp

WHERE sal IN (500, 600, 700)

 

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

 

Прежде чем начинать описания оператора LIKE, сперва разберем регулярные выражения.

Регулярное выражение это конструкции позволяющие вести поиск в тексте по различным условиям. Регулярные выражения на сегодняшний день очень широко используется как для поиска (в случае SQL), так и для ограничений ввода, к примеру, при вводе номера телефона, телефон должен соответствовать маске XXX-XX-XX, где X- это число. Также один из наглядных примеров применение регулярных выражений, это проверка mail (cторока@строка.строка) при регистрации в интернет ресурсах.

Значит, мы уяснил для себя проверка данных, поиск дубликатов, обнаружение лишних пробелов или разбор строки - это все относятся к примерам использования регулярных выражений. И еще раз повторимся, их можно применять для проверки формата телефонного номера, zip-кода, email-адресов, номеров социального обеспечения, IP-адресов, имен файлов и директорий и так далее. Кроме того, можно искать комбинации, например, HTML-тегов, чисел, дат, и другое, которые соответствуют какому-либо шаблону в тексте и заменять их другим набором символов.

Регулярное выражение содержит один и более символов и/или метасимволов. В самом простом виде регулярное выражение может состоять только из символов, например, регулярное выражение cat. Оно читается, как буква c, за которой следует буква a и t, и этому шаблону соответствуют такие строки, как cat, location и catalog.

Метасимволы обеспечивают алгоритмы обработки в Oracle символов, из которых состоит регулярное выражение. Когда значение различных метасимволов будет понятным, вы увидите, как удобны регулярные выражения для выделения и замены каких-либо текстовых данных.

Для работы с регулярными выражениями в SQL имеется оператор сравнения LIKE. Это очень полезный оператор. Например для поиска всех сотрудников, чьи имена начинается с буквы S будет иметь следующий вид:

SELECT * FROM emp

WHERE ename like 'S%'

 

Как видно из примера мы использовали ключевое слово LIKE после, которого обязательно идет одинарная кавычка и собственно сам шаблон поиска. В данном случаи мы использовали шаблон S% буква S означает, что слово начинается  с буквы S большое, а знак процента означает, что за буквой может следовать любое количество любых символов - даже нулевой, таким образом, если у нас был сотрудник с именем S, то он тоже попал бы в результат вывода запроса.

 

Есть два ключевых символов, которые используется во время поиска:

 

%                означает любые символы и любое количество символов (от 0 до ..) (даже пустой символ – null)

_                 символ заменяющий любой один символ

 

Таким образом для поиска человека у кого в имени вторая буква L воспользуемся скриптом:

 

SELECT  * FROM emp

WHERE ename like '_L%'

 

Возможны случае, когда необходимо ввести поиска по символом ‘%’ и ‘_’  в слове. В этом случаи необходимо воспользоваться ключевым словом ESCAPE.

 

SELECT * FROM emp

WHERE ename like 'S/_' ESCAPE '/'

 

Этот SQL означает воспринимать ПЕРВЫЙ символ идущий за символом / символ _ не как часть синтаксиса LIKE, а как обычный символ, как он есть.

 

Отобразить все имена длина, которых равна пяти и данные имена начинаются с символа ‘_’.

 

SELECT * FROM emp

WHERE ename like '/_____' ESCAPE '/'

 

Отобразить все имена различной длины, в составе которых содержиться символ ‘%’.

 

SELECT * FROM emp

WHERE ename like '%/%%' ESCAPE '/'

 

 

Поиск значений с нулевыми значениями IS NULL

 

Нулевое значение - Null является специальным маркером, используемых в SQL, чтобы указать, что данного значение не существует в базе. Понятие нуль было впервые введено Ф. Кодд в качестве метода представления отсутствующих данных в базе данных. Примером пустого значения служит поле comm в некоторых строках таблице emp. Как вы заметили они просто отсутствуют, они просто пусты — это и означает, что это поле содержит значение null.

 

Если нужно вытащить все строки, в которых поле comm пустое (null), то нижний запрос нам не сработает:

 

SELECT  * FROM emp

WHERE comm = ''

 

Для поиска пустых значений, необходимо использовать оператор IS NULL.

 

SELECT  * FROM emp

WHERE comm IS NULL

 

 

 

Трехзначная логика SQL

 

Мы с вами рассмотрели сравнение, где в условном операторе условия было одинарным, т.е. в SQL запросе было всего лишь одно условие. Но пришло время построение более сложных условий к примеру, отобразить всех работников у кого зарплата больше 1000 и еще у них имя начинается с буквы ‘A’ или к примеру условие отобразить работников у кого зарплата в интервале от 1000 до 2000 и

дата устройства на работу работника больше 1980 года или в интервале от 2000 до 4000 и дата устройства на работу работника больше 1983 года.

Для составление таких условий понадобится применение трехзначной логики SQL, но прежде чем разобрать, что такое трехзначная логика в SQL разберем следующие теоретические определения:

Многозначная логика — тип формальной логики, характерный наличием более чем двух возможных истинностных значений (истинности и ложности). В настоящее время существует очень много других систем многозначной логики, которые в свою очередь могут быть сгруппированы по классам.

Трехзначная логика была исторически первой многозначной логикой, и является простейшим расширением двухзначной логики. Перечень истинностных значений трёхзначной логики помимо «истинно» и «ложно» включает также третье значение, которое как правило трактуется как «неопределенно», «неизвестно» или «ошибочно».

Прочитав это два определение интуитивно появляется вопрос «А где здесь SQL ?». Ответ очень прост в SQL реализована трехзначная логика. В качестве «неопределенно», «неизвестно» используется Null. Конечно изучать с Вами трехзначную логику мы не будем. А сразу перейдем практической части использования трехзначной логики в SQL.

 

Оператор пересечения AND

Допустим нам нужен поиск всех менеджеров у кого зарплата меньше определенного значения, для этого необходимо будет запустить запрос с двумя условиями. Добавление условий в ключевое условие WHERE похоже на математическое пересечение множеств.

Пересечение множеств в теории множеств — это множество, состоящее из элементов, которые принадлежат одновременно всем данным множествам.

На языке двоичной логике обычно обозначается как знак умножение двух множеств. Таким образом если у нас одно событие истинно (обычно обозначается 1), а другое событие ложно (обычно обозначается 0), то их пересечение будет ложным т.е. 0.

Т.е. при пересечение наших условий в результат выборки попадут только те строки, которые будут соответствовать обоим условиям. Если под наше пересечение условий не попадет не одна строка, то в результате наш запрос вернет ровно ноль строк.

В SQL пересечение множеств реализовано при помощи ключевого слово AND. При этом при помощи AND можно пересекать результат пересечение двух условий с другими условиями.

Конечно же есть много вариантов пересечения условий, все варианты пересечения и результаты которые получаться в результате изображены в таблице:

AND

TRUE

FALSE

NULL

TRUE

TRUE

FALSE

NULL

FALSE

FALSE

FALSE

FALSE

NULL

NULL

FALSE

NULL

 

То есть, если ИСТИНА и ЛОЖЬ      = ЛОЖЬ

если ИСТИНА и ИСТИНА   = ИСТИНА

если ЛОЖЬ    и ЛОЖЬ      = ЛОЖЬ

если ИСТИНА и ПУСТОТА = ПУСТОТА

но заметьте, если ЛОЖЬ и ПУСТОТА = ЛОЖЬ

 

Скрипт примера запроса описанного выше:

SELECT  * FROM emp

WHERE job = 'MANAGER' and sal < 2500

Оператор объединение OR

 

Мы с вами изучили пересечение нескольких условий при помощи оператора AND. Довольно часто бывает необходимо не только производить пересечение множеств, но и их объединение.

Объединение множеств (сумма или соединение) в теории множеств  — это множество, содержащее в себе все элементы исходных множеств. Объединение двух множеств A и B обычно обозначается в виде суммы A + B. Таким образом, если у нас одно событие истинно (обычно обозначается 1), а другое событие ложно (обычно обозначается 0), то их объединение будет истинным, т.е. 1.

Т.е. при объединение наших условий в результат выборки попадут только те строки, которые будут соответствовать хотя бы одному из условий. Если под наше объедение условий попадет несколько строк удовлетворяющих одному из условий а другому условий не будет удовлетворять не одна строка, то в результате наш запрос нам вернет те несколько строк которые и удовлетворяли одному из условий.

Как вы заметили между AND и OR существует такая же разница, как между математической операцией умножения и сложением.

Все варианты объединения и результаты, которые получаться в результате изображены в таблице:

OR

TRUE

FALSE

NULL

TRUE

TRUE

TRUE

TRUE

FALSE

TRUE

FALSE

FALSE

NULL

TRUE

FALSE

NULL

 

В качестве примера возьмем задачу поиска всех менеджеров и сотрудников, которых получают больше 2500

SELECT * FROM emp

WHERE job = 'MANAGER' OR sal > 2500

 

Если вы внимательно посмотрите на результат выборки, то увидите менеджера у кого зарплата меньше 2500, а также сотрудников, которые не являются менеджерами.

Оператор отрицания NOT

 

Дискретной математике отрицание — это унарная операция над суждениями, результатом которой является суждение, противоположное исходному. А теперь на простом языке, это есть отрицание всего, к примеру отрицание лжи это есть истина.

 

В SQL это реализовано в виде противоположного значения условию. Т.е. Если условие возражает истину, то результат отрицания будет ложным.

 

 

 

Для более наглядности, расмотрим таблицу отрицания:

 

NOT

TRUE

FALSE

NULL

 

FALSE

TRUE

NULL

 

Приведем несколько примеров:

Начнем с простого, список всех сотрудников, не являющихся менеджерами:

SELECT * FROM emp

WHERE not job = 'MANAGER'

 

Конечно же, этот пример можно было реализовать, также путем замены знака равенства на знак не равенства при этом убрав, оператор отрицания NOT. Но такая замена не пройдет для поиска всех сотрудников у кого поле comm не пустое, т.е. не равно null.

 

SELECT * FROM emp

WHERE not comm is  null

 

Как Вы уже заметили для реализации механизма отрицания в вашем запросе необходимо всего лишь перед условием поставить оператор отрицания NOT.

 

Порядок использования операторов в SQL

Вместе с вами мы рассмотрели большое количество операторов SQL, которые могут быть все вместе в одном запросе. Как и в математики ( 2+2*2=6 сперва умножение, а потом сложение), так и в SQL существуют правила порядка обработки операторов. Вам следует ознакомиться таблицей правил обработки операторов:

 

Порядок вычисления

Оператор

1

Арифметические операции

2

Оператор конкатенация

3

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

4

IS [NOT] NULL; LIKE [NOT]; IN [NOT]

5

[NOT] BETWEEN

6

Оператор отрицания NOT

7

Оператор пересечения AND

8

Оператор объединения OR

 

Изучим эту таблицу более подробно:

 

  1. Согласно таблице сперва будет выполнены арифметические операции. Т.е. если мы значения нашего столбца умножим на 100 и сравним с миллионам то, конечно же в первую очередь будет произведена арифметическая операция умножения, а затем уже все остальные операции.
  2. Вторым действием выполниться операция конкатенация. Напомню что конкатенация — это операция объединения двух строк. Сам же оператор конкатенация выглядит довольно просто в виде двух вертикальных параллельных прямых ||.
  3. Конечно же после того, как мы произвели операции указанные выше будет произведено операция сравнения.
  4. Затем будут использованы операции типа IS NULL; LIKE; IN; Если к этим операциям будет применено отрицание, то порядок не измениться.
  5. Указанные 3 операции в 4 пункте равноправны между собой, но они выполняться раньше, чем операция BETWEEN.
  6. Если отрицания будет применено к условию, то сперва будет вычислено каким условием является оно (истинным или ложным), а затем будет произведено операция отрицания.
  7. Конечно же условия использующие между собой оператора AND выполняться раньше нежели оператор OR.
  8. На самом последнем месте стоит оператор OR, но это не означает, что при желании вы не сможете изменить порядок действия AND и OR. Это продемастрирована на примерах:

 

SELECT *

FROM emp

WHERE job = 'CLERK' or job = 'SALESMAN' and sal > 1300

 

В данном примере первое условие это то, что у сотрудника работа SALESMAN и зарабатывает больше 1300, а второе условие то, что сотрудник работает CLERK и результатом будет те строки удовлетворяющие одному из условий.

 

SELECT * FROM  emp

WHERE  (job = 'CLERK' or job = 'SALESMAN')

and sal > 1300

 

Добавив скобки сперва выполняться арифметическое действия т.е. то что скобках, а затем все остальное. Как вы заметили результат первого и второго запроса разные. Все это произошло благодаря скобок и повышения приоритета условию к OR. Наши условия благодаря скобок тоже изменились и сейчас в первую очередь будут искаться сотрудники работающие как CLERK и SALESMAN, а вторым условием будет проверка этих сотрудников на условие с зарплатой. Поэтому во втором запросе результат намного меньше результата первого.

Скобки сами по себе как, выше отмечено позволяют группировать условия в группы, при этом меняя приоритет выполнения. Если использовать скобки бессмысленно при условие, что они образуют пару т.е. у открывающийся скобки есть закрывающиеся скобка, то SQL их просто напросто игнорирует. Результат выборок трех нижних SQL запросов эквиваленты:

 

SELECT *

FROM emp

WHERE job = 'CLERK'

SELECT *

FROM emp

WHERE (job = 'CLERK' )

SELECT *

FROM emp

WHERE ((((job = 'CLERK' ))))

 

Резюме главы 3:

 

В данной главе мы изучили:

 

  • При помощи условного оператора WHERE получать результаты согласно нашим условиям.

       Используя операторы сравнения.

       Используя операторы BETWEEN;LIKE;IN; и NULL

       Используя логические операторы OR; AND; NOT

 

  • Понятие трехзначной логике SQL
  • Таблицу правил порядка обработки операторов
  • Использование скобок в условиях

 

 

Общая схема запроса приобрела в итоге такой вид:

 

SELECT * | {[DISTINCT] column | expression [allias], ...}

FROM table

[WHERE condition(s);]

 

<Предыдущая глава> <Оглавление> <Следующая глава>

Хиты: 21416
 
 

Наши проекты


Друзья