본문 바로가기

데이터베이스57

MSSQL - 프로시저에서 SELECT 절의 값을 변수에 할당하는 방법 MSSQL 프로시저에서 변수를 선언하고 SELECT절의 값을 변수에 할당하는 방법입니다. 변수 선언 DECLARE @변수명 데이터타입; DECLARE @INT_VAL INT; DECLARE @STR_VAL VARCHAR(200); 변수에 값 할당 변수에 값을 할당 할때는 앞에 SET을 기술하고 할당하면 됩니다. SET @INT_VAL = 10; SET @STR_VAL = 'ABCDED'; SELECT 절의 여러 값을 변수에 할당 하는 방법 SELECT 절의 여러 컬럼의 값을 할당 할 수도 있습니다. ROW가 여러개인 경우 마지막 값이 할당되는점 주의하세요. DECLARE @INT_VAL INT; DECLARE @STR_VAL VARCHAR(200); WITH TEST AS ( SELECT 10 VAL1,.. 2023. 7. 12.
MSSQL 문자 함수 모음 MSSQL에서 사용 할 수 있는 문자 관련 함수입니다. ASCII : 왼쪽 첫번째 문자의 ASCII코드 값을 반환 -- ASCII(character_expression) SELECT ASCII('ABC') CHAR : ASCII 코드를 문자로 변환 -- CHAR (integer_expression) SELECTchar(65) CHARINDEX : expression2에서 expression1의 시작 INDEX 반환, 없으면 0 반환 -- CHARINDEX( expression1,expression2 [ , start_location] ) SELECT CHARINDEX('다', '가나다라마', 2) LEFT, RIGHT : 문자열의 왼쪽 또는 오른쪽부터 지정된 수만큼의 문자를 반환 -- LEFT,RIGHT.. 2023. 6. 28.
MSSQL 여러 Row를 하나로 합치기 여러 ROW의 컬럼의 값을 하나로 합치는 방법입니다. XML 만드는 쿼리를 이용하는데, 아래와 같이 쿼리를 실행하면 NAME 태그로 XML이 표시됩니다. WITH A AS ( SELECT '이순신' NAME UNION ALL SELECT '홍길동' NAME UNION ALL SELECT '김유신' NAME ) SELECT NAME FROM A ORDER BY NAME FOR XML PATH('') XML 태그를 없애기 위해서는 컬럼명을 지정하시 않게 해야 하는데, 구분자(,)를 넣고 ALIAS(별칭)를 지정하지 않습니다. WITH A AS ( SELECT '이순신' NAME UNION ALL SELECT '홍길동' NAME UNION ALL SELECT '김유신' NAME ) SELECT ',' + NA.. 2023. 6. 28.
MSSQL WITH NOLOCK 설정하기 MSSQL에서 트랜잭션이 진행중인 테이블을 SELECT 하면 트랜잭션이 끝날때까지 SELECT는 팬딩됩니다. 트랜잭션이 끝날때까지 기다리지 않고 SELECT 하기 위해서는 FROM절의 테이블마다 WITH (NOLOCk)를 넣어 주어야 합니다. SELECT * FROM [테이블명] WITH(NOLOCK) 매번 테이블에 WITH(NOLOCK)을 넣기에는 너무 귀찮죠..^^ SELECT 쿼리 수행 전에 아래의 와 같이 NOLOCK을 설정하면 FORM 절에 WITH(NOLOCK) 을 넣지 않아도 됩니다. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 2023. 6. 22.
MSSQL 여러 컬럼의 MIN, MAX, SUM, AVG 구하기 하나의 Row에 여러 컬럼 중 최소값, 최대값, 합계 등을 구하는 방법입니다. 많이 사용하지 않는 쿼리지만 알고 있으면 힘이 되겠죠. 아래의 간단한 예제를 보고 쉽게 따라 할 수 있을 겁니다. WITH T AS ( SELECT 12 A, 13 B, 56 C, 123 D, 3 E ) SELECT (SELECT MIN(COL) FROM (VALUES (A), (B), (C), (D), (E)) AS V(COL)) MIN_VAL, -- 최소값 (SELECT MAX(COL) FROM (VALUES (A), (B), (C), (D), (E)) AS V(COL)) MAX_VAL, -- 최대값 (SELECT SUM(COL) FROM (VALUES (A), (B), (C), (D), (E)) AS V(COL)) SUM.. 2023. 6. 13.
맥북에서 SQLite 용량 줄이기 윈도우즈의 DB Browser for SQLite 툴의 메뉴에서 DB 파일의 용량을 줄였습니다. 하지만 맥OS의 DB Browser for SQLite에서는 해당 기능이 없네요. ㅠㅠ DB 파일의 용량을 줄이기 위해서는 아래의 명령어를 SQL에서 실행하면 용량이 줄어듭니다. VACUUM; 1. 용량 줄이기 전 SQLite 파일의 크기입니다. 2. DB Browser for SQLite에서 VACUUM을 쿼리 실행 화면에서 실행합니다. 3. 아래와 같이 DB 파일 청소 작업을 할 것인지 물어봅니다. 예를 클릭하면 청소작업을 수행합니다. 4. 아래와 같이 약 1MB 줄어드었습니다. 2022. 11. 27.
MSSQL 문자 숫자 변환 및 숫자 체크 MSSQL에서 문자를 숫자로 변환해야 하는 경우가 종종 있습니다. 그런데 문자에 숫자가 아니 다른 문자가 있을 경우 오류가 발생하게 되는데 이런 경우 처리 방법을 알아보겠습니다. 문자를 숫자로 변화 쿼리 SELECT CONVERT(INT, '1234'), CONVERT(FLOAT, '1234.123'), CONVERT(NUMERIC, '1234.12345') 문자에 숫자가 아닌 경우 쿼리에 오류 발생 SELECT CONVERT(INT, 'a1234') ERROR : varchar 값 'a1234'을(를) 데이터 형식 int(으)로 변환하지 못했습니다. 문자를 숫자로 변화 가능 여부 쿼리 SELECT ISNUMERIC('a1234'), -- 0 (숫자 변환 X) ISNUMERIC('1234') -- 1 (.. 2022. 8. 18.
MSSQL에서 오라클 DECODE 처럼 쿼리 사용하기 오라클의 DECODE는 매우 유용한 함수입니다. MSSQL에서는 DECODE가 없어 처음에는 불편했습니다. 오라클의 DECODE처럼 사용 할 수 있는 방법을 소개합니다. 아래의 쿼리 예제를 실행해보면 쉽게 이해 할 수 있을 겁니다.^^ IIF 하나의 조건에 따라 참과 거짓으로 값을 표시 할 수 있는 함수입니다. IIF( boolean_expression, true_value, false_value ) 쿼리 예제 WITH CITY AS ( SELECT 10 CODE, '서울' NAME UNION SELECT 20 CODE, '부산' NAME UNION SELECT 30 CODE, '대구' NAME UNION SELECT 40 CODE, '대전' NAME ) SELECT NAME, IIF(CODE = 10,.. 2022. 7. 14.
MSSQL 분모가 0 일때 처리 방법 MSSQL 데이터베이스의 데이터로 계산하는 쿼리에서 분모가 0일 때 아래와 같은 오류가 발생합니다. WITH TEST AS ( SELECT 10 A, 0 B ) SELECT A / B FROM TEST 0으로 나누기 오류가 발생했습니다. 아래의 쿼리 같이 IIF 또는 CASE를 사용하여 0 인 경우를 처리하기도 합니다. WITH TEST AS ( SELECT 10 A, 0 B ) SELECT IIF(B = 0, 0, A / B), CASE WHEN B = 0 THEN NULL ELSE A / B END FROM TEST 다른 방법으로 ANSI_WARNINGS, ARITHIGNORE, ARITHABORT을 설정하는 방법입니다. ANSI_WARNINGS ON으로 설정한 경우 SUM, AVG, MAX, MIN.. 2022. 7. 7.
MSSQL 개월 차이에 대해서 MSSQL에서 두 날짜의 개월 차이를 구하는 함수로 DATEDIFF를 사용하면 됩니다. 하지만, DATEDIFF를 사용하면 일자까지를 계산하는 것이 아닌 단순히 월의 차이만을 구합니다. 즉, 올림을 하는 현상이 발생합니다. 아래의 쿼리는 8.x로 계산이 되어야 하지만, 월만으로 계산하여 9개월 나옵니다. SELECT DATEDIFF(MONTH, CONVERT(DATE, '20150529'), CONVERT(DATE, '20160201')) 개월수를 버림으로 처리하고 싶다면 아래의 쿼리로 처리해야 합니다. 결과는 8개월이 됩니다. SELECT CASE WHEN DATEPART(DAY, CONVERT(DATE, '20150529')) > DATEPART(DAY, CONVERT(DATE, '20160201').. 2022. 5. 24.
MSSQL CASE WHEN 조건절에서 NULL 처리 쿼리에서 CASE WHEN 조건절을 사용 시 간혹 NULL을 조건으로 처리해야 하는 경우가 있습니다. ISNULL을 이용하여 사용하기도 했는데 더 정확하게 할 수 있는 방법있네요. 아래의 CASE WHEN 조건절을 참고하세요. CASE WHEN 필드명 IS NULL THEN 값 WHEN NOT 필드명 IS NULL THEN 값 ... END 2021. 10. 21.
MSSQL 행 번호 매기기 MSSQL에서 행(Row) 번호 매기는 방법입니다. 문법 SELECT ROW_NUMBER() OVER(PARTITION BY columnName ORDER BY columnName) AS NO FROM tablename 행번호 쿼리 WITH EX AS ( SELECT '한국' NAME, '아시아' REGION UNION SELECT '미국', '북아메리카' UNION SELECT '캐나다', '북아메리카' UNION SELECT '영국', '유럽' UNION SELECT '독일', '유럽' UNION SELECT '중국', '아시아' UNION SELECT '필리핀', '아시아' ) SELECT ROW_NUMBER() OVER(ORDER BY NAME) AS NO, NAME, REGION FROM EX .. 2021. 8. 4.
MSSQL FORMAT 문자를 활용한 DATETIME 표시하기 MSSQL에서 날짜 타입을 Format으로 변경 CONVERT를 사용하기도 하는데 숫자로 되어 있어 매우 불편합니다. MSSQL에서도 오라클에서 처럼 FORMAT 문자를 이용하여 변환 할 수 있습니다. FORMAT 문자 yyyy - 년도 4자리(2021) yy - 년도 2자리(21) MM - 월(01~12) dd - 일(01-31) hh - 시간 12시로 표시(01~12) HH - 시간 24시로 표시(00~23) mm - 분(00~59) ss - 초(00~59) tt - 오전(AM)/오후(PM) 쿼리 예제 쿼리 결과 SELECT FORMAT (getdate(), 'yyyy-MM-dd') 2021-07-29 SELECT FORMAT (getdate(), 'yyyy-MM-dd hh:mm:ss') 2021-0.. 2021. 7. 29.
MSSQL에서 오라클 MINUS 사용하기 오라클의 MINUS와 같은 기능으로 MSSQL에서는 EXCEPT를 사용하면됩니다. EXCEPT는 두개의 쿼리에서 동일한 값을 빼는 기능입니다 문법 { | ( ) } { EXCEPT } { | ( ) } 아래는 예로 전체 테이블에서 이름(NAME)이 '이%' 로 시작하는 데이터를 삭제하는 쿼리입니다. SELECT ID, NAME FROM TABLE_NAME EXCEPT SELECT ID, NAME FROM TABLE_NAME WHERE NAME LIKE '이%' 2021. 7. 19.
MSSQL 날짜, 주차 데이터 없을 경우 MASTER..SPT_VALUES을 이용한 데이터 만들기 MASTER..SPT_VALUES의 데이블 데이터 템플릿으로 생각됩니다. 일정 기간의 날짜 또는 주차 등의 데이터를 만들경우 유용하게 사용 할 수 있습니다. MASTER..SPT_VALUES의 TYPE이 'P'인 데이터를 이용하여 날짜, 주차를 구하는 쿼리입니다. SELECT * FROM MASTER..SPT_VALUES WHERE TYPE = 'P' 1. 시작일 종료일로 날짜 데이터 쿼리 SELECT CONVERT(VARCHAR, DATEADD(D, NUMBER, '2021-07-01'), 23) FROM MASTER..SPT_VALUES WHERE TYPE = 'P' AND NUMBER 2021. 7. 15.
SQLite - LIMIT OFFSET를 활용한 페이징 처리 오라클에서는 Inline View와 Rownum을 이용하여 페이징 처리를 합니다. SQLite는 더 편리하게 Limit Offset을 활용하여 더욱 쉽게 처리 할 수 있습니다. 1. 전체 데이터 - 아래의 쿼리는 특정위치(위도/경도)에서 가까운 순서로 정렬하는 쿼리입니다. SELECT NAME, LAT, LON FROM SCHOOL_LOC ORDER BY ABS(LAT - 37.5670135) * ABS(LAT - 37.5670135) + ABS(LON - 126.9783740) * ABS(LON - 126.9783740) 2. LIMIT를 이용한 상위 2개만 얻기 SELECT NAME, LAT, LON FROM SCHOOL_LOC ORDER BY ABS(LAT - 37.5670135) * ABS(LAT.. 2021. 4. 23.
SQLite 오라클의 NVL과 같은 NULL 처리 SQLite에서 오라클의 NVL과 같은 NULL 값을 처리하는 함수는 IFNULL입니다. IFNULL(X,Y)는 X가 NULL일 경우 Y를 표시하고, NULL이 아니면 X를 표시합니다. [테이블] [쿼리] SELECT ID, NAME, IFNULL(NAME, '이름없음') FROM TEST [결과] 2021. 3. 4.
MS SQL 버전 확인 MS SQL에서 Select 결과를 JSON으로 변경 할 수 있는 기능 FOR JSON을 사용하려니 오류가 발생했습니다. FOR JSON을 사용하기 위해서 MS SQL의 버전이 2016(13.x) 이상이어야 하네요. MS SQL의 버전을 확인하는 쿼리는 아래와 같습니다. select @@version [결과] 2021. 3. 3.
SQLite - Select 결과 JSON으로 변경하기 요즘 XML보다 JSON을 많이 활용하고 있습니다. 예를 들면 REST 방식의 Open API 등 있겠죠. SQLite에서 Select문의 결과를 JSON으로 변화하는 함수가 존재합니다. (1) json_object : JSON 오브젝트를 생성 (2) json_group_array : JSON 배열 생성 아래의 테이블에 대해서 간단한 쿼리를 소개하겠습니다. 1. Select 결과의 각 Row를 JSON 오브젝트로 변경 쿼리 SELECT json_object('name', NAME, 'name_loc', NAME_LOC, "url", WIKI_URL) AS json_result FROM NATION_KO WHERE NAME_LOC like '%국' [결과] 2. Select 결과의 한 컬럼에 대해서 JSO.. 2021. 3. 2.
SQLite에서 오라클의 DECODE 사용하기(CASE WHEN...) 오라클의 DECODE 함수는 정말 막강한 기능을 제공하고 있습니다. DECODE를 사용하여 간결한 SQL을 만들기도 하는데 SQLite에는 DECODE 함수가 없습니다. DECODE는 SQL 표준이 아닌 오라클의 기능으로 생각해야 할 듯하네요. [CASE 문법1] CASE case_expression WHEN when_expression_1 THEN result_1 WHEN when_expression_2 THEN result_2 ... [ ELSE result_else ] END [예제 쿼리1] SELECT NAME, YYYY||MM||DD, CASE IS_CHECK WHEN 'Y' THEN '체크됨' WHEN 'N' THEN '체크안됨' ELSE '값없음' END FROM HISTORY [CASE 문.. 2021. 2. 19.
SQLite inert or replace(오라클/MS-SQL Merge 기능) 오라클 또는 MS-SQL의 Merge와 유사한 기능으로 SQLite에서는 insert or replace 기능이 있습니다. 사용법은 Merge보다 간단하지만, 디테일한 적용은 조금 부족합니다. insert or replace 기능은 PK 기준으로 해당 데이터가 있으면 insert하지 않고 replace(update)합니다. 1. 테이블 정보 PK는 ID 컬럼 하나만 지정했습니다. 2. 데이터 3. 동일 ID가 없는 경우 insert or replace into TB_USER(ID, NAME, DESC) values('ID-003', '김유신', '') [결과] 4. 동일 ID가 있는 경우 insert or replace into TB_USER(ID, NAME, DESC) values('ID-001', '.. 2021. 2. 8.