SQL 종류
데이터 조작어(DML):
데이터에 대한 CRUD
- SELECT
- INSERT INTO 테이블() VALUES
- DELETE FROM 테이블 WHERE
- UPDATE 테이블 SET 컬럼 = 0 WHERE
SELECT문 관련
- 4번 SELECT 문 특징
- SELECT List에 서브쿼리 적용 가능??
- 8번 SELECT 문 특징
- WHERE 절에는 집계 함수 불가
- 11번 SELECT 문 특징
- VARCHAR2의 컬럼에서 숫자로 된 WHERE 조건을 사용하고 싶으면 해당 컬럼값이 모두 숫자 형식이어야 가능함 -> 그럼 오류가 안 남
- 오라클에서는 ‘’ 값을 삽입하면 NULL로 들어감
- 10번 부정 비교 연산자
- select문 실행 순서
- from(테이블 참조) -> where(조건 아닌 레코드 제거) -> group by(그룹화) -> having(그룹 제거) -> select(컬럼 선택) -> order by(정렬)
- from절에서 별칭을 사용하면 select절에서 반드시 별칭을 사용해줘야함
- select절에서 테이블 컬럼 없이 그냥 스칼라 값만 넣으면 오류 발생
GROUP BY 절과 HAVING 절의 특징
- GROUP BY 절에서는 SELECT 절처럼 별칭 사용 불가
- HAVING 절에서 집계 함수 사용 가능
- WHERE 조건에 만족하지 못하는 건 소그룹에 포함이 안됨
- group by사용없이 having을 사용한다고 오류가 발생하는건 아님
- GROUP BY에서 null도 한개의 소그룹으로 생성됨
- GROUP BY를 사용할 경우 기준 컬럼과 집계 함수 이외의 컬럼은 참조 불가능
ORDER BY
- select 절에는 없는 컬럼이더라도, from 안 테이블에 있는 컬럼이라면 오류가 발생하지 않고 잘 실행됨
- 컬럼명 대신 컬럼 순서나 별칭을 사용 가능
INSERT/UPDATE
- insert문에 삽입 컬럼을 명시하지 않으면 반드시 모든 컬럼을 입력해야함
- 묵시적 형변환
- 문자열 -> 날짜
- 숫자 -> 문자열
- 정수 -> 실수
데이터 정의어(DDL):
테이블 구조에 대한 CRUD
- CREATE
- ALTER
- DROP
- RENAME
- DROP 옵션
- CASCADE: 연관된 모든 객체들을 모두 삭제하는 것
- RESTRICT: (내용이 없습니다. 필요하다면 추가 설명을 입력해 주세요.)
- ALTER 문법
- ALTER TABLE <테이블이름> RENAME <기존컬럼이름> TO <새로운컬럼이름>;
- ALTER TABLE <테이블 이름> ADD <컬럼 이름> <타입> DEFAULT <값>;
- ALTER TABLE <테이블 이름> DROP COLUMN <컬럼 이름>;
- ALTER TABLE <테이블 이름> MODIFY/ALTER COLUMN <컬럼 이름> <데이터 타입> <제약 조건>;
- ALTER TABLE <테이블 이름> ADD CONSTRAINT <제약 조건 이름> CHECK (A > 1000);
- ALTER TABLE <테이블 이름> DROP CONSTRAINT <제약 조건 이름>;
- 제약 조건
- CONSTRAINT <이름명명> ~~
- CONSTRAINT <이름명명> PRIMARY KEY(컬럼)
- CONSTRAINT <이름명명> FOREIGN KEY(컬럼) REFERENCES <다른테이블>(pk컬럼)
- ON DELETE/UPDATE SET NULL/CASCADE
데이터 제어어(DCL):
DB 접근 권한에 관한 것
- GRANT SELECT ON 테이블 TO 유저
- REVOKE SELECT ON 테이블 FROM 유저
- GRANT
- PUBLIC을 사용하면 자신에게 허가된 권한을 모든 사용자들에게 허용할 수 있음
- 예: GRANT SELECT ON Employees TO PUBLIC;
- WITH GRANT OPTION: 다른 사용자가 똑같이 그 권한을 부여할 수 있음
- 예: GRANT SELECT ON Employees TO UserA WITH GRANT OPTION;
- PUBLIC을 사용하면 자신에게 허가된 권한을 모든 사용자들에게 허용할 수 있음
- REVOKE
- 권한을 허가한 사용자가 권한을 취소할 수 있음
- 권한을 취소하면, WITH GRANT OPTION을 통해 다른 사용자에게 허가한 권한까지 연쇄적으로 취소
- 예: REVOKE DELETE ON 테이블 FROM 사용자 CASCADE
트랜잭션 제어어(TCL):
- COMMIT
- ROLLBACK
- 61번 TCL 예제
- 트랜잭션 시작: BEGIN TRANSACTION
- 롤백: ROLLBACK
- 저장점 설정: SAVEPOINT
- 커밋: COMMIT
SQL 함수 종류
오라클/SQL Server로 정리
NULL 관련 함수
- NVL/ISNULL(식1, 식2): 식1이 NULL이면 식2값 출력 (단 타입은 동일해야 함)
- NULLIF(식1, 식2): 1,2의 값이 같으면 NULL, 같지 않으면 식1을 반환
- COALESCE(식1, 식2, 식3...): 주어진 식들에서 NULL이면 다음 식을 반환하는 형태. 모든 식이 NULL이면 NULL 반환
단일 행 문자형 함수 종류
- LOWER/UPPER(문자열): 문자열을 대소문자로 바꿈
- ASCII(문자): 문자나 숫자를 아스키 코드 번호로 전환
- CHR/CHAR(ASCII 코드 번호): 반대
- CONCAT(문자열1, 문자열2): 문자열 연결 함수
- 합성 연산자와 동일
- 오라클: 문자열1||문자열2
- SQL: 문자열1 + 문자열2
- 합성 연산자와 동일
- SUBSTR/SUBSTRING(문자열, n, m): n번째에서 m 크기만큼 자르기 (m이 생략되면 문자열 끝까지)
- 문자열 시작은 0이 아닌 1부터 인덱싱함
- LENGTH/LEN(문자열): 문자열 길이 반환
- 지정 문자 제거 함수 ( SQL은 지정 문자 사용 불가, 무조건 띄어쓰기 )
- 지정 문자가 아닌 문자를 만나면 제거를 종료
- LTRIM(문자열, 지정 문자): 문자열 왼쪽 부분의 지정 문자를 제거해주는 함수 (디폴트는 공백)
- RTRIM(문자열, 지정 문자): 오른쪽 부분 제거
- TRIM( <지정 문자 FROM> 문자열 ): 양쪽 부분 제거
- ROUND(소수, n): 소수 n번째 자리까지 반올림 적용
- DATE_FORMAT()
그룹핑 함수
GROUP BY절에 사용하는 함수
- GROUPING SETS : 여러 컬럼의 조합에 대해 선택적으로 그룹핑을 수행.
- 예: GROUPING SETS((Region, Product), (Region), (Product), ())
- ROLLUP : 계층적 그룹핑으로 중간 합계와 총합계를 포함한 결과 생성.
- 예: ROLLUP(Region, Product)
- CUBE : 모든 가능한 컬럼 조합에 대해 그룹핑을 수행하여 모든 조합의 집계 결과를 생성.
- 예: CUBE(Region, Product)
- GROUPING: 해당 컬럼이 총합계 또는 중간 합계를 나타내는 컬럼이면 1을 반환하는 함수
- 주로 위 3개의 그룹 함수에서 CASE와 함께 사용
- 예: CASE GROUPING(월) WHEN 1 THEN '모든 월' ELSE 월 END AS 월
- ROLLUP vs CUBE: 둘 다 2개 이상의 컬럼을 사용하면 소계와 총계를 표시해줌
- ROLLUP( (A, B) )는 하나의 집합으로 간주
집계함수
- 집계함수 sum, min, avg에서 해당 레코드 값이 null인건 그냥 무시하고 집계함
- 집계 함수에 NULL밖에 없다면 결과값은 NULL이 됨
사용가능한 곳
- WHERE 절에는 집계 함수 불가
- HAVING 절에서 집계 함수 사용 가능
윈도우함수
소집합에 대한 계산을 수행하는 함수
- vs 그룹화 함수: 그룹화 함수는 행 수가 줄어들지만, 윈도우 함수는 행 수를 유지
- 목적:
- 이전 행에 대한 누적 합계
- 주어진 기간의 평균
- 각 행에 순위 부여
- 형식: 함수명(열) OVER (PARTITION BY() ORDER BY() <윈도우 범위>)
- PARTITION BY 컬럼: 소그룹으로 나누는 컬럼
- 윈도우 범위( ROWS BETWEEN ~ AND ~ )
- 이전 행 ~ 현재행(디폴트): ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW이다
- 현재행 ~ 마지막 행: ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
- 종류:
- ROW_NUMBER(): 각 행에 고유한 순서 부여
- ROW_NUMBER() OVER (ORDER BY column_name)
- RANK(): 각 행에 순서 번호를 부여
- 동일한 값에는 동일한 값이 부여
- 그 다음 순서는 중복 행의 수를 고려하여 건너뛰기 ( 1,1,3... )
- RANK() OVER (ORDER BY column_name)
- DENSE_RANK(): rank와 동일하지만 약간의 차이 존재
- 건너뛰기 없이 부여 (1,1,2...)
- DENSE_RANK() OVER (ORDER BY column_name)
- NTILE(): 전체 행을 N개의 그룹으로 나누고, 각 행에 그룹번호 부여
- NTILE(2) OVER (ORDER BY column_name) -> (1,1,2,2,3,3...)
- LEAD(): 현재행을 기준으로 지정된 수만큼 뒤에 있는 행의 값을 반환
- LEAD(column_name, 3) OVER (ORDER BY column_name) -> 해당행의 3번째 뒤에 있는 컬럼값을 반환
- LAG(): 현재행을 기준으로 지정된 수만큼 앞에 있는 행의 값을 반환
- LAG(column_name, 3) OVER (ORDER BY column_name) -> 해당행의 3번째 앞에 있는 컬럼값을 반환
- FIRST_VALUE(): 지정된 윈도우의 첫번째 행 값을 반환
- FIRST_VALUE(column_name) OVER (ORDER BY column_name)
- LAST_VALUE(): 지정된 윈도우의 마지막 행 값을 반환
- LAST_VALUE(column_name) OVER (ORDER BY column_name)
- SUM(): 지정된 윈도우 내의 합계를 계산
- SUM(column_name) OVER (ORDER BY column_name)
- AVG(): 지정된 윈도우 내의 합계를 계산
- AVG(column_name) OVER (ORDER BY column_name)
- ROW_NUMBER(): 각 행에 고유한 순서 부여
CASE 쿼리
- 17, 18번 CASE 쿼리
- CASE WHEN <조건> THEN 1 ELSE 0 END
- CASE 컬럼 WHEN 값 THEN 1 ELSE 0 END ( 동등 비교일 때만 사용 가능 )
JOIN 문
- join꿀팁: 항상 N쪽(많은 쪽)에 조건에 맞는것들이 쪽 붙는다고 생각하자! (카디널리티만 아니면)
- 많은 쪽 부분에서 컬럼이 추가된다고 생각!
종류
- LEFT OUTER JOIN B ON 컬럼: 왼쪽 기준 조인 안 되는 건 NULL로 채우고 반환
- RIGHT OUTER JOIN B ON 컬럼: 오른쪽 기준
- FULL OUTER JOIN B ON 컬럼: 양쪽 기준
- INNER JOIN = JOIN: 조건이 맞지 않는 것들은 아예 제외됨
- NATURAL JOIN: 동일 컬럼 기준으로 조인
- SELF JOIN: 한 테이블 내에서 두 칼럼이 연관 관계가 있을 떄 사용
- 하나의 테이블을 논리적으로 분리시켜 동등 조인을 실행하는 것
- CROSS JOIN: 조인 조건이 없는 경우 카디널리티 진행
- Sort Merge JOIN: 두 개의 테이블을 정렬한 후 join을 하는 것
- pk - fk에서 fk쪽에 인덱스가 없는 경우 옵티마이저가 이렇게 진행
특징
- DBMS 옵티마이저는 FROM 절에 테이블을 항상 2개씩 짝지어 JOIN을 함
- 설계상의 이유로 Non EQUI JOIN을 수행하지 못할 수 있음
- 조인이 항상 PK - FK로 조인이 되는 건 아님
- JOIN 조건은 테이블 개수(n) -1로 발생
OUTER JOIN과 ON 의 조건
- ON에 있는 조건은 조인 대상을 제한하는 것
- WHERE처럼 레코드 자체를 제거하는 게 아님
Using 조건을 이용한 join:
공통열을 기준으로 조인
- SELECT *
- FROM 테이블1
- JOIN 테이블2
- USING (공통열);
서브쿼리
FROM 절 서브쿼리
- 인라인 뷰 = 동적 뷰: from절에서 사용되는 서브쿼리
- 서브쿼리 결과가 테이블이 된다고 생각하면 됨 -> 별칭 사용 가능
서브쿼리 종류
- 스칼라 서브쿼리: 하나의 값을 반환하는 서브쿼리이다.
- 예시: SELECT name, (SELECT AVG(salary) FROM employees) AS avg_salary FROM employees;
- 단일 칼럼 서브쿼리: 단일 열을 반환하는 서브쿼리로, 여러 행을 포함할 수 있다.
- 예시: SELECT name
FROM employees
WHERE department_id IN
(SELECT department_id FROM departments WHERE location_id = 1700);
- 예시: SELECT name
- 다중 행 서브쿼리: 여러 행을 반환하는 서브쿼리로, IN, ANY, ALL 등의 연산자와 함께 사용된다.
- 예시: SELECT name FROM employees WHERE salary > ANY (SELECT salary FROM employees WHERE department_id = 30);
- 다중 열 서브쿼리: 여러 열을 반환하는 서브쿼리로, 복합 조건에서 사용된다.
- 예시: SELECT name FROM employees WHERE (department_id, job_id) IN (SELECT department_id, job_id FROM employees WHERE manager_id = 100);
- 상관 서브쿼리: 외부 쿼리의 각 행에 대해 서브쿼리가 반복 실행되는 구조로, 외부 쿼리와 상호 작용한다.
- 예시: SELECT e1.name FROM employees e1 WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e1.department_id = e2.department_id);
- 다중 컬럼 서브쿼리
- 다중 컬럼 서브쿼리는 한 번에 여러 열을 반환하며, 복합 조건에서 사용된다.
- 예시: WHERE (컬럼1, 컬럼2) IN (서브쿼리)
서브쿼리 연산자
- 서브쿼리와 함께 사용되는 연산자들:
- 단일행 비교연산자: =, >, <, >=, <=, <> 등
- 복수행 비교연산자: IN, ALL, ANY, SOME, EXISTS 등
특징
- 다중 컬럼 서브쿼리는 SQL Server에서는 지원 안됨
집합 연산자
집합 연산자 종류
- UNION: 두 개의 SELECT 결과를 합치되 중복은 제거함 (정렬 과정 발생)
- UNION ALL: 중복을 제거하지 않음
- INTERSECT: 두 개의 SELECT 결과에서 교집합
- EXCEPT/MINUS: 첫 번째 SELECT 결과에서 두 번째 SELECT 결과를 뺌
특징
- 집합 연산자 사용 시 마지막 부분에 ORDER BY를 한 번만 사용할 수 있음
- 집합 연산자는 위에서 정의된 순서로 실행이 됨: union all 다음 union이 수행되면 중복이 제거된다.
계층 쿼리
계층 쿼리의 기본 구조
- START WITH : 데이터 전개를 시작할 조건을 지정. ( 부모 컬럼 지정 )
- 예: START WITH employee_id = 100
- CONNECT BY PRIOR : 자식 데이터를 지정.
- 자식 = 부모: 순방향 ( 보스부터 시작 )
- 부모 = 자식: 역방향 ( 자식부터 시작 )
- ORDER SIBLINGS BY : 같은 계층 내에서의 순서를 지정.
- 예: ORDER SIBLINGS BY hire_date
계층 쿼리의 가상 컬럼
- LEVEL :루트 노트 1부터 계층적으로 증가
특징
- connect by절의 조건: 해당 부분에 작성된 부분은 start절 이후에만 해당함
기타
DATE 연산
- 1/24/60 = 1/24 * 60 = 1분
- 1/24/(60/10) = 10/24 * 60 = 10분
- 1/12/60/30 = 30 / 12 * 60 = 60 / 24 * 60 = 60분
테이블 명명 규칙
- 테이블 명은 문자로 시작해야 하며 A,a,0,_,$,# 등의 문자만 허용됨
- DBMS에 따라 길이 제한이 존재할 수 있음
like문자
- %: 여러개 문자(없을 수 도 있음)
- _: 한개 문자
쿼리 팁: 특정 컬럼 값에서 가장 빨리 시작한 거 한 개만 필요할 때
SELECT
기준 컬럼1,MIN(목표 컬럼)
FROM
테이블GROUP BY
기준 컬럼1- (굳이
ORDER BY
+LIMIT
을 사용해줄 필요가 없다)
순수 관계 연산자
관계 대수에서 사용되는 기호
- SELECT (σ):
- PROJECT (π):
- JOIN (⨝):
- DIVIDE (÷):
- UNION (∪):
- INTERSECTION (∩):
- DIFFERENCE (-):
- CARTESIAN PRODUCT (×):
뷰
- 편의성: 복잡한 sql을 뷰를 생성화하여 단순화 할 수 있음
- 독립성: 테이블 구조가 변경되어도 뷰까지 영향이 없음
- 보안성: 숨기고 싶은 정보를 제외하고 뷰를 생성할 수 있음
TOP함수??
- TOP(n): 상위 n개만 출력
- TOP(n) WITH TIES: 동일 값이 있다면 함께 출력
SQL Server vs 오라클
- 인덱스
- SQL Server는 null값을 인덱스 맨 앞에 저장
- Oracle은 맨 뒤에 저장
- ''값
- SQL SERVER: ''
- 오라클: null