SQL 기본 및 활용
<SQL 문장 종류>
- DDL : CREATE, ALTER, DROP, RENAME, TRUNCATE
- DML : SELECT, INSERT, UPDATE, DELETE
- DCL(데이터베이스에 접근하고 객체들을 사용하도록 권한 부여, 회수) : GRANT, REVOKE
- TCL(논리적인 작업의 단위를 묶어서 DML에 의해 조작된 결과를 작업단위(트랜잭션)별로 제어) : COMMIT, ROLLBACK
<비절차적/절차적 데이터 조작어>
- 비절차적 데이터 조작어(DML)는 사용자가 무슨(What) 데이터를 원하는지 만을 명세
- 절차적 데이터 조작어는 어떻게(How) 데이터를 접근해야하는지 명세
- 절차적 데이터 조작어 : PL/SQL(오라클), T-SQL(SQL Server) 등이 있다.
<테이블 컬럼 정의 변경(ALTER)>
- 오라클 -> ALTER TABLE 테이블명 MODIFY (컬럼명1 데이터유형 제약조건, ...);
- SQL server -> ALTER TABLE 테이블명 ALTER 컬럼명1 데이터유형 제약조건;
- SQL server에서는 여러개의 컬럼을 동시에 수정 불가, 괄호 사용 X
<테이블명/ 컬럼명>
- 테이블명과 컬럼명은 반드시 문자로 시작해야 한다.
- A~Z, a~z, 0~9, _, #, $만 허용된다.
<참조무결성 규정 - DELETE/MODIFY>
- DELETE FROM 테이블명 WHERE 조건식 ON DELETE CASCADE; 과 같은 형식
- CASCADE : Master 삭제시 Child 같이 삭제(그렇다고 해당 테이블 전부 삭제되는건 아님)
- SET NULL : Master 삭제시 Child 해당 필드 NULL
- SET DEFAULT : Master 삭제시 Child 해당 필드 DEFAULT 값으로 설정
- RESTRICT : Child 테이블에 PK값이 없는 경우만 Master 삭제허용
- NO ACTION : 참조무결성 위반하는 삭제, 수정 액션을 취하지 않음
<참조무결성 규정 - INSERT>
- AUTOMATIC : Master 테이블에 PK가 없는 경우, Master PK를 생성후 Child 입력
- SET NULL : Master 테이블에 PK가 없는 경우, Child 외부키를 NULL값으로 처리
- SET DEFAULT : Master 테이블에 PK가 없는 경우, Child 외부키를 지정된 기본값으로 입력
- DEPENDENT : Master 테이블에 PK가 존재하는 경우에만 Child 입력 허용
- NO ACTION : 참조무결성 위반하는 입력 액션을 취하지 않음
<테이블명 변경>
- RENAME 변경전_테이블명 TO 변경후_테이블명; -> 테이블명 변경
- ALTER TABLE 테이블명 RENAME COLUMN 변경전_컬럼명 TO 변경후_컬럼명; -> 컬럼명 변경
<오류발생하는 경우>
- NOT NULL 제약조건 걸려있는 컬럼을 제외하고 INSERT INTO로 값 넣으면 NULL이 입력되므로 오류 발생
- FK로 참조하는 컬럼에 없는 값을 입력하려하면 오류 발생
- FK 설정시, DELETE SET NULL 제약조건을 걸면 Master 삭제시 Child 해당 필드 NULL이 되는데 그 컬럼 제약조건에 NOT NULL이 있다면 오류 발생
<DROP, TRUNCATE, DELETE>
- DROP, TRUNCATE는 AUTO COMMIT되고, DELETE는 사용자가 COMMIT 수행한다.
- 동일 데이터량 삭제시 TRUNCATE가 DELETE보다 빠르다.
<트랜잭션 고립성 낮을 경우>
- Dirty Read : 다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 데이터를 읽는 것
- Non-Repeatable Read : 한 트랜잭션 내에서 같은 쿼리를 두번 수행했는데, 그 사이 다른 트랜잭션이 값을 수정 또는 삭제하는 바람에 두 쿼리 결과가 다르게 나타나는 현상
- Phantom Read : 한 트랜잭션 내에서 같은 쿼리를 두번 수행했는데, 첫번째 쿼리에서 없던 유령 레코드가 두번째 쿼리에서 나타나는 현상
<자동 커밋>
- ORACLE에서는 DDL 문장 수행 후 자동으로 COMMIT 수행한다(<->SQL Server).
<논리연산자 우선순위>
- NOT > AND > OR 순이다.
<NULL>
- ORACLE에서는 NULL조회할려면 WHERE 컬럼명 IS NULL;
- SQL Server에서는 NULL조회할려면 WHERE 컬럼명 = '';로 해야한다.
- IN (NULL)로 하여도 널 출력되지 않는다.
<내장함수>
- 함수는 내장함수와 사용자정의함수로 나눌 수 있다.
- 내장함수는 입력 행수에 따라 단일행 함수와 다중행 함수로 나눌 수 있다.
- 다중행 함수는 집계함수, 그룹함수, 윈도우함수로 구분된다.
- 단일행 함수는 SELECT, WHERE, ORDER BY, UPDATE의 SET절에 사용가능하다.
- 1:M 조인이라고 하더라도 M쪽에서 출력된 행이 하나씩 단일행 함수의 입력값으로 사용가능
- 다중행 함수도 단일행 함수와 동일하게 단일 값만을 반환한다.
<함수>
- LENGTH : 문자열의 길이를 반환하는 함수. A 줄바꿈 A의 길이는 3, B 줄바꿈 B 줄바꿈 B의 길이는 5
- CHR : 주어진 ASC코드에 대한 문자를 반환하는 함수. CHR(10) -> 줄바꿈을 의미
- REPLACE : 문자열을 치환하는 함수. REPLACE(컬럼명, '문자1', '문자2')형식이므로 REPLACE(컬럼명1, CHR(10)) -> 줄바꿈 제거를 의미
- 오라클에서 날짜연산은 숫자연산과 동일. 1/24/60 = 1분을 의미 -> 1/24/(60/10) = 10분
- 통계함수에서는 NULL 제외된다!!! (AVG, COUNT, MAX, MIN, SUM)
<CASE>
- 쿼리 안에 조건문을 의미
- CASE 컬럼명 WHEN 조건 THEN 내용 END
- CASE WHEN 컬럼명 조건 THEN 내용 END
<GROUP BY>
- 속성값을 그룹으로 분류하고자 할 때 사용
- GROUP BY 컬럼명1, ... : 컬럼명1로 그룹을 만든다.
- SELECT 절에 그룹함수(통계함수) 사용시, 일반컬럼은 반드시 GROUP BY 절에 기술해야 한다.
- SELECT GRADE, COUNT(*) FROM EMP GROUP BY GRADE; -> 사원, 대리, 과장, 차장, 부장, NULL 6개의 데이터 출력
- 중첩된 그룹함수 결과값이 1개인 경우 오류발생. ex) AVG(COUNT(*))는 결과가 1개이므로 GROUP BY 사용시 오류
- GROUP BY 없이 HAVING절 사용가능. 이때는 반드시 HAVING절에 그룹함수(통계함수) 사용해야 한다.
<ORDER BY>
- SELECT절에 기술되지 않은 컬럼으로도 정렬가능
- 다만, GROUP BY이 먼저 수행된 경우, SELECT절에 기술되지 않고 COUNT(*)같은 경우가 아니면 오류 발생
- DBMS마다 NULL값에 대한 정렬 순서가 다를 수 있으므로 주의
- ORDER BY절에서 컬럼명 대신 Alias명이나 컬럼 순서를 나타내는 정수로 사용가능하며, 혼용하여 사용가능
<TOP(N)>
- 가장 큰 값 N개를 출력
- SQL Server의 TOP N 질의문에서 N에 해당하는 값이 동일한 경우 함께 출력할려면, TOP(N) WITH TIES 옵션을 ORDER BY절과 함께 사용하여야 한다.
<JOIN>
- 일반적으로 PK와 FK값의 연관성에 의해 성립된다.
- EQUI JOIN은 JOIN에 관여하는 테이블 간의 컬럼 값들이 정확하게 일치하는 경우 사용. = 연산자에 의해서 수행
- 대부분 Non EQUI JOIN을 수행할 수 있지만, 때로는 설계상의 이유로 수행 불가능한 경우도 있다.
- DBMS 옵티마이저는 From절에 나열된 테이블이 아무리 많아도 항상 2개의 테이블씩 짝지어 JOIN 수행
- 공통 존재 컬럼의 값이 같은 경우 -> INNER JOIN
- LEFT OUTER JOIN 은 왼쪽 데이터와 일치하는 오른쪽 데이터 제외하고는 오른쪽 데이터 NULL값 된다.
- USING절 : 조인 사용시, 테이블 간에 동일한 이름과 형식의 컬럼이 있는 경우 사용. 다만, 테이블 이름과 같은 접두사 붙일 수 없다. -> USING(A.NAME = B.NAME) 불가 USING(NAME)으로 해야한다.
- CROSS JOIN : PRODUCT개념으로 조인조건 없는 경우 생길 수 있는 모든 데이터의 조합
- FULL OUTER JOIN : 좌측, 우측 테이블의 모든 데이터를 읽어 JOIN하여 결과 생성, RIGHT OUTER JOIN과 LEFT OUTER JOIN을 UNION한 결과와 동일
<순수 관계 연산자>
- SELECT, PROJECT, JOIN, DIVIDE
<EXIST절>
- EXISTS(서브 쿼리)는 서브 쿼리의 결과가 "한 건이라도 존재하면" TRUE, 없으면 FALSE를 리턴
- NOT EXIST절은 그 반대
<WHERE절과 ON절 차이>
- ON절은 조인조건을 의미하므로 모든 고객에 대해 출력을 하되, 조인 대상 데이터를 ON절로 제한하는 것이다.
- WHERE절은 출력자체를 제한한다.
<SET OPERATOR>
- UNION : 중복을 제외한 합집합, 테이블1과 테이블2 UNION하면 중복 전부 제외된다.
- UNION ALL : 중복을 포함한 합집합
- INTERSECT : 교집합
- MINUS/EXCEPT : 차집합
- 두 테이블이 1:1이며 양쪽 필수관계를 시스템적으로 보장한다면, 두 테이블 간 차집합 결과는 항상 공집합
- 두 테이블이 1:1이며 양쪽 필수관계를 시스템적으로 보장한다면, UNION 수행결과는 한 테이블 건수와 동일
- 두 테이블이 1:1이며 양쪽 필수관계를 시스템적으로 보장한다면, 교집합 결과는 UNION 수행결과와 동일
<START WITH절>
- START WITH 최상위 조건 : 계층구조 전개의 시작위치를 지정
- 루트노드의 LEVEL값은 1이다.
- START WITH의 대상이 2개 이상이 되면, 애네도 ORDER SIBLINGS BY절에 적용된다.
- 001 홍길동 NULL / 005 남도일 NULL이 있으면 001부터 CONNECT BY절 적용하기 -> 홍길동과 남도일 사이에 존재하게 된다.
<CONNECT BY절>
- 이 절을 이용하여 계층 질의에서 상위계층(부모행)과 하위계층(자식행)의 관계를 규정 할 수 있다.
- PRIOR 연산자와 함께 사용하여 계층구조로 표현할 수 있다.
- CONNECT BY PRIOR 자식컬럼=부모컬럼 : 부모에서 자식으로 트리구성(Top Down), 순방향
- CONNECT BY PRIOR 부모컬럼=자식컬럼 : 자식에서 부모로 트리 구성(Bottom Up)->부모컬럼과 같은 자식컬럼 추출
- CONNECT BY NOCYCLE PRIOR : NOCYCLE 파라미터를 이용하여 무한루프 방지
<ORDER SIBLINGS BY>
- 형제노드(동일 LEVEL)사이에서 정렬을 수행
<계층형 질의문>
- 테이블에 계층형 데이터(상위, 하위가 있는 데이터)가 존재하는 경우, 데이터 조회를 위해 계층형 질의문 사용한다.
- SQL Server의 계층형 질의문은 CTE(Common Table Expression)를 재귀호출함으로써 계층구조를 전개
- SQL Server의 계층형 질의문은 앵커멤버를 실행하여 기본결과집합을 만들고 이후 재귀멤버를 지속적 실행
- Oracle의 계층형 질의문에서 WHERE절은 모든 전개 진행한 후, 필터조건으로서 조건을 만족하는 데이터만 추출
- Oracle의 계층형 질의문에서 PRIOR키워드는 CONNECT BY, SELECT, WHERE절에서도 사용할 수 있다.
