Sqld

9 분 소요

데이터베이스의 구성 요소

개체

데이터베이스에서 개체 또는 엔터티(entity)는 데이터베이스에서 표현하려고 하는 무형, 유형의 객체(object)로써 서로 구별되는 것을 뜻한다.

이 개체는 현실 세계에 대해 사람이 생각하는 개념이나 정보의 단위로서 의미가 있다.

개체는 단독으로 존재할 수 있으며, 정보로서의 역할을 한다.

하나의 개체는 하나 이상의 속성, 즉 애트리뷰트(attribute)로 구성되고 각 속성은 그 개체의 특성이나 상태를 기술해 준다.

속성, 즉 애트리뷰트(attribute)라고 하는 것은 이름을 가진 데이터의 가장 작은 논리적 단위가 된다.

일반적으로 파일 구조에서는 데이터 항목(data item) 또는 필드(field)라고 한다.

한 ‘학생’ 개체를 나타내는 개체 인스턴스(학번 : 1, 이름 : 가가가, 학과 : 컴공)는 학생 개체의 한 값으로 볼 수 있는데, 이것을 개체 어커런스(entity occurrence)라고도 한다.

데이터베이스는 바로 이런 개체 인스턴스들을 저장하는 것이다.

‘학번’, ‘이름’, ‘학과’와 같이 속성 이름들로만 기술된 개체의 정의를 개체 타입(entity type)이라 한다. 개체 타입은 논리적인 개념이고, 개체 인스턴스는 데이터베이스에 저장되는 구체적인 객체가 된다.

필드 이름으로만 표현된 레코드 정의를 레코드 타입(record type)이라 하고, 실제 필드 값(field value)으로 표현된 레코드를 레코드 어커런스(record occurrence) 또는 레코드 인스턴스(record instance)라고 한다

관계

개체 집합과 개체 집합 사이에는 여러 가지 유형의 관계(relationship)가 존재 할 수 있는데, 이 관계도 데이터베이스에 저장할 대상이 된다.

관계는 개체들을 서로 연관시켜 어떤 의미를 나타내어 개체와 같기 때문이다.

관계는 어느 특정 개체를 기술하는 속성들 간의 속성 관계(attribute relationship)와 개체 집합과 개체 집합 사이의 관계를 나타내는 개체 관계(entity relationship)로 나누어 볼 수 있다.

속성 관계는 한 개체 내에서만 존재한다는 뜻에서 개체 내 관계(intra-entity relationship)라 할 수 있는 반면에

개체 관계는 개체 외부에 존재하는 개체 간 관계(inter-entity relationship)라 할 수 있다.

개체와 관계를 도식으로 표현한 다이어그램을 E-R 다이어그램(entity-relationship diagram)이라 한다.

SELECT 문장 실행 순서

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

테이블을 참조한다.
WHERE문을 통해 대상 데이터가 아닌 것은 제거한다.
행들을 그룹화 한다. (GROUP BY)
그룹핑 된 값의 조건에 해당하는 것만 출력한다. (HAVING)
데이터 값을 출력한다 / 계산한다. (SELECT)
데이터를 정렬한다. (ORDER BY)

GROUP BY VS JOIN

FROM 절에서 먼저 group by를 실행 (서브 쿼리 사용)
FROM 실행
JOIN 실행
SELECT 실행
등등 쿼리

출처: https://1r15.tistory.com/24 [IRIStory]

식별자

교차 엔티티

계층형

94page, 87번

95page, 89번

96page, 90번 (4)

96page, 91번

CHAR / VARCHAR

CHAR : ‘aa’ = ‘aa ‘
VARCHAR2 : ‘aa’ != ‘aa ‘

SUM

PROJECTINO

Minus (Using join)

데이터 모델의 이해

이론 자료 : https://dataonair.or.kr/db-tech-reference/d-guide/sql/?pageid=5&mod=document&uid=330

ROLLUP CUBE GROUPPING SETS

110page, 106 : grouping

Grouping(컬럼명)

GROUPING 함수는 그룹쿼리에서 사용하는 함수로 파라미터의 평가값이 NULL 이면 1,

NULL 이 아닌 경우에는 0을 반환합니다.

※ GROUPING 함수에서 사용될 컬럼은 반드시 GROUP BY 절에서 명시 되어야 합니다.

출처: https://lee-mandu.tistory.com/65 [개발/일상_Mr.lee]

http://egloos.zum.com/huzii/v/3638265

IN , EXIST

WINDOW FUNCTION

SUM()
RANK()
row_number()

ROWS

RANGE

BETWEEN

BETWEEN이 있다면

ROWS

RANGE

BETWEEN UNBOUNDED PRECEDING

CURRENT ROW

상수값 PRECEDING 또는 FOLLOWING

AND UNBOUNDED FOLLOWING| CURRENT ROW | 상수값 PRECEDING 또는 FOLLOWING

BETWEEN이 없다면

ROWS

RANGE UNBOUNDED PRECEDING

CURRENT ROW

상수값 PRECEDING

BETWEEN을 사용해서 시작과 끝의 범위를 정할 수 있습니다.

UNBOUNDED PRECEDING는 시작 범위를 정하는 데 사용합니다.

무제한으로 앞부분을 뜻하는데 가장 앞을 의미합니다. 시작부겠네요.

CURRENT ROW는 현재 행을 의미합니다.

10건이 조회되었다면 1건씩 10번 행이 추가되는데 그때그때 CURRENT ROW가 됩니다.

10건 중 3번째 행을 처리하는 중이라면 CURRENT ROW는 3번째 행을 말합니다.

상수값 PRECEDING/FOLLOWING은 고정된 값을 넣고 뒤에 옵션을 선택하게 됩니다.

ROWS 상수값 PRECEDING/FOLLOWING를 사용하면 행 기준으로 상수값 만큼 앞의 행 갯수 뒤의 행 갯수만큼 범위를 정하는 것이고

RANGE 상수값 PRECEDING/FOLLOWING를 사용하면 값 기준이기에 행과 관계없이 값의 범위를 상수값만큼 정하게 됩니다.

예를 들어 ROWS 10 PRECEDING을 사용하면 현재 행에서 10행 앞쪽의 범위를 잡는 것이고

RANGE 10 PRECEDING을 사용하면 현재 행의 컬럼값이 100의 값을 갖는다면 -10만큼 작은 값 즉 90부터 범위를 정하는 것이 됩니다.

출처: https://tiboy.tistory.com/570 [신기한 연구소]

LAG 함수

123페이지, 118번

절차열 SQL 모듈

https://dataonair.or.kr/db-tech-reference/d-guide/sql/?mod=document&pageid=1&kboard_search_option%5Btree_category_1%5D%5Bkey%5D=tree_category_1&kboard_search_option%5Btree_category_1%5D%5Bvalue%5D=SQL+기본+및+활용&kboard_search_option%5Btree_category_2%5D%5Bkey%5D=tree_category_2&kboard_search_option%5Btree_category_2%5D%5Bvalue%5D=SQL+활용&uid=353
프로시저, 함수, 트리거
저장형 프로시저 : SQL을 로직과 함께 데이터베이스 내에 저장해놓은 명령문의 집합
저장형 함수 : 혼자 쓰이기 보다는 다른 쿼리를 통해 호출되고 결과를 리턴하는 보조적 역할을 함
트리거 : 데이터의 무결성과 일관성을 위해서 사용됨

정규화

정규화
1차 정규화
- 속성의 원자성을 확보, 기본키를 설정

2차 정규화
- 기본키가 2개 이상의 속성으로 이루어진 경우, 부분 함수 종속성 제거
- abnomal

결정자, 함수적 종속성

3차 정규화
- 이행적 종속 함수 제거

BCNF 정규화

데이터 조작어

기본적인 데이터 처리는 데이터의 검색, 삽입, 삭제, 변경 등을 포함하는데 명세 방법에 따라 두 가지 유형의 데이터 조작어로 나눌 수 있다.

절차적 데이터 조작어
- 절차적 데이터 조작어는 사용자가 무슨 데이터를 원하며 어떻게 그것을 접근해야 되는지를 명세해야 되는 초급 데이터 언어이다.
- 이런 유형의 조작어는 데이터베이스로부터 한 번에 하나의 레코드를 검색해서 호스트 언어(해당 응용 프로그램을 작성하는 데 사용된 범용 프로그래밍 언어)와 함께 처리하는 특성을 가지고 있다.
- 그렇기 때문에 이것은 독자적으로 사용되지 못하고 호스트 프로그래밍 언어로 작성된 응용 프로그램 속에 삽입되어 사용된다.
- 이렇게 사용되는 DML은 통상 일반 프로시저 호출(call) 문 형식으로 표현되는데 이것은 먼저 DML 예비 컴파일러에 의해 추출되고 컴파일 된 뒤에 호스트 프로그래밍 언어 컴파일러로 컴파일된 나머지 프로그램과 연결되어 실행된다.
- 이와 같은 언어는 프로그램도 알고 데이터베이스도 아는 사람만이 사용 가능하므로 일반 비전문가가 이용하기는 상당히 어렵다.
비절차적 데이터 조작어
- 비절차적 데이터 조작어는 사용자가 무슨 데이터를 원하는지 만을 명세하고 그것을 어떻게 접근할 것인지에 대해서는 명세할 필요가 없는 고급 데이터 언어이다.
- 이러한 언어는 데이터베이스로부터 보통 한 번에 여러 개의 레코드를 검색해서 처리하는 특성을 가지고 있고, 어떻게 그 데이터들을 검색하는지는 DBMS에게 위임함으로써 독자적으로 사용될 수 있다.
- 그렇기 때문에 이런 언어는 터미널을 통해 일반 사용자가 대화식으로 사용하는 것이 보통이지만, 필요한 경우에 호스트 컴퓨터 프로그램 속에 이것을 그대로 삽입시켜 사용하기도 한다.
- 특별히 독자적이고 상호 작용 형태로 터미널에서 많이 사용하고 있는 고급 명령어 형태의 독립된 데이터 조작어를 질의어(query language)라고 한다.
- 원래 질의어란 용어는 주로 데이터 검색만을 뜻하지만 데이터베이스 분야에서의 질의어는 데이터의 검색은 물론 데이터의 갱신, 삽입, 삭제뿐만 아니라 데이터 정의 등 광범위한 기능을 가진 독립된 언어의 의미로 사용되고 있다.
- 일반적으로 DML 명령어가 고급이든 초급이든 간에 그것이 호스트 프로그램 속에 삽입되어 사용되는 DML 명령어들을 데이터 부속어(DML : Data SubLanguage)라고 한다.

NULL

문자	ASCII
NULL	0번
공백	32번
숫자 0	48번

ALTER (Oracle)

오라클 컬럼 ALTER 방법
컬럼 안의 내용을 변경할 때 :ALTER가 아닌 UPDATE문을 사용한다.
- ALTER - DDL (Table Definition)
- UPDATE - DML (Data Manipulate)
컬럼을 삭제할 때는 : ALTER TABLE 테이블명 DROP COLUMN 컬럼명;

https://amaze9001.tistory.com/104

컬럼 추가하기

문법 : ALTER TABLE 테이블명 ADD ( 컬럼명1 데이터타입, 컬럼명2 데이터타입, …. ); 예시 : EMP20 테이블에 숫자타입 급여 컬럼과 문자타입 업무코드 컬럼을 추가한다.

ALTER TABLE EMP20 ADD ( SALARY NUMBER(10,2) , JOB_ID VARCHAR2(5) );

컬럼 변경하기

문법 : ALTER TABLE 테이블명 MODIFY ( 컬럼명1 데이터타입, 컬럼명2 데이터타입, …. ) ; 예시 : EMP20 테이블의 급여 컬럼과 업무코드 컬럼의 데이터 사이즈를 변경한다.

ALTER TABLE EMP20 MODIFY ( SALARY NUMBER(8,2) , JOB_ID VARCHAR2(10) );

컬럼 삭제하기

문법 : ALTER TABLE 테이블명 DROP COLUMN 컬럼명 ; 예시 : EMP20 테이블의 업무코드 컬럼을 삭제한다.

ALTER TABLE EMP20 DROP COLUMN JOB_ID;

컬럼 이름 변경하기

문법 : ALTER TABLE 테이블명 RENAME COLUMN 변경전 컬럼명 TO 변경후 컬럼명; 예시 : EMP20 테이블의 급여 컬럼명을 SALARY에서 PAY로 변경한다.

ALTER TABLE EMP20 RENAME COLUMN SALARY TO PAY;

컬럼 추가 (ADD) ALTER TABLE [table_name]
ADD [col_name] [data_type(size)];
컬럼 이름 및 데이터형 수정 (MODIFY) ALTER TABLE [table_name] MODIFY [col_name] [re_data_type(resize)]; ** 데이터가 있을 경우 타입 변경을 불가!(모든 경우)
컬럼 삭제 (DROP) ALTER TABLE [table_name] DROP COLUMN [col_name];

RENAME

테이블의 이름을 변경할 때 : RENAME EMP TO EMP_RENAME;
ANSI표준과 오라클 표기가 동일하다
DDL이다.

CASCADE, SET NULL

CASECADE가 붙어있으면, 참조 중인 데이터가 있어도 삭제할 수 있다. 이때, 참조 중인 데이터도 함께 삭제된다.
오라클_FOREIGN KEY_외래키 제약조건 - DELETE
오라클_FOREIGN KEY_외래키 제약조건 - UPDATE

[DELETE | MODIFY] ACTION

CASECADE : 마스터 삭제 시, 차일드도 삭제
SET NULL : 마스터 삭제 시, 차일드 해당 필드 NULL
SET DEFAULT : 마스터 삭제 시, 차일드 해당 필드 DEFAULT
RESTRICT : 차일드 테이블에 PK값이 없는 경우만 마스터 삭제
NO ACTION : 참조 무결성을 위반하는 삭제/수정을 취하지 않음

[INSERT] ACTION

Automatic : Master 테이블에 PK가 없는 경우 Master PK를 생성 후 Child 입력
Set Null : Master 테이블에 PK가 없는 경우, Child외부키를 Null값으로 처리
Set Default : Master 테이블에 PK가 없는 경우, Child 외부키를 지정된 기본값으로 입력
Dependent : Master 테이블에 PK가 존재할때만 Child입력 허용
No Action : 참조 무결성을 위반하는 입력 액션을 취하지 않음

인덱스

생성

create index idx_emp_01 on emp (join_date);

외래키

NULL 값을 가질 수 있음
외래키 값은 참조 무결성 제약을 받을 수 있음
- 외래키 값은 NULL이거나 참조 릴레이션의 기본키 값과 동일해야 합니다. 즉 릴레이션은 참조할 수 없는 외래키 값을 가질 수 없습니다.
- 당연한 이야기입니다. 없는 값을 참조할 수 없으니 있는 값에서 참조하라는 말입니다.
- ex) <수강> 릴레이션 '학번' 속성에는 <학생> 릴레이션의 '학번' 속성에 없는 값은 입력할 수 없습니다.
한 테이블에 여러개 존재 가능

고유키(Unique Key)

고유키는 NULL값을 가질 수 있다.

CROSS JOIN

cartesian

무결성 제약조건

https://limkydev.tistory.com/161

join 기법

1) Neste Loop Join(가장 기본적인 Join 기법)

2)Sort_Merge join

3) Hash Join(CBE에서만 가능)

NL Join

select 
e.ename, d.dname
from 
emp e, dept d
where
e.deptno = d.deptno

위의 SQL을 실행하면 순서는 다음과 같습니다.

1) 사원·부서 테이블을 메모리에 복사

2) 사원 테이블에서 사원이름 꺼내서 임시 작업공간으로 가져 가져감(인덱스 상황이나 다른 요소에 따라 순서가 변경 될 수도 있음)

3) 부서 테이블에서 해당 부서명을 찾으러 가는데 그때 위 SQL의 where조건을 보고 해당 조건에 맞는 데이터를 찾아서 부서명을 가져옴

4) 한 행의 작업이 끝나면 다시 처음 테이블로 돌아가서 두번째 행의 이름을 PGA(메모리 영역으로 생각)로 가져옴

5) 다시 부서 테이블에 가서 두번째 부서번호와 동일한 부서번호를 가진 부서명을 꺼내옴

이와같은 과정이 먼저 읽었던 사원 테이블의 데이터가 끝이 날때까지 작업이 반복됩니다.(LOOP)

그래서 이 Join을 Nested Loop Join이라고 합니다.

먼저 읽은 테이블의 행의 수만큼 Join이 수행됩니다.(중요)

먼저 읽는 테이블이 Join의 성능을 결정 합니다.

그래서 Driving Table(선행 테이블)이라 하고 나중에 읽는 테이블은 Driven Table(후행 테이블) 이라 합니다.

그러므로, 여러 개의 테이블을 Join해야 할 경우 어떤 테이블을 선행 테이블로 설정하는가가 아주 중요합니다.

이것을 결정해주는 아로클 내부 구성요소를 옵티마이저라고 한다.

(음…쿼리가 실행될때 내부적으로 이루어 지는것을…옵티마이저라고 하는데…자세한건 공부가 필요할 것 같습니다.)

출처: https://lee-mandu.tistory.com/470?category=633572 [개발/일상_Mr.lee]

Sort Merge Join

이때 인덱스가 없을 경우에도 빨리 해당 데이터를 찾아서 결과를 출력해야 하는 경우에 Sort-Merge join 방법을 사용합니다.

Sort-Merge 방법은 where 조건을 기준으로(deptno) 정렬합니다.

그리고 서로 같은 값(deptno)를 비교하여 값을 가져 옵니다.

말그대로 Sort 한 후 그 결과를 Merge 해서 데이터를 찾게 됩니다.

단점은 Sort 할 때 시간이 너무 오래 걸린다는 점 입니다. 그래서 이를 보완하기 위하여 Hash Join을 사용합니다.

출처: https://lee-mandu.tistory.com/470?category=633572 [개발/일상_Mr.lee]

Hash Join

Hash Join은 양쪽 테이블 모두 Join 컬럼에 인덱스가 없을 경우에 사용 합니다. (Sort-Merge Join이 시간이 너무 오래 걸리기 때문)

방법

1) 두 테이블 중에서 범위가 좁은 테이블을 메모리로 가져옵니다.

2) Join 조건 칼럼의 데이터를 Hash 함수에 넣어서 나온 Hash Value 값으로 Hash Table을 생성합니다.

3) 후행 테이블의 Join 조건을 Hash 함수에 넣어서 Hash Value를 생성하고 이 값을 선행 테이블의 Hash Table의 값과 비교하여 같은 값을 찾아 매칭합니다.

Sort-Merge Join과 Hash Join은 둘 다 모든 테이블을 다 읽는다는 부분은 동일하지만 Sort-Merge Join에서 실행하는 정렬은 실행하지 않습니다.

이 차이는 약 2배 이상의 성능차이가 나게 됩니다.

출처: https://lee-mandu.tistory.com/470?category=633572 [개발/일상_Mr.lee]

etc

https://benant.wordpress.com/2010/07/25/sql에서-count-결과에-null이-포함될까/

https://mozi.tistory.com/218

GROUP BY절을 사용하면 그룹핑 기준에 사용된 칼럼과 집계 함수에 사용될 숫자형의 데이터 칼럼을 새로운 집합으로 형성 → GROUP BY 이후 수행절인 SELECT절이나 ORDER BY 절에서 개별 데이터를 생성하는 경우 에러 발생 → GROUP BY 이후 SELECT절이나 ORDER BY 절에는 그룹핑 기준과 숫자형식의 집계함수는 사용가능

EX 4). SELECT JOB FROM EMP GROUP BY JOB HAVING COUNT(*) >0 ORDER BY SAL;

EX 5). SELECT JOB FROM EMP GROUP BY JOB HAVING COUNT(*)>0 ORDER BY MAX(EMPNO). MAX(MGR), SUM(SAL), COUNT(DEPTNO), MAX(HIREDATE);

EX 4) GROUP BY 절을 통해 JOB 칼럼을 새로운 집합으로 만들었는데 SAL을 정렬하라 해서 에러 발생
EX 5) GROUP BY 절 이후 ORDER BY절에 숫자형식의 집계함수가 이용되었으므로 정상출력

출처: https://geniusjo.tistory.com/entry/ORDER-BY-절 [☞지니의 디비디비딥]

ㅇㅓㅋㅓㄹㅓㄴㅅㅡㄹㅏㄴ?

https://cnpnote.tistory.com/entry/SQL-오라클-SQL-열의-최대-5-개-값을-검색하는-방법

Twitter Facebook LinkedIn

이해니

데이터베이스의 구성 요소

개체

관계

SELECT 문장 실행 순서

식별자

교차 엔티티

계층형

CHAR / VARCHAR

SUM

PROJECTINO

Minus (Using join)

데이터 모델의 이해

ROLLUP CUBE GROUPPING SETS

IN , EXIST

WINDOW FUNCTION

BETWEEN

LAG 함수

절차열 SQL 모듈

정규화

데이터 조작어

NULL

ALTER (Oracle)

RENAME

CASCADE, SET NULL

[DELETE | MODIFY] ACTION

[INSERT] ACTION

인덱스

외래키

고유키(Unique Key)

CROSS JOIN

무결성 제약조건

join 기법

NL Join

Sort Merge Join

Hash Join

etc

공유하기

참고

Blockchain Basic

[cmd] 8080 포트 검색 후 죽이기

[Oracle] 테이블, 뷰, 프로시저, 함수 전체 삭제하기

[Beside] 프로젝트 환경 셋팅 (3)