SELECT Hash


SELECT로 Hash 데이터를 조회

SELECT로 Hash 데이터를 조회합니다.

테스트 데이터 입력: Redis 명령으로 입력

Example

명령>hset myname1 name kim age 25 birthday 1995-01-01 city Seoul
결과>4
명령>hset myname2 name kang age 30 birthday 1990-01-01 city Daejeon
결과>4
명령>hset myname3 name park age 20 birthday 2000-01-01 city Seoul hobby ski,bike
결과>5
명령>hset myname4 name choe age 30 birthday 1990-01-01 city Daejeon
결과>4
명령>hset myname5 name lee age 35 birthday 1985-01-01 city Busan
결과>4
명령>hset subject1 name kim kor 90 eng 80 math 70
결과>4
명령>hset subject2 name kang kor 80 eng 90 math 60 science 70
결과>5
명령>hset subject3 name park kor 70 eng 80 math 90 history 60
결과>5
명령>hset subject4 name lee kor 30 eng 40 math 40 history 50 science 40
결과>6

SQL Insert 문으로 입력

Example

명령> insert into hash values('myname1', 'name', 'kim', 'age', 25, 'birthday', '1995-01-01', 'city', 'Seoul');
결과>1 inserted
명령> insert into hash values('myname2', 'name', 'kang', 'age', 30, 'birthday', '1990-01-01', 'city', 'Daejeon');
결과>1 inserted
명령> insert into hash values('myname3', 'name', 'park', 'age', 20, 'birthday', '2000-01-01', 'city', 'Seoul',
'hobby', 'ski,bike');
결과>1 inserted
명령> insert into hash values('myname4', 'name', 'choe', 'age', 30, 'birthday', '1990-01-01', 'city', 'Daejeon');
결과>1 inserted
명령> insert into hash values('myname5', 'name', 'lee', 'age', 35, 'birthday', '1985-01-01', 'city', 'Busan');
결과>1 inserted
명령> insert into hash values('subject1', 'name', 'kim', 'kor', 90, 'eng', 80, 'math', 70);
결과>1 inserted
명령> insert into hash values('subject2', 'name', 'kang', 'kor', 80, 'eng', 90, 'math', 60, 'science', 70);
결과>1 inserted
명령> insert into hash values('subject3', 'name', 'park', 'kor', 70, 'eng', 80, 'math', 90, 'history', 60);
결과>1 inserted
명령> insert into hash values('subject4', 'name', 'lee', 'kor', 30, 'eng', 40, 'math', 40, 'history', 50, 'science', 40);
결과>1 inserted

SELECT

SELECT * : 포함된 모든 필드를 조회합니다.
SELECT key, field1, field2 : 조회할 필드를 지정합니다.
SELECT field1 name1 or field1 AS name1 --> alias(별명) 사용 가능

FROM

FROM hash.*
FROM hash.myhash*
FROM hash.myhash1

SELECT * FROM HASH.MYHASH*

Hash에서 키와 필드를 조회한다.

Example

명령>select * from hash.myname1;   opcode
결과> 0) key|name|age|birthday|city
1) myname1|kim|25|1995-01-01|Seoul
명령>select key,name,city from hash.myname1;   opcode
결과> 0) key|name|city
1) myname1|kim|Seoul
명령>select * from hash.myname*;   opcode
결과> 0) key|name|age|birthday|city|hobby
1) myname1|kim|25|1995-01-01|Seoul|
2) myname2|kang|30|1990-01-01|Daejeon|
3) myname3|park|20|2000-01-01|Seoul|ski,bike
4) myname4|choe|30|1990-01-01|Daejeon|
5) myname5|lee|35|1985-01-01|Busan|

ORDER BY

정렬(sort)해서 보여준다.

Example

명령>select * from hash.myname* order by name;   opcode
결과> 0) key|name|age|birthday|city|hobby
1) myname4|choe|30|1990-01-01|Daejeon|
2) myname2|kang|30|1990-01-01|Daejeon|
3) myname1|kim|25|1995-01-01|Seoul|
4) myname5|lee|35|1985-01-01|Busan|
5) myname3|park|20|2000-01-01|Seoul|ski,bike
명령>select * from hash.myname* order by age;   opcode
결과> 0) key|name|age|birthday|city|hobby
1) myname3|park|20|2000-01-01|Seoul|ski,bike
2) myname1|kim|25|1995-01-01|Seoul|
3) myname2|kang|30|1990-01-01|Daejeon|
4) myname4|choe|30|1990-01-01|Daejeon|
5) myname5|lee|35|1985-01-01|Busan|
명령>select * from hash.myname* order by age desc;   opcode
결과> 0) key|name|age|birthday|city|hobby
1) myname5|lee|35|1985-01-01|Busan|
2) myname2|kang|30|1990-01-01|Daejeon|
3) myname4|choe|30|1990-01-01|Daejeon|
4) myname1|kim|25|1995-01-01|Seoul|
5) myname3|park|20|2000-01-01|Seoul|ski,bike

NULLS FIRST/LAST

Example

명령>select * from hash.subject* order by science;   opcode
결과> 0) key|name|kor|eng|math|science|history
1) subject1|kim|90|80|70|(nil)|(nil)
2) subject3|park|70|80|90|(nil)|60
3) subject4|lee|30|40|40|40|50
4) subject2|kang|80|90|60|70|(nil)
명령>select * from hash.subject* order by science nulls last;   opcode
결과> 0) key|name|kor|eng|math|science|history
1) subject4|lee|30|40|40|40|50
2) subject2|kang|80|90|60|70|(nil)
3) subject1|kim|90|80|70|(nil)|(nil)
4) subject3|park|70|80|90|(nil)|60

LIMIT

출력 행 수를 제한한다.

Example

명령>select * from hash.myname* limit 3;   opcode
결과> 0) key|name|age|birthday|city|hobby
1) myname1|kim|25|1995-01-01|Seoul|
2) myname2|kang|30|1990-01-01|Daejeon|
3) myname3|park|20|2000-01-01|Seoul|ski,bike
명령>select * from hash.myname* order by name limit 3;   opcode
결과> 0) key|name|age|birthday|city|hobby
1) myname4|choe|30|1990-01-01|Daejeon|
2) myname2|kang|30|1990-01-01|Daejeon|
3) myname1|kim|25|1995-01-01|Seoul|

LIMIT OFFSET

몇 행 건너 출력

Example

명령>select * from hash.myname* limit 3 offset 2;   opcode
결과> 0) key|name|age|birthday|city|hobby
1) myname3|park|20|2000-01-01|Seoul|ski,bike
2) myname4|choe|30|1990-01-01|Daejeon|
3) myname5|lee|35|1985-01-01|Busan|
명령>select * from hash.myname* limit 2, 3;   opcode
결과> 0) key|name|age|birthday|city|hobby
1) myname3|park|20|2000-01-01|Seoul|ski,bike
2) myname4|choe|30|1990-01-01|Daejeon|
3) myname5|lee|35|1985-01-01|Busan|

FUNCTIONS

COUNT(), LEN()

개수를 조회한다. len()은 value(값)의 개수를 구합니다.

Example

명령>select count(*) from hash.myname*;   opcode
결과> 0) count(*)
1) 5
명령>select count(key) from hash.myname*;
결과> 0) count(key)
1) 5
명령>select key, len(key) from hash.myname*;
결과> 0) key|len(key)
1) myname1|4
2) myname2|4
3) myname3|5
4) myname4|4
5) myname5|4
명령>select key, count(*) from hash.myname* group by key;
결과> 0) key|count(*)
1) myname1|1
2) myname2|1
3) myname3|1
4) myname4|1
5) myname5|1

MAX(), MIN()

최댓값, 최솟값을 조회한다.

Example

명령>select max(name),max(age) from hash.myname*;   opcode
결과> 0) max(name)|max(age)
1) park|35
명령>select min(name),min(age) from hash.myname*;   opcode
결과> 0) min(name)|min(age)
1) choe|20
명령>select min(age),max(age) from hash.myname*;   opcode
결과> 0) min(age)|max(age)
1) 20|35

이렇게 사용해도 됩니다.
MAX()와 필드를 같이 조회할 경우: MAX()에 해당하는 필드가 조회됩니다.

Example

명령>select max(name), * from hash.myname*;   opcode
결과> 0) max(name)|key|name|age|birthday|city|hobby
1) park|myname3|park|20|2000-01-01|Seoul|ski,bike

숫자 함수

SUM(), AVG()

합계, 평균을 구한다.

Example

명령>select sum(kor),sum(eng) from hash.subject*;   opcode
결과> 0) sum(kor)|sum(eng)
1) 270|290
명령>select avg(kor),avg(eng) from hash.subject*;   opcode
결과> 0) avg(kor)|avg(eng)
1) 67.5|72.5
명령>select sum(history),avg(history),count(*) from hash.sub* where history >= 0;   opcode
결과> 0) sum(history)|avg(history)|count(*)
1) 110|55.0|2


WHERE 조건

비교: =, <, <=, >, >=, !=, <>

키 비교

Example

명령>select * from hash.myname* where key >= 'myname3';   opcode
결과> 0) key|name|age|birthday|city|hobby
1) myname3|park|20|2000-01-01|Seoul|ski,bike
2) myname4|choe|30|1990-01-01|Daejeon|(nil)
3) myname5|lee|35|1985-01-01|Busan|(nil)
명령>select * from hash.myname* where key > 'myname3';   opcode
결과> 0) key|name|age|birthday|city|hobby
1) myname4|choe|30|1990-01-01|Daejeon|(nil)
2) myname5|lee|35|1985-01-01|Busan|(nil)

값(value) 비교

Example

명령>select * from hash.myname* where age = 30;   opcode
결과> 0) key|name|age|birthday|city|hobby
1) myname2|kang|30|1990-01-01|Daejeon|(nil)
2) myname4|choe|30|1990-01-01|Daejeon|(nil)
명령>select * from hash.myname* where age >= 30;   opcode
결과> 0) key|name|age|birthday|city|hobby
1) myname2|kang|30|1990-01-01|Daejeon|(nil)
2) myname4|choe|30|1990-01-01|Daejeon|(nil)
3) myname5|lee|35|1985-01-01|Busan|(nil)
명령>select * from hash.myname* where age < 30;   opcode
결과> 0) key|name|age|birthday|city|hobby
1) myname1|kim|25|1995-01-01|Seoul|(nil)
2) myname3|park|20|2000-01-01|Seoul|ski,bike
명령>select * from hash.myname* where age != 30;   opcode
결과> 0) key|name|age|birthday|city|hobby
1) myname1|kim|25|1995-01-01|Seoul|(nil)
2) myname3|park|20|2000-01-01|Seoul|ski,bike
3) myname5|lee|35|1985-01-01|Busan|(nil)

AND, OR

Example

명령>select * from hash.myname* where age >= 25 and city = 'Seoul';   opcode
결과> 0) key|name|age|birthday|city|hobby
1) myname1|kim|25|1995-01-01|Seoul|(nil)
명령>select * from hash.myname* where age > 30 or city = 'Seoul';   opcode
결과> 0) key|name|age|birthday|city|hobby
1) myname1|kim|25|1995-01-01|Seoul|(nil)
2) myname3|park|20|2000-01-01|Seoul|ski,bike
3) myname5|lee|35|1985-01-01|Busan|(nil)

BETWEEN

Example

명령>select * from hash.myname* where age between 25 and 30;   opcode
결과> 0) key|name|age|birthday|city|hobby
1) myname1|kim|25|1995-01-01|Seoul|(nil)
2) myname2|kang|30|1990-01-01|Daejeon|(nil)
3) myname4|choe|30|1990-01-01|Daejeon|(nil)

IS NULL, IS NOT NULL

Example

명령>select * from hash.subject* where science is null;   opcode
결과> 0) key|name|kor|eng|math|science|history
1) subject1|kim|90|80|70|(nil)|(nil)
2) subject3|park|70|80|90|(nil)|60
명령>select * from hash.subject* where science is not null;   opcode
결과> 0) key|name|kor|eng|math|science|history
1) subject2|kang|80|90|60|70|(nil)
2) subject4|lee|30|40|40|40|50

GLOB

*(별표), ?(물음표)
대소문자를 구분한다.

Example

명령>select * from hash.myname* where name glob 'k*';   opcode
결과> 0) key|name|age|birthday|city|hobby
1) myname1|kim|25|1995-01-01|Seoul|(nil)
2) myname2|kang|30|1990-01-01|Daejeon|(nil)
명령>select * from hash.myname* where birthday glob '19?5*';   opcode
결과> 0) key|name|age|birthday|city|hobby
1) myname1|kim|25|1995-01-01|Seoul|(nil)
2) myname5|lee|35|1985-01-01|Busan|(nil)

LIKE

%(퍼센트), _(밑줄)
대소문자를 구분하지 않는다.

Example

명령>select * from hash.myname* where name like 'K%';   opcode
결과> 0) key|name|age|birthday|city|hobby
1) myname1|kim|25|1995-01-01|Seoul|(nil)
2) myname2|kang|30|1990-01-01|Daejeon|(nil)
명령>select * from hash.myname* where birthday like '1990%';   opcode
결과> 0) key|name|age|birthday|city|hobby
1) myname2|kang|30|1990-01-01|Daejeon|(nil)
2) myname4|choe|30|1990-01-01|Daejeon|(nil)

IN

Example

명령>select * from hash.myname* where key in ('myname1','myname3');   opcode
결과> 0) key|name|age|birthday|city|hobby
1) myname1|kim|25|1995-01-01|Seoul|(nil)
2) myname3|park|20|2000-01-01|Seoul|ski,bike
명령>select * from hash.myname* where city in ('Seoul','Busan');   opcode
결과> 0) key|name|age|birthday|city|hobby
1) myname1|kim|25|1995-01-01|Seoul|(nil)
2) myname3|park|20|2000-01-01|Seoul|ski,bike
3) myname5|lee|35|1985-01-01|Busan|(nil)
명령>select * from hash.myname* where age in (20,30);   opcode
결과> 0) key|name|age|birthday|city|hobby
1) myname2|kang|30|1990-01-01|Daejeon|(nil)
2) myname3|park|20|2000-01-01|Seoul|ski,bike
3) myname4|choe|30|1990-01-01|Daejeon|(nil)

NOT IN

Example

명령>select * from hash.myname* where key not in ('myname1','myname3');   opcode
결과> 0) key|name|age|birthday|city|hobby
1) myname2|kang|30|1990-01-01|Daejeon|(nil)
2) myname4|choe|30|1990-01-01|Daejeon|(nil)
3) myname5|lee|35|1985-01-01|Busan|(nil)
명령>select * from hash.myname* where city not in ('Seoul','Busan');   opcode
결과> 0) key|name|age|birthday|city|hobby
1) myname2|kang|30|1990-01-01|Daejeon|(nil)
2) myname4|choe|30|1990-01-01|Daejeon|(nil)

GROUP BY

Example

명령>select city,count(*) from hash.myname* group by city;   opcode
결과> 0) city|count(*)
1) Busan|1
2) Daejeon|2
3) Seoul|2
명령>select city,min(age),max(age) from hash.myname* group by city;   opcode
결과> 0) city|count(*)
1) Busan|35|35
2) Daejeon|30|30
3) Seoul|20|25

GROUP BY HAVING

Example

명령>select city,sum(age) from hash.myname* group by city having sum(age) > 40;   opcode
결과> 0) city|sum(age)
1) Daejeon|60
2) Seoul|45

OPCODE


select * from hash.myname1;

select key,name,city from hash.myname1;

select * from hash.myname*;

ORDER BY

select * from hash.myname* order by name;

select * from hash.myname* order by age;

select * from hash.myname* order by age desc;

NULLS FIRST/LAST

select * from hash.subject* order by science;

select * from hash.subject* order by science nulls last;

LIMIT

select * from hash.myname* limit 3;

select * from hash.myname* order by name limit 3;

select * from hash.myname* limit 3 offset 2;

select * from hash.myname* limit 2, 3;

FUNCTIONS

select count(*) from hash.myname*;

select max(name),max(age) from hash.myname*;

select min(name),min(age) from hash.myname*;

select min(age),max(age) from hash.myname*;

select max(name), * from hash.myname*;

SUM(),AVG()

select sum(kor),sum(eng) from hash.subject*;

select avg(kor),avg(eng) from hash.subject*;

select sum(history),avg(history),count(*) from hash.sub* where history >= 0;

WHERE

select * from hash.myname* where key >= 'myname3';

select * from hash.myname* where key > 'myname3';

select * from hash.myname* where age = 30;

select * from hash.myname* where age >= 30;

select * from hash.myname* where age < 30;

select * from hash.myname* where age != 30;

select * from hash.myname* where age >= 25 and city = 'Seoul';

select * from hash.myname* where age > 30 or city = 'Seoul';

BETWEEN

select * from hash.myname* where age between 25 and 30;

IS NULL, IS NOT NULL

select * from hash.subject* where science is null;

select * from hash.subject* where science is not null;

GLOB

select * from hash.myname* where name glob 'k*';

select * from hash.myname* where birthday glob '19?5*';

LIKE

select * from hash.myname* where name like 'K%';

select * from hash.myname* where birthday like '1990%';

IN

select * from hash.myname* where key in ('myname1','myname3');

select * from hash.myname* where city in ('Seoul','Busan');

select * from hash.myname* where age in (20,30);

NOT IN

select * from hash.myname* where key not in ('myname1','myname3');

select * from hash.myname* where city not in ('Seoul','Busan');

GROUP BY

select city,count(*) from hash.myname* group by city;

select city,min(age),max(age) from hash.myname* group by city;

select city,sum(age) from hash.myname* group by city having sum(age) > 40;


<< Select ZSet Select Hash Select Stream >>

Email 답글이 올라오면 이메일로 알려드리겠습니다.