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 ""%" [email protected]+"%" ")
BEGIN
SELECT " "?" " db, name FROM ?..sysobjects WHERE name LIKE ""%" [email protected]+"%" "
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 ""%" [email protected]+"%" ""

Зверніть увагу на DISTINCT - може стати в нагоді, тому що іноді для одного об'єкта буває кілька записів у syscomments.

Пошук підрядки у всіх рядкових полях усіх таблиць

Такий скрипт чудово допомагає спати спокійно після "чистки" бази перед відправкою клієнту. Звичайно, цією областю його застосування не обмежується.

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

declare @pattern as nvarchar(MAX )
set @pattern = N"% Test%"
set nocount on
declare @sql як nvarchar(MAX )
declare @table as sysname
declarable tables cursor локальний static read_only для 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".

Резюме

Уважний читач, напевно, здогадався з оформлення, що останній приклад написаний не в той же час як і два перші. Якщо точніше, останній запит я написав місяць тому. Це я до того, що на основі такого підходу ви можете самі писати корисні скрипти, які вирішують ваші специфічні завдання.

Якщо такий формат сподобається читачам, іноді викладатиму корисні скрипти з особистої колекції.

Якщо у вас є зауваження, побажання чи нові теми – пишіть у коментарях,

Це явно невідповідно до розпізнавання syntax для SQL Server WHERE clause, так що ви бачите на деяких 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";

У цьому SQL Server WHERE clause example, we"ve used the WHERE clause до файлів наших результатів від employees table. The SELECT statement above would return all rows from the employees table where the first_name is "Jane". Тому що * є використаним в SELECT, всі поля від employees table would appear в result set.

Example - Using AND condition

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

SELECT * FROM employees WHERE last_name = "Anderson" AND employee_id >= 3000;

Цей SQL Server WHERE clause example використовується для WHERE clause для визначення різних умов. У цьому випадку, цей SELECT statement використовує AND умову до відновлення 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";

Цей SQL Server WHERE clause example use the WHERE clause to define multiple conditions, але instead of using the AND condition , it uses the OR condition . У цьому випадку, цей 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);

Цей SQL Server WHERE clause example use the WHERE clause до Definition multiple conditions, but it combines the AND condition and the OR condition . Цей 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.

parentheses determine the order that the AND and OR умовами є встановлені. 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";

Цей SQL Server WHERE clause example використовується для WHERE clause до join multiple tables together in a single SELECT statement. Цей 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від employees table and the contact_idвід 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 IDIN (4, 12, 58, 67)

Оператор INвиконує ту ж функцію, що і OR, однак має низку переваг:

• При роботі з довгими списками, пропозиція з INлегше читати;
• Використовується менша кількість операторів, що прискорює обробку запиту;
• Найважливіша перевага INу тому, що у його конструкції можна використовувати додаткову конструкцію SELECT, що
відкриває великі можливості створення складних підзапитів.

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

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

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

У рамках цієї статті я розповім вам про умовного оператора CASE, без якого облік даних у ряді завдань перетворився б на нагромадження шматків коду, а також про те, як його застосовувати в SQL-запитах.

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

У принципі, цей оператор має, як мінімум, дві форми в різних базах даних. Перший варіант нагадує звичайний switch із будь-якої мови програмування. Другий залежить від логічних виразів.

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

Зазвичай він описується так (синтаксис може змінюватися в залежності від БД):

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 і 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% (як мінімум, оскільки є ще кількість замовлень).