[SQL] 내가 들은 음악 결산 - 데이터 불러오기

 

📖참고
코드잇 | SQL 프로젝트 : 내가 들은 음악 결산

🔖 목차
1. [SQL] 내가 들은 음악 결산 - 실습 설명
2. [SQL] 내가 들은 음악 결산 - 데이터 불러오기

---

👩🏻‍💻데이터베이스 및 테이블 생성하기 (데이터 불러오기)

 

• music 데이터베이스 생성하고, 'music'데이터베이스 활성화 하기

CREATE DATABASE music;
USE music;

 

• 테이블을 music 데이터베이스에 탑재하기

 artists(아티스트) 

• artists 테이블 구조

컬럼	타입	설명
id	INT	순차적으로 부여된 정수형 아이디(Primary Key)
name	VARCHAR	아티스트 이름

• artists 테이블 생성하기

CREATE TABLE IF NOT EXISTS `artists` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(30) NOT NULL);

• 생성된  artists  테이블에 데이터 추가하기

# artists 데이터 일부
INSERT INTO artists (id, name) VALUES
(1, 'Carmen Johnson'),
(2, 'Lisa Fox'),
(3, 'Tammy Webb'),
(4, 'Amy Hancock'),
(5, 'Lindsay Mendoza');

---

 albums(앨범) 

• albums 테이블 구조

컬럼	타입	설명
id	INT	순차적으로 부여된 정수형 아이디(Primary Key)
title	VARCHAR	앨범 제목
release_date	DATE	앨범 발매일
artist_id	INT	아티스트 ID(artist 테이블의 id 컬럼과 참조 관계)

• albums 테이블 생성하기

CREATE TABLE IF NOT EXISTS `albums` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`title` VARCHAR(250) NOT NULL,
`release_date` DATETIME NOT NULL,
`artist_id` INT NOT NULL);

• 생성된  albums 테이블에 데이터 추가하기

# albums 데이터 일부
INSERT INTO albums (id, title, release_date, artist_id) VALUES
(1, 'She still', '2016-02-01', 123),
(2, 'Collection include evidence', '2021-01-08', 101),
(3, 'American throughout themselves', '2022-09-06', 26),
(4, 'Leg feel', '2018-02-28', 18),
(5, 'Red commercial', '2020-03-11', 93);

• foreign key 설정하기
  
  • foreign key : artist_id 열 ➡️ reference : artists 테이블의 id 열

ALTER TABLE `music`.`albums` 
ADD INDEX `pk_artist_id_idx` (`artist_id` ASC) VISIBLE;
;
ALTER TABLE `music`.`albums` 
ADD CONSTRAINT `pk_artist_id`
  FOREIGN KEY (`artist_id`)
  REFERENCES `music`.`artists` (`id`)
  ON DELETE RESTRICT
  ON UPDATE CASCADE;

---

 songs(곡) 

• songs 테이블 구조

컬럼	타입	설명
id	INT	순차적으로 부여된 정수형 아이디(Primary Key)
title	VARCHAR	곡 제목
duration_seconds	INT	곡 재생 시간 (초)
album_id	INT	앨범 ID(albums 테이블의 id 컬럼과 참조 관계)

• songs 테이블 생성하기

CREATE TABLE IF NOT EXISTS `songs` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`title` VARCHAR(250) NOT NULL,
`duration_seconds` INT NOT NULL,
`album_id` INT NOT NULL);

• songs 테이블에 데이터 추가하기

# songs 데이터 일부
INSERT INTO songs (id, title, duration_seconds, album_id) VALUES
(1, 'Court', 338, 164),
(2, 'Side relationship', 406, 362),
(3, 'Training include', 174, 228),
(4, 'Hour', 182, 372),
(5, 'Then morning', 155, 299);

• foreign key 설정하기
  
  • foreign key : album_id 열 ➡️ reference : albums 테이블의 id 열

ALTER TABLE `music`.`songs` 
ADD INDEX `pk_album_id_idx` (`album_id` ASC) VISIBLE;
;
ALTER TABLE `music`.`songs` 
ADD CONSTRAINT `pk_album_id`
  FOREIGN KEY (`album_id`)
  REFERENCES `music`.`albums` (`id`)
  ON DELETE RESTRICT
  ON UPDATE CASCADE;

---

 users(사용자) 

• users 테이블 구조

컬럼	타입	설명
id	INT	순차적으로 부여된 정수형 아이디(Primary Key)
username	VARCHAR	사용자 계정명
created_at	DATETIME	가입한 날짜 및 시간

• users 테이블 생성하기

CREATE TABLE IF NOT EXISTS `users` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`username` VARCHAR(30) NOT NULL,
`created_at` DATETIME NOT NULL);

• 생성된 users 테이블에 데이터 추가하기

# users 데이터 일부
INSERT INTO users (id, username, created_at) VALUES
(1, 'ljones', '2020-02-16 08:51:25'),
(2, 'alexanderbaker', '2020-03-05 06:16:03'),
(3, 'michaelacosta', '2020-03-23 19:44:23'),
(4, 'joshuadaniel', '2020-03-25 21:09:42'),
(5, 'rramirez', '2020-05-06 04:26:16');

---

 history(곡 재생 기록) 

• history 테이블 구조

컬럼	타입	설명
id	INT	순차적으로 부여된 정수형 아이디(Primary Key)
played_at	DATETIME	사용자가 곡을 재생한 날짜 및 시간
user_id	INT	사용자 ID(users 테이블의 id 컬럼과 참조 관계)
song_id	INT	곡 ID(songs 테이블의 id 컬럼과 참조 관계)

• history 테이블 생성하기

CREATE TABLE IF NOT EXISTS `history` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`played_at` DATETIME NOT NULL,
`user_id` INT NULL,
`song_id` INT NULL);

• history 테이블에 데이터 추가하기

# history 데이터 일부
INSERT INTO history (id, played_at, user_id, song_id) VALUES
(1, '2024-04-07 21:08:53', 12, 1611),
(2, '2024-11-26 09:28:54', 52, 4942),
(3, '2024-09-24 18:25:30', 68, 1155),
(4, '2024-06-10 07:26:45', 57, 1946),
(5, '2024-11-29 22:36:21', 84, 1156);

• foreign key 설정하기
  
  • foreign key : user_id 열➡️reference : users 테이블의 id 열
  • foreign key : song_id 열➡️reference : songs 테이블의 id
  • 사용자나, 곡에 대한 정보가 업데이트 될 때는 함께 history 테이블도 업데이트 되지만, 삭제될 때는 history에는 그대로 남겨두기

ALTER TABLE `music`.`history` 
CHANGE COLUMN `user_id` `user_id` INT NULL ,
CHANGE COLUMN `song_id` `song_id` INT NULL ,
ADD INDEX `pk_user_id_idx` (`user_id` ASC) VISIBLE,
ADD INDEX `pk_song_id_idx` (`song_id` ASC) VISIBLE;
;
ALTER TABLE `music`.`history` 
ADD CONSTRAINT `pk_user_id`
  FOREIGN KEY (`user_id`)
  REFERENCES `music`.`users` (`id`)
  ON DELETE SET NULL
  ON UPDATE CASCADE,
ADD CONSTRAINT `pk_song_id`
  FOREIGN KEY (`song_id`)
  REFERENCES `music`.`songs` (`id`)
  ON DELETE SET NULL
  ON UPDATE CASCADE;