よく使うであろうSQL構文のメモ

最近SQLを扱うことが多くなってきたので、今後も使うであろうSQL文をまとめておく。

今回は主にSQL Liteを使い、また「user」というテーブルを扱うことを想定した記述となる。

DBにテーブルを追加する

CREATE TABLE user (
user_id INTEGER,
name TEXT,
email TEXT,
memo TEXT
)

テーブル情報を確認（SQL Lite）

select * from sqlite_master;

上記はsqliteだが、mysqlでは「SHOW TABLES;」で表示できる

カラムを選択

SELECT user_mail, user_name FROM users

select [カラム1], [カラム2（任意）], [カラム3（任意）] From [テーブル名];

テーブル内の全てのカラムを取得したいとき

"SELECT * FROM users"

アスタリスク「*」を付けることでuser_name, user_mail, user_id,　などテーブル内のカラムデータを全て選択できる

カラムから任意のデータを表示（抽出）

select * From user WHERE user_id=2;

select * From [テーブル名] WHERE [カラム]=[値];

テーブルにデータを挿入

INSERT INTO user (user_id,name ,email) VALUES(1,'taro','taro@example.com');

INSERT INTO [テーブル名] ([カラム1],カラム2] ,カラム3]) VALUES(‘[挿入データ1]’, ‘[挿入データ2]’,'[挿入データ2]’);

ソート（大きい順）で検索

SELECT * FROM user ORDER BY user_id DESC LIMIT 2;

SELECT * FROM [テーブル名] ORDER BY [カラム] DESC（大きい順） LIMIT 2（2行）;

テーブルにデータを更新（既存のカラムのみ）

UPDATE user SET email='changed@example.com' WHERE user_id=2;

UPDATE [テーブル名]＋SET [カラム]='[値]’ WHERE [条件式（user_id2など）];

テーブルにカラムを追加

ALTER TABLE items ADD review INTEGER NULL;

ALTER TABLE [テーブル1] ADD [カラム1] [データ型] [NULL or NOT NULL];

テーブル情報を削除

DROP TABLE user;

DROP TABLE [テーブル名];

データを削除

DELETE FROM user WHERE user_id=1;

DELETE FROM [テーブル名] WHERE [条件式]

少し特殊な操作

ここからは個人的に使う機会があった、少し特殊なやり方だ。

カラムをグループ化して、同一idが一番若いものをセレクト

SELECT item, MIN(favorite_id) as favorite_id FROM favorites WHERE item GROUP BY item;

MIN(favorite_id) AS favorite_id は favorite_id（数字）列の最小値（一番若い番）を選択し、その結果を favorite_id という名前で取得する。

MIN(favorite_id)のみだと、後の$result[‘favorite_id’]が$result[‘min_favorite_id]という名前で保管され、ややこしくなるので、favorite_idという元の名前を新しく作る形になっている。

GROUP BYはitemカラム全体をグループ化する。item カラムの値が同じレコードを1つのグループと見なす。そしてそのグループの中で favorite_id の最小値（つまり、MIN(favorite_id)）を取り出す。