데이터베이스 기초 명령어 1(+예제)

이번 예제는 이 테이블을 기반으로 한다. 

 --Book table

SELECT * FROM book;

DROP TABLE book; --INSERT 실패후 초기화시 사용

CREATE TABLE book(

bookid NUMBER,

bookname VARCHAR2(50),

publisher VARCHAR2(50),

price NUMBER

);

 

--Orders table

SELECT * FROM orders; -- 확인 용도

DROP TABLE orders; -- 초기화 용도

CREATE TABLE Orders(

orderid NUMBER,

custid NUMBER,

bookid NUMBER,

saleprice NUMBER,

orderdate DATE

);

 

--Customer table

SELECT * FROM Customer; -- 확인 용도

DROP TABLE Customer; -- 초기화 용도

CREATE TABLE Customer(

custid NUMBER,

name VARCHAR2(50),

address VARCHAR2(50),

phone VARCHAR2(50)

);

----------------값 삽입-----------------

SELECT * FROM book;

INSERT INTO BOOK VALUES(1, '축구의 역사', '굿스포츠', 7000);

INSERT INTO BOOK VALUES(2, '축구아는 여자', '나무수', 10000);

INSERT INTO BOOK VALUES(3, '축구의 이해', '대한미디어', 22000);

INSERT INTO BOOK VALUES(4, '골프 바이블', '대한미디어', 35000);

INSERT INTO BOOK VALUES(5, '피겨 교본', '굿스포츠', 8000);

INSERT INTO BOOK VALUES(6, '역도 단계별기술', '굿스포츠', 6000);

INSERT INTO BOOK VALUES(7, '야구의 추억', '이상미디어', 20000);

INSERT INTO BOOK VALUES(8, '야구를 부탁해', '이상미디어', 13000);

INSERT INTO BOOK VALUES(9, '올림픽 이야기', '삼성당', 7500);

INSERT INTO BOOK VALUES(10, 'Olympic Champions', 'Pearson', 13000);

 

INSERT INTO orders values(1,1,1,6000,'2014-07-01');--날짜 넣는법

INSERT INTO orders values(2,1,3,21000,'2014-07-03');--날짜 넣는법

INSERT INTO orders values(3,2,5,8000,'2014-07-03');--날짜 넣는법

INSERT INTO orders values(4,3,6,6000,'2014-07-04');--날짜 넣는법

INSERT INTO orders values(5,4,7,20000,'2014-07-05');--날짜 넣는법

INSERT INTO orders values(6,1,2,12000,'2014-07-07');--날짜 넣는법

INSERT INTO orders values(7,4,8,13000,'2014-07-07');--날짜 넣는법

INSERT INTO orders values(8,3,10,12000,'2014-07-08');--날짜 넣는법

INSERT INTO orders values(9,2,10,7000,'2014-07-09');--날짜 넣는법

INSERT INTO orders values(10,3,8,13000,'2014-07-10');--날짜 넣는법

 

INSERT INTO customer values(1, '박지성', '영국 맨체스타', '000-5000-0001');

INSERT INTO customer values(2, '김연아', '대한민국 서울', '000-6000-0001');

INSERT INTO customer values(3, '장미란', '대한민국 강원도', '000-7000-0001');

INSERT INTO customer values(4, '추신수', '미국 클리블랜드', '000-8000-0001');

INSERT INTO customer values(5, '박세리', '대한민국 대전', 'NULL');

SELECT BOOKNAME,PRICE FROM BOOK;

--FROM으로 table을 잘 고르는게 중요하다.

SELECT bookname,PRICE FROM book;

--SELECT 뒤에 어떻게 적을지에 따라서 순번을 바꾸는게 가능하다.

 

!모든 도서의 도서번호, 도서이름, 출판사, 가격을 검색하시오

SELECT bookid, BOOKNAME, publisher, price FROM book; 

SELECT price, PRICE FROM book; --같은 항목도 중복출력으로 여러번 가져올 수 있다.

 

!도서 테이블에 있는 모든 출판사를 검색하시오

SELECT DISTINCT PUBLISHER FROM book;

DISTINCT를 통해서 중복 제거 가능

!가격이 20,000원 미만인 도서를 검색하시오.

SELECT * FROM book WHERE price < 20000;

 

!가격이 10,000원 이상 20,000 이하인 도서를 검색하시오

SELECT * FROM book WHERE PRICE >= 10000 AND PRICE <= 20000;

SELECT * FROM book WHERE PRICE >= 10000 OR PRICE <= 20000;

SELECT * FROM book WHERE price BETWEEN 10000 AND 20000; -- BETWEEN은 이상, 이하를 나타낸다.

 

!가격이 20000원인 도서를 검색하시오

SELECT * FROM BOOK WHERE price = 20000;

데이터 베이스에서는 대입하지 않기 때문에 =가 같다는 표현이다.

 

!출판사가 '굿스포츠' 혹은 '대한미디어'인 도서를 검색하시오. (중요)

SELECT

*

FROM

book

WHERE

PUBLISHER = '굿스포츠'

OR PUBLISHER = '대한미디어';

양이 많으면 들여쓰기를 통해서 가독성을 높인다.

 

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

배열의 값을 생각하면서 ? IN은 포함되어 있느냐 없느냐 확인한다.

 

!출판사가 '굿스포츠' 혹은 '대한미디어'가 아닌 도서를 검색하시오.

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

NOT을 통해서 제외한거 출력 가능

 

<패턴 문자열>

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

SELECT * FROM BOOK WHERE BOOKNAME = '축구의 역사'; 

완벽히 같은지 비교할때는 = 을 사용한다.

SELECT * FROM BOOK WHERE BOOKNAME LIKE '축구의 역사'; 

문자열을 비교 할때는 LIKE를 사용한다.

 

!도서이름에 '축구'가 포함된 출판사를 검색하시오.

SELECT * FROM BOOK WHERE BOOKNAME LIKE '%축구%'; 

%로 끊어서 단위를 설정할 수 있다.

 

SELECT * FROM BOOK WHERE BOOKNAME LIKE '%축구'; -- 무조건 끝이 축구로 끝나야함

SELECT * FROM BOOK WHERE BOOKNAME LIKE '축구%'; -- 무조건 축구로 시작해야함

 

SELECT * FROM BOOK WHERE BOOKNAME LIKE '_축구_'; -- 무조건 첫글자는 있고, 4번째도 글자가 존재하는 상태에서 가운데 축구가 들어있어야 한다.

 

!도서이름의 왼쪽 두 번째 위치에'구'라는 문자열을 갖는 도서를 검색하시오.

SELECT * FROM BOOK WHERE BOOKNAME LIKE '_구%'; -- 무조건 첫번째글자가 있고 두번째가 구

 

!축구에 관한 도서 중 가격이 20,000원 이상인 도서를 검색하시오.

SELECT * FROM BOOK WHERE BOOKNAME LIKE '%축구%' AND PRICE >= 20000;

AND를 사용하여 조건을 두개 걸 수 있다.

 

!도서를 이름 순으로 검색하시오.

SELECT * FROM BOOK ORDER BY BOOKNAME; -- 오름차순

 

!도서를 가격순으로 검색하고, 가격이 같으면 이름순으로 검색하시오.

SELECT * FROM BOOK ORDER BY PRICE, BOOKNAME;

--가격이 같으면 오름차순의 이름으로 정렬한다.

 

!도서를 이름 순으로 검색하시오.

SELECT * FROM BOOK ORDER BY BOOKNAME ASC;

기본적으로는 ASC를 생략하지 않고 오름차순을 표현해준다.

 

!도서의 이름 역순으로 검색하시오.

SELECT * FROM BOOK ORDER BY BOOKNAME DESC;

DESC는 내림차순을 나타낸다.

 

!도서를 가격의 내림차순으로 검색하시오. 만약 가격이 같다면 출판사의 오름차순으로 검색한다.

SELECT * FROM BOOK ORDER BY PRICE DESC, PUBLISHER ASC ;

 

SELECT * FROM ORDERS WHERE ORDERDATE > '2014-07-06';

날짜도 정렬할 수 있지만 아직 안배운 문제점이 있는것 같다. 

Alias(가명) - 중요!!

SELECT aaa.bookname AS 안녕, aaa.price AS yy  ->테이블과 출력하는 컬럼에 별칭을 붙일 수 있다.

FROM book aaa -- AS는 생략가능한데 가독성을 통해서 쓰는게 좋을 듯..?

WHERE aaa.price < 20000 ; -- book뒤의 aaa는 별칭을 붙여줌

 

<집계 함수>

자주쓰는 집계함수 : count, sum, avg, max, min

!고객이 주문한 도서의 총 판매액을 구하시오.

SELECT sum(SALEPRICE) AS totalPrice FROM ORDERS; --> ㄴ자로 행 열(컬럼, 벡터(값의 형태가 같은거))

--> 매트릭스의 하나의 값 = 스칼라

--> sum() 괄호 안에는 벡터값(column,열)이 들어감

--> sum(*)은 불가능하다.  * 은 matrix이기 때문이다. 하나의 벡터(column)만 대입가능

 

SELECT sum(SALEPRICE) ,avg(SALEPRICE),MAX (SALEPRICE) FROM ORDERS;

!문법적으로는 스칼라와 벡터를 동시 출력할 수 없다.

이 경우에는 값이 전부 스칼라이거나, 전부 벡터인 경우에만 

매트릭스의 형태로 출력이 가능하다.

 

SELECT COUNT(*) FROM ORDERS o;

COUNT() 괄호 안은 매트릭스를 받는다. 행의갯수를 세는데 사용된다.

 

!2번 김연야 고객이 주문한 도서의 총 판매액을 구하시오

SELECT SUM(SALEPRICE) FROM ORDERS WHERE CUSTID IN 2;

 

!고객이 주문한 도서의 총 판매액, 평균값, 최저가, 최고가를 구하시오.

SELECT sum(SALEPRICE), avg(SALEPRICE), MIN(SALEPRICE), MAX(SALEPRICE) FROM ORDERS;

 

!마당서점의 도서 판매 건수를 구하시오.

SELECT COUNT(*) FROM ORDERS;

 

!질문: 장미란이 구매한 도서 정가의 합을 구하세요. : 32000

SELECT * FROM CUSTOMER WHERE NAME = '장미란';

SELECT * FROM ORDERS WHERE CUSTID = 3;

SELECT sum(price) FROM book WHERE bookid IN (6,8,10);

 

GROUP BY ~ HAVING -> ~별로 시작되면 대부분 GROUP BY 사용

!고객별로 주문한 도서의 총 수량과 총 판매액을 구하시오. 

SELECT CUSTID, sum(SALEPRICE), count(*) FROM ORDERS --여기서 count는 그룹핑된 행의 갯수

GROUP BY CUSTID --GROUP BY (컬럼명 == select뒤와 같아야만함) 이외의 나머지 행은 스칼라값으로 만들어줘야함

ORDER BY CUSTID ASC

;

 

 

!가격이 8000원 이상인 도서를 구매한 고객에 대하여 고객별 주문 도서의 총 수량을 구하시오.

(단, 두 권 이상 구매한 고객만 구한다.)

SELECT CUSTID, count(*) FROM ORDERS WHERE SALEPRICE >= 8000 --WHERE 필터링,먼저 필터링 된다.

GROUP BY CUSTID --where에는 집계조건 불가능하다.

HAVING count(*) >= 2--having뒤에 쓸 수 있는 조건은 GROUP BY 로 묶인 부분과, 집계된 것만 가능하다

ORDER BY CUSTID ASC

;