MySQL – レコードの検索

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

フィールド選択

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

SELECT * FROM [テーブル];

1	SELECT * FROM [テーブル];

mysql> select * from test;
+-------+------+--------+
| name  | age  | nation |
+-------+------+--------+
| John  |   35 | US     |
| Alex  |   40 | NZ     |
| Luice |   29 | US     |
| Alice |   23 | UK     |
| Sala  |   25 | JP     |
+-------+------+--------+
3 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 |

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

3 rows in set (0.00 sec)

mysql>

指定したフィールド

SELECT [フィールド](, [フィールド], ...) FROM [テーブル];

1	SELECT [フィールド](, [フィールド], ...) FROM [テーブル];

mysql> select nation, name, nation from test;
+--------+-------+--------+
| nation | name  | nation |
+--------+-------+--------+
| US     | John  | US     |
| NZ     | Alex  | NZ     |
| US     | Luice | US     |
| UK     | Alice | UK     |
| JP     | Sala  | JP     |
+--------+-------+--------+
3 rows in set (0.00 sec)

mysql>

mysql> select nation, name, nation from test;

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

| nation | name | nation |

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

| US | John | US |

| NZ | Alex | NZ |

| US | Luice | US |

| UK | Alice | UK |

| JP | Sala | JP |

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

3 rows in set (0.00 sec)

mysql>

レコードの条件検索

SELECT [フィールド選択] FROM [テーブル] WHERE [検索条件];

1	SELECT [フィールド選択] FROM [テーブル] WHERE [検索条件];

文字列検索

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

mysql> select * from test where nation='US';
+-------+------+--------+
| name  | age  | nation |
+-------+------+--------+
| John  |   35 | US     |
| Luice |   29 | US     |
+-------+------+--------+
2 rows in set (0.00 sec)

mysql>

mysql> select * from test where nation='US';

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

| name | age | nation |

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

| John | 35 | US |

| Luice | 29 | US |

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

2 rows in set (0.00 sec)

mysql>

LIKEによるあいまい検索

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

WHERE [フィールド] LIKE [検索文字列]

1	WHERE [フィールド] LIKE [検索文字列]

任意の一文字

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

mysql> select * from test where nation like 'U_';
+-------+------+--------+
| name  | age  | nation |
+-------+------+--------+
| John  |   35 | US     |
| Luice |   29 | US     |
| Alice |   23 | UK     |
+-------+------+--------+
3 rows in set (0.00 sec)

mysql> select * from test where nation like 'U_';

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

| name | age | nation |

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

| John | 35 | US |

| Luice | 29 | US |

| Alice | 23 | UK |

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

3 rows in set (0.00 sec)

任意長の文字列

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

mysql> select * from test where name like 'A%';
+-------+------+--------+
| name  | age  | nation |
+-------+------+--------+
| Alex  |   40 | NZ     |
| Alice |   23 | UK     |
+-------+------+--------+
2 rows in set (0.00 sec)

mysql>

mysql> select * from test where name like 'A%';

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

| name | age | nation |

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

| Alex | 40 | NZ |

| Alice | 23 | UK |

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

2 rows in set (0.00 sec)

mysql>

数値検索

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

mysql> select * from test where age>30;
+------+------+--------+
| name | age  | nation |
+------+------+--------+
| John |   35 | US     |
| Alex |   40 | NZ     |
+------+------+--------+
2 rows in set (0.00 sec)

mysql>

mysql> select * from test where age>30;

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

| name | age | nation |

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

| John | 35 | US |

| Alex | 40 | NZ |

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

2 rows in set (0.00 sec)

mysql>

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

mysql> select * from test where age>=25 and age <= 35;
+-------+------+--------+
| name  | age  | nation |
+-------+------+--------+
| John  |   35 | US     |
| Luice |   29 | US     |
| Sala  |   25 | JP     |
+-------+------+--------+
3 rows in set (0.00 sec)

mysql>

mysql> select * from test where age>=25 and age <= 35;

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

| name | age | nation |

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

| John | 35 | US |

| Luice | 29 | US |

| Sala | 25 | JP |

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

3 rows in set (0.00 sec)

mysql>

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

mysql> select * from test where age between 25 and 35;
+-------+------+--------+
| name  | age  | nation |
+-------+------+--------+
| John  |   35 | US     |
| Luice |   29 | US     |
| Sala  |   25 | JP     |
+-------+------+--------+
3 rows in set (0.00 sec)

mysql> select * from test where age between 25 and 35;

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

| name | age | nation |

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

| John | 35 | US |

| Luice | 29 | US |

| Sala | 25 | JP |

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

3 rows in set (0.00 sec)

対象列挙

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

mysql> select * from test where nation in('UK', 'NZ');
+-------+------+--------+
| name  | age  | nation |
+-------+------+--------+
| Alex  |   40 | NZ     |
| Alice |   23 | UK     |
+-------+------+--------+
2 rows in set (0.00 sec)

mysql>

mysql> select * from test where nation in('UK', 'NZ');

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

| name | age | nation |

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

| Alex | 40 | NZ |

| Alice | 23 | UK |

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

2 rows in set (0.00 sec)

mysql>

さくらサーバー – MySQL – CSVファイルのインポート

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

概要

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

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

CSVファイルの準備

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

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

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

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

MySQLへのログイン

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

mysql -u [ユーザ名] -h mysql???.db.sakura.ne.jp -p --enable-local-infile

1	mysql -u [ユーザ名] -h mysql???.db.sakura.ne.jp -p --enable-local-infile

ファイルの読み込み

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

LOAD DATA LOCAL INFILE "home/USERNAME/.../[csvファイル名]"
INTO TABLE [テーブル名] 
FIELDS TERMINATED BY ',';

LOAD DATA LOCAL INFILE "home/USERNAME/.../[csvファイル名]"

INTO TABLE [テーブル名]

FIELDS TERMINATED BY ',';

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

実行例

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

% cat test.csv
100,abc
200,def
300,ghi
% mysql -u ???????? -h mysql???.db.sakura.ne.jp -p --enable-local-infile           
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
.....
mysql> use ????????_main;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------------+
| Tables_in_taustation_main |
+---------------------------+
| test                      |
+---------------------------+
2 rows in set (0.00 sec)

mysql> desc test;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| code  | char(5)  | YES  |     | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> load data local infile "/home/????????/test.csv"
    -> into table test
    -> fields terminated by ',';
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from test;
+------+------+                                                                      
| code | name |
+------+------+
| 100  | abc  |
| 200  | def  |
| 300  | ghi  |
+------+------+
3 rows in set (0.00 sec)

mysql>

% cat test.csv

100,abc

200,def

300,ghi

% mysql -u ???????? -h mysql???.db.sakura.ne.jp -p --enable-local-infile

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

.....

mysql> use ????????_main;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

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

| Tables_in_taustation_main |

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

| test |

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

2 rows in set (0.00 sec)

mysql> desc test;

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

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

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

2 rows in set (0.00 sec)

mysql> load data local infile "/home/????????/test.csv"

-> into table test

-> fields terminated by ',';

Query OK, 3 rows affected (0.00 sec)

Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from test;

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

| code | name |

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

| 100 | abc |

| 200 | def |

| 300 | ghi |

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

3 rows in set (0.00 sec)

mysql>

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

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

症状

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

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

+--------+------+
| name   | age  |
+--------+------+
| John   |   38 |
| Alex   |   40 |
| 山田   |   50 |
+--------+------+

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

| name | age |

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

| John | 38 |

| Alex | 40 |

| 山田 | 50 |

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

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

mysql> select * from test;
+------+------+
| name | age  |
+------+------+
| John |   38 |
| Alex |   40 |
|  |   50 |
+------+------+
3 rows in set (0.00 sec)

mysql>

mysql> select * from test;

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

| name | age |

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

| John | 38 |

| Alex | 40 |

| | 50 |

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

3 rows in set (0.00 sec)

mysql>

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

ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (ujis_japanese_ci,COERCIBLE) for operation '='

1	ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (ujis_japanese_ci,COERCIBLE) for operation '='

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

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

SHOW VARIABLES LIKE 'character_set%';

1	SHOW VARIABLES LIKE 'character_set%';

以下、実行結果。

mysql> show variables like 'character_set%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | ujis                             |
| character_set_connection | ujis                             |
| character_set_database   | ujis                             |
| character_set_filesystem | binary                           |
| character_set_results    | ujis                             |
| character_set_server     | ujis                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.01 sec)

mysql>

mysql> show variables like 'character_set%';

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

| Variable_name | Value |

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

| character_set_client | ujis |

| character_set_connection | ujis |

| character_set_database | ujis |

| character_set_filesystem | binary |

| character_set_results | ujis |

| character_set_server | ujis |

| character_set_system | utf8 |

| character_sets_dir | /usr/local/share/mysql/charsets/ |

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

8 rows in set (0.01 sec)

mysql>

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

対策

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

CHARSET utf8;

1	CHARSET utf8;

以下、実行例。

mysql> charset utf8
Charset changed
mysql> select * from test;
+--------+------+
| name   | age  |
+--------+------+
| John   |   38 |
| Alex   |   40 |
| 山田   |   50 |
+--------+------+
3 rows in set (0.00 sec)

mysql> show variables like 'character_set%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | ujis                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)

mysql>

mysql> charset utf8

Charset changed

mysql> select * from test;

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

| name | age |

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

| John | 38 |

| Alex | 40 |

| 山田 | 50 |

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

3 rows in set (0.00 sec)

mysql> show variables like 'character_set%';

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

| Variable_name | Value |

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

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | utf8 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | ujis |

| character_set_system | utf8 |

| character_sets_dir | /usr/local/share/mysql/charsets/ |

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

8 rows in set (0.00 sec)

mysql>

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

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

対策・改

その後、MySQLに入る時に以下を実行する方法も知った。--default-character-set=utf8mb4を指定する。

% mysql --host=mysql57.taustation.sakura.ne.jp --user=taustation --password --default-character-set=utf8mb4

1	% mysql --host=mysql57.taustation.sakura.ne.jp --user=taustation --password --default-character-set=utf8mb4

この結果文字コードは以下のようになって、文字化けも解消。

mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql> show variables like 'char%';

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

| Variable_name | Value |

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

| character_set_client | utf8mb4 |

| character_set_connection | utf8mb4 |

| character_set_database | utf8mb4 |

| character_set_filesystem | binary |

| character_set_results | utf8mb4 |

| character_set_server | utf8mb4 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ |

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

8 rows in set (0.00 sec)

20210604

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

2016-10-31 / tau / 2件のコメント

カラムの追加

最後尾に追加

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

ALTER TABLE [テーブル名] ADD [フィールド名] [型];

1	ALTER TABLE [テーブル名] ADD [フィールド名] [型];

以下、実行例。

mysql> select * from test;                                                           
+------+------+
| name | age  |
+------+------+
| John |   32 |
| Alex |   40 |
+------+------+
2 rows in set (0.00 sec)

mysql> describe test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> alter table test add occupation varchar(20);                                  
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> describe test;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| name       | varchar(10) | YES  |     | NULL    |       |
| age        | tinyint(4)  | YES  |     | NULL    |       |
| occupation | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> select * from test;                                                           
+------+------+------------+
| name | age  | occupation |
+------+------+------------+
| John |   32 | NULL       |
| Alex |   40 | NULL       |
+------+------+------------+
2 rows in set (0.00 sec)

mysql>

mysql> select * from test;

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

| name | age |

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

| John | 32 |

| Alex | 40 |

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

2 rows in set (0.00 sec)

mysql> describe test;

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

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

| age | tinyint(4) | YES | | NULL | |

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

2 rows in set (0.01 sec)

mysql> alter table test add occupation varchar(20);

Query OK, 2 rows affected (0.01 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> describe test;

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

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

| age | tinyint(4) | YES | | NULL | |

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

3 rows in set (0.01 sec)

mysql> select * from test;

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

| name | age | occupation |

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

| John | 32 | NULL |

| Alex | 40 | NULL |

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

2 rows in set (0.00 sec)

mysql>

先頭に追加

FIRSTを指定すると、新たな列が先頭に追加される。

ALTER TABLE [テーブル名] ADD [フィールド名] [型] FIRST;

1	ALTER TABLE [テーブル名] ADD [フィールド名] [型] FIRST;

指定位置への追加

AFTERで指定したカラムの後に新たな列が追加される。

ALTER TABLE [テーブル名] ADD [フィールド名] [型] AFTER [基準カラム名];

1	ALTER TABLE [テーブル名] ADD [フィールド名] [型] AFTER [基準カラム名];

カラムの変更

名称と型の変更

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

CHANGEでフィールド名と型を同時に変更する。

ALTER TABLE [テーブル名] CHANGE [フィールド名] [新フィールド名] [新しい型];

1	ALTER TABLE [テーブル名] CHANGE [フィールド名] [新フィールド名] [新しい型];

以下、実行例。

mysql> alter table test change name name varchar(20);
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> describe test;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| name       | varchar(20) | YES  |     | NULL    |       |
| age        | tinyint(4)  | YES  |     | NULL    |       |
| occupation | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql>

mysql> alter table test change name name varchar(20);

Query OK, 1 row affected (0.01 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> describe test;

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

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

| age | tinyint(4) | YES | | NULL | |

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

3 rows in set (0.01 sec)

mysql>

型のみの変更

型のみの変更はMODIFY。

ALTER TABLE [テーブル名] MODIFY [フィールド名] [型];

1	ALTER TABLE [テーブル名] MODIFY [フィールド名] [型];

以下、実行例。

mysql> describe test;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(10) | YES  |     | NULL    |       |
| age    | tinyint(4)  | YES  |     | NULL    |       |
| nation | varchar(10) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table test modify nation varchar(2);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> describe test;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(10) | YES  |     | NULL    |       |
| age    | tinyint(4)  | YES  |     | NULL    |       |
| nation | varchar(2)  | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

mysql> describe test;

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

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

| age | tinyint(4) | YES | | NULL | |

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

3 rows in set (0.00 sec)

mysql> alter table test modify nation varchar(2);

Query OK, 5 rows affected (0.00 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> describe test;

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

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

| age | tinyint(4) | YES | | NULL | |

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

3 rows in set (0.00 sec)

mysql>

カラムの削除

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

ALTER TABLE [テーブル名] DROP COLUMN [フィールド名];

1	ALTER TABLE [テーブル名] DROP COLUMN [フィールド名];

以下、実行例。

mysql> describe test;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| name       | varchar(10) | YES  |     | NULL    |       |
| age        | tinyint(4)  | YES  |     | NULL    |       |
| occupation | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table test drop column occupation;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

mysql> describe test;

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

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

| age | tinyint(4) | YES | | NULL | |

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

3 rows in set (0.00 sec)

mysql> alter table test drop column occupation;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> describe test;

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

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

| age | tinyint(4) | YES | | NULL | |

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

2 rows in set (0.00 sec)

mysql>

MySQL – レコードの追加・変更・削除

2016-10-31 / tau / コメントする

レコードの追加

全フィールド追加

INSERT INTO ~ VALUESコマンドで、全フィールドの内容をフィールドの順番通りに追加する。

INSERT INTO [テーブル名] VALUES([フィールド値1], [フィールド値2], ...);

1	INSERT INTO [テーブル名] VALUES([フィールド値1], [フィールド値2], ...);

以下、実行例。

mysql> insert into test values('John', 32);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+------+
| name | age  |
+------+------+
| John |   32 |
+------+------+
1 row in set (0.00 sec)

mysql>

mysql> insert into test values('John', 32);

Query OK, 1 row affected (0.00 sec)

mysql> select * from test;

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

| name | age |

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

| John | 32 |

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

1 row in set (0.00 sec)

mysql>

フィールドを指定した追加

フィールドを指定した実行は次の通り。

INSERT INTO [テーブル名] ([フィールド名1], ...) VALUES([フィールド値1], ...);

1	INSERT INTO [テーブル名] ([フィールド名1], ...) VALUES([フィールド値1], ...);

以下、実行例。

mysql> insert into test (name, age) values('Alex', 40);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+------+
| name | age  |
+------+------+
| Alex |   40 |
| John |   32 |
+------+------+
2 rows in set (0.00 sec)

mysql>

mysql> insert into test (name, age) values('Alex', 40);

Query OK, 1 row affected (0.00 sec)

mysql> select * from test;

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

| name | age |

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

| Alex | 40 |

| John | 32 |

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

2 rows in set (0.00 sec)

mysql>

フィールドの一部だけを指定すると、指定していないフィールドには初期値が設定される。

mysql> insert into test (name) values('Alice');
Query OK, 1 row affected (0.11 sec)

mysql> select * from test;
+-------+------+
| name  | age  |
+-------+------+
| Alex  |   40 |
| Alice | NULL |
| John  |   32 |
+-------+------+
3 rows in set (0.00 sec)

mysql>

mysql> insert into test (name) values('Alice');

Query OK, 1 row affected (0.11 sec)

mysql> select * from test;

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

| name | age |

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

| Alex | 40 |

| Alice | NULL |

| John | 32 |

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

3 rows in set (0.00 sec)

mysql>

レコードの変更

レコードのフィールド値の変更はUPDATE ~ SET ~ WHEREコマンドを使う。

UPDATE test SET age=28 WHERE name='Alice';

1	UPDATE test SET age=28 WHERE name='Alice';

以下、実行例。

mysql> update test set age=28 where name='Alice';                                    
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test;
+-------+------+
| name  | age  |
+-------+------+
| Alex  |   40 |
| Alice |   28 |
| John  |   32 |
+-------+------+
3 rows in set (0.00 sec)

mysql>

mysql> update test set age=28 where name='Alice';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from test;

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

| name | age |

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

| Alex | 40 |

| Alice | 28 |

| John | 32 |

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

3 rows in set (0.00 sec)

mysql>

UPDATEの注意点

UPDATEコマンドは必ずWHEREで対象となるレコードを指定する。さもないと、全レコードのフィールドが指定した内容で変更されてしまう。

mysql> update test set age=30;                                                       
Query OK, 3 rows affected (0.11 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from test;
+-------+------+
| name  | age  |
+-------+------+
| Alex  |   30 |
| Alice |   30 |
| John  |   30 |
+-------+------+
3 rows in set (0.00 sec)

mysql>

mysql> update test set age=30;

Query OK, 3 rows affected (0.11 sec)

Rows matched: 3 Changed: 3 Warnings: 0

mysql> select * from test;

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

| name | age |

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

| Alex | 30 |

| Alice | 30 |

| John | 30 |

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

3 rows in set (0.00 sec)

mysql>

レコードの削除

レコードの削除は、DELETE FROM ~ WHEREコマンドで行う。

DELETE FROM [テーブル名] WHERE [フィールド名]=[フィールド値];

1	DELETE FROM [テーブル名] WHERE [フィールド名]=[フィールド値];

以下、実行例。

mysql> delete from test where name='John';
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+-------+------+
| name  | age  |
+-------+------+
| Alex  |   30 |
| Alice |   30 |
+-------+------+
2 rows in set (0.00 sec)

mysql>

mysql> delete from test where name='John';

Query OK, 1 row affected (0.01 sec)

mysql> select * from test;

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

| name | age |

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

| Alex | 30 |

| Alice | 30 |

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

2 rows in set (0.00 sec)

mysql>

DELETEの注意点

DELETEコマンドでWHEREを省略すると、全データが削除されてしまうので注意。

mysql> delete from test;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from test;                                                     
Empty set (0.00 sec)

mysql>

mysql> delete from test;

Query OK, 2 rows affected (0.00 sec)

mysql> select * from test;

Empty set (0.00 sec)

mysql>

MySQL – テーブルの追加・確認・変更・複製・削除

2016-10-29 / tau / コメントする

追加

テーブルの追加

テーブルの追加はCREATE TABLEコマンドで行う。

CREATE TABLE [テーブル名]([カラム名] [型], [カラム名] [型], ...);

1	CREATE TABLE [テーブル名]([カラム名] [型], [カラム名] [型], ...);

以下、実行例。

mysql> create table test(
    -> name varchar(10),
    -> age tinyint
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>

mysql> create table test(

-> name varchar(10),

-> age tinyint

-> );

Query OK, 0 rows affected (0.01 sec)

mysql>

カラムの追加

テーブルにカラムを追加する場合はALTER TABLE ... ADDを使う。

確認

テーブル一覧の確認

テーブル一覧の確認はSHOW TABLESコマンド。

mysql> show tables;
+---------------------------+
| Tables_in_??????????_test |
+---------------------------+
| test                      |
+---------------------------+
1 row in set (0.00 sec)

mysql>

mysql> show tables;

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

| Tables_in_??????????_test |

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

| test |

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

1 row in set (0.00 sec)

mysql>

テーブル構造の確認

テーブル構造の確認はDESCまたはDESCRIBEコマンド。

mysql> describe test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql>

mysql> describe test;

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

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

| age | tinyint(4) | YES | | NULL | |

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

2 rows in set (0.01 sec)

mysql>

変更

テーブル名の変更

ALTER TABLE [テーブル名] RENAME TO [新しいテーブル名];

1	ALTER TABLE [テーブル名] RENAME TO [新しいテーブル名];

テーブルのカラム名・カラムの型の変更

テーブルのカラム名やカラムの型の変更はALTER TABLE ... CHANGE、ALTER TABLE ... MODIFYで行う。

複製

テーブルの複製

テーブルの複製は、MySQLの場合、CREATE TABLEとSSELECT文を組み合わせる。

CREATE TABLE [コピー先テーブル名] (SELECT * FROM [コピー元テーブル名]);

1	CREATE TABLE [コピー先テーブル名] (SELECT * FROM [コピー元テーブル名]);

削除

テーブルの削除

テーブルの削除はDROP TABLEコマンド。

mysql> drop table test;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql>

mysql> drop table test;

Query OK, 0 rows affected (0.00 sec)

mysql> show tables;

Empty set (0.00 sec)

mysql>

テーブルのカラムの削除

テーブルのカラムを削除する場合はALTER TABLE ... DROP COLUMNとする。

MySQL – データベースの作成・権限付与・削除

2016-10-29 / tau / コメントする

データベースの作成

基本コマンド

データベースの作成はCREATE DATABASEコマンドを使う。

mysql> create database ??????????_test;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ??????????_main    |
| ??????????_test    |
| ??????????_wp1     |
+--------------------+<code></code>
4 rows in set (0.10 sec)

mysql>

mysql> create database ??????????_test;

Query OK, 1 row affected (0.00 sec)

mysql> show databases;

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

| Database |

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

| information_schema |

| ??????????_main |

| ??????????_test |

| ??????????_wp1 |

+--------------------+<code></code>

4 rows in set (0.10 sec)

mysql>

文字コードと照合順序

データベースの文字コードと照合順序(COLLATE～照合順序)を指定して作成するコマンド。

CREATE DATABALSE db_name CHARACTER SET char_set COLLATE collation_name;

たとえばutf8mb4の場合の例

CREATE DATABALSE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

1	CREATE DATABALSE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

さくらレンタルサーバー

さくらレンタルサーバーの場合はwebのコントロールパネルでデータベースを作成・削除する方法のみが示されていて、コンソールでのコマンド操作はエラーとなる。

mysql> create database taustation_temp;                                                     
ERROR 1044 (42000): Access denied for user 'taustation'@'%' to database 'taustation_temp'

1 2	mysql> create database taustation_temp; ERROR 1044 (42000): Access denied for user 'taustation'@'%' to database 'taustation_temp'

データベースアクセスの権限設定

必要に応じて、ユーザーへのデータベースアクセスの権限を設定する。

GRANT ALL PRIVILEGES ON dbname.* TO dbuser@'localhost'

データベースの削除

データベースの削除にはDROP DATABASEコマンドを使う。

mysql> drop database ??????????_test;
Query OK, 0 rows affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ??????????_main    |
| ??????????_wp1     |
+--------------------+
3 rows in set (0.11 sec)

mysql>

mysql> drop database ??????????_test;

Query OK, 0 rows affected (0.01 sec)

mysql> show databases;

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

| Database |

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

| information_schema |

| ??????????_main |

| ??????????_wp1 |

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

3 rows in set (0.11 sec)

mysql>

存在しないデータベースを削除しようとするとエラーになるが、DROP DATABASE IF EXISTSコマンドを使うと、データベースが存在しない場合もエラーにならない。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ??????????_main    |
| ??????????_test    |
| ??????????_wp1     |
+--------------------+
4 rows in set (0.10 sec)

mysql> drop database if exists ??????????_test;
Query OK, 0 rows affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ??????????_main    |
| ??????????_wp1     |
+--------------------+
3 rows in set (0.11 sec)

mysql> drop database if exists ??????????_test;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> drop database ??????????_test;
ERROR 1044 (42000): Access denied for user 'taustation'@'%' to database 'taustation_foo'

mysql>

mysql> show databases;

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

| Database |

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

| information_schema |

| ??????????_main |

| ??????????_test |

| ??????????_wp1 |

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

4 rows in set (0.10 sec)

mysql> drop database if exists ??????????_test;

Query OK, 0 rows affected (0.01 sec)

mysql> show databases;

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

| Database |

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

| information_schema |

| ??????????_main |

| ??????????_wp1 |

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

3 rows in set (0.11 sec)

mysql> drop database if exists ??????????_test;

Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> drop database ??????????_test;

ERROR 1044 (42000): Access denied for user 'taustation'@'%' to database 'taustation_foo'

mysql>

MySQL – データベースの一覧表示と接続・確認

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

既存のデータベースを確認するコマンドはSHOW DATABASES;。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ????????_????      |
| ????????_???       |
+--------------------+
3 rows in set (0.10 sec)

mysql>

mysql> show databases;

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

| Database |

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

| information_schema |

| ????????_???? |

| ????????_??? |

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

3 rows in set (0.10 sec)

mysql>

データベースに接続するにはuseコマンドを使う。useコマンドは最後にセミコロン(;)を入れなくてもよい。

mysql> use information_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
<code></code>
Database changed
mysql>

mysql> use information_schema

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql>

以降、useコマンドで接続するデータベースを切り替えることができる。

接続中のデータベースの確認

接続中のデータベースの確認方法は、以下の二通り。

SELECT database();

1	SELECT database();

または

SHOW PROCESSLIST;

1	SHOW PROCESSLIST;

以下、実行例。

mysql> select database();
+-----------------+
| database()      |
+-----------------+
| taustation_test |
+-----------------+
1 row in set (0.00 sec)

mysql> show processlist;
+-----------+------------+---------------------+-----------------+---------+------+-------+------------------+
| Id        | User       | Host                | db              | Command | Time | State | Info             |
+-----------+------------+---------------------+-----------------+---------+------+-------+------------------+
| 601690174 | taustation | 59.106.171.51:37572 | taustation_test | Query   |    0 | NULL  | show processlist |
+-----------+------------+---------------------+-----------------+---------+------+-------+------------------+
1 row in set (0.00 sec)

mysql>

mysql> select database();

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

| database() |

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

| taustation_test |

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

1 row in set (0.00 sec)

mysql> show processlist;

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

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

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

1 row in set (0.00 sec)

mysql>

MySQLが強制終了されて起動できない

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

MySQL使用中に接続が切れたりすると、ロックがかかって使えなくなる。

そのような場合は、psコマンドでプロセスID(PID)を調べて、killコマンドで強制終了させる。

MySQL – 状態の確認とコマンド一覧

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

statusコマンドでMySQLのバージョンや文字セット、ポート番号などを状態を確認できる。

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.5.32, for FreeBSD9.1 (amd64) using  5.2

Connection id:          594729209
Current database: 
Current user:           ????@??.??.??.??
SSL:                    Not in use
Current pager:          more
Using outfile:          ''
Using delimiter:        ;
Server version:         5.5.38-log Source distribution
Protocol version:       10
Connection:             mysql???.db.sakura.ne.jp via TCP/IP
Server characterset:    ujis
Db     characterset:    ujis
Client characterset:    ujis
Conn.  characterset:    ujis
TCP port:               3306
Uptime:                 589 days 5 hours 28 min 37 sec

Threads: 16  Questions: 27954918985  Slow queries: 1391539  Opens: 21787261  Flush tables: 590  Open tables: 19200  Queries per second avg: 549.112
--------------

mysql>

mysql> status

--------------

mysql Ver 14.14 Distrib 5.5.32, for FreeBSD9.1 (amd64) using 5.2

Connection id: 594729209

Current database:

Current user: ????@??.??.??.??

SSL: Not in use

Current pager: more

Using outfile: ''

Using delimiter: ;

Server version: 5.5.38-log Source distribution

Protocol version: 10

Connection: mysql???.db.sakura.ne.jp via TCP/IP

Server characterset: ujis

Db characterset: ujis

Client characterset: ujis

Conn. characterset: ujis

TCP port: 3306

Uptime: 589 days 5 hours 28 min 37 sec

Threads: 16 Questions: 27954918985 Slow queries: 1391539 Opens: 21787261 Flush tables: 590 Open tables: 19200 Queries per second avg: 549.112

--------------

mysql>

また、helpコマンドでコマンド一覧を表示できる。

mysql> help

For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
   https://shop.mysql.com/

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.

For server side help, type 'help contents'

mysql>

mysql> help

For information about MySQL products and services, visit:

http://www.mysql.com/

For developer information, including the MySQL Reference Manual, visit:

http://dev.mysql.com/

To buy MySQL Enterprise support, training, or other products, visit:

https://shop.mysql.com/

List of all MySQL commands:

Note that all text commands must be first on line and end with ';'

? (\?) Synonym for `help'.

clear (\c) Clear the current input statement.

connect (\r) Reconnect to the server. Optional arguments are db and host.

delimiter (\d) Set statement delimiter.

edit (\e) Edit command with $EDITOR.

ego (\G) Send command to mysql server, display result vertically.

exit (\q) Exit mysql. Same as quit.

go (\g) Send command to mysql server.

help (\h) Display this help.

nopager (\n) Disable pager, print to stdout.

notee (\t) Don't write into outfile.

pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.

print (\p) Print current command.

prompt (\R) Change your mysql prompt.

quit (\q) Quit mysql.

rehash (\#) Rebuild completion hash.

source (\.) Execute an SQL script file. Takes a file name as an argument.

status (\s) Get status information from the server.

system (\!) Execute a system shell command.

tee (\T) Set outfile [to_outfile]. Append everything into given outfile.

use (\u) Use another database. Takes database name as argument.

charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.

warnings (\W) Show warnings after every statement.

nowarning (\w) Don't show warnings after every statement.

For server side help, type 'help contents'

mysql>