SQLite Заявка: Изберете, Къде, ОГРАНИЧЕНИЕ, ОТСТЪПКА, Преброяване, Групиране по
За да пишете SQL заявки в an SQLite база данни, трябва да знаете как работят клаузите SELECT, FROM, WHERE, GROUP BY, ORDER BY и LIMIT и как да ги използвате.
По време на този урок ще научите как да използвате тези клаузи и как да пишете SQLite клаузи.
Четене на данни с Select
Клаузата SELECT е основният оператор, който използвате, за да направите заявка SQLite база данни. В клаузата SELECT вие посочвате какво да изберете. Но преди клаузата за избор, нека видим откъде можем да изберем данни с помощта на клаузата FROM.
Клаузата FROM се използва за указване къде искате да изберете данни. В клаузата from можете да посочите една или повече таблици или подзаявки, от които да изберете данните, както ще видим по-късно в уроците.
Обърнете внимание, че за всички следващи примери трябва да стартирате sqlite3.exe и да отворите връзка към примерната база данни като текуща:
Стъпка 1) В този етап,
- Отворете Моят компютър и отидете до следната директория „C:\sqlite" и
- След това отворете „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;
- Дадохме на таблицата Students псевдоним „s“, а на таблицата с отдели псевдоним „d“.
- След това, вместо да използваме пълното име на таблицата, използвахме техните псевдоними, за да се позоваваме на тях.
- INNER JOIN свързва две или повече таблици заедно с помощта на условие. В нашия пример ние обединихме таблица Students с таблица Departments с колона DepartmentId. Има също така задълбочено обяснение за INNER JOIN в „SQLite Урок за присъединяване.
Това ще ви даде точния резултат като предишната заявка:
КЪДЕ
Писането на SQL заявки, използвайки клаузата SELECT само с клаузата FROM, както видяхме в предишния раздел, ще ви даде всички редове от таблиците. Ако обаче искате да филтрирате върнатите данни, трябва да добавите клауза „WHERE“.
Клаузата WHERE се използва за филтриране на резултатния набор, върнат от SQL заявка. Ето как работи клаузата WHERE:
- В клаузата WHERE можете да посочите „израз“.
- Този израз ще бъде оценен за всеки ред, върнат от таблицата(ите), посочени в клаузата FROM.
- Изразът ще бъде оценен като булев израз с резултат true, false или null.
- След това ще бъдат върнати само редове, за които изразът е оценен с истинска стойност, а тези с грешни или нулеви резултати ще бъдат игнорирани и няма да бъдат включени в набора от резултати.
- За да филтрирате набора от резултати с помощта на клаузата 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, както следва:
|
Отбележи, че, 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 Оператори за съвпадение на шаблони
"КАТО” – използва се за съпоставяне на образец. Използвайки „като“, можете да търсите стойности, които съответстват на модел, зададен с помощта на заместващ знак.
Операндът отляво може да бъде стойност на низов литерал или низова колона. Моделът може да бъде определен, както следва:
- Съдържа шаблон. например, Име на ученик КАТО „%a%“ – това ще търси имена на студенти, които съдържат буквата „a“ на която и да е позиция в колоната StudentName.
- Започва с шаблона. Например „Име на ученик КАТО 'a%'” – търсене на имената на учениците, които започват с буквата „а”.
- Завършва с шарката. Например „Име на студент КАТО „%a“” – Търсете имената на учениците, които завършват с буквата „а”.
- Съпоставяне на всеки отделен знак в низ с помощта на буквата за подчертаване „_“. Например „Име на ученик КАТО „J___“” – Търсене на имена на ученици с дължина 4 знака. Трябва да започва с буквата „J“ и може да има още три знака след буквата „J“.
Примери за съвпадение на шаблони:
- Вземете имена на ученици, които започват с буквата „j“:
SELECT StudentName FROM Students WHERE StudentName LIKE 'j%';
Резултат:
- Вземете имената на учениците, завършващи с буквата "y":
SELECT StudentName FROM Students WHERE StudentName LIKE '%y';
Резултат:
- Вземете имена на ученици, които съдържат буквата „n“:
SELECT StudentName FROM Students WHERE StudentName LIKE '%n%';
Резултат:
„ГЛОБ“ – е еквивалентен на оператора 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
Логически оператор, който комбинира един или повече изрази, така че ако един от комбинираните оператори даде истина, тогава той ще върне истина. Ако обаче всички изрази дадат false, той ще върне false.
Пример:
Следната заявка ще търси студенти, които имат StudentId > 5 или StudentName започва с буквата N, върнатите студенти трябва да отговарят на поне едно от условията:
SELECT * FROM Students WHERE (StudentId > 5) OR (StudentName LIKE '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:
SELECT * FROM Students WHERE StudentId BETWEEN 5 AND 8;
Това ще даде само на учениците с идентификатори 5, 6, 7 и 8:
SQLite IN
Взема един операнд и списък от операнди. Ще върне истина, ако стойността на първия операнд е равна на една от стойностите на операндите от списъка. Операторът 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 (Id на студент = 2) ИЛИ (Id на студент = 4) ИЛИ (Id на студент = 6) ИЛИ (Id на студент = 8);"
Като този:
SQLite НЕ В
Операндът “NOT IN” е противоположен на оператора IN. Но със същия синтаксис; отнема един операнд и списък от операнди. Ще върне истина, ако стойността на първия операнд не е равна на една от стойностите на операндите от списъка. т.е. ще върне true (0), ако списъкът с операнди не съдържа първия операнд. Подобно на това: „колона НЕ В(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 НЕ
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. Например „LIMIT 4 OFFSET 4” ще игнорира първите 4 реда и ще върне 4 реда, започвайки от петия ред, така че ще получите редове 5,6,7 и 8.
Имайте предвид, че клаузата OFFSET не е задължителна, можете да я напишете като „LIMIT 4, 4” и ще ви даде точните резултати.
Пример:
В следващия пример ще върнем само 3 студента, като започнем от идентификационния номер на ученик 5, като използваме заявката:
SELECT * FROM Students LIMIT 4,3;
Това ще ви даде само трима ученика, започвайки от ред 5. Така че ще ви даде редовете с StudentId 5, 6 и 7:
Премахване на дубликати
Ако вашата SQL заявка върне дублирани стойности, можете да използвате „DISTINCT”, за да премахнете тези дубликати и да върнете различни стойности. Можете да посочите повече от една колона след работата на ключа DISTINCT.
Пример:
Следната заявка ще върне дублиращи се „стойности за име на отдел“: Тук имаме дублирани стойности с имена IT, Physics и Arts.
SELECT d.DepartmentName FROM Students AS s INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
Това ще ви даде дублирани стойности за името на отдела:
Забележете как има дублирани стойности за името на отдела. Сега ще използваме ключовата дума DISTINCT със същата заявка, за да премахнем тези дубликати и да получим само уникални стойности. като това:
SELECT DISTINCT d.DepartmentName FROM Students AS s INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
Това ще ви даде само три уникални стойности за колоната с име на отдел:
Агрегат
SQLite Агрегатите са вградени функции, дефинирани в SQLite който ще групира множество стойности от множество редове в една стойност.
Ето агрегатите, поддържани от SQLite:
SQLite AVG()
Върна средната стойност за всички x стойности.
Пример:
В следващия пример ще получим средната оценка, която учениците трябва да получат от всички изпити:
SELECT AVG(Mark) FROM Marks;
Това ще ви даде стойността „18.375“:
Тези резултати идват от сумирането на всички стойности на оценки, разделени на техния брой.
COUNT() – COUNT(X) или COUNT(*)
Връща общия брой показвания на стойността x. И ето някои опции, които можете да използвате с COUNT:
- COUNT(x): Брои само x стойности, където x е име на колона. Той ще игнорира NULL стойностите.
- COUNT(*): Преброяване на всички редове от всички колони.
- COUNT (DISTINCT x): Можете да посочите DISTINCT ключова дума преди x, която ще получи броя на отделните стойности на x.
Пример
В следващия пример ще получим общия брой отдели с COUNT(DepartmentId), COUNT(*) и COUNT(DISTINCT DepartmentId) и как те се различават:
SELECT COUNT(DepartmentId), COUNT(DISTINCT DepartmentId), COUNT(*) FROM Students;
Това ще ви даде:
Както следва:
- COUNT(DepartmentId) ще ви даде броя на всички идентификатори на отдели и ще игнорира нулевите стойности.
- COUNT(DISTINCT DepartmentId) ви дава отделни стойности на DepartmentId, които са само 3. Кои са трите различни стойности на името на отдела. Забележете, че има 8 стойности на име на отдел в името на студента. Но само различните три стойности, които са математика, ИТ и физика.
- COUNT(*) преброява броя на редовете в таблицата на учениците, които са 10 реда за 10 ученика.
GROUP_CONCAT() – GROUP_CONCAT(X) или GROUP_CONCAT(X,Y)
Агрегираната функция GROUP_CONCAT обединява множество стойности в една стойност със запетая, за да ги раздели. Има следните опции:
- GROUP_CONCAT(X): Това ще свърже цялата стойност на x в един низ, като запетая „,“ се използва като разделител между стойностите. NULL стойностите ще бъдат игнорирани.
- GROUP_CONCAT(X, Y): Това ще свърже стойностите на x в един низ, като стойността на y се използва като разделител между всяка стойност вместо разделителя по подразбиране ','. NULL стойностите също ще бъдат игнорирани.
- GROUP_CONCAT(DISTINCT X): Това ще свърже всички отделни стойности на x в един низ, като запетая „,“ се използва като разделител между стойностите. NULL стойностите ще бъдат игнорирани.
GROUP_CONCAT(Име на отдел) Пример
Следващата заявка ще свърже всички стойности на името на отдела от студентите и таблицата на отделите в един низ, разделен със запетая. Така че вместо да връща списък със стойности, по една стойност на всеки ред. Той ще върне само една стойност на един ред, като всички стойности са разделени със запетая:
SELECT GROUP_CONCAT(d.DepartmentName) FROM Students AS s INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
Това ще ви даде:
Това ще ви даде списък със стойности на имената на 8 отдела, свързани в един низ, разделени със запетая.
GROUP_CONCAT(DISTINCT DepartmentName) Пример
Следната заявка ще свърже отделните стойности на името на отдела от таблицата студенти и отдели в един низ, разделен със запетая:
SELECT GROUP_CONCAT(DISTINCT d.DepartmentName) FROM Students AS s INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
Това ще ви даде:
Забележете как резултатът е различен от предишния резултат; върнати са само три стойности, които са имената на отделните отдели, а дублиращите се стойности са премахнати.
GROUP_CONCAT(DepartmentName ,'&') Пример
Следната заявка ще свърже всички стойности на колоната с името на отдела от таблицата студенти и отдели в един низ, но със знака „&“ вместо запетая като разделител:
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 са NULL. Докато MIN(X) ви връща най-малката стойност от X стойностите. MIN ще върне NULL стойност, ако всички стойности на X са NULL.
Пример
В следващата заявка ще използваме функциите MIN и MAX, за да получим най-високата оценка и най-ниската оценка от „Marks” таблица:
SELECT MAX(Mark), MIN(Mark) FROM Marks;
Това ще ви даде:
SQLite SUM(x), Total(x)
И двете ще върнат сумата от всички x стойности. Но те се различават по следното:
- SUM ще върне null, ако всички стойности са null, но Total ще върне 0.
- TOTAL винаги връща стойности с плаваща запетая. SUM връща цяло число, ако всички x стойности са цяло число. Ако обаче стойностите не са цяло число, той ще върне стойност с плаваща запетая.
Пример
В следващата заявка ще използваме SUM и total, за да получим сумата от всички оценки в „Marks” маси:
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.
Групиране ПО
Клаузата 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 от таблицата студенти и списъка с DepartmentId от таблицата с отдели в същата колона:
SELECT DepartmentId AS DepartmentIdUnioned FROM Students UNION SELECT DepartmentId FROM Departments;
Това ще ви даде:
Заявката връща само 5 реда, които са различните стойности на идентификатор на отдел. Обърнете внимание на първата стойност, която е нулевата стойност.
SQLite UNION ALL Пример
В следващия пример ще получим списъка с DepartmentId от таблицата студенти и списъка с DepartmentId от таблицата с отдели в същата колона:
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 обаче не бяха включени, тъй като null стойността съществува само в таблицата на учениците, а не в таблицата на отделите. И стойността 4 съществува в таблицата на отделите, а не в таблицата на учениците.
Ето защо и двете стойности NULL и 4 бяха игнорирани и не включени във върнатите стойности.
С ИЗКЛЮЧЕНИЕ
Да предположим, че ако имате два списъка с редове, списък1 и списък2, и искате редовете само от списък1, който не съществува в списък2, можете да използвате клаузата „EXCEPT“. Клаузата EXCEPT сравнява двата списъка и връща онези редове, които съществуват в списък1 и не съществуват в списък2.
Пример
В следната заявка ще изберем стойностите на DepartmentId, които съществуват в таблицата на отделите и не съществуват в таблицата на учениците:
SELECT DepartmentId FROM Departments EXCEPT SELECT DepartmentId FROM Students;
Това ще ви даде:
Заявката връща само стойността 4. Която е единствената стойност, която съществува в таблицата на отделите и не съществува в таблицата на учениците.
NULL обработка
"NULL” стойност е специална стойност в SQLite. Използва се за представяне на стойност, която е неизвестна или липсваща стойност. Имайте предвид, че нулевата стойност е напълно различна от „0” или празна стойност „”. Тъй като 0 и празната стойност е известна стойност, обаче, нулевата стойност е неизвестна.
Стойностите NULL изискват специална обработка SQLite, сега ще видим как да обработваме стойностите NULL.
Търсене на NULL стойности
Не можете да използвате нормалния оператор за равенство (=), за да търсите нулевите стойности. Например следната заявка търси студенти, които имат нулева стойност на DepartmentId:
SELECT * FROM Students WHERE DepartmentId = NULL;
Тази заявка няма да даде резултат:
Тъй като стойността NULL не е равна на никоя друга стойност, включваща самата стойност null, това е причината да не върне никакъв резултат.
- Въпреки това, за да накарате заявката да работи, трябва да използвате „Е НУЛЕВ“ оператор за търсене на нулеви стойности, както следва:
SELECT * FROM Students WHERE DepartmentId IS NULL;
Това ще ви даде:
Заявката ще върне онези студенти, които имат нулева стойност на DepartmentId.
- Ако искате да получите тези стойности, които не са нула, тогава трябва да използвате „НЕ Е NULL” оператор като този:
SELECT * FROM Students WHERE DepartmentId IS NOT NULL;
Това ще ви даде:
Заявката ще върне тези студенти, които нямат NULL DepartmentId стойност.
Условни резултати
Ако имате списък със стойности и искате да изберете някоя от тях въз основа на определени условия. За това условието за тази конкретна стойност трябва да е вярно, за да бъде избрано.
CASE изразът ще оцени този списък от условия за всички стойности. Ако условието е вярно, то ще върне тази стойност.
Например, ако имате колона „Оценка“ и искате да изберете текстова стойност въз основа на стойността на оценката, както следва:
– „Отличен“, ако оценката е по-висока от 85.
– „Много добър“, ако оценката е между 70 и 85.
– „Добър“, ако оценката е между 60 и 70.
След това можете да използвате израза CASE, за да направите това.
Това може да се използва за дефиниране на някаква логика в клаузата SELECT, така че да можете да изберете определени резултати в зависимост от определени условия, като оператор if например.
Операторът CASE може да бъде дефиниран с различни синтаксиси, както следва:
- Можете да използвате различни условия:
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN condition3 THEN result3 … ELSE resultn END
- Или можете да използвате само един израз и да поставите различни възможни стойности, от които да избирате:
CASE expression WHEN value1 THEN result1 WHEN value2 THEN result2 WHEN value3 THEN result3 … ELSE restuln END
Имайте предвид, че клаузата ELSE не е задължителна.
Пример
В следващия пример ще използваме CASE израз със NULL стойност в колоната с идентификатор на отдел в таблицата Студенти, за да се покаже текстът „Без отдел“, както следва:
SELECT StudentName, CASE WHEN DepartmentId IS NULL THEN 'No Department' ELSE DepartmentId END AS DepartmentId FROM Students;
- Операторът CASE ще провери стойността на DepartmentId дали е нула или не.
- Ако е стойност NULL, тогава ще избере литералната стойност „Без отдел“ вместо стойността DepartmentId.
- Ако не е нулева стойност, тогава ще избере стойността на колоната 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 и му дадохме името „Всички отдели“. Този 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 студенти.
Резултатът ще бъде както следва:
Oбобщение
Това беше въведение в писането SQLite заявки и основите на заявките към базата данни и как можете да филтрирате върнатите данни. Вече можете да напишете своя собствена SQLite запитвания.