Szybka powtórka z SQLa

Każdy kto idzie na jakąkolwiek rozmowę kwalifikacyjną robi sobie wcześniej rachunek sumienia tego co potrafi a czego nie :) To co potrafi zawsze warto sobie przypomnieć, żeby podczas rozmowy przypadkiem nie zrobić niepotrzebnie gafy. W tym artykule powtórka SQLa na przykładzie bazy MS SQL 2008. Informacje są podane w pigułce tzn. tylko najważniejsze fakty - zero nudzenia. Rozwinięcie każdej z myśli można znaleźć na wielu strona poświęconych SQLowi.

1. Proste zapytania:

SELECT wyrażenie

FROM źródło

WHERE warunek_filtrowania

GROUP BY wyrażenie

HAVING wyrażenie

ORDER BY wyrażenie

NULL - specjalna wartość, oznaczającą niewiadome, nieznane (jakiejś informacji po prostu nam brakuje)

Sprawdzanie krotek zawierających wartości nieznane (NULL) nie może zostać wykonane za pomocą równości =. Stosujemy w tym celu operatory IS NULL czy też IS NOT NULL

Klauzula WHERE:

LIKE - filtrowanie łańcuchów znaków;

% - zastępuje dowolną ilość znaków

_ - zastępuje dokładnie jeden znak

[a,b,c,d] == [a-d] - zawiera znak z przedziału

[^a-d] - każdy znak który nie jest w podanym przedziale

ORDER BY [DESC|ASC]

DESC - malejąco

ASC - rosnąco (domyślnie)

Aliasowanie - nadawanie nowych nazw kolumną lub wyrażenią:

SELECT product AS 'Product Name'

FROM tabela

ORDER BY [Product Name]

Złączenia tabel:

1. Równościowe

2. Naturalne

3. Iloczyn karteznański

4. Zewnętrzne

5. Łączenie tabeli samej z sobą

Ad. 1 INNER JOIN lub samo JOIN:

Zwracane są wiersze spełniające warunek następujący po ON

SELECT wyrażenie

FROM tabela1 JOIN tabela2 ON warunek

Ad. 2 NATURAL JOIN

W wyniku połączenia dostajemy jedną parę atrybutów połączeniowych:

Zwracane są wiersze spełniające warunek następujący po ON

SELECT wyrażenie

FROM tabela1 NATURAL JOIN tabela2 

Ad. 3

każdy wiersz z jednej tabeli jest łączony z każdym wierszem drugiej tabeli - zastosowanie jest bardzo niewielkie ze względów na ilość powstałych krotek w wyniku działania zapytania:

SELECT wyrażenie

FROM tabela1, tabela2

Ad. 4

LEFT OUTER JOIN

RIGHT OUTER JOIN

FULL OUTER JOIN

W zależności od wybranego typu połączenia, wszystkie wiersze z jednej (right, left) bądź obu (full) są łączone z wierszami z drugiej tabeli. Brakujące wartości zastępowane są NULLem.

Ad. 5

Kiedy tabela odwołuje się sama do siebie:

W przypadku przedstawionym powyżej, pracownik ma szefa, który jest identyfikowany na podstawie pola ManagerID. Zapytanie zwracające pracowników i ich szefów miało by postać:

SELECT p.FirstName, p.LastName, s.FirstName, s.LastName

FROM Employee p JOIN Employee s ON p.ManagerId = s.EmpId

Funkcje agregujące

AVG - średnia

COUNT - zwraca ilość danych w zbiorze:

COUNT(*) - ilość wierszy

COUNT(kolumna) - ilość elementów ale bez NULL

COUNT DISTINCT - unikalna ilość, bez NULL

MAX - maksymalna wartość w zbirze

MIN - minimalna wartość w zbirze

GROUP BY - grupuje wyniki w mniejsze zbiory

HAVING - pozwala filtrować na podstawie funckji agregujących (w WHERE nie wolno ich stosować!)

UNION - pozwala na połączenie wyników kilku następujących po sobie selektów o ile każdy z nich wyprowadza taką samą ilość kolumn o tym samym typie:

SELECT ...

FROM ...

UNION

SELECT ...

FROM ...

EXCEPT - wyświetla tylko te wiersze po lewej stronie operatora, których nie ma w wyniku po prawej stronie operatora:

SELECT ...

FROM ...

EXCEPT

SELECT ...

FROM ...

Niektóra funkcje wbudowane w SQL Server:

SYSDATATIME(), GETDATE() - pobiera aktualną datę i czas z systemu

DATEPART(datepart, date); gdzie datepart = year, day, month, dayofyear, week, weakday itd...

CAST(wyrażenie AS typ_danych)

CONVERT(typ_danych, wyrażenie)

Operacje na łańcuchach znakowych:

LEFT, RIGHT (string, ilość znaków) - zwraca ilość znaków od lewej lub prawej

UPPER, LOWER (string) - małe/duże znaki

SUBSTRING(string, start, ilość) - stringi w SQLu zaczynają się od 1 znaku nie 0

REPLACE(źródło, co_zamienić, na_co_zamienić)

LEN(string) - dłogość łańcucha

Wstawianie danych - INSERT

INSERT INTO tabela[(kolumny)]

VALUES(wartośći)

Można także wstawić dane z innej tabeli:

INSERT INTO tabela[(kolumny)]

SELECT kolumny

FROM źródło

Można także utworzyć tymczasową lub stałą tabelę. Przykład utworzenia tabeli tymczasowej:

SELECT IDENTITY (int, 1, 1) AS EmpId, ...

INTO #tabela

FROM...

Uaktualnianie danych - UPDATE

UPDATE tabela

SET kolumna = nowa_wartość

[WHERE warunek]

Można także uaktualnić tabelę docelową poprzez złączenie jej z inną:

UPDATE tabela1

SET kolumna = nowa_wartość

FROM tabela1 JOIN tabela2 ON t1. ... t2. ...

WHERE t2. ... warunek

Usuwanie wierszy - DELETE

DELETE FROM tabela; - usuwa wszystkie wiersze z tabeli

DELETE FROM tabela WHERE warunek; - usuwa wiesze według warunku

DELETE FROM tabela WHERE warunek; - usuwa wszystkie wiersze z tabeli

DELETE FROM tabela1 t1 JOIN tabela2 t2 ON... WHERE warunek; - usuwa wiersze korzystając z połączenia tabeli

TRUNCATE TABLE tabela; - usuwa wszystkie wiersze z tabeli nie logując tego; dodatkowo resetuje licznik autoinkrementacji. Działa przez to szybciej i zużywa mniej zasobów.

Transakcje:

Cechy: spójność, atomowość, izolacja, trwałość

BEGIN TRANSACTION

....

COMMIT TRANSACTION/ROLLBACK TRANSACTION

Poziomy izolacji:

READ UNCOMMITTED - zmiany, wprowadzone przez operacje w ramach aktywnej transakcji (jeszcze nie zatwierdzonej), są widoczne dla innych, równolegle realizowanych transakcji

READ COMMITTED - zmiany, wprowadzone przez operacje w ramach transakcji, są widoczne dla innych transakcji dopiero po zatwierdzeniu transakcji

REPEATABLE READ - zapobiega efektowi niepowtarzalnego odczytu, który polega na tym, iż za każdym razem odpytujący otrzymuje inny wynik tego samego zapytania

SERIALIZABLE - zapobiega wszystkim anomaliom mogącym się pojawić podczas korzystania danych zgromadzonych w bazie danych

Podstawowe typy danych w MS SQL:

char(n), varchar(n)

tinyint, int

decimal(n,m)/numeric(n,m) n - precision; m - scale

datetime, datetime2, date, time

Znaki specjalne w MS SQL:

@ - zmienne lokalne

@@ - zmienne globalne

# - obiekt tymczasowy

## - globalny obiekt tymczasowy

Tworzenie tabeli

CREATE SCHEMA nazwa;

GO

CREATE TABLE schema.nazwa(

definicja kolumn

);

Deklaracja kolumny:

nazwa typ_danych NULL/NOT NULL,

Deklaracja kolumny z autoinkrementacją:

nazwa typ_danych IDENTITY(początek, wartość_inkrementacji) NOT NULL

Tworzenie własnego typu danych:

CREATE TYPE schema.nazwa FROM typ_bazowy;

Modyfikowanie tabeli:

ALTER TABLE nazwa ALTER COLUMN nazwa typ_danych NULL/NOT NULL - zmiana typu danych kolumny

ALTER TABLE nazwa ADD nazwa_kolumny typ_danych NULL/NOT NULL - dodanie kolumny

ALTER TABLE nazwa DROP COLUMN nazwa_kolumny - usunięcie kolumny

DROP TABLE nazwa; - usuwa tabelę (wraz z danymi)

Integralność danych:

Klucz główny:

ALTER TABLE nazwa ADD PRIMARY KEY (kolumna);

Klucz obcy - tworzy relację pomiędzy dwoma tabelami lub z tabelą z samą sobą

Akcje które mogą zostać wykonane podczas UPDATE/DELETE:

NO ACTION - wywołuje błąd w przypadku naruszenia integralności

SET NULL - kolumny odnoszące się do źródła otrzymują wartość null

SET DEFAULT - kolumny odnoszące się do źródła otrzymują wartość domyślną

CASCADE - spowoduje usunięcie komórek związanych relacją

Towrzenie klucza:

Podczas tworzenia tabeli: nazwa_kolumny typ_danych NULL/NOT NULL REFERENCES nazwa_tabeli(kolumna)

Podczas modyfikowania tabeli: ALTER TABLE nazwa tabeli ADD CONSTRAINT nazwa_relacji FOREIGN KEY(kolumna) REFERENCES tabela(kolumna) ON DELETE CASCADE ON UPDATE NO ACTION

CHECK:

ALTER TABLE ADD CHECK(kolumna, warunek)

CTE - Common Table Expression

CTE to nazwa zapytania reprezentującego tymczasowy zestaw rekordów definiowany w zasięgu jednego polecenia SELECT, INSERT, UPDATE lub DELETE.

Przykład CTE:

WITH mojeCTE AS (SELECT ...)

SELECT * FROM mojeCTE

W wyniku uzyskamy wszystkie rekordy zgromadzone w zapytaniu mojeCTE

Można także stworzyć swoistą kolejkę zapytań CTE, w których każde następne będzie korzystało z wyniku poprzedniego:

WITH 

cte1 AS(),

cte2 AS(),...

Podzapytania

Umożliwiają uproszczenie zapytania.

SELECT wyrażenie

FROM źródło

WHERE wyrażenie > (SELECT ...)

Podzapytanie skorelowane:

Składa się z dwóch części: zewnętrznej i wewnętrznej, która wykonuje się dla każdego rekordu przeglądanego przez zapytanie zewnętrzne. Co za tym idzie w podzapytaniu mamy odwołanie do zapytania zewnętrznego:

SELECT wyrażenie

FROM źródło z1

WHERE wyrażenie = (SELECT wyrażenie, FROM źródło z2 WHERE z2. ... = z1. ...)

Deklaracja zmiennych:

DECLARE @identyfikator typ_danych

Deklaracja zmiennej tabelarycznej:

DECLARE @tab TABLE (Id int NOT NULL, FirstName varchar(30) NOT NULL ...);

Przypisanie wartości: SET @identyfikator = wartość; 

SELECT identyfikator = wartość;

Parametry (procedury, funkcje)

INPUT (IN) -wejściowe

OUTPUT (OUT) - wyjściowe

RETURN - zwraca status wykonania procedury lub obliczoną wartośćfunkcji

IF... ELSE - instrukcja warunkowa

BEGIN ... END - blok kodu

WHILE - pętla while (BREAK, CONTINUE)

Powiadomienia o błędach:

Error number 1 - 49999; 50000 - zarezerwowany dla nieznanych błędów

Tworzenie własnych błędów: EXEC sp_addmessage 50001, 16, N'Błąd'; GO

Deklaracja procedury:

CREATE PROCEDURE schama.nazwa [parametry]

AS

definicja 

GO

Wywołanie procedury: EXEC nazwa_procedury[parametry]

Deklaracja funkcji:

CREATE FUNCTION schema.nazwa([parametry]) RETURNS typ_Danych

AS

BEGIN

.

.

RETURN wyrażenie;

END;

Wywołanie: SELECT nazwa_funkcji([parametry])

Wyzwalacze

Są rodzajem procedur, których nie da się wywołać "ręcznie" - są wywoływane automatycznie w wyniku zajścia zdefiniowanego zdarzenia:

CREATE TRIGGER schama.nazwa

ON nazwa_tabeli/nazwa_widoku

AFTER/FOR [INSERT|DELETE|UPDATE] AS

BEGIN

...

END;

GO

Widoki:

Pozwalają na przechowywanie złożonych definicji w bazie danych i późniejsze ich wykorzystanie:

CREATE VIEW schema.nazwa

AS

instrukcja_selekt

Indeksy:

Zalety stosowania:

- zwiększają szybkość wyszukiwania, łączenia tabel, sortowania, grupowania

- stosując dodatkowe parametry mogą wymuszać unikalność wierszy
Wady:

- zużycie miejsca na dysku

- spowolnienie operacji dodawania, usuwania oraz modyfiowania

Co indeksować:

- klucze główne i obce

- kolumny z których są wyciągane zakresy danych

- kolumny według których następuje sortowanie, agregowanie, grupowanie 

Tworzenie indeksu:

CREATE INDEX nazwa ON tabela(kolumna)

Indeks złożony:

- nakładany na przynajmniej dwie kolumny

- kolumny muszą być z tej samej tabeli

- warto podać najpierw kolumnę bardziej unikalną

- w klauzuli WHERE należy podać pierwszą kolumnę indeksu aby indeks został wykorzystany

- indeks (k1, k2) nie jest równoważny indeksowi (k2, k1)