[DB] 3. SQL 기초(2) - 부속질의, 데이터 정의어, 데이터 조작어

1) 부속질의(서브쿼리)

: SELECT 문 안에 또 다른 SELECT 문을 포함하는 질의

ex) 
SELECT (
	-- 부속 질의문
)

➜ 안쪽 부속 질의문 먼저 수행하고, 해당 결과를 이용해 상위 질의문 수행

 

연산자

 

연산자	설명
IN / NOT IN	부속 질의문 결과 값 중 일치하는 것 있으면/없으면 조건 참
EXISTS / NOT EXISTS	부속 질의문 결과 값 중 하나라도 존재하면/하나도 존재하지 않으면 조건 참
ALL / ANY, SOME	부속 질의문 결과 값 중 모두/하나라도 비교한 결과가 참 -> 조건 참

 

/* === 부속 질의문 예제 ==*/

-- 가장 비싼 도서의 이름을 나타내세요.

SELECT bookname
FROM Book
WHERE price = (SELECT MAX(price) FROM Book);

-- 대한미디어에서 출판한 도서를 구매한 고객의 이름을 나타내세요.

SELECT name
FROM Customer
WHERE custid IN(SELECT custid
		FROM Orders
                SHERE bookid IN(SELECT bookid
                		FROM Book
                                WHERE publisher='대한미디어'));

부속 질의문은 조인으로 대체 사용하는 것을 권장한다.

 

상관(연결) 부속질의

 

: 부속질의 간에 상하 관계 존재,
  상위 쿼리의 특정 행 값이 하위 쿼리 조건에 사용된다.
  일반적인 부속질의와 달리 행 단위로 반복 실행!

-- 출판사별로 출판사의 평균 도서 가격보다 비싼 도서를 구하세요.

SELECT b1.bookname
FROM Book b1
WHERE b1.price > (SELECT avg(b2.price)
	  	  FROM Book b2
     	 	  WHERE b2.publisher=b1.publisher);

 

➜  조인을 사용하면?

SELECT b1.bookname
FROM Book b1
JOIN (
	SELECT publisher, avg(price) AS avg_price
    	FROM Book
    	GROUP BY publisher
) b2 ON b2.publisher = b1.publisher
WHERE b1.price > b2.avg_price;

 

집합 연산

 

• 합집합: UNION (중복x) / UNION ALL (중복 포함)

-- 대한민국에 거주하는 고객의 이름과 도서를 주문한 고객의 이름을 나타내세요.

SELECT name
FROM Customer
WHERE address LIKE '대한민국%'
UNION
SELECT name
FROM Customer
WHERE custid IN (SELECT custid FROM Orders);

 

• 차집합/교집합: MINUS / INTERSECT -> NOT IN, IN (MySQL은 대체 사용한다.)

-- 대한민국에 거주하는 고객 중 도서를 주문한 고객의 이름을 나타내세요.

SELECT name
FROM Customer
WHERE address LIKE '대한민국%' AND
	name IN (SELECT name
        	FROM Customer
                WHERE custid IN (SELECT custid FROM Orders));

 

---

🎐 헷갈리는 부분 정리 🎐

 

 

IN / NOT IN vs. EXISTS / NOT EXISTS

 

	IN / NOT IN	EXISTS / NOT EXISTS
비교대상	값(Value)	행의 존재 여부(Row)
처리 방식	전체 리스트 뽑아 비교	그때그때 하나씩 비교
속도	값이 많아지면 느려짐 = 리스트 생성에 걸리는 시간이 늘어나서	리스트를 미리 만들지 않아 속도 빠름

 

---

2) 데이터 정의어

CREATE TABLE

: 테이블 구성

• 속성/속성에 관한 제약 정의: NULL / NOT NULL / DEFAULT
• 데이터 타입 종류

데이터 타입	설명
INTEGER INT	4바이트 정수형
NUMBER(m, d) DECIMAL(m, d)	전체 자릿수 m, 소수점 이하 자릿수 d를 가진 숫자형
CHAR(n)	문자형 고정길이, 남은 공간은 공백으로
VARCHAR(n)	문자형 가변길이
DATE DATETIME	날짜형, 연도-월-일-시간 ex) %Y-%m-%d

 

• 외래키) 투플 삭제시 처리방법 지정 옵션

ON DELETE _

CASCADE	관련 투플 함께 삭제
SET NULL	관련 투플 외래키 값 -> NULL 변경
RESTRICT(= NO ACTION)	부모행 참조되고 있으면 삭제/수정 불가(기본값)

 

-- CREATE TABLE 예시

CREATE TABLE NewOrders (
    orderid INTEGER,
    custid INTEGER	NOT NULL,
    bookid INTEGER	NOT NULL,
    saleprice INTEGER,
    orderdate DATE,
    PRIMARY KEY(orderid),
    FORIGN KEY(custid) REFERENCES NewCustomer(custid) ON DELETE CASCADE
);

 

ALTER TABLE

: 생성된 테이블의 속성/속성에 관한 제약 변경, 기본키/외래키 변경

-- ALTER TABLE 예시

ALTER TABLE 테이블 이름
    -- 속성 추가/변경
    [[ADD | MODIFY] 속성이름 데이터타입]
    -- 속성 제거
    [DROP COLUMN 속성이름]
    
    -- 기본키 추가
    [ADD PRIMARY KEY(속성이름)]
    -- 기본키 제거
    [DROP PRIMARY KEY]
    
    -- 제약조건 추가/수정
    [[ADD | MODIFY] 속성이름 데이터타입 제약조건]
    -- 제약조건 제거
    [DROP 제약이름]

 

DROP TABLE

: 테이블 삭제(구조+데이터)

 

if 데이터만 삭제 -> DELETE문 사용

 

삭제할 테이블을 참조하는 테이블(외래키)이 있다면 삭제가 수행되지 않는다.

➜ 참조하고 있는 테이블 먼저 삭제 / 외래키 제약 조건 수정이나 삭제

 

-- DROP TABLE 예시

DROP TABLE NewBook;

 

---

3) 데이터 조작어

INSERT 문

: 테이블에 새로운 투플을 삽입

-- INSERT 문 문법

INSERT INTO 테이블이름[(속성리스트)]
	VALUES (값리스트);
    
-- 예시

INSERT INTO Book(bookid, bookname, price, publisher)
	VALUES (13, '스포츠 의학', 90000, '한솔의학서적');

지정된 속성 순서대로 삽입되는 경우, 속성리스트를 생략 가능하다.
But, 클린 코드를 위해 속성리스트를 기재하는 것을 권장한다.

 

+ null값 자리는 비워둔다.

 

UPDATE 문

: 특정 속성값을 수정

-- UPDATE 문 예시
-- Customer 테이블에서 고객번호가 5인 고객의 주소를 '대한민국 부산'으로 변경하세요.

SET_SQL_SAFE_UPDATES=0; /* Safe Updates 옵션 미 해제시 실행 */
UPDATE Customer
SET address='대한민국 부산'
WHERE custid=5;

 

DELETE 문

: 테이블에 있는 기존 투플 삭제

-- DELETE 문 문법

DELETE FROM 테이블이름
[WHERE 검색조건];

-- 예시

DELETE FROM Book
WHERE bookid=11;

 

 

[목차여기]