[SQLD] SQL 기본 및 활용(SQL 기본) 정리 및 표준조인(Standard Join)

SQL 기본 및 활용

  * 데이터베이스에는 자료의 성격에 따라 N개의 테이블을 생성한다.

  * 모든 자료들은 테이블에 입력되며, 조회, 수정, 삭제 할 수 있다.

  * 모든 자료는 실질적으로 테이블에 저장이 되며, 테이블에 있는 자료들을 꺼내 볼 수 있다.

 

 

데이터 유형에 대해 파악

데이터 유형	설명
CHAR( number )	- 고정 길이 문자열 정보  - number는 기본 길이 1바이트  - number만큼 최대 길이를 갖고 고정 길이를 가지고 있으므로 할당된 변수 값의 길이가 number보다 작을 경우에는 그 차이 길이만큼 공간으로 채워진다.
VARCHAR( number )	- 가변 길이 문자열 정보  - number는 최소 길이 1바이트  - number만큼의 최대 길이를 갖지만 가변길이로 조정이 되기 때문에 할당된 변수값의 바이트만 적용된다. ( Limit의 개념)
NUMERIC	- 정수, 실수 등 숫자 정보  - Oracle은 처음에 전체 자리 수를 지정하고, 그 다음 소수 부분의 자리 수를 지정한다. ex) NUMERIC(8, 2) -> 전체 8자리, 정수부분 6자리, 소수점 부분 2자리
DATE	- 날짜와 시각 정보

 

테이블 생성시 주의 사항

  - 테이블명은 객체를 의미할 수 잇는 적절한 이름을 사용한다. 가능한 단수형을 권고한다.

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

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

  - 테이블 이름을 지정하고 각 칼럼들은 괄호 "()" 로 묶어 지정한다.

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

  - 칼럼에 대해서는 다른 테이블까지 고려하여 데이터베이스 내에서는 일관성 있게 사용하는 것이 좋다.(데이터 표준화 관점)

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

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

  - 벤더에서 사전에 정의한 예약어는 쓸 수 없다.

  - A-Z, a-z, 0-9, _ , $, # 문자만 허용된다.

  - 테이블명은 반드시 숫자가 아닌 문자로 시작되어야 한다.

  - 특수문자 " - "는 허용하지 않는다.

 

 

INSERT

INSERT INTO 테이블명(COLUMN List)  VALUES (COLUMN_List에 넣을 VALUE_List);  * 테이블의 칼럼을 정의할 수 있는데, 이때 칼럼의 순서는 테이블의 칼럼 순서와 매치할 필요는 없으며, 정의하지 않은 칼럼은 Default로 NULL 값이 입력된다.  단, Primary key나 Not NULL로 지정된 칼럼은 NULL이 허용되지 않는다.

INSERT INTO 테이블명  VALUES (전체 COLUMN에 넣을 VALUE_List);  * 모든 칼럼에 데이터를 입력하는 경우로 굳이 Column_List를 언급하지 않아도 되지만, 칼럼의 순서대로 빠짐없이 데이터가 입력되어야 한다.

 

 

Commit과 Rollback 의 장점

  1) 데이터 무결성 보장

  2) 영구적인 변경을 하기 전에 데이터의 변경 사항 확인 가능

  3) 논리적으로 연관된 작업을 그룹핑하여 처리 가능

 

집계 함수의 종류

집계 함수	사용 목적
COUNT( * )	NULL 값을 포함한 행의 수를 출력한다.
COUNT( 표현식 )	표현식의 값이 NULL값인 것을 제외한 행의 수를 출력한다.
SUM( [DISTINCT | ALL ] 표현식)	표현식이 NULL 값을 제외한 합계를 출력한다.
ACG( [DISTINCT | ALL ] 표현식)	표현현이 NULL 값을 제외한 평균을 출력한다.
MAX( [DISTINCT | ALL ] 표현식)	표현식의 최대값을 출력한다. (문자, 날짜 데이터 타입도 가능)
MIN( [DISTINCT | ALL ] 표현식)	표현식의 최소값을 출력한다. (문자, 날짜 데이터 타입도 가능)
STDDEV( [DISTINCT | ALL ] 표현식)	표현식의 표준 편차를 출력한다.
VARIAN( [DISTINCT | ALL ] 표현식)	표현식의 분산을 출력한다.
기타 통계 함수	벤더별로 다양한 통계식을 제공한다.

 

Caution.

  일반적인 SQL 문장에서 조회하는 데이터를 제한하기 위해서는 WHERE 절을 사용하지만, 그룹별로 조회할 때 집계 데이터에 대한 제한 조건을 사용하기 위해서는 HAVING 절을 사용한다.

 

important.

  - ORDER BY 절에서 정렬 기준이 생략되면 Default로 ASC(오름차순) 정렬이 된다.

  - ORDER BY 절에는 column_name 대신에 SELECT 절에 기술한 Column의 순서 번호나 column의 ALIAS명을 대신해서 사용할 수 있다.(제가 자주 사용...ㅎㅎ)

 

 

INNER JOIN 

  - Join 조건에서 동일한 값이 있는 행만 반환한다.

  - INNER JOIN 표시는 그 동안 WHERE 절에서 사용하던 JOIN 조건을 FROM 절에서 정의하겠다는 표시이므로 USING 조건절이나 ON 조건절을 필수적으로 사용해야 한다.

 

사원 번호와 사원이름, 소속붑서 코드와 소속부서 이름을 찾아본다.

select EMP.EMPNO as 사원번호, EMP.ENAME as 사원이름, DEPT.DNAME as 부서, DEPT.DEPTNO as 부서코드  from EMP inner join DEPT ON EMP.DE=DEPT.DEPTNO ORDER BY 사원번호;

 

 

NATURAL JOIN

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

  - Natural join이 명시되면, 추가로 USING 조건절, ON조건절, WHERE 조건절에서 JOIN조건을 정의할 수 없다.

사원 번호와 사원이름, 소속붑서 코드와 소속부서 이름을 찾아본다.

select EMP.EMPNO as 사원번호, EMP.ENAME as 사원이름, DEPT.DNAME as 부서, DEPT.DEPTNO as 부서코드  from EMP natural join DEPT ORDER BY 사원번호;

  위 SQL은 별도의 JOIN 칼럼을 지정하지 않았지만, 두 개의 테이블에서 DEPTNO라는 공통된 칼럼을 자동으로 인식하여 JOIN 처리한 것이다.

  JOIN에 사용된 칼럼들은 같은 데이터 유형이어야 하며, ALIAS나 테이블명과 같은 접두사를 붙일 수 없다.

 

 

USING 조건절

  - Natural join에서는 모든 일치되는 칼럼들에 대해 Join이 이루어지지만, FROM 절의 USING 조건절을 이요하면 같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해서만 선택적으로 EQUI JOIN을 할 수 있다.

  - 이 아이는 SQL에서는 적용되지 않으므로 저는 안하고 이론만 알고 넘어가겠습니다.

 

 

ON 조건절

- JOIN 서술부와 비 JOIN 서술부(WHERE 조건절)를 분리하여 이해가 쉬우며, 칼럼명이 다르더라도 JOIIN 조건을 사용할 수 있는 장점이 있다.

사원, 부서 테이블에서 사원 번호와 사원 이름, 소속부서 코드, 소속부서 이름을 출력.

select EMP.EMPNO as 사원번호, EMP.ENAME as 사원이름, DEPT.DNAME as 부서, DEPT.DEPTNO as 부서코드  from EMP join DEPT ON(EMP.DEPTNO=DEPT.DEPTNO) ORDER BY 사원번호;

 

Add.

Natural Join의 Join조건은 기본적으로 같은 이름을 가진 모든 칼럼들에 대한 동등 조건이지만, 임의의 Join 조건을 지정하거나, 이름이 다른 칼럼명을 Join 조건으로 사용하거나, Join 칼럼을 명시하기 위해서는 ON조건절을 사용한다.

 

 

where 절과의 혼용

부서코드 30인 부서의 소속 사원 이름 및 소속 부서 코드, 부서코드, 부서 이름을 찾기

select EMP.EMPNO as 사원번호, EMP.ENAME as 사원이름, DEPT.DNAME as 부서, DEPT.DEPTNO as 부서코드  from EMP join DEPT ON(EMP.DEPTNO=DEPT.DEPTNO)  where DEPT.DEPTNO=30  ORDER BY 사원번호;

 

 

ON 조건절 + 데이터 검증 조건 추가

매니저 사원 번호가 7698번인 사원들의 이름 및 소속 부서 코드, 부서이름을 찾기

select EMP.ENAME as 사원이름,EMP.MGR as 매니저번호, DEPT.DNAME as 부서, DEPT.DEPTNO as 부서코드  from EMP join DEPT ON(EMP.DEPTNO=DEPT.DEPTNO)  where EMP.MGR=7698 ORDER BY 매니저번호;

 

 

CROSS JOIN

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

- 두 개의 테이블에 대한 CARTESIAN PRODUCT 또는 CROSS PRODUCT와 같은 표현으로, 결과는 양쪽 집합의 M*N 건의 데이터 조합이 발생한다.

 

CROSS JOIN을 이용하여 사원이름과 소속 부서를 찾기

select EMP.ENAME as 사원이름, DEPT.DNAME as 부서  from EMP cross join DEPT;

 

 

OUTER JOIN

  * LEFT OUTER JOIN

    - 조인 수행시 먼저 표기된 좌측 테이블에 해당하는 데이터를 먼저 읽은 후, 나중 표기된 우측 테이블에서 Join 대상 데이터를 읽어 온다.

    - LEFT JOIN으로 OUTER 키워드를 생략해서 사용 가능

    - NULL값까지 가능하므로 INNER JOIN이랑은 다르게 사용한다.

    - ON 사용함.

 

  * RIGHT OUTER JOIN

    - 조인 수행시 먼저 표기된 우측 테이블에 해당하는 데이터를 먼저 읽은 후, 나중 표기된 우측 테이블에서 Join 대상 데이터를 읽어 온다.

    - RIGHT JOIN으로 OUTER 키워드를 생략해서 사용 가능

    - NULL값까지 가능하므로 INNER JOIN이랑은 다르게 사용한다.

    - ON 사용함.

 

  * FULL OUTER JOIN

    - 조인 수행시 좌측, 우측 테이블의 모든 데이터를 읽어 JOIN하여 결과를 생성한다.

    - RIGHT OUTER JOIN과 LEFT OUTER JOIN의 결과를 합집합으로 처리한 결과와 동일하다.

    - UNION ALL이 아닌 UNION 기능과 같으므로 중복되는 데이터는 삭제한다.

    - FULL JOIN으로 OUTER 키워드를 생략해서 사용 가능.

    - 모든 테이블의 NULL값 까지 다 나온다. 즉 데이터가 하나라도 있는 행은 바로 다 출력됨.