Vyhledávání na webu

Naučte se MySQL/MariaDB pro začátečníky – část 1


V tomto článku si ukážeme, jak vytvořit databázi (také známou jako schéma), tabulky (s datovými typy) a vysvětlíme, jak provést Data Manipulation Language (DML ) operace s daty na serveru MySQL/MariaDB.

Předpokládá se, že jste již dříve 1) nainstalovali potřebné balíčky do svého systému Linux a 2) provedli mysql_secure_installation, abyste zlepšili zabezpečení databázového serveru . Pokud ne, postupujte podle níže uvedených pokynů k instalaci serveru MySQL/MariaDB.

  1. Nainstalujte nejnovější databázi MySQL v systémech Linux
  2. Nainstalujte nejnovější databázi MariaDB v systémech Linux

Pro stručnost budeme v tomto článku odkazovat výhradně na MariaDB, ale zde uvedené koncepty a příkazy platí i pro MySQL.

Vytváření databází, tabulek a oprávněných uživatelů

Jak víte, databázi lze jednoduše definovat jako organizovanou sbírku informací. Konkrétně MariaDB je systém pro správu relačních databází (RDBMS) a používá k provádění operací s databázemi jazyk Structure Query Language. Navíc mějte na paměti, že MariaDB používá termíny databáze a schéma zaměnitelně.

K ukládání trvalých informací v databázi použijeme tabulky, které ukládají řádky dat. Často spolu budou dvě nebo více tabulek nějakým způsobem souviset. To je část organizace, která charakterizuje používání relačních databází.

Vytvoření nové databáze

Chcete-li vytvořit novou databázi s názvem BooksDB, zadejte výzvu MariaDB s následujícím příkazem (budete vyzváni k zadání hesla pro uživatele root MariaDB):

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

Jakmile je databáze vytvořena, musíme na ní vytvořit alespoň dvě tabulky. Nejprve se však podívejme na koncept datových typů.

Představujeme datové typy MariaDB

Jak jsme vysvětlili dříve, tabulky jsou databázové objekty, kde budeme uchovávat trvalé informace. Každá tabulka se skládá ze dvou nebo více polí (známých také jako sloupce) daného datového typu (typu informací), které může takové pole uložit.

Nejběžnější typy dat v MariaDB jsou následující (úplný seznam si můžete prohlédnout v oficiální online dokumentaci MariaDB):

číselné:
  1. BOOLEAN považuje 0 za nepravdu a všechny ostatní hodnoty za pravdivé.
  2. TINYINT, pokud je použit s SIGNED, pokrývá rozsah od -128 do 127, zatímco rozsah UNSIGNED je 0 až 255.
  3. SMALLINT, pokud se použije s SIGNED, pokrývá rozsah od -32768 do 32767. Rozsah NESIGNED je 0 až 65535.
  4. INT, pokud je použit s NESIGNED, pokrývá rozsah od 0 do 4294967295 a -2147483648 do 2147483647 jinak.

Poznámka: V TINYINT, SMALLINT a INT se předpokládá výchozí SIGNED.

DOUBLE(M, D), kde M je celkový počet číslic a D je počet číslic za desetinnou čárkou, představuje číslo s plovoucí desetinnou čárkou s dvojitou přesností. Pokud je zadáno UNSIGNED, záporné hodnoty nejsou povoleny.

Tětiva :
  1. VARCHAR(M) představuje řetězec proměnné délky, kde M je maximální povolená délka sloupce v bajtech (65 535 teoreticky). Ve většině případů je počet bajtů shodný s počtem znaků, kromě některých znaků, které mohou zabírat až 3 bajty. Například španělské písmeno ñ představuje jeden znak, ale zabírá 2 bajty.
  2. TEXT(M) představuje sloupec s maximální délkou 65 535 znaků. Stejně jako u VARCHAR(M) se však skutečná maximální délka zkrátí, pokud jsou uloženy vícebajtové znaky. Pokud je zadáno M, sloupec se vytvoří jako nejmenší typ, do kterého lze uložit takový počet znaků.
  3. MEDIUMTEXT(M) a LONGTEXT(M) jsou podobné TEXT(M), pouze maximální povolená délka je 16 777 215 a 4 294 967 295 znaků, respektive.
Datum a čas:
  1. DATUM představuje datum ve formátu RRRR-MM-DD.
  2. TIME představuje čas ve formátu HH:MM:SS.sss (hodiny, minuty, sekundy a milisekundy).
  3. DATETIME je kombinace DATE a TIME ve formátu RRRR-MM-DD HH:MM:SS.
  4. TIMESTAMP se používá k definování okamžiku přidání nebo aktualizace řádku.

Po zkontrolování těchto datových typů budete moci lépe určit, který datový typ je třeba přiřadit k danému sloupci v tabulce.

Například jméno osoby se snadno vejde do VARCHAR(50), zatímco příspěvek na blogu bude potřebovat typ TEXT (vyberte M jako podle vašich konkrétních potřeb).

Vytváření tabulek s primárními a cizími klíči

Než se vrhneme na vytváření tabulek, musíme si prostudovat dva základní pojmy o relačních databázích: primární a cizí klíč.

Primární klíč obsahuje hodnotu, která jednoznačně identifikuje každý řádek nebo záznam v tabulce. Na druhou stranu cizí klíč se používá k vytvoření vazby mezi daty ve dvou tabulkách a ke kontrole dat, která lze uložit do tabulky, kde se cizí klíč nachází. Primární i cizí klíč jsou obecně INT.

Pro ilustraci použijeme BookstoreDB a vytvoříme dvě tabulky s názvem AuthorsTBL a BooksTBL následovně. Omezení NOT NULL znamená, že přidružené pole vyžaduje jinou hodnotu než NULL.

AUTO_INCREMENT se také používá ke zvýšení hodnoty sloupců primárního klíče INT o jednu, když je do tabulky vložen nový záznam.

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

Nyní můžeme pokračovat a začít vkládat záznamy do AuthorsTBL a BooksTBL.

Výběr, vkládání, aktualizace a mazání řádků

Nejprve naplníme tabulku AuthorsTBL. Proč? Protože před vložením záznamů do BooksTBL potřebujeme mít hodnoty pro AuthorID.

Proveďte následující dotaz z výzvy MariaDB:

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

Poté vybereme všechny záznamy z AuthorsTBL. Nezapomeňte, že k vytvoření dotazu INSERT pro BooksTBL budeme potřebovat AuthorID pro každý záznam.

Pokud chcete načíst jeden záznam po druhém, můžete použít klauzuli WHERE k označení podmínky, kterou musí řádek splnit, aby byl vrácen. Například,

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

Případně můžete vybrat všechny záznamy současně:

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

Nyní vytvoříme dotaz INSERT pro BooksTBL pomocí odpovídajícího ID autora, který odpovídá autorovi každé knihy. Hodnota 1 v BookIsAvailable znamená, že kniha je skladem, 0 jinak:

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

V tomto okamžiku provedeme SELECT, abychom viděli záznamy v BooksTBL. Poté AKTUALIZUJME cenu „The Alchemist“ od Paula Coelha a znovu VYBERTE tuto konkrétní desku.

Všimněte si, že pole BookLastUpdated nyní zobrazuje jinou hodnotu. Jak jsme vysvětlili dříve, pole TIMESTAMP zobrazuje hodnotu, kdy byl záznam vložen nebo naposledy upraven.

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

Ačkoli to zde neuděláme, můžete záznam také smazat, pokud se již nepoužívá. Předpokládejme například, že chceme odstranit „The Alchymist “ z BooksTBL.

K tomu použijeme příkaz DELETE takto:

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

Stejně jako v případě UPDATE je dobré nejprve provést SELECT, abyste viděli záznamy, které mohou být potenciálně ovlivněny ODSTRANIT.

Nezapomeňte také přidat klauzuli WHERE a podmínku (BookID=6) pro výběr konkrétního záznamu, který má být odstraněn. V opačném případě riskujete smazání všech řádků v tabulce!

Pokud si přejete zřetězit dvě (nebo více) polí, můžete použít příkaz CONCAT. Řekněme například, že chceme vrátit sadu výsledků, která se skládá z jednoho pole s názvem knihy a autorem ve tvaru „Alchymista (Paulo Coelho) “ a dalšího sloupce s cenou.

To bude vyžadovat JOIN mezi AuthorsTBL a BooksTBL ve společném poli sdíleném oběma tabulkami (AuthorID):

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

Jak vidíme, CONCAT nám umožňuje spojit více řetězcových výrazů oddělených čárkami. Také si všimnete, že jsme zvolili alias Popis, který představuje sadu výsledků zřetězení.

Výstup výše uvedeného dotazu je zobrazen na obrázku níže:

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)

Vytvořte uživatele pro přístup k databázi BookstoreDB

Použití root k provádění všech operací DML v databázi je špatný nápad. Abychom tomu zabránili, můžeme vytvořit nový uživatelský účet MariaDB (pojmenujeme ho bookstoreuser) a přiřadit všechna potřebná oprávnění pro BookstoreDB:

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)

Mít vyhrazeného samostatného uživatele pro každou databázi zabrání poškození celé databáze v případě ohrožení jediného účtu.

Extra tipy pro MySQL

Chcete-li smazat výzvu MariaDB, zadejte následující příkaz a stiskněte Enter:

MariaDB [BookstoreDB]> \! clear

Chcete-li zkontrolovat konfiguraci dané tabulky, postupujte takto:

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

Například,

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)

Rychlá kontrola odhalí, že pole BookIsAvailable připouští hodnoty NULL. Protože to nechceme dovolit, ZMĚNÍME tabulku následovně:

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

(Neváhejte a znovu zobrazte sloupce – zvýrazněné ANO na obrázku výše by nyní mělo být NE).

Nakonec, chcete-li zobrazit všechny databáze na vašem serveru, proveďte:

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)

Následující obrázek ukazuje výsledek výše uvedeného příkazu po přístupu k výzvě MariaDB jako uživatel knihkupectví (všimněte si, že tento účet „nevidí “ žádné jiné databáze než BookstoreDB a informační_schéma (dostupné pro všechny uživatele):

souhrn

V tomto článku jsme vysvětlili, jak spouštět operace DML a jak vytvořit databázi, tabulky a vyhrazené uživatele v databázi MariaDB. Kromě toho jsme sdíleli několik tipů, které vám mohou usnadnit život jako správce systému/databáze.

  1. Část administrace databáze MySQL – 1
  2. Část administrace databáze MySQL – 2
  3. Ladění a optimalizace výkonu MySQL – 3. část

Pokud máte nějaké dotazy k tomuto článku, neváhejte a dejte nám vědět! Neváhejte nás kontaktovat pomocí níže uvedeného formuláře pro komentáře.