BattleCaseкомпьютерная помощь

When sql синтаксис. SQL Where: способы применения и примеры

В большинстве случаев необходимо получать не все записи, а только те, которые соответствуют определенным критериям. Поэтому для осуществления фильтрации выборки в SQL

When sql синтаксис. SQL Where: способы применения и примеры
Содержание
  1. 1. Простое фильтрование оператором WHERE
  2. 2. Фильтрация по диапазону значений ( BETWEEN )
  3. 3. Выборка пустых записей ( IS NULL )
  4. 4. Расширенное фильтрации ( AND, OR )
  5. 5. Расширенная фильтрация ( оператор IN )
  6. Поиск объектов во всех базах данных сервера
  7. Поиск подстроки в текстах хранимых процедур
  8. Поиск подстроки во всех строковых полях всех таблиц
  9. Резюме
  10. Example - Using AND condition
  11. Example - Joining Tables
  12. Способы применения условия Where
  13. Использование Where для задания параметров выборки
  14. Использование конструкции для проверки вхождения
  15. Примеры использования Where
  16. Оператор CASE

Есть специальный оператор WHERE

1. Простое фильтрование оператором WHERE

Давайте из нашей таблицы, например, отберем записи, относящиеся только к определенному товару. Для этого мы укажем дополнительный параметр отбора, который будет фильтровать значение по колонке Product

Пример запроса для отбора текстовых значений

SELECT

* FROM

Sumproduct WHERE

Product

= "Bikes"

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

Пример запроса для отбора числовых значений

SELECT

> 40000

ORDER BY

Amount

В этом примере мы отобрали записи, в которых выручка от реализации составила более 40 тыс. $

И, дополнительно, все записи посортировали по возрастанию по полю Amount.

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

2. Фильтрация по диапазону значений ( BETWEEN )

Для отбора данных, которые лежат в определенном диапазоне, используется оператор BETWEEN

. В следующем запросе будут отобраны все значения, лежащие в пределах от 1000 $

В 2000 $

Включительно, в поле Amount

SELECT

* FROM

Sumproduct WHERE

Amount

BETWEEN

AND

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

, а затем по City

3. Выборка пустых записей ( IS NULL )

В SQL

Существует специальный оператор для выборки пустых записей (називаеьбся NULL

). Пустой записью считается любая ячейка в таблице, в которую не введены какие-либо символы. Если в ячейку введен 0

Или пробел

, то считается, что поле заполнено.

SELECT

* FROM

Sumproduct WHERE

Amount

IS NULL

В примере выше, мы нарочно удалили два значения в поле Amount

, чтобы продемонстрировать работу оператора NULL

4. Расширенное фильтрации ( AND, OR )

Язык SQL

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

Есть дополнительные операторы, которые расширяют возможности оператора WHERE

. Такими операторами являются: AND, OR, IN, NOT

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

SELECT

* FROM

Sumproduct WHERE

Amount

> 40000

AND

City

= "Toronto"

When sql синтаксис. SQL Where: способы применения и примеры

SELECT

* FROM

Sumproduct WHERE

Month

= "April"

OR

Month

= "March"

Давайте объединим операторы AND

И OR

. Для этого сделаем выборку велосипедов ( Bikes

) и коньков ( Skates

), которые были проданы в марте ( March

SELECT

* FROM

Sumproduct WHERE

Product

= "Bikes"

OR

Product

= "Skates"

AND

Month

= "March"

When sql синтаксис. SQL Where: способы применения и примеры

Видим, что в нашу выборку попало за много значений (кроме марта ( March

), также январь ( January

), февраль ( February

) и апрель ( April

)). В чем же причина? А в том, что SQL

Имеет приоритеты выполнения команд. То есть оператор AND

Имеет более высокий приоритет, чем оператор OR

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

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

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

SELECT

* FROM

Sumproduct WHERE

(Product

= "Bikes"

OR

Product

= "Skates"

AND

Month

= "March"

5. Расширенная фильтрация ( оператор IN )

SELECT

* FROM

Sumproduct WHERE

ID

IN

When sql синтаксис. SQL Where: способы применения и примеры

Оператор IN

Выполняет ту же функцию, что и OR

, однако имеет ряд преимуществ:

  • При работе с длинными списками, предложение с IN легче читать;
  • Используется меньшее количество операторов, что ускоряет обработку запроса;
  • Самое важное преимущество IN в том, что в его конструкции можно использовать дополнительную конструкцию SELECT , что

6. Расширенная фильтрация ( оператор NOT )

SELECT

* FROM

Sumproduct WHERE

NOT City

IN

("Toronto", "Montreal")

Ключевое слово NOT

Позволяет убрать ненужные значения из выборки. Также его особенностью является то, что оно проставляется перед названием столбца, участвующего в фильтровании, а не после.

Поиск объектов во всех базах данных сервера

У вас бывало, что нужно найти таблицу чтобы посмотреть данные или структуру, а вы не помните точно в какой БД она находится? У меня, периодически, такое случается. Пример - найти таблицы из проекта пятилетней давности, чтобы посмотреть как тогда решал текущую задачу.

К счастью, есть процедура sp_MSforeachdb

, которая позволяет написать такой скрипт довольно компактно:

DECLARE

@name AS

SYSNAME,

@strSQL AS

VARCHAR

(MAX

SET

@name = "Hierarchy"

SET

@strSQL = "

IF EXISTS(SELECT * FROM ?..sysobjects WHERE name LIKE "

"%"

+@name+"%"

")

BEGIN

SELECT "

"?"

" db, name FROM ?..sysobjects WHERE name LIKE "

"%"

+@name+"%"

"

END

"

CREATE

TABLE

#result

db SYSNAME,

name SYSNAME,

PRIMARY

KEY

(db,name))

INSERT #result

EXEC

sp_MSforeachdb @strSQL

SELECT

* FROM

#result

DROP

TABLE

#result

Здесь всё достаточно просто:

  1. Задаём часть названия, по которому будем искать (в данном случае - Hierarchy).
  2. Собираем динамический SQL, который ищет по sysobjects (для некоторых старых версий MS SQL можно вместо VARCHAR(MAX) использовать VARCHAR(2000), например). При этом символ "?" заменится на название БД.
  3. С помощью конструкции INSERT ... EXEC сохраняем все результаты во временную таблицу, чтобы отобразить, в итоге, единый результат.

Поиск подстроки в текстах хранимых процедур

Как вы знаете, тексты хранимых процедур, триггеров и представлений можно найти в таблице syscomments

Чтобы наш предыдущий запрос позволил нам найти упоминание в syscomments некоторой подстроки, его можно переписать следующим образом:

SET

@strSQL = "

SELECT DISTINCT "

"?"

" as db, o.name FROM [?].dbo.syscomments s

JOIN [?].dbo.sysobjects o ON o.id = s.id WHERE s. LIKE "

"%"

+@name+"%"

""

Обратите внимание на DISTINCT - может пригодиться, потому что иногда для одного объекта бывает несколько записей в syscomments.

Поиск подстроки во всех строковых полях всех таблиц

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

Для этого случая помощник в виде процедуры от MS не нашёлся, поэтому пришлось использовать курсоры:

declare

@pattern as

nvarchar(MAX

set

@pattern = N"%Test%"

set

nocount on

declare

@sql

as

nvarchar(MAX

declare

@table

as

sysname

declare

tables cursor

local

static

read_only for

select

name from

sys.tables t where

t.is_ms_shipped = 0

open

tables

create

table

#results(name sysname not

null

, value

nvarchar(MAX

) not

null

while

@@FETCH_STATUS

= 0

begin

set

@sql

= ""

select

@sql

= @sql

+ "

insert into #results select "

""

+ @table

+ "."

+ name + ""

" as name, ["

+ name + "] from ["

+ @table

+ "] where ["

+ name + "] like "

""

+ @pattern + ""

""

from

sys.columns c where

c.object_id = OBJECT_ID(@table

) and

c.system_type_id in

(select

system_type_id from

sys.types where

collation_name is

not

null

exec

(@sql

fetch

next

from

tables into

@table

end

select

* from

#results

close

tables

drop

table

#results

Помимо курсоров принципы те же. Есть пара нюансов:

  • Ищем таблицы без флага is_ms_shipped, чтобы просматривать только таблицы, созданные на этапе разработки.
  • Для отбора строковых столбцов вместо списка типов (который поддерживать утомительно) используется фильтр "collation_name is not null".

Резюме

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

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

It is difficult to explain the syntax for the SQL Server WHERE clause, so let"s look at some examples.

We"ll start by looking at how to use the WHERE clause with only a single condition.

SELECT *

FROM employees

WHERE first_name = "Jane";

In this SQL Server WHERE clause example, we"ve used the WHERE clause to filter our results from the employees

table. The SELECT statement above would return all rows from the employees

table where the first_name

is "Jane". Because the * is used in the SELECT, all fields from the employees

table would appear in the result set.

Example - Using AND condition

Let"s look at how to use the WHERE clause with the AND condition.

SELECT *

FROM employees

WHERE last_name = "Anderson"

AND employee_id >= 3000;

This SQL Server WHERE clause example uses the WHERE clause to define multiple conditions. In this case, this SELECT statement uses the AND condition to return all employees

that have a last_name

of "Anderson" and the employee_id

is greater than or equal to 3000.

Example - Using OR condition

Let"s look at how to use the WHERE clause with the OR condition.

SELECT employee_id, last_name, first_name

FROM employees

WHERE last_name = "Johnson"

OR first_name = "Danielle";

This SQL Server WHERE clause example uses the WHERE clause to define multiple conditions, but instead of using the AND condition , it uses the OR condition . In this case, this SELECT statement would return all employee_id

, last_name

, and first_name

values from the employees

table where the last_name

is "Johnson" or the first_name

is "Danielle".

Example - Combining AND & OR conditions

Let"s look at how to use the WHERE clause when we combine the AND & OR conditions in a single SQL statement.

SELECT *

FROM employees

WHERE (state = "California" AND last_name = "Smith")

OR (employee_id = 82);

This SQL Server WHERE clause example uses the WHERE clause to define multiple conditions, but it combines the AND condition and the OR condition . This example would return all employees

that reside in the state

of "California" and whose last_name

is "Smith" as well as all employees whose employee_id

is equal to 82.

The parentheses determine the order that the AND and OR conditions are evaluated. Just like you learned in the order of operations in Math class!

Example - Joining Tables

Let"s look at how to use the WHERE clause when we join multiple tables together.

SELECT employees.employee_id, contacts.last_name

FROM employees

INNER JOIN contacts

ON employees.employee_id = contacts.contact_id

WHERE employees.first_name = "Sarah";

This SQL Server WHERE clause example uses the WHERE clause to join multiple tables together in a single SELECT statement. This SELECT statement would return all rows where the first_name

in the employees

table is "Sarah". And the employee

s and contacts

tables are joined on the employee_id

from the employees

table and the contact_id

from the contacts

table.

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

Для выбора данных с базы используется конструкция Select [набор данных] from [имя таблицы]. Как показывает опыт, в 80 % случаев использования запросов на выборку данных нужно применять разнообразные условия - параметры. Для этого в язык, как дополнение к запросу, его усложнение, введено условие SQL-Where.

Способы применения условия Where

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

Конструкция SQL-Where может быть использована для задания условий выборки данных или для проверки вхождения данных в выборку или стороннюю таблицу.

Использование Where для задания параметров выборки

Если нужно задать определенные параметры для выбора с базы отчетных данных, синтаксис конструкции SQL-Where можно организовать довольно просто. Для этого можно пользоваться следующими правилами:

Такого рода запросы довольно просты в построении и не вызывают трудностей даже у новичков.

Использование конструкции для проверки вхождения

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

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

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

Примеры использования Where

Сейчас будут приведены примеры Для начал представим, что есть две таблицы с данными - Tovar и TovarAmount. В первой имеются названия продаваемого товара, цена, дата продажи и клиент, который приобрел товар. Во второй указано наличие товара, а если точнее, какой есть в наличии.

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

Select * from Tovar

Where T_Date >= '12/01/2016' and T_Date<= ''12/07/1016 and T_PriceOut >0

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

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

Select * from Tovar

Where T_Tovar in (select TA_Tovar where TA_ Amount >0)

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

В реализациях языка SQL может быть выполнено неявное преобразование типов.

Так, например, в T-SQL при сравнении или комбинировании значений типов smallint

И int

Данные типа smallint

Неявно преобразуются к типу int

. Подробно о явном и неявном преобразовании типов в MS SQL Server можно прочитать в BOL.

Пример.

Вывести среднюю цену ПК-блокнотов с предваряющим текстом "средняя цена = ".

Попытка выполнить запрос

В результате получим то, что требовалось:

Даст результат 1926. В принципе все правильно, т.к. Мы получили в результате то, что просили - ГОД. Однако среднее арифметическое будет составлять примерно 1926,2381. Тут агрегатные функции (за исключением функции COUNT

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

А если нас интересует результат с заданной точностью, скажем, до двух десятичных знаков? Применение выражения CAST

К среднему значению ничего не даст по указанной выше причине. Действительно,

Результат - 1926.238095. Опять не то. Причина состоит в том, что при вычислении среднего значения было выполнено неявное преобразование типа. Сделаем еще один шаг:

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

Аналогичные преобразования типа можно выполнить с помощью функции CONVERT

Здесь мы преобразуем строковое представление даты к типу datetime

, после чего выполняем обратное преобразование, чтобы продемонстрировать результат форматирования. Поскольку значение аргумента стиль не задано, используется значение по умолчанию (0 или 100). В результате получим

Перечень всех возможных значений аргумента стиль

Можно посмотреть в BOL.

Оператор CASE

Пусть требуется вывести список всех моделей ПК с указанием их цены. При этом если модель отсутствует в продаже (нет в таблице РС), то вместо цены вывести текст: "Нет в наличии".

Список всех моделей ПК с ценами можно получить с помощью запроса:

SELECT DISTINCT product.model, price FROM product LEFT JOIN pc c

ON product.model=c.model

WHERE product.type="pc";

В результирующем наборе отсутствующая цена будет заменена NULL-значением:

modelprice
1121850
1232350
1232400
1232600
1233600
1233950
1233980
1260350
2111NULL
2112NULL

Чтобы заменить NULL-значения нужным текстом, можно воспользоваться оператором CASE

Оператор CASE

В зависимости от указанных условий возвращает одно из множества

Возможных значений. В нашем примере условием является проверка на NULL.

Если это условие выполняется, то возвращается текст "Нет в наличии", в противном

Случае ( ELSE

) возвращается значение цены.

Здесь есть один принципиальный момент. Поскольку результатом оператора SELECT

Всегда является таблица, то все значения любого столбца должны иметь один и тот

Же тип данных (с учетом неявного приведения типов). Поэтому мы не можем наряду

С ценой (числовой тип) выводить символьную константу. Вот почему к полю price применяется преобразование типов, чтобы привести его значения к символьному представлению. В результате получим

modelprice
1121850
1232350
1232400
1232600
1233600
1233950
1233980
1260350
2111Нет в наличии
2112Нет в наличии

Оператор CASE

Может быть использован в одной из двух синтаксических форм записи:

1-я форма

CASE <проверяемое выражение>

WHEN <сравниваемое выражение 1>

THEN <возвращаемое значение 1>

WHEN <сравниваемое выражение N>

THEN <возвращаемое значение N>

END

2-я форма

CASE

WHEN <предикат 1>

THEN <возвращаемое значение 1>

WHEN <предикат N>

THEN <возвращаемое значение N>

END

Все предложения WHEN

Должны иметь одинаковую синтаксическую форму, т.е. Нельзя смешивать первую и вторую формы.

При использовании первой синтаксической формы условие WHEN

Удовлетворяется,

Как только значение проверяемого выражения

Станет равным значению выражения,

Указанного в предложении WHEN

. При использовании второй синтаксической формы условие WHEN

Удовлетворяется, как только предикат

Принимает значение TRUE.

При удовлетворении условия оператор CASE

Возвращает значение, указанное в соответствующем предложении THEN

Если ни одно из условий WHEN

Не выполнилось, то будет использовано значение, указанное в предложении ELSE

При отсутствии ELSE

, будет возвращено NULL-значение. Если удовлетворены несколько условий, то будет возвращено

Значение предложения THEN

Первого из них.

В приведенном выше примере была использована вторая форма оператора CASE

Заметим, что для проверки на NULL стандарт предлагает более короткую форму оператора

- COALESCE

. Этот оператор имеет произвольное число параметров и возвращает

Значение первого, отличного от NULL. Для двух параметров оператор COALESCE(A, B)

Эквивалентен следующему оператору CASE

CASE WHEN A IS NOT NULL THEN A ELSE B END

Решение рассмотренного выше примера при использовании оператора COALESCE

Можно переписать следующим образом:

Использование первой синтаксической формы оператора CASE

Можно

Продемонстрировать на следующем примере

: Вывести все имеющиеся модели ПК с указанием цены.

Отметить самые дорогие и самые дешевые модели.

SELECT DISTINCT model, price,

CASE price WHEN (SELECT MAX(price) FROM pc) THEN "Самый дорогой"

WHEN (SELECT MIN(price) FROM pc) THEN "Самый дешевый"

ELSE "Средняя цена" END comment

FROM pc ORDER BY price;

В результате выполнения запроса получим

modelpricecomment
1232350Самый дешевый
1260350Самый дешевый
1232400Средняя цена
1233400Средняя цена
1233600Средняя цена
1121850Средняя цена
1233950Средняя цена
1233980Самый дорогой

Рубрика: Новичкам и программирование

Похожие статьи