Wykonywanie
podstawowych zapytań
Schemat bazy danych
1
Schemat bazy danych
Składnia SELECT
SELECT [predykat] { * | tabela.* | [tabela.]pole1 [AS alias1]
[, [tabela.]pole2 [AS alias2] [, ...]]}
FROM krotka [, ...] [IN zewnętrzna baza]
[WHERE... ]
[GROUP BY... ]
[HAVING... ]
[ORDER BY... ]
[WITH OWNERACCESS OPTION]
Predykaty w SELECT
SELECT [ALL | DISTINCT | DISTINCTROW | [TOP n [PERCENT]]]
FROM tabela
2
Przykłady SELECT
Wybranie wszystkich pól z tabeli zarobki
SELECT * FROM zarobki
Aliasy pól i tabel oraz klauzula WHERE
SELECT o.nazwisko AS nazwa
FROM Osoby AS o
WHERE o.IdOsoby>3;
Przykłady SELECT
Zapytanie wybierające z wyrażeniem
SELECT brutto, 0.8*brutto AS dochod FROM zarobki
SELECT brutto, idosoby*brutto AS cos FROM zarobki
3
Zapytania wybierające
Przykłady podstawowe
Wyświetlenie trzech wybranych pól z tabeli
SELECT Nazwisko, Imie, Wiek
FROM Osoby;
Wyświetlenie wszystkich pól z tabeli
SELECT * FROM Osoby;
Zapytania wybierające
Wyrażenia i Aliasy pól
Wyświetlenie iloczynu dwóch pól
SELECT wiek*wzrost AS Iloczyn
FROM Osoby;
Wyświetlenie łączne Imienia i Nazwiska
SELECT Nazwisko + ' ' + Imie AS Osoba
FROM Osoby;
4
Zapytania wybierające
Wyrażenia i Aliasy w sortowaniu
Sortowanie wg wyrażenia
SELECT wiek*wzrost AS Iloczyn FROM Osoby
ORDER BY wiek*wzrost;
Sortowanie wg aliasu
SELECT wiek*wzrost AS Iloczyn FROM Osoby
ORDER BY Iloczyn;
Zapytania wybierające
Operatory
Algebraiczne
Logiczne
+ dodawanie
AND iloczyn bitowy (AND)
- odejmowanie
OR suma bitowa (OR)
* mnożenie
NOT przeczenie bitowe (NOT)
/ dzielenie
% modulo
+ Konaktacja łańcucha
5
Zapytania wybierające
Sortowanie
Sortowanie według pojedynczego pola - rosnąco
SELECT Nazwisko, Imie FROM Osoby
ORDER BY Wiek;
Sortowanie według pojedynczego pola - rosnąco
SELECT Nazwisko, Imie FROM Osoby
ORDER BY Wiek ASC;
Zapytania wybierające
Sortowanie
Sortowanie według pojedynczego pola - malejąco
SELECT Nazwisko, Imie FROM Osoby
ORDER BY Wiek DESC;
Sortowanie według dwóch pól
SELECT Nazwisko, Imie FROM Osoby
ORDER BY Wiek ASC, Wzrost DESC;
6
Zapytania wybierające
Filtrowanie
Filtrowanie wg wyrażenia algebraicznego
SELECT Nazwisko, Imie FROM Osoby
WHERE Wiek >30;
Filtrowanie wg wyrażenia logicznego
SELECT Nazwisko, Imie FROM Osoby
WHERE Wiek >30 AND Wiek <50;
Zapytania wybierające
Operatory algebraiczne
= równe
< mniejsze niż
<= mniejsze niż lub równe
> większe niż
>= większe niż lub równe
<> różne
!= nie równe
7
Zapytania wybierające
Operatory logiczne
NOT negacja
OR suma logiczna
AND iloczyn logiczny
TRUE FALSE NULL
TRUE FALSE NULL OR
AND
TRUE TRUE TRUE TRUE
TRUE TRUE FALSE NULL
FALSE TRUE FALSE NULL
FALSE FALSE FALSE FALSE
NULL TRUE NULL NULL
NULL NULL FALSE NULL
Zapytania wybierające
Operatory specjalne
BETWEEN przedział dwustronnie domknięty
IN lista
LIKE podobny do wzorca
ANY prawda jeśli jedna pozycja na liście prawdziwa
SOME prawda jeśli kilka pozycji na liście prawdziwych
ALL prawda jeśli wszystkie pozycje na liście prawdziwe
EXISTS prawda jeśli zapytanie zwraca rekordy
8
Zapytania wybierające
Filtrowanie - BETWEEN
SELECT Nazwisko, Imie FROM Osoby
WHERE Wiek BETWEEN 30 AND 50;
Równoważne zapytanie
SELECT Nazwisko, Imie FROM Osoby
WHERE Wiek >=30 AND Wiek <=50;
Zapytania wybierające
Filtrowanie  IN
SELECT Nazwisko, Imie FROM Osoby
WHERE Wiek IN (20, 30, 40);
Równoważne zapytanie
SELECT Nazwisko, Imie FROM Osoby
WHERE Wiek = 20 OR Wiek = 30 OR Wiek = 40;
9
Zapytania wybierające
Filtrowanie  IN
SELECT Nazwisko, Imie FROM Osoby
WHERE Nazwisko IN ('Kowalski', 'Nowak');
Równoważne zapytanie
SELECT Nazwisko, Imie FROM Osoby
WHERE Nazwisko = 'Kowalski'
OR Nazwisko= 'Nowak'
Zapytania wybierające
Filtrowanie  LIKE
SELECT Nazwisko, Imie FROM Osoby
WHERE Nazwisko LIKE 'KOW';
Równoważne zapytanie
SELECT Nazwisko, Imie FROM Osoby
WHERE Nazwisko = 'KOW';
10
Zapytania wybierające
Operator LIKE - znaki specjalne
% dowolny ciąg znaków (w tym ciąg pusty)
_dokładnie jeden znak
Zapytania wybierające
Operator LIKE - znaki specjalne przykłady
Nazwiska rozpoczynające się od frazy KOW
SELECT Nazwisko, Imie FROM Osoby
WHERE Nazwisko LIKE 'KOW%';
Nazwiska kończące się frazą KOW
SELECT Nazwisko, Imie FROM Osoby
WHERE Nazwisko LIKE '%KOW';
Nazwiska posiadające w środku frazę KOW
SELECT Nazwisko, Imie FROM Osoby
WHERE Nazwisko LIKE '%KOW%';
11
Zapytania wybierające
Operator LIKE - znaki specjalne przykłady cd
Nazwiska rozpoczynające się od litery K a kończące literą I
SELECT Nazwisko, Imie FROM Osoby
WHERE Nazwisko LIKE 'K%I';
Nazwiska w których na trzeciej pozycji występuje litera W
SELECT Nazwisko, Imie FROM Osoby
WHERE Nazwisko LIKE '__W%';
Nazwiska w których występuje znak %
SELECT Nazwisko, Imie FROM Osoby
WHERE Nazwisko LIKE '[%]%';
Zapytania wybierające
Grupowanie
Wyznaczenie funkcji agregującej dla grupy (dla każdej osoby)
SELECT IdOsoby, SUM(Brutto) AS Razem
FROM Zarobki
GROUP BY IdOsoby;
Wyznaczenie funkcji agregującej dla całej tabeli (wszystkie osoby)
SELECT SUM(Brutto) AS Razem
FROM Zarobki;
12
Zapytania wybierające
Funkcje agregujące
AVG wartość średnia
SUM suma
MAX maksimum
MIN minimum
2
n x2 -(" x)
"
n(n -1)
STDEV odchylenie standardowe
2
VAR wariacja
n x2 -("x)
"
n(n -1)
STDEVP odchylenie populacji
VARP wariacja populacji
COUNT zlicz
Zapytania wybierające
Złączenia w oparciu o klauzulę WHERE
SELECT Nazwisko, Brutto FROM Osoby, Zarobki
SELECT Nazwisko, Brutto
FROM Osoby, Zarobki
WHERE Osoby.IdOsoby=Zarobki.IdOsoby
SELECT Nazwa, Nazwisko, Brutto
FROM Osoby, Zarobki, Dzialy
WHERE Osoby.IdOsoby = Zarobki.IdOsoby
AND Dzialy.IdDzialu = Osoby.IdDzialu
13
Zapytania wybierające
Złączenia w oparciu o operator JOIN
INNER
SELECT Nazwisko, Brutto
FROM Zarobki JOIN Osoby
ON Osoby.IdOsoby=Zarobki.IdOsoby
SELECT Nazwa, Nazwisko, Brutto
FROM Zarobki JOIN Osoby
ON Osoby.IdOsoby = Zarobki.IdOsoby
JOIN Dzialy
ON Dzialy.IdDzialu = Osoby.IdDzialu
Zapytania wybierające
Złączenia w oparciu o operator JOIN cd
SELECT Nazwisko, Brutto
FROM Zarobki RIGHT JOIN Osoby
ON Osoby.IdOsoby=Zarobki.IdOsoby
SELECT Nazwisko, Brutto
FROM Zarobki LEFT JOIN Osoby
ON Osoby.IdOsoby=Zarobki.IdOsoby
SELECT Nazwisko, Brutto
FROM Zarobki FULL JOIN Osoby
ON Osoby.IdOsoby=Zarobki.IdOsoby
14
Zapytania wybierające
Złączenia i aliasing
SELECT Nazwisko, Brutto
FROM Zarobki JOIN Osoby
ON Osoby.IdOsoby=Zarobki.IdOsoby
SELECT Nazwisko, Brutto
FROM Zarobki AS Z JOIN Osoby AS O
ON O.IdOsoby=Z.IdOsoby
SELECT Nazwisko, Brutto
FROM Zarobki Z JOIN Osoby O
ON O.IdOsoby=Z.IdOsoby
Zapytania wybierające
Złączenia
SELECT Nazwisko, Brutto
FROM Zarobki CROSS JOIN Osoby
15
Złączenia optymalizacja i plan wykonania
Zapytania wybierające
Złączenia optymalizacja i plan wykonania
Zapytania wybierające
16
Zapytania wybierające
Złączenia optymalizacja i plan wykonania
Zapytania wybierające
Podzapytania
Podzapytanie jako lista dla operatora IN
SELECT Nazwisko, Imie FROM Osoby
WHERE Nazwisko IN
(SELECT Nazwisko FROM ttt);
17
Zapytania wybierające
Podzapytania
Podzapytanie jako lista dla operatora IN
SELECT Nazwisko, Imie FROM Osoby
WHERE Nazwisko IN
(SELECT Nazwisko FROM ttt)
AND Imie IN
(SELECT Imie FROM ttt);
OSOBY ttt
Kowalski Karol
Kowalski Jan
Nowak Jan
Zapytania wybierające
Dziwne Złączenia w oparciu o operator JOIN
SELECT Osoby.Nazwisko
FROM Osoby JOIN ttt
ON Osoby.Nazwisko=ttt.Nazwisko
OSOBY
Idosoby
ttt
Nazwisko
Nazwisko
Imie
Imie
...
...
18
Zapytania wybierające
Dziwne Złączenia w oparciu o operator JOIN
SELECT Osoby.Nazwisko
FROM Osoby JOIN ttt
ON Osoby.Nazwisko=ttt.Nazwisko
AND Osoby.Imie=ttt.Imie
...
OSOBY
Idosoby
ttt
Nazwisko
Nazwisko
Imie
Imie
...
...
Zapytania wybierające
Grupowanie i wyznaczanie średniej dla grupy
SELECT Nazwa, AVG(brutto) FROM Dzialy
JOIN Osoby ON Dzialy.IdDzialu=Osoby.IdDzialu
JOIN Zarobki
ON Osoby.IdOsoby=Zarobki.IdOsoby
GROUP BY Nazwa
19
Zapytania wybierające
Funkcje agregujące
Funkcja agregująca
Opis
AVG Średnia wartości wyrażenia
COUNT Ilość wartości not NULL
COUNT (*) Ilość wierszy
MAX Maksymalna wartość wyrażenia
MIN Minimalna wartość wyrażenia
2
SUM Suma wyrażenia
n x2 -(" x)
"
n(n -1)
STDEV Odchylenie standardowe
STDEVP Odchylenie standardowe populacji
2
n x2 -("x)
"
VAR Wariancja
n(n -1)
VARP Wariancja wartości dla populacji
Zapytania wybierające
Grupowanie i wyznaczanie średniej dla grupy (podwójne grupowanie)
SELECT opis, Nazwisko, AVG(brutto) FROM Dzialy
JOIN Osoby ON Dzialy.IdDzialu=Osoby.IdDzialu
JOIN Zarobki
ON Osoby.IdOsoby=Zarobki.IdOsoby
GROUP BY opis ,Nazwisko, Osoby.IdOsoby
20
Zapytania wybierające
Grupowanie z klauzulą WHERE
SELECT opis, Nazwisko, AVG(brutto) FROM Dzialy
JOIN Osoby ON Dzialy.IdDzialu=Osoby.IdDzialu
JOIN Zarobki
ON Osoby.IdOsoby=Zarobki.IdOsoby
WHERE Brutto >100
GROUP BY opis ,Nazwisko, Osoby.IdOsoby
Zapytania wybierające
Grupowanie z klauzulą HAVING
SELECT opis, Nazwisko, AVG(brutto) FROM Dzialy
JOIN Osoby ON Dzialy.IdDzialu=Osoby.IdDzialu
JOIN Zarobki
ON Osoby.IdOsoby=Zarobki.IdOsoby
GROUP BY opis ,Nazwisko, Osoby.IdOsoby
HAVING AVG(brutto)>300
Tutaj nie może występować
alias nazwy pola
21
Zapytania wybierające
Grupowanie z klauzulami WHERE i HAVING
SELECT opis, Nazwisko, AVG(brutto) FROM Dzialy
JOIN Osoby ON Dzialy.IdDzialu=Osoby.IdDzialu
JOIN Zarobki
ON Osoby.IdOsoby=Zarobki.IdOsoby
WHERE Brutto >100
GROUP BY opis ,Nazwisko, Osoby.IdOsoby
HAVING AVG(brutto)>300
ORDER BY AVG(Brutto)
Tutaj może występować alias
nazwy pola
Zapytania wybierające
Grupowanie w podzapytaniu
SELECT Nazwisko FROM Osoby
WHERE IdOsoby IN
(SELECT IdOsoby FROM Zarobki
GROUP BY IdOsoby
HAVING SUM(brutto)>300)
SELECT Nazwisko, Imie FROM Osoby
WHERE Nazwisko IN
(SELECT Nazwisko FROM Osoby
GROUP BY Nazwisko
HAVING COUNT(Nazwisko) >1)
22
Zapytania wybierające
Grupowanie z klauzulami ROLLUP i CUBE
SELECT opis, Nazwisko, SUM(brutto) FROM Dzialy
JOIN Osoby ON Dzialy.IdDzialu=Osoby.IdDzialu
JOIN Zarobki
ON Osoby.IdOsoby=Zarobki.IdOsoby
GROUP BY opis ,Nazwisko, Osoby.IdOsoby
WITH ROLLUP
--WITH CUBE
ORDER BY AVG(Brutto)
Grupowanie z klauzulami ROLLUP i CUBE
Administracja Janik 555,00
Administracja Janik 555,00
Administracja Nowak 1332,00
Administracja Nowak 1332,00
Administracja NULL 1887,00
Administracja NULL 1887,00
Dyrekcja Kowalski 2109,00
Dyrekcja Kowalski 2109,00
Dyrekcja NULL 2109,00
Dyrekcja NULL 2109,00
Techniczny Adamczyk 777,00
Techniczny Adamczyk 777,00
Techniczny Kow 222,00
Techniczny Kow 222,00
Techniczny NULL 999,00
Techniczny NULL 999,00
NULL NULL 4995,00 NULL NULL 4995,00
NULL Adamczyk 777,00
NULL Janik 555,00
NULL Kow 222,00
NULL Kowalski 2109,00
NULL Nowak 1332,00
23
WITH CUBE; ROLLUP
SELECT Nazwa, Nazwisko, SUM(Brutto)
FROM Dzialy JOIN Osoby
ON Dzialy.Iddzialu=Osoby.Iddzialu
JOIN Zarobki
On Osoby.Idosoby=Zarobki.Idosoby
GROUP BY Nazwa, Nazwisko
WITH ROLLUP
SELECT Nazwa, Nazwisko, SUM(Brutto)
FROM Dzialy JOIN Osoby
ON Dzialy.Iddzialu=Osoby.Iddzialu
JOIN Zarobki
On Osoby.Idosoby=Zarobki.Idosoby
GROUP BY Nazwa, Nazwisko
WITH CUBE
DateTime and Security Functions (Transact-SQL)
SELECT YEAR(GETDATE());
SELECT DATEPART(hh, GETDATE());
SELECT CURRENT_USER, SESSION_USER,
SYSTEM_USER,SCHEMA_NAME(),
SUSER_ID(),SUSER_NAME(SUSER_ID()),
USER_NAME(SUSER_ID());
24
Funkcje OVER PARTITION
SELECT Idosoby, Brutto,
ROW_NUMBER() OVER (ORDER BY Idosoby)AS RowNumber
FROM Zarobki
SELECT Idosoby, Brutto,
ROW_NUMBER() OVER ( PARTITION BY idosoby ORDER BY Brutto DESC)AS
RowNumber
FROM Zarobki
Funkcje OVER PARTITION
SELECT Idosoby, Brutto,
Dense_Rank()
OVER (PARTITION BY Idosoby ORDER BY Brutto)AS D_Rank
FROM Zarobki
SELECT Idosoby, Brutto,
Rank() OVER (PARTITION BY idosoby ORDER BY brutto)AS Rank
FROM Zarobki
SELECT Idosoby, Brutto,
Ntile(2) OVER (PARTITION BY idosoby ORDER BY Brutto)AS N_Rank
FROM ZAROBKI
25
Ranking Functions (Transact-SQL)
Ustala ranking według wzrostu w obrębie działu.
Dla równych wartości taki sam ranking ale następny zwiększany o N
SELECT IdDzialu ,Wzrost, RANK() OVER (PARTITION BY IdDzialu
ORDER BY Wzrost DESC) AS RANK
FROM Osoby
Wzrost
IdDzialu RANK
ORDER BY Iddzialu
1 1.82 1
1 1.67 2
1 1.67 2
1 NULL 4
2 1.75 1
2 1.72 2
2 1.68 3
3 1.78 1
3 1.72 2
5 1.73 1
Ranking Functions (Transact-SQL)
Ustala ranking według wzrostu w obrębie działu.
Dla równych wartości taki sam ranking ale następny zwiększany o 1
SELECT IdDzialu ,Wzrost, DENSE_RANK() OVER (PARTITION BY IdDzialu
ORDER BY Wzrost DESC) AS RANK
FROM Osoby
IdDzialu Wzrost RANK
ORDER BY Iddzialu
1 1.82 1
1 1.67 2
1 1.67 2
1 NULL 3
2 1.75 1
2 1.72 2
2 1.68 3
3 1.78 1
3 1.72 2
5 1.73 1
26
Ranking Functions (Transact-SQL)
Ustala numer wiersza według wzrostu w obrębie działu.
Dla równych wartości numer wiersza losowo
SELECT IdDzialu ,Wzrost, ROW_NUMBER() OVER (PARTITION BY IdDzialu
order by Wzrost DESC) AS NUMER_WIERSZA
FROM Osoby
IdDzialu Wzrost NUMER_WIERSZA
ORDER BY Iddzialu
1 1.82 1
1 1.67 2
1 1.67 3
1 NULL 4
2 1.75 1
2 1.72 2
2 1.68 3
3 1.78 1
3 1.72 2
5 1.73 1
Ranking Functions (Transact-SQL)
Ustala numer wiersza według wzrostu w obrębie klastra (liczba klastrów dana
parametrem) działu. Dla równych wartości numer wiersza losowo
SELECT IdDzialu ,Wzrost, NTILE(2) OVER (PARTITION BY IdDzialu ORDER
BY Wzrost DESC) AS NUMER_WIERSZA
FROM Osoby
ORDER BY Iddzialu
IdDzialu Wzrost NUMER_WIERSZA
1 1.82 1
1 1.67 1
1 1.67 2
1 NULL 2
2 1.75 1
2 1.72 1
2 1.68 2
3 1.78 1
3 1.72 2
5 1.73 1
27
Funkcje agregujące nad partycją (Transact-SQL)
SELECT IdDzialu, Osoby.IdOsoby, Brutto,
SUM(Brutto) OVER (PARTITION BY IdDzialu) AS suma,
Brutto / SUM(Brutto) OVER(PARTITION BY IdDzialu) AS udzial
FROM Osoby JOIN Zarobki
ON Osoby.IdOsoby=Zarobki.IdOsoby
Funkcje agregujące nad partycją (Transact-SQL)
SELECT IdDzialu, Osoby.IdOsoby, Brutto,
SUM(Brutto) OVER (PARTITION BY Osoby.IdOsoby) AS suma,
Brutto / SUM(Brutto) OVER(PARTITION BY IdDzialu) AS udzial,
SUM(Brutto) OVER (PARTITION BY Osoby.IdOsoby)/
SUM(Brutto) OVER(PARTITION BY IdDzialu) AS udzial_w_dziale
FROM Osoby JOIN Zarobki
ON Osoby.IdOsoby=Zarobki.IdOsoby
28
Informacje o...
SELECT HOST_ID (),HOST_NAME (), APP_NAME()
SELECT @@SERVERNAME, @@SERVICENAME,
@@DBTS;
EXEC sp_who
Zapytania wybierające
Zastosowanie SOME, ANY i ALL
SELECT Brutto FROM Zarobki WHERE
Brutto > ANY (SELECT AVG(Brutto)FROM
Zarobki GROUP BY IdOsoby)
ROWNOWAŻNE
SELECT Brutto FROM Zarobki WHERE
SELECT AVG(Brutto)
FROM Zarobki
Brutto > SOME (SELECT AVG(Brutto)FROM
GROUP BY IdOsoby
Zarobki GROUP BY IdOsoby)
SELECT Brutto FROM Zarobki WHERE
Brutto > ALL (SELECT AVG(Brutto)FROM
Zarobki GROUP BY IdOsoby)
29
Zapytania wybierające
Klauzula COMPUTE
SELECT Nazwa, Nazwisko, Brutto
FROM Dzialy LEFT JOIN Osoby
ON Dzialy.IdDzialu=Osoby.IdDzialu
LEFT JOIN Zarobki
ON Osoby.IdOsoby=Zarobki.IdOsoby
ORDER BY Nazwa
COMPUTE SUM(Brutto) BY Nazwa
Zapytania wybierające
Zamiast klauzuli COMPUTE
SELECT Nazwa, Nazwisko, SUM(Brutto)As RazemO, RazemD
FROM Dzialy JOIN Osoby ON Dzialy.IdDzialu=Osoby.IdDzialu
JOIN Zarobki ON Osoby.IdOsoby=Zarobki.IdOsoby
JOIN
(SELECT Osoby.IdDzialu, SUM(brutto) AS RazemD FROM Dzialy
JOIN Osoby ON Dzialy.IdDzialu=Osoby.IdDzialu
JOIN Zarobki
ON Osoby.IdOsoby=Zarobki.IdOsoby
GROUP BY Osoby.IdDzialu) as xxx
ON Osoby.IdDzialu=xxx.IdDzialu
GROUP BY Nazwa, Nazwisko, zarobki.IdOsoby, RazemD
30
Zapytania wybierające
Klauzula COMPUTE cd
SELECT Nazwa, Nazwisko, Brutto
FROM Dzialy LEFT JOIN Osoby
ON Dzialy.IdDzialu=Osoby.IdDzialu
LEFT JOIN Zarobki
ON Osoby.IdOsoby=Zarobki.IdOsoby
ORDER BY Nazwa, Nazwisko
COMPUTE SUM(Brutto) BY Nazwa, Nazwisko
COMPUTE SUM(Brutto) BY Nazwa Na każdym poziomie
grupowania można
wyznaczać wiele różnych
funkcji agregujących
Zapytania wybierające
Klauzula COMPUTE cd
SELECT Nazwa, Nazwisko, Brutto
FROM Dzialy LEFT JOIN Osoby
ON Dzialy.IdDzialu=Osoby.IdDzialu
LEFT JOIN Zarobki
ON Osoby.IdOsoby=Zarobki.IdOsoby
Na każdym poziomie
grupowania można
wyznaczać wiele różnych
funkcji agregujących
ORDER BY Nazwa, Nazwisko
COMPUTE SUM(Brutto) BY Nazwa, Nazwisko
COMPUTE SUM(Brutto), AVG(Brutto) BY Nazwa
31
Zapytania wybierające
Klauzula Pivot
SELECT kto, [1] AS D1, [2] AS D2, [3] AS D3, [4] AS D4, [5] AS D5
FROM
(SELECT IdDzialu, Nazwisko+ ' '+ Imie as Kto, Brutto
FROM Osoby JOIN Zarobki ON Osoby.IdOsoby=Zarobki.IdOsoby) ppp
PIVOT
(
SUM (Brutto)
Kto D1 D2 D3 D4 D5
FOR IdDzialu IN
1 Adamczyk Janusz NULL NULL 777,00 NULL NULL
2 Janik Paweł NULL 555,00 NULL NULL NULL
( [1], [2], [3], [4], [5] )
3 Kow Piotr NULL NULL 666,00 NULL NULL
4 Kowalczyk Jarosław NULL NULL 777,00 NULL NULL
) AS pvt
5 Kowalski Jan 2109,00 NULL NULL NULL NULL
ORDER BY kto
6 Nowak Karol NULL 1332,00 NULL NULL NULL
7 Nowicki Jan NULL NULL NULL 2109,00 NULL
8 Zięba Andrzej 333,00 NULL NULL NULL NULL
Zapytania wybierające
Predykat TOP
SELECT TOP 5 Nazwisko, Imie
FROM Osoby ORDER BY Wzrost
SELECT TOP 5 Nazwisko, Imie
FROM Osoby ORDER BY Wzrost DESC
SELECT TOP 5 PERCENT Nazwisko, Imie
FROM Osoby ORDER BY Wzrost
32
Zapytania wybierające
Predykat DISTINCT
SELECT DISTINCT Nazwisko
FROM Osoby
SELECT DISTINCT Nazwisko, Imie
FROM Osoby
Zapytania wybierające
Operator CASE
SELECT Nazwisko, RokUrodz, Wiek=
CASE
WHEN RokUrodz >=1970 THEN 'Młody'
WHEN RokUrodz >=1960 AND RokUrodz <1970 THEN 'Taki sobie'
WHEN RokUrodz <1960 THEN 'Stary'
ELSE 'A bo ja wiem'
END
FROM Osoby
33
Zapytania wybierające
Operator CASE  można pominąć część warunku jeśli
odpowiednia kolejność
SELECT Nazwisko, RokUrodz, Wiek=
CASE
WHEN RokUrodz >1970 THEN 'Młody'
WHEN RokUrodz >=1960 THEN 'Taki sobie'
WHEN RokUrodz <1960 THEN 'Stary'
ELSE 'A bo ja wiem'
END
FROM Osoby
Zapytania wybierające
Operator CASE  inny sposób aliasowania
SELECT Nazwisko, RokUrodz,
CASE
WHEN RokUrodz >1970 THEN 'Młody'
WHEN RokUrodz >=1960 THEN 'Taki sobie'
WHEN RokUrodz <1960 THEN 'Stary'
ELSE 'A bo ja wiem'
END AS Wiek
FROM Osoby
34
Zapytania wybierające
UNIA
Suma dwóch zapytań z eliminacją powtarzających się rekordów
SELECT Nazwisko, Imie FROM Osoby
UNION
SELECT Nazwisko, Imie FROM ttt;
Suma dwóch zapytań
SELECT Nazwisko, Imie FROM Osoby
UNION ALL
SELECT Nazwisko, Imie FROM ttt;
Zapytania wybierające
UNIA
Część wspólna dwóch zapytań
SELECT Nazwisko, Imie FROM Osoby
INTERSECT
SELECT Nazwisko, Imie FROM ttt;
Różnica dwóch zapytań
SELECT Nazwisko, Imie FROM Osoby
EXCEPT
SELECT Nazwisko, Imie FROM ttt;
35
Wyprowadzenie danych do formatu XML
SELECT Nazwisko, Imie FROM Osoby FOR XML AUTO
SKUTEK
XML_F52E2B61-18A1-11d1-B105-00805F49916B
-------------------------------------------------------------------------------------------------
nazwisko="Nowak" imie="Karol"/> imie="Piotr"/> nazwisko="Kowalik" imie="Paweł"/> imie="Janusz"/> Wyprowadzenie danych do formatu XML
SELECT Nazwa, Nazwisko, Imie FROM Dzialy JOIN Osoby ON
Osoby.IdDzialu=Dzialy. IdDzialu FOR XML AUTO
SKUTEK
XML_F52E2B61-18A1-11d1-B105-00805F49916B
----------------------------------------------------------------------------------------------------
imie="Jan"/> nazwisko="Nowak" imie="Karol"/> nazwa="Techniczny">
36
Wyprowadzenie danych do formatu XML
SELECT Nazwa, Nazwisko, Imie FROM Dzialy LEFT JOIN Osoby ON
Osoby.IdDzialu=Dzialy. IdDzialu FOR XML AUTO
SKUTEK
Nie ma pracowników Dyrekcji
XML_F52E2B61-18A1-11d1-B105-00805F49916B
----------------------------------------------------------------------------------------------------
nazwa="Administracja">imie="Karol"/>nazwisko="Lis" imie="Janusz"/>nazwa="Techniczny">Wyprowadzenie danych do formatu XML
SELECT Nazwisko, Imie FROM Osoby FOR XML AUTO,
ELEMENTS
SKUTEK
XML_F52E2B61-18A1-11d1-B105-00805F49916B
----------------------------------------------------------------------------------------------------
Kowalski Jan
Nowak Karol
Kow Piotr
Janik Paweł
Kowa...
37
Wyprowadzenie danych do formatu XML
SELECT Nazwa, Nazwisko, Imie FROM Dzialy JOIN Osoby ON
Osoby.IdDzialu=Dzialy. IdDzialu FOR XML AUTO, ELEMENTS
SKUTEK
XML_F52E2B61-18A1-11d1-B105-00805F49916B
----------------------------------------------------------------------------------------------------
Dyrekcja
Kowalski Jan
Administracja
Nowak Karol
Techniczny Ko ....
Wyprowadzenie danych do formatu XML
SELECT Nazwa, Nazwisko, Imie FROM Dzialy LEFT JOIN Osoby ON
Osoby.IdDzialu=Dzialy. IdDzialu FOR XML AUTO, ELEMENTS
SKUTEK
Nie ma pracowników Dyrekcji
XML_F52E2B61-18A1-11d1-B105-00805F49916B
----------------------------------------------------------------------------------------------------
Dyrekcja>AdministracjaNowak>KarolJanikP
awełLisJanus
z38
Zapytania modyfikujące
Tworzenie tabeli i wstawianie danych z
istniejącej tabeli   Kopiowanie tabeli
SELECT Nazwisko, wiek
INTO Nowa1
FROM Osoby
Zapytania modyfikujące
Wstawianie danych
INSERT INTO Nowa
VALUES ('KOWAL', 'JAN', 33);
INSERT INTO Nowa(Nazwisko, Imie)
VALUES ('NOWAK','KAROL');
39
Zapytania modyfikujące
Wstawianie danych z istniejącej tabeli
INSERT INTO Nowa
SELECT Nazwisko, Imie, Wiek FROM Osoby
WHERE Wiek >30
ORDER BY Nazwisko;
INSERT INTO Nowa(Imie, Nazwisko)
SELECT Imie, Nazwisko FROM Osoby
WHERE Wiek <30
ORDER BY Nazwisko DESC;
Zapytania modyfikujące
Wstawianie do pola z ustawionym Identity
SET IDENTITY_INSERT Dzialy ON;
GO
Lista pól jest obowiązkowa
INSERT INTO dzialy (IdDzialu, Nazwa)
VALUES (-13, 'Jakiś')
GO
SELECT * FROM Dzialy;
GO
SET IDENTITY_INSERT Dzialy OFF;
40
Zapytania modyfikujące
Nawiasy są obowiązkowe
INSERT TOP (5) INTO Nowa
SELECT Nazwisko, Imie, RokUrodz
Nie działa
sortowanie dla
FROM Osoby ORDER BY RokUrodz DESC
TOP()
SELECT * FROM Nowa
DELETE FROM Nowa
Nawiasy nie są obowiązkowe
INSERT INTO Nowa
SELECT TOP 5 Nazwisko, Imie, RokUrodz
FROM Osoby Order by RokUrodz DESC
SELECT * FROM Nowa
Działa sortowanie
DELETE FROM Nowa
dla TOP()
Zapytania modyfikujące
Modyfikacja danych
UPDATE Nowa
SET Wiek = 0
WHERE Wiek IS NULL;
UPDATE Nowa
SET Nazwisko = UPPER(Nazwisko),
Imie = UPPER(Imie);
41
Zapytania modyfikujące
Usuwanie danych
DELETE FROM Nowa
WHERE Wiek IS NULL;
DELETE FROM Nowa;
TRUNCATE TABLE Nowa;
Zapytania modyfikujące - OUTPUT
DECLARE @test TABLE(
Komu int NOT NULL,
Zmienna tabelaryczna
stara int,
nowa int,
kiedy datetime);
UPDATE Zarobki SET Brutto=1.1 * Brutto
OUTPUT INSERTED.IdOsoby,
DELETED.Brutto,
INSERTED.Brutto,
getdate()
INTO @test
WHERE IdOsoby<3
SELECT * FROM @test
GO
42
Zapytania modyfikujące - OUTPUT
DECLARE @test TABLE(
Komu int NOT NULL,
nowa int,
kiedy datetime);
INSERT INTO Zarobki
OUTPUT INSERTED.IdOsoby,
INSERTED.Brutto,
getdate()
Działa jak SELECT
INTO @test
VALUES(2, 111)
DELETE FROM Zarobki
SELECT * FROM @test
OUTPUT DELETED.*
GO
WHERE brutto IS NULL
Zapytania tworzące tabelę
Proste
CREATE TABLE Nowa
(
Nazwisko varchar (15),
Imie varchar (15),
Wiek integer
);
Ograniczenia
CREATE TABLE Nowa
(
IdOsoby int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
Nazwisko varchar(20) NULL ,
Imie varchar(20) NULL
);
43
Zapytania tworzące tabelę
Klucz główny złożony
DROP TABLE nnn;
CREATE TABLE nnn
(Nazwisko varchar(15),
Imie varchar(15),
CONSTRAINT klucz PRIMARY KEY (Nazwisko,Imie));
INSERT INTO nnn VALUES('KOWAL','JAN');
INSERT INTO nnn VALUES('KOWAL','KAROL');
INSERT INTO nnn VALUES('KOWAL','JAN');
SELECT * FROM nnn;
Zapytania tworzące tabelę
CREATE TABLE T1
(
Zamiast  minimalny rozmiar varchar
nr1 int IDENTITY,
nr2 uniqueidentifier nr2 varchar(36)
);
GO
INSERT INTO T1 (nr2) VALUES (NEWID());
INSERT INTO T1 DEFAULT VALUES;
GO
SELECT * FROM T1;
SKUTEK
1 DAA7C652-101F-443B-9378-4F55AE7BA3CD
2 NULL
44
Zapytania tworzące tabelę
UNIQUE
DROP TABLE nnn;
CREATE TABLE nnn
(Nazwisko varchar(15),
Imie varchar(15),
CONSTRAINT spr UNIQUE (Nazwisko,Imie));
INSERT INTO nnn VALUES('KOWAL','JAN');
INSERT INTO nnn VALUES('KOWAL','KAROL');
INSERT INTO nnn VALUES('KOWAL','JAN');
SELECT * FROM nnn;
Zapytania tworzące tabelę
Ograniczenia
DROP TABLE nnn;
CREATE TABLE nnn
(nr integer PRIMARY KEY,
Nazwisko varchar(15) NOT NULL,
Imie varchar(15) DEFAULT 'Brak',
m_v integer CHECK (m_v>10));
INSERT INTO nnn(nr, Nazwisko, Imie, m_v)
VALUES(1,'KOWAL','JAN',20);
INSERT INTO nnn(nr, Nazwisko, m_v) VALUES(2,'KOWAL',30);
INSERT INTO nnn(nr, Nazwisko, Imie, m_v)
VALUES(3,'KOWAL','JAN',4);
SELECT * FROM nnn;
45
Zapytania tworzące tabelę
Ograniczenia cd
DROP TABLE nnn;
CREATE TABLE nnn
(nr integer PRIMARY KEY,
Nazwisko varchar(15) NOT NULL,
Imie varchar(15) DEFAULT 'Brak',
m_v integer CHECK (m_v>10) ,
mm_v integer CHECK (mm_v<300));
INSERT INTO nnn(nr, Nazwisko, Imie, m_v, mm_v)
VALUES(1,'KOWAL','JAN',20,50);
INSERT INTO nnn(nr, Nazwisko, m_v,mm_v)
VALUES(2,'KOWAL',30,100);
INSERT INTO nnn(nr, Nazwisko, Imie, m_v,mm_v)
VALUES(3,'KOWAL','JAN',4,100);
Zapytania tworzące tabelę
Ograniczenia cd
DROP TABLE nnn;
CREATE TABLE nnn
(nr integer PRIMARY KEY,
Nazwisko varchar(15) NOT NULL,
Imie varchar(15) DEFAULT 'Brak',
m_v integer CHECK (m_v>10) ,
mm_v integer CHECK (mm_v<300) ,
CONSTRAINT spr CHECK(m_v INSERT INTO nnn(nr, Nazwisko, Imie, m_v, mm_v)
VALUES(1,'KOWAL','JAN',20,50);
INSERT INTO nnn(nr, Nazwisko, m_v,mm_v)
VALUES(2,'KOWAL',30,100);
INSERT INTO nnn(nr, Nazwisko, Imie, m_v,mm_v)
VALUES(3,'KOWAL','JAN',4,100);
46
Ustanowienie wartości domyślnej
DROP TABLE tts
GO
CREATE TABLE tts
(nr1 int,
ddd datetime DEFAULT getdate(),
oo varchar(10),
pp varchar(10),
CONSTRAINT sss CHECK (Left(pp,1)=Left(oo,1))
INSERT INTO tts DEFAULT VALUES;
47
Tworzenie tabeli z zastosowaniem ograniczeń dla daty
DROP TABLE Nowa
GO
CREATE TABLE Nowa
(nr1 int IDENTITY(1,1) PRIMARY KEY,
d1 datetime DEFAULT getdate(),
d2 datetime,
CONSTRAINT spr CHECK(d2>getdate())
);
INSERT INTO Nowa VALUES('2007-05-10','2007-05-11')
INSERT INTO Nowa (d2) VALUES('2007-05-11')
SELECT * FROM Nowa
Zapytania tworzące tabelę
Klucz obcy referencyjny
CREATE TABLE Nagrody
(
IdNagrody int NOT NULL PRIMARY KEY,
IdOsoby int NOT NULL
FOREIGN KEY REFERENCES Osoby(IdOsoby)
ON DELETE NO ACTION,
Nagroda varchar(15)
)
48
Zapytania tworzące tabelę
Klucz obcy referencyjny cd
CREATE TABLE Nagrody
(
IdNagrody int NOT NULL PRIMARY KEY,
IdOsoby int NOT NULL
FOREIGN KEY REFERENCES Osoby(IdOsoby)
ON DELETE CASCADE ON UPDATE CASCADE,
Nagroda varchar(15)
)
Zapytania tworzące tabelę
Tabela Tymczasowa lokalna
DROP TABLE #MyTempTable
GO
CREATE TABLE #MyTempTable
(nr INT PRIMARY KEY)
INSERT INTO #MyTempTable VALUES (1)
SELECT * FROM #MyTempTable
49
Zapytania tworzące tabelę
Tabela Tymczasowa globalna
DROP TABLE ##MyTempTable
GO
CREATE TABLE ##MyTempTable
(nr INT PRIMARY KEY)
INSERT INTO ##MyTempTable VALUES (1)
SELECT * FROM ##MyTempTable
Zapytania modyfikujące tabelę
ALTER
ALTER TABLE wys_tab
ADD nr integer;
SELECT * FROM wys_tab;
ALTER TABLE wys_tab
ALTER COLUMN nr varchar(3);
INSERT INTO wys_tab(nr) VALUES('aaa');
SELECT * FROM wys_tab;
50
Zapytania modyfikujące tabelę
ALTER cd
DROP TABLE nnn;
CREATE table nnn
(nr integer,
Nazwisko varchar(15) NULL,
CONSTRAINT kl PRIMARY KEY(nr));
INSERT INTO nnn(nr,nazwisko) VALUES(1,'KOWAL');
INSERT INTO nnn(nr) values(2);
ALTER TABLE nnn
DROP COLUMN nr;
SELECT * FROM nnn;
Zapytania modyfikujące tabelę
ALTER cd 1
ALTER TABLE wys_tab
DROP COLUMN nr;
SELECT * FROM wys_tab;
ALTER TABLE nnn
DROP CONSTRAINT spr;
INSERT INTO nnn(nr, Nazwisko, m_v, mm_v)
VALUES(2,'KOWAL',30,20);
SELECT * FROM nnn;
51
Zapytania modyfikujące tabelę
ALTER cd 1
ALTER TABLE Nowa
ADD CONSTRAINT kl PRIMARY KEY (nr);
INSERT INTO Nowa(nr, Nazwisko)
VALUES(2,'KOWAL');
SELECT * FROM nnn;
Zapytanie usuwające tabelę
Bez zastosowania i z zastosowaniem
EXISTS
DROP TABLE Nowa;
IF EXISTS
(SELECT Table_Name FROM
INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Rodzaj')
DROP TABLE Rodzaj
52
Widok - perspektywa
Tworzenie widoku
CREATE VIEW Dane AS
SELECT Nazwisko, Imie FROM Osoby
CREATE VIEW Dane_check AS
SELECT Nazwisko, Imie, Wzrost FROM Osoby
WHERE wzrost > 1.80
WITH CHECK OPTION
Nie zezwala na wprowadzenie
danych niezgodnych z warunkiem
w klauzuli WHERE
Widok - perspektywa
Zastosowanie EXISTS do usuwania
widoku
IF EXISTS
(SELECT * FROM SYSOBJECTS
WHERE Id = Object_Id(N'OsobyWiev')
AND OBJECTPROPERTY(Id, N'IsView') = 1)
DROP VIEW OsobyWiev
GO
CREATE VIEW OsobyWiev
AS
SELECT Nazwisko, Imie, Wzrost
FROM Osoby
GO
53
Widok - perspektywa
Wstawianie danych do widoku
INSERT INTO Dane VALUES ('Kowalski','Wiesław')
INSERT INTO Sklad(Nazwisko, Imie)
VALUES ('Kowalski','Wiesław')
Procedura składowana
Tworzenie procedury
CREATE PROCEDURE ap_wyb
@nazw varchar(40)='%',
@im varchar(20)='%'
AS
SELECT Nazwisko, Imie
FROM Osoby
WHERE Nazwisko LIKE @nazw + '%'
AND Imie LIKE @im + '%'
GO
54
Procedura składowana
CREATE PROCEDURE licz
@mini real = 0,
@ile int OUTPUT
AS
SELECT @ile=COUNT(IdOsoby) FROM Osoby
WHERE Wzrost > @mini
GO
DECLARE @ile int
EXEC licz 1.8, @ile OUTPUT
PRINT @ile
Wykonanie zapytania danego zmienną
DECLARE @k AS Varchar(20), @zap Varchar(200)
SET @k='Imie'
SET @zap='SELECT ' + @k + ' FROM Osoby'
EXEC (@zap)
55
Procedura składowana
Procedury zagnieżdżone
DROP PROCEDURE innerproc
DROP PROCEDURE outerproc
GO
CREATE PROCEDURE innerproc AS
SELECT @@NESTLEVEL AS 'Inner Level'
EXEC outerproc
GO
Poziom zagnieżdżenia
Wartość maksymalna 32
CREATE PROCEDURE outerproc AS
SELECT @@NESTLEVEL AS 'Outer Level'
EXEC innerproc
GO
EXECUTE outerproc
GO
Dynamiczny SQL
DROP TABLE zarobki_prac
GO
CREATE TABLE zarobki_prac
( zrodlo varchar(11) NOT NULL, kto int NOT NULL,
Nazwisko varchar(40) NOT NULL, ile money NOT NULL
);
GO
CREATE PROCEDURE spr_dochody
AS
SELECT 'PROCEDURE', Osoby.IdOsoby, Nazwisko, Brutto
FROM Osoby INNER JOIN Zarobki
ON Osoby.IdOsoby = Zarobki.IdOsoby
WHERE Brutto>500
ORDER BY IdOsoby;
GO
56
Dynamiczny SQL
--INSERT...SELECT
INSERT zarobki_prac
SELECT 'SELECT', Osoby.IdOsoby, Nazwisko, Brutto
FROM Osoby INNER JOIN Zarobki
ON Osoby.IdOsoby = Zarobki.IdOsoby
WHERE Brutto<300
ORDER BY IdOsoby;
GO
--INSERT...EXECUTE procedure
INSERT zarobki_prac
EXECUTE spr_dochody;
GO
Dynamiczny SQL
--INSERT...EXECUTE('string')
INSERT zarobki_prac
EXECUTE
('
SELECT ''EXEC STRING'', Osoby.IdOsoby, Nazwisko, Brutto
FROM Osoby INNER JOIN Zarobki
ON Osoby.IdOsoby = Zarobki.IdOsoby
WHERE Brutto BETWEEN 300 AND 500
ORDER BY IdOsoby
SELECT 1 Kowalski 122,10
');
SELECT 2 Nowak 111,00
GO
SELECT 3 Kow 222,00
PROCEDURE 1 Kowalski 732,60
SELECT * FROM zarobki_prac; PROCEDURE 1 Kowalski 1098,90
PROCEDURE 2 Nowak 976,80
GO
PROCEDURE 4 Janik 555,00
PROCEDURE 8 Adamczyk 777,00
EXEC STRING 1 Kowalski 366,30
EXEC STRING 2 Nowak 488,40
57
Funkcje
Funkcja zwracająca wartość
CREATE FUNCTION wysocy (@mm decimal (3,2) = 0)
RETURNS int AS
BEGIN
DECLARE @ile int
SELECT @ile=COUNT(wzrost) FROM Osoby
WHERE Wzrost >= @mm
RETURN @ile
END
WYWOA
ANIE
DECLARE @a int
SET @a=dbo.wysocy(1.5)
SELECT @a
Funkcja zwracająca rekordy (tabelę)  stara
postać
CREATE FUNCTION wysocy_table (@mm decimal (3,2) = 0)
RETURNS
@Wysocy TABLE (Nazwisko varchar(15),
Wzrost decimal(3,2))
AS
BEGIN
INSERT INTO @Wysocy
SELECT Nazwisko, Wzrost FROM Osoby WHERE Wzrost >= @mm
ORDER BY Wzrost DESC
RETURN
END
SELECT * FROM dbo.wysocy_table(1.5)
58
Funkcja zwracająca rekordy (tabelę)  nowa
postać
CREATE FUNCTION wysocyt (@minimum real)
RETURNS TABLE
AS
RETURN
(
SELECT Nazwisko,imie, wzrost
FROM osoby WHERE wzrost >=@minimum
)
SELECT * FROM wysocyt (1.5)
Nie jest potrzebne w wywołaniu funkcji zwracającej tabelę
stosowanie nazw kwalifikowanych (dbo.wysocyt) wystarczy podać
nazwę ale w skalarnych pozostało nawet jeśli określono synonim
(do takiego synonimu też nazwa kwalifikowana)
Złączenie z funkcją zwracającą rekordy (tabelę)
DROP FUNCTION pracownicy
GO
CREATE FUNCTION pracownicy (@dzial int)
RETURNS TABLE
AS
RETURN
(SELECT Nazwisko, Imie FROM Osoby WHERE IdDzialu = @dzial)
GO
SELECT Nazwa, Nazwisko FROM Dzialy CROSS APPLY
dbo.pracownicy(iddzialu);
SELECT Nazwa, Nazwisko FROM Dzialy OUTER APPLY
dbo.pracownicy(iddzialu);
Prefiks dbo opcjonalnie
59
Transakcje
Tworzenie
BEGIN TRANSACTION
DECLARE @nr int
SELECT Nazwisko FROM Osoby
SET @nr=@@ROWCOUNT
PRINT @nr
UPDATE Osoby SET Nazwisko = UPPER(Nazwisko)
IF @@ROWCOUNT = @nr
COMMIT TRAN
IF @@TRANCOUNT > 0
BEGIN
PRINT 'Transakcja musi być cofnięta
ROLLBACK TRAN
END
Transakcje
Hierarchiczne  (zagnieżdżone)
CREATE TABLE TestTran (Cola INT PRIMARY KEY, Colb CHAR(3))
GO
BEGIN TRANSACTION OuterTran
INSERT INTO TestTran VALUES (1, 'aaa')
GO
BEGIN TRANSACTION Inner1
INSERT INTO TestTran VALUES (2, 'bbb')
GO
BEGIN TRANSACTION Inner2
INSERT INTO TestTran VALUES (3, 'ccc')
GO
COMMIT TRANSACTION Inner2
COMMIT TRANSACTION Inner1
COMMIT TRANSACTION OuterTran
GO
60
Transakcje
SAVE  punkt wycofania transakcji
BEGIN TRANSACTION podwyzka
SELECT Brutto FROM ZarobkiWHERE IdOsoby IN (2, 3)
UPDATE zarobki SET Brutto = Brutto*1.1 WHERE IdOsoby = 2
SAVE TRANSACTION podwyzka
UPDATE zarobki SET brutto = brutto*0.9 WHERE idosoby = 3
SELECT Brutto FROM ZarobkiWHERE IdOsoby= 3
ROLLBACK TRANSACTION podwyzka
COMMIT TRANSACTION
SELECT Brutto FROM ZarobkiWHERE IdOsoby IN (2, 3)
Zastosowanie ustawienia błędu do wyświetlenia
komunikatu
DECLARE @DBID INT;
SET @DBID = DB_ID();
DECLARE @DBNAME NVARCHAR(128);
SET @DBNAME = DB_NAME();
RAISERROR (N Identyfikator bazy ID =%d,
Nazwa bazy = %s.',
10, -- grupa błędów.
1, -- podgrupa.
@DBID, -- Pierwszy argument.
@DBNAME); -- Drugi argument.
GO
61
Tworzenie błędów użytkownika
--Tworzenie błędu użytkownika
sp_addmessage 50001, 15, 'Komunikat';
GO
RAISERROR (50001,10,2)
--Nadpisanie definicji błędu i uzupełnienie jej o parametry
sp_addmessage 50001, 15, 'Komunikat %d, %s', @replace='replace';
GO
DECLARE @i int, @s varchar(15)
SET @i=11
SET @s='napis'
RAISERROR (50001,10,2, @i, @s)
Zastosowanie ustawienia błędu do wyświetlenia
komunikatu w bloku TRY CATCH
BEGIN TRY
-- RAISERROR z grup 11-19 przeniesie wykonanie do bloku CATCH
-- Niższe grupy nie powodują przeniesienia
RAISERROR ('Błąd w bloku TRY .', 16, 1 );
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
-- zastosowano RAISERROR w bloku CATCH aby zwrócić
-- informacje o błędzie, który spowodował przeniesienie
-- wykonywania skryptu do bloku CATCH .
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;
62
Zastosowanie ustawienia błędu do wyświetlenia
komunikatu w bloku TRY CATCH
BEGIN TRY
-- Dzielenie przez zero.
SELECT 1/0;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS NumerBłędu,
ERROR_SEVERITY() AS SeverityBłęu,
ERROR_STATE() AS StateBłędu,
ERROR_PROCEDURE() AS ProceduraBłędu,
ERROR_LINE() AS LiniaBłędu,
ERROR_MESSAGE() AS KomunikatBłędu;
END CATCH;
Triggery  procedury wyzwalane
(wyzwalacze)
Tworzenie triggerów
CREATE TRIGGER selall
ON Osoby
FOR INSERT, UPDATE
AS
SELECT * FROM Osoby
CREATE TRIGGER selall1
ON Osoby
FOR DELETE
AS
SELECT Nazwisko FROM Osoby
63
Triggery  procedury wyzwalane
(wyzwalacze)
CREATE TRIGGER up
ON Osoby
FOR INSERT, UPDATE
AS
UPDATE Osoby SET Nazwisko=UPPER(Nazwisko), Imie =UPPER(Imie)
PRINT 'Wykonano'
GO
Wykonuje się dla wszystkich rekordów
bez względu na liczbę modyfikowanych
lub wstawianych rekordów
UPDATE Osoby SET Nazwisko=LOWER(Nazwisko) WHERE IdOsoby >10
UPDATE Osoby Set RokUrodz=0 WHERE RokUrodz IS NULL
INSERT INTO Osoby(Nazwisko) VALUES('Nowy')
Triggery  procedury wyzwalane
(wyzwalacze)
Z zastosowaniem systemowych tabel
tymczasowych
CREATE TRIGGER up
ON Osoby
FOR INSERT, UPDATE
AS
UPDATE Osoby SET Nazwisko=UPPER(Nazwisko), Imie =UPPER(Imie)
WHERE IdOsoby IN (SELECT IdOsoby FROM INSERTED)
PRINT 'Wykonano'
GO
Wykonuje się tylko dla modyfikowanych
lub wstawianych rekordów
UPDATE Osoby SET Nazwisko=LOWER(Nazwisko) WHERE IdOsoby >10
UPDATE Osoby Set RokUrodz=0 WHERE RokUrodz IS NULL
INSERT INTO Osoby(Nazwisko) VALUES('Nowy')
64
Triggery  procedury wyzwalane
(wyzwalacze)
Z zastosowaniem systemowych tabel
tymczasowych
CREATE TRIGGER up
Wykonuje się tylko wtedy gdy
ON Osoby
modyfikowana jest jedna z dwóch kolumn
FOR INSERT, UPDATE
wykonuje się również dla INSERT gdy nie
AS
puste wartości
IF UPDATE(Nazwisko) OR UPDATE(Imie)
BEGIN
UPDATE Osoby SET Nazwisko=UPPER(Nazwisko), Imie =UPPER(Imie)
WHERE IdOsoby IN (SELECT IdOsoby FROM INSERTED)
PRINT 'Wykonano
0x04
PRINT COLUMNS_UPDATED ( )
END
ELSE
PRINT 'Ominięto
PRINT COLUMNS_UPDATED ( )
0x10
GO
UPDATE Osoby SET Nazwisko=LOWER(Nazwisko) WHERE IdOsoby >10
UPDATE Osoby Set RokUrodz=0 WHERE RokUrodz IS NULL
INSERT INTO Osoby(Nazwisko) VALUES('Nowy')
Triggery  procedury wyzwalane
(wyzwalacze)
Z zastosowaniem systemowych tabel
DROP TRIGGER upd tymczasowych
GO
CREATE TRIGGER upd ON Osoby FOR INSERT, UPDATE
AS
DECLARE @max_v tinyint, @act_v tinyint
SELECT @max_v = MAX(o.wiek) FROM Osoby o, INSERTED i
WHERE o.IdOsoby <> i.IdOsoby
SELECT @act_v = i.wiek FROM INSERTED i
PRINT columns_updated()
PRINT @max_v
Nie zezwala na wprowadzenie osoby
PRINT @act_v
starszej niż najstarsza istniejąca w tabeli
if @act_v > @max_v
BEGIN
RAISERROR(50001, 1, 2) WITH SETERROR
RAISERROR ('Wartość powinna być mniejsza równa %d a jest
równa %d.',17,127, @max_v, @act_v)
ROLLBACK TRANSACTION
END
65
Triggery  procedury wyzwalane
(wyzwalacze)
Zamiast
CREATE TRIGGER zamiast
ON Osoby
INSTEAD OF DELETE
Nie zezwala na usuwanie wierszy z tabeli
AS
PRINT 'Zakaz kasowania'
Triggery  procedury wyzwalane
(wyzwalacze)
Zamiast
CREATE TRIGGER zamiast
ON Osoby
INSTEAD OF DELETE
AS
DECLARE @ile int
SELECT @ile=COUNT(IdZarobku) FROM Zarobki
WHERE Idosoby IN
(SELECT IdOsoby FROM DELETED)
Nie zezwala na usuwanie wierszy z tabeli
IF (@ile >0)
dla osób, które mają jakiekolwiek zarobki
PRINT 'Zakaz kasowania'
ELSE
DELETE FROM Osoby WHERE Idosoby IN
(SELECT IdOsoby FROM DELETED)
go
66
Triggery  procedury wyzwalane
(wyzwalacze)
Triggery na bazie danych
DROP TRIGGER Db_Table ON DATABASE
GO
CREATE TRIGGER Db_Table
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
IF IS_MEMBER ('db_owner') = 0
BEGIN
PRINT 'Musisz mieć uprawnienia DBA dla usuwania i
modyfikowania tabel !'
ROLLBACK TRANSACTION
END
Informacje o aktywnym użytkowniku
SELECT CURRENT_USER, USER, SESSION_USER,
SYSTEM_USER, USER_NAME(), HOST_ID(),
HOST_NAME(), APP_NAME()
67
Tworzenie modyfikacja triggera
CREATE TRIGGER schema_name.trigger_name
ON ( table | view )
[ TRIGGER schema_name.trigger_name ] ]
WITH [ ,...n
ALTER
| INSTEAD OF )
ON ( ( FOR | AFTER )
table | view
{ , ]
[ WITH [ DELETE ] [ , ] [ INSERT ] [ ] ] [ UPDATE ] }
[ ,...n
[ | FOR |
NOT REPLICATION
( FOR AFTER INSTEAD OF ]
)
AS { sql_statement [ ; ] UPDATE ] } NAME
| EXTERNAL
{ [ DELETE ] [ , ] [ INSERT ] [ ...n [
] [ , ]

[ NOT FOR REPLICATION ] [ ; ] }
AS { sql_statement [ ; ] [ ...n ] | EXTERNAL NAME
}
::=
[ ; ]
[ ENCRYPTION ]
[ ]
::=
[ ENCRYPTION ]
::=
[ ]
assembly_name.class_name.method_name
::=
assembly_name.class_name.method_name
Tworzenie modyfikacja triggera
CREATE TRIGGER trigger_name
ON { DATABASE | ALL SERVER }
[ WITH [ ,...n ] ]
{ FOR | AFTER } { event_type [ ,...n ] |
event_group }
AS { sql_statement [ ; ] | EXTERNAL NAME specifier>
[ ; ] }
ALTER TRIGGER trigger_name
ON { DATABASE | ALL SERVER }
[ WITH [ ,...n ] ]
{ FOR | AFTER } { event_type [ ,...n ] | event_group }
AS { sql_statement [ ; ] | EXTERNAL NAME specifier>
[ ; ] }
68
Triggery  procedury wyzwalane
(wyzwalacze)
Polecenia wyzwalające triggery na bazie danych
CREATE_FUNCTION ALTER_FUNCTION DROP_FUNCTION
CREATE_INDEX ALTER_INDEX DROP_INDEX
CREATE_PROCEDURE ALTER_PROCEDURE DROP_PROCEDURE
CREATE_ROLE ALTER_ROLE DROP_ROLE
CREATE_SYNONYM DROP_SYNONYM
CREATE_TABLE ALTER_TABLE DROP_TABLE
CREATE_TRIGGER ALTER_TRIGGER DROP_TRIGGER
CREATE_TYPE DROP_TYPE
CREATE_USER ALTER_USER DROP_USER
CREATE_VIEW ALTER_VIEW DROP_VIEW
GRANT_DATABASE DENY_DATABASE REVOKE_DATABASE
Polecenia wyzwalające triggery na bazie danych
cd
CREATE_APPLICATION_ROLE ALTER_APPLICATION_ROLE DROP_APPLICATION_ROLE
CREATE_ASSEMBLY ALTER_ASSEMBLY DROP_ASSEMBLY
ALTER_AUTHORIZATION_DATABASE
CREATE_CERTIFICATE ALTER_CERTIFICATE DROP_CERTIFICATE
CREATE_CONTRACT DROP_CONTRACT
CREATE_EVENT_NOTIFICATION DROP_EVENT_NOTIFICATION
CREATE_MESSAGE_TYPE ALTER_MESSAGE_TYPE DROP_MESSAGE_TYPE
CREATE_PARTITION_FUNCTION ALTER_PARTITION_FUNCTION DROP_PARTITION_FUNCTION
CREATE_PARTITION_SCHEME ALTER_PARTITION_SCHEME DROP_PARTITION_SCHEME
CREATE_QUEUE ALTER_QUEUE DROP_QUEUE
CREATE_REMOTE_SERVICE_BINDING ALTER_REMOTE_SERVICE_BINDING DROP_REMOTE_SERVICE_BINDING
CREATE_ROUTE ALTER_ROUTE DROP_ROUTE
CREATE_SCHEMA ALTER_SCHEMA DROP_SCHEMA
CREATE_SERVICE ALTER_SERVICE DROP_SERVICE
CREATE_STATISTICS DROP_STATISTICS UPDATE_STATISTICS
CREATE_XML_SCHEMA_COLLECTION ALTER_XML_SCHEMA_COLLECTION DROP_XML_SCHEMA_COLLECTION
69
Struktura hierarchiczna zdarzeń DDL
Struktura hierarchiczna zdarzeń DDL cd
70
Polecenia wyzwalające triggery na serwerze
ALTER_AUTHORIZATION_SERVER
CREATE_DATABASE ALTER_DATABASE DROP_DATABASE
CREATE_ENDPOINT DROP_ENDPOINT
CREATE_LOGIN ALTER_LOGIN DROP_LOGIN
GRANT_SERVER DENY_SERVER REVOKE_SERVER
Triggery na serwerze
DROP TRIGGER dla_logowania ON ALL SERVER
GO
CREATE TRIGGER dla_logowania
ON ALL SERVER
FOR LOGON
AS
PRINT 'Nastąpiła zmiana uprawnień'
SELECT
EVENTDATA().value(
'(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
GO
71
Triggery na serwerze
DROP TRIGGER dla_autoryzacji ON ALL SERVER
GO
CREATE TRIGGER dla_autoryzacji
ON ALL SERVER
FOR DDL_LOGIN_EVENTS
AS
PRINT 'Nastąpiła zmiana uprawnień'
SELECT
EVENTDATA().value(
'(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
GO
struktura XML zwracana przez funkcję
EVENT_INSTANCE

type
date-time
spid
name
name
name
name
name
name
type
command

72
Tworzenie triggera dla potrzeb audytu
CREATE TABLE ddl_log (PostTime datetime, DB_User
nvarchar(100), Event nvarchar(100), TSQL nvarchar(2000));
GO
CREATE TRIGGER log
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML
SET @data = EVENTDATA()
INSERT ddl_log
(PostTime, DB_User, Event, TSQL)
VALUES
(GETDATE(),
CONVERT(nvarchar(100), CURRENT_USER),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]',
'nvarchar(2000)') ) ;
GO
Wyłączanie i włączanie triggera
DISABLE TRIGGER { [ schema . ] trigger_name [ ,...n ] | ALL }
ON { object_name | DATABASE | ALL SERVER } [ ; ]
ENABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] |
ALL }
ON { object_name | DATABASE | ALL SERVER } [ ; ]
73
Wyłączanie i włączanie triggera
DISABLE TRIGGER safety ON DATABASE
GO
DISABLE TRIGGER ALL ON ALL SERVER;
GO
ENABLE TRIGGER safety ON DATABASE;
GO
ENABLE Trigger ALL ON ALL SERVER;
GO
Kursory
Tworzenie
DECLARE osoby_c CURSOR FOR SELECT Nazwisko FROM Osoby
OPEN osoby_c
FETCH NEXT FROM osoby_c
CLOSE osoby_c
DEALLOCATE osoby_c
74
Kursory
Tworzenie cd
DECLARE osoby_c CURSOR FOR SELECT Nazwisko FROM Osoby
OPEN osoby_c
FETCH NEXT FROM osoby_c
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM osoby_c
END
CLOSE osoby_c
DEALLOCATE osoby_c
Kursory
Tworzenie cd
DECLARE @nazw varchar(40), @im varchar(20)
DECLARE osoby_c CURSOR FOR SELECT Nazwisko, Imie
FROM Osoby
OPEN osoby_c
FETCH NEXT FROM osoby_c INTO @nazw, @im
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT "Kto to? To " + @im + " " + @nazw
FETCH NEXT FROM osoby_c INTO @nazw, @im
END
CLOSE osoby_c
DEALLOCATE osoby_c
75
Procedura zwracająca kursor
CREATE PROCEDURE cursor_proc
@cur CURSOR VARYING OUTPUT
AS
SET @cur = CURSOR FOR
SELECT nazwisko, imie FROM osoby
OPEN @cur
-- wywołanie
GO
DECLARE @cur CURSOR
EXEC cursor_proc @cur OUTPUT
WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM @cur
PRINT 'miejsce na kod'
END
CLOSE @cur
DEALLOCATE @cur
GO
Tworzenie synonimu
(dla tabeli,perspektywy,procedury,funkcji)
DROP SYNONYM syn
GO
CREATE SYNONYM syn FOR wysocyt
GO
SELECT * FROM syn(1.7)
76
Blok TRY_CATCH bez wywołania procedury 
nie przynosi skutku
BEGIN TRY
-- Tabela nie istnieje błąd nie jest przechwytywany
SELECT * FROM NonexistentTable;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage;
END CATCH
Skutek
Msg 208, Level 16, State 1, Line 4
Invalid object name 'NonexistentTable'.
Procedura i jej wywołanie z TRY_CATCH
DROP PROCEDURE usp_ExampleProc;
GO
CREATE PROCEDURE usp_ExampleProc
AS
SELECT * FROM NonexistentTable;
BEGIN TRY
GO
EXECUTE usp_ExampleProc
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() as ErrorNumber,
Skutek
ERROR_MESSAGE() as
ErrorNumber ErrorMessage ErrorMessage;
----------- ------------------------------------------
END CATCH;
208 Invalid object name 'NonexistentTable'.
(1 row(s) affected)
77
Procedura i jej wywołanie z TRY_CATCH
DROP PROCEDURE usp_ExampleProc;
GO
CREATE PROCEDURE usp_ExampleProc
AS
SELECT * FROM NonexistentTable;
GO
Wywołanie proc bez try_catch kończy się błędem
EXEC usp_ExampleProc;
Skutek
Msg 208, Level 16, State 1, Procedure usp_ExampleProc,
Line 3
Invalid object name 'NonexistentTable'.
TRY_CATCH przykład dla dzielenia przez zero
BEGIN TRY
SELECT 1/0;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
Skutek
GO
-----------
(0 row(s) affected)
ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage
----------- ------------- ----------- --------------- ----------- ------------------------
---------
8134 16 1 NULL 3 Divide by zero error
encountered.
(1 row(s) affected)
78
próba usunięcia powiązanych rekordów klucz obcy , w obrębie transakcji
z zaastosowaniem Try_catch
BEGIN TRANSACTION;
BEGIN TRY
-- Osoba o idOSOBY =2 ma zarobki
delete from osoby WHERE IDosoby=2;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
IF @@TRANCOUNT > 0
Skutek
ROLLBACK TRANSACTION;
ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage
----------- ------------- ----------- ---------------- ----------- -------------------------
END CATCH;
-----
547 16 0 NULL 5 The DELETE statement
IF @@TRANCOUNT > 0
conflicted with the REFERENCE constraint "FK_Zarobki_Osoby". The conflict occurred in databa
"ap", table "dbo.Zarobki", column 'IdOsoby'.
COMMIT TRANSACTION;
(1 row(s) affected)
Próba usunięcia niejstniejącej kolumny kod z tabeli Dzialy
BEGIN TRY
BEGIN TRANSACTION;
BEGIN TRY
-- Generowany jest bład z powodu nieistnienia usuwanej kolumny
ALTER TABLE dzialy DROP COLUMN kod;
-- Jeśli polecenie powiedzie się zatwierdz
transakcje.
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage;
-- Test XACT_STATE for 1 or -1.
-- XACT_STATE = 0 nie jest otwarta żadna transakcja
-- COMMIT lub ROLLBACK będą generowały błąd.
-- sprawdz
czy transakcja jest niecommitowalna (is uncommittable).
IF (XACT_STATE()) = -1
79
Próba usunięcia niejstniejącej kolumny kod z tabeli Dzialy cd
BEGIN
PRINT N'Transakcja jest w stanie uncommittabl. ' +
'Wycofywanie transakcji.'
ROLLBACK TRANSACTION;
END;
-- sprawdz
czy transakcja jest aktywna i w stanie valid (dostępna).
IF (XACT_STATE()) = 1
BEGIN
PRINT N' Transakcja jest w stanie committable. ' +
'Zatwierdzenie transakcji.'
COMMIT TRANSACTION;
END;
END CATCH;
GO
Skutek
ErrorNumber ErrorMessage
----------- ----------------------------------------------------------------------------------------
4924 ALTER TABLE DROP COLUMN failed because column 'kod' does not exist in table 'dzialy'.
(1 row(s) affected)
Transakcja jest w stanie uncommittabl. Wycofywanie transakcji.
Indeksy
Tworzenie
DROP INDEX osoby.Ix_imie
GO
CREATE CLUSTERED INDEX Ix_name ON Osoby (nazwisko)
DROP INDEX osoby.Ix_name
GO
CREATE CLUSTERED INDEX Ix_imie ON Osoby (imie)
DROP INDEX osoby.Ix_imie
GO
CREATE CLUSTERED INDEX Ix_imienazwisko ON
Osoby imie, nazwisko)
80
Wszystko z WORDA
Wyszukiwanie pełnotekstowe
SELECT opis FROM Osoby
WHERE CONTAINS(Opis,'Glupi')
GO
SELECT opis FROM Osoby
WHERE CONTAINS(Opis,'Du*')
GO
SELECT opis FROM Osoby
WHERE CONTAINS(Opis,' "Du*" ')
GO
SELECT opis FROM Osoby
WHERE CONTAINS(Opis,'Duzy Near Glupi')
DO poprawy
Ograniczenia, rules, default etc
Wprowadzenie do tabeli dzialy nowej kolumny opartej o typ
użytkownika
ALTER TABLE dzialy ADD
kod litera
Tworzenie reguły
Create rule IsNotZero
as
@value <> 0
CREATE RULE lit
AS
81
Użytkownicy, role etc..
Tworzenie  logina
EXEC sp_addlogin 'Adam', 'ewa', 'sr8'
Tworzenie użytkownika
EXEC sp_adduser 'Adam', 'Ktos', 'public'
Sprawdzanie użytkowników na serverze
sp_helplogins
sp_helprole
sp_helpsrvrole
sp_helpsrvrolemember 'sysadmin'
Tworzenie użytkownika
nowa postać
USE ap;
DROP USER ktos;
DROP LOGIN ktos;
GO
CREATE LOGIN ktos
WITH PASSWORD = 'kajak';
USE ap;
CREATE USER ktos;
GRANT SELECT ON Osoby to Ktos;
GO
82
Tworzenie logina
różne warianty
CREATE LOGIN [LR\apelikan] FROM WINDOWS;
GO
Tworzenie LOGINA
CREATE LOGIN login_name { WITH | FROM }
::=
WINDOWS [ WITH [ ,... ] ]
| CERTIFICATE certname
| ASYMMETRIC KEY asym_key_name
::=
PASSWORD ='password' [ HASHED ] [ MUST_CHANGE ]
[ , [ ,... ] ]
::=
SID = sid
| DEFAULT_DATABASE = database
| DEFAULT_LANGUAGE = language
| CHECK_EXPIRATION = { ON | OFF}
| CHECK_POLICY = { ON | OFF}
[ CREDENTIAL = credential_name ]
::=
DEFAULT_DATABASE = database
| DEFAULT_LANGUAGE = language
83
Tworzenie LOGINA
CREATE LOGIN test WITH PASSWORD = 'abcd' MUST_CHANGE;
GO
Msg 15195, Level 16, State 1, Line 1
The MUST_CHANGE option is not supported by this version of Microsoft Windows.
CREATE LOGIN [LR\apelikan] FROM WINDOWS;
GO
USE ap;
CREATE user [LR\apelikan]
CREATE CREDENTIAL AlterEgo WITH IDENTITY = 'kto',
SECRET = 'abcd';
GO
CREATE LOGIN ktos WITH PASSWORD = 'xzy', CREDENTIAL = AlterEgo;
GO
USE MASTER;
CREATE CERTIFICATE KtosCert
ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
WITH SUBJECT = 'Opis certyfikatu',
EXPIRY_DATE = '02/02/2009';
GO
CREATE LOGIN KtosC FROM CERTIFICATE KtosCert;
GO
Tworzenie LOGINA
ALTER LOGIN ktos DISABLE;
GO
ALTER LOGIN ktos ENABLE;
ALTER LOGIN ktos WITH PASSWORD = 'qwerty';
ALTER LOGIN ktos WITH NAME = KtosL;
CREATE CREDENTIAL AlterEgo1 WITH IDENTITY = 'inny',
SECRET = 'abcd';
GO
ALTER LOGIN ktosC WITH CREDENTIAL = AlterEgo1;
DROP LOGIN ktosC;
84
Dynamiczne tworzenie LOGINA
DROP TABLE osoby
GO
CREATE TABLE osoby
(id int IDENTITY(1,1) PRIMARY KEY,
kto varchar(50)
);
GO
DROP TRIGGER dd;
GO
CREATE TRIGGER dd
ON osoby
AFTER INSERT
AS
BEGIN
DECLARE @zm varchar(222);
DECLARE @kk varchar(222);
SELECT @kk=KTO FROM inserted;
SET @zm ='use master; CREATE login '+ @kk +' with PASSWORD = '+''''+'kajak' +'''';
PRINT @zm;
EXEC (@zm);
END;
GO
USE ap;
INSERT INTO osoby VALUES ('nowy');
Tworzenie użytkownika
CREATE USER user_name
[ { { FOR | FROM }
{
LOGIN login_name
| CERTIFICATE cert_name
| ASYMMETRIC KEY asym_key_name
}
| WITHOUT LOGIN
]
[ WITH DEFAULT_SCHEMA = schema_name ]
85
Tworzenie użytkownika
CREATE USER ktos;
GRANT CONNECT TO ktos;
CREATE USER KtosU FOR LOGIN ktos
WITH DEFAULT_SCHEMA = ap;
CREATE USER UserC FOR CERTIFICATE KtosCert;
ALTER USER ktosU WITH NAME = nowy;
GO
ALTER USER nowy WITH DEFAULT_SCHEMA = hurtownia;
DROP USER nowy;
Tworzenie roli
CREATE ROLE rola
GO
GRANT SELECT ON Osoby TO rola
GO
REVOKE SELECT ON Osoby TO rola
GO
DROP ROLE rola1
GO
CREATE ROLE rola1 AUTHORIZATION rola;
GO
ALTER ROLE rola WITH NAME = nowa_rola
86
DROP TABLE mylogintable Informacje o połączonym
go użytkowniku
CREATE TABLE mylogintable
(id int,
klucz uniqueidentifier default newid(),
user_id int default user_id(),
date_in datetime default getdate(),
myuser_name AS USER_NAME() ,
kto as user,
os_user as system_user,
c_user as current_user,
s_user as session_user,
gdzie as host_name(),
gdzie_nr as host_id(),
aplikacja as APP_NAME()
) ;
go
insert into mylogintable(id) values(1)
select * from mylogintable;
1 7173254F-7AED-46F5-9126-C5E9CB97D6F5 1 2007-05-19 18:21:40.810
dbo dbo PC214-31\Administrator dbo dbo PC214-31 320
Microsoft SQL Server Management Studio - Query
Użytkownicy, role etc..
Przypisywanie praw użytkownikowi
GRANT SELECT, insert
ON osoby TO Ktos
Sprawdzenie elementów bazy
select* from sysobjects where status>=32
select * from information_schema.tables
select * from information_schema.views
select * from information_schema.columns
sp_help systypes
87
Informacje o loginach i uprawnieniach
SELECT * FROM SYS.SQL_LOGINS
SELECT * FROM SYS.DATABASE_ROLE_MEMBERS
SELECT * FROM SYS.DATABASE_PERMISSIONS
SELECT * FROM SYS.USER_TOKEN
SELECT * FROM SYS.DATABASE_PRINCIPALS
Podszycie się pod użytkownika
USE ap;
GO
--Create two temporary principals
CREATE LOGIN login1 WITH PASSWORD = 'J345#$)thb';
CREATE LOGIN login2 WITH PASSWORD = 'Uor80$23b';
GO
CREATE USER user1 FOR LOGIN login1;
CREATE USER user2 FOR LOGIN login2;
GO
--Give IMPERSONATE permissions on user2 to user1
--so that user1 can successfully set the execution context to user2.
GRANT IMPERSONATE ON USER:: user2 TO user1;
GO
--Display current execution context.
SELECT SUSER_NAME(), USER_NAME();
-- Set the execution context to login1.
EXECUTE AS LOGIN = 'login1';
--Verify the execution context is now login1.
SELECT SUSER_NAME(), USER_NAME();
--Login1 sets the execution context to login2.
EXECUTE AS USER = 'user2';
--Display current execution context.
SELECT SUSER NAME() USER NAME()
88
DO poprawy
Błędy, elementy bazy
Wykaz elementów bazy
SELECT o.id,o.type, c.text
FROM sysobjects o INNER JOIN syscomments c
ON o.id = c.id
SELECT o.id, c.text
FROM sysobjects o INNER JOIN syscomments c
ON o.id = c.id
WHERE o.type = 'TR'
/*and o.name = 'trig2'*/
DO poprawy
Zadanie magazynowe z wykorzystaniem kursorów
drop procedure sprawdz
go
create procedure sprawdz
@towar int ,@sztu int
as
declare @co int,@ile int
declare @nco varchar(3),@nile varchar(3)
set nocount on
DECLARE sprzed CURSOR FOR SELECT idtowaru,szt
from magazyn
OPEN sprzed
FETCH next FROM sprzed into @co, @ile
89
Dynamiczny SQL
Wykonanie polecenia w każdej z baz serwera.
declare @zm varchar(55)
declare cur cursor for select name from sys.databases
declare @sql varchar(255)
open cur
fetch next from cur into @zm
while @@fetch_status=0
Begin
set @sql ='use ' + @zm + '; create table aa(a int);'
execute(@sql);
print @sql;
fetch next from cur into @zm
end
close cur
deallocate cur
Tworzenie bazy danych
CREATE DATABASE nowa;
DROP DATABASE nowa;
90
Tworzenie bazy danych
USE master;
GO
IF DB_ID (N'Nowa') IS NOT NULL
DROP DATABASE Nowa;
GO
-- Pobierz ścieżkę do danych SQL Server
DECLARE @sciezka nvarchar(256);
SET @sciezka = (SELECT SUBSTRING(physical_name, 1,
CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM master.sys.master_files WHERE database_id = 1 AND file_id = 1);
-- Utwórz bazę danych
EXECUTE ('CREATE DATABASE nowa
ON
( NAME = Nowa_dat, FILENAME = '''+ @sciezka + 'nowa.mdf'',
SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 )
LOG ON
( NAME = Nowa_log, FILENAME = '''+ @sciezka + 'nowa.ldf'',
SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB )'
);
GO
Odłączenie bazy danych i utworzenie z
istniejącego pliku
USE master;
GO
sp_detach_db nowa;
GO
DECLARE @sciezka nvarchar(256);
SET @sciezka = (SELECT SUBSTRING(physical_name, 1,
CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM master.sys.master_files
WHERE database_id = 1 AND file_id = 1);
EXEC ('CREATE DATABASE nowa
ON (FILENAME = '''+ @sciezka + 'nowa.mdf'')
FOR ATTACH');
GO
91
Zastosowanie sqlcmd do wykonania skryptu oraz
xp_cmdshell do wykonania polecenia systemu
EXEC xp_cmdshell 'sqlcmd -i C:\pol.sql -o C:\skut.txt'
xp_cmdshell wymaga odblokowania
Backup typu pełnego
-- Tworzenie logicznego urządzenia dla backupu bazy Nowa.
USE master
GO
EXEC sp_addumpdevice 'disk', 'Nowa',
'C:\temp\backup\nowa.bak'
-- Full Back up bazy nowa .
BACKUP DATABASE nowa
TO nowa
92
Backup typu pełnego z plikem loga
-- Aby zezwolić na backup pliku loga przed pełnym backupem
-- zmień bazę danych aby używała pełnego modelu odzyskiwania
USE master;
GO
ALTER DATABASE Nowa SET RECOVERY FULL;
GO
-- Tworzenie logicznego urządzenia dla backupu pliku loga bazy Nowa.
EXEC sp_addumpdevice 'disk', 'Nowalog',
'C:\temp\backup\nowalog.bak';
GO
-- Full Backup bazy nowa .
BACKUP DATABASE nowa TO nowa;
-- Full Backup loga bazy nowa .
BACKUP log nowa TO nowalog;
Backup typu różnicowego
BACKUP DATABASE nowa
TO nowa WITH DIFFERENTIAL
93
Odzyskiwanie bazy danych
RESTORE DATABASE nowa
FROM nowa;
-- This database RESTORE halted prematurely due to power
failure.
RESTORE DATABASE nowa
FROM nowa
-- Here is the RESTORE RESTART operation.
RESTORE DATABASE nowa
FROM nowa WITH RESTART
Odzyskiwanie bazy danych
BACKUP DATABASE nowa
TO DISK = 'C:\temp\backup\nowa.bak'
-- wyświetlenie informacji o pliku backup a
RESTORE FILELISTONLY
FROM DISK = 'C:\temp\backup\nowa.bak'
--Odzyskanie danych i skopiowanie plików do nowej lokacji
RESTORE DATABASE TestDB
FROM DISK = 'C:\temp\backup\nowa.bak'
WITH MOVE 'nowa_Dat' TO 'C:\temp\backup\nowa.mdf',
MOVE 'nowa_Log' TO 'C:\temp\backup\nowa.ldf'
GO
94
Odzyskiwanie bazy danych
USE nowa
GO
BEGIN TRANSACTION zmiana1 WITH MARK 'ZmienNazwiska'
GO
UPDATE Osoby SET Nazwisko=Upper(nazwisko)
GO
COMMIT TRANSACTION zmiana1
GO
-- Time passes. Regular database and log backups are taken. An error
occurs.
USE master
GO
RESTORE DATABASE Nowa FROM nowa
WITH FILE = 1, NORECOVERY
GO
RESTORE LOG nowa FROM nowa
WITH FILE = 1, STOPATMARK = 'ZmienNazwiska'
Odzyskiwanie bazy danych
-- To permit log backups, before the full database backup, modify the database to use the
full recovery model.
USE master;
GO
ALTER DATABASE test SET RECOVERY FULL;
GO
-- Create test and test logical backup devices.
USE master
go
EXEC sp_dropdevice 'test';
go
EXEC sp_dropdevice 'testlog';
GO
EXEC sp_addumpdevice 'disk', 'test', 'c:\backup\test.bak';
GO
EXEC sp_addumpdevice 'disk', 'testLog', 'c:\backup\test.bakLog.bak';
GO
-- Back up the full test database.
BACKUP DATABASE test TO test;
GO
-- Back up the test log.
BACKUP LOG test TO test;
GO
use test;
95
Szyfrowanie  nie wyświetla???
DROP SYMMETRIC KEY klucz
GO
DROP CERTIFICATE certyfikat
GO
CREATE CERTIFICATE certyfikat
ENCRYPTION BY PASSWORD = 'pass'
WITH SUBJECT = 'Testowy certyfikat' ,
START_DATE = '10/31/2005';
GO
CREATE SYMMETRIC KEY klucz WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE certyfikat;
GO
-- Otwarcie klucza
OPEN SYMMETRIC KEY klucz
DECRYPTION BY CERTIFICATE certyfikat WITH PASSWORD = 'pass' ;
GO
-- Szyfrowanie i odszyfrowanie napisu
DECLARE @decrypt varchar(20), @encrypt varchar(20)
SET @decrypt=DecryptByKey('tekst')
SET @encrypt=EncryptByKey(Key_GUID('klucz'), @decrypt)
SELECT @decrypt, @encrypt
GO
96