[DB] SQL 기초2

이현경·2026년 3월 26일
db

Database

목록 보기
5/13

1. 부속질의

부속질의

  • SELECT 문 안에 또 다른 SELECT 문을 포함하는 질의
  • 부속 질의문(서브 질의문): 다른 SELECT 문 안에 들어있는 SELECT 문
    • 괄호로 묶어서 작성
    • ORDER BY 절 사용 불가
    • 실행 순서: 부속 질의문을 먼저 수행하고, 그 결과를 이용해 상위 질의문 수행
  • 단일행 vs 다중행
    • 단일행 부속 질의문
      • 하나의 행을 결과로 반환
      • 비교 연산자 사용 가능
    • 다중 행 부속 질의문

      • 하나 이상의 행을 결과로 반환

      • 다중 행 부속 질의문은 비교 연산자 사용 불가, 아래 연산자 사용

        연산자설명
        IN부속 질의문의 결과 값 중 일치하는 것이 있으면 검색 조건이 참
        NOT IN부속 질의문의 결과 값 중 일치하는 것이 없으면 검색 조건이 참
        EXISTS부속 질의문의 결과 값이 하나라도 존재하면 검색 조건이 참
        NOT EXISTS부속 질의문의 결과 값이 하나도 존재하지 않으면 검색 조건이 참
        ALL부속 질의문의 결과 값 모두와 비교한 결과가 참이면 검색 조건을 만족
        (비교 연산자와 함께 사용)
        ANY 또는 SOME부속 질의문의 결과 값 중 하나라도 비교한 결과가 참이면 검색 조건을 만족
        (비교 연산자와 함께 사용)
  • 질의1) 대한미디어에서 출판한 도서를 구매한 고객의 이름을 나타내세요.
    SELECT name
FROM Customer
WHERE custid IN(SELECT custid
				FROM Orders
				WHERE bookid IN(SELECT bookid
								FROM Book
								WHERE publisher='대한미디어'));

상관(correlated, 연결) 부속질의

  • 부속질의 간에는 상하 관계가 있으며, 상위 부속질의와 하위 부속질의가 독립적이지 않고 서로 관련을 맺고 있음
  • 상위 쿼리와 하위 쿼리는 서로 의존적이며, 상위 쿼리의 특정 행 값이 하위 쿼리 조건에 사용된다.
  • 일반적인 부속질의와 달리 행 단위로 반복 실행된다.
  • 질의2) 출판사별로 출판사의 평균 도서 가격보다 비싼 도서를 구하세요
    SELECT b1.bookname
FROM Book b1
WHERE b1.price > (SELECT avg(b2.price)
			    FROM Book b2
			    WHERE b2.publisher=b1.publisher);
    • 실행과정
      • 외부쿼리 Book b1에서 한 행을 가져온다.
      • 내부 쿼리 실행: publisher가 같은지 확인
      • 외부쿼리의 현재 책 가격과 비교

집합 연산

  • SQL 문의 결과는 테이블로 나타남
  • 테이블 간의 집합 연산을 이용하여 합집합, 차집합, 교집합을 구할 수 있음
  • 질의3) 대한민국에 거주하는 고객의 이름과 도서를 주문한 고객의 이름을 나타내세요
    -- UNION 연산 사용 (UNION ALL은 중복을 포함하여 모든 결과를 구함)
SELECT name
FROM Customer
WHERE address LIKE '대한민국%'
UNION
SELECT name
FROM Customer
WHERE custid IN (SELECT custid FROM Orders);
  • MySQL에는 MINUS, INTERSECT 연산자가 없는 대신 NOT IN, IN을 사용한다.
  • 질의4) 대한민국에 거주하는 고객의 이름과 도서를 주문한 고객의 이름을 나타내세요
    -- 대한민국에 거주하는 고객의 이름에서 도서를 주문한 고객의 이름을 제외하고 나타내세요
SELECT name
FROM Customer
WHERE address LIKE '대한민국%' AND name NOT IN (SELECT name
										FROM Customer
										WHERE custid IN (SELECT custid
													   FROM Orders));

-- 대한민국에 거주하는 고객 중 도서를 주문한 고객의 이름을 나타내세요
SELECT name
FROM Customer
WHERE address LIKE '대한민국%' AND name IN (SELECT name
									FROM Customer
									WHERE custid IN (SELECT custid
												   FROM Orders));
  • EXISTS와 NOT EXISTS
    • 상관 부속질의문 형식으로 부속질의의 결과가 존재하는지 여부를 확인하는 연산자
    • 부속질의 문이 한 행이라도 반환하면 True
    • NOT EXISTS는 반환행이 하나도 존재하지 않으면 True
  • 질의5) 주문이 있는 고객의 이름과 주소를 나타내세요
    SELECT name, address
FROM Customer cs
WHERE EXISTS (SELECT *
			FROM Orders od
			WHERE cs.custid=od.custid);

JOIN 정리

  • 구식 JOIN(FROM ~ WHERE)
    • 논리적 의미: 카티션 프로덕트 후 WHERE로 필터링
    • 현대 DBMS 처리방식: 옵티마이저가 내부적으로 JOIN으로 변환하여 최적화
    • 성능: JOIN과 차이 없음
    • 차이점
      • 가독성과 협업
      • 유지보수 측면에서는 JOIN을 명시적으로 사용하는 것을 권장
      • 구식 문법은 호환성 때문에 남아있음
  • 부속질의
    • 특징: 메인 쿼리 안에서 독립적으로 실행되는 쿼리
    • 최적화: 대부분의 경우 옵티마이저가 JOIN으로 변환 가능하여 성능 차이 없음
    • 사용 이유: 특정 조건을 직관적으로 표현할 때 유용하지만 JOIN 권장
  • 상관질의
    • 특징: 메인 쿼리의 각 행마다 서브쿼리가 반복적으로 실행됨
    • 최적화: 경우에 따라 JOIN으로 최적화되지만 항상 가능한 것은 아님. 최적화가 안 되면 성능 차이 발생 가능
    • 성능: 최적화가 잘 되면 JOIN과 동일, 그렇지 않으면 반복 실행으로 성능 저하 가능
    • 사용 이유: EXISTS, NOT EXISTS 같은 조건을 직관적으로 표현할 때 자주 사용

cf) 실무 권장사항: 성능은 같더라도 협업과 유지보수, 표준 준수 측면에서 명시적 JOIN을 사용하는 것이 가장 바람직하다.




2. 데이터 정의어

2-1. CREATE TABLE 문

  • 테이블을 구성
  • 속성에 관한 제약을 정의 : NULL | NOT NULL | DEFAULT
  • 기본키 및 외래키를 정의하는 명령어
-- 문법
CREATE TABLE 테이블이름
	({속성이름 데이터타입 [NULL|NOT NULL|UNIQUE|DEFAULT 기본값|CHECK 체크조건]}
	[PRIMARY KEY 속성이름(들)]
	[FOREIGN KEY 속성이름 REFERENCES 테이블이름(속성이름)]
		[ON DELETE {CASCADE|SET NULL}]
	);
  • 질의 6) bookname은 NULL 값을 가질 수 없고, publisher에는 같은 값이 있으면 안 된다. price에 값이 입력되지 않을 경우 기본값 10,000을 저장한다. 또 가격은 최소 1,000원 이상으로 한다.
    CREATE TABLE NewBook (
	bookname VARCHAR(20) NOT NULL,
	publisher VARCHAR(20) UNIQUE,
	price INTEGER DEFAULT 10000 CHECK(price >= 1000),
	PRIMARY KEY (bookname, publisher));
  • 외래키 지정시
    • 참조 무결성 제약조건 유지를 위해 참조되는 테이블에서 튜플 삭제 시 처리 방법을 지정하는 옵션

      • ON DELETE CASCADE : 관련 튜플을 함께 삭제함

      • ON DELETE SET NULL : 관련 튜플의 외래키 값을 NULL로 변경함

      • ON DELETE RESTRICT : 부모행이 참조되고 있으면 삭제/수정 불가(기본값)

      • ON DELETE NO ACTION : SQL 표준 키워드, RESTRICT와 동일

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

2-2. ALTER TABLE 문

  • 생성된 테이블의 속성 변경 및 속성에 관한 제약을 변경하며, 기본키 및 외래키를 변경함
-- 문법
ALTER TABLE 테이블이름
	[ADD 속성이름 데이터타입]
	[DROP COLUMN 속성이름]
	[ALTER COLUMN 속성이름 데이터타입]
	[ALTER COLUMN 속성이름 [NULL|NOT NULL]]
	[ADD PRIMARY KEY(속성이름)]
	[[ADD|DROP] 제약이름]
-- NewBook 테이블에 VARCHAR(13)의 자료형으로 isbn 속성을 추가하세요
ALTER TABLE NewBook ADD isbn VARCHAR(13);

-- NewBook 테이블에서 isbn 자료형을 INTEGER로 변경하세요
ALTER TABLE NewBook MODIFY isbn INTEGER;

-- NewBook 테이블의 isbn 속성을 삭제하세요
ALTER TABLE NewBook DROP COLUMN isbn;

-- NewBook 테이블의 bookname 속성에 NOT NULL 제약조건을 적용하세요
ALTER TABLE NewBook MODIFY bookname VARCHAR(20) NOT NULL;

-- NewBook 테이블의 bookid 속성을 기본키로 변경하세요
ALTER TABLE NewBook ADD PRIMARY KEY(bookid);

cf) MODIFY vs ALTER COLUMN

  • SQL 표준: ALTER COLUMN을 사용
  • MySQL & Oracle: MODIFY를 사용

2-3. DROP TABLE 문

  • 테이블을 삭제하는 명령으로, 테이블의 구조와 데이터를 모두 삭제하므로 사용 시 주의
  • 데이터만 삭제하려면 DELETE 문을 사용
  • 삭제할 테이블을 참조하는 테이블이 있다면 삭제가 수행되지 않음
    • 해당 테이블을 참조하고 있는 테이블부터 삭제
    • 관련된 외래키 제약조건을 먼저 삭제
-- 문법
DROP TABLE 테이블이름;
-- NewBook 테이블을 삭제하세요
DROP TABLE NewBook;

-- NewCustomer 테이블을 삭제하세요.
DROP TABLE NewOrders;
DROP TABLE NewCustomer;




3. 데이터 조작어 - 삽입, 수정, 삭제

3-1. INSERT 문

  • 테이블에 새로운 튜플을 삽입하는 명령
  • 문법
    INSERT INTO 테이블이름[(속성리스트)] VALUES (값리스트);
  • INTO 키워드와 함께 튜플을 삽입할 테이블의 이름과 속성의 이름을 나열, 생략가능
    -- 속성 리스트를 생략하면 테이블을 정의할 때 지정한 속성의 순서대로 값이 삽입됨
INSERT INTO Book VALUES (12, '스포츠 의학', '한솔의학서적', 90000);
  • VALUES 키워드와 함께 삽입할 속성 값들을 나열
  • 속성의 순서는 변경 가능하나 INTO 절의 속성 이름과 VALUES 절의 값은 순서대로 일대일 대응되어야 함
    INSERT INTO Book(bookid, bookname, price, publisher)
	VALUES (13, '스포츠 의학', 90000, '한솔의학서적');
  • 질의 7) Book 테이블에 새로운 도서 ‘스포츠 의학’을 삽입하세요. 단, 가격은 미정입니다
    -- 도서 가격은 0이 아닌 NULL 값으로 저장됨
INSERT INTO Book(bookid, bookname, publisher)
	VALUES (12, '스포츠 의학', '한솔의학서적');
  • SELECT 문을 사용하여 작성할 수도 있음
    -- 수입도서테이블(imported_book)에 있는 모든 목록을 Book 테이블에 모두 삽입
INSERT INTO Book(bookid, bookname, price, publisher)
	SELECT bookid, bookname, price, publisher
	FROM Imported_book;

3-2. UPDATE 문

  • 특정 속성값을 수정하는 명령
  • UPDATE 문에서는 다른 테이블의 속성값을 이용할 수도 있음
  • 문법
    -- SET 키워드 다음에 속성값을 어떻게 수정할 것인지를 지정
-- WHERE 절에 제시된 조건을 만족하는 튜플만 속성값을 수정
UPDATE 테이블이름
SET 속성이름1=값1, ...
[WHERE <검색조건>];
    • 주의! : WHERE 절을 생략하면 테이블에 존재하는 모든 튜플을 대상으로 수정
  • 질의 8) Customer 테이블에서 고객번호가 5인 고객의 주소를 ‘대한민국 부산’으로 변경하세요
    UPDATE Customer
SET address='대한민국 부산'
WHERE custid=5;
  • 참고 : UPDATE문이 보안상 막혀있다면 다음 코드 추가
    -- 안전 모드 끄기
SET SQL_SAFE_UPDATES=0
  • 질의 9) Book 테이블에서 14번 ‘스포츠의학’의 출판사를 Imported_book 테이블에 있는 21번 책의 출판사와 동일하게 변경하세요.
    UPDATE Book
SET publisher = (SELECT publisher
			   FROM imported_book
			   WHERE bookid=21)
WHERE bookid=14;

3-3. DELETE 문

  • 테이블에 있는 기존 튜플을 삭제하는 명령
  • 문법
    -- WHERE 절에 제시한 조건을 만족하는 튜플만 삭제
DELETE FROM 테이블이름
[WHERE 검색조건];
    • 주의! : WHERE 절을 생략하면 테이블에 존재하는 모든 튜플을 삭제해 빈 테이블이 됨
  • 질의 10) Book 테이블에서 도서번호가 11인 도서를 삭제하세요
    DELETE FROM Book
WHERE bookid=11;
  • 참고) TRUNCATE : 테이블 안 데이터 삭제
    TRUNCATE TABLE Book;
    • 데이터를 하나씩 지우는 것이 아니라, 통째로 비우는 명령어라 데이터가 아주 많은 테이블을 초기화할 때 유용!
profile
이현경
커피 한 잔의 여유를 아는 품격있는 여자
이전 포스트

[DB] Join SQL 정리

다음 포스트

[DB] SQL 고급1

0개의 댓글