개발일지 -참조관계 , 수정쿼리-

오늘의 수업 내용은 지난 시간에 배운 내용 중 참조 관계이다

 

<예문>

   drop table if exists parent1;
     create table parent1(
		id bigint primary key,
        p1 varchar(10),
        p2 varchar(20)
        );
        
drop table if exists child1;
create table child1(
	id bigint primary key,
    c1 varchar(10),
    c2 varchar(20),
    p_id bigint, -- 참조할 컬럼
    -- 외래키 지정(p_id 컬럼을 parent1 테이블의 id 컬럼을 참조하도록함)
    constraint fk_child1 foreign key (p_id) references parent1(id)
    );
    
    insert into parent1(id , p1 , p2) values(1,'aa','aa');
    insert into parent1(id , p1 , p2) values(2,'bb','bb');
     insert into parent1(id , p1 , p2) values(3,'cc','cc');
     insert into parent1(id , p1 , p2) values(4,'dd','dd');
    select*from parent1;
    
insert into child1(id , c1 , c2 , p_id) values(1,'aaa','aaa',1);
-- 부모 id 컬럼에 없는 값을 p_id에 저장
insert into child1(id , c1 , c2 , p_id) values(2,'bbb','bbb',2);
insert into child1(id , c1 , c2 , p_id) values(3,'ccc','ccc',3);

참조할 컬럼에 외래 키를 지정해 주어 부모테이블의 id 컬럼을 참조하게 만들어주었고

우선 부모테이블만 실행시키고 값을 입력시키면

자식테이블의 값을 입력을 해서 실행을 시키면

위와 같이 실행된다

 

이제 위의 데이터를 가지고 해볼것은 데이터를 삭제해볼 것이다

-- 부모테이블의 데이터 삭제
-- id= 2인 데이터 한 줄을 삭제
-- 자식 테이블에 id= 2 인 데이터를 참조하는 부분이 있기 때문에 삭제 불가
delete from parent1 where id=2;
-- 자식 테이블에 id=4인 데이터를 참조하는 부분이 없기 때문에 삭제 가능
delete from parent1 where id=4;
-- 자식 테이블의 부모 id=2 를 참조하는 데이터 삭제
delete from child1 where id=2;

제일 첫 번째 실행문을 실행하게 되면 자식테이블에 부모테이블 id = 2 인 데이터가 있기 때문에 삭제가 불가능하지만

2번째 실행문에선 참조하는 부분이 없기 때문에 삭제가 가능하다

그리고 3번째 실행문에선 자식테이블에서는 부모테이블에 참조하는 데이터를 삭제할 수 있기에 실행을 시키면

데이터가 사라진 것을 볼 수 있다

 

<예문 2>

drop table if exists child3;
create table child3(
	id bigint primary key,
    c1 varchar(10),
    c2 varchar(20),
    p_id bigint, -- 참조할 컬럼
    -- 외래키 지정(p_id 컬럼을 parent3 테이블의 id 컬럼을 참조하도록함)
    -- 부모 데이터 삭제시 자식 데이터는 유지 되지만 참조 컬럼은 null이 됨
    constraint fk_child3 foreign key (p_id) references parent3(id) on delete set null
    );

 insert into parent3(id , p1 , p2) values(1,'aa','aa');
    insert into parent3(id , p1 , p2) values(2,'bb','bb');
     insert into parent3(id , p1 , p2) values(3,'cc','cc');
     insert into parent3(id , p1 , p2) values(4,'dd','dd');
    select*from parent3;
    delete from parent3 where id = 1;
    
    insert into child3(id , c1 , c2 , p_id) values(1,'aaa','aaa',1);
insert into child3(id , c1 , c2 , p_id) values(2,'bbb','bbb',2);
insert into child3(id , c1 , c2 , p_id) values(3,'ccc','ccc',3);
insert into child3(id , c1 , c2 , p_id) values(4,'ddd','ddd',5);
select*from child3;

예문 1과 같은 내용이지만 데이터를 삭제할 경우 참조 컬럼이 null이 되게 만들어준 쿼리문

 

-수정쿼리-

update child3 set c1 = '수정내용' where id = 2;
update child3 set c1 = '수정내용' , c2 = 'ㅎㅎㅎ' where id = 3;

수정쿼리는 update를 사용하여 테이블에 내용을 수정할 수 있다

 

<예제>

drop table if exists book;
create table book(
	id bigint primary key auto_increment,
    b_bookname varchar (20),
    b_publisher varchar (20),
    b_price int
    );

insert into book(b_bookname , b_publisher , b_price) values('축구 역사' , '좋은출판사' , 7000);
insert into book(b_bookname , b_publisher , b_price) values('축구 리포트' , '나무출판사' ,13000);
insert into book(b_bookname , b_publisher , b_price) values('축구를 알려주마', '대한출판사','22000');
insert into book(b_bookname , b_publisher , b_price) values('배구의 바이블','대한출판사','35000');
insert into book(b_bookname , b_publisher , b_price) values('피겨 고과서','좋은출판사', 8000);
insert into book(b_bookname , b_publisher , b_price) values('피칭의 단계별기술','좋은출판사',6000);
insert into book(b_bookname , b_publisher , b_price) values('야구의 추억 이야기','나이스미디어',20000);
insert into book(b_bookname , b_publisher , b_price) values('야구 읽어주는 남자','나이스미디어',13000);
insert into book(b_bookname , b_publisher , b_price) values('올림픽 스토리', '이야기당' , 7500);
insert into book(b_bookname , b_publisher , b_price) values('olympic history', 'strawberry',13000);
select * from book;

drop table if exists customer;
create table customer(
    id bigint primary key auto_increment,
    c_name varchar (20) not null,
    c_address varchar (20) not null,
    c_phone varchar (20)
    );
    
insert into customer(c_name , c_address , c_phone) values('손흥민','영국런던','000-5000-0001');
insert into customer(c_name , c_address , c_phone) values('김연아','대한민국 서울','000-6000-0001');
insert into customer(c_name , c_address , c_phone) values('김연경','대한민국 서울','000-7000-0001');
insert into customer(c_name , c_address , c_phone) values('류현진','캐나다 토론토','000-8000-0001');
insert into customer(c_name , c_address) values('이강인','프랑스 파리');
select*from customer;

    drop table if exists orders;
		create table orders(
        id bigint primary key auto_increment,
        customer_id bigint,
        book_id bigint,
        o_saleprice int,
        o_orderdate date,
        constraint fk_orders foreign key (customer_id) references customer(id),
        constraint fk_orders1 foreign key (book_id) references book(id)
        );
        
        insert into orders (customer_id , book_id , o_saleprice , o_orderdate) values(1,1,6000,'2023-07-01');
        insert into orders (customer_id , book_id , o_saleprice , o_orderdate) values(1,3,21000,'2023-07-03');
        insert into orders (customer_id , book_id , o_saleprice , o_orderdate) values(2,5,8000,'2023-07-03');
        insert into orders (customer_id , book_id , o_saleprice , o_orderdate) values(3,6,6000,'2023-07-04');
        insert into orders (customer_id , book_id , o_saleprice , o_orderdate) values(4,7,20000,'2023-07-05');
        insert into orders (customer_id , book_id , o_saleprice , o_orderdate) values(1,2,12000,'2023-07-07');
        insert into orders (customer_id , book_id , o_saleprice , o_orderdate) values(4,8,13000,'2023-07-07');
        insert into orders (customer_id , book_id , o_saleprice , o_orderdate) values(3,10,12000,'2023-07-08');
        insert into orders (customer_id , book_id , o_saleprice , o_orderdate) values(2,10,7000,'2023-07-09');
        insert into orders (customer_id , book_id , o_saleprice , o_orderdate) values(3,8,13000,'2023-07-10');
        
        select*from orders;

위의 내용을 지난 시간 동안 배운 것들을 활용하여 각각의 쿼리문을 작성

-- 1. 모든 도서의 가격과 도서명 조회 
select b_bookname , b_price from book;
-- 2. 모든 출판사 이름 조회 
select b_publisher from book;
-- 2.1 중복값을 제외한 출판사 이름 조회
 select distinct b_publisher from book;
-- 3. BOOK테이블의 모든 내용 조회 
select*from book;
-- 4. 20000원 미만의 도서만 조회
select*from book where b_price<20000; 
-- 5. 10000원 이상 20000원 이하인 도서만 조회
select*from book where b_price>10000 and b_price<=20000;
-- 6. 출판사가 좋은출판사 또는 대한출판사인 도서 조회 
select * from book where b_publisher = '좋은출판사' or b_publisher = '대한출판사' order by b_publisher asc;
select * from book where b_publisher in('좋은출판사','대한출판사');
-- 7. 도서명에 축구가 포함된 모든 도서를 조회
select*from book where b_bookname like  '%축구%';
-- 8. 도서명의 두번째 글자가 구인 도서 조회
select*from book where b_bookname like '_구%';
-- 9. 축구 관련 도서 중 가격이 20000원 이상인 도서 조회
select * from book where b_bookname like '%축구%' and b_price >=20000;
-- 10. 책 이름순으로 전체 도서 조회
select * from book order by b_bookname asc;
-- 11. 도서를 가격이 낮은 것 부터 조회하고 같은 가격일 경우 도서명을 가나다 순으로 조회
    select *from book order by b_price asc , b_bookname asc;

-- 12. 주문 도서의 총 판매액 조회 
select sum(o_saleprice) from orders;
-- 13. 1번 고객이 주문한 도서 총 판매액 조회 
select sum(o_saleprice) from orders where customer_id = 1;
-- 14. ORDERS 테이블로 부터 평균판매가, 최고판매가, 최저판매가 조회 
select avg(o_saleprice),max(o_saleprice),min(o_saleprice) from orders; 
-- 15. 고객별로 주문한 도서의 총 수량과 총 판매액 조회 (GROUP BY 활용)
select customer_id, sum(book_id) , sum(o_saleprice) from orders group by customer_id;
-- 16. 가격이 8,000원 이상인 도서를 구매한 고객에 대해 고객별 주문 도서의 총 수량 조회 (GROUP BY 활용)
--    (단, 8,000원 이상 도서 두 권 이상 구매한 고객만) 
select customer_id,count(book_id) from orders where o_saleprice > 8000 group by customer_id having count(book_id)>=2;
-- 17. 김연아고객(고객번호 : 2) 총 구매액
select customer_id  ,sum(o_saleprice) from orders group by customer_id having customer_id = 2;
-- 18. 김연아고객(고객번호 : 2)이 구매한 도서의 수
select customer_id , count(*) from orders where customer_id = 2;
-- 19. 서점에 있는 도서의 총 권수
select count(*) from book;
-- 20. 출판사의 총 수 
select count(b_publisher) from book;
select count(distinct b_publisher) from book;
-- 21. 7월 4일 ~ 7일 사이에 주문한 도서의 주문번호 조회
select * from orders where o_orderdate >= '2023-07-04' and o_orderdate <='2023-07-07';
-- 22. 7월 4일 ~ 7일 사이에 주문하지 않은 도서의 주문번호 조회
select * from orders where o_orderdate < '2023-07-04' or o_orderdate >'2023-07-07';

-- 23. 고객, 주문 테이블 조인하여 고객번호 순으로 정렬
select * from customer,orders where customer.id = orders. id order by customer_id asc;
-- 24. 고객이름(CUSTOMER), 고객이 주문한 도서 가격(ORDERS) 조회 
select c_name , o_saleprice from customer , orders where orders.customer_id = customer.id; 
-- 25. 고객별(GROUP)로 주문한 도서의 총 판매액(SUM)과 고객이름을 조회하고 조회 결과를 가나다 순으로 정렬 
select c_name , sum(o_saleprice) from customer, orders  where customer.id = orders.customer_id group by c_name order by c_name asc; 
-- 26. 고객명과 고객이 주문한 도서명을 조회(3테이블 조인)
select customer.c_name,book.b_bookname  from customer,book,orders where customer.id = book.id = orders.id;
-- 27. 2만원(SALEPRICE) 이상 도서를 주문한 고객의 이름과 도서명을 조회 
select c_name,b_bookname  from customer,book,orders where customer.id  = orders.customer_id and book.id = orders.book_id and o_saleprice>=20000;
-- 28. 손흥민 고객의 총 구매액과 고객명을 함께 조회
select c_name , sum(o_saleprice) from customer , book, orders where customer.id = orders.customer_id and customer.c_name='손흥민';
-- 29. 손흥민 고객의 총 구매수량과 고객명을 함께 조회
select c_name , count(*) from customr , orders where customr.id = orders.customer_id and customr.c_name= '손흥민';

-- 30. 가장 비싼 도서의 이름을 조회 
select b_bookname from book where b_price = (select max(b_price) from book);
-- 31. 책을 구매한 이력이 있는 고객의 이름을 조회
select c_name from customer where id in (select customer_id from orders);
-- 32. 도서의 가격(PRICE)과 판매가격(SALEPRICE)의 차이가 가장 많이 나는 주문 조회 
select * from book;
select * from orders;
-- book , orders 조인해서 정가와 판매가 차이
select book.b_price - orders.o_saleprice from book, orders where book.id = orders.book_id;
select max(book.b_price -orders_o_saleprice) from book, orders where book.id = orders.book_id;
select*from book,orders where book.id = orders.book_id and book.b_price - orders.o_saleprice = 6000;
select*from book,orders where book.id = orders.book_id and book.b_price - orders.o_saleprice = (select max(book.b_price - orders.o_saleprice)from book ,orders where book.id = orders.book_id);
-- 33. 고객별 평균 구매 금액이 도서의 판매 평균 금액 보다 높은 고객의 이름 조회 
-- 도서 판매 평균 금액
select avg(o_saleprice) from orders; -- 11800
-- 고객별 평균 구매 금액 (group by)
select customer.c_name , avg(orders.o_saleprice) from orders , customer where customer.id = orders.customer_id group by customer.c_name;
select customer.c_name , avg(orders.o_saleprice) from orders , customer where customer.id = orders.customer_id group by customer.c_name having avg(orders.o_saleprice)> (select avg(o_saleprice) from orders);
-- 34. 고객번호가 5인 고객의 주소를 대한민국 인천으로 변경 
update customer set c_address = '대한민국 인천' where id = 5;
-- 35. 김씨 성을 가진 고객이 주문한 총 판매액 조회
-- 김씨 성의 고객번호
select id from customer where c_name like '김%';
select sum(o_saleprice) from orders where customer_id=2 or customer_id = 3;
select sum(o_saleprice) from orders where customer_id in(2,3);
select sum(o_saleprice) from orders where customer_id in(select id from customer where c_name like '김%');