MySQL – インストール

2021-02-15 / tau / コメントする

ダウンロードまで

MySQLサイト、下方のダウンロードで”MySQL Community Serverをクリック
MySQL Installer for Windowsをクリック
Web版ではなく約400MBのファイル版をダウンロード
- 64bit版でもWindows(x86, 32-bit)
Loginページで”No thanks, just start my download”をクリック
ダウンロードしたmsiファイルを実行

インストーラー実行

Developer DefaultのままNext
いくつかManualインストールが必要なもの
- VisualStudio
- Python3.x
- これらをインストール後、Nextを押す前にExecuteを押して他の項目も自動解決
Product Configuration→Next
Type and Networking
- TCP/IP
- PORT: 33060のまま
- X-Protocol Port: 33060のまま
Authentication Method
- Use Strong Password Encryption …
Root Password
- パスワードを入力
Windows Service
- Configure MySQL Server as a Windows Service→チェックのまま
- Start the MySQL Server at System Startup→チェックのまま
- Standard System Account→ラジオボタン選択のまま
Apply Configuration→Execute→Finish
Product Configuration→Next
MySQL Router Configuration→Next
Product Configuration→Next
Connect To Server
- root/パスワード入力→Check→Connection succeeded
Apply Configuration→Execute→Finish
Product Configuration→Next
Installation Complete
- Start MySQL Workbench after setup→チェックを外す
- Start MySQL Shell after setup→チェックを外す
- Finish

MySQL – ユーザーの作成・権限設定

2021-02-08 / tau / コメントする

ユーザー一覧の確認

ユーザー一覧のカラムの確認

SELECT COLUMNS FROM mysql.user;

ユーザー一覧の表示

SELECT user, host FROM mysql.user;

mysql> SELECT user, host FROM mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+

mysql> SELECT user, host FROM mysql.user;

+------------------+-----------+

| user | host |

+------------------+-----------+

| mysql.infoschema | localhost |

| mysql.session | localhost |

| mysql.sys | localhost |

| root | localhost |

+------------------+-----------+

ユーザーの作成

CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

権限の設定

権限の確認

SHOW GRANTS FOR 'user'@'localhost';

mysql> SHOW GRANTS FOR 'user'@'localhost';
+----------------------------------------------------------------+
| Grants for testuser@localhost                                  |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `user`@`localhost`                       |
| GRANT ALL PRIVILEGES ON `database`.* TO `user`@`localhost`     |
+----------------------------------------------------------------+

mysql> SHOW GRANTS FOR 'user'@'localhost';

+----------------------------------------------------------------+

| Grants for testuser@localhost |

+----------------------------------------------------------------+

| GRANT USAGE ON *.* TO `user`@`localhost` |

| GRANT ALL PRIVILEGES ON `database`.* TO `user`@`localhost` |

+----------------------------------------------------------------+

権限付与

GRANT permission ON database . table TO 'user'@'localhost';

permission

ALL / ALL PRIVILEGES: 指定したデータベースまたは全体へのフルアクセス
CREATE: 新しいテーブル・データベースの作成
DROP: テーブル・データベースを削除
DELETE: テーブルからの行の削除
INSERT: テーブルへの行の挿
SELECT: SELECTコマンドによるデータの読み取り
UPDATE: テーブルの行の更新
GRANT OPTION: 他のユーザーの権限の設定

MYSQL – インポートできない

2021-01-10 / tau / コメントする

文字コード

ローカルにインストールしたMySQL/phpMyAdminからクラウド上の別のphpMyAdminにテーブルをエクスポート、インポートしようとしたところ、’Unknown collation: ‘utf8mb4_unicode_520_ci’でインポートできなかった。元のMySQLがVer8でインポート先がVer5だったためらしい。テキストのSQLの文字コード部分を変更して読み込み。

utf8mb4→utf8
utf8mb4_unicode_520_ci→utf8_general_ci

PHP/MySQL – トランザクション確認(PDO)

2021-01-10 / tau / コメントする

概要

PHPのオブジェクト型(PDO)でMySQLのトランザクションを確認。

トランザクション中の動作はデータベースステートメントには設定されるが、トランザクション終了後のコミット／ロールバックによって設定結果がデータベースに反映されるかどうかが決まる。

確認コード

5行目でトランザクションでコミットフラグをtrueにセット
通常モードで’one’を書き込み
14行目でトランザクション開始
‘two’, ‘three’を書き込み
25行目でコミット、トランザクション確定
- 通常モードへは自動的に復帰

<?php
$dsn = 'mysql:host=localhost;charset=utf8mb4;dbname=testdb';
$pdo = new PDO($dsn, 'testuser', 'test', [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

$commit = true;

echo '>WRITE one<br>';
$pdo->query("INSERT INTO test_table (data) VALUES ('one')");

$stmt = $pdo->query("SELECT data FROM test_table");
while ($record = $stmt->fetch(PDO::FETCH_ASSOC)) { echo $record['data'], '<br>'; }

echo '>TRANSACTION START<br>';
$pdo->beginTransaction();

echo '>WRITE two, three<br>';
$pdo->query("INSERT INTO test_table (data) VALUES ('two')");
$pdo->query("INSERT INTO test_table (data) VALUES ('three')");

$stmt = $pdo->query("SELECT data FROM test_table");
while ($record = $stmt->fetch(PDO::FETCH_ASSOC)) { echo $record['data'], '<br>'; }

echo '>TRANSACTION END<br>';
if ($commit) {
  $pdo->commit();
} else {
  $pdo->rollBack();
}

$stmt = $pdo->query("SELECT data FROM test_table");
while ($record = $stmt->fetch(PDO::FETCH_ASSOC)) { echo $record['data'], '<br>'; }

echo '>WRITE four<br>';
$pdo->query("INSERT INTO test_table (data) VALUES ('four')");

$stmt = $pdo->query("SELECT data FROM test_table");
while ($record = $stmt->fetch(PDO::FETCH_ASSOC)) { echo $record['data'], '<br>'; }

$pdo = null;
?>

<?php

$dsn = 'mysql:host=localhost;charset=utf8mb4;dbname=testdb';

$pdo = new PDO($dsn, 'testuser', 'test', [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

$commit = true;

echo '>WRITE one ';

$pdo->query("INSERT INTO test_table (data) VALUES ('one')");

$stmt = $pdo->query("SELECT data FROM test_table");

while ($record = $stmt->fetch(PDO::FETCH_ASSOC)) { echo $record['data'], ' '; }

echo '>TRANSACTION START ';

$pdo->beginTransaction();

echo '>WRITE two, three ';

$pdo->query("INSERT INTO test_table (data) VALUES ('two')");

$pdo->query("INSERT INTO test_table (data) VALUES ('three')");

$stmt = $pdo->query("SELECT data FROM test_table");

while ($record = $stmt->fetch(PDO::FETCH_ASSOC)) { echo $record['data'], ' '; }

echo '>TRANSACTION END ';

if ($commit) {

$pdo->commit();

} else {

$pdo->rollBack();

}

$stmt = $pdo->query("SELECT data FROM test_table");

while ($record = $stmt->fetch(PDO::FETCH_ASSOC)) { echo $record['data'], ' '; }

echo '>WRITE four ';

$pdo->query("INSERT INTO test_table (data) VALUES ('four')");

$stmt = $pdo->query("SELECT data FROM test_table");

while ($record = $stmt->fetch(PDO::FETCH_ASSOC)) { echo $record['data'], ' '; }

$pdo = null;

確認結果

以下、出力を見ながらトランザクションの動作を確認。

通常モードでは普通に書き込み
トランザクション開始後、データベースステートメントの内容は指示通り設定
トランザクション終了後、データベース内容の変更が確認できる
通常モード復帰後も普通に書き込み

以下は出力結果で、終了後のデータベースの内容は’one’, ‘two’, ‘three’, ‘four’となる。

>WRITE one
one
>TRANSACTION START
>WRITE two, three
one
two
three
>TRANSACTION END
one
two
three
>WRITE four
one
two
three
four

>WRITE one

one

>TRANSACTION START

>WRITE two, three

one

two

three

>TRANSACTION END

one

two

three

>WRITE four

one

two

three

four

次にコミットフラグを$commit=falseにすると、commit()メソッドではなくrollback()メソッドが実行され、実行結果は以下のようになる。

通常モードで普通に’one’が書き込まれる
トランザクションに入り、データベースステートメントには’two’, ‘three’が設定される
トランザクション終了後にロールバックされると、データベースステートメントがデータベースに反映されてもトランザクション中の’two’, ‘three’は書き込まれない
通常モードに復帰後’four’が書き込まれる

>WRITE one
one
>TRANSACTION START
>WRITE two, three
one
two
three
>TRANSACTION END
one
>WRITE four
one
four

>WRITE one

one

>TRANSACTION START

>WRITE two, three

one

two

three

>TRANSACTION END

one

>WRITE four

one

four

PHP/MySQL – トランザクション確認(mysqli)

2021-01-10 / tau / コメントする

概要

PHPの手続き型(mysqli)でMySQLのトランザクションを確認。

トランザクション中の動作はデータベースリンクには設定されるが、トランザクション終了後のコミット／ロールバックによって設定結果がデータベースに反映されるかどうかが決まる。

確認コード

7行目でトランザクションでコミットフラグをtrueにセット
通常モードで’one’を書き込み
20行目でトランザクション開始
‘two’, ‘three’を書き込み
33行目でコミット、トランザクション確定
41行目で通常モードに復帰

<?php
$host = 'localhost';
$user = 'testuser';
$pass = 'test';
$dbname = 'testdb';

$commit = true;

$dblink = mysqli_connect($host, $user, $pass, $dbname);
mysqli_set_charset($dblink, 'UTF8');

echo '>WRITE one<br>';
$query = "INSERT INTO test_table (data) VALUES ('one')";
mysqli_query($dblink, $query);

$result = mysqli_query($dblink, "SELECT data FROM test_table");
while ($record = mysqli_fetch_assoc($result)) { echo $record['data'], '<br>'; }

echo '>TRANSACTION START<br>';
mysqli_autocommit($dblink, false);

echo '>WRITE two, three<br>';
$query = "INSERT INTO test_table (data) VALUES ('two')";
mysqli_query($dblink, $query);
$query = "INSERT INTO test_table (data) VALUES ('three')";
mysqli_query($dblink, $query);

$result = mysqli_query($dblink, "SELECT data FROM test_table");
while ($record = mysqli_fetch_assoc($result)) { echo $record['data'], '<br>'; }

echo '>TRANSACTION END<br>';
if ($commit) {
  mysqli_commit($dblink);
} else {
  mysqli_rollback($dblink);
}

$result = mysqli_query($dblink, "SELECT data FROM test_table");
while ($record = mysqli_fetch_assoc($result)) { echo $record['data'], '<br>'; }

mysqli_autocommit($dblink, true);

echo '>WRITE four<br>';
$query = "INSERT INTO test_table (data) VALUES ('four')";
mysqli_query($dblink, $query);

$result = mysqli_query($dblink, "SELECT data FROM test_table");
while ($record = mysqli_fetch_assoc($result)) { echo $record['data'], '<br>'; }

mysqli_free_result($result);
mysqli_close($dblink);
?>

<?php

$host = 'localhost';

$user = 'testuser';

$pass = 'test';

$dbname = 'testdb';

$commit = true;

$dblink = mysqli_connect($host, $user, $pass, $dbname);

mysqli_set_charset($dblink, 'UTF8');

echo '>WRITE one ';

$query = "INSERT INTO test_table (data) VALUES ('one')";

mysqli_query($dblink, $query);

$result = mysqli_query($dblink, "SELECT data FROM test_table");

while ($record = mysqli_fetch_assoc($result)) { echo $record['data'], ' '; }

echo '>TRANSACTION START ';

mysqli_autocommit($dblink, false);

echo '>WRITE two, three ';

$query = "INSERT INTO test_table (data) VALUES ('two')";

mysqli_query($dblink, $query);

$query = "INSERT INTO test_table (data) VALUES ('three')";

mysqli_query($dblink, $query);

$result = mysqli_query($dblink, "SELECT data FROM test_table");

while ($record = mysqli_fetch_assoc($result)) { echo $record['data'], ' '; }

echo '>TRANSACTION END ';

if ($commit) {

mysqli_commit($dblink);

} else {

mysqli_rollback($dblink);

}

$result = mysqli_query($dblink, "SELECT data FROM test_table");

while ($record = mysqli_fetch_assoc($result)) { echo $record['data'], ' '; }

mysqli_autocommit($dblink, true);

echo '>WRITE four ';

$query = "INSERT INTO test_table (data) VALUES ('four')";

mysqli_query($dblink, $query);

$result = mysqli_query($dblink, "SELECT data FROM test_table");

while ($record = mysqli_fetch_assoc($result)) { echo $record['data'], ' '; }

mysqli_free_result($result);

mysqli_close($dblink);

確認結果

以下、出力を見ながらトランザクションの動作を確認。

通常モードでは普通に書き込み
トランザクション開始後、データベースリンクの内容は指示通り設定
トランザクション終了後、データベース内容の変更が確認できる
通常モード復帰後も普通に書き込み

以下は出力結果で、終了後のデータベースの内容は’one’, ‘two’, ‘three’, ‘four’となる。

>WRITE one
one
>TRANSACTION START
>WRITE two, three
one
two
three
>TRANSACTION END
one
two
three
>WRITE four
one
two
three
four

>WRITE one

one

>TRANSACTION START

>WRITE two, three

one

two

three

>TRANSACTION END

one

two

three

>WRITE four

one

two

three

four

次にコミットフラグを$commit=falseにすると、msqli_commit()ではなくmsqli_rollback()が実行され、実行結果は以下のようになる。

通常モードで普通に’one’が書き込まれる
トランザクションに入り、データベースリンクには’two’, ‘three’が設定される
トランザクション終了後にロールバックされると、データベースリンクがデータベースに反映されてもトランザクション中の’two’, ‘three’は書き込まれない
通常モードに復帰後’four’が書き込まれる

>WRITE one
one
>TRANSACTION START
>WRITE two, three
one
two
three
>TRANSACTION END
one
>WRITE four
one
four

>WRITE one

one

>TRANSACTION START

>WRITE two, three

one

two

three

>TRANSACTION END

one

>WRITE four

one

four

MySQL – チートシート

2021-01-02 / tau / コメントする

表示

縦表示にする

\G

通常;(セミコロン)で終わるところを\Gとすることで、各カラムを縦方向順番に表示してくれる。

追加

レコードの追加

フィールドを指定して追加する

INSERT INTO table(col1, col2, ...) VALUES (val1, val2, ...);

※フィールド、データが1つの場合も()が必要。

フィールドの追加

ALTER TABLE table ADD col type init;

ALTER TABLE table ADD col type NOT NULL DEFAULT default;

削除

レコードの削除

条件を指定して削除する

DELETE FROM table WHERE condition;

conditionの条件に合う行をtableから削除する。WHERE句を指定しない場合はtableから全レコードが削除されてしまうので注意。

全レコードを削除する

DELETE FROM table;

フィールドの削除

ALTER TABLE table DROP FROM col;

ユーザーの追加

CREATE USER 'user'@'host' IDENTIFIED BY 'password';

検索・抽出

SELECT文を使う。SELECT文では()は不要。

列を選択して全レコードを抽出

全ての列を選択して全レコードを抽出する

SELECT * FROM table;

列を指定して全レコードを抽出する

SELECT col1, col2, ... FROM table;

条件によるレコード抽出

数値条件

SELECT col1, col2, ... FROM table WHERE col <op> num;

演算子<op>の種類

`=`	等しい
`<>`	等しくない
`<, >`	より小さい、より大きい
`<=, >=`	以上、以下
`A BETWEEN B AND C`	AはB以上C以下
`A IN (val1, val2, ...)`	Aはリストの要素val1, val2, …の何れか
`S LIKE 'string'`	文字列Sが’string’に合致

文字列条件

SELECT col1, col2, ... FROM table WHERE col LIKE str;

ワイルドカードの種類

`%`	0文字以上の文字列
`_`	任意の1文字(半角・全角を問わない)

※後方一致、部分一致は処理速度が遅い。

論理演算子

`! a, NOT a`	aの否定
`a AND b, a && b`	aかつb
`a OR b, a \|\| b`	aまたはb
`a XOR b`	aとbの排他論理和

データの変更

条件に合うレコードの指定したフィールドを変更

UPDATE table SET col = new_value WHERE condition;

WHERE句をセットしないと全レコードのデータが変更されてしまうので、基本的にWHERE句でレコードを絞り込む。

ソート～並べ替え

SELECT col1, col2, ... FROM table ORDER BY col ASC/DESC;

colのデータで昇順／降順に並べ替えた結果を返す。

複数のカラムを指定すると、先頭から順にグルーピングされてソート(並べ替え～ORDER BY)。

グループ化

SELECT col, function(col), ... FROM table GROUP BY col;

GROUP BYで指定したフィールドの内容によってレコードをグループ化する。

フィールド指定の場合はグループ先頭のフィールドが抽出される
フィールド関数指定の場合はグループごとに関数が適用される

Tips/Troubleshooting

エイリアス

SELECT ... FROM table AS alias;

FROMやJOINでテーブルを指定する際にエイリアスを定義し、それより前のSELECTでもエイリアスを使うことができる。以下は2つのテーブルの内部結合の例。

SELECT c.customer_name, c.customer_mail, o.item, o.quantity, o.order_date
FROM customer_table AS c
JOIN order_table AS o ON&nbsp; c.customer_id = o.customer_id;

SELECT c.customer_name, c.customer_mail, o.item, o.quantity, o.order_date

FROM customer_table AS c

JOIN order_table AS o ON  c.customer_id = o.customer_id;

また、フィールドにエイリアスを定義できる。フィールド演算結果にエイリアスを定義すればフィールド名として扱われる。

SELECT COL AS alias FROM ...

エイリアスを定義した場合、元の名前は使えなくなる。

サブクエリー

SELECT alias.col, ... FROM (SELECT ... FROM ...) as alias;

SELECTによるクエリーをサブクエリーとして親のSELECTの検索対象とする場合は、サブクエリーにエイリアスが必要。このときサブクエリーのSELECTの要素にエイリアスを定義して親のクエリーで参照可能。

空白を含むフィールド名

フィールド名に空白が含まれる場合はバッククォートで囲む。

SELCT `COL 1`, `COL 2`, ... FROM ...

演算

COUNT～レコード数のカウント

SELECT COUNT(*) FROM table;
SELECT COUNT(col) FROM table;

*で指定した場合はnullもカウント、フィールドを指定した場合はnullはカウントされない（他のフィールドも書くとそれらは1レコード目の値となる）。

結合

内部結合

JOIN～2つのテーブルの内部結合

SELECT table.col, ... FROM table1 JOIN table2 ON table1.col1 = table2.col2;

table1のcol1とtable2のcol2が等しいレコードのみが両テーブルから抽出されて結合される。

JOIN～3つのテーブルの結合

SELECT table.col, ...
FROM table1
JOIN table2 ON table1.col1 = table2.col2
JOIN table3 ON table2.col2 = table3.col3;

tabe1とtable2を内部結合
その結合結果とtable3を内部結合

外部結合

LEFT JOIN

SELECT table.col, ... FROM table1 LEFT JOIN table2 ON table1.col1 = table2.col2;

table1のすべてのレコードと、table2のレコードのうちcol2がtable1のcol1に等しいレコードが抽出され結合される
table1にはあるがtable2にはないレコードのtable2.col2の値はnullになる

RIGHT JOIN

SELECT table.col, ... FROM table1 RIGHT JOIN table2 ON table1.col1 = table2.col2;

table2のすべてのレコードと、table1のレコードのうちcol1がtable2のcol2に等しいレコードが抽出され結合される
table2にはあるがtable1にはないレコードのtable1.col1の値はnullになる

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

2016-11-27 / tau / コメントする

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

CREATE TABLE [talbe]
SELECT [fields] FROM [tables]
...

CREATE TABLE [talbe]

SELECT [fields] FROM [tables]

...

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

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

mysql> select * from test, test_sex where test.name = test_sex.name;
+-------+------+--------+-------+--------+
| name  | age  | nation | name  | sex    |
+-------+------+--------+-------+--------+
| John  |   35 | US     | John  | male   |
| Alex  |   40 | NZ     | Alex  | male   |
| Luice |   29 | US     | Luice | male   |
| Alice |   23 | UK     | Alice | female |
| Sala  |   25 | JP     | Sala  | female |
+-------+------+--------+-------+--------+
5 rows in set (0.00 sec)

mysql> create table test_new                                                              
    -> select * from test, test_sex where test.name = test_sex.name;
ERROR 1050 (42S01): Table 'test_new' already exists

mysql> select * from test, test_sex where test.name = test_sex.name;

+-------+------+--------+-------+--------+

+-------+------+--------+-------+--------+

+-------+------+--------+-------+--------+

5 rows in set (0.00 sec)

mysql> create table test_new

-> select * from test, test_sex where test.name = test_sex.name;

ERROR 1050 (42S01): Table 'test_new' already exists

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

mysql> create table test_new
    -> select test.name, age, nation, sex
    -> from test, test_sex
    -> where test.name = test_sex.name;
Query OK, 5 rows affected (0.01 sec)                                                      
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from test_new;
+-------+------+--------+--------+
| name  | age  | nation | sex    |
+-------+------+--------+--------+
| John  |   35 | US     | male   |
| Alex  |   40 | NZ     | male   |
| Luice |   29 | US     | male   |
| Alice |   23 | UK     | female |
| Sala  |   25 | JP     | female |
+-------+------+--------+--------+
5 rows in set (0.00 sec)

mysql>

mysql> create table test_new

-> select test.name, age, nation, sex

-> from test, test_sex

-> where test.name = test_sex.name;

Query OK, 5 rows affected (0.01 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from test_new;

+-------+------+--------+--------+

| name | age | nation | sex |

+-------+------+--------+--------+

| John | 35 | US | male |

| Alex | 40 | NZ | male |

| Luice | 29 | US | male |

| Alice | 23 | UK | female |

| Sala | 25 | JP | female |

+-------+------+--------+--------+

5 rows in set (0.00 sec)

mysql>

さくらサーバー – MySQL – CSVファイルへのエクスポート

2016-11-25 / tau / コメントする

概要

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

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

SELECT * FROM [table]
  INTO OUTFILE "[outfile]"
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"';

SELECT * FROM [table]

INTO OUTFILE "[outfile]"

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"';

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

ERROR 1045 (28000): Access denied for user '[user]'@'%' (using password: YES)

1	ERROR 1045 (28000): Access denied for user '[user]'@'%' (using password: YES)

これはレンタルサーバのユーザディレクトリの権限が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 – テーブル名のエイリアス

2016-11-04 / tau / コメントする

エイリアスの縛り

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

mysql> SELECT * FROM test;
+-------+------+--------+
| name  | age  | nation |
+-------+------+--------+
| John  |   35 | US     |
| Alex  |   40 | NZ     |
| Luice |   29 | US     |
| Alice |   23 | UK     |
| Sala  |   25 | JP     |
+-------+------+--------+
5 rows in set (0.00 sec)

mysql>

mysql> SELECT * FROM test;

+-------+------+--------+

| name | age | nation |

+-------+------+--------+

| John | 35 | US |

| Alex | 40 | NZ |

| Luice | 29 | US |

| Alice | 23 | UK |

| Sala | 25 | JP |

+-------+------+--------+

5 rows in set (0.00 sec)

mysql>

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

mysql> SELECT name, nation FROM test WHERE test.nation='US';
+-------+--------+
| name  | nation |
+-------+--------+
| John  | US     |
| Luice | US     |
+-------+--------+
2 rows in set (0.00 sec)

mysql>

mysql> SELECT name, nation FROM test WHERE test.nation='US';

+-------+--------+

| name | nation |

+-------+--------+

| John | US |

| Luice | US |

+-------+--------+

2 rows in set (0.00 sec)

mysql>

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

mysql> SELECT name, nation FROM test t WHERE test.nation='US';
ERROR 1054 (42S22): Unknown column 'test.nation' in 'where clause'
mysql>

mysql> SELECT name, nation FROM test t WHERE test.nation='US';

ERROR 1054 (42S22): Unknown column 'test.nation' in 'where clause'

mysql>

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

mysql> SELECT name, nation FROM test t WHERE t.nation='US';
+-------+--------+
| name  | nation |
+-------+--------+
| John  | US     |
| Luice | US     |
+-------+--------+
2 rows in set (0.00 sec)

mysql>

mysql> SELECT name, nation FROM test t WHERE t.nation='US';

+-------+--------+

| name | nation |

+-------+--------+

| John | US |

| Luice | US |

+-------+--------+

2 rows in set (0.00 sec)

mysql>

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

mysql> SELECT test.name, test.nation FROM test t WHERE t.nation='US';
ERROR 1054 (42S22): Unknown column 'test.name' in 'field list'
mysql>

mysql> SELECT test.name, test.nation FROM test t WHERE t.nation='US';

ERROR 1054 (42S22): Unknown column 'test.name' in 'field list'

mysql>

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

mysql> SELECT t.name, t.nation FROM test t WHERE t.nation='US';
+-------+--------+
| name  | nation |
+-------+--------+
| John  | US     |
| Luice | US     |
+-------+--------+
2 rows in set (0.00 sec)

mysql>

mysql> SELECT t.name, t.nation FROM test t WHERE t.nation='US';

+-------+--------+

| name | nation |

+-------+--------+

| John | US |

| Luice | US |

+-------+--------+

2 rows in set (0.00 sec)

mysql>

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

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

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

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

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

mysql> select * from shop;
+--------------+-----------+
| product      | num_order |
+--------------+-----------+
| きゅうり     |        20 |
| リンゴ       |        35 |
| 大根         |        20 |
| バナナ       |        30 |
| みかん       |        50 |
| 玉ねぎ       |        25 |
+--------------+-----------+
6 rows in set (0.11 sec)

mysql> select * from wholesale;
+-----------+-------+
| product   | price |
+-----------+-------+
| リンゴ    |   150 |
| バナナ    |   300 |
| みかん    |   200 |
| ぶどう    |   500 |
| いちご    |   250 |
+-----------+-------+
5 rows in set (0.00 sec)

mysql>

mysql> select * from shop;

+--------------+-----------+

| product | num_order |

+--------------+-----------+

| きゅうり | 20 |

| リンゴ | 35 |

| 大根 | 20 |

| バナナ | 30 |

| みかん | 50 |

| 玉ねぎ | 25 |

+--------------+-----------+

6 rows in set (0.11 sec)

mysql> select * from wholesale;

+-----------+-------+

| product | price |

+-----------+-------+

| リンゴ | 150 |

| バナナ | 300 |

| みかん | 200 |

| ぶどう | 500 |

| いちご | 250 |

+-----------+-------+

5 rows in set (0.00 sec)

mysql>

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

mysql> SELECT shop.product, num_order, price
    -> FROM shop, wholesale
    -> WHERE shop.product = wholesale.product;
+-----------+-----------+-------+
| product   | num_order | price |
+-----------+-----------+-------+
| リンゴ    |        35 |   150 |
| バナナ    |        30 |   300 |
| みかん    |        50 |   200 |
+-----------+-----------+-------+
3 rows in set (0.01 sec)

mysql>

mysql> SELECT shop.product, num_order, price

-> FROM shop, wholesale

-> WHERE shop.product = wholesale.product;

+-----------+-----------+-------+

| product | num_order | price |

+-----------+-----------+-------+

| リンゴ | 35 | 150 |

| バナナ | 30 | 300 |

| みかん | 50 | 200 |

+-----------+-----------+-------+

3 rows in set (0.01 sec)

mysql>

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

mysql> SELECT shop.product, num_order, price
    -> FROM shop, wholesale w
    -> WHERE shop.product = w.product;
+-----------+-----------+-------+
| product   | num_order | price |
+-----------+-----------+-------+
| リンゴ    |        35 |   150 |
| バナナ    |        30 |   300 |
| みかん    |        50 |   200 |
+-----------+-----------+-------+
3 rows in set (0.00 sec)

mysql>

mysql> SELECT shop.product, num_order, price

-> FROM shop, wholesale w

-> WHERE shop.product = w.product;

+-----------+-----------+-------+

| product | num_order | price |

+-----------+-----------+-------+

| リンゴ | 35 | 150 |

| バナナ | 30 | 300 |

| みかん | 50 | 200 |

+-----------+-----------+-------+

3 rows in set (0.00 sec)

mysql>

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

mysql> SELECT s.product, num_order, price
    -> FROM shop s, wholesale w
    -> WHERE s.product = w.product;
+-----------+-----------+-------+
| product   | num_order | price |
+-----------+-----------+-------+
| リンゴ    |        35 |   150 |
| バナナ    |        30 |   300 |
| みかん    |        50 |   200 |
+-----------+-----------+-------+
3 rows in set (0.00 sec)

mysql>

mysql> SELECT s.product, num_order, price

-> FROM shop s, wholesale w

-> WHERE s.product = w.product;

+-----------+-----------+-------+

| product | num_order | price |

+-----------+-----------+-------+

| リンゴ | 35 | 150 |

| バナナ | 30 | 300 |

| みかん | 50 | 200 |

+-----------+-----------+-------+

3 rows in set (0.00 sec)

mysql>

MySQL – 主なコマンド

2016-11-04 / tau / コメントする

データベース操作

データベース一覧の表示: 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