Заявка за SQLite: Изберете, Къде, ОГРАНИЧЕТЕ, ОТМЕНЕТЕ, Брой, Групирайте по

За да пишете SQL заявки в база данни на SQLite, трябва да знаете как работят клаузите SELECT, FROM, WHERE, GROUP BY, ORDER BY и LIMIT и как да ги използвате.

По време на този урок ще научите как да използвате тези клаузи и как да пишете SQLite клаузи.

В този урок ще научите -

• Четене на данни с Select
• Имена и псевдоними
• КЪДЕТО
• Ограничаване и подреждане
• Премахване на дубликати
• Агрегат
• Групирай по
• Заявка и подзаявка
• Задайте операции -UNION, Intersect
• НУЛНО боравене
• Условни резултати
• Общ израз на таблица
• Разширени заявки

Четене на данни с Select

Клаузата SELECT е основният израз, който използвате за заявка към база данни на SQLite. В клаузата SELECT посочвате какво да изберете. Но преди клаузата за избор, нека видим откъде можем да изберем данни, използвайки клаузата FROM.

Клаузата FROM се използва, за да посочи къде искате да изберете данни. В клаузата от можете да посочите една или повече таблици или подзаявки, от които да избирате данните, както ще видим по-късно в уроците.

Имайте предвид, че за всички следващи примери трябва да стартирате sqlite3.exe и да отворите връзка към примерната база данни като текуща:

Стъпка 1) В тази стъпка,

1. Отворете Моят компютър и отидете до следната директория " C: \ sqlite " и
2. След това отворете " sqlite3.exe ":

Стъпка 2) Отворете базата данни " TutorialsSampleDB.db " чрез следната команда:

Сега сте готови да изпълните всякакъв вид заявка в базата данни.

В клаузата SELECT можете да изберете не само име на колона, но имате много други опции, за да посочите какво да изберете. Както следва:

ИЗБЕРЕТЕ *

Тази команда ще избере всички колони от всички препратени таблици (или подзаявки) в клаузата FROM. Например:

ИЗБЕРЕТЕ *ОТ студентиВЪТРЕШНО ПРИСЪЕДИНЯВАНЕ НА ОТДЕЛИ ЗА УЧЕНИЦИ.DepartmentId = Отделения.DepartmentId; 

Това ще избере всички колони както от таблиците за студенти, така и от таблиците на отделите:

ИЗБЕРЕТЕ име на таблица. *

Това ще избере всички колони само от таблицата "име на таблица". Например:

ИЗБЕРЕТЕ ученици. *ОТ студентиВЪТРЕШНО ПРИСЪЕДИНЯВАНЕ НА ОТДЕЛИ ЗА УЧЕНИЦИ.DepartmentId = Отделения.DepartmentId;

Това ще избере всички колони само от таблицата за ученици:

Буквална стойност

Буквалната стойност е константна стойност, която може да бъде посочена в оператора select. Можете да използвате литерални стойности обикновено по същия начин, по който използвате имена на колони в клаузата SELECT. Тези литерални стойности ще се показват за всеки ред от редовете, върнати от SQL заявката.

Ето няколко примера за различни литерални стойности, които можете да изберете:

• Numeric Literal - числа във всякакъв формат като 1, 2.55, ... и т.н.
• Низови литерали - Всеки низ „САЩ“, „това е примерен текст“, ... и т.н.
• NULL - NULL стойност.
• Current_TIME - ще ви даде текущото време.
• CURRENT_DATE - това ще ви даде текущата дата.

Това може да бъде удобно в някои ситуации, когато трябва да изберете постоянна стойност за всички върнати редове. Например, ако искате да изберете всички ученици от таблицата Студенти, с нова колона, наречена държава, която съдържа стойността "САЩ", можете да направите това:

SELECT *, 'USA' AS Country FROM Студенти;

Това ще ви даде всички колони на учениците, плюс нова колона "Държава" като тази:

Имайте предвид, че тази нова колона Държава всъщност не е нова колона, добавена към таблицата. Това е виртуална колона, създадена в заявката за показване на резултатите и няма да бъде създадена в таблицата.

Имена и псевдоними

Псевдонимът е ново име за колоната, което ви позволява да изберете колоната с ново име. Псевдонимите на колоните са посочени с помощта на ключовата дума "AS".

Например, ако искате да изберете колоната StudentName, която да се връща с „Student Name“ вместо „StudentName“, можете да й дадете псевдоним като този:

ИЗБЕРЕТЕ ИМЕ НА СТУДЕНТА КАТО „Име на студент“ ОТ Студенти; 

Това ще ви даде имената на студентите с името "Име на студент" вместо "Име на студент" по следния начин:

Имайте предвид, че името на колоната все още е " StudentName "; колоната StudentName е все същата, тя не се променя с псевдонима.

Псевдонимът няма да промени името на колоната; той просто ще промени показаното име в клаузата SELECT.

Също така имайте предвид, че ключовата дума "AS" не е задължителна, можете да поставите псевдонима без него, нещо подобно:

ИЗБЕРЕТЕ ИМЕ НА СТУДЕНТА „Име на студент“ ОТ Студенти;

И ще ви даде точно същия изход като предишната заявка:

Можете също така да дадете псевдоними на таблици, а не само колони. Със същата ключова дума "AS". Например можете да направите това:

SELECT s. * FROM Студенти AS s; 

Това ще ви даде всички колони в таблицата Студенти:

Това може да бъде много полезно, ако се присъединявате към повече от една маса; вместо да повтаряте пълното име на таблицата в заявката, можете да дадете на всяка таблица кратко име на псевдоним. Например в следната заявка:

ИЗБЕРЕТЕ Студенти. Име на студент, Катедри. Име на отделОТ студентиВЪТРЕШНО ПРИСЪЕДИНЯВАНЕ НА ОТДЕЛИ ЗА УЧЕНИЦИ.DepartmentId = Отделения.DepartmentId;

Тази заявка ще избере името на всеки студент от таблицата "Студенти" с името на отдела от таблицата "Отделения":

Същата заявка обаче може да бъде написана по следния начин:

ИЗБЕРЕТЕ s.StudentName, d.DepartmentNameОТ студенти КАТО sВЪТРЕШНО ПРИСЪЕДИНЯВАНЕ на отдели като d НА s.DepartmentId = d.DepartmentId; 

• Дадохме на таблицата на студентите псевдоним "s", а на таблицата на отделите - псевдоним "d".
• След това, вместо да използваме пълното име на таблицата, ние използвахме техните псевдоними, за да се позовем на тях.
• INNER JOIN обединява две или повече таблици заедно, използвайки условие. В нашия пример ние се присъединихме към таблица за студенти с таблица за отдели с колона DepartmentId. Има и задълбочено обяснение за ВЪТРЕШНОТО СЪЕДИНЕНИЕ в урока "SQLite Joins".

Това ще ви даде точния изход като предишната заявка:

КЪДЕТО

Писането на SQL заявки, използвайки клауза SELECT само с клаузата FROM, както видяхме в предишния раздел, ще ви даде всички редове от таблиците. Ако обаче искате да филтрирате върнатите данни, трябва да добавите клауза "WHERE".

Клаузата WHERE се използва за филтриране на резултата, върнат от SQL заявката. Ето как работи клаузата WHERE:

• В клаузата WHERE можете да посочите „израз“.
• Този израз ще бъде оценен за всеки ред, върнат от таблицата (ите), посочена в клаузата FROM.
• Изразът ще бъде оценен като булев израз, с резултат или true, false, или null.
• Тогава ще бъдат върнати само редове, за които изразът е оценен с истинска стойност, а тези с неверни или нулеви резултати ще бъдат игнорирани и не включени в набора от резултати.
• За да филтрирате набора от резултати, използвайки клаузата WHERE, трябва да използвате изрази и оператори.

Списък на операторите в SQLite и как да ги използвате

В следващия раздел ще обясним как можете да филтрирате с помощта на израз и оператори.

Изразът е една или повече литерални стойности или колони, комбинирани помежду си с оператор.

Имайте предвид, че можете да използвате изрази както в клаузата SELECT, така и в клаузата WHERE.

В следващите примери ще изпробваме изразите и операторите както в клаузата select, така и в клаузата WHERE. За да ви покажа как се представят.

Има различни видове изрази и оператори, които можете да посочите, както следва:

SQLite операторът за конкатенация "||"

Този оператор се използва за свързване на една или повече литерални стойности или колони помежду си. Той ще генерира един низ от резултати от всички конкатенирани литерални стойности или колони. Например:

ИЗБЕРЕТЕ 'Id с име:' || StudentId || StudentName AS StudentIdWithNameОТ студенти;

Това ще се свърже в нов псевдоним " StudentIdWithName ":

• Стойността на буквалния низ " Id с име: "
• със стойността на колоната " StudentId " и
• със стойността от колоната " StudentName "

SQLite CAST оператор:

Операторът CAST се използва за преобразуване на стойност от тип данни в друг тип данни.

Например, ако имате числова стойност, съхранена като низова стойност като тази " '12 .5 ' " и искате да я преобразувате в числова стойност, можете да използвате оператора CAST, за да направите това по следния начин " CAST ('12 .5' AS ИСТИНСКИ) ". Или ако имате десетична стойност като 12,5 и трябва да получите само целочислената част, можете да я прехвърлите на цяло число като това "CAST (12,5 AS INTEGER)".

Пример

В следната команда ще се опитаме да преобразуваме различни стойности в други типове данни:

ИЗБЕРЕТЕ 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 аритметични оператори:

Вземете две или повече числови литерални стойности или числови колони и върнете една числова стойност. Поддържаните аритметични оператори в SQLite са:

Събиране " + " - дава сумата от двата операнда. Изваждане " - " - изважда двата операнда и води до разликата. Умножение " * " - произведението на двата операнда. Напомняне (по модул) " % " - дава остатъка, който е резултат от разделянето на един операнд на втория операнд. Деление " / " - връща резултатите от коефициента от разделянето на левия операнд на десния операнд.

Пример:

В следващия пример ще опитаме петте аритметични оператора с буквални числови стойности в един и същ

клауза за избор:

ИЗБЕРЕТЕ 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.

Пример:

ИЗБЕРЕТЕ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 " - използва се за съвпадение на шаблони. Използвайки „ Харесване “, можете да търсите стойности, които съответстват на модел, посочен с помощта на заместващ символ.

Операндът вляво може да бъде или литерална стойност на низ или колона на низ. Моделът може да бъде посочен както следва:

• Съдържа шаблон. Например StudentName LIKE '% a%' - това ще търси имената на студентите, които съдържат буквата "a" във всяка позиция в колоната StudentName.
• Започва с модела. Например " StudentName LIKE 'a%' " - търсете имената на студентите, които започват с буквата "a".
• Завършва с шарката. Например, " StudentName LIKE '% a' " - Потърсете имената на студентите, които завършват с буквата "a".
• Съвпадение на всеки отделен символ в низ с помощта на долната буква "_". Например, " StudentName LIKE 'J___' " - Търсене на имена на ученици с дължина 4 знака. Той трябва да започва с буквата "J" и може да има други три знака след буквата "J".

Примери за съвпадение на шаблони:

1. Вземете имена на ученици, които започват с буквата „j“:

   ИЗБЕРЕТЕ ИМЕ НА СТУДЕНТА ОТ УЧЕНИ, КЪДЕ ИМЕ НА СТУДЕНТ КАТО „j%“;

   Резултат:

   

2. Вземете имената на учениците да завършват с буквата „y“:

   ИЗБЕРЕТЕ ИМЕ НА СТУДЕНТА ОТ УЧЕНИ КЪДЕ ИМЕ НА СТУДЕНТ КАТО „% y“; 

   Резултат:

   

3. Вземете имената на учениците, които съдържат буквата „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) И (StudentName КАТО „N%“);

Като изход, в горната екранна снимка, това ще ви даде само „Нанси“. Нанси е единствената студентка, която отговаря и на двете условия.

SQLite ИЛИ

Логически оператор, който комбинира един или повече изрази, така че ако един от комбинираните оператори даде true, тогава той ще върне true. Ако обаче всички изрази дават false, той ще върне false.

Пример:

Следващата заявка ще търси студенти, които имат StudentId> 5 или StudentName започва с буквата N, върнатите студенти трябва да отговарят на поне едно от условията:

ИЗБЕРЕТЕ *ОТ студентиКЪДЕ (StudentId> 5) ИЛИ (StudentName КАТО '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:

ИЗБЕРЕТЕ *ОТ студентиКЪДЕ ИДЕНТ ЗА УЧЕНИЦИ МЕЖДУ 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:

ИЗБЕРЕТЕ *ОТ студентиКЪДЕ StudentId IN (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) И (StudentId <> 4) И (StudentId <> 6) И (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 - Ще върне true, ако EXISTS връща false и обратно.

Пример:

В следващия пример ще използваме оператора NOT с оператора EXISTS, за да получим имената на отделите, които не съществуват в таблицата Student, което е обратният резултат от оператора EXISTS. Така че търсенето ще се извърши чрез DepartmentId, който не съществува в таблицата на отдела.

ИЗБЕРЕТЕ ИМЕ на отделаОТ отдели като dКЪДЕ НЕ СЪЩЕСТВУВА (SELECT DepartmentIdОТ студенти КАТО sКЪДЕ d.DepartmentId = s.DepartmentId);

Изход :

Ще бъде върнат само отдел „ Математика “. Тъй като отделът " Математика " е единственият отдел, който не съществува в таблицата на учениците.

Ограничаване и подреждане

Поръчка на SQLite

SQLite Order е да сортирате резултата си по един или повече изрази. За да поръчате набора от резултати, трябва да използвате клаузата ORDER BY, както следва:

• Първо трябва да посочите клаузата ORDER BY.
• Клаузата ORDER BY трябва да бъде посочена в края на заявката; след него може да се посочи само клаузата LIMIT.
• Посочете израза, с който да подредите данните, този израз може да бъде име на колона или израз.
• След израза можете да посочите незадължителна посока на сортиране. Или DESC, за да подредите данните низходящо, или ASC, за да подредите данните възходящо. Ако не сте посочили нито един от тях, данните ще бъдат сортирани възходящо.
• Можете да посочите повече изрази, като използвате „,“ помежду си.

Пример

В следващия пример ще изберем всички ученици, подредени по имената им, но в низходящ ред, след това по име на отдела във възходящ ред:

ИЗБЕРЕТЕ s.StudentName, d.DepartmentNameОТ студенти КАТО sВЪТРЕШНО ПРИСЪЕДИНЯВАНЕ Отдели КАТО d НА 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.

Пример:

Следващата заявка ще върне дублирани "стойности на имена на отдели": Тук имаме дублиращи се стойности с имена IT, Physics и Arts.

SELECT d.DepartmentNameОТ студенти КАТО sВЪТРЕШНО ПРИСЪЕДИНЯВАНЕ на отдели като d НА s.DepartmentId = d.DepartmentId;

Това ще ви даде дублирани стойности за името на отдела:

Забележете как има дублирани стойности за името на отдела. Сега ще използваме ключовата дума DISTINCT със същата заявка, за да премахнем тези дубликати и да получим само уникални стойности. Като този:

ИЗБЕРЕТЕ DISTINCT d.DepartmentNameОТ студенти КАТО sВЪТРЕШНО ПРИСЪЕДИНЯВАНЕ на отдели като d НА s.DepartmentId = d.DepartmentId;

Това ще ви даде само три уникални стойности за колоната с име на отдела:

Агрегат

SQLite агрегатите са вградени функции, дефинирани в SQLite, които ще групират множество стойности от множество редове в една стойност.

Ето агрегатите, поддържани от SQLite:

SQLite AVG ()

Върна средната стойност за всички x стойности.

Пример:

В следващия пример ще получим средната оценка, която студентите ще получат от всички изпити:

ИЗБЕРЕТЕ AVG (Марк) ОТ Марки;

Това ще ви даде стойността "18.375":

Тези резултати идват от сумирането на всички стойности на марката, разделено на броя им.

COUNT () - COUNT (X) или COUNT (*)

Връща общия брой на броя показвания на стойността x. И ето няколко опции, които можете да използвате с COUNT:

• БРОЙ (x): Преброява само x стойности, където x е име на колона. Той ще игнорира NULL стойности.
• БРОЙ (*): Пребройте всички редове от всички колони.
• COUNT (DISTINCT x): Можете да посочите ключова дума DISTINCT преди x, което ще получи броя на различните стойности на x.

Пример

В следващия пример ще получим общия брой на отделите с COUNT (DepartmentId), COUNT (*) и COUNT (DISTINCT DepartmentId) и как те са различни:

SELECT COUNT (DepartmentId), COUNT (DISTINCT DepartmentId), COUNT (*) ОТ студенти;

Това ще ви даде:

Както следва:

• 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)ОТ студенти КАТО sВЪТРЕШНО ПРИСЪЕДИНЯВАНЕ на отдели като d НА s.DepartmentId = d.DepartmentId;

Това ще ви даде:

Това ще ви даде списък със стойностите на имената на 8 отдела, обединени в един низ, разделен със запетая.

GROUP_CONCAT (DISTINCT DepartmentName) Пример

Следващата заявка ще обедини отделните стойности на името на отдела от таблицата за студенти и отдели в една низова запетая:

SELECT GROUP_CONCAT (DISTINCT d.DepartmentName)ОТ студенти КАТО sВЪТРЕШНО ПРИСЪЕДИНЯВАНЕ на отдели като d НА s.DepartmentId = d.DepartmentId;

Това ще ви даде:

Забележете как резултатът е различен от предишния резултат; върнати са само три стойности, които са имената на отделните отдели, и дублираните стойности са премахнати.

GROUP_CONCAT (Име на отдел, '&') Пример

Следващата заявка ще обедини всички стойности на колоната с имена на отдела от таблицата за студенти и отдели в един низ, но със знак „&“ вместо запетая като разделител:

SELECT GROUP_CONCAT (d.DepartmentName, '&')ОТ студенти КАТО sВЪТРЕШНО ПРИСЪЕДИНЯВАНЕ на отдели като d НА s.DepartmentId = d.DepartmentId;

Това ще ви даде:

Забележете как се използва знакът "&" вместо символа по подразбиране "," за разделяне между стойностите.

SQLite MAX () и MIN ()

MAX (X) ви връща най-високата стойност от стойностите X. MAX ще върне NULL стойност, ако всички стойности на x са нула. Докато MIN (X) ви връща най-малката стойност от стойностите X. MIN ще върне NULL стойност, ако всички стойности на X са нула.

Пример

В следващата заявка ще използваме функциите MIN и MAX, за да получим най-високата и най-ниската оценка от таблицата " Marks ":

ИЗБЕРЕТЕ МАКС (Марка), МИН (Марка) ОТ Марки;

Това ще ви даде:

SQLite SUM (x), общо (x)

И двамата ще върнат сумата от всички x стойности. Но те са различни в следното:

• SUM ще върне null, ако всички стойности са null, но Total ще върне 0.
• TOTAL винаги връща стойности с плаваща запетая. SUM връща целочислена стойност, ако всички x стойности са цяло число. Ако обаче стойностите не са цяло число, то ще върне стойност с плаваща запетая.

Пример

В следващата заявка ще използваме SUM и total, за да получим сумата от всички марки в таблиците " Marks ":

ИЗБЕРЕТЕ СУМА (Марка), ОБЩО (Марка) ОТ Марки;

Това ще ви даде:

Както можете да видите, TOTAL винаги връща плаваща точка. Но SUM връща целочислена стойност, тъй като стойностите в колоната "Маркиране" може да са в цели числа.

Разлика между сумата и ОБЩО пример:

В следващата заявка ще покажем разликата между SUM и TOTAL, когато получат сумата от NULL стойности:

ИЗБЕРЕТЕ СУММА (Марка), ОБЩО (Марка) ОТ Марки WHERE TestId = 4;

Това ще ви даде:

Имайте предвид, че няма маркировки за TestId = 4, така че има нулеви стойности за този тест. SUM връща нулева стойност като празно, докато TOTAL връща 0.

Групирай по

Клаузата GROUP BY се използва за задаване на една или повече колони, които ще се използват за групиране на редовете в групи. Редовете със същите стойности ще бъдат събрани (подредени) заедно в групи.

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

Пример:

Следващата заявка ще ви даде общия брой студенти, присъстващи във всеки отдел.

SELECT d.DepartmentName, COUNT (s.StudentId) AS StudentsCountОТ студенти КАТО sВЪТРЕШНО ПРИСЪЕДИНЯВАНЕ Отдели КАТО d НА s.DepartmentId = d.DepartmentIdГРУПИРАНЕ по d. DepartmentName;

Това ще ви даде:

Клаузата GROUPBY DepartmentName ще групира всички ученици в групи по една за всяко име на отдел. За всяка група "отдел" тя ще брои учениците в нея.

Клауза HAVING

Ако искате да филтрирате групите, върнати от клаузата GROUP BY, тогава можете да посочите клауза "HAVING" с израз след GROUP BY. Изразът ще се използва за филтриране на тези групи.

Пример

В следващата заявка ще изберем онези отдели, в които има само двама студенти:

SELECT d.DepartmentName, COUNT (s.StudentId) AS StudentsCountОТ студенти КАТО sВЪТРЕШНО ПРИСЪЕДИНЯВАНЕ Отдели КАТО d НА s.DepartmentId = d.DepartmentIdГРУПИРАНЕ по d. DepartmentNameИМА БРОЙ (s.StudentId) = 2;

Това ще ви даде:

Клаузата HAVING COUNT (S.StudentId) = 2 ще филтрира върнатите групи и ще върне само тези групи, които съдържат точно двама ученици в нея. В нашия случай отделът по изкуства има 2 студенти, така че се показва в резултата.

SQLite заявки и подзаявки

Във всяка заявка можете да използвате друга заявка или в SELECT, INSERT, DELETE, UPDATE или в друга подзаявка.

Тази вложена заявка се нарича подзаявка. Сега ще видим някои примери за използване на подзаявки в клаузата SELECT. В урока за модифициране на данни обаче ще видим как можем да използваме подзаявки с оператора INSERT, DELETE и UPDATE.

Използване на подзаявка в примера на клауза FROM

В следващата заявка ще включим подзаявка в клаузата FROM:

ИЗБЕРЕТЕs.StudentName, t.MarkОТ студенти КАТО sВЪТРЕШНО ПРИСЪЕДИНЯВАНЕ(ИЗБЕРЕТЕ StudentId, МаркОТ Тестове КАТО tINNER JOIN Маркира като m ON t.TestId = m.TestId) ON s.StudentId = t.StudentId;

Заявката:

 ИЗБЕРЕТЕ StudentId, МаркОТ Тестове КАТО tINNER JOIN Маркира като m ON t.TestId = m.TestId

Горната заявка тук се нарича подзаявка, защото е вложена в клаузата FROM. Забележете, че му дадохме псевдоним „t“, за да можем да препращаме към колоните, върнати от него в заявката.

Тази заявка ще ви даде:

Така че в нашия случай,

• s.StudentName се избира от основната заявка, която дава името на учениците и
• t.Mark е избран от подзаявката; което дава оценки, получени от всеки от тези ученици

Използване на подзаявка в примера за клауза WHERE

В следващата заявка ще включим подзаявка в клаузата WHERE:

ИЗБЕРЕТЕ ИМЕ на отделаОТ отдели като dКЪДЕ НЕ СЪЩЕСТВУВА (SELECT DepartmentIdОТ студенти КАТО sКЪДЕ d.DepartmentId = s.DepartmentId);

Заявката:

ИЗБЕРЕТЕ ИДОТ студенти КАТО sКЪДЕ d.DepartmentId = s.DepartmentId

Горната заявка тук се нарича подзаявка, защото е вложена в клаузата WHERE. Подзаявката ще върне стойностите на DepartmentId, които ще бъдат използвани от оператора NOT EXISTS.

Тази заявка ще ви даде:

В горната заявка избрахме отдела, в който няма записан студент. Който е отдела "Математика" тук.

Операции за набор - UNION, Intersect

SQLite поддържа следните операции SET:

СЪЮЗ И СЪЮЗ ВСИЧКИ

Той комбинира един или повече набори от резултати (група редове), върнати от множество оператори SELECT в един набор от резултати.

UNION ще върне различни стойности. UNION ALL обаче няма и ще включва дубликати.

Имайте предвид, че името на колоната ще бъде името на колоната, посочено в първия оператор SELECT.

ПРИМЕР НА СЪЮЗА

В следващия пример ще получим списъка на DepartmentId от таблицата на студентите и списъка на DepartmentId от таблицата на отделите в същата колона:

ИЗБЕРЕТЕ DepartmentId AS DepartmentIdUnioned FROM СтудентиСЪЮЗИЗБЕРЕТЕ ИД на отдел от отдели;

Това ще ви даде:

Заявката връща само 5 реда, които са различните стойности на идентификатор на отдел. Забележете първата стойност, която е нулевата стойност.

Пример за SQLite UNION ALL

В следващия пример ще получим списъка на DepartmentId от таблицата на студентите и списъка на DepartmentId от таблицата на отделите в същата колона:

ИЗБЕРЕТЕ DepartmentId AS DepartmentIdUnioned FROM СтудентиСЪЮЗ ВСИЧКИИЗБЕРЕТЕ ИД на отдел от отдели;

Това ще ви даде:

Заявката ще върне 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 " е специална стойност в SQLite. Използва се за представяне на стойност, която е неизвестна или липсваща стойност. Обърнете внимание, че нулевата стойност е напълно различна от стойността " 0 " или празното "". Тъй като 0 и празната стойност е известна стойност, нулевата стойност обаче е неизвестна.

NULL стойностите изискват специална обработка в SQLite, сега ще видим как да боравим със NULL стойностите.

Търсене на NULL стойности

Не можете да използвате нормалния оператор за равенство (=), за да търсите нулевите стойности. Например следната заявка търси студентите, които имат нулева стойност на DepartmentId:

ИЗБЕРЕТЕ * ОТ студенти WHERE DepartmentId = NULL;

Тази заявка няма да даде резултат:

Тъй като стойността NULL не е равна на която и да е друга стойност, която включва самата нулева стойност, затова тя не връща никакъв резултат.

• За да накарате заявката да работи обаче, трябва да използвате оператора "IS NULL" , за да търсите нулеви стойности, както следва:

ИЗБЕРЕТЕ * ОТ студенти, КЪДЕ ИДИЛИДАТА Е НУЛНА;

Това ще ви даде:

Заявката ще върне тези студенти, които имат нулева стойност на DepartmentId.

• Ако искате да получите тези стойности, които не са нула, тогава трябва да използвате оператора „ НЕ Е НУЛО “ по следния начин:

ИЗБЕРЕТЕ * ОТ студенти, КЪДЕ ОТДЕЛ НЕ Е НУЛЕН;

Това ще ви даде:

Заявката ще върне тези студенти, които нямат стойност NULL DepartmentId.

Условни резултати

Ако имате списък със стойности и искате да изберете някоя от тях въз основа на някои условия. За това условието за тази конкретна стойност трябва да е вярно, за да бъде избрано.

Изразът CASE ще оцени този списък с условия за всички стойности. Ако условието е вярно, то ще върне тази стойност.

Например, ако имате колона „Оценка“ и искате да изберете текстова стойност въз основа на стойността на оценката, както следва:

- "Отличен", ако оценката е по-висока от 85.

- "Много добре", ако оценката е между 70 и 85.

- "Добър", ако оценката е между 60 и 70.

След това можете да използвате израза CASE, за да направите това.

Това може да се използва за дефиниране на някаква логика в клаузата SELECT, така че да можете да изберете определени резултати в зависимост от определени условия, като например оператор if.

Операторът CASE може да бъде дефиниран с различни синтаксиси, както следва:

1. Можете да използвате различни условия:

СЛУЧАЙКОГА условие1 ТОГА резултат1КОГА условие2 ТОГА резултат2КОГА условие3 ТОГА резултат3 ... ИНАЧЕ резултатnКРАЙ

2. Или можете да използвате само един израз и да поставите различни възможни стойности, от които да избирате:

CASE изразКОГА стойност1 ТОГА резултат1КОГА стойност2 ТОГА резултат2WHEN value3 THEN result3 ... ELSE restulnКРАЙ

Обърнете внимание, че клаузата ELSE не е задължителна.

Пример

В следващия пример ще използваме израза CASE със стойност NULL в колоната Id на отдела в таблицата Student, за да покажем текста „No Department“, както следва:

ИЗБЕРЕТЕStudentName,СЛУЧАЙКОГАТО DepartmentId Е НИЩО ТОГАВА 'No Department'ELSE отдел ИдEND AS DepartmentIdОТ студенти;

• Операторът CASE ще провери стойността на DepartmentId дали е нула или не.
• Ако е стойност NULL, тогава тя ще избере буквалната стойност „No Department“ вместо стойността DepartmentId.
• Ако не е нулева стойност, тогава тя ще избере стойността на колоната DepartmentId.

Това ще ви даде резултата, както е показано по-долу:

Общ израз на таблица

Общите изрази на таблици (CTE) са подзаявки, които са дефинирани в SQL израза с дадено име.

Той има предимство пред подзаявките, защото е дефиниран от SQL изразите и ще направи заявките по-лесни за четене, поддържане и разбиране.

Общ израз на таблица може да бъде дефиниран чрез поставяне на клаузата WITH пред SELECT изрази, както следва:

С CTEnameКАТО(Изявление SELECT)ИЗБЕРЕТЕ, АКТУАЛИЗИРАТЕ, ВМЕСТЕТЕ или актуализирайте изявление тук ОТ CTE

„ CTEname “ е всяко име, което можете да дадете за CTE, можете да го използвате, за да се обърнете към него по-късно. Имайте предвид, че можете да дефинирате оператор SELECT, UPDATE, INSERT или DELETE на CTE

Сега, нека видим пример за това как да използваме CTE в клаузата SELECT.

Пример

В следващия пример ще дефинираме CTE от оператор SELECT и след това ще го използваме по-късно при друга заявка:

С AllDepartmentsКАТО(ИЗБЕРЕТЕ DepartmentId, DepartmentNameОТ отдели)ИЗБЕРЕТЕs.StudentId,s.StudentName,a.DepartmentNameОТ студенти КАТО sINNER JOIN AllDepartments AS ON ON s.DepartmentId = a.DepartmentId;

В тази заявка дефинирахме CTE и му дадохме името " AllDepartments ". Този CTE е дефиниран от SELECT заявка:

 ИЗБЕРЕТЕ DepartmentId, DepartmentNameОТ отдели

След това, след като дефинирахме CTE, го използвахме в заявката SELECT, която идва след него.

Обърнете внимание, че изразите на често срещаните таблици не влияят на изхода на заявката. Това е начин да дефинирате логически изглед или подзаявка, за да ги използвате повторно в една и съща заявка. Общите изрази на таблицата са като променлива, която декларирате, и я използвате повторно като подзаявка. Само операторът SELECT влияе върху изхода на заявката.

Тази заявка ще ви даде:

Разширени заявки

Разширени заявки са тези заявки, които съдържат сложни обединения, подзаявки и някои агрегати. В следващия раздел ще видим пример за разширена заявка:

Където получаваме,

• Имената на отделите с всички студенти за всеки отдел
• Името на учениците е разделено със запетая и
• Показване на катедрата, в която има поне трима студенти

ИЗБЕРЕТЕd.DepartmentName,COUNT (s.StudentId) StudentCount,GROUP_CONCAT (StudentName) КАТО студентиОТ отдели като dВЪТРЕШНО ПРИСЪЕДИНЯВАНЕ НА СТУДЕНТИ КАТО s s.DepartmentId = d.DepartmentIdGROUP BY d.DepartmentNameИМА БРОЙ (s.StudentId)> = 3;

Добавихме клауза JOIN, за да получим DepartmentName от таблицата Departments. След това добавихме клауза GROUP BY с две обобщени функции:

• „БРОЙ“ за преброяване на учениците за всяка група от отдел.
• GROUP_CONCAT за обединяване на ученици за всяка група със запетая, разделена в един низ.
• След GROUP BY използвахме клаузата HAVING, за да филтрираме отделите и избрахме само тези отдели, които имат поне 3 ученика.

Резултатът ще бъде както следва:

Резюме:

Това беше въведение в писането на SQLite заявки и основите на заявките към базата данни и как можете да филтрирате върнатите данни. Вече можете да пишете свои собствени SQLite заявки.