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

Aby pisać zapytania SQL w bazie danych SQLite, musisz wiedzieć, jak działają klauzule SELECT, FROM, WHERE, GROUP BY, ORDER BY i LIMIT oraz jak ich używać.

Podczas tego samouczka nauczysz się, jak używać tych klauzul i jak pisać klauzule SQLite.

W tym samouczku nauczysz się:

• Czytanie danych za pomocą Select
• Nazwy i alias
• GDZIE
• Ograniczanie i porządkowanie
• Usuwanie duplikatów
• Agregat
• Grupuj według
• Zapytanie i podzapytanie
• Operacje na zbiorach -UNION, przecięcie
• Obsługa NULL
• Wyniki warunkowe
• Typowe wyrażenie tabeli
• Zaawansowane zapytania

Czytanie danych za pomocą Select

Klauzula SELECT jest główną instrukcją używaną do wysyłania zapytań do bazy danych SQLite. 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ślenia, gdzie chcesz wybrać dane. W klauzuli from można określić jedną lub więcej tabel lub podzapytań, z których będą wybierane dane, co zobaczymy w dalszej części samouczków.

Zauważ, że we wszystkich poniższych przykładach musisz uruchomić sqlite3.exe i otworzyć połączenie z przykładową bazą danych w sposób ciągły:

Krok 1) Na tym etapie

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

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

Teraz jesteś gotowy do uruchamiania dowolnego typu zapytań w bazie danych.

W klauzuli SELECT możesz wybrać nie tylko nazwę kolumny, ale masz także wiele innych opcji, aby określić, co wybrać. W następujący sposób:

WYBIERZ *

To polecenie wybierze wszystkie kolumny ze wszystkich przywoływanych tabel (lub podzapytań) w klauzuli FROM. Na przykład:

WYBIERZ *OD studentówINNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId; 

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

SELECT nazwa tabeli. *

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

WYBIERZ Studentów. *OD studentówINNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

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

Wartość dosłowna

Wartość literału to stała wartość, którą można określić w instrukcji select. Możesz używać wartości literałów normalnie w taki sam sposób, jak używasz nazw kolumn w klauzuli SELECT. Te wartości literałów 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łów, które możesz wybrać:

• Literał numeryczny - liczby w dowolnym formacie, np. 1, 2,55,… itd.
• Literały ciągów znaków - dowolny ciąg „USA”, „to jest przykładowy tekst” itp.
• NULL - wartość NULL.
• Current_TIME - poda aktualny czas.
• CURRENT_DATE - to daje 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, z nową kolumną o nazwie kraj, która zawiera wartość „USA”, możesz to zrobić:

WYBIERZ *, „USA” JAKO kraj OD studentów;

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

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

Nazwy i alias

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

Na przykład, jeśli chcesz wybrać kolumnę StudentName, która ma być zwracana z „Student Name” zamiast „StudentName”, możesz nadać jej alias w następujący sposób:

SELECT StudentName jako „Student Name” FROM Students; 

Spowoduje to wyświetlenie nazwisk uczniów z imieniem „Nazwisko ucznia” zamiast „Nazwa ucznia”, na przykład:

Zwróć uwagę, że nazwa kolumny to nadal „ StudentName ”; kolumna StudentName jest nadal taka sama, nie zmienia się przez alias.

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ć alias bez niego, na przykład:

SELECT StudentName 'Student Name' FROM Students;

I da ci dokładnie to samo wyjście, co poprzednie zapytanie:

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

SELECT s. * FROM Students AS s; 

To da ci wszystkie kolumny w tabeli Uczniowie:

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

SELECT Students.StudentName, Departments.DepartmentNameOD studentówINNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

To zapytanie spowoduje wybranie nazwiska każdego ucznia z tabeli „Studenci” wraz z nazwą jego działu z tabeli „Działy”:

Jednak to samo zapytanie można zapisać w ten sposób:

SELECT s.StudentName, d.DepartmentNameOD Uczniów AS sINNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId; 

• Nadaliśmy tabeli Students alias „s”, a tabeli działów alias „d”.
• Następnie zamiast używać pełnej nazwy tabeli, używaliśmy ich aliasów, aby się do nich odwoływać.
• INNER JOIN łączy ze sobą dwie lub więcej tabel przy użyciu warunku. W naszym przykładzie połączyliśmy tabelę studentów z tabelą Departments z kolumną DepartmentId. Istnieje również szczegółowe wyjaśnienie funkcji INNER JOIN w samouczku „SQLite Joins”.

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

GDZIE

Pisanie zapytań SQL przy użyciu samej klauzuli SELECT z klauzulą ​​FROM, jak widzieliśmy w poprzedniej sekcji, da ci 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żna określić „wyrażenie”.
• To wyrażenie zostanie ocenione dla każdego wiersza zwróconego z tabel określonych w klauzuli FROM.
• Wyrażenie zostanie ocenione jako wyrażenie logiczne, z wynikiem prawda, fałsz lub null.
• Wtedy tylko wiersze, dla których wyrażenie zostało ocenione z wartością true, zostaną zwrócone, a te z wynikami false lub null zostaną zignorowane i nie zostaną uwzględnione w zestawie wyników.
• Aby odfiltrować zestaw wyników za pomocą klauzuli WHERE, musisz użyć wyrażeń i operatorów.

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

W następnej sekcji wyjaśnimy, w jaki sposób można filtrować za pomocą wyrażeń i operatorów.

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

Zauważ, że możesz używać wyrażeń zarówno w klauzuli SELECT, jak iw klauzuli WHERE.

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

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ęcej wartości literałów lub kolumn. Spowoduje to utworzenie jednego ciągu wyników ze wszystkich połączonych wartości literałów lub kolumn. Na przykład:

WYBIERZ „Id z nazwą:” || StudentId || StudentName AS StudentIdWithNameOD studentów;

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

• Wartość ciągu literału „ Id z nazwą: ”
• wartością z kolumny „ StudentId ” i
• wartością z kolumny „ StudentName ”

Operator SQLite CAST:

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

Na przykład, jeśli masz wartość liczbową przechowywaną jako ciąg znaków, taki jak „ 12,5” ”i chcesz przekonwertować ją na wartość liczbową, możesz użyć operatora CAST, aby to zrobić w ten sposób:„ CAST ('12 .5 'AS PRAWDZIWE) ”. Lub jeśli masz wartość dziesiętną, taką jak 12,5, i potrzebujesz tylko części całkowitej, możesz rzucić ją na liczbę całkowitą, taką jak „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 ciągiem znaków, zostanie ona przekonwertowana na wartość RZECZYWISTĄ.
• CAST (12,5 AS INTEGER) - wartość 12,5 jest wartością dziesiętną, zostanie przekonwertowana na liczbę całkowitą. Część dziesiętna zostanie obcięta i stanie się 12.

Operatory arytmetyczne SQLite:

Weź co najmniej dwie wartości literałów liczbowych lub kolumny liczbowe i zwróć jedną wartość liczbową. Operatory arytmetyczne obsługiwane w SQLite to:

Dodatek „ + ” - podaj sumę dwóch operandów. Odejmowanie „ - ” - odejmuje dwa operandy i daje w wyniku różnicę. Mnożenie " * " - iloczyn dwóch operandów. Przypomnienie (modulo) " % " - podaje resztę wynikającą z podzielenia jednego operandu przez drugi operand. Dzielenie „ / ” - zwraca iloraz wynikający z podzielenia lewego operandu przez prawy operand.

Przykład:

W poniższym przykładzie spróbujemy pięciu operatorów arytmetycznych z dosłownymi wartościami liczbowymi w tym samym

wybierz klauzulę:

WYBIERZ 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. Jest to dozwolone w SQLite, o ile wybieramy wartości literalne.

Operatory porównania SQLite

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

„ < ” - zwraca prawdę, jeśli lewy operand jest mniejszy niż prawy operand. „ <= ” - zwraca prawdę, jeśli lewy operand jest mniejszy lub równy prawemu operandowi. " > " - zwraca prawdę, jeśli lewy operand jest większy niż prawy operand. " > = " - zwraca prawdę, jeśli lewy operand jest większy lub równy prawemu operandowi. „ = ” i „ == ” - zwraca prawdę, jeśli dwa operandy są równe. Zauważ, że oba operatory są takie same i nie ma między nimi różnicy. „ ! = ” i „ <> ” - zwraca prawdę, jeśli dwa operandy nie są równe. Zauważ, że oba operatory są takie same i nie ma między nimi różnicy.

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

Przykład:

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

Operatory dopasowania wzorców SQLite

„ LIKE ” - służy do dopasowania wzorców. Używając „ Lubię to ”, możesz wyszukiwać wartości pasujące do wzorca określonego za pomocą symbolu wieloznacznego.

Operand po lewej stronie może być wartością literału ciągu lub kolumną łańcucha. Wzór można określić w następujący sposób:

• Zawiera wzór. Na przykład StudentName LIKE '% a%' - spowoduje to wyszukanie nazwisk uczniów, które zawierają literę „a” na dowolnej pozycji w kolumnie StudentName.
• Zaczyna się od wzoru. Na przykład „ StudentName LIKE 'a%' ” - wyszukaj imiona uczniów, które zaczynają się na literę „a”.
• Kończy się wzorem. Na przykład „ StudentName LIKE '% a' ” - Wyszukaj nazwiska uczniów, które kończą się na literę „a”.
• Dopasowanie dowolnego pojedynczego znaku w ciągu za pomocą znaku podkreślenia „_”. Na przykład „ StudentName LIKE 'J___' ” - wyszukaj imiona uczniów o długości 4 znaków. Musi zaczynać się od litery „J” i może zawierać jeszcze trzy inne znaki po literze „J”.

Przykłady dopasowania wzorców:

1. Uzyskaj nazwiska uczniów zaczynające się na literę „j”:

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

   Wynik:

   

2. Uzyskaj nazwiska uczniów kończące się literą `` y '':

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

   Wynik:

   

3. Uzyskaj nazwiska uczniów, które zawierają literę `` n '':

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

   Wynik:

   

„GLOB” - jest odpowiednikiem operatora LIKE, ale w GLOB rozróżniana jest wielkość liter, w przeciwieństwie do operatora LIKE. Na przykład następujące dwie komendy zwrócą różne wyniki:

WYBIERZ „Jack” GLOB „j%”;WYBIERZ „Jack” LIKE „j%”;

To da ci:

• Pierwsza instrukcja zwraca 0 (fałsz), ponieważ operator GLOB rozróżnia wielkość liter, więc „j” nie jest równe „J”. Jednak druga instrukcja zwróci 1 (prawda), ponieważ operator LIKE nie rozróżnia wielkości liter, więc „j” jest równe „J”.

Inni operatorzy:

SQLite AND

Operator logiczny, który łączy jedno lub więcej wyrażeń. Zwróci prawdę, tylko jeśli wszystkie wyrażenia dadzą wartość „prawdziwą”. Jednak zwróci wartość „fałsz” tylko wtedy, gdy wszystkie wyrażenia dadzą wartość „fałsz”.

Przykład:

Następujące zapytanie wyszuka uczniów, którzy mają StudentId> 5, a StudentName zaczyna się od litery N, zwróceni uczniowie muszą spełniać dwa warunki:

WYBIERZ *OD studentówWHERE (StudentId> 5) AND (StudentName LIKE 'N%');

Jako wynik, na powyższym zrzucie ekranu, da ci to tylko "Nancy". Nancy jest jedyną uczennicą, która spełnia oba warunki.

SQLite OR

Operator logiczny, który łączy jedno lub więcej wyrażeń, więc jeśli jeden z połączonych operatorów daje wartość true, zwróci wartość true. Jeśli jednak wszystkie wyrażenia dadzą wartość fałsz, zwróci ona fałsz.

Przykład:

Następujące zapytanie wyszuka uczniów, których StudentId> 5 lub StudentName zaczyna się od litery N, zwróceni uczniowie muszą spełniać co najmniej jeden z warunków:

WYBIERZ *OD studentówGDZIE (StudentId> 5) LUB (StudentName LIKE 'N%');

To da ci:

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

Jak widać wynik jest inny niż zapytanie z operatorem AND.

SQLite BETWEEN

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

Przykład:

W poniższym przykładowym zapytaniu napiszemy zapytanie, aby uzyskać uczniów o wartości Id od 5 do 8:

WYBIERZ *OD studentówGDZIE StudentId MIĘDZY 5 I 8;

To da tylko uczniom z identyfikatorami 5, 6, 7 i 8:

SQLite IN

Pobiera jeden operand i listę operandów. Zwróci prawdę, jeśli wartość pierwszego argumentu będzie równa wartości jednego z argumentów z listy. Operator IN zwraca wartość true (1), jeśli lista argumentów zawiera pierwszą wartość operandu w swoich wartościach. W przeciwnym razie zwróci false (0).

W ten sposób: „ col IN (x, y, z) ”. Jest to równoważne z „ (kol = x) lub (kol = y) lub (kol = z) ”.

Przykład:

Poniższe zapytanie wybierze tylko uczniów z identyfikatorami 2, 4, 6, 8:

WYBIERZ *OD studentówWHERE StudentId IN (2, 4, 6, 8);

Lubię to:

Poprzednie zapytanie da dokładny wynik jako następujące zapytanie, ponieważ są one równoważne:

WYBIERZ *OD studentówGDZIE (StudentId = 2) LUB (StudentId = 4) LUB (StudentId = 6) LUB (StudentId = 8);

Oba zapytania dają dokładne wyniki. 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 z użyciem 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); ”

Lubię to:

SQLite NIE JEST W

Operand „NOT IN” jest przeciwieństwem operatora IN. Ale z tą samą składnią; wymaga jednego operandu i listy operandów. Zwróci prawdę, jeśli wartość pierwszego argumentu nie jest równa wartości jednego z argumentów z listy. tj. zwróci wartość true (0), jeśli lista argumentów nie zawiera pierwszego operandu. W ten sposób: „ 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 tych identyfikatorów 2, 4, 6, 8:

WYBIERZ *OD studentówGDZIE NIE MA StudentId (2, 4, 6, 8);

Lubię to

W przypadku poprzedniego zapytania dokładny wynik podajemy jako następujące zapytanie, ponieważ są one równoważne:

WYBIERZ *OD studentówGDZIE (StudentId <> 2) AND (StudentId <> 4) AND (StudentId <> 6) AND (StudentId <> 8);

Lubię to:

Na powyższym zrzucie ekranu

Użyliśmy wielu operatorów nierównych "<>", aby uzyskać listę uczniów, którzy nie są równi żadnemu z następujących identyfikatorów 2, 4, 6 ani 8. To zapytanie zwróci wszystkich innych uczniów poza wymienionymi identyfikatorami.

SQLite ISTNIEJE

Operatory ISTNIEJE nie przyjmują żadnych operandów; po nim jest tylko klauzula SELECT. Operator EXISTS zwróci wartość true (1), jeśli istnieją jakiekolwiek wiersze zwrócone z klauzuli SELECT, a zwróci wartość false (0), jeśli w ogóle nie ma żadnych wierszy zwróconych z klauzuli SELECT.

Przykład:

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

Wybierz nazwę działuZ Departamentów AS dGDZIE ISTNIEJE (SELECT DepartmentId FROM Students AS s WHERE d.DepartmentId = s.DepartmentId);

To da ci:

Zwrócone zostaną tylko trzy działy „ informatyka, fizyka i sztuka ”. Nazwa działu „ Matematyka ” nie zostanie zwrócona, ponieważ na tym wydziale nie ma studenta, więc identyfikator wydziału nie istnieje w tabeli uczniów. Dlatego operator EXISTS zignorował dział „ Math ”.

SQLite NIE

Odwraca wynik działania poprzedniego operatora, który występuje po nim. Na przykład:

• NOT BETWEEN - Zwróci wartość true, jeśli BETWEEN zwróci fałsz i na odwrót.
• NOT LIKE - Zwróci wartość true, jeśli LIKE zwróci false i vice versa.
• NOT GLOB - Zwróci wartość true, jeśli GLOB zwróci false i odwrotnie.
• NIE ISTNIEJE - Zwróci prawdę, jeśli ISTNIEJE zwróci fałsz 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ą identyfikatora działu, którego nie ma w tabeli działów.

Wybierz nazwę działuZ Departamentów AS dGDZIE NIE ISTNIEJE (SELECT DepartmentIdOD Uczniów AS sGDZIE d.DepartmentId = s.DepartmentId);

Dane wyjściowe :

Zwrócony zostanie tylko dział „ Matematyka ”. Ponieważ dział „ Matematyka ” jest jedynym działem, którego nie ma w tabeli uczniów.

Ograniczanie i porządkowanie

Zamówienie SQLite

Kolejność SQLite polega na sortowaniu wyniku według co najmniej jednego wyrażenia. Aby zamówić zestaw wyników, musisz 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 niej można podać tylko klauzulę LIMIT.
• Określ wyrażenie, według którego chcesz uporządkować dane. To wyrażenie 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 „,” między sobą.

Przykład

W poniższym przykładzie wybierzemy wszystkich studentów uporządkowanych według imion, ale w porządku malejącym, a następnie według nazwy wydziału w kolejności rosnącej:

SELECT s.StudentName, d.DepartmentNameOD Uczniów AS sINNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentIdZAMÓWIENIE PRZEZ 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 nazwy każdego działu wszyscy uczniowie pod tą nazwą będą wyświetlani w porządku malejącym według ich nazw

Limit SQLite:

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, używając klauzuli OFFSET. Na przykład „ 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.

Zwróć uwagę, że klauzula OFFSET jest opcjonalna, możesz ją zapisać w stylu „ LIMIT 4, 4 ” i poda dokładne wyniki.

Przykład :

W poniższym przykładzie zwrócimy tylko 3 uczniów, zaczynając od identyfikatora studenta 5, używając zapytania:

WYBIERZ * OD STUDENTÓW LIMIT 4,3;

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

Usuwanie duplikatów

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

Przykład:

Następujące zapytanie zwróci zduplikowane „wartości nazw działów”: Tutaj mamy zduplikowane wartości z nazwami IT, Fizyka i Sztuka.

SELECT d.DepartmentNameOD Uczniów AS sINNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

W ten sposób uzyskasz zduplikowane wartości dla nazwy działu:

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

SELECT DISTINCT d.DepartmentNameOD Uczniów AS sINNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

W ten sposób uzyskasz tylko trzy unikalne wartości w kolumnie z nazwą działu:

Agregat

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

Oto agregaty obsługiwane przez SQLite:

SQLite AVG ()

Zwrócono średnią dla wszystkich wartości x.

Przykład:

W poniższym przykładzie otrzymamy średnią ocen ze wszystkich egzaminów:

SELECT AVG (Mark) FROM Marks;

W ten sposób otrzymasz wartość „18,375”:

Wyniki te pochodzą z sumowania wszystkich wartości ocen podzielonych przez ich liczbę.

COUNT () - COUNT (X) lub COUNT (*)

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 zwróci liczbę różnych wartości x.

Przykład

W poniższym przykładzie otrzymamy łączną liczbę działów z COUNT (DepartmentId), COUNT (*) i COUNT (DISTINCT DepartmentId) oraz ich różnicami:

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

To da ci:

W następujący sposób:

• COUNT (DepartmentId) poda liczbę wszystkich identyfikatorów działu i zignoruje wartości null.
• COUNT (DISTINCT DepartmentId) podaje różne wartości DepartmentId, które są równe tylko 3. Są to trzy różne wartości nazwy działu. Zwróć uwagę, że w nazwisku studenta jest 8 wartości nazwy dział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 na 10 uczniów.

GROUP_CONCAT () - GROUP_CONCAT (X) lub GROUP_CONCAT (X, Y)

Funkcja agregująca GROUP_CONCAT łączy wielokrotności wartości w jedną wartość z przecinkiem, aby je oddzielić. Posiada następujące opcje:

• GROUP_CONCAT (X): To połączy całą wartość x w jeden ciąg, z przecinkiem „,” używanym jako separator między wartościami. Wartości NULL zostaną zignorowane.
• GROUP_CONCAT (X, Y): To połączy wartości x w jeden ciąg, z wartością y używaną jako separator między każdą wartością zamiast domyślnego separatora „,”. Wartości NULL również będą ignorowane.
• GROUP_CONCAT (DISTINCT X): To połączy wszystkie odrębne wartości x w jeden ciąg, z przecinkiem „,” używanym jako separator między wartościami. Wartości NULL zostaną zignorowane.

GROUP_CONCAT (DepartmentName) Przykład

Następujące zapytanie połączy wszystkie wartości nazw działów z tabeli studentów i działów w jeden ciąg znaków oddzielony przecinkami. Więc zamiast zwracać listę wartości, po jednej wartości w każdym wierszu. Zwróci tylko jedną wartość w jednym wierszu, z wszystkimi wartościami oddzielonymi przecinkami:

SELECT GROUP_CONCAT (d.DepartmentName)OD Uczniów AS sINNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

To da ci:

Spowoduje to wyświetlenie listy 8 wartości nazw działów połączonych w jeden ciąg oddzielony przecinkami.

GROUP_CONCAT (DISTINCT DepartmentName) Przykład

Poniższe zapytanie połączy różne wartości nazwy działu z tabeli studentów i wydziałów w jeden ciąg znaków oddzielony przecinkami:

SELECT GROUP_CONCAT (DISTINCT d.DepartmentName)OD Uczniów AS sINNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

To da ci:

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

GROUP_CONCAT (DepartmentName, „&”) Przykład

Poniższe zapytanie połączy wszystkie wartości kolumny z nazwą działu z tabeli studentów i wydziałów w jeden ciąg, ale ze znakiem „&” zamiast przecinka jako separatorem:

SELECT GROUP_CONCAT (d.DepartmentName, „&”)OD Uczniów AS sINNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

To da ci:

Zwróć uwagę, jak do oddzielania wartości używany jest znak „&” zamiast znaku domyślnego „”.

SQLite MAX () i MIN ()

MAX (X) zwraca najwyższą wartość z wartości X. MAX zwróci wartość NULL, jeśli wszystkie wartości x są puste. Natomiast MIN (X) zwraca najmniejszą wartość z wartości X. MIN zwróci wartość NULL, jeśli wszystkie wartości X są puste.

Przykład

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

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

To da ci:

SUMA SQLite (x), Suma (x)

Oba zwrócą sumę wszystkich wartości x. Ale różnią się one w następujących przypadkach:

• SUM zwróci wartość null, jeśli wszystkie wartości są puste, ale Total 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ą liczbami całkowitymi, zwróci wartość zmiennoprzecinkową.

Przykład

W poniższym zapytaniu użyjemy SUMA i sumy, aby otrzymać sumę wszystkich znaków w tabelach „ Marks ”:

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

To da ci:

Jak widać, TOTAL zawsze zwraca zmiennoprzecinkowe. SUMA zwraca jednak wartość całkowitą, ponieważ wartości w kolumnie „Mark” mogą być liczbami całkowitymi.

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

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

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

To da ci:

Zauważ, że nie ma żadnych znaczników dla TestId = 4, więc dla tego testu są wartości null. SUMA zwraca wartość null jako pustą wartość, a TOTAL zwraca 0.

Grupuj według

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

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

Przykład:

Poniższe zapytanie poda całkowitą liczbę studentów obecnych na każdym wydziale.

SELECT d.DepartmentName, COUNT (s.StudentId) AS StudentsCountOD Uczniów AS sINNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentIdGROUP BY d. Nazwa oddziału;

To da ci:

Klauzula GROUPBY DepartmentName spowoduje pogrupowanie wszystkich studentów w grupy, po jednej dla każdego działu. Dla każdej grupy „wydziału” będzie liczyć studentów.

HAVING klauzula

Jeśli chcesz filtrować grupy zwrócone przez klauzulę GROUP BY, możesz określić klauzulę „HAVING” z wyrażeniem po GROUP BY. Wyrażenie będzie używane do filtrowania tych grup.

Przykład

W poniższym zapytaniu wybierzemy te wydziały, które mają tylko dwóch studentów:

SELECT d.DepartmentName, COUNT (s.StudentId) AS StudentsCountOD Uczniów AS sINNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentIdGROUP BY d. Nazwa oddziałuHAVING 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 uczniów. W naszym przypadku Wydział Sztuki ma 2 studentów, więc jest to wyświetlane w wyniku.

Zapytanie i podzapytanie SQLite

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 dotyczącym modyfikowania danych zobaczymy, jak możemy używać podzapytań z instrukcjami INSERT, DELETE i UPDATE.

Korzystanie z podzapytania w przykładzie klauzuli FROM

W poniższym zapytaniu umieścimy podzapytanie wewnątrz klauzuli FROM:

WYBIERZs.StudentName, t.MarkOD Uczniów AS sWEWNĘTRZNE DOŁĄCZENIE(SELECT StudentId, MarkZ testów AS tINNER JOIN Zaznacza jako m ON t.TestId = m.TestId) ON s.StudentId = t.StudentId;

Zapytanie:

 SELECT StudentId, MarkZ testów AS tINNER JOIN Zaznacza jako 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 odwoływać się do kolumn zwróconych z niego w zapytaniu.

To zapytanie da ci:

W naszym przypadku

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

Korzystanie z podzapytania w przykładzie klauzuli WHERE

W poniższym zapytaniu umieścimy podzapytanie w klauzuli WHERE:

Wybierz nazwę działuZ Departamentów AS dGDZIE NIE ISTNIEJE (SELECT DepartmentIdOD Uczniów AS sGDZIE d.DepartmentId = s.DepartmentId);

Zapytanie:

SELECT DepartmentIdOD Uczniów AS sGDZIE d.DepartmentId = s.DepartmentId

Powyższe zapytanie nazywa się tutaj podzapytaniem, ponieważ jest zagnieżdżone w klauzuli WHERE. Podzapytanie zwróci wartości DepartmentId, które będą używane przez operatora NIE ISTNIEJE.

To zapytanie da ci:

W powyższym zapytaniu wybraliśmy wydział, do którego nie jest zapisany żaden student. To jest wydział „matematyki” tutaj.

Operacje na zbiorach - UNION, 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.

UNION zwróci różne wartości. Jednak UNION ALL nie będzie zawierać duplikatów.

Zauważ, że nazwą kolumny będzie nazwa kolumny określona w pierwszej instrukcji SELECT.

Przykład UNII

W poniższym przykładzie otrzymamy listę DepartmentId z tabeli studentów, a listę DepartmentId z tabeli działów w tej samej kolumnie:

SELECT DepartmentId AS DepartmentIdUnioned FROM StudentsUNIASELECT DepartmentId FROM Departments;

To da ci:

Zapytanie zwraca tylko 5 wierszy, które są różnymi wartościami identyfikatora działu. Zwróć uwagę na pierwszą wartość, która jest wartością null.

Przykład SQLite UNION ALL

W poniższym przykładzie otrzymamy listę DepartmentId z tabeli studentów, a listę DepartmentId z tabeli działów w tej samej kolumnie:

SELECT DepartmentId AS DepartmentIdUnioned FROM StudentsUNIA WSZYSTKIESELECT DepartmentId FROM Departments;

To da ci:

Zapytanie zwróci 14 wierszy, 10 wierszy z tabeli studentów i 4 z tabeli działów. Zwróć uwagę, że zwracane wartości zawierają duplikaty. Zwróć również uwagę, że nazwa kolumny była taka sama, jak określona w pierwszej instrukcji SELECT.

Zobaczmy teraz, jak UNION all da różne wyniki, jeśli zmienimy UNION ALL na UNION:

SQLite INTERSECT

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 obu tabelach Students i Departments w kolumnie DepartmentId:

SELECT DepartmentId FROM StudentsKrzyżowaćSELECT DepartmentId FROM Departments;

To da ci:

Zapytanie zwraca tylko trzy wartości 1, 2 i 3. Które są wartościami istniejącymi w obu tabelach.

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

Dlatego obie wartości NULL i 4 zostały zignorowane i nie zostały uwzględnione w zwracanych wartościach.

Z WYJĄTKIEM

Załóżmy, że jeśli masz dwie listy wierszy, list1 i list2, i chcesz, aby wiersze tylko z listy1, której nie ma na liście lista2, możesz użyć klauzuli „EXCEPT”. Klauzula EXCEPT porównuje dwie listy i zwraca te wiersze, które istnieją na liście1 i nie istnieją na liście2.

Przykład

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

SELECT DepartmentId FROM DepartmentsZ WYJĄTKIEMSELECT DepartmentId FROM Students;

To da ci:

Zapytanie zwraca tylko wartość 4. Która jest jedyną wartością istniejącą w tabeli działów i której nie ma w tabeli studentów.

Obsługa NULL

Wartość „ NULL ” jest specjalną wartością w SQLite. Służy do reprezentowania wartości, która jest nieznana lub której brakuje. Zwróć uwagę, że wartość pusta jest zupełnie inna niż wartość „ 0 ” lub pusta wartość „”. Ponieważ 0 i wartość pusta są znaną wartością, jednak wartość pusta jest nieznana.

Wartości NULL wymagają specjalnej obsługi w SQLite, zobaczymy teraz, jak obsługiwać wartości NULL.

Wyszukaj wartości NULL

Nie można użyć zwykłego operatora równości (=) do wyszukiwania wartości null. Na przykład poniższe zapytanie wyszukuje ucznió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 zawierającej samą wartość null, dlatego nie zwróciła żadnego wyniku.

• Jednak aby zapytanie działało, musisz użyć operatora „IS NULL” do wyszukania 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ść DepartmentId o wartości null.

• Jeśli chcesz uzyskać te wartości, które nie są zerowe, musisz użyć operatora „ NIE JEST NULL ” w następujący sposób:

SELECT * FROM Students, gdzie DepartmentId NIE JEST 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 prawdziwy, zwróci tę wartość.

Na przykład, jeśli masz kolumnę „Ocena” i chcesz wybrać wartość tekstową na podstawie wartości oceny w następujący sposób:

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

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

- „Dobra”, 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 to wykorzystać do zdefiniowania logiki w klauzuli SELECT, aby można było 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 w następujący sposób:

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

WALIZKAWHEN condition1 THEN wynik 1WHEN condition2 THEN wynik2KIEDY warunek3 TO wynik3… INNY wynikKONIEC

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

Wyrażenie CASEKIEDY wartość1 TO wynik1KIEDY wartość2 TO wynik2KIEDY wartość3 TO result3… ELSE restulnKONIEC

Zauważ, że klauzula ELSE jest opcjonalna.

Przykład

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

WYBIERZNazwa ucznia,WALIZKAGDY DepartmentId JEST NULL, TO „Brak działu”ELSE DepartmentIdKONIEC AS DepartmentIdOD studentów;

• Operator CASE sprawdzi wartość DepartmentId, czy jest zerowa, czy nie.
• Jeśli jest to wartość NULL, wybierze wartość literału „No Department” zamiast wartości DepartmentId.
• Jeśli nie jest wartością null, wybierze wartość kolumny DepartmentId.

To da ci dane wyjściowe, jak pokazano poniżej:

Typowe wyrażenie tabeli

Typowe wyrażenia tabelowe (CTE) to podzapytania zdefiniowane w instrukcji SQL o podanej nazwie.

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

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

Z CTEnameTAK JAK(Instrukcja SELECT)SELECT, UPDATE, INSERT lub update tutaj FROM CTE

„Nazwa CTE ” to dowolna nazwa, którą możesz nadać CTE, możesz jej użyć do późniejszego odniesienia się do niej. Zauważ, że możesz zdefiniować instrukcję SELECT, UPDATE, INSERT lub DELETE na CTE

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

Przykład

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

Z AllDepartmentsTAK JAK(Wybierz identyfikator działu, nazwa działuZ działów)WYBIERZs.StudentId,s.StudentName,a.DepartmentNameOD Uczniów AS sINNER 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:

 Wybierz identyfikator działu, nazwa działuZ działów

Następnie po zdefiniowaniu CTE użyliśmy go w zapytaniu SELECT, które nastąpiło po nim.

Należy zauważyć, że typowe wyrażenia tabelowe nie mają wpływu na dane wyjściowe zapytania. Jest to sposób na zdefiniowanie logicznego widoku lub podzapytania w celu ponownego użycia ich w tym samym zapytaniu. Typowe wyrażenia tabelowe są jak zmienna, którą deklarujesz i której używasz ponownie jako podzapytanie. Tylko instrukcja SELECT wpływa na wynik zapytania.

To zapytanie da ci:

Zaawansowane zapytania

Zapytania zaawansowane to zapytania zawierające złożone sprzężenia, podzapytania i niektóre agregacje. W poniższej sekcji zobaczymy przykład zaawansowanego zapytania:

Gdzie otrzymujemy,

• Nazwy wydziałów ze wszystkimi studentami z każdego wydziału
• Nazwiska uczniów oddzielone przecinkami i
• Pokazuje wydział posiadający co najmniej trzech studentów

WYBIERZd. nazwa działu,COUNT (s.StudentId) StudentsCount,GROUP_CONCAT (StudentName) jako uczniowieZ Departamentów AS dWEWNĘTRZNE DOŁĄCZ Uczniowie, jak na s.DepartmentId = d.DepartmentIdGROUP BY d.DepartmentNameHAVING COUNT (s.StudentId)> = 3;

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

• „COUNT”, aby policzyć uczniów w każdej grupie działów.
• GROUP_CONCAT do łączenia uczniów w każdej grupie przecinkami oddzielonymi jednym ciągiem.
• Po funkcji 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:

Podsumowanie:

Było to wprowadzenie do pisania zapytań SQLite i podstaw wykonywania zapytań w bazie danych oraz sposobu filtrowania zwracanych danych. Możesz teraz pisać własne zapytania SQLite.