인천 일보 아카데미 28일차 - table 구조 변경 alter / ERD보는법

• 27일차 풀어보기 - 정답

-- 13. 1번 고객이 주문한 도서 총 판매액 조회 
select sum(o_saleprice) from orders where id = 1; -- X
select sum(o_saleprice) from orders where customer_id = 1; -- O

-- 17. 김연아고객(고객번호 : 2) 총 구매액
select customer_id , sum(o_saleprice) from orders where customer_id = 2
	group by customer_id; -- X
select sum(o_saleprice) from orders where customer_id = 2; -- O

-- 둘다 가능--

-- 21. 7월 4일 ~ 7일 사이에 주문한 도서의 주문번호 조회 
select * from orders where o_orderdate  >= '2021-07-04' and o_orderdate <= '2021-07-07';
select * from orders where o_orderdate between str_to_date('2021-07-04' , '%Y-%m-%d')
					and str_to_date('2021-07-07' , '%Y-%m-%d');
-- 22. 7월 4일 ~ 7일 사이에 주문하지 않은 도서의 주문번호 조회
select * from orders where !(o_orderdate >= '2021-07-04' and o_orderdate <= '2021-07-07');
select * from orders where o_orderdate not between str_to_date('2021-07-04' , '%Y-%m-%d')
					and str_to_date('2021-07-07' , '%Y-%m-%d');
                                        

-- 추가 --
-- 20. 출판사의 총 수 
select count(distinct b_publisher) from book;
-- distinct 절 : 중복결과 제외

 

 

• 28일차 추가 풀어보기

-- 23. 고객, 주문 테이블 조인하여 고객번호 순으로 정렬
select * from customer c , orders o 
		where o.customer_id = c.id 
			order by c.id asc;

select * from customer c 
		inner join orders o 
			on c.id = o.customer_id;

inner join으로 참조관계로 설정하고 on 으로 바로 비교 가능하다

 

 

-- 31. 책을 구매한 이력이 있는 고객의 이름을 조회
select c_name from customer c , orders o 
		where c.id = o.customer_id 
			group by c_name;
            
select c_name from customer 
	where id in(select customer_id from orders);

내가 적은 방법으로도 가능하지만

선생님이 적은 방법으로 하는게 더 짧고 실행 속도가 더 빠르다

select customer_id from orders where id in (1,2,3,4);를 서브쿼리로 사용하였다

 

 

-- 32. 도서의 가격(PRICE)과 판매가격(SALEPRICE)의 차이가 가장 많이 나는 주문 조회 
select * from book b , orders o  
	where o.o_saleprice = (select max(b_price-o_saleprice) from orders) 
		and o.book_id = b.id;
            
select * from book b , orders o where b.id=o.book_id
		and b.b_price - o.o_saleprice =
			(select max(b.b_price - o.o_saleprice) from book b, orders o
				where b.id = o.book_id);
                    
                    


-- 김씨 성을 가진 고객을 따로 분리하고 그 고객이 주문한 총 판매액 조회
select sum(o_saleprice) from orders where customer_id 
	in(select id from customer where c_name like '김%');


-- 35. 김씨 성을 가진 고객이 주문한 총 판매액 조회
select c.c_name , sum(o_saleprice) from orders o , customer c 
	where o.customer_id = c.id 
		group by c.id 
			having c_name like '김%' 
				and (select sum(o_saleprice) from orders);

 

 

• 테이블 구조 변경 - alter

-- 기존 컬럼에 제약조건 추가
alter table student add constraint primary key(id);
-- 기존 컬럼 타입 변경
alter table student modify s_mobile varchar(30);
-- 새로운 컬럼 추가
alter table student add s_major varchar(30);
-- 컬럼 이름 변경
alter table student change s_mobile s_phone varchar(30);
-- 컬럼 삭제
alter table student drop s_major;

 

 

• ERD보고 table 만들어보기

< 조건 >

1. 회원 탈퇴시 게시글, 댓글, 좋아요 내역도 함께 삭제됨 
2. 게시글 삭제시 댓글, 첨부파일도 함께 삭제됨 
3. 카테고리 삭제시 게시글은 삭제되지 않고 카테고리 부분만 null로 처리됨

 

 

create table board_table(
	id bigint auto_increment primary key,
	board_title varchar(50) not null,
	board_writer varchar(20) not null,
	board_contents varchar(500),
	board_hits int,
	board_created_time datetime default now(),
	board_update_time datetime on update now(),
	-- 업데이트가 발생 했을 때 , 현재시간을 기록해라
	board_file_attached int default 0, 
	-- 파일 첨부 여부 (없으면 0 , 있으면 1)
	member_id bigint,
	category_id bigint,
	constraint fk_member_table foreign key(member_id) 
				references member_table(id)on delete cascade,
	constraint fk_category_table foreign key(category_id) 
				references category_table(id)on delete set null
);

 

 

create table board_file_table(
	id bigint auto_increment primary key,
	original_file_name varchar(100),
    -- 사용자가 업로드한 파일의 이름
	stored_file_name varchar(100),
    -- 관리용 파일 이름(파일이름 생성 로직은 backend에서)
	board_id bigint,
	constraint fk_board_table foreign key(board_id) 
				references board_table(id)on delete cascade
);

 

 

create table category_table(
	id bigint auto_increment,
	category_name varchar(20) not null unique,
	constraint pk_create_table primary key(id)
);

 

 

create table comment_table(
	id bigint auto_increment primary key,
	comment_writer varchar(20)not null,
	comment_contents varchar(200)not null,
	comment_created_time datetime default now(),
	board_id bigint,
	member_id bigint,
	constraint fk_comment_board_talbe foreign key(board_id) 
				references board_table(id) on delete cascade,
	constraint fk_comment_member_tabe foreign key(member_id) 
				references member_table(id)on delete cascade
);

 

 

create table member_table(
	id bigint auto_increment,
	member_email varchar(50) not null unique,
	member_name varchar(20) not null unique,
	member_password varchar(20) not null,
	constraint pk_member_table primary key(id)
);

 

 

create table good_table(
	id bigint auto_increment primary key,
	comment_id bigint,
	member_id bigint,
	constraint fk_good_comment foreign key(comment_id) 
				references comment_table(id),
	constraint fk_good_member foreign key(member_id) 
				references member_table(id)on delete cascade
);

 

 

• 결과 짠!

 

• ERD보는 법