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

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

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

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

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

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

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

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

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

Крок 2) Відкрийте базу даних "ПідручникиSampleDB.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 залишається тим самим, він не змінюється псевдонімом.

Псевдонім не змінить назву стовпця; це просто змінить відображуване ім’я в реченні 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;

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

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

ДЕ

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

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

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

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

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

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

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

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

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

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

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

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

Це буде об'єднано в новий псевдонім "StudentIdWithName":

• Літеральне значення рядка "ID з ім'ям: "
• зі значенням “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 є:

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

приклад:

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

вибрати пункт:

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

Це дасть вам:

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

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

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

"<” – повертає 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” – використовується для зіставлення шаблону. Використовуючи "Люблю“, ви можете шукати значення, які відповідають шаблону, указаному за допомогою символу підстановки.

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

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

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

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

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

   Результат:

   

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

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

   Результат:

   

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

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

   Результат:

   

«ГЛОБ» – еквівалентний оператору 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 МІЖ Y ТА Z” поверне true (1), якщо значення X знаходиться між двома значеннями Y і Z. В іншому випадку воно поверне false (0). «X МІЖ Y ТА Z"еквівалентно"X >= Y І 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).

Подобається це: "стовпець 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 еквівалентний використанню кількох операторів АБО. "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) І (col<>y) І (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

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

• 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. Наприклад, "МЕЖА 4 ЗМІЩЕННЯ 4” проігнорує перші 4 рядки та поверне 4 рядки, починаючи з п’ятого, тож ви отримаєте рядки 5,6,7, 8, XNUMX і XNUMX.

Зауважте, що речення 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. Він використовується для представлення невідомого або відсутнього значення. Зверніть увагу, що значення null повністю відрізняється від "0” або пусте значення „”. Оскільки 0 і порожнє значення є відомим значенням, однак нульове значення невідоме.

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

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

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

SELECT * FROM Students WHERE DepartmentId = NULL;

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

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

• Однак, щоб запит працював, ви повинні використовувати «Є НУЛЬ» оператор для пошуку нульових значень наступним чином:

SELECT * FROM Students WHERE DepartmentId IS NULL;

Це дасть вам:

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

• Якщо ви хочете отримати ці значення, які не є нульовими, тоді вам потрібно використовувати "НЕ ПОВТОРЮЄТЬСЯ” такий оператор:

SELECT * FROM Students WHERE DepartmentId IS NOT NULL;

Це дасть вам:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Приклад

У наступному прикладі ми будемо використовувати СПРАВА вираз с 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

ПодіяCTEname” – це будь-яке ім’я, яке ви можете дати для 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 був визначений із запиту 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;

Ми додали a РЕЄСТРАЦІЯ для отримання DepartmentName із таблиці Departments. Після цього ми додали речення GROUP BY із двома агрегатними функціями:

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

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

Підсумки

Це був вступ до письма SQLite запити та основи запитів до бази даних і те, як можна фільтрувати повернуті дані. Тепер ви можете написати свій власний SQLite запити.