SQL 개발자 자격증 제2과목 1장

김소희·2023년 11월 1일
sql
2

2과목 1장 SQL기본

DDL 데이터 정의어

CREATE: 데이터베이스 개체(테이블, 뷰, 인덱스 등)를 생성합니다.
ALTER: 이미 존재하는 데이터베이스 개체의 구조를 변경합니다.
DROP: 데이터베이스 개체를 삭제합니다.
TRUNCATE: 테이블 내의 모든 데이터를 삭제합니다.
RENAME: 데이터베이스 객체의 이름을 변경합니다.
COMMENT:데이터베이스 객체에 대한 주석을 추가합니다.

DDL(Data Definition Language) 데이터베이스 정의
SQL Server에서는 DDL명령 후 rollback시 되돌아가지만
Oracle에서는 DDL명령어 모두 트랜잭션 종료라는 기능이 있어서 rollback해도 이미 DB 반영됨

테이블 생성 주의사항
테이블명은 객체를 의미할 수 있는 적절한 이름으로 단수형을 권고하며 대소문자를 구분하지 않으며 기본적으로 대문자로 만들어진다. 테이블명은 다른테이블명과 중복이면 안된다. 하나의 테이블 내에서 칼럼명이 중복이면 안된다. 문자 데이터 타입에는 반드시 최대 길이를 표시해야 한다. 칼럼에 대한 제약조건이 있으면 CONSTRAINT를 이용하여 추가할 수 있다.

제약조건의 종류

  • 테이블 생성 (디폴트값 생략가능)
  • 테이블 구조 확인
  • 테이블에 칼럼 추가 (추가된 칼럼은 가장 마지막에 위치한다.)
  • 테이블에 칼럼 삭제 (데이터가 있어도 삭제가능하며, 복구가 불가능하다. 삭제후에도 칼럼이 1개 이상어야한다.)
  • 칼럼 제약조건 변경(데이터 타입, 디폴트 값, not null조건 추가)
    데이터가 훼손될 수 있기 때문에 null값만 있거나 데이터가 없을때만 크기를 줄일 수 있다.
    해당 칼럼에 NULL 값이 없을 경우에만 NOT NULL 제약조건을 추가할 수 있다.
  • 테이블 생성 시 부여했던 제약조건을 삭제
  • 테이블 생성 이후에 제약조건을 추가
  • 테이블 이름 변경(오라클, sql서버)
  • 테이블 삭제
  • 테이블 내 데이터 삭제

DML 데이터 조작어

  • 데이터 입력

  • 데이터 수정

  • 데이터 삭제

  • 데이터 조회

    전체조회 : SELECT * FROM 테이블명;
    열 별칭 : SELECT FirstName AS First, LastName AS Last FROM 테이블명;
    중복된 값 제외 : SELECT DISTINCT 기준칼럼명 FROM 테이블명;

TRUNCATE & DROP & DELECT 차이점
TRUNCATE 테이블은 그대로 있으나 안의 데이터와 인덱스 삭제되어 용량이 줄어듬
DROP 테이블 삭제
DELECT 데이터만 삭제하기에 인덱스는 남아있으며 로그가 남아서 되돌릴 수 있음

Delete Action 명령어
Cascade : 부모테이블 삭제 시 자식테이블도 같이 삭제됩니다.
Set Null : 부모테이블 삭제 시 자식테이블의 해당 필드를 Null값 처리
Set Default : 부모테이블 삭제 시 자식테이블 해당 필드 값을 기본값으로 처리
Restrict : 자식테이블에 PK값이 없는 경우만 부모테이블 삭제 허용
No Action : 참조무결성을 위반하는 삭제/수정 액션을 취하지 않음

Insert Action 명령어
Automatic : 부모테이블에 PK가 없는 경우 부모테이블 PK를 생성 후 자식테이블 입력
Set Null/Set Default : Delete 명령어와 비슷합니다. 단, 부모테이블에 PK가 없는 경우를 전제조건으로 합니다.
Dependent : 부모테이블에 PK가 존재할 때만 자식테이블 입력 허용
No Action : 참조무결성을 위반하는 입력 액션을 취하지 않음

TCL 트랜잭션 조작어

  • COMMIT : 트랜잭션에서 변경된 내용을 영구적으로 적용하는 작업
  • ROLLBACK : 트랜잭션에서 변경된 내용을 취소하고 이전 상태로 되돌리는 작업
  • SAVEPOINT : 트랜잭션 내에서 특정 시점에 저장된 지점을 의미.
  • SET TRANSACTION :트랜잭션의 특성을 설정

트랜잭션 특성

  • 원자성 : 트랜잭션을 통해 정의된 연산들은 실행 완료하던지, 실행 미완료인지로만 남아있어야 한다.
  • 일관성 : 트랜잭션 실행 전 DB의 내용이 잘못된 것이 없다면 트랜잭션 실행 후에도 잘못된 게 없어야 한다.
  • 고립성(격리성) : 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어선 안된다.
  • 지속성 : 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 DB내용은 영구적으로 저장된다.

고립성(격리성)이 낮을 때 발생하는 문제
1) Dirty Read : 다른 트랜잭션에 의해 수정되었음에도 불구하고 커밋되지 않은 데이터를 읽는 것
2) Non-Repeatable Read : 한 트랜잭션 내에서 같은 쿼리를 두번 수행했는데, 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제하는 바람에 두 쿼리 결과가 다르게 나타나는 현상
3) Phantom Read : 한 트랜잭션 내에서 같은 쿼리를 두번 수행했는데 첫번째 쿼리에서 없던 유령 레코드가 두번째 쿼리에서 나타나는 현상.

DCL 데이터 제어어

GRANT : 권한부여
REVOKE : 권한삭제

where절에 사용되는 연산자

연산우선순위
1. 괄호 ()
2. NOT연산자
3. 비교연산자, SQL연산자
4. AND
5. OR

NULL함수

다중행 집계 함수

  • COUNT(*) : NULL포함한 행의 수
  • COUNT(표현식) : NULL제외한 행의 수
  • SUM, AVG : NULL제외한 합계, 평균
  • MAX, MIN : NULL제외한 최대, 최소
  • STDDEV : NULL제외한 표준편차
  • VARIAN : NULL제외한 분산

다음 중 SELECT COL1 + COL3 FROM TAB_A;의 결과로 가장 적절한 것은? 50, null, null

col 1col 2col 3
30NULL20
NULL1040
50NULLNULL

행끼리의 연산은 SUM(COL1+COL2+...) 이런 식이고 행에 NULL이 포함되어있다면 연산결과는 항상 NULL값이 나옵니다. 근데 열의 연산은 다릅니다. SUM(COL1) 이런식으로 나오면 열끼리의 연산인데, 열 연산은 NULL이 보이면 그냥 무시하고 NULL이 아닌 데이터끼리만 연산을 수행합니다!

Oracle에서는 공백이 NULL과 같고, SQL Server에서는 공백은 그냥 하나의 데이터와 같습니다.

GROUP BY, HAVING 절의 특징

  1. GROUP BY 절을 통해 소그룹별 기준을 정한 후, SELECT 절에 집계 함수를 사용한다.
  2. 집계 함수의 통계 정보는 NULL 값을 가진 행을 제외하고 수행한다.
  3. GROUP BY 절에서는 ALIAS 명을 사용할 수 없다.(SELECT 절에는 사용가능)
  4. 집계 함수는 WHERE 절에는 올 수 없다. (집계 함수를 사용할 수 있는 GROUP BY 절보다 WHERE 절이 먼저 수행된다)
  5. WHERE 절은 전체 데이터를 GROUP으로 나누기 전에 행들을 미리 제거시킨다.
  6. HAVING 절은 GROUP BY 절의 기준 항목이나 소그룹의 집계 함수를 이용한 조건을 표시할 수 있다.
  7. GROUP BY 절에 의한 소그룹별로 만들어진 집계 데이터 중, HAVING 절에서 제한 조건을 두어 조건을 만족하는 내용만 출력한다.
  8. HAVING 절은 일반적으로 GROUP BY 절 뒤에 위치한다.

ORDER BY 절의 특징

  • 조회된 데이터들을 목적에 맞게 특정칼럼을 기준으로 정렬하여 출력한다
  • 칼럼명대신 ALIAS명이나 칼럼순서를 나타내는 정수도 가용 가능하다.
  • 디폴트는 오름차순(ASC)이며 DESC옵션으로 내림차순 정렬도 가능하다.
  • SQL 문장 제일 마지막에 위치한다.
  • SELECT 절에서 정의하지 않은 칼럼 사용이 가능하다.
  • 오라클에서는 NULL이 가장 큰값이고, SQL server에서는 NULL이 가장 작은 값이다.

SELECT 문장 실행 순서
1. FROM 테이블명
2. WHERE 조건식 (대상이 아닌 데이터 제거)
3. GROUP BY (행들을 소그룹화)
4. HAVING (그룹핑된 값의 조건에 맞는 것만 출력)
5. SELECT (별명지정)
6. ORDER BY (정렬)

SQL Server의 TOP WITH TIES

SELECT TOP(2) WITH TIES ENAME, SAL
FROM EMP
ORDER BY SAL DESC

급여가 높은 2명을 내림차순으로 출력하는데 같은 ,
급여를 받는 사원은 같이 출력한다.

오라클의 Rownum

-- 급여가 높은 상위 5명을 선택
SELECT *
FROM (
  SELECT *
  FROM employees
  ORDER BY salary DESC
)
WHERE ROWNUM <= 5;

-- 급여순으로 6~10위를 선택
SELECT *
FROM (
  SELECT *
  FROM employees
  ORDER BY salary DESC
)
WHERE ROWNUM BETWEEN 6 AND 10;

JOIN

두 개 이상의 테이블들을 연결하여 데이터를 출력하는 것으로
일반적으로 PK나 FK 값의 연관성에 의해 성립되지만
어떤 경우에는 PK, FK 관계가 없어도 논리적인 값들의 연관으로 JOIN이 가능하다.

EQUI JOIN

  • 2개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하는 경우에 사용하며, 대부분 PK,FK의 관계를 기반으로 한다.
  • '='연산자만 사용 가능하고 그 외에는(BETWEEM, <, >=) 모두 NON EQUI JOIN이다.
  • 때로는 설계상의 이유로 NON EQUI JOIN이 불가능 할 수도 있다.

    방법1) WHERE 절에 JOIN 조건을 넣는다.
    SELECT 테이블1.칼럼명, 테이블2.칼럼명, ... FROM 테이블1, 테이블2
    WHERE 테이블1.칼럼명1 = 테이블2.칼럼명2;
    방법 2) ON 절에 JOIN 조건을 넣는다.
    SELECT 테이블1.칼럼명, 테이블2.칼럼명, ... FROM 테이블1 INNER JOIN 테이블2
    ON 테이블1.칼럼명1 = 테이블2.칼럼명2;

INNER JOIN

  • JOIN 조건에서 동일한 값이 있는 행만 반환한다.
  • JOIN 조건을 FROM에서 USING 조건절이나 ON 조건절을 필수로 사용해야 한다.

FROM 절의 USING 조건절

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

ON 조건절
-SELECT E.EMPNO, D.DNAME FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO);
-ON 조건절과 WHERE 검색 조건은 충돌 없이 사용할 수 있다.

NATURAL JOIN

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

CROSS JOIN

  • 테이블 간 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합을 말한다.
  • 두 개의 테이블에 대한 CARTESIAN PRODUCT 또는 CROSS PRODUCT와 같은 표현으로, 결과는 양쪽 집합의 M*N 건의 데이터 조합이 발생한다.

LEFT OUTER JOIN

  • 조인 수행시 먼저 표기된 좌측 테이블에 해당하는 데이터를 먼저 읽은 후, 나중 표기된 우측 테이블에서 JOIN 대상 데이터를 읽어 온다.
  • A와 B를 비교해서 B의 JOIN 칼럼에서 같은 값이 있을 때 그 해당 데이터를 가져오고, B의 JOIN 칼럼에서 같은 값이 없는 경우에는 B 테이블에서 가져오는 칼럼들은 NULL 값으로 채운다.

RIGHT OUTER JOIN

  • LEFT OUTER JOIN의 반대

FULL OUTER JOIN

  • RIGHT OUTER JOIN과 LEFT OUTER JOIN의 결과를 합집합으로 처리한 결과와 동일하다.
  • 단, UNION ALL이 아닌 UNION 기능과 같으므로 중복되는 데이터는 삭제한다.

profile
김소희
백엔드 자바 개발자 소희의 노트
이전 포스트

앤드류 후버만 루틴 30일 도전

다음 포스트

SQL 개발자 자격증 제2과목 2장

0개의 댓글