とらりもん - PostgreSQL Diff

* 最初はpostgresというユーザーでしか使えない。
$ sudo su postgres
$ psql
=> \l   (テーブル表示)
=> \q   (終了)

* 文の終わりはセミコロン
=> select now();
;を忘れるとエラーも何も出ずにプロンプトが戻る。

* pgadmin3というGUIを使うのが便利！

!インストール (Ubuntu 14.04)
$ sudo apt-get install postgresql pgadmin3

!データベース作成
$ psql -l       (現在あるデータベースの一覧)
$ createdb blogapp    (新規作成)
$ psql blogapp    (新規作成したデータベースに入る）
$ pg_dump blogapp  (データベースのダンプ)
$ dropdb blogapp  (データベースの削除)

* テーブル操作
=> create table posts (title varchar(255), body text);    テーブル新規作成
=> \d       (テーブルもテーブルでないものも全部表示)
=> \dt            (テーブルのリストを表示)
=> \d users    (usersというテーブルの構造を表示)
=> alter table posts rename to myposts;    (テーブル名の変更)
=> drop table myposts;    テーブル削除
=> \i create.sql     (ファイルからSQLコマンドを読み込んで実行)

* SQL文
コメント
--
/*  */

* データ型
** integer(int), real, serial
** char(5)  固定長
** varchar(255) 可変長
** text  上限なし
** boolean TRUE FALSE t f
** date, time, timestamp (日時)

* ライン（レコード）の挿入
=> insert into posts (title, body) values ('title1', 'body11111');
=> select * from posts;

* レコード表示
=> select * from foo;
=> \x    拡張表示 (もういちど\xやると元に戻る)
=> select * from foo;
=> select * from foo where score > 5;
=> select * from foo where name = '@txt';    @は任意の文字列, _は任意の1文字
=> select * from foo order by score;
=> select * from foo order by score desc;
=> select * from foo order by score, team;

* レコード修正
=> update users set score = 5.8 where name = 'taguchi'

* レコード削除
=> delete from users where score < 3.0;

* フィールドの追加
=> alter table users add fullname varchar(255);

* フィールドの削除
=> alter table users drop fullname

* 複数のテーブルをつないで参照
=> select u.name, p.title from users u, posts p  where u.id = p.user_id and u.id=1;

!ビュー
SQL文につけるエイリアスみたいなもの。
=> create view aaa as
-> select u.name, p.title from users u, posts p  where u.id = p.user_id and u.id=1;
=> \dv
=> select * from aaa;
=> drop view aaa;

!トランザクション
=> begin;
...
=> commit;    
=> rollback;    (キャンセルする時)

!GUIのフロントエンド"pgadmin"を使う!

* PostgreSQLの管理者権限になってPostgreSQLを起動し, ユーザー(role)を新規作成し, そのパスワードを設定し, そのユーザーの所有するデータベースを新規作成。
$ sudo su postgres
$ psql
postgres=# create role nishida SUPERUSER LOGIN CREATEDB CREATEROLE;
postgres=# alter role nishida with password '****';
postgres=# create database testdatabase with owner nishida TEMPLATE template0 encoding 'UTF8';
postgres=#  (CTRL-D)

* うまく新規ユーザーと新規データベースができていてログイン可能かどうかをチェックする
$ psql testdatabase nishida -h localhost
Password for user nishida: 1234
testdatabase=#
testdatabase=#  (CTRL-D)

* その新規ユーザーでその新規データベースに, pgadmin3で接続する。
(postgres以外の一般ユーザーで)
$ sudo pgadmin3
画面左上のプラグの絵のアイコンをクリック
名前　test (何でも良い）
ホスト　localhost
Port　　5432
サービス
DBメンテナンス　testdatabase
ユーザ名　nishida
パスワード　****
パスワード保存　レ