Archiwum kategorii: 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.

 

 

PostgreSQL łączenie stringów (konkatenacja) i NULL-e.

PostgreSQL łączenie stringów (konkatenacja) i NULL-e.

 

Do łączenia stringów w PostgreSQL możemy podejść na dwa sposoby.

Sposób 1. Użyj operatora konkatenacji ||

Zadanie 1

Połącz dwa stringi 'Text1' i 'Text2'.

Rozwiązanie

SELECT 'Text1' || 'Text2'

Wynik

Text1Tex2

Zadanie 2

Połącz text 'Text1' i NULL

Rozwiązanie

SELECT 'Text1' || NULL

Wynik

NULL

Zadanie 3

Połącz text 'Text1', wartość NULL i 'Text2'

Rozwiązanie

SELECT 'Text1' || NULL || 'Text2'

Wynik

NULL

 

Sposób 2. Użyj funkcji CONCAT()

Zadanie 1

Połącz dwa stringi 'Text1' i 'Text2'.

Rozwiązanie

SELECT CONCAT( 'Text1' , 'Text2')

Wynik

Text1Tex2

Zadanie 2

Połącz text 'Text1' i NULL

Rozwiązanie

SELECT CONCAT( 'Text1' , NULL)

Wynik

Text1

Zadanie 3

Połącz text 'Text1', wartość NULL i 'Text2'

Rozwiązanie

SELECT CONCAT( 'Text1' , NULL , 'Text2' )

Wynik

Text1Text2

 

Podsumowanie

Wszędzie tam gdzie spodziewasz się, że jedna z części, które chcesz połączyć w nowy string, może być NULL-em, używaj funkcji CONCAT().

PostgreSQL. Jak obliczyć różnicę wyrażoną w latach, miesiącach i dniach, pomiędzy dwoma datami? Funkcje AGE() i DATE_PART().

Witam Was serdecznie w kolejnym wpisie dotyczący PostgerSQL-a. Dzisiaj zajmiemy się obliczaniem różnicy pomiędzy dwoma datami, wyrażoną w latach, miesiącach lub dniach. Tak naprawdę, żeby zrealizować nasz cel  skorzystamy tylko z dwóch funkcji:  AGE() i DATE_PART().

Pierwsza z nich, funkcja AGE(), zwróci nam różnicę pomiędzy dwoma datami. Funkcja ta zwróci nam wynik w postaci interwału czasowego np. 9 years 1 mons 2 days.

Możemy z niej skorzystać na dwa sposoby.

W pierwszym przypadku podajemy dwa argumenty, które są datami. Wtedy funkcja AGE() zwróci nam różnicę pomiędzy nimi. Dodatkowa zasada jest taka, żeby pierwsza data była tą "starszą" a druga "młodszą" (bliższą dacie dzisiejszej), bo inaczej otrzymamy wynik ujemny.

Składnia:

age(timestamp, timestamp)

Przykład:

SELECT  AGE( TIMESTAMP '2010-01-01' , TIMESTAMP '2000-12-31' )

Wynik:

9 years 1 day

W drugim przypadku podajemy jeden argument, który także jest datą. Wtedy funkcja AGE() zwróci nam różnicę między podaną datą a datą dzisiejszą.

Składnia:

age(timestamp)

Przykład:

SELECT    AGE( TIMESTAMP '2010-01-01' ) 

Wynik:

11 years 6 mons 17 days  <- bo zapytanie wykonałem 18 lipca 2021

 

OK. Korzystać z funkcji AGE() już potrafimy.

Teraz weźmy się za funkcję DATE_PART(). Funkcja ta "wyciągnie" nam interesującą część daty z daty w postaci "timestamp", np. rok, miesiąc, dzień lub z Interwału.

Z tej funkcji także możemy skorzystać na dwa sposoby.

W pierwszym sposobie podajemy dwa argumenty: text i timestamp a w drugiem: text i interval.

No to przykład dotyczący pierwszego sposobu, czyli podajemy dwa parametry: text i datę w formacie timestamp.

Składnia:

date_part(text, timestamp)

Przykład:

SELECT DATE_PART( 'year' , TIMESTAMP'2000-01-01' )

Wynik:

2000

oczywiście w miejsce 'year' możemy podać np. 'month' lub 'day' i wtedy otrzymamy interesującą nas część daty.

Drugi sposób to podanie dwóch argumentów: części daty która nas interesuje i interwału czasowego.

Składnia:

date_part(text, interval)

Przykład:

SELECT DATE_PART( 'year' , interval '10 years 4 month 19 days' )

Wynik:

10

Skoro jako drugi argument funkcji DATE_PART() możemy podać interwał czasowy to możemy skorzystać z funkcji AGE() która zwraca nam właśnie taki typ.

Składnia:

date_part(text, AGE())

Przykład:

SELECT DATE_PART( 'year' , AGE( '2020-06-10' , '2008-01-24' ) )

Wynik:

12

Mamy już pełną wiedzę na temat wykorzystania funkcji AGE() i DATE_PART(). Ale chciałem Ci jeszcze zwrócić uwagę na pewiem mały szkopół.

Może posłużmy się przykładem. Wykonaj coś takiego.

Przykład:

SELECT
    DATE_PART( 'year' , AGE( '2020-06-10' , '2008-01-24' ) ),
    DATE_PART( 'year' , AGE( '2020-06-10' , '2008-12-24' ) )

Wynik:

12 , 11

Patrząc na wyniki pojawia się pytanie. Dlaczego mamy inne wyniki w latach (bo pierwszy argument DATE_PART to 'year') skoro w obu wierszach mamy te same daty jeśli chodzi o rok? No to zernijmy na miesiące w obu wierszach. Pierwsza data jest identyczna w obu wierszach i jest nią 10 czerwiec 2020 roku. Natomiast druga data w pierwszym wierszy to 24 styczeń 2008 a w drugim wierszu 24 grudzień 2008. Czyli w pierwszym wierszu styczeń jest przed czerwcem a w drugim wierszu grudzień jest za czerwcem i dlatego funkcja AGE() zwraca inny interwał czasowy. Dla pierwszego wiersza będzie to: "12 years 4 mons 17 days" i dlatego wynikiem działania funkcji DATE_PART jest liczba 12, a w drugiem wierszu funkcja AGE() zwraca: "11 years 5 mons 17 days" dlatego DATE_PART zwróci nam: 11.

Także na poczatku musimy się zastanowić co tak naprawdę nas interesuje. Czy chcemy np. obliczyć różnicę w latach, ale pełnych latach? Czy jeśli rok "nie będzie pełny" to chcemy uzyskać liczbę o jedną mniej niż wynikałoby to lat zapisanych w datach.

Także zwródźcie na to uwagę i miłego programowania.

Dzięki za przeczytanie niniejszego wpisu. Serdecznie zapraszam na kolejne z tej serii.

PostgreSQL. Jak korzystać z interwałów czasowych?

Często w zapytaniach SQL które tworzymy zachodzi potrzeba skorzystania z jakiegoś interwału czasowego a nie wskazywania konkretnych dat. Np. za każdym razem kiedy uruchomię zapytanie w styczniu, marciu czy czerwcu, chcę żeby zapytanie za każdym razem kiedy się uruchoim brało pod uwagę rok wstecz.

I tutaj z pomocą przychodzą interwały. Np. chcemy żeby zapytanie wyświetliło nam wszystkie zdarzenia (np. tabela "events") gdzie data utworzenia tego zdarzenia ("createion_date") była rok wstecz.

Możemy to zrobić np. tak:

 

SELECT *

FROM events

WHERE events.creation_date BETWEEN NOW() – INTERVAL '1 year' AND NOW()

 

Jak wynika z powyższego słówkiem "INTERVAL" określamy interwał czasowy który nas interesuje. W przykładzie jest 1 rok ale oczywiście możemy użyć także "month", "day" itd.

Widzisz, że w bardzo prosty sposob można swoje zapytania tworzyć w bardziej uniwersalny sposób.

PostgreSQL. Jak zresetować hasło dla użytkownika Postgres? (reset password for user postgres)

Każdy kiedyś spotkał się z sytuacją kiedy zapomniał hasła czy to do jakiejś strony www, serwera czy aplikacji. Mi ostatnio przytrafiła się taka sytuacja i zapomniałem hasła do użytkownika Postgres do swojego mało używanego testowego serwera PostgreSQL-a. I już miałem zamiar wszystko zresetować kiedy pomyślałem, że jest to doskonała sytuacja żeby spróbować zresetować jakoś to hasło. Poszukałem chwilkę w sieci i od razu znalazłem ciekawy artykuł który przybliżył mi tą tematykę.  Efektem moich ćwiczeń jest poniższa procedura.

1. Znajdź lokalizację PostgreSQL-a na dysku. U mnie będzie to: C:\Program Files\PostgreSQL\13\data , ponieważ posiadam wersję 13. Wejdź do tej lokalizacji.
2. Znajdź plik pg_hba.conf i zrób jego kopię.
3. Otwórz plik pg_hba.conf w jakimś edytorze tekstów np. Notepad++
4. Zjedź na dół pliku i zmień wszystkie wartości w "kolumnie" METHOD z obecnie wpisanej na trust
5. Zapisz zmiany w pliku i zrestartuj PostgreSQL
    W polu "Wpisz tu wyszukiwane słowa" wpisz "Usługi" i wybierz opcję o tej nazwie z listy.
6. W oknie usługi znajdź usługę "postgresql…"
7. Kliknij w tą pozycję i po lewej stronie kliknij w opcję "Uruchom ponownie".
8. Teraz podaj "master password".
9. Kiedy system zapyta Cię o podanie hasła dla użytkownika "postgres" wciśnij po prostu enter. Powinieneś móc się zalogować pomimo braku hasła.
10. Teraz uruchom okno "Query editor" i zresetuj hasło poleceniem: ALTER USER postgres WITH PASSWORD 'twoje_nowe_hasło'
11. Wróć do lokalizacji z punktu 1 i przywróć stary plik pg_hba.conf i ponownie zrestartuj usługę "postgres…"
12. Teraz już możesz logować się jako użytkownik Postgres swoim nowo ustawionym hasłem.

 

PostgreSQL funkcja ASCII() – zwróć kod ASCII pierwszego znaku ciągu.

Tym razem na "warsztat" bierzemy funkcję ASCII(). Funkcja zwróci nam kod ASCII znaku lub ciągu znaków które umieścimy jako jej argument. W przypadku pojedynczego znaku sprawa jest prosta bo mamy tylko jeden znak i jego kod ASCII będzie zwrócony. W przypadku ciągu znaków otrzymamy kod ASCII pierwszego znaku w ciągu. Pamiętajmy, że np. znak 'A' to nie to samo co znak 'a'.
Zwracany typ: INT

PostgreSQL ASCII() składnia

ASCII(  'łańcuch_znaków'  )


Przykład zastosowania funkcji ASCII()

SELECT
    ASCII('A')

Wynik

65

Widzimy, że kod ASCII znaku 'A' to 65.


Teraz sprawdźmy to samo dla znaku 'a'

SELECT
    ASCII('a')

Wynik

97

Tutaj już widzimy różnicę. Kod ASCII znaku 'a' to 97.


Teraz sprawdźmy dla ciągu znaków. Znamy już kod ASCII znaku 'A' który jest równy 65. Teraz jako argument do funkcji podstawimy ciąg znaków np. 'ASCII'. Na początku ciągu jest znak 'A' czyli funkcja powinna zwrócić jego kod, czyli 65.

SELECT
    ASCII('ASCII')

Wynik

65

Wynik to 65 więc funkcja prawidłowo zwróciła kod ASCII pierwszego znaku ciągu czyli 'A'.


postgresqlpostgresql

PostgreSQL funkcja REPLACE() – zamiana jednego ciągu na drugi.

Dzisiaj omówimy funkcję REPLACE() która służy do zamiany jednego ciągu znaków na drugi. Bardzo przydatna funkcja w sytuacji kiedy hurtowo musimy zamienić ciągi znaków.
Zwracany typ: TEXT

PostgreSQL REPLACE() składnia

REPLACE( 'łańcuch_znaków' , 'ciąg_który_zamieniamy' , 'ciąg_na_który_zamieniamy' )

Przykład zastosowania funkcji REPLACE()

SELECT
    REPLACE('Ala ma kota.', 'kota', 'psa')

Wynik

Ala ma psa.

Co oznacza że słowo 'kota' zostało podmienione na słowo 'psa'.


postgresqlpostgresqlpostgresql

Kurs PostgreSQL (tutorial)

Z uwagi na to, że wpisów dot. PostgreSQL jest coraz więcej postanowiłem skopować wpisy z ogólnego wpisu dotyczcego SQL-a i stworzyć nowy osobny wpis i stworzyć pewnego rodzaju kurs (tutorial) PosgreSQL-a.

Kurs PostgreSQL spis treści

 

 

kurs postgresql tutorial sql PostgreSQL download. Skąd ściągnąć krok po kroku.   PostgreSQL download. Skąd ściągnąć krok po kroku.
kurs postgresql tutorial sql PostgreSQL instalacja krok po kroku.   PostgreSQL instalacja krok po kroku.
    Funkcje i operatory dot. ciągów znaków
sql, postgresql, łaczenie stringów   PostgreSQL łączenie ciągów znaków.
sql, postgresql, łaczenie stringów i innych typów danych   PostgreSQL łączenie ciągów znaków i innych typów danych.
sql, postgresql, funkcja bit_length ilość bitów w łańcuchu   PostgreSQL funkcja BIT_LENGTH() ile bitów w łańcuchu
sql, postgresql, funkcja char_length() character_length() ile znaków w łańcuchu   PostgreSQL funkcja CHAR_LENGTH() ile znaków w łańcuchu
sql, postgresql, funkcja lower() zamiana tekstu na małe litery   PostgreSQL funkcja LOWER() zamiana tekstu na małe litery
sql, postgresql, funkcja upper() zamiana tekstu na duże litery   PostgreSQL funkcja UPPER() zamiana tekstu na duże litery
sql, postgresql, funkcja substring wycinanie podłańcucha z łańcucha znaków   PostgreSQL funkcja SUBSTRING() podłańcuch z łańcucha znaków
sql, postgresql, funkcja substring wyrażenia regularne wycinanie podłańcucha z łańcucha znaków   PostgreSQL funkcja SUBSTRING() + wyrażenia regularne
podłańcuch z łańcucha znaków
sql, postgresql, funkcja position szukamy pozycji podłańcucha w łańcuchu znaków   PostgreSQL funkcja POSITION() pozycja podłańcucha w łańcuchu
sql, postgresql, funkcja trim leading trailing both ucinamy znaki z łańcucha głównego   PostgreSQL funkcja TRIM() leading, trailing, both ucinamy znaki z łańcucha głównego
sql, postgresql, funkcja OVERLAY zamiana ciagów znaków   PostgreSQL funkcja OVERLAY() zamiana ciągów znaków
sql, PostgreSQL funkcja CONCAT() – łączenie stringów   PostgreSQL funkcja CONCAT() łączenie stringów
postgresql sql funkcja left function   PostgreSQL funkcja LEFT() wycinamy string ze stringu od lewej
postgresql sql funkcja right function   PostgreSQL funkcja RIGHT() wycinamy string ze stringu od prawej
postgresql sql funkcja function OCTET_LENGTH() – liczba bajtów w stringu   PostgreSQL funkcja OCTET_LENGTH() liczba bajtów w stringu
    Inne funkcje
    PostgreSQL funkcja EXISTS() sprawdzamy czy zapytanie zwraca wynik
    PostgreSQL funkcja CAST() konwertujemy łańcuch znaków na liczbę
    PostgreSQL funkcja ASCII() zwracam kod znaku
     
    Wróć do nas za jakiś czas, już niedługo nowy wpis.