Zagadnienia poruszane podczas zajęć laboratoryjnych dotyczyć będą podstaw relacyjnych baz danych oraz języka SQL.
Język SQL
SQL to strukturalny język zapytań (ang. Structured Query Language). Jest to bardzo powszechny i standaryzowany język dostępu do systemów zarządzania relacyjnymi bazami danych. Służy do tworzenia i modyfikowania baz danych oraz do pobierania i zapisywania danych z i do bazy. Rozpoznawany jest przez wszystkie najpopularniejsze systemy baz danych takie jak np. MySQL, PostgreSQL, Microsoft SQL Server, Oracle, DB2.
Użycie SQL polega na wysyłaniu zapytań (query) do bazy i przekazywaniu użytkownikowi żądanych danych. Na początku pracy z bazą danych oraz językiem, warto wybrać sposób w jaki będzie się zapisywać nazwy tabeli, zapytania, warunki itd. Zasady przyjęte w materiałach:
nazwy tabel zapisywane są z wielkiej litery oraz składają się tylko z liter i znaku "_"
słowa kluczowe w zapytaniach są pisane wielkimi literami w odróżnieniu od np. nazw kolumn czy warunków
Podczas zajęć wykorzystywać będziemy system zarządzania bazami danych MySQL wraz z narzędziem MySQL Workbench w wersjach darmowych. (https://www.mysql.com/products/workbench/)
Podczas zajęć korzystać będziemy z przygotowanych baz danych na serwerze uczelnianym torus.uck.pk.edu.pl, dalatego na komputerach nie wymagany jest serwer mysql, wystarczy jedynie klient. Adres, nazwy urzytkowników, hasła oraz sposób połącznia z wykorzystaniem MySQL Workbench został podany na zajęciach. Jakiekolwiek problemy z logowaniem proszę zgłaszać na zajęciach lub poprzez kontakt mailowy.
Zapytanie składa się tylko z polecenia:
CREATE DATABASE nazwa_bazy
Jest to czynność równie prosta jak tworzenie bazy, jednak niosąca za sobą poważne konsekwencje w postaci utraty wszystkich danych zawartych w jej tabelach i samych tabel.
DROP DATABASE nazwa_bazy
Powyższych dwóch zapytań na zajęciach nie używamy, ponieważ baza juz istnieje i nie chcemy jej usuwać.
# kod pythona do połączenia z bazą i wykonywania zapytań sql w jupyter
from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://root:*****@127.0.0.1:3306/DB19')
%load_ext sql_magic
%config SQL.conn_name = 'engine'
Do wylistowania istniejących tabel w bazie służy zapytanie
SHOW TABLES
%%read_sql
SHOW TABLES
Baza jest pusta wobec czego otrzymujemy pustą tabele
Tabele w MySQL tworzymy przy użyciu polecenia
CREATE TABLE nazwa (kolumny typ danych atrybuty)
Pamiętać należy, żę kolumny w obrębie tabeli muszą mieć uniklane nazwy oraz zdefiniowany typ danych.
Podstawowe typy danych:
Pozostałe można podglądnąć w interfejsie tworzenia tabeli w MySQL Workbench
Atrybuty kolumn w tabeli MySQL:
%%read_sql
CREATE TABLE Studenci (
Nr_albumu int NOT NULL,
Imie varchar(15) NOT NULL,
Nazwisko varchar(20) NOT NULL,
Data_ur date NOT NULL,
Email varchar(50),
Miejscowosc varchar(50),
Zamieszkanie varchar(50),
Kierunek varchar(50),
Specjalnosc varchar(50),
PRIMARY KEY(Nr_albumu))
Sprawdzamy czy tabela została utworzona
%%read_sql
SHOW TABLES
%%read_sql
CREATE TABLE Pracownicy (
Pracownik_id int NOT NULL AUTO_INCREMENT,
Imie varchar(15) NOT NULL,
Nazwisko varchar(20) NOT NULL,
Tytul varchar(10),
Email varchar(50),
Przedmiot varchar(50),
Pokoj varchar(50),
Instytut varchar(50),
PRIMARY KEY(Pracownik_id));
%%read_sql
SHOW TABLES
Aby wyświetlić metadane tabeli można użyć zapytania DESCRIBE
%%read_sql
DESCRIBE Pracownicy
Do usunięcia całej tabeli służy zapytanie
DROP TABLE nazwa_tabeli
Istnieje możliwość jedynie wyczyszczenia tabeli tzn. usunięcia wszystkich danych pozostawiając strukturę tabeli, służy do tego zapytanie
TURNCATE TABLE nazwa_tabeli
Stworzyć nową tabelę o nazwie 'Oceny' w której zawarte będą informacje o skali ocen. Kolumny: Ocena, Ocena_slownie, Ocena_symbol.
Rozwiązanie:
%%read_sql
CREATE TABLE Oceny (
Ocena real NOT NULL,
Ocena_slownie varchar(15),
Ocena_symbol varchar(1)
);
W każdej tabeli przynajmniej jedna z kolumn powinna posiadać atrybut klucza głównego. Aby dodać taki atrybut do już istniejącej kolumny należy wykonać następujące zapytanie
ALTER TABLE nazwa_tabeli
ADD PRIMARY KEY (nazwa_kolumny);
Można również stworzyć klucz składający się z kilku kolumn
ALTER TABLE nazwa_tabeli
ADD constraint nazwa_klucza
PRIMARY KEY (nazwa_kolumny1, ... , nazwa_kolumnyN);
%%read_sql
ALTER TABLE Oceny
ADD PRIMARY KEY (Ocena);
Sprawdzamy
%%read_sql
DESCRIBE Oceny
Stworzyć nową tabelę o nazwie 'Przedmioty' w której zawarte będą informacje o przedmiotach. Kolumny: Nazwa, Typ, ECTS, Prowadzący_id, Termin, Zaliczenie. Kolumna Nazwa powinna być kluczem głównym.
Rozwiązanie:
%%read_sql
CREATE TABLE Przedmioty (
Nazwa varchar(30) NOT NULL,
Typ varchar(40),
ECTS int,
Prowadzacy_id int,
Termin varchar(50),
Zaliczenie varchar(50),
PRIMARY KEY(Nazwa));
Do dodawanie do tabeli nowych rekordów zawierających w odpowiednich kolumnach podane wartości służy zapytanie INSERT.
INSERT INTO tabela VALUES (wartość,wartość,..)
W powyższym zapytaniu wartości będą wstawiane pokolei do kolumn, jeśli chcemy dodać rekord w którym niektóre kolumny są puste piszemy:
INSERT INTO tabela (kolumna,kolumna..) VALUES (wartość,wartość,..)
%%read_sql
INSERT INTO Studenci
VALUES(0, "Adam", "Marszalek", "1986-08-10", "amarszalek@pk.edu.pl", "Krakow", "Mieszkanie",
"Matematyka", "Modelowanie matematyczne")
Wyświetlanie zawartości tabeli, więcej o zapytaniu SELECT później
%%read_sql
SELECT * FROM Studenci
%%read_sql
INSERT INTO Pracownicy (Pracownik_id, Imie, Nazwisko, Instytut)
VALUES (0, "Adam", "Marszalek", "Instytut Informatyki")
%%read_sql
SELECT * FROM Pracownicy
UWAGA: Kolumna Pracownik_id ma ustawiony atrybut AUTO_INCREMENT bez podanej wartości, domyślnie jest to 1 stąd pojawiła się jedynka w kolumnie Pracownik_id
Polecenia UPDATE służy do zmiany wartości w istniejących już rekordach w tabeli.
Zapytanie składa się z :
UPDATE tabela SET kolumna1=wartość1, kolumna2=wartość2, ..
WHERE kolumna=wartość
%%read_sql
UPDATE Pracownicy SET email='amarszalek@pk.edu.pl'
WHERE Pracownik_id=1
%%read_sql
SELECT * FROM Pracownicy
Zapytanie DELETE służy do usuwania z bazy danych rekordów spełniających podane warunki. Zapytanie składa się z:
DELETE FROM tabela WHERE warunek
Przy usuwaniu rekordów musimy zwrócić szczególną uwagę na klauzulę warunkową. Musi być ona poprawna i uwzględniać tylko te rekordy które faktycznie chcemy usunąć.
%%read_sql
DELETE FROM Studenci
WHERE Nr_albumu = 0
%%read_sql
SELECT * FROM Studenci
Wypełnić danymi tabele Stucenci, Pracownicy, Przedmioty, Oceny, co najmniej po 10 rekordów (nie dotyczy tabeli Oceny).