Naucz się MySQL/MariaDB dla początkujących - część 1

W tym artykule pokażemy, jak utworzyć bazę danych (znaną również jako schemat), tabele (z typami danych) i wyjaśnimy, jak wykonać język manipulacji danymi (DML ) operacje na danych na serwerze MySQL/MariaDB.

Zakłada się, że wcześniej 1) zainstalowałeś niezbędne pakiety w swoim systemie Linux i 2) wykonałeś mysql_secure_installation w celu poprawy bezpieczeństwa serwera bazy danych . Jeśli nie, postępuj zgodnie z poniższymi instrukcjami, aby zainstalować serwer MySQL/MariaDB.

Zainstaluj najnowszą bazę danych MySQL w systemach Linux
Zainstaluj najnowszą bazę danych MariaDB w systemach Linux

Dla zachowania zwięzłości, w tym artykule będziemy odnosić się wyłącznie do MariaDB, ale przedstawione tutaj koncepcje i polecenia mają zastosowanie również do MySQL.

Tworzenie baz danych, tabel i autoryzowanych użytkowników

Jak wiadomo, bazę danych można najprościej zdefiniować jako zorganizowany zbiór informacji. W szczególności MariaDB to system zarządzania relacyjnymi bazami danych (RDBMS) i używa języka Structure Query Language do wykonywania operacji na bazach danych. Dodatkowo należy pamiętać, że MariaDB używa zamiennie terminów baza danych i schemat.

Do przechowywania trwałych informacji w bazie danych użyjemy tabel przechowujących wiersze danych. Często dwie lub więcej tabel będzie ze sobą w jakiś sposób powiązanych. Jest to część organizacji charakteryzująca wykorzystanie relacyjnych baz danych.

Tworzenie nowej bazy danych

Aby utworzyć nową bazę danych o nazwie BooksDB, wprowadź wiersz MariaDB z następującym poleceniem (zostaniesz poproszony o podanie hasła użytkownika 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)]>

Gdy baza danych będzie już utworzona, musimy utworzyć w niej co najmniej dwie tabele. Ale najpierw przyjrzyjmy się koncepcji typów danych.

Przedstawiamy typy danych MariaDB

Jak wyjaśniliśmy wcześniej, tabele to obiekty bazy danych, w których będziemy przechowywać trwałe informacje. Każda tabela składa się z dwóch lub więcej pól (zwanych również kolumnami) danego typu danych (rodzaju informacji), które dane pole może przechowywać.

Najpopularniejsze typy danych w MariaDB są następujące (pełną listę można znaleźć w oficjalnej dokumentacji online MariaDB):

Numeryczne:

Wartość BOOLEAN uważa 0 za fałsz, a pozostałe wartości za prawdę.
TINYINT, jeśli jest używany z SIGNED, obejmuje zakres od -128 do 127, podczas gdy zakres UNSIGNED wynosi od 0 do 255.
SMALLINT, jeśli jest używany z SIGNED, obejmuje zakres od -32768 do 32767. Zakres UNSIGNED wynosi od 0 do 65535.
INT, jeśli jest użyte z UNSIGNED, obejmuje zakres od 0 do 4294967295, a w przeciwnym razie -2147483648 do 2147483647.

Uwaga: w przypadku TINYINT, SMALLINT i INT zakładana jest domyślna wartość PODPISANA.

DOUBLE(M, D), gdzie M to całkowita liczba cyfr, a D to liczba cyfr po przecinku, reprezentuje liczba zmiennoprzecinkowa o podwójnej precyzji. Jeśli określono UNSIGNED, wartości ujemne nie są dozwolone.

Strunowy :

VARCHAR(M) reprezentuje ciąg o zmiennej długości, gdzie M to maksymalna dozwolona długość kolumny w bajtach (teoretycznie 65 535). W większości przypadków liczba bajtów jest identyczna z liczbą znaków, z wyjątkiem niektórych znaków, które mogą zajmować aż 3 bajty. Na przykład hiszpańska litera ñ reprezentuje jeden znak, ale zajmuje 2 bajty.
TEKST(M) reprezentuje kolumnę o maksymalnej długości 65 535 znaków. Jednakże, podobnie jak w przypadku VARCHAR(M), rzeczywista maksymalna długość jest zmniejszana, jeśli przechowywane są znaki wielobajtowe. Jeśli określono M, kolumna jest tworzona jako najmniejszy typ, który może pomieścić taką liczbę znaków.
MEDIUMTEXT(M) i LONGTEXT(M) są podobne do TEXT(M), z tą tylko różnicą, że maksymalne dozwolone długości to 16 777 215 i 4 294 967 295 znaków, odpowiednio.

Data i godzina:

DATA reprezentuje datę w formacie RRRR-MM-DD.
TIME reprezentuje czas w formacie GG:MM:SS.sss (godzina, minuty, sekundy i milisekundy).
DATETIME to kombinacja DATE i TIME w formacie RRRR-MM-DD GG:MM:SS.
TIMESTAMP służy do określenia momentu dodania lub aktualizacji wiersza.

Po zapoznaniu się z tymi typami danych będziesz w stanie lepiej określić, jaki typ danych należy przypisać do danej kolumny w tabeli.

Na przykład imię i nazwisko osoby można łatwo zmieścić w VARCHAR(50), podczas gdy post na blogu będzie wymagał typu TEKST (wybierz M jako zgodnie z Twoimi konkretnymi potrzebami).

Tworzenie tabel z kluczami podstawowymi i obcymi

Zanim zajmiemy się tworzeniem tabel, istnieją dwie podstawowe koncepcje dotyczące relacyjnych baz danych, które musimy przejrzeć: klucze podstawowy i obcy.

Klucz podstawowy zawiera wartość, która jednoznacznie identyfikuje każdy wiersz lub rekord w tabeli. Z drugiej strony klucz obcy służy do tworzenia połączenia między danymi w dwóch tabelach i do kontrolowania danych, które mogą być przechowywane w tabeli, w której znajduje się klucz obcy. Zarówno klucze podstawowe, jak i obce są zazwyczaj kluczami INT.

Aby to zilustrować, użyjmy BookstoreDB i utwórz dwie tabele o nazwach AuthorsTBL i BookstoreDB w następujący sposób. Ograniczenie NOT NULL wskazuje, że powiązane pole wymaga wartości innej niż NULL.

Wartość AUTO_INCREMENT służy także do zwiększenia o jeden wartości kolumn klucza podstawowego INT, gdy do tabeli wstawiany jest nowy rekord.

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

Teraz możemy śmiało zacząć wstawiać rekordy do AuthorsTBL i BooksTBL.

Zaznaczanie, wstawianie, aktualizowanie i usuwanie wierszy

Najpierw zapełnimy tabelę AuthorsTBL. Dlaczego? Ponieważ musimy mieć wartości dla AuthorID przed wstawieniem rekordów do BooksTBL.

Wykonaj następujące zapytanie w wierszu polecenia MariaDB:

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

Następnie wybierzemy wszystkie rekordy z AuthorsTBL. Pamiętaj, że będziemy potrzebować AuthorID dla każdego rekordu, aby utworzyć zapytanie INSERT dla BooksTBL.

Jeśli chcesz pobrać jeden rekord na raz, możesz użyć klauzuli WHERE, aby wskazać warunek, który musi spełnić wiersz, aby został zwrócony. Na przykład,

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

Alternatywnie możesz wybrać wszystkie rekordy jednocześnie:

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

Utwórzmy teraz zapytanie INSERT dla BooksTBL, używając odpowiedniego AuthorID, aby dopasować autora każdej książki. Wartość 1 w BookIsAvailable oznacza, że książka jest w magazynie, 0 w przeciwnym razie:

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

W tym momencie dokonamy WYBIERZ, aby zobaczyć rekordy w BooksTBL. Następnie AKTUALIZUJ cenę „The Alchemist” Paulo Coelho i ponownie WYBIERZ tę konkretną płytę.

Zwróć uwagę, że pole BookLastUpdated pokazuje teraz inną wartość. Jak wyjaśniliśmy wcześniej, pole TIMESTAMP pokazuje wartość czasu wstawienia rekordu lub ostatniej modyfikacji.

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

Chociaż nie zrobimy tego tutaj, możesz także usunąć rekord, jeśli nie jest już używany. Załóżmy na przykład, że chcemy usunąć „The Alchemist” z BooksTBL.

W tym celu użyjemy instrukcji DELETE w następujący sposób:

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

Podobnie jak w przypadku AKTUALIZACJA, dobrze jest najpierw wykonać WYBIERZ, aby wyświetlić rekord(y), na które potencjalnie może mieć wpływ USUŃ.

Nie zapomnij też dodać klauzuli WHERE i warunku (BookID=6), aby wybrać konkretny rekord do usunięcia. W przeciwnym razie ryzykujesz usunięcie wszystkich wierszy w tabeli!

Jeśli chcesz połączyć dwa (lub więcej) pola, możesz użyć instrukcji CONCAT. Załóżmy na przykład, że chcemy zwrócić zestaw wyników składający się z jednego pola z tytułem książki i autorem w postaci „Alchemik (Paulo Coelho)” oraz drugiej kolumny z ceną.

Będzie to wymagało JOIN pomiędzy AuthorsTBL i BooksTBL na wspólnym polu współdzielonym przez obie tabele (AuthorID):

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

Jak widzimy, CONCAT pozwala nam łączyć wiele wyrażeń łańcuchowych oddzielonych przecinkami. Zauważysz również, że wybraliśmy alias Opis, który będzie reprezentował zbiór wyników konkatenacji.

Wynik powyższego zapytania pokazano na poniższym obrazku:

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)

Utwórz użytkownika, aby uzyskać dostęp do bazy danych BookstoreDB

Używanie root do wykonywania wszystkich operacji DML na bazie danych to zły pomysł. Aby tego uniknąć, możemy utworzyć nowe konto użytkownika MariaDB (nazwiemy je bookstoreuser) i przypisać wszystkie niezbędne uprawnienia dla 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)

Posiadanie dedykowanego, osobnego użytkownika dla każdej bazy danych zapobiegnie uszkodzeniu całej bazy danych w przypadku naruszenia bezpieczeństwa pojedynczego konta.

Dodatkowe wskazówki dotyczące MySQL

Aby wyczyścić monit MariaDB, wpisz następujące polecenie i naciśnij Enter:

MariaDB [BookstoreDB]> \! clear

Aby sprawdzić konfigurację danej tabeli wykonaj:

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

Na przykład,

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)

Szybka kontrola pokazuje, że pole BookIsAvailable przyjmuje wartości NULL. Ponieważ nie chcemy na to pozwolić, ZMIENIMY tabelę w następujący sposób:

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

(Możesz wyświetlić kolumny ponownie – podświetlone TAK na powyższym obrazku powinno teraz oznaczać NIE).

Na koniec, aby wyświetlić wszystkie bazy danych na serwerze, wykonaj:

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)

Poniższy obraz przedstawia wynik powyższego polecenia po uzyskaniu dostępu do wiersza MariaDB jako użytkownik księgarni (zwróć uwagę, że to konto nie może „widzieć” żadnych baz danych innych niż BookstoreDB i information_schema (dostępne dla wszystkich użytkowników):

Streszczenie

W tym artykule wyjaśniliśmy, jak uruchamiać operacje DML oraz jak tworzyć bazę danych, tabele i dedykowanych użytkowników w bazie danych MariaDB. Dodatkowo podzieliliśmy się kilkoma wskazówkami, które mogą ułatwić Ci życie jako administratora systemu/bazy danych.

Część administracyjna bazy danych MySQL – 1
Część administracyjna bazy danych MySQL – 2
Strojenie i optymalizacja wydajności MySQL – część 3

Jeśli masz jakiekolwiek pytania dotyczące tego artykułu, nie wahaj się nas o tym powiadomić! Aby się z nami skontaktować, skorzystaj z poniższego formularza komentarza.