Odpověď z MediaWiki API
This is the HTML representation of the JSON format. HTML is good for debugging, but is unsuitable for application use.
Specify the format parameter to change the output format. To see the non-HTML representation of the JSON format, set format=json.
See the complete documentation, or the API help for more information.
{
"batchcomplete": "",
"continue": {
"gapcontinue": "SQLite",
"continue": "gapcontinue||"
},
"warnings": {
"main": {
"*": "Subscribe to the mediawiki-api-announce mailing list at <https://lists.wikimedia.org/postorius/lists/mediawiki-api-announce.lists.wikimedia.org/> for notice of API deprecations and breaking changes."
},
"revisions": {
"*": "Because \"rvslots\" was not specified, a legacy format has been used for the output. This format is deprecated, and in the future the new format will always be used."
}
},
"query": {
"pages": {
"4114": {
"pageid": 4114,
"ns": 0,
"title": "Rozd\u00edly v syntaxi mezi GNU Octave a Matlabem",
"revisions": [
{
"contentformat": "text/x-wiki",
"contentmodel": "wikitext",
"*": "{{upravit}}\n\nJevy budeme postupn\u011b dopl\u0148ovat.\n\n{| class=\"border\"\n! jev !! GNU Octave !! Matlab\n|-\n| rows() || ano || ne\n|-\n| columns() || ano || ne\n|-\n| koment\u00e1\u0159 || % nebo # || %\n|-\n| printf() || ano || ne\n|-\n| \u0159et\u011bzec || \" nebo ' || '\n|-\n| += || ano || ne\n|-\n| logick\u00e9 AND || && nebo & || &\n|-\n| logick\u00e9 OR || <nowiki>||</nowiki> nebo <nowiki>|</nowiki> || <nowiki>|</nowiki>\n|}\n\n{{Geoinformatika}}"
}
]
},
"2138": {
"pageid": 2138,
"ns": 0,
"title": "SQL s datab\u00e1zov\u00fdm syst\u00e9mem PostgreSQL",
"revisions": [
{
"contentformat": "text/x-wiki",
"contentmodel": "wikitext",
"*": "Tento \u010dl\u00e1nek byl p\u0159evzat z [http://www.pgsql.cz/index.php/%C3%9Avod_do_PostgreSQL_SQL www.pgsql.cz] a n\u00e1sledn\u011b modifikov\u00e1n.\n\nPostgreSQL podporuje kompletn\u00ed reperto\u00e1r p\u0159\u00edkaz\u016f ANSI SQL92 a \u010d\u00e1ste\u010dn\u011b ANSI SQL2003. Tento \u010dl\u00e1nek p\u0159edstavuje p\u0159edstavuje jednoduch\u00fd \u00favod do SQL v\u010detn\u011b z\u00e1kladn\u00ed terminologie a n\u011bkolika rad. P\u0159\u00edklady u\u017eite\u010dn\u00fdch SQL p\u0159\u00edkaz\u016f naleznete v \u010dl\u00e1nku [http://www.pgsql.cz/index.php/SQL_Triky SQL Triky].\n\n== SQL ==\n''Structured Query Language''\n\n[[SQL]] je v sou\u010dasnosti nejroz\u0161\u00ed\u0159en\u011bj\u0161\u00edm dotazovac\u00edm (neprocedur\u00e1ln\u00edm) programovac\u00edm jazykem ur\u010den\u00fdm k definici, \u00fadr\u017eb\u011b a vyhled\u00e1v\u00e1n\u00ed dat v rela\u010dn\u00edch datab\u00e1z\u00edch. V roce 1970 publikoval Dr. E. F. Codd svou fundament\u00e1ln\u00ed teoretickou pr\u00e1ci o rela\u010dn\u00edm datov\u00e9m modelu. Jeho pr\u00e1ce se stala z\u00e1kladem v\u00fdzkumn\u00e9ho projektu Syst\u00e9m/R, kter\u00fd prob\u00edhal v druh\u00e9 polovin\u011b sedmdes\u00e1t\u00fdch let v laborato\u0159\u00edch fy. IBM. V r\u00e1mci tohoto projektu vznikl i jazyk SQL. Na p\u0159elomu sedmdes\u00e1t\u00fdch a osmdes\u00e1t\u00fdch ji\u017e existovaly prvn\u00ed komer\u010dn\u00ed verze st\u00e1vaj\u00edc\u00edch RDBMS syst\u00e9m\u016f Oracle a IBM. \n\nV roce 1986 byl vytvo\u0159en prvn\u00ed ANSI standard jazyka. Posledn\u00edm ANSI standardem je ANSI SQL:2003. Po\u017eadavky ANSI SQL:2003 spl\u0148uje jen nemnoho RDBMS. Zat\u00edm nejroz\u0161\u00ed\u0159en\u011bj\u0161\u00ed RDBMS dodr\u017euj\u00ed ANSI SQL:1999 p\u0159\u00edpadn\u011b star\u0161\u00ed ANSI SQL:1992. PostgreSQL podporuje \u00fapln\u011b ANSI SQL:1999 a \u010d\u00e1ste\u010dn\u011b ANSI SQL:2003.\n\n=== Pravidla pro normalizaci datab\u00e1ze ===\nJazyk SQL je ur\u010den pro datab\u00e1ze, kter\u00e9 pou\u017e\u00edvaj\u00ed tzv. rela\u010dn\u00ed datab\u00e1zov\u00fd model. V n\u011bm jsou data ulo\u017een\u00e1 v tabulk\u00e1ch s jasn\u011b definovanou a relativn\u011b nem\u011bnnou strukturou. Datov\u00fd z\u00e1znam odpov\u00edd\u00e1 \u0159\u00e1dku jedn\u00e9 nebo n\u011bkolika tabulek. Datov\u00fd atribut pak pr\u016fniku konkr\u00e9tn\u00edho sloupce a konkr\u00e9tn\u00edho \u0159\u00e1dku tabulky. A\u017e na n\u011bkolik nepodstatn\u00fdch vy j\u00edmek plat\u00ed, \u017ee jedna bu\u0148ka tabulky uchov\u00e1v\u00e1 jednu hodnotu. Data se v tabulk\u00e1ch ukl\u00e1daj\u00ed v p\u0159edepsan\u00e9m form\u00e1tu a typu. Separac\u00ed dat do samostatn\u00fdch tabulek se sna\u017e\u00edme p\u0159edej\u00edt redundanci dat. P\u0159\u00edkladem chybn\u011b navr\u017een\u00e9 datov\u00e9 tabulky m\u016f\u017ee b\u00fdt tato (poru\u0161uje podm\u00ednky tzv. norm\u00e1ln\u00edch forem):\n* 0NF (nult\u00e1 norm\u00e1ln\u00ed forma): Tabulka je v nult\u00e9 norm\u00e1ln\u00ed form\u011b pr\u00e1v\u011b tehdy, existuje-li alespo\u0148 jedno pole, kter\u00e9 obsahuje v\u00edce ne\u017e jednu hodnotu.\n* 1NF (prvn\u00ed norm\u00e1ln\u00ed forma): Tabulka je v prvn\u00ed norm\u00e1ln\u00ed form\u011b, jestli\u017ee lze do ka\u017ed\u00e9ho pole dosadit pouze jednoduch\u00fd datov\u00fd typ (jsou d\u00e1le ned\u011bliteln\u00e9).\n* 2NF (druh\u00e1 norm\u00e1ln\u00ed forma): Tabulka je ve druh\u00e9 norm\u00e1ln\u00ed form\u011b, jestli\u017ee je v prvn\u00ed a nav\u00edc plat\u00ed, \u017ee existuje kl\u00ed\u010d a v\u0161echna nekl\u00ed\u010dov\u00e1 pole jsou funkc\u00ed cel\u00e9ho kl\u00ed\u010de (a tedy ne jen jeho \u010d\u00e1st\u00ed).\n* 3NF (t\u0159et\u00ed norm\u00e1ln\u00ed forma): Tabulka je ve t\u0159et\u00ed norm\u00e1ln\u00ed form\u011b, jestli\u017ee ka\u017ed\u00fd nekl\u00ed\u010dov\u00fd atribut nen\u00ed transitivn\u011b z\u00e1visl\u00fd na \u017e\u00e1dn\u00e9m kl\u00ed\u010di sch\u00e9matu neboli je-li ve druh\u00e9 norm\u00e1ln\u00ed form\u011b a z\u00e1rove\u0148 neexistuje jedin\u00e1 z\u00e1vislost nekl\u00ed\u010dov\u00fdch sloupc\u016f tabulky.\n<pre>\npostgres=# select * from rodina;\n rodic | dite\n----------------+----------------\n Jind\u0159ich Such\u00fd | Jind\u0159ich Such\u00fd\n Jind\u0159ich Such\u00fd | Lenka Such\u00e1\n(2 rows)\n</pre>\nNaopak p\u0159\u00edkladem dob\u0159e navr\u017een\u00e9 sady tabulek m\u016f\u017ee b\u00fdt:\n<pre>\npostgres=# SELECT * FROM rodic;\n id | jmeno | prijmeni\n----+----------+----------\n 1 | Jind\u0159ich | Such\u00fd\n(1 row)\n\npostgres=# SELECT * FROM dite;\n id | rodic | jmeno | prijmeni\n----+-------+----------+----------\n 1 | 1 | Lenka | Such\u00e1\n 2 | 1 | Jind\u0159ich | Such\u00fd\n(2 rows)\n\npostgres=# SELECT rodic.*, dite.* FROM rodic JOIN dite ON rodic.id = dite.rodic;\n id | jmeno | prijmeni | id | rodic | jmeno | prijmeni\n----+----------+----------+----+-------+----------+----------\n 1 | Jind\u0159ich | Such\u00fd | 1 | 1 | Lenka | Such\u00e1\n 1 | Jind\u0159ich | Such\u00fd | 2 | 1 | Jind\u0159ich | Such\u00fd\n(2 rows)\n</pre>\n3NF dosahujeme d\u011blen\u00edm dat do samostatn\u00fdch tabulek. Po\u010det sloup\u016f tabulky by se m\u011bl pohybovat mezi dv\u011bma a\u017e osmi. Za norm\u00e1ln\u00edch okolnost\u00ed (99%) by v\u0161echny tabulky s kter\u00fdmi pracujeme m\u011bly b\u00fdt v 3NF. Nepou\u017e\u00edvejte \u0161irok\u00e9 tabulky. Co u\u0161et\u0159\u00edte na spojen\u00ed tabulek ztrat\u00edte na pomalej\u0161\u00edm \u010dten\u00ed tabulky - s d\u00e9lkou z\u00e1znamu kles\u00e1 po\u010det z\u00e1znamu na str\u00e1nce a roste po\u010det \u010dten\u00ed (p\u0159\u00edstup\u016f na disk). To, \u017ee se nezobrazuj\u00ed v\u0161echny z\u00e1znamy v tabulce neznamen\u00e1, \u017ee se nena\u010d\u00edtaj\u00ed z disku.\n\n=== Kardinalita ===\nRela\u010dn\u00ed datov\u00fd model umo\u017e\u0148uje jednodu\u0161e pracovat s tabulkami, jejich\u017e vz\u00e1jemn\u00fd vztah, kter\u00fd ozna\u010dujeme jako kardinalitu, je 1:1 (jednomu z\u00e1znamu jedn\u00e9 tabulky odpov\u00edd\u00e1 jeden z\u00e1znam druh\u00e9 tabulky) 1:n (jednomu z\u00e1znamu prv\u00e9 tabulky odpov\u00edd\u00e1 n (\u017e\u00e1dn\u00fd nebo v\u00edce z\u00e1znam\u016f z druh\u00e9 tabulky). V SQL syst\u00e9mech je tento vztah explicitn\u011b ur\u010den rovnost\u00ed hodnot n\u011bkter\u00fdch sloupc\u016f (kl\u00ed\u010d\u016f) v tabulk\u00e1ch. V p\u0159\u00edpad\u011b, \u017ee slu\u010dujeme tabulky, mus\u00edme tuto rovnost explicitn\u011b uv\u00e9st jako logick\u00fd v\u00fdraz v SQL p\u0159\u00edkazu:\n<pre>\nSELECT * FROM rodic JOIN dite ON rodic.id = dite.rodic_id;\n</pre>\nKl\u00ed\u010d z nez\u00e1visl\u00e9 tabulky (rodic) ozna\u010dujeme jako ''prim\u00e1rn\u00ed kl\u00ed\u010d'', kl\u00ed\u010d ze z\u00e1visl\u00e9 tabulky ozna\u010dujeme jako ''ciz\u00ed'' kl\u00ed\u010d. Po\u017eadavkem na prim\u00e1rn\u00ed kl\u00ed\u010d je jeho jedine\u010dnost (\u017e\u00e1dn\u00e1 hodnota se nesm\u00ed opakovat). Od ciz\u00edho kl\u00ed\u010de po\u017eadujeme jeho integritu s prim\u00e1rn\u00edm kl\u00ed\u010dem (hodnoty se mohou opakovat, ale nesm\u00ed se vyskytnout \u017e\u00e1dn\u00e1 hodnota, kter\u00e1 se nevyskytuje v prim\u00e1rn\u00edm kl\u00ed\u010di). Od SQL syst\u00e9m\u016f o\u010dek\u00e1v\u00e1me, \u017ee n\u00e1m dok\u00e1\u017ee zajistit spln\u011bn\u00ed t\u011bchto po\u017eadavk\u016f, tzv. zaji\u0161t\u011bn\u00ed referen\u010dn\u00ed integrity.\n\nKrom\u011b vazeb 1:1 a 1:n existuje je\u0161t\u011b vazba n:m. Abychom mohli tento vztah vyj\u00e1d\u0159it, pot\u0159ebujeme pomocnou tzv. asocia\u010dn\u00ed tabulku, kter\u00e1 obsahuje pouze (v obvykl\u00fdch p\u0159\u00edpadech) dva ciz\u00ed kl\u00ed\u010de. Na p\u0159\u00edkladu relace mezi entitami AUTOR a KNIHA uvedu v\u0161echny mo\u017en\u00e9 interpretace:\n* 1:1 - ka\u017ed\u00fd autor napsal jednu knihu a nesm\u00ed napsat \u017e\u00e1dnou dal\u0161\u00ed,\n* 1:n - ka\u017ed\u00fd autor sm\u00ed vydat n\u011bkolik knih (limitn\u00edm p\u0159\u00edpadem je \u017e\u00e1dn\u00e1 kniha),\n* n:m - ka\u017ed\u00fd autor sm\u00ed vydat n\u011bkolik knih, na jedn\u00e9 knize se sm\u00ed pod\u00edlet v\u00edce autor\u016f.\n<pre>\nSELECT autor.*, kniha.* \n FROM autor a \n JOIN autor_kniha ak ON a.id = ak.autor_id \n JOIN kniha k ON ak.kniha_id = k.id; \n</pre>\n\n=== DDL ===\nJazyk SQL se skl\u00e1d\u00e1 z n\u011bkolika p\u0159\u00edkaz\u016f rozd\u011blen\u00fdch do dvou z\u00e1kladn\u00edch skupin: p\u0159\u00edkazy pro definici datov\u00fdch struktur (Data Definition Language) a p\u0159\u00edkazy pro pr\u00e1ci s obsahem tabulek (Data Modeling Language).\n\nDDL m\u00e1 deklarativn\u00ed charakter a umo\u017e\u0148uje n\u00e1m vytv\u00e1\u0159et, modifikovat a ru\u0161it datab\u00e1zov\u00e9 objekty: tabulky, pohledy, indexy, jmenn\u00e9 prostory, tabulkov\u00e9 prostory, datab\u00e1ze, ulo\u017een\u00e9 procedury a funkce, triggery a u\u017eivatele. Objekty vytv\u00e1\u0159\u00edme p\u0159\u00edkazem CREATE, ru\u0161\u00edme p\u0159\u00edkazem DROP, p\u0159id\u00e1v\u00e1me p\u0159\u00edstupov\u00e1 pr\u00e1va p\u0159\u00edkazem GRANT nebo naopak p\u0159\u00edkazem REVOKE p\u0159\u00edstupov\u00e1 pr\u00e1va odeb\u00edr\u00e1me. Vlastnosti objekt\u016f m\u011bn\u00edme p\u0159\u00edkazem ALTER. Ka\u017ed\u00fd SQL p\u0159\u00edkaz se skl\u00e1d\u00e1 z n\u011bkolika voliteln\u00fdch \u010d\u00e1st\u00ed; mus\u00edme ale respektovat logick\u00e9 vazby mezi t\u011bmito \u010d\u00e1stmi a mus\u00edme tyto \u010d\u00e1sti zapisovat ve spr\u00e1vn\u00e9m po\u0159ad\u00ed nap\u0159. klauzule WHERE je za klauzul\u00ed FROM a p\u0159ed klauzulemi GROUP BY a ORDER BY.\n<pre>\nCREATE TABLE dite (\n id SERIAL PRIMARY KEY,\n rodic INTEGER NOT NULL REFERENCES rodic(id),\n jmeno VARCHAR(15) NOT NULL CHECK jmeno <> '',\n prijmeni VARCHAR(15) NOT NULL CHECK prijmeni <> ''\n);\n\nALTER TABLE dite ADD COLUMN vek INTEGER NOT NULL CHECK vek > 0;\nGRANT SELECT ON TABLE dite TO public;\n</pre>\n\n=== DML ===\nDML p\u0159\u00edkazy slou\u017e\u00ed k manipulaci s daty v datab\u00e1zi. ANSI SQL:1999 definije p\u0159\u00edkazy INSERT, UPDATE, DELETE, a SELECT. Tato z\u00e1kladn\u00ed sada je je\u0161t\u011b v ANSI SQL:2003 roz\u0161\u00ed\u0159ena o p\u0159\u00edkaz MERGE (nen\u00ed podporov\u00e1n PostgreSQL).\n* P\u0159\u00edkaz INSERT se pou\u017e\u00edv\u00e1 pro p\u0159id\u00e1v\u00e1n\u00ed nov\u00fdch z\u00e1znam\u016f do tabulky\n* P\u0159\u00edkaz UPDATE se pou\u017e\u00edv\u00e1 pro aktualizaci existuj\u00edc\u00edch z\u00e1znam\u016f v tabulce\n* P\u0159\u00edkaz DELETE se pou\u017e\u00edv\u00e1 pro odstran\u011bn\u00ed z\u00e1znam\u016f z tabulky\n* P\u0159\u00edkaz SELECT se pou\u017e\u00edv\u00e1 pro zobrazen\u00ed a hled\u00e1n\u00ed z\u00e1znam\u016f v tabulce\n\n<pre>\nINSERT INTO dite(rodic,jmeno, prijmeni,vek) VALUES(1,'Jind\u0159ich','Such\u00fd',12);\nUPDATE dite SET vek = 13 WHERE id = 2;\nDELETE FROM dite WHERE vek > 26;\nSELECT * FROM dite WHERE rodic = 1;\n</pre>\n\nNej\u010dast\u011bji pou\u017e\u00edvan\u00fdm p\u0159\u00edkazem DML p\u0159\u00edkazem je nejsp\u00ed\u0161 p\u0159\u00edkaz SELECT. Skl\u00e1d\u00e1 se s n\u00e1sleduj\u00edc\u00edch povinn\u00fdch a nepovinn\u00fdch klauzul\u00ed (p\u0159i z\u00e1pisu je nezbytn\u00e9 respektovat po\u0159ad\u00ed klauzul\u00ed):\n<pre>\nSELECT <seznam zobrazovan\u00fdch sloupc\u016f> \nFROM <zdroj nebo zdroje tabulek>\nWHERE <v\u00fdb\u011brov\u00e1 krit\u00e9ria>\n[GROUP BY <krit\u00e9ria pro sdru\u017eov\u00e1n\u00ed z\u00e1znam\u016f>]\n[HAVING <krit\u00e9ria pro filtrov\u00e1n\u00ed sdru\u017een\u00fdch z\u00e1znam\u016f>]\n[ORDER BY <zp\u016fsob \u0159azen\u00ed dat>]\n</pre>\nP\u0159\u00edkladem m\u016f\u017ee b\u00fdt dotaz, kter\u00fd zobraz\u00ed v\u0161echny rodi\u010de, kter\u00e9 maj\u00ed v\u00edce ne\u017e jedno d\u00edt\u011b:\n<pre>\nSELECT rodic.* FROM rodic \n WHERE id IN (SELECT rodic FROM dite GROUP BY rodic HAVING count(*) > 1)\n</pre>\n\n=== Transakce ===\nV kter\u00e9mkoliv datab\u00e1zov\u00e9m syst\u00e9mu maj\u00ed transakce nezastupitelnou roli. Zaji\u0161\u0165uj\u00ed konzistenci dat bez ohledu na chyby hardwaru, opera\u010dn\u00edho syst\u00e9mu, aplikace nebo datab\u00e1zov\u00e9ho syst\u00e9mu. Transakce spojuje sadu p\u0159\u00edkaz\u016f do jednoho celku, a to tak, \u017ee se zm\u011bny v datech zp\u016fsoben\u00e9 jednotliv\u00fdmi p\u0159\u00edkazy se stanou trval\u00fdmi pokud se \u00fasp\u011b\u0161n\u011b provede ka\u017ed\u00fd p\u0159\u00edkaz transakce. Prvn\u00ed ne\u00fap\u011b\u0161n\u00fd p\u0159\u00edkaz zp\u016fsob\u00ed p\u0159eru\u0161en\u00ed transakce a odvol\u00e1n\u00ed v\u0161ech zm\u011bn v datech. Klasick\u00fdm p\u0159\u00edkladem je p\u0159esun ur\u010dit\u00e9 \u010d\u00e1stky z jednoho \u00fa\u010du na druh\u00fd:\n<pre>\nBEGIN;\nUPDATE ucty SET castka = castka + 100 WHERE id = 122;\nUPDATE ucty SET castka = castka - 100 WHERE id = 133;\nCOMMIT;\n</pre> \nPokud bychom tyto dva SQL nevlo\u017eili do transakce, mohlo by doj\u00edt (v mezn\u00edm p\u0159\u00edpad\u011b), \u017ee by na \u00fa\u010det u\u017eivatele 122 p\u0159ibyla \u010d\u00e1stka 100 ani\u017e by se u\u017eivateli 133 ode\u010detla z \u00fa\u010dtu. Druh\u00fdm \u00fakolem transakc\u00ed je izolace u\u017eivatel\u016f. Neuzav\u0159en\u00e9 transakce jsou izolov\u00e1ny od ostatn\u00edch u\u017eivatel\u016f - ty st\u00e1le vid\u00ed p\u016fvodn\u00ed obsah. A a\u017e od potvrzen\u00ed transakce uvid\u00ed aktualizovan\u00e9 hodnoty, nicm\u00e9n\u011b aktualizace v\u0161ech hodnot se provede v jednom \u010dasov\u00e9m kvantu. D\u00edky tomu se nem\u016f\u017ee st\u00e1t, \u017ee by druh\u00e1 transakce vid\u011bla data nekonzistentn\u00ed, nap\u0159. s celkovou \u010d\u00e1stkou na v\u0161ech \u00fa\u010dtech o 100 vy\u0161\u0161\u00ed. Tato \u00farove\u0148 izolace se ozna\u010duje jako READ COMMITED a v PostgreSQL je pro transakce v\u00fdchoz\u00ed.\n\nIzolace transakc\u00ed m\u00e1 zajistit subjektivn\u00ed dojem, \u017ee u\u017eivatel pracuje s datab\u00e1z\u00ed s\u00e1m. Tak\u017ee nap\u0159\u00edklad pokud opakujeme n\u011bjak\u00fd SQL dotaz, tak ani\u017e bychom modifikovali data, p\u0159edpokl\u00e1d\u00e1me stejn\u00fd v\u00fdsledek. Tento p\u0159edpoklad s \u00farovn\u00ed READ COMMITED nejsme schopni zajistit. Kdokoliv m\u016f\u017ee b\u011bhem na\u0161\u00ed transakce p\u0159idat \u0159\u00e1dek do tabulky a prov\u00e9st COMMIT. Proto tu je \u00farove\u0148 READ SERIALIZABLE. P\u0159i t\u00e9to \u00farovni u\u017eivatel vid\u00ed pouze ty zm\u011bny v datech, kter\u00e9 byly potvrzeny p\u0159ed prvn\u00edm vol\u00e1n\u00edm p\u0159\u00edkazu SELECT. D\u00edky tomuto mechanismu v\u0161echny SQL dotazy mus\u00ed vracet stejn\u00e9 hodnoty. To, \u017ee aplikace vid\u00ed tat\u00e1\u017e data neznamen\u00e1, \u017ee nedo\u0161lo k jejich zm\u011bn\u011b. V p\u0159\u00edpad\u011b, \u017ee se pokus\u00ed modifikovat z\u00e1znamy, kter\u00e9 byly zm\u011bn\u011bny soub\u011b\u017enou transakc\u00ed, \u010dek\u00e1 se na dokon\u010den\u00ed soub\u011b\u017en\u00e9 transakce. Pokud skon\u010d\u00ed odvol\u00e1n\u00edm, norm\u00e1ln\u011b se pokra\u010duje, pokud ale sv\u00e9 zm\u011bny potvrd\u00ed, aktu\u00e1ln\u00ed transakce skon\u010d\u00ed chybou\n<pre>\nERROR: could not serialize access due to concurrent update\n</pre>\n\n=== SQL/PSM ===\nJazyk SQL byl navr\u017een jako jednoduch\u00fd neprocedur\u00e1ln\u00ed jazyk slou\u017e\u00edc\u00ed prim\u00e1rn\u011b k ulo\u017een\u00ed dat do datab\u00e1ze a k z\u00edsk\u00e1n\u00ed dat z datab\u00e1ze. V praxi se \u010dasem uk\u00e1zalo, \u017ee je p\u0159\u00edli\u0161 jednoduch\u00fd, a \u017ee \u010dasto mus\u00edme ps\u00e1t extern\u00ed aplikace, abychom dok\u00e1zali realizovat pot\u0159ebn\u00e9 operace nad daty, a \u017ee tak\u00e9 \u010dasto nedok\u00e1\u017eeme pomoc\u00ed jednoduch\u00fdch prost\u0159edk\u016f zajistit referen\u010dn\u00ed a dom\u00e9novou integritu v komplikovan\u00e9m prost\u0159ed\u00ed firemn\u00edch aplikac\u00ed. Extern\u00ed aplikace v architektu\u0159e klient-server maj\u00ed jednu z\u00e1kladn\u00ed nev\u00fdhodu - vy\u017eaduj\u00ed relativn\u011b intenzivn\u00ed komunikaci na s\u00edti mezi klientem a serverem. V okam\u017eiku, kdy pracujete s n\u011bkolika tis\u00edci z\u00e1znamy to u\u017e m\u016f\u017ee p\u0159edstavovat v\u00fdkonostn\u00ed probl\u00e9m. \u0158e\u0161en\u00edm bylo p\u0159en\u00e9st \u010d\u00e1st tzv. obchodn\u00ed logiky na datab\u00e1zov\u00fd server. K tomu muselo b\u00fdt SQL roz\u0161\u00ed\u0159eno o n\u011bkolik z\u00e1kladn\u00edch konstrukc\u00ed kter\u00e9 zn\u00e1me z klasick\u00fdch programovac\u00edch jazyk\u016f: prom\u011bnn\u00e9, cykly, podm\u00ednky, procedury a funkce, o\u0161et\u0159en\u00ed vyj\u00edmek. Pomoc\u00ed t\u011bchto konstrukc\u00ed dok\u00e1\u017eeme realizovat samostatn\u00e9 programy, jejich\u017e k\u00f3d celkem logicky ukl\u00e1d\u00e1me do datab\u00e1ze a kter\u00e9 spou\u0161t\u00edme v kontextu datab\u00e1ze. \n\nUlo\u017een\u00e9 procedury slou\u017e\u00ed p\u0159edev\u0161\u00edm k:\n* Generov\u00e1n\u00ed v\u00fdstup\u016f, kter\u00e9 nelze jednodu\u0161e nebo efektivn\u011b realizovat pomoc\u00ed p\u0159\u00edkazu SELECT.\n* P\u0159enesen\u00ed zpracov\u00e1n\u00ed dat z klienta na server.\n* Realizaci slo\u017eit\u011bj\u0161\u00edch pravidel p\u0159\u00edstupu, referen\u010dn\u00ed a dom\u00e9nov\u00e9 integrity, kter\u00e9 nelze realizovat standardn\u00edmi prost\u0159edky.\n* Roz\u0161\u00ed\u0159en\u00ed z\u00e1kladn\u00ed sady vestav\u011bn\u00fdch funkc\u00ed o funkce jinych RDBMS z d\u016fvodu zjednodu\u0161en\u00ed portace datab\u00e1ze.\n\nANSI SQL:1999 definuje tato roz\u0161\u00ed\u0159en\u00ed jazyka v kapitole PSM (Persistent Stored Modules). Tuto syntaxi pou\u017e\u00edv\u00e1 DB2 a [[MySQL5:Jemn\u00fd \u00favod do ulo\u017een\u00fdch procedur MySQL5|MySQL5]]. Podobn\u00e9 jsou i jazyky Transact SQL fy. Microsoft a Procedural SQL RDBMS Firebird. Svou vlastn\u00ed cestou jde Oracle s jazykem PL/SQL. PL/SQL je procedur\u00e1ln\u00ed jazyk na b\u00e1zi programovac\u00edho jazyka ADA roz\u0161\u00ed\u0159en\u00fd o SQL. PostgreSQL tento jazyk p\u0159eb\u00edr\u00e1 a upravuje. Oracle implementoval PL/SQL ve verzi Oracle 6 v roce 1988, v PostgreSQL se [[PL/pgSQL]] objevil ve verzi 6.3 o deset let pozd\u011bji. V roce 2005 je PL/pgSQL plnohodnotn\u00fdm jazykem procedur\u00e1ln\u00edm programovac\u00edm jazykem. \n\nIntegrace PL/pgSQL do syst\u00e9mu je absolutn\u00ed, sd\u00edl\u00ed datov\u00e9 typy, funkce. Naopak v SQL p\u0159\u00edkazech m\u016f\u017eeme pou\u017e\u00edvat funkce vytvo\u0159en\u00e9 v PL/pgSQL. V PL/pgSQL jsme schopni vytv\u00e1\u0159et funkce, jejich\u017e v\u00fdsledkem je tabulka.\n<pre>\nCREATE OR REPLACE FUNCTION init(IN _od integer, _do integer) RETURNS void AS $$\nDECLARE _v integer = 1;\nBEGIN\n FOR _i IN _od .. _do LOOP\n _v := _v + _v;\n INSERT INTO data(hodnota) VALUES (_v);\n END LOOP;\nEND;\n$$ LANGUAGE plpgsql;\nSELECT init(1, 10);\n</pre>\nPou\u017eit\u00ed prefixu _ pro lok\u00e1ln\u00ed prom\u011bnn\u00e9 je konvence zabra\u0148uj\u00edc\u00ed kolizi n\u00e1zvu prom\u011bnn\u00fdch s n\u00e1zvy sloupc\u016f tabulek, a kolizi s kl\u00ed\u010dov\u00fdmi slovy SQL.\n\nPostgreSQL nenab\u00edz\u00ed \u017e\u00e1dn\u00fd jednoduch\u00fd prost\u0159edek pro \u010d\u00edslov\u00e1n\u00ed \u0159\u00e1dk\u016f. Funkce, kter\u00e1 zajist\u00ed str\u00e1nkov\u00e1n\u00ed a z\u00e1rove\u0148 p\u0159id\u00e1n\u00ed \u010d\u00edsla \u0159\u00e1dku m\u016f\u017ee m\u00edt n\u00e1sleduj\u00edc\u00ed podobu:\n<pre>\nCREATE OR REPLACE FUNCTION strana_tel_seznamu(\n IN _strana integer, IN _zns integer, \n OUT _rc integer,\n OUT _jmeno varchar(20), OUT _prijmeni varchar(20), OUT _tel numeric(9,0) \n) RETURNS SETOF RECORD AS $$\nDECLARE \n _rc integer; _r seznam%ROWTYPE;\nBEGIN \n _rc := (_strana - 1)*_zns;\n FOR _r IN SELECT * FROM seznam LIMIT _zns OFFSET (_strana - 1)*zns LOOP\n _jmeno := _r.jmeno; _prijmeni := _r.prijmeni; _tel := _r.tel;\n _rc := _rc + 1;\n RETURN NEXT;\n END LOOP;\n RETURN;\nEND;\n$$ LANGUAGE plpgsql; \nSELECT * FROM strana_tel_seznamu(2,30);\n</pre>\nP\u0159edpokl\u00e1dejme, \u017ee m\u00e1me v datab\u00e1zi o\u010d\u00edslovan\u00e1 data, a nechceme, pokud to je jen trochu mo\u017en\u00e9, aby v \u0159ad\u011b \u010d\u00edsel byla d\u00edra. M\u00e1me dv\u011b mo\u017enosti: bu\u010f v\u0161echny \u00fadaje p\u0159ed operac\u00ed zkontrolovat, tak abychom m\u011bli jistotu, \u017ee se operace povede, nebo operaci zkusmo prov\u00e9st, a pokud se operace provede \u00fasp\u011b\u0161n\u011b, tak po\u017e\u00e1dat o \u010d\u00edslo z na\u0161\u00ed posloupnosti a \u010d\u00edselnou hodnotu u vlo\u017een\u00e9ho z\u00e1znamu aktualizovat.\n<pre>\nCREATE OR REPLACE FUNCTION new_entry(IN _v varchar) RETURNS void AS $$\nDECLARE \n _id integer; \n _tmp_code integer; _true_code integer;\nBEGIN\n /* kod objednavky neznam, pri testovacim pokusu jej nahradim nahodnym cislem.\n Muze se stat, ze zpusobim vyjimku UNIQUE_VIOLATION. Zkusim to pak znova. */\n FOR i IN 1..20 LOOP /* maximalne 20 pokusu */\n BEGIN\n SELECT INTO _tmp_code MAX(kod) FROM data;\n _tmp_code := COALESCE(_tmp_code, 1) + 20 + CAST(random()*100 AS integer);\n INSERT INTO data(kod, v) VALUES(_tmp_code, _v);\n _id := lastval(); _true_code := nextval('kod');\n UPDATE data SET kod = _true_code WHERE id = _id;\n RETURN;\n EXCEPTION\n WHEN unique_violation THEN \n NULL; -- nedelej nic\n WHEN others THEN\n RAISE EXCEPTION '%', SQLERRM;\n END;\n END LOOP;\nEND;\n$$ LANGUAGE plpgsql;\n</pre>\nP\u0159\u00ednosem pou\u017e\u00edv\u00e1n\u00ed ulo\u017een\u00fdch procedur nen\u00ed jen men\u0161\u00ed zat\u00ed\u017een\u00ed s\u00edt\u011b, ale i rychlej\u0161\u00ed a bezpe\u010dn\u011bj\u0161\u00ed prov\u00e1d\u011bn\u00ed SQL p\u0159\u00edkaz\u016f. V\u0161echny SQL p\u0159\u00edkazy se p\u0159edp\u0159ipravuj\u00ed, tj. p\u0159ed jejich prvn\u00edm proveden\u00edm se p\u0159iprav\u00ed prov\u00e1d\u011bc\u00ed pl\u00e1n, kter\u00fd se pou\u017eije i p\u0159i ka\u017ed\u00e9m dal\u0161\u00edm pou\u017eit\u00ed SQL p\u0159\u00edkazu. V\u0161echny p\u0159edp\u0159ipraven\u00e9 SQL p\u0159\u00edkazy jsou parametrizov\u00e1ny, nehroz\u00ed tud\u00ed\u017e riziko SQL injection.\n<pre>\nCREATE FUNCTION bezpecne_zobrazeni(_par varchar) RETURNS SETOF varchar AS $$\nDECLARE _v varchar;\nBEGIN\n IF _par = '%' THEN\n RAISE EXCEPTION 'Pouziti zakazaneho znaku';\n END IF;\n FOR _v IN SELECT prijmeni FROM seznam WHERE prijmeni LIKE _par LOOP\n RETURN NEXT _v;\n END LOOP;\n RETURN;\nEND;\n$$ LANGUAGE plpgsql;\nSELECT * FROM bezpecne_zobrazeni('P%');\n</pre>\nV \u010dem je tato procedura bezpe\u010dn\u011bj\u0161\u00ed ne\u017e pou\u017eit\u00ed oby\u010dejn\u00e9ho, na stran\u011b klienta p\u0159ipraven\u00e9ho, SQL dotazu? V tom, \u017ee si m\u016f\u017eeme b\u00fdt jisti, \u017ee bez ohledu na hodnotu argumentu _par se bude prov\u00e1d\u011bt dotaz:\n<pre>\nSELECT prijmeni FROM seznam WHERE prijmeni LIKE _par;\n</pre>\nNa stran\u011b klienta nen\u00ed a\u017e takov\u00fd probl\u00e9m podvrhnout jako parametr hodnotu \"%' UNION SELECT usename FROM pg_user\". P\u0159i sestaven\u00ed SQL dotazu, t\u0159eba v php, dojde k vygenerov\u00e1n\u00ed a proveden\u00ed \u0159et\u011bzce:\n<pre>\nSELECT prijmeni FROM seznam WHERE prijmeni LIKE '%' \n UNION \n SELECT usename FROM pg_user;\n</pre>\nT\u00edmto dotazem dok\u00e1\u017eeme seznam u\u017eivatel\u016f. Chyt\u0159ej\u0161\u00edmi injekcemi dok\u00e1\u017eeme zjistit strukturu datab\u00e1ze a postupn\u011b p\u0159e\u010d\u00edst v\u0161echna data, ke kter\u00fdm m\u00e1 klientsk\u00e1 aplikace opr\u00e1vn\u011bn\u00ed. Sna\u017ete se alespo\u0148 omezit d\u00e9lku \u0159et\u011bzce a zak\u00e1zat pou\u017eit\u00ed znak\u016f '\"%.\n\nV RDBMS Oracle jsou dv\u011b velice u\u017eite\u010dn\u00e9 funkce: next_day a last_day. Pokud p\u0159ipravujeme aplikaci z\u00e1rove\u0148 pro PostgreSQL, tak je bu\u010fto nesm\u00edme pou\u017e\u00edt (PostgreSQL je neobsahuje) nebo si je mus\u00edme dopsat:\n<pre>\nCREATE OR REPLACE FUNCTION ERROR(IN msg VARCHAR) RETURNS varchar AS $$\nBEGIN\n RAISE EXCEPTION '%', msg;\nEND;\n$$ LANGUAGE plpgsql;\n\nCREATE OR REPLACE FUNCTION last_day(IN d date) RETURNS date AS $$\nBEGIN\n RETURN CAST(date_trunc('month',current_date + interval '1month') AS date) - 1;\nEND;\n$$ LANGUAGE plpgsql IMMUTABLE STRICT;\n\nCREATE OR REPLACE FUNCTION next_day(IN _d date, IN _day varchar) RETURNS date AS $$\nDECLARE _id integer; _dow integer;\nBEGIN \n _dow := EXTRACT(dow FROM _d);\n _id := CASE lower(_day)\n WHEN 'sunday' THEN 0\n WHEN 'monday' THEN 1\n WHEN 'tuesday' THEN 2\n WHEN 'wednesday' THEN 3\n WHEN 'thursday' THEN 4\n WHEN 'friday' THEN 5\n WHEN 'saturday' THEN 6\n ELSE\n CAST(ERROR(E'Wrong identifier for day \\''||_day||E'\\'') AS integer)\n END;\n RETURN CASE _id <= _dow\n WHEN true THEN _d + (_id - _dow + 7)\n WHEN false THEN _d + (_id - _dow)\n END;\nEND;\n$$ LANGUAGE plpgsql IMMUTABLE STRICT;\n</pre>\nPodobn\u011b m\u016f\u017eeme PostgreSQL roz\u0161\u00ed\u0159it o funkce podporovan\u00e9 jin\u00fdmi RDBMS.\n\n'''Pou\u017eit\u00ed ulo\u017een\u00fdch procedur si vynucuje zm\u011bny v aplikaci. M\u016f\u017ee m\u00edt negativn\u00ed i positivn\u00ed dopad na p\u0159enositelnost aplikace. K\u00f3d SP zpravidla nen\u00ed p\u0159enositeln\u00fd (z\u00e1le\u017e\u00ed odkud a kam, chcete portovat aplikaci). Na druhou stranu, k\u00f3d kter\u00fd je platformn\u011b z\u00e1visl\u00fd je sout\u0159ed\u011bn v jednom nebo n\u011bkolika modulech. Z m\u00e9 zku\u0161enosti mohu SP jedin\u011b doporu\u010dit. D\u00edky SP se zjednodu\u0161\u00ed k\u00f3d aplikace a zpravidla \u0159\u00e1dov\u011b zrychl\u00ed.'''\n\n=== Doporu\u010den\u00ed pro n\u00e1vrh datab\u00e1z\u00ed ===\n* V n\u00e1zvu tabulek a sloupc\u016f nepou\u017e\u00edvejte kl\u00ed\u010dov\u00e1 slova.\n* Sna\u017ete se, aby polo\u017eky tvo\u0159\u00edc\u00ed prim\u00e1rn\u00ed kl\u00ed\u010d, byly deklarov\u00e1ny na za\u010d\u00e1tku tabulky.\n* Pokud nem\u00e1te k dispozici data, pou\u017eijte hodnotu NULL. Nepou\u017e\u00edvejte 0 nebo pr\u00e1zdn\u00fd \u0159et\u011bzec.\n* Nepou\u017e\u00edvejte prefixy (nap\u0159. t_processes).\n* Nepou\u017e\u00edvejte velbloud\u00ed (Camel) zp\u016fsob z\u00e1pisu identifik\u00e1tor\u016f (nap\u0159. isoCode).\n* N\u00e1zvy sloupe\u010dk\u016f by m\u011bli b\u00fdt v\u00fdsti\u017en\u00e9, rozumn\u011b dlouh\u00e9, zapsan\u00e9 mal\u00fdmi p\u00edsmeny.\n* Tabulky naz\u00fdvejte v mno\u017en\u00e9m \u010d\u00edsle a zapisujte s prvn\u00edm velk\u00fdm a ostatn\u00edmi mal\u00fdmi p\u00edsmeny.\n* Kl\u00ed\u010dova slova zapisujte v\u017edy mal\u00fdmi nebo v\u017edy velk\u00fdmi p\u00edsmeny.\n* Pou\u017e\u00edvejte p\u0159edem dohodnutou sadu sufix\u016f (_id, _code, _date, _nbr, _name, _size, _tot, _cat, _class, _type).\n* Nepou\u017e\u00edvejte postfixy PK, FK.\n* N\u00e1zev tabulky nepou\u017e\u00edvejte v n\u00e1zvu sloupce\n* Nepou\u017e\u00edvejte speci\u00e1ln\u00ed znaky (vyj\u00edmka znak \"_\"), diakritiku v identifik\u00e1torech tabulek a sloupc\u016f.\n* Za \u010d\u00e1rkou nebo st\u0159edn\u00edkem v\u017edy pou\u017eijte mezeru nebo nov\u00fd \u0159\u00e1dek, neza\u010d\u00ednejte \u0159\u00e1dek \u010d\u00e1rkou.\n* Odsazujte: 1 mezera za\u010d\u00e1tek klauzule, 2 mezery pokra\u010dov\u00e1n\u00ed klauzule, 3 mezery pokra\u010dov\u00e1n\u00ed v\u00fdrazu, slovo pokra\u010dov\u00e1n\u00ed seznamu.\n* Poddotaz za\u010d\u00ednejte v\u017edy na nov\u00e9m \u0159\u00e1dku, z\u00e1vorky sva\u017ete s poddotazem.\n* Hledejte p\u0159irozen\u00e9 k\u00f3dy.\n* Dr\u017ete pohromad\u011b atributy, kter\u00e9 spolu v\u011bcn\u011b, chronologicky souvis\u00ed.\n** je chybou rozd\u011blit data do tabulek: FemalePersonel, MalePersonel, prodej2001, prodej2002, atd.\n** je chybou kdy\u017e datum m\u00e1te ve t\u0159ech sloupc\u00edch (den, m\u011bs\u00edc, rok),\n** je chybou, kdy\u017e data rozd\u011bl\u00edte zbyte\u010dn\u011b do dvou \u0159\u00e1dk\u016f, nap\u0159. zah\u00e1jen\u00ed ud\u00e1losti, konec ud\u00e1losti.\n* Pokud navrhujete vlastn\u00ed v\u00fd\u010dtov\u00fd k\u00f3d, definujte si vlastn\u00ed n\u00e1hradu za NULL (0 Unknown, 1 Male, 2 Female, 9 Not applicable (nem\u00e1 smysl)).\n* K\u00f3dy dr\u017ete v datab\u00e1zi v speci\u00e1ln\u00edch tabulk\u00e1ch. Je chybou pou\u017e\u00edvat jednu univerz\u00e1ln\u00ed tabulku pro v\u0161echny t\u0159\u00eddy k\u00f3d\u016f.\n* Pokud to lze, pou\u017e\u00edvejte standardizovan\u00e9 konstrukce a funkce.\n\nP\u0159\u00edklad vhodn\u011b naform\u00e1tovan\u00e9ho vno\u0159en\u00e9ho SQL p\u0159\u00edkazu:\n<pre>\nSELECT DISTINCT pilot \n FROM PolotSkills AS PS1\n WHERE NOT EXISTS\n (SELECT *\n FROM Hangar\n WHERE NOT EXISTS\n (SELECT * \n FROM PilotSkills AS PS2\n WHERE PS1.pilot = PS2.pilot \n AND PS2.plane = Hangar.plane\n )\n ); \n</pre>\nUk\u00e1zka z\u00e1pisu spojen\u00ed tabulek:\n<pre>\nSELECT O1.order_nbr, ..\n FROM Orders AS O1\n INNER JOIN \n OrderDetails AS D1\n ON O1.order_nbr = D1.order_nbr\n WHERE D1.dept = 'mens wear';\n</pre>\nUk\u00e1zka z\u00e1pisu konstrukce CASE:\n<pre>\nCASE WHEN foo = 1 THEN 'bar'\n WHEN foo = NULL THEN 'no_bar' \n ELSE NULL END\n</pre>\n\n== Vno\u0159en\u00e9 poddotazy a odvozen\u00e9 tabulky ==\nSQL umo\u017e\u0148uje rekurzivn\u011b vlo\u017eit dotaz do jin\u00e9ho dotazu. Pokud je dotaz vlo\u017een do \u010d\u00e1sti WHERE, ozna\u010dujeme vlo\u017een\u00fd dotaz jako vno\u0159en\u00fd (nested). Pokud je vlo\u017een do \u010d\u00e1sti FROM ozna\u010dujeme jej jako odvozenou tabulku (derived table), nebo n\u011bkdy tak\u00e9 jako do\u010dasn\u00fd pohled. Vlo\u017een\u00fd dotaz m\u016f\u017ee b\u00fdt prost\u0159ednictv\u00edm hodnot atribut\u016f spojen s vn\u011bj\u0161\u00edm dotazem - v\u00e1zan\u00fd, n\u011bkdy tak\u00e9 korelovan\u00fd dotaz.\n\nP\u0159\u00edklad vno\u0159en\u00e9ho dotazu (vyhled\u00e1n\u00ed v\u0161ech finsk\u00fdch m\u011bst lidnat\u011bj\u0161\u00edch ne\u017e nejlidnat\u011bj\u0161\u00ed m\u011bsto v \u010cR):\n<pre>\nSELECT mesta FROM databaze_mest \n WHERE pocet_obyvatel > ALL \n (SELECT pocet_obyvatel FROM databaze_mest WHERE stat = 'CZ')\n AND stat = 'FI';\n</pre>\n\nP\u0159\u00edklad korelovan\u00e9ho vno\u0159en\u00e9ho dotazu (zobrazen\u00ed zam\u011bstnanc\u016f s nejv\u011bt\u0161\u00ed mzdou v r\u00e1mci odd\u011blen\u00ed):\n<pre>\nSELECT prijmeni, oddeleni, mzda FROM zamestnanci zo\n WHERE mzda = \n (SELECT MAX(mzda) FROM zamestnanci zi WHERE zi.oddeleni = zo.oddeleni);\n</pre>\n\nPostgreSQL podporuje v\u00edce sloupcov\u00e9 predik\u00e1ty:\n<pre>\nSELECT prijmeni, mzda, oddeleni FROM zamestnanci\n WHERE (mzda, oddeleni) IN \n (SELECT MAX(mzda), oddeleni FROM zamestnanci GROUP BY oddeleni);\n</pre>\n\nP\u0159\u00edklad odvozen\u00e9 tabulky (op\u011bt zobrazen\u00ed zam\u011bstnanc\u016f s nejv\u011bt\u0161\u00ed mzdou v r\u00e1mci odd\u011blen\u00ed):\n<pre>\nSELECT prijmeni, dt.mm AS mzda, dt.oddeleni FROM zamestnanci z1,\n (SELECT MAX(mzda) AS mm, oddeleni FROM zamestnanci GROUP BY oddeleni) dt\n WHERE z1.mzda = dt.mm AND z1.oddeleni = dt.oddeleni;\n</pre>\n\nN\u011bkter\u00e9 vno\u0159en\u00e9 dotazy lze zapsat pomoc\u00ed r\u016fzn\u011b efektivn\u00edch p\u0159\u00edkaz\u016f. Uk\u00e1zkov\u00fdm p\u0159\u00edkladem je n\u00e1hrada \"> ALL\" funkc\u00ed MAX v poddotazu (druh\u00fd dotaz je vhodn\u011bj\u0161\u00ed):\n<pre>\nSELECT * FROM a WHERE a > ALL(SELECT v FROM b);\nSELECT * FROM a WHERE a > (SELECT MAX(v) FROM b);\n</pre>\n\n== Vnit\u0159n\u00ed a vn\u011bj\u0161\u00ed spojen\u00ed tabulek ==\nExistuj\u00ed dva z\u00e1kladn\u00ed zp\u016fsoby z\u00e1pisu spojen\u00ed tabulek. Na star\u0161\u00ed variantu zalo\u017eenou na v\u00fd\u010dtu tabulek v \u010d\u00e1sti FROM byste m\u011bli rad\u011bji zapomenout. Jednak podporuje pouze vnit\u0159n\u00ed spojen\u00ed, tj. zobrazen\u00ed pouze zp\u00e1rovan\u00fdch z\u00e1znam\u016f a jednak m\u016f\u017ee, d\u00edky nedokonal\u00e9mu z\u00e1pisu, b\u00fdt zdrojem velice z\u00e1va\u017en\u00e9 chyby, kter\u00e1 m\u016f\u017ee m\u00edt vliv na v\u00fdkon serveru. Je\u0161t\u011b p\u0159ed n\u011bkolika lety pro tuto variantu hovo\u0159il fakt, \u017ee ne v\u0161echny datab\u00e1ze podporovaly z\u00e1pis JOIN, co\u017e je ale v sou\u010dasnostni pas\u00e9.\n\nNajd\u011bte chybu na n\u00e1sleduj\u00edc\u00edm p\u0159\u00edkladu:\n<pre>\nSELECT DISTINCT a.* FROM a, b, c \n WHERE a.id = b.pid;\n</pre> \nChybu byste m\u011bli vid\u011bt na prvn\u00ed pohled. O to h\u016f\u0159 se hled\u00e1 v komplikovan\u00e9m dotazu. Zpravidla na ni upozor\u0148uje pou\u017eit\u00ed fr\u00e1ze DISTINCT. Tou chybou je bu\u010fto nesv\u00e1z\u00e1n\u00ed tabulky c nebo nevypu\u0161t\u011bn\u00ed t\u00e9to tabulky ze seznamu zdroj\u016f. D\u00edky tomu se po\u010det \u0159\u00e1dk\u016f v\u00fdsledku n\u00e1sob\u00ed po\u010dtem \u0159\u00e1dk\u016f tabulky c a op\u011bt ru\u0161\u00ed fr\u00e1z\u00ed DISTINCT. V p\u0159\u00edpad\u011b, \u017ee tato tabulka je mal\u00e1, tak v\u00fdkonnostn\u00ed propad bude minim\u00e1ln\u00ed, bude se ale zv\u011bt\u0161ovat s r\u016fstem t\u00e9to tabulky. Vytv\u00e1\u0159ej\u00ed se tak chyby, kter\u00e9 za\u010dnou p\u016fsobit pot\u00ed\u017ee a\u017e po n\u011bkolika let\u00e9m provozu syst\u00e9m\u016f (samoz\u0159ejm\u011b v dob\u011b, kdy u\u017e v podniku nen\u00ed nikdo, kdo by o syst\u00e9mu n\u011bco v\u011bd\u011bl, a syst\u00e9m se ob\u010das, nepravideln\u011b, ale naprosto tot\u00e1ln\u011b zpomal\u00ed).\n\nZp\u016fsob spojen\u00ed fr\u00e1z\u00ed JOIN podporuj\u00ed v\u0161echny roz\u0161\u00ed\u0159en\u00e9 SQL datab\u00e1ze a skute\u010dn\u011b nen\u00ed jedin\u00fd d\u016fvod pro\u010d pou\u017e\u00edvat star\u0161\u00ed variantu. Zp\u016fsob z\u00e1pisu je jednoduch\u00fd:\n<pre>\nSELECT .. FROM a [LEFT|RIGHT|CROSS|FULL] JOIN b ON log. v\u00fdraz\n</pre>\nP\u0159i operaci JOIN se vytv\u00e1\u0159\u00ed kart\u00e9zk\u00fd sou\u010din tabulek a a b. Log. v\u00fdraz vymezuje podmno\u017einu tohoto sou\u010dinu, kter\u00e1 m\u00e1 smysl. V\u011bt\u0161inou je to v\u00fdraz rovnosti prim\u00e1rn\u00edho kl\u00ed\u010de a ciz\u00edho kl\u00ed\u010de. Pokud bych m\u011bl tabulku rodi\u010d\u016f a d\u011bt\u00ed, pak kart\u00e9zk\u00fd sou\u010din t\u011bchto tabulek p\u0159i\u0159ad\u00ed ke ka\u017ed\u00e9 osob\u011b z tabulky rodi\u010d\u016f ka\u017ed\u00e9 d\u00edt\u011b z tabulky d\u011bt\u00ed, co\u017e je nesmysl. Platn\u00e9 jsou pouze ty dvojice, kter\u00e9 vyhovuj\u00ed podm\u00ednce rovnosti prim\u00e1rn\u00edho kl\u00ed\u010de v tab. rodi\u010de a ciz\u00edho kl\u00ed\u010de v tabulce d\u011bti. \n<pre>\nSELECT * FROM rodice JOIN deti ON rodice.id = deti.rodic_id\n</pre>\nVnit\u0159n\u00ed spojen\u00ed zobraz\u00ed pouze odpov\u00eddaj\u00edc\u00ed si z\u00e1znamy z obou tabulek. Vn\u011bj\u0161\u00ed spojen\u00ed zobraz\u00ed bu\u010fto v\u0161echny z\u00e1znamy z tabulky a (LEFT JOIN) a relevantn\u00ed z\u00e1znamy z tabulky b, nebo relevantn\u00ed z\u00e1znamy z tabulky a a v\u0161echny z\u00e1znamy z tabulky b (RIGHT JOIN), p\u0159\u00edpadn\u011b v\u0161echny z\u00e1znamy z tabulek a a b (FULL JOIN). V\u00fdsledkem SQL dotazu m\u016f\u017ee b\u00fdt jedin\u011b tabulka. Pokud vn\u011bj\u0161\u00ed spojen\u00ed zobraz\u00ed na jedn\u00e9 stran\u011b ur\u010dit\u00e9 hodnoty, a na druh\u00e9 stran\u011b nenajde relevantn\u00ed z\u00e1znam, dopln\u00ed druhou stranu hodnotami NULL. Toho se \u010dasto vyu\u017e\u00edv\u00e1 jako vyhled\u00e1vac\u00ed krit\u00e9rium. P\u0159\u00edkladem m\u016f\u017ee b\u00fdt dotaz na v\u0161echny rodi\u010de, kte\u0159\u00ed nemaj\u00ed ani jedno d\u00edt\u011b.\n<pre>\nSELECT * FROM rodice LEFT JOIN deti ON rodice.id = deti.rodic_id\n WHERE deti.id IS NULL;\n</pre>\nPrim\u00e1rn\u00ed kl\u00ed\u010d z tabulky deti bude m\u00edt ve spojen\u00ed hodnotu NULL pouze v tom p\u0159\u00edpad\u011b, \u017ee dan\u00fd rodi\u010d nem\u00e1 \u017e\u00e1dn\u00e9 d\u00edt\u011b. Jinak, z po\u017eadavk\u016f na prim\u00e1rn\u00ed kl\u00ed\u010d, mus\u00ed b\u00fdt v\u017edy NON NULL.\n\n== Kompozice dotaz\u016f ==\nOperac\u00ed UNION m\u016f\u017eeme spojit vr\u00e1cen\u00e9 mno\u017einy z\u00e1znam\u016f. Varianta UNION ALL vr\u00e1t\u00ed v\u0161echny z\u00e1znamy v\u010detn\u011b mo\u017en\u00fdch duplicitn\u00edch z\u00e1znam\u016f. Jeliko\u017e neprov\u00e1d\u00ed eliminaci duplicit, vyhodnot\u00ed se rychleji. M\u016f\u017eeme ji pou\u017e\u00edt ve spojen\u00ed s predik\u00e1tem IN, kter\u00fd intern\u011b duplicity odstra\u0148uje, nebo tam, kde v\u00edte, \u017ee budete spojovat disjunktn\u00ed mno\u017einy.\n<pre>\nSELECT 'maximum' AS typ, prijmeni, dt.mm AS mzda, dt.oddeleni FROM zamestnanci z1,\n (SELECT max(mzda) AS mm, oddeleni FROM zamestnanci GROUP BY oddeleni) dt\n WHERE z1.mzda = dt.mm AND z1.oddeleni = dt.oddeleni\nUNION ALL\nSELECT 'minimum' AS typ, prijmeni, dt.mm AS mzda, dt.oddeleni FROM zamestnanci z1,\n (SELECT max(mzda) AS mm, oddeleni FROM zamestnanci GROUP BY oddeleni) dt\n WHERE z1.mzda = dt.mm AND z1.oddeleni = dt.oddeleni\nORDER BY dt.oddeleni, typ;\n</pre>\nDotaz z\u00edsk\u00e1 maxim\u00e1ln\u00ed a minim\u00e1ln\u00ed mzdy, a d\u00edky se\u0159azen\u00ed zobraz\u00ed pod sebou zam\u011bstnance s nejv\u011bt\u0161\u00ed a nejmen\u0161\u00ed mzdou v odd\u011blen\u00ed. Krom\u011b operace UNION m\u016f\u017eeme pou\u017e\u00edt operace INTERSECT (pr\u016fnik) a EXCEPT (rozd\u00edl).\n\n== Pou\u017eit\u00ed podm\u00edn\u011bn\u00fdch v\u00fdraz\u016f ==\nBez podm\u00edn\u011bn\u00fdch v\u00fdraz\u016f by se t\u011b\u017eko \u017eilo. Dokud nebyly k dispozici, musely se i nepatrn\u00e9 transformace \u00fadaj\u016f prov\u00e1d\u011bt na klientsk\u00e9 \u010d\u00e1sti aplikace, nebo se musely ps\u00e1t speci\u00e1ln\u00ed funkce. Uvedu p\u0159\u00edklad. Dejme tomu, \u017ee budeme m\u00edt chronologickou \u0159adu hodnot, z kter\u00e9 budu cht\u00edt agregovat \u010dtvrthodinov\u00e9 sumy. S konstrukc\u00ed CASE je tato \u00faloha snadno \u0159e\u0161iteln\u00e1:\n<pre>\nSELECT date_trunc('hour',cas) + \n CASE \n WHEN EXTRACT(minute FROM cas) BETWEEN 0 AND 14 THEN 0\n WHEN EXTRACT(minute FROM cas) BETWEEN 15 AND 29 THEN 15\n WHEN EXTRACT(minute FROM cas) BETWEEN 30 AND 44 THEN 30\n WHEN EXTRACT(minute FROM cas) BETWEEN 45 AND 59 THEN 45\n END * interval '1 minute' AS tcas, \n SUM(hodnota) FROM data\n GROUP BY tcas ORDER BY tcas;\n</pre>\n=== CASE ===\nPodm\u00edn\u011bn\u00fd v\u00fdraz m\u00e1 dva z\u00e1kladn\u00ed tvary:\n<pre>\nCASE v\u00fdraz WHEN hodnota THEN hodnota .. ELSE hodnota END\nCASE WHEN vyraz THEN hodnota .. ELSE hodnota END\n</pre>\nPostupn\u011b se vyhodnocuj\u00ed v\u0161echny v\u011btve, prvn\u00ed pravdivou vyhodnocen\u00ed kon\u010d\u00ed. Pokud \u017e\u00e1dn\u00e1 v\u011btev nen\u00edpravdiv\u00e1, a chyb\u00ed v\u011btev ELSE, pak m\u00e1 v\u00fdraz hodnotu NULL. P\u0159\u00edkaz m\u016f\u017eeme pou\u017e\u00edt pro substituci k\u00f3du, pro ochranu p\u0159ed d\u011blen\u00edm nulou atd:\n<pre>\nSELECT CASE je_muz THEN 'Muz' ELSE 'Zena' END AS Pohlavi FROM zamestnanci;\nSELECT CASE attr <> 0 THEN 100/attr*100 ELSE NULL END FROM data;\n</pre>\n=== COALESCE ===\nDlouhou dobu byla konstanta NULL tabu. Pokud se objev\u00ed v libovoln\u00e9m v\u00fdrazu, je v\u00fdsledkem v\u00fdrazu hodnota NULL. Nav\u00edc existovaly obavy o efektivnost implementace t\u00e9to hodnoty. Roz\u0161\u00ed\u0159ilo se pou\u017e\u00edv\u00e1n\u00ed z\u00e1stupn\u00fdch konstant nap\u0159. 0 nebo -1, p\u0159\u00edpadn\u011b pr\u00e1zdn\u00e9ho \u0159et\u011bzce. PostgreSQL ukl\u00e1d\u00e1 NULL hodnotu jako 1 bit, tud\u00ed\u017e alokuje mnohem m\u00e9n\u011b prostoru ne\u017e pro z\u00e1stupnou konstantu: varchar nulov\u00e9 d\u00e9lky pot\u0159ebuje 4Byte. Tak\u017ee t\u00e9to konstanty bych se neb\u00e1l, jedin\u00e9 m\u00edsto, kde se nesm\u00ed objevit je prim\u00e1rn\u00ed kl\u00ed\u010d. \n\nP\u0159esto n\u011bkdy pot\u0159ebujeme sestavit v\u00fdraz, kde se n\u00e1m NULL m\u016f\u017ee objevit, ale nechceme, aby NULL bylo v\u00fdsledkem. P\u0159\u00edkladem je sestaven\u00ed jm\u00e9na a p\u0159\u00edjmen\u00ed do jednoho \u0159et\u011bzce, kdy jm\u00e9no m\u00e1me nepovinn\u00e9. ANSI SQL a PostgreSQL \u0159e\u0161\u00ed tuto situaci pomoc\u00ed funkce COALESCE.\n<pre>\nSELECT COALESCE(jmeno||' '||prijmeni, prijmeni,'');\n</pre>\nV\u00fdsledkem funkce je prvn\u00ed ne NULL parametr.\n\n=== LEAST a GREATEST ===\nTyto funkce mohou slou\u017eit podobn\u011b jako funkce COALESCE. Tak\u00e9 dok\u00e1\u017e\u00ed nahradit hodnotu NULL a k tomu vyberou minim\u00e1ln\u00ed (LEAST) nebo maxim\u00e1ln\u00ed (GREATEST) hodnotu ze seznamu parametr\u016f. Nap\u0159. pot\u0159ebuji odfiltrovat v\u0161echny men\u0161\u00ed a NULL hodnoty ne\u017e je ur\u010dit\u00e1 mez. Chci ale zachovat \u0159\u00e1dky (nap\u0159. obsahuj\u00ed i jin\u00e9 \u00fadaje, nebo je pro mne d\u016fle\u017eit\u00fd po\u010det z\u00e1znam\u016f):\n<pre>\n SELECT GREATEST(mereni, 0) FROM data;\n</pre>\n\n== Variace p\u0159\u00edkazu SELECT ==\nV SQL je naprosto b\u011b\u017en\u00e9, \u017ee jednu \u00falohu m\u016f\u017eeme vy\u0159e\u0161it n\u011bkolika r\u016fzn\u00fdmi zp\u016fsoby. V na\u0161em p\u0159\u00edkladu m\u00e1m tabulku zam\u011bstnanc\u016f o 1000 z\u00e1znamech a tabulku pracovi\u0161\u0165 o p\u011bti z\u00e1znamech (\u010d\u00edseln\u00edk). Budu cht\u00edt vy\u0159e\u0161it klasickou \u00falohu dohled\u00e1n\u00ed zam\u011bstnanc\u016f s nejv\u011bt\u0161\u00ed mzdou v r\u00e1mci pracovi\u0161t\u011b. Prvn\u00ed varianta vyu\u017e\u00edv\u00e1 vno\u0159en\u00fd korelovan\u00fd poddotaz:\n<pre>\nSELECT label, prijmeni, mzda FROM zamestnanci z, pracoviste p\n WHERE mzda = (SELECT MAX(mzda) FROM zamestnanci WHERE pracoviste = z.pracoviste)\n AND z.pracoviste = p.id;\n</pre>\n\nTento dotaz vy\u017eaduje index nad sloupcem mzda. Bez n\u011bj trv\u00e1 jeho prv\u00e1d\u011bn\u00ed 1360 ms, s n\u00edm 54 ms. Dal\u0161\u00ed varianty jsou postaven\u00e9 nad vn\u011bj\u0161\u00edm spojen\u00edm a pou\u017eit\u00edm odvozen\u00fdch tabulek. V podstat\u011b se jedn\u00e1 z hlediska v\u00fdkonu o ekvivalentn\u00ed \u0159e\u0161en\u00ed. Nevy\u017eaduj\u00ed index nad sloupcem mzda a doba prov\u00e1d\u011bn\u00ed je cca 18 ms.\n<pre>\nSELECT label, prijmeni, MAX FROM zamestnanci z JOIN\n (SELECT MAX(mzda), pracoviste FROM zamestnanci GROUP BY pracoviste) p \n ON p.max = z.mzda AND z.pracoviste = p.pracoviste\n JOIN pracoviste p2 ON p2.id = z.pracoviste;\n\nSELECT label, prijmeni, max FROM zamestnanci z JOIN\n (SELECT MAX(mzda), pracoviste, label FROM zamestnanci z JOIN pracoviste p \n ON p.id = z.pracoviste GROUP BY pracoviste, label) s\n ON z.pracoviste = s.pracoviste AND z.mzda = max;\n</pre>\nMezi verzemi PostgreSQL doch\u00e1z\u00ed ke zm\u011bn\u00e1m v algoritmu hled\u00e1n\u00ed optim\u00e1ln\u00ed strategie vyhodnocen\u00ed dotazu. Zat\u00edm jsem se s t\u00edm nesetkal, ale mus\u00edm to br\u00e1t v \u00favahu, \u017ee po p\u0159echodu na nov\u011bj\u0161\u00ed verzi, budu muset prov\u00e9st op\u011btovn\u011b optimalizaci datab\u00e1ze. Pokud budu m\u00edt k dispozici n\u011bkter\u00fd z n\u00e1stroj\u016f podporuj\u00edc\u00ed automatick\u00e9 testov\u00e1n\u00ed k\u00f3du dost si zjednodu\u0161\u00edm pr\u00e1ci. P\u0159edpokladem je odd\u011blen\u00ed SQL p\u0159\u00edkaz\u016f od k\u00f3du aplikace. N\u00e1sleduj\u00edc\u00ed p\u0159\u00edklad m\u016f\u017ee slou\u017eit jako uk\u00e1zka (na\u0161t\u011bst\u00ed pozitivn\u00ed). Ve verzi 8.0 a ni\u017e\u0161\u00edch byl velk\u00fd v\u00fdkonostn\u00ed rozd\u00edl mezi ob\u011bmi variantami. Ve verzi 8.1 se ob\u011b varianty zpracuj\u00ed za stejn\u00fd \u010das.\n\nV klauzuli WHERE preferujte spojen\u00ed dotaz\u016f (UNION ALL) p\u0159ed slo\u017een\u00fdmi v\u00fdrazy - optimaliz\u00e1tor je p\u0159ipraven\u00fd na optimalizaci dotaz\u016f, ne aritmetick\u00fdch v\u00fdraz\u016f. Dejme tomu, \u017ee bych do n\u00e1\u0161 p\u0159\u00edklad roz\u0161\u00ed\u0159il o tabulku mobil_tel, kde eviduji telef. cislo u zamestnancu, kte\u0159\u00ed obdr\u017eeli mobil. A z n\u011bjak\u00e9ho d\u016fvodu pot\u0159ebuji vypsat seznam zam\u011bstnanc\u016f, kte\u0159\u00ed maj\u00ed ur\u010dit\u00e9 telefonn\u00ed \u010d\u00edslo v\u010detn\u011b jednoho konkr\u00e9tn\u00edho zam\u011bstnance. Prvn\u00ed neoptim\u00e1ln\u00ed varianta:\n<pre>\nSELECT * FROM zamestnanci \n WHERE id = 10 OR id = ANY (SELECT zam_id FROM mobil_tel WHERE skupina = 4);\n</pre>\na druh\u00e1 optim\u00e1ln\u00ed (pro 8.0 a ni\u017e\u0161\u00ed)\n<pre>SELECT * FROM zamestnanci\n WHERE id IN (SELECT zam_id FROM mobil_tel WHERE skupina = 4 \n UNION ALL \n SELECT 10)\n</pre>\nV \u010dem je rozd\u00edl? Slo\u017een\u00fd v\u00fdraz id = 10 OR .. jsem nahradil jednodu\u0161\u0161\u00edm id IN (... a odstranil jsem bin\u00e1rn\u00ed oper\u00e1tor OR. Bylo by nepraktick\u00e9, abyste hledali v\u0161echny variace SQL dotazu a testovali je. Nicm\u00e9n\u011b, m\u011bli byste si ka\u017ed\u00fd SQL p\u0159\u00edkaz otestovat a pokud nebudete spokojeni s jeho efektivitou, tak se pokusit naj\u00edt jin\u00fd tvar.\n\n== Optimalizace ==\nZ\u00e1kladem je vhodn\u011b navr\u017een\u00e1 datab\u00e1ze:\n* vhodn\u011b navr\u017een\u00e1 a zdokumentovan\u00e1 struktura tabulek, kterou m\u016f\u017eeme roz\u0161i\u0159ovat a udr\u017eovat,\n* vhodn\u011b zvolen\u00e9 datov\u00e9 typy, nap\u0159. vyvarovat se pou\u017eit\u00ed varcharu m\u00edsto timestampu atd.,\n* vhodn\u011b zvolen\u00e9 a udr\u017eovan\u00e9 omezen\u00ed pro dom\u00e9novou integritu (datab\u00e1ze se jen velmi obt\u00ed\u017en\u011b dodate\u010dn\u011b \u010dist\u00ed),\n* pravideln\u00e9 prov\u00e1d\u011bn\u00ed auditu datab\u00e1ze: ru\u0161en\u00ed nepou\u017e\u00edvan\u00fdch index\u016f, pracovn\u00edch tabulek a pohled\u016f,\n* v\u017edy se sna\u017ete zapsat v\u00fdrazy obsahuj\u00edc\u00ed porovn\u00e1n\u00ed tak, aby jednu stranu porovn\u00e1n\u00ed tvo\u0159il jeden atribut\n<pre>\nSELECT * FROM test WHERE a+1 = 100 OR b+1 = 100; -- chybn\u011b\nSELECT * FROM test WHERE a = 100-1 OR b = 100-1; -- spr\u00e1vn\u011b\n</pre>\n\n\u010casto lze jeden SQL dotaz napsat n\u011bkolika zp\u016fsoby. Pokud je to mo\u017en\u00e9, sna\u017ete se vyvarovat vno\u0159en\u00fdch, korelovan\u00fdch i odvozen\u00fdch dotaz\u016f a pou\u017e\u00edvejte JOIN, i kdy\u017e to pravd\u011bpodobn\u011b ne v\u017edy bude mo\u017en\u00e9 a m\u016f\u017ee se tak\u00e9 zd\u00e1t, \u017ee JOIN bude n\u00e1ro\u010dn\u011bj\u0161\u00ed. V\u011bt\u0161inou opak je pravdou, a je men\u0161\u00ed riziko, \u017ee se pou\u017eije opravdu nevhodn\u00fd prov\u00e1d\u011bc\u00ed pl\u00e1n. A to p\u0159i chybn\u011b nastaven\u00fdch indexech m\u016f\u017ee znamenat opravdu markantn\u00ed rozd\u00edl.\n\nP\u0159\u00edklad p\u0159evodu vno\u0159en\u00e9ho dotazu na klauzuli JOIN. Pokud budu cht\u00edt zobrazit v\u0161echny autory, kte\u0159\u00ed napsali skripta, tak mohu napsat n\u011bkolik s\u00e9manticky ekvivalentn\u00edch SQL dotaz\u016f:\n<pre>\nSELECT * FROM lide WHERE EXISTS(SELECT id FROM skripta WHERE lide.id = autor);\nSELECT * FROM lide WHERE id IN (SELECT autor FROM skripta);\nSELECT DISTINCT lide.* FROM lide JOIN skripta ON lide.id = autor;\nSELECT lide.* FROM lide JOIN (SELECT DISTINCT autor FROM skripta) p ON p.autor = lide.id;\n</pre>\nkter\u00e9 budou r\u016fzn\u011b rychl\u00e9 v z\u00e1vislosti na pom\u011bru tabulky lide ku tabulce skripta, v z\u00e1vislosti na rozd\u011blen\u00ed dat. Pravd\u011bpodobn\u011b nejrychlej\u0161\u00ed bude druh\u00fd a \u010dtvrt\u00fd p\u0159\u00edklad. Prvn\u00ed m\u00e1 probl\u00e9m se sekven\u010dn\u00edm \u010dten\u00edm tabulky autor, a opakovan\u00fdm vykon\u00e1v\u00e1n\u00edm poddotazu. Chyba t\u0159et\u00edho je eliminace velk\u00e9ho mno\u017estv\u00ed \u0159\u00e1dek. Tomu p\u0159edejdeme pokud klauzuli DISTINCT um\u00edst\u00edme do odvozen\u00e9 tabulky. Pro p\u0159edstavu, \u010dasy vykon\u00e1v\u00e1n\u00ed jednotliv\u00fdch dotaz\u016f je: 3000, 27, 436, 36.\n\nV\u017edy se sna\u017ete naj\u00edt SQL dotaz, kter\u00fd byl \u0159e\u0161en\u00edm Va\u0161eho \u00fakolu. Omezte skl\u00e1d\u00e1n\u00ed v\u00fdsledku na klientu. Omezte po\u010det dotaz\u016f, kter\u00e9 pos\u00edl\u00e1te na SQL server. V p\u0159\u00edpad\u011b, \u017ee by byl dotaz p\u0159\u00edli\u0161 komplikovan\u00fd a ne\u010diteln\u00fd, napi\u0161te ulo\u017eenou SRF funkci. Pokud m\u016f\u017eete, pou\u017e\u00edvejte p\u0159ed p\u0159ipraven\u00e9 p\u0159\u00edkazy.\n\n[[Kategorie:Datab\u00e1ze]]"
}
]
}
}
}
}