GIS

PostGIS (4): SQL 연습

유병혁 2023. 4. 11. 18:42

안녕하세요? 이번 글은 PostGIS 4번째 글로 'SQL 연습'을 주제로 정리해 보겠습니다.

 

DBMS은 데이터베이스를 관리하는 소프트웨어를 의미하며 데이터베이스를 생성하고, 데이터를 저장, 수정, 삭제, 검색할 수 있는 기능을 제공합니다. SQLDBMS와 상호작용할 수 있는 데이터 조작 언어입니다. SQL을 사용하여 데이터베이스에 대한 질의와 갱신, 정의, 제어 등을 수행할 수 있습니다.

 

, DBMS는 데이터베이스를 관리하는 역할을 하고, SQLDBMS와 상호작용하여 데이터베이스를 다루는 역할을 합니다. 따라서, DBMS를 잘 다루려면 SQL을 잘 다루는 것이 필수적입니다.

 

이번 글에서는 SQL 명령어의 세가지 종류인 데이터 정의 언어(DDL), 데이터 조작 언어(DML), 데이터 제어 언어(DCL)에 관해 간단히 소개하고, 실습을 통해 해당 기능을 확인해 보겠습니다.

1. SQL 명령어

SQL(Structured Query Language, 구조화 질의어)은 관계형 데이터베이스 관리 시스템(RDBMS)의 데이터를 관리하기 위해 설계된 특수 목적의 프로그래밍 언어입니다. SQL 명령어는 다음 세 가지로 크게 구별하여 나눌 수 있습니다.

  • 데이터 정의 언어 (DDL : Data Definition Language)
  • 데이터 조작 언어 (DML : Data Manipulation Language)
  • 데이터 제어 언어 (DCL : Data Control Language)

1.1. 데이터 정의 언어 (DDL: Data Definition Language)

데이터 정의 언어(DDL)는 데이터베이스 구조를 설계하는 SQL 명령을 나타냅니다. DDL을 사용하여 데이터베이스 객체를 만들고 수정합니다. CREATE, DROP, ALTER가 대표적인 명령어입니다.

  • CREATE (데이터베이스 객체 생성)
  • DROP (데이터베이스 객체 삭제)
  • ALTER (데이터베이스 객체 변경)

1.2. 데이터 조작 언어 (DML : Data Manipulation Language)

데이터 조작 언어(DML)는 새 정보를 쓰거나 관계형 데이터베이스의 기존 레코드를 수정합니다. SELECT, DELETE, UPDATE, INSERT가 대표적인 명령어입니다.

  • SELECT (테이블에서 데이터 검색)
  • DELETE (테이블에서 데이터 삭제)
  • UPDATE (테이블에서 데이터 수정)
  • INSERT (테이블에 데이터 삽입)

1.3. 데이터 제어 언어 (DCL : Data Control Language)

데이터 제어 언어(DCL)는 다른 사용자의 데이터베이스 액세스를 관리하거나 권한을 부여합니다. GRANT, REVOKE가 대표적인 명령어입니다.

  • GRANT (데이터베이스 사용자 권한 부여)
  • REVOKE (데이터베이스 사용자 권한 회수)

2. SQL 명령어 연습

이번 실습에서는 SQL 명령을 통해 데이터베이스에서 데이터를 정의, 조작해 보겠습니다.

2.1. 데이터 정의 언어 (DDL: Data Definition Language) 연습

앞서 우리는 “gongju”라는 공간 데이터베이스에 “z_ngii_n3a_g0110000”라는 행정경계(읍면동) 테이블을 추가한 상태입니다. 여기에 데이터 정의 언어(DDL)의 대표적인 CREATE, DROP, ALTER 명령을 직접 실습해 보겠습니다.

 

먼저 "gongju" 데이터베이스에서 "cities"라는 이름의 테이블을 생성해 보겠습니다. "gongju" 데이터베이스를 선택하고 Query Tool을 실행합니다.

아래 SQL문은 "gongju" 데이터베이스에서 "cities"라는 이름의 테이블을 생성합니다.

"cities" 테이블은 "id", "name", "population", "country" (column)을 가지며, 각각 정수형(int), 문자열(varchar) 형식으로 저장됩니다. NOT NULL은 해당 열의 값이 NULL이 될 수 없음을 의미합니다. , 해당 열에는 반드시 값이 존재해야 합니다. PRIMARY KEY는 해당 열이 기본 키(primary key)로 사용됨을 의미합니다. 기본 키는 테이블 내에서 각 행(row)을 고유하게 식별하는 열이므로 기본 키 열에는 중복된 값이 들어갈 수 없습니다. 따라서, "id" 열은 값이 반드시 존재하고 중복되지 않아야 하며 테이블의 기본 키로 사용됩니다.

CREATE TABLE cities (
  id INT NOT NULL PRIMARY KEY,
  name VARCHAR(50),
  population INT,
  country VARCHAR(50)
);

SQL 명령어를 입력하고 실행(Execute/Refresh, F5)합니다.

SQL 명령이 성공적으로 반환되었습니다.

Tables에서 Refresh를 클릭합니다.

다음과 같이 “cities” 테이블이 생성되었습니다.

"cities" 테이블을 삭제하기 위해서는 DROP 명령어를 사용할 수 있습니다. 테이블이 삭제되면 해당 테이블의 구조와 데이터가 모두 삭제되므로주의해서 사용해야 합니다.

DROP TABLE cities;

이번에는 행정경계(읍면동) 테이블 이름을 기존 “z_ngii_n3a_g0110000”에서 "umd_kr"로 변경해 보겠습니다. ALTER 명령어를 사용하면 됩니다. 이제부터는 "umd_kr"이라는 이름으로 해당 테이블에 접근할 수 있습니다.

ALTER TABLE z_ngii_n3a_g0110000 RENAME TO umd_kr;

2.2. 데이터 조작 언어 (DML : Data Manipulation Language) 연습

데이터 정의 언어(DDL)과 더불어, 데이터 조작 언어(DDL)의 대표적인 SELECT, DELETE, UPDATE 명령을 직접 실습해 보겠습니다일단 “umd_kr” 테이블에서 모든 데이터를 조회하려면 다음과 같이 작성합니다.

SELECT * FROM umd_kr;

이것은 “umd_kr” 테이블의 모든 열(*)을 가져오는 명령어입니다. 테이블 크기에 따라 SELECT * FROM 쿼리는 매우 오래 걸릴 수 있습니다LIMIT 구문은 결과를 개수로 제한합니다. 다음 구문을 실행하면 "umd_kr" 테이블에서 첫 100개의 레코드만 조회할 수 있습니다.

 

SELECT * FROM umd_kr LIMIT 100;

SELECT 문은 테이블에서 필요한 열만 선택하여 데이터를 조회할 수 있습니다. 참고로 행정경계(읍면동) 테이블에서 “bjcd”는 법정동코드를, “name”은 명칭을 의미합니다. "umd_kr" 테이블의 첫 100개의 레코드에서 “bjcd”“name” 컬럼만 조회해 보겠습니다.

SELECT bjcd, name FROM umd_kr LIMIT 100;

bjcd, 즉 법정동코드는 10자리 숫자로 구성됩니디. 시도(2) + 시군구(3) + 읍면동(3) + 리(2)의 조합입니다. 예를 들면, 충청남도 공주시 사곡면의 법정동코드는 4415039000입니다. WHERE 구문을 사용하여 "umd_kr" 테이블에서 "bjcd" 컬럼 값이 '4415039024'인 레코드의 "bjcd" "name" 컬럼 값을 조회할 수 있습니다. *법정동코드 정보는 '행정표준코드 관리시스템(https://www.code.go.kr/)'을 참고하시면 됩니다.

SELECT bjcd, name FROM umd_kr WHERE bjcd = '4415039000';

이번에는 "umd_kr" 테이블에서 "bjcd" 컬럼 값이 '44150'으로 시작하는, 즉 충청남도 공주시에 해당하는 레코드의 "bjcd""name" 컬럼 값을 조회해 보겠습니다.

SELECT bjcd, name
FROM umd_kr
WHERE bjcd LIKE '44150%';

COUNT 함수를 이용하면 "umd_kr" 테이블에서 "bjcd" 컬럼 값이 '44150'으로 시작하는 레코드의 개수를 조회할 수 있습니다.

SELECT COUNT(*)
FROM umd_kr
WHERE bjcd LIKE '44150%'; --공주시 선택

아래는 “bjcd”'44230'으로 시작(충청남도 논산시)하는 조건을 만족하는 레코드를 삭제하는 SQL문입니다. DELETE 구문은 특정 조건을 만족하는 레코드를 삭제하기 위해 사용합니다.

DELETE FROM umd_kr
WHERE bjcd LIKE '44230%'; --논산시 삭제

다음은 "umd_kr" 테이블에서 "bjcd" 컬럼 값이 충청남도 공주시에 해당하는 레코드들을 새로운 테이블로 추가하는 SQL문의 예시입니다. 여기서 "umd_gongju"는 새로 생성될 테이블의 이름을 나타내며, SELECT 문에서는 "umd_kr" 테이블에서 "bjcd"'44150'으로 시작하는 레코드들을 선택합니다.

CREATE TABLE umd_gongju AS
SELECT * FROM umd_kr WHERE bjcd LIKE '44150%';

이번에는 ALTER 명령을 연습해 보겠습니다. 예를 들어, 'umd_gongju' 테이블에서 “bjcd” 컬럼의 이름을 “gongju_cd”로 변경하고 싶다면 다음과 같이 SQL문을 작성할 수 있습니다.

ALTER TABLE umd_gongju RENAME COLUMN bjcd TO gongju_cd;

다음은 “umd_gongju” 테이블에서 “gongju_cd” 컬럼의 값을 앞에 5자리를 제외하고 보여주는 SQL 구문입니다. RIGHT 함수를 사용하여 문자열의 오른쪽 끝에서 5자리를 제외한 나머지 문자열을 추출하여 “gongju_cd”라는 별칭으로 보여줍니다. LENGTH 함수는 문자열의 길이를 반환합니다.

 

SELECT RIGHT(gongju_cd, LENGTH(gongju_cd) - 5) AS gongju_cd
FROM umd_gongju

UPDATE 명령으로 “gongju_cd”의 원래 값을 변경해 보겠습니다.

UPDATE umd_gongju
SET gongju_cd = RIGHT(gongju_cd, LENGTH(gongju_cd) - 5);

, 이제 첫 5개 레코드만 검색해 보겠습니다.

SELECT gongju_cd, name FROM umd_gongju LIMIT 5;

여기까지 PostgreSQL에서 데이터 정의 언어(DDL)와 데이터 조작 언어(DML)의 대표적인 명령들을 직접 실습해 봤습니다.