AniBumiRami의 블로그

2-1. 표준 조인

1. STANDARD SQL 개요

  • 대표적인 ANSI/ISO 표준 SQL 기능

1. STANDARD JOIN 기능 추가 (CROSS, OUTER JOIN 등 새로운 FROM 절 JOIN 기능들)

2. SCALAR SUBQUERY, TOP-N QUERY 등의 새로운 SUBQUERY 기능들

3. ROLLUP, CUBE, GROUPING SETS 등의 새로운 리포팅 기능

4. WINDOW FUNCTION 같은 새로운 개념의 분석 기능들

  가. 일반 집합 연산자

  나. 순수 관계 연산자

2. FROM 절 JOIN 형태

  • ANSI/ISO SQL 에서 표시하는 FROM 절의 JOIN 형태는 다음과 같다

§ -INNER JOIN -NATURAL JOIN - USING 조건절 -ON 조건절 -CROSS JOIN -OUTER JOIN

3. INNER JOIN

  • 내부 JOIN이라고 하며 JOIN조건에서 동일한 값이 있는 행만 반환한다. 

  • USING 조건절이나 ON 조건절을 필수적으로 사용해야 한다.

4. NATURAL JOIN

  • 두 테이블 간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI(=) JOIN을 수행한다.

  • USING 조건절, ON 조건절, WHERE 절에서 JOIN 조건을 정의할 수 없다.

  • SQL Server 에서는 지원하지 않는다.

5. USING 조건절

  • 같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해서만 선택적으로 EQUI JOIN을 할 수 있다.

  • SQL Server 에서는 지원하지 않는다.

  • 예) SELECT * FROM DEPT JOIN DEPT_TEMP USING (DEPTNO);

6. ON 조건절

  • 칼럼명이 다르더라도 JOIN 조건을 사용할 수 있다.

7. CROSS JOIN

  • 일반 집합 연산자의 PRODUCT의 개념으로 테이블 간 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합.

8. OUTER JOIN

  • JOIN 조건에서 동일한 값이 없는 행도 반환할 때 사용할 수 있다.

9. INNER VS OUTER VS CROSS JOIN 비교

2-2. 집합 연산자

2-3. 계층형 질의와 셀프 조인

1. 계층형 질의

  가. ORACLE 계층형 질의

§ 예) SELECT CONNECT_BY_ROOT 사원 루트사원, SYS_CONNECT_BY_PATH(사원, '/') 경로, 사원, 관리자 FROM 사원 START WITH 관리자 IS NULL CONNECT BY PRIOR 사원 = 관리자;

2. 셀프 조인

  • 동일 테이블 사이의 조인

  • 테이블과 칼럼 이름이 모두 동일하기 때문에 식별을 위해 반드시 테이블 별칭(ALIAS) 를 사용해야 한다.

출처 : SQL 전문가 가이드 교재

1-5. Where 절

1. WHERE 조건절 개요

• 기본구조 : SELECT [DISTINCT/ALL] 칼럼명 [ALIAS명] FROM 테이블 명 WHERE 조건식;

2. 연산자의 종류

3. 비교 연산자

4. SQL 연산자

5. 논리 연산자

6. 부정 연산자

7. ROWNUM, TOP 사용

1. ROWNUM

1) 오라클에서 사용

2) SQL 처리 결과 집합의 각 행에 대해 임시로 부여되는 일련번호

3) 테이블이나 집합에서 원하는 만큼의 행만 가져오고 싶을 때 WHERE 절에서 행의 개수를 제한하는 목적으로 사용

2. TOP 절

1) SQL Server 는 TOP 절을 사용하여 결과 집합으로 출력되는 행의 수를 제한할 수 있다.

2) 표현식 : TOP(Expression) [PERCENT] [WITH TIES]

a) Expression : 반환할 행의 수를 지정하는 숫자

b) PERCENT : 쿼리 결과 집합에서 처음 Expression의 행만 반환됨을 나타낸다.

c) WITH TIES : ORDER BY 절이 지정된 경우에만 사용할 수 있으며, TOP N(PERCENT)의 마지막 행과 같은 값이 있는 경우 추가 행이 출력되도록 지정할 수 있다.

1-6. 함수(Function)

1. 내장 함수 (BULIT-IN FUNCTION) 개요

2. 문자형 함수

3. 숫자형 함수

4. 날짜형 함수

5. 변환형 함수

6. CASE 표현

7. NULL 관련 함수

1-7. Group by, Having 절

1. 집계 함수 (Aggregate Function)

2. GROUP BY 절

§ WHERE 절을 통해 조건에 맞는 데이터를 조회했지만 2차 가공 정보도 필요하다.

GROUP BY 절은 SQL 문에서 FROM 절과 WHERE 절 뒤에 오며, 데이터들을 작은 그룹으로 분류하여 소그룹에 대한 항목별로 통계 정보를 얻을 때 추가로 사용한다.

3. HAVING 절

§ HAVING 절은 WHERE 절과 비슷하지만 그룹을 나타내는 결과 집합의 행의 조건이 적용된다는 점에서 차이가 있다.

§ 예) SELECT POSITION, ROUND(AVG(HEIGHT), 2) FROM PLAYER GROUP BY POSITION HAVING AVG(HEIGHT) >= 180;

4. CASE 표현을 활용한 월별 데이터 집계

• 모델링의 제1정규화로 인해 반복되는 칼럼의 경우 구분 칼럼을 두고 여러 개의 레코드로 만들어진 집합을, 정해진 칼럼 수만큼 확장해서 집계 보고서를 만드는 유용한 기법이다.

5. 집계 함수와 NULL

• 다중 행 함수를 사용하는 경우 NVL 함수를 다중 행 함수 안에 사용할 필요가 없다.

• CASE나 DECODE의 DEFAULT 값은 NULL 인데 NVL 를 이용하여 0(ZERO) 를 적용하면 합계나 평균을 구할 때 불필요한 연산이 추가된다.

1-8. Order by 절

1. ORDER BY 정렬

• SQL 문장으로 조회된 데이터들을 다양한 목적에 맞게 특정 칼럼을 기준으로 정렬하여 출력하는데 사용한다.

• 칼럼명 대신에 SELECT 절에서 사용한 ALIAS명이나 칼럼 순서를 나타내는 정수도 사용 가능하다.

• 기본은 오름차순, SQL문장의 제일 마지막에 위치한다.

• ORACLE에서는 NULL 값이 내림차순의 제일 먼저 위치한다. (제일 큰 값으로 취급)

2. SELECT 문장 실행 순서

•  5. SELECT 칼럼명 [ALIAS명] 1. FROM 테이블명 2. WHERE 조건식 3. GROUP BY 칼럼(COLUMN)이나 표현식 4. HAVING 그룹조건식 6. ORDER BY 칼럼이나 표현식;

3. TOP N 쿼리

• ORACLE의 경우 정렬이 완료된 후 데이터의 일부가 출력되는 것이 아니라, 데이터의 일부가 먼저 추출된 후 (ORDER BY 절은 결과 집합을 결정하는데 관여하지 않음) 데이터에 대한 정렬 작업이 일어나므로 주의해야 한다.

• SQL Server 는 TOP 조건을 사용하면 별도 처리 없이 관련 ORDER BY 절의 데이터 정렬 후 원하는 일부 데이터만 쉽게 출력할 수 있다.

• 예) SQL Server : SELECT TOP(2) ENAME, SAL FROM EMP ORDER BY SAL DESC;

1-9. 조인(Join)

1. JOIN 개요

• 두 개 이상의 테이블을 연결하여 데이터를 출력하기 위해 JOIN을 실행한다.

• 많은 테이블을 조인 처리를 할 때 SQL 에서 데이터를 처리할 때는 단 두 개의 집한 간에 조인이 일어나고, 2개의 테이블이 조인되어서 처리된 새로운 데이터 집합과 다른 테이블이 차례로 조인된다.

2. EQUI JOIN

• 두 개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하는 경우에 사용하는 방법

• 기본구조 : SELECT 테이블1.칼럼명, 테이블2.칼럼명, … FROM 테이블1, 테이블2 WHERE 테이블1.칼럼명 = 테이블2.칼럼명;

• ANSI : SELECT 테이블1.칼럼명, 테이블2.칼럼명, .. FROM 테이블1 [INNER] JOIN 테이블2 ON 테이블1.칼럼명 = 테이블2.칼럼명;

3. NON EQUI JOIN

• 두 개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하지 않는 경우에 사용된다.

• 예) SELECT 테이블1.칼럼명, 테이블2.칼럼명, .. FROM 테이블1, 테이블2 WHERE 테이블1.칼럼명 BETWEEN 테이블2.칼럼명1 AND 테이블2.칼럼명2;

4. 3개 이상 TABLE JOIN

• 예) SELECT 테이블1.칼럼명, 테이블2.칼럼명, 테이블3.칼럼명, .. FROM 테이블1, 테이블2, 테이블3 WHERE 테이블1.칼럼명 = 테이블2.칼럼명 AND 테이블2.칼럼명 = 테이블3.칼럼명;

출처 : SQL 전문가 가이드 교재

1-1. 관계형 데이터베이스 개요

1. 데이터베이스

2. SQL (Structured Query Language)

1. 관계형 데이터베이스에서 데이터 정의, 데이터 조작, 데이터 제어를 하기 위해 사용하는 언어

2. SQL 문장들의 종류

3. TABLE

1. 테이블 용어

2. 테이블 관계 용어들

4. ERD (Entity Relationship Diagram)

1-2. DDL(Data Definition Language)

1. 데이터 유형

2. CREATE TABLE

  가. 테이블과 칼럼 정의

  나. CREATE TABLE

1. 테이블 생성하는 구문 형식

• create table 테이블명 (칼럼명1 data_type [default 형식], 칼럼명2 data_type [default 형식] …);

2. 테이블 생성시 주의사항

1) 테이블 명은 객체를 의미할 수 있는 적절한 이름을 사용한다.

2) 테이블 명은 다른 테이블의 이름과 중복되지 않아야 한다.

3) 한 테이블 내에서는 칼럼명이 중복되게 지정할 수 없다.

4) 각 칼럼들은 콤마 ","로 구분되고, 테이블 생성문의 끝은 항상 세미콜론 ";"으로 끝난다.

5) 칼럼 뒤에 데이터 유형은  꼭 지정되어야 한다.

6) 테이블 명과 칼럼 명은 반드시 문자로 시작해야 하고, 벤더별로 길이에 대한 한계가 있다.

  다. 제약조건 (CONSTRAINT)

  라. 생성된 테이블 구조 확인

1) ORACLE : DESC 테이블명;

2) SQL Server : sp_help 'dbo.테이블명'

  마. SELECT 문장을 통한 테이블 생성 사례

• CTAS : Create Table ~ As Select ~

• 예) ORACLE : Create Table team_temp as select * from team;

• 예) SQL Server : select * into team_temp from team;

3. ALTER TABLE

• 테이블 구조 변경

  가. ADD COLUMN

• 기본구조 : ALTER TABLE 테이블명 ADD 추가할 칼럼명 데이터 유형;

• 예) ORACLE : ALTER TABLE PLAYER ADD (ADDRESS VARCHAR2(80));

• 예) SQL Server : ALTER TABLE PLAYER ADD ADDRESS VARCHAR(80);

  나. DROP COLUMN

• 기본구조 : ALTER TABLE 테이블명 DROP COLUMN 삭제할 칼럼명;

• 예) ORACLE : ALTER TABLE PLAYER DROP COLUMN ADDRESS;

• 예) SQL Server : ALTER TABLE PLAYER DROP COLUMN ADDRESS;

  다. MODIFY COLUMN

• 칼럼의 데이터 유형, 디폴트값, NOT NULL 제약조건에 대한 변경

• 기본구조 : ALTER TABLE 테이블명 MODIFY (칼럼명1 데이터 유형 [DEFAULT 식][NOT NULL], …..);

• 예) ORACLE : ALTER TABLE TEAM_TEMP MODIFY (ORIG_YYYY VARCHAR2(8) DEFAULT '20020129' NOT NULL);

• 예) SQL Server : ALTER TABLE TEAM_TEMP ALTER COLUMN ORIG_YYYY VARCHAR(8) NOT NULL;

ALTER TABLE TEAM_TEMP ADD CONSTRAINT DF_OFIG_YYYY DEFAULT '20020129' FOR ORIG_YYYY;

  라. DROP CONSTRAINT

• 기본구조 : ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;

• 예) ORACLE : ALTER TABLE PLAYER DROP CONSTRAINT PLAYER_FK;

• 예) SQL Server : ALTER TABLE PLAYER DROP CONSTRAINT PLAYER_FK;

  마. ADD CONSTRAINT

• 기본구조 : ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건 (칼럼명);

• 예) ORACLE : ALTER TABLE PLAYER ADD CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID);

• 예) SQL Server : ALTER TABLE PLAYER ADD CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID); 

4. RENAME TABLE

• 기본구조 : RENAME 변경전 테이블명 TO 변경 후 테이블명;

• 예) ORACLE : RENAME TEAM TO TEAM_BACKUP;

• 예) SQL Server : sp_rename 'dbo.TEAM', 'TEAM_BACKUP';

5. DROP TABLE

• 기본구조 : DROP TABLE 테이블명 [CASCADE CONSTRAINT];

• 예) ORACLE : DROP TABLE PLAYER;

• 예) SQL Server : DROP TABLE PLAYER;

6. TRUNCATE TABLE

• 테이블에 들어있단 모든 행이 제거되고 테이블 구조는 그대로 남겨둔다.

• 예) ORACLE : TRUNCATE TABLE TEAM;

• 예) SQL Server : TRUNCATE TABLE TEAM;

1-3. DML(Data Manipulation Language)

1. INSERT

• 테이블에 데이터를 입력

• 기본구조1(일부 데이터만 입력) : INSERT INTO 테이블명 (COLUMN_LIST) VALUES (VALUE_LIST);

• 기본구조2(전체 데이터 입력) : INSERT INTO 테이블명 VALUES (VALUE_LIST);

2. UPDATE

• 데이터 정보 수정

• 기본구조 : UPDATE 테이블명 SET 수정되어야 할 칼럼명 = 수정되기를 원하는 새로운 값;

• 예) ORACLE : UPDATE PLAYER SET BACK_NO=99;

3. DELETE

• 기본구조 : DELETE [FROM] 테이블명 [WHERE 조건절];

4. SELECT

• 데이터 조회

• 예) ORACLE : SELECT PLAYER_ID, PLAYER_NAME, POSITION FROM PLAYER;

5. 산술 연산자와 합성 연산자

1-4. TCL(Transaction Control Language)

1. 트랜잭션 개요

2. COMMIT

• 입력한 자료나 수정한 자료, 삭제한 자료에 대해서 전혀 문제가 없다고 판단되었을 경우 COMMIT으로 트랜잭션을 완료한다.

3. ROLLBACK

• 테이블 내 입력한 데이터, 수정한 데이터, 삭제한 데이터에 대하여 COMMIT 이전에는 변경사항을 ROLLBACK 명령어로 취소할 수 있다. ROLLBACK으로 데이터 변경사항이 취소되면 데이터의 이전 상태로 복구되며, 관련된 행에 대한 잠금(LOCKING)이 풀리고 다른 사용자들이 데이터 변경을 할 수 있다.

4. SAVEPOINT

• 저장점 (SAVEPOINT)을 정의하면 ROLLBACK 할 때 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아니라 현 시점에서 SAVEPOINT까지 트랜잭션의 일부만 롤백할 수 있다.

• 실행문 : SAVEPOINT SVPT1;

• 롤백시 명령문 : ROLLBACK TO SVPT1;