ALTER TABLE -komentoa käytetään tietokannan rakenteen muokkaamiseen, kuten taulujen uudelleen nimeämiseen, sarakkeiden lisäämiseen tai poistamiseen, sekä erilaisten rajoitteiden, kuten UNIQUE, CHECK ja DEFAULT, asettamiseen taulukoille. Esimerkiksi, jos haluamme luoda tietokannan nimeltä "departments" ja taulun "students", käytämme seuraavia komentoja:

sql
CREATE DATABASE departments;

Tämän jälkeen voimme tarkistaa, että tietokanta on luotu ja muodostaa yhteyden siihen:

sql
DROP TABLE IF EXISTS students;
CREATE TABLE students (
    student_id serial PRIMARY KEY,
    sur_name VARCHAR(25) NOT NULL,
    surname VARCHAR(25) NOT NULL,
    email VARCHAR(80)
);

Tässä vaiheessa olemme luoneet taulun, joka sisältää opiskelijan perustiedot. Voimme myös lisätä uusia sarakkeita, kuten arvosanojen tallentamista varten. Esimerkiksi:

sql
ALTER TABLE students ADD COLUMN marks integer;

Jos myöhemmin haluamme poistaa tämän sarakkeen, voimme käyttää seuraavaa komentoa:

sql
ALTER TABLE students DROP COLUMN marks;

Taulujen rakenteen muokkaaminen ei rajoitu vain sarakkeiden lisäämiseen ja poistamiseen, vaan voimme myös muuttaa sarakkeiden nimiä. Jos haluamme muuttaa "surname"-sarakkeen nimen "last_name"-sarakkeeksi, käytämme seuraavaa komentoa:

sql
ALTER TABLE students RENAME COLUMN surname TO last_name;

Tämän jälkeen voimme tarkistaa, että sarake on nimetty uudelleen:

sql
SELECT * FROM students;

Voimme myös asettaa oletusarvoja sarakkeille. Esimerkiksi "grades"-sarakkeen oletusarvoksi voidaan asettaa "_blank":

sql
ALTER TABLE students ALTER COLUMN grades SET DEFAULT '_blank';

Jos sitten lisäämme rivin ilman arvosanaa, se saa oletusarvon:

sql
INSERT INTO students (student_id, first_name, last_name, email) 


VALUES (019, 'Pramoda', 'Khan', '[email protected]');

Taulun rakenteeseen voidaan myös lisätä rajoitteita, kuten CHECK-rajoite. Esimerkiksi haluamme varmistaa, että arvosanat voivat olla vain tietyt arvot (A, B, C, D, E, U):

sql
ALTER TABLE students ADD CHECK (grades IN ('A', 'B', 'C', 'D', 'E', 'U'));

Jos joku yrittää lisätä tauluun arvosanan, joka ei ole määritelty, kuten 'F', se aiheuttaa virheilmoituksen:

sql
INSERT INTO students (student_id, first_name, last_name, email, grades) 


VALUES (017, 'Tarjani', 'Gururani', '[email protected]', 'F');

Virheilmoitus:

sql
ERROR: new row for relation "students" violates check constraint "students_grades_check"

Tämän lisäksi voimme lisätä UNIQUE-rajoitteen sähköpostiosoitteelle, jotta taulu ei salli kahta samannimistä sähköpostia:

sql
ALTER TABLE students ADD CONSTRAINT unique_email UNIQUE (email);

Jos yritämme lisätä rivin, jossa sähköpostiosoite on jo olemassa, PostgreSQL heittää virheen:

sql
INSERT INTO students (student_id, first_name, last_name, email, grades) 


VALUES (011, 'Kaajal', 'Ramesh', '[email protected]','B');

Tällöin saamme virheilmoituksen:

sql
ERROR: duplicate key value violates unique constraint "unique_email"

Taulun nimeä voi myös muuttaa, kuten esimerkissä, jossa "students"-taulu muutetaan "employees"-tauluksi:

sql
ALTER TABLE students RENAME TO employees;

Samalla voimme tarkistaa, että taulun nimi on muuttunut:

sql
SELECT * FROM employees;

Tietokannan nimeäkin voi muuttaa. Ennen tietokannan nimeämistä, meidän on tarkistettava aktiivisten yhteyksien määrä. Jos tietokanta on aktiivisesti käytössä, täytyy kaikki yhteydet sulkea ennen nimen muuttamista:

sql
SELECT * FROM pg_stat_activity WHERE datname = 'departments';


SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'departments';

Kun yhteydet on suljettu, voimme muuttaa tietokannan nimen:

sql
ALTER DATABASE departments RENAME TO sales;

Tietokannan nimenmuutoksen jälkeen voimme tarkistaa, että uusi nimi on tullut voimaan:

sql
SELECT * FROM pg_database WHERE datname = 'sales';

Tärkeää on huomata, että tietokannan tai taulun rakenteen muuttaminen voi vaikuttaa merkittävästi sovellusten toimintaan. Jos esimerkiksi muokkaat sarakkeen nimeä tai lisää rajoitteita, kuten CHECK- tai UNIQUE-rajoitteita, se voi estää aiempien tietojen lisäämisen tai päivittämisen, jos ne eivät täytä uusia ehtoja. Siksi on tärkeää testata kaikki muutokset huolellisesti ennen kuin ne otetaan käyttöön tuotantotietokannoissa.

Kuinka valita, suodattaa ja järjestää tietoja SQL-kyselyissä

SQL (Structured Query Language) on olennainen työkalu tietokantojen hallintaan, ja sen avulla voidaan suorittaa monenlaisia operaatioita, kuten tietojen hakemista, suodattamista ja järjestämistä. Tässä osassa tarkastelemme SQL-kyselyiden perusteita, kuten SELECT- ja FROM-lausekkeiden käyttöä sekä monia muita hyödyllisiä SQL-operaattoreita.

Tietojen hakeminen tietokannasta alkaa yleensä SELECT-lauseella, jonka avulla valitaan halutut sarakkeet tietyltä taululta. Perusmuoto on seuraava:

sql
SELECT sarake_lista FROM taulun_nimi;

Tässä sarake_lista voi olla joko yksi sarake tai useita sarakkeita pilkulla erotettuna. Jos halutaan valita kaikki sarakkeet taulusta, voidaan käyttää asteriskia (*), kuten seuraavassa esimerkissä:

sql
SELECT * FROM asiakas;

Tämä kysely palauttaa kaikki tiedot "asiakas"-taulusta. Kuitenkin tuotantokoodissa on suositeltavaa välttää SELECT * -kyselyjä, koska sarakkeiden lisääminen tauluun voi muuttaa kyselyn tuloksia. Sen sijaan kannattaa aina määritellä tarvittavat sarakkeet, kuten:

sql
SELECT etunimi, sukunimi FROM asiakas;

Tällä kyselyllä saadaan vain asiakkaitten etunimet ja sukunimet. Myös monimutkaisempia kyselyitä voidaan suorittaa, kuten sarakkeiden yhdistäminen uuteen sarakkeeseen. Esimerkiksi, jos haluamme yhdistää asiakkaan etu- ja sukunimen yhdeksi kentäksi:

sql
SELECT CONCAT(etunimi, ' ', sukunimi) AS "Koko_nimi" FROM asiakas;

Tässä CONCAT-komento yhdistää etu- ja sukunimen ja antaa sille nimen "Koko_nimi". SQL-kyselyissä voidaan myös käyttää WHERE-lauseketta suodattamaan haettavia rivejä. WHERE-lauseessa käytetään yleensä loogisia lausekkeita, jotka palauttavat joko totuusarvon (true/false).

sql
SELECT * FROM asiakas WHERE asiakas_id > 80;

Tämä kysely hakee kaikki asiakkaat, joiden asiakas_id on suurempi kuin 80. Jos tuloksia halutaan järjestää jollain kentällä, käytetään ORDER BY -lauseketta:

sql
SELECT * FROM asiakas WHERE asiakas_id > 80 ORDER BY asiakas_id DESC;

Tässä kyselyssä asiakas_id:t järjestetään laskevassa järjestyksessä. Voimme myös lisätä monimutkaisempia ehtoja käyttäen AND- ja OR-operaattoreita:

sql
SELECT * FROM asiakas WHERE asiakas_id > 80 AND kauppa_id = 'B14';

Tämä kysely valitsee vain ne rivit, joissa asiakas_id on suurempi kuin 80 ja kauppa_id on 'B14'. Jos haluamme poistaa duplikaattirivit, voimme käyttää DISTINCT-operaattoria:

sql
SELECT DISTINCT etunimi, sukunimi, kauppa_id, sahkoposti FROM asiakas WHERE asiakas_id > 80 AND kauppa_id = 'B14';

Tämä kysely palauttaa vain uniikit yhdistelmät asiakkaitten etu- ja sukunimistä, kauppa_id:stä ja sähköposteista.

SQL:ssä voidaan myös suorittaa monimutkaisempia operaatioita kuten JOIN-toimintoja, jotka yhdistävät tietoja useista tauluista. Yleisimmät JOIN-tyypit ovat INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN ja CROSS JOIN. Esimerkiksi, jos haluamme yhdistää kaksi taulua asiakas- ja tilaus-taulut asiakas_id:n perusteella, käytämme INNER JOIN -lausetta:

sql
SELECT asiakas.etunimi, asiakas.sukunimi, tilaus.tilaus_id


FROM asiakas
INNER JOIN tilaus ON asiakas.asiakas_id = tilaus.asiakas_id;

Tässä kyselyssä yhdistetään asiakas-taulun ja tilaus-taulun tiedot asiakas_id:n avulla ja haetaan asiakkaitten etu- ja sukunimiä sekä heidän tilaus_id:nsä.

Tietokannan relaatioita käsitellessä on tärkeää ymmärtää, miten taulut voivat liittyä toisiinsa. Tämä varmistaa tietojen eheyden ja mahdollistaa tehokkaan datan hakemisen. Yleisimpiä tietokannan suhteita ovat yhden ja yhden, yhden ja monen sekä monen ja yhden suhteet.

Esimerkiksi yhden ja yhden (1:1) suhteen tapauksessa yksi rivi yhdestä taulusta liittyy täsmälleen yhteen riviin toisessa taulussa. Tämäntyyppistä suhdetta käytetään, kun halutaan jakaa tietoa eri tauluihin mutta säilyttää niiden välinen yhteys. Esimerkkinä voidaan mainita henkilön ja passin suhde, jossa jokaisella henkilöllä on oma ainutlaatuinen passinsa.

Yhden ja monen (1:M) suhde on yleisempi ja tapahtuu, kun yksi rivi yhdestä taulusta liittyy useaan riviin toisessa taulussa. Esimerkiksi yksi kirjoittaja voi kirjoittaa useita kirjoja, mutta jokaisella kirjalla on vain yksi kirjoittaja.

Monen ja yhden (M:1) suhde on käytännössä toisenlainen yhden ja monen suhteen käänteinen suhde, jossa useat rivit yhdestä taulusta liittyvät yhteen riviin toisessa taulussa.

Näiden suhteiden tuntemus on välttämätöntä tietokannan suunnittelussa ja tehokkaassa kyselyjen laatimisessa. Niiden avulla voidaan varmistaa, että tiedot ovat oikein yhdistettyjä ja että hakukyselyt tuottavat oikeita tuloksia.

Kuinka optimoida PostgreSQL:n suorituskyky: Indeksit, analyysi ja huoltotyöt

PostgreSQL:n tehokas tietokannan optimointi on tärkeä osa tietojärjestelmän hallintaa ja suorituskyvyn parantamista. Yksi keskeisistä käsitteistä tässä prosessissa on kyselyjen optimointi, joka mahdollistaa tietojen nopean ja tarkasti kohdistetun haun. Ymmärtämällä tiedon jakautumista ja sen vaikutuksia kyselyiden suunnitteluun, voidaan saavuttaa merkittäviä parannuksia tietokannan suorituskyvyssä.

Yksi tärkeimmistä työkaluista on ANALYZE-komento, joka kerää tilastotietoja tietokannan taulukoista ja niissä olevista arvoista. Tiedon jakautumisen ymmärtäminen auttaa kyselysuunnittelijaa valitsemaan tehokkaimmat tavat käsitellä tietoa. Esimerkiksi, jos taulukon sarakkeessa on suuri määrä erilaisia arvoja, voi olla järkevää käyttää indeksiä tietojen hakemiseen. Jos taas sarakkeessa on paljon NULL-arvoja, voi suorituskyvyn parantamiseksi valita perinteisen sekventiaalisen skannauksen. ANALYZE-komennolla saatujen tilastojen avulla kyselysuunnittelija voi mukauttaa kyselyiden suorituskykyä dynaamisesti muuttuvan tietojakautuman mukaan, erityisesti taulukoissa, joita päivitetään usein tai joissa on suuri datan vaihto.

ANALYZE-komennon käyttäminen PostgreSQL:ssä on yksinkertaista:

pgsql
ANALYZE VERBOSE [taulukkonimi];

VERBOSE-vaihtoehto tarjoaa lisätietoja kerätyistä tilastoista ja auttaa käyttäjää ymmärtämään, miten tiedot on analysoitu.

Toinen tärkeä osa suorituskyvyn parantamisessa on indeksointi. Indeksit nopeuttavat tietojen hakua merkittävästi ja estävät täydellisten taulukon läpikäyntien (sekventiaalisten skannausten) tarpeen. PostgreSQL tarjoaa useita eri indeksityyppejä, kuten B-puu, GIN, GiST, BRIN ja Hash-indeksit, joista B-puu on yleisin ja toimii erinomaisesti yksinkertaisissa vertailuissa ja aluekyselyissä. GIN-indeksit ovat tehokkaita monimutkaisille tietotyypeille, kuten JSONB:lle ja täysimittaiselle tekstihauille. GiST-indeksit puolestaan soveltuvat geometrisille tiedoille ja verkko-osoitteille.

Indeksit ovat tehokkaita kyselyiden nopeuttamisessa, mutta ne voivat myös hidastaa tietojen kirjoittamista, kuten INSERT, UPDATE ja DELETE -toimintoja. Tämä johtuu siitä, että PostgreSQL:n on päivitettävä indeksi kunkin kirjoituksen yhteydessä. Liian monen indeksin luominen voi lisätä ylläpitokustannuksia ja hidastaa tietokannan suorituskykyä. Indeksien poistaminen, kun niitä ei enää tarvita, on myös tärkeä osa optimointia. Poista indeksi seuraavasti:

pgsql
DROP INDEX [indeksin_nimi];

REINDEX-komento on hyödyllinen työkalu, joka mahdollistaa olemassa olevien indeksien uudelleenrakentamisen. Tämä parantaa tietokannan suorituskykyä ja voi olla tarpeen esimerkiksi silloin, kun indeksi on vioittunut tai kun se sisältää paljon tyhjiä tai lähes tyhjiä sivuja. Uudelleenrakentaminen parantaa kyselyiden suoritustehoa ja vähentää levytilan käyttöä. Esimerkiksi, jos indeksin täyttökerrointa muutetaan, REINDEX-komento voi optimoida sen nykyisten tietokannan asetusten mukaan.

REINDEX-komento voi olla aikaa vievä erityisesti suurilla indekseillä, joten se kannattaa suorittaa huipputuntejen ulkopuolella. Esimerkkejä REINDEX-komennon käytöstä:

pgsql
REINDEX INDEX [indeksin_nimi];
REINDEX TABLE [taulukkonimi];
REINDEX SCHEMA [skeeman_nimi];
REINDEX DATABASE [tietokannan_nimi];
REINDEX SYSTEM postgres;

Optimoitu kysely ei ainoastaan paranna suoritusaikaa, vaan myös vähentää palvelimen kuormitusta ja parantaa tietokannan skaalautuvuutta. Kyselyiden optimoinnin etuja ovat parempi vastausaika, vähentynyt resurssien käyttö ja alemmat kustannukset.

On myös tärkeää ymmärtää huoltotöiden merkitys PostgreSQL-tietokannan pitkäaikaisessa suorituskyvyssä. Tietokannan säännöllinen huolto estää suorituskykyongelmia, kuten hidas kyselyjen suoritus, ja takaa tietojen eheyden. Yleisimmät huoltotehtävät PostgreSQL:ssä ovat VACUUM, ANALYZE ja REINDEX.

VACUUM-komento poistaa vanhentuneet tai poistetut tiedot (ns. kuolleet rivit) tietokannasta, vapauttaen tilaa ja estäen tietokannan turvotusta. PostgreSQL:n Multi-Version Concurrency Control (MVCC) -arkkitehtuuri tuottaa kuolleita rivejä, jotka on poistettava, jotta tietokannan suorituskyky säilyy hyvänä. VACUUM-komento yhdessä ANALYZE-komennon kanssa pitää tietokannan tilastot ajan tasalla ja parantaa kyselyjen suorituskykyä.

VACUUM on erityisen tärkeä monivaiheisten transaktioiden ja suurten tietomäärien käsittelyssä, sillä se estää tietokannan koon kasvamisen hallitsemattomaksi ja varmistaa, että kyselyt voivat toimia nopeasti ja tehokkaasti.

Lopuksi on hyvä huomioida, että vaikka indeksit ja analysointi voivat merkittävästi parantaa PostgreSQL-tietokannan suorituskykyä, niiden luominen ja ylläpitäminen vaatii tasapainottelua. Liian monien indeksien luominen voi hidastaa kirjoitustoimintoja, kun taas huoltotöiden kuten VACUUMin ja REINDEXin laiminlyönti voi johtaa tietokannan turvottamiseen ja suorituskyvyn heikkenemiseen ajan myötä. Oikein ajoitettu huolto, optimoitu kyselyiden suunnittelu ja huolellinen indeksointi luovat perustan tehokkaalle ja luotettavalle PostgreSQL-tietokannan hallinnalle.

Mikä on alikysely ja kuinka se parantaa SQL-kyselyjen tehokkuutta?

Alikysely (tunnetaan myös nimellä sisäinen kysely tai upotettu kysely) on SQL-kysely, joka on upotettu toisen kyselyn sisään. Alikyselyn tuloksia käytetään ulkoisen kyselyn toimesta rajoittamaan tai yhteenvetämään hakutuloksia. Alikyselyjä voidaan sijoittaa eri kohtiin SQL-lauseessa, kuten SELECT-, FROM- ja WHERE-lauseisiin.

Alikyselyjen tyypit

Yhden rivin alikysely palauttaa yhden rivin ja sitä käytetään paikoissa, joissa odotetaan yksittäistä arvoa. Esimerkiksi, jos haluamme löytää asiakkaan, joka on tehnyt eniten maksuja, voidaan käyttää yhden rivin alikyselyä, joka palauttaa asiakkaan tunnuksen, jonka jälkeen ulkoinen kysely hakee kyseisen asiakkaan nimen.

Monirivinen alikysely palauttaa useita rivejä ja sitä käytetään usein IN-, ANY- tai ALL-operaattoreiden kanssa. Esimerkiksi, jos haluamme listata kaikki elokuvat, jotka on vuokrattu asiakkailta, jotka ovat käyttäneet yli 100 dollaria, voidaan käyttää monirivistä alikyselyä, joka ensin löytää asiakkaat, jotka ovat kuluttaneet yli 100 dollaria, ja sen jälkeen valitsee elokuvat, jotka nämä asiakkaat ovat vuokranneet.

Skalaari alikysely palauttaa yksittäisen arvon ja sitä voidaan käyttää paikoissa, joissa odotetaan vain yhtä arvoa, kuten SELECT-lauseessa. Esimerkiksi, jos haluamme näyttää keskimääräisen vuokrauksen keston kunkin vuokrauksen tunnuksen ja päivämäärän yhteydessä, voidaan käyttää skalaari alikyselyä, joka laskee keskimääräisen vuokrausajan.

Korjattu alikysely on alikysely, joka viittaa ulkoisen kyselyn sarakkeisiin. Toisin kuin tavallinen alikysely, joka suoritetaan vain kerran, korjattu alikysely arvioidaan jokaista ulkoisen kyselyn käsiteltävää riviä varten. Tämä tarkoittaa, että alikysely on riippuvainen ulkoisesta kyselystä ja ei voi suoritettua itsenäisesti. Korjatut alikyselyt voivat olla vähemmän tehokkaita, koska niitä suoritetaan useita kertoja (kerran jokaista riviä kohden ulkoisessa kyselyssä), mutta niitä voidaan optimoida esimerkiksi indeksoinnilla ja kyselyn rakenneuudistuksella.

Alikyselyjen käyttötilanteet

Alikyselyjä voidaan käyttää erilaisiin tilanteisiin SQL-kyselyissä, kuten:

  • Tulosten suodattaminen: Alikyselyt käytetään usein WHERE-lauseessa, jotta suodatetaan tuloksia monimutkaisempien ehtojen mukaan. Esimerkiksi voidaan listata kaikki asiakkaat, jotka ovat vuokranneet ainakin yhden elokuvan.

  • Aggregaattiarvojen laskeminen: Alikyselyjä voidaan käyttää laskemaan aggregaattiarvoja, kuten asiakaskohtaista kokonaissummaa ja sisällyttämään nämä tulokset pääkyselyyn.

Yhteinen taulukkoilmaus (CTE) on PostgreSQL:ssä käytettävä väliaikainen tulosjoukko, johon voidaan viitata SELECT-, INSERT-, UPDATE- tai DELETE-lauseissa. CTE:t määritellään WITH-avainsanalla ja ne tarjoavat tavan jäsentää monimutkaisia kyselyjä selkeämmiksi ja ylläpidettävämmiksi. CTE:t ovat erityisen hyödyllisiä monimutkaisessa kyselyssä, koska ne auttavat hajottamaan kyselyn osiin, jotka ovat helpommin ymmärrettäviä ja luettavissa.

CTE:itä voidaan käyttää myös toistuvan koodin poistamiseen. Määrittelemällä CTE kerran ja viittaamalla siihen useita kertoja, voidaan välttää saman alikyselyn logiikan toistaminen useaan kertaan SQL-lauseessa. Lisäksi, rekursiivisia CTE:itä voidaan käyttää erityisesti hierarkisten tai rekursiivisten tietojen, kuten organisaatiokaavioiden tai vierekkäislistojen, kyselyyn.

Rekursiiviset CTE:t ja niiden käyttö

Rekursiivinen CTE viittaa itseensä ja sitä käytetään kyselyissä, joissa on hierarkkisia tai rekursiivisia suhteita. Esimerkiksi organisaatiorakenteet ja puu-rakenteiset tiedot voivat hyötyä rekursiivisistä CTE:istä. CTE:n käyttö helpottaa myös koodin lukemista ja suorituskyvyn optimointia, erityisesti silloin, kun käsitellään suuria tietomääriä.

Esimerkki CTE:n käytöstä: Jos haluamme löytää asiakkaan, joka on tehnyt eniten maksuja, voimme käyttää CTE:tä kyselyn selkeyttämiseksi ja suorituskyvyn parantamiseksi. Tällöin määrittelemme CTE:n nimeltä "top_customer", joka laskee asiakaskohtaiset maksut ja sen jälkeen pääkysely palauttaa asiakkaan nimen.

Yhteenveto

Alikyselyjen ja CTE:iden tehokas käyttö voi merkittävästi parantaa SQL-kyselyjen luettavuutta, suorituskykyä ja ylläpidettävyyttä. Alikyselyt tarjoavat joustavan tavan käsitellä monimutkaisempia kyselyitä ja rajoittaa tai tiivistää hakutuloksia. CTE:t puolestaan tarjoavat rakenteen, joka auttaa selkeyttämään monimutkaisempia kyselyjä ja vähentämään toistoa. Näiden työkalujen ymmärtäminen ja tehokas käyttö ovat keskeisiä taitoja SQL:n mestariksi tulemisessa.

Mikä on tasaveron ja myyntiveron vaikutus verotukseen ja oikeudenmukaisuuteen?
Kuinka nanomateriaalit voivat parantaa kalvoteknologian suorituskykyä paine-viivästetyn osmoosin prosessissa
Miten Trumpin hallinto muovasi kriisin keskellä?