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

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

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

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

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

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

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

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

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

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

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

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

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

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

SELECT *

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SELECT *, 'USA' AS Country FROM Students;

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

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

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

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

Псевдонимът е ново име за колоната, което ви позволява да изберете колоната с ново име. Псевдонимите на колоните се задават с помощта на ключовата дума „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 операторът за конкатенация '||'

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;

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

SQLite CAST OperaTor

Резултатът е следният:

  • 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;

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

SQLite аритметика Operaтори

Забележете как използвахме оператор SELECT без клауза FROM тук. И това е разрешено SQLite стига да избираме буквални стойности.

SQLite Сравнителни оператори

Сравнете два операнда един с друг и върнете true или false, както следва:

  • "<” – връща true, ако левият операнд е по-малък от десния.
  • "<=” – връща true, ако левият операнд е по-малък или равен на десния операнд.
  • ">” – връща true, ако левият операнд е по-голям от десния операнд.
  • ">=” – връща true, ако левият операнд е по-голям или равен на десния операнд.
  • "="А"==” – връща true, ако двата операнда са равни. Обърнете внимание, че и двата оператора са еднакви и няма разлика между тях.
  • "!="А"<>” – връща true, ако двата операнда не са равни. Обърнете внимание, че и двата оператора са еднакви и няма разлика между тях.

Отбележи, че, SQLite изразява истинската стойност с 1 и невярната стойност с 0.

Пример:

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

Това ще даде нещо подобно:

SQLite сравнение Operaтори

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

"КАТО” – използва се за съпоставяне на образец. Използвайки „като“, можете да търсите стойности, които съответстват на модел, зададен с помощта на заместващ знак.

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

  • Съдържа шаблон. например, Име на ученик КАТО „%a%“ – това ще търси имена на студенти, които съдържат буквата „a“ на която и да е позиция в колоната StudentName.
  • Започва с шаблона. Например „Име на ученик КАТО 'a%'” – търсене на имената на учениците, които започват с буквата „а”.
  • Завършва с шарката. Например „Име на студент КАТО „%a“” – Търсете имената на учениците, които завършват с буквата „а”.
  • Съпоставяне на всеки отделен знак в низ с помощта на буквата за подчертаване „_“. Например „Име на ученик КАТО „J___“” – Търсене на имена на ученици с дължина 4 знака. Трябва да започва с буквата „J“ и може да има още три знака след буквата „J“.

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

  1. Вземете имена на ученици, които започват с буквата „j“:
    SELECT StudentName FROM Students WHERE StudentName LIKE 'j%';

    Резултат:

    SQLite Съвпадащ модел Operaтори

  2. Вземете имената на учениците, завършващи с буквата "y":
    SELECT StudentName FROM Students WHERE StudentName LIKE '%y';

    Резултат:

    SQLite Съвпадащ модел Operaтори

  3. Вземете имена на ученици, които съдържат буквата „n“:
    SELECT StudentName FROM Students WHERE StudentName LIKE '%n%';

    Резултат:

    SQLite Съвпадащ модел Operaтори

„ГЛОБ“ – е еквивалентен на оператора LIKE, но GLOB е чувствителен към главни и малки букви, за разлика от оператора LIKE. Например следните две команди ще върнат различни резултати:

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

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

SQLite Съвпадащ модел Operaтори

  • Първият израз връща 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 И OperaTor

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

SQLite OR

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

Пример:

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

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

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

SQLite OR OperaTor

Като изход, в горната екранна снимка, това ще ви даде името на ученик с буква „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 МЕЖДУ OperaTor

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);

Като този:

SQLite IN OperaTor

Предишната заявка ще даде точния резултат като следната заявка, тъй като те са еквивалентни:

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 IN OperaTor

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);

Подобно на това

SQLite НЕ В OperaTor

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

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

Като този:

SQLite НЕ В OperaTor

В горната екранна снимка,

Използвахме множество неравни оператори “<>”, за да получим списък със студенти, които не са равни на нито един от следните идентификатори 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);

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

SQLite СЪЩЕСТВУВА OperaTor

Само трите отдела“ИТ, физика и изкуства” ще бъдат върнати. И името на отдела "Математически” няма да се върне, защото няма студент в този отдел, така че идентификаторът на отдела не съществува в таблицата студенти. Ето защо операторът 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 НЕ OperaTor

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

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

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“:

Агрегат:SQLite AVG()

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

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() – COUNT(X) или 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)
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

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

Общо: GROUP_CONCAT() – GROUP_CONCAT(X) или GROUP_CONCAT(X,Y)

Това ще ви даде списък със стойности на имената на 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;

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

Aggregate:GROUP_CONCAT(DISTINCT DepartmentName) Пример

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

GROUP_CONCAT(DepartmentName ,'&') Пример

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

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

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

Aggregate:GROUP_CONCAT(DepartmentName ,'&') Пример

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

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 MAX() & MIN()

SQLite SUM(x), Total(x)

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

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

Пример

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

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

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

Агрегат:SQLite SUM(x), Total(x)

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

Пример за разлика между SUM и TOTAL:

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

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

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

Агрегат: Разлика между SUM и TOTAL Пример

Имайте предвид, че няма оценки за 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;

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

Групиране BY:клауза HAVING

Клаузата 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“, за да можем да се позоваваме на колоните, върнати от него в заявката.

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

SQLite Заявка и подзаявка: Използване на подзаявка в клаузата FROM

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

  • 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.

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

SQLite Заявка и подзаявка: Използване на подзаявка в клаузата WHERE

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

комплект Operaции – UNION, Intersect

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

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

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

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

Обърнете внимание, че името на колоната ще бъде името на колоната, указано в първия оператор SELECT.

СЪЮЗ Пример

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

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

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

комплект Operaции - СЪЮЗ Пример

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

SQLite UNION ALL Пример

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

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

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

комплект Operaции - СЪЮЗ Пример

Заявката ще върне 14 реда, 10 реда от таблицата студенти и 4 от таблицата отдели. Имайте предвид, че има дубликати във върнатите стойности. Също така имайте предвид, че името на колоната е посоченото в първия оператор SELECT.

Сега нека видим как UNION all ще даде различни резултати, ако заменим UNION ALL с UNION:

SQLite ПРЕКРЕСТИ

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

Пример

В следната заявка ще изберем стойностите на DepartmentId, които съществуват в таблиците Students и Departments в колоната DepartmentId:

SELECT DepartmentId FROM Students
Intersect
SELECT DepartmentId FROM Departments;

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

комплект Operaции - СЕЧАТ

Заявката връща само три стойности 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;

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

комплект Operaции - ОСВЕН

Заявката връща само стойността 4. Която е единствената стойност, която съществува в таблицата на отделите и не съществува в таблицата на учениците.

NULL обработка

"NULL” стойност е специална стойност в SQLite. Използва се за представяне на стойност, която е неизвестна или липсваща стойност. Имайте предвид, че нулевата стойност е напълно различна от „0” или празна стойност „”. Тъй като 0 и празната стойност е известна стойност, обаче, нулевата стойност е неизвестна.

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

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

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

SELECT * FROM Students WHERE DepartmentId = NULL;

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

NULL обработка

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

  • Въпреки това, за да накарате заявката да работи, трябва да използвате „Е НУЛЕВ“ оператор за търсене на нулеви стойности, както следва:
SELECT * FROM Students WHERE DepartmentId IS NULL;

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

NULL обработка

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

  • Ако искате да получите тези стойности, които не са нула, тогава трябва да използвате „НЕ Е NULL” оператор като този:
SELECT * FROM Students WHERE DepartmentId IS NOT NULL;

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

NULL обработка

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

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

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

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

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

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

– „Много добър“, ако оценката е между 70 и 85.

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

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

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

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

  1. Можете да използвате различни условия:
CASE 
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  WHEN condition3 THEN result3
  …
  ELSE resultn
END
  1. Или можете да използвате само един израз и да поставите различни възможни стойности, от които да избирате:
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 запитвания.