Tervetuloa oppitunnille yhdeksän ’Tietojen manipulointi’ opintojaksossa SQL-opetus, joka on osa koulutuskokonaisuutta SQL. Tällä oppitunnilla puhumme SQL:n INSERT-, UPDATE- ja DELETE-lauseista.
Tavoitteet
Tämän oppitunnin lopussa osaat:
-
selittää INSERT-lauseen ja sen käytön
-
kuvailla UPDATE-kyselyä esimerkkien avulla
-
selittää DELETE-lauseen esimerkkien avulla
SISÄYTTÖLAUSEKE
SisäYTTÖLAUSEKKEEN (INSERT) lauseke on yksi kolmesta lausekkeesta, jotka kuuluvat ns.niin kutsuttuun SQL:n ”tietojenkäsittelykielen” osaan – eli INSERT, UPDATE ja DELETE.
Kaikki kolme lauseketta mahdollistavat tietokannan tietojen muuttamisen, ei rakenteen vaan sisällön muuttamisen. Ne eroavat SELECT-lausekkeesta, jonka avulla voit vain lukea tietoja tietokannasta.
INSERT-lausekkeen avulla voit siis lisätä uusia tietueita tietokantataulukkoon. Yleensä sitä käytetään tietueiden lisäämiseen taulukon loppuun. Yksi asia, joka on tärkeä aina kun teet INSERT-lausekkeen, on se, että tietojesi on täytettävä kaikki tietokannassa olevat säännöt.
Katsotaan alla olevaa kuvaa:
Katsotaan ensin esimerkkinä Person-taulua. Tässä meillä on ensisijainen avain. Kyseisen tietokannan ensisijaiset avaimet on aiemmin asetettu automaattiseen lisäykseen SQL:ssämme.
Me emme koskaan lisää ensisijaista avainta itse, vaan jätämme sen tietokannan tehtäväksi. Sitten meillä on etunimi ja sukunimi, joilla on NOT NULL -lippu. Kiinteä timantti osoittaa, että nämä kentät ovat pakollisia, emme voi ohittaa niitä.
Viimeisenä on aikaleima, joka on NOT NULL, mutta samalla oletusarvo on nykyinen aikaleima. Eli taas tuo asettaisi automaattisesti nykyisen aikaleiman, jos emme laita sinne mitään päivämäärää.
Jos meidän on lisättävä tietueita toiseen tauluun, meidän on varmistettava, että tiedot todella ovat olemassa. Tällaisissa päästä päähän -taulukoissa tai missä tahansa vierasavaintaulukkotilanteessa on siis aina varmistettava, että sinne lisättävät tiedot todella edustavat kelvollisia arvoja viitatuissa taulukoissa.
Käyttäkäämme taas Person-taulukkoa lähtökohtana.
Person-taulu on alussa kuvan mukainen.
Harkitaan alla esitettyä esimerkkiä:
select * from person
insert into person
values (7, ’Martin’, ’Holzke’, ’1980-05-05’, ’xxx’, now());
Voitamme syöttää vain yhteen taulukkoon kerrallaan. Tietoja voi yhdistää toisiinsa SELECT-funktiolla, mutta perinteisesti tietoja voi käsitellä vain yhdessä taulukossa kerrallaan.
Mitä olemme edellä olevassa esimerkissä tarkastelleet, on ’implisiittinen lisäys’. Se tarkoittaa, että emme mainitse sarakkeita, jotka haluamme täyttää, vaan meidän on asetettava ne kaikki täsmälleen siihen järjestykseen, jossa taulukko on olemassa.
Sisällyönti sovittaa uudet arvot taulukon sarakkeisiin automaattisesti.
Huomautus: Sisällyönti-, päivitys- ja poisto-lauseen kohdalla on pidettävä mielessä, että tietokannoissa ei ole peruutuspainiketta. Joten kun olet lähettänyt minkä tahansa arvon, se tallentuu tietokantaan.
Koska opettelemme implisiittistä lisäystä, meidän on määritettävä arvot kullekin sarakkeelle. Niinpä tietokantataulukon näkeminen samassa ruudussa auttaa suunnattomasti.
Nyt jos suoritamme yllä olevan koodin, näet, että taulukko on päivittynyt ja lisätty tietue on nähtävissä kohdassa 7.
Myös insert-lause ei palauta mitään, toisin kuin SELECT-lause. Tämän vuoksi näyttö jää sen jälkeen tyhjäksi.
Seuraavaksi katsotaan eksplisiittistä versiota, jota on erittäin suositeltavaa käyttää.
select * from person
insert into person (firstname, lastname, managerid, dob)
values (’Martin’, ’Holzke’, 5, ’1980-05-05’);
Ylläolevassa esimerkissä mainittiin vain 4 taulukon arvoa. Loput saa oletusarvoisesti täytettyä. Kun suoritamme kyselyn, näemme, että uudet arvot on lisätty taulukkoon.
Primääriavaimen arvo on 9 eikä 8, koska kyseistä arvoa, koska sitä on käytetty jo jossain vaiheessa tietokannassa.
Tämä on tietoturvan kannalta edullista, koska primääriavainta ei voi käyttää uudelleen ja virheet voidaan tunnistaa.
Havaitsemme myös, että ’created’-sarakkeen arvoksi asetetaan nykyinen aikaleima, vaikka emme nimenomaisesti mainitse sitä.
Jatkossa, jos haluamme tehdä useita lisäyksiä, voimme suorittaa yllä olevan kyselyn uudestaan ja uudestaan käyttäen eri arvoja.
Moninkertaiset lisäykset voidaan suorittaa vain yhdellä insert-lauseella. Lisäämme uuden joukon arvoja, jotka on erotettu pilkulla.
select * from person
insert into person (firstname, lastname, managerid, dob)
values (’Martin’, ’Holzke’, 5, ’1980-05-05’),
(’Fred’, ’Flintstone’, 5, ’1987-06-02’);
Seuraavassa kuvassa näkyy päivitetty taulukko, kun on tehty useita lisäyksiä.
Jatkossa haluamme tehdä lisäyksen valitsemalla tietoja samasta taulukosta, jolloin tietoja voidaan kopioida, mikä osoittautuu varsin käteväksi tietokannan täyttämisessä. Esimerkiksi tiedonsiirrossa halutaan itse asiassa kopioida tietoja tietokannan sisällä tai eri tietokantojen/taulukoiden välillä.
Seuraavassa koodissa näytetään, miten tietoja kopioidaan.
select * from person
insert into person (etunimi, sukunimi, managerid, dob)
select concat(’copy of’, etunimi), lastname, managerid, dob
from person
where id>=10
Taulukon arvot näkyvät seuraavassa kuvassa, kun suoritamme vain select-lauseen.
Jatketaan nyt kysely yhdessä insert-lauseen kanssa. Tämä voidaan tehdä vain valitsemalla lausekkeet, jotka haluamme suorittaa, ja suorittamalla kyseinen kysely.
Oheisessa kuvassa näkyy, miten taulukko on muuttunut edellä mainitun kyselyn suorittamisen jälkeen.
Siten näimme, miten arvojen kopioiminen insert-lauseen avulla voi täyttää tietokannan. Yleensä täytämme tietokannan, kun suoritamme kyselyitä testiympäristössä.
SQL – Päivitä-kysely
Päivityskysely tai -lausunto on toinen kolmesta tiedonkäsittelykielen lausunnosta, jotka ovat INSERT, UPDATE ja DELETE. UPDATE-kyselyn avulla voit siis muuttaa taulukon olemassa olevia tietueita.
Koska puhumme SQL:stä joukkoihin perustuvana kielenä, UPDATE-kysely toimii joukolle tietueita eikä yhdelle tietueelle (riippuen siitä, miten suoritat UPDATE:n).
Miten siis teemme kaikki päivitykset?
Tarkastellaan alla olevaa kyselyä:
select * from person
where id = 10
update person
set dob = ’1990-01-01’
where id = 10
Edellisessä kyselyssä valitsemme ensin Person-taulusta tietueet, joiden id on 10. Seuraavassa kuvassa näkyy arvo, joka valitaan.
Sitten asetamme Update-kyselyllä DOB:n tietueelle, jonka id on 10. Jos emme käytä where-lauseketta, se päivittää taulukkomme jokaisen tietueen asetettuun arvoon.
Ylläolevan kyselyn suorittamisen jälkeen näemme, että taulukon 10. tietueen dob-arvo on päivitetty.
Nyt voimme päivittää useita asioita tietueessa käyttämällä pilkulla erotettua luetteloa.
Tarkastellaan alla esitettyä kyselyä:
select * from person
where id = 10
update person
set dob = ’1990-01-01’, firstname = ’Mike’
where id = 10
Suorittaessamme yllä olevan kyselyn saamme päivitetyn taulukon esitetyn mukaisesti:
Jatkossa, jos haluamme muuttaa useampaa kuin yhtä tietuetta, voimme tehdä seuraavasti:
select * from person
where firstname = ’Martin’
update person
set firstname = ’Mike’
where firstname = ’Martin’
Edellisessä kyselyssä olemme muuttaneet etunimen arvon Martinista Miken arvoksi riippumatta siitä, missä kohtaa Henkilö-taulukkoa se oli aiemmin.
Harkitaanpa erilaista esimerkkiä:
Henkilö-taulussa ei ollut tilaa tehdä hieman numeerisempia juttuja, joten tarkastellaan projektitaulua.
Tässä yritetään ottaa skenaario, jossa koko taulun käsittely voisi olla suotavaa.
Projektitaulussa on tällä hetkellä kolme tietuetta, kuten kuvassa on esitetty.
Kullakin tietueella on budjettiarvo. Oletetaan, että haluamme päivittää niitä kaikkia noin 20 %. Voimme tehdä tämän alla esitetyllä kyselyllä:
select * from project
update project
set budget = budget*1.2
Ylläoleva kysely korottaisi siis nyt kaikkia projektitaulukon tietueita 20 prosentilla.
Päivitetyt tietueet ovat seuraavat:
Siten arvoille voidaan suorittaa monia matemaattisia operaatioita kuten edellä esitetyssä esimerkissä.
Yksi asia, joka on syytä muistaa UPDATE-lauseketta käytettäessä, on käyttää ’where’-lauseketta aina tarvittaessa. Muuten se päivittää kaikkien taulukon merkintöjen arvot.
SQL DELETE-lauseke
Tarkastellaan nyt DELETE-lausetta, joka on viimeinen lauseke kolmesta tiedonkäsittelykielen lausekkeesta. DELETE-lauseella voidaan poistaa yksi tai useampi tietue kokonaan. Emme puhu yksittäisten sarakkeiden sisällön poistamisesta, koska sitä ei voi tehdä.
Jos haluat tehdä sen, sinun on käytettävä UPDATE-lauseen avulla yksittäisten sarakkeiden sisällön muuttamista. DELETE tarkoittaa siis kokonaisen tietueen tai useiden kokonaisten tietueiden poistamista.
Kuten INSERT- ja UPDATE-lausekkeisiin pätee, kun poistat sarjan tietueita, ne ovat poissa, eikä niitä voi mitenkään saada takaisin.
Meidän on myös varmistettava, että kun poistamme minkä tahansa tietueen taulukosta, kyseistä tietuemerkintää (tai sen arvoa) ei käytetä missään muussa taulukossa.
Henkilö-taulukolla on seuraavat alkuperäiset tietueet.
DELETE-lauseketta voidaan käyttää seuraavassa kyselyssä esitetyllä tavalla:
select * from person
where id = 10
delete from person
where id = 10
Yllä esitetyssä kyselyssä olemme poistaneet tietueen, jonka id-arvo oli 10.
Huomaa: Muista käyttää ’where’-lauseketta DELETE-lauseen kanssa. Muuten lauseke tyhjentää henkilötaulukon nopeasti ilman ’where’-lauseketta.
Kun suoritamme yllä olevan kyselyn, huomaamme, että tietue, jonka id-arvo on 10, on poistettu ja päivitetty taulukko on kuvan mukainen. Kaikki muu säilyy, paitsi se, jolla on ensisijainen avain ten.
On tilanteita, joissa haluamme päästä eroon koko lohkosta. Haluamme esimerkiksi poistaa kaikki kopiomerkinnät henkilö-taulusta kuvan mukaisesti.
Kysely, jolla poistetaan kaikki kopiointitietueet henkilö-taulusta, on esitetty alla:
select * from person
where firstname like ’copy%’
delete from person
where firstname like ’copy%’
Ylläolevassa kyselyssä ’copy%’ tarkoittaa lausekkeita, jotka alkavat sanalla copy ja jatkuvat millä tahansa sanalla edelleen.
Toteuttaessamme yllä olevan kyselyn näemme, että kaikki tietueet, jotka alkoivat sanalla ’Copy’, on poistettu henkilö-taulusta.
Huomautus: DELETE-lauseketta on aina suositeltavaa käyttää yhdelle taululle kerrallaan. Lauseketta ei suositella käytettäväksi yhteisten taulukoiden tapauksessa.
Johtopäätös
Tällä olemme päässeet tämän oppitunnin ’Tiedonkäsittely SQL:ssä’ loppuun. Seuraavalla oppitunnilla keskitytään tapahtumien hallintaan.
{{lectureCoursePreviewTitle}} View Transcript Watch Video
Lisätietoa saat kurssista
SQL-koulutus Sertifiointikoulutus
Siirry kurssille
Lisätietoa saat kurssista