Mụta MySQL/MariaDB maka ndị mbido - Akụkụ 1
N'isiokwu a, anyị ga-egosi otu esi emepụta nchekwa data (nke a makwaara dị ka schema), tebụl (nwere ụdị data), ma kọwaa otu esi arụ ọrụ Data Manipulation Language (DML) na data na MySQL/MariaDB server.
A na-eche na ị tinyelarị 1) ngwungwu dị mkpa na sistemụ Linux gị, yana 2) gburu mysql_secure_installation iji melite nchekwa nchekwa data. Ọ bụrụ na ọ bụghị, soro ntuziaka n'okpuru ka ịwụnye ihe nkesa MySQL/MariaDB.
- Wụnye ọdụ data MySQL kacha ọhụrụ na Sistemụ Linux
- Wụnye ọdụ data MariaDB kacha ọhụrụ na sistemụ Linux
Maka nkenke, anyị ga-ezo aka na MariaDB naanị n'ime akụkọ a, mana echiche na iwu ndị akọwapụtara ebe a na-emetụtakwa MySQL.
Ịmepụta ọdụ data, tebụl na ndị ọrụ ikike
Dị ka ị maara, nchekwa data nwere ike ịkọwa n'ụzọ dị mfe dị ka nchịkọta ozi ahaziri ahazi. Karịsịa, MariaDB bụ usoro njikwa nchekwa data mmekọrịta (RDBMS) ma na-eji Asụsụ ajụjụ Structure iji rụọ ọrụ na ọdụ data. Na mgbakwunye, buru n'uche na MariaDB na-eji nchekwa data na schema na-agbanwe agbanwe.
Iji chekwaa ozi na-adịgide adịgide na nchekwa data, anyị ga-eji tebụl na-echekwa ahịrị data. Ọtụtụ mgbe, tebụl abụọ ma ọ bụ karịa ga-ejikọta ibe ha n'ụzọ ụfọdụ. Nke ahụ bụ akụkụ nke nzukọ na-akọwapụta ojiji nke ọdụ data mmekọrịta.
Iji mepụta nchekwa data ọhụrụ aha ya bụ BooksDB, tinye ngwa ngwa MariaDB site na iwu na-esonụ (a ga-akpali gị itinye paswọọdụ maka onye ọrụ MariaDB mgbọrọgwụ):
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)]>
Ozugbo e mepụtara nchekwa data, anyị kwesịrị ịmepụta ma ọ dịkarịa ala tebụl abụọ na ya. Mana ka anyị buru ụzọ nyochaa echiche nke ụdị data.
Na-ewebata Ụdị data MariaDB
Dịka anyị kọwara na mbụ, tebụl bụ ihe nchekwa data ebe anyị ga-edobe ozi na-adịgide adịgide. Tebụl ọ bụla nwere mpaghara abụọ ma ọ bụ karịa (nke a makwaara dị ka kọlụm) nke ụdị data enyere (ụdị ozi) nke ubi dị otú ahụ nwere ike ịchekwa.
Ụdị data a na-ahụkarị na MariaDB bụ ndị a (ị nwere ike ịlele ndepụta zuru ezu na akwụkwọ ntanetị MariaDB):
- BOOLEAN na-ewere 0 dị ka ụgha yana ụkpụrụ ọ bụla ọzọ dị ka eziokwu.
- TINYINT, ọ bụrụ na ejiri ya na SIGNED, na-ekpuchi oke site na -128 ruo 127, ebe enweghị akara bụ 0 ruo 255.
- SMALLINT, ọ bụrụ na ejiri ya na SIGNED, na-ekpuchi oke site na -32768 ruo 32767. Oke enweghị akara bụ 0 ruo 65535.
- INT, ọ bụrụ na ejiri ya na UNSIGNED, na-ekpuchi oke site na 0 ruo 4294967295, yana -2147483648 ruo 2147483647 ma ọ bụghị.
Cheta na: Na TINYINT, SMALLINT, na INT, a na-eche na ndabara SIGNED.
ABỤỌ (M, D), ebe M bụ ọnụọgụ ọnụọgụgụ na D bụ ọnụọgụ ọnụọgụgụ ka akara nrịbaịlụ gachara, na-anọchi anya ọnụọgụ ihe na-ese n'elu mmiri okpukpu abụọ. Ọ bụrụ na akọwapụtara enweghị aha, agaghị anabata ụkpụrụ adịghị mma.
- VARCHAR(M) na-anọchi anya eriri ogologo mgbanwe ebe M bụ ogologo kọlụm kacha ekwe na bytes (65,535 na tiori). N'ọtụtụ oge, ọnụọgụ bytes na-adakọ na ọnụọgụ mkpụrụedemede, ma e wezụga ụfọdụ mkpụrụedemede nwere ike iburu ihe ruru 3 bytes. Dịka ọmụmaatụ, mkpụrụedemede Spanish ñ na-anọchi anya otu agwa mana ọ na-ewe 2 bytes.
- TEXT(M) na-anọchite anya kọlụm nwere mkpụrụedemede 65,535 kacha ogologo. Agbanyeghị, dị ka ọ na-eme na VARCHAR(M), ogologo kachasị n'ezie na-ebelata ma ọ bụrụ na echekwara mkpụrụedemede multi-byte. Ọ bụrụ na akọwapụtara M, a na-emepụta kọlụm dị ka ụdị kacha nta nwere ike ịchekwa ọnụọgụ mkpụrụedemede.
- MEDIUMTEXT(M) na LONGTEXT(M) yitere TEXT(M), naanị na ogologo kacha ekwe ekwe bụ mkpụrụedemede 16,777,215 na mkpụrụedemede 4,294,967,295 n'otu n'otu.
- DATE na-anọchi anya ụbọchị n'ụdị YYYY-MM-DD.
- OGE na-anọchi anya oge n'ụdị HH:MM:SS.ss (awa, nkeji, sekọnd na milliseconds).
- DATETIME bụ nchikota DATE na TIME n'ụdị YYYY-MM-DD HH:MM:SS.
- A na-eji TIMESTAMP kọwaa oge agbakwunyere ma ọ bụ emelitere ahịrị.
Mgbe enyochachara ụdị data ndị a, ị ga-anọ n'ọnọdụ ka mma iji chọpụta ụdị data ị ga-ekenye na kọlụm nyere na tebụl.
Dịka ọmụmaatụ, aha mmadụ nwere ike dabara n'ụzọ dị mfe na VARCHAR(50), ebe blọgụ post ga-achọ ụdị TEXT (họrọ M dịka mkpa gị kpọmkwem).
Tupu anyị banye n'ime imepụta tebụl, e nwere echiche abụọ dị mkpa gbasara ọdụ data mmekọrịta nke anyị kwesịrị inyocha: igodo isi na nke mba ọzọ.
Isi igodo nwere uru na-akọwapụta ahịrị ma ọ bụ ndekọ ọ bụla n'ụzọ pụrụ iche na tebụl. N'aka nke ọzọ, a na-eji igodo mba ọzọ mepụta njikọ dị n'etiti data na tebụl abụọ, na ijikwa data nke nwere ike ịchekwa na tebụl ebe igodo mba ọzọ dị. Ma igodo isi na nke mba ofesi na-abụkarị INT.
Iji maa atụ, ka anyị jiri BookstoreDB wee mepụta tebụl abụọ akpọrọ AuthorsTBL na BooksTBL dị ka ndị a. Mgbochi NOT NULL na-egosi na mpaghara emetụtara chọrọ uru na-abụghị NULL.
Ọzọkwa, AUTO_INCREMENT ka a na-eji otu abawanye uru nke kọlụm igodo isi INT mgbe etinyere ndekọ ọhụrụ na tebụl.
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]>
Ugbu a, anyị nwere ike ịga n'ihu wee malite itinye ndekọ n'ime AuthorsTBL na BooksTBL.
Anyị ga-ebu ụzọ jupụta tebụl AuthorsTBL. Gịnị kpatara? N'ihi na anyị kwesịrị inwe ụkpụrụ maka AuthorID tupu ịtinye ndekọ na BooksTBL.
Mezue ajụjụ a site na ngwa ngwa MariaDB gị:
MariaDB [BookstoreDB]> INSERT INTO AuthorsTBL (AuthorName) VALUES ('Agatha Christie'), ('Stephen King'), ('Paulo Coelho');
Mgbe nke ahụ gasịrị, anyị ga-ahọrọ ihe ndekọ niile sitere na AuthorsTBL. Cheta na anyị ga-achọ AuthorID maka ndekọ ọ bụla iji mepụta ajụjụ INSERT maka AkwụkwọTBL.
Ọ bụrụ na ịchọrọ iweghachite otu ndekọ n'otu oge, ị nwere ike iji nkeji okwu WHERE gosi ọnọdụ na ahịrị ga-ezute ka eweghachi ya. Ọmụmaatụ,
MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';
N'aka nke ọzọ, ị nwere ike họrọ ndekọ niile n'otu oge:
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]>
Ugbu a, ka anyị mepụta ajụjụ INSERT maka BooksTBL, na-eji AuthorID kwekọrọ na onye dere akwụkwọ ọ bụla. Uru nke 1 na BookIsAvailable na-egosi na akwụkwọ ahụ dị na ngwaahịa, 0 ma ọ bụghị:
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
N'ebe a, anyị ga-eme Nhọrọ ka ịhụ ndekọ na BooksTBL. Mgbe ahụ, ka anyị kwalite ọnụahịa nke Alchemist nke Paulo Coelho ma họrọ ndekọ ahụ kpọmkwem ọzọ.
Mara ka ubi BookLastUpdated na-egosi ugbu a uru dị iche. Dịka anyị kọwara na mbụ, mpaghara TIMESTAMP na-egosi uru mgbe etinyere ndekọ ahụ ma ọ bụ mezigharịrị ikpeazụ.
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]>
Agbanyeghị na anyị agaghị eme ya ebe a, ị nwekwara ike ihichapụ ndekọ ma ọ bụrụ na ejighi ya ọzọ. Dịka ọmụmaatụ, were ya na anyị chọrọ ihichapụ \The Alchemist na BooksTBL.
Iji mee nke a, anyị ga-eji nkwupụta HIchapụ dị ka ndị a:
MariaDB [BookstoreDB]> DELETE FROM BooksTBL WHERE BookID=6;
Dị ka ọ dị n'ihe gbasara mmelite, ọ dị mma ka ị buru ụzọ mee Nhọrọ ka ilele ndekọ(s) nke nwere ike imetụta ya.
Ọzọkwa, echefula ịgbakwunye okwu WHERE na ọnọdụ (BookID=6) iji họrọ ndekọ akọwapụtara nke a ga-ewepụ. Ma ọ bụghị ya, ị na-agba ọsọ n'ihe ize ndụ nke ihichapụ ahịrị niile dị na tebụl!
Ọ bụrụ na ịchọrọ ịmekọrịta mpaghara abụọ (ma ọ bụ karịa), ịnwere ike iji nkwupụta CONCAT. Dịka ọmụmaatụ, ka anyị kwuo na anyị chọrọ iweghachite ihe nrụpụta nke mejupụtara otu ubi nwere aha akwụkwọ na onye edemede n'ụdị \The Alchemist (Paulo Coelho) na kọlụm ọzọ nwere ọnụahịa.
Nke a ga-achọ njikọ n'etiti AuthorsTBL na BooksTBL na mpaghara nkịtị nke tebụl abụọ ahụ (AuthorID):
MariaDB [BookstoreDB]> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;
Dịka anyị nwere ike ịhụ, CONCAT na-enye anyị ohere isonye n'ọtụtụ okwu eriri nke rịkọm kewapụrụ. Ị ga-achọpụtakwa na anyị ahọrọla nkọwapụta utu aha iji nọchite anya nsonazụ nke njikọ ahụ.
E gosipụtara nsonaazụ nke ajụjụ a dị n'elu na foto dị n'okpuru:
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)
Iji mgbọrọgwụ rụọ ọrụ DML niile na nchekwa data bụ echiche ọjọọ. Iji zere nke a, anyị nwere ike ịmepụta akaụntụ onye ọrụ MariaDB ọhụrụ (anyị ga-akpọ ya ụlọ ahịa akwụkwọ) wee kenye ikike niile dị mkpa maka ụlọ ahịa 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)
Inwe onye ọrụ raara onwe ya nye, dị iche iche maka nchekwa data ọ bụla ga-egbochi mmebi na nchekwa data niile ma ọ bụrụ na otu akaụntụ emebi.
Iji kpochapụ ngwa ngwa MariaDB, pịnye iwu a wee pịa Tinye:
MariaDB [BookstoreDB]> \! clear
Iji nyochaa nhazi nke tebụl enyere, mee:
MariaDB [BookstoreDB]> SELECT COLUMNS IN [TABLE NAME HERE ];
Ọmụmaatụ,
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)
Nnyocha ngwa ngwa na-ekpughe na ubi BookIsAvailable na-anabata ụkpụrụ NULL. Ebe ọ bụ na anyị achọghị ikwe ka nke ahụ, anyị ga-agbanwe tebụl dị ka ndị a:
MariaDB [BookstoreDB]> ALTER TABLE BooksTBL MODIFY BookIsAvailable BOOLEAN NOT NULL;
(Enwela onwe gị igosi ogidi ndị ahụ ọzọ - EE e mere ka ọ pụta ìhè na foto dị n'elu kwesịrị ịbụ NO).
N'ikpeazụ, iji lelee ọdụ data niile dị na sava gị, mee:
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)
Foto a na-egosi nsonaazụ nke iwu dị n'elu mgbe ị nwetasịrị ngwa ngwa MariaDB dị ka onye na-ere akwụkwọ (rịba ama ka akaụntụ a enweghị ike ịhụ ọdụ data ọ bụla karịa BookstoreDB na information_schema (dị maka ndị ọrụ niile):
Nchịkọta
N'ime edemede a, anyị akọwala otu esi arụ ọrụ DML yana otu esi emepụta nchekwa data, tebụl, na ndị ọrụ raara onwe ha nye na nchekwa data MariaDB. Na mgbakwunye, anyị kesara ndụmọdụ ole na ole nwere ike ime ka ndụ gị dị ka sistemụ/nchekwa data dị mfe.
- MySQL Database Administration Part – 1
- MySQL Database Administration Part – 2
- Ntụgharị na nkwalite arụmọrụ MySQL – Nkebi nke 3
Ọ bụrụ na ị nwere ajụjụ ọ bụla gbasara akụkọ a, egbula oge ime ka anyị mara! Enwere onwe gị iji ụdị nkọwa dị n'okpuru ebe a iji ruo anyị.