SQLコマンドまとめ

これまで学んだSQLコマンドについてまとめた。

• SQL文をうつ時は文末にセミコロン;を忘れない。忘れると文が終わらない。

データベースの操作

データベース作成

createdb <データベース名>

データベース一覧

psql -l

データベース削除

dropdb <データベース名>

データベースに接続

psql <データベース名>

データベースの接続終了

\q

操作方法

help

helpよりもっと詳しい操作方法

\?

テーブルの操作

テーブル作成

# 同時にカラムも登録できる
CREATE TABLE テーブル名 (列名1 データ型1 制約1 , 列名2 データ型2 制約2);

# 一度に複数のカラムを登録したい
CREATE TABLE テーブル名 (
   列名 データ型 制約
   CardID nchar(6),
   CustomerID nchar(5),
   IssueDate datetime,
   ExpireDate datetime,
   EmployeeID int
 )

テーブル一覧表示

\dt

テーブルに設定されてるカラム(列)確認

 \d <テーブル名>

テーブル名変更

alter table <変えたいテーブル名> rename to <変更後の名前>;

テーブル削除

drop table <テーブル名>

カラムの操作

カラム追加

alter table <テーブル名> add <カラム名> <データ型>;

カラム削除

alter table <テーブル名> drop  <カラム名>;

カラムの名前変更

rename <変更前カラム名> to <変更後カラム名>;

カラムのデータ型を変更

ALTER TABLE テーブル名 ALTER COLUMN カラム名 TYPE データ型
例: alter table users alter myname type varchar(32);

INSERT:テーブルにデータを登録する

INSERT  INTO テーブル名 (列名1 , 列名2, …) VALUES (入れたい値1 , 値2 …);

# 例
insert into posts (title, body) values (‘title1’,‘body11111’);
#=>titleというカラムにtitle1という値、bodyというカラムにbody11111を登録する

SELECT:必要なデータを検索して取り出せる

SELECT 列名, … FROM テーブル名;

すべてのカラムを取り出す

select * from テーブル名

一部のカラムを取り出す

select <カラム名> from <テーブル名>

テーブルを複数扱うとき

テーブル名前.フィールド名 と書く

UPDATE:レコードの値を変更する

update <テーブル> set <変えたいカラム> = <値> where <レコード> = ‘<変えたいレコード>‘;

• データ型が数値のカラムを+1で変えることもできる。

DELETE:レコードを削除する

delete from <テーブル名>

これを入力すると、　指定したテーブルに登録されているレコードすべてを消してしまうので注意! whereで条件をつけて消せる。

トランザクション

• トランザクションとは、"複数の処理をまとめて行う処理"の仕組み。
• commitとrollbackがある。

commit

1. begin; でトランザクション開始
2. SQL文①;
3. SQL文②;
4. commit;    # これを打つと、2~3で打ったSQL文の処理が実行される

rollback

• commitをしないで、やっぱりやめること。

1. begin;  でトランザクション開始
2. SQL文①;
3. SQL文②;
(4. select * from <テーブル名>  で、2~3で実行した結果を確認できる)
5. 結果を見て、やっぱり2~3でやった処理を無かったことにしたい時、rollback;する。

例

トランザクションをやってみた例

旬の使い方

• WHERE
• LIKE
• ORDER BY
• LIMIT
• OFFSET
• GROUP BY
• HAVING

WHERE:取り出すときの条件を指定できる

select <フィールド> from <テーブル> where <抽出したい条件>

例

#scoreの数値が4.0より大きいという条件で、usersテーブルの全カラムを取り出す
select * from users where score > 4.0 

# nameが'taguchi'のものだけを抽出したい
select * from users where name = ‘taguchi’

LIKE:対象のカラムに対して文字列検索

like <検索したい文字列>

• 任意の文字0文字以上:%
• 任意の文字1文字以上:_
• 参考:【SQL】LIKE句

例

LIKEを使ってみた例

ORDER BY ::レコードの並び替え

order by <カラム名>

• カラムのデータが数値の場合、小さい順になる

• 大きい順にしたい：descを付ける

• 複数の条件をつけられる

LIMIT:取得するデータの行数の上限を設定

LIMIT <取り出す数>

OFFSET:取り出す開始位置を指定

開始位置は、1番目が0(Rubyのインデックスと同じ) - offset 0 = 1番目が開始位置 - offset 1 = 2番目が開始位置　

GROUP BYとHAVINGは集計したレコードに使う

GROUP BY:レコード集計したものを、グループごとに取り出す

例：scoreの合計値を「teamごとに」出したい

HAVING:レコード集計したものに、条件をつける

• 例： チームごとのスコアの合計を集計→そこから更に、スコア合計が10以上のものを取り出したい = 上記のselect team , sum(score) from users group by teamに、having sum(score) > 10.0; を足す。

参考記事

• PostgreSQLではじめるDB入門
• 【SQL】LIKE句