Wykład 4 - bazy danych - SQL - Access [tryb zgodności]

Relacyjne bazy danych
Podstawy SQL
Język SQL
SQL (Structured Query Language) – język umożliwiający
dostęp i przetwarzanie danych w bazie danych na poziomie
obiektów modelu relacyjnego tj. tabel i perspektyw.
Po co się uczyć?
SQL umożliwia odczyt informacji z każdej relacyjnej bazy
danych
zrozumienie mechanizmów rządzących graficznymi
interfejsami użytkownika
możliwość tworzenia złożonych zapytań
wiedza przydatna na wielu platformach sprzętowych
Funkcje realizowane przez SQL
definicja struktury bazy danych
odczyt danych
modyfikacja danych
kontrola dostępu
współużytkowanie danych
integralność danych
Można używać go jako:
element aplikacji i narzędzi programowych
interakcyjny język zapytań
język komunikacji pomiędzy różnymi SZRBD
Składnia SQL - SELECT
SELECT
nazwa kolumny
FROM
nazwa tabeli
WHERE
warunek wyszukiwania
GROUP BY
nazwa kolumny
Składnia SQL - SELECT
SELECT
nazwa kolumny
FROM
Przykłady:
SELECT nazwisko FROM TSedziowie;
SELECT nazwa, id FROM TKluby;
SELECT gol1, gol2, gol3, gol4 FROM TMecze;
SELECT * FROM TKluby;
nazwa tabeli
SELECT – eliminowanie duplikatów
SELECT DISTINCT
nazwa kolumny
FROM
Przykłady:
SELECT DISTINCT kolejka FROM TMecze;
nazwa tabeli
Sortowanie rekordów
SELECT
nazwa kolumny
ORDER BY
FROM
nazwa kolumny
nazwa tabeli
ASC
DESC
Przykłady:
SELECT imie, nazwisko FROM TSedziowie ORDER BY nazwisko, imie ASC;
SELECT imie, nazwisko FROM TSedziowie ORDER BY nazwisko, imie DESC;
SELECT imie, nazwisko FROM TSedziowie ORDER BY nazwisko ASC, imie
DESC;
SELECT DISTINCT kolejka FROM TMecze ORDER BY kolejka DESC;
Proste wyrażenia
Można używać operatorów do wykonywania działań na polach, np.
+, -, *, /,
Przykłady:
SELECT domin-odmin FROM tsklady;
Nadawanie nazw
SELECT
wyrażenie
AS
nazwa kolumny - nowa
Przykłady:
SELECT domin-odmin AS CzasGryPiłkarza FROM tsklady;
SELECT
m.id, k1.nazwa, k2.nazwa
FROM
TMecze AS m, TKluby AS k1, TKLuby AS k2;
Filtrowanie danych
SELECT DISTINCT
FROM
nazwa tabeli
wyrażenie
AS
WHERE
alias
warunek wyszukiwania
Przykłady:
SELECT * FROM tKartkiGole WHERE rodzaj = "K";
SELECT imie, nazwisko FROM TPilkarze WHERE klubid = 3;;
SELECT nazwa FROM TKluby WHERE id = 999
SELECT p.imie, p.nazwisko, k.nazwa FROM TPilkarze as p,
TKluby as k WHERE p.klubid = k.id AND k.id = 4 ORDER BY
p.nazwisko;
Filtrowanie danych – warunki wyszukiwania
Podstawowe warunki:
porównanie (operatory: =, <>, <, >, <=, >=)
zakres (BETWEEN … AND …)
występowanie (IN)
wzór (LIKE)
* - zastępuje dowolny ciąg znaków
? - zastępuje pojedynczy znak
puste wartości (IS NULL)
istnienie (EXISTS)
Filtrowanie danych – warunki wyszukiwania
Przykłady:
SELECT * FROM tsedziowie WHERE miasto <> "Warszawa";
SELECT * FROM tmecze WHERE kiedy BETWEEN #2003-08-16#
AND #2003-09-13#;
SELECT * FROM tmecze WHERE kolejka IN ("2", "3", "5") ;
SELECT * FROM tsedziowie WHERE miasto IS NULL;
SELECT imie, nazwisko FROM tsedziowie WHERE nazwisko LIKE
"G*";
Filtrowanie danych – łączenie predykatów
Warunki złożone buduje się przy pomocy operatorów
logicznych AND, OR, NOT
Przykłady:
SELECT p.imie, p.nazwisko, k.nazwa FROM TPilkarze as p,
TKluby as k WHERE p.klubid = k.id AND k.id = 4 ORDER BY
p.nazwisko;
SELECT id FROM TMecze WHERE gospodarz = 5 OR przeciwnik
= 5;
Proste funkcje agregujące
SELECT COUNT (*)
wyrażenie
AS
alias
COUNT
SUM
AVG
MIN
MAX
FROM
nazwa tabeli
WHERE
warunek wyszukiwania
Proste funkcje agregujące
Przykłady:
SELECT SUM(widownia) FROM Tmecze;
SELECT SUM(gol1) + SUM(gol2) FROM TMecze;
SELECT COUNT(*) FROM TPilkarze, TKluby;
SELECT COUNT(*) FROM tKartkiGole WHERE rodzaj = "S";
SELECT COUNT(*) as ilegoli FROM tKartkiGole WHERE rodzaj =
"N" OR rodzaj = "K" OR rodzaj = "S";
Zapytania wielotabelowe
Ważne:
należy podać wszystkie nazwy tablic w klazuzuli FROM
w celu uniknięcia niejednoznaczności należy stosować nazwy
kwalifikowane pól (poprzedzone nazwą tabeli lub alias)
we frazie WHERE należy umieścić warunek wiążący zbiory
Nazwa kwalifikowana:
np. GoleKartki.rodzaj, TKluby.nazwa
Alias tablicy umieszcza się we frazie FROM np.
FROM TMecze AS m, TKluby AS k1, TKLuby AS k2
Zapytania wielotabelowe - przykłady
Zapytanie
SELECT imie, nazwisko, nazwa FROM TPilkarze, TKluby
zwraca wszystkie możliwe kombinacje rekordów tabel TPilkarze,
TKluby w ilości:
SELECT COUNT(*) FROM TPilkarze, TKluby
czyli iloczyn:
SELECT COUNT(*) FROM TPilkarze
SELECT COUNT(*) FROM TKluby
Zapytania wielotabelowe - przykłady
SELECT COUNT(*) as liczbapilkarzy FROM TPilkarze AS p,
TKluby AS k WHERE p.klubid = k.id AND k.id = 4
SELECT p.imie, p.nazwisko, k.nazwa FROM TPilkarze AS p,
TKluby AS k WHERE p.klubid = k.id AND p.id = 179
SELECT k1.nazwa, k2.nazwa, m.id FROM TKluby AS k1,
TKluby AS k2, TMecze AS m WHERE m.id = 5 AND k1.id
= m.gospodarz AND k2.id = m.przeciwnik
Klauzula JOIN
SELECT
JOIN
nazwa kolumny
nazwa tabeli
FROM
ON
nazwa tabeli
warunek wyszukiwania
Klazula JOIN pozwala łączyć ze sobą kilka tabel
wykorzystując do tego klucze główne i obce.
Dotyczy tylko tych wierszy, które mają swoje odpowiedniki
w tabeli komplementarnej.
Klauzula INNER JOIN - przykłady
SELECT COUNT(*) as liczbapilkarzy FROM TPilkarze AS p
INNER JOIN TKluby AS k ON p.klubid = k.id AND k.id =
4;
SELECT p.imie, p.nazwisko, k.nazwa FROM TPilkarze AS p
INNER JOIN TKluby AS k ON p.klubid = k.id AND p.id =
179;
SELECT imie, nazwisko, nazwa FROM TPilkarze INNER JOIN
TKluby ON TPilkarze.klubid = TKluby.id AND TPilkarze.id
= 179
Klauzula GROUP BY
SELECT
WHERE
nazwa kolumny
FROM
warunek wyszukiwania
nazwa tabeli
GROUP BY
nazwa kolumny
Przykłady:
liczba goli i kartek zdobytych przez każdego z piłkarzy
SELECT pilkarzid, COUNT(*) AS ile FROM tKartkiGole GROUP
BY pilkarzid ORDER BY ile DESC
liczba goli zdobyta przez każdego z piłkarzy
SELECT pilkarzid, COUNT(*) AS ile FROM tKartkiGole WHERE
(rodzaj = "N" OR rodzaj = "K") GROUP BY pilkarzid ORDER BY
ile DESC
Klauzula GROUP BY - c.d.
Co z nazwiskami piłkarzy, jak je uzyskać?
SELECT
p.imie, p.nazwisko, pilkarzid, COUNT(*) AS ile
FROM
tKartkiGole, TPilkarze AS p
WHERE
(rodzaj = "N" OR rodzaj = "K") AND
p.id = pilkarzid
GROUP BY
pilkarzid
ORDER BY
ile DESC;
Klauzula GROUP BY – c. d.
Jak uzyskać listę strzelców z danego klubu?
SELECT
p.imie, p.nazwisko, kl.nazwa, COUNT(*) AS ile
FROM
tKartkiGole AS gol, TPilkarze AS p, TKluby AS kl
WHERE
gol.rodzaj = "N" AND
p.id = gol.pilkarzid AND kl.id = gol.klubid AND
kl.id = 2
GROUP BY
gol.pilkarzid
ORDER BY
ile DESC;
Ograniczenie liczby rekordów - LIMIT
Klauzula LIMIT ogranicza liczbę jednocześnie wyświetlanych
rekordów
SELECT * FROM "tmecze" LIMIT 15
zwraca 15 pierwszych rekordów
SELECT * FROM "tsklady" LIMIT 105, 15
zwraca 15 kolejnych rekordów pomijając pierwsze 105
Instrukcje modyfikacji tablic
INSERT – pozwala dopisywać nowe wiersze do
tablicy
UPDATE - pozwala modyfikować istniejące dane
DELETE – pozwala usunąć wiersze z tablicy
Instrukcja INSERT
INSERT
INTO
nazwa tabeli,
ew. nazwy kolumn
VALUES
wartości
Przykłady:
INSERT INTO tKartkiGole VALUES ( "1", "1", "266", "7",
"28", "K");
INSERT INTO TPilkarze VALUES ( "114", "Piotr",
"Soczewka", "2");
INSERT INTO TPilkarze (id, imie, nazwisko) VALUES (
"350", "Grzegorz", "Kowalski");
Instrukcja UPDATE
UPDATE
nazwa tabeli
SET
wartości
WHERE
warunek
wyszukiwania
SET – podaje nazwy kolumn i przypisane im nowe wartości
WHERE – określa, które rekordy mają być modyfikowane
Przykłady:
UPDATE TPilkarze SET nazwisko = "Gruszka" WHERE id
= 114;
UPDATE TPilkarze SET imie = "Jan", nazwisko =
"Gruszka" WHERE id = 114;
Instrukcja DELETE
DELETE
FROM
nazwa tabeli
WHERE
warunek
wyszukiwania
Przykłady:
DELETE FROM TPilkarze WHERE id = 350;
DELETE FROM TPilkarze WHERE klubid IN (SELECT id
FROM tkluby WHERE nazwa LIKE "*Warszawa*");
Tworzenie tabel
CREATE
TABLE
nazwa tabeli
Przykłady:
CREATE TABLE TPilkarze (
id int(11) NOT NULL auto_increment,
imie varchar(75) NOT NULL,
nazwisko varchar(75) NOT NULL,
klubid int(11),
PRIMARY KEY (id),
UNIQUE id_2 (id),
KEY id (id, imie, nazwisko, klubid)
);
WHERE
nazwa kolumny=typ
Usuwanie tabel
DROP
TABLE
Przykłady:
DROP TABLE TPilkarze;
DROP TABLE TMecze;
DROP TABLE tKartkiGole;
nazwa tabeli
Klauzula UNION
Klauzula UNION – służy do łączenia rezultatów dwóch lub
więcej instrukcji SELECT w jedną tablicę
Łączone instrukcje SELECT muszą spełniać określone
warunki:
muszą zawierać tę samą liczbe kolumn
odpowiednie kolumny ze wszystkich instrukcji SELECT
muszą mieć zgodne typy
klauzula ORDER BY może wystąpić tylko w ostatniej
instrukcji SELECT
Klauzula UNION
SELECT * FROM
tKartkiGole
WHERE
rodzaj = "N"
UNION
SELECT * FROM
tKartkiGole
WHERE
rodzaj = "S";