스마트팩토리 1주 4일차 DB 4일차 DB

서브쿼리의 구문

SELECT select_list

FROM table명

WHERE 표현식 연산자

(SELECT  select_list

FROM table명);

서브쿼리가 메인쿼리보다 먼저 수행 

서브쿼리의 사용 시 고려사항

서브쿼리는 반드시 괄호에 감싸서 수행

서브쿼리는 비교 연산자의 우측에 위치

TOP-N 분석 기능을 제외하고는 서브쿼리에 ORDER BY 절을 수행할 필요 없음

결과값이 하나인 서브쿼리에 대해서는 단일행 연산자를, 결과값이 여러개인 다중행 서브쿼리에 대해서는 다중행연산자를 사용

ORDER BY는 연산수행속도에 안 좋아서 되도록 안 쓰는 것이 좋음

## 서브쿼리

=> SQL 내부에 있는 또다른 쿼리

=> 소괄호로(서브쿼리) 감싼다

=> 서브쿼리 안에 또 서브쿼리가 올 수 있다

=> 메인쿼리 안에 여러 개의 서브쿼리가 올 수 있다.

=> 수행순서 : 서브쿼리(내부쿼리) 실행 -> 메인쿼리(외부쿼리) 실행

=> 서브쿼리의 리턴값이 NULL이면 결과값도 NULL이다.

=> 사용위치 :

SELECT 절 -> 함수로 구현하여 제공하는 추세

WHERE 절 -> 조건식의 우항(오른쪽)

FROM 절 -> IN-LINE 뷰

Chen보다 많은 급여를 받는 사원의 이름과 급여

SELECT LAST_NAME, SALARY

FROM EMP

WHERE SALARY > ( SELECT SALARY FROM EMP WHERE LAST_NAME='Chen');

부서가 101번 사원과 같고 급여가 141번 사원보다 많은 사원의 이름과 급여

SELECT LAST_NAME, SALARY FROM EMP WHERE SALARY > ( SELECT SALARY FROM EMP WHERE EMPLOYEE_ID=141) AND DEPARTMENT_ID = (SELECT DEPARTMENT_ID FROM EMP WHERE EMPLOYEE_ID =101);

--우리 회사에서 가장 월급을 많이 받는 사원의 이름과 급여

SELECT LAST_NAME, SALARY FROM EMP WHERE SALARY > ( SELECT MAX(SALARY) FROM EMP);

*******

==> 오류발생 : single-row subquery returns more than one row

## 단일행 비교연산자

-- >, <, >=, <=, !=, <>

## 다중행 비교연산자

-- in, any, all

-- in, 단일행 비교연산자 any, 단일행 비교연산자 all

-- >= any, > any, <= all, < all, in

## any

-- 서브쿼리의 리턴값 중 아무것 하나하고 만족

##all

-- 서브쿼리의 리턴값 모두하고 만족

-- 부서별 최고급여와 같은 급여를 받는 사원의 이름과 급여

SELECT LAST_NAME, SALARY

FROM EMP

WHERE SALARY IN (SELECT MAX(SALARY)

FROM EMP

GROUP BY DEPARTMENT_ID);

--IT PROG 직군의 어떤 사원보다도 급여를 많이 받는 사원

SELECT LAST_NAME, SALARY

FROM EMP

WHERE SALARY > ALL (SELECT SALARY

FROM EMP

WHERE JOB_ID='IT PROG');

##TOP-N 알고리즘

-- FROM 절에 사용되는 서브쿼리

-- INLINE VIEW

-- 인라인 뷰 : SQL 실행시 잠깐 생성되었다가 사라지는 임시 테이블

-- ROWNUM : 의사컬럼, 없지만 늘 사용가능한 컬럼, 줄번호

SELECT LAST_NAME, SALARY

FROM EMP

WHERE SALARY>=10000;

-- 급여를 많이 받는 10명의 명단

SELECT LAST_NAME, SALARY

FROM (SELECT LAST_NAME, SALARY)

FROM EMP

ORDER BY SALARY DESC);

WHERE ROWNUM <=10;

-- 급여를 적게 받는 10명의 명단

SELECT LAST_NAME, SALARY

FROM (SELECT LAST_NAME, SALARY)

FROM EMP

ORDER BY SALARY);

WHERE ROWNUM <=10;

상품(상품번호, 상품명, 상품가격...)

구매(구매번호, 구매자, 상품번호, 구매갯수, 구매금액, 구매날짜)

1. 2020년 하반기 1000개 이상 팔린 상품명과 총판매갯수

7월 1일 ~ 12월 31일

SELECT 상품번호, SUM(구매갯수) AS 총판매갯수

FROM 구매

WHERE 구매날짜 BETWEEN '2020-07-01' AND '2020-12-31'

GOURP BY 상품번호

HAVING SUM(구매갯수) >= 1000

ORDER BY 2DESC

SELECT  상품번호, SUM(구매갯수) AS 총판매갯수

FROM (SELECT 상품번호, SUM(구매갯수) AS 총판매갯수

FROM 구매

WHERE 구매날짜 BETWEEN '2020-07-01' AND '2020-12-31'

GROUP BY 상품번호

ORDER BY 2DESC)

ROWNUM <= 10;

2. 2020년 베스트상품 10개 검색 (상품명, 총판매갯수)

DML의 정의

DML문은 다음과 같은 상황에 실행된다.

테이블에 새로운 행을 입력할 경우(INSERT)

테이블에 존재하는 행의 내용을 변경할 경우(UPDATE)

테이블에 존재하는 행을 삭제할 경우(DELETE)

DML의 종류

INSERT

테이블에 새로운 행을 입력할 때 사용

## DML

- 데이터 조작어

- 생성되어 있는 테이블 자료를 입력, 수정, 삭제

- CRUD : 추가, 조회, 변경, 삭제

- 로그가 남는다 : 작업내용 로그를 기반으로 취소할 수 있다.

ST_DEPT (DEPT_NO, DEPT_NAME)

ST(S_NO, NAME, BIRTH, DEPT_NO)

CREATE TABLE ST_DEPT (

  DEPT_NO NUMBER(2) PRIMARY KEY,

  DEPT_NAME VARCHAR2(50) NOT NULL

);

CREATE TABLE ST (

  S_NO CHAR(8) PRIMARY KEY, 

  NAME VARCHAR2(30) NOT NULL, 

  BIRTH DATE, 

  DEPT_NO NUMBER(2) REFERENCES ST_DEPT(DEPT_NO)

);

##자료 입력

## ST_DEPT

## INSERT

-- 컬럼리스트의 수와 값리스트의 수가 일치

-- 컬럼의 데이터타입과 값의 데이터 타입 일치

INSERT INTO 테이블명 (컬럼명, ...) VALUES (값1,...);

INSERT INTO ST_DEPT (DEPT_NO, DEPT_NAME) VALUES (10, '국문과');

INSERT INTO ST_DEPT VALUES (20, '영문과');

INSERT INTO ST_DEPT VALUES (30, '수학과');

INSERT INTO ST_DEPT VALUES (40, '멀티과');

INSERT INTO ST (S_NO, NAME, BIRTH, DEPT_NO)

VALUES ('20201234', '이승수', '2020-10-10', 10);

INSERT INTO ST VALUES ('20201111', '최서아', '1993-09-03', 20);

학번, 이름, 학과번호 입력

-- 생일 컬럼 생략하고 입력

-- 생략된 컬럼이 NOT NULL 제약조건이 아니면 생략가능

INSERT INTO ST (S_NO, NAME, DEPT_NO)

VALUES ('20202222', '유비', 30);

INSERT INTO ST VALUES ('20203333', '권정열', NULL, 20);

## 다른 테이블에서 자료를 읽어서 왕창 입력하는 방법

INSERT INTO ST

  SELECT EMPLOYEE_ID, LAST_NAME, HIRE_DATE, DEPARTMENT_ID

  FROM EMP

  WHERE DEPARTMENT_ID IN (10, 20, 30, 40);

SELECT * FROM ST;

## UPDATE 

## row의 특정 컬럼의 값을 변경

1. 지정한 컬럼에 원하는 값을 지정 => 모든 row 값이 변경

2. 조건에 맞는 row에 대해서만 지정한 컬럼에 원하는 값을 지정 => 일반적 사용법

3. WHERE 절 조건 컬럼은 유니크 컬럼으로 하는 것이 좋다

UPDATE ST

SET DEPT_NO = 30;

=> 모든 row 값이 변경

UPDATE ST

SET DEPT_NO = 30

WHERE NAME='권정열';

=> 권정열의 부서를 30으로 변경

UPDATE ST

SET DEPT_NO = 30, BIRTH = '2000-10-10'

WHERE NAME='권정열';

=> 권정열의 부서를 30으로 변경

-- 권정열의 학과번호를 최서아와 같게 변경하시오

UPDATE ST

SET DEPT_NO = (SELECT DEPT_NO FROM ST WHERE NAME='최서아')

WHERE NAME='권정열';

## ROW 삭제

## DELETE

1. 모든 ROW 삭제

2. 조건에 맞는 ROW만 삭제 => ***

DELETE ST;

DELETE FROM ST;

=> 테이블에 INSERT한 모든 자료가 삭제! 주의!

DELETE ST

WHERE BIRTH < '2005-01-01';

## DML 작업이 안되는 경우

## INSERT

ST_DEPT 테이블에 INSERT 작업 => OK

ST 테이블에 INSERT 작업 => 항상 되는 것은 아니다

INSERT INTO ST VALUES ('20203333', '수지', '2000-20-20', 77);

=> violated - parent key not found 오류 발생!

## UPDATE

ST_DEPT 테이블에 UPDATE 작업 => 항상 되는 것은 아니다

UPDATE ST_DEPT

SET DEPT_NO = 35

WHERE DEPT_NAME = '수학과';

=> violated - child record found 오류 발생!

=> 자식테이블 (ST) DEPT_NO 컬럼에서 30 없앤다.

-> ST_DEPT_DEPT_NO_30 -> 35 수정

-> ST_DEPT DEPT_NO NULL -> 35

UPDATE ST

SET DEPT_NO = NULL

WHERE DEPT_NO = 30;

UPDATE ST

SET DEPT_NO = 35

WEHRE DEPT_NO = 30;

UPDATE ST

SET DEPT_NO = 35

WEHRE DEPT_NO IS NULL;

## DELETE

ST_DEPT 테이블에 DELETE 작업 => 항상 되는 것은 아니다

DELETE ST_DEPT

WHERE DEPT_NO = 35;

Chapter7. 트랜잭션 제어 및 사용자 관리

## TCL

-- 트랜잭션 : 업무처리를 위한 최소 업무 단위 :

-- 예시 : 이체, 가입, 탈퇴 ...

a 통장 인출 ==> b 통장 입금

a 업데이트 ==> b 업데이트

=> 두 개의 DML문 => 같이 실행되어야 한다

## 트랜잭션의 특징

1. 원자성

2. 일관성 : 트랜잭션이 수행을 끝내면 일관성 유지

3. 고립성 : lock 정책 - row lock 기본

4. 영속성 : 

## 트랜잭션 연산

-- commit ; 

-- rollback ;

## 트랜잭션의 시작

-- 로그인

-- commit, rollback 연산 이후 새로운 트랜잭션 시작

-- DDL, DCL 이후 새로운 트랜잭션 시작

## 트랜잭션의 종료

-- commit, rollback 연산 이후

-- DDL, DCL 전에 트랜잭션 종료

-- 로그아웃

-- 시스템에러

CREATE TABLE