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

U ovom članku ćemo pokazati kako kreirati bazu podataka (također poznatu kao šema), tabele (sa tipovima podataka) i objasniti kako se izvodi Jezik za manipulaciju podacima (DML ) operacije sa podacima na MySQL/MariaDB serveru.

Pretpostavlja se da ste prethodno 1) instalirali potrebne pakete na vaš Linux sistem i 2) izvršili mysql_secure_installation kako biste poboljšali sigurnost poslužitelja baze podataka . Ako ne, slijedite donje upute za instalaciju MySQL/MariaDB servera.

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

Radi kratkoće, pozivaćemo se isključivo na MariaDB u ovom članku, ali koncepti i komande koji su ovde navedeni važe i za MySQL.

Kreiranje baza podataka, tabela i ovlaštenih korisnika

Kao što znate, baza podataka se može jednostavno definirati kao organizirana zbirka informacija. Konkretno, MariaDB je sistem za upravljanje relacionim bazama podataka (RDBMS) i koristi jezik upita strukture za obavljanje operacija na bazama podataka. Osim toga, imajte na umu da MariaDB koristi pojmove baza podataka i shema naizmjenično.

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

Kreiranje nove baze podataka

Da kreirate novu bazu podataka pod nazivom BooksDB, unesite MariaDB prompt sa sljedećom naredbom (od vas će biti zatraženo da unesete lozinku za root MariaDB korisnika):

[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)]>

Kada je baza podataka kreirana, potrebno je da kreiramo najmanje dve tabele na njoj. Ali prvo istražimo koncept tipova podataka.

Predstavljamo MariaDB tipove podataka

Kao što smo ranije objasnili, tabele su objekti baze podataka u kojima ćemo čuvati trajne informacije. Svaka tabela se sastoji od dva ili više polja (takođe poznatih kao kolone) datog tipa podataka (tip informacija) koje takvo polje može pohraniti.

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

numerički :

BOOLEAN smatra 0 netačnim, a sve druge vrijednosti istinitim.
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. Opseg UNSIGNED je od 0 do 65535.
INT, ako se koristi sa UNSIGNED, pokriva raspon od 0 do 4294967295, i -2147483648 do 2147483647 u suprotnom.

Napomena: U TINYINT, SMALLINT i INT, pretpostavlja se zadano POTPISAN.

DOUBLE(M, D), gdje je M ukupan broj cifara, a D broj cifara nakon decimalnog zareza, predstavlja broj s pomičnim zarezom dvostruke preciznosti. Ako je specificirano UNSIGNED, negativne vrijednosti nisu dozvoljene.

String :

VARCHAR(M) predstavlja niz varijabilne dužine gdje je M maksimalna dozvoljena dužina stupca u bajtovima (65,535 u teoriji). U većini slučajeva, broj bajtova je identičan broju znakova, osim nekih znakova koji mogu zauzeti čak 3 bajta. Na primjer, špansko slovo ñ predstavlja jedan znak, ali zauzima 2 bajta.
TEXT(M) predstavlja kolonu maksimalne dužine od 65.535 znakova. Međutim, kao što se dešava sa VARCHAR(M), stvarna maksimalna dužina se smanjuje ako se pohranjuju višebajtni znakovi. Ako je naveden M, kolona se kreira kao najmanji tip koji može pohraniti toliki broj znakova.
MEDIUMTEXT(M) i LONGTEXT(M) su slični TEXT(M), samo što su maksimalno dozvoljene dužine 16,777,215 i 4,294,967,295 znakova, respektivno.

Datum i vrijeme:

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

Nakon što pregledate ove tipove podataka, bit ćete u boljoj poziciji da odredite koji tip podataka trebate dodijeliti datoj koloni u tabeli.

Na primjer, ime osobe može se lako uklopiti u VARCHAR(50), dok će za blog post trebati tip TEXT (odaberite M kao prema vašim specifičnim potrebama).

Kreiranje tabela sa primarnim i stranim ključevima

Prije nego što zaronimo u kreiranje tabela, postoje dva osnovna koncepta o relacijskim bazama podataka koje trebamo pregledati: primarni i strani ključevi.

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

Za ilustraciju, koristimo BookstoreDB i kreiramo dvije tabele pod nazivom AuthorsTBL i BooksTBL kako slijedi. Ograničenje NOT NULL označava da povezano polje zahtijeva vrijednost različitu od NULL.

Također, AUTO_INCREMENT se koristi za povećanje vrijednosti INT kolona primarnog ključa za jedan kada se novi zapis ubaci u tabelu.

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 redova

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

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

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

Nakon toga ćemo odabrati sve zapise iz AutorsTBL. Zapamtite da će nam biti potreban AuthorID za svaki zapis da kreiramo INSERT upit za BooksTBL.

Ako želite da dohvatite jedan po jedan zapis, možete koristiti WHERE klauzulu da naznačite uslov koji red mora ispuniti da bi se vratio. 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]>

Sada kreirajmo upit INSERT za BooksTBL, koristeći odgovarajući AuthorID koji odgovara autoru svake knjige. Vrijednost 1 u BookIsAvailable označava da je knjiga na zalihama, 0 u suprotnom:

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 ćemo uraditi SELECT da vidimo zapise u BooksTBL. Onda hajde da AŽURIRAJMO cijenu “The Alchemist” od Paula Coelha i IZABIRAJTE ponovo taj određeni zapis.

Obratite pažnju na to kako polje BookLastUpdated sada prikazuje drugačiju vrijednost. Kao što smo ranije objasnili, polje TIMESTAMP pokazuje vrijednost kada je zapis umetnut ili posljednji 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 nećemo učiniti ovdje, također možete 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 iu slučaju UPDATE, dobra je ideja prvo napraviti SELECT kako biste vidjeli zapis(e) na koje bi moglo utjecati IZBRIŠI.

Također, ne zaboravite dodati klauzulu WHERE i uvjet (ID knjige=6) da odaberete određeni zapis koji želite ukloniti. U suprotnom, rizikujete da izbrišete sve redove u tabeli!

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

Ovo će zahtijevati JOIN između AuthorsTBL i BooksTBL na zajedničkom polju koje dijele obje tabele (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ćava da spojimo više string izraza odvojenih zarezima. Također ćete primijetiti da smo odabrali pseudonim Description za predstavljanje skupa rezultata konkatenacije.

Rezultat gornjeg upita prikazan je na donjoj slici:

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)

Kreirajte korisnika za pristup bazi podataka BookstoreDB

Korištenje root za izvođenje svih DML operacija u bazi podataka je loša ideja. Da bismo to izbjegli, možemo kreirati 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ćenja cijele baze podataka u slučaju da jedan nalog postane ugrožen.

Dodatni MySQL savjeti

Da počistite prompt MariaDB, unesite sljedeću naredbu i pritisnite Enter:

MariaDB [BookstoreDB]> \! clear

Da biste provjerili konfiguraciju date tablice, učinite:

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)

Brza inspekcija otkriva da polje BookIsAvailable prihvata vrijednosti NULL. Pošto to ne želimo dozvoliti, IZMIJENIT tabelu na sljedeći način:

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

(Slobodno prikažite kolone ponovo – istaknuto DA na gornjoj slici bi sada trebalo biti NE).

Konačno, da vidite sve baze podataka na vašem serveru, uradite:

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 promptu kao bookstoreuser (imajte na umu kako ovaj račun ne može "vidjeti" nijednu bazu podataka osim BookstoreDB i information_schema (dostupno za sve korisnike):

Sažetak

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

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

Ako imate bilo kakvih pitanja o ovom članku, ne ustručavajte se javiti nam! Slobodno koristite formular za komentare ispod da nas kontaktirate.