SQLite Zapytanie: Wybierz, Gdzie, LIMIT, PRZESUNIĘCIE, Liczba, Grupuj według

Poniższe sekcje opisują odczytywanie danych za pomocą instrukcji SELECT i FROM, filtrowanie wierszy za pomocą instrukcji WHERE i jej operatorów, porządkowanie i ograniczanie wyników, funkcje agregujące, instrukcje GROUP BY i HAVING, podzapytania, operacje na zbiorach, obsługę wartości NULL, wyrażenia CASE oraz typowe wyrażenia tabelowe.

Pisać zapytań SQL w sposób SQLite bazy danych, musisz wiedzieć, jak działają klauzule SELECT, FROM, WHERE, GROUP BY, ORDER BY i LIMIT i jak z nich korzystać.

W tym samouczku dowiesz się, jak używać tych klauzul i jak je pisać SQLite klauzule.

Odczyt danych za pomocą opcji Wybierz

Klauzula SELECT jest główną instrukcją używaną do wysyłania zapytań SQLite Baza danych. W klauzuli SELECT określasz, co wybrać. Ale przed klauzulą ​​Select zobaczmy, skąd możemy wybierać dane za pomocą klauzuli FROM.

Klauzula FROM służy do określania, skąd chcesz wybrać dane. W klauzuli from możesz określić jedną lub więcej tabel lub podzapytań, z których chcesz wybrać dane, jak zobaczymy później w samouczkach.

Należy pamiętać, że we wszystkich poniższych przykładach należy uruchomić plik sqlite3.exe i nawiązać połączenie z przykładową bazą danych w trybie przepływowym:

Krok 1) W tym etapie,

Otwórz Mój komputer i przejdź do następującego katalogu „C:\sqlite”, a następnie otwórz „sqlite3.exe“:

Krok 2) Otwórz bazę danych „TutorialsSampleDB.db” za pomocą następującego polecenia:

Teraz możesz uruchomić dowolny typ zapytania w bazie danych.

W klauzuli SELECT możesz wybrać nie tylko nazwę kolumny, ale masz też wiele innych opcji, aby określić, co wybrać. Jak poniżej:

WYBIERZ *

To polecenie wybierze wszystkie kolumny ze wszystkich tabel, do których istnieją odniesienia (lub podzapytania) w klauzuli FROM. Na przykład:

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

Spowoduje to zaznaczenie wszystkich kolumn zarówno z tabel studentów, jak i tabel wydziałów:

WYBIERZ nazwę tabeli.*

Spowoduje to wybranie wszystkich kolumn tylko z tabeli „nazwa_tabeli”. Na przykład:

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

Spowoduje to wybranie wszystkich kolumn tylko z tabeli uczniów:

Wartość dosłowna

Wartość literałowa to stała wartość, którą można określić w instrukcji SELECT. Wartości literałowe można używać normalnie w taki sam sposób, w jaki używa się nazw kolumn w klauzuli SELECT. Te wartości literałowe będą wyświetlane dla każdego wiersza z wierszy zwróconych przez zapytanie SQL.

Oto kilka przykładów różnych wartości literału, które można wybrać:

• Literał liczbowy – liczby w dowolnym formacie, np. 1, 2.55, … itd.
• Literały łańcuchowe – dowolny ciąg „USA”, „to jest przykładowy tekst” itp.
• NULL – wartość NULL.
• Current_TIME – wyświetli aktualny czas.
• CURRENT_DATE – wyświetli aktualną datę.

Może to być przydatne w niektórych sytuacjach, gdy trzeba wybrać stałą wartość dla wszystkich zwracanych wierszy. Na przykład, jeśli chcesz wybrać wszystkich uczniów z tabeli Studenci, mając nową kolumnę o nazwie kraj zawierającą wartość „USA”, możesz to zrobić:

SELECT *, 'USA' AS Country FROM Students;

Spowoduje to wyświetlenie wszystkich kolumn uczniów oraz nowej kolumny „Kraj”, takiej jak ta:

Należy pamiętać, że ta nowa kolumna Kraj nie jest w rzeczywistości nową kolumną dodaną do tabeli. Jest to kolumna wirtualna, utworzona w zapytaniu w celu wyświetlenia wyników i nie zostanie utworzona w tabeli.

Imiona i pseudonimy

Alias ​​to nowa nazwa kolumny, która umożliwia wybranie kolumny o nowej nazwie. Aliasy kolumn są określane za pomocą słowa kluczowego „AS”.

Na przykład, jeśli chcesz wybrać kolumnę StudentName, która ma zostać zwrócona z „Nazwisko studenta” zamiast „Nazwa ucznia”, możesz nadać jej alias w następujący sposób:

SELECT StudentName AS 'Student Name' FROM Students;

Spowoduje to wyświetlenie imion uczniów o nazwie „Nazwisko ucznia” zamiast „Nazwa ucznia”, w następujący sposób:

Należy pamiętać, że nazwa kolumny to nadal „StudentName”; kolumna StudentName jest nadal taka sama, nie zmienia się pod wpływem aliasu.

Alias ​​nie zmieni nazwy kolumny; po prostu zmieni nazwę wyświetlaną w klauzuli SELECT.

Pamiętaj też, że słowo kluczowe „AS” jest opcjonalne, możesz umieścić nazwę aliasu bez niego, na przykład tak:

SELECT StudentName 'Student Name' FROM Students;

Da ci dokładnie taki sam wynik, jak poprzednie zapytanie:

Możesz także nadawać aliasy tabelom, a nie tylko kolumnom. Z tym samym słowem kluczowym „AS”. Możesz na przykład to zrobić:

SELECT s.* FROM Students AS s;

To wyświetli wszystkie kolumny w tabeli. Studenci:

Może to być bardzo przydatne, jeśli dołączasz więcej niż jedną tabelę; zamiast powtarzać pełną nazwę tabeli w zapytaniu, możesz nadać każdej tabeli krótką nazwę aliasu. Na przykład w poniższym zapytaniu:

SELECT Students.StudentName, Departments.DepartmentName
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

To zapytanie wybierze każde nazwisko studenta z tabeli „Studenci” wraz z nazwą jego wydziału z tabeli „Wydziały”:

Jednak to samo zapytanie można zapisać w następujący sposób:

SELECT s.StudentName, d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Nadaliśmy tabeli Students alias „s”, a tabeli departments alias „d”. Następnie zamiast używać pełnej nazwy tabeli, użyliśmy ich aliasów do odwoływania się do nich. Połączenie INNER JOIN łączy dwie lub więcej tabel za pomocą warunku. W naszym przykładzie połączyliśmy tabelę Students z tabelą Departments za pomocą kolumny DepartmentId. Szczegółowe wyjaśnienie połączenia INNER JOIN znajduje się również w sekcji „SQLite Łączy" instruktaż.

To da ci dokładny wynik jak w poprzednim zapytaniu:

WHERE

Pisanie zapytań SQL przy użyciu samej klauzuli SELECT i klauzuli FROM, jak widzieliśmy w poprzedniej sekcji, wyświetli wszystkie wiersze z tabel. Jeśli jednak chcesz filtrować zwracane dane, musisz dodać klauzulę „WHERE”.

Klauzula WHERE służy do filtrowania zestawu wyników zwróconego przez zapytanie SQL. Oto jak działa klauzula WHERE:

• W klauzuli WHERE możesz określić „wyrażenie”.
• To wyrażenie zostanie obliczone dla każdego wiersza zwróconego z tabel określonych w klauzuli FROM.
• Wyrażenie zostanie ocenione jako wyrażenie logiczne, a wynikiem będzie prawda, fałsz lub null.
• Następnie zwrócone zostaną tylko wiersze, dla których wyrażenie zostało ocenione z wartością true, a te z wynikami false lub null zostaną zignorowane i nieuwzględnione w zestawie wyników.

Aby filtrować zbiór wyników za pomocą klauzuli WHERE, należy użyć wyrażeń i operatorów.

Lista operatorów w SQLite i jak ich używać

W poniższej sekcji wyjaśnimy, jak można filtrować za pomocą wyrażeń i operatorów.

Wyrażenie to jedna lub więcej wartości literowych lub kolumn połączonych ze sobą za pomocą operatora.

Pamiętaj, że możesz używać wyrażeń zarówno w klauzuli SELECT, jak i w klauzuli WHERE.

W poniższych przykładach wypróbujemy wyrażenia i operatory zarówno w klauzuli select, jak i klauzuli WHERE. Aby pokazać, jak działają.

Istnieją różne typy wyrażeń i operatorów, które można określić w następujący sposób:

SQLite operator konkatenacji „||”

Ten operator służy do łączenia ze sobą jednej lub większej liczby wartości literałowych lub kolumn. Wygeneruje jeden ciąg wyników ze wszystkich połączonych wartości literałowych lub kolumn. Na przykład:

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

Spowoduje to połączenie w nowy alias „StudentIdWithName“:

• Wartość ciągu literowego „Identyfikator z nazwą:”
• z wartością kolumny „StudentId” i
• z wartością z kolumny „StudentName”

SQLite Operator CAST:

Operator CAST służy do konwersji wartości z typ danych do innego typu danych.

Na przykład, jeśli masz wartość liczbową zapisaną jako ciąg znaków, taki jak „'12.5'” i chcesz ją przekonwertować na wartość liczbową, możesz użyć operatora CAST, aby to zrobić, np. „CAST('12.5' ​​AS REAL)”. Jeśli masz wartość dziesiętną, taką jak 12.5, i potrzebujesz uzyskać tylko część całkowitą, możesz ją rzutować na liczbę całkowitą, np. „CAST(12.5 AS INTEGER)”.

Przykład

W poniższym poleceniu spróbujemy przekonwertować różne wartości na inne typy danych:

SELECT CAST('12.5' AS REAL) ToReal, CAST(12.5 AS INTEGER) AS ToInteger;

To da ci:

Wynik jest następujący:

• CAST('12.5' AS REAL) – wartość '12.5' jest wartością typu string, zostanie zamieniona na wartość RZECZYWISTĄ.
• CAST(12.5 AS INTEGER) – wartość 12.5 jest wartością dziesiętną, zostanie przeliczona na liczbę całkowitą. Część dziesiętna zostanie obcięta i będzie wynosić 12.

SQLite Arytmetyka Operatory:

Weź dwie lub więcej liczbowych wartości literałowych lub liczbowych kolumn i zwróć jedną wartość liczbową. Operatory arytmetyczne obsługiwane w SQLite należą:

• Dodawanie „+” – podaje sumę dwóch operandów.
• Podłożetraccja „–” – subtracłączy dwa operandy i daje wynik w postaci różnicy.
• Mnożenie „*” – iloczyn dwóch operandów.
• Przypomnienie (modulo) „%” – zwraca resztę z dzielenia jednego operandu przez drugi operand.
• Dzielenie „/” – zwraca iloraz wyników dzielenia operandu lewego przez operand prawy.

Przykład:

W poniższym przykładzie wypróbujemy pięć operatorów arytmetycznych z dosłownymi wartościami liczbowymi w tej samej klauzuli SELECT:

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

To da ci:

Zwróć uwagę, jak użyliśmy tutaj instrukcji SELECT bez klauzuli FROM. I to jest dozwolone SQLite o ile wybierzemy wartości literałowe.

SQLite Operatory porównania

Porównaj dwa operandy ze sobą i zwróć wartość true (prawda) lub false (fałsz) w następujący sposób:

• “<” – zwraca wartość true, jeżeli lewy operand jest mniejszy od prawego operandu.
• “<=” – zwraca wartość true, jeżeli lewy operand jest mniejszy lub równy prawemu operandowi.
• “>” – zwraca wartość true, jeżeli lewy operand jest większy od prawego operandu.
• “>=” – zwraca wartość true, jeśli lewy operand jest większy lub równy prawemu operandowi.
• „=” i „==” – zwracają wartość true, jeśli oba operandy są równe. Należy pamiętać, że oba operatory są takie same i nie ma między nimi żadnej różnicy.
• „!=” i „<>” – zwracają wartość true, jeśli oba operandy nie są równe. Należy pamiętać, że oba operatory są takie same i nie ma między nimi żadnej różnicy.

Zauważ, że SQLite wyraża wartość prawdziwą za pomocą 1 i wartość fałszywą za pomocą 0.

Przykład:

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

To da coś takiego:

SQLite Operatorzy dopasowywania wzorców

„LIKE” – służy do dopasowywania wzorców. Używając „Like”, można wyszukiwać wartości pasujące do wzorca określonego za pomocą symbolu wieloznacznego.

Operand po lewej stronie może być albo wartością literału ciągu, albo kolumną ciągu. Wzorzec można określić następująco:

• Zawiera wzorzec. Na przykład, StudentName LIKE '%a%' – spowoduje to wyszukanie imion studentów, które zawierają literę „a” w dowolnym miejscu w kolumnie StudentName.
• Rozpoczyna się od wzorca. Na przykład: „StudentName LIKE 'a%'” – wyszukaj imiona uczniów, których imiona zaczynają się na literę „a”.
• Kończy się wzorem. Na przykład: „StudentName LIKE '%a'” – wyszukaj imiona uczniów kończące się na literę „a”.
• Dopasuj dowolny pojedynczy znak w ciągu za pomocą znaku podkreślenia „_”. Na przykład „StudentName LIKE 'J___'” – wyszukaj nazwiska studentów składające się z 4 znaków. Muszą zaczynać się od litery „J” i mogą zawierać dowolne trzy znaki po literze „J”.

Przykłady dopasowania wzorca:

Uzyskaj imiona uczniów zaczynające się na literę „j”:

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

Wynik:

Uzyskaj Imiona uczniów kończą się na literę „y”:

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

Wynik:

Pobierz nazwiska uczniów zawierające literę „n”:

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

Wynik:

„GLOB” – jest odpowiednikiem operatora LIKE, ale GLOB rozróżnia wielkość liter, w przeciwieństwie do operatora LIKE. Na przykład, poniższe dwa polecenia zwrócą różne wyniki:

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

To da ci:

Pierwsze polecenie zwraca 0 (fałsz), ponieważ operator GLOB rozróżnia wielkość liter, więc 'j' nie jest równe 'J'. Jednak drugie polecenie zwróci 1 (prawda), ponieważ operator LIKE nie rozróżnia wielkości liter, więc 'j' jest równe 'J'.

Inni operatorzy:

SQLite ROLNICZE

Operator logiczny łączący jedno lub więcej wyrażeń. Zwróci wartość true tylko wtedy, gdy wszystkie wyrażenia zwrócą wartość „true”. Jednak zwróci wartość false tylko wtedy, gdy wszystkie wyrażenia zwrócą wartość „false”.

Przykład:

Poniższe zapytanie przeszuka studentów, których StudentId > 5 i StudentName zaczyna się na literę N. Zwróceni studenci muszą spełniać dwa warunki:

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

Jako wynik na powyższym zrzucie ekranu otrzymasz tylko „Nancy”. Nancy jest jedyną uczennicą, która spełnia oba warunki.

SQLite OR

Operator logiczny łączący jedno lub więcej wyrażeń, tak że jeśli jeden z połączonych operatorów zwróci wartość true, to zwróci wartość true. Jeśli jednak wszystkie wyrażenia zwrócą wartość false, zwróci wartość false.

Przykład:

Poniższe zapytanie przeszuka studentów, których StudentId > 5 lub StudentName zaczyna się na literę N. Zwróceni studenci muszą spełniać co najmniej jeden z warunków:

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

To da ci:

Jako wynik na powyższym zrzucie ekranu otrzymasz imię i nazwisko ucznia z literą „n” w nazwisku oraz identyfikator ucznia o wartości> 5.

Jak widać wynik jest inny niż w przypadku zapytania z operatorem AND.

SQLite MIĘDZY

Funkcja BETWEEN służy do wybierania wartości mieszczących się w zakresie dwóch wartości. Na przykład, wyrażenie „X BETWEEN Y AND Z” zwróci wartość true (1), jeśli wartość X znajduje się między wartościami Y i Z. W przeciwnym razie zwróci wartość false (0). Funkcja „X BETWEEN Y AND Z” jest równoważna funkcji „X >= Y AND X <= Z”, gdzie X musi być większe lub równe Y, a X mniejsze lub równe Z.

Przykład:

W poniższym przykładowym zapytaniu napiszemy zapytanie, aby uzyskać uczniów, których wartość identyfikatora mieści się w przedziale od 5 do 8:

SELECT *
FROM Students
WHERE StudentId BETWEEN 5 AND 8;

To da tylko uczniom o identyfikatorach 5, 6, 7 i 8:

SQLite IN

Przyjmuje jeden operand i listę operandów. Zwróci wartość true, jeśli wartość pierwszego operandu jest równa wartości jednego z operandów z listy. Operator IN zwraca wartość true (1), jeśli lista operandów zawiera wartość pierwszego operandu w swoich wartościach. W przeciwnym wypadku zwróci wartość false (0).

Na przykład: „col IN(x, y, z)”. Jest to równoważne z „(col=x) lub (col=y) lub (col=z)”.

Przykład:

Poniższe zapytanie wybierze tylko uczniów o identyfikatorach 2, 4, 6, 8:

SELECT *
FROM Students
WHERE StudentId IN(2, 4, 6, 8);

Jak to:

Poprzednie zapytanie da dokładnie taki sam wynik jak poniższe zapytanie, ponieważ są one równoważne:

SELECT *
FROM Students
WHERE (StudentId = 2) OR (StudentId =  4) OR (StudentId =  6) OR (StudentId = 8);

Oba zapytania dają dokładny wynik. Jednak różnica między tymi dwoma zapytaniami polega na tym, że w pierwszym zapytaniu użyliśmy operatora „IN”. W drugim zapytaniu użyliśmy wielu operatorów „OR”.

Operator IN jest równoważny użyciu wielu operatorów OR. „WHERE StudentId IN(2, 4, 6, 8)” jest równoważne z „WHERE (StudentId = 2) OR (StudentId = 4) OR (StudentId = 6) OR (StudentId = 8);”

Jak to:

SQLite NIE W

Operand „NOT IN” jest przeciwieństwem operatora IN. Jednak z tą samą składnią: przyjmuje jeden operand i listę operandów. Zwróci wartość true, jeśli wartość pierwszego operandu nie jest równa wartości jednego z operandów z listy. Oznacza to, że zwróci wartość true (0), jeśli lista operandów nie zawiera pierwszego operandu. Na przykład: „col NOT IN(x, y, z)”. Jest to równoważne z „(col<>x) AND (col<>y) AND (col<>z)”.

Przykład:

Poniższe zapytanie wybierze uczniów, których identyfikatory nie są równe żadnemu z poniższych identyfikatorów: 2, 4, 6, 8:

SELECT *
FROM Students
WHERE StudentId NOT IN(2, 4, 6, 8);

Tak

W poprzednim zapytaniu podajemy dokładny wynik jako poniższe zapytanie, ponieważ są one równoważne:

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

Jak to:

Na powyższym zrzucie ekranu

Użyliśmy wielu operatorów „<>” oznaczających nierówność, aby uzyskać listę studentów, którzy nie są równi żadnemu z następujących identyfikatorów: 2, 4, 6 ani 8. To zapytanie zwróci wszystkich studentów innych niż te z listy identyfikatorów.

SQLite ISTNIEJE

Operatory EXISTS nie przyjmują żadnych operandów; przyjmują tylko klauzulę SELECT po sobie. Operator EXISTS zwróci true (1), jeśli z klauzuli SELECT zwrócono jakieś wiersze, a zwróci false (0), jeśli z klauzuli SELECT nie zwrócono żadnych wierszy.

Przykład:

W poniższym przykładzie wybierzemy nazwę wydziału, jeśli identyfikator wydziału istnieje w tabeli studentów:

SELECT DepartmentName
FROM Departments AS d
WHERE EXISTS (SELECT DepartmentId FROM Students AS s WHERE d.DepartmentId = s.DepartmentId);

To da ci:

Zwrócone zostaną tylko trzy wydziały: „IT, Fizyka i Sztuki”. Nazwa wydziału „Matematyka” nie zostanie zwrócona, ponieważ nie ma w nim studenta, a zatem identyfikator wydziału nie istnieje w tabeli „studenci”. Dlatego operator EXISTS zignorował wydział „Matematyka”.

SQLite NIE

Reversejest wynikiem poprzedniego operatora, który następuje po nim. Na przykład:

• NIE MIĘDZY – zwróci wartość true, jeśli POMIĘDZY zwróci wartość false i odwrotnie.
• NOT LIKE – zwróci wartość true, jeśli LIKE zwróci wartość false i odwrotnie.
• NOT GLOB – zwróci wartość true, jeśli GLOB zwróci wartość false i odwrotnie.
• NIE ISTNIEJE – zwróci wartość true, jeśli ISTNIEJE zwróci wartość false i odwrotnie.

Przykład:

W poniższym przykładzie użyjemy operatora NOT z operatorem EXISTS, aby uzyskać nazwy działów, które nie istnieją w tabeli Students, co jest odwrotnym wynikiem operatora EXISTS. Tak więc wyszukiwanie zostanie przeprowadzone za pomocą DepartmentId, które nie istnieją w tabeli department.

SELECT DepartmentName
FROM Departments AS d
WHERE NOT EXISTS (SELECT DepartmentId
                  FROM Students AS s
                  WHERE d.DepartmentId = s.DepartmentId);

Wyjście:

Zwrócony zostanie tylko wydział „Matematyka”. Ponieważ wydział „Matematyka” jest jedynym wydziałem, nie istnieje on w tabeli studentów.

Ograniczanie i porządkowanie

SQLite Zamówienie

SQLite Kolejność polega na posortowaniu wyniku według jednego lub większej liczby wyrażeń. Aby uporządkować zestaw wyników, należy użyć klauzuli ORDER BY w następujący sposób:

• Najpierw musisz określić klauzulę ORDER BY.
• Na końcu zapytania należy podać klauzulę ORDER BY; po nim można podać tylko klauzulę LIMIT.
• Określ wyrażenie, według którego chcesz uporządkować dane. Wyrażenie to może być nazwą kolumny lub wyrażeniem.
• Po wyrażeniu można określić opcjonalny kierunek sortowania. Albo DESC, aby uporządkować dane malejąco, albo ASC, aby uporządkować dane rosnąco. Jeśli nie określisz żadnego z nich, dane zostaną posortowane rosnąco.
• Możesz określić więcej wyrażeń, używając znaku „,” między sobą.

Przykład

W poniższym przykładzie wybierzemy wszystkich studentów posortowanych według nazwisk, ale w kolejności malejącej, a następnie według nazwy wydziału, ale w kolejności rosnącej:

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;

To da ci:

• SQLite najpierw uporządkuje wszystkich studentów według nazwy wydziału w kolejności rosnącej
• Następnie dla każdej nazwy wydziału wszyscy studenci z tej nazwy wydziału zostaną wyświetleni w kolejności malejącej według ich nazwisk

SQLite Limit:

Możesz ograniczyć liczbę wierszy zwracanych przez zapytanie SQL, używając klauzuli LIMIT. Na przykład LIMIT 10 da ci tylko 10 wierszy i zignoruje wszystkie pozostałe wiersze.

W klauzuli LIMIT możesz wybrać określoną liczbę wierszy, zaczynając od określonej pozycji, za pomocą klauzuli OFFSET. Na przykład, polecenie „LIMIT 4 OFFSET 4” zignoruje pierwsze 4 wiersze i zwróci 4 wiersze, zaczynając od piątego, więc otrzymasz wiersze 5, 6, 7 i 8.

Należy pamiętać, że klauzula OFFSET jest opcjonalna, można ją zapisać np. „LIMIT 4, 4”, a zwróci ona dokładne wyniki.

Przykład:

W poniższym przykładzie zwrócimy tylko 3 studentów, zaczynając od identyfikatora 5, korzystając z zapytania:

SELECT * FROM Students LIMIT 4,3;

To da ci tylko trzech uczniów, zaczynając od wiersza 5. Otrzymasz więc wiersze z StudentId 5, 6 i 7:

Usuwanie duplikatów

Jeśli zapytanie SQL zwróci zduplikowane wartości, możesz użyć słowa kluczowego „DISTINCT”, aby usunąć duplikaty i zwrócić odrębne wartości. Po użyciu klawisza DISTINCT można określić więcej niż jedną kolumnę.

Przykład:

Poniższe zapytanie zwróci zduplikowane „wartości nazw działów”: Tutaj mamy zduplikowane wartości o nazwach IT, Fizyka i Sztuka.

SELECT d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Spowoduje to zduplikowanie wartości nazwy działu:

Zwróć uwagę, że w nazwie działu występują zduplikowane wartości. Teraz użyjemy słowa kluczowego DISTINCT w tym samym zapytaniu, aby usunąć te duplikaty i uzyskać tylko unikalne wartości. Lubię to:

SELECT DISTINCT d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Otrzymasz tylko trzy unikalne wartości w kolumnie nazwy działu:

Agregat

SQLite Agregaty to wbudowane funkcje zdefiniowane w SQLite która zgrupuje wiele wartości z wielu wierszy w jedną wartość.

Oto agregaty obsługiwane przez SQLite:

SQLite AVG()

Zwraca średnią dla wszystkich wartości x.

Przykład:

W poniższym przykładzie obliczymy średnią ocen, jaką uczniowie uzyskali ze wszystkich egzaminów:

SELECT AVG(Mark) FROM Marks;

To da ci wartość „18.375”:

Wyniki te pochodzą z sumy wszystkich wartości ocen podzielonej przez ich liczbę.

LICZBA() – LICZBA(X) lub LICZBA(*)

Zwraca całkowitą liczbę wystąpień wartości x. Oto kilka opcji, których możesz użyć z COUNT:

• COUNT(x): Zlicza tylko wartości x, gdzie x to nazwa kolumny. Zignoruje wartości NULL.
• COUNT(*): Policz wszystkie wiersze ze wszystkich kolumn.
• COUNT (DISTINCT x): Możesz określić słowo kluczowe DISTINCT przed x, które spowoduje zliczenie różnych wartości x.

Przykład

W poniższym przykładzie otrzymamy całkowitą liczbę działów z funkcjami COUNT(DepartmentId), COUNT(*) i COUNT(DISTINCT DepartmentId) oraz pokażemy, jak się one różnią:

SELECT COUNT(DepartmentId), COUNT(DISTINCT DepartmentId), COUNT(*) FROM Students;

To da ci:

Jak następuje:

• COUNT(DepartmentId) wyświetli liczbę wszystkich identyfikatorów wydziałów i zignoruje wartości null.
• COUNT(DISTINCT DepartmentId) daje różne wartości DepartmentId, które wynoszą tylko 3. Są to trzy różne wartości nazwy działu. Zauważ, że w nazwisku studenta znajduje się 8 wartości nazwy wydziału. Ale tylko trzy różne wartości, którymi są matematyka, informatyka i fizyka.
• COUNT(*) zlicza wiersze w tabeli uczniów, czyli 10 wierszy dla 10 uczniów.

GROUP_CONCAT() – GROUP_CONCAT(X) lub GROUP_CONCAT(X,Y)

Funkcja agregująca GROUP_CONCAT łączy wiele wartości w jedną wartość, oddzielając je przecinkiem. Ma następujące opcje:

• GROUP_CONCAT(X): Spowoduje to połączenie wszystkich wartości x w jeden ciąg znaków, z przecinkiem „,” używanym jako separator pomiędzy wartościami. Wartości NULL będą ignorowane.
• GROUP_CONCAT(X, Y): Spowoduje to połączenie wartości x w jeden ciąg, przy czym wartość y zostanie użyta jako separator między każdą wartością zamiast domyślnego separatora „,”. Wartości NULL również będą ignorowane.
• GROUP_CONCAT(DISTINCT X): Spowoduje to połączenie wszystkich odrębnych wartości x w jeden ciąg znaków, z przecinkiem „,” używanym jako separator między wartościami. Wartości NULL będą ignorowane.

GROUP_CONCAT(nazwa działu) Przykład

Następujące zapytanie połączy wszystkie wartości nazwy działu z tabeli students i departments w jeden ciąg oddzielony przecinkami. Zamiast więc zwracać listę wartości, jedną wartość w każdym wierszu. Zwróci tylko jedną wartość w jednym wierszu, a wszystkie wartości będą oddzielone przecinkami:

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

To da ci:

Otrzymasz listę wartości nazw 8 działów połączoną w jeden ciąg znaków oddzielony przecinkami.

GROUP_CONCAT(DISTINCT nazwa działu) Przykład

Poniższe zapytanie połączy różne wartości nazwy wydziału z tabeli students and departments w jeden ciąg znaków rozdzielony przecinkami:

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

To da ci:

Zwróć uwagę, jak wynik różni się od poprzedniego; zwrócone zostały tylko trzy wartości, które są nazwami odrębnych działów, a zduplikowane wartości zostały usunięte.

GROUP_CONCAT(nazwa działu ,'&') Przykład

Poniższe zapytanie połączy wszystkie wartości kolumny nazwy wydziału z tabeli students and departments w jeden ciąg, ale ze znakiem „&” zamiast przecinka jako separatorem:

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

To da ci:

Zwróć uwagę, że zamiast domyślnego znaku „” używany jest znak „&” w celu oddzielenia wartości.

SQLite MAKS() i MIN()

MAX(X) zwraca najwyższą wartość spośród wartości X. MAX zwróci wartość NULL, jeśli wszystkie wartości x mają wartość null. Natomiast MIN(X) zwraca najmniejszą wartość spośród wartości X. MIN zwróci wartość NULL, jeśli wszystkie wartości X mają wartość null.

Przykład

W poniższym zapytaniu użyjemy funkcji MIN i MAX, aby uzyskać najwyższą i najniższą ocenę z tabeli „Oceny”:

SELECT MAX(Mark), MIN(Mark) FROM Marks;

To da ci:

SQLite SUMA(x), Razem(x)

Oba zwrócą sumę wszystkich wartości x. Ale różnią się w następujący sposób:

• SUMA zwróci wartość null, jeśli wszystkie wartości mają wartość null, ale wartość Suma zwróci 0.
• TOTAL zawsze zwraca wartości zmiennoprzecinkowe. SUMA zwraca wartość całkowitą, jeśli wszystkie wartości x są liczbami całkowitymi. Jeśli jednak wartości nie są liczbą całkowitą, zwróci wartość zmiennoprzecinkową.

Przykład

W poniższym zapytaniu użyjemy funkcji SUMA i suma, aby uzyskać sumę wszystkich ocen w tabelach „Oceny”:

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

To da ci:

Jak widać, TOTAL zawsze zwraca liczbę zmiennoprzecinkową. Jednak SUMA zwraca wartość całkowitą, ponieważ wartości w kolumnie „Znak” mogą być liczbami całkowitymi.

Różnica między przykładem SUM i TOTAL:

W poniższym zapytaniu pokażemy różnicę między SUMĄ i TOTAL, gdy otrzymają SUMĘ wartości NULL:

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

To da ci:

Należy pamiętać, że dla TestId = 4 nie ma żadnych znaków, zatem dla tego testu występują wartości null. SUMA zwraca wartość null jako pustą, natomiast TOTAL zwraca 0.

Grupuj według

Klauzula GROUP BY służy do określenia jednej lub większej liczby kolumn, które zostaną użyte do grupowania wierszy w grupy. Wiersze o tych samych wartościach zostaną zebrane (ułożone) razem w grupy.

Dla każdej innej kolumny, która nie jest uwzględniona w grupowaniu według kolumn, możesz użyć dla niej funkcji agregującej.

Przykład:

Poniższe zapytanie pozwoli Ci poznać całkowitą liczbę studentów na każdym wydziale.

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;

To da ci:

Klauzula GROUPBY DepartmentName zgrupuje wszystkich studentów w grupy, po jednej dla każdej nazwy wydziału. Dla każdej grupy „wydziału” policzy znajdujących się na niej studentów.

Klauzula HAVING

Jeśli chcesz filtrować grupy zwracane przez klauzulę GROUP BY, możesz podać klauzulę „HAVING” z wyrażeniem po GROUP BY. Wyrażenie zostanie użyte do filtrowania tych grup.

Przykład

W poniższym zapytaniu wybierzemy te wydziały, na których studiuje tylko dwóch studentów:

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;

To da ci:

Klauzula HAVING COUNT(S.StudentId) = 2 odfiltruje zwrócone grupy i zwróci tylko te grupy, które zawierają dokładnie dwóch studentów. W naszym przypadku wydział sztuk pięknych ma 2 studentów, więc jest to wyświetlane w wynikach.

SQLite Zapytanie i podzapytanie

Wewnątrz dowolnego zapytania możesz użyć innego zapytania w SELECT, INSERT, DELETE, UPDATE lub w innym podzapytaniu.

To zagnieżdżone zapytanie nazywa się podzapytaniem. Zobaczymy teraz kilka przykładów użycia podzapytań w klauzuli SELECT. Jednak w samouczku Modyfikowanie danych zobaczymy, jak możemy używać podzapytań z instrukcjami INSERT, DELETE i UPDATE.

Użycie podzapytania w przykładzie klauzuli FROM

W poniższym zapytaniu dodamy podzapytanie wewnątrz klauzuli 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;

Zapytanie:

   SELECT StudentId, Mark
   FROM Tests AS t
   INNER JOIN Marks AS m ON t.TestId = m.TestId

Powyższe zapytanie nazywa się tutaj podzapytaniem, ponieważ jest zagnieżdżone w klauzuli FROM. Zauważ, że nadaliśmy mu alias „t”, abyśmy mogli w zapytaniu odwoływać się do zwracanych z niego kolumn.

To zapytanie da Ci:

Zatem w naszym przypadku

• s.StudentName jest wybierane z głównego zapytania, które podaje nazwiska uczniów i
• t.Mark jest wybrany z podzapytania; co daje oceny uzyskane przez każdego z tych uczniów

Użycie podzapytania w przykładzie klauzuli WHERE

W poniższym zapytaniu dodamy podzapytanie do klauzuli WHERE:

SELECT DepartmentName
FROM Departments AS d
WHERE NOT EXISTS (SELECT DepartmentId
                  FROM Students AS s
                  WHERE d.DepartmentId = s.DepartmentId);

Zapytanie:

SELECT DepartmentId
FROM Students AS s
WHERE d.DepartmentId = s.DepartmentId

Powyższe zapytanie jest tutaj nazywane podzapytaniem, ponieważ jest zagnieżdżone w klauzuli WHERE. Podzapytanie zwróci wartości DepartmentId, które zostaną użyte przez operator NOT EXISTS.

To zapytanie da Ci:

W powyższym zapytaniu wybraliśmy wydział, na którym nie studiuje żaden student. Który tutaj jest wydział „Matematyka”.

Zestaw Operacje – UNIA, Intersect

SQLite obsługuje następujące operacje SET:

UNIA I UNIA WSZYSTKO

Łączy jeden lub więcej zestawów wyników (grupę wierszy) zwróconych z wielu instrukcji SELECT w jeden zestaw wyników.

UNIA zwróci różne wartości. Jednakże UNION ALL nie będzie i będzie zawierać duplikaty.

Należy pamiętać, że nazwą kolumny będzie nazwa kolumny określona w pierwszej instrukcji SELECT.

Przykład UNII

W poniższym przykładzie pobierzemy listę DepartmentId z tabeli students oraz listę DepartmentId z tabeli departments w tej samej kolumnie:

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

To da ci:

Zapytanie zwraca tylko 5 wierszy, które są odrębnymi wartościami identyfikatorów działów. Zwróć uwagę na pierwszą wartość, która jest wartością null.

SQLite UNIA WSZYSTKIE Przykład

W poniższym przykładzie pobierzemy listę DepartmentId z tabeli students oraz listę DepartmentId z tabeli departments w tej samej kolumnie:

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

To da ci:

Zapytanie zwróci 14 wierszy, 10 wierszy z tabeli studentów i 4 z tabeli działów. Należy pamiętać, że zwracane wartości zawierają duplikaty. Należy również pamiętać, że nazwa kolumny została określona w pierwszej instrukcji SELECT.

Zobaczmy teraz, jak UNION all da różne wyniki, jeśli zastąpimy UNION ALL UNION:

SQLite KRZYŻOWAĆ

Zwraca wartości istniejące w obu połączonych zestawach wyników. Wartości istniejące w jednym z połączonych zestawów wyników zostaną zignorowane.

Przykład

W poniższym zapytaniu wybierzemy wartości DepartmentId, które istnieją w tabelach Students i Departments w kolumnie DepartmentId:

SELECT DepartmentId FROM Students
Intersect
SELECT DepartmentId FROM Departments;

To da ci:

Zapytanie zwraca tylko trzy wartości 1, 2 i 3. Są to wartości istniejące w obu tabelach.

Jednakże wartości null i 4 nie zostały uwzględnione, ponieważ wartość null istnieje tylko w tabeli studentów, a nie w tabeli wydziałów. Wartość 4 istnieje w tabeli wydziałów, a nie w tabeli studentów.

Dlatego zarówno wartości NULL, jak i 4 zostały zignorowane i nie uwzględnione w zwracanych wartościach.

Z WYJĄTKIEM

Załóżmy, że masz dwie listy wierszy, list1 i list2, i chcesz wiersze tylko z list1, które nie istnieją w list2, możesz użyć klauzuli „EXCEPT”. Klauzula EXCEPT porównuje dwie listy i zwraca wiersze, które istnieją w list1 i nie istnieją w list2.

Przykład

W poniższym zapytaniu wybierzemy wartości DepartmentId, które istnieją w tabeli departments i nie istnieją w tabeli students:

SELECT DepartmentId FROM Departments
EXCEPT
SELECT DepartmentId FROM Students;

To da ci:

Zapytanie zwraca tylko wartość 4. Jest to jedyna wartość istniejąca w tabeli wydziałów i nieistniejąca w tabeli studentów.

Obsługa NULL

Wartość „NULL” jest wartością specjalną w SQLiteSłuży do reprezentowania wartości nieznanej lub brakującej. Należy pamiętać, że wartość null to zupełnie co innego niż „0” lub pusta wartość „”. Ponieważ jednak 0 i pusta wartość są znane, wartość null jest nieznana.

Wartości NULL wymagają specjalnego traktowania SQLite, zobaczymy teraz, jak obsługiwać wartości NULL.

Szukaj wartości NULL

Nie można użyć normalnego operatora równości (=), aby wyszukać wartości null. Na przykład poniższe zapytanie wyszukuje studentów, którzy mają wartość null DepartmentId:

SELECT * FROM Students WHERE DepartmentId = NULL;

To zapytanie nie da żadnego wyniku:

Ponieważ wartość NULL nie jest równa żadnej innej wartości, sama w sobie zawierała wartość null, dlatego nie zwróciła żadnego wyniku.

Aby jednak zapytanie zadziałało, należy użyć operatora „IS NULL”, aby wyszukać wartości null, w następujący sposób:

SELECT * FROM Students WHERE DepartmentId IS NULL;

To da ci:

Zapytanie zwróci tych uczniów, którzy mają wartość null DepartmentId.

Jeżeli chcesz uzyskać wartości, które nie są nullem, musisz użyć operatora „IS NOT NULL” w następujący sposób:

SELECT * FROM Students WHERE DepartmentId IS NOT NULL;

To da ci:

Zapytanie zwróci tych uczniów, którzy nie mają wartości NULL DepartmentId.

Wyniki warunkowe

Jeśli masz listę wartości i chcesz wybrać dowolną z nich na podstawie pewnych warunków. W tym celu warunek dla tej konkretnej wartości powinien być prawdziwy, aby został wybrany.

Wyrażenie CASE oceni tę listę warunków dla wszystkich wartości. Jeśli warunek jest spełniony, zwróci tę wartość.

Na przykład, jeśli masz kolumnę „Ocena” i chcesz wybrać wartość tekstową na podstawie wartości oceny, wykonaj następujące czynności:

– „Doskonały”, jeśli ocena jest wyższa niż 85.

– „Bardzo dobry”, jeśli ocena mieści się w przedziale od 70 do 85.

– „Dobry”, jeśli ocena mieści się w przedziale od 60 do 70.

Następnie możesz użyć wyrażenia CASE, aby to zrobić.

Można tego użyć do zdefiniowania logiki w klauzuli SELECT, dzięki czemu można wybrać określone wyniki w zależności od określonych warunków, na przykład instrukcja if.

Operator CASE można zdefiniować za pomocą różnych składni, jak poniżej:

Możesz użyć różnych warunków:

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

Możesz też użyć tylko jednego wyrażenia i ustawić różne możliwe wartości do wyboru:

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

Należy pamiętać, że klauzula ELSE jest opcjonalna.

Przykład

W poniższym przykładzie użyjemy wyrażenia CASE z wartością NULL w kolumnie Identyfikator wydziału w tabeli Studenci, aby wyświetlić tekst „Brak wydziału” w następujący sposób:

SELECT
  StudentName,
  CASE
    WHEN DepartmentId IS NULL THEN 'No Department'
    ELSE DepartmentId
  END AS DepartmentId
FROM Students;

• Operator CASE sprawdzi, czy wartość DepartmentId jest równa null, czy nie.
• Jeśli jest to wartość NULL, zamiast wartości DepartmentId zostanie wybrana wartość literału „Brak działu”.
• Jeśli nie jest to wartość null, zostanie wybrana wartość kolumny DepartmentId.

To da ci wynik, jak pokazano poniżej:

Wspólne wyrażenie tabelowe

Wspólne wyrażenia tabelowe (CTE) to podzapytania zdefiniowane wewnątrz instrukcji SQL o podanej nazwie.

Ma przewagę nad podzapytaniami, ponieważ jest definiowany na podstawie instrukcji SQL i sprawia, że ​​zapytania są łatwiejsze do odczytania, obsługi i zrozumienia.

Typowe wyrażenie tabelowe można zdefiniować, umieszczając klauzulę WITH przed poleceniem SELECT w następujący sposób:

WITH CTEname
AS
(
   SELECT statement
)
SELECT, UPDATE, INSERT, or update statement here FROM CTE

„Nazwa CTE” to dowolna nazwa, którą możesz nadać CTE i używać jej do późniejszego odwoływania się do niego. Pamiętaj, że możesz definiować polecenia SELECT, UPDATE, INSERT lub DELETE w CTE.

Zobaczmy teraz przykład użycia CTE w klauzuli SELECT.

Przykład

W poniższym przykładzie zdefiniujemy CTE z polecenia SELECT, a następnie użyjemy go później w innym zapytaniu:

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;

W tym zapytaniu zdefiniowaliśmy CTE i nadaliśmy mu nazwę „AllDepartments”. To CTE zostało zdefiniowane na podstawie zapytania SELECT:

SELECT DepartmentId, DepartmentName
  FROM Departments

Następnie, po zdefiniowaniu CTE, użyliśmy go w następującym po nim zapytaniu SELECT.

Należy pamiętać, że wspólne wyrażenia tabelowe nie wpływają na wynik zapytania. Jest to sposób na zdefiniowanie widoku logicznego lub podzapytania w celu ponownego wykorzystania ich w tym samym zapytaniu. Typowe wyrażenia tabelowe są jak zmienna, którą deklarujesz i wykorzystujesz ponownie jako podzapytanie. Tylko instrukcja SELECT wpływa na wynik zapytania.

To zapytanie da Ci:

Zapytania zaawansowane

Zaawansowane zapytania to takie zapytania, które zawierają złożone połączenia, podzapytania i niektóre agregaty. W poniższej sekcji zobaczymy przykład zaawansowanego zapytania:

Gdzie dostajemy,

• Nazwy wydziałów wraz ze wszystkimi studentami każdego wydziału
• Imiona i nazwiska uczniów oddzielone przecinkiem i
• Pokazuje, że na wydziale jest co najmniej trzech studentów

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;

Dodaliśmy klauzulę JOIN, aby pobrać nazwę działu z tabeli „Departments”. Następnie dodaliśmy klauzulę GROUP BY z dwiema funkcjami agregującymi:

• „COUNT”, aby policzyć studentów w każdej grupie wydziałowej.
• GROUP_CONCAT, aby połączyć uczniów dla każdej grupy przecinkiem w jednym ciągu.

Po GROUP BY użyliśmy klauzuli HAVING, aby przefiltrować wydziały i wybrać tylko te wydziały, które mają co najmniej 3 studentów.

Wynik będzie następujący:

FAQ