piątek, 16 lipca 2010

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)

Brak komentarzy:

Prześlij komentarz