ciCentOSにPostgreSQLをインストールする (2)

```
[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 ;
```
```sql

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)
```
```sql

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)
```

```sql
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;
```