SQL parancsok

SQL Web
Viktor

Amire szükség lesz: Xampp. Ez egy webszerver MySql támogatással. Letölthető az oktatas.hu oldalról az érettségin is használt verziója.

A Xampp control panelen az Apache-ot és a MySql-t kell elindítani. Az Apache egy webszerver, a MySql pedig az adatbázis szerver. Ha elindultak, utána a böngészőbe írd be: //localhost, vagy //127.0.0.1

Az SQL gyakorlásához a telepített MySql-t fogjuk használni a phpMyAdmin felületen keresztül. Bal oldalt látjuk az adatbázisokat, jobb oldalt pl. a tábla rekordjait, vagy SQL parancsokat tudunk kiadni. Szakmai érettségin a kiadott sql parancsokat kell elmenteni egy szöveges fájlba. Az érettségi feladatok az adatbázis létrehozásával szokott kezdődni.

CREATE DATABASE táblaneve DEFAULT CHARACTER SET utf8 COLLATE utf8_hungarian_ci;

Az adatbázis alapértelmezett karakterkódolása UTF-8 lesz, a rendezési sorrendje a magyar szabályok szerinti lesz.

Fontosabb SQL parancsok

  • SELECT – adatok lekérdezése az adatbázisból
  • UPDATE – adatok frissítése
  • DELETE – adatok törlése
  • INSERT INTO – új adatok bevitele az adatbázisba
  • CREATE DATABASE – új adatbázis létrehozása
  • CREATE TABLE – új tábla létrehozása
  • ALTER DATABASE – adatbázis módosítása
  • ALTER TABLE – tábla módosítása
  • DROP DATABASE – adatbázis törlése
  • DROP TABLE – tábla törlése
  • CREATE INDEX – index létrehozása
  • DROP INDEX – index törlése

Lekérdezések szintaxisa tömören

Legtöbbször a választó lekérdezést fogjuk használni, amikor valameilyen feltétel alapján szűrünk, sorba rendezünk rekodrokat.

SELECT mezőnevek FROM táblanév WHERE feltétel(ek)

Ha egy tábla minen rekordját szeretnénk kiíratni használhatju a *-ot
SELECT * FROM tábla

Rendezés: ORDER BY DESC (csökkenő )vagy ORDER BY ASC (növekvő sorrend)

Mennyi adatot írjunk ki: LIMIT 1,1 Az első paraméter a kezdőérték (mettől) 0-tól kezdődik, a második paraméter a mennyit írjon ki.

Csoportosítás után a feltételeket a having után adjuk meg!

Álnevek használata: SELECT mezőnév AS álnév FROM táblanév WHERE feltételek

Számított mezők: mezőnevekkel műveleteket is végezhetünk, pl. összeadhatjuk, kivonhatjuk őket egymásból. Lent pl. a népsűrűség kiszámításánál láthatsz ilyet.

Ismétlődő adatok kizárása: DISTINCT
SELECT DISTINCT mezőnevek FROM tábla …

WHERE után a feltételek megadása: használhatjuk a relációs jeleket, a LIKE, NOT LIKE parancsot, több feltétel összekapcsolásához az AND és OR operátorokat, a BETWEEN-t

Helyettesítő karakterek (ha nem tudjuk pontosan mit keresünk)

  • * vagy % Nulla vagy több karaktert helyettesít. pl: fovaros LIKE „B*” az összes B-vel kezdődő fővárost kiírja
  • ? egy karaktert helyettesít
  • [] A zárójelbe írt karaktereket helyettesíti be pl: Eg[a,é]r meg fogja találni az Egér, Egar szavakat
  • ! tagadás pl: valtopenz != 100
  • – intervallum megadása pl: a-c
  • # numerikus karakter helyettesítése

Tábla létrehozása

CREATE TABLE táblanév (mezők neve, típusa, feltételek)

CREATE TABLE orszagok (
  id int(11) NOT NULL auto_increment,
  orszag varchar(27) collate latin2_hungarian_ci NOT NULL default '',
  fovaros varchar(19) collate latin2_hungarian_ci NOT NULL default '',
  foldr_hely varchar(37) collate latin2_hungarian_ci NOT NULL default '',
  terulet decimal(11,2) NOT NULL default '0.00',
  allamforma varchar(30) collate latin2_hungarian_ci NOT NULL default '',
  nepesseg int(8) NOT NULL default '0',
  nep_fovaros int(8) NOT NULL default '0',
  autojel char(3) collate latin2_hungarian_ci NOT NULL default '',
  country varchar(31) collate latin2_hungarian_ci NOT NULL default '',
  capital varchar(19) collate latin2_hungarian_ci NOT NULL default '',
  penznem varchar(20) collate latin2_hungarian_ci NOT NULL default '',
  penzjel char(3) collate latin2_hungarian_ci NOT NULL default '',
  valtopenz varchar(18) collate latin2_hungarian_ci NOT NULL default '',
  telefon int(3) NOT NULL default '0',
  gdp int(5) NOT NULL default '0',
  kat int(1) NOT NULL default '0',
  PRIMARY KEY  (id)
) ENGINE=MyISAM  DEFAULT CHARSET=latin2 COLLATE=latin2_hungarian_ci AUTO_INCREMENT=195 ;

Adatok beszúrása táblába

Az érettségi feladatoknál kapunk egy sql fájlt, amit vagy importálunk, vagy beillesztünk, így nem nekünk kell létrehozni a táblákat, és egyesével az adatokat felvinni. Ettől függetlenül szokott lenni ilyen feladat.

INSERT INTO tábla VALUES (konkrét értékek felsorolása) Nézzünk egy példát:

INSERT INTO orszagok VALUES (1, 'SPANYOLORSZÁG', 'MADRID', 'Dél-Európa:Ibériai-félsziget', 504782.00, 'alkotmányos monarchia', 42700, 5100, 'E', 'SPANYOLORSZAG ESPANOLA SPAIN', 'MADRID', 'euró', 'EUR', '100 eurocent', 34, 21110, 1);

Módosító lekérdezések: frissítő, törlő, hozzáfűző, tábla készítő

Frissítő lekérdezés

UPDATE tábla SET mezőnév = érték WHERE feltétel

Törlő lekérdezés

DELETE FROM táblanév WHERE feltétel

Függvények:

GROUP BY – csoportosítás

COUNT – megadja a tábla sorainak számát. (megszámlálás)

SUM – megadja a paraméterében szereplő oszlop adatainak az összegét az összes sorra. Csak numerikus attribútumra alkalmazható.

AVG – megadja a paraméterében szereplő oszlop adatainak az átlagát az összes sorra. Csak numerikus attribútumra alkalmazható.

MIN – megadja a paraméterében szereplő oszlop adatainak a minimumát az összes sorra. Csak numerikus attribútumra alkalmazható.

MAX – megadja a paraméterében szereplő oszlop adatainak a maximumát az összes sorra. Csak numerikus attribútumra alkalmazható.

Példák: Forrás adatbázis

Mennyi a váltószáma az aprópénznek azokban az országokban, ahol nem 100? Ez egy viszonylag egyszerű lekérdezés, 1db feltétellel: valtopenz != 100

SELECT orszag, valtopenz FROM orszagok WHERE valtopenz != 100

Hány ország területe kisebb Magyarországénál? A count(*) segítségével megszámoljuk hány rekord felel meg a feltételnek. A feltételnél egy második lekérdezéssel tudjuk meghatározni MO. területét! Feltétel orszag=’Magyarország’ vagy orszag LIKE ‘Magyarország’

SELECT COUNT(*) FROM orszagok WHERE terulet < (SELECT terulet FROM orszagok WHERE orszag = 'Magyarország')

Melyik a legnagyobb területű ország, és mennyi a területe? Hasonlóan az előzőhöz, itt is egy második lekérdezést használva határozzuk meg a legnagyobb ország területét. Lehetne sorba rendezéssel és limit-tel is!

SELECT orszag, terulet FROM orszagok where terulet = (SELECT MAX(terulet) FROM orszagok)
SELETCT orszag, terulet FROM orszagok ORDER BY terulet DESC LIMIT 0,1

Melyik a legkisebb területű ország, és mennyi a területe?

SELECT orszag, terulet FROM orszagok WHERE terulet = (SELECT MIN(terulet) FROM orszagok)

Melyik a legnépesebb ország, és hány lakosa van?

SELECT orszag, nepesseg FROM orszagok WHERE nepesseg = (SELECT MAX(nepesseg) FROM orszagok)

Melyik a legkisebb népességű ország, és hány lakosa van?

SELECT orszag, nepesseg FROM orszagok WHERE nepesseg = (SELECT MIN(nepesseg) FROM orszagok)

Melyik a legritkábban lakott ország, és mennyi a népsűrűsége? Ennél a feladatnál látunk egy számított mezőt, a nepsuruseget. 

SELECT orszag, nepesseg/terulet*1000 AS nepsuruseg FROM orszagok ORDER BY nepsuruseg ASC LIMIT 0,1

Melyik a legnagyobb afrikai ország és mekkora? 

SELECT orszag, foldr_hely, terulet FROM orszagok WHERE foldr_hely LIKE "%Afrika%" ORDER BY terulet DESC LIMIT 0,1

Melyik a legkisebb amerikai ország és hányan lakják?

SELECT orszag, foldr_hely, nepesseg FROM orszagok WHERE foldr_hely LIKE "%Amerika%" ORDER BY terulet ASC LIMIT 0,1

Melyik az első három legsűrűbben lakott „országméretű” ország (tehát nem város- vagy törpeállam)?

SELECT orszag, nepesseg/terulet*1000 AS nepsuruseg FROM orszagok WHERE foldr_hely NOT LIKE "%varosállam%" AND foldr_hely NOT LIKE "%törpeállam%" ORDER BY nepsuruseg ASC LIMIT 0,3

Melyik a világ hat legnépesebb fővárosa?

SELECT fovaros, nep_fovaros FROM orszagok ORDER BY nep_fovaros DESC LIMIT 0,6

Melyik 10 ország GDP-je a legnagyobb?

SELECT orszag, gdp FROM orszagok ORDER BY gdp DESC LIMIT 0,10

Melyik 10 ország össz GDP-je a legnagyobb? (szorozzuk össze a GDP-t a népességgel)

SELECT orszag, gdp*nepesseg AS osszgdp FROM orszagok ORDER BY osszgdp DESC LIMIT 0,10

Melyik országban a legszegényebbek az emberek? (LIMIT 0 mert 0 az első rekord sorszáma)

SELECT orszag, gdp FROM orszagok ORDER BY gdp ASC LIMIT 0,1
Mivel van 0 érték az adatbázisban, azt kiküszöbölve:
SELECT orszag, gdp FROM orszagok WHERE gdp > 0 ORDER BY gdp ASC LIMIT 0,1

Melyik a 40. legkisebb területű ország? (LIMIT kezdő intervalluma 39 mert 0-tól számoz, 1 mert pont csak a 40. érték kell)

SELECT orszag, terulet FROM orszagok ORDER BY terulet ASC LIMIT 39,1