ITA-101 Bazy Danych
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 6
Wersja 1.0
Język SQL - DML
Spis treści
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 6
ITA-101 Bazy danych
Język SQL DML
Strona 2/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 zostaną wprowadzone, a następnie przedstawione na
przykładach podstawowe instrukcje języka sterowania na danych – języka
SQL DML (z ang. Data Manipulation Language).
Cel modułu
Celem modułu jest zapoznanie słuchacza z podstawowymi instrukcjami
języka manipulowania na danych – języka T-SQL DML oraz pokazania
sposobu używania wprowadzonych instrukcji.
Uzyskane kompetencje
Po zrealizowaniu modułu będziesz:
potrafił używad podstawowych instrukcji języka SQL DML
potrafił pobierad i modyfikowad dane w zawarte w bazie
potrafił formatowad zestaw wyników i przedstawiad je w przejrzystej
formie
potrafił konstruowad proste zapytania do bazy danych Microsoft SQL
Server 2008
Wymagania wstępne
Przed przystąpieniem do pracy z tym modułem powinieneś:
wiedzied w jaki sposób stworzyd bazę danych wraz z jej
podstawowymi obiektami (patrz Moduł 4).
wiedzied, w jaki sposób poruszad się po Microsoft SQL Server
Menagement Studio
potrafid definiowad użytkowników i przydzielad 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 zapoznad się z materiałem zawartym
w Module 3 i Module 5
Moduł 6
Dodatek
Moduł 1
Moduł 2
Moduł 3
Moduł 4
Moduł 5
Moduł 7
Moduł 8
Moduł 9
Moduł 10
Moduł 11
Moduł 12
Moduł 13
Rys. 1 Mapa zależności modułu
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 6
ITA-101 Bazy danych
Język SQL DML
Strona 3/19
Przygotowanie teoretyczne
Przykładowy problem
Z bazy danych może korzystad wielu użytkowników, którzy mogą dysponowad różnymi prawami
dostępu i którzy mają dostęp do różnych obiektów w bazie danych. Głównym ich zadaniem jest
możliwośd przeszukiwania danych w niej zawartych według pewnych reguł, które wcześniej zostały
zdefiniowane przez administratora, projektanta i programistę bazy danych. Zatem podstawowym
zadaniem jaki stoi przed osobami odpowiedzialnymi za prawidłowe funkcjonowanie bazy danych to
wybór odpowiedniego zestawu zapytao z wykorzystaniem polecenia SELECT wraz z różnymi
klauzulami i operatorami.
Kolejnym problemem z jakim często możemy się spotkad to wprowadzanie dane i modyfikacja
danych import i eksport danych pomiędzy różnymi bazami danych. Eksport nie stanowi tutaj
większego problemu. Natomiast Głównym problemem jest tutaj import danych z innych Systemów
Zarządzania Bazą Danych. Problem ten wynika z różnej wewnętrznej struktury bazy danych jak
również z niespójności typów danych w nich zawartych. Dobrze jest zatem wiedzied w jaki sposób
można sobie z potencjalnymi problemami importu poradzid.
Podstawy teoretyczne
W części tej pokazany zostanie sposób budowy poleceo odczytujących dane zawarte w bazie.
Rozpoczniemy od prezentacji prostych poleceo
SELECT
. Następnie prześledzimy informacje na
temat złączeo używanych do pobierania danych z wielu tabel. Na koocu prześledzimy w jaki sposób
można łatwo wprowadzad, modyfikowad i kasowad 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 pobrad wszystkie kolumny z tabeli, to w liście wyboru podajemy gwiazdkę.
Przykładowo:
SELECT * FROM nazwa_tabeli
Powyższe polecenie zwraca wszystkie pola tabeli Department. Zazwyczaj odczytywanie wszystkich
kolumn z tabeli nie jest konieczne a wykonywanie takiego zapytania może spowodowad 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 wymienid 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 zwiera 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ę wydobywad tylko potrzebne wiersze. Zatem należy
odpowiedzied sobie na pytania: które kolumny są potrzebne? Które wiersze są potrzebne?
Prawidłowe odpowiedzi zagwarantują utworzenie przyjaznych dla serwera zapytao.
W celu ograniczenia liczby wierszy dokonujemy filtrowania rekordów. Służy do tego klauzula
WHERE
,
którą możemy umieścid w instrukcji
SELECT
. Przykładowo:
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 6
ITA-101 Bazy danych
Język SQL DML
Strona 4/19
SELECT SELECT employeeid, lastname, firstname, title
FROM employees
WHERE employeeid = 5
Należy pamiętad, iż warunki wyszukiwania, które możemy stosowad po słowie
WHERE
, są
różnorodne i dają dużą swobodę filtrowania rekordów. Warunki te mogą:
korzystad z operatorów porównania
korzystad z porównywania ciągów
korzystad z operatorów logicznych
pobierad zakresu wartości
korzystad z listy wartości, jako kryterium wyszukiwania
pobrad wartości nieznane
Operator porównania
Operator porównania (=) powoduje, że zwracane są tylko te rekordy, których wartośd w zadanej
kolumnie jest równa podanej. Przykładowo:
SELECT lastname, city
FROM employees
WHERE country = 'USA'
Operator LIKE
Operator LIKE porównuje ciągi znaków, które powinny byd do siebie podobne. Wraz z operatorem
LIKE można stosowad znaki maskujące:
%
- zastępuje 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 odwrotnym do operatora
LIKE
jest operator
NOT LIKE
. Jego zastosowanie powoduje
zwrócenie rekordów, które nie spełniają podanego warunku podobieostwa. 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żyd operatorów logicznych
AND
i
OR
.
Operator AND zapewnia prawdziwośd wielu warunków. Przykładowo:
SELECT company, Date
FROM customers
WHERE (company LIKE '%Restaurant%') AND (Date=2001-07-08)
W wyniku otrzymamy nazwy firm zawierające w sobie słowo „Restaurant” i datę 8 lipiec 2001 roku.
Operator
OR
używany jest wówczas, gdy przynajmniej jeden z warunków musi byd spełniony.
Przykładowo:
SELECT company, Date
FROM customers
WHERE (company LIKE '%Restaurant%') OR (Date=2001-07-08)
W wyniku otrzymamy nazwy firm zawierające w sobie słowo „Restaurant” lub datę 8 lipiec 2001
roku.
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 6
ITA-101 Bazy danych
Język SQL DML
Strona 5/19
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 nazwy firm zawierające w sobie słowo „Restaurant” lub numer
identyfikacyjny o wartości 20 i datę 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 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 zastosowao.
Przykładowo:
SELECT companyname, fax
FROM suppliers
WHERE fax IS NULL
W wyniku wykonania zapytania otrzymamy wszystkie nazwy firm, dla których wartośd pola fax jest
pusta.
Wprowadzanie danych
Podstawą dodawania informacji jest instrukcja
INSERT
. Korzysta ona z następującej składni:
INSERT INTO [<NazwaBazyDanych>] <NazwaTabeli>
(<NazwaPola1>[,<NazwaPola2>…])
VALUES
(<Wartosc1>[,<Wartosc2>…])
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 <NazwaTabeli> DEFAULT VALUES
Zdarza się, że występuje potrzeba przeniesienia informacji z jednego systemu bazodanowego do
drugiego bez utraty danych. Służy do tego wiele narzędzi. Omówimy tutaj dwa takie narzędzia.
Jednym z nich jest narzędzie
BCP
, a drugim instrukcja
BULK INSERT
.
Narzędzie
BCP
uruchamiane jest z wiersza poleceo. Pozwala ono na import i eksport danych
wielkich rozmiarów.
BCP
wymaga nazwy bazy danych źródła a także przeznaczania jak również
tabeli lub widoku, identyfikatora działania takiego jak
in
lub
out
oraz nazwy zewnętrznego pliku
danych. Podstawowa składnia jest następująca:
BCP <BazaDanych>.<Schemat>.<NazwaTabeli>/<NazwaWidoku>
<in/out>
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 6
ITA-101 Bazy danych
Język SQL DML
Strona 6/19
<NazwaPlikuZewnętrznego>
<ModyfikatorZabezpieczeń>
<ModyfikatorFormatu>
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 zaimportowad dane za pomocą składni
bcp AdventureWorks.Production.Product2 in ‚Products.txt‛ –T -c
Może zdarzyd się sytuacja, że zajdzie potrzeba zaimportowania informacji do bazy danych, ale ze
względów bezpieczeostwa nie możemy używad 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 postad:
BULK INSERT
[AdventureWorks].[Production].[Product2]
FROM ‘C:\Product.txt’
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
CODEPAGE = 'ACP'
)
Usuwanie danych
Istnieją sytuacje, kiedy musimy usunąd częśd lub wszystkie informacje z bazy danych. Przykładem
może byd skasowanie błędnie wprowadzonych rekordów lub gdy chcemy pozbyd 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 [<NazwaSerwera>.][<NazwaBazyDanych>.]
<NazwaSchematu>.<NazwaTabeli>
WHERE <Warunki>
Wiersze można usunąd korzystając z dowolnych kolumn tabeli w warunku
WHERE.
Przykładowo:
DELATE FROM AdventureWorks.Person.Address
WHERE AddressID = 1
Warunek WHERE może zostad 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:
DELATE 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:
DELATE FOROM [AdventureWorks].[Production].[ProductInventory]
WHERE ProductID In
(
SELECT ProductID
FROM Production.Product
WHERE (MakeFlag = 1)
AND
(ReorderPoint BETWEEN 200 AND 600)
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 6
ITA-101 Bazy danych
Język SQL DML
Strona 7/19
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łao
definiowanych wewnątrz procedury składowanej. Przykładowo:
CREATE PROCEDURE [Sales].[CurrencyRate_Delate] @id int
AS
DELATE 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 [<NazwaSerwera>].[<BazaDanych>].[<Schemat>].[<NazwaTabeli>]
SET [<NazwaKolumny>] = <NowaWartosc>
WHERE <WarunkiWyszukiwania>
Polecenie
UPDATE
oczywiście można stosowad bez warunku
WHERE
.
Przykładowe rozwiązanie
Wprowadzanie danych
Żebyśmy mogli wybierad jakiekolwiek dane w pierwszym kroku powinniśmy naszą bazę danych
zasilid danymi. Załóżmy, że mamy tabelę
Ksiazki
w bazie danych
Biblioteka
. Tabela
Ksiazki
ma
strukturę tak jak pokazano na Rysunku 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 Rysunku 3.
Rys. 3 Tabela Ksiazki po wstawieniu rekordu
Ja widad 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, ze dane, które chcemy zaimportowad znajdują się w pliku
ksiazki.txt
, w postaci jak pokazano na Rysunku 4.
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 6
ITA-101 Bazy danych
Język SQL DML
Strona 8/19
Rys. 4 Dane zawart 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 na kolumnie
CD
mielibyśmy dane typu
booloen
.
Wówczas przy eksporcie danych do pliku wartośd logiczna
true
lub
false
zostałaby zamieniona na
wartośd tekstową
0
lub
1
. Jeżeli w kolejnym kroku chcielibyśmy zaimportowad 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 tryb tekstowy).
Wybieranie danych
Jeżeli nasza baza danych jest już uzupełniona danymi to w kolejnym kroku możemy zastanowid się
jakie dane mogą byd najczęściej wybierane przez użytkowników. Przykładowo dla tabeli Ksiazki
użytkownik najczęściej może chcied:
1. Wyszukiwad książki danego autora lub grupy autorów.
2. Wyszukiwad książki po wydawnictwie
3. Wyszukiwad książki po roku wydania
4. Wyszukiwad książki po informacji o płycie CD
5. Wyszukiwad po kombinacji autor, wydawnictwo, rok wydania i czy jest płyta CD
Przykładowo jeżeli chcielibyśmy znaleźd autora o nazwisku Kowalski wówczas instrukcja SELECT
może mied następującą postad:
SELECT Nazwisko, Imie, Tytul FROM Ksiazki
WHERE Nazwisko=’Kowalski’
Natomiast jeżeli chcielibyśmy znaleźd autorów o nazwisku Kowalski lub Andzioski wówczas
instrukcja SELECT może mied następującą postad:
SELECT Nazwisko, Imie, Tytul FROM Ksiazki
WHERE (Nazwisko=’Kowalski’) OR (Nazwisko=’Andziński’)
W sytuacji kiedy chcielibyśmy znaleźd autorów których nazwisko zawierają ciąg znaków ski
wówczas instrukcja SELECT może mied następującą postad:
SELECT Nazwisko, Imie, Tytul FROM Ksiazki
WHERE Nazwisko LIKE=’%ski’
Oczywiście możemy do tego dołączyd jeszcze wyszukiwanie po dacie wówczas otrzymamy:
SELECT Nazwisko, Imie, Tytul, Rok FROM Ksiazki
WHERE (Nazwisko LIKE=’%ski’) AND (Rok=’2003’)
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 6
ITA-101 Bazy danych
Język SQL DML
Strona 9/19
W sytuacji kiedy chcemy znaleźd autora o nazwisku zawierającym ciąg znaków ski lub o roku
wydania książki 2003, która zawiera płytę CD wówczas zapytanie będzie miało postad:
SELECT Nazwisko, Imie, Tytul, Rok, CD FROM Ksiazki
WHERE (Nazwisko LIKE=’%ski’ OR Rok=’2003’) AND (CD=’1’)
Najbardziej skomplikowane zapytanie występuje wówczas gdy chcemy znaleźd autora o nazwisku
zawierającym ciąg znaków ski lub o roku wydania książki 2003 która zawiera płytę CD i która
została wydana przez Helion, wówczas zapytanie będzie miało postad:
SELECT Nazwisko, Imie, Tytul, Rok, Wydawnictwo, CD FROM Ksiazki
WHERE (Nazwisko LIKE=’%ski’ OR Rok=’2003’) AND
(Wydawnictwo=’Helion’) AND (CD=’1’)
Porady praktyczne
Uwagi ogólne
Najlepszą kontrolę nad operacjami w bazie danych zapewniają procedury składowane. Zatem
lepiej jest umieszczad swoje uaktualnienia wewnątrz procedur składowanych. Pozwala
zwiększyd to zarówno wydajnośd jak i bezpieczeostwo. Korzystanie z procedur jest prostym
sposobem standaryzacji i wywołao z aplikacji.
Tworzenie dynamicznych poleceo wstawiania może zagrażad bezpieczeostwu bazy danych,
dlatego wielce prawdopodobnie jest, że administrator bazy danych będzie preferował
zabezpieczenia tabel przez odmowę uprawnieo zapisu do tabeli. Z tego powodu dobrym
sposobem jest zarządzanie operacjami wstawiania 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 spowodowad sporo problemów. Przykładowo:
a) Aplikacja może działad nieprawidłowo. Po dodaniu nowych kolumn do bazy te
nieoczekiwane kolumny mogą również byd odczytywane przez aplikacje. W konsekwencji
mogą zostad niepoprawnie obsłużone.
b) Jeśli zostaną wybrane wszystkie kolumny, optymalizator zapytao nie będzie używał
niektórych indeksów.
W przypadku, kiedy zajdzie potrzeba wykonania zapytania SELECT z połączeniem operatorów
logicznych AND i OR w celu otrzymania wymaganych danych należy pamiętad o zastosowaniu
nawiasów. Pozwalają one precyzyjnie określid kolejnośd podczas łącznia operatorów AND i
OR.
W wielu przypadkach możemy na wiele sposobów otrzymad tan sam efekt wywołania
zapytania. Poniżej mamy dwa zapytania, które 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 tych zapytao odczytuje datę i miesiąc. Po odczytaniu wszystkich wierszy wykonuje
obliczenia i je porównuje. Drugie zapytanie wykonuje tylko porównanie, bez obliczeo,
ponadto jeżeli na kolumnie ShipDate znajduje się indeks, to zostanie on użyty tylko w drugim
zapytaniu – a zatem zapytanie drugie jest szybsze.
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 6
ITA-101 Bazy danych
Język SQL DML
Strona 10/19
Można używad jednej lub wszystkich kolumn do tworzenia warunku WHERE. Wydajnośd
warunku WHERE można poprawid poprzez:
a) zastosowanie klucza głównego w warunku
b) użycie kolumn, które są indeksowane
Wprowadzanie, usuwanie i uaktualnianie danych
Aby skasowad wszystkie wiersze z tabeli, zamiast instrukcji DELATE można użyd instrukcji
TRUNCATE TABLE. Różnica w stosunku do instrukcji DELATE polega na tym, iż zadanie
kasowania odbywa się szybciej. Używając TRUNCATE TABLE trzeba mied pewnośd, że tabela
nie ma żadnych związków z innymi tabelami.
Możne także usunąd wiersze z tabeli za pośrednictwem widoków, jednak występują tutaj
pewne ograniczenia:
a) Można usunąd wiersze tylko z jednej tabeli.
b) Połączony użytkownik musi posiadad uprawnienia do usuwania dla widoku.
Można także wykonywad 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) Połączony użytkownik musi mied uprawnienia do zapisu do widoku.
Podobnie jak w przypadku instrukcji DELATE, można używad dowolnych lub wszystkich
kolumn tabeli. W celu zwiększenia wydajności polecenia UPDATE, należy:
a) W warunku należy korzystad z kolumn klucza głównego
b) Korzystad z kolumn należących do indeksów.
Uwagi dla studenta
Jesteś przygotowany do realizacji laboratorium jeśli:
rozumiesz składnię języka T-SQL DML
umiesz skonstruowad podstawowe zapytania do bazy danych
umiesz zaimportowad dane do swojej bazy danych
umiesz modyfikowad 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 pobierad dane z instancji
SQL Server za pomocą języka T-SQL. Pozycja polecana dla osób, które chciałyby
dowiedzied 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 odczytywad dane z SQL Server, jak je wybierad, modyfikowad, usuwad i
uaktualniad. Książka oprócz teorii obdarzona dużą liczbą przykładów.
3. Deren Bieniek, Randy Dyess, Mike Hotek, Javier Loria, Adam Machanic, Antonio Soto, Adolfo
Wiernik, SQL Server 2005 Implemantacja i obsuga, APN Promise, Warsyawa 2006
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 6
ITA-101 Bazy danych
Język SQL DML
Strona 11/19
W książce przedstawiono prac e z językiem T-SQL: DML. Pokazano w jaki sposób
tworzyd zapytania formatowad zestaw wyników oraz modyfikowad dane. Książka
szczególnie polecana ze względu na dużą zawartośd dwiczeo laboratoryjnych.
4. Dusan Petkovic, Microsoft SQL Server 2008: A Beginner's Guide, McGraw-Hill, 2008
Pozycja napisana w sposób prosty. 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 tworzyd zapytania, formatowad wyniki zapytao oraz modyfikowad dane.
Pozycja szczególnie polecana dla osób początkujących.
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 6
ITA-101 Bazy danych
Język SQL DML
Strona 12/19
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 National Insurance zarządzająca systemem prac dyplomowych na twoim wydziale planuje
rozszerzenie systemu na skalę uczelnianą. Zadanie, jakie przed Tobą stoi, to zdefiniowanie nowych
zapytao, które najczęściej będą wykonywali użytkownicy na twojej bazie danych.
Zadanie
Tok postępowania
1. Nawiązywanie
połączenia 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 MS 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. Tworzenie
tabeli testowej
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. Zapoznanie 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. 2.
-- (2) Wyswietl cala zawartosc tabeli Osoba_kopia
SELECT *
FROM Osoba_kopia
GO
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 6
ITA-101 Bazy danych
Język SQL DML
Strona 13/19
Rys. 2 Wynik wyświetlenia całej zawartości tabeli Osoab_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. 3
-- (3) Wybierz kolumny, ktore chcemy wyswietlic
SELECT Nazwisko, Imie
FROM Osoba_kopia
GO
Rys. 3 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. 4.
-- (4) Dodajemy literal i alias
SELECT 'Imie i nazwisko: ' + Nazwisko + ' ' + Imie AS Osoba
FROM Osoba_kopia
GO
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 6
ITA-101 Bazy danych
Język SQL DML
Strona 14/19
Rys. 4 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; nigdzie nie zapisywany
na stałe) nagłówek kolumny. Może zawierad 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. 5.
-- (5) Nie wyswietlajmy duplikatow
SELECT DISTINCT Imie
FROM Osoba_kopia
GO
Rys. 5 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 o pustych wszystkich polach, 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. 5.
-- (6) sortujmy rekordy rosnaco (ASC) i malejaco (DESC)
SELECT Imie, nazwisko Nr_Indeksu
FROM Osoba_kopia ORDER BY Nr_Indeksu – DESC
GO
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 6
ITA-101 Bazy danych
Język SQL DML
Strona 15/19
Rys. 6 Wynik sortowania
Zaznacz i uruchom (F5) poniższy fragment kodu odpowiedzialny za
ogranicenie liczny rekordów. Wynik działania pokazano na Rys. 5.
-- (7) ograniczmy liczbe rekordow
SELECT Imie, Nazwisko
FROM Osoba_kopia
WHERE Nazwisko = 'Rawa'
GO
Rys. 7 Wynik ograniczenia liczby rekordów
Zaznacz i uruchom (F5) poniższy fragment kodu odpowiedzialny za
ogranicenie liczny rekordów z użyciem operatora wiekszości. Wynik
działania pokazano na Rys. 5.
-- (8) wyprobujmy operator wiekszosci
SELECT Imie, Nazwisko, Nr_Indeksu
FROM Osoba_kopia
WHERE Nr_Indeksu >= 110
GO
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 6
ITA-101 Bazy danych
Język SQL DML
Strona 16/19
Rys. 8 Wynik ograniczenia liczby rekordów z uzyciem operatora większości
Zaznacz i uruchom (F5) poniższy fragment kodu odpowiedzialny za
ogranicenie liczny rekordów z użyciem operatora OR. Wynik działania
pokazano na Rys. 5.
-- (9) wyprobujmy operator OR
SELECT Imie, Nazwisko, Nr_Indeksu
FROM Osoba_kopia
WHERE Nr_Indeksu < 110 OR Nr_Indeksu >= 115
GO
Rys. 9 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. 5.
-- (10) wyszukajmy w zbiorze wartosci
SELECT Imie, Nazwisko
FROM Osoba_kopia
WHERE Nazwisko IN ('Rawa','Nowak')
GO
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 6
ITA-101 Bazy danych
Język SQL DML
Strona 17/19
Rys. 10 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. 5.
-- (11) wyszukajmy w przedziale wartosci
SELECT Imie, Nazwisko, Nr_Indeksu
FROM Osoba_kopia
WHERE Nr_Indeksu BETWEEN 110 AND 115
GO
Rys. 11 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. 5.
-- (12) uzyjmy operatora LIKE
SELECT Imie, Nazwisko, Nr_Indeksu
FROM Osoba_kopia
WHERE Nazwisko LIKE '[A-F]%'
GO
Rys. 12 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. 5.
-- (13) zliczmy rekordy w tabeli
SELECT Count(*) AS [Liczba osob]
FROM Osoba_kopia
GO
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 6
ITA-101 Bazy danych
Język SQL DML
Strona 18/19
Rys. 13 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
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 6
ITA-101 Bazy danych
Język SQL DML
Strona 19/19
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ą zostad przebudowane
zapytania wysyłane do obiektów bazy danych. W związku z tym częśd zapytao powinna zostad
usunięta, częśd zmodyfikowana, a częśd od nowa utworzona.
Zadania, jakie przed Tobą stoją, to:
1. Podjęcie decyzji w jaki sposób usprawnid proces importu wielu milionów danych do systemu
bazodanowego.
1. Podjęcie, decyzji które zapytania w bazie danych pozostaną bez zmian, a które zostaną
zmodyfikowane lub usunięte.
2. Podjęcie decyzji, jakie nowe zapytania powinny zostad utworzone.