CentOSにPostgreSQL

```sql
@name, age, age+10表示、20代人だけ探すの条件
postgres=# select name, age, age+10 
          from userlist 
              where age between 20 and 30 
;
  name   | age | ?column?
---------+-----+----------
 nao     |  22 |       32
 lisa    |  24 |       34
 bob     |  23 |       33
 mary    |  25 |       35
 linda   |  26 |       36
 saori   |  21 |       31
 yuki    |  20 |       30
 yusuke  |  22 |       32
 mari    |  20 |       30
 yasuko  |  28 |       38
 hana    |  22 |       32
 asa     |  25 |       35
 chan    |  28 |       38
 toshi   |  22 |       32
 lulu    |  26 |       36
 dore    |  28 |       38
 mishima |  25 |       35
 osu     |  22 |       32
 sati    |  25 |       35
 kiriko  |  27 |       37
(20 rows)
```

```sql
@　as
postgres=# select name, age, 
                 age+10 as "in 10 years"
                     from userlist 
                         where age between 20 and 30 

;
  name   | age | in 10 years
---------+-----+-------------
 nao     |  22 |          32
 lisa    |  24 |          34
 bob     |  23 |          33
 mary    |  25 |          35
 linda   |  26 |          36
 saori   |  21 |          31
 yuki    |  20 |          30
 yusuke  |  22 |          32
 mari    |  20 |          30
 yasuko  |  28 |          38
 hana    |  22 |          32
 asa     |  25 |          35
 chan    |  28 |          38
 toshi   |  22 |          32
 lulu    |  26 |          36
 dore    |  28 |          38
 mishima |  25 |          35
 osu     |  22 |          32
 sati    |  25 |          35
 kiriko  |  27 |          37
(20 rows)
```

```sql
@重複なし
postgres=# select distinct address from userlist ;

 address
---------
 kyoto
 nagoya
 paris
 tokyo
 fuhuoka
 newyork
(6 rows)
```

```sql
@where not 文　20台に含めない
postgres=# select * from userlist 
               where 
                  not age >= 20
;

 id |  name   | address | age
----+---------+---------+-----
  1 | sato    | tokyo   |  18
  5 | kaoru   | nagoya  |  15
 12 | takashi | fuhuoka |  19
 18 | nori    | tokyo   |  19
 29 | misa    | newyork |  18
 39 | lyly    | fuhuoka |  19
(6 rows)
```


```sql
@20歳以上の人を検索する
postgres=# select * from userlist
                            where age >=20
;
 id | name | address | age
----+------+---------+-----
  2 | nao  | kyoto   |  22
  3 | john | newyork |  34
  4 | lisa | paris   |  24
  6 | ryo  | fuhuoka |  40


@20歳~25歳間の人を検索する
postgres=# select * from userlist 
                 where age between 20 and 25
;

 id | name | address | age
----+------+---------+-----
  2 | nao  | kyoto   |  22
  4 | lisa | paris   |  24
```

```sql
@where 条件文　tokyoに住んでる人だけ探す
select * from userlist 
         where address = `tokyo` 
;
 id |  name   | address | age
----+---------+---------+-----
  1 | sato    | tokyo   |  18
  7 | bob     | tokyo   |  23
 11 | yosuke  | tokyo   |  41
 16 | take    | tokyo   |  36
 18 | nori    | tokyo   |  19
 21 | jun     | tokyo   |  34
 24 | hana    | tokyo   |  22
 27 | kyoko   | tokyo   |  34
 31 | reiko   | tokyo   |  32
 33 | lulu    | tokyo   |  26
 34 | panda   | tokyo   |  35
 36 | zuke    | tokyo   |  33
 38 | mishima | tokyo   |  25
 41 | sati    | tokyo   |  25
(14 rows)
```

```sql
@and - 条件文を複数に使える
postgres=# select * from userlist 
             where not age >= 20 and not address = 'tokyo' 
;
 id |  name   | address | age
----+---------+---------+-----
  5 | kaoru   | nagoya  |  15
 12 | takashi | fuhuoka |  19
 29 | misa    | newyork |  18
 39 | lyly    | fuhuoka |  19
(4 rows)
```

```sql
@or文
postgres=# select * from userlist 
               where age >= 20 or address = 'tokyo' 
;

 id |  name   | address | age
----+---------+---------+-----
  1 | sato    | tokyo   |  18
  2 | nao     | kyoto   |  22
  3 | john    | newyork |  34
  4 | lisa    | paris   |  24
  6 | ryo     | fuhuoka |  40
  7 | bob     | tokyo   |  23
  8 | jack    | kyoto   |  35
  9 | mary    | newyork |  25
 10 | linda   | paris   |  26
 11 | yosuke  | tokyo   |  41
 13 | saori   | kyoto   |  21
 14 | yuki    | paris   |  20
 15 | tomo    | newyork |  31
 16 | take    | tokyo   |  36
 17 | yusuke  | nagoya  |  22
 18 | nori    | tokyo   |  19
 19 | mari    | newyork |  20
 20 | sachi   | nagoya  |  36
 21 | jun     | tokyo   |  34
 22 | yasuko  | nagoya  |  28
 23 | reo     | nagoya  |  33
```

```sql
@count - 何数か、checkできる
postgres=# select count(*)from userlist 
                  where age >=20 
;
 count
-------
    36
(1 row)

postgres=# select count(*)from userlist
                  where address = 'tokyo' 
;


 count
-------
    14
(1 row)

postgres=# select count(*)from userlist 
                where age between 20 and 30 
;

 count
-------
    20
(1 row)
```



```sql
@group by - 実行した結果をグループ化する
postgres=# select address , count (*) from userlist 
                    group by address 
;
 address | count
---------+-------
 kyoto   |     3
 nagoya  |     5
 paris   |     7
 tokyo   |    14
 fuhuoka |     5
 newyork |     8
(6 rows)
```


```sql
@名前を変えるとき
postgres=# select 
              address as "area" from userlist 
;
  area
---------
 tokyo
 kyoto
 newyork
 paris
 nagoya
 fuhuoka
 tokyo
 kyoto
 newyork
 paris
 tokyo
 fuhuoka
 kyoto
 paris
 newyork
 tokyo
 nagoya
 tokyo
 newyork
 nagoya
 tokyo
```
```sql
@address -> as list名前を変更して見せる
postgres=# select 
              address as "area", 
                   count (*) from userlist 
                       group by address 
;

  area   | count
---------+-------
 kyoto   |     3
 nagoya  |     5
 paris   |     7
 tokyo   |    14
 fuhuoka |     5
 newyork |     8
(6 rows)
```

```sql
@ユーザリストの平均年齢を確認する
postgres=# select avg(age)from userlist;
         avg
---------------------
 25.5000000000000000
(1 row)

@ユーザリストの年齢合計を確認する
postgres=# select sum(age)from userlist;
 sum
-----
 153
```
```sql
postgres=# select address, max(age), min(age), avg(age), sum(age), count(*) from userlist 
                group by address 
;
 address | max | min |         avg         | sum | count
---------+-----+-----+---------------------+-----+-------
 kyoto   |  35 |  21 | 26.0000000000000000 |  78 |     3
 nagoya  |  36 |  15 | 26.8000000000000000 | 134 |     5
 paris   |  36 |  20 | 26.2857142857142857 | 184 |     7
 tokyo   |  41 |  18 | 28.7857142857142857 | 403 |    14
 fuhuoka |  40 |  19 | 28.0000000000000000 | 140 |     5
 newyork |  34 |  18 | 25.8750000000000000 | 207 |     8
(6 rows)
```

```sql

@dbのtable削除
postgres=# drop table userlist
;

@salesと言うデータベース削除
postgres=# drop database sales
;
DROP DATABASE
```


```sql
@postgreSQL終了
postgres=# \q

```




```sql

@年下の人から整列する
postgres=# select * from userlist 
               order by age
;

 id | name | address | age
----+------+---------+-----
  5 | ryo  | newyork |  15
  1 | sato | tokyo   |  18
  2 | nao  | kyoto   |  22
  3 | jo   | nagoya  |  32
  4 | jjno | yagona  |  41



@年上の人から整列する
postgres=# select * from userlist 
               order by age desc
;

 id | name | address | age
----+------+---------+-----
  4 | jjno | yagona  |  41
  3 | jo   | nagoya  |  32
  2 | nao  | kyoto   |  22
  1 | sato | tokyo   |  18
  5 | ryo  | newyork |  15
```

```sql

@\d テーブル構造の表示
postgres=# \d userlist;
           Table "public.userlist"
 Column  |         Type          | Modifiers
---------+-----------------------+-----------
 id      | integer               |
 name    | character varying(20) |
 address | character varying(30) |
 age     | integer               |
```

```sql
update - データの更新
postgres=# update userlist
        set address = 'fukuoka' 
            where name = 'toshi' 
;
UPDATE 2

```
```sql

postgres=# select * from userlist ;

 id | name  | address | age
----+-------+---------+-----
  1 | sato  | tokyo   |  23
  2 | ken   | nagoya  |  29
  2 | toshi | fukuoka |  18
  2 | toshi | fukuoka |  18
```


```sql
@delete データの削除
postgres=# delete from userlist 
where 
    name = 'toshi' 
;
DELETE 2

```
```sql

postgres=# select * from userlist ;

 id | name | address | age
----+------+---------+-----
  1 | sato | tokyo   |  23
  2 | ken  | nagoya  |  29
(2 rows)
```

```sql

postgres=# insert into userlist values (3, 'lisa', 'paris')
;
ERROR:  null value in column "age" violates not-null constraint
DETAIL:  Failing row contains (3, lisa, paris, null).


postgres=# insert into userlist (id, name, address) values (3, 'lisa', 'paris')
;
ERROR:  null value in column "age" violates not-null constraint
DETAIL:  Failing row contains (3, lisa, paris, null).
```

```sql
#\i - fileを読み込み
```

```sql
SQL
\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 ',' ;


\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
@viエディタで、まとめてできる

begin transaction ;

drop table userlist ;

create table userlist(
    id int,
       name varchar(20),
          address varchar(30),
               age int not null
    )
;

insert into userlist values(1,'sato','tokyo',18);
insert into userlist values(2,'nao','kyoto',22);
insert into userlist values(3,'john','newyork',34);
insert into userlist values(4,'lisa','paris',24);
insert into userlist values(5,'kaoru','nagoya',15);
insert into userlist values(6,'ryo','fuhuoka',40);
insert into userlist values(7,'bob','tokyo',23);
insert into userlist values(8,'jack','kyoto',35);
insert into userlist values(9,'mary','newyork',25);
insert into userlist values(10,'linda','paris',26);
insert into userlist values(11,'yosuke','tokyo',41);
insert into userlist values(12,'takashi','fuhuoka',19);
insert into userlist values(13,'saori','kyoto',21);
insert into userlist values(14,'yuki','paris',20);
insert into userlist values(15,'tomo','newyork',31);
insert into userlist values(16,'take','tokyo',36);
insert into userlist values(17,'yusuke','nagoya',22);
insert into userlist values(18,'nori','tokyo',19);
insert into userlist values(19,'mari','newyork',20);
insert into userlist values(20,'sachi','nagoya',36);
insert into userlist values(21,'jun','tokyo',34);
insert into userlist values(22,'yasuko','nagoya',28);
insert into userlist values(23,'reo','nagoya',33);
insert into userlist values(24,'hana','tokyo',22);
insert into userlist values(25,'asa','newyork',25);
insert into userlist values(26,'ryo','paris',36);
insert into userlist values(27,'kyoko','tokyo',34);
insert into userlist values(28,'chan','paris',28);
insert into userlist values(29,'misa','newyork',18);
insert into userlist values(30,'toshi','paris',22);
insert into userlist values(31,'reiko','tokyo',32);
insert into userlist values(32,'kiki','fuhuoka',35);
insert into userlist values(33,'lulu','tokyo',26);
insert into userlist values(34,'panda','tokyo',35);
insert into userlist values(35,'mashi','newyork',27);
insert into userlist values(36,'zuke','tokyo',33);
insert into userlist values(37,'dore','paris',28);
insert into userlist values(38,'mishima','tokyo',25);
insert into userlist values(39,'lyly','fuhuoka',19);
insert into userlist values(40,'osu','newyork',22);
insert into userlist values(41,'sati','tokyo',25);
insert into userlist values(42,'kiriko','fuhuoka',27);

commit 
;
```


```sql
postgres=# select * from userlist 
where 
    address in ('tokyo', 'kyoto') 
;

 id |  name   | address | age
----+---------+---------+-----
  1 | sato    | tokyo   |  18
  2 | nao     | kyoto   |  22
  7 | bob     | tokyo   |  23
  8 | jack    | kyoto   |  35
 11 | yosuke  | tokyo   |  41
 13 | saori   | kyoto   |  21
 16 | take    | tokyo   |  36
 18 | nori    | tokyo   |  19
 21 | jun     | tokyo   |  34
 24 | hana    | tokyo   |  22
 27 | kyoko   | tokyo   |  34
 31 | reiko   | tokyo   |  32
 33 | lulu    | tokyo   |  26
 34 | panda   | tokyo   |  35
 36 | zuke    | tokyo   |  33
 38 | mishima | tokyo   |  25
 41 | sati    | tokyo   |  25
(17 rows)
```

```sql
postgres=# update userlist 
             set address = 'london' 
where 
    address in('tokyo', 'kyoto','nagoya') 
  and 
   age <= 19 
;

UPDATE 3
```
```sql

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