SQL : podstawowe pojęcia teoretyczne

Definicje

SQL (skrót od: Structured Query Language) – strukturalny język zapytań do baz danych. Jest szczególnie przydatny w obsłudze uporządkowanych danych składających się z bytów (zmiennych) i relacji między różnymi podmiotami danych.

Baza danych – zorganizowany zbiór danych zapisanych w odpowiednim formacie, przechowywanych i pobieranych cyfrowo ze zdalnego lub lokalnego systemu komputerowego.

Relacyjna baza danych – opisany i zorganizowany zbiór danych połączonych relacjami – związkami między sobą. Dane te są zapisane w formie krotek, które mają swoje atrybuty.

Tabela – zorganizowany zbiór danych przechowywanych w postaci wierszy i kolumn.

Pola – Kolumny w tabeli nazywane są polami, a wiersze mogą być nazywane rekordami.

Encje – rodzaje “obiektów” przechowywanych w bazie. Na przykład klient, czy dostawca. Odpowiednikiem encji w programowaniu obiektowym jest klasa.

Atrybuty – właściwości encji. Na przykład dla klienta: nazwisko, numer kontaktowy. Atrybuty mają swoje typy. Relacyjne bazy danych obsługują różne typy. W większości przypadków typy z języków programowania mają swoje odpowiedniki w typach w bazie danych.

Krotka – zbiór atrybutów. Można powiedzieć, że krotka w modelu relacyjnym odpowiada instancji obiektu w programowaniu obiektowym. Krotki często prezentowane są w postaci wiersza w tabeli gdzie każda kolumna odpowiada poszczególnym atrybutom.

Klucz główny – unikalny identyfikator dla każdego wiersza w tabeli. Musi zawierać wartości UNIKALNE i mieć niejawne ograniczenie NOT NULL.

CREATE TABLE Students ( 	 /* Create table with a single field as primary key */
    ID INT NOT NULL
    Name VARCHAR(255)
    PRIMARY KEY (ID)
);

CREATE TABLE Students ( 	 /* Create table with multiple fields as primary key */
    ID INT NOT NULL
    LastName VARCHAR(255)
    FirstName VARCHAR(255) NOT NULL,
    CONSTRAINT PK_Student
    PRIMARY KEY (ID, FirstName)
);

ALTER TABLE Students 	 /* Set a column as primary key */
ADD PRIMARY KEY (ID);

ALTER TABLE Students 	 /* Set multiple columns as primary key */
ADD CONSTRAINT PK_Student 	 /*Naming a Primary Key*/
PRIMARY KEY (ID, FirstName);

Klucz obcy – zależności pomiędzy powiązanymi tabelami pokazane są przez klucze obce (ang. foreign key). Klucz obcy to dodatkowa kolumna (lub kolumny), która pokazuje zależność.

DDL – Data Definition Language, wszystkie rzeczy, które zarządzają schematem/ strukturą (create table, create function, create index, drop table, drop database)

DML – Data Manipulation Language (CRUD – create (stwórz)=insert, retrieve (pobierz)=select, update (zaktualizuj)=update, delete (usuń)=delete)

DBMS – oznacza System Zarządzania Bazą Danych. DBMS to oprogramowanie systemowe odpowiedzialne za tworzenie, pobieranie, aktualizację i zarządzanie bazą danych. Zapewnia, że nasze dane są spójne, zorganizowane i łatwo dostępne, służąc jako interfejs między bazą danych a użytkownikami.

RDBMS – jak DBMS, ale dodatkowo przechowuje dane w postaci zbioru tabel, a relacje między wspólnymi polami tych tabel można definiować.

Integralność danych (przykład: transakcje) – to gwarancja dokładności i spójności danych w całym cyklu życia, a także krytyczny aspekt projektowania, wdrażania i użytkowania każdego systemu, który przechowuje, przetwarza lub pobiera dane. Definiuje również ograniczenia integralności w celu wymuszenia reguł biznesowych dotyczących danych, gdy są one wprowadzane do aplikacji lub bazy danych.

Transakcje – jeżeli 2 użytkowników zapisuje/ aktualizuje JEDNOCZEŚNIE to samo pole, to zostanie zapisana tylko operacja, która pierwsza zostanie zapisana.

Zapytanie – to żądanie danych lub informacji z tabeli bazy danych lub kombinacji tabel. Zapytanie do bazy danych może być zapytaniem wyboru lub zapytaniem akcji:

SELECT fname, lname 		 /* select query */
FROM myDb.students
WHERE student_id = 1;

UPDATE myDB.students 		 /* action query */
SET fname = 'Captain', lname = 'America'
WHERE student_id = 1;

SELECT a.studentid, a.name, b.total_marks
FROM student a, marks b
WHERE a.studentid = b.studentid AND b.total_marks >
(SELECT total_marks
FROM marks
WHERE studentid =  'V002');

Rodzaje powiązań

Jeden do jednego – przykładem takiej zależności może być samochód – numer rejestracyjny.

Jeden do wielu – przykładem takiej zależności może być towar – producent.

Wielu do wielu – przykładem takiej zależności mogą być hurtownicy – towary.

Join – typy

Klauzula Join SQL służy do łączenia rekordów (wierszy) z dwóch lub więcej tabel w bazie danych SQL na podstawie powiązanej kolumny między nimi.

INNER Join – Pobiera rekordy, które mają pasujące wartości w obu tabelach uczestniczących w złączeniu

SELECT *
FROM Table_A
JOIN Table_B;

SELECT *
FROM Table_A
INNER JOIN Table_B;

LEFT Join – Pobiera wszystkie rekordy / wiersze z lewej strony i dopasowane rekordy / wiersze z prawej tabeli.

RIGHT Join – Pobiera wszystkie rekordy / wiersze z prawej strony i dopasowane rekordy / wiersze z lewej tabeli.

FULL (OUTER) Join – Pobiera wszystkie rekordy, w których występuje dopasowanie w lewej lub prawej tabeli.

Jakie są ograniczenia (Constraints) w SQL?

Ograniczenia służą do określenia zasad dotyczących danych w tabeli. Można je zastosować do pojedynczego lub wielu pól w tabeli SQL podczas tworzenia tabeli lub po utworzeniu za pomocą polecenia ALTER TABLE. Ograniczenia są następujące:

     NOT NULL – ogranicza wstawianie wartości NULL do kolumny.
     CHECK – sprawdza, czy wszystkie wartości w polu spełniają warunek.
     DEFAULT – Automatycznie przypisuje wartość domyślną, jeśli dla pola nie określono żadnej wartości.
     UNIQUE – zapewnia unikalne wartości do wstawienia w pole.
     INDEX – Indeksuje pole zapewniające szybsze pobieranie rekordów.
     PRIMARY KEY – Unikatowo identyfikuje każdy rekord w tabeli.
     FOREIGN(obcy) KEY – Zapewnia integralność referencyjną dla rekordu w innej tabeli.

Co to jest podzapytanie (Subquery)? Jakie są jego rodzaje?

Podzapytanie jest zapytaniem w ramach innego zapytania, dlatego jest znane jako zapytanie zagnieżdżone lub zapytanie wewnętrzne. Służy do ograniczenia lub ulepszenia danych, które mają być przeszukiwane przez zapytanie główne, tym samym odpowiednio ograniczając lub zwiększając wydajność zapytania głównego. Na przykład tutaj pobieramy informacje kontaktowe dla studentów, którzy zapisali się na przedmiot z matematyki:

SELECT name, email, mob, address
FROM myDb.contacts
WHERE roll_no IN (
	 SELECT roll_no
	 FROM myDb.students
	 WHERE subject = 'Maths');

Skorelowane podzapytanie nie może być traktowane jako niezależne zapytanie, ale może odnosić się do kolumny w tabeli wymienionej w FROM zapytania głównego.
Nieskorelowane podzapytanie jest traktowane jako niezależne zapytanie, a wynik zapytania podrzędnego jest podstawiany w zapytaniu głównym.

Co to są funkcje agregujące i skalarne?

Funkcja agregująca wykonuje operacje na zbiorze wartości, aby zwrócić pojedynczą wartość skalarną. Funkcje agregujące są często używane z klauzulami GROUP BY i HAVING instrukcji SELECT. Oto najczęściej używane funkcje agregujące SQL:

    AVG () – Oblicza średnią z zbioru wartości.
    COUNT () – Zlicza całkowitą liczbę rekordów w określonej tabeli lub widoku.
    MIN () – Oblicza minimum zbioru wartości.
    MAX () – Oblicza maksimum zbioru wartości.
    SUM () – Oblicza sumę zbioru wartości.
    FIRST () – Pobiera pierwszy element z kolekcji wartości.
    LAST () – Pobiera ostatni element z kolekcji wartości.

Uwaga: Wszystkie opisane powyżej funkcje agregujące ignorują wartości NULL, z wyjątkiem funkcji COUNT.

Funkcja skalarna zwraca pojedynczą wartość na podstawie wartości wejściowej. Oto najczęściej używane funkcje skalarne SQL:

    LEN () – Oblicza całkowitą długość danego pola (kolumny).
    UCASE () – Konwertuje zbiór wartości ciągów na wielkie litery.
    LCASE () – Konwertuje zbiór wartości ciągów na małe litery.
    MID () – Wyodrębnia podciągi z kolekcji wartości ciągów w tabeli.
    CONCAT () – Łączy dwa lub więcej ciągów.
    RAND () – Generuje losowy zbiór liczb o podanej długości.
    ROUND () – Oblicza zaokrągloną wartość całkowitą dla pola numerycznego (lub wartości dziesiętnej).
    NOW () – Zwraca bieżące dane i czas.
    FORMAT () – Ustawia format wyświetlania kolekcji wartości.