DB=> select 列名, 列名, ... from テーブル名;
DB=> select distinct * from テーブル名;
指定行数だけ取得
DB=> select * from テーブル名 limit 行数;
・指定列に対して昇順 DB=> select * from テーブル名 order by 列名; ・指定列に対して降順 DB=> select * from テーブル名 order by 列名 desc; ・列を複数指定 DB=> select * from テーブル名 order by 列名 desc, 列名;
DB=> select distinct student || ' 君' as "名前" from テーブル名; 名前 ------------------ 田中 君 鈴木 君 佐藤 君 高橋 君 山本 君
DB=> select student as "名前", subject as "教科", score as "点数",
case
when score >= 70 then '合格'
when score < 70 then '不合格'
else null
end as "結果"
from テーブル名 where student = '田中';
氏名 | 教科 | 点数 | 結果 |
---|---|---|---|
田中 | 国語 | 80 | 合格 |
田中 | 数学 | 65 | 不合格 |
田中 | 英語 | 90 | 合格 |
田中 | 理科 | 60 | 不合格 |
田中 | 社会 | 85 | 合格 |
DB=> select * from テーブル名 where 条件式;
where 句で使用できる演算子
=, >, <, >=, <=, !=(<>), and, or, not, in, like, between 最小値 and 最大値 など
・IS NULL(空である) DB=> select * from テーブル名 where 列名 is null; ・IS NOT NULL(空でない) DB=> select * from テーブル名 where 列名 is not null;
DB=> select coalesce(列名, '空') from テーブル名;
・IN(どれか) DB=> select * from テーブル名 where 列名 in (1, 2, 3); ・NOT IN(以外) DB=> select * from テーブル名 where 列名 not in (1, 2, 3); ・ALL(全て) DB=> select * from テーブル名 where 列名 != all (array[1,2,3]);
最終レコードのデータを取得、()の中が先に処理される
DB=> select * from テーブル名 where no = ( select max(no) from テーブル名 );
DB=> select * from テーブル名 where 列名 like '%文字列%';
'%文字列%' 含まれる
'%文字列' 終わりが一致
'文字列%' 始まりが一致
'文字列_文字列' _(アンダースコア)は、任意の一文字に一致
_(アンダースコア)を文字として扱いたい場合は、\ を付けてエスケープする
DB=> select * from テーブル名 where 列名 ~ 'パターン';
・行数をカウント DB=> select count(列名) from テーブル名; ・最大値 DB=> select max(列名) from テーブル名; ・最小値 DB=> select min(列名) from テーブル名; ・合計値 DB=> select sum(列名) from テーブル名; ・平均 DB=> select avg(列名) from テーブル名; ・合計値の平均 DB=> select avg( A.score_sum ) from ( select sum( score ) as score_sum from test_tbl group by student ) as A; ・標準偏差 DB=> select stddev(列名) from テーブル名;
整数同士の割り算は、必ず結果が整数になる
DB=> select 10 / 3; 3
整数::numeric で整数を numeric 型に変換
DB=> select 10 / 3::numeric; 3.3333333333333333 DB=> select round( 10 / 3::numeric, 1 ); 3.3
0 除算エラー回避
DB=> select 10 / 0::numeric; ERROR: division by zero DB=> select 10 / nullif( 0::numeric, 0 );
group by は、指定した列の値をグループ化
DB=> select 列名, count(*) from テーブル名 group by 列名;
複数指定する場合は、 group by 列名, 列名, ...;
having は where と同じ条件、where が行に適用されるのに対し、having はグループに適用される。
DB=> select 列名, count(*) from テーブル名 group by 列名 having グループに適用される条件;
・例1
DB=> select student as "名前", sum( score ) as "合計点"
from テーブル名 group by student;
名前 | 合計点 |
---|---|
田中 | 380 |
鈴木 | 385 |
佐藤 | 380 |
高橋 | 335 |
山本 | 345 |
DB=> select round( avg( A.score_sum ), 1 ) as "合計点の平均"
from ( select sum( score ) as score_sum from test_tbl group by student ) as A;
合計点の平均 |
---|
365.0 |
DB=> select student as "名前", sum( score ) as "合計点" from テーブル名
group by student having sum( score ) >= 350;
名前 | 合計点 |
---|---|
田中 | 380 |
鈴木 | 385 |
佐藤 | 380 |
DB=> select student as "名前", sum( score ) as "合計点", case when sum( score ) >= 350 then '合格' when sum( score ) < 350 then '不合格' else null end as "結果" from テーブル名 group by student;
名前 | 合計点 | 結果 |
---|---|---|
田中 | 380 | 合格 |
鈴木 | 385 | 合格 |
佐藤 | 380 | 合格 |
高橋 | 335 | 不合格 |
山本 | 345 | 不合格 |
作成 交換 プロシージャ 引数 DB=> CREATE OR REPLACE PROCEDURE hensachi_proc(name varchar(4)) AS $$ DECLARE kamoku_list varchar(4)[]; kamoku varchar(4); avg1 numeric(3,1); stddev1 numeric(3,1); score1 int; hensachi1 numeric(3,1); avg5 numeric(4,1); stddev5 numeric(3,1); score5 int; hensachi5 numeric(3,1); BEGIN kamoku_list := array['国語', '数学', '英語', '理科', '社会']; FOREACH kamoku IN ARRAY kamoku_list LOOP RAISE INFO '%', kamoku; select into avg1 round( avg( score ), 1 ) from test_tbl where subject = kamoku; select into stddev1 round( stddev( score ), 1 ) from test_tbl where subject = kamoku; select into score1 score from test_tbl where student = name and subject = kamoku; select into hensachi1 round( 50 + 10 * ( (score1 - avg1) / stddev1 ), 1 ); RAISE NOTICE '平均 %, 標準偏差 %, % 点数 % 偏差値 %', avg1, stddev1, name, score1, hensachi1; END LOOP; RAISE INFO '5科目'; select into avg5 round( avg(A.score_sum), 1 ) from (select sum( score ) as score_sum from test_tbl group by student) as A; select into stddev5 round( stddev(B.score_sum), 1 ) from (select sum( score ) as score_sum from test_tbl group by student) as B; select into score5 sum( score ) from test_tbl where student = name group by student; select into hensachi5 round( 50 + 10 * ( (score5 - avg5) / stddev5 ), 1 ); RAISE NOTICE '平均 %, 標準偏差 %, % 合計 % 偏差値 %', avg5, stddev5, name, score5, hensachi5; END; $$ LANGUAGE plpgsql; ※ RAISE INFO '%', 変数名; # 変数の中身を表示(% に変数の値がセットされる)・確認
DB=> \df
スキーマ | 名前 | 結果のデータ型 | 引数のデータ型 | タイプ |
---|---|---|---|---|
public | hensachi_proc | IN name character varying | プロシージャ |
DB=> call hensachi_proc ('田中'); INFO: 国語 NOTICE: 平均 74.0, 標準偏差 11.4, 田中 点数 80 偏差値 55.3 INFO: 数学 NOTICE: 平均 68.0, 標準偏差 7.6, 田中 点数 65 偏差値 46.1 INFO: 英語 NOTICE: 平均 83.0, 標準偏差 8.4, 田中 点数 90 偏差値 58.3 INFO: 理科 NOTICE: 平均 65.0, 標準偏差 12.7, 田中 点数 60 偏差値 46.1 INFO: 社会 NOTICE: 平均 75.0, 標準偏差 9.4, 田中 点数 85 偏差値 60.6 INFO: 5科目 NOTICE: 平均 365.0, 標準偏差 23.2, 田中 合計 380 偏差値 56.5 CALL・削除
DB=> drop procedure hensachi_proc;