{"id":547,"date":"2023-05-14T14:52:51","date_gmt":"2023-05-14T14:52:51","guid":{"rendered":"https:\/\/tarkvara.thkit.ee\/veeb\/?page_id=547"},"modified":"2023-05-17T10:14:19","modified_gmt":"2023-05-17T10:14:19","slug":"lihtsamad-paringud-lk-26","status":"publish","type":"page","link":"https:\/\/tarkvara.thkit.ee\/veeb\/lihtsamad-paringud-lk-26\/","title":{"rendered":"Lihtsamad p\u00e4ringud"},"content":{"rendered":"\n<p>Enne, kui andmebaasi loomisega edasi l\u00e4heme vaatame, kuidas oleks v\u00f5imalik juba sisestatud andmeid vaadata ja uurida.<\/p>\n\n\n\n<p>Olemasolevate andmete k\u00e4ttesaamiseks sobib p\u00e4ringulause SELECT. Lihtsaim k\u00e4sk k\u00f5igi olemasolevate andmete tabelist k\u00e4tte saamiseks:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSELECT *\nFROM dbo.Laps_tbl\n<\/pre><\/div>\n\n\n<p>Tulemusena joonistub rakenduse allserva kogu tabelit\u00e4is andmeid koos tulpade nimedega.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"493\" height=\"444\" src=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-2.png\" alt=\"\" class=\"wp-image-559\" srcset=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-2.png 493w, https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-2-300x270.png 300w\" sizes=\"auto, (max-width: 493px) 100vw, 493px\" \/><\/figure>\n\n\n\n<p>Sellist p\u00e4ringut kasutatakse vaid erandolukordades ning v\u00e4ga v\u00e4ikeste tabelite juures. Reaalses t\u00f6\u00f6olukorras tuleks kindlasti loetleda \u00fclesse k\u00f5ik v\u00e4ljad, mida soovite vaadata ning seada piirangud ridade arvule!<\/p>\n\n\n\n<p>J\u00e4rjestamiseks piisab lisaklauslist ORDER BY, millele j\u00e4rgneb tulba nimi. Kui soovime k\u00f5ik tabelis olevad andmed tr\u00fckkida sorteerituna nimede j\u00e4rgi v\u00f5ime kirjutada:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"399\" height=\"439\" src=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-3.png\" alt=\"\" class=\"wp-image-561\" srcset=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-3.png 399w, https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-3-273x300.png 273w\" sizes=\"auto, (max-width: 399px) 100vw, 399px\" \/><\/figure>\n\n\n\n<p>Ja tulevadki andmed nimede j\u00e4rgi sordituna. Et esimeses tulbas olevad id-d n\u00e4evad juhuslikult segi paisatutena v\u00e4lja, see on t\u00e4iesti loomulik. Kui sorditakse nime j\u00e4rgi, siis t\u00f5stetakse read niimoodi \u00fcmber, et eesnimed l\u00e4hevad t\u00e4hestikulisse j\u00e4rjekorda. Iga rea andmed aga kuuluvad endiselt kokku. Nii nagu Siiri oli algul 153 sentimeetrit pikk ja s\u00fcndinud aastal 1996, nii on ta seda ka p\u00e4rast j\u00e4rjestamist. Ja samuti tema id-number j\u00e4\u00e4b neljaks.<\/p>\n\n\n\n<p>Tahtes sorteerimisj\u00e4rjekorra muuta vastupidiseks, tuleb tulba nimele lisada t\u00e4hed DESC (s\u00f5nast descending). Ja ongi Siiri esimene ja Juku viimane.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"393\" height=\"401\" src=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-4.png\" alt=\"\" class=\"wp-image-562\" srcset=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-4.png 393w, https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-4-294x300.png 294w\" sizes=\"auto, (max-width: 393px) 100vw, 393px\" \/><\/figure>\n\n\n\n<p>J\u00e4rjestust m\u00e4\u00e4ravaid tulpi v\u00f5ib olla mitu. Sellisel juhul tuleb ORDER BY j\u00e4rgi loetleda v\u00e4ljad t\u00e4htsuse j\u00e4rgi. Nt v\u00f5ttes sorteerimise aluseks s\u00fcnniaasta ning seej\u00e4rel nime saame, et s\u00f5nniaastad on sorteeritud kasvavasse j\u00e4rjekorda ning kui samal aastal on s\u00fcndinud mitu last on nad sorteeritud nimede j\u00e4rgi:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"391\" height=\"359\" src=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-5.png\" alt=\"\" class=\"wp-image-564\" srcset=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-5.png 391w, https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-5-300x275.png 300w\" sizes=\"auto, (max-width: 391px) 100vw, 391px\" \/><\/figure>\n\n\n\n<p>Sugugi alati pole andmete juures vaja k\u00f5iki tulpasid n\u00e4ha. Kui soovin vaadata vaid nime ja pikkust v\u00f5in selle info panna ka SELECT lausesse, loetledes k\u00f5ik vajalikud v\u00e4ljad SELECT\u00b4 i j\u00e4rel.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"293\" height=\"372\" src=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-6.png\" alt=\"\" class=\"wp-image-565\" srcset=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-6.png 293w, https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-6-236x300.png 236w\" sizes=\"auto, (max-width: 293px) 100vw, 293px\" \/><\/figure>\n\n\n\n<p>Samuti saab seada piirangu ridade n\u00e4itamise suhtes. Siin vaid lapsed, kelle s\u00fcnnilinn on Tallinn e. linna kood on 1<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"297\" height=\"367\" src=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-7.png\" alt=\"\" class=\"wp-image-566\" srcset=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-7.png 297w, https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-7-243x300.png 243w\" sizes=\"auto, (max-width: 297px) 100vw, 297px\" \/><\/figure>\n\n\n\n<p>V\u00f5i vaatame, millised lapsed on nooremad kui 12:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"348\" height=\"321\" src=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-9.png\" alt=\"\" class=\"wp-image-569\" srcset=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-9.png 348w, https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-9-300x277.png 300w\" sizes=\"auto, (max-width: 348px) 100vw, 348px\" \/><\/figure>\n\n\n\n<p>Kui tulemusse tekivad korduvad read saame nendest vabaneda kasutades DISTINCT m\u00e4rks\u00f5na. N\u00e4iteks soovime v\u00e4lja selgitada milliste s\u00fcnniaastatega lapsed meil tabelis on? Kui kirjutame SELECT\u00b4i ilma DISCTINCT m\u00e4rks\u00f5nata saame loetelu, kui k\u00f5igi laste s\u00fcnniaastad, kui lisame DISTINCTI saame k\u00f5ik erinevad s\u00fcnniaastad:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"298\" height=\"345\" src=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-12.png\" alt=\"\" class=\"wp-image-573\" srcset=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-12.png 298w, https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-12-259x300.png 259w\" sizes=\"auto, (max-width: 298px) 100vw, 298px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"335\" height=\"297\" src=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-11.png\" alt=\"\" class=\"wp-image-572\" srcset=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-11.png 335w, https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-11-300x266.png 300w\" sizes=\"auto, (max-width: 335px) 100vw, 335px\" \/><\/figure>\n\n\n\n<p>V\u00f5ime piiranguid seada ka numbrivahemike j\u00e4rgi. Selleks on kaks v\u00f5imalust:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Kasutada BETWEEN operaatorit<\/li>\n\n\n\n<li>Kombineerida kaks v\u00f5rratust AND operaatoriga<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"351\" height=\"340\" src=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-13.png\" alt=\"\" class=\"wp-image-576\" srcset=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-13.png 351w, https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-13-300x291.png 300w\" sizes=\"auto, (max-width: 351px) 100vw, 351px\" \/><\/figure>\n\n\n\n<p>Sama tulemuse saaksime ka j\u00e4rgmise SQL lausega:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSELECT Nimi, Synniaasta\nFROM dbo.Laps_tbl\nWHERE Synniaasta &gt;= 1995 AND Synniaasta &lt;= 1997\nORDER BY Nimi\n<\/pre><\/div>\n\n\n<p>BETWEEN operaatori eeliseks on lihtsus ja \u00fclevaatlikus. Loogikaavaldise kasuks r\u00e4\u00e4gib aga paindlikkus &#8211; nimelt v\u00f5ime m\u00f5nest osapoolest v\u00f5rduse \u00e4ra v\u00f5tta j\u00e4ttes t\u00e4psed v\u00e4\u00e4rtused v\u00e4lja. N\u00e4iteks otsime lapsi, kes on s\u00fcndinud alates aastast 1995, kuid enne aastat 1997:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"403\" height=\"291\" src=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-14.png\" alt=\"\" class=\"wp-image-579\" srcset=\"https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-14.png 403w, https:\/\/tarkvara.thkit.ee\/veeb\/wp-content\/uploads\/2023\/05\/image-14-300x217.png 300w\" sizes=\"auto, (max-width: 403px) 100vw, 403px\" \/><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>Enne, kui andmebaasi loomisega edasi l\u00e4heme vaatame, kuidas oleks v\u00f5imalik juba sisestatud andmeid vaadata ja uurida. Olemasolevate andmete k\u00e4ttesaamiseks sobib p\u00e4ringulause SELECT. Lihtsaim k\u00e4sk k\u00f5igi olemasolevate andmete tabelist k\u00e4tte saamiseks: Tulemusena joonistub rakenduse allserva kogu tabelit\u00e4is andmeid koos tulpade nimedega. Sellist p\u00e4ringut kasutatakse vaid erandolukordades ning v\u00e4ga v\u00e4ikeste tabelite juures. Reaalses t\u00f6\u00f6olukorras tuleks kindlasti loetleda &hellip; <a href=\"https:\/\/tarkvara.thkit.ee\/veeb\/lihtsamad-paringud-lk-26\/\" class=\"more-link\">Loe edasi <span class=\"screen-reader-text\">Lihtsamad p\u00e4ringud<\/span> <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-547","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/tarkvara.thkit.ee\/veeb\/wp-json\/wp\/v2\/pages\/547","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/tarkvara.thkit.ee\/veeb\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/tarkvara.thkit.ee\/veeb\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/tarkvara.thkit.ee\/veeb\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/tarkvara.thkit.ee\/veeb\/wp-json\/wp\/v2\/comments?post=547"}],"version-history":[{"count":10,"href":"https:\/\/tarkvara.thkit.ee\/veeb\/wp-json\/wp\/v2\/pages\/547\/revisions"}],"predecessor-version":[{"id":621,"href":"https:\/\/tarkvara.thkit.ee\/veeb\/wp-json\/wp\/v2\/pages\/547\/revisions\/621"}],"wp:attachment":[{"href":"https:\/\/tarkvara.thkit.ee\/veeb\/wp-json\/wp\/v2\/media?parent=547"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}