Opi SQL:ää – opas, jossa on koo­die­si­merk­ke­jä

SQL:n syntaksi perustuu re­laa­tio­al­gebraan, mikä erottaa tämän oh­jel­moin­ti­kie­len muista kielistä. Syntaksin opettelu käytännön esi­merk­kien avulla voi auttaa sinua oppimaan SQL:ää te­hok­kaas­ti.

Mikä on SQL-syntaksi?

Oh­jel­moin­nis­sa syntaksi tar­koit­taa sitä, miten oh­jel­moin­ti­kie­li kir­joi­te­taan. Syntaksi mää­rit­te­lee koodin pe­rus­ra­ken­teet ja niiden yh­dis­tä­mis­ta­vat. Syntaksin ym­mär­tä­mi­nen on oh­jel­moin­ti­kie­lil­lä koodin lukemisen ja kir­joit­ta­mi­sen pe­rus­e­del­ly­tys.

SQL:n tär­keim­mät syn­tak­si­ra­ken­teet ovat SQL-lauseet, jotka voivat sisältää myös lauseosia. Molempia kutsutaan yleisesti ”SQL-ko­men­noik­si”, vaikka tek­ni­ses­ti katsoen tämä ei olekaan täysin tarkka ilmaisu. Nämä eivät kui­ten­kaan ole ainoat SQL-syn­tak­si­ra­ken­teet. Alla olevasta tau­lu­kos­ta löydät yleis­kat­sauk­sen SQL-syn­tak­si­ra­ken­teis­ta.

Miten SQL-koodi suo­ri­te­taan?

SQL-koodi on läh­de­koo­di­na teks­ti­tie­dos­tois­sa. Koodi herää eloon vasta sopivassa suo­ri­tusym­pä­ris­tös­sä. SQL-tulkki lukee läh­de­koo­din ja muuntaa sen RDBMS-jär­jes­tel­män toi­min­noik­si. Tässä on kaksi pe­rus­lä­hes­ty­mis­ta­paa:

1. Suorita SQL-koodiin­te­rak­tii­vi­ses­ti Tässä me­ne­tel­mäs­sä SQL-koodi syötetään tai ko­pioi­daan suoraan teks­ti­ruu­tuun. SQL-koodi suo­ri­te­taan, ja tulos näytetään. Voit muokata koodia ja suorittaa sen uudelleen. Koodin muok­kaa­mi­sen ja tulosten näyt­tä­mi­sen nopea vuo­rot­te­lu tekee tästä me­ne­tel­mäs­tä parhaiten sopivan mo­ni­mut­kais­ten kyselyjen op­pi­mi­seen ja luomiseen. 2. Suorita SQL-koodi skriptinäTässä me­ne­tel­mäs­sä koko SQL-koodia sisältävä läh­de­koo­di­tie­dos­to suo­ri­te­taan rivi riviltä. Tar­vit­taes­sa pa­lau­tet­ta lä­he­te­tään käyt­tä­jäl­le vasta suo­ri­tuk­sen lopussa. Tämä menetelmä sopii parhaiten pro­ses­sien au­to­ma­ti­soin­tiin ja MySQL-tie­to­kan­ta­var­muus­ko­pioi­den tuontiin MySQL-dump-ko­men­nol­la.

Tuo­te­hal­lin­ta­jär­jes­tel­män mää­rit­tä­mi­nen SQL:n avulla

Helpoin tapa oppia oh­jel­moin­ti­kiel­tä on kir­joit­taa ja suorittaa koodia itse. Tässä ope­tus­oh­jel­mas­sa luomme pienen tie­to­kan­nan ja teemme siihen kyselyjä. Tätä varten käytämme verk­ko­si­vus­ton sql.js tarjoamaa online-SQL-tulkkia. Voit seurata ope­tus­oh­jel­maa siir­ty­mäl­lä si­vus­tol­le ja kor­vaa­mal­la sinne jo syötetyn SQL-koodin esi­merk­kien koodilla. Suorita koodi pala palalta, jotta tulokset tulevat näkyviin.

Luo SQL-tie­to­kan­ta

Tässä esi­mer­kis­sä ra­ken­nam­me kau­pal­li­sen tuo­tan­hal­lin­ta­jär­jes­tel­män eräälle kaupalle. Vaa­ti­muk­set ovat seuraavat:

• Tuotteita on useita, ja jo­kai­ses­ta tuot­tees­ta on va­ras­tos­sa tietty määrä.
• Asia­kas­kun­taam­me kuuluu monia asiak­kai­ta.
• Asiak­kai­den tekemät tilaukset voivat sisältää useita tuotteita.
• Jo­kai­ses­ta ti­lauk­ses­ta tal­len­nam­me ti­laus­päi­vän ja tilaajan tiedot sekä tilatut tuotteet ja ti­laus­mää­rän.

Nämä vaa­ti­muk­set muun­ne­taan ab­strak­tik­si ku­vauk­sik­si ja sen jälkeen SQL-koodiksi:

1. Luo malli
2. Määritä skeema
3. Syötä tietueet
4. Määritä kyselyt

Luo malli en­ti­tee­teis­tä ja suhteista

En­sim­mäi­nen vaihe to­teu­te­taan paperilla tai eri­tyi­sil­lä mal­lin­nus­työ­ka­luil­la. Keräämme tietoa mal­lin­net­ta­vas­ta jär­jes­tel­mäs­tä, jotta voimme määrittää en­ti­tee­tit ja suhteet. Tämä vaihe to­teu­te­taan usein en­ti­teet­ti-suh­de­kaa­vio­na (ER-kaaviona).

Mitä en­ti­teet­te­jä on olemassa ja miten ne liittyvät toisiinsa? En­ti­tee­tit ovat asioiden luokkia. Tuo­te­hal­lin­ta­jär­jes­tel­män esi­mer­kis­säm­me en­ti­teet­te­jä ovat tuotteet, asiakkaat ja tilaukset. Jokaista en­ti­teet­tiä varten tarvitaan oma taulukko. Re­laa­tio­mal­lin eri­tyis­piir­tei­den vuoksi suhteiden mal­lin­ta­mi­sek­si lisätään uusia tau­lu­koi­ta. Tämän ym­mär­tä­mi­nen ja asian­mu­kai­nen to­teut­ta­mi­nen vaatii kokemusta.

Keskeinen kysymys, johon on löy­det­tä­vä vastaus, on se, miten en­ti­tee­tit liittyvät toisiinsa. Tässä yh­tey­des­sä on otettava huomioon suhteen molemmat suunnat ja tehtävä ero yksikön ja monikon välillä. Seu­raa­vas­sa on esimerkki, jossa käytetään autoja ja auton omistajia:

1. Yksi omistaja voi omistaa useita autoja.
2. Auto voi kuulua vain yhdelle omis­ta­jal­le.

Näiden kahden tahon välillä voidaan havaitakolme mah­dol­lis­ta suh­de­mal­lia:

Tuot­tei­den käyt­töön­ot­to

Aluksi luomme tuo­te­tie­to­kan­nan. Tätä varten meidän on mää­ri­tel­tä­vä tie­to­mal­li, syö­tet­tä­vä tietueita ja suo­ri­tet­ta­va muutamia yk­sin­ker­tai­sia kyselyjä testausta varten.

Määritä skeema

Tie­to­kan­ta­tau­lu­koi­den mää­rit­tä­mi­sen keskeinen SQL-komento on CREATE TABLE. Tämän komennon avulla voit luoda taulukon, jolle annetaan nimi, ja määrittää sa­rak­kei­den omi­nai­suu­det. Samalla mää­ri­tel­lään tie­to­tyy­pit ja tar­vit­taes­sa tal­len­net­ta­via arvoja koskevat ra­joi­tuk­set:

DROP TABLE IF EXISTS Products;
CREATE TABLE Products ( product_id int, product_name text, stocked int, price int );

Lisää tie­to­ko­ko­nai­suuk­sia

Luomme nyt muutaman tes­ti­tie­tu­een. Käytämme kenttien täyt­tä­mi­seen SQL-komentoa INSERT INTO sekä VALUES-funktiota:

INSERT INTO Products VALUES (10, 'ABC Product', 74, 1050);
INSERT INTO Products VALUES (20, 'KLM Product', 23, 750);
INSERT INTO Products VALUES (30, 'XYZ Product', 104, 350);

Määritä kyselyt

Tar­kis­taak­sem­me Products-taulukon tilan kir­joi­tam­me yk­sin­ker­tai­sen kyselyn. Käytämme SELECT FROM -komentoa ja tu­los­tam­me koko taulukon:

SELECT * FROM Products;

Nyt kir­joi­tam­me hieman mo­ni­mut­kai­sem­man kyselyn, joka laskee va­ras­tos­sam­me olevien tuot­tei­den ko­ko­nai­sar­von:

SELECT product_name AS 'Name', (stocked * price) AS 'Value' FROM Products;

Lisää tau­lu­koi­ta

Seu­raa­vak­si luomme loput tar­vit­se­mam­me taulukot. Toimimme samalla tavalla kuin Tuotteet-taulukon kohdalla. Ensin luomme Asiakkaat-taulukon:

DROP TABLE IF EXISTS Customers;
CREATE TABLE Customers ( customer_id int, customer_name text, contact text );

Sitten syötämme kahden esi­merk­kia­siak­kaan tiedot:

INSERT INTO Customers VALUES (100, 'EDC Customer', 'ED@example.com');
INSERT INTO Customers VALUES (200, 'WVU Customer', 'WV@example.com');

Tar­kis­taak­sem­me, toimiiko se, tu­los­tam­me asia­kas­tie­dos­ton:

SELECT * FROM Customers;

Seu­raa­vak­si luodaan Orders-taulukko:

DROP TABLE IF EXISTS Orders;
CREATE TABLE Orders ( order_id int, customer_id int, order_date text );

Syötämme nyt kolme esi­merk­ki­ti­laus­ta. En­sim­mäi­sek­si arvoksi mää­ri­täm­me tun­nis­teen (ID) en­si­si­jai­sek­si avaimeksi. Toinen arvo on olemassa olevien asia­kas­tun­nis­tei­den, jotka toimivat vii­tea­vai­mi­na. Sitten tal­len­nam­me ti­laus­päi­vän:

INSERT INTO Orders VALUES (1000, 100, '2022-05-03');
INSERT INTO Orders VALUES (1001, 100, '2022-05-04');
INSERT INTO Orders VALUES (1002, 200, '2022-05-08');

Testaamme sitä antamalla seuraavat komennot:

SELECT * FROM Orders;

Lopuksi tar­vit­sem­me taulukon, joka sisältää tilauksen tuotteet ja niiden määrät. Kyseessä on m:n-suhde, koska yksi tilaus voi sisältää useita tuotteita ja yksi tuote voi esiintyä useissa ti­lauk­sis­sa. Mää­ri­tel­lään taulukko, joka sisältää tilausten ja tuot­tei­den tun­nis­teet vie­ra­sa­vai­mi­na:

DROP TABLE IF EXISTS OrderItems;
CREATE TABLE OrderItems ( orderitem_id int, order_id int, product_id int, count int );

Syötämme nyt muutaman tilatun tuotteen. Va­lit­sem­me tilausten ja tuot­tei­den tun­nis­teet siten, että yksi tilaus sisältää kaksi tuotetta ja toinen tilaus vain yhden tuotteen:

INSERT INTO OrderItems VALUES (10001, 1000, 10, 3);
INSERT INTO OrderItems VALUES (10002, 1000, 20, 2);
INSERT INTO OrderItems VALUES (10003, 1002, 30, 12);

Tar­kis­taak­sem­me tämän, toi­mi­tam­me tilatut tuotteet:

SELECT * FROM OrderItems;

Kirjoita mo­ni­mut­kai­sia kyselyitä

Jos olet suo­rit­ta­nut kaikki tähän mennessä esitetyt koo­din­pät­kät, sinun pitäisi pystyä ym­mär­tä­mään tes­ti­tie­to­kan­tam­me rakenne. Siir­ry­tään nyt mo­ni­mut­kai­sem­piin ky­se­lyi­hin, jotka osoit­ta­vat SQL:n te­hok­kuu­den. Kir­joi­te­taan ensin kysely, joka yhdistää useisiin tau­lu­koi­hin ha­jau­te­tut tiedot. Käytämme SQL JOIN -komentoa yh­dis­tä­mään taulukot, jotka si­säl­tä­vät asia­kas­tie­dot ja tilaukset. Samalla nimeämme sarakkeet ja asetamme vastaavan asia­kas­tun­nuk­sen JOIN-ehdoksi. Muista, että käytämme mää­ri­tel­ty­jä tun­nis­tei­ta erot­ta­maan kahden taulukon sarakkeet toi­sis­taan:

SELECT customers.customer_name as 'Customer', customers.customer_id, orders.order_id, orders.order_date AS 'Date' FROM Customers JOIN Orders ON Orders.customer_id = Customers.customer_id ORDER BY Customers.customer_id;

Nyt laskemme ti­lat­tu­jen tuot­tei­den ko­ko­nais­kus­tan­nuk­set toisen JOIN-komennon avulla:

SELECT OrderItems.order_id, OrderItems.orderitem_id AS 'Order Item', Products.product_name AS 'Product', Products.price AS 'Unit Price', OrderItems.count AS 'Count', (OrderItems.count * Products.price) AS 'Total' FROM OrderItems JOIN Products ON OrderItems.product_id = Products.product_id;