10. Operacje na danych "Data Manipulation Language".

W artykule tym zajmiemy się językiem operacji na danych. Przedstawimy instrukcje wchodzące w skład Data Manipulation Language(DML), bo o nim tutaj mowa, zrobimy kilka przykładowych operacji na danych zawartych w bazie AdventureWorks oraz pokażemy, w jaki sposób wykorzystać narzędzia ułatwiające tworzenie skryptów.

Data Manipulation Language służy do pracy z danymi zgromadzonym w systemie bazodanowym. Dzięki wykorzystaniu wyrażeń DML możemy operować na danych - skutecznie je zmieniać i przeglądać. DML składa się z czterech podstawowych komend:

Najprostszy z możliwych skryptów z wykorzystaniem języka zapytań jaki można wykonać to:

SELECT *
FROM [Person].[Contact]

Zwraca listę wszystkich rekordów jakie zawarte są w tabeli Contact. Słowo kluczowe SELECT określa rodzaj operacji jaką będziemy wykonywać, * daje informacje o tym, iż pobieramy wszystkie kolumny należące do danej tabeli, kolejne słowo kluczowe FROM określa, z jakiej tabeli będziemy pobierać dane, a następnie podajemy nazwę tej tabeli. W SQL Server 2005 musimy przed nazwą tabeli podać dodatkowo nazwę schematu. Schemat jest związany z bezpieczeństwem bazy danych. Każdy obiekt bazodanowy należy do któregoś ze schematów. Jeśli nie przypiszemy jawnie schematu podczas tworzenia tabeli, procedury składowanej itp. to domyślnie umieszczona zostanie w schemacie [dbo]. Szerzej o schematach powiemy sobie w kolejnych publikacjach dotyczącej polityki bezpieczeństwa i języka Data Control Language(DCL).

Ponieważ wychodzę z założenia, że jeśli coś można wyklikać, nie ma potrzeby tego pisać, większość przykładów będziemy tworzyć w narzędziu przeznaczonym do edytowania skryptów. Dzięki temu zaoszczędzimy sporo czasu, a przy tym będziemy mieli pewność, iż zapytanie, które napisaliśmy, nie posiada nieprzyjemnych literówek.
Na początek spróbujmy utworzyć zapytanie, które wcześniej wszyscy widzieliśmy - wylistowujące wszystkie rekordy z tabeli Contact. W tym celu w Microsoft SQL Server Management Studio otwieramy nową zakładkę (naciskamy przycisk New Query). Po załadowaniu się okna służącego do edycji własnych skryptów, pojawi się dostępna opcja uruchomienia Design Query in Editor.

Po jej naciśnięciu przechodzimy do edytowania zapytania. Pierwszy krok to wybranie tabeli nas interesującej, w tym przypadku będzie to tabela Contact w schemacie Person.

Gdy już wybraliśmy tabelę z listy, możemy przejść do edytowania zapytania. Teraz wystarczy wcisnąć checkbox-a opisanego (* All Columns) oraz potwierdzić wciskając OK.

Jak łatwo zauważyć, mamy wygenerowane zapytanie praktycznie identyczne jak to powyżej. Jedyna różnica polega na tym, że tu mamy gwiazdkę poprzedzoną nazwą tabeli z której dane chcemy wybrać.

Chciałbym pokrótce teraz opisać co oznaczają poszczególne pola i w jaki sposób należy korzystać z designera. W oknie znajdującym się w górnej części ekranu mamy umieszczoną tabelkę/tabele wybrane z listy i to właśnie na niej będziemy przeprowadzać wszelkie operacje. Środkowa część monitora zawiera moduł edycji zapytania. Tu będziemy określać jakie kolumny mają się wyświetlać oraz jak te wybrane kolumny mają się nazywać. Można też wybrać wiele innych opcji, między innymi ukrywać niechciane kolumny, grupować i wiele, wiele innych, o których będziemy mówić w następnych rozdziałach. Widok znajdujący się w dolnej części ekranu jest rezultatem operacji w module edycji zapytania. Ważnym aspektem całego designera jest fakt, że wszystkie widoki są ze sobą ściśle związane. Zmiana aliasu kolumny w części środkowej spowoduje, iż zostanie wygenerowany kod odpowiedzialny za taką akcję. Działa to również w drugą stronę. Jeśli napiszemy kod w oknie znajdującym się w dolnej części monitora, odpowiedzialnej za generowanie skryptu, to automatycznie w środkowej pojawią się zmiany odwzorowujące wprowadzony skrypt (o ile nie pojawią się w nim błędy).

Przejdźmy jednak do stworzenia zapytania edytującego dane już istniejące w tabeli Contact. Powtarzamy kroki tak jak w pierwszym przykładzie. Kiedy mamy już włączonego designera musimy zmienić rodzaj zapytania. Standardowo włączona jest opcja SELECT, więc jeśli chcemy zmienić istniejący wiersz/e musimy zaznaczyć opcje UPDATE. W tym celu musimy nacisnąć prawym guzikiem myszy na oknie designera, a następnie z menu podręcznego wybrać opcje Change Type  i Update. Po dokonaniu wyboru w dolnym oknie możemy zauważyć, jak zmienił się wygenerowany skrypt. Z opcji SELECT zmieniła się na UPDATE. Teraz należy podać co i na jaką wartość chcemy zmienić. Podobnie jak we wcześniejszym przykładzie zaznaczamy checkbox z tą różnicą, że teraz nie wybieramy wszystkich kolumn tylko pojedynczą wartość FirstName. Będziemy chcieli zmienić imiona wszystkich Michele na Michał. W środkowym oknie klikamy w pierwszym wierszu na kolumnie New Value i wpisujemy Michał, następnie w kolumnie Filter wpisujemy starą wartość Michele. Teraz wystarczy już tylko wcisnąć przycisk OK i zapytanie jest już zrobione. Ponieważ nie omawialiśmy wcześniej składni DML do modyfikacji danych, więc na przykładzie wygenerowanego kodu opiszę jak to działa.

UPDATE    Person.Contact
SET       FirstName = N'Michał'
WHERE     (FirstName = N'Michele')

Pierwsze słowo kluczowe, podobnie jak przy wyrażeniu SELECT, określa rodzaj operacji, po której występuje nazwa tabeli, którą chcemy edytować. Koleje słowo kluczowe określa listę kolumn, które będziemy edytować i wartość, jaką chcemy przypisać do danej kolumny. Jeśli zakończylibyśmy edycję po pierwszych dwóch liniach, skrypt zmodyfikowałby wszystkie imiona w tabeli Contact na Michał. Takie zachowanie nie jest zgodne z naszym intencjami dlatego ograniczamy zbiór danych do osób o imieniu Michele. Jeśli w jednym zapytaniu chcielibyśmy zmienić automatycznie pole związane z datą modyfikacji, wystarczy do naszego zapytania dodać po przecinku nazwę kolejnej zmienianej kolumny oraz wartość analogicznie jak we wcześniejszym przykładzie.

Tu zmieniamy datę modyfikacji wiersza, przy okazji wykorzystujemy jedną z funkcji SQL Severa pobierającą datę.

UPDATE    Person.Contact
SET       FirstName = N'Michał', ModifiedDate = GETDATE()
WHERE     (FirstName = N'Michele')

Potrafimy już wyświetlić dane i je z edytować. Przejdźmy teraz do operacji wstawiania nowych wierszy. Ponownie włączamy designera, tylko zamiast tabeli Contact wybieramy CountryRegione. Kolejny krok jest identyczny, jak podczas operacji UPDATE. Zmieniamy rodzaj zapytania z SELECT na INSERT VALUES. Czyli najpierw naciskamy prawym guzikiem myszy na okienku designera i z menu podręcznego wybieramy opcję Change Type, a następnie Insert Values.

Dodanie nowego regionu jest już dziecinnie proste. Zaznaczamy w tabeli pola, które chcemy wstawiać, a następnie w środkowym module wstawiamy wartości dla odpowiednich kolumn. Tutaj również użyłem funkcji do automatycznego wstawienia daty GETDATE().

INSERT INTO Person.CountryRegion
            Name, ModifiedDate)
VALUES      N'Polska', GETDATE())

Składnia wyrażenia jest następująca: słowo kluczowe INSERT określające, że mamy do czynienia z operacją wstawiania. Po nim następuje słowo kluczowe INTO, po którym wstawiamy informacje, do jakiej tabeli chcemy wstawić wiersz oraz w jakiej kolejności będziemy wstawiać wartości. Wartości są wylistowane po słowie kluczu VALUES i w naszym przypadku jest to nazwa kraju oraz aktualna data. Jeśli byśmy zmienili kolejność w nazwie kolumn, to aby wyrażenie mogło się wstawić musielibyśmy zmienić kolejność wartości po klauzuli VALUES.

INSERT INTO Person.CountryRegion
            (ModifiedDate, Name)
VALUES      (GETDATE(), N'Polska')

Z podstawowych operacji jakie możemy dokonać na repozytorium zostało nam jeszcze usuwanie. Po tym jak wstawiliśmy do bazy danych wiersz z Polska przydałoby się go teraz wykasować. Intuicja każdemu z nas już podpowiada, co powinniśmy zrobić. Tak więc włączamy designera wybieramy tabelę CountryRegion, a po załadowaniu wybieramy z menu podręcznego opcje DELETE. Tym razem jednak pojawia się pewna różnica. Na tabeli nie ma możliwości zaznaczenia checkbox. Jest to spowodowane faktem, iż operacja usuwania działa na całych wierszach, a nie na kolumnach. Sprawia to, że funkcjonalnie nie ma potrzeby zaznaczania pojedynczych komórek. Chcąc usunąć wiersz gdzie nazwą regionu jest Polska musimy dodać klauzulę WHERE. Klikamy więc w środkowym oknie designera na kolumnę Columns i wybieramy z niej Name teraz w Filter wpisujemy Polska i gotowe.

A oto wygenerowany skrypt:

DELETE FROM Person.CountryRegion
WHERE       (Name = N'Polska')

Podsumowanie

Nauczyliśmy się jak w dość łatwy i przyjemny sposób tworzyć skrypty modyfikujące i wyświetlające dane z naszego serwera bazodanowego. Pokazałem, jak dzięki wykorzystaniu Design Query in Editor szybko zmieniać typy zapytań, tworzyć nowe skomplikowane zapytania i edytować już istniejące. Dla takich małych skryptów jak te wyżej przedstawione zysk czasowy jest niewielki, ale przy zapytaniach sięgających setek linii korzystanie z narzędzia wyżej opisanego znacznie usprawnia i przyspiesza pracę.