6월 26일_SQL(서브쿼리문, 다중행연산자, DML, DDL)

# 서브쿼리

• SELECT문 안에 다시 SELECT문이 기술된 형태의 쿼리
• 서브쿼리의 결과를 메인 쿼리에서 받아 처리하는 구조, 중첩된 쿼리하고도 함
• 단일 SELECT 문으로 조건식을 만들기에는 조건이 복잡할 때 또는 완전히 다른 테이블에서 데이터 값을 조회하여 메인 쿼리의 조건으로 사용하려 할 때 유용

이미지 참고 : https://thebook.io/006977/0294/

 

이미지 참고 : https://thebook.io/006977/0294/

 

**서브쿼리를 사용할 때 규칙

• 서브쿼리는 괄호(( ))로 묶어서 사용합니다. 메인 쿼리는 괄호로 묶을 필요가 없습니다.
• 메인 쿼리와 서브쿼리를 연결하기 위해 단일 행 연산자나 다중 행 연산자1를 사용합니다.
• 메인 쿼리와 서브쿼리의 연결 형태는 연산자에 따라 의미가 다릅니다.
• 메인 쿼리는 연산자의 왼쪽에 기술하고 서브쿼리는 연산자의 오른쪽에 기술합니다.
• ➊ 서브쿼리에서 ➋ 메인 쿼리의 순서로 SELECT 문이 실행됩니다.
• 서브쿼리의 서브쿼리 형태로 계속 중첩하여 SQL 문을 작성할 수 있습니다.

 

# 다중행 연산자

다중 행 연산자	설명	예
IN	같은 값	IN (10, 20) → 10이나 20이 포함
NOT IN	같은 값이 아님	NOT IN (10, 20) → 10이나 20이 포함되지 않음
EXISTS	값이 있으면 반환	EXISTS (10) → 10이 존재하면 참
ANY	최소한 하나라도 만족하는 것(OR) <, = 등 비교 연산자와 같이 사용	ANY (10, 20) → 10이나 20이 포함
ALL	모두 만족하는 것(AND) <, = 등 비교 연산자와 같이 사용	ALL (10, 20) → 10과 20이 포함

 

SHOW USER;

SELECT *
FROM BUY;

SELECT *
FROM MEMBERS;

SELECT M.MEM_ID, M.MEM_NAME, B.PROD_NAME, B.AMOUNT
FROM MEMBERS M
JOIN BUY B ON M.MEM_ID = B.MEM_ID
WHERE M.MEM_ID = 'MMU'
ORDER BY M.MEM_NAME;

-- 서브쿼리 : SELECT문 안에 있는 SELECT문
-- 기본 쿼리문
SELECT *
FROM EMPLOYEES E
WHERE E.SALARY = 17000;

-- 서브쿼리문으로 작성한 구문
-- 위의 쿼리문과 같은 결과  
SELECT *
FROM EMPLOYEES E
WHERE E.SALARY = 
    (
    SELECT SALARY
    FROM EMPLOYEES
    WHERE LAST_NAME = 'De Haan'
    );

-- 다중행 결과가 존재할 때 단일행 연산자를 사용해 요류 발생
-- 단일 행 서브쿼리가 하나 이상의 행을 전달했습니다.
-- 다중행 서브쿼리를 사용해줘야 오류가 없음
SELECT *
FROM EMPLOYEES E
WHERE E.SALARY = 
    (
    SELECT SALARY
    FROM EMPLOYEES
    WHERE LAST_NAME = 'Taylor'
    );
    
-- 다중행 서브쿼리
SELECT *
FROM EMPLOYEES E
WHERE E.SALARY IN
    (
    SELECT MIN(SALARY) AS 최저급여
    FROM EMPLOYEES
    GROUP BY DEPARTMENT_ID
    )
ORDER BY E.SALARY DESC;

SELECT *
FROM EMPLOYEES E
WHERE (E.JOB_ID, E.SALARY)
    IN
    (
    SELECT JOB_ID, MIN(SALARY) AS 그룹별급여
    FROM EMPLOYEES
    GROUP BY JOB_ID
    )
ORDER BY E.SALARY DESC;

-- 단일행 조건 연습문제

SELECT
    MAX(HEIGHT)
FROM MEMBERS;

SELECT MEM_NAME, HEIGHT
FROM MEMBERS
WHERE HEIGHT =
    (
    SELECT MAX(HEIGHT)
    FROM MEMBERS);

 

---

 

# DML 데이터 조작 명령어

- DML은 트랜잭션을 다루는 명령어 (*데이터를 조작하여 저장하는 일련의 과정을 트랜잭션이라고 함)

• INSERT (삽입)
• UPDATE (갱신)
• DELETE (삭제)

INSERT	새로운 데이터를 데이터베이스에 삽입하는 명령어
UPDATE	데이터베이스의 기존 데이터를 수정하는 명령어
DELETE	데이터베이스에서 데이터를 삭제하는 명령어

 

-- DML 데이터 조작명령어
-- INSERT : 행 삽입하기(데이터 삽입)
SELECT *
FROM DEPARTMENTS;

INSERT INTO
    DEPARTMENTS 
    (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
VALUES
(271, 'HONG-GILDONG', 200, 1700);

-- 저장된 데이터를 지워줌(되돌려줌)
ROLLBACK;

-- 데이터를 저장, 롤백을 해도 안지워짐
COMMIT;

-- 열 이름을 생략해도 데이터 저장가능
INSERT INTO
    DEPARTMENTS 
VALUES
(272, 'GO-GILDONG', 200, 1700);

-- 데이터값에 NULL 값을 허용하면 NULL 값 들어감
INSERT INTO
    DEPARTMENTS 
    (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
VALUES
(273, 'DULLY', '', 1700);

 

-- UPDATE : 행 갱신하기(데이터 수정)
SELECT *
FROM DEPARTMENTS;

-- WHERE절의 조건을 가진 데이터를 SET의 데이터로 바꿔줌
UPDATE DEPARTMENTS
SET MANAGER_ID = 201,
    LOCATION_ID = 1800
WHERE DEPARTMENT_NAME = 'HONG-GILDONG';

UPDATE DEPARTMENTS
SET DEPARTMENT_NAME = 'SON.HM'
WHERE MANAGER_ID = 200;

 

-- DELETE : 행 삭제하기(데이터 제거)
SELECT *
FROM DEPARTMENTS;

DELETE FROM DEPARTMENTS
WHERE DEPARTMENT_NAME = 'DULLY';

DELETE FROM DEPARTMENTS
WHERE DEPARTMENT_ID = 272;

---

# DDL 테이블, 열 조작 명령어

- 테이블과 관련 열을 생성, 변경, 삭제하는 명령어

- 따로 커밋(COMMIT)하지 않아도 DB에 바로 반영되어 기록

- 제약 조건에 의해 적용이 어려울수 있음

 

• CREATE : 새로운 테이블 생성

CREATE TABLE 테이블 이름
    (열 이름1 데이터타입,
    열 이름2 데이터타입(자릿수),
    열 이름3 데이터타입);

 

**테이블과 열의 이름 생성 규칙

• 동일한 사용자가 동일한 이름의 테이블을 중복해서 생성할 수 없음.  또한 테이블 안의 열 이름도 동일한 이름으로 생성할 수 없음
• SELECT, FROM, COUNT와 같은 예약어는 사용할 수 없음
• 테이블 이름은 문자로 시작. (한글과 특수문자도 사용할 수 있지만 시스템을 운영할 때 문제가 발생할 수 있으므로 권장하지 않음)
• 문자 길이는 1~30바이트 이내

-- CREATE : 테이블 생성하기
DROP TABLE SAMPLE_PRODUCT;

CREATE TABLE SAMPLE_PRODUCT
    (PRODUCT_ID NUMBER PRIMARY KEY,
    PRODUCT_NAME VARCHAR2(30),
    MANU_DATE DATE);

INSERT INTO SAMPLE_PRODUCT
VALUES (1, 'TELEVISION', TO_DATE('140101','YYMMDD'));

INSERT INTO SAMPLE_PRODUCT
VALUES (2, 'WASHER', TO_DATE('150101','YYMMDD'));

INSERT INTO SAMPLE_PRODUCT
VALUES (3, 'CLEANER', TO_DATE('160101','YYMMDD'));

COMMIT;

SELECT *
FROM SAMPLE_PRODUCT;

 

• ALTER : 테이블 수정하기

-- 열 추가
ALTER TABLE 테이블 이름
      ADD ( 열 이름 1 데이터 타입,
            열 이름 2 데이터 타입,
             …
      );
      
-- 열 수정      
ALTER TABLE 테이블 이름
      MODIFY ( 열 이름 1 데이터 타입,
               열 이름 2 데이터 타입,
               …
             );
             
-- 열 이름 바꾸기
ALTER TABLE 테이블 이름
RENAME COLUMN 열 이름 1 to 바꾸려는 열 이름 1;

-- 열 삭제하기
ALTER TABLE 테이블 이름
DROP COLUMN 열 이름;

 

• 새로운 열을 추가할 수 있지만 테이블에 있던 기존의 열은 삭제할 수 없음
• 새로 생성되는 열은 위치를 지정할 수 없음(테이블의 마지막에 위치합니다).
• 테이블에 이미 행이 있다면 열을 추가했을 때 새로운 열의 데이터 값은 null로 초기화

-- ALTER : 테이블 수정하기
-- 열 추가
ALTER TABLE SAMPLE_PRODUCT
ADD (FACTORY VARCHAR(10));

 

4열에 데이터가 추가되어 들어감

 

-- 열 수정
ALTER TABLE SAMPLE_PRODUCT
MODIFY (FACTORY CHAR(10));

 

4열 데이터의 데이터 타입이 수정

-- 열 이름 바꾸기
ALTER TABLE SAMPLE_PRODUCT
RENAME COLUMN FACTORY TO FACTORY_NAME;

 

4열의 컬럼명이 변경됨

-- 열 삭제하기
ALTER TABLE SAMPLE_PRODUCT
DROP COLUMN FACTORY_NAME;

 

열이 삭제됨

 

 

• TRUNCATE : 테이블 내용 지우기

TRUNCATE TABLE 테이블 이름;

 

- 내용만 지워지고 테이블의 구조는 그대로 남아있음

-- 테이블의 내용 삭제
TRUNCATE TABLE SAMPLE_PRODUCT;

데이터의 구조(뼈대) 만 남고 그 안의 데이터 내용은 지워짐

 

 

 

• DROP : 테이블 삭제

DROP TABLE 테이블 이름;

 

- 테이블을 완전히 삭제함

-- 테이블 완전 삭제
DROP TABLE SAMPLE_PRODUCT;

 

삭제 후 데이터를 조회하면 존재하지 않는다는 오류 발생 > 즉 테이블이 완전히 지워짐