[취성패] 자바프로그래밍 - 4일차 학습일지(SQL-데이터 정의어)

- 도서의 가격(Book 테이블)과 판매가격(Orders 테이블)의 차이가 가장 많은 주문

max(price - saleprice) -> 집합연산 -> 여러개를 하나로 압축한다

 

선생님 답

select *  -> *또는 orderid

from book, orders

where book.bookid = orders.bookid

and price-saleprice = (select max(price-saleprice)

                             from book, orders

                             where book.bookid = orders.bookid);

 

- 나의 답

select distinct o1.orderid, b1.price - o1.saleprice as 할인액

from book b1, orders o1

where b1.price - o1.saleprice = (select max(b2.price - o2.saleprice)

                                          from book b2, orders o2

                                          where b2.bookid = o2.bookid);

 

/* 도서의 판매액 평균보다 자신의 구매액 평균이 더 높은 고객의 이름*/

/* 고객별평균 */

select name, avg(saleprice)

from customer, orders

where customer.custid = orders.custid

group by name

having 고객별평균 > 전체평균;

 

/* 전체 평균*/

select avg(saleprice)

from orders;

 

select name, avg(saleprice) /*여기는 메인절 group by 되어있어서 고객별 평균나옴*/

from customer, orders

where customer.custid = orders.custid

group by name

having avg(saleprice) > (select avg(saleprice)

                                from orders); /*여기는 부속절 -> 독립적이라 전체 평균 나옴*/

 

select -> 조작어 : '데이터'를 ~

- insert : 입력

- update : 수정

- delete : 삭제

 

데이터 정의어 DML(manupulate) - '테이블, 사용자 : 개체(object)'를 ~

- CREATE문 : 생성

- ALTER문 : 수정 - 속성을 추가(add), 변경(modify), 삭제(drop)

- DROP문 : 삭제

 

ex)

create table 테이블이름

create index ~

create user ~

 

CREATE문

CREAT TABLE NewBook( bookid NUMBER,  -> 자료형 : 숫자 number : 1000 숫자 그냥 써주면 됨

bookname VARCHAR2(20),                     -> 자료형 : 문자 varchar2 : 쓸 때는 ' ' 자바에서는 " "도 혼용

publisher VARCHAR2(20),                           자료형 : 날짜 date

price NUMBER);

 

자료형 char : 문자 -> 1글자 - char : character의 약자

                 여러 글자 -> 문자배열, 문자열 : varchar2

                                                          varchar2(20) -> 20바이트(한글10자, 영어 20자 들어감

                                  -> 프로그램(java, python 등 에서는 쓸만큼만 설정! 아니면 낭비임. DB는 상관없음)

varchar - var : variance - variable 변수(변함O) <-> 상수(변함X)

                                                  bookid            7

bookid는 1, 2, 3, 4, 5 .... 등 뭔지 모름 바뀔 수 있고, 덮어쓸 수 있음

 

DB는 완전한 게 아님 -> 개발과 사용의 중간 단계, 개발이 맞기는 함!

User - DB - 개발자

 

- bookname은 null 값을 가질 수 없고, publisher는 같은 값이 있으면 안 된다.

price에 값이 입력되지 않을 경우 기본 값 10000을 저장한다. 또, 가격은 최소 1000원 이상으로 한다.

 

CREAT TABLE NewBook (

bookname VARCHAR2(20) NOT NULL,

publisher VARCHAR2(20) UNIQUE,

price NUMBER DEFAULT 10000 CHECK(price > 1000),

PRIMARY KEY(bookname, publisher));

 

[기본키] unique : 중복을 허용하지 X ex) ID 처럼 값을 하나만 갖는 것

ex) 이름을 기본키로 잡을 수 없음 -> 동명이인 존재 가능성 -> 전화번호를 기본키로 잡을 수 있음

테이블에는 기본키가 꼭 있어야 함 -> 기본키 없는 테이블은 없다

기본키는 한개밖에 존재하지 않음

 

위 작성한 테이블에서 기본키 추가

1)

CREATE TABLE NewCustomer (

custid number primary key,     -> 축약형 표현

bookname varchar2(20),

publisher varchar2(20),

price number);

 

또는

 

2)

CREATE TABLE NewCustomer (

custid number,

bookname varchar2(20),

publisher varchar2(20),

price number primary key (bookid));  -> 별도로 한줄 더 써주는 유형 : 강조의 느낌

만약 두 개를 같이 primary key로 지정(복합키)할 시 이런식으로 써주어야 함

 

- 복합키 : id, 이름

            id1 홍길동

            id2 홍길동

            id1 김연아  가능

            id1 홍길동  불가능 -> 두개가 다 같으면 안됨

 

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

 

bookid(도서번호) - NUMBER

bookname(도서이름) - VARCHAR2(20)

publisher(출판사) - VARCHAR2(20)

price(가격) - NUMBER

 

CREATE TABLE NewBook (

bookid number,

bookname varchar2(20),

publisher varchar2(20),

price number);

 

/* 여기서 기본키를 지정하고 싶으면

CREATE TABLE NewBook (

bookid number PRIMARY KEY,

bookname varchar2(20),

publisher varchar2(20),

price number);

 

bookid 속성이 없어서 두 개의 속성 bookname, publisher가 기본키가 되면 괄호로 복합키 지정

CREATE TABLE NewBook (

bookname varchar2(20),

publisher varchar2(20),

price number,

PRIMARY KEY (bookname, publisher));

 

복합키를 지정하게 되면 New Book 테이블에서

bookname publisher

요정이야기 아이출판사

요정이야기 경림출판사

기사이야기 아이출판사

요정이야기 아이출판사 (X) -> 중복되므로 입력 불가

 

다른 예로 회원가입 테이블에서

복합키로 이름과 주민번호를 같이 지정하게되면

이름    주민번호

김나리 920404-123456

김나리 001212-123456 (가능. 주민번호가 다른 동명이인)

김나리 920404-123456 (불가능. 동일한 사람이 이미 가입함)

 

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

custid(고객번호) - NUMBER, 기본키

name(이름) - VARCHAR2(40)

address(주소) - VARCHAR2(40)

phone(전화번호) - VARCHAR2(30)

 

Create table NewCustomer (

custid NUMBER PRIMARY KEY,

name VARCHAR2(40),

address VARCHAR2(40),

phone VARCHAR2(40));

 

- 다음과 같은 속성을 가진 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); -> 참조무결성

 

FOREIGN KEY 외래키 - NewOrders 테이블의 Custid는 NewCustomer의 기본키인 custid를 참조한다

따라서 NewOrders 테이블에서 Custid는 NewCustomer라는 외부에서 온 남의 기본키(외래키)이다.

 

기본키 : 개체 무결성을 보장

외래키 : 참조 무결성

 

ON DELETE CASCADE

- on : IT에서는 ~하는 중에, ~할 때 등의 진행 중의 늬앙스

-> DELETE를 할 때 custid 1번인 박지성이 bookid 1번인 책을 구매를 취소했다면 Orders 테이블에서 그냥 삭제하면 됨 그러나 NewCustomer의 custid 1번 박지성이 탈퇴를 하면 회원탈퇴 불가

-> why? orders 테이블 주문 목록에 있기 때문에

-> DELTE 원할 시 orders 테이블에서 custid 1번 박지성을 먼저 지운 후 NewCustomer 테이블의 custid 1번 박지성을 삭제 가능

그렇지만

- CASCADE : 추적, 추격 -> 이걸 쓰면 newCustomer 테이블에서 custid 1번 박지성을 삭제하면 Orders 테이블의 Custid 1번 박지성의 주문도 같이 사라짐

 

ON UPDATE CASCADE도 마찬가지! newCustomer 테이블에서 custid를 1번에서 11번으로 바꾸면 Orders 테이블에서도 박지성의 custid가 11번으로 바뀜

 

ALTER문 - 구조(테이블 등)를 수정함

 

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

ALTER TABLE NewBook ADD isbn VARCHAR2(13); 

-> ADD -> Column을 추가

 

- NewBook 테이블에 isbn 속성의 데이터 타입을 NUMBER형으로 변경하시오

ALTER TABLE NewBook MODIFY isbn NUMBER;

-> MODIFY -> Column을 수정

 

- NewBook 테이블에 isbn 속성을 삭제하시오

ALTER TABLE NewBook DROP COLUMN isbn;

-> DROP -> Column을 삭제 그냥 테이블에 오른쪽 마우스 편집 눌러서 수정해도 됨

- isbn 생성 잘 안되면 커밋 눌러보기

 

 

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

ALTER TABLE NewBook MODIFY bookid NUMBER NOT NULL;

 

- NewBook 테이블에 bookid 속성을 기본키로 변경하시오

ALTER TABLE NewBook ADD PRIMARY KEY(bookid);

 

- NewBook 테이블을 삭제하시오

DROP TABLE NewBOOk;

 

- NewCustomer 테이블을 삭제하시오. 만약 삭제가 거절된다면 원인을 파악하고 관련된 테이블을 같이 삭제하시오 

  (NewOrders 테이블이 NewCustomer를 참조하고 있음)

DROP TABLE NewCustomer; -> 이 테이블을 NewOrders 테이블이 참조하고 있으므로 삭제 불가능

  아래에서 NewOrders 테이블을 삭제한 후 NewCustomer를 삭제해야 함

DROP TABLE NewOrders;

 

데이터 조작어 - 삽입, 수정, 삭제(INSERT, UPDATE, DELETE) : '데이터'를 변경

 

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

기본문법 : INSERT INTO 테이블이름 (column1, column2, .. 속성리스트)

                                    VALUES (값1, 값2, .. 각 colum에 들어갈 값);

 

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

  스포츠 의학은 한솔의학서적에서 출간했으며 가격은 90,000원이다.

INSERT INTO Book (bookid, bookname, publisher, price)

                  values(11, '스포츠 의학', '한솔의학서적', 90000); 

 

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

  스포츠 의학은 한솔의학서적에서 출간했으며 가격은 미정이다.

INSERT INTO Book (bookid, bookname, publisher)

                 values(14, '스포츠 의학', '한솔의학서적');

 

대량삽입(bulk insert) : 한꺼번에 여러 개의 투플을 삽입하는 방법

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

 

Insert into book(bookid, bookname, publisher, price)

select bookid, bookname, publisher, price

from Imported_book;

 

-> Imported_book 테이블에서 각 값들을 가져와서 book 테이블에 삽입)

 

UPDATE 문 : 수정 - 특정 속성 값을 수정하는 명령

기본문법 : UPDATE 테이블이름 SET 속성이름1=값1, 속성이름2=값2, ..

              WHERE 검색조건;

 

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

UPDATE Customer

SET address = '대한민국 부산'

where custid = 5;

 

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

UPDATE Customer

set address = ( select address

                    from customer

                    where name like '김연아') - 주소를 김연아의 주소를 가져와서 박세리의 주소에 넣음

WHERE name like '박세리';

 

DELETE 문

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

- 기본문법 DELETE FROM 테이블이름

  WHERE 검색조건;

 

- Customer 테이블에서 고객번호가 5인 고객을 삭제하시오

DELETE FROM Customer where custid = 5;

 

- 모든 고객을 삭제하시오

DELETE FROM Customer;

-> orders 테이블 custid 있음 -> 삭제안됨. ?

 

- 마당서점에서 다음의 심화된 질문에 대해 SQL 문을 작성하시오

(1) 박지성이 구매한 도서의 출판사와 같은 출판사에서 도서를 구매한 고객의 이름

- 박지성이 구매한 책의 출판사 목록 먼저 구해보면

select publisher

from customer, orders, book

where customer.custid = orders.custid

and orders.bookid = book.bookid

and name like = '박지성';

  -> 부속질의 결과 : 대한미디어, 나무수, 굿스포츠

 

내 답 1

select publisher, name

from customer, orders, book

where customer.custid = orders.custid

and orders.bookid = book.bookid

and publisher in (select publisher

                       from customer, orders, book

                       where customer.custid = orders.custid

                       and orders.bookid = book.bookid

                       and name like '박지성');

 

내 답 2

select distinct name

from customer, orders, book

where customer.custid = orders.custid

and orders.bookid = book.bookid

and publisher in (select publisher

                      from customer, orders, book

                      where customer.custid = orders.custid

                      and orders.bookid = book.bookid

                      and name like '박지성');

 

선생님 답

select name

from customer, orders, book

where customer.custid = orders.custid

     and orders.bookid = book.bookid

        and publisher in ('대한미디어', '나무수', '굿스포츠)

     and publisher in (select publisher

                           from customer, orders, book

                           where customer.custid = orders.custid

                           and orders.bookid = book.bookid

                           and name like '박지성')

and not like '박지성';

 

(2) 두 개 이상의 서로 다른 출판사에서 도서를 구매한 고객의 이름

select name, publisher from customer, orders, book

where customer.custid = orders.custid

and orders.bookid = book.bookid;

-> 일단 이름, 구매한 출판사가 나옴

 

select name     -> 이름별로 구매한 출판사 개수를 먼저 구해야 하니까 

from customer, orders, book

where customer.custid = orders.custid

     and orders.bookid = book.bookid

group by name                            -> 이름, 개수가 나올 수 있게 이름으로 group 지음

having count(distinct publisher) >= 2;

-> 출판사 개수가 2이상인 것만! 여기서 같은 출판사를 2번 사는 등의 경우를 제외해주기 위해서

distinct publisher

 

(선생님 다른 풀이)

- 상관쿼리

select name

from customer

where 2 =< ( select count(distinct publisher)

                      from orders, book

                      where orders.bookid = book.bookid); -> 출판사의 개수 : 5

                                   서로 상관 X -> 고객별로 출판사의 개수 구하려면 상관 시켜야 함

-> 

select name

from customer c1 

where 2 <= ( select count(distinct publisher)

                      from orders, book, customer c2

                      where orders.bookid = book.bookid

                      and customer.custid = orders.custid

                      and c1.name = c2.name );

 

 

(3) 전체 고객의 30% 이상이 구매한 도서

select book.bookid as 책번호, bookname as 책이름, count(orders.bookid) as 판매권수

from orders, book

where orders.bookid = book.bookid

group by book.bookid, bookname

having count(orders.bookid) >= (select count(*)*0.3

                                           from customer);

 

(선생님 풀이)

-> 도서별로 구매한 고객 수가 30%(1.5) 이상인 도서

-방법1 : group by

select bookname

from book, orders

where book.bookid = orders.bookid

group by bookname; -> 각 책별 팔린 개수가 나옴

having count(book.bookid) >= (select count(*) from customer)*0.3;

 

-방법2 : 상관쿼리

select bookname

from book

where a > b 형태

 

-> select bookname

from book b1

where (select

         from book b2

         where b1.bookid = b2.bookid ) >= b  -> 서로 상관시킴

 

 

 

->

select bookname

from book b1

where ( select count(b2.bookid)

            from book b2, orders -> 두개 연결하면 팔린책 중

          where b1.bookid = b2.bookid

            and b2.bookid = orders.bookid) >= (select count(*) from customer)*0.3;

 

 

다음 질의에 대해 DML 문을 작성하시오

(1) 새로운 도서 ('스포츠 세계', '대한미디어', 10000원)이 마당서점에 입고되었다.

INSERT INTO BOOK (bookid, bookname, publisher, price)

                   values(12, '스포츠 세계', '대한미디어', 10000);

-> 단순화

INSERT INTO BOOK values(12, '스포츠 세계', '대한미디어', 10000);

 

(2) '삼성당'에서 출판한 도서를 삭제해야 한다.

DELETE FROM BOOK where publisher like '삼성당';

 

후에

select * from book; 해서 삭제됐는지 확인 가능~!

 

(3) '이상미디어'에서 출판한 도서를 삭제해야 한다. 삭제가 안 될 경우 원인을 생각해보자

DELETE FROM Book where publisher like '이상미디어';

-> bookid 7, 8번 책이 '이상미디어'의 책인데 주문이 되어서 orders 테이블에 있으므로 지울 수 없음

   book이 부모 orders가 child -> child쪽에 있는 것부터 지워야 지워짐

 -> 참조무결성 제약조건, 외래키 제약조건

 

(4) 출판사 '대한미디어'가 '대한출판사'로 이름을 바꾸었다.

UPDATE BOOK

      SET publisher = '대한출판사'    -> 여기서는 like불가! 

where publisher like '대한미디어';    -> 여기서는  =, like 둘 다 가능

 

--주석처리 -> 블럭설정 하고 'ctrl + /'

--저장 : 'ctrl + s'

--워크시트 만들기 : '도구' -> '워크시트 만들기'