SQLチートシート１

SELECT(カラム、テーブル指定)

カラム指定取得
SELECT column, another_column, …
FROM mytable;

全カラム取得
SELECT * 
FROM mytable;

重複なし
SELECT DISTINCT column, another_column, …
FROM mytable

カラム定義(二倍)
SELECT column_name / 2.0 AS new_column_name
FROM mytable

関数使用
SELECT AGG_FUNC(column_or_expression) AS new_name, …
FROM mytable

AGG_FUNCの例
COUNT(column)
MIN(column)
MAX(column)
AVG(column)
SUM(column)

WHERE(抽出条件)

SELECT column, another_column, …
FROM mytable
WHERE condition
    AND/OR another_condition
    AND/OR …;

conditionの例

col_name != 4 (比較演算子)
col_name BETWEEN 1.5 AND 10.5
col_name NOT BETWEEN 1 AND 10
col_name IN (2, 4, 6)
col_name NOT IN (1, 3, 5)
col_name LIKE "ABC"
col_name NOT LIKE "ABCD"

col_name LIKE "%AT%"
(matches "AT", "ATTIC", "CAT" or even "BATS")

col_name LIKE "AN_"
(matches "AND", but not "AN")

column IS/IS NOT NULL

ORDER BY(並び替え)

ORDER BY column ASC; (昇順)
ORDER BY column DESC; (降順)

LIMIT OFFSET(レコード数制限)

LIMIT num_limit OFFSET num_offset;

JOIN(テーブルを共通カラムで繋げる)

INNER/LEFT/RIGHT/FULL JOIN another_table 
    ON mytable.id = another_table.id

GROUP BY(複数のレコードを同じカラムでまとめる)

GROUP BY column;

HAVING(GROP BYに対しての抽出条件)

HAVING condition

INSERT(データ挿入)

INSERT INTO mytable
(column, another_column, …)
VALUES (value_or_expr, another_value_or_expr, …),
      (value_or_expr_2, another_value_or_expr_2, …),

UPDATE(データ編集)

UPDATE mytable
SET column = value_or_expr, 
    other_column = another_value_or_expr, 
    …
WHERE condition;

DELETE(データ削除)

DELETE FROM mytable
WHERE condition;

CREATE(テーブル作成)

CREATE TABLE IF NOT EXISTS mytable (
    column DataType TableConstraint DEFAULT default_value,
    another_column DataType TableConstraint DEFAULT default_value,
    …
);

Data typeの例
INTEGER(整数), BOOLEAN
FLOAT, DOUBLE, REAL(実数)
CHARACTER(num_chars), VARCHAR(num_chars), TEXT
DATE, DATETIME
BLOB(バイナリデータ(ファイル保存))

Table constraintsの例
PRIMARY KEY (主キー制約)
AUTOINCREMENT (INTEGERの連番)
UNIQUE
NOT NULL
CHECK (expression)

FOREIGN KEY (coulmn)
      REFERENCES table(coulmn)(外部キー制約)

ADD(カラムの挿入)

ALTER TABLE mytable
ADD column DataType OptionalTableConstraint 
    DEFAULT default_value;

DROP(テーブル削除)

DROP TABLE IF EXISTS mytable;