PostgreSQL

今回はpostgreSQL。自分の作業で直接いじることはなかなかないのですが、rails をherokuにアップしたときの本番環境はpostgreSQLなので少し勉強したものをメモります。

postgreSQLとは、オープンソースかつ高機能なデータベースシステム

データベースのパフォーマンス比較をされているサイトがありました。

※https://cypher256.hatenablog.com/entry/20121013/p1

自分のローカル環境にpostgreSQLが入ってなかったのでインストール。下記サイトを参考にしました。

https://www.ideaxidea.com/archives/2013/12/centos6_postgresql_yum.html

http://dotinstall.com/lessons/basic_local_development_v2/24813

テーブルの作成　例）

create table posts (

　　id serial primary key,

　　title varchar(255) not null, 

　　body text check(length(body) > 5),

　　is_draft boolean default TRUE,

　　created timestamp default ‘now’

);　

※ドットインストールで勉強した際に上記でエラーになった。

その際の対処法を教えてくださったサイトがありました。

http://tomute.hateblo.jp/entry/20131218/1387370615

データの型）

　数値：integer(int), real, serial

　文字：char(5), varchar(255), text

　真偽：boolean TRUE FALSE t f

　日付：date, time, timestamp

制約）

　not null

　unique

　check

　default

　primary key (not null, unique)

こっからは基本コマンドを一覧にしてみます。

psql -l	DBの一覧を見る
createdb blogapp	DBを作る（blogappは例です）
dropdb blogapp	データベースの削除
psql blogapp	DBへ接続
\l	接続後、DB内一覧を見る
\q	DB接続を終了させる
\dt	テーブルの一覧を見る
\d posts	テーブルにどういったカラムが入っているか確認する （postsはテーブルの例です）
alter table posts rename to myposts;	テーブルの名前を変更（mypostsもテーブルの例です）
drop table myposts;	テーブルを削除
\i commands.sql	外部ファイルからテーブルを作成 （commands.sqlは外部ファイルの例です）
insert into posts (title, body) values ('title1', 'body11111');	テーブルに値を入力  (titleとbodyはpostsテーブルの要素、'title1', 'body11111'は値の例です）
select * from posts;	全てのレコードを見る
select name, score from users;	一部（この例ではnameとscoreという要素）を見る
\x	拡張表示をon、offにしてくれる
select * from users where score > 4.0;	whereを使用して部分的に抽出
\dv	ビューの確認

こっからは文章が少し長くなるので表の形式を変えます。

like　と　%	select * from users where name like '%i';	文字列で部分的に抽出（最後がiで終わるもの）
like　と　_	select * from users where name like 'sa_aki';	文字空き（そこは何でも良い）
order by	select * from users order by score;	並べ替え
desc	select * from users order by score desc;	降順並べ替え
limit	select * from users limit 3;	取得する行数を制限
offset	select * from users limit 3 offset 3;	得する開始位置設定（0から始める）
count(*)	select count(*) from users;	テーブルの全カラムをカウント
distinct	select distinct team from users;	重複行を取り除く
	sum(score),  max(score), min(score),  avg(score)	それぞれ計算したものを出す
group by	select team, sum(score) from users group by team having sum(score) > 10.0	特定の列の値が等しい行ごとに表をグループ化する
length()	select name, length(name) from users;	文字列の文字数を取得する
concat()	select concat(name, ' (', team, ')') from users;	指定した文字列データを連結する
as	select concat(name, ' (', team, ')') as namelabel from users;	カラムに別名をつける
substring(文字列,開始桁,切り取り文字数)	select substring(team, 1, 1) from users;	文字列の指定桁数から指定文字数を切り取る
random()	select random();	ランダムにレコードを取得する
※	select * from users order by random() limit 1;	懸賞で1位を決めるときなど
update	update users set score = 5.8 where name = 'taguchi';	更新する
delete	delete from users where score < 3.0;	削除する
alter  /add	alter table users add fullname varchar(255);	既に存在するオブジェクトの特性を変更する :加える
alter  /drop	alter table users drop fullname;	既に存在するオブジェクトの特性を変更する :削除する
alter  /rename	alter table users rename name to myname;	既に存在するオブジェクトの特性を変更する :名前を変更
alter  /type	alter table users alter myname type varchar(32);	データの型の変更
create index	create index team_index on users(team);	indexの作成
drop index	drop index team_index;	indexの削除
テーブル名の省略	select users.name, posts.title from users, posts where users.id = posts.user_id users.id; select u.name, p.title from users u, posts p where u.id = p.user_id and u.id=1;
ビュー	create view katsuji_posts as select u.name, p.title from users u, posts p where u.id = p.user_id and u.id=1; select * from katsuji_posts;　呼び出し drop view katsuji_posts;　削除	view
トランザクション	begin; update users set score = score - 1.0 where name = 'katsujii'; update users set score = score + 1.0 where name = 'katsuji'; commit;　実行！ begin; update users set score = score - 1.0 where name = 'katsuji'; update users set score = score + 1.0 where name = 'katsuji; rollback;　やっぱりやめた！	begin / commit / rollback

以上です。