21.09.29 - 웹 개발 입문 33일차
데이터베이스 - 집합연산
집합연산
= 서로 다른 집합 사이의 합집합, 교집합, 차집합 등을 구하는 과정
= 연산을 위해서는 2개의 서로 다른 결과집합이 필요
= 두 집합의 컬럼이 같아야 한다
- UNION ALL : 중복을 따지지 않고 합집합 처리
- UNION : 중복을 제거하고 합집합 처리
- INTERSERCT : 교집합 처리
- MINUS : 차집합 처리
(참고 : 서브쿼리는 ☆순차적 실행☆, 집합연산은 아니다)
(ex) 피카츄, 파이리가 응시한 과목
-- 합집합(UNION ALL / UNION) : 파이리랑 피카츄가 응시한 모든 과목
select subject from exam where student = '피카츄'
union all
select subject from exam where student = '파이리';
select subject from exam where student = '피카츄'
union
select subject from exam where student = '파이리';
교집합(INTERSECT) : 파이리와 피카츄가 둘 다 응시한 과목
select subject from exam where student = '피카츄'
intersect
select subject from exam where student = '파이리';
차집합(MINUS)
select subject from exam where student = '피카츄'
minus
select subject from exam where student = '파이리';
데이터베이스 - 테이블 분할과 외래키 생성
(ex) 장바구니 구현
장바구니란 회원이 상품을 보관하는 것
개체(Entity) : 회원(cucstomer), 상품(item)
관계(Relation) : 보관한다
create table customer(
customer_id varchar2(20) primary key,
customer_pw varchar2(16) not null,
customer_nick varchar2(30) not null unique,
customer_point number
);
create table item(
item_no number primary key,
item_name varchar2(60) not null,
item_price number
);
insert into customer values ('aaa', 'aaa', '테스트1', 500);
insert into customer values ('bbb', 'bbb', '테스트2', 2000);
insert into item values (1, '헤드셋', 50000);
insert into item values (2, '노트북', 2000000);
commit;create table basket(
basket_code number primary key,
customer_id varchar2(20) not null,
item_no number not null,
item_quantity number,
add_time date
);
create sequence basket_seq;
commit;insert into customer values ('aaa', 'aaa', '테스트1', 500);
insert into customer values ('bbb', 'bbb', '테스트2', 2000);
insert into item values (1, '헤드셋', 50000);
insert into item values (2, '노트북', 2000000);
insert into basket values(basket_seq.nextval, 'aaa', 1, 1, sysdate);
insert into basket values(basket_seq.nextval, 'aaa', 2, 1, sysdate);
insert into basket values(basket_seq.nextval, 'bbb', 1, 1, sysdate);
insert into basket values(basket_seq.nextval, 'ddd', 5, 2, sysdate); --존재하지 않는 회원과 상품
commit;
--> 외래키를 설정하지 않고 장바구니를 생성
문제점 : 존재하지 않는 회원이나 존재하지 않는 상품도 등록이 가능하다
해결책 : 추가 시 대상 테이블에 존재하는지 확인하고 추가하도록 설정(외래키 설정)
외래키를 설정하여 테이블을 재생성
생성법 : 컬럼명 우측에 references 테이블명(항목명) [옵션] 형태로 작성
drop table basket;
create table basket(
basket_code number primary key,
customer_id references customer(customer_id),
item_no references item(item_no),
item_quantity number,
add_time date
);
찜(Wishlist) 테이블 추가
한명의 손님이 하나의 상품을 한번만 좋아요 할 수 있다.
손님이 탈퇴하면 찜내역은 사라져야 한다(on delete cascade).
상품이 삭제되면 찜내역은 사라져야 한다(on delete cascade).
drop table wishlist;
create table wishlist(
customer_id references customer(customer_id) on delete cascade,
item_no references item(item_no) on delete cascade,
primary key (customer_id, item_no)
);
insert into wishlist values('aaa', 1); --모두 존재해서 됨
insert into wishlist values('aaa', 5); --5번 상품이 없어서 안됨
insert into wishlist values('abc', 1); --abc 회원이 없어서 안됨
결제(Payment) 테이블 추가
한명의 손님이 하나의 상품을 여러 번 결제할 수 있다. 복합키 사용 불가. 따라서 기본키 추가 생성
회원이 탈퇴한다고 해서 결제내역까지 사라지는것은 안된다(on delete set null)
상품이 삭제된다고 해서 결제내역까지 사라지는것은 안된다(on delete set null)
(주의) not null 항목에는 적용이 불가 (on delete set null)
create table payment(
payment_code number primary key,
customer references customer(customer_id) on delete set null,
item references item(item_no) on delete set null,
payment_type char(6) not null check(payment_type in ('카드', '현금')),
payment_amount number not null check(payment_amount >=0 ),
payment_time date default sysdate not null
);
--> 외래키는 추가도 문제지만 삭제도 고려해야 한다.
(ex) 회원이 탈퇴하면 장바구니는 어떻게? 찜내역은? 구매내역은?
외래키에 옵션을 지정하여 해결해야 한다
옵션 미지정 : 연결된 자식 항목(child record)이 있을 경우 부모 키는 삭제가 불가
on delete cascade : 부모 키를 삭제하면 연결된 자식 항목은 자동 삭제(ex : 탈퇴한 회원이 찜내역)
on delete set null : 부모 키를 삭제하면 연결된 자식 항목이 null로 변경(ex : 탈퇴한 회원의 게시글)
Q. SNS Follow System 구현
Follow란 회원이 다른 회원을 관심회원으로 설정
개체(Entity) : 회원(client) - 아이디, 비밀번호, 닉네임
관계(Relation) : 팔로우 - 누가 누구를 팔로우했는지에 대한 정보
create table client(
client_id varchar2(20) primary key,
client_pw varchar2(16) not null,
client_nick varchar2(30) not null unique
);create table follow(
who references client(client_id) not null,
target references client(client_id) not null,
primary key(who, target)
);
동일 유저 follow 방지조건
테이블 생성시에는 다른 컬럼 참조 불가하므로 생성 후에 별도로 제약조건을 추가
제약조건명 : same_user , 제약조건 : check(who != target)
alter table follow add constraint same_user_block check(who != target);
정보 입력
insert into client values('aaa', 'aaa', 'a');
insert into client values('bbb', 'bbb', 'b');
insert into client values('ccc', 'ccc', 'c');insert into follow values('aaa', 'bbb');
insert into follow values('ccc', 'aaa');
insert into follow values('bbb', 'aaa');
insert into follow values('bbb', 'ccc');
insert into follow values('ccc', 'ccc');--same_user_block 조건에 의해서 차단
데이터베이스 - 수정, 삭제
데이터 수정(Update)
형식 : update 테이블이름 set 항목변경내용 [where 필터식]
스크류바의 가격을 1300원으로 변경
update product set price = 1300; --전체를 다바꿈
update product set price = 1300 where name = '스크류바';
스크류바의 가격을 100원 인상
update product set price = price +100 where name = '스크류바';
7번 상품의 정보를 다음과 같이 변경
이름 : 테라, 분류 : 주류, 가격 : 2200원
update product set name = '테라', type ='주류', price = 2200 where no=7 ;
멘토스의 가격을 10% 인상
update product set price = price * 1.1 where name = '멘토스' ;
update product set price = floor(price * 1.1) where name = '멘토스' ; -- 소수점없애기
오레오의 가격을 500원 할인
update product set price = price-500 where name = '오레오' ;
데이터 삭제(Delete)
형식 : delete 테이블이름 [where 필터식]
(주의) 삭제는 대부분 Primary key를 이용하여 진행된다.
전체 삭제
delete product;
아이스크림 삭제
delete product where type = '아이스크림' ;
제일 비싼 상품 삭제
delete product where price = (select max(price) from product)
기본 명령어 정리
| DDL(Data Definition Language) | ||
| 구분 | 명령어 | 내용 |
| 생성 | CREATE | 데이터베이스 오브젝트 생성 |
| 변경 | ALTER | 데이터베이스 오브젝트 변경 |
| 삭제 | DROP | 데이터베이스 오브젝트 삭제 |
| TRUNCATE | 데이터베이스 오브젝트 내용 삭제 | |
| DML(Data Manipulation Language) | ||
| 구분 | 명령어 | 내용 |
| 데이터 생성 | INSERT | 삽입 형태로 신규 데이터를 테이블에 저장 |
| 데이터 조회 | SELECT | 테이블의 내용을 조회 |
| 데이터 변경 | UPDATE | 테이블의 내용을 변경 |
| 데이터 삭제 | DELETE | 테이블의 내용을 삭제 |
| DCL(Data Control Language) | ||
| 유형 | 명령어 | 용도 |
| DCL | GRANT | 데이터베이스 사용자 권한 부여 |
| REVOKE | 데이터베이스 사용자 권한 회수 | |
| TCL | COMMIT | 트랜잭션 확정 |
| ROLLBACK | 트랜잭션 취소 | |
| CHECKPOINT | 복귀지점설정 | |
데이스베이스 - 테이블 조인(TABLE JOIN)
테이블 조인(Table Join)
= 조회할 때 여러 테이블들 합쳐서 조회하는 기술
= 목적에 따라서 여러 종류의 join이 존재한다.
= inner join 과 outer join을 구분하는 것을 목표로 진행
basket과 item을 join
연결된 항목끼리 이어서join (INNER JOIN, 내부 조인)
select * from basket inner join item on basket.item_no = item.item_no;
basket과 customer를 join
select * from basket inner join customer on basket.customer_id = customer.customer_id;
basket과 customer, item을 join
select * from basket inner join customer on basket.customer_id = customer.customer_id
inner join item on basket.item_no = item.item_no;
follow와 client를 join (follow + client + client)
select * from follow inner join client on follow.who = client.client_id inner join client on follow.target = client.client_id;
반복되는 테이블 이름 대신 별칭을 부여
select * from follow F
inner join client WHO on F.who = WHO.client_id
inner join client TARGET on F.target = TARGET.client_id;
와일드카드(*) 사용을 피하고 가급적 항목을 지정하고 별칭을 부여하여 조회
select WHO.client_nick "팔로워닉네임", TARGET.client_id "팔로위ID",
TARGET.client_nick "팔로위닉네임" from follow F
inner join client WHO on F.who = WHO.client_id
inner join client TARGET on F.target = TARGET.client_id;
HR계정 사용하기
오라클에는 HR이라는 연습 계정이 있다
이 계정은 최초에는 잠김 상태이므로 잠금을 풀어주고 비빌번호 변경후 HR계정으로 로그인
alter user hr account unlock;
alter user hr identified by hr;
1. 모든 대륙(REGIONS)에 있는 국가(COUNTRIES) 정보를 같이 조회하여 출력
select R.*, C.country_name from regions R inner join countries C on R.region_id = C.region_id;
2. 국가(COUNTRIES)에 존재하는 지역(LOCATIONS) 정보를 같이 조회하여 출력
select C.*, L.city from countries C inner join locations L on C.country_id = L.country_id;
3. 모든 지역(LOCATIONS)에 존재하는 부서(DEPARTMENTS) 정보를 같이 조회하여 출력
select L.city, d.department_name from locations L inner join departments D on L.location_id = D.location_id;