MySQL로 배우는 데이터베이스 개론과 실습 Ch03 데이터베이스 프로그래머 요약

MySQL 로 배우는 데이터베이스 개론과 실습 (- 박우창, 남송휘, 이현룡 지음) 교재의 Chapter 별 요약입니다.

양이 많아서 요약과 연습문제를 나눴습니다.

CH03 데이터베이스 프로그래머

MYSQL

MySQL 은 세계에서 가장 많이 쓰이는 오픈소스의 관계형 데이터베이스 관리 시스템(RDBMS)임.

스웨덴 MySQL AB 사에서 만들어지고 썬 마이크로시스템즈에 10억 달러에 인수되고 썬 마이크로시스템즈가 오라클에 인수되며 같이 넘어갔다.

SQL

SQL(Structured Query Language)은 1970년대 후반 IBM이 SEQUEL(Structured English QUEry Language) 라는 이름으로 개발한 관계형 데이터베이스 언어이다.

데이터 정의어(DLL: Data Definition Language)

테이블이나 관계의 구조를 생성하는데 사용된다. CREATE, ALTER, DROP 문 등이 있다.

데이터 조작어(DML: Data Manipulation Language)

테이블에 데이터를 검색, 삽입, 수정, 삭제할 때 사용한다. SELECT, INSERT, DELETE, UPDATE 문 등이 있다. 

여기서 SELECT 문은 데이터를 조회하는 명령어라 하여 특별히 질의어라고 부른다.

WHERE 조건

WHERE 절 다음에 올 수 있는 조건으로 사용할 수 있는 술어(predicate)는 비교, 범위, 집합, 패턴, NULL 등이 있다.

집계 함수(Aggregate Function)

테이블의 각 열에 대해 계산을 하는 함수로 SUM, AVG, MIN, MAX, COUNT 의 다섯가지가 있다.

GROUP BY

속성의 공통 값에 따라 그룹을 만드는데 사용하는 명령이다.

GROUP BY 로 투플을 그룹으로 묶은 후 SELECT 절에는 GROUP BY 에서 사용한 <속성>과 집계함수만 나올 수 있다.

HAVING

GROUP BY 절의 결과 나타나는 그룹을 제한하는 역할을 한다.

HAVING 절은 반드시 GROUP BY 절과 같이 작성해야 하고 WHERE 절보다 뒤에 나와야 한다. 그리고 HAVING 뒤의 <검색조거>에는 집계함수가 나와야 한다.

 

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

SELECT custid.COUNT(*) AS 도서수량
FROM Orders
WHERE saleprice >= 8000
GROUP BY custid
HAVING 도서수량 > 1
ORDER BY custid;

조인

한 테이블의 행을 다른 테이블의 행에 연결함으로써 두 개 이상의 테이블을 결합하는 언어.

명령	문법	설명
일반적인 조인	SELECT <속성들> FROM     테이블 1, 테이블 2 WHERE  <조인조건> AND <검색조건>	SQL 문에서는 주로 동등 조인을 사용한다. 두 가지 문법 중 하나를 사용할 수 있다.
	SELECT <속성들> FROM    테이블1 INNER JOIN 테이블2 ON <조인조건> WHERE  <검색조건>
외부 조인 (outer join)	SELECT  <속성들> FROM     테이블 1 {LEFT | RIGHT | FULL [OUTER] JOIN}                 테이블 2 ON <조인조건> WHERE   <검색 조건>	외부조인은 FROM 절에 조인 종류를 적고 ON 을 이용하여 조인조건을 명시한다.

 

고객의 이름과 고객이 주문한 도서의 판매가격을 검색하시오

SELECT	name, saleprice
FROM	Customer, Orders
WHERE	Customer.custid = Orders.custid;

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

 

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

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

동등 조인(equi join): 

동등 조건에 의하여 테이블을 조인하는 것을 동등 조인이라고 한다. 보통 조인이라고 하면 대부분 동증 조인이다. 내부 조인이라고 하기도 한다.

 

부속질의(subquery)

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

가장 비싼 도서의 이름을 보이시오.

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

상관 부속질의(correlated subquery)

상관 부속질의는 상위 부속 질의와 하위 부속질의가 독립적이지 않고 상위 부속 질의의 투플을 이용하여 하위 부속질의를 계산하는 질의를 말한다.

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

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

투플 변수(tuple variable)

FROM 절의 테이블 이름 뒤에 테이블의 다른 이름을 붙여주는 것을 투플 변수라고 한다. 

집합 연산

SQL 에서 집합 연산은 합집합을 UNION 으로 나타낸다. MySQL 은 MINUS, INTERSECT 연산자가 없다. 

MINUS 연산자는 NOT IN, NOT EXISTS, LEFT OUTER JOIN 으로 대신 나타내고 INTERSECT 연산은 JOIN 또는 IN 연산으로 대신할 수 있다.

대한민국에서 거주하는 고객의 이름과 도서를 주문한 고객의 이름을 보이시오.

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

 

대한민국에서 거주하는 고객의 이름에서 도서를 주문한 고객의 이름을 빼고 보이시오.

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

 

EXISTS

단어의 의미처럼 조건에 맞는 투플이 존재하면 결과에 포함시킨다. EXISTS 는 부속 질의문의 어떤 행이 조건에 만족하면 참이다. 

반면에 NOT EXISTS 는 부속질의문의 모든 행이 조건에 만족하지 않을 때만 참이다.

주문이 있는 고객의 이름과 주소를 보이시오.

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

CREATE

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

CREATE TABLE 테이블 이름
    ( { 속성이름 데이터타입
        [ NULL | NOT NULL | UNIQUE | DEFAULT 기본값 | CHECK 체크조건 ]
      }
        [ PRIMARY KEY 속성이름(들) ]
        [ FOREIGN KEY 속성이름 REFERENCES 테이블이름(속성이름)]
        	[ ON DELETE { CASCADE | SET NULL } ]
    )

문법에서 대문자는 키워드, { } 안의 내용은 여러 개가 반복해서 나올 수 있음, [ ] 은 선택적으로 사용, | 는 1개를 선택, < > 은 해당되는 문법사항이 있음을 나타냅니다.

다음과 같은 속성을 가진 NewOrders 테이블을 생성하시오.
▪️ orderid(주문번호) - INTEGER, 기본키
▪️ custid(고객번호) - INTEGER, NOT NULL 제약 조건, 외래키(NewCustomer.custid, 연쇄 삭제)
▪️ bookid(도서번호) - INTEGER, NOT NULL 제약 조건
▪️ saleprice(판매가격) - INTEGER
▪️ orderdate(판매일자) - DATE

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
);

ALTER

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

ALTER TABLE 테이블이름
        [ADD    [COLUMN] 속성이름 데이터타입]
        [DROP   [COLUMN] 속성이름]
        [ALTER  [COLUMN] 속성이름 데이터타입]
        [MODIFY [COLUMN] 속성이름 [NULL | NOT NULL]
        [ADD    PRIMARY KEY (속성 이름)]
        [[ADD | DROP] 제약이름]

NewBook 테이블의 bookid 속성에 NOT NULL 제약 조건을 적용하시오.

ALTER TABLE NewBook 
	MODIFY bookid INTEGER NOT NULL;

DROP

테이블의 구조와 데이터를 삭제한다.

DROP TABLE 테이블이름

NewCustomer 테이블을 삭제하시오. 만약 삭제가 거절된다면 원인을 파악하고 관련된 테이블을 같이 삭제하시오. (NewOrders 테이블이 NewCustomer 을 참조하고 있는 상태이다.)

DROP TABLE NewOrders;
DROP TABLE NewCustomer;

 

INSERT

테이블에 새로운 투플을 삽입한다.

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

수입도서 목록(Imported_book)을 Book 테이블에 모두 삽입하시오.

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

UPDATE

기존 투플에 있는 특정 속성 값을 수정한다.

UPDATE 테이블이름
SET    속성이름 1 = 값1[, 속성이름2 = 값2, 속성이름3 = 값3 ...]
[WHERE <검색조건>];

MySQL UPDATE 및 DELETE 수행 시 실수를 방지하기 위해서 기본키 속성을 사용해서만 가능하도록 한 안전 옵션이 기본적으로 걸려 있다. 그래서 기본키가 아닌 속성을 WHERE 조건으로 테이블을 업데이트하면 에러가 발생한다.

ERROR Code: 1175, You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

설정에서 safe update 모드를 수행하지 않도록 영구적인 설정을 하거나 SET SQL_SAFE_UPDATES = 0; 코드를 UPDATE 나 DELETE 문 실행 전에 수행해서 일시적으로 설정해주어야 한다.

 

Book 테이블에서 14 번 '스포츠 의학' 의 출판사를 imported_book 테이블의 21번 책의 출판사와 동일하게 변경하시오.

UPDATE   Book
SET      publisher = (SELECT publisher
                      FROM   imported_book
                      WHERE bookid = '21')
WHERE    bookid = '14';

 

DELETE

테이블에 있는 기존 투플을 삭제한다.

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

Book 테이블에서 도서번호가 11인 도서를 삭제하시오.

DELETE  FROM Book
WHERE   bookid = '11';