MySQL – クエリのテーブル保存

SELECT文の結果を、以下の書き方でテーブルに保存できる。

まずSELECTの結果が意図したとおりになっているかどうかを確認した後に、CREATE TABLE文の後ろに書くとよい。

ただし、SELECT *と書くのは要注意。クエリの結果によって、同じフィールド名が繰り返し現れると、テーブル作成時にエラーとなる。

クエリをテーブルに保存するときは、同じフィールド名が重複しないように注意。

 

 

MySQL – CSVファイルへのエクスポート

概要

MySQLへのCSVファイルのインポートについては、ターミナル上でのLOAD DATAコマンドによる方法を整理した。

テーブルやSELECTの結果のエクスポートについては、以下の方法がネット上でも一般的。

ただし、さくらレンタルサーバで上記を実行しようとすると、以下のようにエラーになる。

これはレンタルサーバのユーザディレクトリの権限がrwx---r-xとなっていて、MySQLからの書き込みが制限されているためらしく、レンタルサーバでこの権限を変更することは難しい。

phpMyAdminを使ったエクスポートなら、ブラウザ上での操作でテーブルのダウンロードが可能。

phpMyAdminを起動する

さくらレンタルサーバのコントロールパネルを立ち上げ、「データベースの設定」をクリック。

phpmyadmin-start-1

データベースの設定画面で、「管理ツールログイン」をクリック。

phpmyadmin-start-2

新しいタブでphpMyAdminのログイン画面が立ち上がるので、MySQLのユーザ名とパスワードを入力して「実行する」ボタンを押す。

phpmyadmin-login

phpMyAdminでのエクスポート操作

ログイン後の画面の左にデータベースの一覧があるので、エクスポートするテーブルがあるデータベースを選択。

phpmyadmin-select-database

データベース内のテーブル一覧が表示されるので、エクスポートするテーブルにチェックを入れて、「チェックしたものを」のドロップダウンリストから「エクスポート」を選択。

phpmyadmin-table-selection

エクスポート画面の左でCSVを選択すると、右側がCSVのオプション設定になる。

  • フィールド区切りのデフォルト';'','に変更
  • フィールド囲み記号のデフォルト'"'を残すか、必要に応じて削除
  • その他は適当に
  • 「ファイルに保存する」のチェックを外す
  • エンコーディング変換はしない
  • 「実行する」ボタンで、[テーブル名].csvのファイルがダウンロードされる

phpmyadmin-csv-settings

 

MySQL – 抽出するカラムの限定

SELECT文で結合したテーブルのうち、カラムの一部を除外して選択して表示させたい場合。

例えば以下の2つのテーブルを結合する。

nameを共通フィールドとして結合してSELECT *で表示すると、双方のテーブルのnameカラムが重複している。

一つひとつの[テーブル名].[フィールド名]を書いていけばよいが、フィールド数が多くなると煩雑。

このような場合、[テーブル名].*が使えるので、除外したいフィールドのあるテーブル以外を*指定すると記述量が減る。

 

 

MySQL – テーブル名のエイリアス

エイリアスの縛り

以下のようなテーブルを考える。

ここでnation=’US’のレコードのみ抽出するのに、WHERE句のフィールド名に敢えてテーブル名を付ける。

ここでFROM句でテーブルのエイリアスを指定し、WHERE句とSELECT句ではフルネームのテーブル名を使うとエラーが発生。

そこでWHERE句のテーブル名をエイリアスに変えると、正常に動作。

さらにSELECT句のフィールド名もフルネームのテーブル名を付けるとエラーになって・・・

ここもエイリアスにすると正常に動作する。

これらから、以下のことが分かる。

  • FROM句でテーブル名に別名を付けると、元のテーブル名はオーバーライドされて使えなくなる
  • FROM句で付けたエイリアスは、WHERE句、SELECT句に波及する。

この意味で、エイリアス、別名という呼び方よりは、「テーブル名のオーバーライド」くらいがいいのではないかと思った。

INNER JOINでのエイリアスの使い方

次のような小売側・問屋側の2つのテーブルを準備。

この2つのテーブルを結合して、果物の取引のみを抽出する。

まずwholesaleテーブルのエイリアスを定義するのに、WHERE句のテーブル名もエイリアスに変更して

またshopテーブルのエイリアスを定義するなら、SELECT句で使われているテーブル名まで全て変更しなければならない。

 

 

MySQL – テーブルの結合(内部結合)

デモ用のテーブル

次のような、小売側のテーブルshopと問屋側のテーブルwholesaleを準備する。

小売り側のテーブルには野菜と果物の仕入れリストが登録されている。

問屋側のテーブルには果物の値段表が登録されている。

INNER JOINによる結合

内部結合(等結合)は、双方のテーブルのキーが一致したレコードだけが抽出される。

以下、実行例。

WHEREによる結合

同じ結果を、WHERE句を使っても得られる。

以下、実行例。

異なるテーブルで同じキーの名前が同じときは、キー名の前に[テーブル名].をつけるが、キー名が固有のものであればテーブル名は不要。

 

MySQL – 主なコマンド

データベース操作

データベース一覧の表示
SHOW DATABASES;
データベースへの接続
USE [database]

テーブル操作

テーブル全体の操作

テーブル一覧の表示
SHOW TABLES;
テーブルの作成
CREATE TABLE [table]([column] [type], [column] [type], ...);
テーブルの複製
CREATE TABLE [dest_DB].[new_table] SELECT * FROM [source_DB].[source_table]
テーブル名の変更
ALTER TABLE [table] RENAME TO [new_table_name];
テーブルの削除
DROP TABLE [table];
テーブル構造の表示
DESCRIBE [table] または DESC [table];

カラム操作

カラムの追加
ALTER TABLE [table] ADD [field] [type];
カラムの名称と型の変更
ALTER TABLE [table] CHANGE [field] [new_field_name] [type];
カラムの型のみ変更
ALTER TABLE [table] MODIFY [field] [type];
カラムの削除
ALTER TABLE [table] DROP COLUMN [field];

レコード操作

全レコード・全フィールドの表示
SELECT * FROM [table];
指定したフィールドの表示
SELECT [field], [field], ... FROM [table];
昇順/降順表示
SELECT [fields] FROM [table] ORDER BY [field] ASC/DESC;
レコードの追加
INSERT INTO [table] VALUES([field], [field], ...);
レコードの削除(全削除に注意)
DELETE FROM [table] WHERE [condition]
全レコードの削除
DELETE FROM [テーブル]
レコードの変更(全変更に注意)
UPDATE [table] SET [field]=[value] WHERE [condition]

テーブルの結合

内部結合/等結合~Inner Join

INNER JOIN句
SELECT [fields]
FROM [table_1] INNER JOIN [table_2]
ON ([table_1].)[key_1] = ([table_2].)[key_2]
WHERE句
SELECT [field], [field], ...
FROM [table_1], [table_2]
WHERE ([table_1].)[キー1] = ([table_2].)[key_2]

抽出カラムの選択

結合後のカラムの限定
SELECT ..., [table].* [table].[field], ... from [table], [table], ...

条件式

値がNULL
[field] IS NULL

MySQL – レコードの検索

フィールド選択

全レコードの全フィールド

指定したフィールド

レコードの条件検索

文字列検索

たとえば国名で検索する場合。

LIKEによるあいまい検索

LIKE指定で任意の文字・文字列を含む検索ができる。

任意の一文字

アンダースコア('_')は任意の一文字に相当。

任意長の文字列

任意長(0文字以上)の文字列に対応するのは'%'

数値検索

年齢の範囲で検索する場合。

上限と下限で検索する場合、ANDで複数条件を結ぶ。

同じ検索条件をBETWEENで指定できる。

対象列挙

IN(...)でカンマで区切った値の何れかと一致するレコードが抽出される。

 

 

MySQL – CSVファイルのインポート

概要

ローカルでつくったCSVファイルをさくらレンタルサーバにアップロードしてテーブルに読み込む手順。

  1. CSVファイルを準備する
  2. CSVファイルをアップロードする
  3. MySQLにログオン(ファイルを読めるようにするオプションが必要)
  4. ファイルをテーブルに読み込む。

CSVファイルの準備

  1. 表計算ソフトでCSVファイルを作成して保存
  2. Excelの場合は文字コードにUTF-8を指定できないので、エディタで読み込んで文字コードと改行コードを指定して保存
    • 文字コードはUTF-8
    • 改行コードはLF

CSVファイルのアップロード

さくらレンタルサーバの場合、ファイルのアップロード先は~/www/のディレクトリ下。古パスだと、/home/USERNAME/www

必要に応じてファイルの場所を移動。

MySQLへのログオン

MySQLがローカルファイルから読み込めるよう、--enable-local-infileオプションをつけてログイン。

ファイルの読み込み

  • コマンドはLOAD DATA INFILEコマンド
  • サーバにアップロードしたファイルをローカルで読み込むので”LOAD DATA LOCAL INFILE ...“とする
  • コマンドが長いので、エディタなどで複数行に打ち込んで、コンソールへコピー・ペースト

改行がCR+LFの場合、LINES TERMINATED BY "\r\n"を付加する。

実行例

以下のようなテストファイルを用意してアップロードしておく。

 

 

 

コンソールでのMySQLの文字化け

症状

コンソールからテーブルに、日本語を含んだレコードを追加すると文字化けしてしまう。

たとえば以下のようなデータを持つテーブルがあるとする。

これをそのままSELECTコマンドで表示させると、以下のように文字化け。

WHERE句で無理矢理元の文字列’山田’で探そうとすると、以下のようなエラーが出る。

これはその文字列は存在しているがコードが違うと言われているようだ。

そこでMySQLのcharacter_set群がどのように設定されているかを確認。確認コマンドは以下の形式。

以下、実行結果。

さくらレンタルサーバでDBを追加する際の文字コード指定はUTF-8としていて、ターミナル側の設定はUTF-8だが、ほとんどがujis = EUC_jpになっている。

 

対策

MySQLにログインした後、一度だけ以下を実行する。

以下、実行例。

適切に表示され、chracter_set変数群も_serverを除いて全てUTF-8になっている。

なお、SET CHARSET utf8での文字コード設定を説明しているサイトが殆どだが、この方法だと文字化けはなくなるが、カラム幅が崩れてしまう。この時のcharacter_setの設定値は上と同じに変更されるが表示のされ方が違う。

 

 

MySQL – カラムの追加・変更・削除

カラムの追加

以下のコマンドで最後尾に新しいカラムが追加される。追加されたカラムのフィールド値には初期値が設定される。

以下、実行例。

カラムの変更

名称と型の変更

カラムの名前や型の変更にもALTERコマンドを使う。

以下、実行例。

型のみの変更

型のみの変更はMODIFY

以下、実行例。

 

カラムの削除

以下のコマンドで、指定したカラムを削除できる。

以下、実行例。