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

MySQL勉強メモ

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

[https://education.oracle.com/ja/mysql-56-developer/pexam_1Z0-882:embed:cite]

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

[https://dev.mysql.com/doc/refman/5.6/ja/:embed:cite]


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

言語構造

[https://dev.mysql.com/doc/refman/5.6/ja/language-structure.html:embed:cite]

スキーマオブジェクト名

  • PostgreSQLでいう「データベースオブジェクト」のこと
  • スペ終わりは駄目:
mysql> CREATE TABLE `tbl `;
ERROR 1103 (42000): Incorrect table name 'tbl '
  • クォートを標準SQL準拠にしている場合は""でも可
mysql> CREATE TABLE "interval" (begin INT, end INT);
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 '"interval" (begin INT, end INT)' at line 1

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

mysql> CREATE TABLE "interval" (begin INT, end INT);
Query OK, 0 rows affected (0.01 sec)
  • 1eとかを使うのはやめようねという話
CREATE TABLE tbl(`1e` INT);
INSERT INTO tbl VALUES (2);

SELECT `1e`+1,1e+1 FROM tbl;
+--------+------+
| `1e`+1 | 1e+1 |
+--------+------+
|      3 |   10 |
+--------+------+
1 row in set (0.00 sec)
  • 識別子の長さ上限

    • だいたい64文字
    • 例外:
    • エイリアス: 256

      • さらなる例外: CREATE VIEWのAS内では64(通常のカラム名と同じ制限)
    • 複合ステートメントラベル: 16

      • トリガーやストアドルーチンのBEGIN…ENDのラベル

識別子の修飾子

[https://dev.mysql.com/doc/refman/5.6/ja/identifier-qualifiers.html:embed:cite]

  • schema_name.table.name.column_nameみたいなやつ
mysql> CREATE TABLE .tbl(col INT);
Query OK, 0 rows affected (0.01 sec)
  • ODBC互換のためにテーブル名に.を前置することが許可されている

    • デフォルトデータベース

関数名の構文解析と解決

mysql> SELECT COUNT(*) FROM tbl;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT (*) FROM 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 '*) FROM tbl' at line 1

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

mysql> SELECT COUNT (*) FROM tbl;
+-----------+
| COUNT (*) |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)
  • デフォルトで関数名の後にスペースを空けることは認められない
  • SET sql_mode='IGNORE_SPACE';すると空けられるようになる

    • SET sql_mode='ANSI;等、コンポジットモードに含まれている場合でも可
mysql> SET sql_mode='ANSI';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT (*) FROM tbl;
+-----------+
| COUNT (*) |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

予約語

[https://dev.mysql.com/doc/refman/5.6/ja/reserved-words.html:embed:cite]

  • 予約語を識別子として使いたい場合はbacktickで囲む
mysql> CREATE TABLE interval (begin INT, end INT);
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 'interval (begin INT, end INT)' at line 1

mysql> CREATE TABLE `interval` (begin INT, end INT);
Query OK, 0 rows affected (0.02 sec)
  • ピリオドの後は予約語でもクォート不要
mysql> CREATE TABLE sample.interval (begin INT, end INT);
Query OK, 0 rows affected (0.01 sec)
  • 歴史的経緯により例外的に大丈夫なものもある

    • 多くの人が使っちゃってたので
mysql> CREATE TABLE date(d date);
Query OK, 0 rows affected (0.01 sec)
  • 大丈夫なやつ

    • ACTION
    • BIT
    • DATE
    • ENUM
    • NO
    • TEXT
    • TIME
    • TIMESTAMP

式の構文

  • NOT!の優先順位の話
  • デフォルトで!NOTよりも優先順位が高い
mysql> SELECT NOT ! 1;
+---------+
| NOT ! 1 |
+---------+
|       1 |
+---------+
1 row in set (0.01 sec)

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

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

mysql> SELECT ! NOT 1;
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 'NOT 1' at line 1
  • ので、!NOTと結合してエラーになる模様
  • これを避けるには、sql_modeHIGH_NOT_PRECEDENCEを設定して!NOTの優先順位を同じにする
mysql> SET sql_mode = 'high_not_precedence';
Query OK, 0 rows affected (0.00 sec)

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

mysql> SELECT NOT NOT 1;
+-----------+
| NOT NOT 1 |
+-----------+
|         1 |
+-----------+
1 row in set (0.01 sec)

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

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

コメントの構文を識別し、使用する

SELECT 1+1; #hogehoge

SELECT 1+1; -- piyopiyo

SELECT 1+
/*
 multiple-line
comment
*/
1;

これは駄目:

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

    -> ;
;
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 '--fugafuga' at line 1
  • --の後はスペースを入れないと単項マイナス2つと解釈される
mysql> SELECT --1;
+-----+
| --1 |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)
  • MySQLでのみ実行されるコメント
CREATE TABLE tbl(id INT, col INT);
INSERT INTO tbl VALUES (1,1),(2,1),(3,2),(4,NULL);
SELECT t1.id AS t1id
     , t2.id AS t2id
     , t2.col
  FROM tbl t1
  /*! LEFT */ JOIN tbl t2
  ON (t1.id = t2.col);
+------+------+------+
| t1id | t2id | col  |
+------+------+------+
|    1 |    1 |    1 |
|    1 |    2 |    1 |
|    2 |    3 |    2 |
|    3 | NULL | NULL |
|    4 | NULL | NULL |
+------+------+------+
5 rows in set (0.00 sec)
  • あまりいい例じゃない
  • 普通はMySQL特有のキーワードに対して/*! */コメントを付ける

    • 例: STRAIGHT_JOIN, HIGH_PRIORITYなど

プリペアード・ステートメントについて説明する。プリペアード・ステートメントを使用する

[https://dev.mysql.com/doc/refman/5.6/ja/sql-syntax-prepared-statements.html:embed:cite]

ユーザー定義変数

こういうやつ:

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

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

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

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

プリペアドステートメント

  • ユーザー定義変数を使う
mysql> SET @s = 'SELECT ? FROM DUAL';
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt FROM @s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

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

mysql> EXECUTE stmt USING @a;
+---+
| ? |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)