Open12

SQL

ピン留めされたアイテム
aiskyaisky

構文の違い

SQL は標準的な仕様が ANSI (American National Standards Institute) によって定めれらている。
しかし、中には SQL-like なだけで実は standard SQL には従っていない構文を使うデータベースもある。
そのため、データベースを触り始める前にはそこで使う構文が standard SQL に従っているかどうか必ず確認する必要がある。

  • Hive → HiveQL
  • Presto → standard SQL + alpha
  • Teradata → TeradataSQL (difference)
  • MySQL → standard SQL + alpha

見てみたところ MySQL はほとんど standard SQL に従っているようなので、MySQL がほぼ standard SQL だと思って学習を進めるのが良さそう。

aiskyaisky

JOIN の動作

テーブルの横方向の結合に用いる JOIN の使い方

  • (INNER) JOIN
    2 つのテーブルを比較して結合条件に一致したレコードだけを返す
  • LEFT/RIGHT/FULL (OUTER) JOIN
    結合条件に一致しなくても LEFT/RIGHT/FULL で指定したテーブルの全てのレコードを返す
    (NATURAL LEFT/RIGHT JOIN も同じ)
  • CROSS JOIN
    2 つのテーブルの全ての組み合わせを返す

ついでに、テーブルの縦方向の結合は UNION ALL/DISTINCT を用いる。
ALL を用いると全てのデータが、DISTINCT (省略可) を用いると重複を除いたデータのみが結合される。

aiskyaisky

条件分岐

MySQL の条件分岐文 statement と operator の 2 種類がある。

基本的には operator, function の CASE, IF が Presto で使えるよう。

-- CASE Operator
CASE value
    WHEN compare_value THEN result
    [WHEN compare_value THEN result ...]
    [ELSE result]
END

CASE 
    WHEN condition THEN result
    [WHEN condition THEN result ...]
    [ELSE result]
END

-- IF Function
IF(expr1, expr2, expr3)
-- CASE Statement
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

-- IF Statement
IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF
aiskyaisky

表示桁数の指定

SQL で表示される桁数を指定したい場合、関数 FORMAT(N, D, local) を用いる。

  • N: フォーマットしたい数
  • D: 小数点以下の表示したい桁数を指定
  • locale (optional):

四捨五入したいときは ROUND() を用いる。

aiskyaisky

正規表現

関数 REGEXP_LIKE(expr, pattern[, match_type]) を用いることで正規表現が使える

expr が指定した pattern にマッチした場合 1 を、そうでなければ 0 を返す。
expr or patternNULL の場合は NULL を返す。

aiskyaisky

大文字と小文字の区別

MySQL ではデータベース名とテーブル名大文字と小文字の区別をすることができる
(コマンドはどっちでも良い)
Default の設定がどうなっているかは OS に依っており、

  • Unix: 区別する
  • Windows, macOS: 区別しない

となっている。

現在の状態の確認には lower_case_table_names の値を参照すれば良い。

> SHOW VARIABLES LIKE 'lower_case_%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 1     |
+------------------------+-------+

値の意味は

  • 0: 大文字・小文字を区別して保存され、名前比較では大文字と小文字が区別される
  • 1: 全て小文字で格納され、名前比較では大文字と小文字は区別されない
  • 2: 大文字・小文字を区別して保存されるが MySQL ではルックアップ時に小文字に変換され、名前比較では大文字と小文字が区別されない
aiskyaisky

MySQL の version の確認方法

やり方は以下の3通り

--
select VERSION();
--
show VARIABLES like '%version%';
--
status;
aiskyaisky

SQL の実行順序

SQL の標準的な実行順序は以下の順になっている。

ORDER CLAUSE FUNCTION
1 FROM, including JOINs Choose and join tables to get base data
2 WHERE Filters
3 GROUP BY Aggregates
4 SUM, AVG ... etc
5 HAVING Filters the aggregated data
6 WINDOW functions
7 SELECT Returns the desired data
8 DISTINCT Takes distinct data
9 UNION Combines the result sets
10 ORDER BY Sorts
11 LIMIT and OFFSET Limits the returned data

以上を踏まえた注意点

  • WHERE, GROUP BY, LIMIT などは普通に書いた場合 JOIN の前に実行させることができない (やりたかったら副問合せなどが必要)

しかし、(公式の資料がないことから想像できるように) 細かい実際の実行順序は書かれた SQL query に応じて異なってくるので注意。

Refs.

aiskyaisky

複数条件に対する LIKE

LIKE で複数条件を指定したい場合、

SELECT * FROM some_table WHERE col LIKE IN ('%hoo%', '%bar%');

としたくなるがこれはできない。

この場合、LIKEOR で繋ぐか、正規表現 REGEXP_INSTR を使う。

SELECT * FROM some_table WHERE col LIKE  '%hoo%' OR col LIKE '%bar%';

SELECT * FROM some_table WHERE REGEXP_INSTR(col, 'hoo|bar');
aiskyaisky

Naming Convention

SQL では特に命名規則は決まっていないが、How to がありそうなのでまとめておく。

  • Use the singular (単数系) for the table name and column names and multiple words are no problem
  • Case はなんでも良い (Camel-, Pascal-case が多そう)
  • Primary key は (table name) x id の組み合わせにする
aiskyaisky

GROUP BYWINDOW 関数の併用

併用できる
group by した後にその total を求めるとかやりたい時に有用