解 説

MySQLの操作をphpMyAdminでは行ったことがあるが、コマンドでは操作できないという脱初心者向けのコマンドによるMySQLの基本操作を紹介します。
環境はMacでMAMPを使用したものです。MySQLバージョン: 5.5.42

コマンドでMySQLを使用する準備

Macでコマンドを使用するときはターミナルを使用します。シェルはMac標準のbashを使います。
ターミナルを開いた時点ではホームディレクトリ(/User/ユーザー名)がカレントディレクトリです。
MAMPを通常インストールするとMySQLの実行ファイルの場所は「Applications/MAMP/Library/bin/」です。
本来はMySQLの実行ファイルがある場所にカレントディレクトリを移動する必要がありますが、通常はMAMPをインストールすればパスが通っていますのでカレントディレクトリはどこにあってもMySQLは実行できるはずです。
もし、パスが通ってなくて実行できない場合はcdコマンドで以下に移動します。(パスの通し方は別途解説する予定)

WindowsのXAMPPの場合

WindowsにXAMPPを導入している場合は、コマンドプロンプトでカレントディレクトリを以下に移動してからMySQLにログインします。以降のSQLコマンドはMACの例と同様です。

MySQLの操作方法

今回のMySQLでデータベースの作成とSQL文の練習内容は以下の通りです。
また、今回作成するデータベース名は「item」でテーブルは「beer」と「maker」の2つを作成します。テーブルの内容は以下の図の通りです。

  1. rootでMySQLにログイン
  2. 新規データベースの作成
  3. 新規ユーザー作成
  4. テーブルの作成
  5. SQL文の練習
  6. テーブル構造の変更
  7. テーブル結合

rootでMySQLにログイン

rootでMySQLにログインするには以下のようにします。

ログインに成功すると以下のようになります。

ここから先はmysqlのSQLコマンドになります。

もしMySQLからログアウトする必要が出たときは「exit」コマンドを使用します。

既存のデータベースを確認するSQLコマンド

実行結果

SQLコマンドは命令文の後をセミコロン「;」で終わります。
セミコロンをつけないで「return」キーを押すと改行になりますので注意してください。矢印が改行の意味になります。(下図)

間違えて改行した場合はそのまま次の行にセミコロンを入れて「return」キーを押して命令文を実行します。

新規データベースの作成

新規データベースを作成するには次のSQLコマンドを使います。

重要:新規データベースを作成するときに、上記のようなコマンドでデータベースを作成するとデータに日本語が含まれていたら文字化けする可能性があります。
それを防止する為には、データベースを作成するときに、文字コードの指定と照合順序を指定しておくことです。データベースを作成してから変更するのは面倒ですから、必ずこの指定はするようにした方が良いでしょう。

文字コードの設定は、mysql5.5.3以降から絵文字なども使用できる「utf8mb4」が使用可能となっています。今後は「utf8mb4」が使用される機会が増えてくると思われます。ただし「utf8」と混在するとトラブルが予想されますので注意が必要です。

文字コードと照合順序を指定して新規データベース作成

「character set」が 文字コードの指定です。通常は「utf8」を指定します。「utf-8」とするとエラーになります。
また、collateが照合順序になります。通常は「utf8_general_ci」とすると良いでしょう。照合順序を指定していないとリレーショナルの仕組みを使うときなどに不具合がでる可能性があります。

データベースの削除

データベース削除は慎重に行いましょう。
データベースを消すSQLコマンド

ユーザー作成

「dororo」というユーザーを作成して、パスワードは今回はサンプルですから「1234」とします。(実運用では推測されにくいパスワードにします)
さらに「item」データベースに権限を与えます。

すでにユーザーを作成済みで権限の付与だけする場合は、 一旦rootになって以下を実行します。

別のデータベースを使用

現在「item」データベースを操作していて、同じユーザーで別の「test」データベースを操作する必要が出た場合は、USE [データベース名];コマンドを使用します。

次の例はtestデータベースに変更するコマンドです。

テーブルの作成

beerテーブル作成SQLコマンド

makerテーブル作成SQLコマンド

テーブルの確認SQLコマンド

テーブルの内容を確認するSQLコマンド

テーブルの削除SQLコマンド

SQL文の練習

SQL文を学習するにはCRUDに基づいた操作をマスターすることが基本になります。
CRUDとはシステムに必要な4つの主要機能である「生成(Create)」「読み取り(Read)」「更新(Update)」「削除(Delete)」を表したもので、データベース作成において必要になる基本機能となります。
もう少し具体的に表すと、「生成(CreateつまりINSERT文)」「読み取り(ReadつまりSELECT文)」「更新(UPDATE文)」「削除(DELETE文)」をしっかりマスターしておけば良いことになります。

*通常SQL文の命令は慣例的に大文字で記述しますが、文法上は大文字でも小文字どちらでも構いません。今回コマンド入力は全て小文字で入力した例です。

INSERT文

INSERT文はCRUDの「C」createにあたるもので、テーブルに新しい行を追加するときに使用します。つまりデータの入力に使います。

単一のレコードを追加する場合のSQLコマンド

複数のレコードを追加する場合のSQLコマンド

beerテーブルに値を追加SQLコマンド

makerテーブルに値を追加SQLコマンド

SELECT文

SELECT文はCRUDのReadにあたるもので、レコード確認のSQLコマンドになります。

全てのレコードの確認方法
現実的には例えば数万のレコードを全部読み出すことになりますので、データベースにかなりの負荷がかかるコマンドです。
実際に使用することはあまりないかもしれません。

一部レコードの確認SQLコマンド
こちらはWHERE句で条件を設定して特定のデータを読み出します。通常はこちらが多く使用されるものです。

UPDATE文

UPDATE文はCRUDのUpdateにあたるもので、テーブルにすでにあるレコードの更新するSQLコマンドになります。

レコードの更新SQLコマンド

DELETE文

DELETE文はCRUDのDeleteにあたるもので、テーブルにすでにあるレコードを削除するSQLコマンドになります。

重要:DELETE文を条件なしで事項すると当然テーブルの全てのデータが削除されます。現実的にはこのコマンドを使用することはまずありません。条件の指定はくれぐれも慎重に行う必要があります。

条件付きのレコードの削除SQLコマンド

様々な条件

レコードの抽出条件SQLコマンド

and条件SQLコマンド

曖昧条件SQLコマンド
任意の複数文字 %

任意の1文字 _ SQLコマンド

並べ替えと集計

抽出レコードの並べ替えSQLコマンド

抽出レコードの数を取得SQLコマンド
limit 取得数;

開始位置と取得数(開始位置は0から始まる)SQLコマンド

データの集計
レコード件数の取得SQLコマンド

指定フィールドの合計値SQLコマンド

指定フィールドの平均値SQLコマンド

テーブル構造の変更

テーブル名の変更SQLコマンド

フィールドの追加SQLコマンド

フィールド追加後のデータ追加はinsert intoではなくupdateを使う

フィールド名の変更SQLコマンド

データ型のみ変更したい場合SQLコマンド

フィールドを削除SQLコマンド

テーブル結合

内部結合

指定したフィールドの値が一致したら取得

内部結合は指定したフィールドの値が一致した内容のみしか表示されません。
今回のサンプルでは商品テーブルの中のデータはサントリーの商品がありませんので
内部結合で結合したテーブルにはサントリーの社名は出てきません。

外部結合

テーブルの指定したフィールドの値が一致するデータに加えてどちらかのテーブルにしか存在しないデータについても取得する
leftとrightの結果の違いを確認してください。

left outer joinを使用

right outer joinを使用