Jifunze MySQL/MariaDB kwa Wanaoanza - Sehemu ya 1


Katika makala haya tutaonyesha jinsi ya kuunda hifadhidata (inayojulikana pia kama schema), jedwali (zilizo na aina za data), na kuelezea jinsi ya kufanya shughuli za Lugha ya Udhibiti wa Data (DML) na data kwenye seva ya MySQL/MariaDB.

Inachukuliwa kuwa hapo awali 1) umesakinisha vifurushi muhimu kwenye mfumo wako wa Linux, na 2) umetekeleza mysql_secure_installation ili kuboresha usalama wa seva ya hifadhidata. Ikiwa sivyo, fuata miongozo hapa chini ili kusakinisha seva ya MySQL/MariaDB.

  1. Sakinisha Hifadhidata ya Hivi Punde ya MySQL katika Mifumo ya Linux
  2. Sakinisha Hifadhidata ya Hivi Punde ya MariaDB katika Mifumo ya Linux

Kwa ufupi, tutarejelea MariaDB pekee katika nakala hii yote, lakini dhana na amri zilizoainishwa hapa zinatumika kwa MySQL pia.

Kuunda Hifadhidata, Majedwali, na Watumiaji Walioidhinishwa

Kama unavyojua, hifadhidata inaweza kufafanuliwa kwa maneno rahisi kama mkusanyiko uliopangwa wa habari. Hasa, MariaDB ni mfumo wa usimamizi wa hifadhidata (RDBMS) na hutumia Lugha ya Maswali ya Muundo kutekeleza shughuli kwenye hifadhidata. Zaidi ya hayo, kumbuka kwamba MariaDB hutumia hifadhidata ya maneno na schema kwa kubadilishana.

Ili kuhifadhi taarifa zinazoendelea katika hifadhidata, tutatumia majedwali yanayohifadhi safu mlalo za data. Mara nyingi, meza mbili au zaidi zitahusiana kwa namna fulani. Hiyo ni sehemu ya shirika ambalo lina sifa ya matumizi ya hifadhidata za uhusiano.

Ili kuunda hifadhidata mpya iitwayo BooksDB, weka kidokezo cha MariaDB kwa amri ifuatayo (utaombwa kuweka nenosiri la mzizi wa mtumiaji wa MariaDB):

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

Mara tu hifadhidata imeundwa, tunahitaji kuunda angalau meza mbili juu yake. Lakini kwanza hebu tuchunguze dhana ya aina za data.

Tunakuletea Aina za data za MariaDB

Kama tulivyoelezea hapo awali, jedwali ni vitu vya hifadhidata ambapo tutaweka habari inayoendelea. Kila jedwali lina sehemu mbili au zaidi (zinazojulikana pia kama safu wima) za aina fulani ya data (aina ya maelezo) ambayo sehemu kama hiyo inaweza kuhifadhi.

Aina za data za kawaida katika MariaDB ni zifuatazo (unaweza kutazama orodha kamili katika hati rasmi ya mtandaoni ya MariaDB):

  1. BOOLEAN inachukulia 0 kuwa si kweli na thamani zingine zozote kuwa za kweli.
  2. TINYINT, ikiwa inatumiwa na SIGNED, inashughulikia masafa kutoka -128 hadi 127, ilhali masafa AMBAYO HAYAJASINIWA ni 0 hadi 255.
  3. SMALLINT, ikiwa inatumiwa na SIGNED, inashughulikia masafa kutoka -32768 hadi 32767. Masafa AMBAYO HAYAJASINIWA ni 0 hadi 65535.
  4. INT, ikiwa inatumiwa na HAIJASINIWA, inashughulikia masafa kutoka 0 hadi 4294967295, na -2147483648 hadi 2147483647 vinginevyo.

Kumbuka: Katika TINYINT, SMALLINT, na INT, chaguomsingi SIGNED inachukuliwa.

DOUBLE(M, D), ambapo M ni jumla ya nambari ya tarakimu na D ni nambari ya tarakimu baada ya nukta ya desimali, inawakilisha nambari ya nukta inayoelea yenye usahihi maradufu. Ikiwa HAIJASINIWA imebainishwa, thamani hasi haziruhusiwi.

  1. VARCHAR(M) inawakilisha mfuatano wa urefu tofauti ambapo M ndio upeo wa juu unaoruhusiwa wa urefu wa safu wima katika baiti (65,535 kwa nadharia). Mara nyingi, idadi ya baiti ni sawa na idadi ya wahusika, isipokuwa kwa baadhi ya herufi ambazo zinaweza kuchukua hadi baiti 3. Kwa mfano, herufi ya Kihispania ñ inawakilisha herufi moja lakini inachukua hadi baiti 2.
  2. TEXT(M) inawakilisha safu iliyo na urefu usiozidi wa vibambo 65,535. Walakini, kama inavyotokea kwa VARCHAR(M), urefu halisi wa juu hupunguzwa ikiwa herufi za baiti nyingi zimehifadhiwa. Ikiwa M imebainishwa, safu wima huundwa kama aina ndogo zaidi inayoweza kuhifadhi idadi kama hiyo ya herufi.
  3. MEDIUMTEXT(M) na LONGTEXT(M) zinafanana na TEXT(M), isipokuwa tu kwamba urefu wa juu unaoruhusiwa ni herufi 16,777,215 na 4,294,967,295, mtawalia.

  1. DATE inawakilisha tarehe katika umbizo la YYYY-MM-DD.
  2. TIME inawakilisha wakati katika umbizo la HH:MM:SS.sss (saa, dakika, sekunde na milisekunde).
  3. DATETIME ni mchanganyiko wa DATE na TIME katika umbizo la YYYY-MM-DD HH:MM:SS.
  4. TIMESTAMP inatumika kufafanua muda ambapo safu mlalo iliongezwa au kusasishwa.

Baada ya kukagua aina hizi za data, utakuwa katika nafasi nzuri zaidi ya kuamua ni aina gani ya data unayohitaji kukabidhi kwa safu wima fulani kwenye jedwali.

Kwa mfano, jina la mtu linaweza kutoshea kwa urahisi katika VARCHAR(50), ilhali chapisho la blogu litahitaji aina ya TEXT (chagua M kulingana na mahitaji yako mahususi).

Kabla ya kuzama katika kuunda majedwali, kuna dhana mbili za kimsingi kuhusu hifadhidata za uhusiano ambazo tunahitaji kukagua: funguo msingi na za kigeni.

Ufunguo msingi una thamani inayotambulisha kwa njia ya kipekee kila safu mlalo au rekodi kwenye jedwali. Kwa upande mwingine, ufunguo wa kigeni hutumiwa kuunda kiungo kati ya data katika meza mbili, na kudhibiti data ambayo inaweza kuhifadhiwa kwenye meza ambapo ufunguo wa kigeni iko. Vifunguo vya msingi na vya kigeni kwa ujumla ni INT.

Ili kufafanua, hebu tutumie BookstoreDB na tuunde majedwali mawili yanayoitwa AuthorsTBL na BooksTBL kama ifuatavyo. Kizuizi cha NOT NULL kinaonyesha kuwa sehemu inayohusishwa inahitaji thamani isipokuwa NULL.

Pia, AUTO_INCREMENT inatumika kuongeza kwa moja thamani ya safu wima msingi za INT rekodi mpya inapowekwa kwenye jedwali.

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

Sasa tunaweza kuendelea na kuanza kuingiza rekodi kwenye AuthorsTBL na BooksTBL.

Kwanza tutajaza jedwali la AuthorsTBL. Kwa nini? Kwa sababu tunahitaji kuwa na thamani za AuthorID kabla ya kuingiza rekodi kwenye BooksTBL.

Tekeleza swali lifuatalo kutoka kwa kidokezo chako cha MariaDB:

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

Baada ya hapo, tutachagua rekodi zote kutoka kwa AuthorsTBL. Kumbuka tutahitaji AuthorID kwa kila rekodi ili kuunda swali la INSERT kwa BooksTBL.

Ikiwa ungependa kurejesha rekodi moja kwa wakati mmoja, unaweza kutumia kifungu cha WHERE ili kuonyesha sharti ambalo safu mlalo lazima itimize ili kurejeshwa. Kwa mfano,

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

Vinginevyo, unaweza kuchagua rekodi zote kwa wakati mmoja:

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

Sasa hebu tuunde swali la INSERT kwa BooksTBL, kwa kutumia AuthorID sambamba na kupatanisha mwandishi wa kila kitabu. Thamani ya 1 katika BookIsAvailable inaonyesha kitabu kiko kwenye hisa, 0 vinginevyo:

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

Kwa hatua hii tutafanya CHAGUA kuona rekodi katika BooksTBL. Kisha TUSASISHE bei ya \The Alchemist ya Paulo Coelho na UCHAGUE rekodi hiyo mahususi tena.

Kumbuka jinsi uga wa BookLastUpdated sasa unaonyesha thamani tofauti. Kama tulivyoeleza hapo awali, sehemu ya TIMESTAMP inaonyesha thamani rekodi ilipowekwa au kurekebishwa mara ya mwisho.

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

Ingawa hatutafanya hapa, unaweza pia kufuta rekodi ikiwa haitatumika tena. Kwa mfano, tuseme tunataka kufuta \The Alchemist kutoka BooksTBL.

Ili kufanya hivyo, tutatumia taarifa ya DELETE kama ifuatavyo:

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

Kama ilivyo kwa UPDATE, ni wazo nzuri kufanya CHAGUA kwanza ili kutazama rekodi ambazo zinaweza kuathiriwa na DELETE.

Pia, usisahau kuongeza kifungu cha WHERE na sharti (KitabuKitabu = 6) ili kuchagua rekodi maalum ya kuondolewa. Vinginevyo, una hatari ya kufuta safu zote kwenye meza!

Ikiwa ungependa kuunganisha sehemu mbili (au zaidi), unaweza kutumia taarifa ya CONCAT. Kwa mfano, hebu tuseme tunataka kurejesha seti ya matokeo ambayo ina sehemu moja yenye jina la kitabu na mwandishi katika mfumo wa \The Alchemist (Paulo Coelho) na safu nyingine yenye bei.

Hii itahitaji JIUNGE kati ya AuthorsTBL na BooksTBL kwenye uwanja wa kawaida ulioshirikiwa na majedwali yote mawili (AuthorID):

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

Kama tunavyoona, CONCAT huturuhusu kuungana na usemi wa kamba nyingi zinazotenganishwa na koma. Pia utagundua kuwa tulichagua lakabu Maelezo ili kuwakilisha seti ya matokeo ya muunganisho.

Matokeo ya swali hapo juu yanaonyeshwa kwenye picha hapa chini:

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)

Kutumia mzizi kufanya shughuli zote za DML kwenye hifadhidata ni wazo mbaya. Ili kuepuka hili, tunaweza kuunda akaunti mpya ya mtumiaji wa MariaDB (tutaiita mtumiaji wa duka la vitabu) na kupeana ruhusa zote zinazohitajika kwa BookstoreDB:

MariaDB [BookstoreDB]> CREATE USER [email  IDENTIFIED BY 'YourPasswordHere';
MariaDB [BookstoreDB]> GRANT ALL PRIVILEGES ON  BookstoreDB.* to [email ;
MariaDB [BookstoreDB]> FLUSH PRIVILEGES;
MariaDB [BookstoreDB]> CREATE USER [email  IDENTIFIED BY 'tecmint';
Query OK, 0 rows affected (0.00 sec)

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

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

Kuwa na mtumiaji aliyejitolea, tofauti kwa kila hifadhidata kutazuia uharibifu kwa hifadhidata nzima iwapo akaunti moja itaathirika.

Ili kufuta haraka ya MariaDB, chapa amri ifuatayo na ubonyeze Enter:

MariaDB [BookstoreDB]> \! clear

Ili kukagua usanidi wa jedwali fulani, fanya:

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

Kwa mfano,

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)

Ukaguzi wa haraka unaonyesha kuwa sehemu ya BookIsAvailable inakubali thamani NULL. Kwa kuwa hatutaki kuruhusu hilo, TUTABADILI jedwali kama ifuatavyo:

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

(Jisikie huru kuonyesha safu wima tena - NDIYO iliyoangaziwa kwenye picha hapo juu inapaswa sasa kuwa HAPANA).

Hatimaye, ili kuona hifadhidata zote kwenye seva yako, fanya:

MariaDB [BookstoreDB]> SHOW DATABASES;
OR
MariaDB [BookstoreDB]> SHOW SCHEMAS;
 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)

Picha ifuatayo inaonyesha matokeo ya amri iliyo hapo juu baada ya kufikia kidokezo cha MariaDB kama mtumiaji wa duka la vitabu (kumbuka jinsi akaunti hii haiwezi \kuona hifadhidata zozote isipokuwa BookstoreDB na information_schema (zinazopatikana kwa watumiaji wote):

Muhtasari

Katika makala haya tumeelezea jinsi ya kuendesha shughuli za DML na jinsi ya kuunda hifadhidata, majedwali, na watumiaji waliojitolea kwenye hifadhidata ya MariaDB. Zaidi ya hayo, tulishiriki vidokezo vichache ambavyo vinaweza kurahisisha maisha yako kama msimamizi wa mfumo/hifadhidata.

  1. Sehemu ya Usimamizi wa Hifadhidata ya MySQL - 1
  2. Sehemu ya Usimamizi wa Hifadhidata ya MySQL - 2
  3. Kurekebisha na Kuboresha Utendaji wa MySQL - Sehemu ya 3

Ikiwa una maswali yoyote kuhusu makala hii, usisite kutujulisha! Jisikie huru kutumia fomu ya maoni hapa chini ili kuwasiliana nasi.