[4주차] SQL 기초 (JOIN, CREATE, ALTER, DROP)

1. JOIN 검색

 - JOIN 검색 :: 여러 개의 테이블을 연결해 데이터를 검색하는 것
 - FROM 절에 검색에 필요한 모든 테이블을 나열하고, WHERE 절에 조인 속성의 값이 같아야 함을 의미하는 조인 조건 제시
 - 파스타집.주문고객, 일식집.주문고객 처럼 같은 속성의 값이 중복될 수 있으니 테이블 이름도 같이 표시함.
 
- 카티션 프로덕트 :: 한 테이블의 행을 다른 테이블 행에 연결해 두 개 이상의 테이블을 결합한 것 (별로 안 좋은 방법이다. 비추천!)

질의#22 : 고객과 고객의 주문에 관한 데이터를 모두 나타내세요

질의#23 :: 고객과 고객의 주문에 관한 데이터를 고객별로 정렬하여 나타내세요

질의#25 : 고객별로 주문한 모든 도서의 총판매액을 구하고, 고객별로 정렬하세요

질의#26 : 고객의 이름과 고객이 주문한 도서의 이름을 구하세요
 - 세 개 이상의 테이블을 이용할 수도 있다!

 
질의#27: 가격이 20,000원인 도서를 주문한 고객의 이름과 도서의 이름을 구하세요.
 - from customer c, order o, book b 처럼 튜플변수를 제기하면 질의문 작성이 편해진다! (customer.custid -> c.custid)

1.1. 외부 조인

 - 조인 조건을 만족하지 않는 튜플에 대해서 검색을 수행함
 - LEFT, RIGHT, FULL OUTER JOIN, ON 키워드를 이용해 작성
 
질의#28: 도서를 구매하지 않은 고객을 포함하여 고객의 이름과 주문한 도서의 판매가격을 구하세요.
 - 왼쪽외부조인을 이용하면 아래와 같이 풀 수 있다. 물론 오른쪽 외부 조인도 사용 가능.

1.2. 부속질의

 - SELECT 문 안에 또 다른 SELECT 문을 포함하는 질의로, 괄호로 묶어서 작성
 - ORDER BY 절을 사용할 수 없음.
 - 부속 질의문과 상의 질의문을 연결하는 연산자가 필요함.

연산자

 
질의#29: 가장 비싼 도서의 이름을 나타내세요.

질의#30: 도서를 구매한 적이 있는 고객의 이름을 검색하세요.

질의#31: 대한미디어에서 출판한 도서를 구매한 고객의 이름을 나타내세요.

1.3. 상관 (Correlated, 연결) 부속질의 

 - 상위 쿼리와 하위 쿼리는 서로 의존적 -> 상위 쿼리의 특정 행 값이 하위 쿼리 조건에 사용
 - 행 단위로 반복 실행됨
 
질의#32: 출판사별로 출판사의 평균 도서 가격보다 비싼 도서를 구하세요. (상관 부속질의)

질의#33: 대한민국에 거주하는 고객의 이름과 도서를 주문한 고객의 이름을 나타내세요
 - UNION 집합 연산자(합집합!)를 활용할 수 있으며, UNION ALL은 중복을 포함해 모든 결과를 보여준다!

 - 이걸 고객의 이름을 제외하고 나타내거나, 거주 고객 중 도서 주문 고객의 이름을 보여주거나 식으로 변형한다면, 

 - MINUS, INTERSECT를 사용. 대신 MySQL에는 해당 연산자가 없으므로 NOT IN, IN을 사용한다.

 

질의#34: 주문이 있는 고객의 이름과 주소를 나타내세요.
 - EXISTS :: 부속질의문이 한 행이라도 반환하면 True (NOT EXISTS는 반대 경우가 True)

2. CREATE, ALTER, DROP

2.1. CREATE TABLE

 - 테이블을 구성하는 명령어로, 속성과 속성에 관한 제약 및 기본키, 외래키 등을 정의함.
 
질의#35 & #36: 특정 속성을 가진 NewCustomer, NewBook 등의 테이블을 생성하세요.
 - NULL 값 정의 및 더 복잡한 조건도 지정할 수 있음
 - ON DELETE CASCADE :: 관련 튜플을 함께 삭제

2.2. ALTER TABLE

 - 생성된 테이블의 속성 및 속성에 관한 제약, 기본키 및 외래키를 변경하는 명령어
 
질의#37 ~ #41: alter 명령어에 대한 실습
 - 아래는 newbook 테이블에 varchar(13)를 지정할 수 있는 isbn을 추가하는 명령어다!

2.3. DROP TABLE

 - 테이블을 삭제하는 명령 (구조와 데이터를 모두 삭제함)
 - 데이터만 삭제하려면 DELETE를 사용
 - 다만 삭제할 테이블을 참조하는 테이블이 있다면 해당 테이블을 참조하는 테이블부터 삭제하거나, 관련 외래키 제약조건을 먼저 삭제해야 삭제가 됨

 

질의#42: NewCustomer 테이블을 삭제하세요.

DROP TABLE NewCustomer;

 - 만약 NewOrder 테이블이 NewCustomer를 참조한다면 DROP TABLE NewOrders;로 미리 삭제해야 한다!

 

3. 데이터 조작어 (삽입, 수정, 삭제)

3.1. INSERT 

 - 테이블에 새로운 튜플을 삽입하는 명령어
 
질의#44: Book 테이블에 새로운 도서 '스포츠 의학'(출판사: 한솔의학서적, 가격: 90000)을 삽입하세요.
 - INTO 키워드의 속성은 생략 가능.

질의#45: Book 테이블에 새로운 도서 '스포츠 의학'을 삽입하세요. 단, 가격은 미정입니다.
 - 몇 개의 속성만 입력해야 한다면 해당 속성만 입력해도 되며, 지정하지 않은 값은 NULL로 저장됨.

 

질의#46: 수입도서테이블(Imported_book)에 있는 모든 목록을 Book 테이블에 모두 삽입하세요.

수입도서목록

수입도서목록의 모든 책 목록이 book 테이블로 이동되었다!

3.2. UPDATE

 - 특정 속성값을 수정하는 명령으로, 다른 테이블의 속성값을 이용할 수도 있음.
 - SET 키워드 다음에 속성 값을 어떻게 수정할 것인지를 지정하여 WHERE절 조건을 만족하는 튜플만 수정.
 
질의#47: Customer 테이블에서 고객번호가 5인 고객의 주소를 '대한민국 부산'으로 변경하세요.

질의#48: Book테이블에서 14번 '스포츠 의학'의 출판사를 Imported_book 테이블에 있는 21번 책의 출판사와 동일하게 변경하세요.

3.3. DELETE

 - 테이블에 있는 기존 튜플을 삭제하는 명령
 - WHERE 절에 제시한 조건을 만족하는 튜플만 삭제
 
질의#49: Book 테이블에서 도서번호가 11인 도서를 삭제하세요.

11번 스포츠의학 책이 사라진 것을 확인할 수 있다!

 

4. SQL 연습문제 풀이

1) SQL1
- 고양이와 강아지 순서대로 두 동물의 이름을 정렬해 표시하는 것이 문제였다.
- animal_ins 데이터베이스를 선택(from)하고, group by로 묶어 cat>dog 순으로 출력한다.
- select 문이 맨 마지막에 실행되면서 animal_type과 animal_type의 각 수를 센 것을 출력한다.

2) SQL2
- ins db를 불러와서, outs db와 이를 left join을 이용해(bd 안의 변수 이름이 달라 헷갈리기때문에  animal_outs 를 outs로 가져와 비교한다.
- imit 3으로 정렬 후에 3개만 표시하도록 제안도 둔다.

3) SQL3
- 이중 select문을 통해 헤비 유저가 등록한 집을 모두 출력할 것이다!. group by로 유저 아이디를 묶고 having count(id) >= 2를 사용하여 그 수가 2개 이상인 것들만 더한다.

4) SQL4
- ifnull 조건을 이용해서 null인 값이 none이라는 특정 문자열로 표시되도록 설정하고, 해당 행의 이름은 as문을 이용해 tlno로 정한다. where 조건에서 and를 이용해 12시 이하이고 여자인 사람으로 필터링을 한다. order by에 두 가지 조건을 지정해서 우선 age 내림차순으로 정렬되듀록 하고, 같은 경우엔 환자의 이름을 오름차순으로 정렬되도록 한다.

5) SQL5
- join을 이용해 두 테이블을 연결하고, like 절을 이용해서 intact로 시작하는지(들어올때 중성화가 안되어있을 것, intact male과 intact female로 두 가지 경우가 있느므로 더 길게 쓸 수 있지만 %을 이용하면 더 짧게 쓸 수 있다!) intact 상태가 아닌지 확인해서 해당되는 조건의 동물들을 출력한다!

6) 데이터베이스1
- or 문을 이용해 세 개의 skill 행들 중 하나라도 python이 존재한다면 표시한다! 그리고 id로 정렬한당

7) 데이터베이스2
- as문을 이용해 결과 표시에 뜨는 값을 total_price로 설정하고, sum 함수를 이용해서 price의 전체 합을 구한다! where 문으로 legend인 것만 더하는 조건도 꼭 추가하기.