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

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

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

К счастью, есть процедура 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 есть специальный оператор 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 1000 AND 2000

Очередность сортировки будет зависеть от порядка расположения полей в запросе. То есть, в нашем случае сначала данные будут посортированы по колонке 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"

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"

Видим, что в нашу выборку попало за много значений (кроме марта (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 (4, 12, 58, 67)

Оператор IN выполняет ту же функцию, что и OR , однако имеет ряд преимуществ:

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

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

SELECT * FROM Sumproduct WHERE NOT City IN ("Toronto", "Montreal")

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

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

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

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

Однако, в рамках данной статьи я рассмотрю именно второй вариант, так как у него не возникает проблем с ситуациями вроде CASE WHEN NULL (null в рамках БД не является конкретным значением, поэтому его нельзя использовать в switch подобном операторе). Кроме того, в обыденной жизни чаще всего встречаются задачи именно для второго варианта - вычисление через логические выражения. Поэтому лучше сразу научиться и продолжать использовать именно его.

Обычно, он описывается так (синтаксис может меняться в зависимости от БД):

CASE WHEN bool_expression1 THEN value1 ..... WHEN bool_expressionN THEN valueN ELSE valueElse END

bool_expressionX - это логическое условие

valueX - это то значение, которое будет подставлено, если выполнено соответствующее логическое условие

valueElse - это значение, которое будет подставлено, если ранее не выполнилось ни одно условие.

После такой небольшой справки, перейдем к практике.

Примечание : Кстати, стоит знать, что обычно этот оператор можно применять не только в select, но и в любом месте, где можно использовать поля. Например, при join таблиц или же даже фильтрации (having) при группировке (group by) .

Условный оператор CASE...WHEN...THEN

Чтобы лучше понять условный оператор CASE...WHEN...THEN, представим себе небольшую задачу. Допустим, у вас есть таблица с данными о клиентах и их суммарном количестве покупок. И стоит задача динамически формировать скидку. Можно было бы, конечно, вручную выставлять скидку. Но, она у вас пороговая, причем пороги жестко зашиты (что-то вроде - сумма больше 1000 получи скидку в 2%, а больше 5000 - получи 5%) и вы бы хотели этот процесс автоматизировать, чтобы не пришлось искать ошибки и каждый раз копаться в базе данных (накопил клиент нужную сумму - скидка автоматически появилась).

Возьмем условную таблицу client с тремя клиентами. Для примера их будет вполне достаточно.

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

1. Сумма от 1000 - скидка 2%

2. Сумма от 5000 - скидка 5%

3. Сумма от 10000 - скидка 8%

4. Количество заказов от 10 - скидка 7%

5. Количество заказов от 20 - скидка 8%

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

1. 2% - Сумма от 1000 до 4999 и количество заказов меньше 10.

2. 5% - Сумма от 5000 до 9999 и количество заказов меньше 10.

3. 7% - Количество заказов от 10 до 19 и сумма меньше 10000

4. 8% - Количество от 20 или сумма от 10000

Теперь, осталось только записать. Получим следующий sql-запрос

Выводим имя и остальные данные select name, order_count, total_sum, -- А теперь выводим скидку CASE -- Первое правило 2% WHEN c.total_sum >= 1000 and c.total_sum <= 4999 and c.order_count < 10 THEN 2 -- Второе правило 5% WHEN c.total_sum >= 5000 and c.total_sum <= 9999 and c.order_count < 10 THEN 5 -- Третье правило 7% WHEN c.total_sum < 10000 and c.order_count >= 10 and c.order_count <= 19 THEN 5 -- Четвертое правило 8% WHEN c.total_sum >= 10000 or c.order_count >= 20 THEN 5 -- Ни одно правило не выполнено, значит скидка 0. ELSE 0 END as discount from client c

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

Как видите, два клиента получили скидку 8 процентов, а один клиент получил скидку в 2 процента. При этом с каждым заказом процент будет автоматически вычисляться и вам ничего не нужно будет корректировать. К примеру, если у Петя сумма увеличится до 5000, то его скидка автоматически поднимется до 5% (как минимум, так как есть еще число заказов).

mob_info