com
오라클 Database 교육 노트 (1)
by Kitle · 2020. 06. 24.
예전 교육자료라 최신 DB와는 안맞는 부분이 있을 수 있습니다.
Oracle DB가 아니더라도 R-DBMS는 80% 정도 유사 속성이 있으니 참고하여 활용해주세요.
[ORACLE DB]
Oracle DB : 대용량 DB에
가장 안정적인 DB로 실시간DB등에 필요
[DB보안의 두가지 측면]
-접근 제어
-암호화
[관계형 데이터베이스의 특징]
무결성, 공유, 정확성과
일관성, 중복제거, 불일치 회피, 표준화 가능
But 표준화 : 현업에서
실제적으로 잘 이루어지지 않음.
Ex) 데이터의 표준화 : Ex) 컴퓨터 : com, COM, 컴퓨터, Computer, computer 등
정규화 위반 경우 가 많음
TABLE, Column 추가 등,
중복데이터 존재, 따라서 일관성 떨어짐
Table이 증가한다는 것 :
join 발생 >> 성능 저하 >>
아주 나쁜 database 화 됨
[ORACLE 기본 생성 아이디]
SYS, SYSTEM : 관리자 계정, SYSTEM은 유지보수용으로 DB 를 생성할 수 없음, DB생성은 SYS 계정으로 접속해야 함
보통 샘플데이터에 SCOTT/HR이 있는데
SCOTT – ORACLE DB엔진 만든사람, SAMPLE 계정
HR : Sample 사용자 아이디
[Database 구조]
<논리적 구조> <물리적 구조>
DB
Table spaces -- (1:n
관계)----------------------- datafiles
Segments(Table, Index) // 뷰는 아님
Extents
Blocks
최소단위 Block 8k
Block들이 모여 확장됨.
Table spaces 예) 업무
영역별로 구분. Ex) 인사업무, 총무업무, 회계업무
[Index 생성]
Pk 컬럼은 자동으로 index 생성. Index가 생성되는 이유는 조회를 빨리하기 위해. Index는 segment라 실제 데이터를 가지고 있음. B-Tree (밸런스 Tree)
[Null 값이란]
Null : 알수없는 값
“Null 값은 연산자로 비교할 수 없다” 따라서 is null, is not null >> 넣어야
한다.
별도 조건 주어야함. 중요!
Select
*
From emp
Where 급여 not in
(300, 250)
Or 급여 is null;
[Oracle PK(Primary Key) column rules]
Not null + 중복불허(중복제거)
자동으로 인덱스를 생성
테이블에 미 존재 할수도 있고 존재 할수도 있다(존재하면 테이블에 1개)
한 개 또는 여러 개의 컬럼으로 단 하나의 PK를 만들 수 있다.
>> 사원번호 + 사원명으로
하나의 pk를 구성할 수 있다. 사원번호, 사원명 각각 가능, 사원번호 와 사원명은 불가
>> 모든 칼럼을 구분할 수 있는 칼럼이 하나라도 존재하여 tuple로 구성하면 PK로 만들 수 있다.
한 컬럼이라도 “NULL”이면 불가!
[Foreign Key (외래키, 참조키)]
외래키는 타 테이블의 Primary Key를 참조하여 구성할 수 있다.
[SQL]
SQL 은 대소문자 구별하지 않음
Select 컬럼명
From 테이블명
Where 조건절;
[단일행 컬럼, Having 절]
Select dept_id, avg(salary)
From emp
Group by dept_id
Having avg(salary) >= 300
Order by 1;
함수를 사용한 컬럼이 select에 존재할 때 단일행 컬럼은 꼭 group by 를 써야한다. ~ 별로(group by) Having 절은 Group 절의 조건임
Group by 단일행컬럼
Having 그룹함수의 조건
Order by 정렬대상 컬럼; //디폴트로 오름차순… 숫자1은 해당컬럼의 첫번째 컬럼
Where 1=1
Group by dept_id, sex_type, avg(salary)
From emp
Where 1=1
Group by dept_id, sex_type, apge_type
Having avg(salary) >= 300
Order by 1;
(내림차순경우 order by 1
desc)
문자열은 ‘ ‘ 로 묶어준다. 숫자는
하지 않는다.
Dept_id, sex_type, avg(salary)
-----------------------
남자 100
10 여자 200
DML 문 다음에 TCL 문장이
옴. 변경 추가 삭제를 COMMIT (DB에 저장하겠다).
변경/수정/삭제 > 본인만 보임 : 작업내용은 임시 정보만 가지고 있음.. Commit을 해야 다른사용자도 볼 수 있다.
(option 에 auto
commit을 할 수있다)
DML 문장 다음에 ROLLBACK을
한다
SAVE POINT : 트랜잭션 단위만
------------------------
Delete : “데이터” 일부
또는 전체삭제
Drop : “테이블” 삭제
Truncate : “데이터” 전체
삭제
Delete : where절로 일부,
전체 삭제.. 단 테이블은 존재함 (테이블 존재o)
-> Undo segment 보관(변경전
데이터 저장, 복구를 위해)
Drop : 테이블 삭제. 데이터도
당연히 사라짐
Truncate : “데이터” 전체
삭제 (테이블 존재o)
-> undo segment 보관하지 않음.
------------------------
Drop table 시 table은 table space 의 segment 영역에서 이동하여 Free 영역 BIN$ 형태로 보관되게 된다.
Size가 너무 크면 삭제되는 되나
undo가 되지 않을 수 있다. (ex) 휴지통에 보관 안되고 바로 삭제)
Truncate 를 복구하기 위해서는 read only 상태로 바꾸고 이전 시점으로 돌림. 해당 테이블만 export 받고 db시점을 원래시점으로 돌리고,
Import 후 read/write
가능하게 변경함.
[SQL 명령 / 테이블
구조 보기]
DB 접속 후 명령어로 확인
desc 테이블명; //테이블의
구조를 볼수있음
[Index Search 와
Full scan 차이 알아보기]
select *
from employees
where employee_id=100;
DBMS는 쿼리요청 수신시(위
문장이 쿼리 요청 문장임), 쿼리 처리 계획(질의 처리계획)에 의해 최적 및 최대의 성능으로 응답하게 끔 설계되어 있다. 불필요한
검색으로 응답속도와 성능을 향상시키기 위함이다. 이런 계획이 질의 계획으로 정의되어 있거나 튜닝이 가능하다고
한다. 기본적으로 Table의 PK는 B-Tree(밸런스 트리)로 index를 구성해 몇번만의 검색만으로 신속히 찾게 된다.
위의
문장의 경우 employee_id 가 100번인 어트리뷰트의
내용을 출력해야하는데, 해당 컬럼이 PK이므로 인덱스 검색을
수행한다.
select *
from employees
where round(employee_id, 0) = 100;
round 반올림 함수. 0이면
정수만.
" index 를 만들었어도 함수를 하면 index검색이 아닌 full 스캐닝 한다!!"
단, 함수기반 index를
만들어야 함수기반 index 를 할 수 있다.
>> create index 어쩌구~~ 해서 만들 수 있다. 추후 강의 예정.
c.f. between 인 경우
range scan 함
SQL주석은 앞에 --를
붙인다.
-------------
any 합집합 : ~이거나
all 교집합 : ~이고
<any
최대보다 작음
<all
최소보다 작음
>any
최소보다 큼
>all
최대보다 큼
------------------------------
true and null >> null 됨
false and null >> false 됨
true or false >> true
true or null >> null
null not >> null
-----------------------------
using index
tablespase USERS (세그먼트를 Table space에 속하게 넣어준다)
tablespase USERS; <- 바깥 구문은 table에 대한 table space를 잡아줌
insert into 컬럼명을 명시하지 않으면 모든 컬럼에 값을 넣어줌. 명시하면 명시한것만 넣어주면 됨.
*데이터는 대소문자를 구분한다.
where 절 데이터 검색시 대소문자로 인해 검색되지 않는다.
오라클 딕션어리 정보 = "대문자"저장
오라클 파라메터 값 = "소문자:저장
------------------------------------
in 여러개 쓸때는 in (10,
20); 으로 괄호로 묶어줘야함
---------------------------------
[FK] 관계 관점의 외래 키
1. 부모 없는 자식은 없다. (데이터)
2. 자식 없는 부모는 있다. (데이터)
3. 데이터의 변경 및 삭제는 자식부터 한다.
자식 테이블에서는 참조할수있는(존재하는) 키만 된다.
자식 없는 테이블(참조당하지 않는)
데이터는 추가 가능하다.
20이 자식에 있는 경우 부모테이블의 20을 50으로 바꿀수 없다.
변경순서
1. 부모 테이블 추가
2. 자식테이블 업데이트
3. 부모테이블의 과거 데이터를 삭제하거나 내버려 둔다.
--------------------------
[요약]
1. PK는 1개 테이블에
없어도 되고,(요약) 여러 개의 칼럼으로 생성할 수 있다.
2. DELETE 는 where 절의
조건으로 일부만 삭제 가능하다.
3. Drop으로 제거시 table
은 남아있지 않고 삭제된다. Truncate는 데이터가 삭제되며 테이블은 존재한다.
4. UNION 은 중복된 데이터를
1번만, UNION ALL 은 모두 보여준다.
5. where 절의 문자열은 ‘ ‘ 괄호로 묶어
사용한다. %, _ 이용시 like 붙일 것. 와일드 카드로 쓰인해당 문자 자체의 포함여부를 검사하려면 escape를
이용한다.
6. select절에 함수 ex)
avg(salary)를 쓴 경우, 그외의 단일행 컬럼은
group by 로 써주는 것이 syntax 이다. 또한 group 함수절의 조건은 having 절에 기술한다.
Ex) select dept_id, avg(salary)
>> having avg(salary) > 300
7. where 의 비교연산자는 한 조건 밖에 안된다. 따라서 여러 개를 보고 싶으면, where dept_id in (10,
20, 30) 형식으로 구분 하여 999개 까지 사용 가능하다.