ごく初歩的なMySQLのこと

2017年6月14日

MySQLについての基本的な内容のメモです。オリジナルはGithubに置いてありました。

基本コマンドの抜粋やらポートフォワーディングやら、いわゆるチートシートです。

データフォーマット

データフォーマットはテキストベースのデータ処理との対比がわかりやすい。(深く突っ込むと違うのかもしれないけれど)

テキストベースのデータ処理ではsedやgrep、awkで扱いやすいよう1行に複数フィールドを持つデータが複数行集まって一つのテキストファイルになるのが一般的。

テキストデータベース:
レコード(フィールド ,フィールド, フィールド, …)
レコード(フィールド ,フィールド, フィールド, …)

MySQLではこれに呼応する形で1行にいくつかのカラムを持つデータの集合が複数行集まって2次元の表形式であるTABLEとなり、MySQLでは複数のTABLEをDATABASEとして扱う。さらに複数のDATABASEに跨ったデータ処理もできるが、ここでは割愛。

TABLE
ROW (COLUMN, COLUMN, COLUMN, …)
ROW (COLUMN, COLUMN, COLUMN, …)

コマンド形式

MySQLコマンドは;を一区切りとした命令形式。改行してもコマンド入力は終わらないことが多いが、終わることもあるので行末に;を与える癖をつけよう。基本は大文字らしいけれど小文字を受け付けるOSもある。

ctrl+Cで命令キャンセルしようとすると、MySQLそのものが終了することが多い。

QUIT: 終了

基本中の基本。これさえ知っていればシェルに戻れる!

QUIT;

HELP: ヘルプ表示

どこまで詳細がわかるかは不明だけれど。。。

HELP;

USE: 使用するDATABASEの指定

基本的にコマンドはDATABASEに対する操作なので、まずは対象を明確に。

USE db_name;

SHOW: 情報の表示

DATABASE名やTABLEの構成要素を表示する。(英語なので対象は複数形で指示)

SHOW DATABASES;
SHOW TABLES;
SHOW GRANTS FOR user_name@localhost;

SELECT: COLUMNの表示

TABLEに格納されたCOLUMNを選択して表示できる。ワイルドカードは*。また、WHERE句で対象ROWを絞り込むことができる。

SELECT user, host FROM user;
SELECT * FROM table_name WHERE key_name = 'value';
SELECT field_name FROM table_name WHERE field_name REGEXP "regexp";

CREATE: DATABASEやTABLEの生成

DATABASE, TABLEやUSER(USERもTABLE管理されている)を生成する。

CREATE USER user_name@localhost IDENTIFIED BY 'password';
CREATE DATABASE db_name;
CREATE TABLE table_name(id INT(5) NOT NULL AUTO_INCREMENT, name VARCHAR(20), PRIMARY KEY(id));

DROP: DATABASEやTABLEの削除

DATABASEやTABLEを削除する。

DROP USER user_name@localhost;
DROP DATABASE db_name;
DROP TABLE table_name;

GRANT: 権限の指定

DATABASEにアクセスできるuserと権限の範囲を指定する。

GRANT ALL PRIVILEGES ON db_name.* TO user_name@localhost;

FLUSH: 権限情報の更新

GRANTで書き換えた権限情報を有効化するために更新作業が必要らしい。。。

FLUSH PRIVILEGES;

DESCRIBE: COLUMN項目の表示

TABLEを構成するCOLUMNの内容を表示する。(短縮形はDESC)

DESCRIBE table_name;

INSERT: TABLEデータを新規作成する

新たにデータを追記する。

INSERT INTO table_name (id, name) VALUES (1, 'name');

UPDATE: TABLEデータの内容を更新する

既にあるデータを更新する。

UPDATE table_name SET field_name = REPLACE(field_name, 'target_value', 'replace_value') WHERE field_name = 'value';

DELETE: TABLEデータを削除する

TABLEを残したままに中身のデータのみを削除する。下記だと全てのデータを削除してしまうが、WHERE句で対象を限定してやることも可能。USEでデータベースを指定している場合はdb_name.を省略できる。

DELETE db_name.table_name;
DELETE FROM table_name WHERE field_name = 'value'; 

リモートでMySQLに接続する

SSHのポートフォワーディングでMySQLに接続する方法|株式会社田村倉庫の記事にあるとおり。 MySQLのデフォルトポート3306を開くことなく、SSHでセキュアに接続する方法。

  1. リモートに対してバックグラウンドでSSHポートフォワーディングを実行
    ssh -f -N -C -L 33306:localhost:3306 username@remote.domain -p 22 -i ~/.ssh/keyfile
    
  2. ローカルからリモートのMySQLに接続
    mysql -uroot -p -h 127.0.0.1 --port=33306
    
  3. 後始末(ポートフォワーディング終了)
    ps aux | grep -e "ssh"
    kill xxxx