postgreSQL その20 テーブル単位の権限、GRANT文とREVOKE文のつづき
postgreSQL その8 課題リストは2018/4/30時点でのオイラの所見
■文字の扱い
[P.206]
・SQLでは下記のキーワードの大文字と小文字は区別されない。
- SQLの構文に使用する「SELECT」や「FROM」などの単語
- テーブル名やインデックス名、テーブルの列名などのオブジェクト名
- 関数名
- データ型の名称
上記の単語/名前であえて大文字と小文字を区別させたい場合は常にダブルクォートで囲み、「"TeSt"」と記述する。
・文字や日付情報はシングルクォートで囲む
SQLなどで文字や日付を表現する場合は、シングルクォートで囲む。 たとえば、文字列の「ABC」は「'ABC'」と表現する。
シングルクォートで囲まない場合は、基本的に数値として認識される。
そのため、単純に「10」と記述した場合は、数値の10を意味し、「'10'」と記述した場合は文字列の10を意味する。
【検証1】
postgres=# create table "TaB1"(n int,m int);
CREATE TABLE
postgres=# create table TaB5(n int,m int);
CREATE TABLE
postgres=# select * from pg_tables where schemaname = 'public';
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
public | tab3 | postgres | | f | f | f | f
public | tab1 | postgres | | f | f | f | f
public | tab2 | postgres | | f | f | f | f
public | TaB1 | postgres | | f | f | f | f
public | tab5 | postgres | | f | f | f | f
■SELECT文
・SELECT文はデータベースから情報を取得するためのコマンド。
・列名、テーブル名を複数指定する場合はカンマで区切る
・同じ列を重複して指定できる。
・同じ名前のテーブルをFROMで重複して指定する場合は 「テーブル名 別名 」のように別名を用いる
・検索条件を複数指定する場合はANDやORで区切る
・別名の指定では、すべての列を表す「*」を用いることができる
・列名に関しては次のように別名を付けることができる
[書式]
select 列名 as 別名 from テーブル名 where 検索条件;
列名はテーブル名で修飾することもできる。これは、fromで指定した複数のテーブルで同じ列名を持つものがあった場合、どのテーブルノ列を指すのかを明示する際に必要となる。次のように「 テーブル名.列名 」という書式になる。
[書式]
select テーブル名.列名 from テーブル名 where 検索条件;
[例]
testdb=# select * from city;
name | population | prefecture | comment
------+------------+------------+--------------------------
前橋 | 30 | 群馬 | 県庁所在地
千葉 | 100 | 千葉 | 県庁所在地、政令指定都市
郡山 | 40 | 福島 | 地方重要都市
横浜 | 350 | 神奈川 | 県庁所在地、政令指定都市
福生 | 12 | 東京 | 多摩地区
那覇 | 30 | 沖縄 | 県庁所在地
旭川 | 30 | 北海道 | かなり寒い
[例]
testdb=# select name as 都市,prefecture as 都道府県 from city;
都市 | 都道府県
------+----------
前橋 | 群馬
千葉 | 千葉
郡山 | 福島
横浜 | 神奈川
福生 | 東京
那覇 | 沖縄
旭川 | 北海道
(7 rows)
[例]
testdb=# select * from city where population <=100 and comment = '県庁所在地';
name | population | prefecture | comment
------+------------+------------+------------
前橋 | 30 | 群馬 | 県庁所在地
那覇 | 30 | 沖縄 | 県庁所在地
(2 rows)
[例]
testdb=# select city.name as 都市,prefecture as 都道府県 from city;
都市 | 都道府県
------+----------
前橋 | 群馬
千葉 | 千葉
郡山 | 福島
横浜 | 神奈川
福生 | 東京
那覇 | 沖縄
旭川 | 北海道
(7 rows)
・その他に、selectは関数や式の呼び出しにも使われる
[書式]
select 関数名;
select 式;
[例]
testdb=# select now();
now
-------------------------------
2018-05-18 23:51:16.479698+09
(1 row)
[例]
testdb=# select 1+1;
?column?
----------
2
(1 row)
■ORDER BY
ORDER BY は、SELECT文で検索された結果をsortする。
昇順はASC、降順はDESCで、省略時は昇順になる。
ORDER BY でソートを行わない場合、SELECTで得られる結果の順序は不定となる。
[書式]
select 列名 from テーブル名 where 検索条件 order by ソート対象 [ ASC | DESC ];
「ソート対象」には、SELECTで指定する「列名」や「列数」を指定する。「列数」は、SELECTで指定した列の位置を表す。 例えば、「select A,B from tab1」とし、「A」でソートする場合は「order by 1」、「B」でソートする場合は、「order by 2」と指定する。
[例]
testdb=# select name,population from city where comment = '県庁所在地' or comment = '県庁所在地、政令指定都市' order by population;
name | population
------+------------
前橋 | 30
那覇 | 30
千葉 | 100
横浜 | 350
(4 rows)
testdb=# select name,population from city where comment = '県庁所在地' or comment = '県庁所在地、政令指定都市' order by population desc;
name | population
------+------------
横浜 | 350
千葉 | 100
前橋 | 30
那覇 | 30
(4 rows)
testdb=# select name,population from city where comment = '県庁所在地' or comment = '県庁所在地、政令指定都市' order by 2;
name | population
------+------------
前橋 | 30
那覇 | 30
千葉 | 100
横浜 | 350
(4 rows)
・ORDER BY には、複数の列を指定できる。左から順にソートの優先順位が高くなる。
下記の例では、最初に「population」で降順にソートして「name」で昇順にソートしている。
[例]
testdb=# select name,population from city where comment = '県庁所在地' or comment = '県庁所在地、政令指定都市' order by 2 desc,1 asc;
name | population
------+------------
横浜 | 350
千葉 | 100
前橋 | 30
那覇 | 30
(4 rows)
■LIMITとOFFSET
・LIMIITは取り出す件数の上限値を指定する。
・OFFSETは先頭から何件をスキップするかを指定する。
[書式]
select 列名 from テーブル名 where 検索条件 order by 列名 [ ASC | DESC ] limit 件数 offset 位置;
[例]
testdb=# select name,population from city order by population desc;
name | population
------+------------
横浜 | 350
千葉 | 100
郡山 | 40
前橋 | 30
那覇 | 30
旭川 | 30
福生 | 12
(7 rows)
testdb=# select name,population from city order by population desc limit 2;
name | population
------+------------
横浜 | 350
千葉 | 100
(2 rows)
testdb=# select name,population from city order by population desc limit 1 offset 1;
name | population
------+------------
千葉 | 100
(1 row)
■DISTINCT
DISTINCT は、重複を除去する。 重複除去の対象とする列名は複数指定できる。
[書式]
select distinct [ on (重複除去対象の列名) ] 列名 from テーブル名 where 検索条件;
「on (重複除去対象の列名)」を省略した場合は、「列名」に列挙したすべての列が重複除去の対象となる
[例]
testdb=# select name,population,comment from city;
name | population | comment
------+------------+--------------------------
前橋 | 30 | 県庁所在地
千葉 | 100 | 県庁所在地、政令指定都市
郡山 | 40 | 地方重要都市
横浜 | 350 | 県庁所在地、政令指定都市
福生 | 12 | 多摩地区
那覇 | 30 | 県庁所在地
旭川 | 30 | かなり寒い
(7 rows)
testdb=# select distinct on (population) name,population,comment from city;
name | population | comment
------+------------+--------------------------
福生 | 12 | 多摩地区
旭川 | 30 | かなり寒い
郡山 | 40 | 地方重要都市
千葉 | 100 | 県庁所在地、政令指定都市
横浜 | 350 | 県庁所在地、政令指定都市
(5 rows)
testdb=# select distinct on (comment) name,population,comment from city;
name | population | comment
------+------------+--------------------------
旭川 | 30 | かなり寒い
前橋 | 30 | 県庁所在地
横浜 | 350 | 県庁所在地、政令指定都市
福生 | 12 | 多摩地区
郡山 | 40 | 地方重要都市
(5 rows)
testdb=# select distinct population from city;
population
------------
350
40
30
100
12
(5 rows)
testdb=# select distinct comment from city;
comment
--------------------------
かなり寒い
県庁所在地、政令指定都市
県庁所在地
地方重要都市
多摩地区
(5 rows)
testdb=# select distinct population,comment from city;
population | comment
------------+--------------------------
350 | 県庁所在地、政令指定都市
30 | かなり寒い
12 | 多摩地区
100 | 県庁所在地、政令指定都市
40 | 地方重要都市
30 | 県庁所在地
(6 rows)
→「population」と「comment」の両方とも重複した「前橋」と「那覇」が重複除去の対象となった。
testdb=# select distinct on (population,comment) name,population,comment from city;
name | population | comment
------+------------+--------------------------
福生 | 12 | 多摩地区
旭川 | 30 | かなり寒い
前橋 | 30 | 県庁所在地
郡山 | 40 | 地方重要都市
千葉 | 100 | 県庁所在地、政令指定都市
横浜 | 350 | 県庁所在地、政令指定都市
(6 rows)
・「on (除去対象の列名)」 を指定した場合は、「除去対象の列名」でソートされる。
列名を複数指定した場合は、ORDER BY と同様左から優先的にソートされる。
DISTINCT ON と ORDER BY を併用した場合は、ORDER BY が優先される。
■GROUP BY と HAVING
・GROUP BY
GROUP BY は特定の列をグループ化する。、グループ化した結果に集計を行う場合に用いられる。
GROUP BYを用いた場合、SELECTで指定できる「列名」は、集約関数を用いた場合を除き、基本的にはGROUP BY で指定したものだけになる。
※ただし、PostgreSQL9.1以降ではユニークキーをしてした場合のみ、SELECTの列名は自由に指定できる。
[書式]
select 列名 [集約関数(列名)] from テーブル名 where 検索条件 group by 対象列名;
・その前にもう一個違うテーブルをつくるんご
testdb=# create table city2(id int,name varchar(30),population int,prefecture varchar(30),県庁所在地 boolean,政令指定都市 boolean,comment varchar(30));
CREATE TABLE
testdb=# \d city2
Table "testschema.city2"
Column | Type | Collation | Nullable | Default
--------------+-----------------------+-----------+----------+---------
id | integer | | |
name | character varying(30) | | |
population | integer | | |
prefecture | character varying(30) | | |
県庁所在地 | boolean | | |
政令指定都市 | boolean | | |
comment | character varying(30) | | |
せ7> su postgres -c "mkdir /tmp/20180518"
せ7> ls -ld /tmp/20180518
drwxr-xr-x. 2 postgres postgres 6 5月 19 01:35 /tmp/20180518
testdb=# copy city to '/tmp/20180518/city.csv' with csv;
COPY 7
・city.csvを下記のように加工してcity2.csvとして保存
せ7> cat /tmp/20180518/city2.csv
1,前橋,30,群馬,t,f,ぐんまー
2,千葉,100,千葉,t,t,ちばらぎ
3,郡山,40,福島,f,f,
4,横浜,350,神奈川,t,t,オサレ
5,福生,12,東京,f,f,米軍基地
6,那覇,30,沖縄,t,f,しまんちゅ
7,旭川,30,北海道,f,f,かなり寒い
8,習志野,20,千葉,f,f,空挺部隊
9,藤沢,40,神奈川,f,f,湘南ナンバーぶいぶい
10,相模原,40,神奈川,f,f,
11,町田,40,東京,f,f,神奈川県じゃないんご
testdb=# copy city2 from '/tmp/20180518/city2.csv' with csv;
COPY 11
testdb=# select * from city2;
id | name | population | prefecture | 県庁所在地 | 政令指定都市 | comment
----+--------+------------+------------+------------+--------------+----------------------
1 | 前橋 | 30 | 群馬 | t | f | ぐんまー
2 | 千葉 | 100 | 千葉 | t | t | ちばらぎ
3 | 郡山 | 40 | 福島 | f | f |
4 | 横浜 | 350 | 神奈川 | t | t | オサレ
5 | 福生 | 12 | 東京 | f | f | 米軍基地
6 | 那覇 | 30 | 沖縄 | t | f | しまんちゅ
7 | 旭川 | 30 | 北海道 | f | f | かなり寒い
8 | 習志野 | 20 | 千葉 | f | f | 空挺部隊
9 | 藤沢 | 40 | 神奈川 | f | f | 湘南ナンバーぶいぶい
10 | 相模原 | 40 | 神奈川 | f | f |
11 | 町田 | 40 | 東京 | f | f | 神奈川県じゃないんご
(11 rows)
[例]
testdb=# select prefecture,sum(population) from city2 group by prefecture;
prefecture | sum
------------+-----
群馬 | 30
東京 | 52
神奈川 | 430
北海道 | 30
千葉 | 120
福島 | 40
沖縄 | 30
(7 rows)
testdb=# select prefecture,sum(population) from city2 where 政令指定都市 = 'f' group by prefecture order by sum;
prefecture | sum
------------+-----
千葉 | 20
北海道 | 30
群馬 | 30
沖縄 | 30
福島 | 40
東京 | 52
神奈川 | 80
(7 rows)
下記のようにorder byで指定する列名が集約関数の場合に()を付加しても結果は同じ。
testdb=# select prefecture,sum(population) from city2 where 政令指定都市 = 'f' group by prefecture order by sum(population);
prefecture | sum
------------+-----
千葉 | 20
北海道 | 30
群馬 | 30
沖縄 | 30
福島 | 40
東京 | 52
神奈川 | 80
(7 rows)
testdb=#
testdb=# select prefecture,sum(population) from city2 where 政令指定都市 = 'f' group by prefecture order by sum(popy);
ERROR: column "popy" does not exist
LINE 1: ...e 政令指定都市 = 'f' group by prefecture order by sum(popy);
・HAVING
HAVINGはGROUP BY と組み合わせて利用される。
HAVINGはGROUP BY でグループ化された結果に対しての条件指定を行う場合に使う。
同じく条件指定を行うWHEREとは評価のタイミングが異なる。
WHEREはGROUP BYの前に評価され行が選別され、その選別された結果に対して
GROUP BYの指定に従いグループ化され、その後に、HAVINGが評価され、グループ化された結果が選別される。
HAVINGでは次のような条件を指定できる
- GROUP BYで指定した列に対する条件指定（例：GROUP BY A,B HAVING A = 'x' and B > 10）
- 列名に対する集約関数を用いた条件（例：GROUP BY A HAVING sum(B) > 10）
[書式]
select 列名 [集約関数(列名)] from テーブル名 where 検索条件 group by 対象列名 having 条件;
[例]
testdb=# select prefecture,sum(population) from city2 where 政令指定都市 = 'f' group by prefecture having sum(population) <= 30;
prefecture | sum
------------+-----
群馬 | 30
北海道 | 30
千葉 | 20
沖縄 | 30
(4 rows)
ちなみに、下記のようにhavingで指定する集約関数から()を省くとエラー
testdb=# select prefecture,sum(population) from city2 where 政令指定都市 = 'f' group by prefecture having sum <= 30;
ERROR: column "sum" does not exist
LINE 1: ...ere 政令指定都市 = 'f' group by prefecture having sum <= 30;
testdb=# select prefecture,sum(population) from city2 where 政令指定都市 = 'f' group by prefecture having prefecture = '群馬' or prefecture = '千葉';
prefecture | sum
------------+-----
群馬 | 30
千葉 | 20
(2 rows)
下記のような「地方」カラムをもったcity3テーブルを作った
testdb=# select * from city3;
id | name | population | prefecture | 地方 | 県庁所在地 | 政令指定都市 | comment
----+--------+------------+------------+--------+------------+--------------+----------------------
1 | 前橋 | 30 | 群馬 | 関東 | t | f | ぐんまー
2 | 千葉 | 100 | 千葉 | 関東 | t | t | ちばらぎ
3 | 郡山 | 40 | 福島 | 東北 | f | f |
4 | 横浜 | 350 | 神奈川 | 関東 | t | t | オサレ
5 | 福生 | 12 | 東京 | 関東 | f | f | 米軍基地
6 | 那覇 | 30 | 沖縄 | 九州 | t | f | しまんちゅ
7 | 旭川 | 30 | 北海道 | 北海道 | f | f | かなり寒い
8 | 習志野 | 20 | 千葉 | 関東 | f | f | 空挺部隊
9 | 藤沢 | 40 | 神奈川 | 関東 | f | f | 湘南ナンバーぶいぶい
10 | 相模原 | 40 | 神奈川 | 関東 | f | f |
11 | 町田 | 40 | 東京 | 関東 | f | f | 神奈川県じゃないんご
12 | 和歌山 | 25 | 和歌山 | 近畿 | t | f |
(12 rows)
testdb=# select 地方,sum(population) from city3 group by 地方;
地方 | sum
--------+-----
関東 | 632
北海道 | 30
九州 | 30
東北 | 40
近畿 | 25
(5 rows)
testdb=# select 地方,sum(population) from city3 group by prefecture;
ERROR: column "city3.地方" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select 地方,sum(population) from city3 group by prefecture;
testdb=# select 地方,prefecture,sum(population) from city3 group by 地方,prefecture;
地方 | prefecture | sum
--------+------------+-----
九州 | 沖縄 | 30
関東 | 神奈川 | 430
関東 | 群馬 | 30
東北 | 福島 | 40
北海道 | 北海道 | 30
関東 | 千葉 | 120
近畿 | 和歌山 | 25
関東 | 東京 | 52
(8 rows)
testdb=# select 地方,prefecture,sum(population) from city3 group by prefecture,地方;
地方 | prefecture | sum
--------+------------+-----
東北 | 福島 | 40
関東 | 千葉 | 120
近畿 | 和歌山 | 25
北海道 | 北海道 | 30
関東 | 神奈川 | 430
九州 | 沖縄 | 30
関東 | 群馬 | 30
関東 | 東京 | 52
(8 rows)
testdb=# select 地方,prefecture,sum(population) from city3 group by prefecture;
ERROR: column "city3.地方" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select 地方,prefecture,sum(population) from city3 group by p...
【分かったこと】
・selectで指定する列名とGROUP BYの列名は複数あってもよい。
・selectで指定する列名とGROUP BYの列名は一致してないとエラーになる。
・selectで指定する列名とGROUP BYの列名が複数あるとき、順序は揃ってなくてもよい。
testdb=# select 地方,prefecture,sum(population) from city3 group by prefecture,地方 order by 地方,sum;
地方 | prefecture | sum
--------+------------+-----
関東 | 群馬 | 30
関東 | 東京 | 52
関東 | 千葉 | 120
関東 | 神奈川 | 430
近畿 | 和歌山 | 25
九州 | 沖縄 | 30
東北 | 福島 | 40
北海道 | 北海道 | 30
(8 rows)
testdb=# select 地方,prefecture,sum(population) from city3 group by prefecture,地方 having sum(population) <= 30;
地方 | prefecture | sum
--------+------------+-----
近畿 | 和歌山 | 25
北海道 | 北海道 | 30
九州 | 沖縄 | 30
関東 | 群馬 | 30
(4 rows)
testdb=# select 地方,prefecture,max(population) from city3 group by prefecture,地方 order by 地方;
地方 | prefecture | max
--------+------------+-----
関東 | 千葉 | 100
関東 | 神奈川 | 350
関東 | 群馬 | 30
関東 | 東京 | 40
近畿 | 和歌山 | 25
九州 | 沖縄 | 30
東北 | 福島 | 40
北海道 | 北海道 | 30
(8 rows)