Õpi SQL-i – õpetus koos koo­di­näi­de­tega

SQL-i süntaks põhineb re­lat­sioo­ni­li­sel algebraal, mis eristab seda prog­ram­mee­ri­mis­keelt teistest keeltest. Sün­tak­siga tutvumine prak­ti­liste näidete abil aitab sul SQL-i tõhusalt omandada.

Mis on SQL-süntaks?

Prog­ram­mee­ri­mises tähendab süntaks seda, kuidas prog­ram­mee­ri­mis­keelt kir­ju­ta­takse. Süntaks määrab kindlaks koodi põ­hi­ele­men­did ja nende oma­va­he­lise ühen­da­mise viisi. Süntaksi mõistmine on prog­ram­mee­ri­mis­keelte koodi lugemise ja kir­ju­ta­mise põ­hi­tin­gi­mus.

SQL-i kõige olu­li­se­mad sün­tak­si­ele­men­did on SQL-käskud, mis võivad sisaldada ka klausleid. Mõlemat ni­me­ta­takse ta­va­li­selt „SQL-käskudeks“, kuigi teh­ni­li­sest sei­su­ko­hast ei ole see päris täpne. Need ei ole aga ainsad SQL-i sün­tak­si­ele­men­did. Allpool on tabel, mis annab ülevaate SQL-i sün­tak­si­ele­men­ti­dest.

Kuidas SQL-koodi täi­de­takse?

SQL-kood on teks­ti­fai­li­des al­li­ka­koo­dina. Kood saab elu alles sobivas täit­mis­kesk­kon­nas. Al­li­ka­koodi loeb SQL-tõl­gen­daja ja teisendab selle re­lat­sioo­ni­lise and­me­ba­asi hal­dus­süs­teemi (RDBMS) toi­min­gu­teks. Siin on kaks põhilist lä­he­ne­mis­viisi:

1. SQL-koodi in­te­rak­tiivne käi­vi­ta­mineSelle meetodi puhul si­ses­ta­takse või ko­pee­ri­takse SQL-kood otse teks­tiak­nasse. SQL-kood käi­vi­ta­takse ja tulemus kuvatakse. Koodi saab muuta ja uuesti käivitada. Kiire koodi muutmine ja tulemuste kuvamine teeb selle meetodi kõige so­bi­va­maks keerukate päringute õp­pi­miseks ja loomiseks. 2. SQL-koodi käi­vi­ta­mine skriptinaSelle meetodi puhul käi­vi­ta­takse kogu SQL-koodi sisaldav läh­te­koo­di­fail rida-realt. Vajaduse korral saa­de­takse ka­su­tajale ta­ga­si­side alles käi­vi­ta­mise lõpus. See meetod sobib kõige paremini prot­ses­side au­to­ma­ti­see­ri­miseks ja MySQL-and­me­ba­asi va­ru­koo­piate im­por­ti­miseks MySQL dump’iga.

Kuidas luua too­te­hal­dus­süs­teem SQL-i abil

Prog­ram­mee­ri­mis­keele õppimise lihtsaim viis on ise koodi kirjutada ja seda käivitada. Selles õpetuses loome väikese and­me­ba­asi ja teeme selle põhjal päringuid. Selleks kasutame vee­bi­sai­dil sql.js asuvat vee­bi­põ­hist SQL-tõlget. Õpetuse jär­gi­miseks mine vee­bi­sai­dile ja asenda sinna juba si­ses­ta­tud SQL-kood meie näidete koodiga. Käivita kood tükk­haa­val, et tulemused kuvataks.

SQL-and­me­ba­asi sea­dis­ta­mine

Selles näites loome poe jaoks kau­ban­dus­liku too­te­hal­dus­süs­teemi. Nõuded on järgmised:

• Meil on mitmeid tooteid ja igast tootest on laos teatud kogus.
• Meie klientuur hõlmab paljusid kliente.
• Klientide esitatud tel­li­mu­sed võivad sisaldada mitut toodet.
• Iga tellimuse kohta sal­ves­tame tellimuse kuupäeva ja tellija andmed, samuti tellitud tooted ja kogused.

Need nõuded vor­mis­ta­takse abst­rakt­seks kir­jel­duseks ja seejärel SQL-koodiks:

1. Loo mudel
2. Määratle skeem
3. Sisesta andmeid
4. Määrake päringud

Loo en­ti­teetide ja seoste mudel

Esimene etapp toimub paberil või spet­siaal­sete mo­del­lee­ri­mis­töö­riis­tade abil. Kogume mo­del­lee­ri­tava süsteemi kohta teavet, et tuletada välja en­ti­teedid ja suhted. See etapp viiakse sageli ellu entiteedi-suhe (ER) diag­ram­mina.

Millised en­ti­teedid on olemas ja kuidas need omavahel seotud on? En­ti­teedid on asjade klassid. Meie too­te­hal­dus­süs­teemi näites on en­ti­teeti­deks tooted, kliendid ja tel­li­mu­sed. Iga entiteedi jaoks on vaja eraldi tabelit. Re­lat­sioo­ni­mu­deli eripärast tu­le­ne­valt lisatakse suhete mo­del­lee­ri­miseks täien­da­vaid tabeleid. Selle mõistmine ja nõue­te­ko­hane ra­ken­da­mine nõuab kogemust.

Peamine küsimus, millele tuleb vastata, on see, kuidas objektid omavahel seotud on. Siin tuleb arvesse võtta suhte mõlemat suunda ning teha vahet ainsuse ja mitmuse vahel. Siin on näide, kus ka­su­ta­takse autosid ja au­tooma­nikke:

1. Ühel omanikul võib olla mitu autot.
2. Auto võib kuuluda ainult ühele omanikule.

Nende kahe üksuse vahel ilmnevadkolm või­ma­likku suhete mudelit:

Tooted ka­su­tusele võtta

Esmalt loome toodete tabeli. Selleks peame mää­rat­lema skeemi, sisestama andmeid ja kat­se­ta­mise eesmärgil tegema mõned lihtsad päringud.

Määra skeem

And­me­ba­asi tabelite mää­rat­le­mise peamine SQL-käsk on CREATE TABLE. Selle käsu abil saab luua tabeli, määrata sellele nime ja mää­rat­leda veergude omadused. Samal ajal mää­rat­le­takse and­me­tüü­bid ja vajaduse korral ka sal­ves­ta­ta­vate väärtuste piirangud:

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

Lisa and­me­ko­gu­mid

Nüüd loome mõned test­kir­jed. Kasutame väljade täit­miseks SQL-käsku INSERT INTO ning funkt­siooni VALUES:

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ääratle päringud

Tabel „Products“ seisundi kont­rol­li­miseks kirjutame lihtsa päringu. Kasutame käsku SELECT FROM ja kuvame kogu tabeli:

SELECT * FROM Products;

Nüüd kirjutame veidi kee­ru­li­sema päringu, mis arvutab laos olevate toodete ko­gu­väär­tuse:

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

Lisada täien­da­vad tabelid

Järg­miseks loome ülejäänud vajalikud tabelid. Jälgime samu samme, mida ka­su­ta­sime tabeli „Products“ puhul. Esmalt loome tabeli „Customers“:

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

Seejärel sisestame kahe näi­diskliendi andmed:

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

Et kont­rol­lida, kas see töötas, kuvame klien­di­ta­beli:

SELECT * FROM Customers;

Järgmine samm on luua tabel „Tel­li­mu­sed“:

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

Nüüd sisestame kolm näi­dis­os­tu­tel­li­must. Esimesele väär­tu­sele määrame ID-numbri kui põhivõtme. Teine väärtus on ole­mas­ole­vate klientide ID-numbrid, mis toimivad võõr­võt­metena. Seejärel sal­ves­tame tellimuse kuupäeva:

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');

Selle kat­se­ta­miseks anname järgmised käsud:

SELECT * FROM Orders;

Lõpuks vajame tabelit, mis sisaldab tel­li­mu­ses olevaid tooteid koos nende kogustega. Tegemist on m:n-suhtes, sest üks tellimus võib sisaldada mitut toodet ja üks toode võib esineda mitmes tel­li­mu­ses. Mää­rat­leme tabeli, mis sisaldab tel­li­muste ja toodete ID-sid võõr­võt­metena:

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

Nüüd sisestame mõned tellitud tooted. Valime tel­li­muste ja toodete ID-d nii, et üks tellimus sisaldaks kahte toodet ja teine ainult ühte toodet:

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

Selle kont­rol­li­miseks väl­jas­tame tellitud tooted:

SELECT * FROM OrderItems;

Kirjuta keerukaid päringuid

Kui olete kõik seni näidatud koo­di­l­õi­gud läbi viinud, peaksite suutma aru saada meie tes­tand­me­ba­asi struk­tuu­rist. Nüüd läheme edasi kee­ru­li­se­mate päringute juurde, mis näitavad SQL-i võimsust. Esmalt kirjutame päringu, mis ühendab mitmes tabelis paiknevad andmed. Kasutame SQL-käsku JOIN, et ühendada tabelid, mis si­sal­da­vad klien­di­and­meid ja tellimusi. Selle käigus nimetame veerud ja seame JOIN-tin­gi­mu­seks kliendi ID-de kok­ku­lan­ge­vuse. Pange tähele, et kasutame kva­li­fit­see­ri­tud iden­ti­fi­kaa­to­reid kahe tabeli veergude eris­ta­miseks:

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;

Nüüd kasutame veel ühte JOIN-käsku, et arvutada tellitud toodete ko­gu­mak­su­mus:

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;