SQLite Запит: Виберіть, Де, ОБМЕЖЕННЯ, ЗМІЩЕННЯ, Підрахунок, Групування за

У розділах нижче описано читання даних за допомогою SELECT та FROM, фільтрацію рядків за допомогою WHERE та його операторів, упорядкування та обмеження результатів, агрегатні функції, GROUP BY та HAVING, підзапити, операції з множинами, обробку NULL, вирази CASE та поширені табличні вирази.

Писати SQL запитів в SQLite бази даних, ви повинні знати, як працюють пропозиції SELECT, FROM, WHERE, GROUP BY, ORDER BY і LIMIT і як ними користуватися.

Під час цього підручника ви навчитеся використовувати ці речення та писати SQLite статті.

Читання даних за допомогою Select

Речення SELECT є основним оператором, який ви використовуєте для запиту SQLite бази даних. У реченні SELECT ви вказуєте, що вибрати. Але перед реченням select давайте подивимося, звідки ми можемо вибрати дані за допомогою речення FROM.

Речення FROM використовується для вказівки місця вибору даних. У реченні from ви можете вказати одну або кілька таблиць або підзапитів для вибору даних, як ми побачимо пізніше в навчальних посібниках.

Зауважте, що для всіх наведених нижче прикладів вам потрібно запустити sqlite3.exe і відкрити підключення до зразка бази даних як поточне:

Крок 1) На цьому етапі

Відкрийте «Мій комп’ютер» і перейдіть до наступного каталогу «C:\sqlite», а потім відкрийте «sqlite3.exe»:

Крок 2) Відкрийте базу даних “TutorialsSampleDB.db” за допомогою такої команди:

Тепер ви готові виконувати будь-який тип запиту до бази даних.

У реченні SELECT ви можете вибрати не лише назву стовпця, але й багато інших параметрів, щоб вказати, що вибрати. Як наступне:

SELECT *

Ця команда вибере всі стовпці з усіх таблиць (або підзапитів), на які посилаються, у реченні FROM. Наприклад:

SELECT *
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Це вибере всі стовпці з таблиць студентів і таблиць кафедр:

ВИБРАТИ назву таблиці.*

Це вибере всі стовпці лише з таблиці “tablename”. Наприклад:

SELECT Students.*
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Це вибере всі стовпці лише з таблиці учнів:

Літеральне значення

Літеральне значення — це постійне значення, яке можна вказати в операторі select. Літеральні значення можна використовувати так само, як імена стовпців у реченні SELECT. Ці літеральні значення відображатимуться для кожного рядка з рядків, повернутих запитом SQL.

Ось кілька прикладів різних літеральних значень, які можна вибрати:

• Числовий літерал – числа в будь-якому форматі, наприклад 1, 2.55 тощо.
• Рядкові літерали – будь-який рядок «США», «це зразок тексту» тощо.
• NULL – значення NULL.
• Current_TIME – дасть вам поточний час.
• CURRENT_DATE – це дасть вам поточну дату.

Це може бути зручно в деяких ситуаціях, коли вам потрібно вибрати постійне значення для всіх повернутих рядків. Наприклад, якщо ви хочете вибрати всіх студентів із таблиці «Студенти» з новим стовпцем під назвою «країна», який містить значення «США», ви можете зробити це:

SELECT *, 'USA' AS Country FROM Students;

Це дасть вам усі стовпці студентів, а також новий стовпець «Країна», як це:

Зауважте, що цей новий стовпець Країна насправді не є новим стовпцем, доданим до таблиці. Це віртуальний стовпець, створений у запиті для відображення результатів, і він не буде створений у таблиці.

Імена та псевдоніми

Псевдонім — це нова назва для стовпця, яка дає змогу вибрати стовпець із новою назвою. Псевдоніми стовпців задаються за допомогою ключового слова «AS».

Наприклад, якщо ви хочете, щоб стовпець StudentName повертався з «Student Name» замість «StudentName», ви можете дати йому такий псевдонім:

SELECT StudentName AS 'Student Name' FROM Students;

Це дасть вам імена студентів з назвою «Ім’я студента» замість «Ім’я студента» ось так:

Зверніть увагу, що назва стовпця все ще «StudentName»; стовпець StudentName залишається тим самим, він не змінюється за псевдонімом.

Псевдонім не змінить назву стовпця; це просто змінить відображуване ім’я в реченні SELECT.

Також зауважте, що ключове слово «AS» необов’язкове, ви можете вказати псевдонім без нього, приблизно так:

SELECT StudentName 'Student Name' FROM Students;

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

Ви також можете давати псевдоніми таблицям, а не лише стовпцям. З тим самим ключовим словом «AS». Наприклад, ви можете зробити це:

SELECT s.* FROM Students AS s;

Це дасть вам усі стовпці в таблиці Студенти:

Це може бути дуже корисним, якщо ви приєднуєтеся до кількох таблиць; замість повторення повного імені таблиці в запиті, ви можете дати кожній таблиці короткий псевдонім. Наприклад, у такому запиті:

SELECT Students.StudentName, Departments.DepartmentName
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Цей запит вибере кожне ім’я студента з таблиці «Студенти» з назвою його факультету з таблиці «Кафедри»:

Однак той самий запит можна записати так:

SELECT s.StudentName, d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Ми надали таблиці Students псевдонім «s», а таблиці departments – псевдонім «d». Потім замість повного імені таблиці ми використали їхні псевдоніми для посилання на них. INNER JOIN об’єднує дві або більше таблиць разом за допомогою умови. У нашому прикладі ми об’єднали таблицю Students з таблицею Departments зі стовпцем DepartmentId. Також є детальне пояснення INNER JOIN у розділі «SQLite з'єднання«підручник».

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

ДЕ

Написання SQL-запитів за допомогою лише пропозиції SELECT і пропозиції FROM, як ми бачили в попередньому розділі, дасть вам усі рядки з таблиць. Однак, якщо ви хочете відфільтрувати повернуті дані, вам потрібно додати пропозицію «WHERE».

Речення WHERE використовується для фільтрації набору результатів, що повертаються SQL-запитом. Ось як працює речення WHERE:

• У реченні WHERE ви можете вказати «вираз».
• Цей вираз буде обчислено для кожного рядка, що повертається з таблиць(-й), указаних у реченні FROM.
• Вираз буде обчислено як логічний вираз із результатом істини, хибності або нуля.
• Тоді будуть повернені лише ті рядки, для яких вираз було обчислено з істинним значенням, а ті з помилковими або нульовими результатами будуть проігноровані та не включені до набору результатів.

Щоб відфільтрувати набір результатів за допомогою речення WHERE, вам потрібно використовувати вирази та оператори.

Список операторів в SQLite і як ними користуватися

У наступному розділі ми пояснимо, як можна фільтрувати за допомогою виразів і операторів.

Вираз — це одне або кілька літеральних значень або стовпців, поєднаних один з одним за допомогою оператора.

Зауважте, що ви можете використовувати вирази як у реченні SELECT, так і в реченні WHERE.

У наступних прикладах ми спробуємо використати вирази та оператори як у реченні select, так і в реченні WHERE. Щоб показати вам, як вони працюють.

Існують різні типи виразів і операторів, які можна вказати наступним чином:

SQLite оператор конкатенації “||”

Цей оператор використовується для об’єднання одного або кількох літеральних значень або стовпців один з одним. Він створить один рядок результатів із усіх об’єднаних літеральних значень або стовпців. Наприклад:

SELECT 'Id with Name: '|| StudentId || StudentName AS StudentIdWithName
FROM Students;

Це об'єднається в новий псевдонім «StudentIdWithName»:

• Літеральне рядкове значення «Ідентифікатор з назвою:»
• зі значенням стовпця «StudentId» та
• зі значенням зі стовпця «Ім'я студента»

SQLite Оператор CAST:

Оператор CAST використовується для перетворення значення з тип даних до іншого типу даних.

Наприклад, якщо у вас є числове значення, збережене як рядкове значення, ось так: «'12.5'», і ви хочете перетворити його на числове значення, ви можете використати оператор CAST, щоб зробити це, ось так: «CAST('12.5' ​​AS REAL)“. Або якщо у вас є десяткове значення, таке як 12.5, і вам потрібно отримати лише цілу частину, ви можете перетворити його на ціле число, ось так: «CAST(12.5 AS INTEGER)».

Приклад

У наступній команді ми спробуємо перетворити різні значення в інші типи даних:

SELECT CAST('12.5' AS REAL) ToReal, CAST(12.5 AS INTEGER) AS ToInteger;

Це дасть вам:

Результат такий:

• CAST('12.5' ​​AS REAL) – значення '12.5' ​​є рядковим значенням, воно буде перетворено на значення REAL.
• CAST(12.5 AS INTEGER) – значення 12.5 є десятковим значенням, яке буде перетворено на ціле число. Десяткова частина буде скорочена, і вона стане 12.

SQLite Арифметика Operaторс:

Візьміть два або більше числових літеральних значень або числових стовпців і поверніть одне числове значення. Арифметичні оператори, які підтримуються в SQLite є:

• Додавання «+» – видає суму двох операндів.
• нижчеtracція “–” – підпунктtracобробляє два операнди та отримує різницю.
• Множення «*» – добуток двох операндів.
• Нагадування (по модулю) «%» – дає залишок від ділення одного операнда на другий операнд.
• Ділення “/” – повертає частку, отриману в результаті ділення лівого операнда на правий операнд.

приклад:

У наступному прикладі ми спробуємо п'ять арифметичних операторів з літеральними числовими значеннями в одному реченні select:

SELECT 25+6, 25-6, 25*6, 25%6, 25/6;

Це дасть вам:

Зверніть увагу, як тут ми використали оператор SELECT без пропозиції FROM. І це дозволено SQLite поки ми вибираємо літеральні значення.

SQLite Оператори порівняння

Порівняйте два операнди один з одним і поверніть значення true або false наступним чином:

• «<» – повертає значення true, якщо лівий операнд менший за правий операнд.
• «<=» – повертає значення true, якщо лівий операнд менший або дорівнює правому операнду.
• «>» – повертає значення true, якщо лівий операнд більший за правий операнд.
• «>=" – повертає значення true, якщо лівий операнд більший або дорівнює правому операнду.
• «=» та «==» – повертає true, якщо два операнди рівні. Зверніть увагу, що обидва оператори однакові, і між ними немає різниці.
• «!=" та «<>» – повертає true, якщо два операнди не рівні. Зверніть увагу, що обидва оператори однакові, і між ними немає різниці.

Зауважте, що, SQLite виражає істинне значення 1, а хибне значення 0.

приклад:

SELECT
  10<6 AS '<', 10<=6 AS '<=',
  10>6 AS '>', 10>=6 AS '>=',
  10=6 AS '=', 10==6 AS '==',
  10!=6 AS '!=', 10<>6 AS '<>';

Це дасть щось на зразок цього:

SQLite Оператори відповідності шаблону

«LIKE» – використовується для зіставлення зі зразком. Використовуючи «Like», можна шукати значення, що відповідають шаблону, заданому за допомогою підстановочного знака.

Операнд ліворуч може бути або значенням рядкового літералу, або стовпцем рядка. Шаблон можна вказати наступним чином:

• Містить шаблон. Наприклад, StudentName LIKE '%a%' – це шукатиме імена студентів, які містять літеру «a» в будь-якій позиції стовпця StudentName.
• Починається зі шаблону. Наприклад, «Ім'яУчня LIKE 'a%'» – пошук імен учнів, які починаються на літеру «a».
• Закінчується шаблоном. Наприклад, «Ім'яУчня LIKE '%a'» – Пошук імен учнів, які закінчуються на літеру «a».
• Зіставлення будь-якого окремого символу в рядку за допомогою літери підкреслення «_». Наприклад, «Ім'яУчня LIKE 'J___'» – Пошук імен учнів довжиною 4 символи. Імена повинні починатися з літери «J» і можуть містити будь-які три інші символи після літери «J».

Приклади зіставлення шаблонів:

Отримайте імена учнів, які починаються з літери «j»:

SELECT StudentName FROM Students WHERE StudentName LIKE 'j%';

Результат:

Отримайте імена студентів, що закінчуються літерою «y»:

SELECT StudentName FROM Students WHERE StudentName LIKE '%y';

Результат:

Отримати імена учнів, які містять літеру «n»:

SELECT StudentName FROM Students WHERE StudentName LIKE '%n%';

Результат:

«GLOB» – еквівалент оператора LIKE, але GLOB чутливий до регістру, на відміну від оператора LIKE. Наприклад, наступні дві команди повернуть різні результати:

SELECT 'Jack' GLOB 'j%';
SELECT 'Jack' LIKE 'j%';

Це дасть вам:

Перший оператор повертає 0(false), оскільки оператор GLOB чутливий до регістру, тому «j» не дорівнює «J». Однак другий оператор поверне 1 (істина), оскільки оператор LIKE не враховує регістр, тому «j» дорівнює «J».

Інші оператори:

SQLite І

Логічний оператор, який поєднує один або кілька виразів. Він поверне істину, лише якщо всі вирази дають «істинне» значення. Однак він поверне false, лише якщо всі вирази дають значення «false».

приклад:

Наступний запит шукатиме студентів, які мають StudentId > 5 і StudentName починається з літери N, повернуті студенти мають відповідати двом умовам:

SELECT *
FROM Students
WHERE (StudentId > 5) AND (StudentName LIKE 'N%');

У результаті на наведеному вище скріншоті ви отримаєте лише «Ненсі». Ненсі — єдина студентка, яка відповідає обом умовам.

SQLite OR

Логічний оператор, який поєднує один або кілька виразів, так що якщо один із комбінованих операторів дає значення true, він повертає значення true. Однак, якщо всі вирази дають false, він поверне false.

приклад:

Наступний запит шукатиме студентів, які мають StudentId > 5 або StudentName починається з літери N, повернуті студенти мають відповідати принаймні одній із умов:

SELECT *
FROM Students
WHERE (StudentId > 5) OR (StudentName LIKE 'N%');

Це дасть вам:

Як результат, на наведеному вище скріншоті це дасть вам ім’я студента з літерою «n» у його імені та ідентифікатор студента зі значенням>5.

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

SQLite МІЖ

BETWEEN використовується для вибору значень, які знаходяться в діапазоні від двох значень. Наприклад, «X BETWEEN Y AND Z» поверне значення true (1), якщо значення X знаходиться між двома значеннями Y та Z. В іншому випадку повернеться значення false (0). «X BETWEEN Y AND Z» еквівалентно «X >= Y AND X <= Z», X має бути більше або дорівнювати Y, а X менше або дорівнювати Z.

приклад:

У наступному прикладі запиту ми напишемо запит, щоб отримати студентів зі значенням Id від 5 до 8:

SELECT *
FROM Students
WHERE StudentId BETWEEN 5 AND 8;

Це дасть лише студентам з ідентифікаторами 5, 6, 7 і 8:

SQLite IN

Приймає один операнд і список операндів. Він поверне значення true, якщо перше значення операнда дорівнює одному зі значень операндів зі списку. Оператор IN повертає істину (1), якщо список операндів містить перше значення операнда в своїх значеннях. В іншому випадку він поверне false (0).

Ось так: «col IN(x, y, z)». Це еквівалентно «(col=x) або (col=y) або (col=z)».

приклад:

Наступний запит вибере лише студентів з ідентифікаторами 2, 4, 6, 8:

SELECT *
FROM Students
WHERE StudentId IN(2, 4, 6, 8);

Подобається це:

Попередній запит дасть точний результат, як наступний запит, оскільки вони еквівалентні:

SELECT *
FROM Students
WHERE (StudentId = 2) OR (StudentId =  4) OR (StudentId =  6) OR (StudentId = 8);

Обидва запити дають точні результати. Однак різниця між двома запитами полягає в тому, що в першому запиті ми використовували оператор «IN». У другому запиті ми використали кілька операторів «АБО».

Оператор IN еквівалентний використанню кількох операторів OR. Оператор «WHERE StudentId IN(2, 4, 6, 8)» еквівалентний оператору «WHERE (StudentId = 2) OR (StudentId = 4) OR (StudentId = 6) OR (StudentId = 8);»

Подобається це:

SQLite НЕ В

Операнд «NOT IN» є протилежністю оператора IN. Але з тим самим синтаксисом; він приймає один операнд та список операндів. Він поверне true, якщо значення першого операнда не дорівнює значенням одного зі значень операндів зі списку, тобто він поверне true (0), якщо список операндів не містить першого операнда. Ось так: «col NOT IN(x, y, z)». Це еквівалентно «(col<>x) AND (col<>y) AND (col<>z)».

приклад:

Наступний запит вибере студентів з ідентифікаторами, що не дорівнюють одному з цих ідентифікаторів 2, 4, 6, 8:

SELECT *
FROM Students
WHERE StudentId NOT IN(2, 4, 6, 8);

Так

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

SELECT *
FROM Students
WHERE (StudentId <> 2) AND (StudentId <> 4) AND (StudentId <> 6) AND (StudentId <> 8);

Подобається це:

На наведеному вище знімку екрана

Ми використали кілька нерівних операторів “<>”, щоб отримати список студентів, які не дорівнюють жодному з наступних ідентифікаторів 2, 4, 6 або 8. Цей запит поверне всіх інших студентів, крім цього списку ідентифікаторів.

SQLite ІСНУЄТЬСЯ

Оператори EXISTS не приймають жодних операндів; він приймає лише речення SELECT після нього. Оператор EXISTS поверне значення true (1), якщо є будь-які рядки, повернені з пропозиції SELECT, і поверне значення false (0), якщо взагалі немає рядків, повернутих з пропозиції SELECT.

приклад:

У наступному прикладі ми виберемо назву кафедри, якщо ідентифікатор кафедри існує в таблиці студентів:

SELECT DepartmentName
FROM Departments AS d
WHERE EXISTS (SELECT DepartmentId FROM Students AS s WHERE d.DepartmentId = s.DepartmentId);

Це дасть вам:

Буде повернуто лише три кафедри: «ІТ, Фізика та Мистецтво». А назва кафедри «Математика» не буде повернута, оскільки на цій кафедрі немає студентів, тому ідентифікатор кафедри не існує в таблиці студентів. Саме тому оператор EXISTS проігнорував кафедру «Математика».

SQLite $NOT

Reverses результат попереднього оператора, що йде після нього. Наприклад:

• NOT BETWEEN – поверне значення true, якщо BETWEEN поверне значення false, і навпаки.
• NOT LIKE – поверне true, якщо LIKE поверне false, і навпаки.
• NOT GLOB – поверне true, якщо GLOB поверне false, і навпаки.
• NOT EXISTS – поверне значення true, якщо EXISTS поверне значення false, і навпаки.

приклад:

У наступному прикладі ми використаємо оператор NOT з оператором EXISTS, щоб отримати назви факультетів, яких немає в таблиці Students, що є зворотним результатом оператора EXISTS. Таким чином, пошук буде здійснюватися через DepartmentId, який не існує в таблиці відділу.

SELECT DepartmentName
FROM Departments AS d
WHERE NOT EXISTS (SELECT DepartmentId
                  FROM Students AS s
                  WHERE d.DepartmentId = s.DepartmentId);

вихід:

Буде повернуто лише кафедру «Математика». Оскільки кафедра «Математика» – це єдина кафедра, якої немає в таблиці студентів.

Обмеження та впорядкування

SQLite замовлення

SQLite Порядок — сортування результату за одним або декількома виразами. Щоб упорядкувати набір результатів, ви повинні використовувати речення ORDER BY наступним чином:

• По-перше, ви повинні вказати речення ORDER BY.
• Речення ORDER BY має бути зазначено в кінці запиту; після нього можна вказати лише речення LIMIT.
• Укажіть вираз, за ​​яким потрібно впорядкувати дані; цей вираз може бути назвою стовпця або виразом.
• Після виразу ви можете вказати додатковий напрямок сортування. Або DESC, щоб упорядкувати дані за спаданням, або ASC, щоб упорядкувати дані за зростанням. Якщо ви не вказали жодного з них, дані будуть відсортовані за зростанням.
• Ви можете вказати більше виразів, використовуючи «,» між собою.

Приклад

У наступному прикладі ми виберемо всіх студентів, упорядкованих за їх іменами, але в порядку спадання, а потім за назвою факультету в порядку зростання:

SELECT s.StudentName, d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId
ORDER BY d.DepartmentName ASC , s.StudentName DESC;

Це дасть вам:

• SQLite спочатку впорядкує всіх студентів за назвами відділів у порядку зростання
• Потім для кожної назви кафедри всі студенти під назвою цієї кафедри відображатимуться в порядку спадання за їхніми іменами

SQLite Ліміт:

Ви можете обмежити кількість рядків, які повертає ваш SQL-запит, за допомогою пропозиції LIMIT. Наприклад, LIMIT 10 дасть вам лише 10 рядків і проігнорує всі інші рядки.

У реченні LIMIT можна вибрати певну кількість рядків, починаючи з певної позиції, за допомогою речення OFFSET. Наприклад, «LIMIT 4 OFFSET 4» проігнорує перші 4 рядки та поверне 4 рядки, починаючи з п'ятого, тому ви отримаєте рядки 5, 6, 7 та 8.

Зверніть увагу, що речення OFFSET є необов'язковим, ви можете записати його як «LIMIT 4, 4», і це дасть вам точні результати.

приклад:

У наступному прикладі ми повернемо лише 3 студентів, починаючи з ідентифікатора студента 5 за допомогою запиту:

SELECT * FROM Students LIMIT 4,3;

Це дасть вам лише трьох студентів, починаючи з рядка 5. Таким чином, ви отримаєте рядки з StudentId 5, 6 та 7:

Видалення дублікатів

Якщо ваш SQL-запит повертає дублікати значень, ви можете використати ключове слово «DISTINCT», щоб видалити ці дублікати та повернути унікальні значення. Ви можете вказати більше одного стовпця після того, як ключ DISTINCT спрацює.

приклад:

Наступний запит поверне повторювані «значення назв кафедри»: Тут ми маємо повторювані значення з назвами IT, Physics і Arts.

SELECT d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Це дасть повторювані значення для назви відділу:

Зверніть увагу, що для назви відділу є повторювані значення. Тепер ми використаємо ключове слово DISTINCT із тим самим запитом, щоб видалити ці дублікати та отримати лише унікальні значення. Подобається це:

SELECT DISTINCT d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Це дасть лише три унікальні значення для стовпця назви відділу:

заповнювач

SQLite Агрегати — це вбудовані функції, визначені в SQLite який згрупує кілька значень кількох рядків в одне значення.

Ось агрегати, які підтримуються SQLite:

SQLite AVG()

Повернуто середнє для всіх значень x.

приклад:

У наступному прикладі ми отримаємо середню оцінку, яку студенти мають отримати з усіх іспитів:

SELECT AVG(Mark) FROM Marks;

Це дасть вам значення «18.375»:

Ці результати отримані в результаті підсумовування всіх оцінок, поділених на їх кількість.

COUNT() – COUNT(X) або COUNT(*)

Повертає загальну кількість разів появи значення x. І ось кілька параметрів, які можна використовувати з COUNT:

• COUNT(x): підраховує лише значення x, де x – ім’я стовпця. Він ігноруватиме значення NULL.
• COUNT(*): підрахувати всі рядки з усіх стовпців.
• COUNT (DISTINCT x): Ви можете вказати ключове слово DISTINCT перед x, яке отримає кількість різних значень x.

Приклад

У наступному прикладі ми отримаємо загальну кількість відділів за допомогою COUNT(DepartmentId), COUNT(*) і COUNT(DISTINCT DepartmentId) і чим вони відрізняються:

SELECT COUNT(DepartmentId), COUNT(DISTINCT DepartmentId), COUNT(*) FROM Students;

Це дасть вам:

Як наступне:

• COUNT(DepartmentId) дасть вам підрахунок усіх ідентифікаторів відділу та ігноруватиме нульові значення.
• COUNT(DISTINCT DepartmentId) дає вам різні значення DepartmentId, яких лише 3. Це три різні значення назви відділу. Зверніть увагу, що в імені студента є 8 значень назви відділу. Але лише три різні значення: математика, ІТ та фізика.
• COUNT(*) підраховує кількість рядків у таблиці студентів, тобто 10 рядків для 10 студентів.

GROUP_CONCAT() – GROUP_CONCAT(X) або GROUP_CONCAT(X,Y)

Агрегатна функція GROUP_CONCAT об’єднує кілька значень в одне значення, розділяючи їх комою. Він має такі параметри:

• GROUP_CONCAT(X): Це об’єднає всі значення x в один рядок із комою «,» як роздільником між значеннями. Значення NULL ігноруватимуться.
• GROUP_CONCAT(X, Y): Це об’єднає значення x в один рядок із значенням y, що використовується як роздільник між кожним значенням замість стандартного роздільника «,». Значення NULL також ігноруватимуться.
• GROUP_CONCAT(DISTINCT X): Це об’єднає всі різні значення x в один рядок із комою «,» як роздільником між значеннями. Значення NULL ігноруватимуться.

GROUP_CONCAT(Назва відділу) Приклад

Наступний запит об’єднає всі значення назви кафедри зі студентів і таблиці кафедр в один рядок, розділений комами. Отже, замість того, щоб повертати список значень, одне значення в кожному рядку. Він поверне лише одне значення в одному рядку, усі значення розділені комами:

SELECT GROUP_CONCAT(d.DepartmentName)
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Це дасть вам:

Це дасть вам список із 8 значень імен відділів, об’єднаних в один рядок, розділених комами.

GROUP_CONCAT(DISTINCT DepartmentName) Приклад

Наступний запит об’єднає різні значення назви кафедри з таблиці студентів і кафедр в один рядок, розділений комами:

SELECT GROUP_CONCAT(DISTINCT d.DepartmentName)
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Це дасть вам:

Зверніть увагу, чим результат відрізняється від попереднього; повернуто лише три значення, які є різними назвами відділів, а повторювані значення було видалено.

GROUP_CONCAT(Назва відділу ,'&') Приклад

Наступний запит об’єднає всі значення стовпця назви відділу з таблиці студентів і відділів в один рядок, але з символом «&» замість коми як розділювача:

SELECT GROUP_CONCAT(d.DepartmentName, '&')
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Це дасть вам:

Зверніть увагу, що символ «&» використовується замість стандартного символу «» для розділення значень.

SQLite MAX() & MIN()

MAX(X) повертає вам найбільше значення зі значень X. MAX поверне значення NULL, якщо всі значення x є нульовими. Тоді як MIN(X) повертає вам найменше значення зі значень X. MIN поверне значення NULL, якщо всі значення X є нульовими.

Приклад

У наступному запиті ми скористаємося функціями MIN та MAX, щоб отримати найвищу та найнижчу оцінки з таблиці «Оцінки»:

SELECT MAX(Mark), MIN(Mark) FROM Marks;

Це дасть вам:

SQLite SUM(x), Total(x)

Обидва вони повернуть суму всіх значень x. Але вони відрізняються в наступному:

• SUM поверне нульове значення, якщо всі значення нульові, але Total поверне 0.
• TOTAL завжди повертає значення з плаваючою комою. SUM повертає ціле число, якщо всі значення x є цілими. Однак, якщо значення не є цілими, він поверне значення з плаваючою комою.

Приклад

У наступному запиті ми використовуватимемо SUM та total, щоб отримати суму всіх балів у таблицях «Бали»:

SELECT SUM(Mark), TOTAL(Mark) FROM Marks;

Це дасть вам:

Як бачите, TOTAL завжди повертає значення з плаваючою комою. Але SUM повертає ціле значення, оскільки значення в стовпці «Позначка» можуть бути цілими числами.

Приклад різниці між SUM і TOTAL:

У наступному запиті ми покажемо різницю між SUM і TOTAL, коли вони отримають SUM значень NULL:

SELECT SUM(Mark), TOTAL(Mark) FROM Marks WHERE TestId = 4;

Це дасть вам:

Зауважте, що для TestId = 4 немає оцінок, тому для цього тесту є нульові значення. SUM повертає нульове значення як пробіл, тоді як TOTAL повертає 0.

Група BY

Речення GROUP BY використовується для визначення одного або кількох стовпців, які використовуватимуться для групування рядків у групи. Рядки з однаковими значеннями будуть зібрані (впорядковані) разом у групи.

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

приклад:

Наступний запит дасть вам загальну кількість студентів, присутніх на кожному факультеті.

SELECT d.DepartmentName, COUNT(s.StudentId) AS StudentsCount
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId
GROUP BY d. DepartmentName;

Це дасть вам:

Речення GROUPBY DepartmentName згрупує всіх студентів у групи по одній для кожної назви відділу. Для кожної групи «відділення» буде підраховано кількість студентів.

речення HAVING

Якщо ви хочете відфільтрувати групи, які повертає речення GROUP BY, ви можете вказати речення «HAVING» із виразом після GROUP BY. Вираз буде використано для фільтрації цих груп.

Приклад

У наступному запиті ми виберемо ті відділи, на яких є лише два студенти:

SELECT d.DepartmentName, COUNT(s.StudentId) AS StudentsCount
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId
GROUP BY d. DepartmentName
HAVING COUNT(s.StudentId) = 2;

Це дасть вам:

Речення HAVING COUNT(S.StudentId) = 2 відфільтрує повернуті групи та поверне лише ті групи, які містять точно двох студентів. У нашому випадку відділ мистецтв має 2 студентів, тому це відображається у вихідних даних.

SQLite Запит і підзапит

Усередині будь-якого запиту ви можете використовувати інший запит у SELECT, INSERT, DELETE, UPDATE або в іншому підзапиті.

Цей вкладений запит називається підзапитом. Зараз ми побачимо кілька прикладів використання підзапитів у реченні SELECT. Однак у посібнику зі змінення даних ми побачимо, як можна використовувати підзапити з операторами INSERT, DELETE та UPDATE.

Використання підзапиту в прикладі пропозиції FROM

У наступному запиті ми включимо підзапит всередину пропозиції FROM:

SELECT
  s.StudentName, t.Mark
FROM Students AS s
INNER JOIN
(
   SELECT StudentId, Mark
   FROM Tests AS t
   INNER JOIN Marks AS m ON t.TestId = m.TestId
)  ON s.StudentId = t.StudentId;

Запит:

   SELECT StudentId, Mark
   FROM Tests AS t
   INNER JOIN Marks AS m ON t.TestId = m.TestId

Наведений вище запит тут називається підзапитом, оскільки він вкладений у речення FROM. Зверніть увагу, що ми дали йому псевдонім «t», щоб ми могли посилатися на стовпці, повернуті з нього в запиті.

Цей запит дасть вам:

Тож у нашому випадку,

• s.StudentName вибирається з основного запиту, який дає імена студентів і
• t.Mark вибирається з підзапиту; що дає бали, отримані кожним із цих студентів

Використання підзапиту в прикладі пропозиції WHERE

У наступному запиті ми включимо підзапит у речення WHERE:

SELECT DepartmentName
FROM Departments AS d
WHERE NOT EXISTS (SELECT DepartmentId
                  FROM Students AS s
                  WHERE d.DepartmentId = s.DepartmentId);

Запит:

SELECT DepartmentId
FROM Students AS s
WHERE d.DepartmentId = s.DepartmentId

Наведений вище запит тут називається підзапитом, оскільки він вкладений у речення WHERE. Підзапит поверне значення DepartmentId, які використовуватимуться оператором NOT EXISTS.

Цей запит дасть вам:

У наведеному вище запиті ми вибрали факультет, на якому немає студентів. Це факультет «Математика».

Установка Operaції – UNION, Intersect

SQLite підтримує такі операції SET:

СОЮЗ І ВСІ СОЮЗ

Він об’єднує один або більше наборів результатів (групу рядків), отриманих з кількох інструкцій SELECT, в один набір результатів.

UNION поверне різні значення. Однак UNION ALL не буде і включатиме дублікати.

Зауважте, що ім’я стовпця буде ім’ям стовпця, указаним у першому операторі SELECT.

СОЮЗ Приклад

У наступному прикладі ми отримаємо список DepartmentId з таблиці students і список DepartmentId з таблиці departments у тому самому стовпці:

SELECT DepartmentId AS DepartmentIdUnioned FROM Students
UNION
SELECT DepartmentId FROM Departments;

Це дасть вам:

Запит повертає лише 5 рядків, які є окремими значеннями ідентифікатора відділу. Зверніть увагу на перше значення, яке є нульовим значенням.

SQLite UNION ALL Приклад

У наступному прикладі ми отримаємо список DepartmentId з таблиці students і список DepartmentId з таблиці departments у тому самому стовпці:

SELECT DepartmentId AS DepartmentIdUnioned FROM Students
UNION ALL
SELECT DepartmentId FROM Departments;

Це дасть вам:

Запит поверне 14 рядків, 10 рядків із таблиці студентів і 4 із таблиці відділів. Зауважте, що повернуті значення повторюються. Також зауважте, що ім’я стовпця було вказане в першому операторі SELECT.

Тепер давайте подивимося, як UNION all дасть різні результати, якщо замінити UNION ALL на UNION:

SQLite ІНТЕРСЕКТ

Повертає значення, наявні в обох об’єднаних наборах результатів. Значення, які існують в одному з об’єднаних наборів результатів, ігноруватимуться.

Приклад

У наступному запиті ми виберемо значення DepartmentId, які існують у таблицях Students і Departments у стовпці DepartmentId:

SELECT DepartmentId FROM Students
Intersect
SELECT DepartmentId FROM Departments;

Це дасть вам:

Запит повертає лише три значення 1, 2 і 3. Це значення, які існують в обох таблицях.

Однак значення null і 4 не було включено, оскільки нульове значення існує лише в таблиці студентів, а не в таблиці відділів. І значення 4 існує в таблиці відділів, а не в таблиці студентів.

Ось чому обидва значення NULL і 4 були проігноровані та не включені до повернених значень.

Окрім

Припустімо, якщо у вас є два списки рядків, список1 і список2, і вам потрібні рядки лише зі списку1, якого немає в списку2, ви можете використати пропозицію «EXCEPT». Речення EXCEPT порівнює два списки та повертає ті рядки, які існують у списку1 і не існують у списку2.

Приклад

У наступному запиті ми виберемо значення DepartmentId, які існують у таблиці departments і не існують у таблиці students:

SELECT DepartmentId FROM Departments
EXCEPT
SELECT DepartmentId FROM Students;

Це дасть вам:

Запит повертає лише значення 4. Це єдине значення, яке існує в таблиці відділів і не існує в таблиці студентів.

Обробка NULL

Значення «NULL» – це спеціальне значення в SQLiteВін використовується для представлення невідомого або відсутнього значення. Зверніть увагу, що нульове значення повністю відрізняється від значення «0» або пустого значення «». Однак, оскільки 0 і пусте значення є відомим значенням, нульове значення невідоме.

Значення NULL вимагають спеціальної обробки SQLite, тепер ми побачимо, як обробляти значення NULL.

Пошук значень NULL

Ви не можете використовувати звичайний оператор рівності (=) для пошуку нульових значень. Наприклад, наступний запит шукає студентів, які мають нульове значення DepartmentId:

SELECT * FROM Students WHERE DepartmentId = NULL;

Цей запит не дасть результату:

Оскільки значення NULL не дорівнює жодному іншому значенню, яке містить саме нульове значення, тому воно не повернуло жодного результату.

Однак, щоб запит працював, потрібно використовувати оператор «IS NULL» для пошуку нульових значень наступним чином:

SELECT * FROM Students WHERE DepartmentId IS NULL;

Це дасть вам:

Запит поверне тих студентів, які мають нульове значення DepartmentId.

Якщо ви хочете отримати значення, які не є null, вам потрібно використовувати оператор «IS NOT NULL» ось так:

SELECT * FROM Students WHERE DepartmentId IS NOT NULL;

Це дасть вам:

Запит поверне тих студентів, які не мають значення NULL DepartmentId.

Умовні результати

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

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

Наприклад, якщо у вас є стовпець «Оцінка», і ви хочете вибрати текстове значення на основі значення оцінки таким чином:

– «Відмінно», якщо оцінка вище 85.

– «Дуже добре», якщо оцінка від 70 до 85.

– «Добре», якщо оцінка від 60 до 70.

Тоді ви можете використовувати для цього вираз CASE.

Це можна використати для визначення деякої логіки в реченні SELECT, щоб ви могли вибрати певні результати залежно від певних умов, наприклад оператор if.

Оператор CASE можна визначити за допомогою різних синтаксисів таким чином:

Ви можете використовувати різні умови:

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  WHEN condition3 THEN result3
  …
  ELSE resultn
END

Або ви можете використовувати лише один вираз і вказати різні можливі значення на вибір:

CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  WHEN value3 THEN result3
  …
  ELSE restuln
END

Зауважте, що речення ELSE є необов’язковим.

Приклад

У наступному прикладі ми використаємо вираз CASE зі значенням NULL у стовпці «Ідентифікатор відділу» таблиці «Студенти», щоб відобразити текст «Без відділу» наступним чином:

SELECT
  StudentName,
  CASE
    WHEN DepartmentId IS NULL THEN 'No Department'
    ELSE DepartmentId
  END AS DepartmentId
FROM Students;

• Оператор CASE перевірить значення DepartmentId, чи є воно нульовим чи ні.
• Якщо це значення NULL, замість значення DepartmentId буде вибрано літеральне значення "No Department".
• Якщо значення не нульове, буде вибрано значення стовпця DepartmentId.

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

Загальний табличний вираз

Загальні табличні вирази (CTE) — це підзапити, визначені в операторі SQL із заданим іменем.

Він має перевагу перед підзапитами, оскільки він визначений із операторів SQL і полегшить читання, підтримку та розуміння запитів.

Загальний табличний вираз можна визначити, розмістивши речення WITH перед операторами SELECT наступним чином:

WITH CTEname
AS
(
   SELECT statement
)
SELECT, UPDATE, INSERT, or update statement here FROM CTE

«Ім'я CTE» – це будь-яке ім'я, яке ви можете надати CTE, ви можете використовувати його для подальшого звернення до нього. Зверніть увагу, що ви можете визначити оператори SELECT, UPDATE, INSERT або DELETE для CTE.

Тепер давайте розглянемо приклад використання CTE у реченні SELECT.

Приклад

У наступному прикладі ми визначимо CTE з інструкції SELECT, а потім використаємо його в іншому запиті:

WITH AllDepartments
AS
(
  SELECT DepartmentId, DepartmentName
  FROM Departments
)
SELECT
  s.StudentId,
  s.StudentName,
  a.DepartmentName
FROM Students AS s
INNER JOIN AllDepartments AS a ON s.DepartmentId = a.DepartmentId;

У цьому запиті ми визначили CTE та дали йому назву «AllDepartments». Цей CTE був визначений із запиту SELECT:

SELECT DepartmentId, DepartmentName
  FROM Departments

Після того, як ми визначили CTE, ми використали його в запиті SELECT, який йде після нього.

Зауважте, що загальні табличні вирази не впливають на результат запиту. Це спосіб визначити логічне представлення або підзапит, щоб повторно використовувати їх у тому самому запиті. Загальні табличні вирази схожі на змінну, яку ви оголошуєте та повторно використовуєте як підзапит. Лише оператор SELECT впливає на результат запиту.

Цей запит дасть вам:

Розширені запити

Розширені запити – це запити, які містять складні об’єднання, підзапити та деякі агрегати. У наступному розділі ми побачимо приклад розширеного запиту:

Де ми отримуємо,

• Назви відділів із усіма студентами кожного відділу
• Імена учнів розділені комою і
• Відображення відділу, де є принаймні три студенти

SELECT
  d.DepartmentName,
  COUNT(s.StudentId) StudentsCount,
  GROUP_CONCAT(StudentName) AS Students
FROM Departments AS d
INNER JOIN Students AS s ON s.DepartmentId = d.DepartmentId
GROUP BY d.DepartmentName
HAVING COUNT(s.StudentId) >= 3;

Ми додали речення JOIN для отримання DepartmentName з таблиці Departments. Після цього ми додали речення GROUP BY з двома агрегатними функціями:

• «ПІДРАХУВАТИ», щоб підрахувати студентів для кожної групи відділу.
• GROUP_CONCAT, щоб об’єднати студентів для кожної групи через кому в один рядок.

Після GROUP BY ми використали речення HAVING, щоб відфільтрувати відділи та вибрати лише ті відділи, які мають принаймні 3 студентів.

Результат буде таким:

Поширені запитання