DB Chapter3 SQL 기초 - SQL 소개(데이터 정의어, 데이터 조작어, 데이터 제어어) / SELECT 문 문법 / 조건 검색(WHERE 절) / 집합(IN, NOT IN) / 패턴(LIKE) / 복합조건 / 검색 결과의 정렬(ORDER BY) / 집계 함수(SUM, AVG, MIN, MAX, COUNT)와 GROUOP BY 검색 / HAVING / 두 개 이상 테이블에서 SQL 질의 / 외부조인(left, right, full outer join) / 부속질의 / 집합연산(합집합, 차집합, 교집합) / EXISTS / 데이터 정의어(CREATE, ALTER, DROP 문) / 데이터 조작어(INSERT, UPDATE, DELETE 문) / COMMIT 문과 ROLLACK 문

02 Jul 2021 • DataBase

Part2. 데이터베이스 프로그래머

Chapter 03. SQL 기초

SQL 소개

DBMS에 원하는 내용을 비교적 쉽게 알려주고 결과를 얻는 데 사용하는 데이터베이스 전용 언어가 SQL이다. DBMS는 SQL문을 해석하고 프로그램으로 변환하여 실행 한 후 결과를 알려준다. SQL은 자바나 C같은 완전한 프로그래밍 언어는 아니다. 대신 데이터 부속어라고 부르는데, 그 이유는 데이터베이스의 데이터와 메타 데이터를 생성하고 처리하는 문법만 갖고 있기 때문이다.

SQL과 일반 프로그래밍 언어의 차이점

구분 SQL 일반 프로그래밍 언어
용도 데이터베이스에서 데이터를 추출하여 문제 해결 모든 문제 해결
입출력 입력은 테이블, 출력도 테이블 모든 형태의 입출력 가능
번역 DBMS 컴파일러
문법 SELECT *
FROM Bok;		 int main()
{…}

SQL은 기능에 따라 3가지로 나뉜다.

  • 데이터 정의어 DDL : 테이블이나 관계의 구조를 생성하는데 사용하며 CREATE(만들고), ALTER(수정), DROP(제거)문 등이 있다.
  • 데이터 조작어 DML : 테이블에 데이터를 검색,삽입,수정,삭제하는데 사용하며 SELECT(검색), INSERT(테이블이 아닌 투플을 삽입), DELETE(테이블이 아닌 투플을 수정), UPDATE(테이블이 아닌 투플을 삭제) 문 등이 있다. 여기서 SELECT문은 특별히 질의어라고 부른다.
  • 데이터 제어어 DCL : 데이터의 사용 권한을 관리하는데 사용하며 GRANT, REVOKE문 등이 있다.

SQL문을 이해하기 위해 SELECT 문의 문장 구조를 살펴보면 다음과 같다.

SELECT : 질의 결과 추출되는 속성 리스트를 열거한다.
FROM : 질의에 어느 테이블이 사용되는지 열거한다.
WHERE : 질의의 조건을 작성한다.

예를들어 Customer 테이블에서 김연아 고객의 전화번호를 찾으시오라는 질의를 표현하면 다음과 같다.

SELECT phone
FROM Customer
WHERE name=’김연아’;

SQL문은 실행 순서가 없는 비절차적인 언어이다. 즉 찾는 데이터만 기술하고 어떻게 찾는지는 기술하지 않는다.

SELECT 문 문법

SQL의 SELECT문은 데이털르 검색하는 기본 문장으로, 특별히 질의어(query)라고 부른다. SELECT문의 기본 문법은 다음과 같다.

SELECT [ALL | DISTINCT] 속성이름(들)
FROM 테이블 이름(들)
[WHERE 검색조건(들)]
[GROUP BY 속성이름]
[HAVING 검색조건(들)]
[ORDER BY 속성이름 [ASC | DESC]]

[] : 대괄호 안의 SQL 예약어들은 선택적으로 사용한다(옵션)
| : 선택 가능한 문법들 중 한 개를 사용할 수 있다.

SELECT, FROM은 기본! 무조건 써야 한다.

질의 3-3) 도서 테이블에 있는 모든 출판사를 검색하시오.

SELECT publisher
FROM Book;

중복을 제거하고 싶으면 DISTICNT 키워드 사용

SELECT DISTINCT publisher
FROM Book;

조건 검색

조건에 맞는 검색을 할 때는 WHERE 절을 사용한다.

WHERE 절에 조건으로 사용할 수 있는 술어

술어 연산자 사용 예
비교 =, <>, <, <=, >, >= price < 20000
범위 BETWEEN price BETWEEN 10000 AND 20000
집합 IN, NOT IN price IN(10000,20000,30000)
패턴 LIKE bookname LIKE’축구의 역사’
NULL IS NULL, IN NOT NULL price IS NULL
복합조건 AND, OR, NOT (price<20000>) AND (bookname LIKE ‘축구의 역사’)
집합

WHERE 절에 두 개 이상의 값을 비교하려면 IN 연산자와 NOT IN 연산자를 사용하면 편리하다. 다음은 IN 연산자를 사용하여 출판사가 ‘굿스포츠’ 혹은 ‘대한미디어’인 도서 검색을 하는 SQL문이다. 문자 값들을 괄호 안에 포함시켜 비교하며, publisher 값이 이 중 하나와 같으면 선택된다.

질의 3-6) 출판사가 ‘굿스포츠’ 혹은 ‘대한미디어’인 도서를 검색하시오.

SELECT *
FROM Book
WHERE publisher IN('굿스포츠', '대한미디어');

출판사가 ‘굿스포츠’ 혹은 ‘대한미디어’가 아닌 출판사를 검색하는 문은 다음과 같다.

SELECT *
FROM Book
WHERE publisher NOT IN('굿스포츠', '대한미디어');

패턴

문자열의 패턴을 비교할 때는 LIKE 연산자를 사용한다. 만약 찾는 속성이 텍스트 혹은 날짜 데이터를 포함하면 반드시 영문 작은 따옴표(‘‘)로 둘러싸야 한다. 한글 작은따옴표(“”)는 오류난다.

일반 프로그래밍 언어에서 문자열은 “” 큰따옴표를 사용하지만 SQL언어에서는 ‘’ 작은 따옴표를 사용한다. 그 이유는 SQL문 자체가 문자열로 인용이 되어 다른 프로그래밍 언어에 삽입 될 때 혼란이 있을 수 가 있기 때문이다.

질의3-7) “축구의 역사”를 출간한 출판사를 검색하시오

SELECT bookname, publisher
FROM Book
WHERE bookname LIKE '축구의 역사';
            //  = 써도 같음

질의3-8) 도서 이름에 “축구”가 포함된 출판사를 검색하시오.

SELECT bookname, publisher
FROM Book
WHERE bookname LIKE '%축구%';

질의3-9) 도서 이름의 왼쪽 두번째 위치에 ‘구’ 라는 문자열을 갖는 도서를 검색하시오.

SELECT *
FROM Book
WHERE bookname LIKE '_구%';
복합조건 검색

질의 3-11) 출판사가 ‘굿스포츠’ 혹은 ‘대한미디어’인 도서를 검색하시오

SELECT *
FROM Book
WHERE publisher = '굿스포츠' OR publisher = '대한미디어';
//                          IN 써도 된다
검색 결과의 정렬

SQL 문의 실행 결과 행의 순서는 각 DBMS에 저장된 위치에 따라 결정된다. 특징을 순서대로 출력하고 싶으면 ORDER BY(기본 오름차순)절을 사용한다.

질의 3-12) 도서를 이름순으로 검색하시오

SELECT *
FROM Book
ORDER BY bookname;

도서를 가격순으로 먼저 정렬 한 후 가격이 같은 도서에 대하여 이름순으로 다시 정렬하고 싶으면 원하는 열 이름을 순서대로 지정한다.

질의 3-13) 도서를 가격순으로 검색하고, 가격이 같으면 이름순으로 검색하시오.

SELECT *
FROM Book
ORDER BY price, bookname;

정렬의 기본은 오름차순이다. 내림차순으로 정렬하려면 열 이름 다음에 DESC 를 사용하면 된다.

질의 3-14) 도서를 가격의 내림차순으로 검색하시오. 만약 가격이 같다면 출판사의 오름차순으로 출력하시오.

SELECT *
FROM Book
ORDER BY price DESC, publisher ASC;
//                             ASC 생략 가능. 기본이 오름차순이기 때문

집계 함수와 GROUOP BY 검색

앞에서는 마당서점의 고객이 필요로 하는 질의를 중심으로 살펴보았다. 이제부터는 ‘운영자’가 필요로 하는 질의를 중심으로 SQL의 기능을 살펴본다.

집계함수

운영자는 전체 도서의 판매액 합계를 알고 싶어 한다. 이를 위해서 SQL의 집계함수를 이용할 수 있다. 집계함수는 테이블의 각 열에 대해 계산을 하는 함수로 SUM, AVG, MIN, MAX, COUNT의 다섯 가지가 있다.

질의 3-15) 고객이 주문한 도서의 총판매액을 구하시오.

SELECT SUM(saleprice)
FROM Orders;

의미있는 열 이름을 출력하고 싶으면 속성이름의 별칭을 지칭하는 AS 키워드를 사용하여 열 이름을 부여한다.

SELECT SUM(saleprice) AS 총매출   //AS 생략 가능
FROM Orders;

집계함수는 WHERE문과 같이 사용하면 더 유용하다.

질의 3-16) 2번 김연아 고객이 주문한 도서의 총판매액을 구하시오.

SELECT SUM(saleprice) AS 총매출
FROM Orders;
WHERE custid=2;

집계함수는 여러 개를 혼합하여 쓸 수 있다.

질의 3-17) 고객이 주문한 도서의 총판매액, 평균값, 최저가, 최고가를 구하시오

SELECT SUM(saleprice) AS Total,
        AVG(saleprice) AS Average,
        MIN(saleprice) AS Minimum,
        MAX(saleprice) AS Maximum
FROM Orders;
COUNT()

집계함수 COUNT는 행의 개수를 센다. COUNT() 의 괄호 안에는 * 혹은 특정 속성의 이름이 사용되며, 해당 속성의 투플의 개수를 세어준다. (NULL 값은 제외). COUNT(DISTINCT publisher)는 중복을 제거한 출판사의 수를 세어준다. (NULL 값은 제외).

GROUP BY

SQL문에서 GROUP BY 절을 사용하면 같은 속성값끼리 그룹을 만들 수 있다. 예로, Orders 테이블을 사용하는 SELECT 문에 GROUP BY custid라고 명시하면, DBMS는 custid가 같은 값끼리 그룹화한다. 결과는 custid가 서로 다른 값들에 대하여 그룹이 만들어진다. 속성의 공통 값에 따라 그룹을 만드는 데 사용하는 명령이다.

질의 3-19) 고객별로 주문한 도서의 총수량과 총판매액을 구하시오.

SELECT custid, COUNT (*) AS 도서수량, SUM(saleprice) AS 총액
FROM Orders
GROUP BY custid;

결과 릴레이션)

CUSTID 도서수량 총액
1 3 39000
2 2 15000
4 2 33000
3 3 31000
HAVING

HAVING 절은 GROUP BY 절의 결과 나타나는 그룹을 제한하는 역할을 한다. 예를 들어 주문 도서가 2권 이상일 경우만 출력하려면 다음과 같이 작성한다. 주의사항: WHERE절과 HAVING절이 같이 포함된 SQL문은 검색조건이 모호해질 수 있다. HAVING절은 반드시 GROUP BY절과 같이 작성해야하고 WHERE절보다 뒤에 나와야 한다. 그리고 검색조건에는 SUM, AVG, MAX, MIN, COUNT와 같은 집계함수가 와야 한다.

질의 3-20) 가격이 8,000원 이상인 도서를 구매한 고객에 대하여 고객별 주문 도서의 총수량을 구하시오. 단, 2권 이상 구매한 고객만 구하시오.

SELECT custid, COUNT (*) AS 도서수량
FROM Orders
WHERE saleprice >= 8000
GROUP BY custid
HAVING count(*) >= 2;

두 개 이상 테이블에서 SQL 질의

조인

한 테이블의 행을 다른 테이블의 행에 연결하여 두 개 이상의 테이블을 결합하는 연산이다. 다음은 Customer 테이블을 Orders 테이블과 조건 없이 연결한 예이다. SQL 문은 다음과 같이 작성한다. 두 테이블을 아무런 조건을 주지 않고 SELECT 시키면 관계대수의 카티션 프로덕트 연산이 된다.

SELECT *
FROM Customer, Orders;

그렇지만 결과는 논리에 맞지 않는다. 필요한 것은 박지성의 custid가 1이므로 Orders 테이블에서 custid가 1인 것만 선택하면 된다.

질의 3-21) 고객과 고객의 주문에 관한 데이터를 모두 보이시오.

SELECT *
FROM Customer, Orders
WHERE Customer.custid=Orders.custid;

여러 개의 테이블을 연결하여 하나의 테이블을 만드는 과정을 조인이라고 한다. 특히 앞의 SQL문처럼 동등 조건에 의하여 테이블을 조인하는 것을 동등조인이라고 한다. 조인이라고 하면 대부분 동등조인을 말한다. 동등조인은 내부조인이라고도 한다.

세 개 이상의 테이블을 조인 할 수도 있다. 기본키와 외래키로 연결한다.

질의 3-25) 고객의 이름과 고객이 주문한 도서의 이름을 구하시오.

SELECT Customer.name, book.bookname
FROM Customer, Orders, Book
WHERE Customer.custid=Orders.custid AND Orders.bookid=Book.bookid;

세 개 이상의 테이블을 조인하는 SQL문에 WHERE 절을 추가하여 원하는 결과만 추출할 수도 있다.

질의 3-26) 가격이 20,000원인 도서를 주문한 고객의 이름과 도서의 이름을 구하시오.

SELECT Customer.name, book.bookname
FROM Customer, Orders, Book
WHERE Customer.custid=Orders.custid AND Orders.bookid=Book.bookid AND Book.price=20000;
외부조인(left, right, full outer join … on)

조인 연산의 특별한 경우로 외부조인이 있다. 질의3-23의 고객의 이름과 고객이 주문한 도서의 가격을 구하는 동등조인의 예에서 도서를 주문하지 않은 고객 박세리는 결과에 포함되지 않는다. 만약 도서를 구매하지 않은 고객 박세리까지 포함하여 고객의 이름과 고객이 주문한 도서의 가격을 구하려면 어떻게 해야 할까? 방법은 외부조인을 사용하면 된다.

질의 3-27) 도서를구매하지 않은 고객을 포함하여 고객의 이름과 고객이 주문한 도서의 판매가격을 구하시오.

SELECT Customer.name, saleprice
FROM Customer LEFT OUTER JOIN Orders
              ON Customer.custid=Orders.custid;

결과 릴레이션)

NAME SALEPRICE
박지성 21000
박지성 20000
박지성 18000
김연아 7000
김연아 8000
장미란 6000
박세리 (null)

tip) 오라클은 +기호로 외부조인을 수행할 수 있다. 주의할 점은 왼쪽 외부조인의 경우 (+) 기호를 조인조건의 오른쪽에, 오른쪽 외부조인인 경우 (+) 기호를 왼쪽에 붙인다는 점이다.

SELECT Customer.name, saleprice
FROM Customer, Orders
WHERE Customer.custid=Orders.custid(+);
부속질의

SELECT 문의 WHERE절에 또 다른 테이블 결과를 이용하기 위해 다시 SELECT 문을 괄호로 묶는 것을 부속질의라고 한다. SQL 문 내에 또다른 SQL 문을 작성해 보자. 부속질의는 질의가 중첩되어 있다는 의미에서 중첩질의라고도 한다.

“가격이 가장 비싼 도서의 이름은 얼마인가?”. 가장 비싼 도서의 가격은 다음과 같이 구할 수 있으며 답은 35,000이다.

SELECT MAX(price)
FROM Book;

만약 가장 비싼 도서의 가격을 알고 있다면 다음과 같이 가격이 35,000원인 도서의 이름을 바로 검색하면 된다.

SELECT bookname
FROM Book
SELECT price=35000;

두 질의를 하나의 질의로 작성할 수 있을까? ㅇㅇ가능. 두번째 질의의 35,000 값 위치에 첫 번째 질의를 대치하면 된다.

질의 3-28) 가장 비싼 도서의 이름을 보이시오.

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

세 개 이상의 중첩된 부속질의도 가능하다.

질의 3-30) ‘대한미디어’에서 출판한 도서를 구매한 고객의 이름을 보이시오.

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

위 문법을 이해하려면 아래에서부터 읽어야 한다.

부속질의 간에는 상하 관계가 있으며, 실행 순서는 하위 부속질의를 먼저 실행하고 그 결과를 이용하여 상위 부속질의를 실행한다. 반면 **상관 부속질의**는 상위 부속질의의 투플을 이용하여 하위 부속질의를 계산한다. 즉 상위, 하위부속질의는 독립적이지 않고 서로 관련을 맺고 있다.

질의 3-31) 출판사별로 출판사의 평균 도서가격보다 비싼 도서를 구하시오.

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

위 SQL문을 보면 상위 부속질의에 사용된 Book 테이블을 b1으로 별칭하고, 하위 부속질의에 사용된 Book 테이블을 b2로 별칭한다. (테이블 이름이 같거나 한 개의 테이블이 두번 사용될 때 혼란을 피하고자 테이블의 별칭을 붙여 사용하는데, 이를 투플변수라고 한다. 투플변수는 FROM절의 테이블 이름 뒤에 테이블의 다른 이름을 표기한다.)

부속질의와 조인은 여러 테이블을 하나의 SQL문에서 다루는 점은 같지만, 차이점이 있다. 부속질의는 SELECT문에 나오는 결과 속성을 FROM절의 테이블에서만 얻을 수 있고, 조인은 조인한 모든 테이블에서 결과 속성을 얻을 수 있다. 조인은 부속질의가 할 수 있는 모든 것을 할 수 있다. 그러나 부속질의를 조인을 이용해서 작성해보면 부속질의만의 편리함을 알 수 있다. 한 개의 테이블에서만 결과를 얻는 여러 테이블 질의는 조인보다 부속질의로 작성하는것이 훨씬 편하다.

집합연산

SQL에서 집합연산은 합집합을 UNION, 차집합을 MINUS, 교집합을 INTERSECT로 나타낸다.

질의 3-32) 도서를 주문하지 않은 고객의 이름을 보이시오.

SELECT name
FROM Customer
MINUS
SELECT name
FROM Customer
WHERE custid IN (SELECT custid FROM Orders);

EXISTS

상관 부속질의문 형식이다. 조건에 맞는 투플이 존재하면 결과에 포함시킨다. 즉 부속질의문의 어떤 행이 조건에 만족하면 참이다. 반면 NOT EXISTS는 부속질의문의 모든 행이 조건에 만족하지 않을때만 참이다.

질의 3-33) 주문이 있는 고객의 이름과 주소를 보이시오.

SELECT name, address
FROM Customer cs
WHERE EXISTS (SELECT *
              FROM Orders od
              WHERE cs.custid=od.custid);

05. 데이터 정의어

데이터를 저장하려면 먼저 데이터를 저장 할 테이블의 구조를 만들어야한다. 데이터정의어는 바로 이 구조를 만드는 명령이다.

CREATE 문

CREATE문은 테이블을 구성하고, 속성과 속성에 관한 제약을 정의하며, 기본키 및 외래키를 정의하는 명령이다.

질의 3-34) 다음과 같은 속성을 가진 NewBook테이블을 생성하시오. 정수형은 NUMBER를 사용하며 문자형은 가변형 문자타입인 VARCHAR2을 사용한다.

  • bookid(도서번호)-NUMBER
  • bookname(도서이름)-VARCHAR2(20)
  • publisher(출판사)-VARCHAR2(20)
  • price(가격)-NUMBER
CREATE TABLE NewBook(
bookid        NUMBER,
bookanme      VARCHAR2(20),
publisher     VARCHAR2(20),
price         NUMBER);

char(n)는 저장되는 문자의 길이가 n보다 작으면 나머지는 공백으로 채워서 n바이트를 만들어 저장한다. varchar2(n)는 마찬가지로 n바이트를 가진 문자형 타입이지만 저장되는 문자의 길이만큼 기억장소를 차지하는 가변형이다.
문자형데이터를 사용할 때 char와 varchar에 저장된 값이 같아도 char는 공백을 채운 문자열이기때문에 동등비교시 실패할 수 있다.

위 예제는 아무 제약사항이 없다. 기본키를 지정하고싶으면 다음 방법이 있다.

CREATE TABLE NewBook(
bookid        NUMBER            PRIMARY KEY,     //이거 추가 또는 맨아래 줄 추가
bookanme      VARCHAR2(20),
publisher     VARCHAR2(20),
price         NUMBER,
PRIMARY KEY   (bookid));      //해당 줄 추가 또는 옆에 프라이머리 키 추가

만약 bookid 속성이 없어서 두 개의 속성이 기본키가 된다면 괄호를 사용하여 복합키를 지정한다.

CREATE TABLE NewBook(
bookid        NUMBER,
bookanme      VARCHAR2(20),
publisher     VARCHAR2(20),
price         NUMBER,
PRIMARY KEY   (bookname, publisher));

위 예제에서 좀 더 복잡한 제약사항을 추가하면 다음과 같다.

bookname은 NULL값을 가질 수 없고, publisher는 같은 값이 있으면 안된다. price에 값이 입력되지 않을 경우 기본값10000을 지정하며, 가격은 최소 1,000원 이상으로 한다.

CREATE TABLE NewBook(
bookid        NUMBER,
bookanme      VARCHAR2(20) NOT NULL,
publisher     VARCHAR2(20) UNIQUE,
price         NUMBER DEFAULT 10000 CHECK(price > 1000),
PRIMARY KEY   (bookname, publisher));

질의 3-36) 다음과 같은 속성을 가진 NewOrders 테이블을 생성하시오.

  • orderid(주문번호)-NUMBER, 기본키
  • custid(고객번호)-NUMBER, NOT NULL 제약조건, 외래키(NewCustomer.custid, 연쇄삭제)
  • bookid(도서번호)-NUMBER, NOT NULL 제약조건
  • saleprice(판매가격)-NUMBER
  • orderdate(판매일자)-DATE
CREATE TABLE NewOrders(
orderid       NUMBER,
custid        NUMBER           NOT NULL,
bookid        NUMBER           NOT NULL,
saleprice     NUMBER,
orderdate     DATE,
PRIMARY KEY   (orderid),
FOREIGN KEY   (custid) REFERENCES NewCustomer(custid) ON DELETE CASCADE);

외래키 제약조건을 명시할 때는 참조되는 테이블의 기본키여야 한다. 외래키 지정 시 ON DELETE 옵션은 참조되는 테이블의 투플이 삭제될 때 취할 수 있는 동작이다. CASCADE는 참조되는 테이블의 투플이 삭제되면 참조하는 해당 테이블의 투플이 연쇄삭제(CASCADE) 된다.

ALTER 문

ALTER 문은 생성된 테이블의 속성과 속성에 관한 제약을 변경하며, 기본키 및 외래키를 변경한다.

ADD는 속성 추가, DROP은 속성 제거, MODIFY는 속성 변경할 때 사용한다. 그리고 ADD<제약이름>, DROP<제약이름>은 제약사항을 추가하거나 삭제할 때 사용한다.

질의 3-37) NewBook 테이블에서 VARCHAR2(13)의 자료형을 가진 isbn 속성을 추가하시오

ALTER TABLE NewBook ADD isbn VARCHAR2(13);

질의 3-38) NewBook 테이블의 isbn 속성의 데이터 타입을 NUMBER형으로 변경하시오.

ALTER TABLE NewBook MODIFY isbn NUMBER;

질의 3-39) NewBook 테이블에서 isbn 속성을 삭제하시오

ALTER TABLE NewBook DROP COLUMN isbn;

질의 3-40) NewBook 테이블에서 bookid속성에 NOT NULL 제약조건을 적용하시오

ALTER TABLE NewBook MODIFY bookid NUMBER NOT NULL;

질의 3-41) NewBook 테이블의 bookid 속성을 기본키로 변경하시오

ALTER TABLE NewBook ADD PRIMARY KEY(bookid);

tip) 기본키는 NOT NULL 속성만 가능하다.

DROP 문

DROP은 테이블을 삭제하는 명령. 테이블의 구조와 데이터를 모두 삭제하므로 사용에 주의해야 한다.

DROP TABLE NewBook;

삭제하려는 테이블의 기본키를 다른 테이블에서 참조하고 있다면, 참조하는 테이블부터 삭제해야 한다.

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

INSERT 문

INSERT문은 테이블에 새로운 투플을 삽입하는 명령이다.

질의 3-45) Book 테이블에 새로운 도서 ‘스포츠 의학’을 삽입하십시오. 스포츠의학은 한솔의학서적에서 출간했으며 가격은 미정이다.

INSERT INTO Book(bookid, bookname, publisher)
      VALUESE(14, '스포츠 의학', '한솔의학서적');

INSERT문은 SELECT문을 사영하여 한꺼번에 여러개의 투플을 삽입할 수 있다.

질의 3-46) 수입도서 목록(Import_book)을 Book테이블에 모두 삽입하십시오.

INSERT INTO Book(bookid, bookname, price, publisher)
        SELECT bookid, bookname, price, publisher
        FROM Imported_book;

UPDATE 문

기존 투플에 있는 특정 속성값을 수정하는 명령이다.

질의 3-47) customer 테이블에서 고객번호가 5인 고객의 주소를 ‘대한민국 부산’으로 변경하시오.

UPDATE Customer
SET address='대한민국 부산'
WHERE custid=5;

다른 테이블의 속성값을 이용할 수도 있다.

질의 3-48) customer 테이블에서 박세리 고객의 주소를 김연아 고객의 주소로 변경하시오

UPDATE Customer
SET address=(SELECT address
              FROM Customer
              WHERE name='김연아')
WHERE name='박세리';

update문에서 여러 속성값을 한꺼번에 수정하는 작업은 가능하나, 잘못 사용하면 위험하다.

DELETE 문

테이블에 있는 기존 투플을 삭제하는 명령이다.

DELETE FROM Customer
WHERE custid=5;

SELECT *FROM Customer;

위 SQL문에서 WHERE절을 빼면 고객 테이블의 모든 투플이 삭제되고 빈 테이블만 남는다.

DELETE FROM Customer;

위 SQL문은 실행되지 않는다. 그 이유는 Orders 테이블에서 Custid.custid 속성을 외래키로 참조하고 있가 때문이다.

COMMIT 문과 ROLLACK 문

이번 절에서 배운 insert, delete, update문의 결과는 최종적으로 commit문을 만나지 않으면 실제로 데이터베이스에 반영되지 않는다. 예를 들어 다음 문장을 실행하면 임시적으로 5번 고객이 삭제된다.

DELETE FROM Customer
WHERE custid=5;

이때 일시적으로 반영된 데이터를 복원시키려면 ROLLBACK 명령어를 사용한다.

ROLLBACK;
SELECT *FROM Customer;

이번에는 다시 삭제 작업을 실행해 보고 COMMIT 명령어로 데이터베이스에 삭제 작업을 반영시켜 본다.

DELETE FROM Customer
WHERE custid=5;
COMMIT;

반영 후에는 ROLLBACK 작업을 수행해도 삭제된 데이터의 복원이 불가능하다.

ROLLBACK;
SELECT * FROM Customer;
 DataBase  DB

Comments

Related Posts