@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)
@ 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)
@重複なし
postgres=# select distinct address from userlist ;
address
---------
kyoto
nagoya
paris
tokyo
fuhuoka
newyork
(6 rows)
@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)
@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
@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)
@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)
@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
@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)
@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)
@名前を変えるとき
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
@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)
@ユーザリストの平均年齢を確認する
postgres=# select avg(age)from userlist;
avg
---------------------
25.5000000000000000
(1 row)
@ユーザリストの年齢合計を確認する
postgres=# select sum(age)from userlist;
sum
-----
153
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)
@dbのtable削除
postgres=# drop table userlist
;
@salesと言うデータベース削除
postgres=# drop database sales
;
DROP DATABASE
@postgreSQL終了
postgres=# \q
@年下の人から整列する
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
@\d テーブル構造の表示
postgres=# \d userlist;
Table "public.userlist"
Column | Type | Modifiers
---------+-----------------------+-----------
id | integer |
name | character varying(20) |
address | character varying(30) |
age | integer |
update - データの更新
postgres=# update userlist
set address = 'fukuoka'
where name = 'toshi'
;
UPDATE 2
postgres=# select * from userlist ;
id | name | address | age
----+-------+---------+-----
1 | sato | tokyo | 23
2 | ken | nagoya | 29
2 | toshi | fukuoka | 18
2 | toshi | fukuoka | 18
@delete データの削除
postgres=# delete from userlist
where
name = 'toshi'
;
DELETE 2
postgres=# select * from userlist ;
id | name | address | age
----+------+---------+-----
1 | sato | tokyo | 23
2 | ken | nagoya | 29
(2 rows)
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).
#\i - fileを読み込み
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 ;
@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
;
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)
postgres=# update userlist
set address = 'london'
where
address in('tokyo', 'kyoto','nagoya')
and
age <= 19
;
UPDATE 3
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)