はじめに
こちらの記事の続きで書いてます。
DBは自分で触って覚えてくのが一番だと聞いたので、
おうちクラウドのVMにDBコンテナを立てて、触りながら学習した記録です。
DB操作
DB接続
DBサーバーから接続する場合は、-hオプションは省略可能
# psql -U {DBユーザー名} -d {DB名} -h {DBサーバーのホスト名}
DB作成
# CREATE DATABASE {DB名};
TABLE操作
# CREATE TABLE Shohin(shohin_id CHAR(4) NOT NULL,
shohin_mei VARCHAR(100) NOT NULL,
shohin_bunrui VARCHAR(32) NOT NULL,
hanbai_tanka INTEGER,
shiire_tanka INTEGER,
torokubi DATE,
PRIMARY KEY (shohin_id));
CREATE TABLE
データ型の種類
| 型 | 型種別 | 備考 |
|---|---|---|
| INTEGER | 数値型 | 整数のみ |
| CAHR | 文字列型 | 固定長の文字列型。CHAR(10)のように、カッコ内に文字列の最大長を入れる。CHAR(10)に'abc'という文字を入れた場合、残りの7枠は半角スペースで埋められる。 |
| VARCHAR | 文字列型 | 可変長文字列型。VARCHAR(10)に'abc'という文字を入れた場合でも、半角スペースで埋められない。 |
| DATE | 日付型 | 年月日を入れる |
制約
| 制約 | 備考 |
|---|---|
| NOT NULL | 空データはNG |
| PRIMARY KEY | 主キーに設定。PRIMARY KEY({カラム名})で、指定するカラム名を主キーに設定する。 |
テーブル削除
# DROP TABLE {テーブル名};
列を追加する
# ALTER TABLE {テーブル名} ADD COLUMN {列の定義};
列を削除する
# ALTER TABLE {テーブル名} DROP COLUMN {列名};
テーブルへのデータ入力
トランザクションを開始する。
COMMITもしくはROLLBACKが行われるまで、入力を制限する。
# BEGIN TRANSACTION;
BEGIN
データ(レコード)を登録する。
# INSERT INTO Shohin VALUES ('0001', 'Tシャツ', '衣服', 1000, 500, '2009-09-20');
INSERT 0 1
登録を確定してトランザクションを解除する。
# COMMIT;
COMMIT
登録を確定しない場合で、トランザクションを解除する場合。
# ROLLBACK
ROLLBACK
テーブル検索
SELECT文
テーブル内のデータを検索するときは、SELECT文を使います。
SELECT句やFROM句は、大文字で記載してますが、小文字でも動きます。
SELECT * FROM {テーブル名};で検索します。
# SELECT * FROM shohin;
shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi
-----------+----------------+---------------+--------------+--------------+------------
0001 | Tシャツ | 衣服 | 1000 | 500 | 2009-09-20
0002 | 穴あけパンチ | 事務用品 | 500 | 320 | 2009-09-11
0003 | カッターシャツ | 衣服 | 4000 | 2800 |
*の箇所にカラム名を入れることで、必要なカラムだけを取り出すことができます。
カラム名は、カンマ区切りで複数指定できます。
カラムの順番は自由に変えられます。
# SELECT shohin_mei, shohin_bunrui FROM shohin;
shohin_mei | shohin_bunrui
----------------+---------------
Tシャツ | 衣服
穴あけパンチ | 事務用品
カッターシャツ | 衣服
# SELECT shohin_bunrui, shohin_mei FROM shohin;
shohin_bunrui | shohin_mei
---------------+----------------
衣服 | Tシャツ
事務用品 | 穴あけパンチ
カラムに別名をつけることもできる。
日本語を使う場合は、ダブルクォーテーションで囲う。
# SELECT shohin_mei AS name, shohin_bunrui AS "分類" FROM shohin;
name | 分類
----------------+--------------
Tシャツ | 衣服
穴あけパンチ | 事務用品
カッターシャツ | 衣服
DISTINCT句
重複削除した検索をすることができます。
複数カラムを選択することもできます。
# SELECT DISTINCT shohin_bunrui FROM shohin;
shohin_bunrui
---------------
キッチン用品
衣服
事務用品
(3 rows)
WHERE句
条件式を書くことができます。
SELECT ~ FROM ~ WHERE {条件式};
以下の例は、商品分類カラムが「衣服」のデータのみに絞って検索しています。
# SELECT * FROM shohin WHERE shohin_bunrui='衣服';
shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi
-----------+----------------+---------------+--------------+--------------+------------
0001 | Tシャツ | 衣服 | 1000 | 500 | 2009-09-20
0003 | カッターシャツ | 衣服 | 4000 | 2800 |
(2 rows)
比較演算子
| 比較演算子 | 内容 |
|---|---|
| = | 等しい |
| <> | 等しくない |
| >= | 以上 |
| <= | 以下 |
| > | より大きい |
| < | より小さい |
論理演算子
WHERE句の条件を否定したり複数合致させたい場合などで使います。
# SELECT * FROM shohin WHERE shohin_bunrui='衣服' AND hanbai_tanka > 1000;
shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi
-----------+----------------+---------------+--------------+--------------+----------
0003 | カッターシャツ | 衣服 | 4000 | 2800 |
(1 row)
| 論理演算子 | 内容 |
|---|---|
| AND | 複数条件に合致する場合 |
| OR | いずれかの条件が合致する場合 |
| NOT | 条件に合致しない場合 |
参考書籍
こちらの書籍で勉強させていただきました。
PostgreSQLメインで書かれてる＆初学者向けの私にちょうどいい書籍でした。
データの集約
COUNT関数でレコード数を数える。
カッコの中でカラムを指定すると、指定したカラムのデータがNULLのものは除外される。
# SELECT COUNT(*) FROM shohin;
count
-------
8
(1 row)
# SELECT COUNT(shiire_tanka) FROM shohin;
count
-------
6
(1 row)
SUM関数で合計値を出す。
# SELECT SUM(shiire_tanka) FROM shohin;
sum
-------
12210
(1 row)
その他にも数値計算する関数があります。
| 関数 | 内容 |
|---|---|
| COUNT | レコード数 |
| SUM | 合計 |
| AVG | 平均 |
| MAX | 最大 |
| MIN | 最小 |
分類ごとにカウントする
GROUP BY句を使うことで分類ごとに集約できます。
# SELECT shohin_bunrui, COUNT(*) FROM shohin GROUP BY shohin_bunrui;
shohin_bunrui | count
---------------+-------
キッチン用品 | 4
衣服 | 2
事務用品 | 2
(3 rows)
ソート(並べ替え)する
ORDER BY句で並び替えをすることができます。
# SELECT * FROM shohin ORDER BY hanbai_tanka DESC;
shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi
-----------+----------------+---------------+--------------+--------------+------------
0006 | 圧力鍋 | キッチン用品 | 6800 | 5000 | 2009-09-20
0003 | カッターシャツ | 衣服 | 4000 | 2800 |
0004 | 包丁 | キッチン用品 | 3000 | 2800 | 2009-01-15
| ソートキー | 内容 |
|---|---|
| ASC | 昇順で並べる。ソートキーを省略した場合は昇順となる。 |
| DESC | 降順で並べる。 |