카테고리 없음

sqld 정리

초코chip 2024. 8. 23. 18:59

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;
  • 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절에 사용하는 함수

  1. GROUPING SETS : 여러 컬럼의 조합에 대해 선택적으로 그룹핑을 수행.
    • 예: GROUPING SETS((Region, Product), (Region), (Product), ())
  2. ROLLUP : 계층적 그룹핑으로 중간 합계와 총합계를 포함한 결과 생성.
    • 예: ROLLUP(Region, Product)
  3. CUBE : 모든 가능한 컬럼 조합에 대해 그룹핑을 수행하여 모든 조합의 집계 결과를 생성.
    • 예: CUBE(Region, Product)
  4. 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)

 

 

 

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);
  • 다중 행 서브쿼리: 여러 행을 반환하는 서브쿼리로, 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

 

'카테고리 없음'의 다른글

  • 현재글sqld 정리
댓글

티스토리툴바