Cheat sheet / MySQL használata

A MySQL egy relációs adatbázis-kezelő rendszer, amely táblákban tárolja az adatokat.

Segítségével adatbázisokat, táblákat, kapcsolatokat, jogosultságokat és lekérdezéseket kezelhetsz.

Ez a cheat sheet a leggyakrabban használt MySQL parancsokat, SQL mintákat és adatbázis-tervezési alapokat tartalmazza.

Alapok

Belépés MySQL-be

mysql -u felhasznalo -p /* Belépés jelszóval */
sudo mysql /* Belépés rootként socket auth esetén */
mysql -h host -P 3306 -u felhasznalo -p /* Távoli/TCP kapcsolat */
EXIT; /* Kilépés */

MySQL parancssori kliens használata. A -p után általában nem írjuk be közvetlenül a jelszót, hanem a MySQL bekéri.

Adatbázisok kezelése

SHOW DATABASES; /* Adatbázisok listázása */
CREATE DATABASE adatbazis_neve CHARACTER SET utf8mb4 COLLATE utf8mb4_hungarian_ci;
USE adatbazis_neve; /* Adatbázis kiválasztása */
SELECT DATABASE(); /* Aktuális adatbázis */
DROP DATABASE adatbazis_neve; /* Adatbázis törlése */

Adatbázis létrehozása, kiválasztása, listázása és törlése. A DROP DATABASE véglegesen töröl, ezért óvatosan használd.

Táblák listázása és szerkezete

SHOW TABLES; /* Táblák listázása */
DESCRIBE tabla_neve; /* Tábla oszlopainak megtekintése */
SHOW COLUMNS FROM tabla_neve;
SHOW CREATE TABLE tabla_neve; /* Teljes CREATE TABLE parancs */

Megmutatja, milyen táblák vannak az aktuális adatbázisban, illetve milyen oszlopokból áll egy tábla.

Megjegyzések SQL-ben

-- Egy soros megjegyzés
# Egy soros megjegyzés MySQL-ben
/* Többsoros
megjegyzés */

Megjegyzések használata SQL parancsokban. A komment nem fut le, csak dokumentációs célt szolgál.

Táblák létrehozása

Egyszerű tábla létrehozása

CREATE TABLE szemelyek (
    id INT AUTO_INCREMENT PRIMARY KEY,
    v_nev VARCHAR(50) NOT NULL,
    k_nev VARCHAR(50) NOT NULL,
    szul_ido DATE,
    szul_hely VARCHAR(100)
);

Tábla létrehozása elsődleges kulccsal és több oszloppal. Az AUTO_INCREMENT automatikusan növeli az id értékét.

Gyakori adattípusok

INT /* Egész szám */
BIGINT /* Nagy egész szám */
DECIMAL(10,2) /* Pontos tizedes szám, pl. pénz */
VARCHAR(255) /* Változó hosszú szöveg */
TEXT /* Hosszabb szöveg */
DATE /* Dátum: YYYY-MM-DD */
DATETIME /* Dátum + idő */
TIMESTAMP /* Időbélyeg */
BOOLEAN /* MySQL-ben TINYINT(1) */
JSON /* JSON adat tárolása */

A leggyakoribb MySQL adattípusok. Relációs adatokhoz inkább normál táblákat használj, JSON-t csak indokolt esetben.

Tábla módosítása

ALTER TABLE tabla ADD COLUMN oszlop VARCHAR(100);
ALTER TABLE tabla MODIFY COLUMN oszlop VARCHAR(200) NOT NULL;
ALTER TABLE tabla CHANGE COLUMN regi_nev uj_nev INT;
ALTER TABLE tabla DROP COLUMN oszlop;
RENAME TABLE regi_tabla TO uj_tabla;
DROP TABLE tabla;

Meglévő tábla szerkezetének módosítása. Éles adatbázisban ALTER/DROP előtt mindig legyen mentés.

Adatkezelés

Adatok beszúrása

INSERT INTO szemelyek (v_nev, k_nev, szul_ido, szul_hely)
VALUES ('Kovács', 'Anna', '1998-05-12', 'Debrecen');

INSERT INTO szemelyek (v_nev, k_nev, szul_ido, szul_hely) VALUES
('Nagy', 'Béla', '1995-01-10', 'Budapest'),
('Tóth', 'Eszter', NULL, 'Szeged');

Egy vagy több sor beszúrása. Szöveget idézőjelbe kell tenni, az ismeretlen értékhez használható a NULL.

Adatok lekérdezése

SELECT * FROM szemelyek;
SELECT v_nev, k_nev FROM szemelyek;
SELECT * FROM szemelyek WHERE szul_hely = 'Debrecen';
SELECT * FROM szemelyek ORDER BY v_nev ASC;
SELECT * FROM szemelyek LIMIT 10;
SELECT * FROM szemelyek LIMIT 10 OFFSET 20;

Adatok kiválasztása, szűrése, rendezése és lapozása.

Adatok módosítása

UPDATE szemelyek
SET szul_hely = 'Budapest'
WHERE id = 1;

UPDATE szemelyek
SET v_nev = 'Szabó', k_nev = 'Péter'
WHERE id = 2;

Adatok frissítése. UPDATE parancsnál a WHERE feltétel különösen fontos, különben minden sor módosulhat.

Adatok törlése

DELETE FROM szemelyek WHERE id = 1;
TRUNCATE TABLE szemelyek; /* Minden sor törlése, gyorsabb */

A DELETE feltétel alapján töröl sorokat. A TRUNCATE kiüríti a teljes táblát, és sok esetben visszaállítja az AUTO_INCREMENT számlálót.

Szűrés és keresés

WHERE feltételek

SELECT * FROM termekek WHERE ar > 1000;
SELECT * FROM termekek WHERE ar BETWEEN 1000 AND 5000;
SELECT * FROM termekek WHERE kategoria IN ('könyv', 'zene');
SELECT * FROM termekek WHERE nev IS NULL;
SELECT * FROM termekek WHERE nev IS NOT NULL;
SELECT * FROM termekek WHERE aktiv = 1 AND keszlet > 0;
SELECT * FROM termekek WHERE aktiv = 0 OR keszlet = 0;

Alap szűrési feltételek számokra, szövegekre, NULL értékekre és logikai kapcsolatokra.

LIKE keresés

SELECT * FROM dalok WHERE cim LIKE 'A%'; /* A-val kezdődik */
SELECT * FROM dalok WHERE cim LIKE '%szeretet%'; /* Tartalmazza */
SELECT * FROM dalok WHERE cim LIKE '_a%'; /* Második karakter a */

Szöveges keresés mintával. Nagy táblánál a kezdő %-os LIKE lassú lehet, mert nehezen indexelhető.

FULLTEXT keresés

ALTER TABLE dalok ADD FULLTEXT(cim, szoveg);

SELECT * FROM dalok
WHERE MATCH(cim, szoveg) AGAINST ('szeretet kegyelem');

SELECT *, MATCH(cim, szoveg) AGAINST ('szeretet') AS relevancia
FROM dalok
WHERE MATCH(cim, szoveg) AGAINST ('szeretet')
ORDER BY relevancia DESC;

Nagyobb szövegek gyors keresésére FULLTEXT index használható. Ez keresésre jó, de nem teljes plágiumdetektor.

Duplikált hosszú szöveg keresése hash-sel

ALTER TABLE szovegek ADD COLUMN text_hash CHAR(64) NOT NULL;
CREATE UNIQUE INDEX uq_text_hash ON szovegek(text_hash);

/* Alkalmazásban számold: SHA-256(normalizált_szöveg) */
SELECT id FROM szovegek WHERE text_hash = ?;

Pontosan azonos szövegek gyors felismerésére érdemes hash-t tárolni és indexelni. Hasonló, de nem teljesen azonos szövegekhez FULLTEXT, n-gram, SimHash vagy embedding alapú megoldás kell.

Kapcsolatok és JOIN-ok

Elsődleges és idegen kulcs

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Az elsődleges kulcs egyedileg azonosít egy sort. Az idegen kulcs kapcsolatot hoz létre két tábla között.

JOIN típusok

SELECT u.email, o.id
FROM users u
INNER JOIN orders o ON o.user_id = u.id;

SELECT u.email, o.id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

SELECT * FROM a
RIGHT JOIN b ON b.a_id = a.id;

INNER JOIN csak a kapcsolódó sorokat adja vissza. LEFT JOIN bal oldali tábla minden sorát visszaadja, akkor is, ha nincs kapcsolódó rekord.

N:N kapcsolat kapcsolótáblával

CREATE TABLE user_roles (
    user_id INT NOT NULL,
    role_id INT NOT NULL,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (role_id) REFERENCES roles(id)
);

Relációs adatbázisban az N:N kapcsolat helyes megoldása kapcsolótábla. CSV vagy JSON mező használata erre általában rossz gyakorlat.

ON DELETE viselkedés

FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT

CASCADE törli a kapcsolódó sorokat, SET NULL nullázza az idegen kulcsot, RESTRICT megakadályozza a törlést, ha van kapcsolódó rekord.

Csoportosítás és aggregálás

Aggregáló függvények

SELECT COUNT(*) FROM szemelyek;
SELECT AVG(ar) FROM termekek;
SELECT MIN(ar), MAX(ar) FROM termekek;
SELECT SUM(mennyiseg) FROM rendeles_tetelek;

Összesítő függvények darabszám, átlag, minimum, maximum és összeg számítására.

GROUP BY és HAVING

SELECT kategoria, COUNT(*) AS db
FROM termekek
GROUP BY kategoria;

SELECT kategoria, COUNT(*) AS db
FROM termekek
GROUP BY kategoria
HAVING COUNT(*) > 5;

A GROUP BY csoportosítja a sorokat. A HAVING a csoportosítás utáni eredményt szűri, míg a WHERE a csoportosítás előtt szűr.

Indexek és teljesítmény

Index létrehozása

CREATE INDEX idx_email ON users(email);
CREATE UNIQUE INDEX uq_email ON users(email);
CREATE INDEX idx_user_date ON orders(user_id, created_at);
DROP INDEX idx_email ON users;

Az index gyorsítja a keresést és rendezést, de lassíthatja az INSERT/UPDATE műveleteket, mert az indexet is karban kell tartani.

Lekérdezés elemzése

EXPLAIN SELECT * FROM users WHERE email = 'a@b.hu';
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1;

Az EXPLAIN megmutatja, hogyan hajtja végre a MySQL a lekérdezést, használ-e indexet, és várhatóan hány sort vizsgál.

Gyakori teljesítmény tippek

/* Indexelj WHERE, JOIN, ORDER BY oszlopokra */
/* Ne kérj SELECT *-ot, ha kevés oszlop kell */
/* Lapozáshoz LIMIT/OFFSET helyett nagy adatoknál keyset pagination jobb lehet */
/* A LIKE '%keresés%' általában lassú nagy táblán */

Nagy adatbázisnál az indexelés és a lekérdezések formája döntően befolyásolja a sebességet.

Felhasználók és jogosultságok

MySQL felhasználó létrehozása

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'eros_jelszo';
ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'uj_eros_jelszo';
DROP USER 'app_user'@'localhost';

A MySQL user az alkalmazás adatbázis-kapcsolatához kell. Nem ugyanaz, mint a weboldal saját felhasználói.

Jogosultság adása és visszavonása

GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_user'@'localhost';
GRANT ALL PRIVILEGES ON app_db.* TO 'app_user'@'localhost';
REVOKE DELETE ON app_db.* FROM 'app_user'@'localhost';
SHOW GRANTS FOR 'app_user'@'localhost';
FLUSH PRIVILEGES;

Projektenként érdemes külön adatbázist és külön MySQL usert használni. A user csak a saját adatbázisához kapjon jogot.

Biztonsági alapelvek

/* Ne használd root usert alkalmazásból */
/* Ne tárold a jelszót publikus Git repóban */
/* Használj prepared statementet SQL injection ellen */
/* Távoli root login tiltása ajánlott */
/* Rendszeres backup kötelező */

Alapvető adatbázis-biztonsági szabályok webes alkalmazásokhoz.

Tranzakciók és zárolás

Tranzakció használata

START TRANSACTION;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
COMMIT;

/* Hiba esetén: */
ROLLBACK;

A tranzakció több műveletet egy egységként kezel. Ha minden sikerül, COMMIT; ha hiba van, ROLLBACK.

Sor zárolása

START TRANSACTION;
SELECT * FROM stock WHERE product_id = 1 FOR UPDATE;
UPDATE stock SET quantity = quantity - 1 WHERE product_id = 1;
COMMIT;

A FOR UPDATE zárolja a kiválasztott sort tranzakción belül. Készletkezelésnél fontos, hogy két folyamat ne írja felül egymás módosítását.

Mentés és visszaállítás

Adatbázis mentése

mysqldump -u user -p adatbazis > backup.sql
mysqldump -u user -p --databases db1 db2 > backup.sql
mysqldump -u user -p --all-databases > all_backup.sql

Adatbázis exportálása SQL fájlba. Éles rendszernél automatizált, rendszeres backup szükséges.

Adatbázis visszaállítása

mysql -u user -p adatbazis < backup.sql
mysql -u user -p < all_backup.sql

SQL mentés visszatöltése. Visszaállítás előtt ellenőrizd, hogy jó adatbázisba importálsz-e.

CSV import

LOAD DATA LOCAL INFILE 'adatok.csv'
INTO TABLE szemelyek
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(v_nev, k_nev, szul_ido, szul_hely);

CSV fájl importálása táblába. Google Táblázat export után hasznos lehet.

PHP kapcsolódás

PDO kapcsolat

$pdo = new PDO(
    'mysql:host=127.0.0.1;dbname=test;charset=utf8mb4',
    'app_user',
    'jelszo',
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);

PDO-val modern, biztonságos adatbázis-kapcsolat hozható létre. A hibákat érdemes exceptionként kezelni.

Prepared statement

$sql = 'INSERT INTO szemelyek (v_nev, k_nev, szul_ido, szul_hely)',
      . ' VALUES (:v_nev, :k_nev, :szul_ido, :szul_hely)';

$stmt = $pdo->prepare($sql);
$stmt->execute([
    ':v_nev' => $_POST['v_nev'],
    ':k_nev' => $_POST['k_nev'],
    ':szul_ido' => $_POST['szul_ido'],
    ':szul_hely' => $_POST['szul_hely']
]);

Felhasználói inputot soha ne fűzz közvetlenül SQL stringbe. Prepared statement használata védi az alkalmazást SQL injection ellen.