본문 바로가기

데이터베이스47

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.
Oracle SQL Developer 날짜 포맷 변경하기 SQL Developer의 날짜(Date) 컬럼은 기본적으로 년/월/일(RR/MM/DD)로 표시됩니다. 그런데 저희는 시분초까지 보고 싶죠. 간단한 설정으로 변경하면 년-월-일 시:분:초(YYYY-MM-DD HH24:MI:SS)로 볼 수 있답니다. 1. 쿼리를 수행하면 아래와 같이 날짜가 년/월/일로 표시됩니다. 2. 메뉴의 도구>환경 설정을 클릭합니다. 3. 환경설정 화면에서 데이터베이스를 하위 메뉴를 펼칩니다. 4. 데이터베이스 아래의 NLS 를 클릭하면 데이터베이스 NLS가 표시되는데 날짜 형식이 RR/MM/DD(년/월/일)로 되어있습니다. 이것을 변경하면 시:분:초를 볼 수 있습니다. 5. 아래와 같이 날짜 형식을 YYYY-MM-DD HH24:MI:SS로 변경하고 확인을 클릭합니다. 6. 다시 쿼.. 2020. 3. 24.
MSSQL용 ODBC 생성하기 오라클에서 MS-SQL을 DB Link를 걸기 위해서는 MS-SQL용 ODBC를 생성해야 하는 경우가 있습니다.이럴때 사용하기 위해서 ODBC를 생성하는 방법을 알아보겠습니다. 1. ODBC 데이터 원본 관리자를 실행합니다. - 윈도우즈 10에서는 시작버튼 옆의 검색(돋보기)를 클릭하고, ODBC를 입력하면 해당 프로그램이 표시됩니다. 2. ODBC 데이터 원본 관리자에서 추가버튼을 클릭합니다. 3. SQL Server를 선택하고 마침 버튼을 클릭합니다. 4. ODBC이름과 설명 서버 정보를 입력하고 다음을 클릭합니다. 예) 서버 : 127.0.0.1 5. 인증은 SQL Server 인증 사용을 선택하고, 로그인 ID와 암호를 입력 후 다음을 클릭합니다. 6. MS-SQL의 데이터베이스를 지정합니다. 7.. 2018. 12. 5.
오라클 기본정보(사용자, 테이블, 컬럼 등) 확인 쿼리 오라클에서 기본정보인 테이블, 컬럼, 사용자, 인덱스 등을 확인 할 수 있는 쿼리입니다. [사용자]1select * from dba_users;cs [테이블스페이스]1select * from user_tablespaces;cs [테이블]1select * from user_tables;cs [테이블 컬럼]1select * from user_tab_columns;cs [테이블 주석]1select * from user_tab_comments;cs [테이블 컬럼 주석]1select * from user_col_comments;cs [뷰]1select * from user_views;cs [인덱스]1select * from user_indexes;cs [인덱스 컬럼]1select * from user_ind_co.. 2018. 11. 26.
Oracle SQL Developer 쿼리 실행 Oracle SQL Developer 쿼리 실행하는 방법입니다.이 툴을 처음 사용하시는 분에게 도움이 되었으면 합니다. 1. SQL 워크시트를 엽니다. - 단축키 : Alt + F10 - 메뉴 : 도구 > SQL 워크시트 2. 접속 할 데이터베이스를 선택하고 확인 버튼을 클릭합니다. 3. 접속 정보(사용자, 비밀번)를 입력하고 접속합니다. 4. 쿼리를 작성하고 외쪽 상단의 명령문 실행(Ctrl + Enter)을 합니다. 5. 아래와 같이 쿼리 실행 결과가 표시됩니다. 2018. 11. 9.
MSSQL 스키마(소유자) 변경하기. MSSQL에서 스키마(소유자)를 변경하는 방법입니다.아래의 Alter문은 하나의 테이블의 스키마를 변경하는 방법입니다. ALTER SCHEMA [변경할 스키마명] TRANSFER [기존 스키마명].[변경할 테이블명] ex) ALTER SCHEMA SchemaName TRANSFER dbo.TableName 2018. 9. 19.
MSSQL에서 Openquery를 사용하지 않고 Linked Server에 쿼리 수행하기 Linked Server에 쿼리를 수행하기 위해 Openquery를 사용합니다.하지만 Openquery를 사용하면 쿼리를 문자열 인자로 넣아야 하기 때문에 가독성이 매우 좋지 않습니다. 따라서 하기의 내용은 Openquery를 사용하지 않고 Linked Server에 쿼리를 수행하는 방법입니다. (1) MS-SQL Linked Server- Syntax : SELECT * FROM [Liked Server Name].[Database Name].dbo.[Table Name] SELECT * FROM ERP.ERP_MDB.dbo.PartMaster (2) Oracle Linked Server- Syntax : SELECT * FROM [Liked Server Name]..[Catalog Name].[Tab.. 2017. 12. 28.
MS-SQL 반올림, 올림, 내림. MS-SQL에서의 숫자 반올림, 올림, 내림입니다. 아래의 쿼리를 참고하시면 쉽게 이해하실 겁니다. -- 반올림 SELECT ROUND(555.555, 2) -- 소숫점 세째자리 반올림 SELECT ROUND(555.555, 1) -- 소숫점 둘째자리 반올림 SELECT ROUND(555.555, 0) -- 소숫점 첫째자리 반올림 SELECT ROUND(555.555, -1) -- 일의 자리 반올림 SELECT ROUND(555.555, -2) -- 십의 자리 반올림 -- 올림 SELECT CEILING(555.555) SELECT CEILING(555.444) -- 내림 SELECT FLOOR(555.555) SELECT FLOOR(555.444) 2017. 11. 14.
MSSQL MERGE 쿼리 활용하기. MSSQL 데이터베이스에 특정 Rows를 Update 하거나 Insert를 쉽게 하는 방법으로 MERGE 사용합니다. 아래의 TB_USER 테이블에 ID가 'aaa'이고, Name이 'Name#1' 데이터를 넣고자 하는데, 동일 아이디가 있으면 UPDATE하고, 없으면 INSERT를 하려고 한다. 프로그램에서 구현하려면 INSERT 쿼리를 수행해서 오류(PK 중복 Exception)가 발생하면 UPDATE를 해야 한다. C# 또는 Java로 프로그래밍하면 try catch를 이용 할 수 있다. try { // Insert 쿼리 수행 // PK로 인해 Exception이 발생 } catch { // Insert 쿼리 수행 } 하지만 아래와 같이 MERGE 쿼리를 수행하면 더욱 쉽게 구현이 가능하다. 쿼리를.. 2017. 6. 19.
MSSQL 로그인 오류 18456 해결 방법 MSSQL 로그인 시 발생하는 오류 18456 해결 방법입니다.원인은 SQL Server 인증 모드가 비활성화 되어서입니다. 1. 아래와 같이 로그인 시 로그인 오류가 발생한 화면입니다. 오류메세지 : 사용자가 로그인하지 못했습니다.(Microsoft SQL Server, 오류 : 18456) 2. 윈도우즈 계정으로 로그인을 하고, 서버를 선택 후 팝업 메뉴에서 속성을 클릭합니다. 3.서버정보 화면에서 보안을 선택하고, 서버인증을 SQL Server 및 Widnows 인증모드를 선택 후 확인 버튼을 클릭 후 다시 로그인하면 ㄷ해결됩니다. 2017. 3. 5.
MS-SQL DB Suspect 해결법 아래의 쿼리를 수행하면 해결됩니다. -- DB 상태 초기화 EXEC sp_resetstatus 'DBName' -- 긴급 모드로 설정 ALTER DATABASE DBName SET EMERGENCY -- 정합성 체크 DBCC CHECKDB ('DBName') -- 단일 사용자 모드로 설정 ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE -- 정합성 체크 DBCC CHECKDB ('DBName', REPAIR_ALLOW_DATA_LOSS) -- 사용자 접근 허용 ALTER DATABASE DBName SET MULTI_USER 2016. 11. 11.