未熟学生エンジニアのブログ

TetsuFeの個人開発ブログ

TetsuFeはテツエフイー と読みます。FlutterやWeb周り全般、チーム開発について語るブログ

常識としてのMySQL(MariaDB)入門〜インストールから基本操作まで・Mac編〜

はじめに

とりあえずステータスとしてMariaDBMySQL)を触っておきたい人や、RailsPythonからデータベースをいじるという方のための、最低限のSQL文とMariaDB自体のインストール方法を説明します。
また、MySQLMariaDBRDBMSと言われるように、データ間に「リレーション」を持たせて管理することができるシステムですが、本記事は「リレーション」に関しては取り扱いません。

環境

macOS High Sierra 10.13
MySQL Ver 15.1 Distrib 10.2.8-MariaDB, for osx10.12 (x86_64)

MariaDBMySQL)のインストール

Homebrewが入っていない方は、先にこのインストールを済ませてください。
インストールはMariaDBというパッケージをインストールします。しかし、MySQLMariaDBはある程度互換性が保たれているので、特に違いはそれほどないと考えて良いです。

$ brew install mariadb

これだけでインストールは完了です。

初期設定

起動時のパスワードなどを設定します。

$ mysql.server start
$ mysql_secure_installation

Enter current password for root (enter for none): 
ここは空欄でEnter

Set root password? [Y/n] 
yを入力
pass: <パスワードを入力>

Remove anonymous users? [Y/n] 
yを入力

Disallow root login remotely? [Y/n] 
yを入力

Remove test database and access to it? [Y/n] 
yを入力

Reload privilege tables now? [Y/n] y

All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

このようになれば成功です。

サーバーの停止

ここで一回MariaDBを停止します。(起動の説明のためなので、ここで必ず停止が必要なわけではないです。)

$ mysql.server stop

Shutting down MySQL
.. SUCCESS! 

サーバーの起動

説明の順番が逆になりましたが、MariaDBサーバーを起動します。
よく起動を忘れていきなりmysqlコマンドを使ってしまいハマる人がいるので、MariaDBを利用する際は必ず以下のコマンドで起動しているか確認しましょう。

$ mysql.server start

Starting MySQL
.171014 22:52:55 mysqld_safe Logging to '/usr/local/var/mysql/***.local.err'.
171014 22:52:55 mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql
SUCCESS! 

PCの起動と同時にmariadb(mysql)を起動したい場合

毎回mariaDBを使うときに$ mysql.server startをするのが面倒という人は、以下のコマンドでPCの起動時に自動的にMariaDBを起動させることもできます。 $ brew services start mariadb

自動起動をやめたい時は
$ brew services stop mariadb

ログイン

$ mysql -u root -p
-pはパスワード入力を行うオプションです。先ほどの初期設定でパスワードを設定した場合は、-pオプションをつけないと
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
と言われてしまいます

ログインすると、

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 33
Server version: 10.2.8-MariaDB Homebrew

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

こんな感じでMariaDB [(none)]>の後にSQL文を書くことで、MariaDBを操作できるようになります。

基本操作の前にMariaDBの簡単な構造の説明

基本操作の説明の前に、MariaDBの簡単な構造の説明をします。

  • ユーザ

    • データベースを操作する。「権限」を持っており、Linuxのようにユーザごとにデータベースの操作をread onlyにするというようなことができる。
  • データベース

    • テーブル(表)の集合
    • テーブル(表)
      • レコードの集合
      • レコード(行)
        • ひとまとまりのデータ。カラム(フィールド・列)の集合
        • カラム(列)
          • 何らかの値(Intなどの型がある)

という構造になっています。図にすると以下のようになります。


(出典:MySQLの基礎 watanaby's OSDN blog)

この例でいうと、データベースは日本の住民のデータベースで、市町村の住民のテーブルが複数含まれており、レコードは「一人一人の住民」、カラムは「名前」、「県名」などになる、というようになっています。

railsなどでは、このデータベースの構造さえ把握していれば、MariaDBmysql)の命令文を知らなくても操作ができたりしますが、構造を把握しておかないとrailsなどのコードが書きにくくなるので、この構造だけは覚えておきましょう。

ユーザの追加

データベースを操作するユーザを追加しましょう。
MariaDBのコマンドはセミコロンが必要なので、忘れないようにしましょう。
また、以下で設定するパスワードは忘れないようにしましょう。

> CREATE USER ユーザ名 IDENTIFIED BY 'パスワード';

以下のようにSQLを実行してみましょう。

MariaDB [(none)]> CREATE USER test_user IDENTIFIED BY 'test_pass';
Query OK, 0 rows affected (0.00 sec)

ユーザ・ホストの一覧を表示

> SELECT user, host FROM mysql.user;

先ほどユーザ「test」を追加したので、以下のような表示になるはずです。

+---------------+-----------+
| user | host |
+---------------+-----------+
| test_user | % |
| root | 127.0.0.1 |
| root | ::1 |
| root | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)

ユーザの削除

削除したい時は以下のようにします。

> DROP USER ユーザ名@ホスト名(省略可);

先ほど作成したユーザを削除してみましょう。

MariaDB [(none)]> DROP USER test_user;
Query OK, 0 rows affected (0.01 sec)

削除できているか確認してみましょう。

MariaDB [(none)]> SELECT user, host FROM mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| root | 127.0.0.1 |
| root | ::1 |
| root | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)

削除したままではあれなので、ユーザーを再作成しましょう。

MariaDB [(none)]> CREATE USER test_user IDENTIFIED BY 'test_pass';
Query OK, 0 rows affected (0.00 sec)

データベースの作成

次にデータベースを作成します。これは以下のような構文になります。

> create database データベース名;

実際にデータベースを作成しましょう。

MariaDB [(none)]> create database test_database;
Query OK, 1 row affected (0.01 sec)

ユーザに権限を付与する

ユーザーは権限を持っており、この権限レベルによっては一部の操作ができなくなります。これは以下のような構文になります。railsなどでは、ここまでがSQL文での操作になると思います。

> GRANT 権限 ON データベース名.* TO ユーザ名;

権限のところには、ALLやSELECT、CREATEなどが入ります。

では、実際にユーザーに権限を付与してみます。ここではさきほど作成したデータベースにのみ権限を付与します。

MariaDB [(none)]> GRANT ALL ON test_database.* TO test_user;

作成したユーザで再ログインする

今はrootでログインしているので、再度先ほど作ったユーザでログインします。

現在のMariaDBクライアントから抜けるには、以下のようにします。

MariaDB [(none)]> quit;
Bye

再度先ほどのユーザ「test_user」でログインします。これは先ほどのrootでログインした時と同じように、

$ mysql -u test_user -p

データベースの指定

データベースは名前空間のように利用できます。
あるデータに対して操作を行いたい場合は、まずそのデータを持つデータベースを使う(USE)ことを指定します。

MariaDB [(none)]> USE データベース名;

ここで、コマンドラインには常にMariaDB [***]> のように表示されているので、現在指定しているデータベースがわかります。

実際に先ほど作成したデータベース「test_database」を指定しましょう。

MariaDB [(none)]> USE test_database;

とすると、

MariaDB [(test_database)]> 

のように変わります。

データベースの基本操作

データベースの基本操作を説明します。これらはWebのCRUDの処理に対応しています。
(以下、「CREATE」などの今まですべて大文字の単語に関しては、小文字でもよいです。
例:CREATE TABLE Person -> create table Person

全て大文字は疲れると思うので、すべて大文字で書いていた単語に関しては、小文字で書いても結構です。)

1. CREATE文

CREATE文は、先ほどのようにDATABESEやUSERだけでなく、テーブル(TABLE)も作ることができます。
ただし、レコードに関しては、後に紹介するINSERT文を使います。

> CREATE TABLE テーブル名 (カラム名 型名, カラム名 型名, ...);

実際にテーブルを作成します。

MariaDB [(test_database)]> CREATE TABLE Person (name VARCHAR(20), age INT);
Query OK, 0 rows affected (0.03 sec)

作成したテーブルの内容を見るには、DESCRIBE テーブル名を使います。

MariaDB [(test_database)]> DESCRIBE Person;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

このように、テーブルの仕様がどのようになっているか確かめることができます。

2. INSERT文

INSERT文は、テーブルに実際のデータ(レコード)を挿入(insert)するためのものです。これは以下のような構文になります。

> INSERT INTO テーブル名 VALUES (値,値...);

値は、文字の場合はシングルクオートで囲みます。数字の場合はそのままでOKです。VALUESの後のデータの1組を「レコード」と言います。

実際にレコードを追加してみましょう。ここでは(name, age)の順番だったので、('花子', 20)というレコードを追加しています。

MariaDB [test_database]> INSERT INTO Person VALUES ('花子',20);
Query OK, 1 row affected (0.00 sec)

もう一つくらい追加しましょう。

INSERT INTO Person VALUES ('太郎',19);
Query OK, 1 row affected (0.01 sec)

3. SELECT文

SELECT文は、レコードを検索してその全部や一部のカラムを取得するものです。以下のような構文になります。

> SELECT カラム名, カラム名... FROM テーブル名 条件(任意);

実際にやってみましょう。

MariaDB [test_database]> SELECT name from Person;
+--------+
| name |
+--------+
| 花子 |
| 太郎 |
+--------+
2 rows in set (0.00 sec)
> SELECT name, age from Person;
+--------+------+
| name | age |
+--------+------+
| 花子 | 20 |
| 太郎 | 19 |
+--------+------+
2 rows in set (0.00 sec)

WHERE句について

SELECT文では、WHERE句を用いた条件指定をすることが多いです。

MariaDB [test_database]> SELECT name, age from Person WHERE age=20;
+--------+------+
| name | age |
+--------+------+
| 花子 | 20 |
+--------+------+
1 row in set (0.00 sec)

WHERE句では = の他に、 < や > なども使えます。

さらに、LIKEを組み合わせることで、キーワード検索をすることができます。

MariaDB [test_database]> SELECT name, age from Person WHERE name LIKE '%郎%';
+--------+------+
| name | age |
+--------+------+
| 太郎 | 19 |
+--------+------+
1 row in set (0.01 sec)

4. UPDATE文

UPDATE文は、レコードを更新する文です。

> UPDATE テーブル名 SET カラム名 = 値 条件(任意);

例えば、すべてのレコードのageを20にしてみます。

MariaDB [test_database]> UPDATE Person SET age = 20;
Query OK, 1 row affected (0.01 sec)
Rows matched: 2 Changed: 1 Warnings: 0

このメッセージからは、2つの行のうち一つが変更されたということのようですが、本当かどうか結果を確認します。これにはSELECT文を使います。

MariaDB [test_database]> SELECT * from Person;
+--------+------+
| name | age |
+--------+------+
| 花子 | 20 |
| 太郎 | 20 |
+--------+------+
2 rows in set (0.00 sec)

しっかり変更されていました!

しかし、すべてのレコードを一気に変更するということはむしろ珍しいと思います。基本的には、WHERE句を使って、特定のレコードにのみ操作することになります。なので、実際にWHERE句を使って、太郎のageだけを19に戻してみましょう。

WHERE句の使い方は覚えていますか?

> (SELECT, UPDATEなどの式) WHERE カラム名 =などの演算子 値

というように書くのでした。では、実際に書いてみましょう。

MariaDB [test_database]> UPDATE Person SET age = 19 WHERE name = '太郎';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
+--------+------+
| name | age |
+--------+------+
| 花子 | 20 |
| 太郎 | 19 |
+--------+------+
2 rows in set (0.00 sec)

太郎だけが19才に戻ってますね!
これでUPDATEまで覚えました。最後はレコードを削除するDELETE文です。

5. DELETE文

DELETE文は、レコードを削除するためのものです。これはSELECT文に近い書き方になり、以下のような構文になります。

> DELETE from テーブル名 条件;
MariaDB [test_database]> DELETE from Person WHERE name = '花子';
Query OK, 1 row affected (0.00 sec)

MariaDB [test_database]> SELECT * from Person;
+--------+------+
| name | age |
+--------+------+
| 太郎 | 19 |
+--------+------+
1 row in set (0.00 sec)

確かに削除できていますね!太郎のレコードだけが残っています!

ここで最後にここで作ったデータベースは削除しておきましょう。

MariaDB [test_database]> DROP DATABASE test_database;
Query OK, 1 row affected (0.02 sec)

MariaDB [test_database]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

test_databaseを削除することができました。ユーザ「test_user」に関しては、後にある演習問題を解くために必要なので、残しておきましょう。
information_schemaはもともとあるものなので気にしなくて大丈夫です。

おまけ 一覧を見る(ls的な使い方)

とりあえずlsと打つように、データベース、テーブル、カラム、レコードの一覧を見たい時は以下のようにします。

1. データベースの一覧をみる

> SHOW DATABASES;

2. テーブルの一覧をみる

> SHOW TABLES;

3. カラムの一覧(テーブルの構成)をみる

> SHOW COLUMNS FROM テーブル名;

もしくは

> DESCRIBE テーブル名

4. レコードの一覧を見る

> SELECT * FROM テーブル名;

MariaDBクライアントから抜ける

> quit;

練習問題

練習問題を用意しましたので、ぜひ一度おさらいとしてやってみてください。特に応用はありませんが、覚えるのには手を動かすのが一番です。
先ほども触れましたが、CREATE文などの今まですべて大文字の単語に関しては、小文字でもよかったのでした。
例:CREATE TABLE Person -> create table Person

全て大文字は疲れると思うので、すべて大文字で書いていた単語に関しては、小文字で書いても結構です。)

0-1. MariaDBクライアントから抜ける
0-2. MariaDBサーバーを停止させる
1. MariaDBクライアントにrootでログイン
2. データベース「SNS」を作成
3. 先ほど作ったユーザ「test_user」にデータベース「SNS」への権限を付与
4. ログアウト
5. 先ほど作ったユーザでログイン
6. 「SNS」データベースの指定
7. 「User」デーブル(カラムにnameとageを持つ)の作成
8. 「太郎, 17才」と「次郎, 18才」のレコードを作る
9. 太郎のageのみを取り出す
10. 太郎のageを19才に変える
11. 18才以下の人のレコードを削除(以下は、<=)

(答えは下にあります)















練習問題の答え

0-1. MariaDBクライアントから抜ける

> quit;

0-2. MariaDBサーバーを停止させる

$ mysql.server stop

0.3 MariaDBサーバーを起動させる

$ mysql.server start

1. MariaDBクライアントにrootでログイン

$ mysql -u root -p

2. データベース「SNS」を作成

MariaDB [(none)]> create database SNS;

3. 先ほど作ったユーザ「test_user」にデータベース「SNS」への権限を付与

MariaDB [(none)]> grant all on SNS.* to test_user; 

4. ログアウト

MariaDB [(none)]> quit;

5. 先ほど作ったユーザでログイン

$ mysql -u test_user -p

6. 「SNS」データベースの指定

MariaDB [(none)]> use SNS;

7. 「User」デーブル(カラムにnameとageを持つ)の作成

MariaDB [SNS]> create table User (name VARCHAR(20), age INT);

8. 「太郎, 17才」と「次郎, 18才」のレコードを作る

MariaDB [SNS]> insert into User values ('太郎', 17);
MariaDB [SNS]> insert into User values ('次郎', 18);

9. 太郎のageのみを取り出す

MariaDB [SNS]> select age from User where name='太郎';

10. 太郎のageを19才に変える

MariaDB [SNS]> update User set age=18 where name='太郎';

11. 18才以下の人のレコードを削除(以下は、<=)

MariaDB [SNS]> delete from Users where age <= 18;
MariaDB [SNS]> select * from Users;
Empty set (0.00 sec)

おわりに

ここまで終わった皆さんは、とりあえず「MariaDBMySQL)を触ったことがある」と言ってよいと思います。
お疲れ様でした!