MySQL 5.6 Developer試験公式「試験内容チェックリスト」

https://education.oracle.com/ja/mysql-56-developer/pexam_1Z0-882education.oracle.com

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

大文字と小文字の区別、修飾名、別名、予約語の使用など、MySQLの識別子の実装について説明する
- リテラル値

大文字と小文字の区別、修飾名、別名、予約語の使用など、MySQLの識別子の実装について説明する

言語構造

dev.mysql.com

リテラル値

文字列リテラル

	文字列リテラル	カラム名
標準SQL	`'`	`"`
MySQL	`'`, `"`	`

PostgreSQLなんかは標準準拠
ANSI_QUOTES SQLモードだと標準準拠になる

SELECT @@GLOBAL.sql_mode;
+------------------------+
| @@GLOBAL.sql_mode      |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

SELECT "hoge";
+------+
| hoge |
+------+
| hoge |
+------+
1 row in set (0.00 sec)

mysql> SET sql_mode='ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT "hoge";
SELECT "hoge";
ERROR 1054 (42S22): Unknown column 'hoge' in 'field list'

文字列リテラルの連結

SELECT 'a' ' ' 'string';
+----------+
| a        |
+----------+
| a string |
+----------+
1 row in set (0.00 sec)

バイナリ文字列
- 文字セット、照合順序をもたないバイト文字列
非バイナリ文字列
- 文字セット、照合順序をもつバイト文字列

SELECT _latin1'string' COLLATE latin1_danish_ci;
+------------------------------------------+
| _latin1'string' COLLATE latin1_danish_ci |
+------------------------------------------+
| string                                   |
+------------------------------------------+
1 row in set (0.00 sec)

エスケープ
- よくある\nとかそういうの
- \z: ASCII 26
  - WindowsにおけるEOF問題の回避に使える
文字列にクォートを含める方法

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

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

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

'' ''は空文字列2つが連結されて空文字列になる

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

数値リテラル

正確値リテラル
- 整数
- DECIMAL
近似値リテラル
- 浮動小数点数

SELECT 1, .2, 3.4, -5, --6, +7;
+---+-----+-----+----+-----+---+
| 1 | .2  | 3.4 | -5 | --6 | 7 |
+---+-----+-----+----+-----+---+
| 1 | 0.2 | 3.4 | -5 |   6 | 7 |
+---+-----+-----+----+-----+---+
1 row in set (0.00 sec)

SELECT 1.2e3;
+-------+
| 1.2e3 |
+-------+
|  1200 |
+-------+
1 row in set (0.00 sec)

日付リテラルと時間リテラル

SELECT DATE '20200202',TIME '235959',TIMESTAMP '20200202235959';
+-----------------+---------------+----------------------------+
| DATE '20200202' | TIME '235959' | TIMESTAMP '20200202235959' |
+-----------------+---------------+----------------------------+
| 2020-02-02      | 23:59:59      | 2020-02-02 23:59:59        |
+-----------------+---------------+----------------------------+
1 row in set (0.00 sec)

デリミタは割と融通が効く

SELECT DATE'2020/02/02', DATE'2020-02-02', DATE'2020!02!02';
+------------------+------------------+------------------+
| DATE'2020/02/02' | DATE'2020-02-02' | DATE'2020!02!02' |
+------------------+------------------+------------------+
| 2020-02-02       | 2020-02-02       | 2020-02-02       |
+------------------+------------------+------------------+
1 row in set (0.00 sec)

まぜても大丈夫

SELECT DATE'2020!02^02';
+------------------+
| DATE'2020!02^02' |
+------------------+
| 2020-02-02       |
+------------------+
1 row in set (0.00 sec)

ASCII 32番(SPC)以下は駄目な感じがある

SELECT DATE'2020 02 02';
ERROR 1525 (HY000): Incorrect DATE value: '2020 02 02'

スペースはオプショナル

SELECT DATE'20200202',TIME'235959',TIMESTAMP'20200202235959';
+----------------+--------------+---------------------------+
| DATE'20200202' | TIME'235959' | TIMESTAMP'20200202235959' |
+----------------+--------------+---------------------------+
| 2020-02-02     | 23:59:59     | 2020-02-02 23:59:59       |
+----------------+--------------+---------------------------+
1 row in set (0.00 sec)

ODBC構文も認識する

SELECT {d'20200202'},{t'235959'},{ts'20200202235959'};
+---------------+-------------+----------------------+
| {d'20200202'} | {t'235959'} | {ts'20200202235959'} |
+---------------+-------------+----------------------+
| 2020-02-02    | 23:59:59    | 2020-02-02 23:59:59  |
+---------------+-------------+----------------------+
1 row in set (0.00 sec)

DATE

不正な日付
- 月や日の値域が不正な場合

mysql> SELECT DATE'071332';
SELECT DATE'071332';
ERROR 1525 (HY000): Incorrect DATE value: '071332'

テーブルの列だとまた違う

mysql> CREATE TABLE tbl (d date);
CREATE TABLE tbl (d date);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO tbl VALUES ('071332');
INSERT INTO tbl VALUES ('071332');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'd' at row 1 |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM tbl;
SELECT * FROM tbl;
+------------+
| d          |
+------------+
| 0000-00-00 |
+------------+
1 row in set (0.00 sec)

ゼロ日付

mysql> SELECT DATE'000000';
SELECT DATE'000000';
+--------------+
| DATE'000000' |
+--------------+
| 0000-00-00   |
+--------------+
1 row in set (0.00 sec)

しかるべきsql_modeを設定すればゼロ日付も不正として弾ける

mysql> SET sql_mode='no_zero_date';
SET sql_mode='no_zero_date';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT DATE'000000';
SELECT DATE'000000';
ERROR 1525 (HY000): Incorrect DATE value: '000000'

TIMESTAMP

日と時の間の分割には、Tが使える

SELECT TIMESTAMP'2020-01-01 23:59:59',TIMESTAMP'2020-01-01T23:59:59';
+--------------------------------+--------------------------------+
| TIMESTAMP'2020-01-01 23:59:59' | TIMESTAMP'2020-01-01T23:59:59' |
+--------------------------------+--------------------------------+
| 2020-01-01 23:59:59            | 2020-01-01 23:59:59            |
+--------------------------------+--------------------------------+
1 row in set (0.00 sec)

一切区切らないことも可

SELECT TIMESTAMP'20200101235959';
+---------------------------+
| TIMESTAMP'20200101235959' |
+---------------------------+
| 2020-01-01 23:59:59       |
+---------------------------+
1 row in set (0.00 sec)

2038年問題がある
- オンメモリは大丈夫

mysql> SELECT TIMESTAMP'2038-01-01 00:00:00';
SELECT TIMESTAMP'2038-01-01 00:00:00';
+--------------------------------+
| TIMESTAMP'2038-01-01 00:00:00' |
+--------------------------------+
| 2038-01-01 00:00:00            |
+--------------------------------+
1 row in set (0.00 sec)

mysql> SELECT TIMESTAMP'2039-01-01 00:00:00';
SELECT TIMESTAMP'2039-01-01 00:00:00';
+--------------------------------+
| TIMESTAMP'2039-01-01 00:00:00' |
+--------------------------------+
| 2039-01-01 00:00:00            |
+--------------------------------+
1 row in set (0.00 sec)

永続化すると警告が出、ゼロ日時が入る

mysql> CREATE TABLE tbl(ts TIMESTAMP);
CREATE TABLE tbl(ts TIMESTAMP);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO tbl VALUES ('2038-01-01 00:00:00');
INSERT INTO tbl VALUES ('2038-01-01 00:00:00');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tbl VALUES ('2039-01-01 00:00:00');
INSERT INTO tbl VALUES ('2039-01-01 00:00:00');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
SHOW WARNINGS;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'ts' at row 1 |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM tbl;
SELECT * FROM tbl;
+---------------------+
| ts                  |
+---------------------+
| 2038-01-01 00:00:00 |
| 0000-00-00 00:00:00 |
+---------------------+
2 rows in set (0.00 sec)

mysql> INSERT INTO tbl '';
INSERT INTO tbl '';
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 '''' at line 1

YYMMDD
- YYが[70,99]: 20世紀
- YYが[00,69]:21正規

mysql> SELECT DATE '700101';
SELECT DATE '700101';
+---------------+
| DATE '700101' |
+---------------+
| 1970-01-01    |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT DATE '691231';
SELECT DATE '691231';
+---------------+
| DATE '691231' |
+---------------+
| 2069-12-31    |
+---------------+
1 row in set (0.00 sec)

TIME

mysql> SELECT TIME'12:34:56';
SELECT TIME'12:34:56';
+----------------+
| TIME'12:34:56' |
+----------------+
| 12:34:56       |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT TIME'12:34';
SELECT TIME'12:34';
+-------------+
| TIME'12:34' |
+-------------+
| 12:34:00    |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT TIME'56';
SELECT TIME'56';
+----------+
| TIME'56' |
+----------+
| 00:00:56 |
+----------+
1 row in set (0.00 sec)

日(D)をスペース区切りで指定できる
- [0,34]
  - 【疑問点】何これ？？？？？
- 2桁指定時の挙動が変わる

mysql> SELECT TIME'0 12:34:56';
SELECT TIME'0 12:34:56';
+------------------+
| TIME'0 12:34:56' |
+------------------+
| 12:34:56         |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT TIME'0 12:34';
SELECT TIME'0 12:34';
+---------------+
| TIME'0 12:34' |
+---------------+
| 12:34:00      |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT TIME'0 12';
SELECT TIME'0 12';
+------------+
| TIME'0 12' |
+------------+
| 12:00:00   |
+------------+
1 row in set (0.00 sec)

デリミタ指定時は各桁0埋めする必要なし

mysql> SELECT TIME'123';
SELECT TIME'123';
+-----------+
| TIME'123' |
+-----------+
| 00:01:23  |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT TIME'1:2:3';
SELECT TIME'1:2:3';
+-------------+
| TIME'1:2:3' |
+-------------+
| 01:02:03    |
+-------------+
1 row in set (0.00 sec)

マイクロ秒までを設定できる

SELECT TIME'12:34:56.123456';
+-----------------------+
| TIME'12:34:56.123456' |
+-----------------------+
| 12:34:56.123456       |
+-----------------------+
1 row in set (0.00 sec)

マイクロ秒はTIME型カラムには永続化できない
- 警告も出ない

mysql> CREATE TABLE tbl (t TIME);
CREATE TABLE tbl (t TIME);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO tbl VALUES ('12:34:56.123456');
INSERT INTO tbl VALUES ('12:34:56.123456');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tbl;
SELECT * FROM tbl;
+----------+
| t        |
+----------+
| 12:34:56 |
+----------+
1 row in set (0.00 sec)

type_name(fsp)型カラムに永続化する

mysql> CREATE TABLE tbl2 (t TIME(3));
CREATE TABLE tbl2 (t TIME(3));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO tbl2 VALUES ('12:34:56.123456');
INSERT INTO tbl2 VALUES ('12:34:56.123456');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tbl2;
SELECT * FROM tbl2;
+--------------+
| t            |
+--------------+
| 12:34:56.123 |
+--------------+
1 row in set (0.00 sec)

fspの標準SQLデフォルトは6だが、MySQLのデフォルトは0

16進数リテラル

バイナリ文字列のように機能

SELECT X'41';
+-------+
| X'41' |
+-------+
| A     |
+-------+
1 row in set (0.00 sec)

数値に変換できる

SELECT X'41' + 0;
+-----------+
| X'41' + 0 |
+-----------+
|        65 |
+-----------+
1 row in set (0.00 sec)

まじめに変換するならCAST

SELECT 0x41, 0x41+0, CAST(0x41 AS UNSIGNED INT);
+------+--------+----------------------------+
| 0x41 | 0x41+0 | CAST(0x41 AS UNSIGNED INT) |
+------+--------+----------------------------+
| A    |     65 |                         65 |
+------+--------+----------------------------+
1 row in set (0.00 sec)

逆変換: HEX関数
- バイナリ文字列 -> 16進数
- 10進数 -> 16進数

SELECT HEX('A'), HEX(65);
+----------+---------+
| HEX('A') | HEX(65) |
+----------+---------+
| 41       | 41      |
+----------+---------+
1 row in set (0.00 sec)

booleanリテラル

SELECT TRUE,FALSE,true,false;
+------+-------+------+-------+
| TRUE | FALSE | TRUE | FALSE |
+------+-------+------+-------+
|    1 |     0 |    1 |     0 |
+------+-------+------+-------+
1 row in set (0.00 sec)

ビットフィールドリテラル

mysql> CREATE TABLE tbl (b BIT(8));
CREATE TABLE tbl (b BIT(8));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO tbl VALUES (255);
INSERT INTO tbl VALUES (255);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tbl SET b = b'1010';
INSERT INTO tbl SET b = b'1010';
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tbl SET b = b'0101';
INSERT INTO tbl SET b = b'0101';
Query OK, 1 row affected (0.02 sec)

mysql> SELECT b+0, BIN(b+0), OCT(b+0), HEX(b+0) FROM tbl;
SELECT b+0, BIN(b+0), OCT(b+0), HEX(b+0) FROM tbl;
+------+----------+----------+----------+
| b+0  | BIN(b+0) | OCT(b+0) | HEX(b+0) |
+------+----------+----------+----------+
|  255 | 11111111 | 377      | FF       |
|   10 | 1010     | 12       | A        |
|    5 | 101      | 5        | 5        |
+------+----------+----------+----------+
3 rows in set (0.00 sec)

【補】
- SET型の内部表現はビットフィールドだったはず
ユーザ変数のビット値
- バイナリ文字列として扱われる

mysql> SET @v1 = 0b1000001;
SET @v1 = 0b1000001;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @v2 = CAST(0b1000001 AS UNSIGNED);
SET @v2 = CAST(0b1000001 AS UNSIGNED);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @v3 = 0b1000001+0;
SET @v3 = 0b1000001+0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @v1,@v2,@v3;
SELECT @v1,@v2,@v3;
+------+------+------+
| @v1  | @v2  | @v3  |
+------+------+------+
| A    |   65 |   65 |
+------+------+------+
1 row in set (0.00 sec)

NULL値

【補】NULLは値ではありません

私たちは、まず「失われてはいるが、適用可能な値」を示すマークの定義から始めよう。これを「A-Mark」と呼ぶ。
このマークは、DBMS において値（value）としても変数（variable）としても扱われない。 (エドガー・F・コッド)

NULL に関する大切なことは、厳密には NULL が値ではないということである。 (クリス・デイト)

LOAD DATA INFILEでは\Nで表現

勉強日記

チラ裏

MySQL 5.6 Developer試験対策 6 MySQLの一般的な構文_1

大文字と小文字の区別、修飾名、別名、予約語の使用など、MySQLの識別子の実装について説明する

リテラル値

文字列リテラル

数値リテラル

日付リテラルと時間リテラル

DATE

TIMESTAMP

TIME

16進数リテラル

booleanリテラル

ビットフィールドリテラル

NULL値