勉強日記

チラ裏

MySQL 5.6 Developer試験対策 3 データに対する問い合わせ

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

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

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

dev.mysql.com


基本的なSELECT文を実行する

\h select
Name: 'SELECT'
Description:
Syntax:
SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
      [PARTITION partition_list]]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [into_option]
    [FOR UPDATE | LOCK IN SHARE MODE]

into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name] ...
}

SELECT is used to retrieve rows selected from one or more tables, and
can include UNION statements and subqueries. See [HELP UNION], and
https://dev.mysql.com/doc/refman/5.6/en/subqueries.html.

The most commonly used clauses of SELECT statements are these:

o Each select_expr indicates a column that you want to retrieve. There
  must be at least one select_expr.

o table_references indicates the table or tables from which to retrieve
  rows. Its syntax is described in [HELP JOIN].

o SELECT supports explicit partition selection using the PARTITION
  keyword with a list of partitions or subpartitions (or both)
  following the name of the table in a table_reference (see [HELP
  JOIN]). In this case, rows are selected only from the partitions
  listed, and any other partitions of the table are ignored. For more
  information and examples, see
  https://dev.mysql.com/doc/refman/5.6/en/partitioning-selection.html.

  SELECT ... PARTITION from tables using storage engines such as MyISAM
  that perform table-level locks (and thus partition locks) lock only
  the partitions or subpartitions named by the PARTITION option.

  For more information, see
  https://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations-lock
  ing.html.

o The WHERE clause, if given, indicates the condition or conditions
  that rows must satisfy to be selected. where_condition is an
  expression that evaluates to true for each row to be selected. The
  statement selects all rows if there is no WHERE clause.

  In the WHERE expression, you can use any of the functions and
  operators that MySQL supports, except for aggregate (summary)
  functions. See
  https://dev.mysql.com/doc/refman/5.6/en/expressions.html, and
  https://dev.mysql.com/doc/refman/5.6/en/functions.html.

SELECT can also be used to retrieve rows computed without reference to
any table.

URL: https://dev.mysql.com/doc/refman/5.6/en/select.html

  • テーブルを参照しないこともできる
SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.6.48    |
+-----------+
1 row in set (0.00 sec)
  • この場合、ダミーのテーブル名としてDUALを指定することが許可されている
SELECT VERSION() FROM DUAL;
+-----------+
| VERSION() |
+-----------+
| 5.6.48    |
+-----------+
1 row in set (0.00 sec)
  • 【補】実際にそういう表があるわけではない
SELECT * FROM information_schema.tables WHERE table_name='DUAL';
Empty set (0.00 sec)
DESC DUAL;
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 'DUAL' at line 1

SELECT文で返される行を制限する

CREATE TABLE tbl(num INT, ch CHAR(1));
INSERT INTO tbl(num,ch) VALUES
(1,'a'),(2,'c'),(3,'b'),(1,'d'),(1,'e'),(3,'a');
SELECT * FROM tbl LIMIT 2,3;
+------+------+
| num  | ch   |
+------+------+
|    3 | b    |
|    1 | d    |
|    1 | e    |
+------+------+
3 rows in set (0.00 sec)
  • 下記と同義
    • PostgreSQLとの互換性のため
    • 【補】LIMIT/OFFSETは非標準
SELECT * FROM tbl  LIMIT 3 OFFSET 2;
+------+------+
| num  | ch   |
+------+------+
|    3 | b    |
|    1 | d    |
|    1 | e    |
+------+------+
3 rows in set (0.00 sec)

SELECT文で返される列を制限する

SELECT文の結果にソートを適用する

    [ORDER BY {col_name | expr | position}
  • カラム名、式、位置を指定できる
  • 位置: [1,N]
    • 標準SQLから削除されたため非推奨

データを集計およびグループ化するSELECT文を実行する

SELECT num, COUNT(*) FROM tbl GROUP BY num;
+------+----------+
| num  | COUNT(*) |
+------+----------+
|    1 |        3 |
|    2 |        1 |
|    3 |        2 |
+------+----------+
3 rows in set (0.00 sec)
SELECT ch, COUNT(*) FROM tbl GROUP BY ch;
+------+----------+
| ch   | COUNT(*) |
+------+----------+
| a    |        2 |
| b    |        1 |
| c    |        1 |
| d    |        1 |
| e    |        1 |
+------+----------+
5 rows in set (0.00 sec)
  • GROUP BYでは暗黙のソートが走る
  • これをやめたい場合(オーバヘッドを嫌う場合)は、ORDER BY NULLする
SELECT num, COUNT(*) FROM tbl GROUP BY num ORDER BY NULL;
+------+----------+
| num  | COUNT(*) |
+------+----------+
|    1 |        3 |
|    2 |        1 |
|    3 |        2 |
+------+----------+
3 rows in set (0.00 sec)

mysql> SELECT ch, COUNT(*) FROM tbl GROUP BY ch ORDER BY NULL;
SELECT ch, COUNT(*) FROM tbl GROUP BY ch ORDER BY NULL;
+------+----------+
| ch   | COUNT(*) |
+------+----------+
| a    |        2 |
| c    |        1 |
| b    |        1 |
| d    |        1 |
| e    |        1 |
+------+----------+
5 rows in set (0.00 sec)
  • 当然result setの行の順番は保証されなくなる

そのた

EXPLAIN SELECT SQL_SMALL_RESULT * FROM tbl \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra: NULL
1 row in set (0.00 sec)

ERROR: 
No query specified
EXPLAIN SELECT SQL_BUFFER_RESULT * FROM tbl \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra: Using temporary
1 row in set (0.00 sec)

ERROR: 
No query specified
  • SQL_BUFFER_RESULT: 結果セットを強制的に一時テーブルに格納する
  • 他にもいろいろ