[학습일지]JAVA교육일지 48일차

1. UPDATE

2. DELETE

3. GROUP BY

4. SUBQUERY

5. SQL 기본 문법 및 실행 순서

---

1. UPDATE

[ 정의 ]

- DML 명령어

- 원하는 행의 원하는 컬럼값을 수정하는 명령어

- UPDATE의 WHERE절은 정말 중요하다. (데이터 다 날라갈 수 있음)

- UPDATE에서 컬럼값 수정시 PK값은 절대로 수정하면 안된다.

UPDATE 테이블명 SET 컬럼명 = [컬럼명 = 수정할 값] x N [WHERE절]

 

[ 예제 ]

--기존 데이터 복구를 위한 트랜잭션 처리(추후 배울 예정)
COMMIT;
ROLLBACK;

SELECT * FROM TBLCOUNTRY;

예제1) 대한민국의 수도 '서울' -> '세종시' 이전하기
UPDATE TBLCOUNTRY SET
    CAPITAL = '세종시'
        WHERE NAME = '대한민국'; --PK컬럼을 조건으로 지정하면 유일한 행을 검색할 수 있다.

예제2) 대한민국의 수도 '서울' -> '제주시' 이전하기, 면적 10 넓히기, 인구수 12% 증가
UPDATE TBLCOUNTRY SET
    CAPITAL = '제주시'
    AREA = AREA + 10,
    POPULATION = POPULATION * 1.2
        WHERE NAME = '대한민국';

2. DELETE

[ 정의 ]

- DML 명령어

- 원하는 레코드, 행 데이터를 삭제하는 명령어이다

DELETE [FROM] 테이블명 [WHERE절]

 

[ 예제 ]

COMMIT;
ROLLBACK;

SELECT * FROM TBLCOUNTRY;

DELETE FROM TBLCOUNTRY; --전체삭제
DELETE FROM TBLCOUNTRY WHERE CONTINENT = 'AS'; -- CONTINENT컬럼 리스트 중 'AS'가 속한 행 삭제
DELETE FROM TBLCOUNTRY WHERE NAME = '대한민국'; -- NAME컬럼 리스트 중 '대한민국'이 속한 행 삭제

3. GROUP BY

[ 정의 ]

- 레코드들을 특정 컬럼값(1개 or N개)에 맞춰서 그룹을 나누는 역할

- 그룹을 나누는 이유: 집계 함수(COUNT, SUM, AVG, MAX, MIN) 를 적용하기 위해서이다.

GROUP BY 컬럼명 [, 컬럼명] x N

 

[ 예제 ]

예제1) 부서별 인원수, 평균급여, 급여총액 구하기
SELECT
    BUSEO AS 부서명,
    COUNT(*) AS 부서인원수,
    ROUND(AVG(BASICPAY)) AS 부서평균급여,
    ROUND(SUM(BASICPAY)) AS 부서급여총액
FROM TBLINSA
    GROUP BY BUSEO;
    
예제2) 주민번호 데이터(123456-1234567)로 남자 직원수, 여자 직원수 구하기
SELECT
    CASE
        WHEN SUBSTR(SSN, 8, 1) = '1' THEN '남자'
        WHEN SUBSTR(SSN, 8, 1) = '2' THEN '여자'
    END AS GENDER,
    COUNT(*)
FROM TBLINSA
    GROUP BY SUBSTR(SSN, 8, 1);

 

[ HAVING ]

- GROUP BY에 대한 조건절이며 집계 결과를 대상으로 조건을 질문한다.

- HAVING, WHERE의 차이점?

  1) FROM절 -> WHERE절 : 개인(레코드 1개씩)에 대한 조건

  2) GROUP BY절 -> HAVING절 : 그룹(집계 함수)에 대한 조건

예제1) 부서별 평균급여가 150만원 이상인 인원수 구하기  
SELECT
    BUSEO,
    COUNT(*)
FROM TBLINSA
    GROUP BY BUSEO
        HAVING AVG(BASICPAY) > 1500000;

예제2) 부서별 직급의 인원수 구하기
SELECT
    BUSEO AS 부서,
    COUNT(*) AS 인원,
    COUNT(DECODE(POSITION, '부장', 1)) AS 부장,
    COUNT(DECODE(POSITION, '과장', 1)) AS 과장,
    COUNT(DECODE(POSITION, '대리', 1)) AS 대리,
    COUNT(DECODE(POSITION, '사원', 1)) AS 사원
FROM TBLINSA
    GROUP BY BUSEO;

 

4. SUB QUERY

[ 메인 쿼리, MAIN QUERY ]

- 하나의 SELECT(INSERT, UPDATE, DELETE)로만 구성된 쿼리

- 여지껏 배운것이 메인 쿼리이다.

 

[ 서브 쿼리, SUB QUERY ]

- 하나의 문장에 2개 이상의 SELECT를 추가해서 구성된 쿼리

- 하나의 쿼리안에 또 다른 쿼리가 들어있는 형태

- 삽입위치: SELECT절, FROM절, WHERE절, ORDER BY절 등...

 

[ 서브 쿼리 용도 ]

1. 조건절 비교값으로 사용
    A. 반환값이 1행 1열 > 단일값, 값 1개  > 스칼라 쿼리(1행 1열) > 연산자 이용
    B. 반환값이 N행 1열 > 다중값, 값 N개  > IN 사용(열거형 비교)
    C. 반환값이 1행 N열 > 복합값, 값 N개  > 연산자 사용 (N:N)
    D. 반환값이 N행 N열 >     B + C      > B와 C의 방식을 혼합해서 사용

2. 컬럼리스트에서 사용
    A. 반환값이 1행 1열 > 스칼라 쿼리
        1) 정적 쿼리 (모든 행에 동일한 값을 반환)
        2) 상관 서브 쿼리 (서브 쿼리의 값과 바깥쪽 메인 쿼리의 값을 연계시켜 값을 반환) ******중요

 

[ 예제 ]

예제1) 인구수가 가장 많은 나라?
SELECT NAME FROM TBLCOUNTRY --> 메인쿼리
	WHERE POPULATION = (SELECT MAX(POPULATION) FROM TBLCOUNTRY); --> 서브쿼리

예제2) '홍길동'과 같은 부서에 근무하는 인원은?
SELECT * FROM TBLINSA 
	WHERE BUSEO = (SELECT BUSEO FROM TBLINSA WHERE NAME = '홍길동') AND NAME <> '홍길동';	

예제3) 정적 쿼리, 남자 이름('홍길동')으로 남자 키, 여자친구 이름, 키 구하기
SELECT
    NAME AS 남자이름,
    HEIGHT AS 남자키,
    COUPLE AS 여자이름,
    (SELECT HEIGHT FROM TBLWOMEN WHERE NAME = '장도연') AS 여자키
FROM TBLMEN
    WHERE NAME = '홍길동';

예제4) 상관 서브 쿼리 - 모든 커플의 남자/여자 이름, 키 구하기
SELECT
    NAME AS 남자이름,
    HEIGHT AS 남자키,
    COUPLE AS 여자이름,
    (SELECT HEIGHT FROM TBLWOMEN WHERE NAME = TBLMEN.COUPLE) AS 여자키
FROM
    WHERE COUPLE IS NOT NULL;

5. SQL 기본 문법 및 실행 순서

[ SQL 기본 문법 ]

SELECT 컬럼리스트
FROM 테이블명
WHERE 조건
GROUP BY 그룹기준
ORDER BY 정렬

 

[ SQL 실행 순서 ]

1) FROM

2) WHERE

3) GROUP BY

4) SELECT

5) ORDER BY

---

MEMO>

 

# UPDATE, DELETE 는 사용법이 쉬운만큼 매우 신중하게 해야한다. 트랜잭션(COMMIT, ROLLBACK) 필수!!

 

# 데이터베이스 공부에서 JOIN 과 SUBQUERY는 엄청 많이 쓰이기 때문에 공부를 가장 많이 하자. (JOIN은 이번주에 배울 예정)

 

# 결과값을 확인할때 Ctrl + Enter를 입력하지만, 단축키 F5를 누른다면 표 형태로 확인 할 수 있다. (복사하기 좋음)