Tananyag: https://www.w3schools.com/sql/default.asp
Tesztkérdések: https://www.w3schools.com/sql/sql_quiz.asp

XAMPP használata

• setup_xampp.bat: egyszer kell futtatni, konfigurációs állományok beállítása
• indítás: xampp-control.exe
• Start Apache, MySQL
• MySQL melletti Admin gomb -> phpMyAdmin-t nyitja meg

Alapfogalmak

• SQL jelentése: Structured Query Language
• RDBMS: Relational Database Management System (Relációs adatbázis kezelő rendszer)
• Az SQL kis/nagybetűre nem érzékeny, de szokás az utasításokat, záradékokat nagybetűvel, táblák, mezők nevét, feltételeket kisbetűkkel írni.
• SQL megjegyzések:
  • egysoros komment: --
  • többsoros komment: /* ... */

Adatbázis szerkezete:

• Database
  • Table
    • Column (Field)
    • Row (Record)

Legfontosabb SQL utasítások és csoportosításuk

DDL (Data Definition Language):

• CREATE: This command is used to create the database or its objects (like table, index, function, views, store procedure, and triggers).
• DROP: This command is used to delete objects from the database.
• ALTER: This is used to alter the structure of the database.
• TRUNCATE: This is used to remove all records from a table. (Maga a tábla nem törlődik!)

DML(Data Manipulation Language):

• INSERT: It is used to insert data into a table.
• UPDATE: It is used to update existing data within a table.
• DELETE: It is used to delete records from a database table.
• LOCK: Table control concurrency.

DQL (Data Query Language):

• SELECT: It is used to retrieve data from the database.

DCL (Data Control Language):

• GRANT: This command gives users access privileges to the database.
• REVOKE: This command withdraws the user’s access privileges given by using the GRANT command.

Adatbázisok kezelése

Adatbázis létrehozása:

CREATE DATABASE adatbázisnév
CHARACTER SET utf8
COLLATE utf8_hungarian_ci;

Adatbázis törlése:

DROP DATABASE adatbázisnév;

Táblák kezelése

Tábla létrehozása

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ...
);

Példa:

CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

Tábla törlése

DROP TABLE table_name;

Tábla csonkolása (Truncate)

A TRUNCATE utasítása törli a sorokat a táblából, de nem törli magát a táblát!

TRUNCATE TABLE table_name;

Tábla modósítása

A táblák az ALTER TABLE utasítással módosíthatók:

• oszlopok hozzáadása, módosítása, törlése egy létező táblában
• különféle kényszerek (constraints) hozzáadása, törlése egy létező táblában

SQL Constraints (kényszerek)

• NOT NULL – Ensures that a column cannot have a NULL value
• UNIQUE – Ensures that all values in a column are different
• PRIMARY KEY – A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
• AUTO_INCREMENT: Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.
• FOREIGN KEY – Prevents actions that would destroy links between tables
• CHECK – Ensures that the values in a column satisfies a specific condition
• DEFAULT – Sets a default value for a column if no value is specified
• CREATE INDEX – Used to create and retrieve data from the database very quickly

Tábla módosítása – Oszlop hozzáadása:

ALTER TABLE table_name
ADD column_name datatype;

Tábla módosítása – Oszlop törlése:

ALTER TABLE table_name
DROP column_name datatype;

Elsődleges kulcs (PRIMARY KEY)

Az elsődleges kulcsot létre lehet hozni:

• tábla létrehozása során
• tábla létrehozása után

Elsődleges kulcs létrehozása a tábla létrehozása során:

CREATE TABLE Persons (
    ID int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)    -- elsődleges kulcs
);

Elsődleges kulcs elnevezése:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CONSTRAINT PK_Person PRIMARY KEY (ID)   -- elsődleges kulcs, elnevezéssel
);

Elsődleges kulcs létrehozása a tábla létrehozása UTÁN:

ALTER TABLE Persons
ADD PRIMARY KEY (ID);

Elnevezéssel:

ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);

Idegen kulcs (FOREIGN KEY)

Az idegen kulcsot létre lehet hozni:

• tábla létrehozása során
• tábla létrehozása után

Idegen kulcs létrehozása a tábla létrehozása során:

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)     --idegen kulcs
);

Elnevezéssel:

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

Idegen kulcs létrehozása a tábla létrehozása UTÁN:

ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

Elnevezéssel:

ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

Adatok kezelése

Új sor beszúrása táblába

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Ha a tábla minden mezőjébe viszünk be adatot, akkor nem kell megadni a mezőneveket, viszont nagyon fontos, hogy az adatok ugyanolyan sorrendben legyenek megadva, mint a mezőnevek a táblában.

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

Megjegyzés:

• Ha nem adunk meg értéket egy mezőnek, akkor az alapértelmezett érték (DEFAULT kényszer), vagy NULL érték kerül beállításra
• Nem kell megadni értéket olyan mezőknek, amelyekre be van állítva az AUTO_INCREMENT kényszer, ezek értéke automatikusan generálódik (egész szám)

Adatsorok módosítása táblában

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE  condition;

Pl.:

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;

A fenti utasítás a nevet és a várost módosítja az 1-es azonosítójú vásárló esetén.

Megjegyzés:
Vigyázat: Nagyon fontos az UPDATE utasítás esetén, hogy a WHERE záradék határozza meg, hogy mely sorokat szeretnénk módosítani. Ha a WHERE záradékot elhagyjuk, akkor a tábla összes sorát módosítja!!!

Adatok törlése táblából

DELETE FROM table_name
WHERE condition;

Pl.:

DELETE FROM Customers
WHERE CustomerName='Alfreds Futterkiste';

Az ‘Alfreds Futterkiste’ nevű vásárló(k) törlése a táblából.

Megjegyzés:
Ahogy az UPDATE esetén, úgy a DELETE-nél is figyelni kell a WHERE záradékra; elhagyása esetén az összes sor törlődik a táblából!!!

Lekérdezések

SELECT utasítás általános alakja:

SELECT [DISTINCT] ...
FROM ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...
LIMIT ...

Egyszerű, egytáblás lekérdezések

1. Jelenítsd meg a varos tábla összes adatát:

   SELECT * FROM varos;

2. Jelenítsd meg a 20000 főnél nagyobb városok nevét és népességét, népesség szerint csökkenő sorrendben:

   SELECT varos.vnev, varos.nepesseg
   FROM varos
   WHERE varos.nepesseg > 20000
   ORDER BY nepesseg DESC;

Egyszerű lekérdezés, számított mező

Jelenítsd meg a 20000 főnél nagyobb városok nevét és népsűrűségét, népsűrűség szerint csökkenő sorrendben:

SELECT `varos`.`vnev`, `varos`.`nepesseg` / `varos`.`terulet` AS `népsűrűség`
FROM `varos`
WHERE `varos`.`nepesseg` > 20000
ORDER BY `népsűrűség` DESC

Többtáblás lekérdezések

Jelenítsük meg a 20000 főnél nagyobb városok nevét, népességét és a megyét. Az adatokat megye szerint növekvő, azon belül népesség szerint csökkenő sorrendbe rendezzük!

SELECT varos.vnev, varos.nepesseg, megye.mnev
FROM varos INNER JOIN megye ON varos.megyeid = megye.id
WHERE varos.nepesseg > 20000 AND varos.nepesseg < 30000
ORDER BY megye.mnev ASC, varos.nepesseg DESC

Egyszerű lekérdezés, összesítő (aggregátum) függvényekkel

Aggregátum függvények:

• SUM()
• AVG()
• MIN(), MAX()
• COUNT()

Számítsuk ki az össznépességet:

SELECT SUM(varos.nepesseg) AS `Össznépesség`
FROM varos

Egyszerű lekérdezés, összesítő (aggregátum) függvényekkel, csoportosított mezővel

Számítsuk ki az össznépességet megyénként, és rendezzük össznépesség szerint csökkenőbe!

SELECT megye.mnev AS `Megye`, SUM(varos.nepesseg) AS `Megye lakossága`
FROM varos INNER JOIN megye ON varos.megyeid = megye.id
GROUP BY megye.mnev
ORDER BY `Megye lakossága` DESC

WHERE záradék

• relációs műveletek: (<, >, =, <=, >=, <>)
• logikai műveletek: AND, OR, NOT
• BETWEEN ... AND ...
• IN(..., ..., ..., ...,)

Sztringek

• '' között
• helyettesítő karakterek:
  • %: bármilyen karakter, tetszőleges számban
  • _: bármilyen karakter, 1 darab
• sztringek összehasonlitása: LIKE, NOT LIKE vs =, <>

SQL függvények

String függvények

• CONCAT() : 2 vagy több string összefűzése
• LCASE(), UCASE(): kis- ill. nagybetűssé alakítás
• LEFT(), RIGHT(): a string elejéről ill végéről levág egy adott számú karaktert
• LENGTH(): string hossza
• REPLACE(): egy szövegrészlet helyettesítése egy másik szöveggel
• SUBSTRING(): részsztringet ad vissza egy stringből

numerikus függvények:

• SIN(), COS(), TAN(), … : szögfüggvények
• CEIL(), CEILING(): felfelé kerekítés
• FLOOR(): lefelé kerekítés
• ROUND(): kerekítés
• MOD(), DIV(): maradékos osztás, maradék képzés
• POW(), POWER(): hatványozás
• SQRT(): gyökvonás

dátum függvények:

• dátum és idő formátum: 'yyyy-mm-dd 00:00:00.000'
• NOW(): aktuális dátum és idő
• YEAR(), MONTH(), DAY(): egy adott dátumból évet, hónapot, napot adják vissza
• HOUR(), MINUTE(), SECOND(): egy adott dátumból órát, percet, mp-t adják vissza
• WEEK(): hét sorszáma (0..52)
• WEEKDAY(): hét napja, 0-monday, 1-tuesday, …