Kọ ẹkọ MySQL/MariaDB fun Awọn Ibẹrẹ - Apá 1
Ninu àpilẹkọ yii a yoo fihan bi a ṣe le ṣẹda ibi ipamọ data kan (eyiti a tun mọ bi apẹrẹ), awọn tabili (pẹlu awọn iru data), ati ṣalaye bi a ṣe le ṣe awọn iṣẹ Awọn ifọwọyi Ẹtan Ifa Data (DML) pẹlu data lori olupin MySQL/MariaDB kan.
O ti gba pe o ti ni iṣaaju 1) fi awọn idii ti o yẹ sori ẹrọ Lainos rẹ, ati 2) pa mysql_secure_installation lati mu aabo olupin olupin data dara. Ti kii ba ṣe bẹ, tẹle awọn itọsọna isalẹ lati fi sori ẹrọ olupin MySQL/MariaDB.
- Ṣafikun Ibi ipamọ data MySQL Tuntun ni Awọn ọna Linux
- Ṣafikun Ibi ipamọ data MariaDB Tuntun ni Awọn ọna Linux
Fun kukuru, a yoo tọka si MariaDB nikan ni gbogbo nkan yii, ṣugbọn awọn imọran ati awọn ofin ti o ṣe ilana nibi kan MySQL daradara.
Ṣiṣẹda Awọn apoti isura data, Awọn tabili, ati Awọn olumulo ti a fun ni aṣẹ
Bi o ṣe mọ, a le ṣalaye ibi ipamọ data ni awọn ọrọ ti o rọrun bi ikojọpọ eto alaye. Paapa, MariaDB jẹ eto iṣakoso data data ibatan (RDBMS) ati lo Ede Ibeere Ẹtọ lati ṣe awọn iṣẹ lori awọn apoti isura data. Ni afikun, ni lokan pe MariaDB nlo ibi ipamọ data awọn ofin ati apẹrẹ ni paarọ.
Lati tọju ifitonileti itẹramọṣẹ ninu ibi ipamọ data kan, a yoo lo awọn tabili ti o tọju awọn ori ila data. Nigbagbogbo, awọn tabili meji tabi diẹ sii yoo ni ibatan si ara wọn ni ọna kan. Iyẹn jẹ apakan ti agbari ti o ṣe afihan lilo awọn apoti isura data ibatan.
Lati ṣẹda ibi ipamọ data tuntun ti a npè ni BooksDB , tẹ iyara MariaDB pẹlu aṣẹ atẹle (iwọ yoo ni itara lati tẹ ọrọ igbaniwọle fun gbongbo MariaDB olumulo):
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)]>
Lọgan ti a ti ṣẹda ipilẹ data, a nilo lati ṣẹda o kere ju tabili meji lori rẹ. Ṣugbọn akọkọ jẹ ki a ṣawari imọran ti awọn iru data.
Ṣiṣe Awọn oriṣi data MariaDB
Gẹgẹbi a ti ṣalaye ni iṣaaju, awọn tabili jẹ awọn nkan data ibi ti a yoo tọju alaye ti o tẹsiwaju. Tabili kọọkan ni awọn aaye meji tabi diẹ sii (tun mọ bi awọn ọwọn) ti iru data ti a fun (iru alaye naa) ti iru aaye le fipamọ.
Awọn oriṣi data ti o wọpọ julọ ni MariaDB ni atẹle:
- BOOLEAN ka 0 si eke ati awọn iye miiran bi otitọ.
- TINYINT, ti o ba lo pẹlu SIGNED, bo ibiti o wa lati -128 si 127, lakoko ti ibiti UNSIGNED jẹ 0 si 255.
- SMALLINT, ti o ba lo pẹlu SIGNED, o bo ibiti o wa lati -32768 si 32767. Iwọn UNSIGNED jẹ 0 si 65535.
- INT, ti o ba lo pẹlu UNSIGNED, o bo ibiti o wa lati 0 si 4294967295, ati -2147483648 si 2147483647 bibẹkọ.
Akiyesi: Ni TINYINT, SMALLINT, ati INT, A ti gba aami ti a fiwe si aiyipada.
DOUBLE (M, D), nibiti M jẹ nọmba apapọ ti awọn nọmba ati D jẹ nọmba awọn nọmba lẹhin ti nomba eleemewa, o duro fun nọmba onigun omi-konge meji-deede. Ti UNSIGNED ba ṣalaye, awọn iye odi ko ni gba laaye.
- VARCHAR (M) duro fun okun ti gigun iyipada nibiti M jẹ ipari ọwọn ti a gba laaye ti o pọ julọ ninu awọn baiti (65,535 ni imọran). Ni ọpọlọpọ awọn ọran, nọmba awọn baiti jẹ aami si nọmba awọn ohun kikọ, ayafi fun diẹ ninu awọn ohun kikọ ti o le gba to bi awọn baiti mẹta. Fun apẹẹrẹ, lẹta ara Sipeeni ñ duro fun iwa kan ṣugbọn o gba awọn baiti 2.
- TEXT (M) duro fun iwe kan pẹlu ipari gigun ti awọn ohun kikọ 65,535. Sibẹsibẹ, bi o ti n ṣẹlẹ pẹlu VARCHAR (M), ipari gigun ti o pọ julọ dinku ti awọn ohun kikọ baiti pupọ ti wa ni fipamọ. Ti o ba ṣalaye M, a ṣẹda iwe naa bi oriṣi ti o kere julọ ti o le tọju iru nọmba ti awọn kikọ sii.
- MEDIUMTEXT (M) ati LONGTEXT (M) jọra si TEXT (M), nikan pe awọn gigun ti o gba laaye ti o pọ julọ jẹ awọn ohun kikọ 16,777,215 ati 4,294,967,295, lẹsẹsẹ.
- DATE duro fun ọjọ ni ọna kika YYYY-MM-DD.
- Akoko duro fun akoko ni HH: MM: Ọna kika SS.sss (wakati, iṣẹju, iṣẹju-aaya, ati awọn iṣẹju-aaya).
- DATETIME ni idapọ ti DATE ati Akoko ni YYYY-MM-DD HH: MM: Ọna kika SS.
- TIMESTAMP ti lo lati ṣalaye akoko ti a fi kun ila kan tabi imudojuiwọn.
Lẹhin ti o ti ṣe atunyẹwo awọn iru data wọnyi, iwọ yoo wa ni ipo ti o dara julọ lati pinnu iru iru data ti o nilo lati fi si iwe ti a fun ni tabili kan.
Fun apẹẹrẹ, orukọ eniyan kan le ni rọọrun wọ inu VARCHAR (50), lakoko ti ifiweranṣẹ bulọọgi yoo nilo iru TEXT (yan M gẹgẹbi fun awọn aini pataki rẹ).
Ṣaaju ki a to bọ sinu ṣiṣẹda awọn tabili, awọn imọran ipilẹ meji wa nipa awọn apoti isura data ibatan ti o nilo lati ṣe atunyẹwo: awọn bọtini akọkọ ati ajeji.
Bọtini akọkọ kan ni iye kan ti o ṣe idanimọ kana kọọkan, tabi igbasilẹ kọọkan, ninu tabili. Ni apa keji, a lo bọtini ajeji lati ṣẹda ọna asopọ laarin data ni awọn tabili meji, ati lati ṣakoso data ti o le fipamọ ni tabili nibiti bọtini ajeji wa. Awọn bọtini akọkọ ati ajeji ni gbogbo INT.
Lati ṣapejuwe, jẹ ki a lo BookstoreDB ki o ṣẹda awọn tabili meji ti a npè ni AuthorsTBL ati BooksTBL bi atẹle. Ihamọ NOT NULL tọka pe aaye ti o ni nkan nilo iye miiran ju NULL.
Pẹlupẹlu, AUTO_INCREMENT ti lo lati ṣe alekun nipasẹ ọkan iye ti awọn ọwọn bọtini INT akọkọ nigbati a fi igbasilẹ tuntun sinu tabili.
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]>
Bayi a le lọ siwaju ki o bẹrẹ sii fi sii awọn igbasilẹ sinu AuthorsTBL ati BooksTBL .
A yoo kọkọ tẹ tabili AuthorsTBL sii. Kí nìdí? Nitori a nilo lati ni awọn iye fun AuthorID ṣaaju fifi awọn igbasilẹ sii sinu Awọn iweTBL.
Ṣiṣe ibeere wọnyi lati inu iyara MariaDB rẹ:
MariaDB [BookstoreDB]> INSERT INTO AuthorsTBL (AuthorName) VALUES ('Agatha Christie'), ('Stephen King'), ('Paulo Coelho');
Lẹhin eyi, a yoo yan gbogbo awọn igbasilẹ lati AuthorsTBL. Ranti a yoo nilo AuthorID fun igbasilẹ kọọkan lati ṣẹda ibeere INSERT fun BooksTBL.
Ti o ba fẹ gba igbasilẹ kan ni akoko kan, o le lo NIPA NIPA lati fihan ipo kan ti ọna kan gbọdọ pade lati pada. Fun apere,
MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';
Ni omiiran, o le yan gbogbo awọn igbasilẹ nigbakanna:
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]>
Bayi jẹ ki a ṣẹda ibeere INSERT fun BooksTBL, ni lilo AuthorID ti o baamu lati ba onkọwe iwe kọọkan mu. Iye ti 1 ninu BookIs Wa ti o tọka pe iwe wa ni iṣura, 0 bibẹẹkọ:
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
Ni aaye yii a yoo ṣe Yiyan lati wo awọn igbasilẹ ni Awọn iweTBL. Lẹhinna jẹ ki a Mu imudojuiwọn iye owo ti\"The Alchemist" nipasẹ Paulo Coelho ki o si Yan igbasilẹ igbasilẹ kan pato lẹẹkansii.
Akiyesi bi aaye BookLastUpdated ti bayi ṣe afihan iye ti o yatọ. Gẹgẹbi a ti ṣalaye ni iṣaaju, aaye TIMESTAMP kan fihan iye nigbati o fi sii igbasilẹ naa tabi ti a tunṣe kẹhin.
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]>
Biotilẹjẹpe a ko ni ṣe nibi, o tun le paarẹ igbasilẹ kan ti ko ba lo mọ. Fun apẹẹrẹ, jẹ ki a fẹ paarẹ\"The Alchemist" lati BooksTBL.
Lati ṣe bẹ, a yoo lo DELETE gbólóhùn gẹgẹbi atẹle:
MariaDB [BookstoreDB]> DELETE FROM BooksTBL WHERE BookID=6;
Bii ninu ọran ti Imudojuiwọn, o jẹ imọran ti o dara lati ṣe Yiyan akọkọ lati wo igbasilẹ (s) ti o le ni ipa nipasẹ DELETE.
Pẹlupẹlu, maṣe gbagbe lati ṣafikun ipin WHERE ati ipo kan (BookID = 6) lati yan igbasilẹ pato lati yọkuro. Bibẹẹkọ, o ni eewu piparẹ gbogbo awọn ori ila ninu tabili!
Ti o ba fẹ lati ṣajọpọ awọn aaye meji (tabi diẹ sii), o le lo alaye CONCAT naa. Fun apẹẹrẹ, jẹ ki a sọ pe a fẹ pada si abajade abajade ti o ni aaye kan pẹlu orukọ iwe ati onkọwe ni irisi\"The Alchemist (Paulo Coelho)" ati ọwọn miiran pẹlu idiyele naa.
Eyi yoo nilo JOIN laarin Awọn onkọweTBL ati Awọn iweTBL lori aaye ti o wọpọ ti awọn tabili mejeeji pin (AuthorID):
MariaDB [BookstoreDB]> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;
Bii a ti le rii, CONCAT gba wa laaye lati darapọ mọ awọn ifihan ti okun lọpọlọpọ ti o yapa nipasẹ awọn aami idẹsẹ. Iwọ yoo tun ṣe akiyesi pe a yan inagijẹ Apejuwe lati ṣe aṣoju ṣeto abajade ti isomọpọ.
Iṣajade ti ibeere ti o wa loke ni a fihan ninu aworan isalẹ:
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)
Lilo gbongbo lati ṣe gbogbo awọn iṣẹ DML ninu ibi ipamọ data jẹ imọran ti ko dara. Lati yago fun eyi, a le ṣẹda akọọlẹ olumulo MariaDB tuntun kan (a yoo pe orukọ rẹ ni ile itaja-itaja) ati fi gbogbo awọn igbanilaaye pataki fun 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)
Nini ifiṣootọ kan, olumulo ti o ya sọtọ fun ibi ipamọ data kọọkan yoo ṣe idiwọ awọn bibajẹ si gbogbo ibi ipamọ data yẹ ki akọọlẹ kan kan di adehun.
Lati ko iyara MariaDB kuro, tẹ aṣẹ atẹle ki o tẹ Tẹ:
MariaDB [BookstoreDB]> \! clear
Lati ṣayẹwo iṣeto ti tabili ti a fun, ṣe:
MariaDB [BookstoreDB]> SELECT COLUMNS IN [TABLE NAME HERE ];
Fun apere,
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)
Ayewo yara yara fihan pe aaye ti BookIsAvailable gba awọn iye NULL. Niwọn igba ti a ko fẹ gba iyẹn laaye, a yoo TỌTỌ tabili bi atẹle:
MariaDB [BookstoreDB]> ALTER TABLE BooksTBL MODIFY BookIsAvailable BOOLEAN NOT NULL;
(Ni ominira lati fihan awọn ọwọn lẹẹkansi - afihan BẸẸNI ninu aworan loke yẹ ki o jẹ Bẹẹkọ bayi).
Lakotan, lati wo gbogbo awọn apoti isura data lori olupin rẹ, ṣe:
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)
Aworan ti n tẹle n fihan abajade aṣẹ ti o wa loke lẹhin ti o wọle si iyara MariaDB bi olutawe iwe-ọja (akiyesi bi akọọlẹ yii ko ṣe le "wo" eyikeyi awọn apoti isura data miiran ju BookstoreDB ati information_schema (wa fun gbogbo awọn olumulo):
Akopọ
Ninu àpilẹkọ yii a ti ṣalaye bii a ṣe le ṣiṣẹ awọn iṣẹ DML ati bii o ṣe le ṣẹda ibi ipamọ data kan, awọn tabili, ati awọn olumulo ifiṣootọ lori ibi ipamọ data MariaDB. Ni afikun, a pin awọn imọran diẹ ti o le ṣe igbesi aye rẹ bi eto/olutọju ibi ipamọ data rọrun.
- Apakan Isakoso aaye data MySQL - 1
- Apakan Isakoso aaye data MySQL - 2
- Tunning ati Iṣapeye Iṣe MySQL - Apakan 3
Ti o ba ni ibeere eyikeyi nipa nkan yii, ma ṣe ṣiyemeji lati jẹ ki a mọ! Ni idaniloju lati lo fọọmu asọye ni isalẹ lati de ọdọ wa.