MySQL-i põhilised andmebaasi päringud (CRUD).

1. CRUD

Põhitoimingud, mida saab teha andmetega…
C – create – luua/lisa andmed
R – read – kuva andmed
U – update – andmete muutmine
D – delete – andmete kustutamine

Sümbol * tähendab kõik veergud, kui kasutame tema algusel (SELECT, UPDATE, INSERT, DELETE pärast). Edaspidi see tähendab korrutamine. Matemaatika sümbolid + – * /

1.1 Andmete lisamine

INSERT INTO	Lisa andmed. Pärast seda kirjutame veergud, kuhu paneme andmed
VALUES	pärast seda sõna kirjutame andmed, mida soovime lisada. Järjekord peab olema sama kui veergude järjestus, mille määrasite pärast INSERT INTO

näidis:

INSERT INTO users (id, first_name, last_name, birthday)
VALUES (6, ‘Martin’, ‘Ljuter’, ‘2001-04-14’);

1.2 Kuva andmed

SELECT	näitab andmed
FROM	millest tabelist
*	näitab kõik veergud. Kirjutage * või vajaliku veegu nime.
WHERE	loogiline KUS. Kirjutame enne tingimusi. Nt. …WHERE age > 18; Kasutame koos SELECT, UPDATE, DELETE-ga.

skeem:

SELECT * FROM table_name;	näitab kõiki tabeli veerge
SELECT column1, column2 FROM table_name;	näitab ainult neid veerge, mis on päringus.

näidised:

SELECT * FROM users; Select näitab tulemusi "users" tabelist(kõik veergud tabelist). 

SELECT last_name, first_name, birthday
FROM users; näitab veergude (last_name, first_name ja birthday) sisu, tabelist users.

SELECT-i funktsioonid:

COUNT()	näitab veerus olevate ridade arvu
AVG()	keskmine väärtus
SUM()	näitab summat

näidised:

SELECT COUNT(age)FROM users; Näitab ridade koguarvu, mitte loendit ( nt 5). SELECT AVG(age)FROM users; näitab keskmine vanus, mitte loendit.

1.3 Muutused

UPDATE

muutus / vaheta

UPDATE products //vaheta tabel "products" SET name = 'taldrik' //paneme "name" veergusse "taldrik" WHERE name = 'talbrik'; // seale, kus “name” veergus on olemas “talbrik”

 UPDATE products
SET count = 10, //paneme kogus 10
price = price + 5 //paneme “price” veerguse uus hind (praegune hind +5)
WHERE id=4; //sinna, kus veeru id väärtus on 4
teeme ID 4 jaoks kogus 10 ja paneme uus hind (olev hind + 5)

1.4.1 Andmete kustutamine

DELETE	tabeli sisu kustutamine.
IS NULL	NULL või tühi
IS NOT NULL	tabeli lahtris on andmeid

DELETE FROM products WHERE count = 0
DELETE FROM users WHERE last_name IS NULL; Veel on olemas IS NOT NULL (mitte tühi)
DELETE FROM users

1.4.2 Tabeli kustutamine

DROP TABLE users

2. Veel mõned andmetoimingud ja -tingimused

Te oskate WHERE, IS, IS NOT, NULL, vaatame veel

2.1 Andmete filtreerimine

AND	loogiline JA
OR	loogiline VÕI
NOT	loogiline MITTE (EI)

skeem:

SELECT veerg1, veerg2 FROM tabeli_nimi WHERE tingimus1 AND tingimus2 OR tingimus3;	näitab veerge, kus on tingimus1 ja tingimus2 saamal ajal, või on olemas tingimus3.
SELECT veerg1, veerg2 FROM tabeli_nimi WHERE NOT tingimus1;	näitab ainult neid veerge, mis on päringus.

näidised:

SELECT last_name, first_name, birthday
FROM users
WHERE age > 18; Kõik üle 18-aastased

 SELECT last_name, first_name, birthday
FROM users
WHERE age >= 18
AND sex = 'm'; Mehed ja kõik 18 ning üle 18-aastased

 SELECT last_name, first_name, birthday
FROM users
WHERE age > 18
OR sex = 'm'; Üle 18-aastased naised ja kõik mehed

SELECT last_name, first_name, birthday FROM users WHERE NOT age > 18; Vanus kuni 18 aastat.  

lühendid:

lühend IN-ga
Tavaline
SELECT * FROM products
WHERE country = ‘EE’ OR country = ‘FI’ OR country = ‘LT’;

Lühend
SELECT * FROM products
WHERE country IN (‘EE’, ‘FI’, ‘LT’);

lühend BETWEEN-ga
Tavaline
SELECT * FROM products
WHERE price >= 10
AND price <=20;

Lühend
SELECT * FROM products
WHERE price BETWEEN 10 AND 20; kaup alates 10 kuni 20 eurot kaasa arvatud.

2.2 Sorteerimine

ORDER BY	sorteerimine
ASC	tavaline sorteerimine (A-Z), pole kohustlik kirjutada päringus.
DESC	DESC tähendab, et sorteerimine lõpust alguseni Z-A

näidised:

SELECT last_name, first_name
FROM users
ORDER BY last_name; sordi perekonnanime (last_name) järgi.

 SELECT last_name, first_name, birthday
FROM users
ORDER BY last_name, first_name; sortimine perekonnanime ja seejärel eesnime järgi.

 SELECT last_name, first_name
FROM users
ORDER BY birthday;

 SELECT last_name, first_name
FROM users
WHERE age >= 18
ORDER BY birthday DESC;

 SELECT last_name, first_name, birthday
FROM users
WHERE age >= 18
ORDER BY last_name, birthday DESC;

UPDATE products //vaheta tabel “products”
SET price = price * 0.8 //paneme “price” veerguse uus hind (praegune hind korrutatakse 0,8-ga)
ORDER BY price DESC LIMIT 1; //
korrutame hinna 0,8-ga, siis tõuseme kõige kallima ja kõige kallima 1. piirmäära. Saame allahindlust kõige kallimale tootele. Siin * tähendab korrutamine.

2.3 Piirangud

LIMIT

piirang
LIMIT 5 – kuvatakse ainult tabeli esimesed 5 rida
LIMIT 20,5 – jätab tabeli esimesed 20 rida vahele ja näitab järgmist 5.

näidised:

SELECT *
FROM products
ORDER BY price DESC 
LIMIT 4; kuvab viimast 4 kõige kallimat toodet

 SELECT *
FROM products
WHERE count > 0
ORDER BY price DESC
LIMIT 6; kuvab viimased 6 ja laos olev kogus on suurem kui null.

 SELECT *
FROM products
WHERE count > 0
ORDER BY price DESC
LIMIT 6,3; jätab esimese 6 vahele ja näitab järgmist 3.

HARJUTUSED

Laadige alla, ja tehke “IMPORT” teie andmebaasisse. https://mega.nz/file/GxRyBDCJ#rnnFVmXRm0rI4du83bsfTJypN2LkOT-s3qCSOLSgPp0

Teie andmebaasis on kolm uut tabelit, kuid neis on natuke rohkem andmeid. =)

Harjutus #1.

Tabel City.
Mitu kirjet on järgmiste parameetritega: linnaelanike arv (Population) 1500–200000 ja nende riigikood (CountryCode) on ‘KIR’

Harjutus #2

Tabel country
Kui paljudel riikidel on olemas iseseisvusaasta (IndepYear)?

Harjutus #3

Tabel countrylanguage
Muidugi, Eesti põhikeel on eesti keel. Teisel kohal on vene keel. Leidke, milline keel on kolmandal kohal (andmebaasist, mitte Googlist) Teile aitavad CountryCode ja percentage veerud.

Harjutus #4

Tabel City
Millised linnad Eestist on olemas tabelis?.

Harjutus #5

Tabel Country
Mitu riigides on Republic (GovernmentForm)

Harjutus #6

Tabel countrylanguage
Kui paljudel riikidel kasutavad Dutch

Harjutus #7

Tabel City

CountryCode NLD, ja elaniku arv rohkem kui 500000. Nimetage need linnad.

Harjutus #8

Tabel Country
IndepYear riigis Sudan

Harjutus #9

Tabel CountryLanguage
Millised keeled kasutavad riigis rigikoodiga MTQ

Vastused

SELECT COUNT(ID)
FROM city
WHERE Population between 1500 and 200000  
AND CountryCode = 'KIR';


SELECT COUNT(Code)
FROM country
WHERE IndepYear IS NOT NULL;

SELECT * FROM countrylanguage
WHERE CountryCode = 'EST'
ORDER BY percentage DESC LIMIT 2,1;


City table
SELECT Name FROM city WHERE CountryCode = 'est';

Tallinn, Tartu


Contry table
SELECT COUNT(Name) FROM `country` WHERE GovernmentForm = 'Republic';

122


CountryLanguage
SELECT COUNT(Language) FROM `countrylanguage` WHERE Language = 'Dutch';

5

Table City
Kui palju linnu, kus (veerg population) rohkem või 1000000? 
SELECT COUNT(Name) FROM `city` WHERE Population >= 1000000;

238

AND tähendab seda, et korraga peavad mõlemad tingimused kehtima

Tabelist city.
Mitu (nende arv) (veerg population) rohkem või 2000000 ja need linnad, kus vähem kui 100000 inimesi.
SELECT COUNT(Name) FROM `city` 
WHERE Population >= 2000000 
OR Population < 100000;

609


Tabel City

CountryCode NLD, ja elaniku arv rohkem kui 500000. Nimetage need linnad.

SELECT Name FROM `city` WHERE CountryCode = 'NLD' AND Population > 500000;

Amsterdam, Rotterdam


Tabel Country
IndepYear riigis Sudan
SELECT IndepYear FROM country WHERE Name = 'Sudan';

1956

Tabel CountryLanguage
Millised keeled kasutavad riigis rigikoodira MTQ

SELECT Language FROM `countrylanguage` WHERE CountryCode = 'MTQ';

	
Creole French, French