Naučite MySQL/MariaDB za početnike - 1. dio

U ovom ćemo članku pokazati kako izraditi bazu podataka (također poznatu kao shema), tablice (s vrstama podataka) i objasniti kako izvesti Data Manipulation Language (DML ) operacije s podacima na MySQL/MariaDB poslužitelju.

Pretpostavlja se da ste prethodno 1) instalirali potrebne pakete na svoj Linux sustav i 2) izvršili mysql_secure_installation kako biste poboljšali sigurnost poslužitelja baze podataka . Ako nije, slijedite upute u nastavku za instalaciju MySQL/MariaDB poslužitelja.

Instalirajte najnoviju MySQL bazu podataka u Linux sustavima
Instalirajte najnoviju MariaDB bazu podataka u Linux sustavima

Ukratko radi, u ovom ćemo se članku pozivati isključivo na MariaDB, ali ovdje navedeni koncepti i naredbe odnose se i na MySQL.

Stvaranje baza podataka, tablica i ovlaštenih korisnika

Kao što znate, baza podataka može se jednostavno definirati kao organizirana zbirka informacija. Konkretno, MariaDB je sustav za upravljanje relacijskim bazama podataka (RDBMS) i koristi Structure Query Language za izvođenje operacija na bazama podataka. Osim toga, imajte na umu da MariaDB koristi termine baza podataka i shema naizmjenično.

Za pohranjivanje trajnih informacija u bazi podataka koristit ćemo tablice koje pohranjuju retke podataka. Često će dvije ili više tablica na neki način biti povezane jedna s drugom. To je dio organizacije koji karakterizira korištenje relacijskih baza podataka.

Stvaranje nove baze podataka

Za izradu nove baze podataka pod nazivom BooksDB, unesite MariaDB prompt sa sljedećom naredbom (od vas će se tražiti da unesete lozinku za root korisnika MariaDB):

[root@TecMint ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.14-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE BookstoreDB;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]>

Nakon što je baza podataka izrađena, potrebno je izraditi najmanje dvije tablice na njoj. Ali prvo istražimo koncept tipova podataka.

Predstavljamo MariaDB vrste podataka

Kao što smo ranije objasnili, tablice su objekti baze podataka u kojima ćemo čuvati trajne informacije. Svaka se tablica sastoji od dva ili više polja (također poznatih kao stupci) određenog tipa podataka (vrsta informacija) koje takvo polje može pohraniti.

Najčešći tipovi podataka u MariaDB-u su sljedeći (potpuni popis možete pogledati u službenoj online dokumentaciji MariaDB-a):

Numeričko:

BOOLEAN smatra 0 lažnom, a sve druge vrijednosti istinitima.
TINYINT, ako se koristi sa SIGNED, pokriva raspon od -128 do 127, dok je UNSIGNED raspon od 0 do 255.
SMALLINT, ako se koristi sa SIGNED, pokriva raspon od -32768 do 32767. Raspon UNSIGNED je od 0 do 65535.
INT, ako se koristi s UNSIGNED, pokriva raspon od 0 do 4294967295, a inače -2147483648 do 2147483647.

Napomena: U TINYINT, SMALLINT i INT pretpostavlja se zadani SIGNED.

DOUBLE(M, D), gdje je M ukupan broj znamenki, a D broj znamenki iza decimalne točke, predstavlja broj s pomičnim zarezom dvostruke preciznosti. Ako je navedeno UNSIGNED, negativne vrijednosti nisu dopuštene.

Niz:

VARCHAR(M) predstavlja niz promjenjive duljine gdje je M najveća dopuštena duljina stupca u bajtovima (65,535 u teoriji). U većini slučajeva, broj bajtova je identičan broju znakova, osim za neke znakove koji mogu zauzimati čak 3 bajta. Na primjer, španjolsko slovo ñ predstavlja jedan znak, ali zauzima 2 bajta.
TEKST(M) predstavlja stupac s maksimalnom dužinom od 65.535 znakova. Međutim, kao što se događa s VARCHAR(M), stvarna najveća duljina je smanjena ako su pohranjeni višebajtni znakovi. Ako je navedeno M, stupac se stvara kao najmanji tip koji može pohraniti takav broj znakova.
MEDIUMTEXT(M) i LONGTEXT(M) slični su TEXT(M), samo što su maksimalne dopuštene duljine 16.777.215 i 4.294.967.295 znakova, odnosno.

Datum i vrijeme:

DATUM predstavlja datum u formatu GGGG-MM-DD.
VRIJEME predstavlja vrijeme u formatu HH:MM:SS.sss (sat, minute, sekunde i milisekunde).
DATUM je kombinacija DATUM i VRIJEME u formatu GGGG-MM-DD HH:MM:SS.
TIMESTAMP koristi se za definiranje trenutka kada je red dodan ili ažuriran.

Nakon što ste pregledali ove vrste podataka, bit ćete u boljoj poziciji da odredite koju vrstu podataka trebate dodijeliti određenom stupcu u tablici.

Na primjer, ime osobe može lako stati u VARCHAR(50), dok će post na blogu trebati vrstu TEKST (odaberite M kao prema vašim specifičnim potrebama).

Stvaranje tablica s primarnim i stranim ključevima

Prije nego što zaronimo u stvaranje tablica, postoje dva temeljna koncepta o relacijskim bazama podataka koje moramo pregledati: primarni i strani ključevi.

Primarni ključ sadrži vrijednost koja jedinstveno identificira svaki redak ili zapis u tablici. S druge strane, strani ključ koristi se za stvaranje veze između podataka u dvije tablice i za kontrolu podataka koji se mogu pohraniti u tablici u kojoj se nalazi strani ključ. I primarni i strani ključevi općenito su INT.

Za ilustraciju, upotrijebimo BookstoreDB i stvorimo dvije tablice pod nazivom AuthorsTBL i BooksTBL kako slijedi. Ograničenje NOT NULL označava da povezano polje zahtijeva vrijednost koja nije NULL.

Također, AUTO_INCREMENT se koristi za povećanje za jedan vrijednosti stupaca primarnog ključa INT kada se novi zapis umetne u tablicu.

MariaDB [(none)]> USE BookstoreDB;

MariaDB [(none)]> CREATE TABLE AuthorsTBL (
AuthorID INT NOT NULL AUTO_INCREMENT,
AuthorName VARCHAR(100),
PRIMARY KEY(AuthorID)
);

MariaDB [(none)]> CREATE TABLE BooksTBL (
BookID INT NOT NULL AUTO_INCREMENT,
BookName VARCHAR(100) NOT NULL,
AuthorID INT NOT NULL,
BookPrice DECIMAL(6,2) NOT NULL,
BookLastUpdated TIMESTAMP,
BookIsAvailable BOOLEAN,
PRIMARY KEY(BookID),
FOREIGN KEY (AuthorID) REFERENCES AuthorsTBL(AuthorID)
);

MariaDB [(none)]> USE BookstoreDB;
Database changed
MariaDB [BookstoreDB]> CREATE TABLE AuthorsTBL (
    -> AuthorID INT NOT NULL AUTO_INCREMENT,
    -> AuthorName VARCHAR(100),
    -> PRIMARY KEY(AuthorID)
    -> );
Query OK, 0 rows affected (0.05 sec)

MariaDB [BookstoreDB]> CREATE TABLE BooksTBL (
    -> BookID INT NOT NULL AUTO_INCREMENT,
    -> BookName VARCHAR(100) NOT NULL,
    -> AuthorID INT NOT NULL,
    -> BookPrice DECIMAL(6,2) NOT NULL,
    -> BookLastUpdated TIMESTAMP,
    -> BookIsAvailable BOOLEAN,
    -> PRIMARY KEY(BookID),
    -> FOREIGN KEY (AuthorID) REFERENCES AuthorsTBL(AuthorID)
    -> );
Query OK, 0 rows affected (0.05 sec)

MariaDB [BookstoreDB]>

Sada možemo nastaviti i početi umetati zapise u AuthorsTBL i BooksTBL.

Odabir, umetanje, ažuriranje i brisanje redaka

Prvo ćemo popuniti tablicu AuthorsTBL. Zašto? Zato što moramo imati vrijednosti za AuthorID prije umetanja zapisa u BooksTBL.

Izvršite sljedeći upit iz vašeg MariaDB odzivnika:

MariaDB [BookstoreDB]> INSERT INTO AuthorsTBL (AuthorName) VALUES ('Agatha Christie'), ('Stephen King'), ('Paulo Coelho');

Nakon toga ćemo odabrati sve zapise iz AuthorsTBL. Upamtite da ćemo trebati AuthorID za svaki zapis kako bismo stvorili upit INSERT za BooksTBL.

Ako želite dohvatiti jedan po jedan zapis, možete upotrijebiti klauzulu WHERE da označite uvjet koji redak mora ispuniti da bi bio vraćen. Na primjer,

MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';

Alternativno, možete odabrati sve zapise istovremeno:

MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL;

MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';
+----------+-----------------+
| AuthorID | AuthorName      |
+----------+-----------------+
|        1 | Agatha Christie |
+----------+-----------------+
1 row in set (0.00 sec)

MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL;
+----------+-----------------+
| AuthorID | AuthorName      |
+----------+-----------------+
|        1 | Agatha Christie |
|        2 | Stephen King    |
|        3 | Paulo Coelho    |
+----------+-----------------+
3 rows in set (0.00 sec)

MariaDB [BookstoreDB]>

Kreirajmo sada upit INSERT za BooksTBL, koristeći odgovarajući AuthorID za podudaranje s autorom svake knjige. Vrijednost 1 u BookIsAvailable označava da je knjiga na zalihi, 0 inače:

MariaDB [BookstoreDB]> INSERT INTO BooksTBL (BookName, AuthorID, BookPrice, BookIsAvailable)
VALUES ('And Then There Were None', 1, 14.95, 1),
('The Man in the Brown Suit', 1, 23.99, 1),
('The Stand', 2, 35.99, 1),
('Pet Sematary', 2, 17.95, 0),
('The Green Mile', 2, 29.99, 1),
('The Alchemist', 3, 25, 1),
('By the River Piedra I Sat Down and Wept', 3, 18.95, 0);

MariaDB [BookstoreDB]> INSERT INTO BooksTBL (BookName, AuthorID, BookPrice, BookIsAvailable)
    -> VALUES ('And Then There Were None', 1, 14.95, 1),
    -> ('The Man in the Brown Suit', 1, 23.99, 1),
    -> ('The Stand', 2, 35.99, 1),
    -> ('Pet Sematary', 2, 17.95, 0),
    -> ('The Green Mile', 2, 29.99, 1),
    -> ('The Alchemist', 3, 25, 1),
    -> ('By the River Piedra I Sat Down and Wept', 3, 18.95, 0);
Query OK, 7 rows affected (0.03 sec)
Records: 7  Duplicates: 0  Warnings: 0

U ovom trenutku napravit ćemo SELECT da bismo vidjeli zapise u BooksTBL. Zatim AŽURIRAJMO cijenu filma “Alkemičar” Paula Coelha i ponovo ODABERIMO taj određeni zapis.

Imajte na umu kako polje BookLastUpdated sada prikazuje drugu vrijednost. Kao što smo ranije objasnili, polje TIMESTAMP prikazuje vrijednost kada je zapis umetnut ili zadnji put izmijenjen.

MariaDB [BookstoreDB]> SELECT * FROM BooksTBL;
MariaDB [BookstoreDB]> UPDATE BooksTBL SET BookPrice=22.75 WHERE BookID=6;
MariaDB [BookstoreDB]> SELECT * FROM BooksTBL WHERE BookID=6;

MariaDB [BookstoreDB]> SELECT * FROM BooksTBL;
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
| BookID | BookName                                | AuthorID | BookPrice | BookLastUpdated     | BookIsAvailable |
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
|      1 | And Then There Were None                |        1 |     14.95 | 2016-10-01 23:31:41 |               1 |
|      2 | The Man in the Brown Suit               |        1 |     23.99 | 2016-10-01 23:31:41 |               1 |
|      3 | The Stand                               |        2 |     35.99 | 2016-10-01 23:31:41 |               1 |
|      4 | Pet Sematary                            |        2 |     17.95 | 2016-10-01 23:31:41 |               0 |
|      5 | The Green Mile                          |        2 |     29.99 | 2016-10-01 23:31:41 |               1 |
|      6 | The Alchemist                           |        3 |     25.00 | 2016-10-01 23:31:41 |               1 |
|      7 | By the River Piedra I Sat Down and Wept |        3 |     18.95 | 2016-10-01 23:31:41 |               0 |
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
7 rows in set (0.00 sec)

MariaDB [BookstoreDB]> UPDATE BooksTBL SET BookPrice=22.75 WHERE BookID=6;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [BookstoreDB]> SELECT * FROM BooksTBL WHERE BookID=6;
+--------+---------------+----------+-----------+---------------------+-----------------+
| BookID | BookName      | AuthorID | BookPrice | BookLastUpdated     | BookIsAvailable |
+--------+---------------+----------+-----------+---------------------+-----------------+
|      6 | The Alchemist |        3 |     22.75 | 2016-10-01 23:35:00 |               1 |
+--------+---------------+----------+-----------+---------------------+-----------------+
1 row in set (0.00 sec)

MariaDB [BookstoreDB]>

Iako to ovdje nećemo učiniti, možete i izbrisati zapis ako se više ne koristi. Na primjer, pretpostavimo da želimo izbrisati “The Alchemist ” iz BooksTBL.

Da bismo to učinili, koristit ćemo naredbu DELETE na sljedeći način:

MariaDB [BookstoreDB]> DELETE FROM BooksTBL WHERE BookID=6;

Kao i u slučaju UPDATE, dobra je ideja prvo napraviti SELECT kako biste vidjeli zapis(e) na koje potencijalno može utjecati IZBRIŠI.

Također, ne zaboravite dodati klauzulu WHERE i uvjet (ID knjige=6) za odabir određenog zapisa za uklanjanje. U suprotnom, riskirate brisanje svih redaka u tablici!

Ako želite spojiti dva (ili više) polja, možete upotrijebiti naredbu CONCAT. Na primjer, recimo da želimo vratiti skup rezultata koji se sastoji od jednog polja s nazivom knjige i autora u obliku “Alkemičar (Paulo Coelho)” i drugog stupca s cijenom.

Ovo će zahtijevati JOIN između AuthorsTBL i BooksTBL na zajedničkom polju koje dijele obje tablice (AuthorID):

MariaDB [BookstoreDB]> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;

Kao što vidimo, CONCAT nam omogućuje spajanje više izraza niza odvojenih zarezima. Također ćete primijetiti da smo odabrali pseudonim Opis za predstavljanje skupa rezultata ulančavanja.

Rezultat gornjeg upita prikazan je na slici ispod:

MariaDB [BookstoreDB]> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;
+--------------------------------------------------------+-----------+
| Description                                            | BookPrice |
+--------------------------------------------------------+-----------+
| And Then There Were None (Agatha Christie)             |     14.95 |
| The Man in the Brown Suit (Agatha Christie)            |     23.99 |
| The Stand (Stephen King)                               |     35.99 |
| Pet Sematary (Stephen King)                            |     17.95 |
| The Green Mile (Stephen King)                          |     29.99 |
| The Alchemist (Paulo Coelho)                           |     25.00 |
| By the River Piedra I Sat Down and Wept (Paulo Coelho) |     18.95 |
+--------------------------------------------------------+-----------+
7 rows in set (0.00 sec)

Stvorite korisnika za pristup BookstoreDB bazi podataka

Korištenje root za izvođenje svih DML operacija u bazi podataka je loša ideja. Da bismo to izbjegli, možemo stvoriti novi MariaDB korisnički račun (nazvat ćemo ga bookstoreuser) i dodijeliti sva potrebna dopuštenja za BookstoreDB:

MariaDB [BookstoreDB]> CREATE USER bookstoreuser@localhost IDENTIFIED BY 'YourPasswordHere';
MariaDB [BookstoreDB]> GRANT ALL PRIVILEGES ON  BookstoreDB.* to bookstoreuser@localhost;
MariaDB [BookstoreDB]> FLUSH PRIVILEGES;

MariaDB [BookstoreDB]> CREATE USER bookstoreuser@localhost IDENTIFIED BY 'tecmint';
Query OK, 0 rows affected (0.00 sec)

MariaDB [BookstoreDB]> GRANT ALL PRIVILEGES ON  BookstoreDB.* to bookstoreuser@localhost;
Query OK, 0 rows affected (0.00 sec)

MariaDB [BookstoreDB]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Posjedovanje namjenskog, zasebnog korisnika za svaku bazu podataka spriječit će oštećenje cijele baze podataka ako jedan račun postane ugrožen.

Dodatni MySQL savjeti

Da biste izbrisali upit MariaDB, upišite sljedeću naredbu i pritisnite Enter:

MariaDB [BookstoreDB]> \! clear

Da biste provjerili konfiguraciju dane tablice, učinite sljedeće:

MariaDB [BookstoreDB]> SELECT COLUMNS IN [TABLE NAME HERE ];

Na primjer,

MariaDB [BookstoreDB]> SHOW COLUMNS IN BooksTBL;

MariaDB [BookstoreDB]> SHOW COLUMNS IN BooksTBL;
+-----------------+--------------+------+-----+-------------------+-----------------------------+
| Field           | Type         | Null | Key | Default           | Extra                       |
+-----------------+--------------+------+-----+-------------------+-----------------------------+
| BookID          | int(11)      | NO   | PRI | NULL              | auto_increment              |
| BookName        | varchar(100) | NO   |     | NULL              |                             |
| AuthorID        | int(11)      | NO   | MUL | NULL              |                             |
| BookPrice       | decimal(6,2) | NO   |     | NULL              |                             |
| BookLastUpdated | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| BookIsAvailable | tinyint(1)   | YES  |     | NULL              |                             |
+-----------------+--------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.02 sec)

Brzi pregled otkriva da polje BookIsAvailable dopušta NULL vrijednosti. Budući da to ne želimo dopustiti, IZMIJENIMO tablicu na sljedeći način:

MariaDB [BookstoreDB]> ALTER TABLE BooksTBL MODIFY BookIsAvailable BOOLEAN NOT NULL;

(Slobodno ponovno pokažite stupce – istaknuto DA na gornjoj slici sada bi trebalo biti NE).

Konačno, da biste vidjeli sve baze podataka na svom poslužitelju, učinite sljedeće:

MariaDB [BookstoreDB]> SHOW DATABASES;
OR
MariaDB [BookstoreDB]> SHOW SCHEMAS;

[root@TecMint ~]# mysql -u bookstoreuser -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.1.14-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [BookstoreDB]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| BookstoreDB        |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

MariaDB [BookstoreDB]> SHOW SCHEMAS;
+--------------------+
| Database           |
+--------------------+
| BookstoreDB        |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

Sljedeća slika prikazuje rezultat gornje naredbe nakon pristupa MariaDB odzivniku kao bookstoreuser (imajte na umu da ovaj račun ne može “vidjeti” nijednu bazu podataka osim BookstoreDB i informacijska_shema (dostupna svim korisnicima):

Sažetak

U ovom smo članku objasnili kako pokrenuti DML operacije i kako stvoriti bazu podataka, tablice i namjenske korisnike na MariaDB bazi podataka. Osim toga, podijelili smo nekoliko savjeta koji bi vam mogli olakšati život kao administratora sustava/baze podataka.

MySQL administracija baze podataka Dio – 1
MySQL administracija baze podataka Dio – 2
Podešavanje i optimizacija performansi MySQL – 3. dio

Ako imate bilo kakvih pitanja o ovom članku, ne ustručavajte se javiti nam! Slobodno upotrijebite obrazac za komentare u nastavku da biste nas kontaktirali.