Artykuł
12/20/2011

Język SQL – czym jest SQL Server, podstawowa składnia języka T-SQL

Autor: Paweł Wilkosz

Opublikowano: 2011-04-29

Wprowadzenie

Z poprzedniego artykułu Czytelnik dowiedział się, w jaki sposób projektować bazę danych, aby mogła być prawidłowo rozpoznawana i obsługiwana przez system zarządzania bazą danych. W kolejnej części zaprezentuję sposoby praktycznego tworzenia struktur bazodanowych w środowisku SQL Server przy wykorzystaniu języka Transact-SQL (T-SQL). Czytelnik zapozna się również z różnymi typami ekstrakcji i kontroli informacji przy wykorzystaniu Data Manipulation Language (DML), Data Control Language (DCL), Data Definition Language (DDL) oraz Transactional Control Language (TCL).

System zarządzania bazą danych i Microsoft SQL Server

Stara maksyma głosi, iż „Kto ma informacje, ten ma władzę”. Niestety, posiadając zbiór nawet kilkudziesięciu terabajtów danych nie bylibyśmy w stanie wydobyć z niego interesujących nas wiadomości, gdyby nie było mechanizmów pozwalających na ich pogrupowanie, indeksowanie (np. według alfabetu – jak w książce telefonicznej), przeszukiwanie, zabezpieczanie przed utratą, wyodrębnianie interesujących fragmentów itp. Przytoczone rzeczowniki odczasownikowe charakteryzują owe mechanizmy w kategoriach zarządzania. W związku z tym każdy system, który będzie w stanie opanować duże zbiory informacji, jak również odpowiednio je grupować, zabezpieczać, przeszukiwać i wykonywać podstawowe operacje, takie jak dodawanie bądź usuwanie elementu z jego zbioru, będzie nosił nazwę system zarządzania bazą danych.

Microsoft SQL Server jest przykładem systemu zarządzania bazą danych, promowanego przez firmę Microsoft. Oprócz podstawowych operacji związanych z manipulacją danymi, zakres usług, jaki oferuje SQL Server, rozszerzony jest o tworzenie kwerend, synchronizowanie, raportowanie, analizowanie oraz zwiększenie bezpieczeństwa przechowywania wiadomości. Więcej informacji nt. SQL Server można znaleźć na stronie Microsoft TechNet, dostępnej pod adresem internetowym: https://www.microsoft.com/poland/technet/prodtechnol/sqlserver/default.mspx.

Strukturalny język zapytań SQL

Język SQL znacząco różni się od innych języków programowania, takich jak chociażby C# czy Java. SQL należy do klasy języków deklaratywnych, co oznacza, iż jego podstawowe operacje oparte są na rachunku relacyjnym. W przeciwieństwie do języków obiektowych czy proceduralnych, SQL nie służy do tworzenia klas, obiektów czy procedur, które będą modyfikowały bądź zwracały informacje jako oczekiwany wynik. Jego podstawowym celem jest formułowanie zbioru danych, który programista będzie chciał otrzymać jako rezultat wykonania zapytania. W 1986 r. język SQL stał się oficjalnym standardem Międzynarodowej Organizacji Normalizacyjnej (ISO) oraz Amerykańskiego Narodowego Instytutu Normalizacji (ANSI). W związku z tym raz napisane zapytanie powinno wykonać się bez problemów na różnych systemach zarządzania bazami danych, tj. SQL Server czy Oracle. Firma Microsoft rozszerzyła możliwości języka SQL w zakresie tworzenia pętli warunkowych, zmiennych, wyzwalaczy i procedur, tworząc (na bazie języka SQL firmy Sybase) język Transact-SQL (T-SQL), stanowiący podstawowe narzędzie pracy na danych składowanych w zasobach serwera SQL Server.

Szereg instrukcji (poleceń) języka T-SQL grupuje się w tzw. zapytania, które po wykonaniu zwracają dane. Zapytanie składa się z następujących elementów:

słowa kluczowe (instrukcje, klauzule T-SQL, nazwy funkcji systemowych);
identyfikator (nazwy tabel, pól, obiektów bazodanowych);
informacje stałe (liczby, ciągi znakowe interpretowane dosłownie);
dyrektywa wsadowa (GO, EXEC);
znaki specjalne (#, @, nawiasy itp.);
komentarz.

Z technicznego punktu widzenia T-SQL składa się z kilku podjęzyków, które wykorzystywane są wtedy, gdy używa się odpowiednich wyrażeń. Podzbiór języka T-SQL to:

Data Manipulation Language (DML) – przekazywanie zapytań do serwera SQL, który formułuje odpowiedź, np. zapytanie:
```
SELECT AddressLine1, City
FROM Person.Address;
```
Data Control Language (DCL) – zarządzanie zabezpieczeniami bazy danych, np. nadanie uprawnień typu CONTROL SERVER dla loginu DatabaseUserTmp:
```
GRANT CONTROL SERVER TO DatabaseUserTmp;
```
Data Definition Language(DDL) – tworzenie i modyfikowanie struktury obiektów w bazie danych, np. tworzenie bazy danych MyFirstDatabase:
```
CREATE DATABASE MyFirstDatabase;
```

Transactional Control Language(TCL) – zarządzanie różnymi typami transakcji, np. zatwierdzenie realizowanej transakcji:

BEGIN TRANSACTION
USE AdventureWorks
GO
UPDATE Person.Address
SET AddressLine2 = ‘1970 Napa Ct.’
WHERE AddressId = 1
GO
COMMIT
GO

Przykładowe instrukcje oraz odpowiednie „podjęzyki” T-SQL odpowiedzialne za wykonanie zapytania zostały zaprezentowane w tabeli 1, oraz szczegółowo wyjaśnione w dalszej części artykułu.

DML	SELECT, INSERT, UPDATE, DELETE
DDL	CREATE, ALTER, DROP
DCL	GRANT, DENY, REVOKE
TCL	COMMIT, ROLLBACK

Tab. 1. Instrukcje języka T-SQL wykonywane przez poszczególne jego podzbiory.

Data Definition Language – CREATE

Komenda CREATE w języku T-SQL służy głównie do tworzenia obiektów bazodanowych, takich jak: bazy danych, tabele, procedury składowane, widoki itd. Dla potrzeb niniejszej prezentacji zostanie utworzona baza danych oraz, w jej ramach jedna tabela.

Pełna składnia polecenia CREATE DATABASE jest dość obszerna, dlatego na wstępie wykorzystamy jej skróconą postać, gdzie większość parametrów (tj. rozmiar bazy danych, przyrost, grupy plików itp.) będzie ustawiana na wartości domyślne. Dla dociekliwych polecam stronę: https://msdn.microsoft.com/en-us/library/ms176061.aspx, zawierającą pełną składnię syntaktyczną zapytania.

Otwierając SQL Server Management Studio i wpisując komendę:

CREATE DATABASE TestDatabase;

oraz wykonując ją (wciskając F5 bądź przycisk EXECUTE), użytkownik powinien otrzymać nowo utworzoną bazę danych, dostępną z poziomu Object Explorer (rysunek 1).

Rys. 1. Tworzenie bazy danych za pomocą komendy CREATE DATABASE.

Do nowo utworzonej bazy danych zdefiniujemy tabelę Użytkownicy. Na początku jednak należy zmienić kontekst wykonywania zapytań. Jak zapewne Czytelnik zauważył, bazując chociażby na rysunku 1, w obrębie jednego serwera bazodanowego może istnieć wiele baz danych. Należy zatem określić, dla jakiej bazy (bądź jej migawki) mają być wykonywane polecenia (trudno sobie wyobrazić użyteczność zapytań, które dodawałyby bądź usuwały rekordy ze wszystkich dostępnych obiektów bazodanowych). Do zmiany kontekstu posłużymy się dyrektywą USE. Wykonując polecenie:

USE TestDatabase
GO;

oraz upewniając się, że kontekst został zmieniony (patrz rysunek 2), można przystąpić to utworzenia tabeli w obrębie bazy TestDatabase:

Rys. 2. Zmiana kontekstu bazy danych za pomocą dyrektywy USE.

Podobnie jak polecenie CREATE DATABASE, składnia CREATE TABLE jest nie mniej skomplikowana. Aby wyjaśnić wszystkie możliwe opcje, trzeba by odbiec nieco od tematu i wprowadzić Czytelnika w bardziej zaawansowane opcje tworzenia tabel w SQL Server. W kolejnych częściach cyklu szkoleniowego na pewno jeszcze nie raz powrócę do tematu tychże parametrów, a tymczasem dla niecierpliwych polecam publikację dostępną pod adresem: https://msdn.microsoft.com/en-us/library/ms174979.aspx.

W niniejszym artykule zostanie wykorzystana najprostsza forma tworzenia tabeli w obrębie bazy danych. Przyjrzyjmy się na początku poniższemu zapytaniu:

CREATE TABLE Users
(
    UserID INT NOT NULL PRIMARY KEY,
    FirstNameVARCHAR(50),
    SecondNameVARCHAR(50),
    DateOfBirth DATE
);

Wykonując polecenie, należy określić nazwę tabeli oraz pola, jakie będzie w sobie agregować. Dodatkowo wymagane jest określenie typu danych kolumn oraz wybór klucza głównego, który nie powinien przyjmować wartości NULL (polecam Czytelnikowi przypomnieć sobie treść artykułu z zakresu normalizacji bazy danych).

Po wykonaniu polecenia tabela Users będzie dostępna z poziomu Object Explorera (rysunek 3).

Rys. 3. Tabela Users z poziomu Object Explorer.

Data ManipulationLanguage – INSERT, SELECT, UPDATE, DELETE

INSERT INTO

W poprzednich krokach Czytelnik stworzył bazę danych TestDatabase oraz tabelę Users. Na wstępie tabela nie jest wypełniona żadnymi danymi, zanim więc omówiona zostanie najpopularniejsza komenda języka T-SQL – SELECT, dodamy kilka rekordów do tabeli Users.

Aby wprowadzić dane do poszczególnych kolumn bazy danych, należy posłużyć się poleceniem INSERT. Standardowa składnia zapytania prezentuje się następująco:

[ WITH <common_table_expression> [ ,...n ] ]
INSERT 
{
        [ TOP ( expression ) [ PERCENT ] ] 
        [ INTO ] 
        { <object> | rowset_function_limited
          [ WITH ( <Table_Hint_Limited> [ ...n ] ) ] }
    {
        [ (column_list ) ] 
        [ <OUTPUT Clause> ]
        { VALUES ( { DEFAULT | NULL | expression } [ ,...n ] ) [ ,...n ] 
          | derived_table
          | execute_statement
          | <dml_table_source>
          | DEFAULT VALUES 
        }
    }
}
[; ]

Ogólnie rzecz ujmując, należy zdefiniować następujące obszary:

gdzie dane mają być wstawione (tabela, widok)?
do jakich kolumn mają trafić informacje?
jakie wartości mają być wstawione do kolumn?

Wstawiając dane do tabeli Users, posłużymy się następującym zapytaniem:

INSERT INTO Users(UserID, FirstName, SecondName, DateOfBirth)
VALUES (1, ‘Jan’, ‘Kowalski’, ‘1986-12-01’);

Bezwzględnie należy pamiętać o tym, aby typy danych, jakie chcemy wstawić, zgadzały się ze zdefiniowanym typem dla kolumny. W przeciwnym wypadku SQL Server wygeneruje błąd.

SELECT

Naturalnym odruchem jest chęć sprawdzenia, czy informacje, które zostały wstawione do odpowiedniej tabeli, faktycznie się tam znalazły. Do wyświetlania zawartości danych z tabel służy polecenie SELECT. Na początku wykonajmy poniższe zapytanie:

SELECT UserId, FirstName, SecondName, DateOfBirth
FROM Users

Jako wynik Czytelnik powinien otrzymać zbiór informacji, jak na rysunku 4.

Rys. 4. Polecenie SELECT – wyświetlenie zawartości tabeli Users.

W powyższym zapytaniu podawaliśmy nazwy kolumn, które chcieliśmy wyświetlić. Oczywiście zapytanie można uprościć i zamiast listingu kolumn użyć gwiazdki ‘*’ (co oznacza wszystkie kolumny z danej tabeli). Jednakże tego typu forma może w przyszłości powodować poważne problemy z wydajnością czy mapowaniem relacyjno-obiektowym dla frameworków, np. ASP .NET (NHibernate, Data Entity Framework), należy zatem używać jej tak rzadko, jak tylko jest to możliwe. Druga forma zapisu zapytania została przedstawiona poniżej:

SELECT *
FROM Users

Na chwilę chciałbym zatrzymać się na teorii relacji. Polecenie SELECT niezwykle jasno odzwierciedla koncepcję Codda w zakresie relacyjnego ujęcia baz danych. Odnosząc się do algebry relacji, poszczególne konstrukcje polecenia SELECT można skategoryzować wg schematu znajdującego się w tabeli 3.

Wypisanie elementów tabeli	SELECT * FROM Table
Suma mnogościowa z wyłączeniem duplikatów	SELECT col1, col2 FROM Table1 UNION SELECT col1, col2 FROM Table2
Suma mnogościowa wraz z duplikatami	SELECT col1, col2 FROM Table1 UNION ALL SELECT col1, col2 FROM Table2
Różnica mnogościowa	SELECT col1, col2 FROM Table1 EXCEPT SELECT col1, col2 FROM Table2
Przekrój mnogościowy (część wspólna)	SELECT col1, col2 FROM Table1 INTERSECT SELECT col1, col2 FROM Table2
Iloczyn kartezjański	SELECT * FROM Table1 CROSS JOIN Table2
Projekcja	SELECT DISTINCT col1 FROM Table1

Tab. 3. Algebra relacji w odniesieniu do instrukcji SELECT.

Opis klauzul, które czytelnik bezwzględnie powinien zapamiętać dla polecenia SELECT, został zestawiony w tabeli 2.

SELECT

Wylistowanie danych

 FROM

 Zwraca listę tabel, na których operować będzie polecenie SELECT

 WHERE

 Określenie warunku logicznego dla listingu pól

 ORDER BY

 Sposób posortowania wyników

 GROUP BY

 Funkcja agregująca pola podsumowywane w klauzuli SELECT

 HAVING

 Dla funkcji agregujących określa warunki pól podsumowywanych w klauzuli SELECT

Tab. 2. Najpopularniejsze klauzule języka T-SQL.

UPDATE

T-SQL umożliwia również wprowadzanie zmian do istniejących rekordów. Można tego dokonać za pomocą polecenia UPDATE. Wykonując poniższe zapytanie, należy mieć na uwadze następujące czynniki:

Jaki obiekt bazodanowy będziemy modyfikować?
Jakie i gdzie nowe wartości zostaną wstawione?
Które jego części będą podlegały modyfikacji?

Na poniższym zapytaniu nazwisko użytkownika o numerze identyfikacyjnym 1 zostało zmienione na Nowak.

UPDATE Users
SET SecondName = ‘Nowak’
WHERE UserId = 1

Pełny opis składni syntaktycznej zapytania można znaleźć pod adresem: https://msdn.microsoft.com/en-us/library/ms177523.aspx.

DELETE

Ostatnim przedstawicielem języka DML, omawianym w niniejszym artylule, jest klauzula DELETE. Jak nietrudno się domyślić, posłuży ona programiście baz danych do usuwania rekordów. Z pewnych przyczyn – dla zachowania spójności danych oraz gromadzenia informacji historycznych do późniejszych analiz – nadużywanie tego polecenia nie jest wskazane. Jako przykład wykorzystania DELETE posłużymy się operacją usunięcia rekordu o numerze identyfikacyjnym 1 z tabeli Users.

W podstawowej składni polecenia DELETE należy określić, z jakiego obiektu bazodanowego będziemy usuwali dane oraz które rekordy będą podlegały destrukcji. Przykład zapytania został dołączony poniżej:

DELETE FROM Users

WHERE UserId = 1

Data Control Language – GRANT, DENY, REVOKE

W kolejnej części niniejszej publikacji chciałbym przedstawić Czytelnikowi trzy podstawowe komendy języka DCL – GRANT, DENY praz REVOKE

GRANT

Polecenie GRANT służy do nadawania uprawnień dostępu do obiektów bazodanowych użytkownikom wykorzystującym specyficzny login bądź należącym do zdefiniowanej grupy. Składnia komendy GRANT została przedstawiona na poniższym listingu:

    GRANT { ALL [ PRIVILEGES ] } 
    | permission [ (column [ ,...n ] ) ] [ ,...n ] 
    [ ON [ class:: ] securable ] TO principal [ ,...n ]  
    [ WITH GRANT OPTION ] [ AS principal ]

W praktycznym ujęciu do naszej tabeli Users dla użytkownika test dodamy uprawnienia na wykonywanie zapytań SELECT. Formuła zapytania:

GRANT SELECT ON Users TO test;

DENY

W przeciwieństwie do GRAND, komenda DENY służy głównie do odbierania uprawnień dostępu do obiektów bazodanowych. Jej podstawowa składnia wygląda następująco:

    DENY { ALL [ PRIVILEGES ] } 
        | permission [ (column [ ,...n ] ) ] [ ,...n ] 
    [ ON [ class:: ] securable ] TO principal [ ,...n ]  
    [ CASCADE] [ AS principal ]

Załóżmy, że użytkownik test nadużył zaufania administratora bazy danych, próbując wyciągać z tabeli Users dane poufne. Istnieje możliwość cofnięcia mu uprawnień wykonywania zapytań z SELECT za pomocą poniższej komendy:

 DENY SELECT ON OBJECT::Users TO test;

REVOKE

Za pomocą polecenia REVOKE programista bądź administrator bazy danych posiada możliwość usunięcia wcześniej przyznanych bądź odebranych uprawnień. Składnia polecenia została przedstawiona na poniższym listingu:

    REVOKE [ GRANT OPTION FOR ]
    { 
    [ ALL [ PRIVILEGES ] ]
            |
                    permission [ (column [ ,...n ] ) ] [ ,...n ]
          }
    [ ON [ class:: ] securable ] 
    { TO | FROM } principal [ ,...n ] 
    [ CASCADE] [ AS principal ]

Cofając odebrane prawo na wykonywanie poleceń SELECT dla użytkownika test, można posłużyć się następującym zapytaniem:

REVOKE SELECT ON OBJECT::Users FROM test;

Transactional Control Language – COMMIT, ROLLBACK

W ostatnim punkcie niniejszej publikacji chciałbym przedstawić dwa polecenia języka TCL: COMMIT oraz ROLLBACK, wykorzystywane głównie podczas pracy z transakcjami.

Transakcja bazodanowa stanowi zbiór elementarnych operacji na bazie danych (np. SELECT, UPDATE, INSERT itp.), które stanowią spójną całość względem wykonania. Warunki, jakie musi spełnić transakcja, są szczegółowo opisane w zasadach ACID (Atomicity, Consistency, Isolation, Durability) i będą one omówione w kolejnych częściach niniejszego cyklu. Ogólnie rzecz ujmując – wszystkie warunki, jakie zdefiniowane zostały dla danej transakcji, muszą zostać spełnione, aby wykonanie elementarnych operacji mogło zostać zatwierdzone (mówimy wtedy o zatwierdzeniu transakcji). W przeciwnym wypadku wszelkie zmiany zostają cofnięte. Polecenie COMMIT służy właśnie do zatwierdzenia transakcji, w przeciwieństwie do ROLLBACK, który odrzuca zmiany, jakie miała wprowadzić.

Wykonując niniejsze zapytanie:

DELETE FROM Users;
GO

BEGIN TRANSACTION;
GO

INSERT INTO Users(UserId, FirstName, SecondName)
VALUES(6, ‘Jan’, ‘Kowalski’, ‘1986-12-01’)
GO

COMMIT TRANSACTION;
GO

SELECT * FROM Users
GO

BEGIN TRANSACTION;
GO

INSERT INTO Users(UserId, FirstName, SecondName)
VALUES(7, ‘Jan’, ‘Nowak’, ‘1986-12-01’)
GO

ROLLBACK TRANSACTION;
GO

SELECT * FROM Users

Jako wynik wykonania zapytania SELECT, zawsze będzie widoczny tylko jeden wpis, z użytkownikiem Jan Kowalski, identyfikującym się numerem ID = 6, mimo iż w drugim zapytaniu dodany został Jan Nowak (ID=7). Transakcja związana z zapisem tego rekordu została wycofana przez kolejne polecenie ROLLBACK TRANSACTION. Wynik wykonania zapytania został pokazany poniżej:

(1 row(s) affected)
UserID      FirstName                                          SecondName                                         DateOfBirth
----------- -------------------------------------------------- -------------------------------------------------- -----------
6           Jan                                                Kowalski                                           1986-12-01

(1 row(s) affected)


(1 row(s) affected)
UserID      FirstName                                          SecondName                                         DateOfBirth
----------- -------------------------------------------------- -------------------------------------------------- -----------
6           Jan                                                Kowalski                                           1986-12-01

(1 row(s) affected)

Podsumowanie

W niniejszym artykule zostały przedstawione podstawowe informacje n t. systemu SQL Server oraz języka operującego na strukturach danych – T-SQL. Z kolejnej części tej serii artykułów Czytelnik będzie mógł się dowiedzieć, w jaki sposób tworzyć procedury i funkcje w bazie danych.