Предложение WHERE и условия поиска

⇐ ПредыдущаяСтр 2 из 2

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

Отметим, что условия поиска применяются не только в предложениях WHERE опера-торов SELECT, но и в операторах UPDATE и DELETE языка манипулирования данными (см. выше примеры этих операторов).

Договоримся сначала об используемой терминологии. Условие поиска (search condition) может содержать произвольное количество предикатов (predicates), соединенных логически-ми операциями (logical operators) AND, ORи NOT (И, ИЛИи НЕ). Предикаты –это выраже-ния, возвращающие значения TRUE, FALSE или UNKNOWN (ИСТИНА, ЛОЖЬ или НЕИЗ-ВЕСТНО). Выражение (expression) может быть именем колонки, константой, скалярной функцией (т.е. функцией, возвращающей одно значение), переменной, скалярным подзапро-сом (т.е. запросом, возвращающим одну колонку), либо комбинацией этих элементов, соеди-ненных операциями.

Операции сравнения. В выражениях можно использовать имеющие традиционный смысл операции сравнения (comparison operators), такие как =, <>, >, <, >=, <=.

Например, в простом предложении WHEREможет производиться сравнение двух выра-жений при помощи операции сравнения на равенство (=). Ниже приведен пример оператора SELECT,проверяющего значения в колонке au_lname для всех строк (а эти значения имеют тип данных char) и возвращающего TRUE, если это значение равно "Ringer" (в набор резуль-татов будут включены строки, для которых возвращается значение TRUE):

SELECT *

FROM authors

WHERE au_lname = 'Ringer'

Запрос, приведенный в этом примере, вернет две строки. Фамилия Ringer должна быть задана в кавычках, потому что она является текстовой строкой.

Отметим, что SQL Server допускает применение как символов одинарных кавычек ('...'), так и символов двойных кавычек ("..."). Но для того чтобы двойные кавычки могли быть ис-пользованы, надо применить команду SET QUOTED_IDENTIFIER OFF.

В следующем запросе применяется операция неравенства (<>), на этот раз по отноше-нию к колонке job_id, имеющей тип данных integer:

SELECT job_desc FROM jobs WHERE job_id <> 1

Этот запрос выдает текст с описанием должностных обязанностей из строк таблицы jobs, имеющих значения job_id, не равные 1. Будет выдано 13 строк.

Логические операции (logical operators) ANDи ORпроверяют два выражения и, в зави-симости от их значений, возвращают булево значение TRUE, FALSEили NULL. Операция NOT возвращает булево значение, противоположное значению выражения, следующего за ним. Исключение составляет значение Null. Противоположное ему значение – тоже Null. Значения, возвращаемые операциями логическими операциями, показаны в следующей таб-лице.

Таблица 3.1 Логические операции над операндами, принимающими значения TRUE, FALSE, NULL


Вход		Результат операции

A	B	NOTA	A AND B	A OR B

TRUE	TRUE	FALSE	TRUE	TRUE

TRUE	FALSE	FALSE	FALSE	TRUE
TRUE	Null	FALSE
TRUE	Null	FALSE	Null	TRUE

FALSE	TRUE	TRUE	FALSE	TRUE

FALSE	FALSE	TRUE	FALSE	FALSE

FALSE	Null	TRUE	FALSE	Null

Null	TRUE	Null	Null	TRUE

Null	FALSE	Null	FALSE	Null
Null	Null	Null	Null	Null

В следующем запросе в предложении WHEREимеются два выражения, соединенных ло-гической операцией AND:

SELECT job_desc, min_lvl, max_lvl FROM jobs

WHERE min_lvl >= 100 AND max_lvl <= 225

Как видно из таблицы 3.1, операция ANDвозвращает значение TRUE, когда оба условия возвращают TRUE. Этот запрос вернет четыре строки.

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

SELECT p.pub_name, p.state, t.title FROM publishers p, titles t

WHERE p.state = 'DC' OR p.state = 'MA' AND t.pub_id = p.pub_id

Этот запрос вернет 23 строки.

Операция NOTвозвращает просто отрицание булева значения выражения, следующего за ним. Например, чтобы получить список всех названий книг, у которых авторские отчисле-ния составляют не менее 20%, можно применить операцию NOT:

SELECT t.title, r.royalty FROM titles t, roysched r

WHERE t.title_id = r.title_id AND NOT r.royalty < 20

Этот запрос выдаст 18 названий книг, у которых авторские отчисления составляют 20% или более.

Другие ключевые слова

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

LIKE. Ключевое слово LIKEприменяется для поиска по соответствию шаблону. Соот-ветствие шаблону (pattern matching) проверяется для двух операндов условия поиска – со-поставляемого выражения (match expression) и шаблона (pattern), задающего условие поиска. При этом используется такой синтаксис:

<сопоставляемое_выражение> LIKE <шаблон>

Если сопоставляемое выражение соответствует шаблону, то возвращается булево зна-чение TRUE, а если нет, то возвращается FALSE. Сопоставляемое выражение должно иметь

тип данных character string, в противном случае СУБД преобразует его в данные, имеющие тип character string, если это возможно.

Шаблоны являются строковыми выражениями (string expressions), т.е. строками, со-стоящими из символов (characters) и метасимволов (wildcard characters). Метасимволы – это символы, имеющие особый смысл при использовании внутри строковых выражений. Мета-символы, которые можно применять в шаблонах T-SQL, перечислены в табл. 3.2.

Таблица 3.2 Метасимволы T-SQL


Метасимвол	Описание

%	Символ процента соответствует строке из нескольких символов (в том числе, пустой строке и строке из одного символа)

_	Символ подчеркивания соответствует любому одному символу

[]	Метасимвол диапазона соответствует любому одному символу из заданного диапазона или набора символов. Например, [m-p] или [mnop] соответствуют любому из символов m, n, o или p

[^]	Метасимвол "не в диапазоне" соответствует любому одному символу, не входящему в диапазон или набор символов. Например, [^m-p] или [^mnop] соответствуют любому из символов, кроме символов m, n, o или p

Чтобы лучше понять применение ключевого слова LIKEи метасимволов, рассмотрим два примера. Если нужно найти в таблице authors все фамилии, начинающиеся с буквы S, то можно воспользоваться таким запросом с метасимволом %:

SELECT au_lname FROM authors

WHERE au_lname LIKE 'S%'

Результат этого запроса может быть, например, таким: au_lname

-----------Smith Straight Stringer

В этом запросе "S%" означает, что нужно возвращать все строки с фамилиями, первой буквой которых будет S, а за ней может следовать произвольное количества букв.

Теперь рассмотрим пример применения метасимвола []. Чтобы получить фамилии ав-торов, начинающиеся на букву от A до M, можно воспользоваться метасимволом [] в сочета-нии с метасимволом %, вот так:

SELECT au_lname FROM authors

WHERE au_lname LIKE '[A-M]%'

Результат будет содержать 14 строк с фамилиями, начинающимися на букву от A до M. Если в этом запросе поменять метасимвол [] на [^], то будут выданы строки, содержа-

щие фамилии, начинающиеся на буквы вне диапазона от A до M:

SELECT au_lname FROM authors

WHERE au_lname LIKE '[^A-M]%'

Такой запрос вернет 9 строк.

Перед ключевым словом LIKE можно помещать операцию NOT. NOT LIKE выдает строки, не соответствующие заданному условию. Например, чтобы найти названия книг, не начинающиеся со слова The, можно воспользоваться таким запросом:

SELECT title FROM titles

WHERE title NOT LIKE 'The %'

Этот запрос вернет 15 строк.

BETWEEN. Ключевое слово BETWEEN используется всегда в сочетании с ключевым словом AND и задает диапазон вхождения, применяемый как условие поиска. Ниже показан его синтаксис:

<проверяемое_выражение> BETWEEN <начальное_выражение> AND <конечное_выражение>

Если проверяемое_выражениебольше или равно, чем начальное_выражение, и в то же время меньше или равно, чем конечное_выражение, то результатом условия поиска яв-ляется булево значение TRUE,а в противном случае результатом условия поиска является FALSE.

Пример запроса с ключевым словом BETWEEN был приведен выше, когда рассматрива-лось использование аргумента DISTINCT.

Вместе с BETWEENтоже можно применять NOT, тогда будут искаться строки, не входя-щие в заданный диапазон. Например, чтобы найти названия книг с ценами вне диапазона от 20 до 30 долларов (а именно, книг дешевле 20 и дороже 30 долларов), можно было бы при-менить такой запрос:

SELECT price, title FROM titles

WHERE price NOT BETWEEN 20.00 AND 30.00

В конструкции с ключевым словом BETWEENпроверяемое_выражениедолжно иметь та-кой же тип данных, как и начальное_выражениеи конечное_выражение.

В последнем примере колонка price (цена) имеет тип данных money, поэтому и началь-ное_выражение, и конечное_выражение должны быть числами, сравнимыми с типом данных money, или для них должно быть возможным неявное преобразование в этот тип данных. Вы не можете применять в качестве проверяемого выражения значение из колонки price, а для начального и конечных выражений – символьные строки. Если вы все-таки сделаете это, то SQL Server выдаст сообщение об ошибке.

IS NULL . Ключевое слово IS NULLиспользуется в условиях поиска, ищущих строки, содержащие null-значение в заданной колонке. Например, чтобы найти в таблице titles на-звания книг, у которых нет данных в колонке notes (примечания), т.е. когда значением ко-лонки notesявляется NULL, можно воспользоваться таким запросом:

SELECT title, notes FROM titles

WHERE notes IS NULL

Этот запрос выдаст такой набор результатов:

title notes

---------------------------------------- ------The Psychology of Computer Cooking NULL

Как видите, null-значения в колонке notesв наборе результатов отображаются словом NULL. Это слово не является содержимым колонки, оно служит просто обозначением того, что в данной колонке находится null-значение (введенные Э.Коддом null-значения поясня-лись в теме 2).

Чтобы найти названия книг, у которых имеются данные о колонке notes (т.е., у кото-рых значение колонки notesне является null-значением), следует применить конструкцию IS NOT NULL:

SELECT title, notes FROM titles

WHERE notes IS NOT NULL

Набор результатов будет содержать 17 строк, в каждой из которых в колонке notes будут иметься символы (один или несколько), т.е. не null-значения.

IN. Ключевое слово INиспользуется как условие поиска, проверяющее, не соответст-вует ли проверяемое выражение какому-либо из значений в подзапросе или в списке значе-ний. Если соответствие найдено, то возвращается значение TRUE. NOT INвозвращает отри-цание того, что получилось бы при применении IN, поэтому NOT INвозвратит значение TRUE, если проверяемое выражение не найдено в подзапросе или в списке значений. Исполь-зуется такой синтаксис:

<проверяемое_выражение> IN (<подзапрос>)

или

<проверяемое_выражение> IN (<список_значений>)

Подзапрос – это оператор SELECT, который возвращает в наборе результатов только од-ну колонку. Подзапрос должен быть заключен в скобки.

Список значений – это просто список из нескольких значений, разделенных запятыми и заключенный в скобки. Колонка, получающаяся как набор результатов подзапроса или спи-сок значений, должна иметь такой же тип данных, как и проверяемое выражение. Если необ-ходимо, SQL Server выполнит неявное преобразование типов данных.

В приведенном ниже примере запроса ключевое слово INприменяется в сочетании со списком значений для поиска идентификаторов должностей для трех описаний должностных обязанностей:

SELECT job_id FROM jobs

WHERE job_desc IN ('Operations Manager', 'Marketing Manager', 'Designer')

В этом запросе применен такой список значений: (Operations Manager, Marketing Man-ager, Designer). Запрос будет возвращать идентификаторы должностей из строк, содержащих в колонке job_desc какое-либо значение из списка. Благодаря ключевому слову INваш запрос проще и понятней для восприятия, чем запрос, который получился, если бы вы применили две операции OR, вот такой:

SELECT job_id FROM jobs

WHERE job_desc = 'Operations Manager' OR

job_desc = 'Marketing Manager' OR job_desc = 'Designer'

В следующем запросе ключевое слово INиспользуется в одном операторе дважды – первый раз с подзапросом, а второй раз – со списком значений внутри подзапроса:

SELECT fname, lname -- Внешний запрос FROM employee

WHERE job_id IN (

SELECT job_id -- Внутренний запрос, подзапрос FROM jobs

WHERE job_desc IN

('Operations Manager', 'Marketing Manager', 'Designer') )

Сначала будет выполнен подзапрос (в нашем примере – набор значений job_id). Зна-чения job_idполучаются как результаты подзапроса и не выводятся на экран, они исполь-зуются внешним запросом как выражения для его собственного условия поиска IN. Оконча-

тельный набор результатов будет содержать имена и фамилии всех сотрудников, должности которых называются Operations Manager, Marketing Manager или Designer.

Запрос вернет 11 строк.

EXISTS. Ключевое слово EXISTSиспользуется для проверки существования строк в выводе подзапроса, указанного после него. Используется такой синтаксис:

EXISTS (<подзапрос>)

Если имеется хоть одна строка, удовлетворяющая подзапросу, то возвращается значе-ние TRUE.

Чтобы найти имена авторов, у которых имеются опубликованные книги, можно приме-нить такой запрос:

SELECT au_fname, au_lname FROM authors

WHERE EXISTS (

SELECT au_id

FROM titleauthor

WHERE titleauthor.au_id = authors.au_id )

Авторы, имена которых имеются в таблице authors, но не имеющие опубликованных книг в таблице titleauthor, выбраны не будут. Если в подзапросе не будет найдено ни одной строки, то набор результатов внешнего запроса будет пустым (будет найдено ноль строк).

Предложение GROUP BY

Предложение GROUP BYприменяется после предложения WHEREи означает, что строки набора результатов должны быть сгруппированы в соответствии с данными в колонке груп-пировки. Если в предложении SELECT используется агрегатная функция, то для каждой группы вычисляется и отображается в выводе итоговое агрегатное значение.

Агрегатная функция выполняет вычисления и возвращает значение. (Про агрегатные функции см. раздел "Агрегатные функции" далее.)

Предложение GROUP BYособенно полезно, когда в предложении SELECTимеется агре-гатная функция. Рассмотрим пример оператора SELECT, применяющего предложение GROUP BYдля получения сведений об общем количестве проданных книг для каждого из названий книг:

SELECT title_id, SUM(qty) FROM sales

GROUP BY title_id

Будет выдан результат, содержащий 16 строк: title_id

------------------BU1032 15 BU1111 25 BU2075 35 BU7832 15

…

TC7777 20

Этот запрос не содержит предложения WHERE – оно не нужно. Набор результатов со-стоит из колонки title_id (идентификатор названия книги) и итоговой колонки, не имеющей заголовка. Для каждого отдельного названия книги будет подсчитано общее количество эк-земпляров этой книги, это число будет показано в итоговой колонке. Например, пусть значе-ние BU1032 колонки title_id встретится в таблице sales (продажи) два раза, первый раз оно будет обозначать продажу 5 экземпляров книги (колонка qty будет иметь значение 5), а во второй раз будет обозначать продажу книг по другому заказу, на этот раз будет продано 10

экземпляров книги. Агрегатная функция SUM произведет суммирование этих двух продаж, отсюда и получится, что общее количество проданных экземпляров равно 15, что и будет по-казано в итоговой колонке. Если вы хотите, чтобы итоговая колонка имела заголовок, вос-пользуйтесь ключевым словом AS, как показано на рис. 3.1.

Рис. 3.1

Возможна вложенная ("гнездовая") группировка, при которой в предложении GROUP BY задается более одной колонки. При вложенной группировке набор результатов будет группироваться по каждой из колонок, участвующих в группировке, в том порядке, в кото-ром были заданы колонки. Например, чтобы узнать средние цены книг, сгруппированных сначала по типу, а затем по издательству, можно выполнить запрос, показанный на рис. 3.2.

Рис. 3.2

Обратите внимание, что книги, имеющие тип psychologyи business, попали в ре-зультирующий набор более одного раза, потому что они сгруппированы для разных иденти-фикаторов издательств. Значение NULL, показанное в качестве средней цены книг типа UNDECIDED (нераспределенные), отражает тот факт, что для книг этого типа в таблицу не были введены их цены, поэтому невозможно вычислить среднюю цену.

Предложение GROUP BY часто применяется в сочетании с предложением HAVING, к рассмотрению которого мы переходим.

Предложение HAVING

Предложение HAVINGприменяется, чтобы задать условия поиска для групп или для аг-регатной функции. Предложение HAVINGчаще всего используется после предложения GROUP BY в случаях, когда условие поиска должно проверяться уже после группировки результа-тов. Если условие поиска можно было бы проверить до группировки, то гораздо эффектив-ней было бы поместить его в предложение WHERE, а не пользоваться предложением HAVING (за счет этого уменьшилось бы количество строк, участвующих в группировке).

Предложение HAVINGимеет такой синтаксис:

HAVING <условие_поиска>

Здесь условие_поиска имеет такой же смысл, что и условие поиска в предложении WHERE, рассмотренном выше. Единственным различием между предложениями HAVING и WHEREявляется то, что предложение HAVINGможет содержать агрегатную функцию в усло-вии поиска, а предложение WHERE – нет.

На рис. 3.3 приведен пример запроса, использующего предложение HAVINGдля поиска книг, сгруппированных по типам и по издательствам, средняя цена на которые превышает 15 долларов:

Рис. 3.3

В набор результатов попадают 4 строки, где мы имеем среднюю цену большую, чем $15 по типу и издательствам (см. рис. 3.3).

В предложениях HAVING можно употреблять логические операции. Ниже показан не-сколько измененный последний пример, в нем теперь применяется логическая операция AND:

SELECT FROM GROUP BY HAVING

type, pub_id, AVG(price) AS 'Средняя цена' titles

type, pub_id

AVG(price) >= 15.00 AND AVG(price) <= 20.00

В набор результатов попадут 2 строки:

type pub_id Средняя цена

--------------------------------------------------trad_cook 0877 15.96

business 1389 17.31

Такой же результат получится, если вместо ANDприменить предложение BETWEEN:

SELECT FROM GROUP BY HAVING

type, pub_id, AVG(price) AS "Средняя цена" titles

type, pub_id

AVG(price) BETWEEN 15.00 AND 20.00

Предложение ORDER BY

Предложение ORDER BY применяется, чтобы задать порядок, в котором должны сорти-роваться строки набора результатов. Пользуясь ключевыми словами ASCи DESC, вы можете задать как возрастающий (ascending,от меньших значений к большим), так и убывающий (descending, от больших значений к меньшим) порядок сортировки. Если порядок сорти-ровки не указать, то по умолчанию будет применяться возрастающий порядок сортировки. В предложении ORDER BYможно задать более одной колонки. Результаты будут сортироваться по первой из заданных колонок, но если в первой колонке встретятся строки с одинаковыми значениями, то они будут сортироваться в порядке возрастания значения из второй колонки, и т.д. Ниже будет показано, что такая сортировка особенно полезна при использовании вме-сте с предложением GROUP BY. Рассмотрим сначала пример, в котором предложение ORDER BYработает с одной колонкой и сортирует список авторов по фамилии, в возрастающем по-рядке:

SELECT FROM ORDER BY

au_lname, au_fname authors

au_lname ASC

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

бавьте в предложение ORDER BY имена колонок, разделяя их запятыми. На рис. 3.4 приведен пример запроса, выдающего идентификаторы должностей, фамилии и имена сотрудников, сортирующего вывод сначала по идентификатору должности, затем по фамилии, а затем по имени.

: Рис. 3.4

Запрос возвращает (см. рис. 3.4) 43 строки. Сортировка по именам (не фамилиям) лю-дей в этом запросе не влияет на набор результатов, потому что там нет даже двух людей с одинаковыми фамилиями и идентификаторами должности.

Рассмотрим теперь предложение ORDER BY, работающее совместно с предложением GROUP BYи агрегатной функцией. Добавим сортировку по средней цене в запрос, показан-ный на рис. 3.2. Запрос с сортировкой и его результат приведены на рис. 3.5.

Рис. 3.5

Результат запроса отсортирован в порядке возрастания средней цены книг.

Операция UNION

UNIONсчитается не предложением (clause), а операцией (operator). Она используется для объединения результатов двух или нескольких запросов в один набор результатов. При-меняя UNION, вы должны соблюдать два следующих правила:

все запросы должны иметь одинаковое количество колонок;

типы данных соответственных колонок из запросов должны быть со-вместимыми.

Колонки, перечисленные в операторах SELECT, объединенные при помощи UNION, со-поставляются друг с другом следующим образом: первая колонка из первого оператора SELECT будет соответствовать первым колонкам из всех последующих операторов SELECT, вторая колонка будет соответствовать вторым колонкам из всех последующих операторов SELECT, и т.д. Поэтому во всех операторах SELECT, объединенных при помощи UNION, должно быть одинаковое количество колонок, что гарантирует однозначное сопоставление.

Кроме того, соответственные колонки должны иметь совместимые типы данных. Это значит, что соответственные колонки должны иметь либо одинаковые типы данных, либо SQL Server сможет выполнить неявное преобразование одного типа данных в другой. На рис. 3.6 дан пример применения операции UNION, соединяющей наборы результатов двух опера-торов SELECT, выдающих колонки city и state из обеих таблиц publishers и stores:

Рис. 3.6

Агрегатные функции

Как уже говорилось, агрегатные функции выполняют вычисления над набором значе-ний и возвращают одно значение. Агрегатные функции могут быть заданы в списке выборки и чаще всего применяются в случаях, когда оператор содержит предложение GROUP BY.

Пять наиболее используемых агрегатных функции приведены в таблице 4.3.

Таблица 4.3. Агрегатные функции


Функция	Результат

COUNT	Количество элементов в выражении (равное количеству строк)

SUM	Сумма всех значений выражения

AVG	Среднее арифметическое для значений выражения; null-значения игнорируются

MIN	Минимальное значение из значений выражения
MAX	Максимальное значение из значений выражения

В некоторых из приведенных ранее примеров агрегатные функции уже использовались. Функция COUNT применяется специальным образом: она подсчитывает все строки таб-

лицы. Для этого нужно после COUNTпоместить символ-звездочку в скобках, вот так

SELECT COUNT(*) FROM publishers Результат будет число 8.

Функции AVG, COUNT, MAX, MINи SUMмогут применяться с необязательными ключе-выми словами ALLили DISTINCT. Для каждой из этих функций ALLозначает, что функция должна применяться ко всем значениям выражения, а DISTINCTозначает, что повторяющие-ся значения должны участвовать в расчете только по одному разу. По умолчанию применя-ется опция ALL.

На рис. 3.7. приведен пример, в котором используются сразу две агрегатные функции MAX и MIN, здесь вычисляется разница цен между самой дорогой и самой дешевой книгами.

Рис. 3.7.

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

Рис. 3.8

Отметим, что в Microsoft SQL Server имеется множество разновидностей функций. Ес-ли вам необходимо выполнять какие-либо специфические действия, обратитесь к SQL Server Books Online и посмотрите, какие встроенные функции уже имеются

Дата добавления: 2018-04-15; просмотров: 358; Мы поможем в написании вашей работы!

Поделиться с друзьями:

⇐ Предыдущая 12

Мы поможем в написании ваших работ!