📕 5day 요약
1. 객체의 종류
2. 제약조건
3. 사용자와 권한관리
4. SQL-99 표준문법
5. 데이터베이스 모델링
6. 관계형 데이터 모델링
📝 간략복습
📕
✏️
📖 1. 객체의 종류
- 데이터베이스를 위한 데이터를 저장한 데이터 사전
- 더 빠른 검색을 위한 인덱스
- 테이블처럼 사용하는 뷰
- 규칙에 따라 순번을 생성하는 시퀀스
- 공식 별칭을 지정하는 동의어
📌 3. 테이블처럼 사용하는 뷰
가상 테이블(Virtual table)로 부르는 뷰(view)는 하나 이상의 테이블을 조회하는 SELECT문을 저장한 객체를 뜻하며, SELECT문을 저장하기 때문에 물리적 데이터를 따로 저장하지는 않음
편리성 : SELECT문의 복잡도 완화 / 보안성 : 테이블의 특정 열 노출 방지
1. 🔍 뷰 생성하기
💖 SYSTEM 계정으로 변경 후 CREATE VIEW 권한 부여
💚 입력
GRANT CREATE VIEW TO SCOTT;💙 출력
Grant을(를) 성공했습니다.💖 SCOTT 계정으로 변경 후 뷰 생성
💚 입력
CREATE VIEW VE_EMP20
AS (SELECT EMPNO, ENAME
FROM EMP
WHERE DEPTNO=20);💙 출력
View VE_EMP20이(가) 생성되었습니다.💖 생성한 뷰 확인
💚 입력
SELECT *
FROM USER_VIEWS;💙 출력
💖 생성한 뷰 조회
💚 입력
SELECT *
FROM VE_EMP20;💙 출력
View VE_EMP20이(가) 삭제되었습니다.2. 🔍 뷰 삭제하기
DROP VIEW VE_EMP20;💡 ROWNUM 조회하기
ROWNUM은 의사 열(pseudo column)이라고 하는 특수 열로 SELECT문의 행 번호를 매겨 줌
3. 🔍 ROWNUM 사용해보기
💚 입력
SELECT ROWNUM, E.*
FROM EMP E
ORDER BY SAL DESC;💙 출력
☑️ 번호가 매겨진 후
ORDER BY SAL DESC가 실행됨
3. 🔍 인라인 뷰
💚 입력
SELECT ROWNUM, E.*
FROM (SELECT *
FROM EMP E
ORDER BY SAL DESC) E;💙 출력
☑️ 서브쿼리를 이용하여 FROM절에서 정렬 후 ROWNUM 사용
💡 SQL 실행순서
FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY
4. 🔍 인라인 뷰로 연봉이 TOP3 인 사원 출력하기
💚 입력
SELECT ROWNUM, E.ENAME, E.SAL
FROM (SELECT *
FROM EMP E
ORDER BY SAL DESC) E
WHERE ROWNUM <= 3;💙 출력
☑️ 순위 표시는 RANK() 함수도 있다!
📌 4. 규칙에 따라 순번을 생성하는 시퀀스
오라클 데이터베이스에서 특정 규칙에 맞는 연속 숫자를 생성하는 객체
1. 🔍 테이블 생성하기
💚 입력
CREATE TABLE DEPT_SEQUENCE
AS SELECT *
FROM DEPT
WHERE 1<>1;💙 출력
Table DEPT_SEQUENCE이(가) 생성되었습니다.2. 🔍 시퀀스 생성하기
💚 입력
CREATE SEQUENCE SEQ
INCREMENT BY 10
START WITH 10
MAXVALUE 100
MINVALUE 10
CYCLE
CACHE 2;INCREMENT BY N 증가시킬 값
START WITH N 시작값 (MINVALUE보다 같거나 커야함)
MAXVALUE N 최댓값
MINVALUE N 최솟값
CYCLE 최댓값 도달 시 최솟값 1부터 다시 시작 (OR "NOCYCLE")
CACHE 2 CACHE를 사용할 지 (OR "NOCACHE") / 사용 시 속도 빠름
💙 출력
Sequence SEQ이(가) 생성되었습니다.3. 🔍 시퀀스 실행하기
💚 입력
INSERT INTO DEPT_SEQUENCE
VALUES(SEQ.NEXTVAL, 'DATABASE', 'SEOUL');💙 출력
1 행 이(가) 삽입되었습니다.💚 입력
SELECT *
FROM DEPT_SEQUENCE;💙 출력
3. 🔍 시퀀스 수정하기
💚 입력
ALTER SEQUENCE SEQ
INCREMENT BY 5
NOMAXVALUE
NOCYCLE
CACHE 2;💙 출력
Sequence SEQ이(가) 변경되었습니다.☑️ 시퀀스의 시작번호는 변경이 불가하다.
💚 입력
SELECT *
FROM DEPT_SEQUENCE;💙 출력
4. 🔍 시퀀스 삭제하기
💚 입력
DROP SEQUENCE SEQ;💙 출력
Sequence SEQ이(가) 삭제되었습니다.📌 5. 공식 별칭을 지정하는 동의어
테이블, 뷰, 시퀀스 등 객체 이름 대신 사용할 수 있는 다른 이름을 부여하는 객체
SELECT문의 SELECT절이나 FROM절에서 사용하는 별칭과 유사하나, 오라클 데이터베이스에 저장되는 객체이기 때문에 일회성이 아니라는 점에서 차이
1. 🔍 동의어 생성하기
💖 SYSTEM계정으로 전환 후 권한부여하기
💚 입력
GRANT CREATE SYNONYM TO SCOTT;
GRANT CREATE PUBLIC SYNONYM TO SCOTT;❔ GRANT CREATE PUBLIC SYNONYM TO SCOTT;
동의어를 데이터 베이스 내 모든 사용자가 사용할 수 있도록 설정 / 생략 시 동의어를 생성한 사용자만 사용가능
💙 출력
Grant을(를) 성공했습니다.
Grant을(를) 성공했습니다.💖 SCOTT계정으로 전환 후 동의어 생성
💚 입력
CREATE SYNONYM E
FOR EMP;💙 출력
Synonym E이(가) 생성되었습니다.💖 동의어 조회
💚 입력
SELECT * FROM E;💙 출력
####☑️ EMP테이블이 출력된다
💖 동의어 삭제
💚 입력
DROP SYNONYM E;💙 출력
Synonym E이(가) 삭제되었습니다.📖 2. 제약조건
| 종류 | 설명 |
|---|---|
| NOT NULL | 지정한 열에 NULL을 허용하지 않음 |
| UNIQUE | 지정한 열이 유일한 값을 가져야 함. 중복될수 없음. 단, NULL은 값의 중복에서 제외 |
| PRIMARY KEY | 지정한 열이 유일한 값이면서 NULL을 허용하지 않음. 테이블에 하나만 지정가능 |
| FOREIGN KEY | 다른 테이블의 열을 참조하여 존재하는 값만 입력 |
| CHECK | 설정한 조건식을 만족하는 데이터만 입력 |
❓ 데이터 무결성이란?
데이터베이스에 저장되는 데이터의 정확성과 일관성을 보장함
💖 제약조건 조회
💚 입력
SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS;💙 출력
📖 3. 사용자와 권한관리
📌 사용자란?
접속하여 데이터를 관리하는 계정
📌 데이터베이스 스키마란?
데이터베이스에서 데이터 간 관계, 데이터 구조, 제약 조건등 모든 객체 데이터베이스 구조의 범위
오라클 DB에서는 스키마와 사용자를 구별하지 않고 사용
💖 사용자 생성
💚 입력
CREATE USER ORCL
IDENTIFIED BY ORCL;💙 출력
User ORCL이(가) 생성되었습니다.📌 시스템 권한과 객체 권한
- 시스템 권한 : 데이터베이스 관리 권한이 있는 사용자가 부여할 수 있는 권한
- 객체 권한 : 특정 사용자가 생성한 테이블, 인덱스, 뷰, 시퀀스 등과 관련된 권한
💖 시스템 권한 부여
GRANT [시스템권한] TO [사용자이름/롤이름/PUBLIC]
[WITH ADMIN OPTION];
💚 입력
GRANT CREATE TABLE TO SCOTT;💙 출력
Grant을(를) 성공했습니다.💖 시스템 권한 부여 취소
REVOKE [시스템권한] FROM [사용자이름/롤이름/PUBLIC];
💚 입력
REVOKE CREATE TABLE FROM SCOTT;💙 출력
Revoke을(를) 성공했습니다.💚 입력
GRANT CREATE TABLE TO SCOTT;💙 출력
Grant을(를) 성공했습니다.💖 객체 권한 부여
GRANT [객체권한/ALL PRIVILEGES]
ON [스키마/객체이름]
TO [사용자이름/롤이름/PUBLIC][WITH ADMIN OPTION];
💚 입력
💙 출력
💖 객체 권한 부여 취소
GRANT [객체권한/ALL PRIVILEGES]
ON [스키마/객체이름]
FROM [사용자이름/롤이름/PUBLIC][CASCADE CONSTRAINTS/FORCE](선택);
💚 입력
💙 출력
📌 롤 관리
여러 종류의 권한을 묶어 놓은 그룹을 뜻하며 한 번에 부여하고 해제할 수 있음
💖 롤 생성하기
💚 입력
CREATE ROLE ROLESTUDY;
GRANT CONNET, CREATE VIEW, CREATE TABLE TO ROLESTUDY;
GRANT ROLESTUDY TO SCOTT;💙 출력
Role ROLESTUDY이(가) 생성되었습니다.
Grant을(를) 성공했습니다.
Grant을(를) 성공했습니다.💖 롤 취소하기
💚 입력
REVOKR ROLESTUDY FROM SCOTT;💙 출력
Revoke을(를) 성공했습니다.💖 롤 삭제하기
💚 입력
DROP ROLE ROLESTUDY;💙 출력
Role ROLESTUDY이(가) 삭제되었습니다.📖 4. SQL-99 표준문법
FROM 테이블1 NATURAL JOIN 테이블2
💚 기존
SELECT E.EMPNO, E.ENAME, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;💙 SQL-99 표준문법
SELECT E.EMPNO, E.ENAME, D.DNAME
FROM EMP E NATURAL JOIN DEPT D;☑️ 두 테이블에 이름과 자료형이 같은 열을 찾은 후 그 열을 기준으로 등가조인!
FROM 테이블1 JOIN 테이블2 USING(조인에 사용할 칼럼)
💚 기존
SELECT E.EMPNO, E.ENAME, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;💙 SQL-99 표준문법
SELECT E.EMPNO, E.ENAME, D.DNAME
FROM EMP E JOIN DEPT D USING(DEPTNO);FROM 테이블1 JOIN 테이블2 ON ( 테이블1.사용할칼럼 = 테이블2.사용할칼럼 )
💚 기존
SELECT E.EMPNO, E.ENAME, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;💙 SQL-99 표준문법
SELECT E.EMPNO, E.ENAME, D.DNAME
FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO);FROM 테이블1 LEFT OUTER JOIN 테이블2 ON (테이블1.사용할칼럼 = 테이블2.사용할칼럼)
💚 기존
SELECT E.EMPNO, E.ENAME, E.MGR, E1.EMPNO, E1.ENAME
FROM EMP E, EMP E1
WHERE E.MGR=E1.EMPNO(+);💙 SQL-99 표준문법
SELECT E.EMPNO, E.ENAME, E.MGR, E1.EMPNO, E1.ENAME
FROM EMP E LEFT OUTER JOIN EMP E1 ON (E.MGR=E1.EMPNO);FROM 테이블1 RIGHT OUTER JOIN 테이블2 ON (테이블1.사용할칼럼 = 테이블2.사용할칼럼)
💚 기존
SELECT E.EMPNO, E.ENAME, E.MGR, E1.EMPNO, E1.ENAME
FROM EMP E, EMP E1
WHERE E.MGR(+)=E1.EMPNO;💙 SQL-99 표준문법
SELECT E.EMPNO, E.ENAME, E.MGR, E1.EMPNO, E1.ENAME
FROM EMP E RIGHT OUTER JOIN EMP E1 ON (E.MGR=E1.EMPNO);FROM 테이블1 FULL OUTER JOIN 테이블2 ON (테이블1.사용할칼럼 = 테이블2.사용할칼럼)
💙 SQL-99 표준문법
SELECT E.EMPNO, E.ENAME, E.MGR, E1.EMPNO, E1.ENAME
FROM EMP E FULL OUTER JOIN EMP E1 ON (E.MGR=E1.EMPNO);💡 세 개 이상의 테이블을 조인할 때
-
기존문법
FROM 테이블1, 테이블2, 테이블3
WHERE 테이블1.COL=테이블2.COL
AND 테이블2.COL=테이블3.COL -
SQL-99문법
FROM 테이블1
JOIN 테이블2 ON 조건식
JOIN 테이블3 ON 조건식
📖 5. 데이터베이스 모델링
📌 데이터베이스 모델링
현실에서 쓰이는 것을 테이블로 변경하기 위한 작업
- 개념적 데이터 모델링(conceptual modeling)
- 현실 세계의 중요 데이터를 추출하여 개념 세계로 옮기는 작업
- 개체-관계 모델 - 논리적 데이터 모델링(logical modeling)
- 개념 세계의 데이터를 데이터베이스에 저장하는 구조로 표현하는 작업
- 관계 데이터 모델
- 개체 : 다른 개체와 구별되는 이름을 가지고 있고, 각 개체만의 고유한 특성이나 상태. 즉, 속성 하나 이상을 가지고 있음 / 테이블과 비슷한 개념
- 개체 인스턴스 : 테이블의 행과 비슷한 개념 / 개체 집합 : 데이터와 비슷한 개념
- 속성 : 의미 있는 데이터의 가장 작은 논리적 단위 / 개체나 관계가 가지고 있는 고유의 특성 / 칼럼과 비슷한 개념
- 속성 값의 갯수
- 단일 값 속성 : 값을 하나만 가질 수 있음 ex)이름
- 다중 값 속성 : 값을 여러개 가질 수 있음 ex)연락처 - 의미의 분해 가능성
- 단순 속성 : 의미를 더는 분해할 수 없는 속성 ex)고객의 적립금
- 복합 속성 : 의미를 분해할 수 있는 속성 ex) 주소의 도, 시, 동으로 세분화 - 유도속성 : 기존의 다른 속성의 값에서 유도되어 결정되는 속성
- ex) 책 개체의 가격과 할인율의 속성으로 계산되는 판매가격 속성 - 키 속성(key attribute) : 각 개체 인스턴스를 식별하는 데 사용되는 속성
- ex) 고객 개체의 고객아이디 속성
- 속성 값의 갯수
- 관계(relationship) : 개체 집합들 사이의 대응 관계 (매핑을 의미)
- 일대일(1:1) 관계 ex) 남편개체 - 아내개체- 일대다(1:n) 관계 ex) 부서개체 - 사원개체
- 다대다(n:m) 관계 ex) 고객개체 - 책개체
📖 6. 관계형 데이터 모델링
개념적 구조를 논리적 구조로 표현하는 논리적 데이터 모델, 하나의 개체에 대한 데이터를 하나의 릴레이션에 저장
📌 관계 데이터 모델의 기본 용어
릴레이션(relation)
하나의 개체에 관한 데이터를 2차원 테이블의 구조로 저장한 것
파일관리시스템 관점에서 파일에 대응
릴레이션의 특성 : 투플의 유일성 / 투플의 무순서 / 속성의 무순서 / 속성의 원자성
-
속성(attribute)
릴레이션의 열
파일관리시스템 관점에서 필드에 대응 -
투플(tuple)
릴레이션의 행
파일관리시스템 관점에서 레코드에 대응 -
도메인(domain)
하나의 속성이 가질 수 있는 모든 값의 집합
일반적으로 속성의 특성을 고려한 데이터 타입으로 정의 -
널(NULL)
속성 값을 아직 모르거나 해당되는 값이 없음을 표현
-
차수(degree)
하나의 릴레이션에서 속성의 전체 개수
-
카디널리티(cardicality)
하나의 릴레이션에서 투플의 전체 개수
📌 데이터베이스의 구성
-
데이터베이스 스키마_
데이터베이스의 전체 구조
데이터베이스를 구성하는 릴레이션 스키마의 모음_ -
데이터베이스 인스턴스_
데이터베이스를 구성하는 릴레이션 인스턴스의 모음_
📌 키(key)
릴레이션에서 투플들을 유일하게 구별하는 속성 또는 속성들의 집합
유일성, 최소성
-
슈퍼키(super key)
유일성을 만족하는 속성 또는 속성들의 집합
ex)고객아이디, (고객아이디, 고객이름), (고객이름, 주소)등 -
후보키(candidate key)
유일성과 최소성을 만족하는 속성 또는 속성들의 집합
ex)고객아이디, (고객이름, 주소)등 -
기본키(primary key)
후보키 중에서 기본적으로 사용하기 위해 선택한 키
ex)고객아이디 -
대체키(alternate key)
기본키로 선택되지 못한 후보키
ex)(고객이름, 주소)
외래키(foreign key)
다른 릴레이션의 기본키를 참조하는 속성 또는 속성들의 집합
릴레이션들 간의 관계를 표현
- 참조하는 릴레이션 : 외래키를 가진 릴레이션
- 참조되는 릴레이션 : 외래키가 참조하는 기본키를 가진 릴레이션
