다음의 test1, test2테이블은 아래의 예제들을 테스트하기 위한 테이블이다.
create table test1 (id_ngd int4, name_ngd text);
insert into test1 values(2, 'ngd1_2');
insert into test1 values(1, 'ngd1_1');
insert into test1 values(3, 'ngd1_3');
insert into test1 values(4, 'ngdx_4');
insert into test1 values(1, 'ngdx_4');
create table test2 (id_ngd int4, name_ngd text);
insert into test2 values(2, 'ngd2_2');
insert into test2 values(1, 'ngd2_1');
insert into test2 values(3, 'ngd2_3');
insert into test2 values(4, 'ngdx_4');
insert into test2 values(1, 'ngdx_4');
select * from test1 , test2;
id_ngd | name_ngd | id_ngd | name_ngd
--------+----------+--------+----------
2 | ngd1_2 | 2 | ngd2_2
1 | ngd1_1 | 2 | ngd2_2
3 | ngd1_3 | 2 | ngd2_2
4 | ngdx_4 | 2 | ngd2_2
1 | ngdx_4 | 2 | ngd2_2
2 | ngd1_2 | 1 | ngd2_1
1 | ngd1_1 | 1 | ngd2_1
3 | ngd1_3 | 1 | ngd2_1
4 | ngdx_4 | 1 | ngd2_1
1 | ngdx_4 | 1 | ngd2_1
2 | ngd1_2 | 3 | ngd2_3
1 | ngd1_1 | 3 | ngd2_3
3 | ngd1_3 | 3 | ngd2_3
4 | ngdx_4 | 3 | ngd2_3
1 | ngdx_4 | 3 | ngd2_3
2 | ngd1_2 | 4 | ngdx_4
1 | ngd1_1 | 4 | ngdx_4
3 | ngd1_3 | 4 | ngdx_4
4 | ngdx_4 | 4 | ngdx_4
1 | ngdx_4 | 4 | ngdx_4
2 | ngd1_2 | 1 | ngdx_4
1 | ngd1_1 | 1 | ngdx_4
3 | ngd1_3 | 1 | ngdx_4
4 | ngdx_4 | 1 | ngdx_4
1 | ngdx_4 | 1 | ngdx_4
(25 rows)
다음은 두개의 테이블을 합친다.
select id_ngd , name_ngd from test1
union
select id_ngd , name_ngd from test2 ;
id_ngd | name_ngd
--------+----------
1 | ngd1_1
1 | ngd2_1
1 | ngdx_4
2 | ngd1_2
2 | ngd2_2
3 | ngd1_3
3 | ngd2_3
4 | ngdx_4
(8 rows)
다음은 두개의 테이블에서 같은 항목은 뺀다.
select id_ngd , name_ngd from test1
except
select id_ngd , name_ngd from test2 ;
id_ngd | name_ngd
--------+----------
2 | ngd1_2
1 | ngd1_1
3 | ngd1_3
(3 rows)
select id_ngd , name_ngd from test1
intersect
select id_ngd , name_ngd from test2 ;
id_ngd | name_ngd
--------+----------
4 | ngdx_4
1 | ngdx_4
(2 rows)
select t1.name_ngd from test1 t1
WHERE t1.name_ngd IN
(SELECT test2.name_ngd
FROM test2 t2
WHERE t2.id_ngd=4);
name_ngd
----------
ngdx_4
ngdx_4
(2 rows)
select t1.name_ngd from test1 t1
WHERE t1.name_ngd NOT IN
(SELECT test2.name_ngd
FROM test2 t2
WHERE t2.id_ngd=4);
name_ngd
----------
ngd1_2
ngd1_1
ngd1_3
(3 rows)
select AVG(t1.id_ngd) from test1 t1;
select max(t1.id_ngd) from test1 t1;
select min(t1.id_ngd) from test1 t1;
select count(t1.id_ngd) from test1 t1;
select sum(t1.id_ngd) from test1 t1;
avg
-----
2
(1 row)
max
-----
4
(1 row)
min
-----
1
(1 row)
count
-------
5
(1 row)
sum
-----
11
(1 row)
select id_ngd from test1
where id_ngd >=1
group by id_ngd
having count(id_ngd) = 1;
id_ngd
--------
2
3
4
(3 rows)
VIEW는 가상 테이블입니다.
create table test3(id_01 int4, id_02 int4 ,in_03 int4);
create table test4(id_01 int4, id_name text);
insert into test3 values(1,2,3);
insert into test3 values(2,1,7);
insert into test3 values(3,5,7);
insert into test3 values(4,9,7);
insert into test4 values(1,'ngd_01');
insert into test4 values(2,'ngd_02');
insert into test4 values(5,'ngd_05');
insert into test4 values(6,'ngd_06');
select t3.id_01 , t4.id_name
from test3 t3, test4 t4
where t3.id_01 = t4.id_01 ;
id_01 | id_name
-------+---------
1 | ngd_01
2 | ngd_02
(2 rows)
create view join1
as select t3.id_01 , t4.id_name
from test3 t3, test4 t4
where t3.id_01 = t4.id_01 ;
select * from join1;
id_01 | id_name
-------+---------
1 | ngd_01
2 | ngd_02
(2 rows)
다음의 예제에서 "%"는 0개 이상의 문자를 "_" 는 하나의 문자만을 의미한다.
select * from test1 where name_ngd like 'ng%' order by id_ngd;
id_ngd | name_ngd
-------+---------
1 | ngd1_1
1 | ngdx_4
2 | ngd1_2
3 | ngd1_3
4 | ngdx_4
(5 rows)
select * from test1 where name_ngd like 'ng_1%' order by id_ngd;
id_ngd | name_ngd
-------+---------
1 | ngd1_1
2 | ngd1_2
3 | ngd1_3
(3 rows)
create table xyz( x_txt text , y_txt text );
insert into xyz values('abc' ,'def');
insert into xyz values('cde' ,'efg');
insert into xyz values('mmm' ,'yyy');
insert into xyz values('ccc' ,'ddd');
insert into xyz values('fff' ,'fff');
insert into xyz values('가나다라' ,'마바사아자');
insert into xyz values('구누두루' ,'무부수우주');
select * from xyz where x_txt like '_bc';
x_txt | y_txt
-------+-------
abc | def
(1 row)
select * from xyz where x_txt like '_Bc';
x_txt | y_txt
-------+-------
(0 rows)
select * from xyz where y_txt like '%f%';
x_txt | y_txt
-------+-------
abc | def
cde | efg
fff | fff
(3 rows)
select * from xyz where y_txt like '%F%';
x_txt | y_txt
-------+-------
(0 rows)
--CASE SENSITIVE 대소문자 구분 ( ~ )
select * from xyz where x_txt ~ '.*d.*';
x_txt | y_txt
-------+-------
cde | efg
(1 row)
select * from xyz where x_txt ~ '.*D.*';
x_txt | y_txt
-------+-------
(0 rows)
--CASE INSENSITIVE 대소문자 구분없음 ( ~* )
select * from xyz where x_txt ~* '.*d.*';
x_txt | y_txt
-------+-------
cde | efg
(1 row)
select * from xyz where x_txt ~* '.*D.*';
x_txt | y_txt
-------+-------
cde | efg
(1 row)
select * from xyz where x_txt !~ '.*D.*';
x_txt | y_txt
----------+------------
abc | def
cde | efg
mmm | yyy
ccc | ddd
fff | fff
가나다라 | 마바사아자
구누두루 | 무부수우주
(7 rows)
select * from xyz where x_txt !~* '.*D.*';
x_txt | y_txt
----------+------------
abc | def
mmm | yyy
ccc | ddd
fff | fff
가나다라 | 마바사아자
구누두루 | 무부수우주
(6 rows)
select * from xyz where x_txt ~* '[a-c]';
x_txt | y_txt
-------+-------
abc | def
cde | efg
ccc | ddd
(3 rows)
select * from xyz where x_txt !~ '[a-c]';
x_txt | y_txt
----------+------------
mmm | yyy
fff | fff
가나다라 | 마바사아자
구누두루 | 무부수우주
(4 rows)
select * from xyz where x_txt ~* '[가-누]';
x_txt | y_txt
----------+------------
가나다라 | 마바사아자
구누두루 | 무부수우주
(2 rows)
select * from xyz where y_txt !~ '[마-수]';
x_txt | y_txt
-------+-------
abc | def
cde | efg
mmm | yyy
ccc | ddd
fff | fff
(5 rows)