Jzyki baz danych FJzyk definiowania danych Data Definition

Języki baz danych FJęzyk definiowania danych (Data Definition Language – DLL), który umożliwia definiowanie struktury danych przechowywanych w bazie, czyli tworzenie schematu implementacyjnego; FJęzyk manipulowania danymi (Data Manipulaition Language – DML), który umożliwia wypełnianie, modyfikowanie i usuwanie informacji z bazy danych; FJęzyk sterowania danymi (Data Control Language – DCL), który umożliwia sterowanie transakcjami; FJęzyk zapytań (Qery Language), który umożliwia pobieranie z bazy informacji zgodnie z podanymi warunkami. Języki baz danych 2

Język SQL (Structured Query Language) to strukturalny język zapytań używany do tworzenia, modyfikowania baz danych oraz do umieszczania i pobierania danych z baz danych. Język SQL jest językiem deklaratywnym. § Oznacza to, że zamiast określać wprost co należy zrobić, aby otrzymać interesujący wynik, określa się tylko jaki wynik chce się uzyskać, a za dobór działań niezbędnych do wygenerowania tego wyniku odpowiada sam język. § Decyzję o sposobie przechowywania i pobrania danych pozostawia się systemowi zarządzania bazą DBMS. Języki baz danych 3

Historia SQL • • • Jest to język programowania opracowany w latach siedemdziesiątych w firmie IBM dla bazy DB 2. Stał się on standardem w komunikacji z serwerami relacyjnych baz danych. Wiele współczesnych systemów relacyjnych baz danych używa do komunikacji z użytkownikiem SQL. Pierwszą firmą, która włączyła SQL do swojego produktu komercyjnego, był Oracle. Wprowadzanie SQL-a, w produktach innych firm, wiązało się z wprowadzaniem modyfikacji pierwotnego języka i wkrótce utrzymanie dalszej jednolitości języka wymagało wprowadzenia standardu. Języki baz danych 4

Standardy SQL • • W 1986 roku SQL stał się oficjalnym standardem, wspieranym przez Międzynarodową Organizację Normalizacyjną (ISO) i jej członka, Amerykański Narodowy Instytut Normalizacji (ANSI). Wczesne wersje specyfikacji (SQL 86 i SQL 89) były w dużej mierze jedynie określeniem wspólnej płaszczyzny łączącej różne istniejące wówczas produkty. Z czasem pojawiła się potrzeba określenia standardu ściślejszego i tak powstał SQL 92 i następnie SQL 99 W roku 2003 przedstawiono nowy standard - SQL: 2003. Języki baz danych 5

SQL: 2003 • • Jest to głównie poprawione SQL 99 z wyjątkiem części SQL/XML oraz kilku dodatkowych właściwości. W SQL: 2003 wprowadzono: § nowe typy danych (BIGINT, MULTISET oraz XML) § rozszerzenia w sposobie wywoływania procedur § poszerzono instrukcję CREATE TABLE § wprowadzono instrukcję MERGE § wprowadzono nowy obiekt schematu – generator sekwencji § wprowadzono dwa nowe typy kolumn § retrospektywne sprawdzanie więzów integralności Języki baz danych 6

Formy SQL • Z technicznego punktu widzenia, SQL jest podjęzykiem danych. o Oznacza to, że jest on wykorzystywany wyłącznie do komunikacji z bazą danych. o Nie posiada on cech pozwalających na tworzenie kompletnych programów. o Jego wykorzystanie może być trojakie i z tego względu wyróżnia się trzy formy SQL-a: Języki baz danych 7

Formy SQL interakcyjny - wykorzystywany przez użytkowników w celu bezpośredniego pobierania lub wprowadzania informacji do bazy. SQL statyczny (Static SQL) - pisany jest wraz z całą aplikacją, podczas której pracy jest wykorzystywany. Statyczny SQL występuje w dwóch odmianach § Embedded SQL (osadzony) oznacza włączenie kodu SQL do kodu źródłowego innego języka. § Język modułów - moduły SQL łączone są z modułami kodu w innym języku. Języki baz danych 8

Formy SQL dynamiczny (Dynamic SQL) - generowany jest w trakcie pracy aplikacji. § Tę formę SQL generują przede wszystkim takie narzędzia jak graficzne języki zapytań. § Utworzenie odpowiedniego zapytania jest tu odpowiedzią na działania użytkownika. Języki baz danych 9

Składnia SQL • Użycie SQL, zgodnie z jego nazwą, polega na zadawaniu zapytań do bazy danych. • Instrukcje SQL w obrębie zapytań tradycyjnie zapisywane są wielkimi literami (nie jest to wymóg). • Każde zapytanie w SQL-u musi kończyć się znakiem "; " (średnik). • Dodatkowo, niektóre interpretery SQL (np. psql w przypadku Postgre. SQL), używają swoich własnych instrukcji, spoza standardu SQL, które służą np. do połączenia się z bazą, wyświetlenia dokumentacji, itp. Języki baz danych 10

Zapytania SQL • Zapytania można zaliczyć do jednego z dwóch głównych podzbiorów: § SQL DML (Data Manipulation Language, czyli Język Manipulacji Danymi), § SQL DDL (Data Definition Language, czyli Język Definicji Danych). Języki baz danych 11

SQL DML • • DML służy do operacji na danych - do ich umieszczania w bazie, kasowania, przeglądania, zmiany. Najważniejsze polecenia z tego zbioru to: § SELECT - pobranie z bazy danych, § INSERT - umieszczenie danych w bazie, § UPDATE - zmiana danych, § DELETE - usunięcie danych z bazy. • Dane tekstowe podawane muszą być zawsze w formie ograniczonej znakami pojedynczego cudzysłowu ('). Języki baz danych 12

SQL DDL • • DDL służy do operowania na strukturach, w których te dane są przechowywane - pozwala np. dodawać, zmieniać i kasować tabele lub bazy. Najważniejsze polecenia z tego zbioru to: § CREATE - utworzenie struktury (bazy, tabeli, indeksu, itp. ), § DROP - całkowite usunięcie struktury, § ALTER - zmiana struktury (dodanie kolumny do tabeli, zmiana typu danych w kolumnie tabeli). Języki baz danych 13

Podstawowe rozkazy języka SQL Rozkaz Typ Opis ALTER TABLE DDL Dodaje kolumnę do tabeli, redefiniuje kolumnę lub redefiniuje ilość miejsca rezerwowanego dla danych. CREATE INDEX DDL Tworzy indeks dla tabeli. CREATE SEQUENCE DDL Tworzy obiekt służący do generowania kolejnych liczb CREATE TABLE DDL Tworzy tabelę i definiuje jej kolumny oraz alokację przestrzeni dla danych. CREATE VIEW DDL Definiuje widok dla jednej lub większej ilości tabel, lub innych widoków. DELETE DML Usuwa wszystkie lub wyróżnione DROP DDL Języki baz danych wiersze z tabeli. Usuwa indeks, sekwencję, tablicę, widok lub inny obiekt. 14

Podstawowe rozkazy języka SQL Rozkaz Typ INSERT DML Dodaje nowy wiersz (lub wiersze) do RENAME DDL SELECT DML Wybiera wiersze i kolumny z jednej lub UPDATE DML Zmienia dane w tabeli COMMIT DML Kończy transakcję i na stałe zapisuje ROLLBACK DML Wycofuje wprowadzone zmiany dla SAVEPOINT DML Zaznacza punkt do którego będzie SET TRANSACTION DML Zaznacza aktualną transakcje jako Języki baz danych Opis tabeli lub widoku. Zmienia nazwę tabeli, widoku lub innego obiektu kilku tabel. wprowadzone zmiany. całej lub tylko części transakcji. wykonywany rozkaz ROLLBACK. „tylko do odczytu”. 15

Elementy składni poleceń SQL – identyfikatory • Identyfikatory (nazwy) - ciągi liter, cyfr i znaków podkreślenia rozpoczynający się literą lub znakiem podkreślenia. Ø Identyfikator jest czymś, co jednoznacznie definiuje jakiś element systemu bazy danych (np. nazwa bazy danych, nazwa tabeli, nazwa pola) Przykład: Identyfikatorem nowo utworzonej tabeli Sprzedawcy jest jej nazwa (czyli Sprzedawcy). Identyfikatorem pola w tej tabeli jest między innymi nazwa Imie. Sprzedawcy. Odpowiednie odwołane do tego pola przyjmie postać: SELECT Imie. Sprzedawcy FROM Sprzedawcy; Języki baz danych 16

Elementy składni poleceń SQL - literały • Literały – literał jest faktyczną wartością, np. 120, Krzysztof lub 10 stycznia 2007. Przykład: Jeżeli chcemy uzyskać listę imion i nazwisk wszystkich sprzedawców o imieniu „Krzysztof”, zapisanych w tabeli Sprzedawcy, to możemy użyć następującej konstrukcji: SELECT Imie. Sprzedawcy, Nazwisko. Sprzedawcy FROM Sprzedawcy; WHERE Imie. Sprzedawcy = ‘Krzysztof’; Języki baz danych 17

Elementy składni poleceń SQL – liczby • • • Liczby – mogą być rzeczywiste lub całkowite (bez kropki dziesiętnej). W systemie ORACLE liczby rzeczywiste można zapisywać w formacie zwykłym lub wykładniczym. Możliwe też jest zakończenie liczby całkowitej literą „K” (razy 1 KB=1024) lub literą „M” (razy 1 MB=1048576). Języki baz danych 18

Elementy składni poleceń SQL – słowa kluczowe • Słowa kluczowe – słowo kluczowe jest wyrazem, który ma konkretne znaczenie dla systemu bazy danych przetwarzającego całe wyrażenie. § Każde słowo kluczowe ma przypisane pewne reguły określające jego przeznaczenie, możliwość wystąpienia oraz pozycję w składni polecenia. Przykład: SELECT Nr. Rej FROM Samochody WHERE Marka = ‘Ford’; lub Języki baz danych SELECT Wlasciciel FROM Samochody WHERE Marka = ‘Opel’. AND. Model = ‘Astra’; 19

Słowa kluczowe access add all alter and any as asc audit between by char check cluster column comment compress connect create current date dba decimal default delete desc distinct drop else exclusive exists file float for from grant graphic group having identified if immediate in increment index install initial insert integer intersect into is level like lock long maxextents minus mode modify Języki baz danych 20

Słowa kluczowe noaudit nocompress not nowait null number of offline on online option or order pctfree prior privileges public raw rename resource revoke rowid rownum rows select session set share size smallint start successful synonym sysdate table then to trigger uid union unique update user validate values varchar vargraphic view where with Języki baz danych 21

Tworzenie danych • Istnieją dwa główne sposoby tworzenia bazy danych: § Wiele RSZB (Microsoft Access, Microsoft SQL Server, Oracle czy IBM DB 2) udostępnia przyjazne użytkownikowi interfejsy frontów, które czynią proces tworzenia bazy danych bardzo prostym. § System My. SQL wprawdzie nie posiada domyślnego frontu, istnieje jednak mnóstwo darmowych narzędzi tego typu, np. My. SQL Control Center. § Pozostałe relacyjne systemy zarządzania bazami danych oferują możliwość używania do tego celu języka SQL. Języki baz danych 22

Tworzenie bazy danych CREATE DATABASE Moja. Pierwsza. Baza; • Istnieją pewne ograniczenia odnośnie stosowanych nazw (np. dotyczące długości). § W systemie DB 2 długość nazwy jest ograniczona do ośmiu znaków, § W systemie SQL Server do 123 znaków. § Zawsze bezpieczniejszym rozwiązaniem jest stosowanie w nazwach liter, cyfr i znaku podkreślenia (unikając jakichkolwiek znaków interpunkcyjnych, symboli, znaków narodowych lub sterujących – interpretowanych przez system). Języki baz danych 23

Usuwanie bazy danych DROP DATABASE Moja. Pierwsza. Baza; • Usunięcie bazy danych z RSZB może spowodować trwałą utratę wszystkich zapisanych w niej informacji. § W systemie Oracle zastosowano nieco inne podejście do procesu usuwania bazy danych – zamiast wywoływać polecenie DROP DATABASE, należy jeszcze raz poleceniem CREATE DATABASE utworzyć nową bazę danych o takiej samej nazwie. Języki baz danych 24

Typy danych • W przypadku baz danych pojęcie typu danych odnosi się do klasyfikacji rodzajów składowanej informacji niezależnie od tego czy są to liczby, znaki czy daty. § Ułatwia to systemom baz danych interpretowanie i właściwe przetwarzanie wstawianych wartości oraz poprawia efektywność przetwarzania. § Typy danych pozwalają także relacyjnemu systemowi zarządzania bazą danych określić zbiór możliwych do wykonania operacji na składowanych informacjach. Języki baz danych 25

Typy danych ANSI SQL MS ACCESS SQL SERVWR IBM DB 2 My. SQL ORACLE 10 Znakowy char char Znakowy o zmiennej długości varchar varchar Regionalny znakowy char nchar graphic char nchar Reg. znakowy o zmiennej długości varchar nvarchar vargraphic varchar nvarchar Całkowitoliczbowy long integer int int Mały całkowitoliczbowy integer smallint Rzeczywisty double real Dziesiętny decimal decimal Daty datetime date Czasu time datetime Języki baz danych 26

Tworzenie tabeli • Do tworzenia tabel służy wyrażenie CREATE TABLE. § Najprostszy sposób tworzenia tabeli wymaga podania jej nazwy oraz zdefiniowania odpowiednich kolumn wraz z określeniem typu danych. CREATE TABLE nazwa_tabeli ( nazwa_kolumny typ_danych_kolumny ); Przykład: CREATE TABLE Rozkład_jazdy ( miejsce_wyjazdu varchar(75), miejsce_przyjazdu varchar(75), godzina_wyjazdu time, godzina_przyjazdu time ); Języki baz danych 27

Tworzenie tabel • Inne sposoby tworzenia tabel to: § poprzez skopiowanie struktury istniejącej tabeli; COPY STRUCTURE TO Table. Name [FIELDS Field. List]; § utworzenie struktury na podstawie informacji zapisanych w tablicy. CREATE TABLE Table. Name FROM ARRAY Array. Name ; Języki baz danych 28

Tworzenie tabeli CREATE TABLE | DBF Table. Name 1 [NAME Long. Table. Name] [FREE] ( Field. Name 1 Field. Type [( n. Field. Width [ n. Precision] )] [NULL | NOT NULL] [CHECK Expression 1 [ERROR c. Message. Text 1]] [AUTOINC [NEXTVALUE Next. Value [STE Step. Value]]] [DEFAULT e. Expression 1] [PRIMARY KEY | UNIQUE [COLLATE c. Collate. Sequence]] [REFERENCES Table. Name 2 [TAG Tag. Name 1]] [NOCPTRANS] [, Field. Name 2. . . ] [, PRIMARY KEY e. Expression 2 TAG Tag. Name 2 |, UNIQUE e. Expression 3 TAG Tag. Name 3 [COLLATE c. Collate. Sequence]] [, FOREIGN KEY e. Expression 4 TAG Tag. Name 4 [NODUP] [COLLATE c. Collate. Sequence] REFERENCES Table. Name 3 [TAG Tag. Name 5]] [, CHECK l. Expression 2 [ERROR c. Message. Text 2]] ) | FROM ARRAY Array. Name Języki baz danych 29

Modyfikacja tabeli • Kluczem do modyfikowania istniejącej tabeli jest wyrażenie ALTER TABLE. § Za jego pomocą można dodawać i usuwać kolumny w istniejącej tabeli. § Standard ANSI SQL nie dopuszcza wykorzystywania tego wyrażenia do zmiany typu danych, jednak wiele relacyjnych systemów zarządzania bazą danych zaimplementowało mechanizmy modyfikacji definicji kolumn. Języki baz danych 30

Modyfikacja tabeli § dodanie nowej kolumny do tabeli: ALTER TABLE Nazwa_Tabeli ADD Nazwa_Pola Typ_Danych; Przykład: ALTER TABLE Rozklad_jazdy ADD Cena_biletu DECIMAL(7, 2); § usunięcie kolumny z tabeli: ALTER TABLE Nazwa_Tabeli DROP COLUMN Nazwa_Pola; Przykład: ALTER TABLE Rozklad_jazdy DROP COLUMN Cena_biletu; Języki baz danych 31

Usunięcie istniejącej tabeli • Do usuwania tabel służy wyrażenie DROP TABLE Nazwa_Tabeli; Przykład: DROP TABLE Rozklad_jazdy; Języki baz danych 32

Wstawianie nowych danych do tabeli • Do wstawiania nowych danych do tabel służy wyrażenie INSERT INTO. § W składni wyrażenia należy wskazać tabele, nazwy kolumn do których będą wprowadzane dane oraz wartości tych danych. INSERT INTO Nazwa_Tabeli (nazwy_kolumn) VALUES (wartości); Przykład: INSERT INTO Rozklad_jazdy (miejsce_wyjazdu, miejsce_przyjazdu) VALUES (‘Olsztyn’, ‘Warszawa’); Języki baz danych 33

Wstawianie nowych danych do tabeli § Warianty składni INSERT INTO: INSERT INTO dbf_name [(fname 1 [, fname 2, . . . ])] VALUES (e. Expression 1 [, e. Expression 2, . . . ]) INSERT INTO dbf_name FROM ARRAY Array. Name | FROM MEMVAR | FROM NAME Object. Name INSERT INTO dbf_name [(fname 1 [, fname 2, . . . ])] SELECT [(fname 1 [, fname 2, . . . ])] FROM tablename WHERE condition Języki baz danych 34

Aktualizowanie danych w tabeli • Do aktualizacji rekordu służy polecenie UPDATE. § Wystąpienie w składni polecenia klauzuli WHERE umożliwia odniesienie polecenia tylko do tych rekordów tabeli, dla których warunek klauzuli jest prawdziwy. UPDATE Nazwa_Tabeli SET nazwy_kolumn = wartość WHERE warunek; Przykład: UPDATE Rozklad_jazdy SET miejsce_wyjazdu = ‘Dworzec Olsztyn’ WHERE miejsce_wyjazdu = ‘Olsztyn’; Języki baz danych 35

Aktualizowanie danych w tabeli • • Operatory relacyjne klauzuli WHERE: = równy; <> różny; > większy niż; < mniejszy niż; >= nie mniejszy; <= nie większy; Operatory logiczne klauzuli WHERE: ADN koniunkcja; OR alternatywa Przykład: UPDATE Kursanci SET programowanie = ‘zal. ‘ WHERE ocena_progr >= 3 ; UPDATE Adresy SET wojewodztwo = ‘podlaskie ‘ WHERE wojewodztwo =‘białostockie’ OR ‘suwalskie’ ; Języki baz danych 36

Usuwanie danych z tabeli • • W tym celu SQL wykorzystuje polecenie DELETE. Składnia polecenia jest zależna od zakresu kasowania: § usunięcie wszystkich rekordów tabeli § usuniecie tylko rekordów spełniających warunki klauzuli WHERE. DELETE FROM [Database. Name!] Table. Name [WHERE Filter. Condition 1 [AND | OR Filter. Condition 2. . . ]] ; Przykład: DELETE FROM Rozklad_jazdy WHERE miejsce_wyjazdu = ‘Olsztyn’ AND miejsce_przyjazdu =‘Warszawa’; Języki baz danych 37

Pobieranie danych z tabeli • W najprostszej postaci wyrażenie SELECT wymaga tylko wskazania nazwy tabeli i nazw kolumn z których maja być pobierane dane: SELECT kolumna 1, kolumna 2, … , kolumnax FROM nazwa_tabeli ; • Użycie klauzuli WHERE pozwala na wydobywanie informacji tylko z rekordów spełniających warunki klauzuli: SELECT kolumna 1, kolumna 2, … , kolumnax FROM nazwa_tabeli WHERE warunek; • Jeżeli zakres udostępniana dotyczy wszystkich kolumn: SELECT * FROM nazwa_tab WHERE warunek; Języki baz danych 38

Stosowanie aliasów i filtrowanie danych • Alias jest drugą (zastępczą) nazwą symboliczną dla określonego zbioru danych. SELECT kolumnax AS nazwa_zast_kolumnyx FROM nazwa_tabeli ; • Udostępniane dane można „odfiltrować” klauzulą WHERE Ä będą dostępne tylko te dane, które spełniają warunek określony w klauzuli: WHERE warunek_logiczny ; Przykład: WHERE Data_urodzenia <# 1987 -12 -24# ; Języki baz danych 39

Operatory logiczne • Operator NOT to operator negacji, zmieniający wartość wyrażenia logicznego na przeciwną: Przykład: SELECT Imie FROM Dane_Osobowe WHERE Nazwisko NOT ‘Kowalski’ ; Języki baz danych 40

Operatory logiczne • Operator BETWEEN umożliwia określenie przedziału do którego muszą należeć akceptowane wartości. Przykład: SELECT Imie, Nazwisko FROM Uczniowie WHERE Ocena BETWEEN 3 AND 5 ; • Operator LIKE umożliwia stosowanie symboli wieloznacznych w warunkach przeszukujących pola znakowe § % - dopasowuje jeden lub wiele znaków (* - Access) § _ - dopasowuje tylko jeden znak (? – Access) Przykład: SELECT Imie, Nazwisko FROM Uczniowie WHERE Nazwisko LIKE ‘K% ‘ ; Języki baz danych 41

Operatory logiczne • Operator IN pozwala sprawdzić czy wartość pola jest zgodna z którąkolwiek pozycją listy Przykład: SELECT Imie, Nazwisko FROM Studenci WHERE Miasto IN (‘Olsztyn‘, ‘Gdańsk‘, ‘Elbląg‘) ; Języki baz danych 42

Porządkowanie danych wynikowych • Jeżeli zachodzi potrzeba uporządkowania danych wynikowych zgodnie z założonym porządkiem sortowania, to można użyć w tym celu klauzuli ORDER BY Przykład: SELECT Imie, Nazwisko FROM Studenci ORDER BY Nazwisko, Imie ; • Klauzula GROUP BY pozwala z kolei na określenie kolejności grupowania Przykład: SELECT Nazwisko, Imie, Data_Ur FROM Studenci WHERE Data_Ur BETWEEN ‘ 1987 -01 -01’ AND ‘ 1987 -31 -12’ GROUP BY Imie, Nazwisko, Data_Ur ; Języki baz danych 43

Łączenie kolumn – konkatenacja • W systemach SQL Server i Access operatorem konkatenacji jest znak „+” Przykład: SELECT Imie + ’ ‘ + Nazwisko AS Dane_Personalne FROM Studenci ; • W systemach Oracle i IBM DB 2 konkatenację można wykonać stosując operator „||”, lub wywołując funkcję CONCAT( ) Przykład: SELECT Imie || ’ ‘ || Nazwisko AS Dane_Personalne FROM Studenci ; SELECT CONCAT( Imie, ’ ‘ , Nazwisko) AS Dane_Personalne FROM Studenci ; Języki baz danych 44

Wydobywanie danych z wielu tabel – złączenie • • Naturalnym typem złączenia jest złączenie wewnętrzne. • Wskazanie złączanych tabel odbywa się w języku SQL przy użyciu słowa kluczowego INNER Złączenie wewnętrzne tworzy kombinację dwu tabel i wiąże je (złącza) na podstawie wartości ich kolumn. Przykład: SELECT Imie, Nazwisko, Bazy_Danych FROM Studenci INNER JOIN Oceny ON Id_Osoby = Id_Studenta WHERE Semestr = 7 ; Języki baz danych 45

Wydobywanie danych z wielu tabel – złączenie • W przypadku jednoczesnego pobierania danych z wielu tabel może pojawić się problem z jednoznacznym określeniem przynależności kolumny do tabeli (np. gdy w dwu tabelach zastosowano takie same nazwy niektórych kolumn. • W takich przypadkach rozsądnym rozwiązaniem jest poprzedzenie nazwy kolumny nazwą tabeli, rozdzielając obie nazwy znakiem „. ” (kropka) Przykład: SELECT Studenci. Imie, Studenci. Nazwisko, Oceny. Bazy FROM Studenci INNER JOIN Oceny ON Studenci. Id_Osoby = Oceny. Id_Osoby WRERE Oceny. Semestr = 7 ; Języki baz danych 46

Wartość NULL • Język SQL nie dopuszcza możliwości braku wartości w istniejących danych. • Pola bez określonej wartości i tak muszą zawierać jakąś wartość – tzw. wartość nieznaną, czyli NULL. • Sprawdzenie, czy poszczególne pola zawierają wartość NULL, wymaga stosowania operatora IS NULL, z kolei aby upewnić się, że dane pole ma wartość różną od NULL należy użyć operatora IS NOT NULL Przykład: SELECT miejsce_wyjazdu, miejsce_przyjazdu FROM Rozklad_jazdy WHERE cena_biletu IS NULL ; Języki baz danych 47