{"id":16,"date":"2024-04-04T06:16:28","date_gmt":"2024-04-04T03:16:28","guid":{"rendered":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/sql-paringud\/"},"modified":"2024-04-04T06:16:43","modified_gmt":"2024-04-04T03:16:43","slug":"sql-paringud","status":"publish","type":"page","link":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/sql-paringud\/","title":{"rendered":"SQL p\u00e4ringud \u00fchetabelilisel andmebaasil"},"content":{"rendered":"
\n\t\u00a0\n<\/div>\n
\n\tP\u00e4ringute akna avamiseks otsi \u00fcles Query Tool ja kl\u00f5psa sellel.\n<\/div>\n
\n\t\u00a0\n<\/div>\n
\n\t\"0naidisparing\"\n<\/div>\n
\n\t\u00a0\n<\/div>\n
\n\tK\u00f5ige lihtsam p\u00e4ring k\u00f5igi tabelis olevate ridade kuvamiseks:\n<\/div>\n
\n\t\u00a0\n<\/div>\n
\n\tSELECT<\/strong> <\/span>* FROM<\/span><\/strong> \u201cIsik\u201d;\n<\/div>\n
\n\tSemikoolon t\u00e4histab sql keeles \u00fche p\u00e4ringu l\u00f5ppu.\n<\/div>\n
\n\tAlati peab \u00fctlema, millisest tabelist andmeid p\u00e4rime. Tabeli nimi peab olema jutum\u00e4rkides, nt \u201cIsik\u201d.\n<\/div>\n
\n\t\u00a0\n<\/div>\n
\n\t\u00dclds\u00fcntaks: SELECT<\/span><\/span><\/span><\/b> <veerunimi> FROM<\/span><\/b> <tabeli nimi>;<\/span><\/span>\n<\/div>\n
\n\tSiin SELECT on k\u00e4sk (command), eksisteerivad ka operaatorid (nt loogilised operaatorid OR, AND, NOT) ja parameetrid (tunnused), mille alusel andmeid sorteeritakse.\n<\/div>\n
\n\tSQL keeles on rida s\u00f5nu, millel on kindlaksm\u00e4\u00e4ratud l\u00f5plik t\u00e4hendus, need on niinimetatud v\u00f5tmes\u00f5nad, nt SELECT, UPDATE. Need ei ole t\u00f5stutundlikud, st et UPDATE ja update ja Update on t\u00e4henduselt v\u00f5rdsed. On kokkuleppeline, et need v\u00f5tmes\u00f5nad kirjutatakse l\u00e4biva suurt\u00e4hega ja tabelite nimed l\u00e4bivalt v\u00e4ikeset\u00e4hega.\n<\/div>\n
\n\tSaab lisada kommentaare:\n<\/div>\n
\n\t\u00dcherealise kommentaari lisamiseks kaks kriipsu \u2013 \u2013\n<\/div>\n
\n\t\u00a0\n<\/div>\n
\n\tMitmerealise kommentaari lisamiseks\n<\/div>\n
\n\t\u00a0\n<\/div>\n
\n\t\/* kommentaar\n<\/div>\n
\n\tkommentaar j\u00e4tkub\n<\/div>\n
\n\t*\/\n<\/div>\n
\n\t\u00a0\n<\/div>\n
\n\tKommentaaride abil saab p\u00e4ringu osi v\u00e4lja kommenteerida v\u00f5i \u00fcles m\u00e4rkida, mida see p\u00e4ring t\u00e4pselt teeb.\n<\/div>\n
\n\t* t\u00e4histab k\u00f5iki veerge, aga saab ka eraldi v\u00e4lja tuua veerud, millest andmeid kuvada soovime, nt:\n<\/div>\n
\n\t\u00a0\n<\/div>\n
\n\tSELECT<\/strong> <\/span>perenimi, eesnimi FROM<\/span><\/strong> \u201cIsik\u201d;\n<\/div>\n
\n\t\u00a0\n<\/div>\n
\n\tP\u00e4ringu k\u00e4ivitamisel kuvatakse tulemuseks:\n<\/div>\n
\n\t\u00a0\n<\/div>\n
\n\t\"paringunaide1\"\n<\/div>\n
\n\t\u00a0\n<\/div>\n
\n\t\u00a0\n<\/div>\n
\n\tP\u00e4ringuga saab muuta tabelis olevate veergude j\u00e4rjestust:\n<\/div>\n
\n\t\u00a0\n<\/div>\n
\n\t\u00a0\n<\/div>\n
\n\tSELECT<\/strong> <\/span>eesnimi, perenimi, vara_vaartus FROM<\/span><\/strong> \u201cIsik\u201d;\n<\/div>\n
\n\t\u00a0\n<\/div>\n
\n\t\"paringunaide2\"\n<\/div>\n
\n\t\u00a0\n<\/div>\n
\n\tSaab lisada tingimusi, millele vastavaid kirjeid tahame, et kuvataks, n\u00e4iteks kui tahame k\u00f5iki isikuid, kes on vanemad kui 21, siis:\n<\/div>\n
\n\tSELECT<\/strong> <\/span>perenimi, eesnimi, vanus FROM<\/span><\/strong> \u201cIsik\u201d WHERE<\/span><\/strong> vanus \u02c3 21;\n<\/div>\n
\n\t\u00a0\n<\/div>\n
\n\t\"paringunaide3\"\n<\/div>\n
\n\t\u00a0\n<\/div>\n
\n\tLisame \u00fche isiku andmed juurde. SQL k\u00e4sk peab sisaldama ainult nende v\u00e4ljade andmeid, mis ei ole automaatselt t\u00e4idetavad, id ja kirje lisamise aeg t\u00e4idetakse andmebaasijuhtimiss\u00fcsteemi poolt ise:\n<\/div>\n
\n\t\u00a0\n<\/div>\n
\n\tINSERT INTO<\/strong><\/span> \u201cIsik\u201d (perenimi, eesnimi, sugu, vanus, vara_vaartus) VALUES<\/span><\/strong> (\u2018<\/span>Soo\u2018<\/span>, \u2018<\/span>Mart\u2018<\/span>, \u2018<\/span>m\u2018<\/span>, 38.5, 279.5);\n<\/div>\n
\n\t\u00a0\n<\/div>\n
\n\t\"4.png\"\n<\/div>\n
\n\tIsikuandmetest andmebaasi tehes on m\u00f5istlik alati perenimi ja eesnimi eraldi v\u00e4ljadel esitada, sest kokku saab neid alati tuua. K\u00f5iki veerge saab ka p\u00e4ringutega \u00fcmber nimetada, andmebaasi mudeli disainimisel veergude nimetamisel peab seega eelk\u00f5ige silmas pidama seda, et veeru nimetus oleks meeldej\u00e4\u00e4v ja l\u00fchike, v\u00e4ljav\u00f5tete tegemisel saab muuta.\n<\/div>\n
\n\tSELECT<\/strong><\/span> perenimi ||<\/span> \u2018, \u2018<\/span><\/strong> || eesnimi AS<\/strong> <\/span><\/span>t\u00e4isnimi, vanus FROM<\/span><\/strong> \u201cIsik\u201d;\n<\/div>\n
\n\t\u00a0\n<\/div>\n
\n\t\"5.png\"
\u00a0\n<\/div>\n
\n\t\"11.png\"\n
\n\t\t\u00a0\n\t<\/div>\n
\n\t\tProovi, mis juhtuks siis kui \u00fclakomade vahel ei paneks koma j\u00e4rele t\u00fchikut.\n\t<\/div>\n<\/div>\n

\n\t\u00a0\n<\/p>\n

\n

\n\t\tKustutame lisatud isiku j\u00e4lle \u00e4ra, l\u00e4htume sellest, et teame tema id-d, mille saame tingimusena ette anda:\n\t<\/p>\n

\n\t\tDELETE FROM<\/span><\/strong> \u201cIsik\u201d WHERE <\/span><\/strong>id = 7;\n\t<\/p>\n

\n\t\tV\u00f5i ka DELETE FROM<\/span><\/strong> \u201cIsik\u201d WHERE perenimi <\/span><\/strong>= \u2018Soo\u2019 AND<\/span><\/strong> eesnimi = \u2018<\/span><\/strong>Mart\u2018<\/span><\/strong>;\n\t<\/p>\n

\n\t\t\"11.png\"\n
\n\t\t\t\u00a0\n\t\t<\/div>\n
\n\t\t\tMis juhtuks kui kui kustutades m\u00e4rgiksime tingimusena ainult WHERE eesnimi = \u2018 Mart \u2018? Mitu isikut kustutataks?\n\t\t<\/div>\n
\n\t\t\t\u00a0\n\t\t<\/div>\n
\n\t\t\t\u00a0\n\t\t<\/div>\n<\/div>\n<\/div>\n
\n\t\u00a0\n<\/div>\n","protected":false},"excerpt":{"rendered":"

\u00a0 P\u00e4ringute akna avamiseks otsi \u00fcles Query Tool ja kl\u00f5psa sellel. \u00a0 \u00a0 K\u00f5ige lihtsam p\u00e4ring k\u00f5igi tabelis olevate ridade kuvamiseks: \u00a0 SELECT * FROM \u201cIsik\u201d; Semikoolon t\u00e4histab sql keeles \u00fche p\u00e4ringu l\u00f5ppu. Alati peab \u00fctlema, millisest tabelist andmeid p\u00e4rime. …<\/p>\n","protected":false},"author":231,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_acf_changed":false,"inline_featured_image":false,"footnotes":""},"class_list":["post-16","page","type-page","status-publish","hentry"],"acf":[],"_links":{"self":[{"href":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/wp-json\/wp\/v2\/pages\/16","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/wp-json\/wp\/v2\/users\/231"}],"replies":[{"embeddable":true,"href":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/wp-json\/wp\/v2\/comments?post=16"}],"version-history":[{"count":1,"href":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/wp-json\/wp\/v2\/pages\/16\/revisions"}],"predecessor-version":[{"id":135,"href":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/wp-json\/wp\/v2\/pages\/16\/revisions\/135"}],"wp:attachment":[{"href":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/wp-json\/wp\/v2\/media?parent=16"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}