AWS RDS 에서 MySQL Query 조작하기

msung99·2022년 10월 25일

이번 포스팅에서는 DataGrip 에서 Query 를 다뤄보는 방법에 대해 알아보겠습니다. AWS RDS 를 기반으로 하며, 이와 관련한 자세한 내용은 아래의 링크 (지난번 포스팅) 를 참고하시면 되겠습니다.

https://velog.io/@msung99/AWS-EC2-%EB%8F%84%EB%A9%94%EC%9D%B8%EA%B3%BC-IP-%EC%97%B0%EB%8F%99-https-%EC%A0%81%EC%9A%A9-MySQL-%EC%99%B8%EB%B6%80%EC%A0%91%EC%86%8D

QuickDBD 란?

ERD 설계 Tool인 QucikDBD 에서 expert를 하여 DDL(create 등)을 뽑아서 사용할 수 있다.

Quickdbd 는 ERD 툴줄에 하나로써, 이곳에서 작성한 ERD 들은 SQL 로써 자동으로 Export 가 가능합니다.

쉽게말해, QuickDBD 에서 만든 데이터베이스를 Export 버튼 하나만 누르면 SQL 파일이 자동으로 하나 생성됩니다! 그만큼 SQL 초기 생성이 정말 편해지겠죠~?

위와 같이 데이터베이스를 생성하고, Export 를 클릭하면 SQL 파일이 아래와 같이 생성되는 것을 확인할 수 있습니다.

AWS RDS MySQL 스키마 생성

지난 포스팅에서 AWS EC2 서버에 외부에서 접속할 수 있도록 연동을 해주는 test connection 과정을 진행했었습니다. 이에 이어서, 데이터베이스에 새로운 스키마를 생성할 수 있습니다.

새로운 Schema 를 생성하는 방벙은 아래 트러블 슈팅을 참고해주시길 바랍니다.

Export한 DDL Query 를 DB 에 적용하기

앞서 생성한 데이터베이스에 Query 를 적용시켜서 테이블에 대한 CRUD 가 가능합니다. 저는 practicedb 라는 DB 를 생성했고, 아래처럼 새로운 Query Console 을 생성하고 해당 콘솔에 Export 한 쿼리를 복사해주면 됩니다.

참고로 위 화면은 DDL Query 를 모두 실행시켜서 모든 테이블들이 생성된 결과입니다. 테이블 생성시 모든 쿼리문을 작동시키면 됩니다.

쿼리문 작성

간단히 아래와 같은 Query 문들을 작성해봤습니다. 아래 Query 들을 잘 모르신다면, 제 다음 포스팅을 참고하시길 권장드립니다.

https://velog.io/@msung99/MySQL-DDL-DML-DCL

회원가입 (User 생성)

INSERT INTO User(nickname, userId, userPassword, profileImgUrl, email)
VALUES ('eeemsung99', 'skwds7167', 'ihms9799', 'https://msung99_image.com', 'msung6924@naver.com');

2. 회원정보수정

UPDATE User
SET nickname = 'NEW_msung',
    userId = 'msung99',
    userPassword = 'msung6924',
    profileImgUrl = 'https://new_msung99.com'
WHERE userIdx = 1;
COMMIT;

3. 특정 유저에 대한 주문내역(Order) 리스트 조회

SELECT ordered_who as ordered_person, delivery_completed, delivery_number as ordered_number, delivery_location FROM `Order`
where ordered_who = 2 ;

4. 매뉴에 대한 스토어 가게들 리스트 나열 (ex. 치킨에 대한 Store 나열(BBQ 인하대점, BHC 김포점, ...)

SELECT * FROM Store
WHERE what_menu = 1;

5. 특정 가게(Store) 에 대한 상세정보 조회

SELECT * FROM Store
WHERE StoreIdx = 1;

6. 특정 가게(Store) 에 대한 상세정보 + 리뷰 "별점순(내림차순)" 조회

SELECT * FROM Review
WHERE Review.what_store = 1
ORDER BY star_rate desc ; # 오름차순은 "asc"

7. 특정 가게(Store) 에 대한 총 리뷰수 1 증가

UPDATE Store
SET review_count = review_count + 1
WHERE Store.StoreIdx = 1;

8. 배달 시간이 20분 이하인 스토어 조회

SELECT * FROM Store
WHERE delivery_time <= 20;

9. 리뷰 삭제

DELETE FROM Review WHERE Review.ReviewIdx = 1;

10. 주문하기

INSERT INTO `Order` (ordered_who, delivery_completed, delivery_number, delivery_location)
VALUES (1, 0, '00001111', 'inha_university');
INSERT INTO Food_buy (foodName, user_who_food, food_what, order_what)
VALUES ('gold_olive_chicken', 1, 2, 2);

트러블슈팅

1. schema 생성

schema 를 생성하기 위해서는 winscp 를 통해 mysql 에 모든 접근권한을 가진 계정(msung99) 로 접근해야 했다. 그런데 datagrip 에서 무식하게 스키마를 생성하려고 했었음..!

데이터베이스, 즉 schema 를 생성하기 위해서는 아래와 같이 msungdb 스키마를 생성했어야 했다.

mysql> create database msungdb
mysql> show databases;

그리고 아래처럼 해당 스키마(데이터베이스)에 대해 접근할 수 있는 권한을 부여했어야 했다!

mysql> grant all privileges on msungdb.* to 'msung99'@'%';
mysql> flush privileges;
mysql> show grants for 'msung99'@'%';    // 해당 사용자에 대한 권한을 보여줌

$ sudo service mysql restart // 재시작

이제 Apply 버튼을 눌러서 AWS EC2 인스턴스의 MySQL 정보들을 불러오면,
그럼 다음과 같이 나오는데 분명 앞서 msung99 사용자에 대해서 msungdb에 대한 권한을 전부 부여했는데,