SQL összefoglaló

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, …

Vélemény, hozzászólás?

Az e-mail-címet nem tesszük közzé.