10. [SQL기초] 데이터 타입과 컬럼 값 연산하기

데이터베이스의 데이터를 행 단위로 가져오기만 할 수 있으면 쿼리 결과를 사용하는데 많은 제약이 따릅니다. 데이터를 재가공하기 위해 개발 언어에 전적으로 의존을 해야 하고, 경우에 따라서는 실제로 원하는 데이터를 얻기가 어려울 수도 있습니다.

SQL에는 일반 프로그래밍 언어와 비슷한 수준의 연산 기능을 제공합니다. 숫자 값 계산을 위한 산술 연산자부터 문자열 연산자와 함수, 그리고 수학적인 연산을 위한 내장 함수들까지 모두 제공합니다.

산술 연산자

숫자형 데이터를 사칙 연산하기 위한 연산을 지원합니다. +(덧셈), -(뺄셈), *(곱셈), /(나눗셈), %(나머지 값)와 같은 기본적인 연산자를 제공합니다. 산술 연산을 위한 피 연산자의 데이터 형은 숫자형 데이터여야 합니다. 상수와 컬럼 데이터 형은 정수, 실수형 데이터여야 합니다.

연산자 중 “%”는 나눗셈을 한 후 남는 나머지 값을 가져오는 연산자입니다. 연산자 대신 나머지 값을 구하는 내장함수인 MOD() 또한 사용할 수 있습니다. 다만 SQL Server는 MOD() 함수를 지원하지 않으므로 “%”를 사용하는 것을 추천합니다.

7 % 2 = 1

산술 연산자는 우선 순위가우선순위가 있습니다. *, /, % 연산자가 +, - 연산자보다 우선순위가 있습니다. 항상 그렇지만, 우선순위가 낮은 연산의 우선순위를 높이려면 괄호로 감싸서 최우선 처리되도록 해야 하고, 같은 우선순위인 연산은 왼쪽에서 오른쪽으로 계산합니다.

1 + 2 * 3 - (4 % 2) = 7

컬럼 값 연산

컬럼 값은 select 절로 선택하는 시점에 여러 가지 연산을 할 수 있습니다. 사칙 연산만이 아니라 여러 가지 복잡한 소식과 함수를 적용할 수 있습니다.

주문정보 테이블에서 주문 합계 금액을 구해보겠습니다.

Select id, p_sell_price, p_num, p_sell_price * p_num as sum from p_order order by wt desc;

Price * p_num 은 상품 가격 x 주문 수량이 됩니다. 그리고 AS 키워드로 컬럼에 별칭을 “sum”으로 붙입니다. 별칭은 꼭 붙이는 습관을 들이는 것이 좋습니다. 한글로 “합계”라고 표시하면 훨씬 보기는 좋습니다. 다만, 한글로 별칭을 사용할 때는 큰따옴표로 별칭을 감싸서 인코딩 문제가 발생하지 않도록 해주는 것을 잊지 말아야 합니다.

Select id, p_sell_price, p_num, p_sell_price * p_num as “합계” from p_order;

별칭은 따옴표로 감싸서 표시해도 됩니다. 다만, 표기 원칙상 데이터베이스 객체는 큰따옴표를 사용하고, 문자열은 따옴표로 표기한다는 표준 SQL 표기 원칙에 따라 대상 구분을 위해 열 이름 표기인 “합계”는 큰따옴표로 감싸는 것입니다.

실제로는 큰따옴표, 따옴표로 따로 구분하지 않아도 한글 이름을 표기하는데 대부분은 문제가 없습니다. 다만, 객체 이름을 표기할 때는 숫자로 시작할 수 없다는 SQL 객체 이름 규칙에 따라 따옴표나 큰따옴표로 감싸지 않은 별칭 이름 앞에 숫자를 넣어서는 안 됩니다.

다음 쿼리문은 실제로 MySQL에서는 에러가 발생하지 않습니다. 숫자로 별칭이 시작하고, 따옴표나 큰따옴표로 감싸지 않았지만 정상적으로 실행됩니다. 다른 데이터베이스에서는 에러가 발생하므로 이렇게 별칭을 사용하면 안 됩니다.

Select id, p_sell_price, p_num, p_sell_price * p_num as 3월29일합계 from p_order;

계산식을 where절 검색 조건으로 사용하기

Where 절에 컬럼 연산 결과를 검색 조건으로 설정해 연산식 조건에 맞는 검색 결과 행들을 얻을 수 있습니다.

주문 합계 금액이 30000원 이상인 주문을 찾고 싶으면 다음과 같이 쿼리문을 작성할 수 있습니다.

Select * from p_order where p_sell_price * p_num >= 30000;

조금 더 세련되게 앞에서 배운 컬럼에 수식으로 컬럼 연산을 했던 것을 적용해 합계 금액까지 별칭으로 함께 표시할 수 있습니다.

Select *, p_sell_price * p_num as “합계” from p_order where p_sell_price * p_num >= 30000;

Where 절의 검색 조건을 확장해서 특정 날짜의 주문 중 30000원 이상인 것만 찾아보겠습니다.

Select *, p_sell_price * p_num as “합계” from p_order where p_sell_price * p_num >= 30000 and wt >= '2021-03-14' and wt < '2021-03-15';

이때 주의할 점이 있습니다. 별칭으로 컬럼 이름을 정했기 때문에 이 계산 결과 컬럼 명 별칭을 where 절에도 사용할 수 있지 않을까 싶지만, 되지 않습니다.

Select *, p_sell_price * p_num as “합계” from p_order where “합계” >= 30000;

Select 쿼리문의 각 절은 실행 순서가 있습니다. Select 절의 컬럼 선택보다 where 절이 먼저 실행되기 때문에 where절의 “합계” 컬럼 별칭은 아직 존재하지 않습니다. Where절이 실행되면서 테이블의 검색 조건에 맞는 행들이 선택되고, 선택된 행들의 컬럼을 선택하고, 연산이 필요한 경우 연산을 해서 별칭을 할당하게 됩니다.

계산식을 order by 절 정렬 조건으로 사용하기

Where절과 마찬가지로 order by 절에도 컬럼 계산 결과를 기준으로 쿼리 결과를 정렬할 수 있습니다. 주문 테이블에서 주문 합계 금액이 큰 순서대로 정렬하려면 다음과 같은 쿼리문을 작성할 수 있습니다.

Select *, p_sell_price * p_num as “합계” from p_order order by p_sell_price * p_num desc;

Where절과는 다른 중요한 차이점이 있습니다. Where 절은 select절에 앞서 실행되기 때문에 select절의 컬럼 별칭을 사용할 수 없었지만 order by 절에는 사용할 수 있습니다.

Select *, p_sell_price * p_num as “합계” from p_order order by “합계” desc;

앞서 절의 실행 순서에 대해서 설명한 것처럼 order by는 select절의 컬럼 선택보다 나중에 실행됩니다.

별칭의 사용 여부를 기준으로 알게 된 것처럼 where절-> select절-> order by절 순서대로 실행됩니다.

널과 산술 연산자 연산

널은 모든 쿼리문에서 특별한 예외 취급을 받습니다. 산술 연산자로 널과 숫자 데이터를 연산할 때도 특별한 예외 처리를 받습니다. 다음 수식은 모두 널이 되며, 일반 수식에서는 에러를 발생시키는 나누기 0도 널로 처리됩니다.

Null + 1

Null – 10

1 + 2 * null / 3

Null / 0

문자열 연산

문자열의 일부를 가져오거나, 문자열과 문자열을 결합해서 긴 문자열을 만드는 문자열 처리 방법을 알아보겠습니다. 문자열을 처리하는 연산자는 데이터베이스마다 조금씩 다릅니다. 데이터베이스마다 문자열을 저장하고 처리하는 방식이 다르다 보니 문자열 연산을 위해 수식 연산자를 사용하기도 하고 함수를 사용하기도 합니다. 데이터베이스에 맞는 연산자, 또는 함수를 사용해야 합니다.

문자열 길이 알기

문자열 길이를 알려주는 함수는 CHARACTER_LENGTH입니다. CHARACTER_LENGTH(‘문자열’), 또는 CHARACTER_LENGTH(열 이름)으로 문자열 길이를 알 수 있습니다. Varchar 데이터 형은 실제 들어있는 데이터의 길이가 다르기 때문에 실제 문자열의 실제 길이를 알려면 CHARACTER_LENGTH 함수를 사용해 길이를 알아야 합니다.

문자열의 길이는 한글, 영문, 숫자 모두 1글자에 1이 되며, 문자열 개수만큼 정수 값을 반환합니다. CHARACTER_LENGTH()는 줄여서 CHAR_LENGHT()로 사용할 수 있습니다. 둘은 같은 함수이며, 단축 표현의 차이만 있습니다.

문자 인코딩과 문자열 길이의 관계

영문 전용인 문자열, 또는 문자는 ASCII 코드로 표현합니다. 영문 1 글자당 1바이트의 공간을 차지합니다. 한글, 일본어, 중국어와 같은 동양권 언어들을 포함해 다국어 문자를 모두 표현하려면 1바이트로는 불가능합니다. 그래서 모든 문자를 표현할 수 있도록 글자당 바이트를 늘리게 됩니다.

한글과 영문 표현을 위해서 만들어졌던 구형 한글 코드 체계는 EUC-KR입니다. 1글자당 2바이트를 사용합니다. 모든 다국어를 표현할 수 있도록 만들어진 코드 체계는 UTF-8입니다. 1글자당 3바이트를 사용합니다. 이렇게 문자를 일정한 규칙에 따라 데이터 형태로 저장하는 것을 인코딩이라고 합니다.

UTF-8은 글자를 데이터로 저장하기 위한 표준입니다. 최근의 데이터베이스들은 모두 UTf-8을 기본 인코딩으로 사용합니다.

어떤 인코딩 체계를 사용할지는 데이터베이스를 생성할 때, 또는 테이블을 생성할 때 개별적으로 설정할 수 있습니다. 데이터베이스를 생성할 때 설정한 코드 체계는 변경을 하지 않으면 테이블 생성할 때도 그대로 상속을 받아 유지되게 됩니다.

EUC-KR 인코딩으로 문자열을 저장하면 한글 1글자당 2바이트씩을 차지합니다. 다음 쿼리문은 인코딩 방식에 따라 실제 저장공간을 차지하는 바이트 수가 다릅니다. 문자열의 바이트 수를 반환하는 내장 함수는 OCTET_LENGTH() 입니다. 단축형으로 LENGTH()로도 사용할 수 있습니다.

select char_length('한글1글자당길이 1증가. alphabet과 공백도 1씩 증가');

문자열의 문자 개수는 33개로 반환되지만, 실제 바이트 수는 인코딩에 따라서 다르게 저장됩니다. 데이터베이스의 저장 공간을 차지하는 용량이 euc-kr이 더 적지만 표준인 UTF-8을 기본으로 사용하는 것이 좋습니다.

문자열 길이를 아는 표준 SQL 함수명은 CHAR_LENGTH(), OCTET_LENGTH() 입니다. MySQL은 추가로 CHARACTER_LENGTH()과 LENGTH()를 지원합니다.

문자열 길이를 얻는 내장 함수는 데이터베이스마다 조금씩 다릅니다.

문자열 앞뒤의 공백 제거

데이터베이스에서 문자열을 저장하고 관리를 하면서 가장 많이 발생하는 문제가 문자열 앞뒤의 공백입니다. 웹 페이지에서 사용자 입력을 받거나, 엑셀 데이터를 가져와 데이터베이스에 저장할 때 가장 빈번하게 발생하고, 가장 많이 후처리를 해야 하는 문제입니다.

문자열 검색, 또는 컬럼 값 비교를 할 때 문자열 앞뒤의 공백은 가져와야 하는 데이터를 가져오지 못하게 만드는 주요 원인 중의 하나입니다.

그래서 모든 데이터베이스에는 문자열 앞뒤의 공백을 제거해주는 내장 함수를 제공합니다.

함수 이름도 TRIM()으로 동일합니다. 내장 TRIM() 함수는 문자열 앞뒤의 공백을 제거한 문자열을 반환합니다.

select trim(' 앞 뒤에 공백이 있는 문자열      ');

앞, 또는 뒤에 있는 공백만 제거하려면 LTRIM(), RTRIM() 함수를 사용할 수 있습니다. LTRIM()은 왼쪽 공백만 제거, RTRIM()은 오른쪽 공백만 제거합니다.

SQL Server와 오라클 데이터베이스는 TRIM() 함수로 삭제하는 대상을 공백이 아닌 다른 문자로 지정해서 삭제할 수 있습니다.

Select TRIM(‘#’ from ‘#문자열 앞뒤 정리#’);

문자열 일부만 가져오기

문자열에서 원하는 위치부터 시작해서 원하는 개수의 문자를 잘라서 반환해주는 내장 함수를 제공합니다.

함수명은 SUBSTRING()이며, 오라클에서는 예외적으로 SUBSTR()로 사용합니다.

사용방법은 모두 동일해서 시작 위치에서 몇 개의 문자를 가져오는지 표시하는 방법으로 사용합니다. 다음 쿼리문은 문자열 4번째 위치에서부터 시작해서 3개의 문자를 가져오는 쿼리문입니다. “s I”를 반환합니다.

Select substring(‘This is a string!’, 4, 3);

오라클은 예외적으로 substr() 함수를 사용해 다음과 같이 일부 문자열을 가져옵니다.

Select substr('This is a string!', 4, 3) from dual;

문자열 합치기

2개 이상의 문자열을 하나의 긴 문자열로 합치는 방법을 알아보겠습니다. 연산자를 사용해 문자열 상수, 또는 컬럼 값을 합치는 간단한 연산이지만, 연산자가 데이터베이스마다 다르므로 주의해야 합니다.

연산자, 또는 함수를 사용해 합치는 문자열의 개 수에 제한이 없지만, 예외적으로 오라클의 CONCAT() 함수는 2개의 문자열만 합칠 수 있습니다.

그 외에 다른 데이터베이스들도 “||” 연산자와 CONCAT() 함수를 지원합니다.

Select concat(‘문자열’, ‘결합’);
Select ‘문자열’ + ‘결합’;

주문정보 테이블의 배송지, 우편번호를 합쳐서 하나의 배송 주소로 만들려면 다음과 같이 문자열을 합칠 수 있습니다.

Select concat(address, ‘[‘, zipcode, ‘]’) from p_order where address is not null;

단순히 문자열을 합치는 것에 더해 구분자를 이용해서 문자열을 하나로 합칠 수도 있습니다. CONCAT_WS() 함수는 첫 번째 파라미터로 구분자를 받아 구분자로 이어진 하나의 문자열을 반환합니다.

Select concat_ws(‘,’ , ’주문일’, ’주문자’, ’합계금액’, ’배송주소’);

구분자를 사용해 문자열을 합치는 기능을 활용해 쉼표를 구분자로 사용해 합친 문자열을 파일로 저장하면 엑셀에서 바로 열 수 있는 CSV 파일을 생성합니다.

주문 정보를 CSV 파일로 저장하기 위해 주문 행을 쉼표로 구분한 문자열로 생성하려면 다음과 같이 작성할 수 있습니다. 컬럼 데이터 안에 쉼표가 있는 경우를 대비해 큰따옴표로 각 열을 감싸면 안전한 CSV 파일을 생성할 수 있습니다.

select concat('"', concat_ws('","', id, p_code, p_num, mobile, address, zipcode), '"') AS csvrow from p_order where cancel <> 'Y';

오라클에서는 지원되지 않는 함수입니다.

날짜 연산

날짜와 관련된 데이터 형은 날짜, 시간, 날짜 시간 3개입니다. 날짜와 시간 데이터를 저장하는 방식은 데이터베이스 종류에 따라 다르기 때문에 사용하는 연산자와 내장 함수들도 모두 다릅니다.

MySQL은 기본 시간 관련 데이터 형이 초 단위까지만 지원하기 때문에 밀리세컨드 단위까지 지원하려면 데이터 형의 추가 타입을 설정해야 하는 주의점이 있습니다.

문자열을 날짜 객체로 바꾸기

날짜 문자열은 여러 가지 형식으로 표현할 수 있습니다. 국가마다, 또 문화권마다 년-월-일을 표시하는 방식과 순서가 모두 다릅니다.

2021-04-05

2021/03/27

05/05/2021

11 Apr 2021

모든 날짜 연산 기능은 날짜 데이터 객체 사이에서만 적용할 수 있습니다. 문자열로 표시된 날짜 표시는 데이터 연산을 할 수 없으므로 날짜 객체 함수를 사용해 문자열을 연산이 가능한 날짜 데이터 객체로 변환해야 합니다.

문자열을 날짜 데이터 객체로 변환하는 함수는 데이터베이스마다 다릅니다.

STR_TO_DATE(), STR_DATE() 함수는 첫 번째 파라미터로 날짜 문자열을 넣고, 두 번째 파라메터로 문자열의 날짜 포맷에 대한 정보를 표시하는 포맷 문자열을 넣게 됩니다.

‘2021-04-05’ 형태인 문자열을 날짜 데이터 객체로 변환하려면 문자열이 년월일을 어떻게 표시하는지 포맷으로 명시해야 합니다. 오라클 TO_DATE()는 “YYYY-MM-DD”로 포맷 정보를 표시하며, “-“로 구분된 4자리 연도, 2자리 월, 2자리 일로 된 문자열임을 알려주는 것입니다. MySQL은 “%Y-%m-%d”로 포맷을 명시해야 합니다.

사용할 일이 많지 않지만 1913년 9월 15일을 “15/09/13” 문자열로 표기했다면 STR_TO_DATE(%d/%m/%y), TO_DATE(‘15/09/13’, ‘DD/MM/RR’) 함수 표기로 변환합니다.

포맷을 작성하는 지시자들은 다음과 같은 것들이 있습니다.

SQL Server는 3번째 파라미터인 스타일로 포맷을 명시합니다. SQL Server의 스타일 지시자는 미리 정해진 날짜 포맷에 대한 3자리 숫자 표기로 다음과 같습니다. 다만, 별도로 스타일을 명시하지 않아도 대부분의 경우 자동으로 인식해서 날짜 데이터 객체로 변환합니다.

Select convert(datetime, ‘17/11/2021’);

날짜 데이터 객체를 다시 문자열로 바꾸려면 SQL Server는 앞의 스타일 표를 적용해 convert() 함수로 문자열로 변환할 수 있습니다.

Select convert(varchar, 날짜 객체/컬럼, 121);

한국과 동양권에서는 모두 연월일 순서로 날짜를 표기하고 년-월-일, 또는 년/월/일로 구분자를 사용하므로 여기에 해당하는 포맷으로 맞춰서 사용하면 됩니다. 그리고 대부분 데이터베이스가 한글 버전, 또는 지역 로케일을 지정해 설치한 경우 기본 표시 날짜 포맷이 년-월-일로 출력됩니다.

현재 시각 알기

현재 DBMS가 실행되는 서버의 시각을 아는 방법은 데이터베이스마다 다릅니다.

현재 시각을 아는 함수, 또는 변수는 데이터베이스에 따라 여러 종류가 있지만, 공통적으로 CURRENT_TIMESTAMP를 사용할 수 있습니다. CURRENT_TIMESTAMP는 변수형으로 “()” 표시 없이 사용합니다. MySQL은 변수형과 함수형 표기 모두를 지원하지만, 오라클은 변수형 표기만 지원합니다.

날짜 덧셈과 뺄셈

날짜 객체로 변환하는 방법이 데이터베이스마다 다른 것처럼 날짜 연산을 하는 방법도 데이터베이스마다 조금씩 다릅니다. 날짜 연산은 대부분의 경우, 특정 날짜를 기준으로 일수를 더하거나 뺀 날짜를 구해 두 날짜 범위에 해당하는 행을 검색하는 방식으로 사용합니다.

MySQL은 다음과 같이 날짜를 더합니다. 현재 시각에서 1년을 더한 날짜를 구하려면 다음과 같이 작성합니다.

select CURRENT_TIMESTAMP + interval 1 year;

“interval 더할 숫자 기간 타입”으로 더하거나 뺄 기간을 설정합니다. “+” 대신 “-“를 사용하면 기간을 뺍니다. 기간타입 키워드는 “year”, “day”, “month”, “hours”, “minutes”를 사용할 수 있습니다. 시(hours), 분(minutes)은 복수형이라는 점에 주의해야 합니다.

SQL Server는 DATEADD() 함수를 사용해 기간 덧셈과 뺄셈을 합니다.

DATEADD(기간 타입, 숫자, 날짜) 포맷으로 사용을 합니다. 사용 구문이 다를 뿐 파라미터와 함수 결과는 동일합니다. 현재 시각에서 1일을 더하는 함수 사용은 다음과 같습니다.

select dateadd(day, 1, getdate());

날짜 객체 사이의 연산은 날짜 객체 전용의 지원 함수를 사용해 몇 가지 원하는 값을 얻을 수 있습니다.

DATEDIFF() 함수는 두 날짜 사이의 일 수 차이를 반환하는 함수입니다. 실제 사용은 다음과 같이 오늘 날짜와 문자열로 받은 날짜를 비교해 며칠의 차이가 있는지를 알 수 있습니다. 날짜 계산식은 첫 번째 파라미터 – 두 번째 파라미터입니다. 두 번째 파라미터의 날짜가 더 크면 음수가 반환됩니다.

Select DATEDIFF(str_to_date('2021-03-14', '%Y-%m-%d'), CURRENT_TIMESTAMP);

날짜 객체가 아닌 문자열 날짜를 파라미터로 입력해도 일 수 차이를 구할 수 있습니다.

Select DATEDIFF(‘2021-03-14’, ‘2021-03-22’);

주문 정보 테이블에서 오늘부터 일주일 전까지의 날짜 기간 안에 있는 주문만 가져오려면 다음처럼 쿼리문을 작성합니다. 차이 나는 일 수를 알 수 있도록 datediff 별칭으로 계산식 컬럼을 추가해서 계산된 날짜 차이까지 표시를 했습니다.

Select *, datediff(current_timestamp, wt) as datediff from p_order where datediff(current_timestamp, wt) < 7;

SQL Server는 조금 독특하게, 반환받는 값의 타입을 설정할 수 있습니다. 두 날짜(시간)의 차이를 일 수로 반환받을지, 또는 시간이나 분으로 반환받을지를 파라미터로 지정할 수 있습니다. 시간으로 반환 받으려면 다음처럼 타입을 지정하면 192(시간)가 반환됩니다. 그리고 차이를 계산식도 MySQL과는 반대여서 3번째 파라메터 - 2번째 파라메터로 계산합니다. MySQL과는 양수/음수가 반대로 계산됩니다.

Select DATEDIFF(hour, ‘2021-03-14’, ‘2021-03-22’);

오라클은 뺄셈 산술 연산자 날짜 객체를 계산해서 일 수 차이를 얻습니다.

Select TO_DATE('2021-03-22','YYYY-MM-DD') - TO_DATE('2021-03-14','YYYY-MM-DD') from dual;

수학 함수 사용

기본 산술 연산자 외에 복잡한 계산식을 쉽고 단순하게 할 수 있도록 여러 가지 내장 수학 함수가 제공됩니다. 숫자 상수나 컬럼 값에 사용하는 대표적인 내장 수학 함수들은 다음과 같습니다.

MySQL에는 나머지를 구하는 산술 연산자로 %가 이미 있지만, 추가로 MOD()와 같은 나머지를 구하는 함수를 제공합니다. MOD 함수는 “MOD(a, b)”로 사용하며 MySQL은 “a MOD b”와 같이 연산자처럼 사용할 수 있습니다. SQL Server는 지원하지 않는 함수이며 “%”연산자를 사용해야 합니다.

몫을 구하는 div 연산자는 함수형 표기를 지원하지 않습니다. 산술 연산자처럼 “a mod b” 형식으로 표기해야 하며, MySQL 전용 연산자입니다.

주문정보에서 주문 합계액의 부가세 금액과 공급가액을 얻는 쿼리문은 다음과 같이 작성합니다. 이때 공급가는 합계 금액에서 부가세를 빼서 구해야 합니다.

Select p_sell_price * p_num, FLOOR((p_sell_price * p_num) * 0.1) as '부가세', p_sell_price * p_num - FLOOR((p_sell_price * p_num) * 0.1) as '공급가' from p_order;

데이터 형 바꾸기

데이터 형을 변경하는 내장 함수는 CAST()와 CONVERT() 2가지가 있습니다. 기본적으로 같은 기능을 하지만 CONVERT() 함수는 데이터베이스 종류에 따라 용도가 조금씩 다르기 때문에 CAST()를 사용해서 데이터 형을 바꾸는 것이 좋습니다.

CAST()로 데이터 형 바꾸기

기본 구문

Cast(값 as 데이터 타입)

사용 가능한 데이터 타입

Select cast(‘2021-03-27 12:34:56’ AS DATETIME);
Select ( 5 + cast('15' AS SIGNED))/4;
select cast(32768 as char);
select cast(20201017 as varchar(8)); /*MySQL  미지원*/

MySQL은 데이터 타입으로 varchar를 지원하지 않는 점을 주의해야 합니다. 데이터베이스에 따라 변환을 지원하는 데이터 타입이 약간씩 다르므로 변환이 가능한 데이터 타입인지 확인한 후 사용해야 합니다.

CONVERT()로 데이터 형 바꾸기

기본 구문

Convert(값, 데이터 타입)

Convert(문자열, 타깃 인코딩, 소스 인코딩)

Convert() 함수는 데이터 형을 바꾸는 기능과 함께 문자열 인코딩을 바꾸는 기능을 함께 합니다. 조금 더 광범위한 변환 기능을 제공하지만, 오라클에서는 데이터 형을 바꾸는 기능은 지원하지 않고, 문자열 인코딩 변환만 할 수 있습니다. 사용하는 데이터베이스 종류에 따라 제약이 있으므로 주의해야 합니다.

select Convert('2020-04-05 05-23-11', datetime);
select CONVERT('32768', decimal);

SQL Server는 파라미터 순서가 반대입니다. 주의해야 합니다.

Convert(datetime, ‘2020-04-05’);
Convert(int, ’32768’);

문자열 인코딩 변환은 다음과 같이 현재 인코딩과 바꿀 인코딩을 표시해서 3개의 파라미터로 작성합니다.

SELECT CONVERT( 'STRING', 'utf8', 'us7ascii' ) FROM dual; /*오라클 문자열 인코딩 변환*/

CASE 조건문 분기 처리

SQL 작성 구문 중에서 유일하게 조건식에 따라 선택적인 실행, 또는 데이터를 선택할 수 있는 조건절입니다. 프로그래밍 언어의 “if ~ then ~ else ~ end” 와 같습니다.

기본 구문

CASE WHEN 조건식1 THEN 실행1

                 [WHEN 조건식2 THEN 실행2]

[ELSE 실행3]

END

가장 기본적인 CASE 절은 다음과 같이 표시합니다.

Case when 조건식1 then 실행 end

데이터베이스에서 쿼리문으로 선택한 컬럼 값은 연산자나 내장 함수를 사용해 결과 값을 변경할 수 있지만 조건에 따라 원하는 값을 선택적으로 변경할 수는 없습니다.

주문 정보 테이블의 배송비(dtdmoney)의 컬럼 값이 0이면 “무료”, 0보다 크면 “유료”로 표시하기로 했다면, 연산자나 내장 함수로는 처리가 불가능합니다.

Select case
when dtdmoney = 0 then ‘무료’
else ‘유료’ end
as dtdtype
from p_order;

CASE 조건문의 WHERE 절 조건식이 참이면 THEN 절의 실행 내용이 실행되고, 거짓이면 다음 WHEN 절로 넘어가거나 ELSE 절의 실행문이 실행됩니다. ELSE의 실행문이 실행되거나 END를 만나면 CASE문은 종료됩니다.

ELSE 절은 WHEN절의 조건식 중 만족하는 것이 없을 경우 마지막으로 실행되는 절입니다.

널 값과 비교하기

널은 특별한 값으로 간주하기 때문에 항상 값을 비교할 때는 예외적인 처리와 예외적인 비교 연산자를 사용해야 합니다. CASE 조건문을 사용할 때도 널 값은 IS 연산자를 사용해서 별도로 구분을 해야 합니다.

case

when madein = 2 then ‘국산’

when madein is null then ‘미상’

else ‘외산’

end

as dtdtype

from product;

case 조건문을 사용할 때는 마지막 else 절은 가능하면 사용하는 것이 좋습니다. 조건 비교 과정에서 누락되거나 배제된 나머지 값에 대한 처리를 해야 값이 누락되는 컬럼이 생기지 않습니다.

스위칭 CASE 조건문

기본 CASE 조건문은 WHEN 절로 조건식이 참이 되는지를 순서대로 하나씩 비교해가면서 마지막에 ELSE나 END가 나올 때까지 순서대로 WHEN 절을 실행합니다.

같은 CASE 조건문이지만, 조건의 특정 값과 같은지를 비교하는 경우 WHEN절의 조건식 비교를 반복 표시하지 않고 비교할 값만 표시할 수 있습니다.

스위칭 CASE 구문

CASE 열 이름/변수 명 WHEN 값 THEN 실행1

[WHEN 값 THEN 실행2]

[ELSE 실행3]

END

WHEN절에 조건식을 반복 표시해서 비교할 필요가 없기 때문에 조금 더 간결한 표현이 가능합니다. 단, WHEN 절의 값과 CASE절의 열 이름/변수명이 같은 지만 비교만 가능하기 때문에 값의 대소를 비교하는 것과 같은 경우에는 사용할 수 없습니다. 그리고 널과 같은 특별한 값은 이 방법으로는 비교할 수 없으므로 반드시 NULL이 생기지 않는 컬럼에 사용하거나 ELSE절을 반드시 사용해서 컬럼 값이 누락되지 않도록 해야 합니다.

상품 정보 테이블에서 원산지 코드를 비교해서 원산지 국가 문자열로 변환하는 CASE 문은 다음과 같이 작성합니다.

Select CASE madein
when 1 then ‘중국’
when 2 then ‘대한민국’
when 3 then ‘안드로메다’
else ‘미상’
end as ‘원산지’
from product;