Zapytania w języku SQL ją esencją zarządzania bazami danych. 

W poprzednim wpisie została przedstawiona idea relacji pomiędzy bazami. Na serwerze natywnych została uruchomiona baza BazaT.  Baza dotyczy spedycji transportowej operującej na terenie Warszawy.

Przytoczyłem przykład złożonego zapytania w języku SQL:

Aby dowiedzieć się jak nazywała się firma, która dokonała konkretnej dostawy należy odwołać się z tabeli trasy do tabeli Samochody po kluczu Id_samochodu. Następnie trzeba z tabeli Samochody po kluczy Id_firmy przejść do tabeli Firmy i dopiero stamtąd odczytać nazwę firmy, która wykonała usługę.

Schemat takiego zapytania przytaczam poniżej:

W tym wpisie zapytanie to zostanie zrealizowane.

 Tworzenie zapytania w języku SQL 

Proszę w oknie poleceń wpisać instrukcję SELECT * FROM Firmy następnie proszę nacisnąć klawisz: Execute.

 Wyrażenie SELECT FROM jest podstawowym związkiem zapytania w języku SQL 

Teraz będę rozbudowywał i jednocześnie zawężał to polecenie. Instrukcja SELECT mówi, co ma zostać wyświetlone. Gwiazdka * oznacza wszystkie kolumny tabeli.  Klauzula FROM mówi skąd mają pochodzić dane. To wyrażenie wyświetla wszystkie dane z tabeli Firmy:

SELECT * FROM Firmy

Zadaniem jest przyporządkować wykonane trasy do nazw firm, do której należały ciężarówki.

Proszę wpisać następującą instrukcję:

SELECT * FROM trasy

Zastała wyświetlona cała tabela trasy. Z pośród wszystkich pól tylko po kolumnie Id_samochodu można dojść do informacji, jakie firmy wykonała kolejne usługi transportowe.

Proszę wyświetlić samą kolumnę Id_samochodu z tabeli trasy:

SELECT Id_samochodu FROM trasy

Program podpowie nazwę kolumny, ponieważ, wie, z której tabeli pobierane są dane.

Teraz zostanie druga tabela bazy: Samochody.

SELECT [Id_samochodu] FROM trasy, Samochody

Została wyświetlona informacja o dwuznaczności kolumny Id_samochodu.  Część odmian języków SQL interpretuje taką sytuację, jako błąd. Mimo, że kolumna Id_samochodu występuje zarówno w tabeli Trasy jak i w tabeli Samochody (obie kolumny są identyczne), SQL nie wie, którą kolumnę ma wyświetlić.

Proszę zadeklarować, że ma zostać wyświetlona kolumna Id_samochodu z tabeli trasy.

SELECT trasy.Id_samochodu FROM trasy, Samochody

Teraz dołączamy kolumnę z tabeli samochody, uwaga na przecinek między nazwami kolumn.

SELECT trasy.Id_samochodu, Id_firmy FROM trasy, Samochody

Zapytanie wyświetliło 198198 rekordów. Jest to przykład iloczynu kartezjańskiego. Z zapytania nie wynikało, że tabele są ze sobą połączone kluczem Id_samochodu. Wynik to wszystkie pozycje tabeli trasy (1573 wiersze) ze wszystkimi pozycjami tabeli Samochody (126 wierszy). Iloczyn kartezjański obu tabel wynosi 198198. Jest to błąd zapytania, należy wskazać, że kolumna Id_samochodu jest kolumną wspólną, że jest kluczem łączącym.

SELECT trasy.Id_samochodu, Id_firmy FROM trasy, Samochody

WHERE trasy.Id_samochodu = Samochody.Id_samochodu

Klauzula WHERE wskazała, że obie kolumny są identyczne. Zostało wyświetlonych 1573 wiersze. Obie tabele zostały połączone.

Niestety w tabeli nie ma informacji o trasach, jakie obsługiwały samochody.

Proszę dopisać do raportu kolumny z tabeli trasy: Id_trasy, km,data_wyjazdu, Wynagrodzenie, Trasy oraz kolumny z tabeli Samochody: Num_rejestr, Marka.

SELECT trasy.Id_samochodu,

Id_trasy,

km,

data_wyjazdu,

Wynagrodzenie,

Trasy,

Num_rejestr,

Marka

Id_firmy FROM trasy, Samochody

WHERE trasy.Id_samochodu = Samochody.Id_samochodu

Zadanie nie jest jeszcze ukończone, ponieważ w poleceniu wskazane było, aby raport zawierał Nazwę firmy a nie jej Id. Konieczne jest włączenie do zapytania trzeciej tabeli o nazwie firmy. Tabelę do klauzuli WHERE dołącza się operatorem logicznym AND.

SELECT trasy.Id_samochodu,

Id_trasy,

km,

data_wyjazdu,

Wynagrodzenie,

Trasy,

Num_rejestr,

Marka,

Nazwa_firmy

Id_firmy FROM trasy, Samochody, Firmy

WHERE trasy.Id_samochodu = Samochody.Id_samochodu AND Firmy.Id_firmy = Samochody.Id_firmy

Zapytania w języku SQL wyświetliło raport złożony z trzech tabel połączonych wspólnymi, kluczami relacyjnymi.

W następnym wpisie zostaną przedstawione sposoby porządkowania raportów SQL.

SQL Server 2017 Express to darmowa, relacyjna baza danych udostępniona przez firmę Microsoft.

Relacyjne bazy danych to bazy gdzie dane grupowane są w relacje, które reprezentowane są przez tablice. Relacje są pewnym zbiorem rekordów o identycznej strukturze wewnętrznie powiązanych za pomocą związków zachodzących pomiędzy danymi. Relacje zgrupowane są w tzw. schematy bazy danych. Relacją może być tabela zawierająca dane teleadresowe pracowników, zaś schemat może zawierać wszystkie dane dotyczące firmy. Takie podejście w porównaniu do innych modeli danych ułatwia wprowadzanie zmian, zmniejsza możliwość pomyłek, ale dzieje się to kosztem wydajności. https://pl.wikipedia.org/wiki/Model_relacyjny

System do zarządzania bazami danych określa się skrótem DBMS (Database Management System). Takim DBMS jest system MySQL, poza nim popularne są PostgreSQL, Firebird oraz ORACLE Database. Skrót SQL (Structured Query Language) oznacza strukturalny język zapytań, system funkcji wyrażeń i składni, który pozwala zarządzać relacyjnymi bazami danych.

Środowiskiem bazy danych jest serwer. Serwer może być uruchomiony na komputerze osobistym lub w sieci Internet.

Serwer – program świadczący usługi na rzecz innych programów, zazwyczaj korzystających z innych komputerów połączonych w sieć. Serwerem nazywa się często komputer świadczący takie usługi, sprowadzające się zazwyczaj do udostępniania pewnych zasobów innym komputerom lub pośredniczący w przekazywaniu danych między komputerami. Serwerem nazywa się też systemy oprogramowania biorące udział w udostępnianiu zasobów. https://pl.wikipedia.org/wiki/Serwer

Relacyjna baza danych MySQL może być zarządzane za pośrednictwem przeglądarki internetowej (phpMyAdmin ) lub za pomocą aplikacji serwerowych, bardzo popularną jest Microsoft SQL Server Management Studio.

Instalacja SQL Server 2017 Express

Darmową bazą danych udostępnioną przez firmę Microsoft jest SQL Server 2017 Express.

Baza dostępna jest pod adresem: https://www.microsoft.com/pl-pl/sql-server/sql-server-editions-express

Krok 1: Proszę pobrać plik bazy na swój komputer.

Przedmiotem dzisiejszego wpisu jest eksport arkuszy Excela do bazy mySQL. 

Aby prowadzić ćwiczenia z języka mySQL przygotowałem szkoleniową bazę danych o nazwie BazaT.

Jest to baza nieistniejącej firmy spedycyjnej działającej na terenie Warszawy. Spedycja ta organizuje krótkie trasy korzystając z usług zewnętrznych firm transportowych. Ta relacyjna baza danych składa się z pięciu tabel. Została sporządzona w Excelu w postaci pięciu arkuszy kalkulacyjnych. Teraz należy te arkusze przekształcić w bazę MySQL

Eksport arkuszy Excela do bazy mySQL

Proszę wejść do SQL Server Management Studio i otworzyć nową bazę danych o nazwie BazaT.

Proszę nadać nowej bazie danych nazwę BazaT.

Pojawiła się nowa baza BazaT.

Teraz proszę uruchomić formularz importu danych z Excela

Zostaje otwarty wizard eksportu. Należy wskazać, z jakiego programu chcemy importować dane oraz wskazać plik, który ma być importowany.

Teraz należy wskazać, do jakiego formatu będzie sprowadzony plik z Excela.

Skoroszyt Excela zawiera pięć arkuszy: Samochody, Trasy, Firmy, Umowy, Kierowcy.

Teraz przekształconych te arkusze w tabele bazy SQL.

Proszę zmienić nazwy wszystkich tabel usuwając $.

Należy ustawić odpowiednie parametry pól dla tabeli mySQL.

 Typy pól 

Teraz sprawdzamy naszą bazę w programie SQL Management Studio.

Eksport arkuszy Excela do bazy mySQL udał się.

Przedmiotem tego wpisu będzie analiza relacyjnej bazy danych mySQL: BazaT. Baza ta została wgrana na serwer lokalny (inaczej serwer natywny) w poprzednim wpisie. 

Przedmiotem tego kursu będzie nauka złożonych zapytań języka mySQL. Aby nauka była efektywna należy najpierw zapoznać się z bazą danych oraz z kluczami własnymi tabel.

Klucz własny tabeli to kolumna zawierająca niepowtarzalne wartość i przyporządkowane do wierszy.

 W bazie BazaT nazwy tabel zawierających klucze zaczynają się od liter „Id_” od słowa identifier – identyfikator 

Dzięki kluczom tabele w relacyjnej bazie danych korespondują między sobą, inaczej utrzymują między sobą relacje.

Aby lepiej to zobrazować przedstawię BazaT w formie graficznej.

Proszę stanąć w pustym polu SQL Query (tam gdzie pisane są kwerendy) i kliknąć prawym przyciskiem myszy.

Poniżej przedstawiona jest graficzna analiza relacyjnej bazy danych mySQL – BazaT.

Można wskazać dwa najważniejsze wnioski:

1. żadna tabela nie zawiera kompletu informacji.

2. wszystkie tabele są ze sobą powiązane kolumnami kluczowymi.

Największą i najważniejszą tabela w bazie jest tabela „Trasy”. Zawiera ona 1573 wiersze (rekordy) i jest listą tras, które samochody dostawcze zrobiły w ciągu trzech miesięcy.

Proszę zwrócić uwagę, że w tabeli „Trasy” nie ma wskazanego kierowcy ani nazwy firmy transportowej. Wskazany jest tylko numer „Id samochodu” dostawczego. Aby dowiedzieć się jak nazywała się firma, która dokonała konkretnej dostawy należy odwołać się z tabeli „Trasy” do tabeli „Samochody” po kluczu „Id_samochodu”. Następnie trzeba z tabeli „Samochody” po kluczy Id_firmy przejść do tabeli „Firmy” i dopiero stamtąd odczytać nazwę firmy, która wykonała usługę.

Tak wygląda typowy przebieg realizacji zapytania w relacyjnej bazie danych SQL.

Analiza relacyjnej bazy danych mySQL, zapoznanie się z tabelami i kluczami jest najważniejszą czynnością poprzedzającą formułowanie zapytań SQL.

 Aliasy w języku SQL służą do zamiany nazw w tabelach wynikowych.  Instrukcje te są szczególnie przydatne przy tworzeniu tabel wynikowych zawierających obliczenia arytmetyczne. Wyniki obliczeń są pokazywane w oddzielnej kolumnie. Nie ma jednak tabel, które przechowywałyby te obliczenia. Język SQL nazywa te kolumny wynikowe domyślnie.  Aliasy w języku SQL pozwalają zamienić te nazwy domyślne na nazwy jakie chcielibyśmy zamieścić w raporcie.

Alias (inaczej zwany, znany jako), vel (łac. albo, czyli) – alternatywna nazwa służąca do identyfikacji obiektów, ludzi itp. (pseudonim). Zwykle z aliasem związana jest funkcja polegająca na skróceniu i uproszczeniu nazewnictwa. https://pl.wikipedia.org/wiki/Alias

W poprzednim wpisie uzyskaliśmy następującą tabelę wynikową:

Korzystając z klauzuli AS  można zamienić domyślne nazwy wybranymi przez nas nazwami. Wielkość liter nie ma znaczenia. Język SQL przedstawi w raporcie nazwy dużymi literami.

SELECT FIRST_NAME as Imie,
LAST_NAME as nazwisko,
SALARY as Wynagrodzenie,
SALARY*0.08 as Premia,
200 as Dodatek,
SALARY+SALARY*0.08+200 as Razem
FROM EMPLOYEES;

W ostatniej kolumnie znajdzie się podsumowanie pensji i dodatków.

W efekcie uzyskujemy tabelę wynikową:

 Aliasy w języku SQL nie mogą zawierać spacji. Aby uzyskać spacje w opisie należy użyć podkreślnika. 

SELECT FIRST_NAME as Imie,
LAST_NAME as nazwisko,
SALARY as Wynagrodzenie,
SALARY*0.08 as Premia,
200 as Dodatek,
'do wyplaty:' as Komentarz,
SALARY+SALARY*0.08+200 as Razem_na_konto
FROM EMPLOYEES;

Dodaliśmy kolumnę „Komentarz” z informacją „do wyplaty”.

Operatory logiczne w SQL Oracle służą do określania warunków dla zapytań.  Operacje wyboru polegają na wyborze w oparciu o pewne, określone kryteria.

Operatory logiczne w SQL Oracle do przeprowadzenia selekcji stosuje się m.in. w oparciu o klauzulę WHERE. Klauzula WHERE występuje bezpośrednio po klauzurze SELECT.

 Występują dwa rodzaje operatorów selekcji działających w oparciu o klauzule WHERE: operatory logiczne i operatory języka SQL 

Operatory logiczne to: <, >, =, false, true itp. Służą one do wyszukiwania danych spełniających określone kryteria.

Skorzystamy z tabeli EMPLOYEES z bezpłatnej bazy Oracle HR dostępnej tutaj: http://www.oracle.com/technetwork/database/express-edition/downloads/index.html

Wybierzmy wszystkich pracowników z kodem ID: SA_REP

SELECT *

FROM EMPLOYEES

WHERE JOB_ID = 'SA_REP';

Otrzymujemy tabelę wynikową:

Teraz wylistujemy wszystkich pracowników, których pensja jest większa od 12 000. Dane mają być posortowane w malejąco.

SELECT *

FROM EMPLOYEES

WHERE SALARY >12000

ORDER BY SALARY DESC;

Otrzymujemy tabelę wynikową:

Operatory logiczne w SQL Oracle można stosować w różnych konfiguracjach. Bardziej złożone zapytania poznamy w następnym wpisie.

Najprostsze zapytanie w SQL składa się z dwóch komend SELECT – czyli „co” oraz FROM – czyli „skąd”. Aby rozpocząć musimy najpierw utworzyć na swoim komputerze środowisko pracy.

Oracle Database – system zarządzania relacyjnymi bazami danych (ang. RDBMS) stworzony przez firmę Oracle Corporation. Relacyjna baza danych Oracle posługuje się standardowym językiem zapytań SQL oraz posiada wbudowany wewnętrzny język tworzenia procedur składowanych PL/SQL – będący proceduralnie obudowanym językiem SQL. Jako języka tworzenia procedur składowanych w bazach danych Oracle (od wersji 8i) można używać również języka Java. https://pl.wikipedia.org/wiki/Oracle_Database

Tworzenie środowiska

Środowisko do ćwiczeń skład się z Bazy ORACLE oraz edytora zapytań Oracle SQL Developer. Oba składniki należy zainstalować na komputerze następnie uruchomić bazę na edytorze.

Darmowa baza danych udostępniona przez ORACLE:

http://www.oracle.com/technetwork/database/express-edition/downloads/index.html

Darmowy edytor Oracle SQL Developer:

http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

Uruchamiamy bazę danych hr na Oracle SQL Developer.

Bazę hr możemy podejrzeć wystarczy kliknąć na jedną z jej tabel.

Aby uruchomić edytora zapytań należy wybrać poniższą zakładkę:

Najprostsze zapytanie w SQL

Po zainstalowaniu obu elementów oraz podłączeniu bazy do dewelopera możemy przystąpić do pisania zapytań.

 Instrukcja SELECT służy do pobierania danych z bazy. Instrukcja ta łączy się  z instrukcją FROM. Polecenia SQL mogą być umieszczane w oddzielnych wierszach. Koniec polecenia zaznacza się średnikiem. 

1. Wyświetlamy zawartość tabeli COUNTRIES. Wpisujemy w żółte pole SELE i program sam podpowiada składnię zapytania. Wybieramy z podpowiedzi SELECT * FROM.

Teraz za FROM możemy znowu wyświetlić podpowiedź i wybrać, którą tabelę chcemy wyświetlić. Znak * oznacza, że chcemy wyświetlić wszystko.

Na dole w oknie wyników ukazuje nam się wynik zapytania. To było najprostsze zapytanie w SQL.

Teraz możemy wyświetlić tylko jedną kolumnę z tabeli COUNTRIES.

Z naszego zapytania kasujemy * i wybieramy z podpowiedzi  DEPARTAMENT_NAME

Otrzymujemy kolumnę z danymi DEPARTAMENT_NAME

Obliczenia arytmetyczne SQL prowadzi się za pośrednictwem paska poleceń. Zanim jednak pokażę przykład obliczeń, zrobimy jedno proste zapytanie składające się z trzech kolumn. Uruchamiamy Oracle SQL developer i zadajemy zapytanie dla tabeli EMPLOYEES. Chcemy poznać numery telefonów zatrudnionych pracowników. Przy pisaniu wyrażenia musimy pamiętać aby nazwy kolumn oddzielane były przecinkami.

 Wybór kilku kolumn z tabeli nazywa się selekcją pionową (projekcją). 

W tabeli wynikowej uzyskujemy:

Obliczenia arytmetyczne SQL

 Instrukcja SQL może zawierać operatory: + (plus), - (minus) , *(mnożenie), /(dzielenie) oraz stałe wartości liczbowe. Zaleca się aby kolejne instrukcje SQL znajdowały się w oddzielnych wierszach. 

Aby wyświetlić tabelę z pensjami pracowników z tabeli EMPLOYEES należy wpisać następującą instrukcję:

SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES;

Uzyskujemy tabele poborów pracowników:

Aby obliczyć wysokość premii wysokości 8

SELECT FIRST_NAME, LAST_NAME, SALARY, SALARY*0.08
FROM EMPLOYEES;

Program utworzył dodatkową kolumnę z wartością premii dla pracowników.

 Należy pamiętać, że w SQL przecinki w wyrażeniach liczbowych wpisuje się jako kropki.  

Teraz dodatkowo doliczamy do premii pracowników dodatek 200 zł. Aby zachować czytelność obliczeń utworzyliśmy dodatkową kolumnę zawierającą dodatki 200 zł oraz sumę premii i dodatku. Język SQL zachowuje kolejność działań arytmetycznych.

SELECT FIRST_NAME, LAST_NAME, SALARY, SALARY*0.08, 200, SALARY*0.08+200
FROM EMPLOYEES;

Otrzymaliśmy następujący wynik zapytania:

Dzięki instrukcji ORDER BY można sortować dane w SQL Oracle. Chodzi o porządkowanie danych według określonych kryteriów.

 Klauzula ORDER BY musi być ostatnia w wyrażeniu, jeżeli występuje z klauzulą SELECT. Należy podać, według której kolumny ma być posortowana tabela wynikowa. 

Skorzystamy z tabeli EMPLOYEES z bezpłatnej bazy Oracle HR dostępnej tutaj: http://www.oracle.com/technetwork/database/express-edition/downloads/index.html

Posortujemy dane według nazwisk pracowników.

Sortować dane w SQL Oracle można za pomocą poniższego wyrażenia:

SELECT *

FROM EMPLOYEES

ORDER BY LAST_NAME

Otrzymujemy tabele wynikową:

Domyślnie SQL sortuje dane w porządku rosnącym. Aby dane sortować dane w SQL Oracle w porządku malejącym należy zastosować klauzule DESC.

SELECT *

FROM EMPLOYEES

ORDER BY LAST_NAME DESC;

Teraz posortujemy dane pracowników wg wielkości ich zarobków zaczynając od największych wartości.

SELECT *

FROM EMPLOYEES

ORDER BY SALARY DESC;

Teoretycznie można sortować dane w SQL Oracle według wielu kolumn. Program nie zgłasza tego jako błąd, niestety ustawienia większości baz danych nie pozwalają na taką formę sortowania.

SELECT *

FROM EMPLOYEES

ORDER BY SALARY DESC, LAST_NAME

Jeżeli podobają Ci się ćwiczenia. Chcesz aby było ich więcej, dużo więcej! Oddaj głos na któryś z moich wpisów na Stock Overflow! Oto link – wystarczy tylko kliknąć!

Łączenie nazw kolumn w SQL Oracle jest bardzo użyteczną funkcją. Skorzystamy z tabeli EMPLOYEES z bezpłatnej bazy Oracle HR dostępnej tutaj: http://www.oracle.com/technetwork/database/express-edition/downloads/index.html

Połączmy dwie kolumny: FIRST_NAME i LAST_NAME w jedną kolumnę IMIE_NAZWISKO. 

 Aby połączyć ze sobą dwie kolumny w bazie danych Oracle lub DB2 można użyć funkcję CONCAT. 

Oto składnia instrukcji:

SELECT CONCAT(FIRST_NAME, LAST_NAME) AS imie_nazwisko

FROM EMPLOYEES;

Otrzymujemy poniższą tabelę wynikową:

Nazwiska i imiona nie są rozdzielone spacją, tabela nie nadaje się do użycia w raporcie.

Łączenie kolumn w SQL Oracle wymaga aby zastosować separator ciągów tekstowych obu łączonych tabel. Nazywamy go operatorem konkatenacji (||).

SELECT (FIRST_NAME||' '||LAST_NAME) AS imie_nazwisko

FROM EMPLOYEES;

Otrzymujemy oddzielone ciągi znaków z obu kolumn.

Łączenie nazw kolumn w SQL Oracle z zastosowaniem operatora konkatenacji pozwala budować złożone zestawy informacji.

SELECT (FIRST_NAME||' '||LAST_NAME||' - telefon: '||PHONE_NUMBER) AS imie_nazwisko

FROM EMPLOYEES;