MySQL Basic

MySQLの主なデータベースオブジェクト

オブジェクトの種類	キーワード(オブジェクト種類名)
データベース(スキーマ)	DATABASE
テーブル	TABLE
インデックス	INDEX
ビュー	VIEW
トリガ	TRIGGER
ストアドファンクション	FUNCTION
ストアドプロシージャ	PROCEDURE
イベント	EVENT
空間情報	SPATIAL REFERENCE SYSTEM
テーブルスペース	TABLE SPACE
ログファイル・グループ	LOGFILE GROUP
サーバー	SERVER

基本構文

生成

CREATE オブジェクト種類名 オブジェクト名 オブジェクトに応じた各種情報;

破棄

DROP オブジェクト種類名 オブジェクト名;

変更

ALTER オブジェクト種類名 オブジェクト名 オブジェクトに応じた各種情報;

データーベースの作成削除変更

データベースを作成するにはCREATE DATABASE文を使用する。MySQLでは「データベース」という言葉は「スキーマ」と同じ意味で使用される。

CREATE DATABASEはCREATE SCHEMAと同じ意味。(「SCHEMA」が「DATABASE」の別名。)

CREATE DATABASE文は、通常、作成しようとしているデータベースメイト同じ名前のものが存在する場合エラーになる。IF NOT EXISTSをつけると、既存の場合にはエラーではなく、何もせずに終了するようになる。

`CREATE DATABASE`の構文

CREATE DATABASE [IF NOT EXISTS] db_name
    [DEFAULT CHARACTER SET = charset_name]
    [DEFAULT COLLATE SET = collation_name]
    [DEFAULT ENCRYPTION] = 'Y'/'N'

データベースを削除するにはDROP DATABASE文を使用する。IF EXISTSを付ければ、対象のデータベースが存在しない場合でもエラーにならない。

`DROP DATABASE`の構文

DROP DATABASE [IF EXISTS] db_name

ALTER DATABASEで既存データベースのデフォルト指定内容を変更できる。データベースの名前は変更できない。

`ALTER DATABASE`の構文

ALTER DATABASE db_name
    [DEFAULT CHARCTER SEC = charset_name]
    [DEFAULT COLLATE SET = collation_name]
    [DEFAULT ENCRYPTION] = 'Y'/'N'

テーブルの作成削除変更

テーブルを作成するにはCREATE TABLE文を使用する。テーブルはデータベース(スキーマ)の中に作成する。テーブル名は設定により大文字小文字が区別されることがある。使用するスキーマ(デフォルトスキーマ)を指定するには、「USE db_name」とする。

`CREATE TABLE`の構文

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (
    列1の情報,
    列2の情報,
    .
    .
    インデックスに関する情報
)
[DEAULT CHARACTER SET = charset_name]
[DEAULT COLLATE = collation_name]
[ENGINE = engine_name]
[COMMENT = comment_string]

DEFAULT CHARACTER SET テーブルの文字コード
DEFAULT COLLATE コレーション
ENGINE 使用したいストレージエンジン

指定しない場合は、データベースのデフォルト、それもない場合は動作中のMySQLサーバーのデフォルトでの動作になる。

「列 n の情報」の部分は、列に関する情報を記述。,で区切られた1つかたまりが1つの列を表し、それを必要な列数だけ羅列する。1つの列の情報は次のような項目を指定できる。

列の情報　部分の構文

col_name data_type
[NOT NULL]
[DEFAULT default_value]
[AUTO_INCREMENT]
[UNIQUE]
[PRIMARY KEY]
[COMMENT comment_string]

NOT_NULL

カラムにNULLを許さない。基本的には特別にNULLを許可したいカラム以外の全カラムにNOT NULLを指定すべき。

DEFAULT

INSERT文でカラムに入れる値が記述されなかったときのデフォルトの値を指定する。

AUTO_INCREMENT

テーブルに行が追加されるたびに自動的にインクリメントされた値がセットされる列であることを指示するオプション。1つのテーブルに1列丈指定可能。AUTO_INCREMENTを指定する列の型は整数型である必要がある。

UNIQUE

この列へ入る値に重複を許さない。

PRIMARY KEY

この列が主キーであることを指定。複合キーの場合は此の方法ではなく、列を網羅する最後の「インデックスに関する情報」の部分に、次のように記述。

PRIMARY KEY (col1, col2)

`CREATE TABLE`例

CREATE TABLE SAMPLE_INFO (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    birth DATETIME,
    comment VARCHAR(200)
)   ENGINE=InnoDB;

テーブル定義の確認

descまたは、describeで確認できる。

mysql> desc SAMPLE_INFO;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int          | NO   | PRI | NULL    |       |
| name    | varchar(100) | NO   |     | NULL    |       |
| birth   | datetime     | YES  |     | NULL    |       |
| comment | varchar(200) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

`DROP TABLE`構文

既存のテーブルを削除するDROP TABLE文。

DROP TABLE [IF EXISTS] tbl_name

既存のテーブル定義の変更

ALTER TABLE文を使用すると、既存テーブルの定義を変更できる。先程作成したテーブルを変更してみる。

ALTER TABLE SAMPLE_INFO
    ADD COLUMN yomi VARCHAR(100) NOT NULL AFTER name;

yomiカラムが追加された。既存のテーブルの任意の位置にカラムを追加できるのは、MySQLの特徴のひとつ。

mysql> desc SAMPLE_INFO;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int          | NO   | PRI | NULL    |       |
| name    | varchar(100) | NO   |     | NULL    |       |
| yomi    | varchar(100) | NO   |     | NULL    |       |
| birth   | datetime     | YES  |     | NULL    |       |
| comment | varchar(200) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

インデックスの作成

テーブルのカラムに対してインデックスを作成すると、そのカラムに対する検索を高速化できる。インデックスはテーブル作成時に指定することも可能だが、既存テーブルに対して新たなインデックスを作成することも可能。インデックスを作るにはCREATE INDEX文を使用する。

最低限必要なもの

インデックスを張るテーブルとそのカラム名(複数の場合もある)
張ったインデックスにつける名前

`CREATE INDEX`の構文

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX ind_name
       [USING BTREE/HASH]
    ON tbl_name (col1_name, col2_name....)
       [COMMENT comment_string]

最小限の指定でインデックスを作成する例

CREATE INDEX idx01 ON mytable (name1, name2);

インデックスの削除

インデックスを削除するには、DROP INDEXを使用する。DROP INDEXには[IF EXISTS]のオプションの指定はない。

`DROP INDEX`の構文

DROP INDEX ind_name on tbl_name

インデックスの変更

インデックスの変更を行いたい場合は、既存のインデックスをDROPしてから新たにCREATEする。インデックスの扱いには、ALTER文はない。

ストアドルーチンの作成

ストアドプロシージャとストアドファンクションは総称してストアドルーチンと呼ばれる。どちらも記述された一連の処理を行う点は同様。

ストアドプロシージャ

ストアドプロシージャを作成するには、CREATE PROCEDURE文を使用する。戻り値を返さない。

CREATE PROCEDURE procedure_name
    ([IN/OUT/INOUT] param1_name data_type, ...)
    [COMMENT comment_str]
    [DETERAMISTIC]
  routine_body

ストアドファンクション

ストアドファンクションを作成するには、CREATE FUNCTION文を使用する。実行結果として1つの値を戻り値として呼び出し元に返す。

CREATE FUNCTION function_name
    (param1_name data_type, ...)
    RETURNS data_type
    [COMMENT comment_str]
    [DETERMSTIC]
  routine_body

ストアドルーチンは、複数のSQL処理を実施する。CREATE文で定義を記述する際に、途中に命令の終了を示す「;」が入る。この記号があるとmysqlコマンドラインクライアントは内容をMySQLサーバーに創出し、実行を試みる。そのため、ストアドルーチン内に出てくる「;」命令の終了とみなされないようにするために、ストアドルーチン作成前に一時的に終端文字の変更を行う。

`delimiter`文

CREATE FUNCTIONの前に一時的に終端文字を「//」に変更し、ファンクショ生成終了を、その「//」で伝えたあと、再びdelimiter文で元の終端文字に戻している。

`ストアドファンクション`の例

delimiter //
CREATE FUNCTION dfb2deg(d FLOAT, f FLOAT, b FLOAT)
    RETURNS FLOAT DETERMINISTIC
BEGIN
    RETURN d + f/60 + b/60/60;
END
//
delimiter

SELECT

`SELECT`文

テーブルに登録されているデータを取り出すにはSELECT文を使用する。FROM句には別のテーブルとの結合を表すJOINを記述することも可能。

SELECT カラム名を羅列
    FROM テーブル名1
         .
         .
    WHERE 検索条件
    GROUP BY カラム名を羅列
    HAVING カラム名を羅列
    ORDER BY カラム名を羅列 [ASC/DESC]
    LIMIT 取得件数

`SELECT`文実行するための準備

`mytable`を作成

CREATE TABLE mytable (
    id INT,
    name VARCHAR(255),
    val VARCHAR(255),
    col1 INT,
    col2 INT,
    col3 INT
);

`mytable`にデータを挿入

INSERT INTO mytable (id, name, val, col1, col2, col3)
VALUES
    (1, 'name A', 'val A', 111, 11, 1),
    (2, 'name B', 'val B', 222, 22, 2),
    (3, 'name C', 'val C', 333, 33, 3),
    (4, 'name D', 'val D', 444, 44, 4),
    (5, 'name E', 'val E', 555, 55, 5);

`othertable`を作成

CREATE TABLE othertable (
    id INT,
    name VARCHAR(255)
);

`othertable`にデータを挿入

INSERT INTO othertable (id, name)
VALUES
    (1, 'Item A'),
    (2, 'Item B'),
    (3, 'Item C'),
    (4, 'Item D');

`SELECT`例

SELECT m.col1, MAX(m.col2), MAX(o.id), MAX(o.name)
    FROM mytable m
         RIGHT OUTER JOIN othertable o ON (m.col3 = o.id)
         WHERE col1 IN (111, 222, 333)
         GROUP BY m.col1
         ORDER BY m.col1
         LIMIT 50;

実行結果

+------+-------------+-----------+-------------+
| col1 | MAX(m.col2) | MAX(o.id) | MAX(o.name) |
+------+-------------+-----------+-------------+
|  111 |          11 |         1 | Item A      |
|  222 |          22 |         2 | Item B      |
|  333 |          33 |         3 | Item C      |
+------+-------------+-----------+-------------+
3 rows in set (0.00 sec)

検索結果をファイルに出力

SELECT 〜 INTO OUTFILEの仕様にはFILE権限が必要。

SELECT id, name, val INTO OUTFILE 'myoutfile.txt' FROM mytable;

INSERT

`INSERT`文

テーブルに対して新たな1件のデータを追加するには、INSERT文を使用する。

`INSERT`構文

INSERT INTO テーブル名 ( カラム名を羅列 )
     VALUES ( 各カラムに対応する値を羅列 )

`INSERT`文を実行するための準備

`mytable`を作成

CREATE TABLE mytable (
    id INT,
    name VARCHAR(255)
);

`INSERT`例

INSERT INTO mytable (id, name) VALUES(13, 'yamada');

`SELECT`文で取り出す

SELECT * FROM mytable;

実行結果

mysql> SELECT * FROM mytable;
+------+--------+
| id   | name   |
+------+--------+
|   13 | yamada |
+------+--------+
1 row in set (0.01 sec)

`REPLACE`文

INRSERTとにた動作をする命令として、REPLACE文がある。REPLACE文の構文は、ほぼINSERT文。通常、INSERT文はプライマリーキーの値(またはユニークキーの値)が既存のものを追加しようとするとエラーが発生する。しかし、REPLACE文を使えば、既存の行を削除して新たに行を追加することが可能になる。プライマリーキーの値(またはユニークキーの値)が一致する行がなければ、動作はINSERT文と全く同じになる。

`REPLACE`例

REPLACE INTO mytable (id, name) VALUES(13, 'yamada');

UPDATE

`UPDATE`文

テーブルにすでに存在するデータを更新(変更)するには、UPDATE文を使用する。UPDATE文は、WHERE句で指定した検索条件にマッチした行の指定カラムの値が変更される。WHEREをつけない場合はテーブル全件の指定カラムの値が変更される。

`UPDATE`構文

UPDATE テーブル名
   SET カラム名1=値1, カラム名2=値2, ....
 WHERE 検索条件

`UPDATE`文を実行するための準備

`mytable`を作成

CREATE TABLE mytable (
    id INT,
    val VARCHAR(255),
    name VARCHAR(255),
    col1 VARCHAR(255),
    col2 VARCHAR(255),
    col3 VARCHAR(255)
);

`mytable`にデータを挿入

INSERT INTO mytable (id, name, val, col1, col2, col3)
VALUES
    (1, 'name A', 'val A', 111, 11, 1),
    (2, 'name B', 'val B', 222, 22, 2),
    (3, 'name C', 'val C', 333, 33, 3),
    (4, 'name D', 'val D', 444, 44, 4),
    (5, 'name E', 'val E', 555, 55, 5);

`UPDATE`例

UPDATE mytable
    SET name='change_tarou', val='val change', col1='change1', col2='change2', col3='change3'
   WHERE id=3;

`UPDATE`前との比較

id=3のカラムの値の更新されていることがわかる。

`UPDATE`前

mysql> SELECT * FROM mytable;
+------+-------+--------+------+------+------+
| id   | val   | name   | col1 | col2 | col3 |
+------+-------+--------+------+------+------+
|    1 | val A | name A | 111  | 11   | 1    |
|    2 | val B | name B | 222  | 22   | 2    |
|    3 | val C | name C | 333  | 33   | 3    |
|    4 | val D | name D | 444  | 44   | 4    |
|    5 | val E | name E | 555  | 55   | 5    |
+------+-------+--------+------+------+------+
5 rows in set (0.00 sec)

`UPDATE`後

+------+------------+--------------+---------+---------+---------+
| id   | val        | name         | col1    | col2    | col3    |
+------+------------+--------------+---------+---------+---------+
|    1 | val A      | name A       | 111     | 11      | 1       |
|    2 | val B      | name B       | 222     | 22      | 2       |
|    3 | val change | change_tarou | change1 | change2 | change3 |
|    4 | val D      | name D       | 444     | 44      | 4       |
|    5 | val E      | name E       | 555     | 55      | 5       |
+------+------------+--------------+---------+---------+---------+
5 rows in set (0.00 sec)

DELETE TRUNCATE

`DELETE`文

テーブルに存在するデータから行を削除するには、DELETE文を使用する。WHERE句で指定した検索にマッチする行が削除される。WHERE句を指定しなかった場合はテーブルの全件が削除される。

`DELETE`構文

DELETE FROM テーブル名
　WHERE 検索条件

`DELETE`文を実行するための準備

`mytable`を作成

CREATE TABLE mytable (
    id INT,
    val VARCHAR(255),
    name VARCHAR(255),
    col1 VARCHAR(255),
    col2 VARCHAR(255),
    col3 VARCHAR(255)
);

`mytable`にデータを挿入

INSERT INTO mytable (id, name, val, col1, col2, col3)
VALUES
    (1, 'name A', 'val A', 111, 11, 1),
    (2, 'name B', 'val B', 222, 22, 2),
    (3, 'name C', 'val C', 333, 33, 3),
    (4, 'name D', 'val D', 444, 44, 4),
    (5, 'name E', 'val E', 555, 55, 5);

`DELETE`例

DELETE FROM mytable
  WHERE col1=111;

`DELETE`前との比較

WHERE col1=111のカラムが削除されたことがわかる。 DELETE前

mysql> SELECT * FROM mytable;
+------+-------+--------+------+------+------+
| id   | val   | name   | col1 | col2 | col3 |
+------+-------+--------+------+------+------+
|    1 | val A | name A | 111  | 11   | 1    |
|    2 | val B | name B | 222  | 22   | 2    |
|    3 | val C | name C | 333  | 33   | 3    |
|    4 | val D | name D | 444  | 44   | 4    |
|    5 | val E | name E | 555  | 55   | 5    |
+------+-------+--------+------+------+------+
5 rows in set (0.00 sec)

`DELETE`後

ysql> SELECT * FROM mytable;
+------+-------+--------+------+------+------+
| id   | val   | name   | col1 | col2 | col3 |
+------+-------+--------+------+------+------+
|    2 | val B | name B | 222  | 22   | 2    |
|    3 | val C | name C | 333  | 33   | 3    |
|    4 | val D | name D | 444  | 44   | 4    |
|    5 | val E | name E | 555  | 55   | 5    |
+------+-------+--------+------+------+------+
4 rows in set (0.00 sec)

`TRUNCATE`文

TRUNCATE文はテーブルの全データを削除する命令。DELETEと似ているが、truncate(切り捨て)という言葉のとおり、余計なことはいっさいせずに無慈悲にデータを消去可能。

`TRUNCATE`構文

TRUNCATE TABLE テーブル名

TRUNCATE文はデータ定義文(DDL)に分類される。TRUNCATE文を発行すると自動コミットが内部的に走るため、消去したデータはROLLBACKできない。

SQL 文の組み合わせ

`SELECT`の結果を`INSERT`

INSERT INO テーブル名1 (テーブル1のカラム羅列)
    SELECT テーブル2のカラム羅列 FROM テーブル名2 WHERE テーブル2の抽出条件

あらゆるSELECT文の結果を、別のテーブルへと登録できる。SELECT文での取得列と、INSERT文に記述する挿入先は対応している必要がある。

`SELECT`の結果を新たなテーブルに

CREATE TABLE ~ SELECT文。SELECT文の結果をカラムを持つ新たなテーブルを作成して、データを登録可能。

CREATE TABLE テーブル名1
    AS SELECT テーブル2のカラム羅列 FROM テーブル名2 WHERE テーブル2の抽出条件

`WHERE`文で指定する条件

WHERE句に記述する条件について、

条件が複数ある場合

    条件1 AND 条件2 AND 条件3 ....
    条件1 AND (条件2 OR 条件3) ....

各条件をANDやORでつなげて指定する。括弧も使用可能。

条件の使い方

    col1 = 値 : 指定値に一致するもの
    col1 <> 値 : 指定値に一致しないもの
    col1 > 値 : 指定値より大きいもの
    col1 >= 値 : 指定値以上のもの
    col1 < 値 : 指定値より小さいもの
    col1 <= 値 : 指定値以下のもの
    col1 LIKE '%文字列%' : 指定された文字列を含むもの
    col1 LIKE '文字列%' : 指定された文字列で始まるもの
    col1 LIKE '%文字列' : 指定された文字列で終わるもの
    col1 IN (値1, 値2...) : 指定された値1, 値2... のいずれかに一致するもの

カラムや値には関数を使用することもできる。インデックスが張ったカラムの場合、関数を使用することでインデックスが適用されなくなる点に注意。

    LEFT(col1, 2) = '01'

値の部分にはサブクエリーを使用可能。使用できるサブクエリーは、INの場合は1列の値を複数返すもの、IN以外の場合は1列の値を1つだけ返すもの。

    col1 IN (SELECT col2 FROM tbl2 WHERE category='22')

LIKE式の「%」はワイルドカード(0文字以上の文字列にマッチ)。「_」は任意の1文字にマッチ。

SQL 便利な式構文

`CASE`式

SQL処理内のIF文のようなものを実現するのがCASE式。

CASE
    WHEN col1='THREE' THEN 3
    WHEN col1='TWO'   THEN 2
    WHEN col1='ONE'   THEN 1
    ELSE              0
END

判断に使用する一方の辺が固定の場合は、次のように固定辺を1箇所にまとめて記述可能。

CASE col1
    WHEN 'THREE' THEN 3
    WHEN 'TWO'   THEN 2
    WHEN 'ONE'   THEN 1
    ELSE              0
END

`WINDOW`関数

WINDOW関数は「行をひとつにまとめないGROUP BY」とも言われる。

SELECT id,
    RANK() OVER (PARTITON BY col1, col2 ORDER BY col3) AS rnk
  FROM tbl1;

tbl1テーブルから、col1とcol2カラムの組み合わせをグループとし、それぞれのグループ内でcol3の小さい順に1から番号を振った値をrnkという名前のカラムとして返す。

共通表形式(`CTE`)

CTEはSQL文内でのみ有効なテンポラリーテーブルのようなもの。

WITH cte_g1 AS (
    SELECT id, ST_Distance(pos1, pos2) dist FROM g1
)
SELECT id, round(dist, 2) do, round(dist/60,2) fun, round(60/60,2) byou
    FROM cte_g1;

cte_g1という名前をつけたCTEで2点間の距離を求めている。続くメインのSELECT文で、このCTEを利用して60で割った値と60 × 60で割った値を求めている。

`トランザクション`の制御

データーベースを安全に更新するには、トランザクションが欠かせない。トランザクションは、複数のSQL文をひとかたまりとして、その全体を確定させる機能。処理の途中でサーバーが停止した場合でも、一部のSQLだけが確定しまうことを避けられる。

`トランザクション`の開始と終了

トランザクションを開始するにはSTART TRANSACTION文を使用する(BEGINでも可能)。更新系SQLをいくつか実行して、それらの変更を確定させたいときは、COMMIT文を実行。SQL実行後に、確定せずにそれらを元に戻す場合ROLLBACK文を実行。

`COMMIT`の使用方法

mysql> START TRANSACTION;
    : いくつかの更新処理実行
    :
mysql> COMMIT;

`ROLLBACK`の使用方法

mysql> START TRANSACTION;
    : いくつかの更新処理実行
    : ここで何らかの、処理続行をしたくない事象が発生
mysql> ROLLBACK;

トランザクション実行中に新たにSTART TRANSACTIONを実行すると、実行中のトランザクションはその場でCOMMITされる。

暗黙の`トランザクション`制御

トランザクションの実行中に、電源断など何らかの理由でサーバーが停止した場合、次回のMySQL起動時に、実行中のトランザクションはすべて自動的にROLLBACKされる。

自動的な`ROLLBACK`の使用方法

mysql> START TRANSACTION;
    : いくつかの更新処理実行
    : ここでサーバー停止
    ↓
 次回起動時に自動的に ROLLBACK

`SAVEPOINT`

トランザクション制御では、SAVEPOINTも使用可能。SAVEPOINTは、トランザクション実行中の楔のようなもの。ROLLBACKで、指定した楔の部分までだけを戻せる。元々のトランザクションは継続する。

mysql> START TRANSACTION;
    : いくつかの更新処理実行
mysql> START TRANSACTION mysave1;
    : いくつかの更新処理実行
mysql> START TRANSACTION mysave2;
    : いくつかの更新処理実行
mysql> START TRANSACTION mysave3;
    : いくつかの更新処理実行
mysql> START TRANSACTION mysave4;
    : いくつかの更新処理実行
mysql> COMMIT;

オートコミットモード

MySQLは、デフォルトの動作として、ひとつの更新クエリーを実行すると即座にコミットされる。これをオートコミット(自動コミット)と言う。オートコミットモードやめるには、次のようにする。

SET AUTOCOMMIT=0;

オートコミットモードに戻すには次のようにする。

SET AUTOCOMMIT=1;

MySQL データ型

数値型

数値型は、次のような型に分類される。

真数値 : 整数型
真数値 : 固定少数点型
概数値 : 浮動小数点型

分類	型名	バイト数	Signed 型の最大値	備考
整数型	TINYINT	1	127
整数型	SMALLINT	2	32,767
整数型	MEDIUMINT	3	8,388,607
整数型	INTEGER	4	2,147,483,647	INTEGER は INT をシノニムに持つ
整数型	BIGINT	8	2 ** 63 -1
固定小数点型	DECIMAL(P, S)			P は精度、S はスケール(小数点以下の桁数)
固定小数点型	NUMBERIC(P, S)			P は精度、S はスケール(小数点以下の桁数)
浮動小数点型	FLOAT(p)	4		p は仮引数部のビット数。24 を超えた時に自動的に DOUBLE 形になる
浮動小数点型	DOUBLE	8

文字列型

文字列型には、固定長文字列と可変長文字列がある。

固定長文字列 : CHAR
可変長文字列 : VARCHAR

固定長文字列であるCHAR型はそのカラムに入れる値が何文字であっても内部的にカラムに宣言されたサイズの領域を使用する。可変長文字列であるVARCHAR型は、カラムに宣言されたサイズを上限値として値の文字数だけの領域を使用する。

MySQLでは、VARCHAR型の右側のスペースは指定されたとおりに保持される。(RDBMSにより、スペースが削除されてしまうものもある)。CHAR型でカラムに宣言したサイズの領域を使用するのは保管された状態の時であり、取り出す際にはこれらのスペースは削除される。このため、VARCHAR型と異なり、CHAR型では右側のスペースは情報として保持されない。

mysql> CREATE TABLE mychartest (id INTEGER, val VARCHAR(4), val2 CHAR(4));

mysql> INSERT INTO mychartest VALUES (1, 'a', 'a');
mysql> INSERT INTO mychartest VALUES (2, 'a ', 'a ');
mysql> INSERT INTO mychartest VALUES (3, 'a  ', 'a  ');

mysql> SELECT id, CONCAT(val, "***") varcharcol, CONCAT(val2, "***") charcol FROM mychartest;
+------+------------+---------+
| id   | varcharcol | charcol |
+------+------------+---------+
|    1 | a***       | a***    |
|    2 | a ***      | a***    |
|    3 | a  ***     | a***    |
+------+------------+---------+

日付時刻型

MySQLの日付時刻に関する型

DATETIME型 : 日付と時刻を格納可能。(1970-01-01 00:00:01~2038-01-19 03:14:07)
TIMESTAMP型 : 日付と時刻を格納可能。(1970-01-01 00:00:01~2038-01-19 03:14:07)
TIME型 : 時刻情報を格納可能。(-839:59~838:59:59)
YEAR型 : 年の情報を格納可能。

その他の型

`BLOB`型

大きなバイナリを扱うための型。BLOB型はMySQL独自の型。

TINYBLOB
BLOB
MEDIUMBLOB
LONGBLOB

`TEXT`型

65,535 バイトを超える長い文字列を扱うための型。TEXT型はMYSQL独自の型。

TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT

長い文字列をBLOB型に格納しても構わないが、その場合バイナリ情報として格納され、ソートや比較で使われる照合順序を考慮しない。TEXT型では照合順序に対応する。

`ENUM`型

列挙型。テーブル生成時にENUMで宣言した値のみをカラムに登録できる。ENUM型はMySQL独自の型。

mysql> CREATE TABLE enumsample 
    ->      (id INTEGER,
    ->       val ENUM('MySQL', 'PostgreSQL', 'Oracle', 'Db2'));

mysql> INSERT INTO enumsample VALUES (1, 'MySQL');
mysql> INSERT INTO enumsample VALUES (2, 'mysql');
mysql> INSERT INTO enumsample VALUES (3, 'Oracle');

mysql> SELECT * FROM  enumsample;
+------+--------+
| id   | val    |
+------+--------+
|    1 | MySQL  |
|    2 | MySQL  |
|    3 | Oracle |
+------+--------+

ENUM型での一致判定はコレーション(照合順序)に従う。"MySQL"という文字列の格納がENUMで許可されているとき、INSERT文に"mysql"と小文字で指定しても(デフォルトの挙動では)同一視されるため、INSERTが成功する。登録された値はENUMで指定されたも、"mysql"ではなく"MySQL"になる。ENUMで指定された値以外のものを挿入しようととするとエラーになる。

mysql> INSERT INTO enumsample VALUES (4, 'MariaDB');
ERROR 1265 (01000): Data truncated for column 'val' at row 1

`SET`型

テーブル生成時に宣言した値集合から複数のものを保持できるのがSET型。SET型は、同じ値を複数持つことはできない。また、INSERT時にどのような順序を指定しても、取り出す時にはテーブル宣言時の順序で取り出される。照合順序の挙動はENUM型と同様。SET型はMySQL独自の型

mysql> CREATE TABLE setsample (
    ->        id INTEGER,
    ->        val SET('Sno', 'S', 'A', 'T', 'B', 'GB'));

mysql> INSERT INTO setsample VALUES (1,'s,a,b');
mysql> INSERT INTO setsample VALUES (2,'a,a,t');
mysql> INSERT INTO setsample VALUES (3,'t,a');

mysql> SELECT * FROM setsample;
+------+-------+
| id   | val   |
+------+-------+
|    1 | S,A,B |
|    2 | A,T   |
|    3 | A,T   |
+------+-------+

`JSON`型

JSONデータを格納。JSONの文字列として格納されるのではなく、解析され、内部的にバイナリとして格納される。

`SPATIAL`型

空間情報型。軽度や緯度など格納。

関数

現在日時を返す関数

MySQLではFROM句を省略できる。

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2022-02-22 02:22:22 |
+---------------------+

ほかのRDBMSの中には、FROM句が必須であるために、どのテーブルからもデータを取得しない場合はFROM dualと記述するものがある。MySQLでもそれに合わせるために、FROM dualを付けても動作するようになっている。しかし、MySQLではFROM句が必須ではないので、わざわざ打つ必要はない。

mysql> SELECT NOW() FROM dual;
+---------------------+
| NOW()               |
+---------------------+
| 2022-02-22 02:22:22 |
+---------------------+

日付を加算する

DATE_ADD(date, INTERVAL expr unit)

1ヶ月後の日付を取得する

mysql> SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH);
+-----------------------------------+
| DATE_ADD(NOW(), INTERVAL 1 MONTH) |
+-----------------------------------+
| 2022-03-22 02:22:22               |
+-----------------------------------+

変換する挙動を比較する方法

元の値があり、それを変換する関数の挙動を見たいときには並べて指定する。

mysql> SELECT NOW() AS a, DATE_ADD(NOW(), INTERVAL 1 MONTH) AS b;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2024-03-22 07:30:55 | 2024-04-22 07:30:55 |
+---------------------+---------------------+

固有

MySQLのコメントは、ほかのRDBMSと同様「--」を使用可能(/*...*/も使用可能)。ただし、ほかのRDBMSと異なり、「--」の後ろに空白を1つ挟む必要がある。

mysql> SELECT 'A'; --display 'A'
+---+
| A |
+---+
| A |
+---+

MySQLでは負の数値を表す際に、必ずしも括弧が必要なわけではない。次のようなクエリーとコメントの区別がつかないため、コメントのときは「--」の後ろに空白を入れる必要がある。

mysql> SELECT 4--3;
+------+
| 4--3 |
+------+
|    7 |
+------+

※ 4-(-3)と同じ意味

文字列のクォート

MySQLでは文字列を囲むクォート文字として、ダブルクォートもシングルクォートも同じように使用可能。

mysql> SELECT "stringA" a, 'stringB' b;
+---------+---------+
| a       | b       |
+---------+---------+
| stringA | stringB |
+---------+---------+

`IF NOT EXISTS`

テーブル作成時に「もし存在していなかったら作成する」というオプションを指定する。

`IF EXISTS`

テーブル破棄時に「もし存在している場合は削除する」というオプションを指定する。IF EXISTSは、MySQL独自のオプション。

mysql> DROP TABLE IF EXISTS mytable111;

`CREATE DATABASE`

MySQLでは、「データべース」という単位の中にテーブルを作成する。ほかのRDBMSでは「スキーマ」であることも多い。そのため、CREATE DATABASE / DROP DATABASE文はMySQL独自の命令。

カラム別名

MySQLでは、SELECT時にカラムに付けた別名を、ORDER BY句やGROUP BY句で使用可能。カラムMAX(name)に付けたonenameという別名を、OREDER BYで使う例。

mysql> SELECT id this_id, MAX(name) onename
    -> FROM mytable
    -> GROUP BY this_id
    -> ORDER BY onename;
+---------+---------+
| this_id | onename |
+---------+---------+
|       2 | Alice   |
|       3 | Bob     |
|       1 | John    |
+---------+---------+

`SHOW`コマンド

SHOWコマンドは、mysqlコマンドクライアントの命令であり、ほかのRDBMSには存在しない。

文字列の結合

MySQLでは文字列の結合||や&&を使用不可能。MySQLでは、||はOR、&&はANDとして動作する。

※ sql_modeに'PIPES_AS_COUNT'を指定すると||を文字列結合として使用可能。

文字列結合の行うには、CONCAT()関数を使用する。この関数は可変長の引数を取ることができる関数。

mysql> SELECT CONCAT('ABC', 'de', 'FGH', 'ij');
+----------------------------------+
| CONCAT('ABC', 'de', 'FGH', 'ij') |
+----------------------------------+
| ABCdeFGHij                       |
+----------------------------------+

文字列を結合しようとして||を使用したときに、OR演算が実行される。次の例では3つのOR結合をしているので、FALSE(0)が返される。文字列を結合しようとして||を使用したと思われる場合にWARNINGが出されるので、SHOW WARNINGSで内容を確認すると文字列結合に||を使えないことがわかる。

mysql> SELECT 'ABC'||'de'||'FGH';
+--------------------+
| 'ABC'||'de'||'FGH' |
+--------------------+
|                  0 |
+--------------------+
1 row in set, 5 warnings (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                               |
+---------+------+-------------------------------------------------------------------------------------------------------+
| Warning | 1287 | '|| as a synonym for OR' is deprecated and will be removed in a future release. Please use OR instead |
| Warning | 1287 | '|| as a synonym for OR' is deprecated and will be removed in a future release. Please use OR instead |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'ABC'                                                               |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'de'                                                                |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'FGH'                                                               |
+---------+------+-------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

MySQL Basic ​

MySQLの主なデータベースオブジェクト ​

基本構文 ​

生成 ​

破棄 ​

変更 ​

データーベースの作成 削除 変更 ​

CREATE DATABASEの構文 ​

DROP DATABASEの構文 ​

ALTER DATABASEの構文 ​

テーブルの作成 削除 変更 ​

CREATE TABLEの構文 ​

列の情報 部分の構文 ​

NOT_NULL ​

DEFAULT ​

AUTO_INCREMENT ​

UNIQUE ​

PRIMARY KEY ​

CREATE TABLE例 ​

テーブル定義の確認 ​

DROP TABLE構文 ​

既存のテーブル定義の変更 ​

インデックスの作成 ​

最低限必要なもの ​

CREATE INDEXの構文 ​

最小限の指定でインデックスを作成する例 ​

インデックスの削除 ​

DROP INDEXの構文 ​

インデックスの変更 ​

ストアドルーチンの作成 ​

ストアドプロシージャ ​

ストアドファンクション ​

delimiter文 ​

ストアドファンクションの例 ​

SELECT ​

SELECT文 ​

SELECT文実行するための準備 ​

mytableを作成 ​

mytableにデータを挿入 ​

othertableを作成 ​

othertableにデータを挿入 ​

SELECT例 ​

実行結果 ​

検索結果をファイルに出力 ​

INSERT ​

INSERT文 ​

INSERT構文 ​

INSERT文を実行するための準備 ​

mytableを作成 ​

INSERT例 ​

SELECT文で取り出す ​

実行結果 ​

REPLACE文 ​

REPLACE例 ​

UPDATE ​

UPDATE文 ​

UPDATE構文 ​

UPDATE文を実行するための準備 ​

mytableを作成 ​

mytableにデータを挿入 ​

UPDATE例 ​

UPDATE前との比較 ​

UPDATE前 ​

UPDATE後 ​

DELETE TRUNCATE ​

DELETE文 ​

DELETE構文 ​

DELETE文を実行するための準備 ​

mytableを作成 ​

mytableにデータを挿入 ​

DELETE例 ​

DELETE前との比較 ​

DELETE後 ​

TRUNCATE文 ​

TRUNCATE構文 ​

SQL 文の組み合わせ ​

SELECTの結果をINSERT ​

SELECTの結果を新たなテーブルに ​

WHERE文で指定する条件 ​

条件が複数ある場合 ​

MySQL Basic

MySQLの主なデータベースオブジェクト

基本構文

生成

破棄

変更

データーベースの作成削除変更

`CREATE DATABASE`の構文

`DROP DATABASE`の構文

`ALTER DATABASE`の構文

テーブルの作成削除変更

`CREATE TABLE`の構文

列の情報　部分の構文

NOT_NULL

DEFAULT

AUTO_INCREMENT

UNIQUE

PRIMARY KEY

`CREATE TABLE`例

テーブル定義の確認

`DROP TABLE`構文

既存のテーブル定義の変更

インデックスの作成

最低限必要なもの

`CREATE INDEX`の構文

最小限の指定でインデックスを作成する例

インデックスの削除

`DROP INDEX`の構文

インデックスの変更

ストアドルーチンの作成

ストアドプロシージャ

ストアドファンクション

`delimiter`文

`ストアドファンクション`の例

SELECT

`SELECT`文

`SELECT`文実行するための準備

`mytable`を作成

`mytable`にデータを挿入

`othertable`を作成

`othertable`にデータを挿入

`SELECT`例

実行結果

検索結果をファイルに出力

INSERT

`INSERT`文

`INSERT`構文

`INSERT`文を実行するための準備

`mytable`を作成

`INSERT`例

`SELECT`文で取り出す

実行結果

`REPLACE`文

`REPLACE`例

UPDATE

`UPDATE`文

`UPDATE`構文

`UPDATE`文を実行するための準備

`mytable`を作成

`mytable`にデータを挿入

`UPDATE`例

`UPDATE`前との比較

`UPDATE`前

`UPDATE`後

DELETE TRUNCATE

`DELETE`文

`DELETE`構文

`DELETE`文を実行するための準備

`mytable`を作成

`mytable`にデータを挿入

`DELETE`例

`DELETE`前との比較

`DELETE`後

`TRUNCATE`文

`TRUNCATE`構文

SQL 文の組み合わせ

`SELECT`の結果を`INSERT`

`SELECT`の結果を新たなテーブルに

`WHERE`文で指定する条件

条件が複数ある場合