본문 바로가기
관리자

Programming-[Backend]/SQL

 (23)
[TIL] SQL sequence로 postgresql primary key sequence 없는 값 insert 하기 : 시퀀스 생성 완벽하게 정확한 내용은 아님. 개인 기록용. 혹시라도 참고하시는분은 테스트 디비 등에 주의해서 적용해보시고 진행하시길.. 1. 상황 postgresql 테이블에서 sequence가 지정되지 않고 serial로만 지정되어있어서 pk값을 default로 넣을 수가 없는 상황 with tmp as (select a_id, b_id from ab_table join b_table b on ab_table.b_id = b.id where 1 = 1 and ab.b_id in (복잡한 서브 쿼리) insert into c_table (id, deleted, created, modified, a_id, b_id) select nextval('c_tabld_id_seq'), null, now(), now(), tmp...
[SQLD]12. SQL 기본 8 - 옵티마이저, 실행 계획, 인덱스 1. 옵티마이저 옵티마이저는 개발자가 작성한 SQL문을 어떻게 실행할 것인지 계획하고 최적화하는 역할을 한다. 간단한 예로, 2개의 테이블을 조인할 때는 기준이 되는 FROM절의 테이블(Outer table)의 인스턴스의 숫자가 적을 때 두 테이블간 외래키 비교 연산이 적으므로 속도가 향상될 수 있다. 이런 경우 혹시 개발자가 많은 양의 데이터를 갖는 테이블을 FROM 테이블로 설정하더라도 SQL문을 변형하여 최적화해주는 것이 옵티마이저이다. 옵티마이저는 최적화 결과를 실행 계획(Execution Plan)에 저장한다. IDE별로 이 Execution plan을 볼 수 있는 tool을 제공한다. IntelliJ에서는 Explain Plan 이라는 메뉴로 제공한다. 조인 방법뿐 아니라 데이터의 개수, 연산..
[SQLD]11. SQL 기본 7 - GROUP BY, ROLLUP, CUBE, WINDOW, RANK, FIRST_VALUE, LAG, PERCENT_RANK 등 1. 그룹함수 ROLLUP ROLLUP은 GROUP BY의 컬럼에 대해서 subtotal을 만들어준다. 예시1) SELECT DECODE(DEPTNO, NULL, '전체합계', DEPTNO), SUM(SAL) FROM EMP GROUP BY ROLLUP(DEPTNO); Oracle에서 제공하는 DECODE구문을 사용해서 DEPNO가 NULL일때는 '전체합계', 아니면 DEPNO를 표시하도록 해주었다. MSSQL에서는 'IIF(조건, 참일때 값, 거짓일때 값)' 함수를 사용하면 된다. 예시2) SELECT DEPTNO, JOB, SUM(SAL) FROM EMP GROUP BY ROLLUP(DEPTNO, JOB); 각 부서, 직업별 합계가 SUM_SAL 컬럼의 값으로 표시되었다. 각 부서별 합계는 JOB이 ..
[SQLD]10. SQL 기본 6 - UNION UNION ALL 차이, MINUS(EXCEPT), Connect by, 서브쿼리 inline view, scalar subquery 1. UNION 과 UNION ALL의 차이 UNION은 두 개의 테이블을 하나로 만드는 연산이다. 두 개 테이블의 컬럼 수, 컬럼 데이터 형식이 모두 일치해야한다. UNION 연산은 두 개의 테이블을 하나로 합치면서 중복된 데이터를 제거한다. 그래서 UNION은 정렬을 발생시킨다. UNION ALL은 중복을 제거하거나 정렬을 유발하지 않는다. ALL 이니까 중복제거 없이 전부 보여준다고 기억하면 좋을 것 같다. SELECT * FROM DEPT; UNION SELECT * FROM DEPT; 2. MINUS(EXCEPT) MINUS는 두 개의 테이블의 차집합을 구하는 연산이다. 선행 SELECT문에는 있고, 후행 SELECT 문에는 없는 집합을 조회한다. MSSQL에서는 EXCEPT로 쓴다. SELEC..
[SQLD] 9. SQL 기본 5 - WITH, GRANT, SAVEPOINT, HASH JOIN, INTERSECT, CROSS JOIN 1. WITH 구문 WITH 구문은 서브쿼리를 사용해서 임시 테이블이나 뷰처럼 사용할 수 있는 구문이다. WITH로 지정하는 서브쿼리에 별칭을 지정할 수 있으며, 옵티마이저는 SQL을 인라인 뷰나 임시 테이블로 판단한다. 주로 서브쿼리문의 재사용성을 높이기 위해 사용한다. 특정 조건이나 데이터를 반복적으로 조회해야할 때, WITH 구문을 통해서 저장해놓고 사용하면 편리하다. 예제 ) WITH W_EMP AS (SELECT * FROM EMP WHERE DEPTNO = 30) SELECT * FROM W_EMP; 2. DCL 문 : GRANT, REVOKE DCL(Data Control Language)는 보통 잘 사용하지 않는다. 실무에서 사용하는 클라우드 서비스에서는 이를 한 단계 더 추상화한 형태로 ..
[SQLD] 8. SQL 기본 4 - NULL, 형변환, 내장형 함수, ROWNUM/ROWID 1. NULL의 특징 및 관련 함수 Null의 특징 NULL과 숫자 혹은 날짜를 더하면 NULL이 된다. NULL과 어떤 값을 비교할 때, '알 수 없음'이 반환된다. Null 관련 함수(Oracle DB) NVL Null이면 다른 값으로 바꾼다. NVL(age, 99)는 age 컬럼이 null값을 가지면 99로 바꾼다. NVL2 NVL + DECODE라고 할 수 있다. NVL2(age, 1, 0)은 age 컬럼이 null이 아니면 1, null이면 0을 반환한다. NULLIF 두 값이 같으면 null, 아니면 첫 번째 값을 반환한다. NULLIF(exp1, exp2)는 exp1 = exp2이면 null, 아니면 exp1을 반환한다. COALESCE(합치다) NULL이 아닌 최초의 인자값을 반환한다. C..
[TIL] INSERT INTO 여러 개 한 번에 넣기!(bulk insert, table insert) 1. 기존 DB에 있는 값들을 바탕으로 여러 인스턴스들을 한 번에 삽입하고 싶을 때 INSERT INTO (필드값1, 필드값2, ...) SELECT col1, col2, ... FROM ... WHERE ... INSERT INTO 뒤에 그냥 SELECT문으로 시작하여 삽입을 원하는 값들을 테이블로 만들어주면 된다! 그림 1대로 하지말고, 그림 2처럼 하면 된다. 2. insert into 자체를 정말 여러 개(수 백개 수준) 넣어야할 때 뭔가 초기값을 수백개 수준으로 넣고 싶다면 (위에서 그림 1대로 하고 싶다면), 열편집 기능을 사용한다. 1. 정제되지 않은 데이터를 메모장에 붙여넣는다. (뒤에서 notepad ++ 프로그램 사용할 것임) 2. 전체 복사해서 엑셀에 붙여넣고, 데이터 -> 텍스트 나..
[SQLD] 7. SQL 기본 3 - 데이터베이스 로그, TRUNCATE, Order by와 index 힌트, like문 underscore 1. 데이터베이스 로그 데이터 베이스는 트랜잭션이 발생하면 데이터베이스의 무결성을 확보하기 위해서 로그 파일을 남긴다. 트랜잭션이 일어나는 도중에 정전이나 예기치 못한 특별한 상황이 발생하여 시스템이 다운될수도 있다. 따라서 데이터베이스와 관련된 로그파일을 남겨두어 트랜잭션의 원자성이 깨진 부분 등을 복구할 수 있도록 한다. 다만 로그는 모든 정보를 남기는 것이 아니라, insert나 update 등 데이터의 변화가 생길 때만 기록을 남긴다. select 처럼 조회하는 부분은 로그 파일에 기록하지 않는다. MSSQL의 로그파일 관리(참조1) MSSQL은 3가지 타입의 로그파일을 남긴다. Primary Data File : 주 데이터 파일이며 데이터베이스의 나머지 파일들을 가리키고 있는 파일이다. 모든 데..

티스토리툴바