2018年4月２０日コマンド

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 "[e-mail]"