Щоб писати запити SQL у базі даних SQLite, ви повинні знати, як працюють речення SELECT, FROM, WHERE, GROUP BY, ORDER BY та LIMIT, і як ними користуватися.
Під час цього підручника ви дізнаєтесь, як використовувати ці речення та як писати речення SQLite.
У цьому підручнику ви дізнаєтесь-
- Читання даних за допомогою Select
- Імена та псевдоніми
- ДЕ
- Обмеження та впорядкування
- Видалення дублікатів
- Сукупний
- Група BY
- Запит та підзапит
- Набір операцій -UNION, Intersect
- NULL обробка
- Умовні результати
- Загальний вираз таблиці
- Розширені запити
Читання даних за допомогою Select
Речення SELECT - головне твердження, яке ви використовуєте для запиту бази даних SQLite. У реченні SELECT ви вказуєте, що вибрати. Але перед пропозицією select, давайте подивимося, звідки ми можемо вибрати дані, використовуючи речення FROM.
Речення FROM використовується, щоб вказати, де ви хочете вибрати дані. У пункті from ви можете вказати одну або декілька таблиць або підзапитів для вибору даних, як ми побачимо далі в навчальних посібниках.
Зверніть увагу, що для всіх наступних прикладів вам потрібно запустити sqlite3.exe і відкрити підключення до зразка бази даних як поточне:
Крок 1) На цьому кроці
- Відкрийте Мій комп'ютер і перейдіть до наступного каталогу " C: \ sqlite " та
- Потім відкрийте " sqlite3.exe ":
Крок 2) Відкрийте базу даних " TutorialsSampleDB.db " за такою командою:
Тепер ви готові до запуску будь-якого типу запиту в базі даних.
У реченні SELECT ви можете вибрати не тільки назву стовпця, але у вас є безліч інших варіантів, щоб вказати, що вибрати. Наступне:
ВИБРАТИ *
Ця команда виділить усі стовпці з усіх таблиць, на які посилаються (або підзапити) у реченні FROM. Наприклад:
ВИБРАТИ *ВІД студентівВНУТРІШНЄ ПРИЄДНАННЯ Відділів НА Студентів.DepartmentId = Відділів.DepartmentId;
Буде вибрано всі стовпці як із таблиць студентів, так і з таблиць кафедр:
ВИБЕРІТЬ назву таблиці. *
Це дозволить виділити всі стовпці лише з таблиці "ім'я таблиці". Наприклад:
ВИБЕРІТЬ студентів. *ВІД студентівВНУТРІШНЄ ПРИЄДНАННЯ Відділів НА Студентів.DepartmentId = Відділів.DepartmentId;
Буде вибрано всі стовпці лише з таблиці студентів:
Буквальне значення
Буквальне значення - це постійне значення, яке можна вказати в операторі select. Ви можете використовувати літеральні значення, як правило, так само, як імена стовпців у реченні SELECT. Ці літеральні значення відображатимуться для кожного рядка із рядків, повернутих запитом SQL.
Ось кілька прикладів різних літеральних значень, які ви можете вибрати:
- Числовий літерал - цифри в будь-якому форматі, наприклад 1, 2,55, ... тощо.
- Рядкові літерали - Будь-який рядок "США", "це зразок тексту" тощо.
- NULL - значення NULL.
- Current_TIME - Це дасть вам поточний час.
- CURRENT_DATE - це дасть вам поточну дату.
Це може бути корисним у деяких ситуаціях, коли вам потрібно вибрати постійне значення для всіх повернутих рядків. Наприклад, якщо ви хочете вибрати всіх студентів з таблиці Студенти, з новим стовпцем, який називається країною, що містить значення "США", ви можете зробити це:
SELECT *, 'США' ЯК Країна ВІД студентів;
Це дасть вам усі стовпці для студентів, а також нову колонку "Країна", таку як:
Зверніть увагу, що цей новий стовпець Країна насправді не є новим стовпцем, доданим до таблиці. Це віртуальний стовпець, створений у запиті для відображення результатів, і він не буде створений у таблиці.
Імена та псевдоніми
Псевдонім - це нова назва стовпця, що дозволяє вибрати стовпець з новою назвою. Псевдоніми стовпців задаються за допомогою ключового слова "AS".
Наприклад, якщо ви хочете вибрати стовпець StudentName, який буде повернуто з "Student Name" замість "StudentName", ви можете вказати йому такий псевдонім:
ВИБЕРІТЬ ІМЯ СТУДЕНТА ЯК «Ім'я студента» ІЗ студентів;
Це дасть вам імена студентів з іменем "Ім'я студента" замість "Ім'я студента", як це:
Зверніть увагу, що назва стовпця все ще " Ім'я студента "; стовпець StudentName залишається незмінним, він не змінюється псевдонімом.
Псевдонім не змінить назву стовпця; він просто змінить відображуване ім'я в реченні SELECT.
Крім того, зауважте, що ключове слово "AS" не є обов'язковим, ви можете вказати псевдонім без нього, приблизно так:
ВИБЕРІТЬ ІМЯ СТУДЕНТА 'Ім'я студента' ЗІ студентів;
І це дасть вам точно такий же результат, як і попередній запит:
Ви також можете вказати псевдоніми таблиць, а не лише стовпці. З тим самим ключовим словом "ЯК". Наприклад, ви можете зробити це:
SELECT s. * FROM Students AS s;
Це дасть вам усі стовпці таблиці Студенти:
Це може бути дуже корисно, якщо ви приєднуєтеся до кількох таблиць; замість повторення повного імені таблиці у запиті, ви можете дати кожній таблиці коротке псевдонім. Наприклад, у наступному запиті:
ВИБЕРІТЬ ІМЕННЯ СТУДЕНТІВ, ІНФОРМАЦІЙВІД студентівВНУТРІШНЄ ПРИЄДНАННЯ Відділів НА Студентів.DepartmentId = Відділів.DepartmentId;
За допомогою цього запиту буде вибрано ім’я кожного студента з таблиці "Студенти", а також його назву кафедри з таблиці "Відділи":
Однак той самий запит можна записати так:
ВИБЕРІТЬ s.StudentName, d.DepartmentNameВІД студентів ЯК сВНУТРІШНЄ ПРИЄДНАННЯ Відділи AS d ON s.DepartmentId = d.DepartmentId;
- Ми дали таблиці студентів псевдонім "s", а таблиці відділів - "d".
- Потім, замість повного імені таблиці, ми використовували їхні псевдоніми для посилання на них.
- INNER JOIN об'єднує дві або більше таблиці разом, використовуючи умову. У нашому прикладі ми об’єднали таблицю Студенти з таблицею Відділи зі стовпцем DepartmentId. У посібнику "Приєднання до SQLite" також є поглиблене пояснення ВНУТРІШНЬОГО ПРИЄДНАННЯ.
Це дасть вам точний результат, як попередній запит:
ДЕ
Написання SQL-запитів за допомогою пропозиції SELECT окремо із пропозицією FROM, як ми бачили в попередньому розділі, дасть вам усі рядки з таблиць. Однак, якщо ви хочете відфільтрувати повернені дані, вам слід додати речення "ДЕ".
Речення WHERE використовується для фільтрації набору результатів, що повертається запитом SQL. Ось як працює речення WHERE:
- У реченні WHERE ви можете вказати "вираз".
- Цей вираз буде обчислюватися для кожного рядка, що повертається з таблиць (таблиць), зазначених у реченні FROM.
- Вираз буде обчислюватися як логічний вираз із результатом true, false або null.
- Тоді будуть повернуті лише ті рядки, для яких вираз обчислювався істинним значенням, а ті, що мають хибні чи нульові результати, будуть ігноруватися і не включатись у набір результатів.
- Для фільтрування набору результатів за допомогою речення WHERE потрібно використовувати вирази та оператори.
Список операторів у SQLite та способи їх використання
У наступному розділі ми пояснимо, як можна фільтрувати за допомогою виразу та операторів.
Вираз - це одне або кілька буквальних значень або стовпців, поєднаних між собою за допомогою оператора.
Зверніть увагу, що ви можете використовувати вирази як в реченні SELECT, так і в реченні WHERE.
У наступних прикладах ми спробуємо вирази та оператори як в реченні select, так і в реченні WHERE. Для того, щоб показати вам, як вони працюють.
Існують різні типи виразів та операторів, які ви можете вказати наступним чином:
SQLite оператор конкатенації "||"
Цей оператор використовується для об'єднання одного або декількох літеральних значень або стовпців між собою. Він дасть один рядок результатів з усіх об’єднаних значень літералу або стовпців. Наприклад:
ВИБЕРІТЬ 'Id with Name:' || StudentId || StudentName AS StudentIdWithNameВІД студентів;
Це об’єднається в новий псевдонім " StudentIdWithName ":
- Буквальне значення рядка " Id with Name: "
- зі значенням стовпця " StudentId " та
- зі значенням зі стовпця " Ім'я студента "
Оператор CAST SQLite:
Оператор CAST використовується для перетворення значення з типу даних на інший тип даних.
Наприклад, якщо у вас числове значення зберігається у вигляді рядкового значення, подібного до цього " '12 .5 ' ", і ви хочете перетворити його на числове значення, ви можете використовувати оператор CAST, щоб зробити це як " CAST ('12 .5' AS РЕАЛЬНО) ". Або якщо у вас є десяткове значення, таке як 12,5, і вам потрібно отримати лише цілу частину, ви можете передати його на ціле число, подібне до цього "CAST (12,5 AS INTEGER)".
Приклад
У наступній команді ми спробуємо перетворити різні значення в інші типи даних:
ВИБЕРІТЬ CAST ('12 .5 'ЯК РЕАЛЬНИЙ) ToReal, CAST (12.5 AS INTEGER) ЯК ToInteger;
Це дасть вам:
Результат такий:
- CAST ('12 .5 'AS REAL) - значення '12 .5' є значенням рядка, воно буде перетворено в REAL значення.
- CAST (12,5 AS INTEGER) - значення 12,5 є десятковою величиною, воно буде перетворено в ціле число. Десяткова частина буде усічена, і вона стане 12.
Арифметичні оператори SQLite:
Візьміть два або більше числових літеральних значень або числових стовпців і поверніть одне числове значення. Арифметичні оператори, що підтримуються в SQLite:
|
Приклад:
У наступному прикладі ми спробуємо п’ять арифметичних операторів з буквальними числовими значеннями в одному і тому ж
виберіть пункт:
ВИБЕРІТЬ 25 + 6, 25-6, 25 * 6, 25% 6, 25/6;
Це дасть вам:
Зверніть увагу, як ми використовували тут оператор SELECT без речення FROM. І це дозволено в SQLite, поки ми вибираємо літеральні значення.
Оператори порівняння SQLite
Порівняйте два операнди один з одним і поверніть значення true або false наступним чином:
|
Зауважте, що SQLite виражає справжнє значення 1, а хибне значення 0.
Приклад:
ВИБЕРІТЬ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
" ПОДОБАЄТЬСЯ " - використовується для узгодження зразків. Використовуючи " Подобається ", ви можете шукати значення, які відповідають шаблону, вказаному за допомогою підстановочного символу.
Операндом ліворуч може бути або літеральне значення рядка, або стовпець рядка. Шаблон можна вказати наступним чином:
- Містить візерунок. Наприклад, StudentName ПОДОБАЄТЬСЯ '% a%' - це буде шукати імена студентів, які містять літеру "a" у будь-якій позиції стовпця StudentName.
- Починається з візерунка. Наприклад, " Ім'я студента ПОДОБАЄТЬСЯ 'a%' " - шукайте імена студентів, які починаються на букву "а".
- Закінчується візерунком. Наприклад, " Ім'я студента ПОДОБАЄТЬСЯ '% a' " - Шукайте імена учнів, які закінчуються літерою "а".
- Збіг будь-якого окремого символу в рядку з використанням літери підкреслення "_". Наприклад, " Ім'я студента, ЯК 'J___' " - пошук імен студентів довжиною 4 символи. Він повинен починатися з букви "J" і може мати будь-які інші три символи після букви "J".
Приклади зіставлення зразків:
- Отримайте імена студентів, які починаються на букву `` j '':
ВИБЕРІТЬ ІМЯ СТУДЕНТА З-ДО СТУДЕНТІВ, ДОЗВАТИЩЕ ІМЯ СТУДЕНТА ПОДОБАЄТЬСЯ 'j%';
Результат:
- Знайдіть імена студентів, які закінчуються буквою `` y '':
ВИБЕРІТЬ ІМЯ СТУДЕНТА З-ДО СТУДЕНТІВ, ДОЗВАННЯ ЩОДО ЩОДО ЩОДО "% y";
Результат:
- Отримайте імена студентів, які містять букву 'n':
ВИБЕРІТЬ ІМЯ СТУДЕНТА ВІД СТУДЕНТІВ, ДО ЯКІХ ІМЕНІ СТУДЕНТА ПОДОБАЄТЬСЯ '% n%';
Результат:
"GLOB" - еквівалентно оператору LIKE, але GLOB чутливий до регістру, на відміну від оператора LIKE. Наприклад, наступні дві команди повернуть різні результати:
ВИБЕРІТЬ 'Jack' GLOB 'j%';ВИБЕРІТЬ 'Jack' LIKE 'j%';
Це дасть вам:
- Перше твердження повертає 0 (хибне), оскільки оператор GLOB чутливий до регістру, тому 'j' не дорівнює 'J'. Однак друге твердження поверне 1 (true), оскільки оператор LIKE не враховує регістр, тому 'j' дорівнює 'J'.
Інші оператори:
SQLite І
Логічний оператор, що поєднує один або кілька виразів. Він поверне true, лише якщо всі вирази дають значення "true". Однак він поверне значення false, лише якщо всі вирази дають значення "false".
Приклад:
Наступний запит буде шукати студентів, у яких StudentId> 5, а StudentName починається з літери N, повернуті студенти повинні відповідати двом умовам:
ВИБРАТИ *ВІД студентівДЕ (StudentId> 5) І (Ім'я студента ПОДОБАЄТЬСЯ 'N%');
На виході, на наведеному вище скріншоті, ви отримаєте лише "Ненсі". Ненсі - єдина студентка, яка відповідає обом умовам.
SQLite АБО
Логічний оператор, який поєднує один або кілька виразів, так що якщо один із комбінованих операторів дасть true, то він поверне true. Однак, якщо всі вирази дають false, це поверне false.
Приклад:
Наступний запит буде шукати студентів, у яких StudentId> 5 або StudentName починається з літери N, повернуті студенти повинні відповідати принаймні одній із умов:
ВИБРАТИ *ВІД студентівДЕ (StudentId> 5) АБО (StudentName ПОДОБАЄ 'N%');
Це дасть вам:
Як вихідний результат, на наведеному вище скріншоті, ви отримаєте ім’я студента, у якому в імені буде літера „n“, а також ідентифікатор студента, що має значення> 5.
Як бачите, результат відрізняється від запиту за допомогою оператора AND.
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:
ВИБРАТИ *ВІД студентівДЕ СТУДЕНТ ІЗМІЖ 5 І 8;
Це дасть лише студентам з ідентифікаторами 5, 6, 7 та 8:
SQLite IN
Бере один операнд і список операндів. Поверне true, якщо значення першого операнда дорівнює одному зі значень операндів зі списку. Оператор IN повертає true (1), якщо список операндів містить перше значення операнда в межах його значень. В іншому випадку він поверне false (0).
Ось так: " col IN (x, y, z) ". Це еквівалентно " (col = x) або (col = y) або (col = z) ".
Приклад:
Наступний запит відбере студентів лише з ідентифікаторами 2, 4, 6, 8:
ВИБРАТИ *ВІД студентівДЕ Студент-ІД (2, 4, 6, 8);
Подобається це:
Попередній запит дасть точний результат як наступний запит, оскільки вони еквівалентні:
ВИБРАТИ *ВІД студентівДЕ (StudentId = 2) АБО (StudentId = 4) АБО (StudentId = 6) АБО (StudentId = 8);
Обидва запити дають точний результат. Однак різниця між двома запитами полягає в тому, що для першого запиту ми використовували оператор "IN". У другому запиті ми використовували кілька операторів "АБО".
Оператор IN еквівалентний використанню декількох операторів OR. " WHERE StudentId IN (2, 4, 6, 8) " еквівалентно " WHERE (StudentId = 2) АБО (StudentId = 4) АБО (StudentId = 6) АБО (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:
ВИБРАТИ *ВІД студентівДЕ Студент НЕ БУВ (2, 4, 6, 8);
Подобається це
Попередній запит ми даємо точний результат як наступний запит, оскільки вони еквівалентні:
ВИБРАТИ *ВІД студентівДЕ (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 взагалі немає рядків.
Приклад:
У наступному прикладі ми виберемо назву кафедри, якщо ідентифікатор кафедри існує в таблиці студентів:
ВИБРАТИ Ім'я відділуВІД відділів ЯК dДЕ ІСНУЄ (ВИДІБИТИ ІДЕНТИФІДАЦІЮ ІЗ СТУДЕНТІВ ТАК, ДЕ d.DepartmentId = s.DepartmentId);
Це дасть вам:
Будуть повернуті лише три кафедри " ІТ, фізика та мистецтво ". І назва кафедри " Математика " не буде повернута, оскільки на цьому факультеті немає студента, тому ідентифікатор кафедри не існує в таблиці студентів. Ось чому оператор EXISTS проігнорував відділ " Математика ".
SQLite НЕ
Змінює результат попереднього оператора, який настає після нього. Наприклад:
- НЕ МІЖ - Поверне істину, якщо МІЖ поверне помилку і навпаки.
- NOT LIKE - це поверне true, якщо LIKE поверне false і навпаки.
- NOT GLOB - Це поверне true, якщо GLOB поверне false і навпаки.
- НЕ ІСНУЄ - Це поверне істину, якщо ІСНУЄ повертає хибне і навпаки.
Приклад:
У наступному прикладі ми будемо використовувати оператор NOT з оператором EXISTS, щоб отримати імена відділів, які не існують у таблиці Студенти, що є зворотним результатом оператора EXISTS. Отже, пошук буде здійснюватися через DepartmentId, який не існує в таблиці відділів.
ВИБРАТИ Ім'я відділуВІД відділів ЯК dДЕ НЕ ІСНУЄ (ВІДБЕРІТЬВІД студентів ЯК сДЕ d.DepartmentId = s.DepartmentId);
Вихід :
Буде повернуто лише кафедру " Математика ". Оскільки відділ " Математика " - це єдиний відділ, якого немає в таблиці студентів.
Обмеження та впорядкування
Порядок SQLite
Порядок SQLite - це сортування результату за одним або кількома виразами. Щоб замовити набір результатів, вам слід використати речення ORDER BY наступним чином:
- По-перше, вам слід вказати речення ORDER BY.
- У кінці запиту потрібно вказати речення ORDER BY; після нього можна вказати лише речення LIMIT.
- Вкажіть вираз для упорядкування даних, він може бути ім'ям стовпця або виразом.
- Після виразу можна вказати необов’язковий напрямок сортування. Або DESC, щоб замовити дані за спаданням, або ASC, щоб замовити дані за зростанням. Якщо ви не вказали жодного з них, дані будуть відсортовані за зростанням.
- Ви можете вказати більше виразів, використовуючи "," між собою.
Приклад
У наступному прикладі ми виділимо всіх студентів, упорядкованих за їх іменами, але в порядку зменшення, а потім за назвою кафедри у порядку зростання:
ВИБЕРІТЬ s.StudentName, d.DepartmentNameВІД студентів ЯК сВНУТРІШНЄ ПРИЄДНАННЯ Відділи AS d ON s.DepartmentId = d.DepartmentIdЗАМОВЛЕННЯ 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, використовуючи запит:
ВИБЕРІТЬ * ВІД студентів ОБМЕЖИТИ 4,3;
Це дасть вам лише трьох студентів, починаючи з рядка 5. Тож це дасть вам рядки з StudentId 5, 6 та 7:
Видалення дублікатів
Якщо ваш запит SQL повертає повторювані значення, ви можете використовувати ключове слово " DISTINCT ", щоб видалити ці дублікати та повернути різні значення. Ви можете вказати більше одного стовпця після роботи ключа DISTINCT.
Приклад:
Наступний запит поверне повторювані "значення назв відділів": Тут ми маємо повторювані значення з іменами ІТ, фізика та мистецтво.
SELECT d.DepartmentNameВІД студентів ЯК сВНУТРІШНЄ ПРИЄДНАННЯ Відділи AS d ON s.DepartmentId = d.DepartmentId;
Це дасть вам повторювані значення для назви відділу:
Зверніть увагу, як існують повторювані значення для назви відділу. Тепер ми будемо використовувати ключове слово DISTINCT з тим самим запитом, щоб видалити ці дублікати та отримати лише унікальні значення. Подобається це:
ВИБЕРІТЬ ВИЗНАЧЕННЯ dВІД студентів ЯК сВНУТРІШНЄ ПРИЄДНАННЯ Відділи AS d ON s.DepartmentId = d.DepartmentId;
Це дасть вам лише три унікальних значення для стовпця назви відділу:
Сукупний
Агрегати SQLite - це вбудовані функції, визначені в SQLite, які групують кілька значень декількох рядків в одне значення.
Ось агрегати, що підтримуються SQLite:
SQLite AVG ()
Повернуто середнє значення для всіх значень x.
Приклад:
У наступному прикладі ми отримаємо середню оцінку студентів за всі іспити:
ВИБЕРІТЬ AVG (Позначити) ІЗ Позначок;
Це дасть вам значення "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) і як вони відрізняються:
ВИБЕРІТЬ КІЛЬКУ (Ідентифікатор відділу), КІЛЬКІСТЬ (ВИЗНАЧЕНИЙ Ідентифікатор відділу), КОЛИЧКУ (*) ВІД студентів;
Це дасть вам:
Наступне:
- 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)ВІД студентів ЯК сВНУТРІШНЄ ПРИЄДНАННЯ Відділи AS d ON s.DepartmentId = d.DepartmentId;
Це дасть вам:
Це дасть вам список значень 8 назв відділів, об’єднаних в одну рядок, розділену комами.
GROUP_CONCAT (DISTINCT DepartmentName) Приклад
Наступний запит об’єднає різні значення імені відділу з таблиці студентів та відділів в одну рядок, розділену комами:
ВИБЕРІТЬ GROUP_CONCAT (DISTINCT d.DepartmentName)ВІД студентів ЯК сВНУТРІШНЄ ПРИЄДНАННЯ Відділи AS d ON s.DepartmentId = d.DepartmentId;
Це дасть вам:
Зверніть увагу, чим результат відрізняється від попереднього результату; повернуто лише три значення, які є іменами різних підрозділів, і повторювані значення були видалені.
GROUP_CONCAT (Ім'я відділу, '&') Приклад
Наступний запит об’єднає всі значення стовпця імені відділу з таблиці студентів та відділів в один рядок, але з символом '&' замість коми як роздільник:
SELECT GROUP_CONCAT (d.DepartmentName, '&')ВІД студентів ЯК сВНУТРІШНЄ ПРИЄДНАННЯ Відділи AS d ON s.DepartmentId = d.DepartmentId;
Це дасть вам:
Зверніть увагу, як символ "&" використовується замість символу за замовчуванням "," для розділення значень.
SQLite MAX () і MIN ()
MAX (X) повертає вам найвище значення із значень X. MAX поверне значення NULL, якщо всі значення x є нульовими. Тоді як MIN (X) повертає вам найменше значення із значень X. MIN поверне значення NULL, якщо всі значення X є нульовими.
Приклад
У наступному запиті ми будемо використовувати функції MIN та MAX, щоб отримати найвищу оцінку та найнижчу оцінку з таблиці " Позначки ":
ВИБЕРІТЬ MAX (Mark), MIN (Mark) FROM Marks;
Це дасть вам:
SQLite SUM (x), усього (x)
Обидва вони повернуть суму всіх значень x. Але вони відрізняються в наступному:
- SUM поверне нуль, якщо всі значення нульові, але Total поверне 0.
- TOTAL завжди повертає значення з плаваючою комою. SUM повертає ціле значення, якщо всі значення x є цілими. Однак, якщо значення не є цілим числом, воно поверне значення з плаваючою комою.
Приклад
У наступному запиті ми будемо використовувати SUM і total, щоб отримати суму всіх позначок у таблицях " Позначки ":
ВИБЕРІТЬ СУММУ (Позначка), ВСЬОГО (Позначку) ВІД позначок;
Це дасть вам:
Як бачите, TOTAL завжди повертає плаваючу крапку. Але SUM повертає ціле число, оскільки значення у стовпці "Позначити" можуть бути цілими числами.
Різниця між прикладом SUM та TOTAL:
У наступному запиті ми покажемо різницю між SUM і TOTAL, коли вони отримають SUM значень NULL:
ВИБЕРІТЬ СУММУ (Позначка), ВСЬОГО (Позначку) ВІД позначок, ДЕ TestId = 4;
Це дасть вам:
Зверніть увагу, що для TestId = 4 немає позначок, тому для цього тесту існують нульові значення. SUM повертає нульове значення як порожнє, тоді як TOTAL повертає 0.
Група BY
Речення GROUP BY використовується для вказівки одного або декількох стовпців, які будуть використовуватися для групування рядків у групи. Рядки з однаковими значеннями будуть зібрані (розташовані) разом у групи.
Для будь-якого іншого стовпця, який не входить до групи за стовпцями, ви можете використовувати для нього агрегатну функцію.
Приклад:
Наступний запит дасть вам загальну кількість студентів у кожному відділенні.
SELECT d.DepartmentName, COUNT (s.StudentId) AS StudentsCountВІД студентів ЯК сВНУТРІШНЄ ПРИЄДНАННЯ Відділи AS d ON s.DepartmentId = d.DepartmentIdГРУПА ЗА d. Ім'я департаменту;
Це дасть вам:
Речення GROUPBY DepartmentName об’єднує всіх студентів у групи по одній для кожного найменування кафедри. Для кожної групи "кафедри" вона буде рахувати студентів.
Речення HAVING
Якщо ви хочете відфільтрувати групи, що повертаються реченням GROUP BY, тоді ви можете вказати речення "HAVING" із виразом після GROUP BY. Вираз буде використано для фільтрування цих груп.
Приклад
У наступному запиті ми оберемо ті кафедри, на яких навчаються лише двоє студентів:
SELECT d.DepartmentName, COUNT (s.StudentId) AS StudentsCountВІД студентів ЯК сВНУТРІШНЄ ПРИЄДНАННЯ Відділи AS d ON s.DepartmentId = d.DepartmentIdГРУПА ЗА d. Ім'я департаментуМАЮЧИ КІЛЬКІСТЬ (s.StudentId) = 2;
Це дасть вам:
Речення HAVING COUNT (S.StudentId) = 2 відфільтрує повернуті групи та поверне лише ті групи, які містять у собі рівно двох студентів. У нашому випадку на факультеті мистецтв навчаються 2 студенти, тому це відображається у вихідних даних.
Запити та підзапити SQLite
Усередині будь-якого запиту ви можете використовувати інший запит або в SELECT, INSERT, DELETE, UPDATE, або всередині іншого підзапиту.
Цей вкладений запит називається підзапитом. Зараз ми побачимо кілька прикладів використання підзапитів у реченні SELECT. Однак у підручнику з модифікації даних ми побачимо, як ми можемо використовувати підзапити з оператором INSERT, DELETE та UPDATE.
Використання підзапиту в прикладі речення FROM
У наступному запиті ми включимо підзапит всередині речення FROM:
ВИБЕРІТЬs.StudentName, t.MarkВІД студентів ЯК сВНУТРІШНЄ З'ЄДНАННЯ(ВИБЕРІТЬ StudentId, ПозначВІД тестів ЯК тВНУТРІШНЄ ПРИЄДНАННЯ Позначки як m НА t.TestId = m.TestId) ON s.StudentId = t.StudentId;
Запит:
ВИБЕРІТЬ StudentId, ПозначВІД тестів ЯК тВНУТРІШНЄ ПРИЄДНАННЯ Позначки як m НА t.TestId = m.TestId
Наведений вище запит тут називається підзапитом, оскільки він вкладений у речення FROM. Зверніть увагу, що ми дали йому псевдонім "t", щоб ми могли посилатися на стовпці, повернуті з нього у запиті.
Цей запит дасть вам:
Отже, у нашому випадку,
- s.StudentName вибирається з основного запиту, який дає ім'я студентів та
- t.Mark вибирається з підзапиту; що дає оцінки, отримані кожним із цих учнів
Використання підзапиту в прикладі речення WHERE
У наступному запиті ми включимо підзапит у речення WHERE:
ВИБРАТИ Ім'я відділуВІД відділів ЯК dДЕ НЕ ІСНУЄ (ВІДБЕРІТЬВІД студентів ЯК сДЕ d.DepartmentId = s.DepartmentId);
Запит:
ВИБІР ІДВІД студентів ЯК сДЕ d.DepartmentId = s.DepartmentId
Наведений вище запит тут називається підзапитом, оскільки він вкладений у речення WHERE. Підзапит поверне значення DepartmentId, які будуть використовуватися оператором НЕ ІСНУЄ.
Цей запит дасть вам:
У наведеному вище запиті ми обрали кафедру, в якій не було студентів. Який тут відділ "Математики".
Набір операцій - СОЮЗ, перетин
SQLite підтримує такі операції SET:
СОЮЗ І СОЮЗ ВСІХ
Він поєднує один або більше наборів результатів (група рядків), повернутих із декількох операторів SELECT, в один набір результатів.
UNION поверне різні цінності. Однак UNION ALL не буде і включатиме дублікати.
Зверніть увагу, що ім'ям стовпця буде ім'я стовпця, вказане в першому операторі SELECT.
ПРИКЛАД СОЮЗУ
У наступному прикладі ми отримаємо список DepartmentId з таблиці студентів і список DepartmentId з таблиці Department у тому ж стовпці:
ВИБЕРІТЬ DepartmentId AS DepartmentIdUnioned FROM StudentsСОЮЗВИБІР ІДЕНТИФІДАТУ З кафедр;
Це дасть вам:
Запит повертає лише 5 рядків, які є різними значеннями ідентифікатора відділу. Зверніть увагу на перше значення, яке є нульовим значенням.
Приклад SQLite UNION ALL
У наступному прикладі ми отримаємо список DepartmentId з таблиці студентів і список DepartmentId з таблиці Department у тому ж стовпці:
ВИБЕРІТЬ DepartmentId AS DepartmentIdUnioned FROM StudentsСОЮЗ ВСІХВИБІР ІДЕНТИФІДАТУ З кафедр;
Це дасть вам:
Запит поверне 14 рядків, 10 рядків із таблиці студентів та 4 з таблиці відділів. Зверніть увагу, що у повернутих значеннях є дублікати. Також зауважте, що ім'ям стовпця було те, яке вказано в першому операторі SELECT.
А тепер давайте подивимося, як все UNION дасть різні результати, якщо замінити UNION ALL на UNION:
SQLite INTERSECT
Повертає значення, наявні в обох комбінованих результатах. Значення, що існують в одному з комбінованих наборів результатів, ігноруватимуться.
Приклад
У наступному запиті ми виберемо значення DepartmentId, які існують як у таблицях Студенти, так і у Відділах у стовпці DepartmentId:
ВИБЕРІТЬ ІДЕНТИФІДАЛЬНИК зі студентівПеретинВИБІР ІДЕНТИФІДАТУ З кафедр;
Це дасть вам:
Запит повертає лише три значення 1, 2 і 3. Які значення є в обох таблицях.
Однак значення null та 4 не були включені, оскільки значення null існує лише в таблиці студентів, а не в таблиці відділів. І значення 4 існує в таблиці кафедр, а не в таблиці студентів.
Ось чому і значення NULL, і 4 були проігноровані та не включені до повернутих значень.
ОКРЕМ
Припустимо, якщо у вас є два списки рядків, list1 і list2, і ви хочете, щоб рядки були лише з list1, який не існує в list2, ви можете використовувати речення "EXCEPT". Речення EXCEPT порівнює два списки і повертає ті рядки, які існують у list1 та не існують у list2.
Приклад
У наступному запиті ми виберемо значення DepartmentId, які існують у таблиці відділів і не існують у таблиці студентів:
ВИБЕРІТЬ ІДЕНТИФІД ІЗ кафедрОКРЕМВИБЕРІТЬ ІДЕНТИФІДАЛЬНИК зі студентів;
Це дасть вам:
Запит повертає лише значення 4. Яке є єдиним значенням, яке існує в таблиці підрозділів і не існує в таблиці студентів.
NULL обробка
Значення " NULL " - це особливе значення в SQLite. Він використовується для представлення значення, яке невідоме або відсутнє значення. Зверніть увагу, що нульове значення абсолютно відрізняється від значення " 0 " або порожнього "" значення. Оскільки 0 і порожнє значення - це відоме значення, однак, нульове значення невідоме.
Значення NULL вимагають спеціальної обробки в SQLite, зараз ми побачимо, як обробляти значення NULL.
Пошук значень NULL
Ви не можете використовувати звичайний оператор рівності (=) для пошуку нульових значень. Наприклад, такий запит шукає студентів, які мають нульове значення DepartmentId:
ВИБЕРІТЬ * ВІД студентів, ДЕ DepartmentId = NULL;
Цей запит не дасть результату:
Оскільки значення NULL не дорівнює будь-якому іншому значенню, що включає саме нульове значення, саме тому воно не повернуло жодного результату.
- Однак для того, щоб запит працював, вам потрібно використовувати оператор "IS NULL" для пошуку нульових значень, як показано нижче:
ВИБЕРІТЬ * ЗІ СТУДЕНТІВ ТАМ, ЩО ВІДДІЛ НУЩИЙ;
Це дасть вам:
Запит поверне тих студентів, які мають нульове значення DepartmentId.
- Якщо ви хочете отримати ті значення, які не є нульовими, тоді вам доведеться використовувати оператор " НЕ НУЛЬНИЙ " таким чином:
ВИБЕРІТЬ * ЗІ СТУДЕНТІВ, ДЕ кафедра НЕ НУЛЬНА;
Це дасть вам:
Запит поверне тих студентів, які не мають значення NULL DepartmentId.
Умовні результати
Якщо у вас є список значень, і ви хочете вибрати будь-яке з них, виходячи з певних умов. Для цього умова для цього конкретного значення має бути істинним, щоб бути обраним.
Вираз CASE оцінить цей перелік умов для всіх значень. Якщо умова відповідає дійсності, воно поверне це значення.
Наприклад, якщо у вас стовпець "Оцінка", і ви хочете вибрати текстове значення на основі значення оцінки, як показано нижче:
- "Відмінно", якщо оцінка вища за 85.
- "Дуже добре", якщо оцінка від 70 до 85.
- "Добре", якщо оцінка становить від 60 до 70.
Тоді для цього можна використовувати вираз CASE.
Це може бути використано для визначення деякої логіки в реченні SELECT, щоб ви могли вибрати певні результати залежно від певних умов, наприклад, наприклад, оператор if.
Оператор CASE можна визначити з різними синтаксисами наступним чином:
- Ви можете використовувати різні умови:
СПРАВАКОЛИ умова1 ПОТІМ результат1КОЛИ умова2 ПОТІМ результат2КОЛИ умова3 ПОТІМ результат3 ... Інакше результатнКІНЕЦЬ
- Або ви можете використовувати лише один вираз і вказати різні можливі значення на вибір:
Вираз CASEКОЛИ значення1 ПОТІМ результат1КОЛИ значення2 ПОТІМ результат2WHEN value3 THEN result3 ... ELSE restulnКІНЕЦЬ
Зауважте, що речення ELSE є необов’язковим.
Приклад
У наступному прикладі ми будемо використовувати вираз CASE зі значенням NULL у стовпці Id відділу в таблиці Студенти, щоб відобразити текст 'No Department' наступним чином:
ВИБЕРІТЬІм'я студента,СПРАВАКОЛИ кафедра ІДЕ НУЛЬКА, ТОГДА "немає департаменту"ІНШІ кафедриEND AS DepartmentIdВІД студентів;
- Оператор CASE перевірить значення DepartmentId, чи є воно нульовим чи ні.
- Якщо це значення NULL, тоді замість значення DepartmentId буде вибрано буквальне значення 'No Department'.
- Якщо не є нульовим значенням, тоді воно вибере значення стовпця DepartmentId.
Це дасть вам результат, як показано нижче:
Загальний вираз таблиці
Загальні вирази таблиць (CTE) - це підзапити, які визначаються в операторі SQL із заданим іменем.
Він має перевагу перед підзапитами, оскільки він визначається із операторів SQL і полегшує читання, підтримку та розуміння запитів.
Звичайний вираз таблиці можна визначити, поставивши речення WITH перед операторами SELECT таким чином:
З CTEnameЯК(Оператор SELECT)ВИБЕРІТЬ, ОНОВИТИ, ВСТАВИТИ або оновити оператор тут З ЦТЕ
" Ім'я CTE " - це будь-яке ім'я, яке ви можете назвати для CTE, і ви можете використовувати його для подальшого посилання на нього. Зверніть увагу, що ви можете визначити оператор SELECT, UPDATE, INSERT або DELETE на CTE
Тепер давайте подивимось приклад того, як використовувати CTE у реченні SELECT.
Приклад
У наступному прикладі ми визначимо CTE з оператора SELECT, а потім використаємо його пізніше для іншого запиту:
З усіма відділамиЯК(ВИБЕРІТЬ Ідентифікатор відділу, Ім'я департаментуІЗ відділів)ВИБЕРІТЬs.StudentId,s.StudentName,a.Ім'я департаментуВІД студентів ЯК сВНУТРІШНЄ ПРИЄДНАННЯ до всіх відділів ЯК ON s.DepartmentId = a.
У цьому запиті ми визначили CTE і дали йому назву " AllDepartments ". Цей CTE був визначений із запиту SELECT:
ВИБЕРІТЬ Ідентифікатор відділу, Ім'я департаментуІЗ відділів
Потім, після того, як ми визначили CTE, ми використали його в запиті SELECT, який йде після нього.
Зверніть увагу, що загальні вирази таблиці не впливають на результат запиту. Це спосіб визначити логічний вигляд або підзапит, щоб повторно використовувати їх у тому самому запиті. Загальні вирази таблиці схожі на змінну, яку ви оголошуєте, і використовуйте її повторно як підзапит. Тільки оператор SELECT впливає на вихід запиту.
Цей запит дасть вам:
Розширені запити
Розширені запити - це ті запити, які містять складні об’єднання, підзапити та деякі агрегати. У наступному розділі ми побачимо приклад розширеного запиту:
Де ми отримуємо,
- Назви кафедри з усіма студентами кожного відділення
- Імена учнів розділяються комами та
- Показано, що на кафедрі є щонайменше троє студентів
ВИБЕРІТЬd.Ім'я департаменту,COUNT (s.StudentId) StudentCount,GROUP_CONCAT (StudentName) AS СтудентиВІД відділів ЯК dВНУТРІШНЄ ПРИЄДНАННЯ УЧНІВ ЯК НА s s.DepartmentId = d.DepartmentIdГРУПА ЗА дМАЮЧИ КІЛЬКІСТЬ (s.StudentId)> = 3;
Ми додали пропозицію JOIN, щоб отримати DepartmentName з таблиці Departments. Після цього ми додали речення GROUP BY з двома сукупними функціями:
- "COUNT" для підрахунку студентів для кожної групи кафедри.
- GROUP_CONCAT для об’єднання студентів для кожної групи комами, розділеними в один рядок.
- Після GROUP BY ми використовували речення HAVING для фільтрації кафедр та відбору лише тих кафедр, у яких є принаймні 3 студенти.
Результат буде таким:
Короткий зміст:
Це було введенням до написання запитів SQLite та основ запитів до бази даних та способу фільтрації повернутих даних. Тепер ви можете писати власні запити SQLite.