IT 성장일기

MySQL, MariaDB에서 Merge 구문 구현하기Merge가 머지?키를 비교해서 행이 이미 존재한다면 UPDATE를, 존재하지 않으면 INSERT를 수행하는 구문을 의미합니다.Oracle이나 SQL Server에는 Merge 구문이 존재하지만 MySQL에서는 조금 다른 방법으로 같은 동작을 수행할 수 있습니다.INSERT ~ ON DUPLICATE KEY UPDATE대상 테이블의 기본 키 또는 고유 키를 기반으로 INSERT 또는 UPDATE를 수행합니다.가장 널리 알려져 있으며 단순하고 쉽게 구현할 수 있습니다.INSERT INTO tavle (col_id, col01, col02, col03, reg_date)VALUES ('A0001', 'val01', 'val02', 'val03'..

MySQL, Maria DB에서 컬럼 암, 복호화 하기얼마 전 운영 중인 서비스 상에서 이벤트를 위한 작은 프로젝트 하나를 담당할 기회가 있었습니다.프로젝트를 진행하던 중 Maria DB에서 자체적으로 제공하는 암, 복호화 기능을 사용하게 되어 정리해보고자 합니다. 암호화 알고리즘은 AES 대칭키 방식을 따르고 있습니다.따라서 암, 복호화에 사용되는 키를 꼭 준비해야 합니다.간단 함수 설명HEX : 숫자 또는 이진 문자열을 16진수로 변환함UNHEX : 16진수 문자열을 다시 숫자 또는 이진 문자열로 변환함AES_ENCRYPT : 문자열을 AES 알고리즘을 사용해 이진 문자열로 암호화함AES_DECRYPT : 이진 문자열을 평문으로 복호화함암호화하기아래와 같이 평문과 키를 이용해 암호화할 수 있습니다.아..

MySQL, Maria DB에서 날짜를 구하는 다양한 방법MySQL은 현재 날짜와 시간을 반환하는 다양한 함수를 제공합니다.대표적으로 SYSDATE()나 NOW() 함수가 있는데요, 이 외의 다양한 함수와 응용방법을 함께 알아보겠습니다.날짜와 시간 가져오기1. SYSDATE()날짜와 시간을 반환하는 가장 대표적인 함수입니다.함수가 호출되는 순간의 날짜와 시간을 반환합니다. 2. NOW()현재 날짜와 시간을 반환합니다.쿼리가 시작되는 순간의 날짜와 시간을 반환하기 때문에 SYSDATE()와 비교했을 때 약간의 오차가 존재합니다. 3. CURRENT_TIMESTAMP()NOW() 함수와 동일합니다. 4. LOCALTIME()NOW() 함수와 동일합니다. 5. LOCALTIMESTAMP()NOW() 함수와 동..

DATE_ADD와 DATE_SUB로 날짜 계산하기MySQL, Maria DB에서 주어진 날짜를 이용해 특정 기간의 전, 후를 계산하는 방법입니다.두 함수의 사용법은 사실상 동일한데 기본 문법은 아래와 같습니다.DATE_ADD(date, INTERVAL expr unit)DATE_SUB(date, INTERVAL expr unit)date : 기준이 되는 날짜expr : 더하거나 빼고 싶은 기간unit : 단위DATE_ADDDATE_ADD 함수는 주어진 날짜를 기준으로 특정 기간 이후를 계산하기 위한 함수입니다.DATE_ADD(date, INTERVAL expr unit)실제 예제를 보겠습니다.오늘 날짜를 기준으로 3개월 후의 날짜를 구해보겠습니다.SELECT DATE_ADD(NOW(), ..

사용자 비밀번호 만료 기간 핸들링하기 오라클 사용 중 비밀번호가 만료되어 변경해야 하는 경우를 몇 번 겪다 보니 귀찮기도 하면서 한편으로는 이런 의문이 생겼습니다. '서비스 중인 고객사 내부망에 설치된 오라클 계정은 만료되는 일이 없는 걸까?' 찾아보니 역시 사용자의 비밀번호 만료 정책을 설정할 수 있는 방법이 있었고 정리해보고자 합니다. DBA 권한이 필요합니다. 프로필 생성하기 먼저 사용자에게 적용할 프로필을 생성해줘야 합니다. 프로필을 생성할 때 비밀번호 만료 기간을 30일로 정해주겠습니다. CREATE PROFILE profile_set_password_life_time LIMIT PASSWORD_LIFE_TIME 30; 생성한 프로필의 만료 기간을 변경해 줄 수도 있습니다. ALTER PROFI..

테이블 레이아웃 한번에 조회하기 테이블 레이아웃에 대한 종합적인 정보가 필요할 때 아래 쿼리를 통해 번에 조회할 수 있습니다. 이 때 제공되는 정보는 특정 테이블의 컬럼명, 데이터타입, 테이블 코멘트와 컬럼 코멘트 등이 포함됩니다. SELECT TC.TABLE_NAME , TC.COMMENTS , CC.COLUMN_NAME , CC.COMMENTS , C.DATA_TYPE , C.DATA_LENGTH , C.NULLABLE , C.DATA_DEFAULT FROM ALL_TAB_COMMENTS TC LEFT JOIN ALL_TAB_COLUMNS C ON TC.OWNER = C.OWNER AND TC.TABLE_NAME = C.TABLE_NAME LEFT JOIN ALL_COL_COMMENTS CC ON T..

문자열이나 이진 데이터는 잘립니다. (String or binary data would be truncated) 주로 컬럼 길이보다 긴 데이터를 삽입하려고 시도할 때 발생하는 예외입니다. SQL Server(MS SQL)와 함께 작업을 하다 보면 꽤나 빈번하게 마주치는 예외메시지입니다. 컬럼의 최대 문자열 길이를 늘여주거나, 삽입하고자 하는 데이터의 길이를 줄이는 것으로 해결 수 있습니다. 개인적으로 문자열 데이터의 길이를 줄이는 것은 권장하지 않는데 그 이유는 다음과 같습니다. 데이터의 손상이나 변경을 초래할 수 있음. 컬럼의 길이를 초과하는 데이터의 삽입이 또 다시 실행될 수 있음. 컬럼 길이 조절하기 아래 SQL을 실행하여 테이블의 컬럼 길이를 조회하고 수정할 수 있습니다. /*컬럼 길이 조회하기*..

SQL Server 테이블 정보 확인하기 SQL Server에서 현재 인스턴스에 생성된 DB 객체와 각 DB의 테이블 리스트, 테이블 구조 등을 조회할 수 있습니다. DB 객체 조회하기 SELECT * FROM SYS.sysdatabases 주요 항목 : DB name SID status create date mdf file name version 테이블 리스트 조회하기 SELECT * FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME /*order by 옵션은 생략해도 상관 없습니다.*/ 주요 항목 : table catalog (DB name) table schema table name table type 테이블 구조 조회하기 SELECT * FROM INFOR..

모두의 SQL을 읽고 배운 것을 복습하기 위해 정리해서 기록합니다. 해당 도서는 Oracle 데이터베이스를 기준으로 하고 있습니다. 이번에는 조인(Join)에 대해 알아보겠습니다. 조인은 테이블을 연계한다는 점에서 집합연산자와 비슷하지만 더 다양한 방식으로 정보를 가공할 수 있게 해 줍니다. 실무에서도 정말 많이 활용되는 만큼 확실히 알아두면 정말 좋을 것 같습니다. 0. 조인의 종류 조인은 데이터를 연계하는 조건이나 기준이 되는 테이블 등에 따라 여러 가지 방식으로 나누어집니다. 곱집합 (Cartisian product, cross join) : 구성 가능한 모든 행을 조인합니다. 동등 조인 (Equi join, Inner join) : 조인 조건이 일치하는 경우의 결과를 출력합니다. 비동등 조인 (N..

집합 연산자 모두의 SQL을 읽고 배운 것을 복습하기 위해 정리해서 기록합니다. 해당 도서는 Oracle 데이터베이스를 기준으로 하고 있습니다. 이번에는 테이블을 연계하여 데이터를 조회하는 다양한 방식에 대해 알아보겠습니다. 0. 집합 연산자 (Set operators) 집합 연산자는 SELECT 문 여러 개를 조건에 따라 붙여내어 결과를 합치거나 분리하는 방식입니다. 집합 연산자를 사용하는 데에는 몇 가지 문법적 제약이 있습니다. - 첫 번째 SELECT 문에서 기술한 컬럼과 두 번째 SELECT 문에서 기술한 컬럼은 서로 순서대로 1:1로 대응됩니다. 즉, 컬럼의 개수는 물론이고 데이터 타입 또한 일치해야 합니다. 컬럼의 개수나 데이터 타입이 일치하지 않으면 어마어마한 오류를 내뿜으며 쿼리를 내팽개칩..

키와 제약 조건 모두의 SQL을 읽고 배운 것을 복습하기 위해 정리해서 기록합니다. 해당 도서는 Oracle 데이터베이스를 기준으로 하고 있습니다. 0. 키 (Key)와 제약조건 (Constraint) 데이터가 저장되는 테이블에는 컬럼의 속성을 나타내는 키와 데이터의 형식, 중복되는 값 등에 관여하는 제약조건이 존재합니다. 가장 대표적으로 테이블 내에 중복된 값이 존재하지 않게 하기 위한 고유키 등이 있습니다. 키와 제약조건은 컬럼의 속성으로서 설정되며 데이터의 무결성을 보호하기 위한 방법으로서 활용됩니다. 1. 기본 키 (Primary Key) 기본키는 테이블 내에서 데이터를 식별하는 식별자 (Identifier) 역할을 합니다. 사이트의 계정이나 게임 상의 닉네임, 또는 주민등록번호 뒷자리와 같은 역..

함수 - 그룹 함수 모두의 SQL을 읽고 배운 것을 복습하기 위해 정리해서 기록합니다. 해당 도서는 Oracle 데이터베이스를 기준으로 하고 있습니다. 0. 그룹 함수 그룹 함수는 단일 행 함수와 달리 하나의 열의 여러 개의 행에 대하여 함수가 적용됩니다. 반 평균을 내거나 인원수를 세는 등 특정 집단에 대한 값을 가져오는 데 사용합니다. 1. 그룹 함수의 종류 COUNT : 열의 갯수 COUNT 함수는 열의 모든 행의 개수를 셉니다. [COUNT(culumn)] 또는 [COUNT(*)] 의 형태로 사용합니다. 별표(애스터리스크)는 보통 '모든'이라는 의미로 사용하는데 따로 컬럼이름을 지정하지 않고 별표를 매개변수로 사용하면 NULL값을 포함한 테이블의 모든 행의 개수를 셉니다. SUM, AVG : 합계..