Ⅱ. 실습
1. RDS 접속하기
1) EC2에서 CLI로 접속하기
① putty를 통해 EC2 서버에 원격 접속한다.
② 아래의 명령을 입력한다.
- 여기서는 username으로 root를 사용하였다.
- 이후 설정한 비밀번호를 입력한다.
mysql -u {username} -p -h {RDS 엔드포인트}③ 아래의 명령을 순차적으로 입력하여 DB와 테이블을 선택한다.
show databases;
use MyDB;
show tables;④ 수행하고자 하는 쿼리의 내용을 입력한다.
select * from user;2) Datagrip을 활용해 GUI로 접속하기
① DataGrip에서 +기호를 누른 후 Data Source > MySQL을 클릭한다.
② 아래와 같이 입력한다.
- Name에는 DB 이름인 MyDB를 입력하고, Host에는 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문을 실행해보면, 가격이 잘 변경된 것을 확인할 수 있다. 만약 수량을 곱하는 예제였다고 하더라도, 더하기만 곱하기로 바꾸어 주면 되기 때문에 큰 어려움이 없을 것이다.
