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:
/* ... */
- egysoros komment:
Adatbázis szerkezete:
- Database
- Table
- Column (Field)
- Row (Record)
- Table
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 theGRANT
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 aNULL
valueUNIQUE
– Ensures that all values in a column are differentPRIMARY KEY
– A combination of aNOT NULL
andUNIQUE
. Uniquely identifies each row in a tableAUTO_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 tablesCHECK
– Ensures that the values in a column satisfies a specific conditionDEFAULT
– Sets a default value for a column if no value is specifiedCREATE 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), vagyNULL
é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 azUPDATE
utasítás esetén, hogy aWHERE
záradék határozza meg, hogy mely sorokat szeretnénk módosítani. Ha aWHERE
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 azUPDATE
esetén, úgy aDELETE
-nél is figyelni kell aWHERE
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
- Jelenítsd meg a varos tábla összes adatát:
SELECT * FROM varos;
- 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éseLCASE()
,UCASE()
: kis- ill. nagybetűssé alakításLEFT()
,RIGHT()
: a string elejéről ill végéről levág egy adott számú karaktertLENGTH()
: string hosszaREPLACE()
: egy szövegrészlet helyettesítése egy másik szöveggelSUBSTRING()
: részsztringet ad vissza egy stringből
numerikus függvények:
SIN()
,COS()
,TAN()
, … : szögfüggvényekCEIL()
,CEILING()
: felfelé kerekítésFLOOR()
: lefelé kerekítésROUND()
: kerekítésMOD()
,DIV()
: maradékos osztás, maradék képzésPOW()
,POWER()
: hatványozásSQRT()
: 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 visszaHOUR()
,MINUTE()
,SECOND()
: egy adott dátumból órát, percet, mp-t adják visszaWEEK()
: hét sorszáma (0..52)WEEKDAY()
: hét napja, 0-monday, 1-tuesday, …