Wprowadzenie

Bazy danych napędzają nowoczesną sieć. Każda duża lub dynamiczna witryna wykorzystuje bazę danych w jakiś sposób a w połączeniu z SQL (Structured Query Langauge) daje praktycznie nieograniczone możlwiości manipulowania danymi. Jeżeli znasz już SQL upewnij się, że znasz wszystkie niezbędne polecenia ponieważ są one ciągle wykorzystywane przez twórców witryn internetowych.

W tym artykule skupimy się na podstawowych poleceniach jako, że planuje kolejne artykuły związane z tą tematyką.

Istnieje wiele nazw danych zwracanych z tabeli bazy danych. Definiowane są one powszechnie jako wiersze, rekordy oraz krotki. Terminów tych będę używał zamiennie.

1. Select

Instrukcja select jest najprostsza a jednocześnie fundamentalna ponieważ jest to podstawa niemal wszystkich innych zapytań. Za najlepszą praktykę uważa się pisanie dużymi literami słów zastrzeżony dla SQL ponieważ czyni tą całą instrukcję łatwiejszą do przeczytania i zrozumienia.

Jak sugeruje nazwa, select używany jest do wybierania danych z bazy danych. Oto najprostsze zastosowanie:

SELECT * FROM table;

Zapytanie to składa się z dwóch części. Pierwsza cześć SELECT * określa kolumny, które chcesz wybrać. Gwiazdka oznacza, że chcesz wybrać wszystkie kolumny z tabeli. Druga część FROM table informuje bazę danych o tabeli z której chcesz pobrać dane. Pamietaj, aby zastąpić table nazwą tabeli z bazy danych.

To polecenie przyjmuje również nazwę select gwiazdka. Używanie gwiazdki jest dobrym sposobem na określenie jakie dane znajdują się w tabeli – nie jest zalecane na produkcji. W przypadku takiego polecenia to silnik bazy danych decyduje w jaki sposób zostaną przedstawione dane. Nie mamy żadnego wpływu na kolejność zwracania danych. Po dodaniu nowej kolumny w bazie danych może się okazać, że zmienne przygotowane po stronie kodu nie są już poprawne. Na szczęście mamy rozwiązanie.

Możemy jawnie określić, które kolumny chcemy pobrać:

SELECT column1, column2, column3,…columnN FROM table
To zapytanie zwróci nam wskazane kolumny ze wskazanej tabeli. Nazwy kolumn oraz nazwa tabeli muszą być zgodne z tymi zdefiniowanymi w bazie danych. Taki sposób wybierania danych może być dla Was nieco żmudny jeżeli mamy dużo danych ale jest to zdecydowanie mniejszy problem na przyszłość a Twój kod będzie dużo czytelniejszy dla innych programistów.

W swoich artykułach zawsze posługuje się przykładami ponieważ taki sposób dużo bardziej pozwala sobie utrwalić dane. Użyjemy dlatego przykładowej bazy danych nazwanej AdventureWorks - może pobrana być z tej lokalizacji baza danych AdventureWorks.

W pierwszym przykładzie wykonamy instrukcję select gwiazdka:

SQL - Select (Gwiazdka)

Możemy zaobserwować szereg kolumn oraz rekordów. Spróbujmy teraz zawęzić poszukiwania ponieważ nie potrzebujemy aż tylu informacji:

SELECT ProductID,Name,ProductNumber FROM Production.Product
Oraz wynik tak zmodyfikowanej instrukcji:

SQL Server - wynik operacji select

Możemy zaobserwować, że zmniejszyła nam się liczba kolumn ale wciąż mamy tak samo dużo rekordów. Przechodzimy zatem płynnie do kolejnej części artykułu.

2. Where

Where jest odpowiedzią na pytanie z powyższego punktu: “Co jeśli chcemy zawęzić nieco dalej kryteria wyszukiwania?" Może się zdarzyć, że potrzebujemy określonej liczby produktów, albo produktów jedynie o określonych wymiarach. W tym miejscu pojawia się komenda where. Pozwala na dodawanie warunków do naszej instrukcji.

SELECT ProductID, Name, Size, StandardCost FROM Production.Product
WHERE StandardCost < 50
Tak przygotowane zapytania zwraca nam produkty, których cena jest mniejszą niz 50. Możemy łączyć wiele warunków za pomocą operatora AND:
SELECT ProductID, Name, Size, StandardCost FROM Production.Product
WHERE StandardCost > 10 AND StandardCost < 50
W powyższym przypadku ogranicza on listę produktów do tych, które kosztują powyżej 10 ale również ich cena nie dochodzi do 50. Kolejnym często używanym operatorem jest OR:
SELECT ProductID, Name, Size, StandardCost FROM Production.Product
WHERE StandardCost < 10 OR StandardCost > 100
W tym przypadku skupiamy się również na produktach z pewnego zakresu z zaznaczeniem, że poszukujemy produktów naprawdę tanich LUB tych, których cena przekracza 100.

Pamiętajcie, aby każde z tych zapytań przetestować lokalnie u siebie na komputerze oraz wypróbować wielu kombinacji.

3. Order

Polecenie to pozwala na posortowanie zwracanych wyników. Kolejne z tych, które możemy zaliczyć do łatwych w użyciu. Wystarczy dodać je na koniec naszej instrukcji:

SELECT ProductID, Name, Size, StandardCost FROM Production.Product
WHERE StandardCost > 1 AND StandardCost > 100
ORDER BY StandardCost ASC
Pamiętaj, że musisz zdefiniować kolumnę oraz kolejność, która może być rosnąca - ASC lub malejąca DESC. Spójrz na wykonanie powyższego polecania:

SQL - OrderBy

Możesz zaobserwować, że użyliśmy sortowania rosnącego po kolumnie StandardCost. ORDER BY jest niezwykle przydatny w połączeniu z innymi poleceniami. Nie wszystkie zapytania zwracają dane w sposób logiczny lub uporządkowany – to polecenie pozwala to zmienić.

4. Join

Polecenie join służy do dołączania powiązanych pól przechowywanych w jednej lub kilku tabelach. Dołączasz drugą tabelę do pierwszej i określasz sposób połączenia danych. Poniżej podstawowy przykład:

SELECT * FROM Person.Address
LEFT JOIN Person.BusinessEntityAddress USING (AddressID)
W powyższym przykładzie dzieje się kilka istotnych rzeczy. Trzeba zacząć od polecenia LEFT JOIN, które oznacza, że chcesz dołączyć tabelę za pomocą łączenia lewego. Kolejnym krokiem jest wskazanie tabeli, którą chcesz dołączyć. Polecenie USING określa, że w obu tabelach powinna wystąpić kolumna służąca do połączenia obu tabel.

Nie musisz się jednak przejmować jeżeli kolumny mają różne nazwy w Twoich tabelach. Możesz użyć polecenia ON:

SELECT * FROM Person.Address
LEFT JOIN Person.BusinessEntityAddress ON Person.Address.AddressID = Person.BusinessEntityAddress.AddressID

Polecenie ON jawnie określa, które kolumny mają zostać użyte do połączenia. Istnieje wiele typów złączeń na których szczegółow skupie się w kolejnych artykułach. W tym podrozdziale skupimy się na krótkim przedstawieniu ich właściwości:

  • (INNER) JOIN - zwraca rekordy z dopasowaniem w obu tabelach;
  • LEFT (OUTER) JOIN – zwraca wszystkie rekordy z lewej tabeli z dowolnymi dopasowaniami z prawej tabeli. Jeżeli nie ma dopasowań rekordy z lewej tabeli nadal są zwracane;
  • RIGHT (OUTER) JOIN – jest to przeciwieństwo powyższego przykładu: zwracane są wszystkie rekordy z prawej tabeli wraz z dowolnymi doapsowaniami z lewej;
  • FULL (OUTER) JOIN - zwraca rekordy z dopasowaniem z dowolnej tabeli

Użycie składni INNER lub OUTER jest opcjonalne. Może to ułatwić zrozumienie ale nie wymagane przy każdym zapytaniu.

5. Alias

Teraz, kiedy już znasz podstawy możemy się skupić na poleceniu alias. Używany jest do tymczasowej zmiany nazwy tabeli – bardziej traktowany jako „pseudonim" ponieważ ta nazwa istnieje tylko w trakcie wykonywania pojedynczej transakcji. Oto jak można go używać:

SELECT p.FirstName FROM Person.Person p
Możesz używać dowolnej nazwy (nie zastrzeżonej) lub pojedycznych liter alfabetu. Nazwa każdej kolumny musi być poprzedzona prefixem. Alias jest przypisany do tabeli bezpośrednio po zdeklarowaniu. Poniższy zapis można traktować jako równoważny:
SELECT Person.FirstName FROM Person.Person
Zamiast używania długich nazw tabel możesz w prosty i łatwy sposób zdecydować się na łatwą do zapamiętania literę. Ale czy to ma sens? Tak, jeżeli przygotujesz zapytanie dotyczące większej liczby kolumn możesz pomylić kolumny z różych tabel. Jeżeli obie tabele mają kolumny o tej same nazwie może dojść nawet do przerwania wykonywania instrukcji z uwagi na brak poprawnych aliasów wskazujących. Poniżej przykład z dwoma tabelami:
SELECT Person.Address.AddressID, Person.Address.City, Person.AddressType.Name
FROM Person.Address, Person.AddressType
Oraz ten sam przykład przy użyciu aliasów:
SELECT a.AddressID,a.City,at.Name
FROM Person.Address a, Person.AddressType at
Pierwsza tabela dostała alias „a" a druga „at" dzięki czemu kod jest łatwiejszy w zrozumieniu i zmniejsza ilość użytego kodu.

Warto pamietać, że można również zmieniać nazwy kolumn za pomocą aliasu AS jak na poniższym przykładzie:
SELECT p.FirstName AS PierwszeImie FROM Person.Person p
Proszę spojrzeć poniżej:

SQL - dodanie aliasu

6. Union

Union jest wspaniałą komendą. Umożliwia wzajemne dodawanie wierszy do siebie. W przeciwieństwie do złączeń, które dołączają dopasowane kolumny, union pozwala nam dołączyć niepowiązane wiersze pod warunkiem, że mają taką samą liczbę oraz nazwę kolumn. Oto jak używać tej komendy:

SELECT p.FirstName as Name FROM Person.Person p
UNION
SELECT d.Name FROM HumanResources.Department d
Polecenie union możesz traktować jako sposób łączenia wyników dwóch zapytań. Komenda ta wyświetli tylko te wyniki w których istnieje unikalny wiersz pomiędzy dwoma zapytaniami. Możesz również użyć składni UNION ALL, aby zwrócić wszystkie dane niezależnie od duplikatów:
SELECT p.FirstName as Name FROM Person.Person p
UNION ALL
SELECT d.Name FROM HumanResources.Department d

7. Insert

Dowiedzieliśmy się wszystkiego o pobieraniu danych z bazy danych, co z ich wstawianiem? Polecenie insert pozwala na wykonywanie tego typu operacji:

INSERT INTO HumanResources.Department(Name,GroupName) VALUES ('Inżynier', 'Dział badań i rozwoju')
Musisz podać nazwę tabeli oraz kolumn do których chcesz wstawić nowe wartości. Następnie składnia VALUES jest używana do przygotowania odpowiednich danych do wstawienia. Te muszą być w tej samej kolejność jak zdefiniowane w pierwszej cześci instrukcji.

Nie ma możliwości użycia w tym przypadku klauzuli where oraz należy się upewnić, że wszelkie ograniczenia nałożone na kolumny są spełnione (jest to szeroki temat, który zostanie omówiony szczegółowo w kolejnych artykułach).

8. Update

Po dodaniu nowego wiersza do tabeli może się okazać, że potrzebujemy przeprowadzć aktualizację. Oto jak wygląda składnia tego polecenia:

  UPDATE HumanResources.Department SET Name='NazwaDepartamentu', GroupName='NazwaGrupy'
Należy wskazać tabele w której chcesz dokonać aktualizacji a następnie przy użyciu polecenia SET określamy nazwy kolumn i porządane wartości. Powyższy przykład jest dobry ale trzeba mieć na uwadzę, że dokonamy aktualizacji każdego wiersza w danej tabeli - takie zachowanie nie jest porządane!

Z pomocą przychodzi nam klauzula where, której używamy tak jak w przypadku standardowej operacji select:

UPDATE HumanResources.Department SET Name='Inżynieria', GroupName='Dział badań i rozwoju'
WHERE NAME='Engineering'
Można również wykorzystać w tym miejscu operatory takie jak AND czy OR:
UPDATE HumanResources.Department SET Name='Inżynieria - Oddział II', GroupName='Dział badań i rozwoju - Oddział II'
WHERE (NAME='Engineering' OR NAME='Inzynier')

9. Upsert

Upsert to dziwnie brzmiące ale niezwykle użyteczne polecenie. Zróbmy proste założenie: nałożone są ograniczenia mówiące, że rekody mają mieć unikalną nazwę – nie chcemy zapisywać dwóch wierszy o tej samej nazwie. Jeżeli spróbujesz kilkakrotnie dodać tą samą nazwę departamentu dostaniesz błąd ze strony bazy danych mówiący, że taka operacja narusza więzy integralności. Upsert pozwala nam na aktualizację rekordu jeżeli ten już istnieje. Jest to niezwykle przydatne. Bez tego polecania należałoby napisać całą kogikę niezbędną do sprawdzenia, czy dany rekord istnieje, jeżeli nie, należałoby wstawić nową wartość, jeżeli istnieje – należy pobrać odpowiedni klucz a następnie dokonać aktualizacji tego wiersza.

Niestety, Upsert jest zaimplementowany inaczej w różnych bazach danych. Jest to nowość dla PostgreSQL z kolei w MySQL istnieje od dawna. SQL Server czy SQL Developer mają swoje własne interpretacje tej komendy za pomocą klauzuli Merge.

10. Delete

Delete jest używany do całkowitego usunięcia rekordów – może być bardzo szkodliwe w przypadku nadużycia! Podstawowa składnia jest bardzo prosta w użyciu:

DELETE FROM HumanResources.Department
Tak wykonane zapytanie usunie wszystkie rekordy ze wskazanej tabeli. Należy użyć klauzuli where, aby ograniczyć wykonanie tylko do określonego/określonych rekordów:
DELETE FROM HumanResources.Department
WHERE Name = 'Inzynieria - Oddzial II'
Jeżeli pracujesz nad rozwojem jakiegoś systemu warto pamiętać o regularnym tworzeniu kopii zapasowych. Jest to niezywkle istotne w przypadku nieprawidłowego lub zbyt pośpiesznego wykonania instrukcji wraz z ominięciem klauzuli where - wówczas tracimy wszystkie rekordy.

11. Create Table

Komenda create table jest używana do tworzenia tabel. Podobnie jak w poprzednich przykładach składania nie jest skomplikowana:

CREATE TABLE TEST(FirstName VARCHAR, Age INT, PRIMARY KEY(FirstName))
Zwróć uwagę na konstrukcję w której nazwy i ograniczenia są wewnątrz nawiasów a kolumny posiadają zdefiniowany typ danych. Określono klucz główy co jest wymagane w dowolnym projekcie bazy danych.

12. Alter Table

Komenda alter table jest używana do modyfikowania istniejącej struktury tabeli. Działanie to jest nieco ograniczone ponieważ silnik bazy danych nie pozowoli na modyfikacje tabeli jeżeli może to doprowadzić do konfliktu związanego z istniejącymi danymi. W takich przypadkach należy wcześniej zmodyfikować istniejące dane a dopiero wówczas przystąpić do zmiany tabeli. Poniżej najprostszy przykład:

ALTER TABLE TEST ADD LastName VARCHAR
W powyższym przykładzie została dodana kolumna LastName. Instrukcje takie mogą być modyfikowane praktycznie dowolnie.

13. Drop Table

Ostatnim przykładem w tym artykule jest drop table. Polecenie to służy do usuwania, ale nie określonej grupy rekordów jak to miało miejsce w przypadku delete, a do usunięcia wszystkich danych wraz z całą strukturą tabeli. Poniżej przykład pokazujący użycie tego polecenia:

DROP TABLE TEST;
Wydaje się, że umieszczanie takich instrukcji w systemie jest pozbawione sensu ponieważ może wyrządzić naprawdę wiele szkód. Drop table w większości przypadków należy używać ręcznie w trakcie kampanii zwanych database purge - czyli operacji czyszczenia bazy danych ze zbędnych tabel, a co za tym idzie i danych.

To wszystko na dzisiaj. Mam jednak nadzieje, że nauczyłeś się kilku przydatnych sztuczek! Powyższe instrukcje nie są skomplikowane dlatego dobrym pomysłem wydaje się przygotowanie swojego własnego projektu w którym będziesz wykonywał przeróżne operacje na bazie danych. Zobaczysz jak szybko Twoje instrukcje rozrosna się o kolejne linie a pisanie zapytań będzie Ci przychodziło z większą łatwością. Musisz jednak zawsze mieć na uwadze liczne błędy, które będą się pojawiały a także takie pisanie zapytań, żeby nie były podatne np. na SQL Injection.