# データベース操作

データベースの作成

CREATE DATABASE database_name;

データベースの削除

DROP DATABASE database_name;

テーブルの作成

CREATE TABLE table_name (col_name type, ...);

主キー

CREATE TABLE table_name (col_name type, ..., PRIMARY KEY (col_name));

• PRIMARY KEY の重複と NULL は NG
• 複数の列を指定できる (複合主キー)
  • PRIMARY KEY (列名 1, 列名 2 )

外部キー

CREATE TABLE table_name (
  col_name type, ...,
  FOREIGN KEY (col_name) REFERENCES parent_table_name(col_name)
);

• 子テーブルの外部キーの値は親テーブルに存在しないといけない
• 参照される値 (親テーブル) の UPDATE / DELETE は NG

親テーブルの UPDATE / DELETE 時の設定

CREATE TABLE table_name (
  col_name type, ...,
  FOREIGN KEY (col_name) REFERENCES parent_table_name(col_name)
  ON DELETE [ アクション ]
  ON UPDATE [ アクション ]
);

設定できるアクション。

データの検索

SELECT col_name1, col_name2, ... FROM table_name [ 条件 ];

条件

• 比較演算子

• BETWEEN

SELECT * FROM table_name WHERE BETWEEN value1 AND value2;

指定した範囲に値があるものを取得。

• IN

SELECT * FROM table_name WHERE col_name IN ('value1', 'value2');

指定した値のリストの中にあるものを取得。

• LIKE

SELECT * FROM table_name WHERE col_name LIKE '_100%';

% は任意の文字数の任意の文字、 _ は 1 文字の任意の文字。

• サブクエリ

SELECT * FROM table_name
WHERE col_name1 = (
  SELECT * FROM table_name WHERE col_name2 = value
);

副問合せ。

DISTINCT

SELECT DISTINCT col_name1, col_name2, ... FROM table_name;

SELECT の結果から重複業を除外できる。

GROUP BY でも重複削除できる。

SELECT col_name1, col_name2, ... FROM table_name GROUP BY col_name1;

ORDER BY

SELECT * FROM table_name ORDER BY col_name1 ASC, col_name2 DESC;

ASC は昇順。 DESC は降順。 ASC は省略可能。

LIMIT

SELECT * FROM table_name ORDER BY col_name LIMIT 5;

集約関数

SELECT col_name, MAX(col_name) FROM table_name;

基本的に NULL は無視される。 集約関数の種類。

SUM / AVG / COUNT は DISTINCT をサポートしている。

SELECT col_name, COUNT( DISTINCT col_name ) FROM table_name;

GROUP BY

SELECT col_name, COUNT(col_name) FROM table_name GROUP BY col_name;

テーブルを集約キーでグループ分けしてから集計する。

HAVING

SELECT COUNT(col_name1) FROM table_name GROUP BY col_name1 HAVING COUNT(col_name1) >= value;

GROUP BY されたグループに対して条件を指定できる。

この場合、列名 1 で集計した値をカウントしてから HAVING で指定した条件のもののみを取得する。

結合 ( JOIN )

結合の種類は 3 つ。

• クロス結合 ( CROSS JOIN )
• 内部結合 ( INNER JOIN )
• 外部結合 ( OUTER JOIN )

クロス結合 ( CROSS JOIN )

SELECT *
FROM table_name1 CROSS JOIN table_name2;

すべてのテーブルの組み合わせを作る。

内部結合 ( INNER JOIN )

SELECT table_name1.*, table_name2.col_name, ...
FROM table_name1 INNER JOIN table_name2
ON table_name1.col_name1 = table_name2.col_name1;

クロス結合の部分集合。条件一致したものだけを取得する。

USING でも書ける。

SELECT table_name1.*, table_name2.col_name, ...
FROM table_name1 INNER JOIN table_name2 USING(col_name);

外部結合

SELECT table_name1.*, table_name2.col_name, ...
FROM table_name1 ( LEFT | RIGHT ) OUTER JOIN table_name2
ON table_name1.col_name1 = table_name2.col_name1;

内部結合と同様に条件一致したものを取得し、さらに、マッチしなかった ( LEFT | RIGHT ) のテーブルの行は残す。

テーブルの削除

DROP TABLE table_name;

データの挿入

INSERT INTO database_name.table_name(col_name1, col_name2, ...) VALUES (value1, value2, ...);

全てのカラムに値を追加する場合は以下のように書ける。

INSERT INTO table_name VALUES(value1, value2, ...);

ビュー

データを持たず実行分だけが入っている。

CREATE VIEW view_name (col_name1, col_name2, ...) AS select_statement;

数値として取り出す

SELECT col_name FROM as unsigned FROM table_name;

index の付け方

ALTER TABLE table_name AND INDEX index_name (col_name);

トランザクション

BEGIN で開始して, COMMIT で反映, ROLLBACK で取り消しができる。

BEGIN;
  SQL1;
  SQL2;
COMMIT;