Ⅱ. 실습
1. Datagrip으로 RDS 접근하기
① DataGrip에서 +기호를 누른 후 Data Source > MySQL을 클릭한다.
② 이름은 MyDB라 하고 Host에는 RDS의 엔드포인트(RDS > 데이터베이스 > 인스턴스 클릭)를 넣어주면 되고 user와 password에는 각각 root와 솔정한 비밀번호를 넣어주면 된다. password 아래의 Database에는 우리가 설정한 초기데이터베이스 이름인 MyDB를 넣어야 한다. Test Connection을 눌러서 체크표시가 나오면 OK를 클릭한다.
2. ERD export해서 구축된 RDS에 반영하기
AqueryTool에 접속한 후 ERD 메뉴 > ERD 불러오기를 통해 지난 주차에 만든 ERD를 load한다. 그 후 ERD 메뉴 > 모든 테이블 생성 SQL 을 눌러 코드 파일을 다운받는다. MyDB에 우클릭 > New > Table을 클릭하고 하나하나 붙여 넣어준다.
(ERD를 통해 만든 SQL문은 아래의 링크에서 확인할 수 있다.)
>> 모든 테이블 생성 SQL문
특히, Order 테이블을 작성할 때에는 예약어라 식별자로 사용할 수 없다는 에러가 뜬다. Aquery Tool에서 Order를 Place_Order라는 이름으로 바꾼 후 다시 SQL을 문을 받아 테이블을 생성하였다. 그 결과 아래와 같이 테이블이 모두 생성되었다.
3. 접근된 RDS에 더미데이터 반영하기
더미데이터란 성능 테스트를 목적으로 DB에 입력하는 가짜 데이터를 의미한다. + 표시를 눌러 User테이블에 user1과 user2를 추가해보자. 추가가 완료된 이후에는 반드시 + 표시의 3칸 옆에 있는 submit 버튼을 눌러주어야 한다.
4. 접근된 RDS에 쿼리 작성하기
화면상단에 QL이라고 쓰여 있는 Jump To Query Console을 클릭하여 default console을 열어준다.
① USER 테이블에 더미데이터가 잘 추가되었는지 확인하는 쿼리
② user_id가 1인 user의 주소를 조회하는 쿼리
③ user1이 주문을 한 경우에만 user1의 주문정보(로그인 아이디, 등급, 주소, 전화번호)를 가져오는 쿼리
- 먼저 Place_Order테이블에 더미데이터를 넣어주자.
- 아래와 같이 쿼리를 작성하면 Place_Order테이블에 user1의 user_id인 1이 있을 때에만 주문과 관련한 필드의 정보가 조회된다.
④ user1의 password를 1234로 바꾸는 쿼리
- 위와 같이 작성한 쿼리를 실행한 후 User 테이블을 새로고침하면 아래와 같은 결과가 나온다.
⑤ 더미데이터 user3을 추가하는 쿼리
- default값이 있는 경우와 Null이 가능한 경우는 모두 비우고, 필수로 입력해야 하는 모든 필드에 값을 넣었다. User테이블을 새로고침하면 아래의 추가 데이터를 확인할 수 있다.
⑥ Order By를 이용한 쿼리
- user_id를 기준으로 내림차순 정렬한 결과를 출력한다.
5. SQL 실습
1) Alias
Alias란 별칭이라는 쿼리의 가독성 향상을 위해 사용한다. 일반적으로 알아보기 어렵거나 긴 컬럼명이나 테이블명에 별칭을 붙인다. 별칭을 붙이는 방법은 테이블 또는 컬럼 이름 뒤에 한 칸 띄고 이름을 지어주면 된다. 아래의 예시를 보자.
위 코드에서 에러가 발생하는 이유는 무엇일까? 두 가지 컬럼이 각각 다른 테이블에 있기 때문이다. 보통의 생각으로는 user_id는 User테이블에 있고 order_id는 Place_Order테이블에 있으니 순서만 맞춰주면 되지 않을까 생각하겠지만, 그렇지 않다. 컬럼명은 여러 개를 적을 수 있고 두 테이블에서 각각 하나씩만 가져오란 법은 없다. 따라서 어떤 테이블의 컬림인지를 명시해주어야 한다. 명시하는 방법은 아래와 같다.
이번에는 아무런 문제없이 잘 실행이 된다. 하지만, 코드가 너무 길어져 가독성이 떨어진다. 아래와 같이 Alias를 적용하여 코드의 가독성을 높일 수 있다.
이번에도 역시 잘 실행이 된다. User는 첫번째 테이블이라는 뜻으로 T1이라는 별칭을 주었고 Place_Order테이블은 두번째 테이블이라는 뜻에서 T2라 이름하였다.
2) WHERE
눈치를 챘는지 모르겠지만, 위 코드에는 문제가 하나 있다. 바로 User테이블과 Place_Order테이블 간의 아무런 관계가 없다는 것이다. 위의 결과를 보면 알겠지만 아무런 규칙도, 관계도 없는 이상한 결과가 나왔다(관계형 DB의 장점을 전혀 이용하고 있지 않는 코드이다). 위와 같은 상황에서 WHERE을 이용하여 두 테이블을 연관시킬 수 있다. 예를 들어 User테이블의 user_id와 Place_Order의 user_id가 같은 경우의 user_id와 order_id를 조회해보기로 하자.
결과가 정상적으로 출력된다. 이번에는 user1의 주문 정보를 조회하는 쿼리를 작성해보자. 주문정보에는 order_id와 user_id, category, menu_id, menu_option_id, price가 포함되어야 한다. 먼저는 Order_Menu와 Menu테이블에 더미데이터를 추가해주자.
order_id 1이 menu_id가 1이면서 menu_option_id가 1인 10000원짜리 음식을 시킨 정보가 Order_Menu테이블에 저장된다. 또한 Menu테이블은 menu_id 1이 store_id가 1인 식당의 분식 카테고리의 떡볶이를 의미하고 있음을 알려준다.
Place_Order테이블로 주문한 사실이 입력되면 Place_Order테이블의 user_id와 같은 user_id를 User테이블에서 찾아 결과를 출력한다. 또한 Place_Order테이블의 order_id와 같은 order_id를 Order_Menu테이블에서 찾아 menu_id와 menu_option_id를 출력한다. 마지막으로 Order_Menu테이블의 menu_id와 같은 menu_id를 Menu테이블에서 찾아 category와 name을 출력한다. 어렵게 설명할 거 없이 WHERE문으로 테이블 간의 관계를 파악할 수 있다. 가령 예를 들면 T1과 T2는 user_id로 연결되어 있고 T2와 T3는 order_id로 연결되는 것이다. 참고로, price의 경우는 연산식을 포함해야 하므로 차후에 설명하기로 하겠다.
3) as
Alias가 개발자를 위한 별칭이라면, as는 사용자를 위한 별칭이라 할 수 있다. 위 쿼리의 결과를 보면 다소 사용자 친화적이지 못하다고 느낄 수 있다. 사용자가 쉽게 식별할 수 있도록 몇가지 이름만 바꿔주도록 하자.
name은 메뉴_이름으로 바꿔주었고, price는 가격으로 바꾸어주었다. 다른 필드도 동일하게 변경 가능하니 필요에 따라 직접 해보기 바란다. 참고로 띄어쓰기는 사용할 수 없으니 언더바를 적극 활용하자.
4) price계산
price를 제대로 계산하려면 사실 고려해야 할 것이 한 두가지가 아니다. 주문 금액이나 거리에 따라 배달 팁을 차등 적용해야 하고, 쿠폰의 적용 여부를 고려해야 하며, 옵션 추가나 수량에 따라 가격을 계산해야 한다. 단순한 쿼리 연습을 위해 이 모든 것을 다 고려하지는 않겠다.
원래라면 price * quantity로 total_price를 계산하는 쿼리를 짜고 싶었지만, 깜빡하고 Order_Menu 테이블에 Quantity 필드를 빼먹어버렸기 때문에 다른 예제를 실습해보기로 한다.
Menu_Option 테이블의 옵션을 추가하여 그만큼의 추가 금액을 total_price에 반영해보자. 먼저 Menu_Option테이블에 아래와 같이 더미데이터를 추가하자.
이제는 더 이상 price as 가격을 T3나 T4에서 가져오면 안 된다. (사실 위에 예제에서 T3에서 price를 가져오긴 했지만, 사실은 T4에서 가져오는 것이 더 적절하다. 위에서 price는 사실 고려대상이 아니었기에 아무데서나 대충 가져왔을 뿐이다.) 이제 price as 가격은 T4의 price에 T5(Menu_Option테이블)의 price를 더한 값을 출력해야 할 것이다. 이에 대해 SQL문을 작성해보면 아래와 같다.
T5는 T4와 menu_id로 연결된다. SQL문을 실행해보면, 가격이 잘 변경된 것을 확인할 수 있다. 만약 수량을 곱하는 예제였다고 하더라도, 더하기만 곱하기로 바꾸어 주면 되기 때문에 큰 어려움이 없을 것이다.
