자격증/SQLD

01. SQL 기본

염두리안 2024. 5. 21. 01:34
728x90
반응형
  • 관계형 데이터베이스
    • 등장) 1970년대, E.F.codd 박사 논문에서 처음 소개된 DB
    • DB와 DBMS 차이점) DB는 데이터를 어떠한 형태의 자료구조로 사용하느냐에 따라서 나뉨 / DBMS는 DB를 관리하기 위한 SW
      • DB) 계층형(트리 형태 자료구조 데이터 저장, 1:N) / 네트워크형 DB(오너, 멤버 형태로 저장, 1:N, M:N) / 관계형 DB(릴레이션에 데이터 저장, 릴레이션 사용해 집합&관계연산 수행)
      • DBMS) 오라클, MS-SQL, MySQL, Sybase 등 (모두 관계형 DB 지원)
    • 관계형DB 집합&관계 연산
      • 집합연산) 합집합(Union, 중복행은 한번만 조회) / 차집합(Difference) / 교집합(Intersection) / 교집합(Cartesian product, 각 릴레이션에 존재하는 모든 데이터 조합해 연산)
      • 관계연산) 선택연산(Selcection, 조건에 맞는 행 조회) / 투영연산(Projection, 조건에 맞는 속성 조회) / 결합연산(Join, 여러 릴레이션의 공통 속성 사용해 새로운 릴레이션 만듦) / 나누기연산(Division, 동일값 행 추출&나누는 릴레이션 속성 삭제한 후 중복행 제거)
  • 테이블 구조

  • SQL 종류
    • SQL(Structured Query Language) : 관계형DB에 대해 데이터의 구조 정의, 데이터 조작, 데이터 제어 등을 할 수 있는 절차+비절차형 언어 / ANSI/ISO 표준 준수하기에 DBMS가 변경되어도 그대로 사용 가능
      • ANSI/ISO SQL 표준) INNER JOIN, NATURAL JOIN, USING 조건, ON 조건절
      • ANSI/ISO SQL3 표준) DBMS 벤더별로 차이가 있던 SQL 표준 제정
    • SQL 종류
      • 종류
        • DDL(Definition) : 관계형DB 구조 정의 / CREATE, ALTER, DROP, RENAME, TRUNCATE
        • DML(Manipulation) : 테이블에서 데이터 입력, 수정, 삭제, 조회 / INSERT, UPDATE, DELETE, SELECT
        • DCL(Control) : DB 사용자에게 권한 부여, 회수(접근권한) / GRANT, REVOKE
        • TCL(Transaction Control) : 트랜잭션 제어 / COMMIT, ROLLBACK, SAVEPOINT
      • 트랜잭션) DB 작업 처리 단위
        • 원자성(Atomicity) : 트랜잭션의 처리가 완전히 끝나지 않았을 경우, 실행되지 않은 상태와 같아야 함
        • 일관성(Consistency) : 트랜잭션 실행 결과로 DB상태가 모순되지 않아야 함 / 트랜잭션 실행 후에도 일관성 유지
        • 고립성(Isolation) : 트랜잭션 실행 중 생성되는 연산의 중간결과는 다른 트랜잭션이 접근 X (부분적인 실행 결과를 다른 트랜잭션이 못 봄)
        • 영속성(Durability) : 트랜잭션이 그 실행을 성공적으로 완료하면 그 결과는 영구적으로 보장
      • SQL문 실행 순서
        1. 파싱(Parsing) : SQL문 문법 확인, 구문 분석.... 구문분석한 SQL문은 Library Cache에 저장
        2. 실행(Execution) : 옵티마이저가 수립한 실행 계획에 따라 SQL 실행
        3. 인출(Fetch) : 데이터를 읽어서 전송
      • SELECT문 : 특정 칼럼, 행 조회
        • ORDER BY : ASC - 오름차순 / DESC - 내림차순 정렬
        • Index를 사용한 정렬 회피 : 정렬은 오라클DB에 부하를 줌... 인덱스 사용해 ORDERBY 회피
          • /*+ INDEX_DESC(A) *+/ 사용시 테이블 내 생성된 인덱스를 내림차순으로 지정
        • DISTINCT : 칼럼명 앞에 지정해 중복된 데이터를 한번만 조회하게 함.
        • Alias(별칭) : 테이블명, 칼럼명을 간략히 표현할 때 사용
SELECT 조회칼럼
FROM 테이블명
WHERE 데이터조건;

SELECT DISTINCT 조회칼럼

SELECT ENAME AS "이름" FROM EMP a
WHERE a.EMPNO=1000;
  • WHERE문 사용
    • 사용 가능한 연산자
      • 비교연산자(>, =, <)
      • 부정비교연산자((같지 않은 것 조회) !=, ^=, <>, NOT칼럼명 = / (크지 않은 것) NOT 칼럼명 >)
      • 논리연산자(AND, OR, NOT)
      • SQL연산자
        • LIKE '%비교문자열%'  : 비교 문자열 조회(% = 모든 값)
        • BETWEEN A AND B : A B 사이 값 조회
        • IN(list) : OR 의미... list 값 중 하나만 일치해도 조회
        • IS NULL : NULL 값 조회
      • 부정SQL연산자
        • NOT BETWEEN A AND B : A B 사이의 해당되지 않는 값
        • NOT IN (list) : list와 불일치한 것 조회
        • IS NOT NULL : 널이 아닌 것 조회
    • LIKE문 사용 : 와일드카드 사용해 데이터 조회 / LIKE문에 와일드카드 사용하지 않으면 =과 같음
      • % : 어떤 문자를포함한 모든 것 조회 / ex) '염%'는 염으로 시작하는 모든 문자 조회, '%두리안%'는 중간에 두리안이 있는 모든 것 조회
      • _ : 한 개인 단일문자 / ex) 'test_'는 test로 시자가고 하나의 글자만 더 있는 것 조회
    • BETWEEN문 사용 : 지정된 범위에 있는 값 조회 / ex) BETWEEN 1000 AND 2000
    • IN문 사용 : OR의 의미... 하나의 조건만 만족해도 조회, 여러 개 칼럼에 대한 조건 지정 가능 / ex) WHERE ANIMAL IN('DOG', 'CAT')은 ANIMAL 칼럼이 DOG이거나 CAT인 레코드 조회
    • NULL 값 조회 : IS NULL 사용 / NULL이 아닌 것을 조회할 땐 NOT NULL
      • NULL 값 특) 모르는 값, 값의 부재, NULL+숫자=NULL, NULL과 어떤 값을 비교하면 '알수없음' 반환
      • NULL 관련 함수
        • NVL(오라클) : NULL이면 다른 값으로 대체 / NUL(MGR, 0)은 MGR칼럼이 NULL이면 0으로 바꿈
        • NVL2(오라클) : NVL과 DECODE 함수를 하나로 만듦... NVL2(MGR, 1, 0)은 MGR칼럼이 NULL이 아니면 1, NULL이면 0 반환
        • NULLIF(오라클, MS, My) : 두개 값이 같으면 NULL, 아니면 첫 번째 값 반환 / ex) NULLIF(exp1, exp2) 는 둘이 같으면 1, 아니면 exp1 반환
        • COALESCE : NULL이 아닌 최초의 인자 값 반환 / COALESCE(exp1, exp2, exp3, ...)는 exp1이 NULL이 아니면 exp1을, 아니면 그 뒤의 값의 null여부를 판단해 값 반환
  • GROUP 연산
    • GROUP BY문 : 테이블에서 소규모 행 그룹화 가능 / HAVING절에 조건문 사용 / 그룹핑된 결과에 대한 조건문 사용 / ORDER BY 사용해 정렬 가능
      • HAVING문 사용 : GROUP BY에서 조건절 사용하려면 HAVING 사용... 만약 WHERE절에 조건문 사용하면 조건 충족하지 못하는 데이터들은 GROUP BY 대상에서 제외됨.
      • 집계함수 : COUNT() / SUM() / AVG() / MAX, MIN() / STDDEV() - 표준편차 / VARIANCE() - 분산 계산
      • COUNT() : 행 수를 계산하는 함수... COUNT(*)는 NULL 포함, COUNT(칼럼명)은 NULL 제외
// EMP 테이블에서 직업별(JOB) 급여합게 중에 급여(SAL) 합계가 1000 이상인 직업
SELECT JOB, SUM(SAL)
FROM EMP
GROUP BY JOB
HAVING SUM(SAL) >= 1000;

// 사원번호 1000 ~ 1003번의 부서별 급여합계
SELECT DEPTNO, SUM(SAL) FROM EMP
WHERE EMPNO BETWEEN 1000 AND 1003
GROUP BY DEPTNO;
  • SELECT문 실행 순서) FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY
SELECT ename			// 5
FROM emp				// 1
WHERE empno = 10		// 2
GROUP BY ename			// 3
HAVING count(*) >= 1	// 4
ORDER BY ename;			// 6
  • 명시적(Explicit) 형변환 / 암시적(Implicit) 형변환
    • 형변환? 두 개의 데이터의 데이터 타입이 일치하도록 변환하는 것
    • 명시적 형변환 : 형변환 함수를 사용해 데이터 타입을 일치시키는 것
      • TO_NUMBER(문자열) : 문자열 → 숫자
      • TO_CHAR(숫자OR날짜, [FORMAT]) : 숫자(날짜) → 지정된 포맷 문자로
      • TO_DATE(문자열, FORMAT) : 문자열 → 포맷의 날짜형
    • 암시적 형변환 : 개발자가 형변환을 하지 않은 경우 DBMS가 자동으로 형변환 하는 것
  • 내장형 함수(BUILT-IN Function)
    • DUAL 테이블 : 오라클DB에 의해서 자동으로 생성되는 테이블 / 임시로 사용할 수 있는 테이블로 내장형 함수 실행시에도 사용 가능 / 모든 사용자가 사용 가능
    • 문자열
      • ASCII(문자)
      • CHR/CHAR(ASCII코드값)
      • SUBSTR(문자열, M, N) - 문자열에서 M번째 위치부터 N개를 자름
      • CONCAT(문자열1, 문자열2) - 1, 2번 문자열 결합... 오라클은 ||, MS는 + 사용 가능
      • LOWER(문자열) / UPPER(문자열)
      • LENGTH/LEN(문자열) - 공백포함 문자열 길이 / LTRIM(문자열, 지정문자) - 왼쪽에서 지정된 문자 삭제(지정된 문자 생략시 공백 삭제) / RTRIM(문자열)
      • TRIM(문자열, 지정된 문자) - 왼~오에서 지정된 문자 삭제(지정된 문자 생략시 공백 삭제)
    •  날짜형 : SYSDATE - 오늘의 날짜를 날짜타입으로 알려줌 / EXTRACT(YEAR FROM SYSDATE) - 날짜에서 년, 월, 일 조회
    • 숫자형
      • ABS(숫자) - 절댓값
      • SIGN(숫자) - 양수, 음수, 0 구별
      • MOD(숫자1, 숫자2) - % 대체 가능, 숫자1을 숫자2로 나눈 나머지 계산
      • CEIL/CEILING(숫자) - 숫자보다 크거나 같은 최소의 정수 반환
      • FLOOR(숫자) - 숫자보다 작거나 같은 최대 정수 반환
      • ROUND(숫자, M) - 소수점M에서 반올림(M 기본값은 0) / TRUNC(숫자, M) - 소수점M에서 절삭(기본값 0)
  • DECODE, CASE문
    • DECODE : IF문 구현 가능... 특정조건 참이면 A, 거짓이면 B로 응답
    • CASE : 조건문 사용 가능... 해당 조건이 참이면 THEN 실행, 거짓이면 ELSE 실행
// DECODE
// EMPNO가 1000이면 TRUE, 거짓이면 FALSE 출력
DECODE (EMPNO, 1000, 'TRUE', 'FALSE')

// CASE
CASE [expression]
	WHEN condition_1 THEN result_1
    WHEN condition_2 THEN result_2
    ...
    WHEN condition_n THEN result_n
    ELSE result
END
  • WITH문 : 서브쿼리를 사용해 임시 테이블이나 뷰처럼 사용할 수 있는 구문 / 별칭 지정 가능 / 옵티마이저는 SQL을 인라인 뷰나 임시테이블로 판단
WHIT viewData AS
	// 서브쿼리를 사용해 임시테이블 생성
	(SELECT * FROM EMP
    UNION ALL
    SELECT * FROM EMP
    )
    SELECT * FROM viewData WHERE EMPNO = 1000;
728x90
반응형