[Database] im-sprint-learn-sql

윤태영 | Taeyoung Yoon·2022년 5월 16일
0

TIL (Today I Learned)

목록 보기
38/53
post-thumbnail

데이터베이스 생성

mysql -u root -p
mysql 진입

CREATE DATEBASE learnmysql
learnmysql 데이터베이스 생성

데이터베이스 연결

/lib/common/mysql.js

const dotenv = require('dotenv');
const mysql = require('mysql');

node.js의 require메서드로 외부모듈 dotenv, mysql을 가져온다.

dotenv.config();

dotenv.config 메서드를 이용해 .env를 process.env에 적용한다.

class SingletonBase {
  //생략
}

module.exports = class DatabaseConnector extends SingletonBase {
  constructor() {
    super(); //부모 생성자 호출
    this.config = {
      host: 'localhost',
      user: process.env.DATABASE_USERNAME,
      password: process.env.DATABASE_PASSWORD,
      database: process.env.DATABASE_NAME
    };
    return this;
  }
  //생략
};

SingletonBase의 자식클래스 DatabaseConnector 클래스에서
.env의 정보를 가져온다.


/lib/databaseConnector.js

const DatabaseConnector = require('./common/mysql');

module.exports = class MyDatabaseConnector extends DatabaseConnector {
  constructor() {
    super();
  }
};

스키마 구현


learnmysql 데이터베이스의 스키마 모식도

/migration/schema.sql

USE learnmysql;

learnmysql 데이터베이스 사용

FieldTypeNullKeyDefaultExtra
idintNoPKnullauto_increment
namevarchar(255)Nonull
emailvarchar(255)Nonull
roleIdintYesFKnull
CREATE TABLE `user` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) not NULL,
  `email` varchar(255) not NULL
);
ALTER TABLE `user` ADD roleId int;
ALTER TABLE `user` ADD FOREIGN KEY (`roleId`) REFERENCES `role` (`id`);

해당 구조의 user 테이블 생성

FieldTypeNullKeyDefaultExtra
idintNoPKnullauto_increment
titlevarchar(255)Nonull
bodyvarchar(255)Nonull
created_attimestampNoCURRENT_TIMESTAMPDEFAULT_GENERATED
userIdintYesFKnull
CREATE TABLE `content` (
  `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`)
);

해당 구조의 content 테이블 생성

FieldTypeNullKeyDefaultExtra
idintNoPKnullauto_increment
namevarchar(255)Nonull
CREATE TABLE `category` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) not NULL
);

해당 구조의 category 테이블 생성

FieldTypeNullKeyDefaultExtra
idintNoPKnullauto_increment
contentIdintNoFKnull
categoryIdintNoFKnull
CREATE TABLE `content_category` (
  `id` int PRIMARY KEY AUTO_INCREMENT
);
ALTER TABLE `content_category` ADD contentId int not NULL;
ALTER TABLE `content_category` ADD FOREIGN KEY (`contentId`) REFERENCES `content` (`id`);
ALTER TABLE `content_category` ADD categoryId int not NULL;
ALTER TABLE `content_category` ADD FOREIGN KEY (`categoryId`) REFERENCES `category` (`id`);

해당 구조의 content_category 테이블 생성

FieldTypeNullKeyDefaultExtra
idintNoPKnullauto_increment
namevarchar(255)Nonull
CREATE TABLE `role` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) not NULL
);

해당 구조의 role 테이블 생성

스크립트

기본 sql문 정리

SHOW DATABASES; 
// 모든 데이터베이스의 정보 확인

USE database_name; 
// 해당 데이터베이스 사용

SHOW TABLES; 
// 모든 테이블 정보 확인

DESC table_name; 
// 해당 테이블 구조 확인

SELECT * FROM table_name; 
// 해당 테이블의 모든 데이터 확인

SELECT table_name.calumn1 FROM table_name 
// 해당 테이블의 해당 calumn1 데이터만 확인

SELECT * FROM table_name WHERE table_name.calumn1='kim'; 
// 해당 테이블에서 특정 조건을 만족하는 데이터 찾기 (조건: calumn1의 value가 'kim'이다.)

SELECT * FROM table_name AS byname WHERE byname.calumn1='kim';
// AS로 별칭을 만들수 있다.

INSERT INTO table_name (calumn1, ...) VALUES (value1,...); 
// 해당 테이블에 데이터 추가

UPDATE table_name SET table_name.calumn1='value';
// 해당테이블 calumn1의 value 수정

SELECT table1.column1 FROM table1
INNER JOIN table2 ON table1.column4 = table2.column7;
// table1에서 column1에서 조건에 맞는 record와 table2에서 조건에 맞는 record 리턴
// (조건: table1의 column4과 table2의 column7중에서 동일한 value)

SELECT table1.column1 FROM table1
LEFT JOIN table2 ON table1.column4 = table2.column7;
// table1에서 column1의 모든 record와 table2에서 조건에 맞는 record 리턴
// (조건: table1의 column4과 table2의 column7중에서 동일한 value)

SELECT table1.column1 FROM table1
RIGHT JOIN table2 ON table1.column4 = table2.column7;
// table1에서 column1에서 조건에 맞는 record와 table2의 모든 record 리턴
// (조건: table1의 column4과 table2의 column7중에서 동일한 value)

요구조건에 따른 sql문

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

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

/*
    TODO: Q 3-3. content 테이블의 구조를 보기위한 SQL을 작성해주세요.
        - 요구사항에 맞는 content 테이블을 작성해야만, 테스트를 통과합니다.
*/
const PART3_3 = `DESC content`;
/*
----------------------------------------------------------------------------------------------
    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 ('Taeyoung','tyoon225@naver.com')`;

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

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

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

/*
----------------------------------------------------------------------------------------------
    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) value ('database', 'make it', 1)`;
/*
----------------------------------------------------------------------------------------------
    TODO: Q 5-1-1. category 테이블의 구조를 보기위한 SQL을 작성해주세요.
        - 요구사항에 맞는 category 테이블을 작성해야만, 테스트를 통과합니다.
*/
const PART5_1_1 = `DESC category`;

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

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

/*
----------------------------------------------------------------------------------------------
    TODO: Q 5-1-4. user 테이블의 구조를 보기위한 SQL을 작성해주세요.
        - 요구사항에 맞는 user 테이블을 작성해야만, 테스트를 통과합니다.
*/
const PART5_1_4 = `DESC 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, user.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: 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
INNER JOIN user ON content.userId = user.id
WHERE user.name = 'jiSungPark'`;

/*
----------------------------------------------------------------------------------------------
    TODO: Q 5-2-6. JiSungPark이 작성한 content의 category name을 찾기위한 SQL을 작성해주세요.
*/
const PART5_2_6 = `SELECT category.name FROM category
INNER JOIN content_category ON category.id = content_category.categoryId
INNER JOIN content ON content_category.contentId = content.id
INNER 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 title, body, created_at FROM content
INNER JOIN content_category ON content.id = content_category.contentId
INNER 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 title, body, created_at, user.name FROM content
INNER JOIN user ON content.userId = user.id
INNER JOIN content_category ON content.id = content_category.contentId
INNER 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(*) AS ContentCount FROM content
INNER JOIN user ON content.userId = user.id
WHERE user.name = 'duRiCha'`;

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

0개의 댓글