Kompletny przewodnik i szczegółowe przykłady poleceń SQL: od podstawowych do zaawansowanych zapytań

Strukturalny Język Zapytań (SQL) to jedno z tych narzędzi, które po opanowaniu stają się Twoim najlepszym sprzymierzeńcem w interakcji z dowolną relacyjną bazą danych. Jeśli kiedykolwiek zastanawiałeś się, jak łatwo i wydajnie uzyskiwać dostęp do dużych wolumenów danych, modyfikować je, usuwać lub analizować, SQL jest odpowiedzią, której szukałeś. Nauka poprawnego używania poleceń SQL stanowi różnicę między byciem początkującym użytkownikiem a prawdziwym ekspertem w zarządzaniu danymi i ich analizie.

W tym artykule zebrałem obszerną listę głównych poleceń SQL, wraz z przejrzystymi wyjaśnieniami i praktycznymi przykładami, obejmujących wszystko, od podstaw po bardziej zaawansowane zapytania. Pokażę Ci również triki i szczegóły, które często są pomijane w bardziej powierzchownych artykułach, abyś mógł w pełni wykorzystać potencjał swoich baz danych, niezależnie od tego, czy dopiero zaczynasz, czy chcesz doskonalić swoje umiejętności. Przygotuj się na zanurzenie w świecie SQL i odkryj, jak ten język może odmienić Twój sposób pracy z informacjami.

Czym jest SQL i dlaczego jest nadal tak ważny?

SQL , czyli Structured Query Language, to język programowania zaprojektowany specjalnie do zarządzania i manipulowania relacyjnymi bazami danych. Chociaż został opracowany w latach 70. XX wieku, pozostaje standardowym rozwiązaniem do komunikacji z bazami danych, takimi jak MySQL, PostgreSQL, SQL Server, MariaDB i Oracle Database.

Powód jego długowieczności jest prosty: SQL umożliwia wykonywanie wielu zadań, od prostych, takich jak wyszukiwanie danych, po zaawansowane operacje, takie jak łączenie złożonych tabel, tworzenie struktur, zbiorcza aktualizacja rekordów i definiowanie uprawnień . Co więcej, jego składnia jest na tyle prosta, że ​​każdy może się z nią szybko zapoznać, a jednocześnie wystarczająco zaawansowana, aby rozwiązywać złożone problemy biznesowe.

Profesjonaliści z różnych dziedzin — od programistów, analityków i administratorów baz danych po naukowców zajmujących się danymi — wykorzystują język SQL do przetwarzania, oczyszczania i analizowania kluczowych danych na potrzeby swoich projektów i organizacji.

Kategorie poleceń SQL: DQL, DML, DDL i DCL

Zanim przejdziemy do przykładów, warto przypomnieć, że polecenia SQL można podzielić na różne kategorie w zależności od ich funkcji:

• DQL (język zapytań o dane): Są to zdania służące do wyszukiwania danych, takie jak: SELECT.
• DML (język manipulacji danymi): Manipulują rekordami za pomocą poleceń takich jak INSERT, Aktualizacja y DELETE.
• DDL (język definicji danych): Zarządzają strukturą bazy danych, na przykład: TWORZENIE, ZMIANA, DROP y ŚCIĘTY.
• DCL (język kontroli danych): Kontrolują uprawnienia i dostęp, korzystając z GRANT y UNIEWAŻNIĆ.

Każdy typ polecenia pełni istotną funkcję w codziennym zarządzaniu bazą danych. Ważne jest, aby wiedzieć, którego polecenia użyć w każdej sytuacji, aby uniknąć błędów lub nawet katastrof w zarządzaniu danymi.

Główne polecenia SQL: podstawy i praktyczne przykłady

Opanowanie podstawowych poleceń SQL otwiera drzwi do praktycznie każdego zadania zarządzania danymi lub analizy. Poniżej wyjaśnię te najbardziej istotne, jak działają i podam przykłady użycia, aby pomóc Ci się ich nauczyć.

WYBIERZ: Król zapytań

Polecenie SELECT jest podstawą większości operacji SQL. Umożliwia pobieranie danych przechowywanych w jednej lub wielu tabelach.

Składnia ogólna:

SELECT column1, column2, ... FROM table_name;

Aby uzyskać wszystkie kolumny w tabeli, wystarczy użyć gwiazdki:

WYBIERZ * Z pracowników;

Jeśli chcesz filtrować określone dane, możesz dodać warunki:

SELECT imię, wiek FROM pracownicy GDZIE wiek > 30;

Dzięki SELECT możesz pobierać zarówno pojedyncze wartości, jak i złożone zestawy danych, stosować funkcje agregujące, obliczać kolumny pochodne i przygotowywać informacje do raportów lub wizualizacji.

GDZIE: Filtrowanie wyników precyzyjnie

Klauzula WHERE pozwala określić warunki, które wybiorą tylko te wiersze, które spełniają określone kryteria. Jest to podstawowy filtr każdego zapytania.

Podstawowa składnia:

WYBIERZ * Z produktów GDZIE cena < 100;

Warunki można łączyć za pomocą operatorów logicznych, takich jak AND , OR i NOT , a także operatorów porównania ( =, <, >, <=, >=, <> ).

Na przykład, aby zatrudnić pracowników działu sprzedaży poniżej 30 roku życia:

WYBIERZ * Z pracowników GDZIE dział = 'Sprzedaż' I wiek < 30;

INSERT: Dodawanie nowych rekordów

Za pomocą INSERT możesz dodawać nowe wiersze do tabeli. Jest to niezbędne do aktualizowania baz danych o nowe informacje.

Składnia ogólna:

WSTAW DO produktów (nazwa, cena) WARTOŚCI ('Klawiatura', 29.99);

Jeśli chcesz dodać wiele rekordów w jednym zapytaniu, możesz to również zrobić:

WSTAW DO produktów (nazwa, cena) WARTOŚCI ('Mysz', 15.50), ('Monitor', 120.00);

Pamiętaj, że jeśli pominiesz listę kolumn, musisz podać wartość dla każdego pola w tabeli, przestrzegając ich kolejności.

AKTUALIZACJA: Modyfikacja istniejących danych

Polecenie UPDATE umożliwia zmianę wartości jednego lub kilku pól w rekordach, które spełniają określony warunek. Jest przydatne do korygowania błędów lub aktualizowania informacji.

Składnia:

AKTUALIZUJ pracowników USTAW pensję = pensja * 1.05 GDZIE dział = 'Marketing';

W tym przykładzie wszyscy pracownicy działu marketingu zobaczą wzrost pensji o 5%. Używanie klauzuli WHERE przy każdej aktualizacji jest niezbędne , chyba że chcesz zmodyfikować całą tabelę.

USUŃ: Usuwanie danych

Aby usunąć rekordy, użyj polecenia DELETE wraz z warunkiem. Dzięki temu unikniesz przypadkowego usunięcia wszystkich danych z tabeli.

przykład:

USUŃ Z produktów GDZIE id_producto = 10;

Jeśli pominiesz klauzulę WHERE, wszystkie wiersze w tabeli zostaną usunięte. Uwaga!

Sortuj według: Organizowanie wyników

Klauzula ORDER BY służy do sortowania wyników zapytania według jednej lub kilku kolumn. Domyślnie kolejność jest rosnąca, ale w razie potrzeby można określić kolejność ASC lub DESC .

WYBIERZ * Z pracowników KOLEJNOŚĆ WEDŁUG wynagrodzenia MNIEJĄCO;

W sortowaniu można łączyć wiele kolumn:

WYBIERZ * Z produktów KOLEJNOŚĆ WEDŁUG kategorii rosnąco, ceny malejąco;

GROUP BY i funkcje agregujące: SUM, AVG, MIN, MAX, COUNT

Funkcja GROUP BY umożliwia grupowanie wyników według jednej lub większej liczby kolumn. Jest ona bardzo użyteczna w połączeniu z funkcjami takimi jak SUM() (suma), AVG() (średnia), MIN (minimum), MAX (maksimum) i COUNT (liczba).

Na przykład, aby dowiedzieć się, ile produktów jest w każdej kategorii:

WYBIERZ kategorię, LICZ(*) Z produktów GRUPUJ WEDŁUG kategorii;

Lub aby obliczyć całkowity rachunek dla każdego klienta:

WYBIERZ customer_id, SUMA(kwota) Z GRUPY sprzedaży WEDŁUG customer_id;

Możesz filtrować powstałe grupy, używając klauzuli HAVING :

WYBIERZ dział, ŚREDNIA(wynagrodzenie) OD pracowników GRUPUJ WEDŁUG działu MAJĄCEGO ŚREDNIA(wynagrodzenie) > 2000;

LIMIT i OFFSET: Ograniczanie liczby wyników

Jeśli tabele zawierają tysiące rekordów, polecenie LIMIT jest niezbędne, aby pokazać tylko część wyników, co poprawia wydajność i czytelność.

WYBIERZ * Z produktów LIMIT 10;

Za pomocą OFFSET można pominąć określoną liczbę wierszy, co jest przydatne przy paginacji:

WYBIERZ * Z produktów LIMIT 10 OFFSET 20;

BETWEEN, IN i LIKE: Zaawansowane warunki WHERE

Te słowa kluczowe rozszerzają możliwości filtrowania:

• MIĘDZY: Wybierz wartości z zakresu.

  WYBIERZ * Z pracowników, GDZIE pensja pomiędzy 1000 a 3000;

• IN: Wyszukaj wiele wartości.

  WYBIERZ * Z produktów GDZIE kategoria W ('Komputery', 'Elektronika');

• TAK JAK: Szukaj wzorców w tekście.

  WYBIERZ * Z klientów GDZIE nazwa JAK 'A%';

Można również używać symboli wieloznacznych, takich jak % (dowolna liczba znaków) i _ (pojedynczy znak) , aby jeszcze bardziej dostosować wyszukiwania LIKE.

DOŁĄCZ: Przenoszenie informacji między tabelami

Jedną z największych zalet języka SQL jest możliwość łączenia danych z różnych tabel za pomocą połączeń JOIN . Do najpopularniejszych z nich należą:

• POŁĄCZENIE WEWNĘTRZNE: Zwraca pasujące wiersze w obu tabelach.
• LEWY DOŁĄCZ: Zwraca wszystkie wiersze z lewej tabeli i wszystkie dopasowania z prawej (w przypadku braku dopasowania uzupełniane wartością NULL).
• PRAWE DOŁĄCZ: Zwraca wszystkie wiersze z prawej tabeli i odpowiadające im wiersze z lewej.
• PEŁNE DOŁĄCZENIE ZEWNĘTRZNE: Zwraca wszystkie wiersze z obu tabel (uzupełnione wartością NULL w przypadku braku dopasowania).

Przykład INNER JOIN:

WYBIERZ orders.id, customers.name Z orders WEWNĘTRZNIE DOŁĄCZ customers NA orders.customer_id = customers.id;

LEWY DOŁĄCZ:

Wybierz employees.name, sales.amount FROM employees LEFT DOŁĄCZ sales ON employees.id = sales.employee_id;

ALTER, CREATE i DROP: Modyfikowanie struktury bazy danych

Oprócz manipulowania danymi, SQL pozwala definiować i modyfikować strukturę tabel i innych obiektów bazy danych:

• STWÓRZ: Aby utworzyć nowe obiekty (tabele, widoki, indeksy, użytkowników…).

  UTWÓRZ TABELĘ produkty (id INT PRIMARY KEY, nazwa VARCHAR(100), cena DECIMAL(8,2));

• ZMIENIAĆ: Aby zmodyfikować strukturę istniejącego obiektu.

  ALTER TABLE produkty DODAJ opis VARCHAR(255);

  ALTER TABLE produkty ALTER COLUMN cena SET NOT NULL;

• UPUSZCZAĆ: Aby usunąć obiekty z bazy danych.

  Produkty DROP TABLE;

  USUŃ UŻYTKOWNIKA przykładowy_użytkownik CASCADE;

Widoki (VIEW): Ponowne wykorzystanie zapytań i zaawansowane filtrowanie

Widoki to wirtualne obiekty reprezentujące zapisane zapytanie. Działają jak tabela, ale nie przechowują danych; wskazują jedynie na informacje w innych tabelach. Są przydatne do upraszczania złożonych zapytań, centralizacji filtrów i ułatwiania dostępu do określonych informacji.

UTWÓRZ WIDOK active_employees JAKO WYBIERZ * Z employees WHERE active = 1;

Można wykonać zapytanie do widoku tak jak do tabeli:

WYBIERZ * Z aktywnych_pracowników;

Widoki idealnie nadają się do zarządzania uprawnieniami i wyświetlania wyłącznie danych istotnych dla wybranych użytkowników.

Funkcje agregujące i obliczenia na kolumnach

Język SQL umożliwia pracę z funkcjami agregującymi i operatorami arytmetycznymi w celu wykonywania obliczeń na danych:

• SUMA(): Suma całkowita kolumny.
• Śr.(): Średnia arytmetyczna.
• MIN() i MAX(): Minimum i maksimum.
• LICZYĆ(): Liczba rekordów.

Na przykład, jeśli chcesz poznać liczbę pracowników w każdym dziale:

WYBIERZ dział, LICZ(*) Z pracowników GRUPUJ WEDŁUG działu;

Lub najwyższe wynagrodzenie według działu:

WYBIERZ dział, MAX(wynagrodzenie) Z pracowników GRUPUJ WEDŁUG działu;

Możesz nawet tworzyć kolumny obliczeniowe bezpośrednio w zapytaniu:

WYBIERZ imię, pensja * 0.95 AS pensja_netto OD pracowników;

Używanie aliasów (AS) dla tabel i kolumn

W zapytaniach możesz przypisywać kolumny i tabele tymczasowych nazw za pomocą polecenia AS . Ułatwia to odczytywanie wyników i pisanie złożonych zapytań.

Na przykład:

WYBIERZ e.name JAKO pracownik, d.name JAKO dział Z pracownicy JAKO e DOŁĄCZ do działów JAKO d NA e.department_id = d.id;

W ten sposób wynik wyświetli kolumny z niestandardowymi nazwami. Używanie aliasów jest szczególnie przydatne podczas wykonywania JOIN-ów lub gdy kolumny mają długie lub nieopisowe nazwy.

Przykłady zaawansowanych zapytań SQL: rozwiązywanie przypadków z życia wziętych

Aby stać się samowystarczalnym w SQL, musisz stawić czoła wyzwaniom w świecie rzeczywistym. Poniżej znajdują się rozbudowane przykłady zaczerpnięte z praktyki, które pomogą Ci utrwalić wiedzę.

Zapytaj wszystkich pracowników w dziale i sprawdź ich całkowitą sprzedaż

WYBIERZ e.id, e.name, SUMA(v.amount) JAKO total_sales OD pracowników e DOŁĄCZ sprzedaż v NA e.id = v.employee_id GDZIE e.department = 'Sprzedaż' GRUPUJ WEDŁUG e.id, e.name;

W tym przykładzie pokazano, jak łączyć tabele, stosować funkcje agregujące i filtrować wyniki.

Obliczanie średniego i maksymalnego wynagrodzenia według działu

SELECT dział, AVG(wynagrodzenie) JAK średnia_wynagrodzenie, MAX(wynagrodzenie) JAK maksymalna_wynagrodzenie OD pracowników GRUPUJ WEDŁUG działu;

W ten sposób możesz przeanalizować rozkład wynagrodzeń i zidentyfikować nierówności lub obszary o większych zachętach.

Liczenie produktów według przedziału cenowego za pomocą CASE

WYBIERZ PRZYPADEK GDY cena < 50 WTEDY 'Tani' GDY cena < 150 WTEDY 'Średni' W PRZECIWNYM PRZYPADKU 'Drogi' ZAKOŃCZ JAKO zakres cenowy, LICZ(*) JAKO ilość Z produktów GRUPUJ WEDŁUG zakresu cenowego;

Użycie CASE umożliwia tworzenie niestandardowych kategorii w zapytaniu.

Przykładowe zapytanie w celu znalezienia duplikatów rekordów

WYBIERZ nazwę, LICZBA(*) Z klientów GRUPUJ WEDŁUG nazwy MAJĄC LICZBĘ(*) > 1;

Dzięki temu możesz utrzymać bazę danych w czystości i pozbyć się zbędnych danych.

Zidentyfikuj n-tą najwyższą pensję (podzapytania)

WYBIERZ TOP 1 wynagrodzenie Z ( WYBIERZ WYRAŹNIE TOP N wynagrodzenie Z pracowników UPORZĄDKUJ WEDŁUG wynagrodzenia MALEJĄCO ) UPORZĄDKUJ WEDŁUG wynagrodzenia ASC;

Doskonale nadaje się do odpowiadania na pytania podczas rozmów kwalifikacyjnych o pracę na stanowiskach technicznych lub tworzenia zaawansowanych raportów.

Automatyczne usuwanie starych rekordów

USUŃ Z dzienniki GDZIE data < TERAZ() - INTERWAŁ 6 MIESIĘCY;

Idealne dla baz danych produkcyjnych, w których konieczna jest kontrola rozmiarów tabel.

Przydatne wskazówki i triki w SQL

Opanowanie SQL to nie tylko umiejętność pisania podstawowych zapytań. Oto kilka wskazówek, które pomogą Ci w pełni wykorzystać możliwości tego języka:

• Zawsze używaj WHERE w UPDATE i DELETE aby zapobiec przypadkowym masowym modyfikacjom lub usunięciom.
• Umieść indeksy w najczęściej konsultowanych kolumnach aby przyspieszyć wyszukiwanie i łączenie.
• Preferuj wyraźne sprzężenia zwrotne zamiast sprzężeń WHERE w celu łączenia tabel: ułatwia konserwację i czytelność.
• Użyj funkcji agregujących i GROUP BY do automatycznego raportowania i analiz bez konieczności korzystania z zewnętrznych arkuszy kalkulacyjnych.
• Test na bazie danych testowej przed wprowadzeniem większych zmian.
• Kontroluj uprawnienia za pomocą GRANT i REVOKE aby zapewnić bezpieczeństwo Twoich danych.
• Skorzystaj z widoków (VIEW) aby uprościć dostęp do złożonych informacji i scentralizować logikę biznesową.
• Nie nadużywaj SELECT *, ponieważ możesz pobrać więcej danych, niż jest to konieczne, co spowolni działanie systemu.
• Komentarze do złożonych zapytań aby ułatwić przyszłą konserwację.

Często zadawane pytania dotyczące poleceń SQL na rozmowach kwalifikacyjnych i w rzeczywistych przypadkach

Znajomość języka SQL jest niezbędna do osiągnięcia sukcesu w rozmowach kwalifikacyjnych i rozwiązywania codziennych problemów. Oto kilka typowych pytań i odpowiedzi, które pomogą Ci się przygotować:

• Czym jest INNER JOIN?
  To jest domyślne łączenie w SQL; zwraca wiersze, które pasują do obu tabel. Przykład:

  WYBIERZ * Z POŁĄCZENIA WEWNĘTRZNEGO B NA A.id = B.a_id;

• Do czego służy LEFT JOIN?
  Zwraca wszystkie wiersze z lewej tabeli i wszystkie dopasowania z prawej tabeli. Przykład:

  WYBIERZ * Z pracowników LEWO DOŁĄCZ sprzedaż NA employees.id = sales.employee_id;

• Jaka jest różnica pomiędzy poleceniami DROP i DELETE?
  DELETE usuwa wiersze z tabeli, podczas gdy DROP całkowicie usuwa obiekt (tabelę, widok, użytkownika) z bazy danych.
• Czy można wycofać zmiany po operacji ALTER?
  Nie, ponieważ ALTER jest DDL i wiele systemów zatwierdza automatycznie. Ważne jest, aby upewnić się przed użyciem.
• Czym są pseudokolumny?
  Są to wirtualne kolumny generowane przez system, takie jak ROWID, USER lub CURRVAL w Oracle.
• Jak utworzyć widok?

  UTWÓRZ WIDOK view_employees AS WYBIERZ nazwę, pensję Z pracownicy;

• Jak przyznać uprawnienia?

  UDZIELENIE ZEZWOLENIA NA WYBÓR EKWIWALENTU DLA ana, rita;

• Jak usunąć użytkownika wraz z jego danymi?

  USUŃ UŻYTKOWNIKA użytkownik CASCADE;

Wyrażenia regularne i operatory specjalne w SQL

Mniej znaną, ale bardzo potężną częścią SQL jest możliwość wyszukiwania wzorców za pomocą wyrażeń regularnych i zaawansowanych operatorów. Na przykład:

• % : Reprezentuje dowolny ciąg znaków o dowolnej długości (używany w LIKE).
• _ : Reprezentuje pojedynczy znak (również w LIKE).
• | : Naprzemienność, poszukiwanie jednej rzeczy lub drugiej.
• *: Powtórzenie znaku zero lub więcej razy.

Zaawansowane zarządzanie: role, uprawnienia i zabezpieczenia

Zarządzanie bazą danych to coś więcej niż tylko wyszukiwanie i modyfikowanie danych. Kluczowe jest kontrolowanie, kto może uzyskiwać dostęp do informacji, modyfikować je lub usuwać, za pomocą poleceń takich jak GRANT , REVOKE lub zaawansowanego zarządzania ROLE.

UTWÓRZ ROLĘ manager;

UDZIELENIE SELECT, INSERT, UPDATE pracownikom menedżerowi;

Menedżer ds. dotacji dla Juana, Marii;

Pozwala to scentralizować zabezpieczenia i uprościć zarządzanie uprawnieniami w dużych środowiskach. Więcej informacji na temat zarządzania uprawnieniami można znaleźć w naszym artykule na temat automatyzacji i administracji.