- SET OPERATOR
- 두개 이상의 SELECT한 결과를 합치거나하는 집합 형태의 결과물로 조회하는 명령어
- 합집합
- UNION : ROW 늘어남
- 두개 이상의 SELECT한 결과(RESULT SET)를 구하는 명령어
- 만약 중복이 있을 경우 중복되는 결과는 한번만 보여준다
- UNION ALL : ROW 늘어남
- 두개이상의 SELECT한 결과(RESULT SET)를 구하는 명령어
- 만약 중복이 있을 경우 중복되는 내용도 그대로 조회하여 보여준다.
- JOIN : COLUM 늘어남
- 두개이상의 테이블을 하나로 합쳐 사용하는 명령 구문
- 두개의 테이블(EMPLYOEE, DEPARTMENT)에서 공통 컬럼의 이름이 다를 경우
- join DEPARTMENT ON(DEPT_CODE = DEPT_ID);
- 공통 컬럼의 이름이 같을 경우
- join DEPARTMENT USING(DEPT_ID);
- INNER : ON과 함께 사용, 조건에 만족하는 데이터만 선택
- LEFT : 첫번째 테이블을 기준으로 두번째 테이블을 조합, 조건에 만족하지 않는 경우에는 첫번째 테이블의 필드값은 그대로 유지, 두번재 테이블은 모두 NULL로 표시
- RIGHT : LEFT JOIN의 반대
- INNER / OUTER(LEFT, RIGHT)
- ON() 안에 컬럼 뿐만 아니라 계산식, 함수식, AND, OR 등의 표현식 사용가능
- SELF JOIN
- 자기 자신을 조인, 한 테이블의 정보 중 값 비교가 필요한 정보들을 계산 조회하는 방식
- 직원의 정보와 직원을 관리하는 매니저의 정보를 조회
SELECT *
from EMPLOYEE E
join EMPLOYEE M ON(E.MANAGER_ID=M.EMP_ID);
- 다중 JOIN
- 여러개의 테이블을 JOIN하는 것
- 일반 조인과 선언방식은 같으나 앞서 조인한 결과를 기준으로 나중에 조인하는 테이블을 연결 짓는다.
- 조인순서에 주의
- SUB QUERY
- 주가되는 메인쿼리 안에서 조건이나 검색을 위한 또 하나의 쿼리를 추가하는 기법
- 단일 행 서브쿼리 : 결과값이 1개 나오는 서브쿼리
- 다중 행 서브쿼리 : 결과값이 여러줄 나오는 서브쿼리
- e.g. 각 직급 별 최소 급여 받는 사원의 정보
select *
where SALARY = (select MIN(SALARY) from EMPLOYEE );
from EMPLOYEE
select *
from EMPLOYEE
where SALARY IN(select MIN(SALARY) from EMPLOYEE group by JOB_CODE);
- 서브쿼리의 사용 위치
- SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, JOIN
- DML : INSERT, UPDATE, DELETE
- DDL : CREATE TABLE, CREATE VIEW ...
- INLINE VIEW(인라인뷰)
- FROM 위치에 사용되는 서브쿼리
- 테이블을 테이블명으로 직접 조회를 하는 대신 서브쿼리의 결과(RESULT SET)를 활용하여 데이터 조회
- 기존의 테이블을 대체한다
- 별칭 필수
select *
from (select EMP_ID, EMP_NAME, DEPT_TITLE, JOB_NAME
from EMPLOYEE
join DEPARTMENT ON(DEPT_CODE = DEPT_ID)
join JOB ON(EMPLOYEE.JOB_CODE = JOB.JOB_CODE)) A;
- RANK() 함수 / DENSE_RANK() 함수
- RANK() : 동일한 순번이 있을 경우 이후 순번은 이전과 동일한 순번의 개수만큼 건너뛰고 번호를 매기는 함수
- RANK() OVER(order by SALARY DESC)
- DENSE_RANK() : 동일한 순번이 있을경우 이후 순번에는 영향을 미치지 않는 함수
- ROW_NUMBER() : 동일 순번은 무시, 그냥 넘버링
- 상호 연관 쿼리 : 상관쿼리
- 일반적으로 서브쿼리는 서브쿼리대로, 메인쿼리는 서브쿼리의 결과만 받아서 실행 방식
- 메인쿼리가 사용하는 컬럼값, 계산식 등을 서브쿼리에 적용, 서브쿼리 실행 시 메인쿼리의 값도 함께 사용하는 방식
select EMP_ID, EMP_NAME, JOB_CODE, SALARY
from EMPLOYEE E
where SALARY > (
select TRUNCATE(AVG(SALARY),-3) from EMPLOYEE E2 where E.JOB_CODE = E2.JOB_CODE );
- 스칼라 서브쿼리
- 상관쿼리 + 단일 행(보통 SELECT에 많이 사용하여 SELECT LIST라고도 부른다)
select EMP_ID, EMP_NAME, MANAGER_ID,
IFNULL((select EMP_NAME from EMPLOYEE M where E.MANAGER_ID = M.EMP_ID),"없음") 관리자이름
from EMPLOYEE E;
- DDL : CREATE
- CREATE : 데이터베이스의 객체를 생성하는 DDL (DATABASE, TABLE, VIEW ...)
- 테이블 생성
- 테이블 : 데이터를 저장하기 위한 틀, 2차원 표 형태로 데이터들을 담을 수 있는 객체
[사용형식]
[테이블 생성 시]
- CREATE TABLE TEST( 컬럼명 자료형(길이) 제약조건);
[제약조건]
- 테이블에 데이터를 저장하고자 할때 지켜야하는 규칙
- NOT NULL - NULL값을 허용하지 않는다.(필수 입력 사항)
- UNIQUE - 중복값을 허용하지 않는다.
- CHECK - 지정한 입력 사항 외에는 받지 못하게 하는 조건
- PRIMARY KEY - (NOT NULL + UNIQUE)
- 테이블 내에서 해당 행(ROW)을 인식할 수 있는 고유한 값
- 테이블에서 단 1개만 설정 가능
- FOREIGN KEY - 다른 테이블에서 저장된 값을 연결지어서 참조로 가져오는 데이터를 지정하는 제약조건
- 제약조건
- NOT NULL
- 널 값을 허용하지 않는다.
- 해당 제약조건을 등록한 컬럼에 반드시 값을 기록해야하는 경우
- 컬럼 레벨 제약조건 등록(테이블 레벨 제약조건 부락)
- 데이터를 INSERT/UPDATE/DELETE NULL값 고려
- 테이블에 값 추가 (DML : INSERT) INSERT INTO ~ VALUES();
- 테이블 수정 (DDL : ALTER) alter table USER_NOT_NULL modify column GENDER VARCHAR(3) not null;
- UNIQUE
- 중복을 허용하지 않는다.
- 테이블 레벨에서 제약조건 설정 가능
- 컬럼이 모두 작성된 후에 별도로 작성하는 제약조건
- 두개 이상의 컬럼을 제약조건으로 묶을 경우, 반드시 테이블레벨에서 제약 조건을 선언해야함 UNIQUE(NO,ID);
- CHECK
- 컬럼에 값을 기록할 때 지정한 값 이외에는 값이 기록되지 않도록 범위를 제한하는 조건 CHECK(GENDER in ('남','여')) constraint CK_TESTDATA CHECK(TEST_DATA > 0)
- PRIMARY KEY
- ‘기본키 제약조건’
- 테이블 내에서 행을 식별하기 위한 고유값을 가지는 컬럼에 부여하는 제약조건
- NOT NULL과 UNIQUE 제약조건이 함께 걸린다.
- 테이블 전체에 대한 각 ROW별 식별자 역할을 수행시키는 제약조건이다.
- 반드시 값이 들어가고 중복이 없어야한다.
- 기본키 제약조건은 각 테이블마다 한개 존재해야한다.
- PRIMARY KEY 제약조건은 한 컬럼에 적용 가능하고 여러 컬럼 묶어서도 적용 가능하다. constraint PK_USER_NO primary KEY(USER_NO,USER_ID)