2022.04.19. 화요일 이공계 전문기술 연수사업 17일차

오늘 배운 것

서점관리를 위하여 필요한 테이블을 만듦

도서(도서번호 도서이름 출판사 가격)
고객(고객이름 고객번호 주소 전화)
주문(주문번호 고객번호 도서번호 구매가격 구매일)

----------------------------------------------------------------------------------
create table book(bookid number primary key, bookname varchar2(50), publisher varchar2(50), price number);

create table customer(custid number primary key, name varchar2(50), address varchar2(20), phone varchar2(20));

create table orders(orderid number primary key, custid number references customer(custid), bookid number references book(bookid), saleprice number, orderdate date);

테이블 구조까지 삭제: drop table 테이블명

insert into book values(1,'축구아는 여자','대한미디어',7000);
insert into book values(2,'축구의 이해','굿스포츠',17000);
insert into book values(3,'골프 바이블','굿스포츠',27000);
insert into book values(4,'피겨 교본','굿스포츠',37000);
insert into book values(5,'역도 단계별 기술','삼성당',47000);
insert into book values(6,'야구의 추억','kbg',57000);
insert into book values(7,'등산의 매력','내가엄홍길',70600);
insert into book values(8,'달리기 잘하는 법','런런런',70070);
insert into book values(9,'snpe 완전정복','한국snpe협회',70800);
insert into book values(10,'케틀밸 스윙 접근하기','홈트나라',70050);

데이터를 추가하고 그것을 데이터베이스에 반영하기 위해 commit;함

연습)다음과 같은 고객의 정보를 추가해봅니다.
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);

박지성 고객이 축구의 역사를 6000원에 구매한 정보를 orders 테이블에 추가하고 싶음
1번 고객이 1번 도서를 6000원에 구매한 정보를 orders 테이블에 추가함

insert into orders values(1, 1, 1, 6000, '2022/04/01');
insert into orders values(2, 2, 2, 16000, '2022/04/02');
insert into orders values(3, 3, 3, 36000, '2022/04/03');
insert into orders values(4, 4, 4, 6000, '2022/04/04');
insert into orders values(5, 1, 5, 8000, '2022/04/05');
insert into orders values(6, 2, 6, 6000, '2022/04/06');
insert into orders values(7, 3, 7, 14000, '2022/04/07');
insert into orders values(8, 4, 8, 62000, '2022/04/08');
insert into orders values(9, 1, 9, 26000, '2022/04/09');
insert into orders values(10, 2, 10, 52000, '2022/04/11');
----------------------------------------------------------------------------------
sql
데이터베이스의 명령어
데이터 정의어 : 테이블이나 관계의 구조를 생성하는데 사용함 CREATE, ALTER, DROP
데이터 조작어 : 테이블에 데이터를 검색,삽입,수정,삭제하는 데 사용함 SELECT, INSERT, DELETE, UPDATE
데이터 제어어 : 데이터의 사용 권한을 관리함 GRANT, REVOKE

데이터베이스 명령어는 대소문자를 구분하지 않음
하지만 값에 대해서만 대소문자를 구분함

-------------------------------------------------------------------------------
데이터 조작어 - 검색
ㄴselect 문
ㄴ집계함수와 GROUP BY
ㄴ두 개 이상의 테이블에서 sql질의 -ex) 4월 3일에 구매한 고객의 이름과 도서를 검색하고 싶음

관계형 데이터베이스
ㄴ서로 테이블간에 관계가 있는 데이터베이스들(공통으로 들어가는 컬럼이 있다는 말)

SELECT문의 구성요소
ㄴselect 속성이름 from 테이블이름 where 검색조건;
ㄴselect[ALL|DISTINCT] 속성이름 from 테이블이름 [where 검색조건(들)] [GRUOP BY 속성이름] [HAVING 검색조건(들)] [ORDER BY 속성이름 [ASC|DESC]]
ALL:중복 허용해서 나타냄
DISTINCT:중복 허용하지 않고 나타냄

가격이 20000원 미만인 도서가 무엇인지 알고싶다
where 절에 조건으로 사용할 수 있는 술어
비교 :  =,<,>,<=,>=,<>(같지않냐는 소리)
범위 : between a and b
집합 : in, not in -ex) price in(10000,20000,30000)
패턴 : like -ex) bookname like '축구%' (축구로 시작하는 아무거나 오라는 소리)
NULL : is null, is not null -ex) price is null
복합조건 : and, or, not -ex) (price < 20000) and (bookname like '축구%')
복합조건 :

와일드카드 문자: * 모두라는 의미

만원과 이만원 사이의 도서를 출력
select * from book where price>=10000 and price<=20000;
select * from book where price between 10000 and 20000;

출판사가 '굿스포츠' 혹은 '대한미디어'인 도서를 검색하시오
select * from book where publisher in('굿스포츠','대한미디어');
select * from book where publisher ='굿스포츠' or publisher ='대한미디어';

출판사가 '굿스포츠' 혹은 '런런런'가 아닌 도서를 검색하시오
select * from book where publisher not in('굿스포츠','런런런');
select * from book where publisher <>'굿스포츠' and publisher <>'런런런';

축구의 역사를 출간한 출판사를 검색하시오
select bookname, publisher from book where bookname like '축구의 역사';
select bookname, publisher from book where bookname = '축구의 역사';

도서 이름에 '축구'가 포함된 출판사를 검색하시오
select bookname, publisher from book where bookname like '%축구%';\

도서 이름의 왼쪽 두 번째 위치에 '구'라는 문자열을 갖는 도서를 검색하시오
select * from book where bookname like '_구%';

like 연산자
문자열의 패턴을 검색코자 할 때 사용하는 연산자
% : 아무글자
_ : 모르는 한 글자

복합조건 
ㄴ조건식이 두 개 이상일 때 and나 or연산자를 사용함

축구와 관련한 도서 중에 가격이 2만원 이상인 도서의 정보를 출력
select * from book where bookname like '%축구%' and price >=20000;

출판사가 '굿스포츠' 혹은 '런런런'에서 출간하는 모든 도서의 정보를 출력
select * from book where publisher like '굿스포츠' or publisher like '런런런';
select * from book where publisher in ('굿스포츠', '런런런');

order by
ㄴ특정 칼럼을 기준으로 자료를 정렬하여 검색하고자 사용할 때 사용
order by 컬럼이름 [asc(오름차순)|desc(내림차순)};

도서를 이름 순으로 검색
select * from book order by bookname;

도서를 가격 순으로 검색하고 가격이 같으면 이름 순으로 출력
select * from book order by price, bookname;

도서를 가격이 높은 순으로 출력하되 가격이 똑같으면 책이름 순으로 출력
select * from book order by price desc,bookname; 

select * from book order by publisher, price desc;

---------------------------------------------------------------------------
와일드 문자의 종류

---------------------------------------------------------------------------
집계함수
ㄴ컬럼 별로 총합,평균,최대값,최소값,개수를 파악하기 위한 함수를 말함
sum,avg,max,min,count

고객이 주문한 도서의 총 판매액을 구하시오
select sum(saleprice) from  orders; (실행 개수 : 한 개)
select saleprice from orders; (실행 개수 : 레코드의 수만큼)
select sum(saleprice) as 총매출 from orders; (애칭 부여.. 애칭..?)

2번 김연아 고객이 주문한 도서의 총 판매액 출력
select sum(saleprice) as 총매출 from orders where custid=2;

고객이 주문한 도서의 총 판매액,평균값,최저가,최고가를 출력
select sum(saleprice) 총매출, avg(saleprice) 평균값, max(saleprice) 최고가, min(saleprice) 최저가 from orders;

-----------------------------------------------------------------------------------------
카운트함수
ㄴ개수를 파악하는 함수
count(컬럼이름) =>그 컬럼의 값이 null이 아닌 개수를 알려주는 함수
count(*) =>모든 레코드의 수를 알려주는 함수

모든 도서의 판매 건수를 출력
select count(*) from orders;

select count(phone) from customer;
COUNT(PHONE)
------------
           4 =>null값 때문에 4가 출력됨

1번 고객이 주문한 총 주문 건수와 총 주문 금액을 출력
select count(bookid), sum(saleprice) from orders where custid=1;

전체 총주문 건수와 총 주문금액 출력
select count(*), sum(saleprice) from orders;

select custid, count(*), sum(saleprice) from orders;
이건 오류남! custid는 값이 여러 개고 집계함수의 결과는 한 건이기 때문에 다른 컬럼을 함께 출력할 수 없음

고객 아이디별로 총 주문건수와 총 주문금액을 출력
ㄴ한 건이 아니라 고객 아이디 수 만큼 출력. 집계함수와 함께 group by 절을 사용
select custid, count(*), sum(saleprice) from orders group by custid;

집계함수를 사용할 때 group by 절에 나타난 칼럼만이 select 절에 올 수 있음!

출판사 별로 출간하는 도서의 수, 평균 도서가격, 최저 도서가격, 최고 도서가격을 출력 단, 도서의 수가 높은 순으로 출력
select publisher, count(*), avg(price), min(price), max(price) from book group by publisher order by count(*) desc;

---------------------------------------------------------------------------------------------------------------------
'굿스포츠'나 '대한snpe협회','런런런'에서 출간하는 도서를 고객 아이디별로 구매한 건수를 출력하시오
ㄴ구매한 정보는 orders 테이블에 있고 출판사의 정보는 book테이블에 있음

book테이블로부터 '굿스포츠'나 '한국snpe협회','런런런'에서 출간하는 도서번호를 검색
select bookid from book where publisher in('굿스포츠','한국snpe협회','런런런');
BOOKID
------
     1
     2
     3
     4
     8
     9
select custid, count(*) from orders where bookid in(select bookid from book where publisher in('굿스포츠','한국snpe협회','런런런')) group by custid;
(이걸 서브쿼리라고 함)

-------------------------------------------------------------------------------------------------------------------------------------------
구매 가격이 8000원 이상인 구매한 정보에 대하여 고객 아이디별로 총 구매 건수를 출력
select custid, count(*) from orders where saleprice >= 8000 group by custid;

CUSTID   COUNT(*)
------ ----------
     2          2
     3          2
     1          2
     4          1

-----------------------------------------------------------------------------------------
having
ㄴgroup by절에 나타난 결과에 대하여 조건식을 표현하고자할 때 사용함

구매가격이 8000원 이상인 구매한 정보에 대하여 고객 아이디별로 총 구매 건수를 출력 단,총 구매 건수가 두 건이상인 것만 출력
select custid, count(*) from orders where saleprice >= 8000 group by custid having count(*)>=2;

'굿스포츠','한국snpe협회','런런런'에서 출간하는 출판사별 도서의 수, 평균 도서가격을 출력하시오
단,평균 도서가격이 8000원 이상이고 평균 가격이 높은 순으로 출력
select publisher, count(*), avg(price) from book where publisher in ('굿스포츠','한국snpe협회','런런런') group by publisher having avg(price)>=8000 order by avg(price) desc;

도서번호가 1인 도서의 이름
select bookname from book where bookid=1;
가격이 2만원 이상인 도서의 이름
select bookname from book where price >=20000;
박지성의 총 구매액(박지성의 고객번호는 1번으로 놓고 작성)
select sum(saleprice) from orders where custid=1;
박지성이 구매한 도서의 수
select count(*) from orders where custid=1;

------------------------------------------------------------------------------------------------------------------
마당서점 도서의 총 개수
select count(*) from book;
마당서점에 도서를 출고하는 출판사의 총 개수
select count(distinct publisher) from book;
모든 고객의 이름,주소
select name, address from customer;
2022년 4월 4일~ 2022년 4월 7일 사이에 주문받은 도서의 주문 번호
select orderid from orders where orderdate between '2022/04/07' and '2022/04/07';
select orderid from orders where orderdate >='2022/04/04' and orderdate<='2022/04/07'; 
2022년 4월 4일~ 2022년 4월 7일 사이에 주문받은 도서를 제외한 도서의 주문번호
select orderid from orders where orderdate not between '2022/04/07' and '2022/04/07';
select orderid from orders where orderdate <='2022/04/04' or orderdate >='2022/04/07'; 
성이 김씨인 고객의 이름과 주소
select name, address from customer where name like '김%'; //여기선 name =이 아닌 name like임!(숫자 자료형이 아니니까)
성이 김씨고 이름이 아로 끝나는 고객의 이름과 주소
select name, address from customer where name like '김%아';

-------------------------------------------------------------------------------------------------------
고객 번호를 입력받아 그 고객이 주문한 주문번호,고객번호,도서번호,구매금액,구매일을 출력하는 프로그램을 작성

select * from orders where custid=1;

데이터베이스 연동 프로그램을 만들 때는 vo와 dao를 만듦
vo : value object 데이터베이스 테이블을 객체로 표현하기 위한 클래스
dao : database access object 데이터베이스에 접근하여 검색,추가,수정,삭제의 명령을 수행하는 클래스

오늘 헷갈린 것

• for each 문 다시 학습하기
• where name like ''는 되고 where name =''은 안되는 이유
• 안보고 vo,dao,메인메소드 구현하기

오늘 한 생각

오라클 생각보다 까다롭다

카테고리 db하나 파서 오류나 학습한 것들 정리해놔야겠다!

그 전에.. 자바정복부터...ㅎ