Archiwum kategorii: KURS SQL

KURS SQL

SQL Dobre praktyki przy pisaniu zapytań sql cz.2.

Z uwagi na to, że artykuł „SQL Dobre praktyki przy pisaniu zapytań sql.” cieszy się dużą popularnością pozwoliłem sobie dopisać część nr 2 🙂

No to zaczynamy!

1. Zawsze prezentuj to co niezbędne i tylko to.

Początkujący programiści mają taką „przypadłość”, że często zostawiają znak ” * ” w części SELECT. Przeważnie nasze zapytanie nie musi zwracać wszystkich kolumn z wszystkich tabel które występują w zapytaniu. Ogranicz więc ilość zwracanych kolumn w części SELECT do niezbędnego minimum. Na pewno będzie to miało pozytywny wpływ na szybkość wykonywania się zapytania. To samo tyczy się podzapytań w środku zapytania głównego.

2. Poprawnie dobieraj typy danych do poszczególnych kolumn.

Ta porada dotyczy bardzie tworzenia struktury danych ale postanowiłem także o niej wspomnieć.

Jeśli nie ma potrzeby stosowania „większego” typu danych to stosuj jak „najmniejszy” typ danych. Pamiętaj że różne typy to różna ilość bajtów na dane a co za tym inna wielkość potrzebna na przechowywanie Twoich danych na dysku. Np. po co stosować typ bigint do kolumny id w tabelach słownikowych skoro „zwykły” int w zupełności wystarczy. Po co przechowywać samą datę w typie datetime skoro chcemy przechowywć tylko i wyłącznie datę bez czasu. Pamiętaj datetime to 8 bajtów a date tylko 3. itd.

3. Przy porównaniach zadbaj o zgodność typów.

Na początek dam przykład:

WHERE timestampdate BETWEEN '2000-01-01′ AND '2000-12-31′ — gdzie timestampdate jest typu timestamp.

W przykładzie powyżej timestampdate jest typu timestamp a daty w BETWEEN-ie typu date. Generalnie zapytanie zadziała ale kompilator będzie musiał konwertować w locie jeden typ na drugi a to już jest czas 🙂

SQL Dobre praktyki przy pisaniu zapytań sql.

Czesto w sieci widzę pytania typu: jak dobrze pisać zapytania SQL? albo jakie są dobre praktyki pisania zapytań sql? itp. Postanowiłem podzielić się z Wami kilkoma poradami tego typu. Modyfikacja "swojego warsztatu" zalezy oczywiście od stopnia Twojego zaawandowania w pisaniu zapytań ale może komus się przydadzą. Podane poniżej porady stosuję osobiście i wynikaja one z mojego długoletniego doświadczenia w pracy z bazami danych.

1. Nie rzucaj sie od razu na głęboką wodę.

Jeśli masz do napisania jakieś skomplikowane zapytanie to dobrym sposobem jest rozbić je sobie na kilka kroków. Nie zaczynaj od razu pisać postaci finalnej tylko dojdź do tej postaci etapami. Chodzi o to, że jak zbudujesz dość duże zapytanie to ciężko jest później szukać ewentualnych błędów a tak jak podzielisz prace na mniejsze etapy to za każdym razem przy końcu danego etapu możesz sprawdzić poprawność swojego zapytania.

2. Podzapytania testuj osobno.

Często się zdarza, że w swoim zapytaniu potrzebujesz utworzyć podzapyanie. Ja podzapytania staram się budować obok głównego zapytania i dopiero jak sie upewnię, że działa ono poprawnie dołączam je do zapytania głównego. Czasami zdarza się, że Twoje zapytanie zwraca błędne wyniki a Ty ciągle analizujesz "główne zapytanie" a tak naprawdę to Twoje podzapytanie może zwracać błędne wyniki cząstkowe.

3. Poprawność, czytelność, optymalizacja

Bardzo często za szybko i za dużo chcesz zrobić w jednym kroku. Od razu przy budowaniu myślisz o optymalizacji i to nie jest generalnie złe. Ale często za bardzo skupiasz się na optymalizacji na samym początku i cierpi na tym poprawność i czytelność zapytania.

Moim zdaniem lepiej jest zacząć od poprawności i czytelności zapytania. Oczywiście kluczowa jest tutaj poprawność ale NIE bagatelizujcie czytelności zapytania. Czasami na szybko próbujesz coś stworzyć. Piszesz i piszesz i Twoje zapytanie staje się juz dosyć sporawe i nagle błąd. Bez zadbania od razu o czytelność zapytania trudno Ci będzie szybko zdiagnozować problem.

4. Zapoznaj się z danymi.

Jak nie poznasz dobrze zbioru danych do którego będziesz pisał zapytania to przygotuj się na drogę przez mękę. Poświęć czas na poznanie tabel, zależności pomiędzy nimi i typów danych w kolumnach itd. Ale także a może przede wszystkim zastanów się jakie wartości mogą przechowywać Twoje tabele/kolumny i głównie chodzi mi tutaj np o NULL-e. Bardzo często to właśnie wartości null mogą bardzo pomóc lub wygenerować jakiś nichciany błąd. Powiedzmy, że mamy kolumnę "DataKoniecZatrudnienia" i tam przy niektórych pracownikach są wartości NULL. Oznacza to, że jeśli jest NULL to pracownik jeszcze pracuje w firmie bo w tej klumnie nie ma konkretnej wartości i w przykładowym zapytaniu np.: pokaż mi wszystkich pracowników, którzy pracują aktualnie w firmie ta informacja może bardzo pomóc. Ale np. okazuje się, że są przypadki kiedy przy pracowniku nie ma ani daty zatrudnienia ani daty końca zatrudnienia i oznacza to, że pracownik jest zatrudniany np. na umowy zlecenia ale jest wykazywany na liście pracowników itp.

Sam widzisz, że kombinacji może być wiele i bez dobrego rozpoznania tematu można nieźle zamieszać zapytaniem.

5. Do poprawności analizuj mniejsze ilości danych lub pojedyncze małe lokalizacje/działy.

Jak napiszamy sobie zapytanie które "wypluwa" nam dziesiątki tysięcy, a może i więcej, rekordów to skąd będziesz wiedział, że zapytanie zwraca poprawne wyniki? Żeby zminimalizować ryzyko wykonuj swoje zapytania na mniejszych ilościach danych np. z jednego miesiąca lub np. z jednego działu firmy itp. Niestety mniejsza ilość danych nie gwarantuje oczywiście pełnego sukcesu bo ograniczamy także ilość możliwości wystąpienia takichś niestandardowych sytuacji. Chodzi tylko o to, że np. na mniejszej ilości danych jesteś w stanie szybciej przeanalizować poprawność wyników zapytania.

6. Robisz UPDATE? Pamiętaj o kryteriach WHERE.

Informatyk jest jak saper. Niektóre czynności robi tylko raz i z reguły to wystarczy żeby uczyć się na swoich błędach. Czasami jednak straty są już nie do naprawienia. Przykład z życia. Pewien znajomy informatyk napisał sobie UPDATE-a w celu zmiany ceny jednego produktu w sklepie internetowym. Jakieś było jego zdziwienie jak po chwili wszystkie produkty miały takią samą cenę. Jeśli modyfikujesz jeden lub tylko kilka rekordów to przyjmij zasadę UPDATE = WHERE czyli jeśli stosujesz UPDATE to musi wystąpić WHERE z warunkami opisującymi który rekord, lub grupę rekordów, zmodyfikować w innym przypadku "polecisz po całej tabeli" i będzie rozmowa dyscyplinująca z przełożonym (w najlepszym przypadku).

To na dzisiaj tyle. Myślę, że w miarę możliwości będę ten post rozbudowywał lub dorzucał jego kolejne części.

 

 

SQL UPDATE

SQL UPDATE


    


DEFINICJA

Polecenie UPDATE służy do modyfikacji wartości w rekordach tabeli. Po poleceniu UPDATE określamy gdzie, w której tabeli, chcemy zmodyfikować rekord. Po słówku SET określamy które pola w rekordzie chcemy zmodyfikować i jakie wartości chcemy im wstawić. I na sam koniec określamy, który rekord chcemy zmodyfikować czyli korzystamy z warunków po klauzuli WHERE.

SQL UPDATE składnia

UPDATE
    nazwa_tabeli
SET
    nazwa_kolumny1 = wartość1 , nazwa_kolumny2 = wartość2 — itd
WHERE
    warunki_filtrowania_rekordów

PAMIĘTAJ, musisz bardzo dobrze określać warunki dotyczące filtrowania rekordów, jeżli źle je określisz możesz zmodyfikować większą liczbę rekordów (przy okazji tych których nie chciałeś modyfikować). Najlepiej rekordy do modyfikacji znajdować po jakimś niepowtarzalnym identyfikatorze, wtedy mamy pewność, że modyfikujemy dobry rekord.

 


Przykład zastosowania UPDATE (baza Adventureworks)

Zamień imię "Ken" na "Jon" przy osobie Ken Sanchez.

UPDATE Person.Person
SET FirstName = 'Jon'
WHERE BusinessEntityID = 1

 INFO: Ktoś może zapytać dlaczego nie wyszukujemy do modyfikacji rekordu po imieniu i nazwisku. Odpowiedź jest prosta, bo jeśli w bazie znalazły by się dwie osoby o takim samym imieniu i nazwisku to system zmodyfikowałby imiona przy obu tych osobach. Dlatego musimy znaleźć taką wartość która jednoznacznie identyfikuje dany rekord. W przypadku tabeli Person.Person taką kolumną jest BusinessEntityID a rekord z osobą Ken Sanchez ma w tym polu wartość 1. Dlatego nasz warunek który jednoznacznie identyfikuje rekrod z Kenem wygląda następująco WHERE BusinessEntityID = 1.


Przykład zastosowania UPDATE (baza Northwind)

Ktoś zauważył, że w systemie Pani Nacy Davolio ma wpisane, że pracuje o rok dłużej, popraw ten błąd (obecna data zatrudnienia w bazie to 1992-05-01).

UPDATE Employees
SET HireDate = '1993-05-01 00:00:00.000'
WHERE EmployeeID = 1

INFO: Tak jak w przykładzie z bazy AdventureWorks nie wyszukujemy rekordu po imieniu i nazwisku bo może zdarzyć się taka sytuacja, że w bazie jest więcej osób o tym samym imieniu i nazwisku. Dlatego szukamy pola którego wartość jednoznacznie identyfikuje rekord który szukamy w naszym przypadku jest to pole EmployeeID. Ponieważ rekord z danymi dotyczącymi Pani Davolio ma identyfikator 1 to warunek wygląda tak jak na powyższym przykładzie.


   

SQL INSERT INTO

SQL INSERT INTO


    


DEFINICJA

Polecenie INSERT INTO służy do wstawiania/tworzenia nowych rekordów w tabeli. Ogólnie rzecz biorą po INSERT INTO wskazujemy tabelę do której chcemy dołożyć rekord, musimy określić do których kolumn chcemy "wrzucić" wartości i określić te wartości, składnia poniżej.

SQL INSERT INTO składnia

INSERT INTO nazwa_tabeli(nazwa_kolunny1, nazwa_kolunny2, nazwa_kolunny3, itd)
VALUES (wartość1, wartość2, wartość3, itd)

PAMIĘTAJ, żeby w nawiasie określającym nazwy kolumn było tyle samo parametrów co w nawiasie określającym wartości pól w poszczególnych kolumnach.

Istnieje jeszcze jedna forma tego polecenia a mianowicie

INSERT INTO nazwa_tabeli
VALUES (wartość1, wartość2, wartość3, itd)

 W tym przypadku nie określamy kolumn. Ja osobiście jestem zwolennikiem pierwszego rozwiązania, czyli jawnego deklarowania wszystkiego co można, co pozwala uniknąć późniejszych problemów szczególnie jeżeli daną bazą administruje większa liczba osób.


Przykład zastosowania INSERT INTO (baza Adventureworks)

Dodaj do typów osób w bazie nowy typ "Temporary Worker".

INSERT INTO Person.ContactType(Name,ModifiedDate)
VALUES('Temporary Worker' , '2017-01-01 00:00:00.000')


Przykład zastosowania INSERT INTO (baza Northwind)

Dodaj nowego dostawcę do tabeli "Suppliers".

INSERT INTO Suppliers(CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone,Fax,HomePage)
VALUES ('Zzimo','Rob Zikosen','Sales Manager','Zielona 4','Warszawa','NULL','00-900','Poland','(22)127624','(22)735483',NULL)

INFO: do kolumny "SupplierID" nie trzeba "wpisywać" wartości, zostanie ona wygenerowana automatycznie.  


   

SQL DROP INDEX

SQL DROP INDEX


    


DEFINICJA

Polecenie DROP INDEX służy do usuwania indeksów z wybranej tabeli. W różnych środowiskach bazodanowych składnia do usuwanięcia indeksu wygląda trochę inaczej. W tym wpisie pokażę składnię w wybranych środowiskach.

SQL DROP INDEX składnia dla MySQL

ALTER TABLE nazwa_tabeli DROP INDEX nazwa_indeksu


SQL DROP INDEX składnia dla SQL SERVER

DROP INDEX nazwa_tabeli.nazwa_indeksu


SQL DROP INDEX składnia dla ORACLE

DROP INDEX nazwa_indeksu


   

SQL DELETE

SQL DELETE


    


DEFINICJA

Polecenie DELETE służy do usuwania rekordów (wierszy) ze wskazanej tabeli. Po poleceniu DELETE określamy gdzie, w której tabeli, chcemy usunąć rekord. Po słówku WHERE definiujemy warunki, które konkretnie rekordy chcemy usunąć. Pamiętaj, z poleceniem DELETE trzeba się obchodzić bardzo starannie, trzeba bardzo jasno określić które rekordy chcemy usunąć, bo można nieodwracalnie usunąć potrzebne rekordy. Przed zastosowaniem polecenia DELETE, dobrą praktyką jest użycie zwykłego SELECT-a w połączeniu z warunkami w WHERE, w celu wizualizacji rekordów które chcesz skasować, wtedy masz pewność, że usuwasz prawidłowe dane.

SQL DELETE składnia

DELETE FROM
    nazwa_tabeli
WHERE
    warunki_filtrowania_rekordów

UWAGA: Jeżeli nie określisz warunków w części WHERE skasujesz wszystkie rekordy we wskazanej tabeli!


Przykład zastosowania DELETE (baza AdventureWorks)

Usuń rekord z tabeli osób dotyczący Terri Duffy.

DELETE FROM
    Person.Person

WHERE
    BusinessEntityID = 2

INFO: Pamiętaj, żeby usunąć ten rekord który chcemy musimy dokładnie określić warunki. Jeżeli chcemy usunąć dokładnie jeden rekord musimy posłużyć się unikalnym polem w skali kolumny który zidentyfikuje ten właśnie rekord. W przypadku tabeli Person.Person w bazie AdventureWorks tym polem jest BusinessEntityID. 

Dlaczego w warunku nie posłużyliśmy się imieniem i nazwiskiem? Dlatego, że jeżeli mielibyśmy taki przypadek, że w bazie znalazłyby się dwie osoby o takim samym imieniu i nazwisko system usunąłby obie.


Przykład zastosowania DELETE (baza Northwind)

Usuń z zamówienie o identyfikatorze 10625 produkt o identyfikatorze 60.

DELETE FROM
    OrderDetails

WHERE
    OrderID = 10625 AND ProductID = 60

INFO: w warunku wskazaliśmy zestawienie dwóch warunków bo taka kombinacja gwarantuje nam już wskazanie jednego, interesującego nas, rekordu.


   

SQL ROUND() function

SQL ROUND() function


    


DEFINICJA

Funkcja ROUND() służy do zaokrąglania pól numerycznych do zdefiniowanego przez użytkownika miejaca dziesiętnego.

SQL ROUND() składnia funkcji

SELECT
    ROUND( pole_numeryczne , ilość_miejsc_po_przecinku )
FROM
    tabela

Parametry:
pole_numeryczne – pola których wartości wyrażone są w postaci numerycznej np. liczba zmiennoprzecinkowa
ilość_miejsc_po_przecinku – tutaj określamy z jaką dokładnością chcemy zaokrąblicz liczbę podaną w pierwszym parametrze (ile miejsc po przecinku)


Przykład zastosowania funkcji ROUND()

SELECT
    ROUND( 123.456 , 2)

Wynik: 123,45 


    

SQL CONVERT() function

SQL CONVERT() function


    


DEFINICJA

Funkcja CONVERT() to druga, oprócz funkcji CAST(), funkcja służąca do konwersji danych jednego typu na drugi. Przykładam: konwersja liczb na tekst, tekstu na liczbę lub daty na tekst.

SQL CONVERT() składnia funkcji

SELECT
    CONVERT( nowy_typ_danych , wartość_konwertowana , styl_danych_po_konwersji)

Parametry:
nowy_typ_danych – określamy na jaki typ danych chcemy konwertować wskazacą wartość
wartość_konwertowana – określamy jaką wartość chcemy konwertować
styl_danych_po_konwersji – (OPCJONALNIE) możemy określić stylk danych po konwersji. Dotyczy np. daty i czasu ale także liczb np. określnie precyzji.


Przykład zastosowania funkcji CONVERT()

SELECT
    '1 styczeń 2017 = ' + CONVERT( VARCHAR(10) , '2017-01-01' )


    

SQL CAST() function

SQL CAST() function


    


DEFINICJA

Funkcja CAST() służy do konwersji danych jednego typu na drugi. Przykładami konwersji może być konwersja liczb na tekst lub odwrotnie oraz daty na tekst.

SQL CAST() składnia funkcji

SELECT
    CAST( wartość_konwertowana AS nowy_typ_danych)

Parametry:
wartość_konwertowana – określamy jaką wartość chcemy konwertować
nowy_typ_danych – określamy na jaki typ danych chcemy konwertować wskazacą wartość


Przykład zastosowania funkcji CAST()

SELECT
    '1 styczeń 2017 = ' + CAST( '2017-01-01' AS VARCHAR(10) )


    

SQL SUBSTRING() function

SQL SUBSTRING() function


    


DEFINICJA

Funkcja SUBSTRING() służy do wyodrębnienia ciągu znaków z tekstu znajdującego się w polach o typie tekstowym.

SQL SUBSTRING() składnia funkcji

SELECT
    SUBSTRING( nazwa_kolumny , pozycja_początkowa , długość )
FROM
    źródło_danych

Parametry:
nazwa_kolumny – określamy tutaj kolumnę na której będziemy używać funkcji SUBSTRING()
pozycja_początkowa – określamy miejsce początkowe w tekście w polu w kolumnie wskazanym w nazwa_kolumny
długość – określamy długość ciągu który chcemy wyodrębnić od miejsca wskazanego w pozycja_początkowa


INFO: Funkcja SUBSTRING() nie występuje we wszystkich środowiskach bazodanowych. Np. odpowiednikiem funkcji SUBSTRING() w Oracl jest funkcja SUBSTR() a w innych środowiskach używana jest funkcja MID()


Przykład zastosowania funkcji SUBSTRING() (baza Adventureworks)

Wyświetl dane pracowników w postaci: Imię , Nazwisko , pierwsza_litera_imiania . nazwisko.

SELECT
     FirstName AS Imię
    ,LastName AS Nazwisko
    ,SUBSTRING(FirstName,1,1)+ '.' + LastName AS [Połączenie imienia z nazwiskiem]
FROM
    Person.Person
WHERE
    PersonType = 'EM'
    OR PersonType = 'SP'


Przykład zastosowania funkcji SUBSTRING() (baza Northwind)

Skróć nazwy regionów do poztaci 4 znakowej, zaczynając od pierwszego znaku.

SELECT
    SUBSTRING(RegionDescription , 1 , 4) AS [Skrócone nazwa regionów]
FROM    
    Region