본문 바로가기
관리자

Programming-[Backend]/SQL

 (24)
[TIL] 문자열 LIKE 검색 인덱스 MYSQL DB 컬럼 설계 시 name과 같이 사람이 알아볼 수 있는 문자열의 컬럼이 있고, 그를 검색하는 로직이 있는 경우가 흔하다. 이 때, name을 index로 잡더라도 LIKE 검색 시에는 무조건 동작하지 않는다. MYSQL에 한해, 다음과 같은 조건으로만 인덱스가 적용된다. 인덱스가 적용되지 않을 경우 full scan이 적용되어 주의해야한다. • Containing → %값% (부분 검색) → 인덱스 사용 X• StartingWith → 값% (접두어 검색) → 인덱스 사용 O• EndingWith → %값 (접미어 검색) → 인덱스 사용 X  JPA의 경우의 예시이다. 위 규칙에 따라 적용된다고 생각하면 된다. findByNameContaining("abc") WHERE name LIKE '%abc%..
[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. 전체 복사해서 엑셀에 붙여넣고, 데이터 -> 텍스트 나..

티스토리툴바