Koyi MySQL/MariaDB don Masu farawa - Kashi na 1
A cikin wannan labarin za mu nuna yadda ake ƙirƙirar bayanai (wanda kuma aka sani da tsari), tebur (tare da nau'ikan bayanai), da kuma bayyana yadda ake aiwatar da Harshen Manipulation Data (DML) tare da bayanai akan sabar MySQL/MariaDB.
Ana ɗauka cewa a baya kun shigar da fakitin da suka dace akan tsarin Linux ɗin ku, kuma 2) kashe mysql_secure_installation don inganta tsaro na uwar garken bayanai. Idan ba haka ba, bi jagororin ƙasa don shigar da uwar garken MySQL/MariaDB.
- Saka Sabbin Database MySQL a cikin Linux Systems
- Saka Sabbin Database na MariaDB a cikin Linux Systems
Don taƙaitawa, za mu koma zuwa MariaDB na musamman a cikin wannan labarin, amma dabaru da umarnin da aka zayyana anan sun shafi MySQL kuma.
Ƙirƙirar Databases, Tables, da Masu Amfani masu izini
Kamar yadda kuka sani, ana iya bayyana ma'ajin bayanai a cikin sassauƙan kalmomi azaman tarin bayanai da aka tsara. Musamman, MariaDB tsarin kula da bayanai ne na dangantaka (RDBMS) kuma yana amfani da Harshen Tambayar Tsarin don yin ayyuka akan bayanan bayanai. Bugu da ƙari, ka tuna cewa MariaDB yana amfani da bayanan bayanan bayanai da ƙima a musanyawa.
Don adana bayanai na dindindin a cikin ma'ajin bayanai, za mu yi amfani da teburi masu adana layuka na bayanai. Yawancin lokaci, tebur biyu ko fiye za su kasance da alaƙa da juna ta wata hanya. Wannan wani bangare ne na ƙungiyar da ke keɓance amfani da bayanan bayanai na alaƙa.
Don ƙirƙirar sabon bayanai mai suna BooksDB, shigar da saurin MariaDB tare da umarni mai zuwa (za a sa ka shigar da kalmar wucewa don tushen mai amfani da 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)]>
Da zarar an ƙirƙiri ma'ajin bayanai, muna buƙatar ƙirƙirar tebur aƙalla guda biyu akansa. Amma da farko bari mu bincika manufar nau'ikan bayanai.
Gabatar da Nau'in bayanan MariaDB
Kamar yadda muka yi bayani a baya, Tables abubuwa ne na bayanai inda za mu adana bayanai na dindindin. Kowane tebur ya ƙunshi filaye biyu ko fiye (wanda kuma aka sani da ginshiƙai) na nau'in bayanan da aka bayar (nau'in bayanan) wanda irin wannan filin zai iya adanawa.
Mafi yawan nau'ikan bayanai na yau da kullun a cikin MariaDB sune masu zuwa (zaku iya tuntuɓar cikakken jerin a cikin takaddun MariaDB na kan layi):
- BOOLEAN yana ɗaukar 0 a matsayin ƙarya kuma duk wasu dabi'u a matsayin gaskiya.
- TINYINT, idan aka yi amfani da shi tare da SIGNED, yana rufe kewayon daga -128 zuwa 127, yayin da ba a sanya hannu ba kewayon 0 zuwa 255.
- SMALLINT, idan aka yi amfani da shi tare da SIGNED, yana rufe kewayon daga -32768 zuwa 32767. Matsakaicin kewayon 0 zuwa 65535.
- INT, idan aka yi amfani da shi tare da UNSIGNED, yana rufe kewayon daga 0 zuwa 4294967295, da -2147483648 zuwa 2147483647 in ba haka ba.
Lura: A cikin TINYINT, SMALLINT, da INT, ana ɗaukar tsohowar SIGNED.
NUBA(M, D), inda M shine jimlar adadin lambobi kuma D shine adadin lambobi bayan ma'aunin ƙima, yana wakiltar lamba mai faɗi-biyu. Idan an ƙayyade UNSIGNED, ba za a yarda da ƙima mara kyau ba.
- VARCHAR(M) yana wakiltar zaren tsayi mai canzawa inda M shine matsakaicin tsayin shafi da aka yarda a cikin bytes (65,535 a ka'idar). A mafi yawan lokuta, adadin bytes yana daidai da adadin haruffa, sai dai wasu haruffa waɗanda zasu iya ɗaukar har zuwa 3 bytes. Misali, harafin Sifen ñ yana wakiltar hali ɗaya amma yana ɗaukar bytes 2.
- TEXT(M) yana wakiltar ginshiƙi mai matsakaicin tsayin haruffa 65,535. Koyaya, kamar yadda yake faruwa tare da VARCHAR(M), ainihin matsakaicin tsayi yana raguwa idan an adana haruffan byte masu yawa. Idan M, an ƙirƙiri ginshiƙi a matsayin mafi ƙarancin nau'in da zai iya adana adadin haruffa.
- MEDIUMTEXT(M) da LONGTEXT(M) suna kama da TEXT(M), kawai cewa matsakaicin tsayin da aka yarda shine 16,777,215 da haruffa 4,294,967,295, bi da bi.
- DATE yana wakiltar kwanan wata a tsarin YYYY-MM-DD.
- TIME yana wakiltar lokaci a tsarin HH:MM:SS.ss (awa, mintuna, daƙiƙa, da millise seconds).
- DATETIME shine haɗin DATE da TIME a cikin tsarin YYYY-MM-DD HH:MM:SS.
- Ana amfani da TIMESTAMP don ayyana lokacin da aka ƙara ko sabunta jere.
Bayan yin bitar waɗannan nau'ikan bayanan, zaku kasance cikin mafi kyawun matsayi don tantance nau'in bayanan da kuke buƙatar sanyawa ga wani shafi da aka bayar a cikin tebur.
Misali, sunan mutum zai iya shiga cikin sauƙi cikin VARCHAR(50), yayin da rubutun bulogi zai buƙaci nau'in RUBUTU (zaɓa M gwargwadon buƙatun ku).
Kafin mu nutse cikin ƙirƙirar teburi, akwai mahimman ra'ayoyi guda biyu game da bayanan alaƙa waɗanda muke buƙatar sake dubawa: maɓallan farko da na waje.
Maɓalli na farko ya ƙunshi ƙima wanda ke keɓance kowane jere, ko rikodin, a cikin tebur. A gefe guda kuma, ana amfani da maɓalli na waje don ƙirƙirar hanyar haɗi tsakanin bayanan da ke cikin tebur guda biyu, da sarrafa bayanan da za a iya adanawa a cikin tebur inda maɓallin ketare yake. Duk maɓallan farko da na waje gabaɗaya INTs ne.
Don kwatanta, bari mu yi amfani da BookstoreDB kuma mu ƙirƙiri tebur biyu masu suna MarubutaTBL da LittattafaiTBL kamar haka. Ƙuntataccen BA NULL yana nuna cewa filin da ke da alaƙa yana buƙatar ƙima banda NULL.
Hakanan, ana amfani da AUTO_INCREMENT don haɓaka da ɗaya ƙimar ginshiƙan maɓalli na farko na INT lokacin da aka saka sabon rikodin a cikin tebur.
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]>
Yanzu za mu iya ci gaba kuma mu fara saka bayanai cikin MarubutaTBL da LittattafaiTBL.
Za mu fara cika tebur MarubutaTBL. Me yasa? Domin muna buƙatar samun ƙima don AuthorID kafin saka bayanai a cikin BooksTBL.
Yi tambaya mai zuwa daga faɗakarwar MariaDB:
MariaDB [BookstoreDB]> INSERT INTO AuthorsTBL (AuthorName) VALUES ('Agatha Christie'), ('Stephen King'), ('Paulo Coelho');
Bayan haka, za mu zaɓi duk bayanan daga AuthorsTBL. Ka tuna za mu buƙaci AuthorID don kowane rikodin don ƙirƙirar tambayar INSERT don BooksTBL.
Idan kana so ka dawo da rikodin guda ɗaya a lokaci ɗaya, zaka iya amfani da jumlar INA don nuna sharadi cewa layi dole ne ya cika don dawo da shi. Misali,
MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';
A madadin, zaku iya zaɓar duk bayanan lokaci guda:
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]>
Yanzu bari mu ƙirƙiri tambayar INSERT don BooksTBL, ta yin amfani da madaidaicin AuthorID don dacewa da marubucin kowane littafi. Ƙimar 1 a cikin BookIsAvailable tana nuna littafin yana hannun jari, 0 in ba haka ba:
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
A wannan lokacin za mu yi SELECT don ganin bayanan a cikin BooksTBL. Sa'an nan kuma bari mu Ɗaukaka farashin \The Alchemist na Paulo Coelho kuma mu sake zaɓar takamaiman rikodin.
Lura yadda filin BookLastUpdated yanzu yana nuna wata ƙima daban. Kamar yadda muka yi bayani a baya, filin TIMESTAMP yana nuna ƙimar lokacin da aka saka rikodin ko aka gyara na ƙarshe.
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]>
Kodayake ba za mu yi shi a nan ba, kuna iya share rikodin idan ba a yi amfani da su ba. Misali, a ce muna son share The Alchemist daga BooksTBL.
Don yin haka, za mu yi amfani da bayanin DELETE kamar haka:
MariaDB [BookstoreDB]> DELETE FROM BooksTBL WHERE BookID=6;
Kamar yadda yake a cikin UPDATE, yana da kyau a fara ZABI don duba rikodin (s) wanda zai iya tasiri ta hanyar DELETE.
Hakanan, kar a manta da ƙara jumlar INA da sharadi (BookID=6) don zaɓar takamaiman rikodin da za a cire. In ba haka ba, kuna da haɗarin share duk layuka a cikin tebur!
Idan kuna son haɗa filaye biyu (ko fiye), zaku iya amfani da bayanin CONCAT. Alal misali, bari mu ce muna so mu dawo da saitin sakamako wanda ya ƙunshi filin ɗaya tare da sunan littafin da marubucin a cikin nau'i na The Alchemist (Paulo Coelho) da kuma wani shafi tare da farashi.
Wannan yana buƙatar JOIN tsakanin AuthorsTBL da BooksTBL akan filin gama gari wanda allunan biyu (AuthorID):
MariaDB [BookstoreDB]> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;
Kamar yadda muke iya gani, CONCAT tana ba mu damar haɗa maganganun kirtani da yawa waɗanda waƙafi ke raba su. Za ku kuma lura cewa mun zaɓi abin laƙabi da Bayanin don wakiltar saitin sakamako na haɗakarwa.
Ana nuna fitowar tambayar da ke sama a hoton da ke ƙasa:
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)
Yin amfani da tushen don aiwatar da duk ayyukan DML a cikin ma'ajin bayanai mummunan ra'ayi ne. Don kauce wa wannan, za mu iya ƙirƙirar sabon asusun mai amfani na MariaDB (za mu sanya masa suna kantin sayar da littattafai) kuma mu ba da duk wasu izini masu mahimmanci don 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)
Samun keɓaɓɓen mai amfani, keɓantaccen mai amfani ga kowane rumbun adana bayanai zai hana lalacewa ga ɗaukacin ma'ajin bayanai idan asusu ɗaya ya lalace.
Don share faɗakarwar MariaDB, rubuta umarni mai zuwa kuma danna Shigar:
MariaDB [BookstoreDB]> \! clear
Don duba daidaitawar teburin da aka bayar, yi:
MariaDB [BookstoreDB]> SELECT COLUMNS IN [TABLE NAME HERE ];
Misali,
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)
Binciken sauri yana nuna cewa filin BookIsAvailable yana yarda da ƙimar NULL. Tun da ba ma so mu ƙyale hakan, za mu canza teburin kamar haka:
MariaDB [BookstoreDB]> ALTER TABLE BooksTBL MODIFY BookIsAvailable BOOLEAN NOT NULL;
(Ka ji kyauta don sake nuna ginshiƙan - YES da aka haskaka a hoton da ke sama ya kamata yanzu ya zama NO).
A ƙarshe, don duba duk bayanan bayanai akan sabar ku, yi:
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)
Hoton da ke gaba yana nuna sakamakon umarnin da ke sama bayan samun dama ga saurin MariaDB a matsayin mai amfani da littattafai (lura yadda wannan asusun ba zai iya gani kowane bayanan bayanai ban da BookstoreDB da information_schema (akwai ga duk masu amfani):
Takaitawa
A cikin wannan labarin mun bayyana yadda ake gudanar da ayyukan DML da kuma yadda ake ƙirƙirar bayanai, teburi, da masu amfani da aka sadaukar akan bayanan MariaDB. Bugu da ƙari, mun raba ƴan shawarwari waɗanda zasu iya sauƙaƙa rayuwar ku azaman tsarin/mai gudanar da bayanai.
- Kashi na Gudanarwar Database na MySQL - 1
- MySQL Gudanar da Database Administration Part – 2
- Tunning Performance Performance MySQL da Ingantawa - Kashi na 3
Idan kuna da wasu tambayoyi game da wannan labarin, kada ku yi shakka a sanar da mu! Jin kyauta don amfani da fam ɗin sharhin da ke ƙasa don isa gare mu.