[SQLD] SQL 요약

DCL

데이터베이스 사용자에게 권한을 부여/회수하는 언어

• GRANT : 권한 부여

```sql
GRANT 권한
ON 테이블
TO 유저;
```

• REVOKE : 권한 회수

  REVOKE 권한
  ON 테이블
  FROM 유저;

• 권한의 종류
  • SELECT, INSERT, UPDATE, DELETE
  • REFERENCES, ALTER, INDEX
  • ALL
• GRANT 옵션

  TO 유저
  WITH GRANT OPTION;

  • 특정 사용자에게 권한 부여가능한 권한을 부여함

  • 엄마가 회수될때 자식도 회수

    TO 유저
    WITH ADMIN OPTION;

  • 테이블에 대한 모든 권한 부여

  • 엄마의 권한 회수는 나랑 상관없음

DDL

데이터를 보관하고 관리하기 위한 객체의 구조를 정의하기 위한 언어

CREATE

데이터베이스 상 테이블 구조 생성

CREATE TABLE USER (
	이름 varchar2(10) NOT NULL,
	생년 number(4) NOT NULL DEFAULT 9999,
	phone varchar(2) NOT NULL,
	첫방문일 date,
	고객번호 varchar2(10) PRIMARY KEY,
);

• 컬럼명 : 영문, 한글, 숫자 모두 가능 → 시작만 문자로
  
• 데이터 타입
  • number : 숫자형
  • date : 날짜형
  • varchar2 : 가변길이 문자열 ex)'호호' -> '호호'
  • char : 고정된 크기 문자열 할당된 길이만큼 문자 채움 ex) ‘호호’ → ‘ 호호’ → varchar과 char의 차이점 : char은 할당된 공간만큼 빈 문자열을 채움 → 비교연산시 똑같은 문자열이 저장되어도 FALSE 반환
    
• 제약조건(CONSTRAINT)
  • DEFAULT : 기본값 지정
  • NOT NULL
  • UNIQUE
  • PRIMARY KEY : 기본키로 지정
    • PK는 not null, unique 포함
  • FOREIGN KEY : 외래키 지정

ALTER

테이블과 컬럼에 대해 이름 치 속성 변경, 추가/삭제 등 구조 수정을 위해 사용

• 테이블명 변경

```sql
ALTER TABLE MENU RENAME TO ho_MENU;
```

• 컬럼명 변경

  ALTER TABLE MENU RENAME COLUMN phone TO 전화번호;

• 컬럼 속성 변경

  ALTER TABLE MENU MODIFY (이름 varchar(20) not null);

• 컬럼 추가

  ALTER TABLE MENU ADD (거주지역 varchar(10))

• 컬럼 삭제

  ALTER TABLE MENU DROP COLUMN 이름;

• 제약 조건 추가/삭제

  ALTER TABLE MENU ADD CONSTRAINT;
  ALTER TABLE MENU DROP CONSTRAINT;

• 테이블명 변경 → 다수 테이블명 동시에 변경 가능

  RENAME TABLE MENU TO ho_MENU;

DROP

테이블 및 컬럼 삭제

DROP TABLE MENU

유의사항

DROP TABLE MENU CASCADE CONSTRAINT;

• 해당 테이블의 데이터를 외래키로 참조한 제약사항도 모두 삭제

TRUNCATE

TRUNCATE TABLE MENU

→ 테이블 데이터만 삭제

DML

정의된 데이터베이스에 레코드를 입력하거나, 수정, 삭제 및 조회하기 위한 명령어다.

INSERT

INSERT INTO MENU (NAME) VALUES ('연어스시');

UPDATE

UPDATE MENU SET discount_rate = 10
WHERE name = '연어스시';

DELETE

DELETE FROM MENU
WHERE name = '연어스시';

• 삭제된 데이터에 대해 로그를 남길 수 있는 방법
• 삭제된 데이터를 다시 되돌릴 수 있음
• 데이터는 삭제되지만 용량은 줄어들 지 않음

DROP vs TRUNCATE vs DELETE

• DROP : 시그마, 데이터 둘다 삭제
• TRANCATE : 데이터 삭제
• DELETE : 데이터 삭제 (용량은 줄어들지 않음), 복구가능(commit 되기 전 rollback으로)

SELECT

SELECT 컬럼명
FROM 테이블명
WHERE 조건
GROUP BY 그룹화할 컬럼
HAVING 그룹핑된 후 조건
ORDER BY 컬럼명

• 연산 순서
  • FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
• DISTINCT : 중복을 제거한 값 반환

  • NULL은 제외안함

    SELECT DISTINCT 성별
    FROM C_INFO

    SELECT DISTINCT 성별, 연령대
    FROM C_INFO

    → (성별, 연령대)를 합해서 중복제거

문자형 함수 (SELECT, WHERE)

• LOWER(문자열) : 소문자 변환
• UPPER(문자열) : 대문자 변환
• CONCAT(문자열1, 문자열2) : ex) CONCAT(”LOVE”, “YOU”) → “LOVEYOU” 문자열1 + 문자열2 : “LOVE” + “YOU” → “LOVEYOU” 문자열1 || 문자열2 : “LOVE” || “YOU” → “LOVEYOU”
  
• SUBSTR(문자열, m, n) : 문자열에서 m번째 자리값부터 n개를 자른다. ex)SUBSTR(KATE, 2, 2) → “AT” SUBSTRING(문자열, m, n) : 문자열에서 m번째 자리값부터 n개를 자른다. ex)SUBSTRING(KATE, 2, 2) → “AT”
  
• LEN(문자열), LENGTH(문자열) : 문자열 길이 반환(공백포함)

---

• TRIM(문자열, 제거대상) : 문자열 왼쪽과 오른쪽에서 제거대상 제거
• LTRIM(문자열, 제거대상) : 문자열 왼쪽에서 제거대상 제거
• RTRIM(문자열, 제거대상) : 문자열 오른쪽에서 제거대상 제거

위 3개 함수는 제거대상을 인자로 안 넘기면 공백을 제거함.

숫자형 함수(SELECT, WHERE)

• ROUND(숫자, 소수점 자릿수) : 반올림
• TRUNC(숫자, 소수점 자릿수) : 버림
• CEIL(숫자) : 올림
• FLOOR(숫자) : 내림
• MOD(분자, 분모) : 나머지 연산
• SIGN(숫자) : 숫자가 양수면 1, 0이면 0, 음수면 -1
• ABS(숫자) : 절댓값

날짜형 함수(SELECT, WHERE)

• SYSDATE : 현재 시간&날짜 출력

명시적 / 암시적 형변환(SELECT, WHERE)

• TO_NUMBER(문자열) : ex) TO_NUMBER(’2022’) → 2022
• TO_CHAR(숫자 or 날짜. 포맷) : ex) TO_CHAR(200) → ‘200’
• TO_DATE(문자열, 포맷)

NULL 관련 함수(SELECT, WHERE)

• NVL(컬럼, “빈 값”), ISNULL(컬럼, “빈 값”) : 컬럼의 값이 NULL, 일 경우 두번째 인자로 치환
• NULLIF(표현식1, 표현식2) : 표현식1과 표현식2가 같으면 표현식1 반환, 같지 않으면 NULL 반환
• COALESCE(표현식1, 표현식2, ...) : NULL이 아닌 것 반환, 전부 NULL이면 NULL 반환

ROWNUM, TOP

임시로 부여되는 일련번호

• ROWNUM

  # ex) 한건의 행만 가져오고 싶을 때
  SELECT *
  FROM MEMBER
  WHERE ROWNUM == 1;
  
  SELECT *
  FROM MEMBER
  WHERE ROWNUM < 2;
  
  # ex) 두 건 이상의 N 행을 가져오고 싶을 때
  SELECT *
  FROM MEMBER
  WHERE ROWNUM <= N;
  
  SELECT *
  FROM MEMBER
  WHERE ROWNUM < N+1;

• TOP

  # ex) 한건의 행만 가져오고 싶을 때
  SELECT TOP(1)
  FROM MEMBER
  
  # ex) 두 건 이상의 N 행을 가져오고 싶을 때
  SELECT TOP(N)
  FROM MEMBER

집계함수(SELECT, HAVING, ORDER BY)

• COUNT(*) : NULL 값을 포함한 행의 수를 출력
• COUNT(표현식) : NULL 값을 제외한 행의 수를 출력
• SUM(표현식) : 표현식의 NULL 값을 제외한 합계를 출력
• AVG(표현식) : 표현식의 평균 출력
• MAX(표현식) : 표현식의 최대값 출력
• MIN(표현식) : 표현식의 최소값 출력

TCL

트랜잭션을 제어하기 위한 언어

** 트랜잭션 ** : 데이터베이스의 상태를 변화시키기 위해 수행하는 작업의 단위

COMMIT

데이터에 대한 변화를 DB에 반영하기 위한 명령어

ROLLBACK

트랜잭션이 시작되기 이전이 상태로 되돌리기 위한 언어

최신 COMMIT이나 특수한 SAVEPOINT로 되롤릴 수 있는 명령어

SAVEPOINT

코드를 분할하기 위한 저장 포인트 지정

계층형 질의

계층형 데이터를 다루는 쿼리를 수행하는 것

** 계층형 데이터 :** 동일한 테이블에 계층적으로 상위와 하위 데이터가 포함되어진 데이터

SELECT ...
FROM ...
WHERE 조건
START WITH 조건
CONNECT BY <NOCYCLE> 조건
[ ORDER SIBLINGS BY 컬럼명1, 컬럼명2, ...];

• START WITH : 루트노드의 조건(시작점 지정)

• CONNECT BY : 상하 계층이 이루어지는 조건

• PRIOR : CONNECT BY 절에서 사용

  • CONNECT BY PRIOR 자식 = 부모 : 부모에서 자식으로 전개 (순방향전개)
  • CONNECT BY 자식 = PRIOR 부모 : 자식에서 부모로 전개 (역방향전개)
  • CONNECT BY PRIOR 부모 = 자식 : 자식에서 부모로 전개 (역방향전개)
  • CONNECT BY 부모 = PRIOR 자식 : 부모에서 자식으로 전개 (순방향전개)

• NOCYCLE : 데이터를 전개하는 도중에 동일한 데이터가 다시 나타나면 CYCLE 발생 → 런타임 오류 발생 NOCYCLE 추가시 런타임 오류 방지

• ORDER SIBLINGS BY : 동일한 LEVEL을 가진 노드 (형제 노드) 사이에서 정렬을 수행

• WHERE : 모든 데이터 전개를 수행한 후 지정된 조건을 만족하는 데이터만을 추출

• 계층형 질의에서 자주 사용되는 가상 컬럼

  • LEVEL : 전개 과정에서 루트 데이터 1, 그 하위 데이터면 2, 루트에서 리프로 내려갈 때 1씩 증가
  • CONNECT_BY_ISLEAF : 리프 데이터면 1, 그렇지 않으면 0
  • CONNECT_BY_ISCYCLE : 해당 데이터가 자식이 존재하면 1, 그렇지 않으면 0

• 계층형 질의에서 자주 사용되는 함수

  • SYS_CONNECT_BY_PATH(컬럼명, 경로분리기호) :
  • CONNECT_BY_ROOT : 최상위 데이터 표시

그룹함수

윈도우(WINDOW) 함수

WINDOW_FUNCTION OVER (<PARTITION BY 칼럼> <ORDER BY 절> <WINDOWING 절>)

• OVER : 윈도우 함수가 적용될 계산
• (<PARTITION BY 칼럼> <ORDER BY 칼럼> <WINDOWING 절>) : 윈도우 함수 지정 범위
• 행을 분할 : PARTITION BY (GROUP BY 같은 역할)
• 행을 정렬 : ORDER BY (ORDER BY 역할)
• 행을 지정 : ROWS 또는 RANGE (WHERE의 역할)
  • ROWS : 행의 수를 선택할 때 사용
    • UNBBOUNDED PRECEDING : 맨 위에서부터 현재
    • PRECEDING : 이전의 행
      • ex) 1 PRECEDING : 한 행 이전, 2 PRECEDING : 두 행 이전
    • CURRENT ROW : 현재 행
    • FOLLOWING : 다음 나오는 행
      • ex) 1 FOLLOWING : 한 행 후, 2 FOLLOWING : 두 행 이전
    • UNBOUNDED FOLLOWING : 맨 아래서부터 현재 행까지
  • RANGE : 값의 범위를 선택할 때 사용
    • UNBBOUNDED PRECEDING : 현재 행보다 작거나 같은 행
    • PRECEDING : 현재 행의 값보다 작거나 같고 차이가 ~ 이하인 행
      • ex) 150 PRECEDING : 현재 값보다 작거나 같고 차이가 150이하 인 행
    • CURRENT ROW : 현재 행
    • FOLLOWING : 현재 행의 값보다 크거나 같고 차이가 ~ 이하인 행
      • ex) 150 FOLLOWING : 현재 값보다 크거나 같고 차이가 150이하인 행
    • UNBOUNDED FOLLOWING : 현재 행보다 크거나 같은 행
  • 예제
    • ROWS UNBOUNDED PRECEDING ~ CURRENT ROW : 맨 위부터 현재까지
    • ROWS CURRENT ROW ~ UNBOUNDED FOLLOWING : 현재부터 맨 아래까지
    • ROWS UNBOUNDED PRECEDING : 맨 위부터 현재행
    • ROWS UNBOUNDED FOLLOWING : 현재부터 맨 아래까지
    • ROWS 1 PRECEDING : 한 칸 위 부터 현재까지
    • ROWS 2 FOLLOWING : 현재부터 두 칸 아래까지
    • ROWS BETWEEN A BETWEEN B : 사이
      • ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING : 한칸 위부터 한칸 아래까지
    • RANGE UNBOUNDED PRECEDING ~ CURRENT ROW : 현재 행보다 작거나 같은 행
    • RANGE CURRENT ROW ~ UNBOUNDED FOLLOWING : 현재 행보다 크거나 같은 행
    • RANGE UNBOUNDED PRECEDING : 현재 행보다 작거나 같은 행
    • RANGE UNBOUNDED FOLLOWING : 현재 행보다 크거나 같은 행
    • RANGE 150 PRECEDING : 현재 행보다 작거나 같고 차이가 150이하 ex: 1000 : 850 ~ 1000
    • RANGE 150 FOLLOWING : 현재 행보다 크거나 같고 차이가 150이하 ex: 1000 : 1000 ~ 1150
    • RANGE BETWEEN A BETWEEN B : 사이
      • ROWS BETWEEN 150 PRECEDING AND 150 FOLLOWING : 1000 → 850 ~ 1150

순위 함수

• RANK

  SELECT ENAME, SAL, RANK() OVER (ORDER BY SAL) AS RANK

  • ORDER BY 기준으로 RANKING 선정
  • 공동 순위 허용, 다음 등수 삭제
    • ex) 1000, 1100, 1100, 1200 → 1, 2, 2, 4
• DENSE_RANK

  SELECT ENAME, SAL, DENSE_RANK() OVER (ORDER BY SAL) AS RANK

  • ORDER BY 기준으로 RANKING 선정
  • 공동 순위 허용
    • ex) 1000, 1100, 1100, 1200 → 1, 2, 2, 3
• ROW_NUMBER

  SELECT ENAME, SAL, ROW_NUMBER() OVER (ORDER BY SAL) AS RANK

  • ORDER BY 기준으로 RANKING 선정
  • 행의 번호를 기준으로 순위 지정
    • ex) 1000, 1100, 1100, 1200 → 1, 2, 3, 4

윈도우 집계 함수

• SUM
• MAX
• MIN
• AVG
• COUNT

행 순서 함수

• FIRST_VALUE : 첫째 행 표시
• LAST_VALUE : 마지막 행 표시
• LAG : 이전 행 표시
• LEAD : 다음 행 표시

비율 함수

• RATIO_TO_REPORT : 누적 백분율
• PERCENT_RANK
• CUME_DIST : 누적 백분율
• NTILE