Articles

Wprowadzenie do SQL: przykłady, najlepsze praktyki i pułapki

Posted by admin

SQL (Structured Query Language) jest z nami od ponad pół wieku i nie zniknie w najbliższym czasie. Popularny zarówno w tradycyjnych relacyjnych bazach danych, jak i nowszych technologiach baz danych NoSQL, SQL jest szeroko stosowany do analizy danych, przetwarzania dużych zbiorów danych, języków kodowania i innych.

jestem fanem SQL. To potężny, ale prosty język. W tym poście podzielę się:

  • moc SQL i dlaczego go używamy
  • typowe pułapki i najlepsze praktyki

co to jest SQL i dlaczego warto go używać?

SQL został początkowo opracowany przez IBM na początku lat 70., przyjęty przez Oracle w późniejszej dekadzie i od tego czasu jest używany w relacyjnych bazach danych. W 1986 r. SQL został przyjęty jako standard przez American National Standards Institute (ANSI).

wraz ze wzrostem ilości danych pojawiły się nowe bazy danych i technologie. Jednym z nich jest Big Data, dla zbiorów danych zbyt złożonych dla tradycyjnych aplikacji do przetwarzania danych. W przypadku dużych zbiorów danych bazy danych NoSQL nie są oparte na modelu relacyjnym. Czasami nazywa się je nie tylko SQL, aby podkreślić, że mogą obsługiwać język zapytań podobny do SQL. Niektóre przykłady nowszych nierelacyjnych baz danych i bibliotek obsługujących SQL obejmują:

  • Apache Spark Analytics Engine
  • Google Big Query – zarządzany magazyn danych analitycznych
  • Facebook Presto i AWS Athena
  • elastyczne wyszukiwanie
  • Python Data Analysis Library (pandasql)

SQL jest szeroko stosowany zarówno w OLTP (przetwarzanie transakcji), jak i OLAP (przetwarzanie analityczne), mimo że dwa z nich są bardzo różnymi przypadkami użycia:

Snippet 1: pandasql

przetwarzanie transakcji przetwarzanie analityczne
często używane relacyjne bazy danych Duże bazy danych
użycie SQL Wstawianie, aktualizacja i zapytania przez wielu użytkowników jednocześnie Wybierz zapytania. Kilka zapytań na raz
Skala obliczeń mała duża – całe tabele
czas trwania zapytania szybki może wynosić godziny lub więcej
funkcje związane z wydajnością Indeksy i klucze partycje, nadmiarowe dane (na każdy przypadek użycia)
funkcje analityczne rzadko używane często

przed przejściem do SQL, oto przykład użycia SQL poza bazami danych-Python ’ s Data Analysis Library:

Kod
import pandas as pdimport pandasql as psdata_frame = pd.DataFrame(, , , ], columns=)sql = """SELECT name, COUNT(DISTINCT id) FROM data_frame GROUP BY NAME """print(ps.sqldf(sql, locals()))
 Name COUNT(DISTINCT ID)0 James 11 John 22 Ted 1

Jak pokazują te przykłady, SQL żyje i ma się dobrze w najnowszych i najpopularniejszych technologiach.

przyjrzyjmy się bliżej jego mocy.

przykłady funkcji SQL

najsilniejszą częścią SQL są jego funkcje. Oto trzy typy funkcji obsługujących SQL:

Typ zakres redukcja rekordów przykłady
prosty pojedynczy rekord Nie CASE … WHEN 1 THEN column_1 WHEN 2 THEN column_2 ELSE 3 END
GREATEST (column_1, column_2, column_3)
COALESCE (column_1)
zagregowane (Grupa wg) Grupa rekordów tak SUM(column_1)
AVG(column_1)
analityczne Grupa rekordów Nie SUM(column_1) OVER (PARTITION BY column_2)
LEAD/LAG - value of a field in the next or previous record

zalecamy zapoznanie się z funkcjami mechanizmu zapytań, aby zrozumieć jego możliwości. Na przykład tutaj można znaleźć dokumentację Presto dotyczącą funkcji agregujących i funkcji analitycznych.

zalecane konwencje kodowania SQL (nazewnictwo i inne)

aby uzyskać moc SQL, zalecamy używanie konwencji kodowania. Poprawi to zarówno pisanie, jak i czytanie, a także usprawni pracę zespołową. Oto krótka lista najważniejszych:

nazwy i wielkość liter użyj podkreślników, w których naturalnie umieścisz spację w nazwie (nazwisko -> ostatnia nazwa)
wielkie litery dla słów kluczowych i funkcji (SELECT, FROM, WHERE, IN, SUM…)
małe litery dla wszystkiego innego: zmiennych, nazw tabel, kolumn itp.
jawnie użyj słów kluczowych w połączeniach użyj słów kluczowych: CROSS, INNER… ON, LEFT … ON, RIGHT… on
unikaj łączenia jako części klauzuli WHERE: SELECT … FROM table_a, table_b WHERE…
Aliasing – zawsze Dołącz słowo kluczowe AS
unikaj zagnieżdżonych formularzy, używaj zamiast nich unikaj SELECT … FROM (SELECT …)

Używaj z klauzulami! W ten sposób możesz uniknąć złożonych zapytań i tworzyć czytelne zapytania krok po kroku

unikaj łączenia w / nie w użyciu.

odstępy między wierszami przed i lub po każdej definicji słowa kluczowego
Dołącz komentarze zarówno/*, jak i zamykanie * / i — są dopuszczalne
nie używaj SELECT * tak, jest kompaktowy i łatwy, ale nowe kolumny są dodawane i dołączane do wyniku zapytania bez potrzeby. Napisz wyraźnie nazwę kolumny, której potrzebujesz

przykłady zapytań SQL: dobry & zły

aby pokazać, jak ważne są konwencje kodowania, spójrz na te dobre i złe przykłady.

Typ dobry zły
Flow dwa proste zapytania jedno złożone zapytanie
słowa kluczowe Wielkie Litery małe litery, jak inne teksty
Dołącz Explicit-łatwy do zrozumienia Implicit – z innymi predykatami WHERE
wcięcia odstępy między wierszami przed lub pomaga poznać jego zakres brak odstępów między wierszami przed lub. Większy stan będzie znacznie trudny do zrozumienia
używany nie używany

fragment 4: zły przykład

select emp.department, sum(exp.amount) expensesfrom employees emp, expenses expwhere emp.department in (select departmentfrom employeeswhere department like '%development%' or department like '%management%'group by departmenthaving count(distinct id) > 50) and emp.id = exp.employee_idgroup by emp.department

fragment 5: dobry przykład

-- filter: Development and Management departments with more than 50 employeesWITH dept AS (SELECT departmentFROM employeesWHERE department LIKE '%Development%'OR department LIKE '%Management%'GROUP BY departmentHAVING Count(DISTINCT id) > 50)-- find expenses for each of the filtered departmentsSELECT emp.department,SUM(exp.amount) AS expensesFROM employees AS empINNER JOIN deptON emp.department = dept.departmentINNER JOIN expenses AS expON emp.id = exp.employee_idGROUP BY emp.department

oto ładne narzędzie online do formatowania SQL, którego możesz użyć: https://sqlformat.org/

problemy SQL Rozwiązywanie problemów-błędy & pułapki

jak każdy inny język, SQL ma swoje własne pułapki, o których powinieneś wiedzieć, aby zaoszczędzić czas i zapobiec błędom w aplikacji.

pierwsza przyczyna błędów SQL& błędy – wartości Null

fragment 6: wynik zapytania NULL

SELECT NULL = NULL --> NULLSELECT NULL != NULL --> NULLSELECT NULL IS NULL --> trueSELECT 1 + NULL --> NULL SELECT SUM(num_column) WHERE FALSE --> NULL

aby rozwiązać ten problem, należy użyć coalesce, gdy istnieje niebezpieczeństwo:

fragment 7: przezwyciężyć Null w połączeniach

FROM t1INNER JOIN t2 ON COALESCE(t1.name, '') = COALESCE(t2.name, '')

indeksy sql i partycje nie są używane

spędzamy dużo czasu na budowaniu naszego schematu bazy danych poprzez dodawanie indeksów i deklarowanie partycji. Ale nie osiągniemy pożądanej wydajności, jeśli na tym poprzestaniemy – musimy upewnić się, że nasz silnik bazy danych ich używa.

na przykład załóżmy, że mamy indeks w kolumnie first_name:

SELECT first_nameFROM employeeWHERE UPPER(first_name) LIKE 'A%'
indeks nieużywany
SELECT first_nameFROM employeeWHERE first_name LIKE 'A%' OR first_name LIKE 'a%'
używany indeks

mogą występować zmiany z jednej implementacji na drugą, ale koncepcja jest ważna w każdej implementacji SQL.

jak upewnić się, że używane są indeksy i partycje? Przeanalizuj swój plan realizacji zapytań. W niektórych implementacjach SQL można użyć słowa kluczowego EXPLAIN. Na przykład w Oracle nazywa się to planem wyjaśnień.

funkcje analityczne pułapki w kolejności oceny SQL

ta jest ważna, jeśli używasz funkcji analitycznej, ponieważ niezrozumienie kolejności oceny może prowadzić do błędów. Na przykład:

fragment 8: Funkcja analityczna z filtrem

SELECT first_name, last_name, department, COUNT(1) OVER (PARTITION BY department) AS size_of_departmentFROM employeesWHERE last_name LIKE 'A%'

otrzymamy liczbę pracowników w dziale z nazwiskiem zaczynającym się na A, zamiast wszystkich pracowników w dziale. Pamiętaj, że funkcje analityczne są wykonywane jako ostatnie.

standaryzacja SQL

składnia daty i czasu, wartości null, konkatenacja łańcuchów, wrażliwość na przypadki porównawcze i inne aspekty SQL różnią się w zależności od dostawcy – co utrudnia przenoszenie aplikacji między dostawcami.

spróbuj użyć tylko standardowego SQL. Na przykład – użyj COALESCE zamiast ISNULL (SQL Server) lub NVL (Oracle).

zapobieganie atakom SQL Injection (SQLi)

dobrze znaną pułapką bezpieczeństwa w aplikacjach internetowych jest atak SQL injection – technika wstrzykiwania kodu, która atakuje aplikacje poprzez wstawianie instrukcji do pola w celu wykonania. Takie ataki są zwykle używane do kradzieży danych, zmiany danych lub zmiany uprawnień w zaatakowanej bazie danych.

zapobieganie atakom SQL Injection rozpoczyna się w trakcie programowania, a następnie należy użyć skanerów i zapór sieciowych aplikacji internetowych do ochrony aplikacji. Przykłady i sposoby zapobiegania takim atakom można znaleźć w poście na blogu SQL Injection.

SQL jest świetny. Osobiście wolę język oparty na SQL od dowolnego innego języka zapytań lub API. Musisz jednak używać go poprawnie, aby ułatwić sobie pracę. Zacznij od poprawnego budowania schematu, a później użyj konwencji kodowania i innych opcji, takich jak ze słowem kluczowym i funkcjami okna.

nie zapomnij pamiętać o znanych pułapkach. A jeśli piszesz aplikację, chroń ją przed SQL Injection.

wprowadzenie do SQL: Przykłady, najlepsze praktyki i pułapki appeared first on Blog.

* * * jest to blog o bezpieczeństwie, którego autorem jest Ori Nakar. Przeczytaj oryginalny post na stronie: https://www.imperva.com/blog/introduction-to-sql-examples-best-practices-and-pitfalls/

Related Post