sprint - learn sql ( part3 ~ part5 )

이번 챕터는 환경 변수 생성과 .env 파일 생성 그리고 연동까지 이해가 안되서 3~4시간 정도 열심히 삽질했던 챕터다 ㅋㅋㅋ 다행히 지금은 잘 이해한 상태다~

• .env.example 파일을 확인하고, .env 파일을 생성하세요.
  
  

VS Code에서 .env.example 파일을 .env 파일로 변경하면 된다 ( 파일명 변경하면 알아서 형식도 변경된다 )

1. learnmysql 이라는 이름으로 데이터베이스를 만드세요. 이 스프린트에서는 데이터베이스 learnmysql 를 사용합니다.
   
   

CLI에서 mysql 로그인 후 

CREATE DATABASE learnmysql;

2. lib/common/mysql.js 에서 process.env 변수가 어떻게 사용되는지 확인하세요.
   
   

process.env는 .env ( linux OS에 선언한 환경변수 ) 에 담긴 데이터를 불러오는 역할을 한다. 

.env 파일에는 DATABASE 정보가 담겨있다. 

즉, mysql.js에서는 .env파일을 process.env로 연동시키는 작업을 진행하는 것이다.

3. dotenv 문서를 확인하거나, dotenv를 검색해보세요.
   
   

https://www.npmjs.com/package/dotenv

dotenv.config(); // .env 파일 인식하고 환경변수 세팅 

--- dotenv는 .env파일에서 불러온 후 process.env로 로드하는 의존성 모듈이다.

4. mysql 문서를 확인하거나, Node.js MySQL with w3school을 통해 Node.js환경에서 데이터베이스에 어떻게 연결하는지 확인하세요.
   
   https://www.w3schools.com/nodejs/nodejs_mysql.asp
   https://github.com/mysqljs/mysql
   
   
   

1. CLI를 통해 mysql에 접속 후 linux OS 커널예 원하는 DB를 생성한 후 

2. VS코드로 커널단의 DB 정보를 불러오기 위한 .env 파일을 생성

---- 가령 id, password, name이 담긴 .env 파일 생성 후

3. mysql.js 파일 생성하여 

전역변수로 

const dotenv = require('dotenv');
const mysql = require('mysql'); // 당연히 dotenv 모듈과 mysql 모듈을 install 해야 한다

선언 이후 

dotenv.config()를 선언한 후 // .env 파일을 인식하고 환경변수를 세팅한다

4. process.env.DATABASE_USERNAME || 'root' , ...로 DB와 연동한다 

// dotenv.config()로 .env파일과 연결을 시켜놓고 process.env로 담긴 데이터를 불러온다



정리하면 cli로 mysql DB를 생성한 후 .env파일에 DB 정보를 등록하고 mysql.js에서 dotenv.config(), process.env로 연동을 한다. 

잊지 말 것은 .gitignore 파일을 생성 후 .env 를 등록해주는 것이다

5. 다음 질문에 대해 고민하고, 페어와 토론하세요.

• .gitignore 에 .env 파일이 추가되어 있는 이유가 무엇일까요?
  
  

git에 등록할 때 보안과 관련된 .env 파일을 누락시키기 위해

• .env.example 파일을 제공하는 이유가 뭘까요?
  
  

팀과 함께 개발 중인 경우 응용프로그램에 .env.example 파일을 사용할 경우, 예제 구성 파일에 place holder (' ') 값을 입력하면 팀의 다른 개발자가 응용프로그램을 실행하는 데 필요한 환경 변수를 명확하게 확인할 수 있습니다.

https://laravel.com/docs/5.6/configuration#environment-configuration

https://stackoverflow.com/questions/52084447/what-is-the-need-for-env-and-env-example-files-in-laravel

migrations / schema.sql

/* DO NOT TOUCH OR CHANGE BELOW SQL STATEMENT */
USE learnmysql;

/* DESIGN SCHEMA FOR REQUIREMENT */

-- PART 3:
-- 여기 user 테이블 생성 SQL 구문이 있습니다. user 테이블을 참고해서, content 테이블 생성 SQL 구문을 완성하세요.

CREATE TABLE `user` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) not NULL,
  `email` varchar(255) not NULL
);

CREATE TABLE `content` (
  -- TODO: `id`
  -- TODO: `title`
  -- TODO: `body`

  `id` int PRIMARY KEY AUTO_INCREMENT,
  `title` varchar (255) not NULL,
  `body` varchar (255) not NULL,
  `created_at` timestamp not NULL DEFAULT CURRENT_TIMESTAMP,
  `userId` int,
  FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
);

-- PART 5:
-- 아래 주석을 제거하고 category, content_category, role 테이블을 만드세요.


CREATE TABLE `category` (
  -- TODO:
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) not NULL
);


CREATE TABLE `content_category` (
  -- TODO:
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `contentId` int not NULL,
  FOREIGN KEY (`contentId`) REFERENCES `content` (`id`),
  `categoryId` int not NULL,
  FOREIGN KEY (`categoryId`) REFERENCES `category` (`id`)
);


CREATE TABLE `role` (
  -- TODO:
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) not NULL
);


ALTER TABLE `user` ADD roleId int;
ALTER TABLE `user` ADD FOREIGN KEY (`roleId`) REFERENCES `role` (`id`);

script / part3.js

/*
    TODO: Q 3-1. 현재 있는 데이터베이스에 존재하는 모든 테이블 정보를 보기위한 SQL을 작성해주세요.
*/
const PART3_1 = `SHOW TABLES`;

/*
    TODO: Q 3-2. user 테이블의 구조를 보기위한 SQL을 작성해주세요.
        - 요구사항에 맞는 user 테이블을 작성해야만, 테스트를 통과합니다.
*/
const PART3_2 = `DESCRIBE user`;

/*
    TODO: Q 3-3. content 테이블의 구조를 보기위한 SQL을 작성해주세요.
        - 요구사항에 맞는 content 테이블을 작성해야만, 테스트를 통과합니다.
*/
const PART3_3 = `DESCRIBE content`;

module.exports = { PART3_1, PART3_2, PART3_3 };

script / part4.js

    TODO: Q 4-1. user 테이블에 존재하는 모든 컬럼을 포함한 모든 데이터를 확인하기 위한 SQL을 작성해주세요.
*/
const PART4_1 = `SELECT * FROM user`;

/*
----------------------------------------------------------------------------------------------
    TODO: Q 4-2. user 테이블에 존재하는 모든 데이터에서 name 컬럼만을 확인하기 위한 SQL을 작성해주세요.
*/
const PART4_2 = `SELECT name FROM user`;

/*
----------------------------------------------------------------------------------------------
    TODO: Q 4-3. user 테이블에 데이터를 추가하기 위한 SQL을 작성해주세요.
        - 원하는 name, email을 사용하시면 됩니다.
*/
const PART4_3 = `INSERT INTO user (name, email) VALUES ('john', 'google.com')`;

/*
----------------------------------------------------------------------------------------------
    TODO: Q 4-4. user 테이블에서 특정 조건을 가진 데이터를 찾기위한 SQL을 작성해주세요.
        - 조건 : name이 duhyunkim이여야 합니다.
*/
const PART4_4 = `SELECT * FROM user WHERE name='duhyunkim'`;

/*
----------------------------------------------------------------------------------------------
    TODO: Q 4-5. user 테이블에서 특정 조건을 가진 데이터를 찾기위한 SQL을 작성해주세요.
        - 조건 : name이 duhyunkim이 아니여야 합니다.
*/
const PART4_5 = `SELECT * FROM user WHERE NOT name='duhyunkim'`;

/*
----------------------------------------------------------------------------------------------
    TODO: Q 4-6. content 테이블에 존재하는 모든 데이터에서 title 컬럼만을 찾기 위한 SQL을 작성해주세요.
*/
const PART4_6 = `SELECT title FROM content`;

/*
----------------------------------------------------------------------------------------------
    TODO: Q 4-7. content의 title과 그 컨텐츠를 작성한 user의 name을 찾기 위한 SQL을 작성해주세요.
        - 저자가 없더라도, 켄턴츠의 title을 모두 찾아야합니다.
*/
const PART4_7 = `Select title, name from content left join user on user.id = content.userid `; 

/*
----------------------------------------------------------------------------------------------
    TODO: Q 4-8. content의 title과 그 컨텐츠를 작성한 user의 name을 찾기 위한 SQL을 작성해주세요.
        - 저자가 있는 컨텐츠의 title만 찾아야합니다.
*/
const PART4_8 = `SELECT title, name FROM content JOIN user  On user.id = content.userid`;

/*
----------------------------------------------------------------------------------------------
    TODO: Q 4-9. content의 데이터를 수정하기 위한 SQL을 작성해주세요.
        - title이 database sprint인 content 데이터에서 body를 database is very easy로 수정해야합니다.
*/
const PART4_9 = `UPDATE content SET body='database is very easy' WHERE title='database sprint'`;

/*
----------------------------------------------------------------------------------------------
    TODO: Q 4-10. content의 데이터를 추가하기 위한 SQL을 작성해주세요.
        - duhyunkim이 작성한 컨텐츠를 추가해주세요. 제목과 본문은 자유입니다. (참고: duhyunkim의 아이디는 1입니다.)
*/
const PART4_10 = `INSERT INTO content(title, body, userID) VALUES ('good', 'good2', 1)`;

module.exports = {
  PART4_1,
  PART4_2,
  PART4_3,
  PART4_4,
  PART4_5,
  PART4_6,
  PART4_7,
  PART4_8,
  PART4_9,
  PART4_10,
};

script / part5.js

    TODO: Q 5-1-1. category 테이블의 구조를 보기위한 SQL을 작성해주세요.
        - 요구사항에 맞는 category 테이블을 작성해야만, 테스트를 통과합니다.
*/
const PART5_1_1 = `DESCRIBE category`;

/*
----------------------------------------------------------------------------------------------
    TODO: Q 5-1-2. content_category 테이블의 구조를 보기위한 SQL을 작성해주세요.
        - 요구사항에 맞는 content_category 테이블을 작성해야만, 테스트를 통과합니다.
*/
const PART5_1_2 = `DESCRIBE content_category`;

/*
----------------------------------------------------------------------------------------------
    TODO: Q 5-1-3. role 테이블의 구조를 보기위한 SQL을 작성해주세요.
        - 요구사항에 맞는 role 테이블을 작성해야만, 테스트를 통과합니다.
*/
const PART5_1_3 = `DESCRIBE role`;

/*
----------------------------------------------------------------------------------------------
    TODO: Q 5-1-4. user 테이블의 구조를 보기위한 SQL을 작성해주세요.
        - 요구사항에 맞는 user 테이블을 작성해야만, 테스트를 통과합니다.
*/
const PART5_1_4 = `DESCRIBE user`;

/*
----------------------------------------------------------------------------------------------
    TODO: Q 5-2-1. category 테이블에 존재하는 데이터에서 id, name을 찾는 SQL을 작성해주세요.
*/

const PART5_2_1 = `SELECT id, name FROM category`;

/*
----------------------------------------------------------------------------------------------
    TODO: Q 5-2-2. user의 name과 email 그리고 그 user가 속한 role name(컬럼명: roleName)을 찾기 위한 SQL을 작성해주세요.
        - 속한 role이 없더라도, user의 name과 email,role name을 모두 찾아야합니다.
*/
const PART5_2_2 = `SELECT user.name, email, role.name as roleName 
                    FROM user LEFT JOIN role ON user.roleId = role.id`;

/*
----------------------------------------------------------------------------------------------
    TODO: Q 5-2-3. 어느 role에도 속하지 않는 user의 모든 컬럼 데이터를 찾기위한 SQL을 작성해주세요.
*/
const PART5_2_3 = `SELECT * FROM user WHERE roleId IS NULL`;

/*
----------------------------------------------------------------------------------------------
    TODO: content_category 테이블의 의미를 테이블 관계로서 이해하고 있는지 점검해주세요. 질문자체가 이해가 안된다면, Help-Desk에 질문해주세요.
----------------------------------------------------------------------------------------------    
*/

/*
----------------------------------------------------------------------------------------------
    TODO: Q 5-2-4. content_category 테이블에 존재하는 모든 칼럼의 데이터를 찾기위한 SQL을 작성해주세요.
*/
const PART5_2_4 = `SELECT * FROM content_category`;

/*
----------------------------------------------------------------------------------------------
    TODO: Q 5-2-5. jiSungPark이 작성한 content의 title을 찾기위한 SQL을 작성해주세요.
*/
const PART5_2_5 = `SELECT title FROM content WHERE content.userID = (SELECT id FROM user WHERE user.name = 'jisungPark')`;

/*
----------------------------------------------------------------------------------------------
    TODO: Q 5-2-6. JiSungPark이 작성한 content의 category name을 찾기위한 SQL을 작성해주세요.
*/
const PART5_2_6 = `SELECT category.name FROM category
JOIN content_category ON category.id = content_category.categoryId
JOIN content ON content_category.contentId = content.id
JOIN user ON content.userId = user.id
WHERE user.name = 'jiSungPark'`; 

/*
----------------------------------------------------------------------------------------------
    TODO: Q 5-2-7. category의 name이 soccer인 content의 title, body, created_at을 찾기위한 SQL을 작성해주세요.
*/
const PART5_2_7 = `SELECT content.title, content.body, content.created_at FROM content
JOIN content_category ON content.id=content_category.contentId
JOIN category ON content_category.categoryId=category.id WHERE category.name='soccer'`;

/*
----------------------------------------------------------------------------------------------
    TODO: Q 5-2-8. category의 name이 soccer인 content의 title, body, created_at, user의 name을 찾기위한 SQL을 작성해주세요.
*/
const PART5_2_8 = `SELECT user.name, content.title, content.body, content.created_at FROM user
                    JOIN content ON user.id = content.userId
                    JOIN content_category ON content.id=content_category.contentId
                    JOIN category ON content_category.categoryId=category.id WHERE category.name='soccer'`;

/*
----------------------------------------------------------------------------------------------
    TODO: Q 5-2-9. duRiCha가 작성한 글의 개수 (컬럼명: ContentCount)를 출력하기 위한 SQL을 작성해주세요.
*/
const PART5_2_9 = `SELECT count(content.userId) as ContentCount 
                    FROM content 
                    JOIN user 
                    ON user.id = content.userId 
                    WHERE user.name = 'duRiCha'`;

/*
----------------------------------------------------------------------------------------------
    TODO: Q 5-2-10. 각 user(컬럼명: name)가 작성한 글의 개수 (컬럼명: ContentCount)를 출력하기 위한 SQL을 작성해주세요.
*/
const PART5_2_10 = `SELECT user.name as name, count(content.userId) as ContentCount FROM content RIGHT JOIN user ON user.id = content.userId GROUP BY user.name`; 

module.exports = {
  PART5_1_1,
  PART5_1_2,
  PART5_1_3,
  PART5_1_4,
  PART5_2_1,
  PART5_2_2,
  PART5_2_3,
  PART5_2_4,
  PART5_2_5,
  PART5_2_6,
  PART5_2_7,
  PART5_2_8,
  PART5_2_9,
  PART5_2_10,
};