Ismerje meg a MySQL/MariaDB kezdőknek – 1. rész
Ebben a cikkben bemutatjuk, hogyan hozhatunk létre adatbázist (más néven sémát), táblázatokat (adattípusokkal), és elmagyarázzuk, hogyan kell végrehajtani az Adatmanipulációs nyelvet (DML). ) műveletek egy MySQL/MariaDB szerveren lévő adatokkal.
Feltételezzük, hogy korábban 1) telepítette a szükséges csomagokat Linux rendszerére, és 2) végrehajtotta a mysql_secure_installation programot az adatbázis-kiszolgáló biztonságának javítása érdekében. . Ha nem, kövesse az alábbi útmutatókat a MySQL/MariaDB szerver telepítéséhez.
- Telepítse a legújabb MySQL-adatbázist a Linux rendszerekben
- Telepítse a legújabb MariaDB adatbázist Linux rendszereken
A rövidség kedvéért ebben a cikkben kizárólag a MariaDB-re hivatkozunk, de az itt felvázolt fogalmak és parancsok a MySQLre is vonatkoznak.
Adatbázisok, táblák és jogosult felhasználók létrehozása
Mint ismeretes, az adatbázist egyszerűen úgy definiálhatjuk, mint egy szervezett információgyűjteményt. A MariaDB egy relációs adatbázis-kezelő rendszer (RDBMS), és a Structure Query Language nyelvet használja az adatbázisokon végzett műveletek végrehajtására. Ezenkívül ne feledje, hogy a MariaDB az adatbázis és a séma kifejezéseket felcserélhetően használja.
Az állandó információk adatbázisban való tárolásához táblázatokat használunk, amelyek adatsorokat tárolnak. Gyakran két vagy több tábla valamilyen módon kapcsolódik egymáshoz. Ez része annak a szervezetnek, amely a relációs adatbázisok használatát jellemzi.
Új adatbázis létrehozása
Új BooksDB nevű adatbázis létrehozásához írja be a MariaDB promptot a következő paranccsal (a rendszer kéri, hogy adja meg a gyökér MariaDB felhasználó jelszavát):
[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)]>
Az adatbázis létrehozása után legalább két táblát kell létrehoznunk rajta. De először vizsgáljuk meg az adattípusok fogalmát.
A MariaDB adattípusok bemutatása
Ahogy korábban kifejtettük, a táblázatok olyan adatbázis-objektumok, amelyekben állandó információkat tárolunk. Minden tábla két vagy több mezőből (más néven oszlopból) áll egy adott adattípusból (az információ típusából), amelyeket az adott mező tárolhat.
A MariaDB leggyakoribb adattípusai a következők (a teljes listát a MariaDB hivatalos online dokumentációjában tekintheti meg):
Numerikus:
- A BOOLEAN a 0-t hamisnak, minden más értéket pedig igaznak tekint.
- A TINYINT, ha a SIGNED-del együtt használja, a -128 és 127 közötti tartományt fedi le, míg az NEM SZIGNÉLT tartomány 0 és 255 között van.
- A SMALLINT, ha a SIGNED-del együtt használja, a -32768 és 32767 közötti tartományt fedi le. Az UNSINGNED tartomány 0 és 65535 között van.
- Az INT, ha az UNSZIGNED-el együtt használja, a 0 és 4294967295 közötti tartományt fedi le, egyébként -2147483648 és 2147483647 között.
Megjegyzés: A TINYINT, SMALLINT és INT esetén a rendszer az alapértelmezett SIGNED értéket feltételezi.
A DOUBLE(M, D), ahol az M a számjegyek teljes száma, a D pedig a tizedesvessző utáni számjegyek száma, egy dupla pontosságú lebegőpontos szám. Ha az UNSZIGNED van megadva, akkor a negatív értékek nem megengedettek.
Húr :
- A VARCHAR(M) egy változó hosszúságú karakterlánc, ahol az M a maximális megengedett oszlophossz bájtokban (elméletileg 65 535). A legtöbb esetben a bájtok száma megegyezik a karakterek számával, kivéve néhány karaktert, amelyek akár 3 bájtot is elfoglalhatnak. Például a spanyol ñ betű egy karaktert jelöl, de 2 bájtot foglal el.
- A SZÖVEG(M) egy oszlopot jelöl, amelynek maximális hossza 65 535 karakter. Azonban, ahogy az a VARCHAR(M) esetében is megtörténik, a tényleges maximális hossz csökken, ha többbájtos karaktereket tárol. Ha az M meg van adva, akkor az oszlop az ilyen számú karakter tárolására alkalmas legkisebb típusként jön létre.
- A KÖZEPES SZÖVEG(M) és a HOSSZÚ SZÖVEG(M) hasonló a SZÖVEG(M)-hez, csak annyi, hogy a maximális megengedett hossz 16 777 215 és 4 294 967 295 karakter, illetőleg.
Dátum és idő:
- A DATE a dátumot jelöli ÉÉÉÉ-HH-NN formátumban.
- Az IDŐ az időt jelenti ÓÓ:PP:SS.sss formátumban (óra, perc, másodperc és ezredmásodperc).
- A DATETIME a DATE és az TIME kombinációja ÉÉÉÉ-HH-NN ÓÓ:PP:PP formátumban.
- Az TIMESTAMP a sor hozzáadásának vagy frissítésének pillanatának meghatározására szolgál.
Ezen adattípusok áttekintése után jobban meg tudja határozni, hogy melyik adattípust kell a táblázat egy adott oszlopához hozzárendelnie.
Például egy személy neve könnyen beilleszthető a VARCHAR(50)-be, míg egy blogbejegyzésnek SZÖVEG típusúra van szüksége (válassza az M-et, mint egyedi igényei szerint).
Táblázatok létrehozása elsődleges és idegen kulcsokkal
Mielőtt belevágnánk a táblák létrehozásába, a relációs adatbázisokkal kapcsolatban két alapvető fogalmat kell áttekintenünk: az elsődleges és az idegen kulcsokat.
Az elsődleges kulcs olyan értéket tartalmaz, amely egyedileg azonosítja a táblázat minden sorát vagy rekordját. Másrészt az idegen kulcs segítségével kapcsolatot hoz létre a két tábla adatai között, és szabályozza, hogy milyen adatok tárolhatók abban a táblában, ahol az idegen kulcs található. Mind az elsődleges, mind az idegen kulcsok általában INT-ek.
Szemléltetésképpen használjuk a BookstoreDB-t, és hozzunk létre két táblát AuthorsTBL és BooksTBL néven az alábbiak szerint. A NOT NULL megszorítás azt jelzi, hogy a társított mező a NULL értéktől eltérő értéket igényel.
Ezenkívül az AUTO_INCREMENT az INT elsődleges kulcs oszlopok értékének eggyel növelésére szolgál, amikor új rekordot szúr be a táblázatba.
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]>
Most már folytathatjuk a rekordok beszúrását az AuthorsTBL és BooksTBL mappába.
Sorok kiválasztása, beszúrása, frissítése és törlése
Először az AuthorsTBL táblát töltjük fel. Miért? Mert rendelkeznünk kell az AuthorID értékekkel, mielőtt rekordokat szúrnánk be a BooksTBL-be.
Hajtsa végre a következő lekérdezést a MariaDB promptból:
MariaDB [BookstoreDB]> INSERT INTO AuthorsTBL (AuthorName) VALUES ('Agatha Christie'), ('Stephen King'), ('Paulo Coelho');
Ezt követően az AuthorsTBL összes rekordját kiválasztjuk. Ne feledje, hogy minden rekordhoz szükségünk lesz az AuthorID-re, hogy létrehozzuk az INSERT lekérdezést a BooksTBL számára.
Ha egyszerre csak egy rekordot szeretne lekérni, a WHERE záradékkal jelezheti azt a feltételt, amelyet egy sornak teljesítenie kell, hogy visszaküldje. Például,
MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';
Alternatív megoldásként az összes rekordot egyszerre is kiválaszthatja:
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]>
Most hozzuk létre az INSERT lekérdezést a BooksTBL számára, a megfelelő AuthorID használatával, hogy megfeleljen az egyes könyvek szerzőjének. Az 1 érték a BookIsAvailable mezőben azt jelzi, hogy a könyv raktáron van, ellenkező esetben a 0 értéke:
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
Ezen a ponton SELECT-et fogunk végezni, hogy megtekintsük a rekordokat a BooksTBL-ben. Akkor FRISSÍTSÜK Paulo Coelho „The Alchemist” című dalának árát, és ismét SELECTezzük az adott lemezt.
Vegye figyelembe, hogy a BookLastUpdated mező most más értéket mutat. Amint azt korábban kifejtettük, a TIMESTAMP mező a rekord beszúrásának vagy legutóbbi módosításának időpontját mutatja.
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]>
Bár itt nem tesszük meg, törölhet is egy rekordot, ha már nem használja. Tegyük fel például, hogy törölni akarjuk a „The Alchemist ”-t a BooksTBL-ből.
Ehhez a DELETE utasítást fogjuk használni az alábbiak szerint:
MariaDB [BookstoreDB]> DELETE FROM BooksTBL WHERE BookID=6;
Akárcsak az UPDATE esetében, érdemes először egy SELECT műveletet végrehajtani, hogy megtekinthesse azokat a rekordokat, amelyekre hatással lehet a TÖRLÉSre.
Ne felejtse el hozzáadni a WHERE záradékot és a (BookID=6) feltételt az eltávolítani kívánt rekord kiválasztásához. Ellenkező esetben fennáll a veszélye annak, hogy a táblázat összes sorát törli!
Ha két (vagy több) mezőt szeretne összefűzni, használhatja a CONCAT utasítást. Tegyük fel például, hogy egy olyan eredményhalmazt szeretnénk visszaadni, amely egy mezőből áll a könyv nevével és szerzőjével „Az alkimista (Paulo Coelho)” formában, egy másik oszlopból pedig az árral.
Ehhez JOIN-ra lesz szükség az AuthorsTBL és a BooksTBL között a mindkét tábla által megosztott közös mezőben (AuthorID):
MariaDB [BookstoreDB]> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;
Amint látjuk, a CONCAT lehetővé teszi több karakterlánc-kifejezés összekapcsolását vesszővel elválasztva. Azt is észre fogja venni, hogy a Leírás álnevet választottuk az összefűzés eredményhalmazának megjelenítésére.
A fenti lekérdezés kimenete az alábbi képen látható:
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)
Felhasználó létrehozása a BookstoreDB adatbázis eléréséhez
Rossz ötlet a root használata az összes DML művelet végrehajtására az adatbázisban. Ennek elkerülése érdekében létrehozhatunk egy új MariaDB felhasználói fiókot (nevezzük bookstoreuser), és hozzárendelhetünk minden szükséges engedélyt a BookstoreDB számára:
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)
Ha minden adatbázishoz külön felhasználó tartozik, akkor elkerülhető, hogy a teljes adatbázis kárt okozzon, ha egyetlen fiók veszélybe kerül.
Extra MySQL tippek
A MariaDB prompt törléséhez írja be a következő parancsot, és nyomja meg az Enter billentyűt:
MariaDB [BookstoreDB]> \! clear
Egy adott táblázat konfigurációjának ellenőrzéséhez tegye a következőket:
MariaDB [BookstoreDB]> SELECT COLUMNS IN [TABLE NAME HERE ];
Például,
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)
Egy gyors ellenőrzés felfedi, hogy a BookIsAvailable mező NULL értékeket fogad el. Mivel ezt nem akarjuk megengedni, a táblázatot a következőképpen MÓDOSÍTSUK:
MariaDB [BookstoreDB]> ALTER TABLE BooksTBL MODIFY BookIsAvailable BOOLEAN NOT NULL;
(Nyugodtan jelenítse meg újra az oszlopokat – a fenti képen a kiemelt IGENnek most NEMnek kell lennie).
Végül a kiszolgálón lévő összes adatbázis megtekintéséhez tegye a következőket:
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)
A következő kép a fenti parancs eredményét mutatja, miután elérte a MariaDB promptot könyvesbolt-felhasználóként (figyelje meg, hogy ez a fiók a BookstoreDB és a < adatbázisokon kívül más adatbázisokat nem látinformation_schema (minden felhasználó számára elérhető):
Összegzés
Ebben a cikkben elmagyaráztuk, hogyan kell DML műveleteket futtatni, és hogyan hozhat létre adatbázist, táblákat és dedikált felhasználókat a MariaDB adatbázison. Ezenkívül megosztottunk néhány tippet, amelyek megkönnyíthetik az Ön életét rendszer-/adatbázis-adminisztrátorként.
- MySQL adatbázis-felügyeleti rész – 1
- MySQL adatbázis-felügyeleti rész – 2
- MySQL teljesítményhangolás és -optimalizálás – 3. rész
Ha bármilyen kérdése van ezzel a cikkel kapcsolatban, ne habozzon ossza meg velünk! Nyugodtan használja az alábbi megjegyzés űrlapot, hogy kapcsolatba léphessen velünk.