SQLD 대비 개념 공부
아래 내용들은 SQLD 준비하면서 여러 구글링한 정보들을 모아 따로 정리한 것입니다.
모두 합격하시길~!
테이블
가로 = 행 = row = 튜플 == 인스턴스 (즉, 한개임)
세로 = 열 = column
SQL 종류(MLCT 암기!)
DML : CRUD → INSERT, SELECT, UPDATE, DELETE (Move)
DDL : CARD → CREATE, ALTER, RENAME, DROP (Dㅔ이터베이스 건들임)
DCL : GR → GRANT, REVOKE (데이터 Control)
TCL : CR → COMMIT, ROLLBACK (TCC 테크닉 커밋)
SQLD는 사실상 SQL 종류별로 시작됨.
DDL(데이터베이스)
1. CREATE
--테이블 생성
CREATE TABLE PLAYER (
PLAYER_ID CHAR(7) NOT NULL,
PLAYER_NAME VARCHAR2(20) NOT NULL);
--PLAYER 테이블을 만들어라.
--PLAYER_ID 는 무조건 7바이트고 공백일 수 없음.
--PLAYER_NAME은 문자열인데 최대 20바이트까지 가능이고 공백일 수 없음.
CHAR(7) : 고정형으로 한글자가 7바이트 차지함.
VARCHAR2(20) : 가변형으로 문자열을 최대 20바이트까지 넣을 수 있음.
- CHAR 사용 이유? → 주민번호, 전화번호처럼 자릿수 고정되어있는 애들한테 쓰면 읽는 속도가 VARCHAR보다 빠름.
2. ALTER + ADD, DROP, MODIFY, RENAME
- ALTER table 테이블이름A ADD 컬럼이름X 데이터유형;
- ALTER table 테이블이름A DROP column 컬럼이름X;
- ALTER table 테이블이름A MODIFY 컬럼이름X 데이터타입;
- ALTER table 테이블이름A RENAME TO 테이블이름B;
--테이블에 데이터 추가, 삭제, 수정, 이름변경
ALTER TABLE PLAYER ADD(ADDRESS VARCHAR2(80));
--테이블에 새로운 컬럼 추가하는데 걔 이름은 ADDRESS 고 80바이트 제한임.
ALTER TABLE PLAYER DROP COLUMN ADDRESS;
-- ADDRESS 컬럼 지우셈
TRUNCATE TABLE PLAYER;
-- 테이블이 헤더 빼고 싹 잘려나감 + 저장공간도 사라짐.
ALTER TABLE TEAM_TEMP MODIFY
(ORIG_YYYY VARCHAR2(8) DEFAULT '20020129'NOT NULL);
-- TEAM_TEMP 테이블에 ORIG_YYYY 를 변경해주셈.(단, 비어있는애만 가능)
-- 기본값은 20020129로 설정할꺼임(딱 8자임), 그리고 공백 안됨.
ALTER TABLE PLAYER RENAME TO PLAYGROUND;
-- 테이블 이름 PLAYER -> PLAYGROUND 로 바꿔주셈.--테이블에 제약사항(CONSTRAINT) 추가, 수정
ALTER TABLE PLAYER ADD CONSTRAINT EMP01_EMPNO_PK PRIMARY KEY(EMPNO);
--테이블에 새로운 제약사항을 추가하는데 PK는 EMPNO임
ALTER TABLE TEAM_TEMP MODIFY
MODIFY ENAME CONSTRAINT EMP01_ENAME_NN NOT NULL;
-- TEAM_TEMP 테이블에 ENAME 의 제약조건을 NOT NULL로 바꾸셈.
DML(조작어)
1. INSERT, SELECT, UPDATE, DELETE
- INSERT INTO 테이블이름(필드이름1, 필드이름2, 필드이름3, ...) VALUES (데이터값1, 데이터값2, 데이터값3, ...);
- SELECT 필드이름 FROM 테이블이름 [WHERE 조건];
- UPDATE 테이블이름 SET 필드이름1=데이터값1, 필드이름2=데이터값2, ... WHERE 필드이름=데이터값;
- DELETE FROM 테이블이름 WHERE 필드이름=데이터값;
INSERT INTO Reservation(ID, Name, ReserveDate, RoomNum)
VALUES(5, '이순신', '2016-02-16', 1108);
-- Reservation 테이블에서 데이터 하나 넣어줄껀데
-- ID=5, Name=이순신, ReserveDate=2016-02-16, RoomNum=1108를 넣어서 추가하셈.
SELECT * FROM Reservation;
-- Reservation 테이블 데이터 모두(*) 뽑으셈
SELECT DISTINCT Name FROM Reservation;
-- Reservation 테이블에서 Name 만 뽑아라. 중복없이(DISTINCT)
UPDATE Reservation SET RoomNum = 2002 WHERE Name = '홍길동';
-- Reservation 테이블에서 RoomNum 을 2002로 업데이트해라.이름이 홍길동인 애를.
DELETE FROM Reservation WHERE Name = '홍길동';
-- Reservation 테이블에서 이름이 홍길동인 애 지우셈.
- : 모든 % : 모든
- : 한 글자 || : 문자와 문자 연결
TCL(컨트롤)
1. COMMIT, ROLLBACK, SAVEPOINT, TRANSACTION
- TRANSACTION : 밀접히 관련되어 분리될 수 없는 1개 이상의 DB 조작 논리적 연산단위
- COMMIT; : 올바르게 반영된 데이터를 DB에 반영
- ROLLBACK TO 시점: 트랜잭션 시작 이전의 상태로 되돌림 COMMIT 되지 않은 모든 트랜잭션을 롤백함.
- SAVEPOINT 시점: 저장 지점
트랜잭션은 모두 성공하던지 아니면 아예 안되던지(원자성, 일관성),
진행하다가 다른애 영향을 안받던지(고립성),
성공하면 저장되어야 함(지속성)
정규화
중복제거 → 왜? 데이터 CRUD 시 발생하는 이상현상 방지(중복 제거하면 무결성 유지)
이상현상이 있는 릴레이션을 분해하여 이상현상을 없애는 과정
참고) https://superohinsung.tistory.com/111
정규화하면 저쪽 테이블에 있는거 갖다쓰지 내꺼에 똑같은거 생성하지 않는다.(중복제거)
즉, JOIN을 써서 연결을 시키는데 이러면 일부만 수정하니까 좋지만, JOIN으로 묶인 만큼 응답시간이 느려질 수 있다.
[요약]
A,B → A따로 B따로 : 1정규화 컬럼(속성)에 여러개가 들어있는걸 분리했다.
A,A’,B → B / A,A’ 로 분리 : 2정규화 테이블 하나에 강의명-강의실 등 관련있는 애가 같이있으면 따로 분리해줌.
A - B - C → A - B / B - C : 3정규화 학번,전공,교수일 경우, 학번이 바뀌면 과도 바뀌니까 전공도 바뀌고, 그 해당 교수도 바뀜. 3개가 관련되어있는거임. 얘네를 따로 분리해줌.
- 정규화 하면 중복을 제거(조인)하므로 성능이 올라감.
- 반정규화 : 하지만 조회 속도가 느리기 때문에 일부러 정규화를 안하기도 함.(이게 꼭 성능이 좋다는말은 아님, 자주 처리하고 속도가 빨라야 되는 상황에서 씀.)
- 어떤 릴레이션 R이 2정규형이고 기본키에 솏하지 않은 속성 모두가 기본키에 “이행적 함수종속”이 아닐 때 제3정규형에 속한다.
- 제 2정규형 : 엔터티의 일반속성은 주 식별자 전체에 종속적이어야 한다.
- 1정규화(1NF) : 같은 성격 내용 컬럼이 연속될 때 컬럼 제거 테이블 , 생성
| 이름 | 나이 | 수강과목 |
| 홍길동 | 20 | C,C++ |
| 이순신 | 21 | Java |
| 이 산 | 22 | DB, 운영체제 |
위에꺼를 중복 제거해서 아래처럼 만든다.
| 이름 | 나이 | 수강과목 |
| 홍길동 | 20 | C |
| 홍길동 | 20 | C++ |
| 이순신 | 21 | Java |
| 이 산 | 22 | DB |
| 이 산 | 22 | 운영체제 |
- 2 정규화(2NF) : 1정규화 진행한 테이블에서 종속을 없애는 것. 복합키 구성일 때 부분적 함수 종속 관계(Partial Dependency) 테이블 분리
이름 나이 수강과목 홍길동 20 C 홍길동 20 C++ 이순신 21 Java 이 산 22 DB 이 산 22 운영체제
이렇게 아래 2개로 나누는 것.
이걸 기본키가 아닌 모든 속성이 기본키에 완전 함수 종속된 상태 라고 한다….
| 학생이름 | 나이 |
| 홍길동 | 20 |
| 이순신 | 21 |
| 이 산 | 22 |
| 학생이름 | 수강과목 |
| 홍길동 | C |
| 홍길동 | C++ |
| 이순신 | Java |
| 이 산 | DB |
| 이 산 | 운영체제 |
- 3정규화(3NF) : 2정규화 진행한 테이블에서 이행적 종속을 없애도록 테이블 분해
| 고객번호 | 이름 | 등급 | 할인율 |
| 1212 | 철수 | A | 50% |
| 1213 | 영희 | B | 30% |
| 1214 | 미애 | C | 10% |
이렇게 되면 고객번호 알면 고객 이름과 등급이 나오고, 할인율까지 연결되서 나온다. 나머지 정보도 알 수 있어지는게 문제임.
X->Y, Y->Z 일 때 X->Z 를 만족해버리면 이행 함수 종속이 발생한다고 한다. 이를 제거하는 것이 제 3정규화.
| 등급 | 할인율 |
| A | 50% |
| B | 30% |
| C | 10% |
| 고객번호 | 이름 | 등급 |
| 1212 | 철수 | A |
| 1213 | 영희 | B |
| 1214 | 미애 | C |
요렇게 만든게 3정규화 상태.
- BCNF(Boyce-codd Normal Form) : 3정규화 강화버전.
| 학생 | 과목 | 교수 | 학점 |
| 1 | AB123 | 김인영 | A |
| 2 | CS123 | Mr.Sim | A |
| 3 | CS123 | Mr.Sim | A |
이걸 분리해서 아예 중복값 자체가 없게 한다. 위에는 단순히 쪼갰다면, 얘는 분리임.
| 교수 | 과목 |
| 김인영 | AB123 |
| Mr.Sim | CS12 |
| 학생 | 과목 | 학점 |
| 1 | AB123 | A |
| 2 | CS123 | A |
| 3 | CS123 | A |
3까지만 나오는듯?
- 반 정규화는 많이 엮여있으니 느려지면서 성능 저하가 와서 반대로 중복을 시켜주는 것.
NULL
Null은 아직 정의되지 않은 값으로 모르는 값이라고도 할 수 있다.
일단 공백이나 0은 아님!
NULL을 더하고 뺴고 곱하고 평균내고 하는건 null인데, 비교문자(> = < ) 이런거는 undefined임
그룹함수는 NULL값은 무시하고 연산한다.
count 셀 때 컬럼이 3개면 셋 다 null이어야 count에서 빠짐.
avg도 NULL을 제외한 대상의 평균을 리턴 → NVL 쓰면 포함계산됨.
SUM도 null이 포함되어있으면 걔 뺴고 계산하고, null 포함된 컬럼 두개 SUM 하면 가로줄로 둘 다 값 있는것만 계산함.
SQL 논리연산자
SELECT PLAYER_NAME FROM PLAYER …
- BETWEEN a AND b : a b 와 값 사이에 있으면 됨
- IN (list) : 리스트에 있는 값 중 어느 하나라도 일치
- NOT IN (list) : 리스트에 있는 값이 없어야함.
- IS NULL : NULL 값인 경우 (Oracle은 VARCHAR2 빈 문자열을 NULL 로 판단)
- IS NOT NULL : NULL 값이 아닌 경우
- LIKE “비교문자열” : 비교문자열과 일치하는 경우
- ALL 조건 : 조건 전부 만족
- ANY 조건 : 조건 하나라도 만족
- 키 종류
기본키 PK : 독립적인 대표 키
외부키 FK : 다른 테이블 기본키를 내가 가져와서 요소로 씀(NULL가능)
고유키 UK : 고유키 정의
NOT NULL : 공백안됨
CEHCK : 입력값 체크해서 제한할꺼임
SELECT PLAYER_NAME FROM PLAYER
-- PLAYER 테이블에서 PLAYER_NAME 을 뽑아라....단,
--1) ID K2 팀 가 인 사람
WHERE TEAM_ID = ‘K2’;
--2) 괄호 안 리스트(K2,K7) 중 해당되는 사람 == K2 또는 K7
WHERE TEAM_ID IN (‘K2’,‘K7’);
--3) HEIGHT 가 170~180 사이 인 사람.
WHERE HEIGHT BETWEEN 170 AND 180;
--4) POSITION 이 null로 비어있는 사람.
WHERE POSITION IS NULL;
--5) 이름이 김으로 시작하는 사람.
WHERE PLAYER_NAME LIKE '김%';
WHERE PLAYER_NAME LIKE '%김%'; --김이 들어가는 사람
WHERE PLAYER_NAME LIKE '%김'; --김으로 끝나는 사람
연산자 우선순위
()괄호[우선순위 높음] > 산술연산자 > 비교연산자 > BETWEEN연산자 > 논리연산자
즉, () %X+- <> BETWEEN AND OR
함수 종류는 크게 SQL이 제공하는 내장함수 / 사용자 정의 함수로 나뉜다.
내장함수는 또다시 단일행 함수, 다중행 함수로 나뉜다.
- 단일행 함수는 가로줄 하나(row)에 대해 개별로 작용해서 데이터 값을 조작할 수 있다.(SELECT, WHERE, ORDER BY, UPDATE SET 절에 사용 가능)
- 다중행 함수는 반대로 개별 row별이 아니라 시트 전체적으로 row간의 관계에 대해서 조작할 수 있다.(총합계, 평균, 최소값, 최대값, 소계등)
단일행 함수
문자, 숫자, 날짜, Null 형 데이터가 단일행에 포함된다.
1. 문자 대소(UPPER, LOWER, INITCAP/**대문자, 소문자, 첫글자 대문자)
SELECT UPPER(ENAME), LOWER(ENAME), INITCAP(ENAME)
FROM EMP;
--대문자, 소문자, 첫글자 대문자로2. 문자 조작(SUBSTR, INSTR, LENGTH, LPAD, RPAD, LTRIM, RTRIM)
SELECT SUBSTR('ABCDE',3,2) FROM DUAL;
-- ABCDE 에서 3번째 위치에서 2개의 문자열 읽는다. (DUAL은 계산용 기본테이블)
-- [CD] 가 출력됨.
SELECT INSTR('Oracle Database', 'Database', 3) AS result1;
-- Oracle Database 에서 Database를 찾는데 3번째부터 찾아라.
-- result1 값이 6으로 출력됨. a부터 6 뒤로 가면 나오거든.
--#### 심화과정 SUBSTR+INSTR ####
SELECT SUBSTR('jd0922@naver.com',1, INSTR('jd0922@naver.com', '@')-1 ) "A"
FROM DUAL;
-- jd0922@naver.com에서 1번째부터 읽으면서, @를 기준으로 거꾸로 뽑으면(-1)
-- A의 값이 jd0922로 출력됨.
SELECT ENAME, LENGTH(ENAME) FROM EMP;
--EMP에 SMITH가 있었다면, SMITH와 글자수인 5가 출력됨.
SELECT ENAME, DEPTNO, LPAD(DEPTNO, 5, 'X'), RPAD(DEPTNO,6,'X') FROM EMP;
--LPAD("값", "총 문자길이", "채움문자")
--지정한 길이 만큼 왼쪽부터 특정문자로 채워준다.
--DEPTNO이 20이라면 [XXX20] 이 출력됨
--RPAD("값", "총 문자길이", "채움문자")
--지정한 길이 만큼 오른쪽부터 특정문자로 채워준다.
--DEPTNO이 20이라면 [20XXXX] 이 출력됨
SELECT ENAME, LTRIM(ENAME, 'C'), RTRIM(ENAME, 'K') FROM EMP;
-- LTRIM('문자열' 또는 칼럼명, '제거할 문자')
--첫 글자가 '제거할 문자'일 경우 제거한 결과값 출력
--ENAME이 CLACK 인 경우, [LARK]이 출력됨
-- RTRIM('문자열' 또는 칼럼명, '제거할 문자')
--끝 글자가 '제거할 문자'일 경우 제거한 결과값 출력
--ENAME이 CLACK 인 경우, [CLAR]이 출력됨
3. 숫자 조작(round, trunc, MOD)
- ROUND("값","자리수까지") : 반올림
- TRUNC("값", "옵션") : 옵션자리까지 소수점 자름
- MOD(m,n) : m을 n으로 나누었을 때의 나머지 반환
4. 날짜형 함수(SYSDATE, GETDATE, TO_NUMBER)
-- 현재 날짜, 시간 가져오기
-- 오라클
SELECT SYSDATE FROM DUAL ;
-- sql server
SELECT GETDATE() AS CURRENTTIME ;- EXTRACT : 날짜형 데이터를 찢어서 새로운 컬럼형태로 뽑는 것. (Ex. 고용날짜에서 년/월/일 분리해서 뽑음)
-- 년, 월, 일 출력하기
-- 오라클
SELECT ENAME, HIREDATE,
EXTRACT (YEAR FROM HIREDATE) 입사년도,
EXTRACT (MONTH FROM HIREDATE) 입사월,
EXTRACT (DAY FROM HIREDATE) 입사일
FROM EMP;
-- sql server
SELECT ENAME, HIREDATE,
TO_NUMBER (TO_CHAR(HIREDATE, 'YYYY') 입사년도,
TO_NUMBER (TO_CHAR(HIREDATE, 'MM') 입사월,
TO_NUMBER (TO_CHAR(HIREDATE, 'DD') 입사일
FROM EMP ;
5. Null과 관련된 함수(NVL, COALESCE, NULLIF)
단일행 함수 중에는 NULL 값과 관련된 함수들이 있습니다.
이러한 함수들은 NULL 값을 다루거나 NULL 값을 특정 값으로 대체하는 등의 작업을 수행합니다.
주로 데이터 정제나 연산에서 활용됩니다. 여기에는 일반적으로 사용되는 몇 가지 함수와 그에 대한 예제를 살펴보겠습니다.
1) NVL 함수
NVL 함수는 첫 번째 인자 값이 NULL인 경우 두 번째 인자 값을 반환하고, 첫 번째 인자 값이 NULL이 아닌 경우 첫 번째 인자 값을 반환합니다.
-- commission_pct이 null값이면 0으로 대체해
SELECT employee_id, NVL(commission_pct, 0) AS commission_pct
FROM employees;
이 쿼리는 employees 테이블에서 commission_pct 컬럼을 조회하며, 만약 commission_pct가 NULL인 경우에는 0으로 대체하여 반환합니다.
2) COALESCE 함수
COALESCE 함수는 인자로 전달된 값 중 NULL이 아닌 첫 번째 값을 반환합니다.
--commission_pct 중에 null이 아닌 첫번째 값 반환
SELECT employee_id, COALESCE(commission_pct, 0) AS commission_pct
FROM employees;
NVL 함수와 비슷하지만, NVL은 두 개의 인자만을 받는 반면 COALESCE는 여러 개의 인자를 받을 수 있습니다. 따라서 COALESCE는 더 유연한 대체 기능을 제공합니다.
3) NULLIF 함수
NULLIF 함수는 두 인자가 같으면 NULL을 반환하고, 다르면 첫 번째 인자 값을 반환합니다.
-- if commission_pct=='ORACLE' 이면 null, 아니면 commission_pct값
****SELECT employee_id, NULLIF(commission_pct,'ORACLE' ) AS commission_pct
FROM employees;
- AS ~ : ALIAS를 뜻하므로 별명이 된다.
다중행 함수
다중행 함수는 집계함수, 그룹함수, 윈도우 함수로 구분된다.
- 집계함수 : COUNT, AVG, SUM, MIN, MAX, STDDEV (표준편차), VARIANCE(분산)
- 그룹함수 : ROLLUP(소계), CUBE(모든 조합의 그룹별 소계), GROUPING SETS(다양한 소계 집합)
- 윈도우 함수
- 그룹 내 순위(RANK) 관련 함수 : RANK, DENSE_RANK, ROW_NUMBER
- 그룹 내 집계(AGGREGATE) 관련 함수 : SUM, MAX, MIN, AVG, COUNT (sql server는 OVER절의 ORDER BY 지원 X)
- 그룹 내 행 순서 관련 함수 : FIRST_VALUE, LAST_VALUE, LAG, LEAD (오라클에서만 지원)
- 그룹 내 비율 관련 함수 : CUME_DIST, PERCENT_RANK, NTLE, RATIO_TO_REPORT
- 집계함수,그룹함수 : 여러 row로 한가지 결과값 나온다
- 그룹 함수는 일반적으로 NULL 값을 제외하여 처리한다.
- 그룹 함수는 WHERE절에 사용할 수 없다.
1. 집계함수 : 단순계산(COUNT, SUM, AVG, MAX, MIN)
| 이름 | 의미 |
| COUNT( * ) | 컬럼의 행 개수 |
| SUM( * ) | NULL을 제외한 모든 행의 합 |
| AVG( * ) | NULL을 제외한 모든 행의 평균 |
| MAX( * ) | NULL을 제외한 최대값 |
| MIN( * ) | NULL을 제외한 최소값 |
- DISTINCT : 중복제외
- NVL : null 을 다른걸로 바꿔서 계산
-- 이정도는 간단하쥬?
SELECT COUNT(*) FROM tb; --전체갯수
SELECT COUNT(Name) FROM tb; --이름 갯수
SELECT COUNT(DISTINCT Country) FROM tb; --중복없이 도시갯수
SELECT AVG(Age) FROM tb; --나이 평균
SELECT AVG(NVL(Age,0)) FROM tb; --null값 0으로 바꿔서 나이 평균
SELECT SUM(Age) FROM tb; --나이 총합
SELECT MAX(Age) FROM tb; --최대 나이
SELECT MIN(Age) min_age FROM tb; --최소나이 이름은 min_age
1-1) 집계함수 : 그룹화(GROUP BY) : ~~별
//나라별 나이 평균을 구하자.
SELECT Country, AVG(Age) FROM tb GROUP BY Country;
// 부서별 월급 총합을 구하자.
SELECT DEPTNO, SUM(SAL) FROM EMP GROUP BY DEPTNO;
// 직업 별 사원 수를 조회해보자.
SELECT JOB, COUNT(JOB) FROM EMP GROUP BY JOB;
1-2) 집계함수 : 그룹화의 조건(HAVING) : 단,~~만 주셈
where절 같은 거임
//나라별 나이 평균을 구하자. 단, 평균나이 23세 이상만
SELECT Country, AVG(Age) FROM tb
GROUP BY Country HAVING AVG(Age) >= 23
// 부서별 월급 총합을 구하자. 단, 월급합이 7000 이상만.
SELECT DEPTNO, SUM(SAL) FROM EMP
GROUP BY DEPTNO HAVING SUM(SAL) >= 7000;
2. 그룹함수 : 소계/총계 등이 같이 나옴.(ROLLUP, CUBE, GROUPING SETS, GROUPING)
그룹 함수인 ROLLUP, CUBE, GROUPING SETS, GROUPING은 데이터베이스에서 다양한 레벨의 집계를 수행하기 위해 사용됩니다. 이들은 주로 GROUP BY 구문과 함께 사용되며, 다양한 계층의 집계 결과를 반환하여 보고서 작성이나 데이터 분석에 유용합니다. 각 함수의 기능과 차이점에 대해 설명해보겠습니다.
2-1) ROLLUP
ROLLUP 함수는 GROUP BY 절에서 사용되며, 지정된 열 또는 열 그룹에 대한 계층적인 합계를 생성합니다. ROLLUP을 사용하면 지정된 열 또는 열 그룹에 대한 총계 및 소계가 생성됩니다. 각 총계 및 소계는 해당 열 또는 열 그룹의 순서에 따라 생성됩니다.
-- 월별 매출 테이블에서 상품ID, 월, 매출액의 합을 구한다. 단, 소그룹으로 묶을 때 상품ID를 기준으로 묶어서 걔네 개별 소계도 내줘.
SELECT 상품ID, 월, SUM(매출액)
FROM 월별매출
GROUP BY ROLLUP(상품ID, 월);
개별 소계와 전체합계. 단, 개별 소그룹은 처음 명시한 컬럼 기준으로 만들어짐.
2-2) CUBE
CUBE 함수는 GROUP BY 절에서 사용되며, 지정된 열 또는 열 그룹에 대한 모든 가능한 조합의 합계를 생성합니다. CUBE를 사용하면 ROLLUP과 유사하게 총계 및 소계가 생성되지만, 모든 가능한 조합에 대한 합계가 생성됩니다.
-- 월별 매출 테이블에서 상품ID, 월, 매출액의 합을 구한다. 단, 맨 위에 상품ID와 월별 총 합 쓰고, 상품ID로 소그룹 묶어서 개별 소계도 내줘.
SELECT 상품ID, 월, SUM(매출액)
FROM 월별매출
GROUP BY CUBE(상품ID, 월);
최상단 전체합계, 월별합계, 그 아래에 소그룹별 소계
2-3) GROUPING SETS
GROUPING SETS 함수는 여러 그룹화 기준에 따라 그룹화된 데이터를 생성합니다. GROUPING SETS는 ROLLUP과 CUBE보다 더 유연한 그룹화를 제공합니다. 특정 열 또는 열 그룹에 대해 그룹화된 결과를 개별적으로 지정할 수 있습니다.
--월별 매출 테이블에서 상품ID, 월, 매출액의 합을 구한다. 단, 상품ID와 월의 합계만 추출해.
SELECT 상품ID, 월, SUM(매출액)
FROM 월별매출
GROUP BY GROUPING SETS(상품ID, 월);
다른거 없이 소계들만 추출함.
GROUPING SETS 함수는 각각의 컬럼으로 GROUP BY한 값을 UNION ALL 한 것과 동일한 결과를 보여준다.
2-4) GROUPING
GROUPING 함수는 ROLLUP, CUBE 또는 GROUPING SETS와 함께 사용될 때 각 열의 그룹화 수준을 식별합니다. GROUPING 함수는 특정 열이 총계 또는 소계의 일부인지 여부를 확인하는 데 사용됩니다.
그룹화가 되면 0, 아니면 1을 반환하는데, 즉 앞서 봤던 'NULL' 부분에만 1이 나온다고 생각하면 쉽다.
-- GROUPING에 묶여있는 department_id와 job_id는 null이 아니라 1이 나온다.
SELECT department_id, job_id, SUM(salary), GROUPING(department_id) AS dept_grouping, GROUPING(job_id) AS job_grouping
FROM employees
GROUP BY ROLLUP(department_id, job_id);
SELECT CASE WHEN GROUPING(year1) = 1 THEN '총계'
ELSE year1 END AS 연도_총계,
CASE WHEN GROUPING(ch_code) = 1 THEN '소계'
ELSE ch_code END AS 채널코드_총계,
SUM(sales_amt) AS tot_amt
FROM (SELECT CAST(YEAR(order_date) AS VARCHAR) AS year1,
CAST(channel_code AS VARCHAR) AS ch_code,
sales_amt
FROM [ORDER]) A
GROUP BY year1, ch_code
WITH ROLLUP
- CAST : 숫자형 → 문자형으로 바꿔준다.
*** ORDER BY(정렬하라!)
- SQL 문 중 제일 뒤에 있음. ASC 오름차순(기본값) DESC 내림차순
- Oracle에서는 NULL을 가장 큰값으로 취급, SQL Server 에서는 NULL을 가장 작은 값으로 취급한다.
JOIN(두 개 이상의 테이블을 연결 또는 결합)
두개의 테이블 중 겹쳐지는게 있고 그걸 기준으로 결합해서 데이터를 추출하는 방식.
- 5가지 테이블을 JOIN 하려면 최소 4번의 JOIN 과정 필요.(N-1)
- INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN 4가지가 있고 영역은 아래 이미지와 같다.
- orders 테이블
| order_id | customer_id | order_date |
| 1035 | 1 | 2021-10-14 |
| 1036 | 2 | 2021-10-15 |
| 1037 | 3 | 2021-10-16 |
- customer 테이블
| customer_id | name | address | phone |
| 1 | 김호준 | 경기도 파주시 | 010-0000-0002 |
| 2 | 이성민 | 서울특별시 송파구 | 010-0000-0101 |
| 3 | 남건우 | 강원도 춘천시 | 010-0000-0301 |
| 4 | 이성규 | 대전광역시 유성구 | 010-1234-5678 |
1. INNER JOIN(교집합)
- A, B 각각 존재하는 동물 종류는 9가지였다.
- A와 B에 모두 동시에 존재하는 동물은 총 7 종류로 나왔다.
- 즉 7개의 교집합만을 산출해서 보여준 것이다.
-- order테이블과 customer 테이블 join 해서
--중복되는 row만 합쳐서 보여줘
SELECT order_id, name, address, phone, order_date
FROM orders
INNER JOIN customer
ON orders.customer_id = customer.customer_id;customer_id가 겹치는건 총 3개 행이기 때문에 딱 그 3개만 나옴.
| order_id | name | address | phone | order_date |
| 1035 | 김호준 | 경기도 파주시 | 010-0000-0002 | 2021-10-14 |
| 1036 | 이성민 | 서울특별시 송파구 | 010-0000-0101 | 2021-10-15 |
| 1037 | 남건우 | 강원도 춘천시 | 010-0000-0301 | 2021-10-16 |
2. LEFT OUTER JOIN(한쪽모두)
- A에 해당하는 동물들은 모두 호출 되었다.
- B에서는 A와 동일한 종이 있는 경우에만 호출 되었다.
- B에서는 Elephant와 Mouse가 없는 것을 확인할 수 있었다.
-- order테이블과 customer 테이블 join 해서
--order 기준으로 중복되는 row만 합쳐서 보여줘
SELECT order_id, name, address, phone, order_date
FROM orders
LEFT JOIN customer ON orders.customer_id = customer.customer_id;
위와 동일함. order가 갯수가 더 적고 모조리 포함되어있어서 그럼.
| order_id | name | address | phone | order_date |
| 1035 | 김호준 | 경기도 파주시 | 010-0000-0002 | 2021-10-14 |
| 1036 | 이성민 | 서울특별시 송파구 | 010-0000-0101 | 2021-10-15 |
| 1037 | 남건우 | 강원도 춘천시 | 010-0000-0301 | 2021-10-16 |
3. RIGHT OUTER JOIN(한쪽모두)
- 위와 반대로 B는 모두 있고 A는 동일한거만 있는거.
-- order테이블과 customer 테이블 join 해서
-- customer기준으로 중복되는 row만 합쳐서 보여줘
SELECT order_id, name, address, phone, order_date
FROM orders
RIGHT JOIN customer ON orders.customer_id = customer.customer_id;
customer가 갯수가 더 많기 때문에 모조리 나오되, order에서 없는건 null처리됨.
| order_id | name | address | phone | order_date |
| 1035 | 김호준 | 경기도 파주시 | 010-0000-0002 | 2021-10-14 |
| 1036 | 이성민 | 서울특별시 송파구 | 010-0000-0101 | 2021-10-15 |
| 1037 | 남건우 | 강원도 춘천시 | 010-0000-0301 | 2021-10-16 |
| NULL | 이성규 | 대전광역시 유성구 | 010-1234-5678 | NULL |
4. FULL OUTER JOIN(합집합)
- 이번에는 A와 B에 존재하는 모든 동물의 종이 조회가 되었다.
- 그래서 A에는 racoon과 zebra 빼고 모두 호출된 반면, B에서는 elephant와 mouse를 제외하고 모두 호출 되었다.
- 이 경우 모든 데이터를 조회할 수 있는 장점이 있는 대신, 데이터 처리 리소스 비용이 많이 드는 단점 또한 있으니 적절하게 사용해야 한다.
-- order테이블과 customer 테이블 join 해서
--order 기준으로 중복되는 row만 합쳐서 보여줘
SELECT order_id, name, address, phone, order_date
FROM orders
FULL JOIN customer ON orders.customer_id = customer.customer_id;
위와 동일함. order가 갯수가 더 적고 모조리 포함되어있어서 그럼.
| order_id | customer_id | order_date | customer_id | name | address | phone |
| 1035 | 1 | 2021-10-14 | 1 | 김호준 | 경기도 파주시 | 010-0000-0002 |
| 1036 | 2 | 2021-10-15 | 2 | 이성민 | 서울특별시 송파구 | 010-0000-0101 |
| 1037 | 3 | 2021-10-16 | 3 | 남건우 | 강원도 춘천시 | 010-0000-0301 |
| NULL | NULL | NULL | 4 | 이성규 | 대전광역시 유성구 | 010-1234-5678 |
방금꺼는 테이블 두개 갖다놓고 합쳤잖아?
일반 집합 연산자
- UNION : 합집합(중복 행 1개로) 정렬
- UNION ALL : 합집합(중복 행도 표시) 정렬X
- INTERSECT : 교집합 ( 중복 행 1개로 )
- MINUS : 차집합(중복 행 1개로) + Except
- CROSS JOIN : 곱집합(PRODUCT)
이런 애들도 있음.
SELECT DEPTNO,JOB FROM EMP1
UNION
SELECT DEPTNO,JOB FROM EMP2
ORDER BY DEPTNO, JOB
이런식으로 비슷한 정렬과 비슷한 테이블을 합하고 곱하고 하는거다.
UNION || 두 테이블의 결과를 중복을 제거해서 보여준다.
UNION ALL || 두 테이블의 결과를 중복을 허용해서 보여준다.
CROSS JOIN이 생소한 개념인데 한쪽이 나머지를 다 빨아먹는거임(?)
SELECT*FROM A, B;
SELECT*FROM A CROSS JOIN B;
--두개가 같은거임. 하나로 다 출력되니까.
즉, 상호 조인의 결과로 전체 행 갯수는 두 테이블 행의 갯수의 곱이다. (a.length * b.length)
카티션 곱(CARTESIAN PRODUCT)라고도 함.
NATURAL JOIN
두 테이블 간의 동일한 이름을 갖는 모든 컬럼들에 대해 등가조인(EQUI JOIN)을 수행한다.
- 두 테이블이 갖는 공통 컬럼에 대해서 Inner Join은 별개의 컬럼으로, Natural Join은 하나의 컬럼으로 나타낸다.
그냥 id를 기준으로 합쳐버린 것.
USING 조건절
USING 조건절을 이용하면 같은 이름을 가진 칼럼들 중에서, 특정 칼럼에 대해서만 선택적으로 EQUI JOIN 실행
SELECT * FROM t_dept JOIN dept_temp USING (DEPTNO);
10 여러개, 20 여러개가 아니라 하나만 나온 이유는 쟤만 합쳤기 때문.
ON 조건절
ON 조건으로 필터링이 된 레코들간 JOIN이 이뤄진다.
- ON : JOIN 을 하기 전 필터링을 한다 (=ON 조건으로 필터링이 된 레코들간 JOIN이 이뤄진다)
- WHERE : JOIN 을 한 후 필터링을 한다 (=JOIN을 한 결과에서 WHERE 조건절로 필터링이 이뤄진다)
순위형 함수(RANK, DENSE_RANK, ROW_NUMBER) -> 꼭나옴
- RANK : 동일한 값에 대해서는 동일한 순위를 부여(1,2,2,4)
- DENSE_RANK : 동일한 순위를 하나의 등수로 간주(1,2,2,3)
- ROW_NUMBER : 동일한 값이라도 고유한 순위 부여(1,2,3,4)
속성이 가질 수 있는 값의 범위 는 도메인이다.
하나의 키로 특정 행을 바로 찾아낼수 있는 고유한 데이터 속성 = 유일성
[식별자 특징]
유일성 - 주식별자는 모든 인스턴스를 유일하게 구분가능 해야한다.
최소성 - 주식별자를 구성하는 속성 수는 1개이어야 한다.
불변성 - 주식별자는 자주 변경되지 않아야 한다.
존재성 - 주식별자는 항상 데이터 값이 있어야 한다.
대표성 - 주식별자는 엔터티를 대표 할 수 있어야 한다.
SQL SERVER vs ORACLE
- AVG, SUM 등 계산과 그룹 함수는 WHERE절에 사용할 수 없다.
- SQL SERVER는 마이크로소프트꺼고 거 간단하고 쉬운 구문이다. 단 윈도우에서밖에 사용 안됨.
- ORACLE은 오라클껀데 다른 OS 다 지원함. 단 좀 복잡함.
- 둘다 SQL을 지원하지만 명령어가 약간씩 다름.
| SQL SERVER | ORACLE |
| SUBSTRING | SUBSTR |
| LEN | LENGTH |
| CHARINDEX | INSTR |
| 숫자형은 거의 비슷하지만 날짜형은 거의 다름 | |
| GETDATE | SYSDATE |
| DATEADD(월 뿐만 아니라 모든 단위 날짜 연산 가능) | ADD_MONTHS |
| DATEDIFF(두 날짜 사이의 년, 월, 일 추출) | MONTHS_BETWEEN |
| CONVERT(포맷변환) CAST(단순변환) |
TO_NUMBER, TO_DATE, TO_CHAR (포맷변환 시) |
| VAR(분산) | VARIANCE |
| STDEV(표준편차) | STDDEV |
| ISNULL(NULL 대체) | NVL |
COALESCE : CASE / if 같은듯.