HOWTO - SQL
Kvalitní kniha o SQL: Martin Gruber: Mistrovství v SQL, SoftPress 2004
Vytvoření tabulky
-- Vlastnosti sloupců -- NOT NULL -- DEFAULT -- UNIQUE -- PRIMARY KEY -- REFERENCES -- CHECK -- Není funkční příklad (všechno pohromadě) CREATE TABLE tabulka ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, jmeno VARCHAR(20) NOT NULL UNIQUE, cislo INT CHECK (cislo < 50), mesto VARCHAR(20) DEFAULT = 'Praha' CHECK (mesto IN ('Praha', 'Brno')), cizi_klic INT UNSIGNED NOT NULL REFERENCES jina_tabulka(id), -- nebo omezeni zvlast PRIMARY KEY (id), UNIQUE (jmeno), CHECK (cislo < 50), CHECK (mesto IN ('Praha', 'Brno')) FOREIGN KEY (cizi_klic) REFERENCES jina_tabulka(id) ) ENGINE=INNODB;
Zrušení tabulky
DROP TABLE tabulka [CASCADE CONSTRAINTS]
Změna struktury tabulky
ALTER TABLE tabulka ADD nový_sloupec vlastnosti_sloupce; ALTER TABLE tabulka ALTER COLUMN sloupec ADD DEFAULT 'implicitní_hodnota'; ALTER TABLE tabulka ADD CONSTRAINT io | DROP CONSTRAINT io ...
Přidání cizích klíčů
ALTER TABLE tabulka ADD(FOREIGN KEY(sloupec) REFERENCES jina_tabulka);
Zrušení tabulky
DROP TABLE tabulka
Spouštěné referenční akce
-- ON UPDATE -- ON DELETE -- CASCADE cizí klíč se změní zároveň s rodičovským -- SET NULL -- SET DEFAULT -- NO ACTION CREATE TABLE `sessions` ( `id_user` INT UNSIGNED NOT NULL , FOREIGN KEY ( `id_user` ) REFERENCES users ( id_user ) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE=INNODB;
Vložení řádku
INSERT INTO tabulka (položky) VALUES (hodnoty);
Vkládání s využitím dat z jiné tabulky
INSERT INTO admins (id_user, id_webpage) SELECT (SELECT id_user FROM users WHERE login='root'), id_webpage FROM webpages; INSERT INTO dnycelkem(datum, celkem) SELECT datum, SUM(hodnota) FROM objednavky GROUP BY datum; -- Naplnění hned při vytvoření CREATE TABLE kolik_kopii (rod_c CHAR(10), pocet SMALLINT) AS SELECT rod_c, COUNT(c_kopie) FROM vypujcky GROUP BY rod_c;
Smazání řádků
DELETE FROM tabulka WHERE omezení_řádků;
Aktualizace hodnot
UPDATE tabulka SET sloupec = hodnota WHERE omezení_řádků; UPDATE tabulka SET sloupec = sloupec*1.1 WHERE omezení_řádků;
Základní dotazy
-- Všechny sloupce SELECT * FROM tabulka; -- Určité sloupce SELECT sloupce FROM tabulka; -- Jedinečné řádky, zpomaluje! SELECT DISTINCT sloupce FROM tabulka; -- Omezení řádků -- =, >, <, <=, <=, <> AND, OR, NOT SELECT sloupce FROM tabulka WHERE podmínky;
Speciální operátory
-- Test více hodnot najednou SELECT sloupce FROM tabulka WHERE mesto = 'Praha' OR mesto = 'Brno'; SELECT sloupce FROM tabulka WHERE mesto IN ('Praha', 'Brno'); -- Rozsah, včetně okrajových hodnot -- První musí být menší než druhá, převádí se to na >= AND <= SELECT sloupce FROM tabulka WHERE sloupec BETWEEN 50 AND 100; -- Rozsah, bez okrajových hodnot SELECT sloupce FROM tabulka WHERE sloupec BETWEEN 50 AND 100 AND NOT sloupec IN (50, 100); -- Neúplně specifikované řetězce -- '_' právě jeden znak -- '%' nula a více znaků SELECT sloupce FROM tabulka WHERE retezec LIKE 'P%'; -- Escape znaky -- hledá dvojici _/ SELECT sloupce FROM tabulka WHERE retezec LIKE '%/_//%' ESCAPE '/'; -- Výběr NULLových hodnot SELECT sloupce FROM tabulka WHERE sloupec IS (NOT) NULL;
Agregační funkce
-- COUNT(sloupec) počet vybraných řádků -- SUM(sloupec) součet hodnot -- AVG(sloupec) aritmetický průměr hodnot -- MAX(sloupec) maximální hodnota -- MIN(sloupec) minimální hodnota -- Počet řádků v tabulce SELECT COUNT(*) FROM tabulka; -- Počet různých hodnot ve sloupci -- Bez DISTINCT počet neNULLových hodnot SELECT COUNT(DISTINCT sloupec) FROM tabulka; -- Součet plateb za jednotlivé dny SELECT datum, SUM(platby) FROM tabulka GROUP BY datum; -- Počet (např. zpráv ve fóru) za jednotlivé dny SELECT datum, COUNT(*) FROM messages GROUP BY datum; -- Pouze dny, ve kterých byl počet zpráv větší než deset -- WHERE se provádí nad řádky původní tabulky -- HAVING nad řádky s agregovanými hodnotami SELECT datum, COUNT(*) FROM messages GROUP BY datum HAVING COUNT(*) > 10; -- Průměr ze všech řádků, včetně NULLových SELECT AVG(COALESCE(cena, 0)) FROM tabulka;
Pojmenování výstupních sloupců, vkládání textu do výstupu
SELECT sloupec * 100 AS procentualne, '%' FROM tabulka;
Datum, čas, v každém SQL je to trochu jinak :-(
-- Tohle mi fungovalo v MySQL RRRR-MM-DD HH:MM:SS 2004-08-28 11:27:46 -- A tohle je z jiného zdroje (asi IBM cosi) DD/MM/RRRR HH:MM:SS -- Hodnotový výraz INTERVAL YEAR TO MONTH '4/11' -- 4 roky a 11 měsíců po určitém datu DATE '2/4/98' + INTERVAL YEAR TO MONTH '4/11' -- Interval z dvou dat (vytvoří INTERVAL DAY '2') DATE '2/4/98' - DATE '2/2/98'
Přetypování
CAST ('12/03/2001' AS DATE)
Podmíněné výrazy
-- Dvě možnosti, nelze je míchat SELECT CASE jmeno WHEN 'Bžeťa' THEN 'Bženda' ELSE jmeno END FROM tabulka; SELECT CASE WHEN jmeno = 'Bžeťa' THEN 'Bženda' ELSE jmeno END FROM tabulka; -- Když jsou obě hodnoty stejné, vrátí NULL SELECT NULLIF(jmeno, 'Bžeťa') FROM tabulka; -- Vrátí první z hodnot, která není NULLová -- Pokud jsou všechny NULL, vrátí NULL SELECT COALESCE(prijmeni, login, jmeno) FROM tabulka; -- Autoincrement ;-) -- Pokud nemá žádný řádek, inkrementuje se nula, jinak maximální ID -- Problém byl s vložením prvního řádku INSERT INTO tabulka (id) VALUES(1 + COALESCE(SELECT MAX(id) FROM tabulka, 0));
Řazení výstupu
-- ASC vzestupně (implicitní) -- DESC sestupně SELECT * FROM tabulka ORDER BY sloupec1 DESC, sloupec2 ASC;
Přirozené spojení tabulek
SELECT t1.sloupce, t2.sloupce FROM t1, t2 WHERE t1.mesto = t2.mesto;
Speciální operátory spojení
-- CROSS JOIN křížové spojení (celý kartézský součin) -- NATURAL JOIN přirozené spojení (podle jména!) -- UNION JOIN všechny řádky z tabulky A s NULL ve sloupcích z B a naopak -- INNER JOIN vnitřní spojení (INNER je implicitní, neuvádí se) -- OUTER JOIN vnější spojení, obsahuje i NULL hodnoty -- LEFT OUTER JOIN všechny řádky z A spárované nebo nespárované s B -- RIGHT OUTER JOIN všechny řádky z B spárované nebo nespárované s A -- FULL OUTER JOIN všechny řádky z obou, nespojené části mají NULL -- Přirozené spojení (klasicky) SELECT sloupce FROM t1 a, t2 b WHERE a.mesto = b.mesto; -- Přirozené spojení (podle jmen sloupců) SELECT sloupce FROM t1 a NATURAL JOIN t2 b; -- Přirozené spojení pomocí predikátu v ON SELECT sloupce FROM t1 a, t2 b ON a.mesto = b.mesto WHERE podmínka; -- Přirozené spojení pomocí USING se jménem sloupce SELECT sloupce FROM t1 a, t2 b USING (mesto) WHERE podmínka; -- Všichni prodejci s objednávkami k určitému datu -- Kdyby nebylo vnější, vynechali by se prodejci, kteří neměli objednávky SELECT sloupce FROM prodejci NATURAL LEFT OUTER JOIN objednavky WHERE datum = '10/03/2000';
Poddotazy, vhnízďování dotazů
-- Celkový počet zpráv zaslaných do diskuze ke článku clanek.php SELECT count(*) FROM messages WHERE id_webpage = (SELECT id_webpage FROM webpages WHERE filename = 'clanek.php'); -- Poddotaz musí generovat právě jednu hodnotu -- Pokud jich generuje víc, musí se použít místo = operátor IN -- Korelované poddotazy: parametry z vnějšího dotazu se používají i ve vnitřním
Poddotazové operátory
-- EXIST true, pokud poddotaz něco nalezl -- ANY/SOME true, pokud se rovná některé z vrácených hodnot -- ALL podmínka platí pro všechny -- UNIQUE unikátní výstup, právě jedna výstupní hodnota -- Vybrat zakazniky, pokud je alespoň jeden z nich z Morávky SELECT * FROM zakaznici WHERE EXISTS (SELECT * FROM zakaznici WHERE city='Morávka'); -- Všichni prodejci, kteří mají alespoň jednoho zákazníka ze stejného města -- U ANY lze použít třeba menší než, u IN ne SELECT * FROM prodejci WHERE city IN (SELECT city FROM zakaznici); SELECT * FROM prodejci WHERE city = ANY (SELECT city FROM zakaznici); -- All pomocí EXIST -- Najdi kino, které hraje všechna představení -- Najdi takové kino, pro něž neexistuje představení, -- které není dáváno tímto kinem SELECT název_k FROM Kina K WHERE NOT EXISTS (SELECT * FROM Představení P WHERE K.název_k <> P.název_k);
Slučování dotazů
-- UNION sloučí výstup ze dvou dotazů do jednoho, automaticky vylučuje duplikáty -- UNION ALL nevylučuje duplikáty -- UNION CORRESPONDING ??? (SELECT * ... UNION CORRESPONDING SELECT * ...) -- INTERSECT průnik výstupu dvou dotazů -- EXCEPT odečte výstup druhého dotazu od prvního (někdy jako MINUS) -- Všichni prodejci a zákazníci z Morávky SELECT jmeno FROM prodejci WHERE city = "Morávka" UNION SELECT jmeno FROM zákazníci WHERE city = "Morávka" ORDER BY 1; -- sloupce nemají jména
Postupné dotazování
WITH r1 AS SELECT nazev_k FROM program, r2 AS SELECT jmeno_f FROM film WHERE herec='Bžeťa', r AS SELECT * FROM r1 CROSS JOIN r2, s AS (SELECT * FROM r) EXCEPT (SELECT nazev_k, jmeno_f FROM program), t AS SELECT DISTINCT nazev_k FROM s (SELECT DISTINCT nazev_k FROM program) EXCEPT (SELECT * FROM t);
Pohledy
CREATE VIEW lidi_z_moravky AS SELECT name FROM lidi WHERE city = 'Moravka'; -- Odteď se může lidi_z_moravky používat, jako kdyby to byla klasická tabulka -- Aby šlo UPDATE a INSERT, musí to být jednoznačná operace
Přístupová práva
-- ALTER -- SELECT -- INSERT -- UPDATE -- DELETE -- REFERENCES -- INDEX -- DROP -- ALL PRIVILEGES -- V příkladech je Bženda vlastníkem schématu (tabulek) GRANT SELECT ON tabulka TO Bžéťa; GRANT SELECT, UPDATE(sloupce) ON tabulka TO Bžéťa; GRANT SELECT ON tabulka TO PUBLIC; -- všem uživatelům -- Bžéťa bude moct taky udělovat práva GRANT SELECT ON tabulka TO Bžéťa WITH GRANT OPTION; -- Tohle už dělá Bžéťa GRANT SELECT ON Bženda.tabulka TO někdo_s_Bž_na_začátku; -- Odebrání práv Bžéťovi REVOKE UPDATE ON tabulka FROM Bžéťa
Transakce
COMMIT WORK; -- změny se stanou trvalé ROLLBACK WORK; -- zahození změn -- Možná se to jmenuje jinak :-( SET AUTOCOMMIT ON; -- zapne automatické potvrzování změn SET AUTOCOMMIT OFF; -- vypne
Funkce pro datum a čas (možná jenom MySQL)
HOUR(soupec) MINUTE(soupec) SECOND(soupec) DAYNAME(soupec) DAYOFMONTH(soupec) MONTHNAME(soupec) MONTH(soupec) YEAR(soupec) ADDDATE(sloupec, INTERVAL x typ) SUBDATE(sloupec, INTERVAL x typ) CURDATE() CURTIME() NOW() UNIX_TIMESTAMP(datum) DATE_FORMAT(sloupec, '%d.%m.%Y') -- DD.MM.YYY TIME_FORMAT(sloupec, '%H:%i:%s') -- HH:MM:SS
Copyright © 2001-2011 Michal Turek <WOQ (zavináč) seznam.cz>
Valid XHTML 1.0 Strict, Valid CSS