EFUB_STUDY_DATABASE 추가과제

Song Chae Won·2023년 3월 25일

Database EFUB_STUDY_DATABASE mysql

DATABASE_STUDY

목록 보기

7/19

3주차 추가 과제

☑️ `passenger` 테이블 생성

🔻실습 캡쳐본

🔻데이터 추가 쿼리문

INSERT INTO `efub_week3`.`passenger` (`passenger_id`, `last_name`, `first_name`, `nationality`, `plane`) VALUES ('1', '김', '퍼비', '한국', '1');
INSERT INTO `efub_week3`.`passenger` (`passenger_id`, `last_name`, `first_name`, `nationality`, `plane`) VALUES ('2', 'Smith', 'Oliver', '호주', '4');
INSERT INTO `efub_week3`.`passenger` (`passenger_id`, `last_name`, `first_name`, `nationality`, `plane`) VALUES ('3', '송', '채원', '한국', '3');
INSERT INTO `efub_week3`.`passenger` (`passenger_id`, `last_name`, `first_name`, `nationality`, `plane`) VALUES ('4', '미야와키', '사쿠라', '일본', '1');
INSERT INTO `efub_week3`.`passenger` (`passenger_id`, `last_name`, `first_name`, `nationality`, `plane`) VALUES ('5', '이', '펍', '한국', '2');
INSERT INTO `efub_week3`.`passenger` (`passenger_id`, `last_name`, `first_name`, `nationality`, `plane`) VALUES ('6', 'Williams', 'Noah', '영국', '3');

☑️ `plane` 테이블 생성

🔻실습 캡쳐본

🔻데이터 추가 쿼리문

INSERT INTO `efub_week3`.`plane` (`plane_id`, `departure`, `arrival`, `departure_time`, `gate`, `meal`) VALUES ('1', '서울', '로마', '2023-03-30 12:10:00', '57', '1');
INSERT INTO `efub_week3`.`plane` (`plane_id`, `departure`, `arrival`, `departure_time`, `gate`, `meal`) VALUES ('2', '서울', '오사카', '2023-04-14 09:35:00', '9', '0');
INSERT INTO `efub_week3`.`plane` (`plane_id`, `departure`, `arrival`, `departure_time`, `gate`, `meal`) VALUES ('3', '런던', '파리', '2023-07-02 19:00:00', '26', '0');
INSERT INTO `efub_week3`.`plane` (`plane_id`, `departure`, `arrival`, `departure_time`, `gate`, `meal`) VALUES ('4', '시드니', '뉴욕', '2023-12-24 14:55:00', '144', '1');

⭐ JOIN

SELECT + [표시하고 싶은 애트리뷰트] + FROM + [테이블1] + LEFT JOIN + [테이블2] + ON + [두 개의 테이블에서 공통으로 갖고 있는 컬럼]

mysql> SELECT passenger_id, last_name, first_name, nationality, departure, arrival, departure_time, gate, meal FROM passenger LEFT JOIN plane ON passenger.plane = plane.plane_id;