004 - SQL 기초

01. SQL 개요

DBMS는 SQL 문을 해석하고 프로그램으로 변환하여 실행한 후 결과 출력.

 

• SQL(Structured Query Language)은 1970년대 후반 IBM이 SEQUEL(Structured English QUEry Language)이라는 이름으로 개발한 관계형 데이터베이스 표준 언어로 승인.
• SQL은 데이터 부속어(data sublanguage)라고 한다.
  - DB의 Data와 메타 데이터를 생성하고 처리하는 문법만 갖고 있기 때문.
• SQL은 DBMS에 직접 입력, 클라이언트/서버 응용 프로그램에 삽입하여 사용 가능.
• HTML 웹 페이지 문서에 삽입, 다른 개발 도구에서 직접 실행이 가능.

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

 

SQL은 기능에 따라 나뉜다.

• 데이터 정의어(DDL) : 테이블이나 관계의 구조를 생성
  - CREATE, ALTER, DROP 문 등
• 데이터 조작어(DML) : 테이블에 데이터를 검색, 삽입, 수정, 삭제
  - SELECT, INSERT, DELETE, UPDATE 문 등
  - SELECT문은 특별히 질의어 (query)라고 한다.
• 데이터 제어어(DCL) : 데이터의 사용 권한을 관리하는데 사용
  - GRANT, REVOKE문 등

EX)SECECT문의 문장 프레임워크

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

02. 데이터 조작어 - 검색

SQL의 SELECT 문은 데이터를 검색하는 기본 문장으로, 특별히 질의어(query)라고 한다.

 

SELECT문의 기본 문법.

SELECT

    [ALL | DISINCT]

    [테이블이름.]{* | 속성이름(들) [[AS] 속성이름 별칭]}

[FROM

    {테이블이름 [ AS 테이블이름별칭]}

    [ INNER JOIN | LEFT [OUTER] JOIN | RIGHT [OUTER] JOIN

    {테이블이름 [ON 검색조건]}

    | FULL [OUTER] JOIN {테이블이름}]]

[WHERE 검색조건(들)]

[GROUP BY {속성이름, [..., n]}]

[HAVING 검색조건(들)]

[질의 UNION 질의 | 질의 UNION ALL 질의]

[ORDER BY 속성이름 [ASC|DESC], [ ..., n]}]

----------------------------------------------------

[] : 대괄호 안의 SQL 예약어들은 선택적으로 사용.

{} : 중괄호 안의 SQL 예약어들은 필수적으로 사용.

| : 선택 가능한 문법들 중 한개를 사용.

 

[WHERE 문]

 

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

 

실습 

https://github.com/ainochi-kor/HowToOracle/tree/master/HanbitAcademy_Oracles

ainochi-kor/HowToOracle

user : CreateUserMadang_bySystem.sql

dataset : madang_dataset.sql

/* SELECT문 실습 */

--모든 도서의 이름과 가격을 검색하시오.
SELECT bookname, price
FROM book;

--모든 도서의 도서번호, 도서이름, 출판사, 가격을 검색하시오
SELECT bookid, bookname, publisher, price
FROM book;

SELECT * --전체 출력
FROM book;

--도서 테이블에 있는 모든 출판사를 검색하시오.
SELECT publisher
FROM book;

SELECT DISTINCT publisher --중복 제거
FROM book;

 

/* WHERE 조건 실습 */

와일드 문자의 종류

와일드 문자	의미	사용 예
+	문자열 연결	'골프 ' + '바이블' : '골프 바이블'
%	0개 이상의 문자열과 일치	'%축구%' : 축구를 포함하는 문자열
[ ]	1개의 문자와 일치	'[0-5]%' : 0-5 사이 숫자로 시작하는 문자열
[^]	1개의 문자와 불일치	'[^0-5]%' : 0-5 사이 숫자로
_	특정 위치의 1개의 문자와 일치	'_구%' : 두 번째 위치에서 '구'가 들어가는 문자열

 

--가격이 20,000원 미만인 도서를 검색하시오.
SELECT *
FROM BOOK
WHERE price < 20000;

--가격이 10,000원 이상 20,000원 이하인 도서를 검색하시오.
SELECT *
FROM Book
WHERE price BETWEEN 10000 AND 20000;

SELECT *
FROM Book
WHERE price >= 10000 AND price <= 20000;

--출판사가 '굿스포츠' 혹은 '대한미디어'인 도서를 검색하시오.
SELECT *
FROM book
WHERE publisher in ('굿스포츠', '대한미디어');

--출판사가 '굿스포츠' 혹은 '대한미디어'인 아닌 도서를 검색하시오.
SELECT *
FROM Book
WHERE publisher not in ('굿스포츠', '대한미디어');

 

--'축구의 역사'를 출간한 출판사를 검색하시오.

SELECT bookname, publisher
FROM Book
WHERE bookname LIKE '축구의 역사';

 

--도서이름에 '축구'가 포함된 출판사를 검색하시오
SELECT bookname, publisher
FROM book
WHERE bookname LIKE '%축구%';

 

--축구에 관한 도서 중 가격이 20,000원 이상인 도서를 검색하시오
SELECT *
FROM Book
WHERE bookname LIKE '%축구%' AND price > 20000;

 

--출판사가 '굿스포츠' 혹은 '대한미디어'인 도서를 검색하시오
SELECT *
FROM book
WHERE publisher = '굿스포츠' or publisher = '대한미디어'

 

--도서를 이름순으로 검색하시오
SELECT *
FROM book
ORDER BY bookname;

 

--도서를 가격순으로 검색하고, 가격이 같으면 이름순으로 검색하시오.
SELECT *
FROM Book
ORDER BY price, bookname;

 

--도서를 가격의 내림차순으로 검색하시오. 만약 가격이 같다면 출판사의 오름차순으로 출력하시오.
SELECT *
FROM Book
ORDER BY price DESC, publisher;

 

/* 집계함수 */

집계 함수	문법	사용예
SUM	SUM([ALL | DISTINCT] 속성이름)	SUM(price)
AVG	AVG([ALL | DISTINCT] 속성이름)	AVG(price)
COUNT	COUNT({[[ALL|DISTINCT] 속성이름] | *})	COUNT(*)
MAX	MAX([ALL | DISTINCT] 속성이름)	MAX(price)
MIN	MIN([ALL | DISTINCT] 속성이름)	MIN(price)

--고객이 주문한 도서의 총 판매액을 구하시오.
SELECT SUM(saleprice)
FROM Orders;

 

--2번 김연아 고객이 주문한 도서의 총 판매액을 구하시오
SELECT SUM(saleprice) as 총매출
FROM Orders
WHERE custid = 2;

 

--마당서점의 도서 판매 건수를 구하시오.
SELECT COUNT(*)
FROM Orders;

 

/* GROUP BY */

문법	주의사항
GROUP BY <속성>	GROUP BY로 튜플을 그룹으로 묶은 후 SELECT 절에는 GROUP BY절에서 사용한 <속성>과 집계 함수만 나올 수 있다. [맞는 예] SELECT custid, SUM(saleprice) FROM Orders GROUP BY custid; [틀린 예] SELECT bookid, SUM(saleprice) /* SELECT 절에 bookid 속성이 올 수 없다. */ FROM Orders GROUP BY custid;
HAVING <검색조건>	WHERE 절과 HAVING 절이 같이 포함된 SQL 문은 검색조건이 모호해질 수 있다. HAVING 절은 (1)반드시 GROUP BY 절과 같이 작성해야 하고 (2)WHERE 절보다 뒤에 나와야 한다. 그리고 (3) <검색조건>에 SUM, AVG, MAX, MIN, COUNT와 같은 집계 함수가 와야 한다. [맞는 예] SELECT custid, COUNT(*) AS 도서 수량 FROM Orders WHERE saleprice >= 8000 GROUP BY custid HAVING COUNT(*) >= 2; [틀린 예] SELECT custid, COUNT(*) AS 도서수량 FROM Orders HAVING COUNT(*) >= 2 /* 순서가 틀렸다 */ WHERE saleprice >= 8000 GROUP BY custid;

--고객별로 주문한 도서의 총 수량과 총 판매액을 구하시오.
SELECT custid, COUNT(*) AS 도서수량, SUM(saleprice) AS 총액
FROM Orders
GROUP BY custid;

--가격이 8,000원 이상인 도서를 구매한 고객에 대하여 고객별 주문 도서의 총 수량을 구하시오.
-- 단, 두권 이상 구매한 고객만 구하시오.
SELECT custid, COUNT(*) as 도서수량
FROM Orders
WHERE saleprice >= 8000
GROUP BY custid
HAVING Count(*) >= 2;

 

/* 조인 */

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

--고객과 고객의 주문에 관한 데이터를 모두 보이시오.
SELECT *
FROM Customer, Orders
WHERE Customer.custid = Orders.custid;

--고객과 고객의 주문에 관한 데이터를 고객번호순으로 정렬하여 보이시오.
SELECT *
FROM CUSTOMER,ORDERS
WHERE Customer.custid = orders.custid
ORDER BY customer.custid;

 

--고객의 이름과 고객이 주문한 도서의 판매가격을 검색하시오.
SELECT name, saleprice
FROM Customer, Orders
WHERE Customer.custid = Orders.custid

 

--고객별로 주문한 모든 도서의 총 판매액을 구하고, 고객별로 정렬하시오.
SELECT name, SUM(saleprice)
FROM Customer, Orders
WHERE Customer.custid = Orders.custid
GROUP BY name
ORDER BY name;

 

--고객의 이름과 고객이 주문한 도서의 이름을 구하시오.
SELECT Customer.name, Book.bookname
FROM Customer, Book , Orders
WHERE Customer.custid = Orders.custid and Orders.bookid = Book.Bookid;

 

--가격이 20,000원인 도서를 주문한 고객의 이름과 도서의 이름을 구하시오.
SELECT Customer.name, book.bookname
FROM customer, book, orders
WHERE customer.custid = orders.custid and book.bookid = orders.bookid
and book.price = 20000;

 

--도서를 구매하지 않은 고객을 포함하여 고객의 이름과 고객이 주문한 도서의 판매가격을 구하시오.
SELECT c.name , saleprice
FROM customer c LEFT OUTER JOIN orders o On c.custid = o.custid;

 

/* 부속질의 */

-- 가격이 가장 비싼 도서의 이름을 보이시오.
SELECT bookname
FROM book
WHERE price = (SELECT MAX(price) FROM book);

 

--도서를 구매한 적이 있는 고객의 이름을 검색하시오.
SELECT name
FROM Customer
WHERE custid IN (SELECT custid FROM Orders);

 

--대한미디어에서 출판한 도서를 구매한 고객의 이름을 보이시오
SELECT name FROM customer WHERE custid =
    (SELECT custid  FROM Orders  WHERE 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 b1.publisher = b2.publisher);

 

/* 집합 연산 */

-- 도서를 주문하지 않은 고객의 이름을 보이시오.
SELECT name
FROM customer
MINUS
SELECT name
FROM customer
WHERE custid in (SELECT custid FROM Orders);

 

/* EXISTS */

--주문이 있는 고객의 이름과 주소를 보이시오.
SELECT name, address
FROM customer cs
WHERE EXISTS (SELECT * FROM Orders od WHERE cs.custid = od.custid);

 

03. 데이터 정의어

데이터를 저장하려면 먼저 데이터를 저장할 테이블의 구조를 만들어야 한다.

= SQL의 데이터 정의어(DDL,Data Definition Language).

 

[CREATE 문]

 

CREATE TABLE 테이블 이름

    (    {속성이름 데이터타입

            [NULL | NOT NULL | UNIQUE | DEFAULT 기본값 | CHECK 체크조건]

         }

         [PRIMARY KEY 속성이름(들)]

         {[FOREIGN KEY 속성이름 REFERENCES 테이블이름(속성이름)]

                    [ON DELETE {CASCADE | SET NULL}

         }

     }

 

 

문자형 데이터 타입 - CHAR, VARCHAR, VARCHAR2 - CHAR(n)은 n 바이트를 가진 문자형 타입이다.  *문자의 길이가 n보다 작으면 나머지는 공백으로 채워서 n바이트를 만들어 저장. - VARCHAR2(n) 타입은 n바이트를 가진 문자형 타입 *저장되는 문자의 길이만큼 기억장소를 차지하는 가변형. ※VARCHAR2(n) 타입은 VARCHAR2(n) 타입과 같지만 오라클에서는 미래에 다른 용도로 사용하기 위하여 사용자에게 VARCHAR2(n)를 권장

데이터 타입	설명	ANSI SQL 표준 타입
NUMBER(p, s)	실형 p자리 정수 부분/s자리 소수 부분, p와 s를 생략하여 NUMBER라고 쓰면 NUMBER(8,2)로 저장된다.	DECIMAL(p,s) NUMBER[(p,s)] INTEGER, INT SMALLINT
CHAR(n)	문자형 고정 길이, 문자를 저장하고 남은 공간은 공백으로 채운다.	CHARACTER(n) CHAR(n)
VARCHAR2(n)	문자형 가변 길이, 4000바이트까지 저장된다.	CAHRACTER VARYING(n) CHAR VARYING(n)
DATE	날짜형, 연도/월/날/시간을 저장한다.

 

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

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

Drop table NewBook;

CREATE TABLE NewBook(  --기본키 추가.
bookid NUMBER,
bookname VARCHAR2(20),
publisher VARCHAR2(20),
price NUMBER,
PRIMARY KEY (bookid)
);

 

-- 다음과 같은 속성을 가진 NewCustomer 테이블을 생성하시오.

• custid(고객번호) - NUMBER, 기본키
• name(이름) - VARCHAR2(40)
• address(주소) - VARCHAR2(40)
• phone(전화번호) - VARCHAR2(30)

CREATE TABLE NewCustomer(
    custid NUMBER,
    name VARCHAR2(40),
    address VARCHAR2(40),
    phone VARCHAR2(30),
    PRIMARY KEY (custid)
)

 

--다음과 같은 속성을 가진 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
)

 

/* ALTER */

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

 

--NewBook 테이블에 VARCHAR2(13)의 자료형을 가진 isbn 속성을 추가하시오.
ALTER TABLE NewBook ADD isbn VARCHAR2(13);

--NewBook 테이블의 isbn 속성의 데이터 타입을 NUMBER형으로 변경하시오.
ALTER TABLE NewBook MODIFY isbn NUMBER;

--NewBook 테이블의 isbn 속성을 삭제하시오.
ALTER TABLE NewBook DROP COLUMN isbn;

--NewBook 테이블의 bookid 속성에 NOT NULL 제약조건을 적용하시오.
ALTER TABLE NewBook MODIFY bookid NUMBER NOT NULL;

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

 

/* DROP문 */

 

DROP TABLE 테이블 이름

 

--NewBook 테이블을 삭제하시오.
DROP TABLE NewBook;

 

--NewCustomer 테이블을 삭제하시오. 만약 삭제가 거절된다면 원인을 파악하고 관련된 테이블을 같이 삭제하시오
--(NewOrders 테이블이 NewCustomer를 참조하고 있는 상태다)
/*
삭제하려는 테이블의 기본키를 다른 테이블에서 참조하고 있다면 삭제가 거절된다.
NewCustomer 테이블을 삭제하기 위해서는 참조하고 있는 NewOrders 테이블부터 삭제해야한다.
*/
DROP TABLE NewOrders;
DROP TABLE NewCustomer;

 

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

/* INSERT 문 */

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

          VALUES (값리스트);

 

--Book 테이블에서 새로운 도서 '스포츠 의학'을 삽입하시오.

스포츠 의학은 한솔의학서적에서 출간했으며 가격은 90,000원이다.
INSERT INTO Book(bookid, bookname, publisher, price)
    VALUES(11,'스포츠 의학','한솔의학서적',90000);

 

SELECT * FROM BOOK;

 

--Book 테이블에 새로운 도서 '스포츠 의학'을 삽입하시오.

스포츠 의학은 한솔의학서적에서 출간했으며 가격은 미정이다.
INSERT INTO Book(bookid, bookname, publisher)
VALUES (14, '스포츠 의학', '한솔의학서적');

SELECT * FROM Book;

--수입도서 목록(Imported_book)을 Book 테이블에 모두 삽입하시오.
INSERT INTO Book(bookid, bookname, publisher, price)
SELECT bookid, bookname, publisher, price
FROM IMPORTED_BOOK;

 

/* UPDATE 문 */

UPDATE 테이블이름
SET 속성이름1 = 값1 [, 속성이름2 = 값2, ...]

[WHERE <검색조건>];

 

-- Customer 테이블에서 고객번호가 5인 고객의 주소를 '대한민국 부산'으로 변경하시오.
UPDATE Customer SET address = '대한민국 부산' WHERE custid = 5;

SELECT * FROM Customer;

--Customer 테이블에서 박세리 고객의 주소를 김연아 고객의 주소로 변경하시오.
UPDATE Customer
SET address = (SELECT address FROM Customer WHERE name = '김연아')
WHERE name = '박세리';

SELECT * FROM Customer;

 

/* DELETE 문 */

DELETE FROM 테이블이름

[WHERE 검색조건];

 

-- Customer 테이블에서 고객번호가 5인 고객을 삭제하시오.
DELETE FROM Customer
WHERE custid = 5;

SELECT * FROM Customer;

COMMIT 문과 ROLLBACK문

INSERT, DELETE, UPDATE 문의 결과는 최종적으로 COMMIT문을 만나지 않으면 실제로 데이터베이스에 반영되지 않는다. *DELETE 이후 이때, 일시적으로 반영된 데이터를 복원 시키려면 ROLLBACK 명령어를 사용한다. *COMMIT명령어로 데이터베이스 반영시킨다. 이후 ROLLBACK 작업을 수행해도 삭제된 데이터는 복원이 불가능하다.

 

#요약

1. SQL 
   SQL(Structured Query Language)은 1970년대 후반 IBM이 SEQUEL(Structured English QUEry Language)이라는 이름으로 개발한 관계형 데이터베이스 언어다.
2. 데이터 정의어(DDL)
   테이블이나 관계의 구조를 생성하는데 사용하며 CREATE, ALTER, DROP 문 등이 있다.
3. 데이터 조작어(DML)
   테이블에 데이터를 검색, 삽입, 삭제하는데 사용하며 SELECT, INSERT, DELETE, UPDATE문 등이 있다.
   여기서 SELECT문은 데이터를 조회하는 명령어라 하여 특별히 질의어(query)라고 한다.
4. WHERE 조건
   WHERE 절 다음에 나올 수 있는 조건으로 사용할 수 있는 술어(predicate)는 비교, 범위, 집합, 패턴, NULL 등이 있다.
5. 집계 함수
   테이블의 각 열에 대해 계산을 하는 함수로 SUM, AVG, MIN, MAX, COUNT의 다섯 가지가 있다.
6. GROUP BY
   속성의 공통 값에 따라 그룹을 만드는데 사용하는 명령.
7. HAVING
   GROUP BY절의 결과를 나타내는 그룹을 제한하는 역할.
8. 조인
   한 테이블의 행을 다른 테이블의 행에 연결함으로써 두 개 이상의 테이블을 결합하는 연산이다.
9. 동등조인(내부조인)
   동등조건에 의하여 테이블을 조인하는 것을 동등조인(equi join)이라고 한다. 조인이라고 하면 대부분 동등조인을 말한다. 동등조인은 내부조인(inner join)이라고도 한다.
10. SELECT 문의 WHERE 절에 또 다른 테이블 결과를 이용하기 위해 다시 SELECT문을 괄호로 묶는 것을 부속질의(subquery)라고 한다. 부속질의는 질의가 중첩되어 있다는 의미에서 중첩질의(nested query)라고도 한다.
11. 상관 부속질의
    상관 부속질의(correlated subquery)는 상위 부속질의와 하위 부속질의가 독립적이지 않고 상위 부속질의의 튜플을 이용하여 하위 부속질의를 계산하는 질의를 말한다.
12. 튜플 변수
    FROM절의 테이블 이름 뒤에 테이블의 다른 이름을 붙여주는 것을 튜플 변수라고 한다.
13. 집합 연산
    SQL에서 집합연산은 합집합을 UNION, 차집합을 MINUS, 교집합을 INTERSECT로 나뉜다.
14. EXISTS
    EXISTS는 부속질의문의 어떤 행이 조건에 만족하면 참이다. 반면 NOT EXISTS는 부속질의문의 모든 행이 조건에 만족하지 않을 때만 참이다. 
15. CREATE
    테이블을 구성하고, 속성과 속성에 관한 제약을 정의하며, 기본키 및 외래키를 정의한다.
16. ALTER
    생성된 테이블의 속성과 속성에 관한 제약 그리고 기본키 및 외래키를 변경한다.
17. DROP
    테이블의 구조와 데이터를 삭제한다.
18. INSERT
    테이블에 새로운 튜플을 삽입한다.
19. UPDATE
    기존 튜플에 있는 특정 속성 값을 수정한다.
20. DLELTE
    테이블에 있는 기존 튜플을 삭제한다.