[root@ik1-321-20522 date]# psql -U postgres
psql (9.2.23)
Type "help" for help.
postgres=#
postgres=# \q
[root@ik1-321-20522 date]#
まずファイルに中身を入れてまたpostgresに入る。
中身を入れる時
begin transaction;
insert into uselist values(1, 'ken', 'tokyo', 20);
insert into uselist values(2, 'bob', 'kyoto', 30);
insert into uselist values(3, 'lisa', 'paris');
commit;
TABLEを作くる。
create table userlist(
id int,
name varchar(20),
address varchar(30),
age int
);
CREATE TABLE
postgres=# \i 中身が入ったファイル名
TABLEを削除。
postgres=# drop table userlist ;
DROP TABLE
中身を変更する。
postgres=# update userlist
postgres-# set 何か='何'
postgres-# where 何か='何';
postgres=# update userlist
postgres-# set address='fukuoka'
postgres-# where name='toshi';
東京と京都でイベントを開催することになり、
チラシの配布が必要になった。
ここに済んでいる20代の人に対して、
年齢の20倍のチラシをノルマにしたい。
name, address, age, printのカラムを表示し、
枚数の多い順番で表示しなさい。
ただしprintがチラシの枚数である。
postgres=# select name, address, age, age*20 as "frint" from userlist where address in ('tokyo','kyoto') and age between 20 and 29 order by frint desc;
name | address | age | frint
---------+---------+-----+-------
lulu | tokyo | 26 | 520
mishima | tokyo | 25 | 500
sati | tokyo | 25 | 500
bob | tokyo | 23 | 460
hana | tokyo | 22 | 440
nao | kyoto | 22 | 440
saori | kyoto | 21 | 420
(7 rows)
奨学金の給付制度により、東京、京都、fuhuokaの内、
人数が5人以上の場合、1人7000円の給付をすることになった。
該当する都市のみテーブルに表示しなさい。
また結果を/home/lisa/data/result4.csvに保存しなさい。
カラムはaddress, count, moneyだけで良い。ただし、moneyの大きい順番で表示すること。
postgres=# select address, count(*), count(*)*7000 as "money" from userlist where address in ('tokyo','kyoto','fuhuoka') group by address having count(*) >=5 order by money desc;
address | count | money
---------+-------+-------
tokyo | 14 | 98000
fuhuoka | 6 | 42000
(2 rows)
東京、京都、名古屋に住んでいる10代の人が
londonに留学することになった。
それに伴い、住所を変更したい。
住所を変更し、name, address, ageを表示しなさい。
postgres=# select name, address, age from userlist where address in ('tokyo','kyoto','nagoya') and age between 10 and 20 ;
name | address | age
-------+---------+-----
sato | tokyo | 18
kaoru | nagoya | 15
nori | tokyo | 19
(3 rows)
postgres=# update userlist set addrees='london' where address in ('tokyo','kyoto','nagoya') and age between 10 and 20 ;
postgres=# select * from userlist where address = 'london';
id | name | address | age
----+-------+---------+-----
1 | sato | london | 18
5 | kaoru | london | 15
18 | nori | london | 19
(3 rows)
\COPY ( SQL ) TO ' directory ' WITH CSV DELIMITER ',' ;
\COPY ( select address, max(age), min(age), avg(age), sum(age), count(*) from userlist group by address ) TO '/home/sato/data/result1.csv' WITH CSV DELIMITER ',' HEADER ;
postgres=# create view グループ名(A, B, C, D, E)
postgres-# as
postgres-# select Aの中身, Bの中身, Cの中身, Dの中身, Eの中身
postgres-# from userlist group by address ;
postgres=# create view grouplist(address, count, maxage, minage, avgage)
postgres-# as
postgres-# select address, count(*), max(age), min(age), avg(age)
postgres-# from userlist group by address ;
CREATE VIEW
postgres=# select * from grouplist;
address | count | maxage | minage | avgage
---------+-------+--------+--------+---------------------
kyoto | 1 | 35 | 35 | 35.0000000000000000
nagoya | 5 | 36 | 15 | 26.8000000000000000
paris | 7 | 36 | 20 | 26.2857142857142857
tokyo | 14 | 41 | 18 | 28.7857142857142857
londen | 2 | 22 | 21 | 21.5000000000000000
fuhuoka | 5 | 40 | 19 | 28.0000000000000000
newyork | 8 | 34 | 18 | 25.8750000000000000
(7 rows)
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+----------
public | grouplist | view | postgres
public | userlist | table | postgres
(2 rows)
create view flaglist1(name, age, flag)
as
select name, age,
case
when (address in ('tokyo','kyoto','nagoya')) and (age between 10 and 19) then 1
when (address in ('tokyo','kyoto')) and (age between 20 and 29) then 2
when (address in ('tokyo','kyoto')) and (age between 30 and 39) then 3
else 4
end as flag
from userlist ;
postgres=# select * from flaglist1 ;
name | age | flag
---------+-----+------
sato | 18 | 1
john | 34 | 4
lisa | 24 | 4
kaoru | 15 | 1
ryo | 40 | 4
bob | 23 | 2
jack | 35 | 3
mary | 25 | 4
linda | 26 | 4
yosuke | 41 | 4
takashi | 19 | 4
yuki | 20 | 4
tomo | 31 | 4
take | 36 | 3
yusuke | 22 | 4
nori | 19 | 1
mari | 20 | 4
sachi | 36 | 4
jun | 34 | 3
yasuko | 28 | 4
reo | 33 | 4
hana | 22 | 2
asa | 25 | 4
ryo | 36 | 4
kyoko | 34 | 3
chan | 28 | 4
misa | 18 | 4
postgres=# select count(*) from flaglist1 ;
count
-------
42
(1 row)
postgres=# select count(*) from flaglist1 group by flag;
count
-------
27
3
7
5
(4 rows)
postgres=# select flag, count(*) from flaglist1 group by flag;
flag | count
------+-------
4 | 27
1 | 3
3 | 7
2 | 5
(4 rows)
postgres=# select * from clublist ;
id | name | address | age
----+----------+---------+-----
1 | music | |
2 | tennic | |
3 | baseboll | |
4 | soccer | |
(4 rows)
postgres=# select * from flaglist1 ;
name | age | address | flag
---------+-----+---------+------
sato | 18 | tokyo | 1
john | 34 | newyork | 4
lisa | 24 | paris | 4
kaoru | 15 | nagoya | 1
ryo | 40 | fuhuoka | 4
bob | 23 | tokyo | 2
jack | 35 | kyoto | 3
mary | 25 | newyork | 4
linda | 26 | paris | 4
yosuke | 41 | tokyo | 4
takashi | 19 | fuhuoka | 4
yuki | 20 | paris | 4
tomo | 31 | newyork | 4
take | 36 | tokyo | 3
yusuke | 22 | nagoya | 4
nori | 19 | tokyo | 1
mari | 20 | newyork | 4
sachi | 36 | nagoya | 4
jun | 34 | tokyo | 3
yasuko | 28 | nagoya | 4
reo | 33 | nagoya | 4
hana | 22 | tokyo | 2
asa | 25 | newyork | 4
ryo | 36 | paris | 4
kyoko | 34 | tokyo | 3
chan | 28 | paris | 4
misa | 18 | newyork | 4
postgres=# select * from flaglist1
left join clublist
on flaglist1.flag = clublist.id ;
name | age | address | flag | id | name | address | age
---------+-----+---------+------+----+----------+---------+-----
sato | 18 | tokyo | 1 | 1 | music | |
kaoru | 15 | nagoya | 1 | 1 | music | |
nori | 19 | tokyo | 1 | 1 | music | |
lulu | 26 | tokyo | 2 | 2 | tennic | |
hana | 22 | tokyo | 2 | 2 | tennic | |
mishima | 25 | tokyo | 2 | 2 | tennic | |
bob | 23 | tokyo | 2 | 2 | tennic | |
sati | 25 | tokyo | 2 | 2 | tennic | |
jack | 35 | kyoto | 3 | 3 | baseboll | |
reiko | 32 | tokyo | 3 | 3 | baseboll | |
kyoko | 34 | tokyo | 3 | 3 | baseboll | |
panda | 35 | tokyo | 3 | 3 | baseboll | |
jun | 34 | tokyo | 3 | 3 | baseboll | |
zuke | 33 | tokyo | 3 | 3 | baseboll | |
take | 36 | tokyo | 3 | 3 | baseboll | |
asa | 25 | newyork | 4 | 4 | soccer | |
ryo | 36 | paris | 4 | 4 | soccer | |
chan | 28 | paris | 4 | 4 | soccer | |
misa | 18 | newyork | 4 | 4 | soccer | |
toshi | 22 | paris | 4 | 4 | soccer | |
kiki | 35 | fuhuoka | 4 | 4 | soccer | |
mashi | 32 | newyork | 4 | 4 | soccer | |
dore | 28 | paris | 4 | 4 | soccer | |
lyly | 19 | fuhuoka | 4 | 4 | soccer | |
osu | 22 | newyork | 4 | 4 | soccer | |
kiriko | 27 | fuhuoka | 4 | 4 | soccer | |
nao | 22 | londen | 4 | 4 | soccer | |
中身を変更
postgres=# update clublist set name = 'tennis' where id = 2;
UPDATE 1
postgres=# select * from clublist ;
id | name | address | age
----+------------+---------+-----
1 | music | |
3 | baseboll | |
4 | soccer | |
5 | valleyball | |
2 | tennis | |
(5 rows)
create view grouplist(name, age, address, flag1)
as
select name, age, address,
case
when (address in ('tokyo','kyoto','fukuoka')) and (age between 20 and 29) then 1
when (address in ('tokyo','kyoto')) and (age between 30 and 39) then 2
when (address in ('tokyo','kyoto')) and (age between 40 and 49) then 3
else 4
end as flag1
from userlist;
create view flaglist2(name, address, age, flag1, flag2, flag3)
as
select name, address, age,
case
when age < (select avg(age) from userlist) then 1
else null
end as flag1,
case
when age < (select avg(age) from userlist where address = 'tokyo') then 1
else null
end as flag2,
case
when age < (select avg(age) from userlist where address = 'tokyo') and address not in ('tokyo') then 1
else null
end as flag3
from userlist;
create view clubgroup(name, age, address, flag1, id2, clubname)
as
select * from grouplist as g
left outer join clublist as c
on g.flag1 = c.id;
select
sum(case when (age between 20 and 29) and (clubname = 'tennis') then 1 else 0 end) as hoge1,
sum(case when (age between 30 and 39) and (clubname = 'music') then 1 else 0 end) as hoge2,
sum(case when (age between 40 and 49) and (clubname = 'baseball') then 1 else 0 end) as hoge3
from clubgroup;