初心者向けに MySQL/MariaDB を学ぶ - パート 1

この記事では、データベース (スキーマとも呼ばれます) とテーブル (データ型を含む) を作成する方法を示し、データ操作言語 (DML) を実行する方法について説明します。 ) MySQL/MariaDB サーバー上のデータを使用した操作。

以前に Linux システムに必要なパッケージを 1) インストールし、データベースサーバーのセキュリティを向上させるために 2) mysql_secure_installation を実行していることを前提としています。。そうでない場合は、以下のガイドに従って MySQL/MariaDB サーバーをインストールします。

Linux システムに最新の MySQL データベースをインストールする
Linux システムに最新の MariaDB データベースをインストールする

簡潔にするために、この記事ではMariaDB のみを指しますが、ここで説明する概念とコマンドはMySQL にも当てはまります。

データベース、テーブル、および許可されたユーザーの作成

ご存知のとおり、データベースは、簡単に言えば、組織化された情報のコレクションとして定義できます。特に、MariaDB はリレーショナルデータベース管理システム (RDBMS) であり、構造クエリ言語を使用してデータベースに対する操作を実行します。さらに、MariaDB ではデータベースとスキーマという用語が同じ意味で使用されることに注意してください。

永続的な情報をデータベースに保存するには、データ行を保存するテーブルを使用します。多くの場合、2 つ以上のテーブルが何らかの方法で相互に関連します。これは、リレーショナルデータベースの使用を特徴付ける組織の一部です。

新しいデータベースの作成

BooksDB という名前の新しいデータベースを作成するには、次のコマンドを使用して MariaDB プロンプトを入力します (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)]>

データベースを作成したら、その上に少なくとも 2 つのテーブルを作成する必要があります。まず、データ型の概念を見てみましょう。

MariaDB データ型の紹介

前に説明したように、テーブルは永続的な情報を保持するデータベースオブジェクトです。各テーブルは、フィールドに保存できる特定のデータ型 ( 情報の種類) の 2 つ以上のフィールド (列とも呼ばれます) で構成されます。

MariaDB で最も一般的なデータ型は次のとおりです (MariaDB の公式オンラインドキュメントで完全なリストを参照できます)。

数値:

BOOLEAN は 0 を false と見なし、その他の値を true と見なします。
TINYINT を SIGNED とともに使用すると、-128 ～ 127 の範囲がカバーされますが、UNSIGNED の範囲は 0 ～ 255 になります。
SMALLINT を SIGNED とともに使用すると、-32768 ～ 32767 の範囲がカバーされます。UNSIGNED の範囲は 0 ～ 65535 です。
INT は、UNSIGNED とともに使用した場合は 0 ～ 4294967295、それ以外の場合は -2147483648 ～ 2147483647 の範囲をカバーします。

注: TINYINT、SMALLINT、および INT では、デフォルトの SIGNED が想定されます。

DOUBLE(M, D) (M は合計桁数、D は小数点以下の桁数) を表します。倍精度浮動小数点数。 UNSIGNED が指定されている場合、負の値は許可されません。

弦：

VARCHAR(M) は可変長の文字列を表します。M はバイト単位で許可される列の最大長です (理論上は 65,535)。ほとんどの場合、バイト数は文字数と同じですが、一部の文字が 3 バイトを占める場合があります。たとえば、スペイン語の文字 ñ は 1 文字を表しますが、2 バイトを占めます。
TEXT(M) は、最大長が 65,535 文字の列を表します。ただし、VARCHAR(M) の場合と同様、マルチバイト文字を格納すると実際の最大長は減少します。 M を指定した場合、列はその文字数を格納できる最小の型として作成されます。
MEDIUMTEXT(M) と LONGTEXT(M) は TEXT(M) と似ていますが、許可される最大長が 16,777,215 文字と 4,294,967,295 文字である点が異なります。それぞれ。

日時：

DATE は、YYYY-MM-DD 形式で日付を表します。
TIME は、時刻を HH:MM:SS.sss 形式 (時、分、秒、ミリ秒) で表します。
日時は、日付と時刻をYYYY-MM-DD HH:MM:SS形式で組み合わせたものです。
TIMESTAMP は、行が追加または更新された瞬間を定義するために使用されます。

これらのデータ型を確認すると、テーブル内の特定の列にどのデータ型を割り当てる必要があるかをより適切に判断できるようになります。

たとえば、人の名前は VARCHAR(50) に簡単に収まりますが、ブログ投稿には TEXT タイプが必要です (M を選択します)。特定のニーズに応じて)。

主キーと外部キーを使用したテーブルの作成

テーブルの作成に入る前に、リレーショナルデータベースについて、主キーと外部キーという 2 つの基本概念を確認する必要があります。

主キーには、テーブル内の各行またはレコードを一意に識別する値が含まれます。一方、外部キーは、2 つのテーブル内のデータ間のリンクを作成し、外部キーが配置されているテーブルに格納できるデータを制御するために使用されます。通常、主キーと外部キーは両方とも INT です。

説明のために、次のように BookstoreDB を使用して、AuthorsTBL と BooksTBL という名前の 2 つのテーブルを作成してみましょう。 NOT NULL 制約は、関連するフィールドに NULL 以外の値が必要であることを示します。

また、AUTO_INCREMENT は、新しいレコードがテーブルに挿入されるときに INT 主キー列の値を 1 つ増やすために使用されます。

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

これで、AuthorsTBL と BooksTBL へのレコードの挿入を開始できます。

行の選択、挿入、更新、削除

まず、AuthorsTBL テーブルにデータを入力します。なぜ？ BooksTBL にレコードを挿入する前に、AuthorID の値が必要であるためです。

MariaDB プロンプトから次のクエリを実行します。

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

その後、AuthorsTBL からすべてのレコードを選択します。 BooksTBL の INSERT クエリを作成するには、各レコードの AuthorID が必要であることに注意してください。

一度に 1 つのレコードを取得する場合は、WHERE 句を使用して、行が返されるために満たす必要がある条件を示すことができます。例えば、

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

あるいは、すべてのレコードを同時に選択することもできます。

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

次に、対応する著者IDを使用して各書籍の著者と一致するBooksTBLのINSERTクエリを作成しましょう。 BookIsAvailable の値 1 は書籍が在庫があることを示し、それ以外の場合は 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);

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

この時点で、SELECT を実行して BooksTBL 内のレコードを表示します。次に、パウロ・コエーリョの「アルケミスト」の価格を更新し、その特定のレコードを再度選択してみましょう。

BookLastUpdated フィールドに異なる値が表示されるようになったことに注目してください。前に説明したように、TIMESTAMP フィールドには、レコードが挿入されたとき、または最後に変更されたときの値が表示されます。

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

ここでは行いませんが、使用されなくなったレコードを削除することもできます。たとえば、BooksTBL から「The Alchemist 」を削除するとします。

これを行うには、次のように DELETE ステートメントを使用します。

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

UPDATE の場合と同様、 の影響を受ける可能性のあるレコードを表示するには、最初に SELECT を実行することをお勧めします。削除します。

また、WHERE 句と条件 (BookID=6) を追加して、削除する特定のレコードを選択することも忘れないでください。そうしないと、テーブル内のすべての行が削除される危険があります。

2 つ (またはそれ以上) のフィールドを連結したい場合は、CONCAT ステートメントを使用できます。たとえば、「The Alchemist (Paulo Coelho) 」の形式で書籍名と著者を含む 1 つのフィールドと、価格を含む別の列で構成される結果セットを返したいとします。

これには、両方のテーブルで共有される共通フィールド (AuthorID) で AuthorsTBL と BooksTBL の間のJOINが必要です。

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

ご覧のとおり、CONCAT を使用すると、カンマで区切られた複数の文字列式を結合できます。また、連結の結果セットを表すためにエイリアス説明を選択したことにも気づくでしょう。

上記のクエリの出力を以下の画像に示します。

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)

BooksstoreDB データベースにアクセスするためのユーザーを作成する

root を使用してデータベース内のすべての DML 操作を実行するのは悪い考えです。これを回避するには、新しい MariaDB ユーザーアカウント (bookstoreuser という名前にします) を作成し、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)

データベースごとに専用の個別のユーザーを設定すると、単一のアカウントが侵害された場合でもデータベース全体への損害を防ぐことができます。

MySQL に関するその他のヒント

MariaDB プロンプトをクリアするには、次のコマンドを入力して Enter を押します。

MariaDB [BookstoreDB]> \! clear

特定のテーブルの構成を検査するには、次の手順を実行します。

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

例えば、

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)

簡単に調べてみると、BookIsAvailable フィールドが NULL 値を許可していることがわかります。これを許可したくないので、次のようにテーブルを変更します。

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

(列をもう一度表示してください。上の画像で強調表示されているはいがいいえになっているはずです)。

最後に、サーバー上のすべてのデータベースを表示するには、次の手順を実行します。

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)

次の画像は、bookstoreuser として MariaDB プロンプトにアクセスした後の上記のコマンドの結果を示しています (このアカウントが BookstoreDB と < 以外のデータベースを「参照」できないことに注意してください)information_schema (すべてのユーザーが利用可能):

まとめ

この記事では、DML 操作を実行する方法と、MariaDB データベース上にデータベース、テーブル、専用ユーザーを作成する方法について説明しました。さらに、システム/データベース管理者としての作業を容易にするいくつかのヒントを共有しました。

MySQL データベース管理パート – 1
MySQL データベース管理パート – 2
MySQL のパフォーマンスのチューニングと最適化 – パート 3

この記事に関してご質問がございましたら、お気軽にお問い合わせください。お気軽に以下のコメントフォームをご利用ください。