データベースの基本操作 3th (June)
sample というユーザ名で sample というデータベースを扱う。
$ psql -l
List of databases
Name | Owner | Encoding
------------+------------+------------
postgres | postgres | EUC_JP
sample | postgres | EUC_JP
template0 | postgres | EUC_JP
template1 | postgres | EUC_JP (テスト用なので使わない)
(3 rows)
sampleデータベースに接続
$ psql -U <データベースユーザ名> -p <ポート番号> -h <ホスト名> <データベース名>
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
sample=>
| オプションの値 | デフォルト |
|---|---|
| データベース・ユーザ名 | Linuxにログインしているユーザ名 |
| ポート番号 | 5432 |
| ホスト名 | ローカルホスト |
| データベース名 | データベース・ユーザ名と同じ名前 |
| コマンド | 内容 |
|---|---|
| \d | オブジェクト一覧 |
| \dt | テーブル一覧 |
| \di | インデックス一覧 |
| \du | ユーザ一覧 |
| \df | 関数一覧 |
| \dS | システムカタログ一覧 |
| \d <テーブル名> | <テーブル名>の定義情報 |
| \h |
SQLコマンドのヘルプ |
| データ型 | 内容 |
|---|---|
| SMALLINT | ２バイトの整数 |
| INTEGER | ４バイトの整数 |
| BIGINT | ８バイトの整数 |
| SERIAL | 数値の連番 |
| FLOAT | ４バイトの浮動小数点 |
| CHAR(n) | ｎ文字の固定長文字列 |
| VARCHAR(n) | 最大ｎ文字の可変長文字列 |
| TEXT | 制限なしの可変長文字列 |
| DATE | 年月日 |
| TIME | 時分秒 |
| TIMESTAMP | 年月日時分秒 |
| BOOL | 真偽 |
テーブルの作成
psqlではセミコロン";"が入力されるまでが１つのコマンドとみなされるので、途中で改行を入れてもOK。見やすい...
sample=> CREATE TABLE addressbook ( sample(> id SERIAL, sample(> name TEXT, sample(> kana TEXT, sample(> zip VARCHAR(8), sample(> address TEXT, sample(> tel VARCHAR(12), sample(> birth DATE sample(> ); NOTICE: CREATE TABLE will create implicit sequence "addressbook_id_seq" for serial column "addressbook.id" CREATE TABLE
ちょっとわかりにくいSERIAL型。これは連番を扱う４バイトのデータ型で、内部的にシーケンスに連番を振ってくれる。ありがたい。他に８バイトのSERIAL型もある。
確認してみよう
sample=> \d
List of relations
Schema | NAME | Type | Owner
--------+----------------------+------------+--------
public | addressbook | table | sample
public | addressbook_id_seq | sequence | sample
(2 rows)
データを挿入してみよう
sample=> INSERT INTO addressbook VALUES (
sample(> nextval('addressbook_id_seq'),
sample(> '鈴木たろう',
sample(> 'すずきたろう',
sample(> '111-1111',
sample(> '東京都千代田区',
sample(> '03-3333-3333',
sample(> '1990/1/1',
sample(> );
sample=> INSERT INTO addressbook
sample(> (name,kana,zip,address,tel,birth,id)
sample(> VALUES (
sample(> '田中次郎',
sample(> 'たなかじろう',
sample(> '222-2222',
sample(> '大阪市中央区',
sample(> '06-6666-6666',
sample(> '1995/2/2',
sample(> nextval('addressbook_id_seq')
sample(> );
sample=> INSERT INTO addressbook
sample(> (name,kana,zip,address,tel.birth)
sample(> VALUES (
sample(> '佐藤花子',
sample(> 'さとうはなこ',
sample(> '333-3333',
sample(> '京都市下京区',
sample(> '075-123-3333',
sample(> '1980/3/3',
sample(> );
３つのパターンでデータを挿入。
１つめは、すべての列に対して VALUES で挿入したいデータを指定。
２つめ３つめは、列名を指定して VALUES でデータを指定。指定がない項目は NULL となる。
ちょっと指定の方法が違うところが、nextval。SERIALのデータ型を利用している場合には自動的に連番を付加していかないといけない。数値を直接指定するのではなく "nextval" という次の値を取り出す関数を使っている。
データを検索してみよう
sample=> select * from addressbook;
[省略]
sample=> select name,birth from addressbook;
[省略]
sample=> select count(*) from addressbook;
count
-------
3
(1 row)１つめのselect文は、addressbook の内容をすべて表示。
２つめのselect文は、addressbook から name, birth を表示。
３つめのselect文は、addressbook の件数を表示。count は集約関数。集約関数をまとめるとこんな感じ。
| 関数 | 内容 |
|---|---|
| count() | 総数 |
| max() | 最大値 |
| min() | 最小値 |
| sum() | 和 |
| avg() | 平均 |
他に並べ替えて表示することもできる。select文の最後に "order by"句をつける。
文法としては
select … from … order by order_by_expression [ASC|DESC|USING operator][,…]
order_by_expression 並びかえをする式、または行
operator 並びかえに使う比較演算子(<,>)
ASC 昇順(default)
DESC 降順
使ってみよう
sample=> select * from addressbook order by kana; sample=> select * from addressbook order by zip desc; sample=> select * from addressbook order by kana using >; ← desc のかわりに using >
並びかえもいいが、表示するものを限定できる機能もおいしい。
まずは文法。
select … from … [LIMIT {limit_counter | ALL}] [OFFSET offset_start]
LIMIT 出力する最大数を設定
OFFSET 指定された個数の結果を読み飛ばすlimit_counter 出力する最大数
offset_start 読み飛ばす数
先頭の２行を読み飛ばし続く３行を表示する
sample=> select * from addressbook order by id offset 2 limit 3;
データの更新
sample=> update addrssbook sample-> set zip='444-4444', address='神奈川県横浜市' sample-> where id = 1; UPDATE 1
ここで where を忘れるとすべてのデータに対してsetを実行してしまうので要注意だ。
データの削除
sample=> delete addrssbook sample-> where id = 2; DELETE 1
ここもそうだが、whereを忘れるとすべてのデータを削除してしまうので、要要要注意だ。
