[SQLD] SQL 기본 및 활용

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절에서도 사용할 수 있다.