연결된 테이블 안의 데이터를 작업하는 방법

-- 핸드폰 테이블
CREATE TABLE TBL_PHONE (
    PHONE_NUMBER NUMBER PRIMARY KEY,
    PHONE_COLOR VARCHAR2(1000),
    PHONE_SIZE VARCHAR2(1000),
    PHONE_PRICE NUMBER,
    PHONE_PRODUCTION_DATE DATE,
    PHONE_SALE NUMBER
);

-- 케이스 테이블
CREATE TABLE TBL_CASE (
    CASE_NUMBER NUMBER PRIMARY KEY,
    CASE_COLOR VARCHAR2(1000),
    CASE_PRICE NUMBER,
    PHONE_NUMBER NUMBER,
    CONSTRAINT FK_CASE FOREIGN KEY (PHONE_NUMBER)
        REFERENCES TBL_PHONE (PHONE_NUMBER)
);

기본 테이블을 입력해주려고합니다.

INSERT INTO TBL_PHONE 
VALUES (1, 'GREEN', '8', 100, TO_DATE('2023-02-14','YYYY-MM-DD'), 0);

INSERT INTO TBL_PHONE 
VALUES (2, 'PHANTHOM', '8', 100, SYSDATE - 10, 0);

INSERT INTO TBL_PHONE 
VALUES (3, 'LANVENDA', '8', 80, TO_DATE('2023-03-01','YYYY-MM-DD'), 0);

TO_DATE : 날짜 지정

SYSDATE : 오늘날짜

참조된 데이터 변경하는방법

UPDATE TBL_PHONE
SET PHONE_NUMBER = 4
WHERE PHONE_NUMBER = 1;

SQL Error [2292] [23000]: ORA-02292: integrity constraint (HR.FK_CASE) violated - child record found

이런 에러가나는데 이미 부모자식간으로 연결되어있기때문에 안된다는겁니다.

해결방법1 . 업데이트

UPDATE TBL_CASE
SET PHONE_NUMBER =2
WHERE CASE_NUMBER =1;
SELECT * FROM TBL_CASE;

UPDATE로 재실행해주고 다시 변경된값을 참조테이블에 수정을 했습니다.

해결방법2. NULL값

UPDATE TBL_PHONE
SET PHONE_NUMBER = 4
WHERE PHONE_NUMBER = 1;
SELECT * FROM TBL_PHONE;

UPDATE TBL_CASE
SET PHONE_NUMBER =NULL
WHERE PHONE_NUMBER = 2;

SELECT * FROM TBL_CASE;
SELECT * FROM TBL_PHONE;

UPDATE TBL_PHONE
SET PHONE_NUMBER =5
WHERE PHONE_NUMBER = 2;

--NULL->5로 변경
SELECT * FROM TBL_CASE;

UPDATE TBL_CASE
SET PHONE_NUMBER = 5 
WHERE PHONE_NUMBER IS NULL; 

SELECT * FROM TBL_CASE;

문제는 외래키는 널,중복도 허용하는데 NULL 값은 권장하지않구요.(추후문제발생)

기본키는 NULL값도 안된다.

해결방법3. 임시값 활용

---임시값 넣기
INSERT INTO TBL_PHONE(PHONE_NUMBER) VALUES (0);

SELECT * FROM TBL_PHONE;

SELECT * FROM TBL_CASE;

자식테이블에있는3을 0으로 변경해보는것을 하려고하는데 일단 NULL로 변경한 후 0으로 변경해보면될것같다.

자식, 부모 테이블간에 어떻게 연결되고있는지를 잘 이해하고있어야한다.

--CASE에서 3->0 변경

UPDATE TBL_CASE
SET PHONE_NUMBER = 0
WHERE PHONE_NUMBER = 3;
SELECT * FROM TBL_CASE;

--부모쪽 3->6변경
UPDATE TBL_PHONE
SET PHONE_NUMBER = 6
WHERE PHONE_NUMBER = 3;
SELECT * FROM TBL_PHONE;

-- 회원정보 변경완료
UPDATE TBL_CASE
SET PHONE_NUMBER = 6
WHERE PHONE_NUMBER = 0;
SELECT * FROM TBL_CASE;

이렇게 했으면 이제 임시값을 삭제해야합니다.

--임시값 삭제
DELETE FROM TBL_PHONE
WHERE PHONE_NUMBER = 0;

이후 다시 조회해보면

임시값이 사라졌고 데이터들도 내가 원하는방향으로 모두 변경된것을 확인할 수 있다.

그외 데이터 변경

항상 자식이 먼저 삭제하고 그 다음 부모에서 삭제할 수 있다는점을 꼭 알아두어야한다.

혹은 FK를 설정할때 옵션(온디스테이드)를 설정하면 부모테이블에서 삭제하면 자동으로 자식에게도 삭제된다.

이건 리스크가있다.

데이터 삭제

자식테이블 연결해제 후 삭제하면됨.

--삭제안되는 케이스
DELETE TBL_PHONE
WHERE PHONE_NUMBER =4;

SQL Error [2292] [23000]: ORA-02292: integrity constraint (HR.FK_CASE) violated - child record found

똑같은 에러입니다.

--삭제 (자식테이블삭제)
DELETE TBL_CASE
WHERE PHONE_NUMBER =4;

DELETE TBL_PHONE
WHERE PHONE_NUMBER =4;
SELECT * FROM TBL_PHONE;
SELECT * FROM TBL_CASE;

옵션을 줘서 한번에 삭제하기

---------옵션설정해서 한번에삭제하기
-- 1. 테이블연결 끊기
ALTER TABLE TBL_CASE DROP CONSTRAINT FK_CASE;
--실행하면 테이블 해제가 되어있는것을 확인할 수 있음

-- 2. 구조관계로 알터를 집어넣기
ALTER TABLE TBL_CASE ADD CONSTRAINT FK_CASE FOREIGN KEY(PHONE_NUMBER)
REFERENCES TBL_PHONE(PHONE_NUMBER) ON DELETE CASCADE;

SELECT * FROM TBL_PHONE;


DELETE FROM TBL_PHONE
WHERE PHONE_NUMBER = 5;

SELECT * FROM TBL_PHONE;

여기서는

ON DELETE CASCADE; 라는 옵션을 알면됩니다.

이렇게되면 부모에 걸려있던 자식테이블 모두 다 한번에 일괄삭제가 되는것을 확인할 수 있습니다.

▶ 실습1

1. 회원정보 추가
    1. 이름(보라돌이), 나이(10), 연락처(010-1111-1111), 주소(중랑구)
    2. 이름(나나), 나이(20), 연락처(010-2222-2222), 주소(강남구)
    3. 이름(뚜비), 나이(30), 연락처(010-3333-3333), 주소(관악구)

2.정보 추가
    1. 책이름(명탐정 코난), 장르(추리), 값(NULL)
    2. 책이름(파친코), 장르(로맨스), 값(NULL)
    3. 책이름(이것이DBMS다), 장르(IT), 값(NULL)

3. 회원 이름을 변경
   -> 회원번호 1번을 ‘뽀오’로 변경하시오

4. 대여 하기
   -> 첫 번째 책과 두 번째 책은 회원 1번이 대여한다.
   -> 세 번째 책은 회원 3번이 대여한다.

5. 책 대여한 회원 번호 수정
   -> 대여한 책 중 명탐정 코난은 회원 4번이 대여했으며,
   -> 회원 1번이 회원번호 4번인 것으로 파악되었다.

6. 회원 삭제
   -> 3번 회원이 탈퇴하였다. 테이블에서 삭제처리하여라.

2. 회원정보 추가
    1. 이름(보라돌이), 나이(10), 연락처(010-1111-1111), 주소(중랑구)
    2. 이름(나나), 나이(20), 연락처(010-2222-2222), 주소(강남구)
    3. 이름(뚜비), 나이(30), 연락처(010-3333-3333), 주소(관악구)
INSERT INTO TBL_MEMBER2 VALUES(1,'보라돌이',10,'010-1111-1111','중랑구');
INSERT INTO TBL_MEMBER2 VALUES(2,'나나',20,'010-2222-2222','강남구');
INSERT INTO TBL_MEMBER2 VALUES(3,'뚜비',30,'010-3333-3333','관악구');

3. 책 정보 추가
    1. 책이름(명탐정 코난), 장르(추리), 값(NULL)
    2. 책이름(파친코), 장르(로맨스), 값(NULL)
    3. 책이름(이것이DBMS다), 장르(IT), 값(NULL)
INSERT INTO TBL_BOOK VALUES(1, '명탐정 코난','추리',NULL);
INSERT INTO TBL_BOOK VALUES(2, '파친코','로맨스',NULL);
INSERT INTO TBL_BOOK VALUES(3, '이것이 DBMS다','IT',NULL);


4. 회원 이름을 변경
    1. 회원번호 1번을 ‘뽀오’로 변경하시오
UPDATE TBL_MEMBER2
SET MEMBER2_NAME = '뽀오'
WHERE MEMBER2_NUMBER = 1;

5. 책 대여 하기
    1. 첫 번째 책과 두 번째 책은 회원 1번이 대여한다.
    2. 세 번째 책은 회원 3번이 대여한다.

UPDATE TBL_BOOK
SET MEMBER2_NUMBER = 1
WHERE BOOK_NUMBER = 1;

UPDATE TBL_BOOK
SET MEMBER2_NUMBER = 1
WHERE BOOK_NUMBER = 2;

UPDATE TBL_BOOK
SET MEMBER2_NUMBER = 3
WHERE BOOK_NUMBER = 3;


6. 책 대여한 회원 번호 수정
    1. 대여한 책 중 명탐정 코난은 회원 4번이 대여했으며, 
    회원 1번이 회원번호 4번인 것으로 파악되었다.
UPDATE TBL_BOOK
SET MEMBER2_NUMBER = NULL
WHERE MEMBER2_NUMBER = 1;

UPDATE TBL_MEMBER2
SET MEMBER2_NUMBER = 4
WHERE MEMBER2_NUMBER = 1;

UPDATE TBL_BOOK
SET MEMBER2_NUMBER = 4
WHERE MEMBER2_NUMBER IS NULL;

7. 원 삭제
    1. 3번 회원이 탈퇴하였다. 테이블에서 삭제처리하여라.
UPDATE TBL_BOOK
SET MEMBER2_NUMBER = NULL
WHERE BOOK_NUMBER=3;

DELETE FROM TBL_MEMBER2
WHERE MEMBER2_NUMBER = 3;

정규화

- 관계형 데이터베이스에서 데이터의 중복을 최소화

- 데이터의 일관성 & 무결성을 유지하기 위한 하나의 방법론

- 테이블을 분리하는 과정(쪼개는 과정이라고 일컫음)

- 1차~6차까지 분리하는 과정입니다. 보통 1~3차정도까지 합니다.

#정규화의 목적= 장점

1. 데이터의 중복 최소화

2. 데이터의 일관성 유지

3. 데이터 무결성 보장

4. 저장 공간의 효율화

5. 데이터베이스 성능 향상

6. 데이터베이스 유지보수 용이

# 단점

1. 과도한 정규화는 테이블 수를 증가하고 연산능력을 저하시킨다.

2. 복잡한 쿼리 작성 및 데이터 관리에 어려움이 있다.

# 1NF(제 1 정규화), 2NF(제 2 정규형), 3NF(제 3 정규형)

라고 일컫음.

01. 1정규형 (원자 값)

> 모든 속성 값이 원자 값(automic values)을 가져야 한다. 즉, 하나의 컬럼에는 하나의 값만 가져야한다.

02. 2정규형 (조합키)

> 1정규형을 만족하고, 조합키가 아닌 다른 속성값으로 기본 키(조합키) 역할을 수행하면 안된다.

03. 3정규형 (종속성)

> 2정규형을 만족하고 기본키 또는 식별할 수 있는 키가 여러개 있는 경우 테이블을 분리하여 단 하나의 컬럼값으로 나머지 컬럼 값을 추측할 수 있도록 분리한다.

* 정규화를 진행하지 않는 경우 발생할 수 있는 문제점(이상현상)

- 삽입이상 : 새로운 데이터를 입력하기 위해서 필요하지 않는 데이터도 입력

- 수정이상 : 중복되는 행 중에서 일부만 변경해서 데이터가 일치하지 않는 경우(일관성)

- 삭제이상 : 행을 삭제했을 때 필요한 데이터도 같이 삭제되는 현상

실습2 (머리푸는 문제)

PLAYER테이블에서 WEIGHT가 70이상이고 80이하인 선수 검색

PLAYER테이블에서 TEAM_ID가 ‘K03’이고 HEIGHT가 180 미만인 선수 검색

PLAYER테이블에서 TEAM_ID가 ‘K06’이고 NICKNAME이 ‘제리’인 선수 검색

PLAYER테이블에서 HEIGHT가 150이상이고 WEIGHT가 80이상인 선수 이름 검색

STADIUM테이블에서 SEAT_COUNT가 30000초과이고 41000이하인 경기장 검색

PLAYER테이블에서 TEAM_ID가 ‘K06’이거나 ‘K07’이고 포지션은 ‘MF’인 선수 검색

SELECT * FROM PLAYER;

-- PLAYER테이블에서 WEIGHT가 70이상이고 80이하인 선수 검색
SELECT * 
FROM PLAYER
WHERE WEIGHT BETWEEN 70 AND 80;

-- PLAYER테이블에서 TEAM_ID가 ‘K03’이고 HEIGHT가 180 미만인 선수 검색
SELECT * 
FROM PLAYER
WHERE TEAM_ID = 'K03' AND HEIGHT < 180;

--PLAYER테이블에서 TEAM_ID가 ‘K06’이고 NICKNAME이 ‘제리’인 선수 검색
SELECT * 
FROM PLAYER
WHERE TEAM_ID = 'K06' AND NICKNAME = '제리';

--PLAYER테이블에서 HEIGHT가 150이상이고 WEIGHT가 80이상인 선수 이름 검색
SELECT * 
FROM PLAYER
WHERE HEIGHT >= 150 AND WEIGHT >= 80;

--STADIUM테이블에서 SEAT_COUNT가 30000초과이고 41000이하인 경기장 검색
SELECT * 
FROM STADIUM
WHERE SEAT_COUNT > 30000 AND SEAT_COUNT <= 41000;

--PLAYER테이블에서 TEAM_ID가 ‘K06’이거나 ‘K07’이고 포지션은 ‘MF’인 선수 검색
SELECT * FROM PLAYER
WHERE TEAM_ID IN('K06', 'K07') AND "POSITION" = 'MF';

여기서 맨 마지막 문제는 POSITION 은 쌍따옴표로 잡아야 컬럼명으로 인식된다는점을 꼭 알아야합니다!!

물론 OR연산자로 사용해도됩니다만 IN 연산자를 사용하면 조금이라도 더 짧게 작성가능합니다.

SELECT * FROM PLAYER
WHERE (TEAM_ID = 'K06' OR TEAM_ID = 'K07') AND "POSITION" = 'MF';

여기서 중요한건 연산순서때문에 꼭! ()를 쳐주어야합니다.

두개가 다른 ROW값이 있기때문에 (괄호 전 30줄, 후 11줄) 꼭 알아두어야한다.

실습3

-- AVG, MAX, MIN, SUM, COUNT
--COUNT 함수 포함해서 모든 집계함수는 NULL값을 제외하고 개수의 합계를 구한다!
SELECT * FROM PLAYER;

풀이방법 1.

-- NULL값을 다른 값으로 치환하기
-- NVL, NVL2를이용하기
SELECT COUNT(NVL(HEIGHT,0)) FROM PLAYER;

다른값으로 치환하기

SELECT * FROM PLAYER;
SELECT COUNT(HEIGHT) FROM PLAYER; --447개

확인해보기 447!

GROUP BY [HAVING]

>> 특정 컬럼을 그룹으로 묶어서 조회함

> 특정 컬럼에 대한 그룹화를 적용시킬대 사용하는 방법

> ~별, ~그룹을 기준으로 EX). FW, DF, GK 등 별 평균키

> HAVING 절은 WHERE절과 같이 GROUP BY절에 조건을 설정할 수 있다.

> WHERE절은 쿼리 전체에 적용 <--->

GROUPNY는 "POSITION"처럼 전체는 되지 않는다.

실습4

01. LOCATIONS테이블에서 국가별 수를 검색하여라.

02. STADIUM테이블에서 경기장별로 좌석수가 많은 순서대로 조회해라.

03. SCHEDULE테이블에서 HOMETEAM_ID가 K06이상인 대상자를 조회하여라.
단, 조회 시 Y,N을 구분하여 합계의 수를 조회하여라.

저작자표시

'DBMS' 카테고리의 다른 글

서브쿼리, ROWNUM, View (0)	2025.02.08
조합(복합)키, NULL값 제어, 모델링 (0)	2025.02.02
제약조건, REFERENCES, 기타 제약조건 (0)	2025.01.21
TABLE만들기 & DDL언어 (0)	2025.01.20
DMBS의 기본(DISTINCT, 자료형,연산자, LIKE, NULL )_(3) (0)	2025.01.17

윤슬의 바다

연결된 테이블 안의 데이터를 작업하는 방법 | 정규화

참조된 데이터 변경하는방법

해결방법1 . 업데이트

해결방법2. NULL값

해결방법3. 임시값 활용

그외 데이터 변경

데이터 삭제

옵션을 줘서 한번에 삭제하기

▶ 실습1

정규화

실습2 (머리푸는 문제)

실습3

GROUP BY [HAVING]

>> 특정 컬럼을 그룹으로 묶어서 조회함

실습4

'DBMS' 카테고리의 다른 글

티스토리툴바

연결된 테이블 안의 데이터를 작업하는 방법 | 정규화

참조된 데이터 변경하는방법

해결방법1 . 업데이트

해결방법2. NULL값

해결방법3. 임시값 활용

그외 데이터 변경

데이터 삭제

옵션을 줘서 한번에 삭제하기

▶ 실습1

정규화

실습2 (머리푸는 문제)

실습3

GROUP BY [HAVING]

>> 특정 컬럼을 그룹으로 묶어서 조회함

실습4

'DBMS' 카테고리의 다른 글

관련글

티스토리툴바