sqld 혼동사항 정리

기본엔터티 : 독립적으로 생성가능한 원래 업무에 존재하는 정보 학생 고객 회원 등등

중심엔터티 : 기본엔터티에서 발생하고 데이터양이 많고 업무에 있어 중심이 되는 역할 주문, 예약, 대출, 수강 등등

행위엔터티 : 두개 이상의 부모엔터티로부터 발생 자주 내용이 바뀌거나 추가되는 엔터티 주문내역, 수강내역, 예약내역

관계엔터티 : 엔티티간의 관계만 따로 엔터티로 표현한것 추가 속성이 추가도 가능하고 없어도 됌

교차엔터티 : nm관계 엔터티 사이에 연결만 하는 엔터티 추가속성이 없고 외래키로만 이루어 짐 1:n n:1로 변환

 

관계명 : 수강한다 신청한다 결제한다

관계차수 : 1:n, n:m

 

기본속성 : 발급 갯수, 사용기간, 이름 등등 기본저긍로 주어진 속성

파생속성 : 할인가, 평균판매량 등등 계산되어진 속성

설계속성 : 분류와 같이 실제로 존재하지는 않지만 필요에 의해 만들어진 속성

 

단일 식별자 : 하나의 속성으로 구성된 식별자 분할 불가

복합 식별자 : 둘 이상의 속성으로 구성된 식별자 분할 가능 주소같은 경우 1:n에서 n의 복합식별자의 경우 유일성은 깨질수있음

본질 식별자 : 업무에 의해 만들어지는 식별자 상품번호 학번 같은 경우

인조 식별자 : 업무적으로 만들어진건 아니지만 원조 식별자가 복잡해서 인위적으로 만들어쓰는 식별자

 

정규화

제1정규화 : 한 속성에 값이 여러개 인걸 분할한다 속성과 값은 1대1

제2정규화 : 주식별자와 모든 속성은 연관을 가진다 부식별자와 일반속성간에 연관관계가 있으면 안됌 

제3정규화 : 일반속성들간에 연관관계를 가지면 안된다. 분할해줌 이행함수 종속성 제거

이행함수 : A>B, B>C라는 종속관계가 있을때 A>C가 성립되는것

 

반정규화

테이블 반정규화

1. 테이블 병합 : 1:1 테이블 병합, 1:m 테이블 병합, 슈퍼타입/서브타입 관계 테이블 병합(One to One Type, Plus Type, Single Type)

2. 테이블 분할 : 수직분할(특정 속성만 접근이 빈번할때 1:1 관계로 분할), 수평 분할(속성값에 따라서 구분해서 조회 파티셔닝 기능) 

수직 수평인 이유는 테이블을 기준으로 쪼갬 행이 데이터 열이 속성

3. 테이블 추가 : 중복 테이블 추가, 통계 테이블 추가, 이력 테이블 추가, 부분 테이블 추가

칼럼 반정규화

1. 중복 칼럼 추가

2. 파생 칼럼 추가

3. 이력 테이블 칼럼추가

4. 복합식별자인 pK에 의한 칼럼추가 : 복합식별자의 부분을 뜯어서 다른 테이블 일반속성에 별도로 저장

5. 이전 작업 상태 컬럼을 추가해서 같이 저장

관계 반정규화

여러 조인이 연쇄적으로 있을때 테이블간에 관계를 추가해서 조회성능 상향하는 방법

 

 

 

순수관계연산자

SELECT , PROJECT, JOIN, DIVIVE, UNION, Difference

 

서브쿼리

스칼라 서브쿼리 : 대부분 select절에 오지만 다른데 들어가도 됌

인라인 뷰 : from절에 옴

중첩 서브쿼리 : WHERE절, HAVING절에 옴

 

묵시적형변환

숫자형 컬럼에 숫자로 이루어진 문자형 데이터가 들어가면 형변환 가능 col1(number) = '1'(varchar) > 가능

반대로 문자형 데이터에 숫자가 들어가면 형변환 불가능 col1(varchar) = 1(number) > 불가능

 

 

sql구문 작동순서 

FROM > WHERE > GROUP BY> HAVING > SELECT > ORDER BY

SELECT 절의 alias를 WHERE, GROUP BY, HAVING은 사용 불가

HAVING절에는 alias사용하지 않고 COUNT나 AVG등 원형을 씀

ORDER BY는 select 갯수에 따라 1,2,3 같이 숫자로 지정할수있음 

order by에 case문이나 연산이 들어가도 실제 값이 바뀌지는 않고 정렬기준만 바뀜 값은 비교만 하고 사라짐

서브쿼리는 select alias사용가능

 

IFNULL(NULL,'A'), NVL(NULL, 'A') : 둘다 인수1이 null이면 인수2를 반환한다. (sql 차이)

NULLIF(NULL, 'A') : 인수1과 인수2가 같으면 null, 같지 않으면 인수1을 반환한다. >IFNULL과 혼동주의

COALESCE(NULL, 'A') : null이 아닌 최초의 인수를 반환한다.

DECODE('X', 'Y', 'Z', null) : x가 y면 z반환 아니면 null반환

NVL(NULL, 4) : 인수1의 null이면 인수2반환, 인수2가 null이 아니면 인수 1반환

NVL2(COL2, null, 3) : col2가 null이 아니면 인수2반환, null이면 인수3반환

 

숫자함수

ABS(-3.8) : 절대값으로 바꿈 3.8

SIGN(-3.8) : 부호만 반환함 음수면 -1, 양수면 1

CEIL(-3.8) : 올림 음수일때는 -3

FLOOR(-3.8) : 내림 음수일때는 -4 양수일때는 버림과 같음

ROUND(-3.8) : 반올림 절대값으로 계산하고 부호붙힘 -4 

 

문자함수

LPAD(문자열, 문자길이, 채울문자) : 문자열 왼쪽에 문자길이가 될때까지 문자를 채움 3번인자 없을시 공백

LPAD(' ', 4*(LEVEL-1)) ||  : 공백문자열에 길이만큼 공백으로 채움

LTRIM('XYZXYZ', 'XYZ') : 왼쪽부터 x or y or z가 포함된 문자 삭제

RTRIM('XYZX', 'XY') : 오른쪽부터 X or Y가포함된 문자 삭제

INSTR(문자열, 찾을문자, 시작위치, 출현순서) : 포함된 문자열의 위치 찾는 함수

REGEXP_INSTR(문자열, 패턴, 시작위치, 출현순서) : 포함된 정규식 패턴으로 검색해서 위치 찾는 함수

REGEXP_INSTR('A1B2C3D4', '[0-9]', 1, 3) : 숫자가 들어가는 패턴 처음부터 3번째 위치 답은 6

 

집계함수( 집계함수의 조건은 HAVING절 이용해야함 alias 사용주의)

SUM(col1+col2+col3)과 SUM(col1) + SUM(col2) + SUM(col3)차이

행끼리 데이터를 더할시 null이 있으면 null반환

열끼리 데이터를 더할시 null이 있으면 빼고 계산

SUM(1), SUM(2) , SUM(*)와 같이 상수나 *는 모든 값을 더하지만 SUM(col1)같이 컬럼명이 들어가면 null제외
AVG나 다른 집계함수도 동일

 

IN은 or절로 (인수1, 인수2, 인수3) 3개중에 하나만 맞아도 만족함

NOT IN 은 and절로 (인수1, 인수2, 인수3) 모두 만족해야함 NULL은 is NULL이나 IS NOT NULL로 비교

SELECT COL2 FROM SAMPLE WHERE COL1 IN (3, NULL) 같은 경우 COL1의 NULL일 경우 false라 아무값도 나오지 않음 col1 = NULL로 처리

(COL1, COL2) IN ((20,10),(0,10))의 경우 (COL1 = 20 AND COL2 = 10) or (COL1 = 0 AND COL2 = 10) 으로 나뉨

COL2의 값을 출력하고 싶다면 COL1 is NULL로 가야 COL2의 값이 나옴

 

SELECT COUNT(*) FROM SAMPLE1 A WHERE EXISTS ( SELECT 1 FROM SAMPLE2 B WHERE B.COL = A.COL);

B.COL = A.COL 조건이 맞는 값만 EXISTS조건 만족

SELECT COUNT(*) FROM SAMPLE1 A WHERE NOT EXISTS ( SELECT 1 FROM SAMPLE2 B WHERE B.COL = A.COL);

B.COL = A.COL 조건이 안 맞는 값만 NOT EXISTS조건 만족

 

계층쿼리

SELECT * FROM SQLD_11 START WITH COL3 = 4 CONNECT BY COL1 = PRIOR CO2;

START WITH COL3 = 4 > 시작절

CONNECT BY COL1 = PRIOR CO2 > 조건절 PRIOR가 앞에 있으면 순방향, 뒤에 있으면 역방향

CONNECT BY PRIOR 자식 = 부모 > 순방향 

CONNECT BY 자식 = PRIOR 부모 > 역방향

 

윈도우함수

ROWS : 행 자체가 기준

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING : 현재행부터 마지막 행까지

RANGE : 행이 가지고 있는 데이터 값이 기준

RANGE UNBOUNDED PRECEDING : 첫째행부터 현재행까지 합계 (기본설정)

RANGE BETWEEN 10 PRECEDING AND CURRENT ROW : 현재 행부터 10만큼 적은 행부터 현재행까지

= RANGE 10 PRECEDING

 

소계함수

CUBE(A,B) - A,B로 그룹핑 > A로 그룹핑 > B로 그룹핑 > 총합계

ROLLUP(A,B) - A,B로 그룹핑 > A로 그룹핑 > 총합계

GROUPING SETS(A,B,(A,B),()) - A로 그룹핑 > B로 그룹핑, (A,B)로 그룹핑, ()는 총합계 > GROUPING이랑 다름 혼동주의

GROUPING(속성명) : GROUP BY에 소계함수를 사용하면 소계를 나타내는 행은 1을 반환 대부분 CASE절로 소계에 이름을 넣어줌

SELECT CASE GROUPING(CYL) WHEN 1 THEN '총계' ELSE TO_CHAR(CYL) END AS CYL, COUNT(*) FROM MTCARS GROUP BY ROLLUP(CYL) ORDER BY CYL;

 

 

집합함수

교집합

intersect : 중복된 데이터만

합집합

UNION : 중복데이터 제거된 합집합

UNION ALL : 중복데이터 포함된 합집합

차집합

MINUS : 마이너스 연산시 중복값을 하나만 출력되게 바꿔줌 set연산자이기때문에 UNION ALL같이 중복값이 넘어오는 문제일때 낚시 주의 (중복제거 역할 비슷한것 DISTINCT, GROUP BY 컬럼 등등)

except

 

순위함수

RANK : 중복값 12333678 처리

DENSE_RANK : 중복값 12333456 처리

ROW_NUMBER : 중복값 12345678 처리 > ROWNUM이랑 계속 낚시 문제 주의

ROWNUM은 위부터 그냥 12345 순서대로 멜론탑10 같이 순서만 정해주는것

 

날짜함수

EXTRACT('YEAR' | 'MONTH' | 'DAY' FROM X) : 날짜 데이터에서 연월일 출력

EXTRACT(YEAR FROM SYSDATE), EXTRACT(MONTH FROM SYSDATE) 이와 같이 사용 sysdate는 현재

 

파티션함수

LEAD(인수1, 인수2, 인수3) : 파티션별 윈도우에서 아래로 몇번째 행 값 출력 인수1기준 인수2만큼 이동후 값이 없으면 인수3 대입 인수3이 없는 경우는 null

LAG(인수1, 인수2, 인수3) : 파티션별 윈도우에서 위로 몇번째 행 값 출력 인수1기준 인수2만큼 이동후 값이 없으면 인수3 대입 인수3이 없는 경우는 null

NTITLE : 파티션별 전체 건수를 인수값만큼 균등분할 후 남으면 앞부터 할당

PERCENT_RANK : 파티션별 첫째행을 0 마지막행을 1로두고 순서별 백분률 구함

RATIO_TO_REPORT : 파티션별 합계에서 차지하는 비율을 구함

CUME_DIST : 해당 파티션에서 누적 백분률을 구함 값이 0~1이 나온다

 

조인종류

innerjoin : 중복조인

leftjoin : 왼쪽 모든값 + 오른쪽 중복값 (중복값은 하나만)

rightjoin : 오른쪽 모든값 + 왼쪽 중복값 (중복값은 하나만)

crossJoin : 왼쪽 오른쪽 모든값 연결 m * n

selfJoin : 한테이블에 연관있는 필드가 있을시

naturalJoin : 컬럼이랑 값 모든게 같이 일치할 시 조인

 

DML : insert, update, delete, merge

DDL : CREATE, ALTER, DROP, RENAME, TRUNCATE

DCL : GRANT, REVOKE

TCL : REROLL, COMMIT, SAVEPOINT

 

DCL 구문

GRANT 제약조건1, 제약조건2, ... ON 테이블명 to 유저명 : 특정 테이블에 권한을 줄때

GRANT 제약조건 to 유저명 : 유저에게 db시스템적인 권한을 줄때

GRANT CREATE TABLE to UESR

 

DDL 구문 (ddl시 자동으로 commit이 됌 rollback불가)

ALTER TABLE 테이블명 ADD 컬럼명 데이터타입;

ALTER TABLE 테이블명 DROP COLUMN 컬럼명;

ALTER TABLE 테이블명 MODIFY (컬럼명 데이터타입 [DEFAULT] [NOT NULL], ...);

ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건 (컬럼명);

예시>> ALTER TABLE TEACHER ADD CONSTRAINT TEACHER_FK FOREIGN KEY (SUBJECT_ID) REFERENCES SUBJECT(SUBJECT_ID);