MySQL の基本操作 (1)

MySQL (1)

更新日：2022年03月11日

作成日：2016年06月19日

MySQL の概要と設定

MySQL のパスの設定
MySQL モニタの起動
MySQL での日本語文字コードの設定
MySQL モニタの入力
入力を誤った場合
予約語
プロンプトの変更

データベースは「ある規則を持ったデータの集まり」です。データベースの構造を示すときは，テーブル（表）を使うことが一般的です。

テーブル（表）は，カラム（列）とレコード（行）で構成されます。1件のデータをレコード（行）、そして項目をカラム（列）またはフィールドといいます。

また、テーブルを構成する項目をフィールド、実際に入力されたレコードを構成する項目のデータをカラムと用語を分けることもあります。

データベースの構造

１つのデータベースには、複数のテーブルを置くことができます。

データベースの特徴

データベースではそれぞれのカラムには同じデータ型の値しか入れることができません。

また、トランザクションなどの特別な機能を使用しない限り、1度行った操作は元に戻すことができません。

MySQL とは

MySQL はリレーショナル型のデータベースです。リレーショナルデータベースを管理するシステムをRDBMS (Relational DataBase Management System) と言います。MySQL も RDBMS の１つです。

また、MySQL はオープンソースのデータベースで無償で利用することができます。MySQL には以下の２つの形態があります。

MySQL Community Server: 無償
一定の制限の中で再配布可能

MySQL Enterprise Server: 有償
再配布できない

MySQL 5.6 リファレンスマニュアル

SQL とは

データベースを操作するには、データベースに対してステートメント（命令）を出して処理する内容を指定します。このステートメントを文字として表したものがクエリ（query 問い合わせ）です。

例えばテーブルを作成する際は、CREATE TABLE .... というクエリを使用します。このクエリを書く際の規則が SQL (Structured Query Language) と言う言語で、SQL はデータベースに問い合わせを行うコマンド（ステートメント）です。

SQL文（ステートメント）は大文字・小文字を区別しないので大文字・小文字どちらでも大丈夫です。

また、SQL の文法は使用するデータベースごとに少しずつ違いがあります。

MySQL の環境設定

XAMPP を使うと簡単に「MySQL + Apache + PHP」の動作環境を構築することができます。（省略）

最初の MySQL アカウントのセキュリティー設定

MySQL のパスの設定

デフォルトでは、「C:\xampp\mysql\bin」から MySQL を起動することができますが、どこのフォルダからでも「MySQL モニタ」を起動できるように、パスを通しておくと便利です。パスを通すとは、Windows システムの「環境変数」にアプリケーションを配置した位置を登録して、どこからでも対象のアプリケーションを起動できるようにすることです。以下は Windows7 の例です。

コンピュータを右クリックしてプロパティを選択。
左上の「システムの詳細設定」をクリック。
「環境変数」ボタンをクリック。
「Path」を選択して、「編集」をクリック。
「変数値」の記述の最後に ; を付けて C:\xampp\mysql\bin\ を追記。
それぞれのダイアログボックスでOKをクリックして閉じる。
Windows を再起動

環境変数

MySQL のパスの設定

MySQL モニタの起動

MySQL モニタは、MySQL を操作するための CUI (Character User Interface) のクライアント用プログラムで、MySQL をインストールすると、MySQL モニタが使えるようになります。

MySQL モニタを使用するには、コマンドプロンプトを起動して、mysql というコマンドを実行します。以下が書式です。

mysql -u ユーザー名 -p

上記を入力して、Enter キーを押すと、パスワードの入力を促されるので、パスワードを入力します。

またパスワードを設定していない場合は、パスワードなしで再度 Enter キーを押します。

C:\Users\username>mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.16 MySQL Community Server (GPL)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Windows のプロンプト C:\Users\username> から、 mysql> のプロンプトに変わります。

MySQL モニタの終了

MySQL モニタを終了するには、exit または quit と入力し Enter キーを押します。

MySQL での日本語文字コードの設定

文字コードの確認は「MySQL モニタ」で「show variables like "chara%";」と入力すると現在の文字コードの設定が表示されます。

mysql> show variables like "chara%";
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | cp932                          |
| character_set_connection | cp932                          |
| character_set_database   | latin1                         |
| character_set_filesystem | binary                         |
| character_set_results    | cp932                          |
| character_set_server     | latin1                         |
| character_set_system     | utf8                           |
| character_sets_dir       | C:\xampp\mysql\share\charsets\ |
+--------------------------+--------------------------------+
8 rows in set (0.00 sec)

MySQL に関する設定は my.ini（または my.cnf）ファイルで行います。XAMPP を標準でインストールした場合、my.ini は以下のフォルダにあります。

C:\xampp\mysql\bin

ファイルを修正するので元のファイルをコピーしてバックアップ（別名で保存する等）を取っておいて下さい。

以下は、XAMPP の MySQL で文字コードを UTF-8 として使用する場合の設定変更の例です。

my.ini はいくつかのセクションに分かれています。

[client]：全クライアントで共通のオプションの設定
[mysqld]：MySQLサーバ（SQL デーモン）に関するオプションの設定
[mysqldump]：MySQL データベースをファイルにダンプするクライアントの設定
[mysql]：MySQLコマンドラインツールが参照するオプションの設定（コマンドプロンプト経由で接続する場合に使用される設定）

テキストエディタで my.ini を開き、[mysqld] の最後のほうにある以下のような箇所を探します。

## UTF 8 Settings
#init-connect=\'SET NAMES utf8\'
#collation_server=utf8_unicode_ci
#character_set_server=utf8
#skip-character-set-client-handshake
#character_sets-dir="C:/xampp/mysql/share/charsets"

character_set_server=utf8 の行の先頭のコメントアウト「#」を外します。

## UTF 8 Settings
#init-connect=\'SET NAMES utf8\'
#collation_server=utf8_unicode_ci
character_set_server=utf8
#skip-character-set-client-handshake
#character_sets-dir="C:/xampp/mysql/share/charsets"

続いて、[client] [mysqldump] [mysql] の最後に「default-character-set=utf8」を追記します。

# The following options will be passed to all MySQL clients
[client] 
# password       = your_password 
port            = 3306 
socket          = "C:/xampp/mysql/mysql.sock"
default-character-set=utf8

# Here follows entries for some specific programs 
# The MySQL server
・・・中略・・・
[mysqldump]
quick
max_allowed_packet = 16M
default-character-set=utf8

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
default-character-set=utf8
・・・中略・・・

設定を有効にするために MySQL を再起動します。

「MySQL モニタ」で「show variables like "chara%";」と入力して確認します。

mysql> show variables like "chara%";
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | cp932                          |
| character_set_connection | cp932                          |
| character_set_database   | utf8                           |
| character_set_filesystem | binary                         |
| character_set_results    | cp932                          |
| character_set_server     | utf8                           |
| character_set_system     | utf8                           |
| character_sets_dir       | C:\xampp\mysql\share\charsets\ |
+--------------------------+--------------------------------+
8 rows in set (0.00 sec)

latin1 が utf8 に変更されたのが確認できます。いくつかの Value は cp932 ですが、これは Windows のコマンドプロンプト（MySQL モニタ）で日本語入力をするためにこのままにしてあります。

または、「status」と入力して確認することもできます。

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.6.16, for Win32 (x86)

Connection id:          1
Current database:
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.6.16 MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    cp932
Conn.  characterset:    cp932
TCP port:               3306
Uptime:                 2 min 36 sec

Threads: 1  Questions: 177  Slow queries: 0  Opens: 79  Flush tables: 1  Open ta
bles: 72  Queries per second avg: 1.134
--------------

全てを UTF-8 にするには以下の skip-character-set-client-handshake のコメントアウトを外します。但し、コマンドプロンプト（MySQL モニタ）で日本語を入力するには、「SET NAMES cp932; 」を使用する必要があります。

## UTF 8 Settings
#init-connect=\'SET NAMES utf8\'
#collation_server=utf8_unicode_ci
character_set_server=utf8
skip-character-set-client-handshake
#character_sets-dir="C:/xampp/mysql/share/charsets"

設定を有効にするために MySQL を再起動し確認します。

mysql> show variables like "chara%";
+--------------------------+--------------------------------+
| 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     | utf8                           |
| character_set_system     | utf8                           |
| character_sets_dir       | C:\xampp\mysql\share\charsets\ |
+--------------------------+--------------------------------+
8 rows in set (0.00 sec)

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.6.16, for Win32 (x86)

Connection id:          1
Current database:
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.6.16 MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 53 sec

Threads: 1  Questions: 6  Slow queries: 0  Opens: 70  Flush tables: 1  Open tabl
es: 63  Queries per second avg: 0.113
--------------

上記設定の場合、コマンドプロンプト（MySQL モニタ）で日本語を入力するには、「SET NAMES cp932; 」を使用する必要があります。

mysql> set names cp932;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "chara%";
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | cp932                          |
| character_set_connection | cp932                          |
| character_set_database   | utf8                           |
| character_set_filesystem | binary                         |
| character_set_results    | cp932                          |
| character_set_server     | utf8                           |
| character_set_system     | utf8                           |
| character_sets_dir       | C:\xampp\mysql\share\charsets\ |
+--------------------------+--------------------------------+
8 rows in set (0.00 sec)

MySQL モニタの入力

SQL 文（ステートメント）は大文字・小文字を区別しないので大文字・小文字どちらでもかまいませんが、半角文字で入力します。

但し、データベース名やテーブル名は、Windowsの場合は大文字・小文字を区別しませんが、Linux は区別するので注意が必要です。

SQL 文には、途中にタブや半角スペース、改行を入れてもかまいませんが、キーワードやデータの途中で改行するとエラーになります。

MySQL モニタでは、ステートメントの最後にデリミタ「;」を付けます。（デリミタに使用する文字は変更可能です）

1行に表示するデータが多い場合は、最後のデリミタ「;」の代わりに「\G」を使うと見易くなります（この場合、; は不要です）。

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

入力を誤った場合

文法的に誤って入力して Enter キーを押すとエラーが表示されますが、場合によってはプロンプトが mysql> から -> や '> に変わってそのままの状態になることがあります。

一般に、1 行のコマンドを発行しようとして終端のセミコロンを忘れると、複数行ステートメントが発生します。この場合、mysql は追加の入力を待機します。

mysql> SELECT USER()
    ->

ステートメントを入力したのに -> プロンプトの応答が示される場合は、mysql がセミコロンを待機している可能性が高いです。セミコロンを入力してステートメントを完了すると、処理が実行されます。

mysql> SELECT USER()
    -> ;
+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

入力途中のコマンドの実行の取り消し

入力途中のコマンドの実行を取り消すには、\c と入力します。

mysql> SELECT
    -> USER()
    -> \c
mysql>

'> または "> プロンプトが表示される場合、それは「'」または「"」引用符で始まる文字列を含む行を入力したのに、その文字列を終了させる対応する引用符をまだ入力していないことを意味します。

mysql> SELECT name FROM tb1 WHERE num = "A001;
    ">

このプロンプトは、mysql が完了していない文字列の残りを待機していることを示しています。(文字列 "A001 には、対になる二重引用符がありません。)

この場合、もっとも簡単なのは、コマンドを取り消すことです。ただし、この場合は単に \c と入力することはできません。なぜなら、mysql で収集中の文字列の一部とみなされるからです。代わりに、閉じる引用符を入力して (つまり、文字列が完了したことを mysql に認識させ)、次に \c と入力します。

mysql> SELECT name FROM tb1 WHERE num = "A001;
    "> "\c
mysql>

\c の入力後に mysql> に戻り、mysql が新しいコマンドに対して準備できていることがわかります。

参考：MySQL 5.6 リファレンスマニュアル / チュートリアル / クエリーの入力

予約語

データベースやテーブル、カラムなどを作成する時に名前を指定しますが、この名前のことを識別子と呼びます。

識別子は例外を除いて引用符などで囲う必要はなく、そのまま記述できます。

但し、予約語をそのまま使用する場合や特別な文字が含まれる場合はバッククォート(`)で囲う必要があります。

以下は予約語の div という名前のテーブルを作成しようとする例です。（エラーになります）

mysql> CREATE TABLE div (name VARCHAR(30));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'div (name VARCHAR(30))' at line 1

以下のように予約語でもバッククォートで囲めば識別子として使用できます。但し、予約語などを識別子に使うことはできる限り避ける方が安全です。

mysql> CREATE TABLE `div` (name VARCHAR(30));
Query OK, 0 rows affected (0.05 sec)

mysql> SHOW TABLES;
+---------------+
| Tables_in_db1 |
+---------------+
| div           |
| tb1           |
  ...
+---------------+
6 rows in set (0.00 sec)

MySQL の予約語は以下のページに記載されています（かなり多くの予約語があります）。

予約語

プロンプトの変更

MySQL モニタを実行している時に表示されるプロンプト「mysql>」を表示したい文字列に変更することができます。

１つの方法は、MySQL モニタを起動する際に「--prompt=プロンプト」オプションで指定する方法です。

別の方法は、途中で「prompt」と言うメタコマンドで指定する方法です。

また、プロンプトを定義するための文字列には、次の特殊なシーケンスを含めることができます。

プロンプトを定義するための特殊なシーケンス（一部抜粋）
シーケンス	説明
\D	現在の日付 (フルで)
\d	現在のデータベース名
\h	サーバーホスト名<
\u	ユーザー名
\U	完全な user_name@host_name アカウント名
\_	スペース

以下は、MySQL モニタを起動する際に「--prompt=プロンプト」オプションで指定する例です。

C:\Users\Toyoki>mysql -u root -p --prompt="\u [\d] >"
Enter password:
・・・中略・・・
root [(none)]  >use db1;
Database changed
root [db1]  >

以下は、「prompt」と言うメタコマンドで指定する方法です。

mysql > prompt \U/\d  >>
PROMPT set to '\U/\d  >>'
root@localhost/db1  >>

元に戻したい場合は、prompt の後に何も指定しないか、「mysql>」を指定します。

root@localhost/db1 >>prompt
Returning to default PROMPT of mysql>
mysql>

MySQL の基本操作

MySQLの基本コマンド（ステートメント）
内容	コマンド
データベースの作成	CREATE DATABASE データベース名;
データベースの確認	SHOW DATABASES;
データベースの指定	USE データベース名
現在使用しているデータベースの表示	SELECT DATABASE( );
テーブルの作成	CREATE TABLE テーブル名 (カラム名1 データ型1,..., カラム名nデータ型n);
全てのテーブルの表示	SHOW TABLES;
テーブルのカラム構造の確認	DESC テーブル名;　または DESCRIBE テーブル名;
データの挿入	INSERT INTO テーブル名 VALUES(データ1,..., データn);
カラム名を指定してのデータの挿入	INSERT INTO テーブル名 (カラム名1,..., カラム名n) VALUES(データ1,..., データn);
複数のデータの挿入	INSERT INTO テーブル名 (カラム名1, カラム名2,...) VALUES(データ1,データ2, ...),(データ1,データ2, ...)
データのロード	LOAD DATA LOCAL INFILE '/パス/ファイル名' INTO TABLE テーブル名
データの表示	SELECT カラム名1, カラム名2,... FROM テーブル名;

データベースの作成・確認・指定

以下はデータベースを作成する書式です。

CREATE DATABASE データベース名;

以下は、db1 というデータベースを作成する例です。

mysql> CREATE DATABASE db1;
Query OK, 1 row affected (0.01 sec)

データベースが問題なく作成されれば、「Query OK, 1 row affected (0.01 sec)」のように表示され、「Query OK」と表示されればステートメントは成功です。

ステートメントの最後にデリミタ「;」を付けずに、Enter キーを押すと「->」と表示されます。これはステートメントが完結していないという意味なので、ここで「;」を入力して Enter キーを押せばステートメントが実行されます。

データベースの確認

データベースの情報を確認するには、SHOW ステートメントを使用します。以下は現在存在するデータベース名を表示する書式です。

SHOW DATABASES;
//DATABASESと複数形

以下は実行結果の例です。実際に表示されるデータベースは他にもありますが、省略しています。一番最後の行に出力された行数が表示されます。

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| phpmyadmin         |
| test               |
  .....           
+--------------------+
15 rows in set (0.15 sec)

test や mysql データベース

先ほど作成した「db1」以外にも、「test」や「mysql」というデータベースが表示されていますが、これらは MySQL をインストールすると自動的に作成されるデータベースです。

test：自動的に作成される中身が空のテスト用データベース
mysql：MySQL の色々な情報（ユーザー情報等）が保管されているデータベース
information_schema：データベースまたはテーブルの名前、カラムのデータ型、アクセス権限などの MySQL Server に関する情報
performance_schema：MySQLの稼働統計や履歴に関する情報

SHOW ステートメントは MySQL 固有のステートメント

SHOW ステートメントを使用すると、データベース名やテーブル名など色々な情報を表示させることができますが、SHOW ステートメントは他の RDBMS の SQL にはない MySQL 固有のステートメントです。

データベースの指定

MySQL では、「どのデータベースを使用するのか」を明示的に宣言する必要があります。

データベースを指定するには USE ステートメントを使用します。以下が書式です。

USE データベース名

通常の SQL 文では、ステートメントの最後に「;」を付けますが、USE は SQL 文ではないので「;」を付けなくてもかまいません。以下は実行例です。

ステートメントを実行すると「Database changed」と表示されます。

mysql> USE db1
Database changed

現在使用しているデータベースの表示

MySQL では、データベースを選択していない状態で MySQL モニタを起動できるので、常にどのデータベースを使用しているのかを意識する必要があります。

現在どのデータベースを使用しているかを調べるには、以下のステートメントを使用します。

mysql> SELECT DATABASE();

以下は実行例です。

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| db1        |
+------------+
1 row in set (0.00 sec)

データベースを指定して MySQL モニタを起動

データベースを指定して MySQL モニタを起動するには、コマンドプロンプトで以下のようにデータベース名を指定して mysql コマンドを実行します。

C:\Users\username>mysql db1 -u root -p
Enter password:******

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

テーブルの作成

テーブルを作成する際には、データの種類やカラムの名前などの構造を指定する必要があります。

カラムに保存するデータの種類をデータ型といいます。データ型の詳細については「データ型」を参照ください。

この例では、以下のデータ型を使用します。

VARCHAR：文字のデータ。VARCHAR(10) は10個までの文字という意味です。
DATE：日付のデータ。YYYY-MM-DD の形式で入力します。

以下は作成するテーブル（tb1）の構造です。

テーブル tb1
内容	社員番号	名前	生年月日
カラム名	num	name	birth
データ型	VARCHAR(10)	VARCHAR(10)	DATE

テーブル名やカラム名には日本語を使用することもできますが、文字コード関連の問題が発生する可能性があるので、半角英数字を使用したほうが安全です。

テーブルの作成には、CREATE TABLE ステートメントを使用します。引数にはカラム名とそのデータ型をスペースを入れて記述し、カラムごとの記述をカンマ（,）で区切って記述します。以下が書式です。

CREATE TABLE テーブル名 (カラム名1 データ型1, カラム名2 データ型2, ...);

データベース db1 にテーブル tb1 を作成するので、データベース db1 が指定されている状態で以下のように入力します。

mysql> CREATE TABLE tb1 (num VARCHAR(10), name VARCHAR(10), birth DATE);
Query OK, 0 rows affected (0.12 sec)

「Query OK」と表示されれば成功です。

文字コードを指定してテーブルを作成

色々な原因で文字化けが起きますが、その対策の１つに「文字コードを指定してテーブルを作成する」という方法があります。但し、この方法で文字化けが解消するとは限りません。

CREATE TABLE ステートメントに「CHARSET=文字コード」というオプションを付けて実行します。

以下は、シフトJIS（sjis）を指定してテーブルを作成する例です。

CREATE TABLE tb1 (num VARCHAR(10), name VARCHAR(10), birth DATE) CHARSET=sjis;

コピーしてテーブルを作成

既存のテーブルのデータをコピーして、テーブルを作成することができます。詳細は「テーブルのコピー」を参照ください。

CREATE TABLE 新規テーブル名 SELECT * FROM 元となるテーブル名 WHERE 条件;

全てのテーブルを表示

データベースに存在する全てのテーブルを表示するには、SHOW TABLES ステートメントを使用します。

mysql> SHOW TABLES;
+---------------+
| Tables_in_db1 |
+---------------+
| tb1           |
+---------------+
1 row in set (0.00 sec)

この例では、データベース db1 が指定されている状態で上記ステートメントを実行したので、データベース db1 に含まれる全てのテーブルが表示されます。

データベース db1 が指定されている状態で他のテーブル（以下の例では mysql）を表示するには FROM を使い以下のようにします。

mysql> SHOW TABLES FROM mysql;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
　　・・・中略・・・
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
28 rows in set (0.00 sec)

テーブルのカラム構造の確認

作成したテーブルのカラムのデータ型などの構造を確認するには、DESC または DESCRIBE ステートメントを使用します。

mysql> DESC tb1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| num   | varchar(10) | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| birth | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

//または
mysql> DESCRIBE tb1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| num   | varchar(10) | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| birth | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.04 sec)

Field がカラム名、Type がデータ型を示します。また、Null は「何も入力しなくても許可するかどうか」、Default は「何も入力しない場合の値（初期値）」を意味します。

テーブルの詳細情報の表示

テーブルの詳細情報を表示するには、SHOW CREATE TABLE を使用します。表示結果は1行に表示されるデータが多いため、最後の「;」の代わりに「\G」を使用すると見やすくなります。

mysql> SHOW CREATE TABLE tb1\G
*************************** 1. row ***************************
       Table: tb1
Create Table: CREATE TABLE `tb1` (
  `id` varchar(10) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `birth` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ストレージエンジン（ENGINE=InnoDB）や言語設定（DEFAULT CHARSET=utf8）が表示されているのが確認できます。

主キーの設定

社員の ID 番号などのように重複してはいけないデータがあります。「必ず１つだけが特定できる」という状態をユニーク（unique/一意）であるといいます。

そのような場合使用するのが主キー(PRIMARY KEY)です。主キーはユニークなレコードを作りたいときに設定する識別子です。

主キーはレコードを厳密に特定できる以下のようなカラムです。

値の重複がない
何のデータも入力しないことはできない（NULLにならない）

テーブルを作成する際に、主キーを設定するには以下のように記述します。

CREATE TABLE テーブル名 (カラム名 データ型 PRIMARY KEY,...);

以下は、主キーとなる INT 型のカラム id と、 VARCHAR(30) 型のカラム name を持つテーブル tb2 を作成する例です。

mysql> CREATE TABLE tb2 (id INT PRIMARY KEY, name VARCHAR(30));
Query OK, 0 rows affected (0.01 sec)

mysql> DESC tb2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.04 sec)

DESC で表示された構造で「Key」に「PRI」と表示されているフィールドが主キー（PRIMARY KEY）です。

また、「Null」の項目が「NO」になっていて、「NULL」が入力できない（何も入力しないことはできない）ことを意味しています。

主キーを設定したカラムでは、INSERT や UPDATE ですでに存在する値を入力することはできません。

mysql> DESC tb2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.04 sec)

mysql> INSERT INTO tb2 VALUES (1, 'Smith');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tb2 VALUES (1, 'Davis');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
//すでに存在する値を入力するとエラーになる

mysql> INSERT INTO tb2 VALUES (NULL, 'Jackson');
ERROR 1048 (23000): Column 'id' cannot be null
//NULL を入力するとエラーになる

一意キーの設定

一意キー（UNIQUE）が設定されたカラムには重複する値は格納することができませんが、プライマリーキーとは異なり NULL を入れることはできます。また NULL の値だけは複数のカラムで重複して格納することができます。

テーブルを作成する際に、一意キーを設定するには以下のように記述します。

CREATE TABLE テーブル名 (カラム名 データ型 UNIQUE,...);

以下は、一意キーとなる VARCHAR(50) 型のカラム email と、 VARCHAR(30) 型のカラム name を持つテーブル tb3 を作成する例です。

mysql> CREATE TABLE tb3 (email VARCHAR(50) UNIQUE, name VARCHAR(30));
Query OK, 0 rows affected (0.02 sec)

mysql> DESC tb3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| email | varchar(50) | YES  | UNI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.04 sec)

「NULL」の項目が「YES」になっています。「何も入力しないことは許されるが、重複は許されない」というカラムになります。

連続番号機能付きカラムの定義

連続番号機能付きカラムは、自動的に連続する数値（連番）が格納されます。

連続番号機能付きカラムにするには以下の3つの設定が必要です。

データ型は INT などの整数型を指定
「AUTO_INCREMENT」というキーワードを付ける
「PRIMARY KEY」や「UNIQUE」などを設定して一意にする

CREATE TABLE テーブル名 (カラム名 INT AUTO_INCREMENT PRIMARY KEY,...);

連続番号機能付きカラム id と、 VARCHAR(30) 型のカラム color を持つテーブル tb4 を作成する例です。

mysql> CREATE TABLE tb4 (id INT AUTO_INCREMENT PRIMARY KEY, color VARCHAR(30));
Query OK, 0 rows affected (0.03 sec)

mysql> DESC tb4;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| color | varchar(30) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.03 sec)

「Extra」の項目に「auto_increment」と表示されて、連続番号機能が設定されていることが確認できます。

連続番号機能を使ってのレコードの挿入

連続番号機能が設定されているカラムで、自動的に連続番号を入力させるには以下のようにします。

何も入力しない（NULLを入力）
0を入力する

mysql> INSERT INTO tb4 (color) VALUES
    -> ('blue'),('green'),('red');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tb4;
+----+-------+
| id | color |
+----+-------+
|  1 | blue  |
|  2 | green |
|  3 | red   |
+----+-------+
3 rows in set (0.00 sec)

mysql> INSERT INTO tb4 VALUES (0, 'yellow');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tb4;
+----+--------+
| id | color  |
+----+--------+
|  1 | blue   |
|  2 | green  |
|  3 | red    |
|  4 | yellow |
+----+--------+
4 rows in set (0.00 sec)

連続番号機能が設定されているカラムには、自由な値を設定することもできます。例えば「INSERT INTO tb4 VALUES (100, 'black');」とすれば、そのカラムには「100」が入り、その後は「101」からの連番になります。

mysql> INSERT INTO tb4 VALUES (100, 'black');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tb4;
+-----+--------+
| id  | color  |
+-----+--------+
|   1 | blue   |
|   2 | green  |
|   3 | red    |
|   4 | yellow |
| 100 | black  |
+-----+--------+
5 rows in set (0.00 sec)

mysql> INSERT INTO tb4 (color) VALUES ('white');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tb4;
+-----+--------+
| id  | color  |
+-----+--------+
|   1 | blue   |
|   2 | green  |
|   3 | red    |
|   4 | yellow |
| 100 | black  |
| 101 | white  |
+-----+--------+
6 rows in set (0.00 sec)

またレコードを削除しても、番号は1から振り直されることはなく、それまであった最大値+1の値から始まります。

mysql> DELETE FROM tb4;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tb4 (color) VALUES ('gold');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tb4;
+-----+-------+
| id  | color |
+-----+-------+
| 102 | gold  |
+-----+-------+
1 row in set (0.00 sec)

連続番号の初期化

全てのレコードを削除し、連続番号の値を１から入力する場合は、以下のようにして「AUTO_INCREMENT」を初期化する必要があります。

ALTER TABLE テーブル名 AUTO_INCREMENT=1;

以下は全てのレコードを削除し、「AUTO_INCREMENT」を初期化する例です。

mysql> DELETE FROM tb4;
Query OK, 1 row affected (0.00 sec)

mysql> ALTER TABLE tb4 AUTO_INCREMENT = 1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO tb4 (color) VALUES ('silver');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tb4;
+----+--------+
| id | color  |
+----+--------+
|  1 | silver |
+----+--------+
1 row in set (0.00 sec)

カラムの初期値の設定

カラムに初期値（デフォルトの値）を設定することができます。初期値を設定しておけば、何も入力しなければ、予め設定してあるデータが自動的に入力されます。

カラムに初期値を設定するには、DEFAULT キーワードを付けます。

以下はテーブルを作成する際にカラムに初期値を設定する例です。

CREATE TABLE テーブル名 (カラム名 データ型 DEFAULT 初期値,...);

mysql> CREATE TABLE tb5 (name VARCHAR(20), division VARCHAR(30) DEFAULT '未定');

Query OK, 0 rows affected (0.04 sec)

mysql> DESC tb5;
+----------+-------------+------+-----+---------+-------+

| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name     | varchar(20) | YES  |     | NULL    |       |
| division | varchar(30) | YES  |     | 未定    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

mysql> INSERT INTO tb5 (name) VALUES ('秋山');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tb5;
+------+----------+
| name | division |
+------+----------+
| 秋山 | 未定     |
+------+----------+
1 row in set (0.00 sec)

以下は既存のテーブルにカラムの初期値を設する例です。

ALTER TABLE テーブル名 MODIFY カラム名 データ型 DEFAULT 初期値;

mysql> ALTER TABLE tb5 MODIFY name VARCHAR(20) DEFAULT '未記入';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC tb5;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name     | varchar(20) | YES  |     | 未記入  |       |
| division | varchar(30) | YES  |     | 未定    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.03 sec)

mysql> INSERT INTO tb5 (division) VALUES ('HQ');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tb5;
+--------+----------+
| name   | division |
+--------+----------+
| 秋山   | 未定     |
| 未記入 | HQ       |
+--------+----------+
2 rows in set (0.00 sec)

インデックスの設定

テーブルに設定する索引をインデックスといい、検索にはテーブルそのものではなく、インデックスを利用するようにすることができます。これによりレコード数が多い場合には、検索の時間が短縮される可能性があります。なお、「主キー」を設定した場合、自動的にインデックスも作成されています。

但し、インデックスを作成すれば必ず検索の時間が短縮されるとは限らず、場合によってはインデックスがあるために余計に時間がかかるということもあります。

「重複が多い場合（全ての値がyes、noだけなど）」インデックスを作成しないほうがよいとされています。また、インデックスが設定されているテーブルでデータを更新すれば、インデックスも作り直されるので検索は速くなりますが、逆に更新は遅くなる可能性が高くなります。

インデックスの設定は以下のようにします。

CREATE INDEX インデックス名 ON テーブル名（カラム名）;

以下はテーブル tb1 のカラム id に tb1_index という名前のインデックスを設定する例です。

mysql> CREATE INDEX tb1_index ON tb1 (id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

インデックスの表示・確認

インデックスの設定は以下のようにして確認できます。

SHOW INDEX FROM テーブル名;

mysql> SHOW INDEX FROM tb1;
+-------+------------+-----------+--------------+-------------+-----------+-----
--------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Card
inality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-----
--------+----------+--------+------+------------+---------+---------------+
| tb1   |          1 | tb1_index |            1 | id          | A         |
      5 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+-----------+--------------+-------------+-----------+-----
--------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

上記の表示では見づらいので、「;」の代わりに「\G」を使うと見易くなります。（最後に ; は不要です）

mysql> SHOW INDEX FROM tb1 \G
*************************** 1. row ***************************
        Table: tb1
   Non_unique: 1
     Key_name: tb1_index
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 5
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.00 sec)

インデックスの削除

設定したインデックスを削除するには、以下のようにします。

DROP INDEX インデックス名 ON テーブル名;

以下は、テーブル tb1 に設定してあるインデックス tb1_index を削除する例です。

mysql> DROP INDEX tb1_index ON tb1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM tb1 \G
Empty set (0.00 sec)

テーブルへのデータの挿入

データの挿入には、INSERT ステートメントを使用します。

もっとも単純な形式では、CREATE TABLE ステートメントに指定したカラムの順序に従って、各カラムの値を入力します。以下が書式です。

INSERT INTO テーブル名 VALUES(データ1, データ2, ...);

この例では、テーブル tb1 に以下のデータを挿入します。

テーブル tb1 に入れるデータ
num に入れる値	name に入れる値	birth に入れる値
A001	山田	1980-01-23
A002	鈴木	1959-12-25
A003	田中	1978-08-17
A004	青山	1967-09-17
A005	伊東	1984-10-10

最初の1件分のデータ（レコード）を挿入します。

mysql> INSERT INTO tb1 VALUES('A001', '山田', '1980-01-23');
Query OK, 1 row affected (0.00 sec)

name は VARCHAR(10) に設定してあるので、10文字を超える文字列を入力することはできません。但し、MySQL の場合、指定の文字数を超えて入力してもエラーにはならず、入りきれない文字は消えてしまうので注意が必要です。

カラム名を指定してのデータの挿入

前述の方法では、CREATE TABLE ステートメントに指定したカラムの順序でデータを記述しましたが、カラム名を指定してデータを挿入することで、指定したカラム名の順序でデータを挿入することができます。以下が書式です。

 INSERT INTO テーブル名 (カラム名1, カラム名2,...) VALUES (データ1, データ2, ...);

以下の例では、見やすいように VALUES の前で Enter キーを押して改行しています。SQL 文が長くなると読みにくくなるので途中で改行を入れることができます。但し、キーワードやステートメントの途中で改行するとエラーになります。

mysql> INSERT INTO tb1 (birth, name, num)
    -> VALUES ('1959', '鈴木', 'A002');
Query OK, 1 row affected, 1 warning (0.00 sec)

複数のデータの挿入

レコードを1行1行入力するのではなく、一度に複数のデータを挿入することもできます。以下が書式です。

INSERT INTO テーブル名 (カラム名1, カラム名2,...) VALUES(データ1, データ2, ...), (データ1, データ2, ...), ...;

mysql> INSERT INTO tb1 (num, name, birth) VALUES
    ->  ('A003', '田中', '1978-08-17'),
    ->  ('A004', '青山', '1967-09-17'),
    ->  ('A005', '伊東', '1984-10-10');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

テーブルへのデータのロード

空のテーブルから始める場合、各レコードに対応する行を記述したテキストファイルを作成してから、1 つのステートメントでそのファイルの内容をテーブルにロードすることができます。

例えば、テキストファイル tb1.txt を作成し、1 行に 1 レコードを記述します。値は、CREATE TABLE ステートメントに指定したカラムの順序に従い、タブで区切って指定します。

テキストファイルをテーブルにロードするには、次のステートメントを使用します。

LOAD DATA LOCAL INFILE '/パス/ファイル名' INTO TABLE テーブル名;

このファイルを Windows で作成した場合、作成に使用したエディタで \r\n が行ターミネータとして使用されているときは、代わりに次のステートメントを使用します。

LOAD DATA LOCAL INFILE '/パス/ファイル名' INTO TABLE テーブル名
-> LINES TERMINATED BY '\r\n';

また、この例では文字コードに UTF-8 を指定しているので、Windows のメモ帳ではなく UTF-8 が使用できるテキストエディタを使用する必要があります。

テキストエディタの例

以下はデスクトップに保存した上記ファイルの内容をテーブルにロードする例です。

mysql> LOAD DATA LOCAL INFILE 'Desktop/tb1.txt' INTO TABLE tb1
    -> LINES TERMINATED BY '\r\n';
Query OK, 5 rows affected (0.00 sec)
Records: 5  Deleted: 0  Skipped: 0  Warnings: 0

関連情報：ファイルのインポート

テキストエディタからのコピペ

UTF-8 が使用できるテキストエディタがない場合などは、Windows のメモ帳にコマンドを記述します。

メモ帳の例

全てを選択してコピーします。そして MySQL モニタで右クリックして「貼り付け」を選択します。

メモ帳の例「貼り付け」

値を貼り付けたら Enter キーを押して実行します。

メモ帳の例「貼り付け」２

このように一度作成した SQL 文をファイルに保存しておけば、後で使いまわすことができます。

テーブルのデータの表示

テーブルから情報を取り出すには、SELECT ステートメントを使用します。以下が書式です。

SELECT カラム名のリスト FROM テーブル名 WHERE 条件（オプション）;

テーブル tb1 から name と birth を表示させるには以下のようにします。

mysql> SELECT name, birth FROM tb1;
+------+------------+
| name | birth      |
+------+------------+
| 山田 | 1980-01-23 |
| 鈴木 | 0000-00-00 |
| 田中 | 1978-08-17 |
| 青山 | 1967-09-17 |
| 伊東 | 1984-10-10 |
+------+------------+
5 rows in set (0.00 sec)

※ 指定するカラムはカラム名のリストなので、それぞれのカラム名をカンマで区切ります。（最後のカラム名の後にカンマは付けません）

※ 指定するカラムの順番を変えれば、その順番で表示することができます。

すべてのデータの選択

SELECT のもっとも単純な形式では、テーブルのすべての内容が取り出されます。カラム名のリストを記述する代わりに「*」で全てのカラムを指定することができます。「*」はワイルドカードを意味します。

SELECT * FROM テーブル名;

以下は実行例です。

mysql> SELECT * FROM tb1;
+------+------+------------+
| num  | name | birth      |
+------+------+------------+
| A001 | 山田 | 1980-01-23 |
| A002 | 鈴木 | 0000-00-00 |
| A003 | 田中 | 1978-08-17 |
| A004 | 青山 | 1967-09-17 |
| A005 | 伊東 | 1984-10-10 |
+------+------+------------+
5 rows in set (0.00 sec)

他のデータベースのデータの表示

USE でデータベースを選択した状態で、他のデータベースにあるテーブルに対して操作することも可能です。

その場合、「データベース名.テーブル名」のようにデータベース名とテーブル名を「.」で繋げて記述します。

以下はデータベース「db1」を選択した状態で、データベース「mysql」のテーブル「user」のカラム「user」の情報を表示する例です。

mysql> select user from mysql.user;
+------+
| user |
+------+
| root |
| root |
|      |
| pma  |
| root |
+------+
5 rows in set (0.00 sec)

SELECT で値を表示

SELECT コマンドはデータベースやテーブルと関係のない値を表示することもできます。

mysql> SELECT 'Hello';
+-------+
| Hello |
+-------+
| Hello |
+-------+
1 row in set (0.00 sec)

mysql> SELECT now();
+---------------------+
| now()               |
+---------------------+
| 2016-06-07 19:13:58 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT VERSION(), CURRENT_DATE;
+-----------+--------------+
| VERSION() | CURRENT_DATE |
+-----------+--------------+
| 5.6.16    | 2016-06-07   |
+-----------+--------------+
1 row in set (0.00 sec)

mysql> SELECT (8*9)/4 + 76;
+--------------+
| (8*9)/4 + 76 |
+--------------+
|      94.0000 |
+--------------+
1 row in set (0.00 sec)

MySQL のデータ型

データベースでは、テーブルを作成する際、それぞれの形式のデータしか入力できないように設定します。「数値型」で指定したカラムには文字列や日付などは入力できません。このようなデータの形式をデータ型と呼びます。

但し、MySQL は他の RDBMS に比べるとデータの入力チェックが緩いので、データ型と異なるデータを入力してもエラーにならないことがあるので注意が必要です。

以下は MySQL で設定できる代表的なデータ型です。（他にも多数の SQL データ型があります）

数値型

以下は数値型の主なものです。


データ型	意味	範囲
TINYINT	とても小さな整数	-128 ～ 127
SMALLINT	小さな整数	-32768 ～ 32767
MEDIUMINT	中くらいの整数	-8388608 ～ 8388607
INT	整数	-2147483648 ～ 2147483647
BIGINT	大きい整数	-9223372036854775808 ～ 9223372036854775807
FLOAT	単精度浮動小数点数	-3.402823466E+38 ～ -1.175494351E-38 0 1.175494351E-38 ～ 3.402823466E+38
DOUBLE	倍精度浮動小数点数	-1.7976931348623157E+308 ～ -2.2250738585072014E-308 0 2.2250738585072014E-308 ～ 1.7976931348623157E+308

数値データの入力

数値データは正負の符号（+, -）を付けて入力することもできます。以下はテーブル tb5 の INT型の point カラムに -123 を挿入する例です。

mysql> INSERT INTO tb5 (point) VALUES (-123);
Query OK, 1 row affected (0.00 sec)

指数表現を使って入力することもできます。以下はテーブル tb5 の INT型の point カラムに 123万（1230000 または 123E+4）を挿入する例です。

mysql> INSERT INTO tb5 (point) VALUES (123E+4);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT point FROM tb5;
+---------+
| pointr  |
+---------+
|    -123 |
| 1230000 |
+---------+
3 rows in set (0.00 sec)

文字列型

以下は文字列型の主なものです。


データ型	意味	範囲
CHAR	固定長の文字列	255 バイトまで
VARCHAR	可変長の文字列	65,535 バイトまで
TEXT	長い文字列	65,535 バイトまで
LONGTEXT	とても長い文字列	4,294,967,295 バイトまで

CHAR 型と VARCHAR 型には、格納する最大文字数を表す長さを指定できます。たとえば、CHAR(30) には最大 30 文字を格納できます。

CHAR 値は固定長で格納されると、指定された長さになるように右側がスペースで埋められます。但し、取り出されるときはデフォルトでは、末尾のスペースが削除されます。

CHAR および VARCHAR 型

文字列の入力

文字列データは、ダブルまたはシングルクォーテーション（" または '）で囲んで入力します。

文字列の中で、囲み文字として使用したダブルまたはシングルクォーテーション（" または '）を使う場合は、その前にバックスラッシュ（\）を付けます。（バックスラッシュは環境により円マークになります）

mysql> INSERT INTO tb1 (name) VALUES ("石井");
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO tb1 (name) VALUES ("'ヤマダ'");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tb1 (name) VALUES ("\"トニー\"");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT name from tb1;
+----------+
| name     |
+----------+
| 石井     |
| 'ヤマダ' |
| "トニー" |
+----------+
8 rows in set (0.00 sec)

日付・時刻型

以下は日付・時刻型の主なものです。


データ型	意味	範囲
DATETIME	日付と時刻	1000-01-01 00:00:00 ～ 9999-12-31 23:59:59 ('YYYY-MM-DD HH:MM:SS' 形式)
DATE	日付	1000-01-01 ～ 9999-12-31 ('YYYY-MM-DD' 形式)
TIME	時刻	-838:59:59.000000 ～ 838:59:59.000000 ('HH:MM:SS' 形式または 'HHH:MM:SS' 形式)
YEAR	年	1901 ～ 2155（4 桁）(YYYY の形式) 1970 ～ 2069（70 ～ 69 2 桁）非推奨

日付・時刻型の入力

日付・時刻型データは、ダブルまたはシングルクォーテーション（" または '）で囲んで入力します。

日付は 'YYYY-MM-DD' 形式、時刻は 'HH:MM:SS' 形式で入力します。時刻は経過時間など時間の部分の値が大きい場合は 'HHH:MM:SS' 形式を使います。

mysql> INSERT INTO tb1 (birth) VALUES ('2016-06-05');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT birth FROM tb1;
+------------+
| birth      |
+------------+
| 1980-01-23 |
| 1978-08-17 |
　　・・・
| 2016-06-05 |
+------------+
9 rows in set (0.00 sec)

テーブルの変更

テーブルのカラム構造を変更するには、ALTER TABLE コマンドを使用します。

変更の種類により以下の構文を使用します。

カラムの定義の変更：ALTER TABLE ... MODIFY ...
カラムの追加：ALTER TABLE ... ADD ...
カラムの名前と定義を変更：ALTER TABLE ... CHANGE ...
カラムの削除：ALTER TABLE ... DROP ...

カラムのデータ型の変更

カラムのデータ型は、データ型が対応可能な変更の場合、いつでも変更可能です。対応できない変更をすればエラーになり、また、変更はできても「意味のない値」になったり、「データが消える」こともありえるので注意が必要です。

例えば、50文字入力されているカラムを VARCHAR(10) に変更すれば10文字以降は消えてしまいます。

また、先頭が0になっていない、数値の文字だけで構成されているデータの場合、「INT型」→「VARCHAR型」→「INT型」のような変更は可能ですが、基本的にカラムにデータが存在する場合は、データ型の変更を行うべきではありません。

以下は、カラムのデータ型変更の構文です。

ALTER TABLE テーブル名 MODIFY カラム名 データ型;

以下のような構造のテーブルのカラム name を50文字まで入力できるようにします。

mysql> DESC tb1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| num   | varchar(10) | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| birth | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

mysql> ALTER TABLE tb1 MODIFY name VARCHAR(50);
Query OK, 5 rows affected (0.07 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> DESC tb1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| num   | varchar(10) | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| birth | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

カラムの位置の変更

以下は MODIFY を使ったカラムの順序の変更方法です。データ型を変更する必要がなければ、同じデータ型を指定します。

カラム位置を先頭に変更 FIRST

ALTER TABLE テーブル名 MODIFY カラム名 データ型 FIRST;

変更前

mysql> DESC tb1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| num   | varchar(10) | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| birth | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

mysql> ALTER TABLE tb1 MODIFY birth DATE FIRST;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC tb1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| birth | date        | YES  |     | NULL    |       |
| num   | varchar(10) | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

カラム位置を任意の位置に変更 AFTER

以下は対象となるカラムの後にカラムの位置を変更する構文です。

ALTER TABLE テーブル名 MODIFY カラム名 データ型 AFTER 対象となるカラム名;

変更前

mysql> DESC tb1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| birth | date        | YES  |     | NULL    |       |
| num   | varchar(10) | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

テーブル tb1 のカラム birth を num の後に変更します。

mysql> ALTER TABLE tb1 MODIFY birth DATE AFTER num;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC tb1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| num   | varchar(10) | YES  |     | NULL    |       |
| birth | date        | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

カラムの追加

以下は ADD を使ったカラムの追加方法です。位置（FIRST/AFTER）を指定しない場合は、最後の位置に追加されます。

新規のカラムを最後に追加

ALTER TABLE テーブル名 ADD カラム名 データ型;

変更前

mysql> DESC tb1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| num   | varchar(10) | YES  |     | NULL    |       |
| birth | date        | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

テーブル tb1 の VARCHAR(10) の gender というカラムを追加する例です。

mysql> ALTER TABLE tb1 ADD gender VARCHAR(10);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC tb1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| num    | varchar(10) | YES  |     | NULL    |       |
| birth  | date        | YES  |     | NULL    |       |
| name   | varchar(50) | YES  |     | NULL    |       |
| gender | varchar(10) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

新規のカラムを先頭に追加

先頭に追加するには FIRST を付けます。

ALTER TABLE テーブル名 ADD カラム名 データ型 FIRST;

テーブル tb1 の VARCHAR(20) の divsion というカラムを先頭に追加する例です。

mysql> ALTER TABLE tb1 ADD divsion VARCHAR(20) FIRST;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC tb1;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| divsion | varchar(20) | YES  |     | NULL    |       |
| num     | varchar(10) | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| name    | varchar(50) | YES  |     | NULL    |       |
| gender  | varchar(10) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.02 sec)

新規のカラムを任意の位置に追加

任意の位置に追加するには AFTER を使い対象となるカラム名を指定します。

ALTER TABLE テーブル名 ADD カラム名 データ型 AFTER 挿入する位置のカラム名;

テーブル tb1 の VARCHAR(30) の title というカラムを name の後に追加する例です。

mysql> ALTER TABLE tb1 ADD title VARCHAR(30) AFTER name;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC tb1;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| divsion | varchar(20) | YES  |     | NULL    |       |
| num     | varchar(10) | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| name    | varchar(50) | YES  |     | NULL    |       |
| title   | varchar(30) | YES  |     | NULL    |       |
| gender  | varchar(10) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.03 sec)

カラムの名前と定義の変更

カラムの名前を含めてデータ型・位置を変更するには、ALTER TABLE ... CHANGE を使います。

ALTER TABLE テーブル名 CHANGE 変更前カラム名 変更後カラム名 変更後データ型;

テーブル tb1 の VARCHAR(10) の num というカラムを VARCHAR(20) に変更し、名前を id に変更する例です。

mysql> ALTER TABLE tb1 CHANGE num id VARCHAR(20);
Query OK, 5 rows affected (0.08 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> DESC tb1;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| divsion | varchar(20) | YES  |     | NULL    |       |
| id      | varchar(20) | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| name    | varchar(50) | YES  |     | NULL    |       |
| title   | varchar(30) | YES  |     | NULL    |       |
| gender  | varchar(10) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

カラムの削除

SQL ではカラムやデータベース、テーブルなどを削除する場合は DROP コマンドを使用します。

以下はカラムを削除する書式です。カラムを削除するとそのカラムに格納されていたデータも削除されます。

ALTER TABLE テーブル名 DROP カラム名;

テーブル tb1 の gender というカラムを削除する例です。

mysql> ALTER TABLE tb1 DROP gender;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC tb1;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| divsion | varchar(20) | YES  |     | NULL    |       |
| id      | varchar(20) | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| name    | varchar(50) | YES  |     | NULL    |       |
| title   | varchar(30) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

テーブルのコピー

テーブルのカラム構造やデータのコピー方法です。以下のような方法でコピーができます。

テーブルのカラム構造とレコードのコピー
テーブルのカラム構造だけをコピー
データをコピー

テーブルのカラム構造とレコードのコピー

テーブルのカラム構造とレコードをコピーする方法です。

SELECT した結果から、カラム構造とデータをコピーし、新規テーブルを作成します。「SELECT * FROM 」としているので全てのレコードをコピーします。

但し、この方法では「AUTO_INCREMENT」などの一部の属性はコピーされないので、コピー後設定する必要があります。DESC で構造を確認して必要な設定を追加してください。

CREATE TABLE 新規テーブル名 SELECT * FROM 元となるテーブル名;

以下は元となるテーブル tb1 です。

mysql> SELECT * FROM tb1;
+------+------------+------+
| id   | birth      | name |
+------+------------+------+
| A001 | 1980-01-23 | 山田 |
| A002 | 0000-00-00 | 鈴木 |
| A003 | 1978-08-17 | 田中 |
| A004 | 1967-09-17 | 青山 |
| A005 | 1984-10-10 | 伊東 |
+------+------------+------+
5 rows in set (0.00 sec)

mysql> DESC tb1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | varchar(20) | YES  |     | NULL    |       |
| birth | date        | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.04 sec)

以下コピーの実行とその結果です。

mysql> CREATE TABLE tb1c1 SELECT * FROM tb1;
Query OK, 5 rows affected (0.04 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tb1c1;
+------+------------+------+
| id   | birth      | name |
+------+------------+------+
| A001 | 1980-01-23 | 山田 |
| A002 | 0000-00-00 | 鈴木 |
| A003 | 1978-08-17 | 田中 |
| A004 | 1967-09-17 | 青山 |
| A005 | 1984-10-10 | 伊東 |
+------+------------+------+
5 rows in set (0.00 sec)

mysql> DESC tb1c1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | varchar(20) | YES  |     | NULL    |       |
| birth | date        | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.04 sec)

テーブルのカラム構造だけをコピー

以下は、テーブルのカラム構造だけをコピーして、新規にテーブルを作成します。

この方法では「AUTO_INCREMENT」、「PRIMARY KEY」などの属性もコピーされます。

以下が書式です。

CREATE TABLE 新規テーブル名 LIKE 元となるテーブル名;

以下は実行結果です。構造のみがコピーされています。データはコピーされません。

mysql> CREATE TABLE tb1c2 LIKE tb1;
Query OK, 0 rows affected (0.04 sec)

mysql> DESC tb1c2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | varchar(20) | YES  |     | NULL    |       |
| birth | date        | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> SELECT * FROM tb1c2;
Empty set (0.00 sec)

他のテーブルのデータをコピー

以下は既存のテーブルに他のテーブルのデータ（レコード）をコピーする方法です。

同じカラム構造のテーブルのデータを全てコピーするには以下のようにします。

INSERT INTO 既存テーブル名 SELECT * FROM 元となるテーブル名;

以下の例では、前述のテーブルのカラム構造だけをコピーしたテーブル tb1c2 に、テーブル tb1 の全てのレコードをコピーします。

mysql> SELECT * FROM tb1c2;  //空のテーブル
Empty set (0.00 sec) 

mysql> INSERT INTO tb1c2 SELECT * FROM tb1;
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tb1c2;
+------+------------+------+
| id   | birth      | name |
+------+------------+------+
| A001 | 1980-01-23 | 山田 |
| A002 | 0000-00-00 | 鈴木 |
| A003 | 1978-08-17 | 田中 |
| A004 | 1967-09-17 | 青山 |
| A005 | 1984-10-10 | 伊東 |
+------+------------+------+
5 rows in set (0.00 sec)

特定のカラムを選択してコピー

コピー元の特定のカラムのデータを選択してコピーすることもできます。但し、コピー元とコピー先のカラムのデータ型が同じである必要があります。

INSERT INTO テーブル名 (カラム名) SELECT 元となるカラム名 元となるテーブル名;

空のテーブル tb1c3 のカラム name に、テーブル tb1 のカラム name をコピーする例です。

mysql> SELECT * FROM tb1c3;
Empty set (0.00 sec)

mysql> INSERT INTO tb1c3 (name) SELECT name FROM tb1;
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tb1c3;
+------+-------+------+
| id   | birth | name |
+------+-------+------+
| NULL | NULL  | 山田 |
| NULL | NULL  | 鈴木 |
| NULL | NULL  | 田中 |
| NULL | NULL  | 青山 |
| NULL | NULL  | 伊東 |
+------+-------+------+
5 rows in set (0.00 sec)

コピーしたカラム name にはデータがコピーされますが、他のカラムには NULL が入ります。

条件に一致したレコードをコピーする方法は「条件に一致したレコードをコピー」を参照ください。

テーブル・レコードの削除

一度削除してしまうと、基本的に元に戻すことはできないので、削除の際は注意が必要です。

テーブルの削除

以下はテーブルを削除する書式です。

DROP TABLE テーブル名;

mysql> DROP TABLE tb1c3;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM tb1c3;
ERROR 1146 (42S02): Table 'db1.tb1c3' doesn't exist

指定のテーブルがある場合にのみ削除

IF EXISTS（もし存在すれば）を付けると、指定のテーブルが存在する場合のみテーブルを削除することができます。

DROP TABLE IF EXISTS テーブル名;

存在しないテーブルに対して DROP TABLE を行うとエラーになりますが、IF EXISTS を使うとエラーになりません。SQL コマンドをファイルから読み込んで実行する場合などに使用するとエラーの発生を回避できます。

mysql> DROP TABLE tb1c3;
ERROR 1051 (42S02): Unknown table 'db1.tb1c3'  
//テーブルが存在しないのでエラー

mysql> DROP TABLE IF EXISTS tb1c3;
Query OK, 0 rows affected, 1 warning (0.00 sec)
//テーブルが存在しなくてもエラーにならない

データベースの削除

データベースの削除も DROP コマンドで行います。以下が書式です。

これを実行すると、データベースを元に戻すことはできません。データベース内の全てのテーブルや情報が消えてしまうので注意が必要です。

DROP DATABASE データベース名;

mysql> DROP DATABASE db1test;
Query OK, 0 rows affected (0.02 sec)

全てのレコードの削除

以下はテーブル自体は削除せずに、テーブルの全てのレコード（データ）を削除する書式です。

DELETE FROM テーブル名;

以下はテーブル tb1c1 の全レコードを削除する例です。

mysql> SELECT * FROM tb1c1;
+------+------------+------+
| id   | birth      | name |
+------+------------+------+
| A001 | 1980-01-23 | 山田 |
| A002 | 0000-00-00 | 鈴木 |
| A003 | 1978-08-17 | 田中 |
| A004 | 1967-09-17 | 青山 |
| A005 | 1984-10-10 | 伊東 |
+------+------------+------+
5 rows in set (0.00 sec)

mysql> DELETE FROM tb1c1;
Query OK, 5 rows affected (0.00 sec)

mysql> SELECT * FROM tb1c1;
Empty set (0.00 sec)

特定のレコードの削除

レコードを選択して削除するには、WHERE などで条件を設定します。

DELETE FROM テーブル名 WHERE 条件;

以下は、テーブル tb1c2 でカラム id の値が 'A005' のレコードを削除する例です。

mysql> SELECT * FROM tb1c2;
+------+------------+------+
| id   | birth      | name |
+------+------------+------+
| A001 | 1980-01-23 | 山田 |
| A002 | 0000-00-00 | 鈴木 |
| A003 | 1978-08-17 | 田中 |
| A004 | 1967-09-17 | 青山 |
| A005 | 1984-10-10 | 伊東 |
+------+------------+------+
5 rows in set (0.00 sec)

mysql> DELETE FROM tb1c2 WHERE id = 'A005';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tb1c2;
+------+------------+------+
| id   | birth      | name |
+------+------------+------+
| A001 | 1980-01-23 | 山田 |
| A002 | 0000-00-00 | 鈴木 |
| A003 | 1978-08-17 | 田中 |
| A004 | 1967-09-17 | 青山 |
+------+------------+------+
4 rows in set (0.00 sec)

「条件に一致したレコードを削除」も参照ください。

データの抽出 SELECT

SELECT を使うと色々な表示ができます。また、SELECT を使っていろいろな条件でレコードを表示（抽出）することができます。

エイリアスを使って表示
カラムの値を計算
関数を使って計算
比較演算子
文字列の操作
日付・時刻の操作
条件を指定して選択
レコード数を決めて表示 LIMIT
WHERE を使って条件設定
あいまい検索 LIKE
エスケープ
BINARY 演算子（大文字・小文字を区別）
NULL を使った条件
重複したデータを省いて抽出 DISTINCT
AND / OR
CASE 演算子（条件分岐）
並べ替え ORDER
グループ化 GROUP
個数を数える COUNT()
合計を算出 SUM()
平均を算出 AVG()
条件付グループ表示 HAVING
抽出してからグループ化
グループ化してから並べ替える
グループ化の手順

サンプルとして以下のようなデータベース db1 のテーブル tb と tb1 を使用します。

mysql> USE db1;
Database changed
mysql> CREATE TABLE tb (id VARCHAR(10), sales INT, month INT);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO tb (id, sales, month) VALUES
    -> ('C001', 100, 4),
    -> ('C003', 77, 5),
    -> ('C007', 312, 4),
    -> ('C002', 238, 4),
    -> ('C004', 98, 5),
    -> ('C006', 156, 4),
    -> ('C001', 210, 5),
    -> ('C007', 180, 5),
    -> ('C004', 222, 6),
    -> ('C003', 198, 6);
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> DESC tb;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | varchar(10) | YES  |     | NULL    |       |
| sales | int(11)     | YES  |     | NULL    |       |
| month | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.05 sec)

mysql> SELECT * FROM tb;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C003 |    77 |     5 |
| C007 |   312 |     4 |
| C002 |   238 |     4 |
| C004 |    98 |     5 |
| C006 |   156 |     4 |
| C001 |   210 |     5 |
| C007 |   180 |     5 |
| C004 |   222 |     6 |
| C003 |   198 |     6 |
+------+-------+-------+
10 rows in set (0.00 sec)

テーブル tb は、月々の社員ごとの売り上げのデータです。社員番号（id）、売り上げ（sales）、売り上げ月（month）のカラムから構成されています。

テーブル tb1 は、社員データのテーブルで、社員番号（id）、名前（name）、誕生日（birth）のカラムから構成されています。

mysql> CREATE TABLE tb1 (id VARCHAR(10), name VARCHAR(20), birth DATE);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO tb1 (id, name, birth) VALUES
    -> ('C001',  '山田', '1980-01-23'),
    -> ('C002',  '鈴木', '1959-12-25'),
    -> ('C003',  '田中',  '1978-08-15'),
    -> ('C004',  '青山',  '1967-09-17'),
    -> ('C005',  '伊東',  '1984-10-10'),
    -> ('C006',  '津村',  '1974-06-19'),
    -> ('C007',  '加藤',  '1990-03-03');
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> DESC tb1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | varchar(10) | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| birth | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.04 sec)

mysql> SELECT * FROM tb1;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
| C001 | 山田 | 1980-01-23 |
| C002 | 鈴木 | 1959-12-25 |
| C003 | 田中 | 1978-08-15 |
| C004 | 青山 | 1967-09-17 |
| C005 | 伊東 | 1984-10-10 |
| C006 | 津村 | 1974-06-19 |
| C007 | 加藤 | 1990-03-03 |
+------+------+------------+
7 rows in set (0.00 sec)

エイリアスを使って表示

SELECT * FROM tb; を実行すると、1行目にカラム名が表示されますが、カラム名の代わりにエイリアス（別名）で表示することができます。

SELECT カラム名 AS エイリアス FROM テーブル名;

複数のカラムを指定する場合は、カンマで区切って「カラム名 AS エイリアス」とします。

また、エイリアス名に特殊記号（括弧など）を使う場合はエイリアス名を「""」（ダブルクォート）で囲みます。

mysql> SELECT * FROM tb;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C003 |    77 |     5 |
| C007 |   312 |     4 |
| C002 |   238 |     4 |
| C004 |    98 |     5 |
| C006 |   156 |     4 |
| C001 |   210 |     5 |
| C007 |   180 |     5 |
| C004 |   222 |     6 |
| C003 |   198 |     6 |
+------+-------+-------+
10 rows in set (0.00 sec)

mysql> SELECT id AS 社員番号, sales AS 売り上げ, month AS 月 FROM tb;
+----------+----------+------+
| 社員番号 | 売り上げ | 月   |
+----------+----------+------+
| C001     |      100 |    4 |
| C003     |       77 |    5 |
| C007     |      312 |    4 |
| C002     |      238 |    4 |
| C004     |       98 |    5 |
| C006     |      156 |    4 |
| C001     |      210 |    5 |
| C007     |      180 |    5 |
| C004     |      222 |    6 |
| C003     |      198 |    6 |
+----------+----------+------+
10 rows in set (0.00 sec)

カラムの値を計算

カラムの値に対して四則演算を行うことができます。

SELECT col * 100 FROM tb;   //カラム col の値を100倍して表示
SELECT col / 10 FROM tb;   //カラム col の値を10分の1にして表示

例えば、テーブル tb の売り上げの単位がドル（$）だった場合、これを円に変換して表示する場合以下のように簡単に計算して表示することができます。（以下は1ドル100円の換算レートとしています）

mysql> SELECT sales * 100 AS "売り上げ（円）" FROM tb;
+----------------+
| 売り上げ（円） |
+----------------+
|          10000 |
|           7700 |
|          31200 |
|          23800 |
|           9800 |
|          15600 |
|          21000 |
|          18000 |
|          22200 |
|          19800 |
+----------------+
10 rows in set (0.00 sec)

また、カラムの値同士の計算も可能です。

SELECT col1 + col2 FROM tb1; //カラム col1 とカラム col2 の値を足して表示

関数を使って計算

SQL の場合、関数の引数に指定するのは多くの場合カラム名になります。例えば AVG() という平均を返す関数の場合、AVG(col) とすれば、カラム col のデータの平均を返します。

関数処理の対象となるレコードを限定するには、「WHERE」などを使った条件で抽出し、また「GROUP BY」を使ってグループごとの値を計算することもできます。

【備考】AVG, SUM, MAXなどの集計を行う関数は「GROUP BY関数」とも呼ばれ、本来はグループ化されたときの値を処理するものですが、「GROUP BY」がない場合は、テーブル全体を１つのグループとして処理が行われます。

SELECT AVG(カラム名) FROM テーブル名; //平均
SELECT SUM(カラム名) FROM テーブル名;  //合計
SELECT MAX(カラム名) FROM テーブル名;  //最大値
SELECT MIN(カラム名) FROM テーブル名;  //最小値
SELECT COUNT(カラム名) FROM テーブル名;   //個数

以下はこれらの関数を使う例です。

mysql> SELECT * FROM tb;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C003 |    77 |     5 |
| C007 |   312 |     4 |
| C002 |   238 |     4 |
| C004 |    98 |     5 |
| C006 |   156 |     4 |
| C001 |   210 |     5 |
| C007 |   180 |     5 |
| C004 |   222 |     6 |
| C003 |   198 |     6 |
+------+-------+-------+
10 rows in set (0.00 sec)

mysql> SELECT AVG(sales) FROM tb;
+------------+
| AVG(sales) |  //平均値
+------------+
|   179.1000 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT SUM(sales) FROM tb;
+------------+
| SUM(sales) |  //合計値
+------------+
|       1791 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT MAX(sales) FROM tb;
+------------+
| MAX(sales) |  //最大値
+------------+
|        312 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT MIN(sales) FROM tb;
+------------+
| MIN(sales) |  //最小値
+------------+
|         77 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(sales) FROM tb;
+--------------+
| COUNT(sales) |  //個数
+--------------+
|           10 |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT SUM(sales), MAX(sales), MIN(sales), AVG(sales) from tb;
+------------+------------+------------+------------+
| SUM(sales) | MAX(sales) | MIN(sales) | AVG(sales) |
+------------+------------+------------+------------+
|       1791 |        312 |         77 |   179.1000 |
+------------+------------+------------+------------+
1 row in set (0.00 sec)

MySQL にはこの他にも多数の関数が用意されています。「MySQL 関数と演算子」

数値関数と演算子

比較演算子

比較演算子は値と値を比較するために使用します。

比較演算の結果は、1 (TRUE)、0 (FALSE)、または NULL の値になります。これらの演算は、数字と文字列の両方で機能します。必要に応じて、文字列は数字に、数字は文字列に自動的に変換されます。

デフォルトでは、文字列の比較では大文字と小文字が区別されず、現在の文字セットが使用されます。

比較関数と演算子

以下は MySQL で利用できる比較演算子の例です。


演算子（名前）	例	説明
=　等価演算子	A = B	A と B は等しい
<=>　NULL 安全等価演算子	A <=> B	A と B は等しい（NULL 安全）
>　右不等演算子	A > B	A は B よりも大きい
>=　以上演算子	A >= B	A は B よりも大きいか等しい
<　左不等演算子	A < B	A は B よりも小さい
<=　以下演算子	A <= B	A は B よりも小さいか等しい
!= または <>　不等価演算子	A != B　A <> B	A と B は等しくない

等価演算子

mysql> SELECT 1 = 1, 2 = 3, '青' = '青', '.01' = 0.01;
+-------+-------+-------------+--------------+
| 1 = 1 | 2 = 3 | '青' = '青' | '.01' = 0.01 |
+-------+-------+-------------+--------------+
|     1 |     0 |           1 |            1 |
+-------+-------+-------------+--------------+
1 row in set (0.00 sec)

必要に応じて、文字列は数字に、数字は文字列に自動的に変換されます。

mysql> SELECT .01 = '0.01', .01 = 0.01, '.01' = '0.01';
+--------------+------------+----------------+
| .01 = '0.01' | .01 = 0.01 | '.01' = '0.01' |
+--------------+------------+----------------+
|            1 |          1 |              0 |
+--------------+------------+----------------+
1 row in set (0.00 sec)

NULL との比較では、以下のように NULL が返ります。

mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
+-------+-------------+----------+
| 1 = 1 | NULL = NULL | 1 = NULL |
+-------+-------------+----------+
|     1 |        NULL |     NULL |
+-------+-------------+----------+
1 row in set (0.00 sec)

NULL 安全等価演算子

この演算子では、= 演算子のように等価比較が実行されますが、両方のオペランドが NULL であれば、NULL でなく 1 が返され、一方のオペランドが NULL の場合は、NULL でなく 0 が返されます。

mysql> SELECT 1 <=> 1, NULL <=> NULL, 0 <=> NULL;
+---------+---------------+------------+
| 1 <=> 1 | NULL <=> NULL | 0 <=> NULL |
+---------+---------------+------------+
|       1 |             1 |          0 |
+---------+---------------+------------+
1 row in set (0.00 sec)

右不等演算子

より多い

mysql> SELECT 2 > 2;
+-------+
| 2 > 2 |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

以上演算子

より多いか等しい

mysql> SELECT 2 >= 2;
+--------+
| 2 >= 2 |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

左不等演算子

より少ない

mysql> SELECT 3 < 2;
+-------+
| 3 < 2 |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

以下演算子

より少ないか等しい

mysql> SELECT 5 <= 5.01;
+-----------+
| 5 <= 5.01 |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

不等価演算子

等しくない

mysql> SELECT 1 <> 2, 3 <> 3, 1 <> '1', 'abc' <> 'DEF';
+--------+--------+----------+----------------+
| 1 <> 2 | 3 <> 3 | 1 <> '1' | 'abc' <> 'DEF' |
+--------+--------+----------+----------------+
|      1 |      0 |        0 |              1 |
+--------+--------+----------+----------------+
1 row in set (0.00 sec)

文字列の操作

MySQL には文字列を操作する関数も多数用意されています。

文字列の結合：CONCAT関数

文字列を結合するには CONCAT 関数を使用します。

カラム a, b, c の文字をつなげるには「CONCAT(a,b,c)」とします。

また、文字列を直接指定することもできます。その場合、文字列はダブルまたはシングルクォーテーション（" または '）で囲みます。

CONCAT(str1,str2,...)
//引数を連結することで生成される文字列を返します。

以下は、カラム id 、文字列": "、カラム name を結合して表示する例です。

mysql> SELECT * FROM tb1;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
| C001 | 山田 | 1980-01-23 |
| C002 | 鈴木 | 1959-12-25 |
| C003 | 田中 | 1978-08-15 |
| C004 | 青山 | 1967-09-17 |
| C005 | 伊東 | 1984-10-10 |
| C006 | 津村 | 1974-06-19 |
| C007 | 加藤 | 1990-03-03 |
+------+------+------------+
7 rows in set (0.00 sec)

mysql> SELECT CONCAT(id, "： ", name) FROM tb1;
+-------------------------+
| CONCAT(id, "： ", name) |
+-------------------------+
| C001： 山田             |
| C002： 鈴木             |
| C003： 田中             |
| C004： 青山             |
| C005： 伊東             |
| C006： 津村             |
| C007： 加藤             |
+-------------------------+
7 rows in set (0.00 sec)

以下は、文字列"社員番号 "、カラム id 、文字列": "、カラム name を結合し、エイリアスを使ってカラム名を表示する例です。

mysql> SELECT CONCAT("社員番号 ", id, "： ", name) AS 社員リスト FROM tb1;
+----------------------+
| 社員リスト           |
+----------------------+
| 社員番号 C001： 山田 |
| 社員番号 C002： 鈴木 |
| 社員番号 C003： 田中 |
| 社員番号 C004： 青山 |
| 社員番号 C005： 伊東 |
| 社員番号 C006： 津村 |
| 社員番号 C007： 加藤 |
+----------------------+
7 rows in set (0.00 sec)

文字列の取り出し：RIGHT, LEFT, SUBSTRING 関数

文字列を取り出すには RIGHT, LEFT, SUBSTRING 関数を使用します。

右から取り出す：RIGHT関数: SELECT RIGHT(col, 2) FROM tb1;
カラム col の右から2文字を表示

左から取り出す：LEFT関数: SELECT LEFT(col, 2) FROM tb1;
カラム col の左から2文字を表示

x 番目から y 個取り出す：SUBSTRING関数: SELECT SUBSTRING(col, 2, 3) FROM tb1;
カラム col の2文字目から3文字分を表示

mysql> SELECT * FROM tb1;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
| C001 | 山田 | 1980-01-23 |
| C002 | 鈴木 | 1959-12-25 |
| C003 | 田中 | 1978-08-15 |
| C004 | 青山 | 1967-09-17 |
| C005 | 伊東 | 1984-10-10 |
| C006 | 津村 | 1974-06-19 |
| C007 | 加藤 | 1990-03-03 |
+------+------+------------+
7 rows in set (0.00 sec)

mysql> SELECT RIGHT(birth, 2) FROM tb1;
+-----------------+
| RIGHT(birth, 2) |
+-----------------+
| 23              |
| 25              |
| 15              |
| 17              |
| 10              |
| 19              |
| 03              |
+-----------------+
7 rows in set (0.00 sec)

mysql> SELECT LEFT(birth, 4) FROM tb1;
+----------------+
| LEFT(birth, 4) |
+----------------+
| 1980           |
| 1959           |
| 1978           |
| 1967           |
| 1984           |
| 1974           |
| 1990           |
+----------------+
7 rows in set (0.00 sec)

mysql> SELECT SUBSTRING(birth, 6, 2) FROM tb1;
+------------------------+
| SUBSTRING(birth, 6, 2) |
+------------------------+
| 01                     |
| 12                     |
| 08                     |
| 09                     |
| 10                     |
| 06                     |
| 03                     |
+------------------------+
7 rows in set (0.00 sec)

繰り返して表示：REPEAT 関数

文字列を繰り返し表示するには、REPEAT 関数を使います。

REPEAT(str,count)
//文字列 str を count 回繰り返します。

以下はテーブル tb のカラム sales の値に応じて★を表示する例です。

sales の値は大きいので適当な値（この例では30）で割って、その値の小数点以下を切り下げ（FLOOR関数）、その値の分だけ★を表示しています。

mysql> SELECT * FROM tb;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C003 |    77 |     5 |
| C007 |   312 |     4 |
| C002 |   238 |     4 |
| C004 |    98 |     5 |
| C006 |   156 |     4 |
| C001 |   210 |     5 |
| C007 |   180 |     5 |
| C004 |   222 |     6 |
| C003 |   198 |     6 |
+------+-------+-------+
10 rows in set (0.00 sec)

mysql> SELECT id, REPEAT('★', FLOOR(sales / 30)) FROM tb;
+------+---------------------------------+
| id   | REPEAT('★', FLOOR(sales / 30)) |
+------+---------------------------------+
| C001 | ★★★                          |
| C003 | ★★                            |
| C007 | ★★★★★★★★★★            |
| C002 | ★★★★★★★                  |
| C004 | ★★★                          |
| C006 | ★★★★★                      |
| C001 | ★★★★★★★                  |
| C007 | ★★★★★★                    |
| C004 | ★★★★★★★                  |
| C003 | ★★★★★★                    |
+------+---------------------------------+
10 rows in set (0.00 sec)

文字列の長さを返す：CHAR_LENGTH, LENGTH 関数

文字列の長さを返す関数はいくつかありますが、文字数で返すものと。バイト単位で返すものがあります。

マルチバイト文字は、複数のバイトとしてカウントされます。つまり、5 つの 2 バイト文字を含む文字列では、CHAR_LENGTH() は 5 を返し、LENGTH() は 10 を返します。

CHAR_LENGTH(str)
//文字列 str の長さ（文字数）を返します。

LENGTH(str)
//文字列 str の長さをバイト単位で返します

以下は日本語（マルチバイト文字）のカラム name に対して CHAR_LENGTH 及び LENGTH 関数を実行した例です。それぞれ文字数とバイト数が返されています。

mysql> SELECT * FROM tb1;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
| C001 | 山田 | 1980-01-23 |
| C002 | 鈴木 | 1959-12-25 |
| C003 | 田中 | 1978-08-15 |
| C004 | 青山 | 1967-09-17 |
| C005 | 伊東 | 1984-10-10 |
| C006 | 津村 | 1974-06-19 |
| C007 | 加藤 | 1990-03-03 |
+------+------+------------+
7 rows in set (0.00 sec)

mysql> SELECT CHAR_LENGTH(name), LENGTH(name) FROM tb1;
+-------------------+--------------+
| CHAR_LENGTH(name) | LENGTH(name) |
+-------------------+--------------+
|                 2 |            6 |
|                 2 |            6 |
|                 2 |            6 |
|                 2 |            6 |
|                 2 |            6 |
|                 2 |            6 |
|                 2 |            6 |
+-------------------+--------------+
7 rows in set (0.00 sec)

文字列関数

日付・時刻の操作

現在の日時を返す： NOW 関数

NOW 関数は現在の日付と時刻を返す関数です。

NOW() は日付と時刻の両方を返すので、入力するカラムのデータ型は「DATETIME」型に設定します。

以下は、連続番号機能を使ったカラム num（INT型）と NOW() を使って入力するカラム datetime（DATETIME型）から成るテーブル tbn の例です。

カラム num には連続番号機能を設定しているので自動的に番号が入力されます。

カラム datetime には NOW() を使って現在（その時点での）日時を入力します。

mysql> CREATE TABLE tbn (num INT AUTO_INCREMENT PRIMARY KEY, nowtime DATETIME);
Query OK, 0 rows affected (0.05 sec)

mysql> DESC tbn;
+---------+----------+------+-----+---------+----------------+
| Field   | Type     | Null | Key | Default | Extra          |
+---------+----------+------+-----+---------+----------------+
| num     | int(11)  | NO   | PRI | NULL    | auto_increment |
| nowtime | datetime | YES  |     | NULL    |                |
+---------+----------+------+-----+---------+----------------+
2 rows in set (0.04 sec)

mysql> INSERT INTO tbn (nowtime) VALUES(NOW());
Query OK, 1 row affected (0.00 sec)

......

mysql> INSERT INTO tbn (nowtime) VALUES(NOW());
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tbn;
+-----+---------------------+
| num | nowtime             |
+-----+---------------------+
|   1 | 2016-06-08 21:52:52 |
|   2 | 2016-06-08 21:52:56 |
|   3 | 2016-06-08 21:52:58 |
|   4 | 2016-06-08 21:53:00 |
|   5 | 2016-06-08 21:53:02 |
+-----+---------------------+
5 rows in set (0.00 sec)

日付を指定された書式に設定：DATE_FORMAT 関数

DATE_FORMAT 関数は日時の値を指定された書式（フォーマット）に変換します。以下が関数の構文です。

DATE_FORMAT(date,format)
//format 文字列に従って、date 値を書式設定します。

format 文字列では、以下の指定子を利用できます。（指定子はこの他にもあります）


指定子	説明
%a	簡略曜日名 (Sun..Sat)
%b	簡略月名 (Jan..Dec)
%c	月、数字 (0..12)
%D	英語のサフィクスを持つ日付 (0th, 1st, 2nd, 3rd, …)
%d	日、数字 (00..31)
%e	日、数字 (0..31)
%H	時間 (00..23)
%h	時間 (01..12)
%i	分、数字 (00..59)
%j	年間通算日 (001..366)
%k	時 (0..23)
%l	時 (1..12)
%M	月名 (January..December)
%m	月、数字 (00..12)
%S	秒 (00..59)
%W	曜日名 (Sunday..Saturday)
%w	曜日 (0=Sunday..6=Saturday)
%Y	年、数字、4 桁
%y	年、数字 (2 桁)

以下は、指定子を利用して書式を指定して表示する例です。書式の中には文字列を入れることができます。

mysql> SELECT * FROM tbn;
+-----+---------------------+
| num | nowtime             |
+-----+---------------------+
|   1 | 2016-06-08 21:52:52 |
|   2 | 2016-06-08 21:52:56 |
|   3 | 2016-06-08 21:52:58 |
|   4 | 2016-06-08 21:53:00 |
|   5 | 2016-06-08 21:53:02 |
+-----+---------------------+
5 rows in set (0.00 sec)

mysql> SELECT DATE_FORMAT(nowtime, '%W %M %D %Y %H時 %i分 %s秒') FROM tbn;
+----------------------------------------------------+
| DATE_FORMAT(nowtime, '%W %M %D %Y %H時 %i分 %s秒') |
+----------------------------------------------------+
| Wednesday June 8th 2016 21時 52分 52秒             |
| Wednesday June 8th 2016 21時 52分 56秒             |
| Wednesday June 8th 2016 21時 52分 58秒             |
| Wednesday June 8th 2016 21時 53分 00秒             |
| Wednesday June 8th 2016 21時 53分 02秒             |
+----------------------------------------------------+
5 rows in set (0.00 sec)

生年月日から年齢を算出

生年月日から年齢を算出するには以下のようにします。

(今日の日付 - 誕生日の日付)/10000 //小数点以下切捨て

例えば、今日の日付が2016年6月9日で、誕生日が1977年3月17日の場合、以下のように算出できます。

(20160609 - 19770317) / 10000 → 39.0292 → 39歳

今日の日付は CURDATE() 関数で求められます。

CURDATE() は文字列と数値コンテキストのどちらで使用されているのかに応じて、現在の日付を 'YYYY-MM-DD' または YYYYMMDD 書式の値として返します。

mysql> SELECT CURDATE(), CURDATE() + 0;
+------------+---------------+
| CURDATE()  | CURDATE() + 0 |
+------------+---------------+
| 2016-06-09 |      20160609 |
+------------+---------------+
1 row in set (0.00 sec)

以下のような生年月日のカラム birth があるテーブルでそれぞれの現時点での年齢を算出します。

mysql> SELECT * FROM tb1;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
| C001 | 山田 | 1980-01-23 |
| C002 | 鈴木 | 1959-12-25 |
| C003 | 田中 | 1978-08-15 |
| C004 | 青山 | 1967-09-17 |
| C005 | 伊東 | 1984-10-10 |
| C006 | 津村 | 1974-06-19 |
| C007 | 加藤 | 1990-03-03 |
+------+------+------------+
7 rows in set (0.00 sec)

mysql> DESC tb1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | varchar(10) | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| birth | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.04 sec)

mysql> SELECT name, FLOOR(((CURDATE() + 0) - (birth + 0))/10000)
    -> AS 年齢 FROM tb1;
+------+------+
| name | 年齢 |
+------+------+
| 山田 |   36 |
| 鈴木 |   56 |
| 田中 |   37 |
| 青山 |   48 |
| 伊東 |   31 |
| 津村 |   41 |
| 加藤 |   26 |
+------+------+
7 rows in set (0.00 sec)

または、TRUNCATE() 関数を使って以下のようにも記述できます。

mysql> SELECT name, TRUNCATE(((CURDATE() + 0) - (birth + 0))/10000, 0)
    -> AS 年齢 FROM tb1;
+------+------+
| name | 年齢 |
+------+------+
| 山田 |   36 |
| 鈴木 |   56 |
| 田中 |   37 |
| 青山 |   48 |
| 伊東 |   31 |
| 津村 |   41 |
| 加藤 |   26 |
+------+------+
7 rows in set (0.00 sec)

または、年を引き算して、月日の部分を比較して、誕生日を過ぎていたらそのままで、誕生日がまだだったら1歳引くという方法もあります。

年の引き算には、日付から年を返す YEAR() 関数を利用します。

月日の部分の比較は、DATE_FORMAT() を使うか、RIGHT() を使って月日の部分を取り出して比較します。

また、比較演算子を使用すると TRUE の場合は 1 を、FALSE の場合は 0 を返すのを利用します。

mysql> SELECT DATE_FORMAT(CURDATE(), '%m%d');
+--------------------------------+
| DATE_FORMAT(CURDATE(), '%m%d') |
+--------------------------------+
| 0609                           |
+--------------------------------+
1 row in set (0.00 sec)

mysql> SELECT RIGHT(CURDATE(), 5);
+---------------------+
| RIGHT(CURDATE(), 5) |
+---------------------+
| 06-09               |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT name, (YEAR(CURDATE()) - YEAR(birth))
    -> - (DATE_FORMAT(CURDATE(), '%m%d') < DATE_FORMAT(birth, '%m%d'))
    -> AS 年齢 FROM tb1;
+------+------+
| name | 年齢 |
+------+------+
| 山田 |   36 |
| 鈴木 |   56 |
| 田中 |   37 |
| 青山 |   48 |
| 伊東 |   31 |
| 津村 |   41 |
| 加藤 |   26 |
+------+------+
7 rows in set (0.00 sec)

mysql> SELECT name, (YEAR(CURDATE()) - YEAR(birth))
    -> - (RIGHT(CURDATE(), 5) < RIGHT(birth, 5))
    -> AS 年齢 FROM tb1;
+------+------+
| name | 年齢 |
+------+------+
| 山田 |   36 |
| 鈴木 |   56 |
| 田中 |   37 |
| 青山 |   48 |
| 伊東 |   31 |
| 津村 |   41 |
| 加藤 |   26 |
+------+------+
7 rows in set (0.00 sec)

日付および時間関数

条件を指定して選択

SELECT を使ってレコードを表示する場合、条件を指定して表示するレコードを選択することができます。

レコード数を決めて表示 LIMIT

表示するレコード数を限定するには、LIMIT を使用します。

SELECT カラム名 FROM テーブル名 LIMIT 表示するレコード数;

大量にレコードを表示すると時間がかかってしまうので、LIMIT で表示数を制限すると結果が早く表示される場合があります。

mysql> SELECT * FROM tb;  //全て表示
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C003 |    77 |     5 |
| C007 |   312 |     4 |
| C002 |   238 |     4 |
| C004 |    98 |     5 |
| C006 |   156 |     4 |
| C001 |   210 |     5 |
| C007 |   180 |     5 |
| C004 |   222 |     6 |
| C003 |   198 |     6 |
+------+-------+-------+
10 rows in set (0.01 sec)

mysql> SELECT * FROM tb LIMIT 3;  //3件のみ表示
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C003 |    77 |     5 |
| C007 |   312 |     4 |
+------+-------+-------+
3 rows in set (0.00 sec)

m～n 件目のデータの取り出し

m～n 件目のデータの取り出すには、LIMIT の後に取得開始位置（何行スキップするか）と件数を指定します。

開始位置は0がスタートになります。

SELECT カラム名 FROM テーブル名 LIMIT 取得開始位置, 件数;

以下は、3行スキップして、4件目～7件目のレコード（4件）を表示する例です。

mysql> SELECT * FROM tb LIMIT 3, 4;  //4件目～7件目を表示
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C002 |   238 |     4 |
| C004 |    98 |     5 |
| C006 |   156 |     4 |
| C001 |   210 |     5 |
+------+-------+-------+
4 rows in set (0.00 sec)

または、OFFSET を使って以下のように記述することもできます。前述の方法とは、取得開始位置と件数の順番が逆になっています。

SELECT カラム名 FROM テーブル名 LIMIT 件数 OFFSET 取得開始位置;

以下は、3行スキップして、4件目～7件目のレコード（4件）を表示する例です。

mysql> SELECT * FROM tb LIMIT 4 OFFSET 3;  //4件目～7件目を表示
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C002 |   238 |     4 |
| C004 |    98 |     5 |
| C006 |   156 |     4 |
| C001 |   210 |     5 |
+------+-------+-------+
4 rows in set (0.00 sec)

WHERE を使って条件設定

WHERE を使って条件を設定して、その条件に一致したレコードだけを取り出すことができます。以下が書式です。

SELECT カラム名 FROM テーブル名 WHERE 条件;

以下はテーブル tb1 のカラム sales の値が200以上のレコードを表示する例です。

mysql> SELECT * FROM tb;  //全て表示
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C003 |    77 |     5 |
| C007 |   312 |     4 |
| C002 |   238 |     4 |
| C004 |    98 |     5 |
| C006 |   156 |     4 |
| C001 |   210 |     5 |
| C007 |   180 |     5 |
| C004 |   222 |     6 |
| C003 |   198 |     6 |
+------+-------+-------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM tb WHERE sales >= 200;  
//sales の値が200以上のレコードを表示
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C007 |   312 |     4 |
| C002 |   238 |     4 |
| C001 |   210 |     5 |
| C004 |   222 |     6 |
+------+-------+-------+
4 rows in set (0.00 sec)

MySQL では以下のような比較演算子が使えます。「比較演算子」も参照ください。

比較演算子
比較演算子	意味
A = B	A と B は等しい
A > B	A は B より大きい
A >= B	A は B よりも大きいか等しい(以上）
A < B	A は B より小さい
A <= B	A は B よりも小さいか等しい（以下）
A <> B	A と B は等しくない
○　IN 　△	○がリスト△の中にある
○　NOT IN 　△	○がリスト△の中にない
○　BETWEEN 　× AND ××	○が ×から××の間にある
○　NOT BETWEEN 　× AND ××	○が ×から××の間にない

以下は使用例です。

mysql> SELECT * FROM tb;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C003 |    77 |     5 |
| C007 |   312 |     4 |
| C002 |   238 |     4 |
| C004 |    98 |     5 |
| C006 |   156 |     4 |
| C001 |   210 |     5 |
| C007 |   180 |     5 |
| C004 |   222 |     6 |
| C003 |   198 |     6 |
+------+-------+-------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM tb WHERE id = 'C007';  
//カラム id の値が'C007' のレコード
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C007 |   312 |     4 |
| C007 |   180 |     5 |
+------+-------+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM tb WHERE month <> 4;
//カラム month の値が4でないレコード
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C003 |    77 |     5 |
| C004 |    98 |     5 |
| C001 |   210 |     5 |
| C007 |   180 |     5 |
| C004 |   222 |     6 |
| C003 |   198 |     6 |
+------+-------+-------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM tb WHERE sales BETWEEN 50 AND 100;
//カラム sales の値が50と100の間にある（50以上100以下）
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C003 |    77 |     5 |
| C004 |    98 |     5 |
+------+-------+-------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM tb WHERE sales NOT BETWEEN 100 AND 300;
//カラム sales の値が100と300の間にない（100未満、または300を超える）
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C003 |    77 |     5 |
| C007 |   312 |     4 |
| C004 |    98 |     5 |
+------+-------+-------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM tb WHERE month IN (5, 6);
//カラム month の値が5か6のどちらか
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C003 |    77 |     5 |
| C004 |    98 |     5 |
| C001 |   210 |     5 |
| C007 |   180 |     5 |
| C004 |   222 |     6 |
| C003 |   198 |     6 |
+------+-------+-------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM tb WHERE month NOT IN (5, 6);
//カラム month の値が5か6でない
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C007 |   312 |     4 |
| C002 |   238 |     4 |
| C006 |   156 |     4 |
+------+-------+-------+
4 rows in set (0.00 sec)

※ MySQLのデフォルトの設定では大文字、小文字を区別しないで検索しますが、「=」によって等価比較をするときは、大文字、小文字を区別します。

あいまい検索 LIKE

「～の文字を含む」というような条件で検索する場合は、LIKE を使用します。

デフォルトでは LIKE の検索は、大文字、小文字を区別しません。（BINARY 演算子を使うと区別することができます）

ワイルドカードとして使用できるのは次の2文字です。


ワイルドカード	意味
%	任意の0文字以上の文字列にマッチ
_	任意の1文字にマッチ

以下はあいまい検索の例です。

mysql> SELECT * FROM tb1;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
| C001 | 山田 | 1980-01-23 |
| C002 | 鈴木 | 1959-12-25 |
| C003 | 田中 | 1978-08-15 |
| C004 | 青山 | 1967-09-17 |
| C005 | 伊東 | 1984-10-10 |
| C006 | 津村 | 1974-06-19 |
| C007 | 加藤 | 1990-03-03 |
+------+------+------------+
7 rows in set (0.28 sec)

mysql> SELECT * FROM tb1 WHERE birth LIKE '%08%';
//カラム birth の値が 08 を含む
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
| C003 | 田中 | 1978-08-15 |
+------+------+------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT * FROM tb1 WHERE birth LIKE '%3';
//カラム birth の値が 3 で終わる
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
| C001 | 山田 | 1980-01-23 |
| C007 | 加藤 | 1990-03-03 |
+------+------+------------+
2 rows in set, 1 warning (0.00 sec)

mysql> SELECT * FROM tb1 WHERE name LIKE '_山';
//カラム name の値が 山 で終わる
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
| C004 | 青山 | 1967-09-17 |
+------+------+------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM tb1 WHERE name LIKE '%山%';
//カラム name の値が 山 を含む
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
| C001 | 山田 | 1980-01-23 |
| C004 | 青山 | 1967-09-17 |
+------+------+------------+
2 rows in set (0.00 sec)

指定した文字列を含まないものを検索： NOT LIKE

指定した文字列を含まないものを検索するには、NOT LIKE を使用します。

mysql> SELECT * FROM tb1;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
| C001 | 山田 | 1980-01-23 |
| C002 | 鈴木 | 1959-12-25 |
| C003 | 田中 | 1978-08-15 |
| C004 | 青山 | 1967-09-17 |
| C005 | 伊東 | 1984-10-10 |
| C006 | 津村 | 1974-06-19 |
| C007 | 加藤 | 1990-03-03 |
+------+------+------------+
7 rows in set (0.00 sec)

mysql> SELECT * FROM tb1 WHERE birth NOT LIKE '%0%';
//カラム birth の値が 0 を含まないもの
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
| C002 | 鈴木 | 1959-12-25 |
+------+------+------------+
1 row in set, 1 warning (0.00 sec)

エスケープ

'%' や '_' にマッチさせたい場合には '\' 文字を使ってエスケープを行います。

また '\' はエスケープ文字として使用されるため、'\'を文字としてマッチさせる場合にもエスケープを行います。（\ はバックスラッシュです。環境によっては円マークとして表示されます）

文字としての'\'にマッチさせるには、'\\\\'と記述することになります。

\%
\_
\\\\

以下はエスケープを使った例です。

mysql> SELECT * FROM tbe;
+-------------+
| strings     |
+-------------+
| 99.8%       |
| TEST_STRING |
| 100         |
| \100        |
+-------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM tbe WHERE strings LIKE '%\%%';
//カラム strings の値が % を含む
+---------+
| strings |
+---------+
| 99.8%   |
+---------+
1 row in set (0.00 sec)

mysql> SELECT * FROM tbe WHERE strings LIKE '%\_%';
//カラム strings の値が _ を含む
+-------------+
| strings     |
+-------------+
| TEST_STRING |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM tbe WHERE strings LIKE '\\%';
Empty set (0.00 sec)

mysql> SELECT * FROM tbe WHERE strings LIKE '\\\\%';
//カラム strings の値が \ を含む
+---------+
| strings |
+---------+
| \100    |
+---------+
1 row in set (0.00 sec)

BINARY 演算子（大文字・小文字を区別）

文字列比較の際、デフォルトでは大文字と小文字を区別しませんが、区別したい場合は BINARY 演算子を使用します。

（BINARY 演算子は、それに続く文字列をバイナリ文字列にキャストし、比較を文字ごとでなくバイトごとに強制的に実行させます。）

mysql> SELECT * FROM tbc;
+----------+
| city     |
+----------+
| New York |
| new york |
| Tokyo    |
| tokyo    |
+----------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM tbc WHERE city LIKE 'new%';
+----------+
| city     |
+----------+
| New York |
| new york |
+----------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM tbc WHERE city LIKE BINARY 'new%';
+----------+
| city     |
+----------+
| new york |
+----------+
1 row in set (0.00 sec)

NULL を使った条件

NULL は「何もない値」です。カラムにデータを指定せず、初期値が設定されていなければ、そのカラムには NULL が入ります。

mysql> DESC tb1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | varchar(10) | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| birth | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.03 sec)

mysql> INSERT INTO tb1 (id, name) VALUES ('C008', '一之瀬');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tb1;
+------+--------+------------+
| id   | name   | birth      |
+------+--------+------------+
| C001 | 山田   | 1980-01-23 |
| C002 | 鈴木   | 1959-12-25 |
| C003 | 田中   | 1978-08-15 |
| C004 | 青山   | 1967-09-17 |
| C005 | 伊東   | 1984-10-10 |
| C006 | 津村   | 1974-06-19 |
| C007 | 加藤   | 1990-03-03 |
| C008 | 一之瀬 | NULL       |
+------+--------+------------+
8 rows in set (0.00 sec)

NULL は特殊な値のため、上記のテーブル tb1 で NULL の値を持つレコードを抽出するために条件を「WHERE birth = NULL」としても抽出できません。

NULLの場合：IS NULL

カラムの値が NULL のレコードを抽出するときは、IS NULLを使います。

mysql> SELECT * FROM tb1 WHERE birth = NULL; //抽出できない
Empty set (0.00 sec)

mysql> SELECT * FROM tb1 WHERE birth IS NULL; //IS NULL を使う
+------+--------+-------+
| id   | name   | birth |
+------+--------+-------+
| C008 | 一之瀬 | NULL  |
+------+--------+-------+
1 row in set (0.00 sec)

NULLでない場合：IS NOT NULL

カラムの値が NULL でないレコードを抽出するときは、IS NOT NULLを使います。

mysql> SELECT * FROM tb1 WHERE birth IS NOT NULL;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
| C001 | 山田 | 1980-01-23 |
| C002 | 鈴木 | 1959-12-25 |
| C003 | 田中 | 1978-08-15 |
| C004 | 青山 | 1967-09-17 |
| C005 | 伊東 | 1984-10-10 |
| C006 | 津村 | 1974-06-19 |
| C007 | 加藤 | 1990-03-03 |
+------+------+------------+
7 rows in set (0.00 sec)

重複したデータを省いて抽出 DISTINCT

重複した値（同じ値）が複数ある場合に、重複したデータを１つだけ表示させるには「DISTINCT」を使います。

mysql> SELECT id FROM tb;
+------+
| id   |
+------+
| C001 |
| C003 |
| C007 |
| C002 |
| C004 |
| C006 |
| C001 |
| C007 |
| C004 |
| C003 |
+------+
10 rows in set (0.00 sec)

mysql> SELECT DISTINCT id FROM tb;
+------+
| id   |
+------+
| C001 |
| C003 |
| C007 |
| C002 |
| C004 |
| C006 |
+------+
6 rows in set (0.00 sec)

AND / OR

「○○であり、かつ△△である」を意味する AND（または &&）、「○○であるか、または△△である」を意味する OR（または ||）は、「論理演算子」と呼ばれます。AND や OR を使うと WHERE などで条件を複数設定することができます。

以下はテーブル tb のカラム sales の値が100以上かつ200以下のレコードを抽出する例です。

この例の場合は比較演算子「○ BETWEEN × AND ××」を使っても同じことが可能です。

mysql> SELECT * FROM tb;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C003 |    77 |     5 |
| C007 |   312 |     4 |
| C002 |   238 |     4 |
| C004 |    98 |     5 |
| C006 |   156 |     4 |
| C001 |   210 |     5 |
| C007 |   180 |     5 |
| C004 |   222 |     6 |
| C003 |   198 |     6 |
+------+-------+-------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM tb WHERE sales >=100 AND sales <= 200;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C006 |   156 |     4 |
| C007 |   180 |     5 |
| C003 |   198 |     6 |
+------+-------+-------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM tb WHERE sales BETWEEN 100 AND 200;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C006 |   156 |     4 |
| C007 |   180 |     5 |
| C003 |   198 |     6 |
+------+-------+-------+
4 rows in set (0.00 sec)

以下はテーブル tb のカラム id の値が C007 でかつ、カラム month の値が5のレコードを抽出する例です。

mysql> SELECT * FROM tb WHERE id = 'C007' AND month = 5;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C007 |   180 |     5 |
+------+-------+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM tb WHERE id = 'C007' && month = 5;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C007 |   180 |     5 |
+------+-------+-------+
1 row in set (0.00 sec)

以下はテーブル tb のカラム sales の値が100未満、または300を超えるレコードを抽出する例です。

この例の場合は比較演算子「○ NOT BETWEEN × AND ××」を使っても同じことが可能です。

mysql> SELECT * FROM tb;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C003 |    77 |     5 |
| C007 |   312 |     4 |
| C002 |   238 |     4 |
| C004 |    98 |     5 |
| C006 |   156 |     4 |
| C001 |   210 |     5 |
| C007 |   180 |     5 |
| C004 |   222 |     6 |
| C003 |   198 |     6 |
+------+-------+-------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM tb WHERE sales < 100 OR sales > 300;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C003 |    77 |     5 |
| C007 |   312 |     4 |
| C004 |    98 |     5 |
+------+-------+-------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM tb WHERE sales NOT BETWEEN 100 AND 300;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C003 |    77 |     5 |
| C007 |   312 |     4 |
| C004 |    98 |     5 |
+------+-------+-------+
3 rows in set (0.00 sec)

AND や OR を複数使う

AND や OR は何回でも使うことができ、より複雑な条件を設定することができます。

以下はテーブル tb の「カラム id の値が 1 で終わり、かつ、カラム month の値が4」または「カラム sales の値が100未満」のレコードを抽出する例です。

mysql> SELECT * FROM tb;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C003 |    77 |     5 |
| C007 |   312 |     4 |
| C002 |   238 |     4 |
| C004 |    98 |     5 |
| C006 |   156 |     4 |
| C001 |   210 |     5 |
| C007 |   180 |     5 |
| C004 |   222 |     6 |
| C003 |   198 |     6 |
+------+-------+-------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM tb WHERE id LIKE '%1' AND month = 4 OR sales < 100;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C003 |    77 |     5 |
| C004 |    98 |     5 |
+------+-------+-------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM tb WHERE sales < 100 OR id LIKE '%1' AND month = 4;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C003 |    77 |     5 |
| C004 |    98 |     5 |
+------+-------+-------+
3 rows in set (0.00 sec)

以下は、同じ意味になります。

SELECT * FROM tb WHERE id LIKE '%1' AND month = 4 OR sales < 100;
SELECT * FROM tb WHERE sales < 100 OR id LIKE '%1' AND month = 4;

これは、AND が優先して処理されるためどちらの場合でも「id LIKE '%1' AND month = 4」が先に処理されるためです。

演算子には「演算子の優先順位」があり、AND の方が OR より優先順位が高くなっています。

演算子の優先順位によって、式の項の評価順序が決まります。この順序をオーバーライドし、明示的に項をグループ化するには、丸括弧を使用します。

AND と OR が混ざっているときは、AND が優先されて処理されます
丸括弧（）で囲むとその処理を優先して行います

以下は「sales が200以上、または id の値の最後が1」で、かつ「 month の値が5」のレコードを表示する例です。

mysql> SELECT * FROM tb WHERE (sales >= 200 OR id LIKE '%3') AND month = 5;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C003 |    77 |     5 |
| C001 |   210 |     5 |
+------+-------+-------+
2 rows in set (0.00 sec)

CASE 演算子（条件分岐）

条件によって値を変えて表示するには、CASE 演算子を使用します。言い換えると CASE 演算子を使うと条件分岐ができます。

以下が CASE 文の書式です。条件が真であれば、値1が、偽であれば値2が使われます。

CASE WHEN 条件 THEN 値1 ELSE 値2 END

複数の条件を設定する場合は、以下のような書式になります。

CASE
　　WHEN 条件1 THEN 条件1が真の場合の値
　　WHEN 条件2 THEN 条件2が真の場合の値
　　・・・
　　ELSE 全ての条件に当てはまらないときの値
END

以下は、テーブル tb でカラム sales の値が200以上の場合は"Excellent"と表示し、100以上の場合は"Good"表示し、それ以外の場合は"Fair"と表示する例です。

mysql> SELECT * FROM tb;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C003 |    77 |     5 |
| C007 |   312 |     4 |
| C002 |   238 |     4 |
| C004 |    98 |     5 |
| C006 |   156 |     4 |
| C001 |   210 |     5 |
| C007 |   180 |     5 |
| C004 |   222 |     6 |
| C003 |   198 |     6 |
+------+-------+-------+
10 rows in set (0.00 sec)

mysql> SELECT
 CASE
   WHEN sales >= 200 THEN "Excellent"
   WHEN sales >= 100 THEN "Good"
   ELSE "Fair"
 END
FROM tb;

+----------------------------------------------------------------------------
--------+
| CASE
WHEN sales >= 200 THEN "Excellent"
WHEN sales >= 100 THEN "Good"
ELSE "Fair"
END |
+----------------------------------------------------------------------------
--------+
| Good
        |
| Fair
        |
| Excellent
        |
| Excellent
        |
| Fair
        |
| Good
        |
| Excellent
        |
| Good
        |
| Excellent
        |
| Good
        |
+----------------------------------------------------------------------------
--------+
10 rows in set (0.00 sec)

以下はカラム id, sales も表示し、見やすくするため、エイリアス（AS）を使って表示する例です。

また、* を使って全てのカラムを表示する場合、* の後にもカンマが必要です。

mysql> SELECT id, sales,
  CASE
    WHEN sales >= 200 THEN "Excellent"
    WHEN sales >= 100 THEN "Good"
    ELSE "Fair"
  END AS Rating
FROM tb;
+------+-------+-----------+
| id   | sales | Rating    |
+------+-------+-----------+
| C001 |   100 | Good      |
| C003 |    77 | Fair      |
| C007 |   312 | Excellent |
| C002 |   238 | Excellent |
| C004 |    98 | Fair      |
| C006 |   156 | Good      |
| C001 |   210 | Excellent |
| C007 |   180 | Good      |
| C004 |   222 | Excellent |
| C003 |   198 | Good      |
+------+-------+-----------+
10 rows in set (0.00 sec)

mysql> SELECT * ,
  CASE
    WHEN sales >= 200 THEN "Excellent"
    WHEN sales >= 100 THEN "Good"
    ELSE "Fair"
  END AS Rating
FROM tb;
+------+-------+-------+-----------+
| id   | sales | month | Rating    |
+------+-------+-------+-----------+
| C001 |   100 |     4 | Good      |
| C003 |    77 |     5 | Fair      |
| C007 |   312 |     4 | Excellent |
| C002 |   238 |     4 | Excellent |
| C004 |    98 |     5 | Fair      |
| C006 |   156 |     4 | Good      |
| C001 |   210 |     5 | Excellent |
| C007 |   180 |     5 | Good      |
| C004 |   222 |     6 | Excellent |
| C003 |   198 |     6 | Good      |
+------+-------+-------+-----------+
10 rows in set (0.00 sec)

次の例は、カラム sales の値により、1または0を設定してその合計を表示する例です。

mysql> SELECT
  COUNT(*),
  SUM(CASE WHEN sales >= 200 THEN 1 ELSE 0 END) AS Excellent,
  SUM(CASE WHEN sales >=100 && sales < 200 THEN 1 ELSE 0 END) AS Good,
  SUM(CASE WHEN sales < 100 THEN 1 ELSE 0 END) AS Fair
FROM tb;
+----------+-----------+------+------+
| COUNT(*) | Excellent | Good | Fair |
+----------+-----------+------+------+
|       10 |         4 |    4 |    2 |
+----------+-----------+------+------+
1 row in set (0.00 sec)

並べ替え ORDER

SELECT を使って表示するレコードの順序は不規則です。レコードを指定したカラムの値の順序で表示させるには、ORDER を使います。

以下は、レコードを昇順（小さいものから大きいもの）に表示させる書式です。

SELECT カラム名 FROM テーブル名 ORDER BY キーとなるカラム名; 
//昇順（Ascending）を明示する場合は、ASCを付けます（上記と同じこと）
SELECT カラム名 FROM テーブル名 ORDER BY キーとなるカラム名 ASC;

以下はテーブル tb のカラム sales の値で昇順に表示する例です。

mysql> SELECT * FROM tb;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C003 |    77 |     5 |
| C007 |   312 |     4 |
| C002 |   238 |     4 |
| C004 |    98 |     5 |
| C006 |   156 |     4 |
| C001 |   210 |     5 |
| C007 |   180 |     5 |
| C004 |   222 |     6 |
| C003 |   198 |     6 |
+------+-------+-------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM tb ORDER BY sales;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C003 |    77 |     5 |
| C004 |    98 |     5 |
| C001 |   100 |     4 |
| C006 |   156 |     4 |
| C007 |   180 |     5 |
| C003 |   198 |     6 |
| C001 |   210 |     5 |
| C004 |   222 |     6 |
| C002 |   238 |     4 |
| C007 |   312 |     4 |
+------+-------+-------+
10 rows in set (0.00 sec)

降順に表示

降順（Descending）に並べ替えて表示するには、DESC を付けます。

SELECT カラム名 FROM テーブル名 ORDER BY キーとなるカラム名　DESC;

以下はテーブル tb のカラム sales の値で降順に（値の大きいものから）表示する例です。また、この例では LIMIT を使って表示する件数を5件に限定しています。

mysql> SELECT * FROM tb ORDER BY sales DESC LIMIT 5;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C007 |   312 |     4 |
| C002 |   238 |     4 |
| C004 |   222 |     6 |
| C001 |   210 |     5 |
| C003 |   198 |     6 |
+------+-------+-------+
5 rows in set (0.00 sec)

グループ化 GROUP

特定のカラムに同じ値のレコードが複数ある場合、同じ値が入っているレコードをグループにすることがます。グループとして扱うことで、そのグループのレコードだけの合計や平均などグループ単位での処理が行えます。

GROUP BY (集約) 関数

グループ化を行うには、GROUP BY を使用します。以下が書式です。

SELECT カラム名 FROM テーブル名 GROUP BY グループ化するカラム名;

グループごとに表示するだけでは、あまり意味がありませんが、以下はテーブル tb のカラム id でグループ化する例です。

mysql> SELECT * FROM tb;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C003 |    77 |     5 |
| C007 |   312 |     4 |
| C002 |   238 |     4 |
| C004 |    98 |     5 |
| C006 |   156 |     4 |
| C001 |   210 |     5 |
| C007 |   180 |     5 |
| C004 |   222 |     6 |
| C003 |   198 |     6 |
+------+-------+-------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM tb GROUP BY id;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C002 |   238 |     4 |
| C003 |    77 |     5 |
| C004 |    98 |     5 |
| C006 |   156 |     4 |
| C007 |   312 |     4 |
+------+-------+-------+
6 rows in set (0.00 sec)

カラム id でグループ化したので、id がそれぞれ１つずつ表示されています。その他のカラムの値は、たまたまその値が表示されているだけで特に意味はありません（結果から見ると最初の値が表示されています）。このため単にグループ化するだけでは、グループ化したカラムの値が何種類あるかがわかるぐらいです。

個数を数える COUNT()

個数を調べるには COUNT() 関数を使用します。COUNT(col) でカラム col の NULL を除いた値の個数を調べることができます。

また、全てのカラムを表す * を使って COUNT(*) とすると、NULL も含めてレコードの数を数えます。

mysql> SELECT * FROM tb;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C003 |    77 |     5 |
| C007 |   312 |     4 |
| C002 |   238 |     4 |
| C004 |    98 |     5 |
| C006 |   156 |     4 |
| C001 |   210 |     5 |
| C007 |   180 |     5 |
| C004 |   222 |     6 |
| C003 |   198 |     6 |
| C007 |  NULL |  NULL |
| C002 |  NULL |  NULL |
+------+-------+-------+
12 rows in set (0.00 sec)

mysql> SELECT id, COUNT(id) FROM tb GROUP BY id;
+------+-----------+
| id   | COUNT(id) |
+------+-----------+
| C001 |         2 |
| C002 |         2 |
| C003 |         2 |
| C004 |         2 |
| C006 |         1 |
| C007 |         3 |
+------+-----------+
6 rows in set (0.00 sec)

mysql> SELECT id, COUNT(*) FROM tb GROUP BY id;
+------+----------+
| id   | COUNT(*) |
+------+----------+
| C001 |        2 |
| C002 |        2 |
| C003 |        2 |
| C004 |        2 |
| C006 |        1 |
| C007 |        3 |
+------+----------+
6 rows in set (0.00 sec)

mysql> SELECT id, COUNT(*) AS 件数 FROM tb GROUP BY id;
//件数というエイリアスを設定
+------+------+
| id   | 件数 |
+------+------+
| C001 |    2 |
| C002 |    2 |
| C003 |    2 |
| C004 |    2 |
| C006 |    1 |
| C007 |    3 |
+------+------+
6 rows in set (0.00 sec)

上記の例では、id でグループ化していますが、id の値に NULL がないので、COUNT(*) としても結果は同じです。

以下は、month でグループ化する例ですが、COUNT(month) とするのと COUNT(*) とするのでは、NULL のカウントが異なります。

mysql> SELECT * FROM tb;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C003 |    77 |     5 |
| C007 |   312 |     4 |
| C002 |   238 |     4 |
| C004 |    98 |     5 |
| C006 |   156 |     4 |
| C001 |   210 |     5 |
| C007 |   180 |     5 |
| C004 |   222 |     6 |
| C003 |   198 |     6 |
| C007 |  NULL |  NULL |
| C002 |  NULL |  NULL |
+------+-------+-------+
12 rows in set (0.00 sec)

mysql> SELECT month, COUNT(month) FROM tb GROUP BY month;
+-------+--------------+
| month | COUNT(month) |
+-------+--------------+
|  NULL |            0 |
|     4 |            4 |
|     5 |            4 |
|     6 |            2 |
+-------+--------------+
4 rows in set (0.00 sec)

mysql> SELECT month, COUNT(*) FROM tb GROUP BY month;
+-------+----------+
| month | COUNT(*) |
+-------+----------+
|  NULL |        2 |
|     4 |        4 |
|     5 |        4 |
|     6 |        2 |
+-------+----------+
4 rows in set (0.00 sec)

合計を算出 SUM()

合計を算出するには、SUM() を使用します。SUM(col) でカラム col の合計を算出することができます。

以下はテーブル tb でカラム id ごとの sales の合計を表示する例です。

mysql> SELECT * FROM tb;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C003 |    77 |     5 |
| C007 |   312 |     4 |
| C002 |   238 |     4 |
| C004 |    98 |     5 |
| C006 |   156 |     4 |
| C001 |   210 |     5 |
| C007 |   180 |     5 |
| C004 |   222 |     6 |
| C003 |   198 |     6 |
| C007 |  NULL |  NULL |
| C002 |  NULL |  NULL |
+------+-------+-------+
12 rows in set (0.00 sec)

mysql> 
SELECT
    id, SUM(sales) AS 合計
FROM tb
    GROUP BY id;
+------+------+
| id   | 合計 |
+------+------+
| C001 |  310 |
| C002 |  238 |
| C003 |  275 |
| C004 |  320 |
| C006 |  156 |
| C007 |  492 |
+------+------+
6 rows in set (0.00 sec)

以下はテーブル tb でカラム month ごとの sales の合計を表示する例です。

mysql> SELECT * FROM tb;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C003 |    77 |     5 |
| C007 |   312 |     4 |
| C002 |   238 |     4 |
| C004 |    98 |     5 |
| C006 |   156 |     4 |
| C001 |   210 |     5 |
| C007 |   180 |     5 |
| C004 |   222 |     6 |
| C003 |   198 |     6 |
| C007 |  NULL |  NULL |
| C002 |  NULL |  NULL |
+------+-------+-------+
12 rows in set (0.00 sec)

mysql> SELECT month, SUM(sales) AS 合計 FROM tb GROUP BY month;
+-------+------+
| month | 合計 |
+-------+------+
|  NULL | NULL |
|     4 |  806 |
|     5 |  565 |
|     6 |  420 |
+-------+------+
4 rows in set (0.00 sec)

平均を算出 AVG()

合計を算出するには、AVG() を使用します。AVG(col) でカラム col の平均を算出することができます。

以下はテーブル tb でカラム id ごとの sales の平均を表示する例です。NULL の値は対象外になります。

mysql> SELECT * FROM tb;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C003 |    77 |     5 |
| C007 |   312 |     4 |
| C002 |   238 |     4 |
| C004 |    98 |     5 |
| C006 |   156 |     4 |
| C001 |   210 |     5 |
| C007 |   180 |     5 |
| C004 |   222 |     6 |
| C003 |   198 |     6 |
| C007 |  NULL |  NULL |
| C002 |  NULL |  NULL |
+------+-------+-------+
12 rows in set (0.00 sec)

mysql> 
SELECT
    id, AVG(sales) AS 平均
FROM tb
    GROUP BY id;
+------+----------+
| id   | 平均     |
+------+----------+
| C001 | 155.0000 |
| C002 | 238.0000 |
| C003 | 137.5000 |
| C004 | 160.0000 |
| C006 | 156.0000 |
| C007 | 246.0000 |
+------+----------+
6 rows in set (0.00 sec)

以下はテーブル tb でカラム month ごとの sales の平均を表示する例です。例えば4月の場合、４つのレコードがあるのでその合計を４で割った値になります。

mysql> SELECT * FROM tb;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C003 |    77 |     5 |
| C007 |   312 |     4 |
| C002 |   238 |     4 |
| C004 |    98 |     5 |
| C006 |   156 |     4 |
| C001 |   210 |     5 |
| C007 |   180 |     5 |
| C004 |   222 |     6 |
| C003 |   198 |     6 |
| C007 |  NULL |  NULL |
| C002 |  NULL |  NULL |
+------+-------+-------+
12 rows in set (0.00 sec)

mysql> SELECT month, AVG(sales) AS 平均 FROM tb_copy GROUP BY month;
+-------+----------+
| month | 平均     |
+-------+----------+
|  NULL |     NULL |
|     4 | 201.5000 |
|     5 | 141.2500 |
|     6 | 210.0000 |
+-------+----------+
4 rows in set (0.00 sec)

条件付グループ表示 HAVING

「GROUP BY」によるグループ化にさらに抽出の条件を設定するには「HAVING」を使用します。グループ化して条件を付けて抽出するには以下のようにします。

SELECT 
    集計するカラム名 
FROM テーブル名 
    GROUP BY グループ化するカラム名 
HAVING 条件;

HAVING で設定する条件は、グループ化した値に対するものです。

以下はテーブル tb でカラム id ごとの sales の合計で値が300以上のカラムを表示する例です。

mysql> SELECT * FROM tb;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C003 |    77 |     5 |
| C007 |   312 |     4 |
| C002 |   238 |     4 |
| C004 |    98 |     5 |
| C006 |   156 |     4 |
| C001 |   210 |     5 |
| C007 |   180 |     5 |
| C004 |   222 |     6 |
| C003 |   198 |     6 |
+------+-------+-------+
10 rows in set (0.00 sec)

mysql> SELECT id, SUM(sales) FROM tb GROUP BY id HAVING SUM(sales) >= 300;
+------+------------+
| id   | SUM(sales) |
+------+------------+
| C001 |        310 |
| C004 |        320 |
| C007 |        492 |
+------+------------+
3 rows in set (0.00 sec)

//条件（HAVING）を設定しない場合
mysql> SELECT id, SUM(sales) FROM tb GROUP BY id;
+------+------------+
| id   | SUM(sales) |
+------+------------+
| C001 |        310 |
| C002 |        238 |
| C003 |        275 |
| C004 |        320 |
| C006 |        156 |
| C007 |        492 |
+------+------------+
6 rows in set (0.00 sec)

この例の場合、sales の合計を id ごとに処理（グループ化）して、HAVING で値が300以上のものを抽出しました。

つまり、処理の順番はグループ化してから抽出したことになります。

抽出してからグループ化

前項では、「グループ化してから抽出」しましたが、以下は「抽出してからグループ化」します。

グループ化前の条件を使った抽出は WHERE を使います。そしてその後 GROUP BY でグループ化します。（HAVING で設定する条件は、グループ化した値に対するものです。）

以下は、テーブル tb でカラム sales の値が100以上のレコードを抽出し、そのレコードを対象に id ごとの平均を表示する例です。

mysql> SELECT * FROM tb;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C003 |    77 |     5 |
| C007 |   312 |     4 |
| C002 |   238 |     4 |
| C004 |    98 |     5 |
| C006 |   156 |     4 |
| C001 |   210 |     5 |
| C007 |   180 |     5 |
| C004 |   222 |     6 |
| C003 |   198 |     6 |
+------+-------+-------+
10 rows in set (0.00 sec)

mysql> SELECT id, AVG(sales) FROM tb WHERE sales >= 100 GROUP BY id;
+------+------------+
| id   | AVG(sales) |
+------+------------+
| C001 |   155.0000 |
| C002 |   238.0000 |
| C003 |   198.0000 |
| C004 |   222.0000 |
| C006 |   156.0000 |
| C007 |   246.0000 |
+------+------------+
6 rows in set (0.00 sec)

//「sales の値が100以上」という条件を付けない場合
mysql> SELECT id, AVG(sales) FROM tb GROUP BY id;
+------+------------+
| id   | AVG(sales) |
+------+------------+
| C001 |   155.0000 |
| C002 |   238.0000 |
| C003 |   137.5000 |
| C004 |   160.0000 |
| C006 |   156.0000 |
| C007 |   246.0000 |
+------+------------+
6 rows in set (0.00 sec)

グループ化してから並べ替える

グループ化した結果を並べ替えるには、GROUP BY と ORDER BY を使用します。順番は「GROUP BY」を最初に行い、次に「ORDER BY」を行います。

以下はテーブル tb で、id ごとの sales の平均（AVG(sales)）を値の大きいものから順番に表示する例です。

mysql> SELECT * FROM tb;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C003 |    77 |     5 |
| C007 |   312 |     4 |
| C002 |   238 |     4 |
| C004 |    98 |     5 |
| C006 |   156 |     4 |
| C001 |   210 |     5 |
| C007 |   180 |     5 |
| C004 |   222 |     6 |
| C003 |   198 |     6 |
+------+-------+-------+
10 rows in set (0.00 sec)

mysql> SELECT id, AVG(sales) FROM tb GROUP BY id ORDER BY AVG(sales) DESC;
+------+------------+
| id   | AVG(sales) |
+------+------------+
| C007 |   246.0000 |
| C002 |   238.0000 |
| C004 |   160.0000 |
| C006 |   156.0000 |
| C001 |   155.0000 |
| C003 |   137.5000 |
+------+------------+
6 rows in set (0.00 sec)

グループ化の手順

グループ化で条件を設定する手順には以下のような場合があります。

抽出してからグループ化する
「WHERE」で条件を設定して抽出し、「GROUP BY」でグループ化
グループ化してから抽出する
「GROUP BY」でグループ化し、「HAVING」で条件を設定して抽出

条件の設定、グループ化、並べ替えが組み合わさってくると、その順序が大切になります。順序が違うとエラーになってしまいます。

以下は、「WHERE」「GROUP BY」「HAVING」「ORDER BY」「LIMIT」を使う場合の順番です。詳細は「SELECT 構文」を参考にしてください。

「WHERE」
「GROUP BY」
「HAVING」
「ORDER BY」
「LIMIT」

以下は、sales の値が100以上のものを対象にして、id ごとの sales の平均（AVG(sales)）を値の大きいものから順番に表示する例です。

mysql> SELECT * FROM tb;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C003 |    77 |     5 |
| C007 |   312 |     4 |
| C002 |   238 |     4 |
| C004 |    98 |     5 |
| C006 |   156 |     4 |
| C001 |   210 |     5 |
| C007 |   180 |     5 |
| C004 |   222 |     6 |
| C003 |   198 |     6 |
+------+-------+-------+
10 rows in set (0.00 sec)

mysql> 
SELECT 
    id, AVG(sales) 
FROM tb 
    WHERE sales >= 100 
GROUP BY id 
    ORDER BY AVG(sales) DESC;
+------+------------+
| id   | AVG(sales) |
+------+------------+
| C007 |   246.0000 |
| C002 |   238.0000 |
| C004 |   222.0000 |
| C003 |   198.0000 |
| C006 |   156.0000 |
| C001 |   155.0000 |
+------+------------+
6 rows in set (0.00 sec)

データの編集

カラムのデータの修正、レコードの削除、コピー等についてです。

カラムの全てのデータを修正
条件に一致したカラムのデータを修正
条件に一致したレコードをコピー
条件に一致したレコードを挿入
条件に一致したレコードを削除

データの修正

データを修正するには UPDATE コマンドを使用します。

カラムの全てのデータを修正

カラムの全てのデータを修正するには、以下のようにします。

この場合、カラムの全データが一瞬にして置き換わるので注意が必要です。

UPDATE テーブル名 SET カラム名 = 設定する値;

通常 UPDATE コマンドは WHERE 等により条件を設定して、修正する対象を選択して実行します。

ここでは、テーブル tb に VARCHAR(100) 型の remark というカラムを追加して、そのカラムに UPDATE コマンドで全てのレコードに「none」という文字列を入力します。

mysql> ALTER TABLE tb ADD remark VARCHAR(100);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC tb;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id     | varchar(10)  | YES  |     | NULL    |       |
| sales  | int(11)      | YES  |     | NULL    |       |
| month  | int(11)      | YES  |     | NULL    |       |
| remark | varchar(100) | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> SELECT * FROM tb;
+------+-------+-------+--------+
| id   | sales | month | remark |
+------+-------+-------+--------+
| C001 |   100 |     4 | NULL   |
| C003 |    77 |     5 | NULL   |
| C007 |   312 |     4 | NULL   |
| C002 |   238 |     4 | NULL   |
| C004 |    98 |     5 | NULL   |
| C006 |   156 |     4 | NULL   |
| C001 |   210 |     5 | NULL   |
| C007 |   180 |     5 | NULL   |
| C004 |   222 |     6 | NULL   |
| C003 |   198 |     6 | NULL   |
+------+-------+-------+--------+
10 rows in set (0.00 sec)

mysql> UPDATE tb SET remark = "none";  //カラム remark のデータを全て修正
Query OK, 10 rows affected (0.00 sec)
Rows matched: 10  Changed: 10  Warnings: 0

mysql> SELECT * FROM tb;
+------+-------+-------+--------+
| id   | sales | month | remark |
+------+-------+-------+--------+
| C001 |   100 |     4 | none   |
| C003 |    77 |     5 | none   |
| C007 |   312 |     4 | none   |
| C002 |   238 |     4 | none   |
| C004 |    98 |     5 | none   |
| C006 |   156 |     4 | none   |
| C001 |   210 |     5 | none   |
| C007 |   180 |     5 | none   |
| C004 |   222 |     6 | none   |
| C003 |   198 |     6 | none   |
+------+-------+-------+--------+
10 rows in set (0.00 sec)

条件に一致したカラムのデータを修正

条件に一致したカラムのデータを修正するには、WHERE を使って条件を設定し、その条件に一致するデータのみを修正するようにします。以下が書式になります。

UPDATE テーブル名 SET カラム名 = 設定する値 WHERE 条件;

ORDER BY や LIMIT なども条件として設定することが可能です。

以下は、カラム salse の値が300以上のレコードのカラム remark を Excellent に変更する例です。

mysql> SELECT * FROM tb;
+------+-------+-------+--------+
| id   | sales | month | remark |
+------+-------+-------+--------+
| C001 |   100 |     4 | none   |
| C003 |    77 |     5 | none   |
| C007 |   312 |     4 | none   |
| C002 |   238 |     4 | none   |
| C004 |    98 |     5 | none   |
| C006 |   156 |     4 | none   |
| C001 |   210 |     5 | none   |
| C007 |   180 |     5 | none   |
| C004 |   222 |     6 | none   |
| C003 |   198 |     6 | none   |
+------+-------+-------+--------+
10 rows in set (0.00 sec)

mysql> UPDATE tb SET remark = "Excellent" WHERE sales >=300;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM tb;
+------+-------+-------+-----------+
| id   | sales | month | remark    |
+------+-------+-------+-----------+
| C001 |   100 |     4 | none      |
| C003 |    77 |     5 | none      |
| C007 |   312 |     4 | Excellent |
| C002 |   238 |     4 | none      |
| C004 |    98 |     5 | none      |
| C006 |   156 |     4 | none      |
| C001 |   210 |     5 | none      |
| C007 |   180 |     5 | none      |
| C004 |   222 |     6 | none      |
| C003 |   198 |     6 | none      |
+------+-------+-------+-----------+
10 rows in set (0.00 sec)

以下は sales の値を比較して、値の少ないレコード3件のカラム remark の値を Fair に変更する例です。

mysql> SELECT * FROM tb;
+------+-------+-------+-----------+
| id   | sales | month | remark    |
+------+-------+-------+-----------+
| C001 |   100 |     4 | none      |
| C003 |    77 |     5 | none      |
| C007 |   312 |     4 | Excellent |
| C002 |   238 |     4 | none      |
| C004 |    98 |     5 | none      |
| C006 |   156 |     4 | none      |
| C001 |   210 |     5 | none      |
| C007 |   180 |     5 | none      |
| C004 |   222 |     6 | none      |
| C003 |   198 |     6 | none      |
+------+-------+-------+-----------+
10 rows in set (0.00 sec)

mysql> UPDATE tb SET remark = "Fair" ORDER BY sales LIMIT 3;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * FROM tb ORDER BY sales;
+------+-------+-------+-----------+
| id   | sales | month | remark    |
+------+-------+-------+-----------+
| C003 |    77 |     5 | Fair      |
| C004 |    98 |     5 | Fair      |
| C001 |   100 |     4 | Fair      |
| C006 |   156 |     4 | none      |
| C007 |   180 |     5 | none      |
| C003 |   198 |     6 | none      |
| C001 |   210 |     5 | none      |
| C004 |   222 |     6 | none      |
| C002 |   238 |     4 | none      |
| C007 |   312 |     4 | Excellent |
+------+-------+-------+-----------+
10 rows in set (0.00 sec)

条件に一致したレコードをコピー

条件に一致したレコードをコピーするには、CREATE TABLE でテーブルを作成し、WHERE などで条件を設定して SELECT したレコードを挿入します。また、CREATE TABLE の代わりに INSERT INTO を使えば、既存のテーブルに条件に一致したレコードを挿入することができます。

CREATE TABLE 新規テーブル名 SELECT * FROM 元となるテーブル名 WHERE 条件;

以下はテーブル tb のカラム ID の値が C007 のレコードをコピーして、新しいテーブル tbc007 を作成する例です。

mysql> SELECT * FROM tb;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C003 |    77 |     5 |
| C007 |   312 |     4 |
| C002 |   238 |     4 |
| C004 |    98 |     5 |
| C006 |   156 |     4 |
| C001 |   210 |     5 |
| C007 |   180 |     5 |
| C004 |   222 |     6 |
| C003 |   198 |     6 |
+------+-------+-------+
10 rows in set (0.00 sec)

mysql> CREATE TABLE tbc007 SELECT * FROM tb WHERE id LIKE "C007";
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tbc007;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C007 |   312 |     4 |
| C007 |   180 |     5 |
+------+-------+-------+
2 rows in set (0.00 sec)

条件は、WHERE だけでなく、ORDER BY や LIMIT などを使って設定することができます。

以下はテーブル tb のカラム sales の値の大きいもの3件のレコードをコピーして、新しいテーブル tbtop を作成する例です。

mysql> SELECT * FROM tb;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C003 |    77 |     5 |
| C007 |   312 |     4 |
| C002 |   238 |     4 |
| C004 |    98 |     5 |
| C006 |   156 |     4 |
| C001 |   210 |     5 |
| C007 |   180 |     5 |
| C004 |   222 |     6 |
| C003 |   198 |     6 |
+------+-------+-------+
10 rows in set (0.00 sec)

mysql> CREATE TABLE tbtop SELECT * FROM tb ORDER BY sales DESC LIMIT 3;
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tbtop;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C007 |   312 |     4 |
| C002 |   238 |     4 |
| C004 |   222 |     6 |
+------+-------+-------+
3 rows in set (0.00 sec)

条件に一致したレコードを挿入

INSERT INTO を使えば、既存のテーブルに条件に一致したレコードを挿入することができます。

以下は、前述のテーブル tbtop に更にテーブル tb のカラム sales の値の大きいもの3件のレコードをコピーして挿入する例です。

すでにカラム sales の値の大きいもの3件はコピーされているので4番目に大きいもの（OFFSET 3）から3件（LIMIT 3）を追加挿入します。

mysql> SELECT * FROM tbtop;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C007 |   312 |     4 |
| C002 |   238 |     4 |
| C004 |   222 |     6 |
+------+-------+-------+
3 rows in set (0.00 sec)

mysql> INSERT INTO tbtop SELECT * FROM tb ORDER BY sales DESC LIMIT 3 OFFSET 3;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tbtop;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C007 |   312 |     4 |
| C002 |   238 |     4 |
| C004 |   222 |     6 |
| C001 |   210 |     5 |
| C003 |   198 |     6 |
| C007 |   180 |     5 |
+------+-------+-------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM tb ORDER BY sales DESC;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C007 |   312 |     4 |
| C002 |   238 |     4 |
| C004 |   222 |     6 |
| C001 |   210 |     5 |
| C003 |   198 |     6 |
| C007 |   180 |     5 |
| C006 |   156 |     4 |
| C001 |   100 |     4 |
| C004 |    98 |     5 |
| C003 |    77 |     5 |
+------+-------+-------+
10 rows in set (0.00 sec)

条件に一致したレコードを削除

条件に一致したレコードを削除するには、DELETE FROM でレコードを削除する際に、WHERE や ORDER などで条件を設定します。

DELETE FROM テーブル名 WHERE 条件;

以下はテーブル tb のカラム id の値が "C007" のレコードを削除する例です。

mysql> SELECT * FROM tb;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C003 |    77 |     5 |
| C007 |   312 |     4 |
| C002 |   238 |     4 |
| C004 |    98 |     5 |
| C006 |   156 |     4 |
| C001 |   210 |     5 |
| C007 |   180 |     5 |
| C004 |   222 |     6 |
| C003 |   198 |     6 |
+------+-------+-------+
10 rows in set (0.00 sec)

mysql> DELETE FROM tb WHERE id = "C007";
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM tb;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C003 |    77 |     5 |
| C002 |   238 |     4 |
| C004 |    98 |     5 |
| C006 |   156 |     4 |
| C001 |   210 |     5 |
| C004 |   222 |     6 |
| C003 |   198 |     6 |
+------+-------+-------+
8 rows in set (0.00 sec)

順番に並べてから削除

ORDER BY で順番に並べて、LIMIT で削除するレコードの数を指定して削除します。

以下はテーブル tb のカラム sales の値が少ない3件のレコードを削除する例です。

mysql> SELECT * FROM tb;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C003 |    77 |     5 |
| C002 |   238 |     4 |
| C004 |    98 |     5 |
| C006 |   156 |     4 |
| C001 |   210 |     5 |
| C004 |   222 |     6 |
| C003 |   198 |     6 |
+------+-------+-------+
8 rows in set (0.00 sec)

mysql> DELETE FROM tb ORDER BY sales LIMIT 3;
Query OK, 3 rows affected (0.00 sec)

mysql> SELECT * FROM tb;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C002 |   238 |     4 |
| C006 |   156 |     4 |
| C001 |   210 |     5 |
| C004 |   222 |     6 |
| C003 |   198 |     6 |
+------+-------+-------+
5 rows in set (0.00 sec)

Web Design Leaves