この記事は？(あらすじ)
Python でデータセットを読み込んで色々計算するプログラムを作成していた。
そこから応用して、Web アプリとして実装し、ブラウザから計算実行、結果確認できるものを作った。
で、あるとき思った。「毎度毎度ファイル全部読んでっての、頭悪くね？」
この機会に、データベースの勉強でもしよう。そっちのがきっとスマート。
というわけで、MySQL のコマンドのお勉強を初めた自分であった。
学んだことを、備忘録がてら綴ったものです。
MySQL の基礎的な機能を支障無く使える程度の量になったかなと思っています。
間違いや不正確な情報もあるかもしれませんが、そこはコメントにてご指摘いただければと思います。
環境
| 項目 | バージョン等 |
|---|---|
| OS | Linux Mint 20.1 Ulyssa 64bit |
| MySQL | Ver 8.0.23-0ubuntu0.20.04.1 for Linux on x86_64 (Ubuntu) |
| Python3 | 3.8.5 |
| Python3 mysql-connector-python | 8.0.23 |
| Python3 mysql-connector-python-rf | 2.2.2 |
SQL, MySQL とは？
データベースでは、データをテーブル (表) の形で管理している。テーブルから必要なデータをすぐ応答してくれるのがデータベースシステム。
MySQL というのは、データベース管理システムの一つ。SQL というのは、データベース操作をするコマンド。
軽く調べると、MySQL の他にも SQLite というものも有名みたい。自分が見つけた情報を超ざっくりまとめると、
- MySQL
- サーバソフト
- 大規模向け
- オープンソース, 非商用なら無料, 商用利用にはライセンスが必要らしい
- SQLite
- こちらはサーバソフトではなくライブラリ
- 中, 小規模向け
- オープンソース (パブリックドメイン), 商用・非商用問わず誰でも使い放題
- セキュリティについては少々注意が必要らしい？
別に言うほど大規模な開発というわけではないが、興味本位で MySQL を使ってみる。
MySQL では、OS とかにログインするのと同じように、ユーザやパスワード設定があり、ファイルパーミッションと同じように、データのアクセス権限設定なんかがある。
SQL データベースでは、システムの中にデータベースが、データベースの中にテーブル (表) が、テーブルの中にキーと値 (レコード) がある。
MySQL では専用の Shell があり、シェルにログインしてコマンドを打つと、色々と操作ができる。言語ライブラリを用いることで、SQL コマンドを自作プログラムから実行し、データの操作ができる。
インストールと初期設定
MySQL 本体のインストールは、APT でこの 2つのパッケージを入れてやるだけで多分ヨシ。
sudo apt install mysql-server mysql-client
mysql-serverは、MySQL を提供するサーバソフトのパッケージ。
mysql-clientは、MySQL へアクセスするクライアントソフトのパッケージ。
サーバマシンとクライアントマシンが別々なら片方ずつのインストールでも多分良いが、ここでは一つのマシン上で練習をするので、両方ともインストール。
続いては MySQL サーバのセットアップ。どうやら、このコマンド一つでセキュリティ関連の初期設定ができるらしい。
sudo mysql_secure_installation
コマンドを実行すると、設定ウィザードが始まる。rootユーザのパスワード設定とか、匿名ユーザやテストデータベースの削除とか、遠隔でのrootログイン拒否設定とかができる様子。
MySQL サーバの起動・停止
Apache とか Samba とかの他のサーバソフトと同様に、Linux Mint (Ubuntu 系)ではsystemctlコマンドで起動・停止を制御できる。
# 起動
sudo systemctl start mysql
# 停止
sudo systemctl stop mysql
# 自動起動オン
sudo systemctl enable mysql
# 自動起動オフ
sudo systemctl disable mysql
MySQL シェルログイン・ログアウト
# root ユーザでログイン
sudo mysql
# 他のユーザでログイン
mysql -u <Username> -p
Enter password: <パスワード入力>
# OS のユーザ名と MySQL のユーザ名が同じときは以下でも可
mysql -p
Enter password: <パスワード入力>
# 他のホスト、ポートを指定
mysql -u <Username> -h <Host> -P <Port> -p
Enter password: <パスワード入力>
-
-u: ユーザ名指定 (未指定なら OS のログイン中のユーザ名) -
-h: ホスト名指定 (未指定なら localhost) -
-P: ポート指定 (未指定なら 3306) -
-p: パスワードを入力してログイン
localhostは、簡単に言えば操作中のマシン自身のこと。ネットワーク経由で別マシンからの操作ではなく、サーバマシン上で操作をしているなら、ホストはlocalhostとすれば良いハズ。
初期設定のままでは、mysql -u root -pではログインできないようになっている。ログインできるようにする方法もあるみたいだが、多分sudoでログインの方が早い気がする。
ログインできると、プロンプトが変わる。
mysql>
この状態になったら、色々と SQL コマンドを打って操作ができる。
コマンドは、大文字でも全て小文字でもどちらで打っても良い。ヘルプとかのリファレンスでは全て大文字で打たれている。
MySQL シェルを終了するには、exit、quitコマンド。どちらを入力しても終了できる。また、後述の SQL コマンドはセミコロン;で 1行のコマンドの終わりを示す文法だが、exit、quit等一部のコマンドは;が不要。
# どちらでも同じ
mysql> exit
mysql> quit
ユーザとパスワードの管理
デフォルトではrootユーザとシステムユーザだけ存在。システムユーザでの人力ログインは普通しない (多分できない)。rootユーザは全権限を持つため、簡易的なテストやメンテナンス等を除いて常時利用するのはよろしくない。
# ユーザの作成 ('<Password>' には登録するパスワードを入れる)
mysql> create user <Username>@<Host> identified by '<Password>';
# ユーザのパスワードの変更 ('NewPassword' には新規登録するパスワードを入れる)
mysql> set password for <Username>@<Host> = 'NewPassword';
## MySQL バージョンが 5.x のときは PASSWORD('NewPassword') だったが
## 8.x だと PASSWORD() が廃止され、このの書き方で良いらしい
# ユーザの削除
mysql> drop user <Username>@<Host>;
MySQL では、システム側でmysqlというデータベースが生成されており、その中のuserというテーブルからユーザ情報を確認することができる。(テーブルデータの呼び出しのコマンドについて詳しくは後述。) 以下のコマンドで登録されているユーザを確認できる。
mysql> select Host, User from mysql.user;
権限の管理
# 権限の確認
mysql> show grants for <Username>@<Host>;
# 権限の付与 (コンマ区切りで複数の権限を指定)
## 特定のデータベース内のテーブル
mysql> grant <AuthType>, ... on <Database>.<Table> to <Username>@<Host>;
## 特定のデータベース内のテーブル全て (* はワイルドカードと言われる)
mysql> grant <AuthType>, ... on <Database>.* to <Username>@<Host>;
## 全てのデータベースとその中のテーブル全て
mysql> grant <AuthType>, ... on *.* to <Username>@<Host>;
# 権限の削除
## ワイルドカードについては上記同様
mysql> revoke <AuthType>, ... on <Database>.<Table> from <Username>@<Host>;
mysql> revoke <AuthType>, ... on <Database>.* from <Username>@<Host>;
mysql> revoke <AuthType>, ... on *.* from <Username>@<Host>;
主な権限 (<AuthType>)
| 権限名 | 内容 |
|---|---|
SELECT |
レコードやコラムの表示 |
INSERT |
レコードの追加 |
UPDATE |
レコードの更新 |
DELETE |
レコードの削除 |
CREATE |
テーブルやデータベースの作成 |
ALTER |
テーブルやデータベースの構造等更新 |
DROP |
テーブルやデータベースの削除 |
この他にも色々な権限があるっぽいが、ひとまずこれが分かっていれば困ることは無さそう。
データベースの管理
# データベースの作成
mysql> create database <Database>;
# データベースの確認
mysql> show databases;
# データベースの削除
mysql> drop database <Database>;
テーブルの管理
テーブル本体の管理
# テーブルの作成
mysql> create table <Database>.<Table> (<ColumnName> <DataType> [Option], ...);
## <ColumnName> <DataType> [Option] をコンマ区切りで続けると、複数のカラムを作成できる
## (カラムの追加も同様)
# データベース内のテーブルの表示
mysql> show tables from <Database>;
# テーブルの削除
mysql> drop table <Table>;
MySQL で使えるデータ型 (<DataType>)
| データ型 | 内容 | 備考 |
|---|---|---|
int |
整数 |
[tiny/small/medium/big] intという型もあり、扱える整数の範囲が変わってくる。 |
float / double |
小数 | 単精度と倍精度。doubleはrealと打っても良い。 |
bit(M) |
ビット | M はビット数。1にすれば、いわゆるブール型に。コマンド上から値を入れるにはb'0001', B'0001', 0b0001と表記。 |
text |
文字列 | 最大文字数を任意に定めるならchar / varchar型。 |
blob |
バイナリ | 最大サイズを任意に定めるならbinary / varbinary型。 |
とりあえず、自分が使いそうなものだけ。他の型や、より詳しい説明は、以下の参照元を。
参照元:
MySQLのデータ型 | MySQLの使い方
MySQLデータ型一覧 (詳細) - Miuran Business Systems
データカラムのオプション (レコード制約) ([Option])
| オプション | 意味 |
|---|---|
primary key |
同一キー内のレコードを重複できなくなり、NULL を入れることができなくなる。 |
unique key |
同一キー内のレコードを重複できなくなる。 |
not null |
キーのレコードに NULL を入れることができなくなる。 |
default <Value> |
レコード追加時、値が未指定だったときのデフォルト値を<Value>にする。未指定なら NULL がデフォルトに。 |
primary keyは、ユーザアカウントID のように、値が無かったり重複されたりしては困るものによく使うと思う。
これも、とりあえず自分が使いそうなものだけ。
テーブルカラムの管理
# テーブルのカラムの表示 (どちらでも同じ結果)
mysql> show desc <Database>.<Table>;
mysql> show columns from <Database>.<Table>;
# テーブルのカラムの詳細表示
mysql> show full columns from <Database>.<Table>;
# テーブルのカラムの追加
mysql> alter table <Table> add (<ColumnName> <DataType> [Option], ...);
# テーブルのカラムの追加 (位置指定)
## 位置指定をするときは、カラム数は 1個でないと不可
mysql> alter table <Table> add <ColumnName> <DataType> [Option] [Position];
# テーブルのカラムの削除
## 複数同時指定は不可？
mysql> alter table <Table> drop column <ColumnName>;
カラム追加位置の指定 ([Position])
| コマンド | 意味 |
|---|---|
| 無し | 最後尾に挿入 |
first |
最初に挿入 |
after <Key> |
<Key>の後に挿入 |
レコードの呼び出し
全表示
# 指定のテーブルの全てのカラムのレコードを確認
mysql> select * from <Database>.<Table>;
カラム指定 (表示列指定)
# 指定のテーブルの特定のカラムのレコードを確認
## 複数のキーのレコードを表示するには、カラム名をコンマ区切りで記述
mysql> select <Column1>, <Column2>, ... from <Database>.<Table>;
条件式指定 (表示行指定)
# <条件式> に 合ったレコードだけ表示される
mysql> select * from <Database>.<Table> where <条件式>;
条件式 (<条件式>)
| 式 | 意味 |
|---|---|
<Column> = <Value> |
指定のカラムの値が<Value>に等しい。 |
<Column> != <Value> |
指定のカラムの値が<Value>に等しくない。 |
<Column> < <Value> |
指定のカラムの値が<Value>よりも低い。 |
>, <=, >= |
略 |
<条件式1> and <条件式2> |
<条件式1>, <条件式2>両方を満たしている。 |
<条件式1> or <条件式2> |
<条件式1>, <条件式2 >いずれかを満たしている。 |
not <条件式> |
<条件式>を満たしていない。 |
例えば、user_id が 3 のデータのみを取得したいときはwhere user_id=3。演算子の両端はkey=3、key = 3のように、スペースを入れても入れなくても良い。
whereは、レコードの変更や削除等で特定のレコードを指定するのにも利用する。
レコードの操作
# レコードの追加
mysql> insert into <Database>.<Table> values ("<Value>", ...);
## テーブルのカラム数と values () への指定データ数が合っていないとエラー
# レコードの変更
mysql> update <Database>.<Table> set <Collumn>="<Value>" where <条件式>;
## where 未指定で全レコードの当該キー変更
# レコードの削除
mysql> delete from <Database>.<Table> where <条件式>;
## where 未指定で全レコード削除
その他メモ
文字データの入力
mysql> insert into <Database>.<Table> values (1, 'any string');
mysql> insert into <Database>.<Table> values (1, "any string");
mysql> show grants for user@localhost;
mysql> show grants for 'user'@'localhost';
mysql> show grants for "user"@"localhost";
mysql> show grants for `user`@`localhost`;
文字列のデータ入力では、", 'が無いとエラー。整数や実数であれば無くても良い。
ユーザとホストは、無し, ", ', `でもどれでも良い。
操作先データベース・テーブルの指定
# データベース名から直接テーブルを指定
mysql> any_command <Database>.<Table>;
# Use コマンドを使ってテーブル指定
mysql> use <Database>;
mysql> any_command <Table>;
SH コマンドで言えば、上の方法は絶対パスで操作先指定、下の方法は相対パスで操作先指定する感じ。useコマンドは、いわゆるcdコマンド的な役割。一つのデータベースに対していくつも操作をするというときには、useコマンドを使うと、打ち込む文字数が減って少し楽になる。
この記事では、全て<Database>.<Table>の形で記述。
カラムとかキーとか
記事内でカラムとキーとで言葉が混合していたが、示すものは同じ。表のヘッド部分、つまりその列のデータの意味を示すもの。MySQL のコマンドでも、Key と呼ぶことと Column と呼ぶこととある。
データ自体はデータとかレコードとか呼ぶが、こちらも 2者の意味はほぼ同じ。
| キー (カラム) 1 | キー (カラム) 2 |
|---|---|
| レコード 1-1 | レコード 1-2 |
| レコード 2-1 | レコード 2-2 |
| ... | ... |
pager コマンドを使うと幸せになれる
データ量が大規模なテーブルを表示させたとき、横幅が足りなくて変に改行が入って見え辛かったり、縦にめちゃめちゃ長かったりするとき、以下のコマンドを使うことで、矢印キーで表示移動できる。(このコマンドはセミコロン不要。)
# 矢印キー移動表示へ切り替え (less コマンドを使って表示)
mysql> pager less -S
# 解除する (stdout 出力へ戻す)
mysql> nopager
パスワードのポリシーに弾かれるとき
MySQL では、パスワードのポリシーの設定ができる。パスワードのポリシー (条件) を満たしていない、弱いパスワードは登録することができないようにできる。rootユーザからの設定だからポリシー回避、というようなことも無い。テスト動作等のために一時的にポリシーの条件を下げたいというときは、以下のコマンド。
# MySQL内環境変数？ (パスワードポリシー設定) を確認
mysql> show variables like 'validate_password%';
# ポリシーを変更
mysql> set global validate_password.policy = LOW;
## MySQL 5.x だと validate_password_policy らしい
## 8.x で変わったらしい
MySQL 内にも独自で環境変数的なものがある。MySQL サーバを再起動すると、設定は元に戻される。永続的に設定するには、これとは別の方法を行う必要があるっぽい。
パスワードポリシーについて、より詳しい話は以下の参照元記事にて。
参照元: Mysql 5.7* パスワードをPolicyに合わせるとめんどくさい件について - Qiita
Python3 から動かしてみる
準備
Python3 から MySQL へアクセスするには、以下の 2つのパッケージを使うそう。
# 必要に応じて、適宜仮想環境を構築してから実行
python3 -m pip install mysql-connector-python
python3 -m pip install mysql-connector-python-rf
MySQL では、デフォルトでcaching_sha2_passwordという認証方式が動いている。しかし、Python のこのモジュールでは、この認証方式に対応していないらしい。mysql_native_passwordだったら対応しているみたいなので、MySQL サーバの認証方式を変更する。
# 認証方式の設定
mysql> alter user <User>@<Host> identified with mysql_native_password by '<Password>';
# 全ユーザの認証方式の表示
mysql> select Host, User, plugin from mysql.user;
データ取得
importする際のモジュール名はmysql。データベースとテーブルは事前に作成。
# coding=UTF-8
# Module import
import mysql.connector
# Connection start
conn = mysql.connector.connect(host="<Host>", user="<User>", password="<Password>", auth_plugin='mysql_native_password')
# dictionary=True とすると辞書型で、無ければタプルで取得される
# csr = conn.cursor(dictionary=True)
csr = conn.cursor()
# Commands execute
csr.execute("select * from plantinfo.dataset")
data = csr.fetchall()
print(data)
# conn.commit() # データの操作が無いときは、commit はしなくても良い
# Quit
conn.close()
データ追加
# coding=UTF-8
# Module import
import mysql.connector
# Connection start
conn = mysql.connector.connect(host="<Host>", user="<User>", password="<Password>", auth_plugin='mysql_native_password')
csr = conn.cursor(dictionary=True)
# Commands execute
csr.execute("insert into plantinfo.dataset values (%d, '%s')" % (1, "desc"))
# data = csr.fetchall() # データの呼び出しでないときは、fetchall するとエラー
# print(data)
conn.commit()
# Quit
conn.close()
おしまいに
ここには書き切れないほどもっと多彩な機能がありますが、MySQL を理解し、最低限使うにはこの程度を知っていれば十分かと思います。以上、僕の勉強ノートでした。
参考
MySQL基本コマンド一覧まとめ - Qiita
よく使うMySQLコマンド集 - Qiita
[MySQL]権限の確認と付与 - Qiita
MySQLのデータ型 | MySQLの使い方
MySQLデータ型一覧 (詳細) - Miuran Business Systems
MySQLでカラムを追加する「ALTER TABLE ~ ADD」 | UX MILK
Mysql 5.7* パスワードをPolicyに合わせるとめんどくさい件について - Qiita