[SQL] postgreSQL 기본문법 정리

 

postgreSQL DB | 데이터베이스 확인

select * from pg_catalog.pg_namespace;​

postgreSQL schema | 스키마 확인

select * from pg_catalog.pg_tables;​

postgreSQL DB, schema | 특정 스키마, 데이터베이스 조회

SELECT * FROM pg_catalog.pg_tables
WHERE schemaname = 'public'
AND tablename LIKE 'Weath%';​

postgreSQL diagram Visualization | 우클릭으로 Diagram Visualization 확인 (DATAGRIP)

postgreSQL table | 테이블명 변경

alter table "1.book2" rename to book;
alter table "3.user2" rename to "user";​

postgreSQL column | 컬럼명 변경

alter table book rename column c1 to Book_id;
alter table book rename column c2 to Title;
alter table book rename column c3 to isbn;
alter table book rename column c4 to 분야_1;
alter table book rename column c8 to 저자;​

postgreSQL select, from, order by | 내림 차순 desc

SELECT c8, c2
FROM Weather
ORDER BY c8 DESC;​

PostgreSQL Data type | 데이터 타입 변경 numeric 실수형으로

ALTER TABLE Weather ALTER COLUMN c8 TYPE NUMERIC USING c8::NUMERIC;
ALTER TABLE Weather ALTER COLUMN c6 TYPE NUMERIC USING c6::NUMERIC;
ALTER TABLE Weather ALTER COLUMN c5 TYPE NUMERIC USING c5::NUMERIC;​

조건 내림 차순

select 
	c8, c2, c3
from Weather
where c2 in ('Seoul', 'Busan')
and c8 > 3
order by c8 desc;​

 

JOIN

select "Weather".*
from "Weather", "TimeProvince"
where ("Weather".c3="TimeProvince".c1) and ("Weather".c2="TimeProvince".c3);​

max-min 컬럼 파생

SELECT 
	"Weather".c6
    , "Weather".c5
    , "Weather".c6-"Weather".c5 As Weather_gap
FROM "Weather", "TimeProvince";​

특정 값 횟수 조회

SELECT 
	"Weather".c3
    , count(1) as Freq
FROM "Weather"
GROUP BY "Weather".c3
ORDER BY "Weather".c3 DESC;​

다수 특정 값 횟수 조회

SELECT 
	"Weather".c3
    , "Weather".c2
    , count(1) as Freq
FROM "Weather"
GROUP BY "Weather".c3, "Weather".c2
ORDER BY "Weather".c3 DESC;​

postgreSQL join | JOIN 후 (max-min) 파생

select 
	"Weather".*
    , "Weather".c6-"Weather".c5 As Weather_gap
FROM "Weather", "TimeProvince"
WHERE ("Weather".c3 = "TimeProvince".c1) and ("Weather".c2 = "TimeProvince".c3);​

postgreSQL groupby | Join 후 평균 온도

select 
	"Weather".c2
    , "Weather".c3
    , sum("Weather".c6 - "Weather".c5) As TotalWeather
    , count(1) as Freq
    , avg("Weather".c6 - "Weather".c5) AS AvgWeather
from "Weather", "TimeProvince"
where "Weather".c3 = "TimeProvince".c1 and "Weather".c2 = "TimeProvince".c3
group by "Weather".c2, "Weather".c3;​

Join 후 도시별 누적환자수 집계

SELECT 
	"TimeProvince".c3
    ,count(1) as Num_Patients
FROM "TimeProvince", "PatientInfo"
WHERE "TimeProvince".c3 = "PatientInfo".c5
GROUP BY "TimeProvince".c3;​

Join 후 도시별,성별 누적환자 수 집계

SELECT 
	"TimeProvince".c3, "PatientInfo".c2, count(1) as Num_Patients
FROM "TimeProvince", "PatientInfo"
WHERE "TimeProvince".c3 = "PatientInfo".c5
GROUP BY "TimeProvince".c3, "PatientInfo".c2;​

도시별, 성별, 연령별 누적환자 수 집계

SELECT 
	"PatientInfo".c2, "PatientInfo".c3, "PatientInfo".c5, count(1) as Num_Patients
FROM "PatientInfo"
GROUP BY "PatientInfo".c5, "PatientInfo".c2, "PatientInfo".c3;​

최대,최소 환자수 집계

SELECT 
	MAX(Num_Patients) as Max_num_patients
	,MIN(Num_Patients) as Min_num_patients
FROM
(
    SELECT 
    	"TimeProvince".c3
        , count(1) as Num_Patients
    FROM "TimeProvince", "PatientInfo"
    WHERE "TimeProvince".c3 " atientInfo".c5
    GROUP BY "TimeProvince".c3
) AS derivedTable;​

도시별,성별 환자수 10000명 이상 결과조회

SELECT 
	"TimeProvince".c3
    , "PatientInfo".c2
    , count(1) as Num_Patients
FROM "TimeProvince", "PatientInfo"
WHERE "TimeProvince".c3 " atientInfo".c5
GROUP BY "TimeProvince".c3, "PatientInfo".c2
HAVING count(1) >= 10000;​

테이블 조인, 컬럼 합치기(concat)

select id, Concat(last_name, ' ', first_name) as names, c4
from name 
inner join genre ON name.id = genre.c1;​