728x90
반응형
※ 이전글1: 2023.03.01 - [Cerificate/SQLD] - [SQLD] 2과목 1장 SQL 기본 요약 - WHERE (구 DML ~ WHERE절 / 2024 개정사항 반영)
SQLD 이기적 강의의 흐름에 따라 기존 요약에 추가 및 수정하는 작업을 하며 이론을 가다듬고 있다. 다만, 이 과정에서 순서가 다소 달라져 독자들이 이해하기 불편할 수 있을 듯하다. 개정된 SQLD 강의에서는 SELECT문 이후 함수를 다루고 있다. 그리고 WHERE절에 대해 학습하므로 본 블로그의 글을 왔다갔다 해야한다. 따라서 위의 이전글1, 2 링크를 클릭해서 내용을 살펴보도록 하자. 그럼 이어서 계속 보라색으로 표시하며 정리하도록 하겠다. (2024.03.02 작성.)
※ 필독! SQL 기본 파트 순서: 데이터베이스 (이전글2)> 함수 > WHERE절 (이전글1) > GROUP BY~ORDER BY > JOIN + 표준조인 (이전글3)
필자가 요약하는 대부분의 이론 내용은 Data On Air의 내용에서 확인하실 수 있습니다.
Index
6절 함수
1. 내장 함수 개요
- 함수의 구분
- 내장형 함수 (Built-in Function) : 벤더에서 제공하는 함수. / DBMS에 이미 만들어진 함수로 문자형, 숫자형, 날짜형 함수 등이 있음 (이기적).
- 단일행 함수(Single-Row Function)
- 다중행 함수(Multi-Row Function)
- 집계 함수(Aggregate Function)
- 그룹 함수(Group Function)
- 윈도우 함수(Window Function)
- 사용자 정의 함수
- 내장형 함수 (Built-in Function) : 벤더에서 제공하는 함수. / DBMS에 이미 만들어진 함수로 문자형, 숫자형, 날짜형 함수 등이 있음 (이기적).
- 함수는 입력되는 값이 아무리 많아도 출력은 하나만 된다는 M:1 관계임.
- 단일행 함수의 종류
- 단일행 함수의 특징
- SELECT, WHERE, ORDER BY 절에 사용 가능.
- 각 행에 개별적으로 작용하여 데이터 값들을 조작하고, 각각의 행에 대한 조작 결과를 리턴.
- 여러 인자(Argument)를 입력해도 단 하나의 결과만 리턴.
- 함수의 인자(Arguments)로 상수, 변수, 표현식이 사용 가능.
- 대부분의 경우, 함수의 인자(Arguments)로 함수를 사용하는 함수의 중첩 가능.
2. 문자형 함수
- 문자형 함수: 문자 데이터를 매개 변수로 하여 문자나 숫자 값의 결과를 돌려주는 함수.
- UPPER(문자): 대문자 변환.
- SUBSTR(문자, 시작위치, 길이): 주어진 문자의 시작위치에서 길이만큼 출력.
- TRIM(문자): 문자의 양 끝 공백 제거.
- REPLACE(문자, 바꿀 문자, 바꾸려는 문자): 주어진 문자에서 바꿀 문자를 바꾸려는 문자로 대체.
- 단일행 문자형 함수의 종류
* SELECT * FROM DUAL: 테스트용 더미 테이블 생성. (Oracle에는 있으나 SQL Server에는 없음.)
3. 숫자형 함수
- 숫자형 함수: 숫자 데이터를 입력받아 처리하고 숫자를 리턴하는 함수.
- MOD(숫자, 몫): 나머지 반환.
ROUND(실수, 표현할 자리수): 반올림. (예. ROUND(3.1415, 2): 3.14로 두번째 자리까지 표현.) (* ROUND 자리수 주의)
- 단일행 숫자형 함수의 종류
4. 날짜형 함수
- 날짜형 함수: DATE 타입의 값을 연산하는 함수.
- SYSDATE (SQL Server에서는 GETDATE() 활용): YYYY/MM/DD HH24:MI:SS (*시간표현 주의)
- 단일행 날짜형 함수의 종류
- DB는 날짜를 저장할 때, 내부적으로 세기(Century), 년(Year), 월(Month), 일(Day), 시(Hours), 분(Minutes), 초(Seconds)와 같은 숫자 형식으로 변환하여 저장.
- 날짜 + 숫자/24 ▷날짜에 시간을 더함. | 숫자/24/60 ▷ 1분 단위.
5. 형변환 함수
- 형변환 함수: 특정 데이터 타입을 다양한 형식으로 바꿔 출력할 때 사용.
- 데이터 유형 변환
- 명시적 데이터 유형 변환 : 데이터 변환형 함수로 데이터 유형 명시.
- 암시적 데이터 유형 변환 : DB가 자동으로 데이터 유형을 변환해 연산. | 성능 저하의 우려 있음.
- 형 변환 우선순위: 날짜형 > 숫자형 > 문자형 (예. 문자형과 숫자형을 더할 시, 문자형을 숫자형으로 간주해 연산.)
- 단일행 형변환 함수의 종류
6. CASE 표현
- IF-THEN-ELSE 논리와 유사하게 표현식을 작성해 SQL의 비교 연산 기능을 보완하는 역할.
- 단일행 Case 표현의 종류
7. NULL 관련 함수
- Null 특성
- Null 값은 아직 정의되지 않은 값으로 0 또는 공백과 다름.. 0은 숫자이고, 공백은 하나의 문자이다.
- 테이블 생성 시, NOT NULL or PRIMARY KEY로 정의되지 않은 모든 데이터 유형은 null값을 포함 가능.
- Null값을 포함하는 연산의 경우 결과 값도 Null이다.
- 결과값을 NULL이 아닌 다른 값을 얻고자 할 때 NVL/ISNULL 함수를 사용한다.
- NULL함수: NULL값을 대체할 수 있는 함수.
- 단일행 Null 관련 함수의 종류
- NVL(data1, data2): data1에 Null이 있으면 data2 출력. Null이 아니면 data1 출력.
- DECODE(data1, data2, data3, ...): data1과 data2가 같으면 data3 출력, 아니면 data4 출력.
2024 SQLD 이기적 강의에서는 함수 파트 학습 후, WHERE로 넘어감.
7절 GROUP BY, HAVING절
1. 집계 함수
- GROUP BY로 집계 (합계, 개수, 최대, 평균 등)를 구할 수 있음. (이기적)
- 집계 함수 (Aggregate Function): 여러 행들의 그룹이 모여 그룹당 하나의 결과를 돌려주는 다중행 함수.
- 집계 함수의 특성
- GROUP BY 절은 행들을 소그룹화
- SELECT 절, HAVING 절, ORDER BY 절에 사용 가능.
- 집계 함수의 종류
2. GROUP BY 절
- SQL문에서 FROM절과 WHERE절 뒤에 위치. | 데이터들을 작은 그룹으로 분류하여 소그룹에 대한 항목별 통계 정보를 얻을 때 사용.
- 튜플을 묶어 단일행으로 표시하므로 출력되는 튜플이 감소. > 입력가능한 컬럼 제한.
- 형식 : SELECT [DISTINCT] 칼럼명 [ALIAS명] FROM 테이블명 [WHERE 조건식] [GROUP BY 칼럼(Column)이나 표현식] [HAVING 그룹조건식] ;
- GROUP BY절과 HAVING절의 특성
- GROUP BY절을 통해 소그룹별 기준을 정하고 SELECT 절에 집계 함수를 사용.
- 집계 함수의 통계 정보는 NULL 값을 가진 행을 제외하고 수행.
- GROUP BY절에서는 SELECT 절과는 달리 ALIAS 명을 사용할 수 없음.
- 집계 함수는 WHERE 절에는 올 수 없음.
- WHERE절은 전체 데이터를 GROUP으로 나누기 전에 행들을 미리 제거.
- HAVING절은 GROUP BY절의 기준 항목이나 소그룹의 집계 함수를 이용한 조건을 표시할 수 있다. | GROUP BY 절에 의한 소그룹별로 만들어진 집계 데이터 중, HAVING 절에서 제한 조건을 두어 조건을 만족하는 내용만 출력한다. | HAVING 절은 일반적으로 GROUP BY 절 뒤에 위치한다.
- COUNT(expr)
- count에서만 * 사용 가능. (주의! COUNT(*): NULL 포함한 행의 개수 ↔ COUNT(컬럼명): NULL제외 행의 개수)
- 모든 자료형 사용 가능.
- MAX, MIN(expr)
- NULL값 무시.
- 모든 자료형 사용 가능.
- AVG(expr), SUM(expr)
- NULL값 무시. (단, 데이터가 모두 NULL일 경우, NULL 반환)
- 숫자형만 사용.
3. HAVING 절
- 집계가 완료된 데이터를 걸러내는 문법. (이기적)
- HAVING절은 WHERE절과 비슷하나 그룹을 나타내는 결과 집합의 행에 조건이 적용된다는 점에서 차이. (WHERE에서는 집계함수 사용 불가 - 실행순서 때문)
- ex) SELECT POSITION 포지션, ROUND(AVG(HEIGHT),2) 평균키 FROM PLAYER GROUP BY POSITION HAVING MAX(HEIGHT) >= 190;
※ DECODE 함수 : 'if else문', 'case when ~ then end구문' 과 유사. 형식 ▷ DECODE(컬럼명, 조건1, 결과1, 조건2, 결과2, 조건3, 결과3, ....)
8절 ORDER BY절
1. ORDER BY 정렬
- 조회된 데이터들을 다양한 목적에 맞게 특정 칼럼을 기준으로 정렬하여 출력하는데 사용.
- 정렬 방식을 지정하지 않으면 기본적으로 오름차순이 적용
- SQL 문장의 제일 마지막에 위치
2. SELECT 문장 실행 순서
- FROM 테이블명 ▷ WHERE 조건식 ▷GROUP BY 칼럼 or 표현식 ▷ HAVING 그룹조건식 ▷ ORDER BY 칼럼 or 표현식 (중요!)
- ORDER BY절에서 SELECT절에서 정의하지 않은 칼럼을 사용해도 무방. (관계형 데이터베이스가 데이터를 메모리에 올릴 때 행 단위로 모든 칼럼을 가져오기 때문)
- 컬럼명 외에 별칭이나 컬럼의 위치를 나타내는 숫자로도 표현할 수 있음.
아래 TOP N 쿼리 파트 건너뛰고 JOIN으로!
3. Top N 쿼리
1) ROWNUM
- (Oracle) ORDER BY절과 WHERE절의 ROWNUM 조건을 같이 사용하는 경우, 원하는 결과를 얻을 수 없음. (데이터의 일부가 먼저 추출되고 정렬 작업을 수행하므로)
- X ▷ SELECT ENAME, SAL FROM EMP WHERE ROWNUM < 4 ORDER BY SAL DESC;
- O ▷ SELECT ENAME, SAL FROM (SELECT ENAME, SAL FROM EMP ORDER BY SAL DESC) WHERE ROWNUM < 4 ;
2) TOP ( )
- (SQL Server)별도 처리 없이 관련 Order By 절의 데이터 정렬 후, 원하는 일부 데이터만 쉽게 출력 가능
- ex) TOP (Expression) [PERCENT] [WITH TIES]
- WITH TIES 옵션 : ORDER BY절의 조건 기준으로 TOP N의 마지막 행으로 표시되는 추가 행의 데이터가 같다면, N+ 동일 정렬 순서 데이터를 추가 반환하도록 지정.
9절 조인(JOIN)
SQLD에서 가장 중요한 개념. 이 부분은 ORACLE을 기준으로 서술함.
1. 조인 개요
- JOIN: 두 개 이상의 테이블을 결합하여 데이터를 출력하는 것.
- 일반적인 경우, 행들은 PRIMARY KEY(PK) or FOREIGN KEY(FK) 값의 연관에 의해 JOIN이 성립.
- 하지만 어떤 경우에는 논리적인 값들의 연관만으로 JOIN이 성립.
- FROM 뒤 테이블이 2개 이상 오면 테이블의 이름을 명시해야 함. (별칭 부여 권장.)
- 단, 별칭을 부여한 경우 별칭으로만 테이블을 표기해야 함.
- 카티션 조인: 각 테이블의 모든 경우의 수를 고려해 합치는 것. (FROM 뒤 테이블만 입력한 경우.)
- 조인조건을 WHERE 절에 넣어 두 테이블에서 필요한 부분만 추출 가능. 이외의 WHERE 조건을 일반조건이라 칭함.
이전글3, 표준조인으로!
[SQLD] 2과목 1장 SQL 기본 요약 - 표준조인 + 2과목 2장 SQL 활용 요약 - 서브쿼리 (구 표준 조인~서브
※ 이전글1: 2023.03.02 - [Cerificate/SQLD] - [SQLD] 2과목 1장 SQL 기본 요약 - 함수, GROUP BY, JOIN (구 함수~JOIN + SQL 노랭이 오답 / 2024 개정사항 반영) ※ 이전글2: 대부분의 독자가 알고 있드시 2024년 SQLD가 개
fromitot.tistory.com
2. EQUI JOIN
이전글3의 표준조인에서 이어짐. 이기적에서는 ppt 한 장을 훑어보는 것으로 동등, 비동등 JOIN 마무리.
- 조인 조건이 모두 '='인 경우
- 두 개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하는 경우에 사용.
- JOIN의 조건은 WHERE 절에 “=” 연산자를 사용하여 표현.
- ex) SELECT 테이블1.칼럼명, 테이블2.칼럼명, ... FROM 테이블1, 테이블2 WHERE 테이블1.칼럼명1 = 테이블2.칼럼명2;
- JOIN 조건 : 대상 테이블의 개수에서 하나를 뺀 N-1개 이상이 필요.
3. Non EQUI JOIN
- 조인 조건이 하나라도 '='가 아닌 경우.
- 두 개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하지 않는 경우에 사용.
- Between, >, >=, <, <= 등의 연산자들을 사용하여 JOIN을 수행.
- ex) SELECT 테이블1.칼럼명, 테이블2.칼럼명, ... FROM 테이블1, 테이블2 WHERE 테이블1.칼럼명1 BETWEEN 테이블2.칼럼명1 AND 테이블2.칼럼명2;
※ JOIN이 필요한 이유 : 정규화에서 출발 (정규화란 불필요한 데이터의 정합성을 확보하고 이상현상(Anomaly) 발생을 피하기 위해, 테이블을 분할하여 생성하는 것) ▷ 분할된 테이블에서 데이터를 조회하려면 테이블 간에 논리적인 연관관계가 필요 ▷ It's JOIN!
이상으로 JOIN 종료. 다음 테마는 이전글3의 서브쿼리.
참고
- Data On Air
- codingD 블로그 (링크) - decode
SQL 자격검정 실전문제 오답
- SQL 문장 종류
- 데이터 조작어 DML
- As-Is : DML은 사용자가 무슨 데이터를 원하는지 명세.
- To-Be : 절차적 데이터 조작어는 어떻게 데이터를 접근하는지 명세. ex) PL/SQL (Oracle), T-SQL (SQL Server)
- SQL 문장
- CONSTRAINT ~~ PRIMARY KEY ~~ ON ();
- NOT NULL도 제약조건 필요
- SQL Server에서는 괄호 사용 X
- SQL Server에서 복수 칼럼 동시 수정 X
- ORACLE : ALTER TABLE OO MODIFY(~~);
- SQL SERVER : ALTER TABLE 00 ALTER(~~);
- PK를 CREATE에서 지정했으면 ALTER TABLE에서 반복할 필요 없음.
- 칼럼 삭제 : ALTER TABLE ~~ DROP COLUMN ~~ ;
- ANSI 표준 = ORACLE
- RENAME 원래 TO 새로 ;
- INSERT INTO ~~ ;
- 삽입 컬럼을 안 적으면, 모든 컬럼 입력.
- 입력되는 컬럼 길이 확인 요망.
- (ORACLE) '' ☞ NULL
- 실행 순서 : FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY
- (SQL SERVER) WITH TIES : 동일값 동시 출력 (공동 순위) (ORDER BY와 사)
- NULL
- 공백 or 숫자 0 or 공집합 아님!
- 모르는 값, 정의되지 않은 값, 데이터 입력이 불가능한 경우.
- NULL과의 모든 비교는 Unknown 반환 (ISNULL 제외)
- DELETE FROM ~
- CASCADE : 관계 테이블 모두 삭제 | Master, Child 모두 삭제.
- SET NULL : Master 삭제 시, 자식 칼럼값이 NULL로 됨.
- SET DEFAULT : Master 삭제 시, 자식 칼럼값이 DEFAULT로 됨.
- RESTRICT : Child 테이블에 PK값이 없어야 Master 삭제 가능.
- INSERT INTO ~
- AUTOMATIC : Master 테이블에 PK 없으면, Master PK 생성 후 Child 입력.
- SET NULL : Master 테이블에 PK 없으면, Child FK를 NULL로 처리.
- DEPENDENT : Master 테이블에 PK 있을 때만 Child 입력 가능.
- 제약조건
- UNIQUE : 테이블 내 중복값 없으나 NULL 가능. | 테이블 당 하나의 제약만 가능.
- PK : UNIQUE + NOT NULL
- NOT NULL : 명시적으로 NULL 입력 방지.
- CHECK (Constraint) : DB에서 데이터 무결성을 유지하고자 특정 컬럼에 설정.
- FK : 테이블간의 관계 정의를 위해 PK를 타 테이블의 외래키가 참조하게 함.
- Foreign Key
- 테이블 생성 시 설정 가능
- NULL 가능
- 복수 존재 가능
- 참조무결성 제약 가능
- 무결성 : 신뢰할 수 있는 서비스 제공을 위해서 의도하지 않은 요인에 의해 데이터, 소프트웨어, 시스템 등이 변경되거나 손상되지 않고 완전성, 정확성, 일관성을 유지함을 보장하는 특성. (by 한국정보통신기술협회)
- 데이터 삭제 (DROP, TRUNCATE, DELETE)
- 모든 삭제 데이터에 대한 로그 필요. ☞ DELETE FROM
- 로그를 남기지 않음.
- TRUNCATE : 테이블의 모든 데이터 삭제 | 디스크 사용량 초기화 | UNDO를 위한 데이터를 생성하지 않으므로 삭제 속도가 DELETE 보다 빠름.
- DROP : 스키마 정의까지 모두 삭제
- 표 참고 (SQL 자격검정 실정문제 p.228 참고)
| DROP | DELETE | TRUNCATE | |
| 구분 | DDL | DML | DDL (일부 DML) |
| ROLLBACK | X | O | X |
| COMMIT | Auto | User | Auto |
| RELEASE | 사용 Storage 모두 Release |
모두 DELETE해도 사용 Storage Release 안됨 |
최초 할당 Storage 제외 Release |
| 데이터 삭제 | 테이블 정의 포함 완전 삭제 | 데이터만 삭제 | 최초 상태로 초기화 |
- 트랜젝션
- 특성
- 원자성 : All or Nothing. 실행 or Not
- 일관성 : 실행 전, 에러 X → 실행 후, 에러 X
- 고립성 : 다른 트랜잭션의 영향으로 잘못된 결과를 만들면 안됨.
- 지속성 : 트랜잭션 성공 시, 갱신된 내용은 영구 저장.
- 격리성이 낮은 경우의 문제
- Dirty Read : 타 트랜젝션으로 변경되었으나 아직 커밋되지 않은 데이터를 읽는 것
- Non-Repeatable Read : 한 트랜잭션에서 동일 쿼리를 두 번 실행했는데 값이 변경되어 결과가 다른 것.
- Phantom Read : 한 트랜잭션에서 동일 쿼리를 두 번 실행했는데 없었던 레코드가 두번째 쿼리에서 나타나는 것.
- ORACLE : DDL Auto Commit | 내부적으로 트랜젝션 종료 ↔ SQL Server : DDL Auto Commit X | Create Table도 Transaction에 포함
- Transaction, Commit, Rollback
- 특성
- 연산자 우선순위 : () > 부정 > 비교 > 논리
- NULL
- (ORACLE) WHERE절에서 IS NULL or IS NOT NULL로 사용
- (SQL Server) '= NULL' 사용
- NULLIF(표현1, 표현2) : 표현1과 표현2가 같으면 NULL, 다르면 표현 1 반환.
- 분자가 0이면 0, 분모가 0이면 ERROR!
- COALESCE : NULL이 아닌 첫째값 반환.
- NULL은 AVG, COUNT 계산에서 제외.
- 내장 함수
- 다중행 함수, 단일행 함수 ☞ 단일값 출력
- CHR : ASC2 코드에 대한 문자 반환 (CHR(10) → 줄바꿈)
- REPLACE : 문자열 치환
- 1/24/60 = 1분, 1/24/(60/10) = 10분
- 중첩된 그룹함수는 1건의 결과값 출력.
- SEARCHED_CASE_EXPRESSION ↔ SIMPLE_CASE_EXPRESSION
- DBMS 옵티마이저는 2개의 테이블과 짝을 이뤄 JOIN
- 23.03.02 이론 작성 시작, 완료.
- 23.03.09 2과목 1장 오답노트 작성.
- 23.03.09 오답노트 완성.
- 24.03.02 이기적 SQLD 개정사항 반영.
728x90
반응형
