Edited at

2018年4月20日コマンド

More than 1 year has passed since last update.

2018/04/20

select count(age) from userlist where age between 20 and 30;

select address, name, count(age) from userlist where age between 20 and 30 and address in('tokyo','kyoto');

select address, count(*) from userlist where age between 20 and 30 and address in ('tokyo','kyoto') group by address order by address desc;

\COPY (select address, count(*) from userlist where age between 20 and 30 and address in ('tokyo','kyoto') group by address order by address desc) TO '/home/lisa/data/addressGroup.csv' WITH CSV DELIMITER ',' HEADER ;

select address, count(*), max(age), min(age), avg(age) from userlist group by address;

create view grouplist(address,count,maxage,minage,avgage)

as

select address, count(*), max(age), min(age), avg(age) from userlist group by address;

create view grouplist(address,count,maxage,minage,avgage)

as

select address from userlist where count(*)>=4 group by address;

create view addresslist(address,name)

as

select address, name from userlist;

create view agelist(name, age)

as

select name, age from userlist;

create view futurelist(name,age,age10)

as

select name, age, age+10 from userlist;

select name, age,

case

when age between 20 and 29

then 1

else null

end

from userlist

select name, age,

case

when age between 10 and 19 then 1

when age between 20 and 29 then 2

else null

end

from userlist;

select name, age,

case

when age between 10 and 19 then 1

when age between 20 and 29 then 2

when age between 30 and 39 then 3

else 0

end as flag

from userlist;

select name,address,

case

when address='tokyo' then 1

when address='kyoto' then 2

else 0

end as flag

from userlist;

create view agegroup(name,age,address,flag)

as

select name,age,address,

case

when age between 10 and 19 and address='tokyo' then 1

when age between 10 and 19 and address='kyoto' then 1

when age between 20 and 29 and address<>'tokyo' then 2

else 0

end

from userlist;

create view addressgroup(name,age,flag)

as

select name,age,address,

case

when age between 10 and 19 and address in('tokyo','kyoto') then 1

when age between 20 and 29 and address<>'tokyo' then 2

else 0

end

from userlist;

create view ageaddressview(name,age,address,flag)

as

select name,age,address,

case

when age between 10 and 19 and address in('tokyo','kyoto') then 1

when age between 20 and 29 and address<>'tokyo' then 2

else 0

end

from userlist;

select name,age,address from userlist where age between 10 and 19 and address in('tokyo','kyoto');

create table clublist(

id int,

name varchar(20));

insert into clublist values(1,'music');

insert into clublist values(2,'tenis');
insert into clublist values(3,'baseball');
insert into clublist values(4,'soccer

create view flaglist1(name,age,address,flag)

as

select name,age,address,

case

when age between 10 and 19 and address in('tokyo','kyoto','nagoya') then 1

when age between 20 and 29 and address in('tokyo','kyoto') then 2

when age between 30 and 39 and address in('tokyo','kyoto') then 3

else 4

end

from userlist;

select flag,count(*) from flaglist1 group by flag order by flag;

 flag | count

------+-------
1 | 3
2 | 7
3 | 7
4 | 25
(4 rows)

select name, age, address from userlist where age between 20 and 29 and address in('tokyo','kyoto') order by age;

  name   | age | address

---------+-----+---------
saori | 21 | kyoto
hana | 22 | tokyo
nao | 22 | kyoto
bob | 23 | tokyo
sati | 25 | tokyo
mishima | 25 | tokyo
lulu | 26 | tokyo
(7 rows)

update userlist set address='london' where age between 20 and 29 and address='tokyo';

select address, name from userlist where

address='tokyo' and age between 20 and 29;

 name  | age | address

-------+-----+---------
saori | 21 | kyoto
nao | 22 | kyoto
(2 rows)

 flag | count

------+-------
1 | 3
2 | 2
3 | 7
4 | 30
(4 rows)

select address,cout(*) from userlist group by address;

select * from flaglist1

leftjoin clublist

on flaglist1.flage=clublist.id;

select * from flaglist1

left join clublist on flaglist1.flag=clublist.id ;

insert into clublist values(5,valleyball);

select * from flaglist1 as f left outer join clublist as c on f.flag=c.id;

select * from clublist as c left join flaglist1 as f on c.id=f.flag;

select * from clublist as c left outer join flaglist1 as f on c.id=f.flag;

select * from clublist left outer join flaglist1 on clublist.id=flaglist1.flag;

select *from flaglist1 left outer join clublist on flaglist1.flag=clublist.id;

select id, name from userlist union select id,name from clublist;

create table hobbylist(

id int,

name varchar(20)

);

insert into hobbylist values(1,'soccer');

insert into hobbylist values(2,'cooking');

insert into hobbylist values(3,'music');

insert into hobbylist values(4,'tennis');

insert into hobbylist values(5,'cafe');

select * from hobbylist

select name,age,address, rank() over(order by age) as rank from userlist;

select name, age, address, rank() over(partition by address order by age) as rank from userlist;

git

git init

git status

touch sample.txt

git add sample.txt

git commit sample.txt

git log

git branch

git branch fix1

git checkout fix1

git merge --no-ff fix1

git log --graph

git rest --hard

git reset --hard 3b671699b423a35cead3a8f5cee2d56ec0531bc5

git checkout -

git config --global user.name "testkho"

git config --global user.email "test007@gmail.com"