Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 6 Wersja 1.0
Język SQL – DML
Spis treści
Język SQL – DML ... 1
Informacje o module ... 2
Przygotowanie teoretyczne ... 3
Przykładowy problem ... 3
Podstawy teoretyczne... 3
Przykładowe rozwiązanie ... 7
Porady praktyczne ... 9
Uwagi dla studenta ... 10
Dodatkowe źródła informacji... 10
Laboratorium podstawowe ... 12
Problem 1 (czas realizacji 45 min) ... 12
Laboratorium rozszerzone ... 19
Zadanie 1 (czas realizacji 90 min) ... 19
Informacje o module
Opis modułu
Język SQL składa się z trzech składowych: języka definiowania danych (DDL), języka sterowania danymi (DCL) oraz języka operowania na danych (DML).
W module tym dowiesz się w jaki sposób można pobierać i przeglądać dane, formatować zestawy wyników oraz konstruować proste zapytania używając jeżyka T-SQL DML.
Cel modułu
Celem modułu jest zapoznanie słuchacza z podstawowymi instrukcjami języka T-SQL DML, służącego do manipulowania danymi, oraz pokazanie sposobu używania zaprezentowanych instrukcji.
Uzyskane kompetencje
Po zrealizowaniu modułu będziesz:
• potrafił używać podstawowych instrukcji języka SQL DML
• potrafił pobierać i modyfikować dane w zawarte w bazie
• potrafił formatować zestaw wyników i przedstawiać je w przejrzystej formie
• potrafił konstruować proste zapytania do bazy danych Microsoft SQL Server 2008
Wymagania wstępne
Przed przystąpieniem do pracy z tym modułem powinieneś:
• wiedzieć, w jaki sposób stworzyć bazę danych wraz z jej podstawowymi obiektami (patrz: moduł 3)
• wiedzieć, w jaki sposób poruszać się po Microsoft SQL Server Management Studio
• potrafić definiować użytkowników i przydzielać im uprawnienia Mapa zależności modułu
Zgodnie z mapą zależności przedstawioną na rys. 1, przed przystąpieniem do realizacji tego modułu należy zapoznać się z materiałem zawartym w modułach 3 i 5.
Rys. 1 Mapa zależności modułu
Przygotowanie teoretyczne
Przykładowy problem
Z bazy danych może korzystać wielu użytkowników, dysponujących różnorakimi uprawnieniami i mających dostęp do różnych obiektów w bazie danych. Głównym ich zadaniem jest przeszukiwanie danych w niej zawartych według pewnych reguł, które wcześniej zostały zdefiniowane przez administratora, projektanta i programistę bazy danych. W związku z tym podstawowym zadaniem, jakie stoi przed osobami odpowiedzialnymi za prawidłowe funkcjonowanie bazy danych, jest określenie, jakie polecenia będą używane do pobierania z niej danych
Kolejnym problemem, z jakim często możemy się spotkać, jest wprowadzanie, modyfikacja oraz import i eksport danych pomiędzy różnymi bazami. Eksport nie powinien sprawiać większych kłopotów, natomiast problem może stanowić import danych z innych systemów zarządzania bazami danych. Może on wynikać z odmiennej wewnętrznej struktury przechowywania danych w różnych systemach, jak również z niespójności typów danych w nich zawartych. Dobrze jest wiedzieć, w jaki sposób można poradzić sobie z potencjalnymi problemami przy importowaniu danych.
Podstawy teoretyczne
W części tej pokazany zostanie sposób budowy poleceń odczytujących dane zawarte w bazie.
Rozpoczniemy od prezentacji prostych poleceń SELECT. Następnie prześledzimy informacje na temat złączeń używanych do pobierania danych z wielu tabel. Na końcu prześledzimy, w jaki sposób można łatwo wprowadzać, modyfikować i kasować dane zawarte w bazie.
Wybieranie potrzebnych danych
Najprostszym sposobem pozyskiwania danych z pojedynczej tabeli jest wykonanie instrukcji SELECT. Instrukcja ta służy do odczytywania danych przechowywanych w bazie danych. Sposób wywołania instrukcji znajduje się poniżej:
SELECT [{ ALL | DISTINCT }] lista_wyboru FROM nazwa_tabeli[,…n]
WHERE warunek_wyszukiwania
Jeżeli chcemy pobrać wszystkie kolumny z tabeli, możemy w liście wyboru podać gwiazdkę, przykładowo:
SELECT * FROM nazwa_tabeli
Powyższe polecenie zwraca wszystkie dane zawarte w wybranej tabeli. Zazwyczaj odczytywanie wszystkich kolumn z tabeli nie jest konieczne, a wykonywanie takiego zapytania może spowodować wiele problemów.
Załóżmy, że potrzebne są nam dane tylko z niektórych kolumn z danej tabeli. Wówczas po słowie SELECT należy wymienić nazwy tych kolumn w takiej kolejności, w jakiej chcemy, aby zostały wyświetlone. Przykładowo:
SELECT employeeid, lastname, firstname, title FROM employees
Gdy tabela zawiera małą liczbę wierszy, czas potrzebny na ich zwrócenie jest akceptowalny. W sytuacji, gdy tabela składa się z milionów wierszy, zwracanie ich wszystkich nie jest dobrym rozwiązaniem, dlatego powinno się wydobywać tylko potrzebne wiersze. Należy w związku z tym odpowiedzieć sobie na pytania: które kolumny są potrzebne? Które wiersze są potrzebne?
Udzielenie odpowiedzi na te pytania powinno pomóc przy tworzeniu przyjaznych dla serwera zapytań.
W celu ograniczenia liczby wierszy dokonujemy filtrowania rekordów. Służy do tego klauzula WHERE, którą możemy zawrzeć w instrukcji SELECT. Przykładowo:
SELECT SELECT employeeid, lastname, firstname, title FROM employees
WHERE employeeid = 5
Należy pamiętać, iż warunki wyszukiwania, które możemy stosować po słowie WHERE, są różnorodne i dają dużą swobodę filtrowania rekordów. Warunki te mogą:
• korzystać z operatorów porównania
• korzystać z porównywania ciągów
• korzystać z operatorów logicznych
• pobierać zakresu wartości
• korzystać z listy wartości jako kryterium wyszukiwania
• pobrać wartości nieznane Operator porównania
Operator porównania (=) użyty w klauzulu WHERE powoduje, że zwracane są tylko te rekordy, których wartość w zadanej kolumnie jest równa podanej. Przykładowo:
SELECT lastname, city FROM employees
WHERE country = 'USA'
Operator LIKE
Operator LIKE służy do porównywania ciągów znaków, które powinny być do siebie podobne.
Wraz z operatorem LIKE można stosować znaki maskujące „%”, reprezentujące dowolny ciąg znaków. Przykładowo:
SELECT companyname FROM customers
WHERE companyname LIKE '%Restaurant%'
W wyniku otrzymamy nazwy firm zawierające w sobie słowo „Restaurant”.
Operatorem o odwrotnym działaniu jest operator NOT LIKE. Jego zastosowanie powoduje zwrócenie rekordów, które nie spełniają podanego warunku podobieństwa. Przykładowo:
SELECT companyname FROM customers
WHERE companyname NOT LIKE 'D%'
W wyniku otrzymamy nazwy firm które nie rozpoczynają się na literę „D”.
Operatory logiczne AND i OR
Czasem zachodzi potrzeba połączenia kilku warunków w celu wyodrębnienia potrzebnych danych.
Do łączenia warunków można użyć operatorów logicznych AND i OR.
Operator AND zapewnia prawdziwość wielu warunków. Przykładowo:
SELECT company, Date FROM customers
WHERE (company LIKE '%Restaurant%') AND (Date='2001-07-08')
W wyniku otrzymamy zestaw danych składający się z nazw firm zawierających w sobie słowo
„Restaurant”, które to firmy zostały założone dnia 8 lipca 2001 roku.
Operator OR używany jest wówczas, gdy przynajmniej jeden z warunków musi być spełniony.
Przykładowo:
SELECT company, Date FROM customers
WHERE (company LIKE '%Restaurant%') OR (Date='2001-07-08')
W wyniku otrzymamy zestaw danych składający się z nazw firm zawierających w sobie słowo
„Restaurant” lub firmy, które to firmy zostały założone dnia 8 lipca 2001 roku.
Często zachodzi potrzeba połączenia operatorów AND i OR w celu otrzymania wymaganych danych.
Przykładowo:
SELECT company, productid, Date FROM customers
WHERE (company LIKE '%Restaurant%' OR productid=20) AND (Date='2001-07-08') W wyniku otrzymamy zestaw danych składających się z nazw firm zawierających w sobie słowo
„Restaurant” lub numer identyfikacyjny o wartości 20, które to firmy zostały założone dnia 8 lipiec 2001 roku.
Wyszukiwanie z listy wartości
Do wyszukiwania wartości z podanej listy służy słowo IN. Przykładowo:
SELECT companyname, country FROM suppliers
WHERE country IN ('Japan', 'Italy')
W wyniku zapytania otrzymamy wszystkie nazwy firm, które mieszczą się w Japonii lub we Włoszech.
Wyszukiwanie wartości nieznanych
Bazy danych pozwalają na przechowywanie wartości NULL w niektórych polach. Wyszukiwanie rekordów o nieokreślonych lub pustych wartościach ma wiele praktycznych zastosowań.
Przykładowo:
SELECT companyname, fax FROM suppliers
WHERE fax IS NULL
W wyniku wykonania zapytania otrzymamy wszystkie nazwy firm, dla których wartość pola fax jest pusta.
Wprowadzanie danych
Podstawą dodawania informacji jest instrukcja INSERT. Korzysta ona z następującej składni:
INSERT INTO [nazwa_serwera.][nazwa_bazy_danych.]
[nazwa_schematu.]nazwa_tabeli (nazwa_pola1[,nazwa_pola2[,…n]]) VALUES
(wartosc1[,wartosc2[,…n]]) Przykładowo:
INSERT INTO customers
(customersid, companyname, address, city, phone, fax)
VALUES (‘PECOF’, ‘Pecos Coffee Company’, ‘1900 Street’, ‘London’, ‘(604)555-3392’, ‘(604)555-3393’)
Czasami zachodzi potrzeba wstawienia pustego wiersza do tabeli. Wstawianie takie dokonuje się w następujący sposób:
INSERT INTO [nazwa_serwera.][nazwa_bazy_danych.]
[nazwa_schematu.]nazwa_tabeli DEFAULT VALUES
Zdarza się, że występuje potrzeba przeniesienia informacji z jednego systemu bazodanowego do drugiego bez utraty danych. Zadanie to można zrealizować przy pomocy wielu metod. Omówimy tutaj dwa sposoby przenoszenia danych: z wykorzystaniem narzędzia BCP oraz instrukcji BULK INSERT.
Narzędzie BCP uruchamiane jest z wiersza poleceń. Pozwala ono na import i eksport danych wielkich rozmiarów. BCP wymaga podania nazwy bazy danych źródła, nazwy tabeli lub widoku, identyfikatora działania (in lub out) oraz nazwy zewnętrznego pliku danych. Podstawowa składnia jest następująca:
BCP baza_danych.schemat.{ nazwa_tabeli | nazwa_widoku } { in | out }
nazwa_pliku_zewnętrznego modyfikator_zabezpieczeń modyfikator_formatu
Poniższy przykład eksportuje rekordy z tabeli Product do pliku CSV:
bcp AdventureWorks.Production.Product out "Products.txt" –T -c Następnie można zaimportować dane za pomocą składni:
bcp AdventureWorks.Production.Product2 in "Products.txt" –T -c
Może zdarzyć się sytuacja, że zajdzie potrzeba zaimportowania informacji do bazy danych, ale ze względów bezpieczeństwa nie będzie można użyć narzędzia BCP. Istnieje polecenie T-SQL, które pozwala na rozwiązanie tego problemu. Do wstawiania danych do bazy z poziomu języka T-SQL służy instrukcja BULK INSERT, której przykładowe wywołanie ma postać:
BULK INSERT
[AdventureWorks].[Production].[Product2]
FROM 'C:\Product.txt' WITH
(
FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', CODEPAGE = 'ACP' )
Usuwanie danych
Istnieją sytuacje, kiedy musimy usunąć część lub wszystkie informacje z bazy danych. Przykładem może być skasowanie błędnie wprowadzonych rekordów lub gdy chcemy pozbyć się starych informacji z tabel historycznych. We wszystkich tych sytuacjach korzysta się z polecenia DELETE.
Poniżej przedstawiono podstawową składnię polecenia DELETE.
DELETE FROM [nazwa_serwera.][nazwa_bazy_danych.]
[nazwa_schematu.]nazwa_tabeli WHERE warunki
Wiersze można usunąć korzystając z dowolnych kolumn tabeli w klauzuli WHERE. Przykładowo:
DELETE FROM AdventureWorks.Person.Address WHERE AddressID = 1
Warunek w klauzuli WHERE może zostać użyty z dowolnymi argumentami do usuwania informacji, także tymi, które określają zakresy danych i korzystają z logicznych kombinacji AND, OR i NOT.
Przykładowo:
DELETE FROM Production.Product WHERE (MakeFlag = 1)
AND
(ReorderPoint BETWEEN 200 AND 600)
AND
(SellStarDate<CONVERT(DATETIME, '2000-01-01 00:00:00', 102))
Czasami istnieje potrzeba usunięcia wierszy z tabeli na postawie warunków dotyczących innej tabeli lub tabel. Najlepszym sposobem jest wówczas użycie operatora IN. Przykładowo:
DELETE FROM [AdventureWorks].[Production].[ProductInventory]
WHERE ProductID In (
SELECT ProductID
FROM Production.Product WHERE (MakeFlag = 1) AND
(ReorderPoint BETWEEN 200 AND 600) AND
(SellStarDate<CONVERT(DATATIME,'2000-01-01 00:00:00', 102))
Najlepszą kontrolę nad operacjami w bazie danych zapewniają procedury składowane. W takiej sytuacji implementacja procedury składowanej jest podobna do implementacji innych działań definiowanych wewnątrz procedury składowanej. Przykładowo:
CREATE PROCEDURE [Sales].[CurrencyRate_Delete] @id int AS
DELETE FROM [AdventureWorks].[Sales].[CurrencyRate]
WHERE CurrencyRateID = @id GO
Uaktualnianie danych
Do modyfikacji danych w tabelach używa się polecenia UPDATE. Jego podstawowa składnia jest następująca:
UPDATE [nazwa_serwera.][nazwa_bazy_danych.]
[nazwa_schematu.]nazwa_tabeli SET nazwa_kolumny = nowa_wartosc [WHERE warunek]
Polecenie UPDATE oczywiście można stosować bez warunku WHERE.
Przykładowe rozwiązanie Wprowadzanie danych
Żebyśmy mogli wybierać jakiekolwiek dane, w pierwszym kroku powinniśmy zasilić naszą bazę danych przykładowymi wartościami. Załóżmy, że mamy tabelę Ksiazki w bazie danych Biblioteka. Strukturę tabeli Ksiazki pokazano na rys. 2.
Rys. 2 Tabela Ksiazki w bazie danych Biblioteka
Pokażemy dwa sposoby wstawiania przykładowych danych do tabeli Ksiazki. Sposób pierwszy polega na wstawieniu pojedynczego wiersza za pomocą instrukcji INSERT. Przykładowo:
INSERT INTO Ksiazki
(ID_Ksiazka, Nazwisko, Imie, Tytul, Wydawnictwo, Rok_wydania, CD) VALUES ('1', 'Kowalski', 'Jan', 'Programowanie baz danych',
'Microsoft Press', '2006', '1')
W wyniku wykonania powyższej instrukcji tabela Ksiazki będzie zawierała jeden rekord, co pokazano na rys. 3.
Rys. 3 Tabela Ksiazki po wstawieniu rekordu
Ja widać sposób ten nie jest zbyt efektywny przy wstawianiu większej liczby danych. W sytuacji kiedy mamy do zaimportowania tysiące rekordów, lepszym rozwiązaniem jest użycie na przykład instrukcji BULK INSERT. Załóżmy że dane, które chcemy zaimportować, znajdują się w pliku ksiazki.txt, w postaci jak pokazano na rys. 4.
Rys. 4 Dane zawarte w pliku książki.txt
Wówczas wywołanie instrukcji importu będzie wyglądało w następujący sposób:
BULK INSERT Ksiazki FROM 'C:\ksiazki.txt' WITH
(
FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', CODEPAGE = 'ACP' )
Poważny problem pojawiłby się w sytuacji, kiedy w kolumnie CD przechowywalibyśmy wartości typu logicznego. Wówczas przy eksporcie danych do pliku wartość logiczna TRUE lub FALSE zostałaby zamieniona na wartość tekstową 0 lub 1. Jeżeli w kolejnym kroku chcielibyśmy zaimportować te same dane do bazy, wówczas pojawiłby się błąd związany z niezgodnością typów danych (kolumna ma typ logiczny, a z pliku importujemy dane tekstowe).
Wybieranie danych
Jeżeli nasza baza danych jest już uzupełniona danymi, to w kolejnym kroku możemy zastanowić się, jakie dane mogą być najczęściej wybierane przez użytkowników. Przykładowo dla tabeli Ksiazki użytkownik najczęściej może wyszukiwać książki po:
• imionach autorów
• wydawnictwie
• roku wydania
• informacji o płycie CD
• kombinacji: autor, wydawnictwo, rok wydania i informacje o płycie CD
Przykładowo jeżeli chcielibyśmy znaleźć autora o nazwisku Kowalski, wówczas instrukcja SELECT mogłaby mieć następującą postać:
SELECT Nazwisko, Imie, Tytul FROM Ksiazki WHERE Nazwisko='Kowalski'
Natomiast jeżeli chcielibyśmy znaleźć autorów o nazwisku Kowalski lub Andziński, wówczas instrukcja SELECT mogłaby wyglądać w ten sposób:
SELECT Nazwisko, Imie, Tytul FROM Ksiazki
WHERE (Nazwisko='Kowalski') OR (Nazwisko='Andziński')
W sytuacji kiedy chcielibyśmy znaleźć autorów, których nazwisko kończy się na -ski, wówczas moglibyśmy sformułować instrukcję SELECT następująco:
SELECT Nazwisko, Imie, Tytul FROM Ksiazki WHERE Nazwisko LIKE '%ski'
Oczywiście moglibyśmy do tego dołączyć jeszcze wyszukiwanie po dacie, wówczas otrzymalibyśmy:
SELECT Nazwisko, Imie, Tytul, Rok FROM Ksiazki WHERE (Nazwisko LIKE '%ski') AND (Rok='2003')
W sytuacji kiedy chcemy znaleźć autora o nazwisku zakończonym na -ski, który wydał w 2003 roku książkę z dołączoną płytą CD, możemy użyć następującego zapytania:
SELECT Nazwisko, Imie, Tytul, Rok, CD FROM Ksiazki WHERE (Nazwisko LIKE '%ski' OR Rok='2003') AND (CD='1')
Najbardziej skomplikowanego zapytania potrzebujemy wówczas, gdy chcemy znaleźć autora o nazwisku zakończonym na -ski, którego książka wraz z dołączoną płytą CD została wydana w 2003 roku nakładem wydawnictwa Selion. Wówczas zapytanie może przyjąć postać:
SELECT Nazwisko, Imie, Tytul, Rok, Wydawnictwo, CD FROM Ksiazki WHERE (Nazwisko LIKE='%ski' OR Rok='2003') AND
(Wydawnictwo='Selion') AND (CD='1')
Porady praktyczne Uwagi ogólne
• Najlepszą kontrolę nad operacjami wykonywanymi na bazie danych zapewniają procedury składowane. Dzięki nim możesz zapewnić jednolity model dostępu do bazy z poziomu aplikacji, a także poprawić bezpieczeństwo i wydajność. Z tych powodów powinieneś rozważyć użycie procedur składowanych we własnych aplikacjach.
• Tworzenie dynamicznych poleceń wstawiania danych może zagrażać bezpieczeństwu bazy, dlatego wielce prawdopodobnie jest, że administrator będzie preferował zabezpieczenia tabel przez odmowę uprawnień zapisu do tabeli. Z tego powodu dobrym rozwiązaniem jest zarządzanie operacjami wstawiania danych za pomocą procedur składowanych.
Wybieranie danych
• Użycie polecenia SELECT * FROM Department odczytuje wszystkie kolumny z tabeli.
Zazwyczaj nie potrzebujemy informacji o wszystkich kolumnach, natomiast wykonanie takiego zapytania bez potrzeby może spowodować sporo problemów. Przykładowo:
a) Aplikacje mogą działać nieprawidłowo po dodaniu nowych kolumn do tabeli. Jeśli ich twórcy nie przewidzieli takiego możliwości, nieoczekiwane kolumny zostaną niepoprawnie obsłużone.
b) Jeśli zostaną wybrane wszystkie kolumny, optymalizator zapytań nie będzie używał niektórych indeksów.
• Jeśli zajdzie potrzeba wykorzystania w klauzuli WHERE operatorów logicznych AND i OR, warto rozważyć stosowanie nawiasów. Pozwalają one precyzyjnie określić kolejność wykonywania operacji logicznych.
• W wielu przypadkach możemy na wiele sposobów otrzymać tan sam efekt wywołania zapytania. Przykładowo następujące dwa zapytania zwracają te same rekordy:
SELECT company, Date FROM customers
WHERE year(ShipDate)=2001 and month(ShipDate)=7 SELECT company, Date
FROM customers
WHERE ShipDate between '2001-07-01' and '2001-07-31'
Pierwsze z zapytań odczytuje datę i miesiąc. Po odczytaniu wszystkich wierszy wykonuje obliczenia i je porównuje. Drugie zapytanie wykonuje tylko porównanie, bez obliczeń, ponadto jeżeli na kolumnie ShipDate znajduje się indeks, to zostanie on użyty – a zatem zapytanie drugie jest szybsze.
• Można używać jednej, kilku lub wszystkich kolumn do utworzenia warunku w klauzuli WHERE.
Jego wydajność można poprawić poprzez:
a) zastosowanie klucza głównego w warunku b) użycie kolumn, które są indeksowane
Wprowadzanie, usuwanie i uaktualnianie danych
• Aby skasować wszystkie wiersze z tabeli, zamiast instrukcji DELETE można użyć instrukcji TRUNCATE TABLE. Różnica w stosunku do instrukcji DELETE polega na tym, iż zadanie kasowania odbywa się szybciej. Używając TRUNCATE TABLE trzeba mieć pewność, że tabela nie ma żadnych związków z innymi tabelami.
• Możne usuwać wiersze z tabeli za pośrednictwem widoków, jednak występują tutaj pewne ograniczenia:
a) Można usunąć wiersze tylko z jednej tabeli.
b) Użytkownik musi posiadać uprawnienia do usuwania dla widoku.
• Można wykonywać zadania uaktualniające korzystając z widoków. W takiej sytuacji istnieją pewne ograniczenia:
a) Uaktualnienia są dozwolone tylko dla kolumn z pojedynczej tabeli.
b) Użytkownik musi mieć uprawnienia do zapisu dla widoku.
Uwagi dla studenta
Jesteś przygotowany do realizacji laboratorium jeśli:
• rozumiesz składnię języka T-SQL DML
• umiesz skonstruować podstawowe zapytania do bazy danych
• umiesz zaimportować dane do swojej bazy danych
• umiesz modyfikować dane w swojej bazie danych
Pamiętaj o zapoznaniu się z uwagami i poradami zawartymi w tym module. Upewnij się, że rozumiesz omawiane w nich zagadnienia. Jeśli masz trudności ze zrozumieniem tematu zawartego w uwagach, przeczytaj ponownie informacje z tego rozdziału i zajrzyj do notatek z wykładów.
Dodatkowe źródła informacji
1. Kalen Delaney, Microsoft SQL Server 2005: Rozwiązania praktyczne krok po kroku, Microsoft Press, 2006
W książce autor przedstawia między innymi w jaki sposób pobierać dane z instancji SQL Server za pomocą języka T-SQL. Pozycja polecana dla osób, które chciałyby dowiedzieć się nieco więcej na temat praktycznych metod pobierania danych.
2. Kalen Delaney, Podstawy baz danych krok po kroku, APN Promise, 2006
Bardzo dobra książka dla osób początkujących. W łatwy i przejrzysty sposób pokazano, jak należy odczytywać dane z SQL Server, jak je wybierać, modyfikować, usuwać i uaktualniać. Książka oprócz teorii zawiera dużo przykładów.
3. Deren Bieniek, Randy Dyess, Mike Hotek, Javier Loria, Adam Machanic, Antonio Soto, Adolfo Wiernik, SQL Server 2005. Implementacja i obsługa, APN Promise, Warszawa 2006
W książce przedstawiono pracę z językiem T-SQL DML. Pokazano, w jaki sposób tworzyć zapytania, formatować wynikowy zbiór wierszy oraz modyfikować dane.
Książka szczególnie polecana ze względu na dużą zawartość ćwiczeń laboratoryjnych.
4. Dusan Petkovic, Microsoft SQL Server 2008: A Beginner's Guide, McGraw-Hill, 2008
Pozycja napisana w sposób przystępny. Wprowadza w SQL Server 2008 w sposób szybki i łatwy. Osoba początkująca w SQL Server 2008 znajdzie w niej podstawy z każdego tematu dotyczącego serwera bazodanowego. W prosty sposób dowiesz się jak należy tworzyć zapytania, formatować wyniki zapytań oraz modyfikować dane.
Pozycja szczególnie polecana dla osób początkujących.
Laboratorium podstawowe
Problem 1 (czas realizacji 45 min)
Jesteś administratorem w firmie National Insurance. Właśnie dowiedziałeś się od swojego szefa, że firma planuje rozszerzenie na skalę uczelnianą systemu prac dyplomowych, którym zarządzała dotąd na Twoim wydziale. Zadanie, jakie przed Tobą stoi, to zdefiniowanie nowych zapytań, które najczęściej będą wykonywali użytkownicy na Twojej bazie danych.
Zadanie Tok postępowania
1. Nawiąż połączenie z SQL Server 2008
• Uruchom maszynę wirtualną BD2008.
— Jako nazwę użytkownika podaj Administrator.
— Jako hasło podaj P@ssw0rd.
Jeśli nie masz zdefiniowanej maszyny wirtualnej w Microsoft Virtual PC, dodaj nową maszynę używając wirtualnego dysku twardego z pliku D:\VirtualPC\Dydaktyka\BD2008.vhd.
• Kliknij Start. Z grupy programów Microsoft SQL Server 2008 uruchom SQL Server Management Studio.
• W oknie logowania kliknij Connect.
2. Utwórz tabelę testową
• Z menu głównego wybierz File -> Open -> File.
• Odszukaj plik C:\Labs\Lab06\dml_1.sql.
• Zaznacz i uruchom (F5) poniższy fragment kodu:
-- (1) Przygotuj tabele do testowania polecen jezyka DML USE PraceDyplomowe
GO
SELECT ID_Osoby, Imie, Nazwisko, Nr_Indeksu, ID_Stopnian INTO Osoba_kopia
FROM Osoba GO
INSERT INTO Osoba_kopia(ID_Osoby, Imie, Nazwisko, Nr_Indeksu, ID_Stopnian)
VALUES ('6','Pawel','Zaremba','65432','3') GO
3. Zapoznaj się z poleceniem SELECT
• Zaznacz i uruchom (F5) poniższy fragment kodu odpowiedzialny za wyświetlenie zawartości całej tabeli Osoba. Wynik działania pokazano na rys. 5:
-- (2) Wyswietl cala zawartosc tabeli Osoba_kopia SELECT *
FROM Osoba_kopia GO
Rys. 5 Wynik wyświetlenia całej zawartości tabeli Osoba_kopia
• Zaznacz i uruchom (F5) poniższy fragment kodu odpowiedzialny za wyświetlenie dwóch kolumn: Nazwisko i Imie. Wynik działania pokazano na rys. 6:
-- (3) Wybierz kolumny, ktore chcemy wyswietlic SELECT Nazwisko, Imie
FROM Osoba_kopia GO
Rys. 6 Wynik wyświetlenia dwóch kolumn Nazwisko i Imię
• Zaznacz i uruchom (F5) poniższy fragment kodu odpowiedzialny za dodanie literału i aliasu. Wynik działania pokazano na rys. 7:
-- (4) Dodajemy literal i alias
SELECT 'Imie i nazwisko: ' + Nazwisko + ' ' + Imie AS Osoba FROM Osoba_kopia
GO
Rys. 7 Wynik dodania literału i aliasu
Alias nie jest nazwą własną. Jest to tylko tymczasowy (istniejący tylko dla zapytania, w którym został zdefiniowany, nie zapisywany nigdzie na stałe) nagłówek kolumny. Może zawierać napis będący nazwą innej kolumny lub obiektu bazy danych.
• Zaznacz i uruchom (F5) poniższy fragment kodu odpowiedzialny za nie wyświetlanie duplikatu. Wynik działania pokazano na rys. 8:
-- (5) Nie wyswietlajmy duplikatow SELECT DISTINCT Imie
FROM Osoba_kopia GO
Rys. 8 Wynik zapytania bez wyświetlania duplikatów
Słowo DISTINCT, oprócz usunięcia duplikatów z wyników, ignoruje także wartości puste w zbiorze rekordów (tzn. gdy w wybranym zestawie zdarzy się wiersz, w którym wszystkie pola są puste, nie zostanie on wyświetlony).
• Zaznacz i uruchom (F5) poniższy fragment kodu odpowiedzialny za sortowanie rosnące i malejące. Wynik działania pokazano na rys. 9:
-- (6) sortujmy rekordy rosnaco (ASC) i malejaco (DESC) SELECT Imie, nazwisko Nr_Indeksu
FROM Osoba_kopia ORDER BY Nr_Indeksu – DESC GO
Rys. 9 Wynik sortowania
• Zaznacz i uruchom (F5) poniższy fragment kodu odpowiedzialny za ogranicenie liczby rekordów. Wynik działania pokazano na rys. 10.
-- (7) ograniczmy liczbe rekordow SELECT Imie, Nazwisko
FROM Osoba_kopia WHERE Nazwisko = 'Rawa' GO
Rys. 10 Wynik ograniczenia liczby rekordów
• Zaznacz i uruchom (F5) poniższy fragment kodu odpowiedzialny za ogranicenie liczby rekordów z użyciem operatora wiekszości. Wynik działania pokazano na rys. 11:
-- (8) wyprobujmy operator wiekszosci SELECT Imie, Nazwisko, Nr_Indeksu FROM Osoba_kopia
WHERE Nr_Indeksu >= 110 GO
Rys. 11 Wynik ograniczenia liczby rekordów z uzyciem operatora większości
• Zaznacz i uruchom (F5) poniższy fragment kodu odpowiedzialny za ogranicenie liczby rekordów z użyciem operatora OR. Wynik działania pokazano na rys. 12:
-- (9) wyprobujmy operator OR SELECT Imie, Nazwisko, Nr_Indeksu FROM Osoba_kopia
WHERE Nr_Indeksu < 110 OR Nr_Indeksu >= 115 GO
Rys. 12 Wynik ograniczenia liczby rekordów z uzyciem operatora OR
• Zaznacz i uruchom (F5) poniższy fragment kodu odpowiedzialny za ogranicenie liczny rekordów z wyszukiwaniem w zbiorze wartości.
Wynik działania pokazano na rys. 13.
-- (10) wyszukajmy w zbiorze wartosci SELECT Imie, Nazwisko
FROM Osoba_kopia
WHERE Nazwisko IN ('Rawa','Nowak') GO
Rys. 13 Wynik ograniczenia liczby rekordów z wyszukiwaniem w zbiorze wartości
• Zaznacz i uruchom (F5) poniższy fragment kodu odpowiedzialny za ogranicenie liczny rekordów z wyszukiwaniem w przedziale wartości.
Wynik działania pokazano na rys. 14.
-- (11) wyszukajmy w przedziale wartosci SELECT Imie, Nazwisko, Nr_Indeksu
FROM Osoba_kopia
WHERE Nr_Indeksu BETWEEN 110 AND 115 GO
Rys. 14 Wynik ograniczenia liczby rekordów z wyszukiwaniem w przedziale wartości
• Zaznacz i uruchom (F5) poniższy fragment kodu odpowiedzialny za ogranicenie liczny rekordów z użyciem operatora LIKE. Wynik działania pokazano na rys. 15.
-- (12) uzyjmy operatora LIKE SELECT Imie, Nazwisko, Nr_Indeksu FROM Osoba_kopia
WHERE Nazwisko LIKE '[A-F]%' GO
Rys. 15 Wynik ograniczenia liczby rekordów z użyciem operatora LIKE
• Zaznacz i uruchom (F5) poniższy fragment kodu odpowiedzialny za zliczanie liczny rekordów. Wynik działania pokazano na rys. 16.
-- (13) zliczmy rekordy w tabeli SELECT Count(*) AS [Liczba osob]
FROM Osoba_kopia GO
Rys. 16 Zliczanie liczby rekordów
• Zaznacz i uruchom (F5) poniższy fragment kodu odpowiedzialny za dodanie nowego rekordu:
-- (14) dodajmy nowy rekord
INSERT INTO Osoba_kopia(ID_Osoby, Imie, Nazwisko, Nr_Indeksu, ID_Stopnian)
VALUES ('7','Marek','Kogut','35472','2') GO
• Zaznacz i uruchom (F5) poniższy fragment kodu odpowiedzialny za zmianę istniejącego rekordu:
-- (15) zmienmy istniejacy rekord UPDATE Osoba_kopia
SET Nazwisko = 'Kogucinski' WHERE LastName = 'Kogut' GO
• Zaznacz i uruchom (F5) poniższy fragment kodu odpowiedzialny za zmianę istniejącego rekordu:
-- (16) usunmy rekord DELETE FROM Osoba_kopia WHERE Nazwisko = 'Kogucinski' GO
• Zaznacz i uruchom (F5) poniższy fragment kodu odpowiedzialny za usunięcie tabeli:
-- (17) usunmy zawartosc tabeli TRUNCATE TABLE Osoba_kopia GO
Laboratorium rozszerzone
Zadanie 1 (czas realizacji 90 min)
Jesteś administratorem w firmie National Insurance. Właśnie dowiedziałeś się od swojego szefa, że firma zarządzająca bazą AdventureWorks planuje rozszerzenie i modernizacje systemu w celu spełnienia pewnych standardów. Rozszerzenie to wiąże się z modyfikacją struktury bazy danych oraz importem do bazy danych wielu milionów rekordów. W konsekwencji w związku z modernizacją systemu bazodanowego oraz spełnienia standardów muszą zostać przebudowane zapytania wysyłane do obiektów bazy danych. W związku z tym część zapytań powinna zostać usunięta, część zmodyfikowana, a część od nowa utworzona.
Zadania jakie przed Tobą stoją to:
1. Podjęcie decyzji, w jaki sposób usprawnić proces importu wielu milionów danych do systemu bazodanowego.
2. Podjęcie decyzji, które zapytania w bazie danych pozostaną bez zmian, a które zostaną zmodyfikowane lub usunięte.
3. Podjęcie decyzji, jakie nowe zapytania powinny zostać utworzone.
