表示
縦表示にする
\G
通常;(セミコロン)で終わるところを\Gとすることで、各カラムを縦方向順番に表示してくれる。
追加
レコードの追加
フィールドを指定して追加する
INSERT INTO table(col1, col2, ...) VALUES (val1, val2, ...);
※フィールド、データが1つの場合も()が必要。
フィールドの追加
ALTER TABLE table ADD col type init;
ALTER TABLE table ADD col type NOT NULL DEFAULT default;
削除
レコードの削除
条件を指定して削除する
DELETE FROM table WHERE condition;
conditionの条件に合う行をtableから削除する。WHERE句を指定しない場合はtableから全レコードが削除されてしまうので注意。
全レコードを削除する
DELETE FROM table;
フィールドの削除
ALTER TABLE table DROP FROM col;
ユーザーの追加
CREATE USER 'user'@'host' IDENTIFIED BY 'password';
検索・抽出
SELECT文を使う。SELECT文では()は不要。
列を選択して全レコードを抽出
全ての列を選択して全レコードを抽出する
SELECT * FROM table;
列を指定して全レコードを抽出する
SELECT col1, col2, ... FROM table;
条件によるレコード抽出
数値条件
SELECT col1, col2, ... FROM table WHERE col <op> num;
演算子<op>の種類
= |
等しい |
<> |
等しくない |
<, > |
より小さい、より大きい |
<=, >= |
以上、以下 |
A BETWEEN B AND C |
AはB以上C以下 |
A IN (val1, val2, ...) |
Aはリストの要素val1, val2, …の何れか |
S LIKE 'string' |
文字列Sが’string’に合致 |
文字列条件
SELECT col1, col2, ... FROM table WHERE col LIKE str;
ワイルドカードの種類
% |
0文字以上の文字列 |
_ |
任意の1文字(半角・全角を問わない) |
※後方一致、部分一致は処理速度が遅い。
論理演算子
! a, NOT a |
aの否定 |
a AND b, a && b |
aかつb |
a OR b, a || b |
aまたはb |
a XOR b |
aとbの排他論理和 |
データの変更
条件に合うレコードの指定したフィールドを変更
UPDATE table SET col = new_value WHERE condition;
WHERE句をセットしないと全レコードのデータが変更されてしまうので、基本的にWHERE句でレコードを絞り込む。
ソート～並べ替え
SELECT col1, col2, ...
FROM table ORDER BY col ASC/DESC;
colのデータで昇順／降順に並べ替えた結果を返す。
複数のカラムを指定すると、先頭から順にグルーピングされてソート(並べ替え～ORDER BY)。
グループ化
SELECT col, function(col), ... FROM table GROUP BY col;
GROUP BYで指定したフィールドの内容によってレコードをグループ化する。
- フィールド指定の場合はグループ先頭のフィールドが抽出される
- フィールド関数指定の場合はグループごとに関数が適用される
Tips/Troubleshooting
エイリアス
SELECT ... FROM table AS alias;
FROMやJOINでテーブルを指定する際にエイリアスを定義し、それより前のSELECTでもエイリアスを使うことができる。以下は2つのテーブルの内部結合の例。
|
|
SELECT c.customer_name, c.customer_mail, o.item, o.quantity, o.order_date FROM customer_table AS c JOIN order_table AS o ON c.customer_id = o.customer_id; |
また、フィールドにエイリアスを定義できる。フィールド演算結果にエイリアスを定義すればフィールド名として扱われる。
SELECT COL AS alias FROM ...
エイリアスを定義した場合、元の名前は使えなくなる。
サブクエリー
SELECT alias.col, ... FROM (SELECT ... FROM ...) as alias;
SELECTによるクエリーをサブクエリーとして親のSELECTの検索対象とする場合は、サブクエリーにエイリアスが必要。このときサブクエリーのSELECTの要素にエイリアスを定義して親のクエリーで参照可能。
空白を含むフィールド名
フィールド名に空白が含まれる場合はバッククォートで囲む。
SELCT `COL 1`, `COL 2`, ... FROM ...
演算
COUNT～レコード数のカウント
SELECT COUNT(*) FROM table;
SELECT COUNT(col) FROM table;
*で指定した場合はnullもカウント、フィールドを指定した場合はnullはカウントされない（他のフィールドも書くとそれらは1レコード目の値となる）。
結合
内部結合
JOIN～2つのテーブルの内部結合
SELECT table.col, ...
FROM table1
JOIN table2 ON table1.col1 = table2.col2;
- table1のcol1とtable2のcol2が等しいレコードのみが両テーブルから抽出されて結合される。
JOIN～3つのテーブルの結合
SELECT table.col, ...
FROM table1
JOIN table2 ON table1.col1 = table2.col2
JOIN table3 ON table2.col2 = table3.col3;
- tabe1とtable2を内部結合
- その結合結果とtable3を内部結合
外部結合
LEFT JOIN
SELECT table.col, ...
FROM table1
LEFT JOIN table2 ON table1.col1 = table2.col2;
- table1のすべてのレコードと、table2のレコードのうちcol2がtable1のcol1に等しいレコードが抽出され結合される
- table1にはあるがtable2にはないレコードのtable2.col2の値はnullになる
RIGHT JOIN
SELECT table.col, ...
FROM table1 RIGHT JOIN table2 ON table1.col1 = table2.col2;
- table2のすべてのレコードと、table1のレコードのうちcol1がtable2のcol2に等しいレコードが抽出され結合される
- table2にはあるがtable1にはないレコードのtable1.col1の値はnullになる