데이터베이스 모델링
-
테이블 설계
-
컬럼 결정
-
타입 정하기
-
관계 정의
-
폭포수 모델 : back 할 수 없다
-
애자일 모델 : 자유롭게 back 할 수 있다.
-
ER-WIN (ERD(Entity Realation Diagram)) : ERD를 보며 조인 해야한다!
-
관계 형태(1:1, 1:n, n:n)
-
PK와 FK를 통해서 상속관계 증명 : 관계가 있다. 주는 쪽과 받는 쪽이 결정된다.
논리적 설계
개체, Entity, 속성(Attribute)
물리적 설계
테이블, 컬럼 형성 - 타입이 결정된다
DML
- DML
SELECT
FROM
WHERE
GROUP BY
[[HAVING]]
ORDER BY
SELECT 컬럼명1, 컬럼명2,.... 함수명(컬럼명3)
FROM 집합1, 집합2, (SELECT 문 - 인라인뷰)
WHERE 컬럼명1 = 값(상수만 아니다. select문 - 서브쿼리) : 조건검색, 조인하는 곳
AND 컬럼명2 = 값(SELECT 문) : AND는 교집합 - 원소가 줄어듦. 경우의 수 감소. 일량 감소 -> 속도 빠름
OR 컬럼명3 = 값(IN) : OR는 합집합 - 경우의 수가 계속 증가. 일량 증가(가능하면 사용하지 말 것)
GROUP BY 컬럼명1, 컬럼명2(단, 그룹함수 아님. GROUP BY 절에 업는 컬럼을 썼을 때)
[[HAVING]]
ORDER BY- OR는 있지만 사용하지 않음
- 합집합으로 경우의 수가 계속 증가한다
- 따라서 일량이 증가하기 때문에 웬만하면 사용하지 않는 것이 좋다
(char타입(고정형)도 있지만 varchar2타입(가변형)만 쓰듯)
GROUP BY
GROUP BY절에 여러 개의 조건이 올 수 있다 : 업무에 대한 복잡도 높을수록 조건 증가
--에러: 단일 그룹의 그룹 함수가 아닙니다
SELECT ename, sum(sal)
FROM emp;
--원하는 값 출력되지 않음
SELECT max(ename), sum(sal)
FROM emp
GROUP BY ename;
--GROUP BY절에 여러 개의 조건이 올 수 있다 : 업무에 대한 복잡도 높을수록 조건 증가
SELECT deptno, JOB
FROM emp
GROUP BY deptno, JOB
ORDER BY deptno;sum(decode(..)) 패턴
--sum(decode(...)) 패턴 : 소계, 총계, 계...
SELECT decode(JOB, 'CLERK', sal, NULL)
FROM emp;
SELECT sum(decode(JOB, 'CLERK', sal, NULL))
FROM emp;JOIN
- 2개 이상의 테이블을 가지고 JOIN 한다
- 집합과 집합은 관계가 있다
- 관계형태 : 1:1관계, 1:n관계, n:n관계
- !주의!
n:n은 업무에 대한 정의가 덜 된 경우이므로조인을 하면카타시안 곱이 된다.
1. NATURAL JOIN ( = EQUAL JOIN)
- 양 쪽에 모두 있는 값만 나온다
- 한 쪽 테이블에만 있는 값은 나오지 않는다 - 이 때는 OUTER JOIN
2. NON-EQUALS JOIN
:= 같다로 비교하는 것 이외의 모든 것
3. OUTER JOIN
2개 이상의 테이블 조인 시 A 테이블의 행에 대해 B 테이블에 일치하는 행이 없더라도 B 테이블의 행을 NULL로 하여 행을 리턴한다.
4. SELF JOIN
- 셀프 조인을 사용하는 경우
- 하나의 테이블 안에 서로 계층적 관계와 같이 연관성이 있는 경우 이 컬럼들 간의 셀프 조인을 통해 값을 출력하고자 한다.
<self join 과정>
1. 모든 경우의 수를 만든다(FROM tdept A, tdept b와 같이 카타시안 곱을 만듦).
2. 카타시안 곱으로 모든 값이 출력된 상태에서 WHERE절로 조건검색을 한다(WHERE A.parent_dept = b.dept_code;).
문제)
tdept테이블에 자신의 상위 부서 정보를 관리하고 있다.
이 테이블을 이용하여 부서코드, 부서명, 상위부서코드, 상위부서명을 읽어오는
쿼리를 만들어 보자.
<생각하는 과정>
1. 테이블 하나로는 원하는 값을 출력할 수 없다.
--테이블 하나로는 원하는 값을 출력할 수 없다
SELECT dept_name, parent_dept
FROM tdept; 
- 테이블 하나로 alisa명만 바꾼다해도 결국 같은 값만 두 번 출력될 뿐이다.
SELECT dept_name AS "부서명"
, dept_name AS "상위부서명"
FROM tdept; 
- 동일 테이블 두 개를 가져와 부서명, 상위부서명으로 출력하면 같은 값들 간 카타시안 곱이 발생하여 10 X 10으로 100개의 값이 출력된다.
SELECT A.dept_name AS "부서명"
, b.dept_name AS "상위부서명"
FROM tdept A, tdept b; 
- 아래 쿼리문에서 10개의 행으로 이미지와 같은 값이 출력되는 과정은 다음과 같다.
-
WHERE절의 의미는 a테이블 parent_dept 컬럼이 b테이블 dept_code 컬럼과 동일한 것만 조인한다는 것이다.
-
따라서 a.parent_dept의 aa0001 X3, b.dept_code의 aa0001이 동일하기 때문에 얘네끼리 조인을 하게 된다.
-
이때 a.parent_dept의 aa0001은 3개라서 dept_name을 경영지원, 재무, 총무를 다 갖게 된다.
b.dept_code의 aa0001은 1개라서 dept_name을 경영지원만 갖게 된다. -
출력
- WHERE절에 a를 기준으로 셀프 조인을 했기 때문에 출력도 a를 기준으로 동일한 코드를 가진 dept_name들만 출력돼서 총 10개의 row가 보여진다.
<답>
--둘의 관계형태가 1:n이니 정의를 잘해서 사용해야 한다 SELECT A.dept_code AS "부서코드" , A.dept_name AS "부서명" , b.parent_dept AS "상위부서코드" , b.dept_name AS "상위부서명" FROM tdept A, tdept b WHERE A.parent_dept = b.dept_code;
PL/SQL
http://www.gurubee.net/lecture/1039
<함수와 프로시저가 같은 것인가?>
- 함수
- 사용자 정의 함수는 반드시 반환값이 존재한다.
- 호출해야 실행된다
- 프로시저
- 프로시저는 return 예약어를 사용하지 않고 파라미터 자리에 OUT 속성을 추가하여 자바단으로 보낼 수 있다.
- 호출해야 실행된다
- SELECT ... INTO 변수 문을 지원한다 - 단 한 개 로우에 대해서만 가능하다
- 만일 멀티 로우에 있는 값을 담으려면 반드시 cursor를 사용해야 한다.
구구단 구하기
CREATE OR REPLACE PROCEDURE proc_gugudan(dan IN number)
IS
n_i number(2);
BEGIN
n_i :=0;
dbms_output.put_line(dan||'단을 출력합니다.');
FOR n_i IN 1..9 LOOP
dbms_output.put_line(dan||'*'||n_i||'='||(dan*n_i));
END LOOP;
END;🔽출력
exec proc_gugudan(3);
문제
SELECT *
FROM (
SELECT ROWNUM rno, workcd_vc, time_nu FROM t_worktime
WHERE ROWNUM < 4
)A,
(
SELECT ROWNUM rno, workcd_vc, time_nu FROM t_worktime
WHERE ROWNUM < 4
)b;
