postgreSQL入門 ~備忘録~

とりあえず備忘録的に書いておこうと思います。

CentOS6にpostgreSQLを導入してとりあえず使えるようにするまでの記事も書いています。↓
CentOS6にpostgreSQLを導入して使えるようにするまで

postgresqlの基本操作

“”ダブルクォーテーションが使えない

コメントアウト

—  1行コメントアウト
/**/ 複数行コメントアウト

データ型

数値: integer(int) reak, serial
文字: char(5), varchar(255), text
真偽: boolean TRUE FALSE t f
日付: date, time, timesatmp(日付と時間)

制約

not null 空の値を許さない
unique  重複を許さない
check 内容をチェック check(length(body) > 5)
default defalt TRUEなど default ‘now’など
primary key (not null, unique) 1つだけしか設定できない

postgresqlの起動や停止

#サービスの起動 sudo service postgresql-11 start
#サービスの再起動 sudo service postgresql-11 restart
#サービスの停止 sudo service postgresql-11 stop
#サービスの状態を確認 sudo service postgresql-11 status

#初期化(データベースクラスタの作成)
service postgresql initdb

コマンド

psql -l データベースの一覧表示
createdb dbname データベースの作成
dropdb dbname データベースの削除
psql hoge データベースへの接続

postgresqlの、シェルのコマンド

\l データベース一覧の表示
\q データベースから抜ける

\dt テーブルの一覧を表示
\dv ビューの一覧を表示
\d tablename テーブルの内容を表示

\i hoge.sql 外部ファイルの使用

\x  拡張表示

SQL文

テーブルの作成と削除

create table hoge(カラム名 データ型, カラム名 データ型);  
drop table hoge;

テーブル名の変更

alter table hoge rename to hogehoge;

テーブルに値の挿入

insert into posts(title, body) values('title1', 'body1111');

where

select * from posts; --レコードをすべて表示
select * from users where score > 4; --条件付き -- < <= >= > = != like('%' '_') and or
select * from users where name = 'taguti';
select * from users where name like '%i';

order

select * from users order by score; --昇順
select * from users order by score desc; --降順
select * from users order by team, score desc; --並べ替えた上でさらに並べ替える

limit

select * from users limit 3;
select * from users limit 3 offset3; --4番目から3件を表示
select * from users order by score desc limit 3; --スコアが上位3人

count distinct

select count(*) from users; --行のカウント
select distinct team from users; --重複行をまとめる

group by グループ化

select team, sum(score) from users group by team; --グループ化
select team, sum(score) from users group by team having sum(score) > 10.0; --グループ化してさらに条件をしぼる

関数

sum() max() min() avg()
select avg(score) from users;
select name, length(name) from users; --名前の文字数
select concat(name, ' (', team, ')') from users; --文字列の連結

select concat(name, ' (', team, ')') as namelabel from users; --asは名前を変える
select substring(team, 1, 1) from users; --team の 1文字目から1文字目だけを取り出す

select * from users order by random() limit 1; --ランダムに1件だけ取り出す

値の更新

update users set score = 5.8 where name = 'taguti';
update users set score = score + 1 where team = 'red'; --赤チームのものだけ1増やす

delete from users where score < 3.0; --scoreが3より低い人を削除する

カラム(フィールドの追加)

alter table users add fullname varchar(255) --追加
alter table users drop fullname; --削除
alter table users rename name to myname; --カラム名の変更
alter table users alter myname type varchar(32); --カラムのタイプを変更
create index hoge_index on users(team); --indexの追加
drop index hoge_index; indexの削除

紐づけ whereで外部キーと主キーの紐づけを行う

select users.name, posts.title from users, posts where users.id  = posts.user_id;
select u.name, p.title from users u, posts p where u.id = p.user_id and u.id = 1; --省略

View

create view hoge as hogehoge
drop view hoge

トランザクション

begin; 開始
commit; コミット
rollbak; 取り消し

ユーザーの作成と削除

ユーザーの作成やデータベースの作成について、知識が足りないことがあるので、補足として書いておきます。postgreSQLのユーザーは最初のうちは概念がよくわからなくなるので頭に入れておくとよいかもしれないです。

postgreSQLでユーザを追加するにはcreateuserコマンドを使用して作成しますが、PostgreSQLにユーザ作成するのはスーパーユーザで行ないます。ここでのスーパーユーザーとはpostgreSQLのスーパーユーザーのことを指しています。デフォルトではpostgresユーザーがスーパーユーザーになっています。

つまりはユーザーを作成するには、su – postgres↓の状態になっている必要があります

$ su - postgres
パスワード:
-bash-4.1$ createuser hoge;

createuserにはいろいろなオプションがあります。あとあと調べることになりそうなので書いておきました↓

-a(–adduser) 他のユーザを作成することが許可。
※このオプションを付けるとスーパーユーザ。
-d(–createdb) データベースの作成を許可
-U [接続ユーザ名] (–username) PostgreSQLに接続するユーザ名を指定します
-P(–pwprompt) パスワードのプロンプトを表示します

これらのオプションを全て使用すると下記のようになりました。

-bash-4.1$ createuser -a -d -U postgres -P sasaki
新しいロールのためのパスワード:
もう一度入力してください:

postgreSQLのシェルからユーザーを作成する場合は以下のような書き方になります。

postgres=# create user hoge;
CREATE ROLE

データベースの作成

データベースの作成はcreatedbコマンドを使います。

データベースの作成に関して知っておいたほうがよさそうなのは、データベースの使用者を指定する -O オプションです。
その他もいろいろあるので下記に書いておきます。

-E [エンコーディング名] (–encording) 作成するデータベース内で使用する文字エンコード方式を指定します。
-O [オーナ名] (–owner) 作成するデータベースの所有者となるユーザを指定します。
-U [接続ユーザ名] (–username) PostgreSQLに接続するユーザ名を指定します

オプションを書く順番ですが、後に書いても先に書いてもよいですし、順不同です。オプション名のあと値をきちんと指定すれば、あとの書き順はなんでも大丈夫です。以下は所有者をhogeさんにした状態でhogehogeというデータベースを作成しています。

-bash-4.1$ createdb -O hoge hogehoge

データベースの作成も postgreSQLのシェルで行うことが可能です。

postgres=# create database hogehoge;
CREATE DATABASE

postgresqlは抑えるべき点が多いかんじがしますが重要なのは以下かと思います。

  • postgresユーザーでデータベースの作成削除を行う(su – postgresの状態)
  • データベース・ユーザーの作成はシェルの外または中から行うことができるが微妙に書き方が異なる