[새싹X코딩온]웹 풀스택 6주차(8/25)회고록|MYSQL+MVC패턴

오늘은 지난 시간에 이어 MYSQL과 MVC패턴에 대해서 배웠습니다!! 

한번 다시 정리하면서 복습해보도록 하겠습니다 ㅎㅎ

---

1. MySQL 

지난 시간에 dml에서 select문을 이어서배웠습니다

1) order by

order by이 default값은 asc(오름차순)이고, 내림차순으로 정렬하기 위해서는 order by (key) desc를 해주면 됩니다!

코드로 알아볼게요~

-- <ORDER BY>
-- order by 없는 경우: PK기준으로 오름차순 정렬
SELECT * FROM customer;

SELECT * FROM customer order by custname; -- asc가 default값이기 떄문에 생략하면 가나다 순으로 오름차순 정렬이 됨
SELECT * FROM customer order by custname desc; -- 가나다 내림차순

-- WHERE 절과 ORDER BY 함께 사용 (단, 이때 ORDER BY 가 WHERE 보다 뒤에 위치해야 함)
-- SELECT * FROM customer ORDER BY addr desc  WHERE birth>='2000-01-01'; -- error: 구문 순서 오류
-- 2000년생 이후 출생자 중에서 주소를 기준으로 내림차순 검색
SELECT * FROM customer WHERE birth>='2000-01-01' ORDER BY addr desc; -- 값이 동일할 경우 pk를 기준으로 정렬
-- order by는 적용 순서를 정할 수 있다
-- 2000년 생 이후 출생자 중에서 주소를 기준으로 내림차순 그리고 아이디를 기준으로 내림차순 검색
SELECT * FROM customer WHERE birth>='2000-01-01' ORDER BY addr desc, custid desc;
-- 2000년 생 이후 출생자 중에서 주소를 기준으로 오름차순 그리고 아이디를 기준으로 내림차순 검색
SELECT * FROM customer WHERE birth>='2000-01-01' ORDER BY addr, custid desc;

2) limit 

select문에 의해서 선택된 행들의 갯수를 제한해주는 녀석입니다!

-- <LIMIT>
-- 행의 개수를 제한
SELECT * FROM CUSTOMER WHERE BIRTH >+'2000-01-01' limit 2;
Select * from customer limit 3;

3) 집계함수

-- <집계함수>
-- 계산하여 어떤 값을 리턴하는 "함수"
-- group by 절과 함께 쓰이는 케이스가 많음

-- 주문 테이블에서 상품의 총 판매 개수 검색
SELECT sum(amount) from orders;


-- 주문 테이블에서 총 판매 개수 검색 + 의미있는 열이름으로 변경 (total_sales)
SELECT SUM(AMOUNT) AS 'total_amount' from orders;

-- 주문 테이블에서 총 판매 개수, 평균 판매 개수, 상품 최저가, 상품 최고가 검색
-- avg_amount, min_price, max_price
select sum(amount) as 'total_amount', 
avg(amount) as 'avg_amount', 
min(price) as 'min_price', 
max(price) as 'max_price' 
from orders ; -- 세미콜론이 끝나야 MySQL에서는 한문장임
    
-- 주문 테이블에서 총 주문 건수 (= 튜플 개수)
select count(*) from orders;

-- 주문 테이블에서 주문한 고객 수 (중복없이)
select count(distinct custid) from orders; -- 총 8명의 사람이 주문을 한 이력이 있음(총 9명이 있지만 custid에 등록만 되어있고 주문을 하지 않았음~)

 다양한 집계함수들을 다시한번 정리해보면~~

sum():합계

AVG():평균

MAX():최대값

MIN():최소값

COUNT():행개수

COUNT(DISTINCT):중복 제외한 행 개수

 

4) group by⭐⭐

-- <GROUP BY>
-- "~별로"

-- 고객별로 주문한 주문 건수 구하기
select custid, count(*)  from orders group by custid; -- 전체 행에서 custid가 몇번 나왔는지

-- 고객별로 주문한 상품 총 수량 구하기
select custid, sum(amount), count(*) from orders group by custid;

-- 고객별로 주문한 총 주문액 구하기
select custid, sum(amount*price) from orders group by custid;

-- 상품 별 판매개수
select prodname, sum(amount) from orders group by prodname;

group by에서 특이한 점은 select뒤에 오는 key와 group by 뒤에 오는 key가 동일하게 있다는 거 보이시나요? 이렇게 select문이 구성되어 있어야 이 select문을 실행했을 때 어떤 그룹별로 묶었는지 확인할 수 있습니다! 뭐 안써도 상관은 없지만.. 그래도 데이터를 가시적으로 표현하려면 이렇게 하는 게 좋겠죠?ㅎㅎ 

그럼,,

⚠️group by 주의사항⚠️

select 절에서 group by 에서사용한 속성과 집계함수만 사용가능

 

5) HAVING : group by 절 이후 추가 조건 

-- 총 주문액이 10000원 이상인 고객에 대해서 // 고객별로 주문한 상품 총 수량 구하기
select custid, sum(amount),sum(price*amount) from orders group by custid having sum(amount*price)>=10000; -- having 이하 절은 group by 절에 대한 조건
-- select custid, sum(amount),sum(price*amount) from orders WHERE sum(price*amount)>=10000 group by custid ; -- error code 1111. group 함수 잘못 사용

-- 총 주문액이 10000원 이상인 고객에 대해서 고객별로 주문한 상품 총 수량 구하기(단, custid가 'bunny'인 고객은 제외하고 출력)
select custid, sum(amount),sum(price*amount) from orders WHERE custid!='bunny' group by custid having sum(amount*price)>=10000;

어?? 그런데 조건을 추가한다??? WHERE과 비슷하지 않나요?? 

그래서 한번 정리해보겠습니다

WHERE VS HAVING

 

1. WHERE

select * from 테이블명 where 조건절

위와 같은 방법으로 where 조건절을 사용합니다.

항상 from뒤에 위치하고 조건에는 다양한 비교연산자들이 사용되어 구체적인 조건을 줄 수 있습니다.

2. HAVING

select * from 테이블명 group by 필드명 having 조건절

위와 같은 방법으로 having 조건절을 사용합니다.

항상 group by뒤에 위치하고 where 조건절과 마찬가지로 조건에는 다양한 비교연산자들이 사용되어 구체적인 조건을 줄 수 있습니다.

3. 차이점

where은 기본적인 조건절로서 우선적으로 모든 필드를 조건에 둘 수 있습니다. 하지만 having은 group by 된 이후 특정한 필드로 그룹화 되어진 새로운 테이블에 조건을 줄 수 있습니다.

예를 들어

1)mysql> select name, count(*) as cnt from where name='홍길동' 테이블 group by name;
2)mysql> select name, count(*) as cnt from 테이블 group by name;

1)의 경우는 where 조건절에 맞는 정보를 우선적으로 들고와서 group by로 그룹화 하고

2)의 경우는 group by로 그룹화 한 상태에서 having 조건절에 맞는 정보를 들고옵니다. 이때 having 조건절에 이용할 컬럼은 select 뒤에 반드시 명시 되어져야 합니다.

출처 : http://www.cyworld.com/springlog

이렇게 dml을 다 배우고,,, 실습을 하고,,, 

6)JOIN

다음으로 JOIN을 배워보았습니다!!

SQL에서 기본적으로 JOIN 키워드를 사용하거나 콤마(,)를 통해 연결하면 INNER JOIN 연산을 수행합니다..이때, 조건이 설정되지 않은 INNER JOIN은 CROSS JOIN과 같은 연산을 하게 되는데요!

CROSS JOIN이 뭐냐??

예를들어서

이런 instructor(교수)테이블과 teaches table이 있고, 그 안에 이런 values들을 insert했다고 해 봅시

create table instructor (
	id int primary key,
    name varchar(7),
    dept_name varchar(7),
    salary int
);
create table teaches (
	id int primary key,
    course varchar(7),
    semester varchar(7),
    year varchar(4)
);
insert into instructor values (1, 'james', '심리', 95000);
insert into instructor values (2, 'john', '컴공', 95000);

insert into teaches values (1, '운영체제', '봄', '2022');
insert into teaches values (2, '상담심리', '가을', '2023');

그럼 이렇게 표가 생성되겠죠? 그럼 CROSS JOIN을 해보겠습니다!

SELECT * FROM instructor JOIN teaches;
SELECT * FROM instructor, teaches;

이런식으로 아무조건없이 join을 하면 아무 의미 없는 join이 발생하고 잘못하면 표의 크기가 엄청나게 커지는 문제가 발생하기도 합니다... 

 

이때, INNER JOIN 뒤에 ON 키워드를 통해 일치하는 속성을 기준으로 결합할 수도 있으며, 이를 EQUAL JOIN이라고 합니다..

이 표는 어떤 조건으로 join을 했을까요?

네.. 이 table에서 id 값이 같은거 보이시나요? 여기에서는 id값을 기준으로 join을 했습니다.

SELECT * FROM instructor I JOIN teaches T ON I.id=T.id;

그럼 OUTER JOIN은 뭘까요? 

OUTER JOIN은 공통된 부분만 결합하는 INNER JOIN과 다르게 공통되지 않은 ROW도 유지하는 것을 말합니다!

이때, 왼쪽 테이블의 row를 유지하면 left outer join,

오른쪽 테이블의 row를 유지하면 right outer join,

양쪽 테이블의 row를 모두 유지하면 full outer join

이라 합니다

 

그럼 행을 조금 더 추가해서 outer join 확인해볼게요~

insert into instructor values (3, 'mark', '수학', 75000);
insert into instructor values (4, 'tom', '심리', 90000);
insert into teaches values (3, '인공지능', '봄', '2022');
insert into teaches values (4, '사회심리', '가을', '2023');
insert into teaches values (5, '네트워크', '봄', '2022');
insert into teaches values (6, '알고리즘', '가을', '2023');

-- left outer join
SELECT * FROM instructor I LEFT OUTER JOIN teaches T ON I.id=T.id;

-- right outer join
SELECT * FROM instructor I RIGHT OUTER JOIN teaches T ON I.id=T.id;

아 참!! 오른쪽 왼쪽은 "ON"을 기준으로 나누어집니다!

다음으로 FULL OUTER JOIN을 알아볼건데요! 그런데 MySQL에서는 full outer join을 지원하지는 않아서 

left join과 right join을 union(합쳐서) full outer join을 구현할 수 있습니다!

SELECT * FROM instructor I LEFT OUTER JOIN teaches T ON I.id=T.id
UNION
SELECT * FROM instructor I RIGHT OUTER JOIN teaches T ON I.id=T.id;

마지막으로~~ natural join 이라는 것도 있습니다! 같은 값을 갖는 항목끼리 결합하는 것으로, 같은 값이 없다면 항목에서 제외 됩니다.

SELECT * FROM instructor NATURAL JOIN teaches;

full outer join 에서 교집합이라고 보면 되겠죠?

자.. 이렇게 즐거웠던 MySQL이 끝나고... MVC패턴이...

아 그전에 DCL도 있었는데 이건 나중에 MVC패턴 하면서 계정생성하고 하는 거 정리해볼게요...

---

2. MVC패턴..

우선 mvc패턴을 적용하기 전의 app.js 입니다..

const express = require('express');
const app = express();
const PORT = 8000;
app.set('view engine', 'ejs');
app.use('/views', express.static(__dirname + '/views'));
app.use('/static', express.static(__dirname + '/static')); //동적파일 업로드를 위한 (클라이언트가 바로 확인할 수 있도록...)
app.use(express.urlencoded({ extended: true }));
app.use(express.json());

app.get('/', (req, res) => {
  res.render('index');
});
app.get('/comments', (req, res) => {
  res.render('comments', { comments: comments });
});
app.get('/comment/:id', (req, res) => {
  //:id라고 된 부분에서 id가 마치 변수같은 역할을 하고 있다~
  //req.params: 라우트 매개변수에 대한 정보가 담겨있음.
  console.log(req.params); // locathost:8000/comment/1을 하면 콘솔창에 { id: '1' }찍힘 ->id는 문자열이니까 숫자로 바꿔야함
  const cmtId = Number(req.params.id); //id값을 cmtId라는 변수에 저장을 하고
  // 0, 7 같은 존재하지 않는 id로 접근시 404 페이지
  //   if (cmtId < 1 || cmtId > comments.length) {
  //     return res.render('404');
  //   }
  //   // :id 변수에 숫자가 아닌 문자가 온다면 404 페이지
  //   if (isNaN(cmtId)) {
  //     return res.render('404');
  //   }
  //-->위의 404에러처리를 한번에!!
  if (!comments[cmtId - 1]) {
    return res.render('404');
  }
  res.render('comment', { comment: comments[cmtId - 1] }); //db에 임시로 저장된 배열 comments를 comment라는 객체로 묶어서 comment.ejs에 전달!
});
// param 여러개 사용 가능
app.get('/test/:id/:name', (req, res) => {
  //localhost:8000/test/banana/바나나라고 하면
  console.log('req.params', req.params); //req.params { id: 'banana', name: '바나나' }
  res.send('test req success!');
});

//404 error 처리
//반드시 맨 마지막 라우트로 선언해야함(앞의 라우트들이 아닌경우를 의미하기 떄문에~~~)
app.get('*', (req, res) => {
  res.render('404');
});
app.listen(PORT, () => {
  console.log(`${PORT} is opening!`);
});
// (임시) DB로부터 받아온 댓글 목록
const comments = [
  {
    id: 1,
    userid: 'helloworld',
    date: '2022-10-31',
    comment: '안녕하세요^~^',
  },
  {
    id: 2,
    userid: 'happy',
    date: '2022-11-01',
    comment: '반가워유',
  },
  {
    id: 3,
    userid: 'lucky',
    date: '2022-11-02',
    comment: '오 신기하군',
  },
  {
    id: 4,
    userid: 'bestpart',
    date: '2022-11-02',
    comment: '첫 댓글입니당ㅎㅎ',
  },
  {
    id: 5,
    userid: 'apple',
    date: '2022-11-02',
    comment: '우와!!!!!',
  },
];

 

 

이렇게 구성된 app.js를 mvc패턴으로 만들어 볼 겁니다!

아 참!! 404에러 처리에 대해서 새롭게 배우게 되었는데요, 여기에서 중요한건, 앞의 라우트들이 적용되지 않을 경우를 의미하기 때문에 라우트의 가장 마지막에 404error처리 코드가 와야합니다!

 

MVC패턴이란? Model View Controller 의 약자입니다!

MVC패턴의 흐름은 이렇게 되는데요..! 여기에서 중요한건 view와 model은 직접적으로 연결되지않고 controller에 의해서 연결된다는 겁니다..! 

Model: 데이터를 처리하는 부분

View: UI관련된 것을 츠리하는 부분(사용자에게 보여지는 부분)

Controller:View와 Model을 연결해주는 부분

자 그럼 이제 직접 적용해서 위의 app.js를 MVC패턴으로 분리해서 코드를 짜 보아야 겠죠..?

1) 폴더구조

먼저, 폴더 구조는 이렇게 구성해주세요!

2) app.js

app.js부터 다시 한번 만들어 볼게요!

mvc패턴에서 app.js는 Router를 불러오는 부분으로 위의 코드를 이용해 특정 시작 url의 역할 구분이 가능합니다.

const express = require('express');
const app = express();
const PORT = 8000;

app.set('view engine', 'ejs');
app.use('/views', express.static(__dirname + '/views'));
app.use('/static', express.static(__dirname + '/static'));
app.use(express.urlencoded({ extended: true }));
app.use(express.json());

// [라우터 분리]
const indexRouter = require('./routes'); // index는 생략 가능!
app.use('/', indexRouter); // localhost:PORT/ 경로를 기본으로 ./routes/index.js 파일에 선언한 대로 동작

// [404 error] 맨 마지막 라우트로 선언 -> 나머지 코드 무시되기 때문!!
app.get('*', (req, res) => {
  res.render('404');
});

app.listen(PORT, () => {
  console.log(`http://localhost:${PORT}`);
});

3)routes/index.js :controller와 연결해 설정가능

//모듈!!
//index.js가 전체 mvc흐름에서 router역할
//이 모듈의 역할
// 경로 선언과관련된 내용 기술
const express = require('express');
const controller = require('../controller/Cmain'); //router<-controller
const router = express.Router(); //Router객체를 router(객체)에 묶어서 module.exports=router로 내보냄
// 이 파일의 기본경로: localhost:PORT/user/

router.get('/', controller.main);
router.get('/comments', controller.comments);
router.get('/comment/:id', controller.comment);

//module.exports 구문을 통해 router를 내보내야
// 다른 모듈(파일)에서 router 객체를 사용가능
module.exports = router;

4)Controller/Cmain.js

const Comment = require('../model/Comment'); //controller<-model
exports.main = (req, res) => {
  res.render('index');
}; //각각의함수들을 만듦과 동시에 exports하고있다.
exports.comments = (req, res) => {
  res.render('comments', { comments: Comment.getCommentAll() });
};
exports.comment = (req, res) => {
  const cmtId = Number(req.params.id);
  const comments = Comment.getCommentAll(); //댓글 목록 배열
  if (!comments[cmtId - 1]) {
    return res.render('404');
  }
  res.render('comment', { comment: comments[cmtId - 1] });
};

경로(routes)와 연결될 함수 내용을 정의합니다.

경로와 연결되는 함수이기때문에 req객체와 res객체 사용이 가능합니다!

모델과도 연결해 놓았습니다!

그리고 id변수에 숫자가 아닌 값이 오거나 존재하지 않는 댓글 id 접근시에는 404페이지와 연결되도록 해 놓았습니다.

5)model/Comment.js

// // (임시) DB로부터 받아온 댓글 목록
// db에서 전체 댓글 목록을 받아왔다고 가정
exports.getCommentAll = () => {
  return [
    {
      id: 1,
      userid: 'helloworld',
      date: '2022-10-31',
      comment: '안녕하세요^~^',
    },
    {
      id: 2,
      userid: 'happy',
      date: '2022-11-01',
      comment: '반가워유',
    },
    {
      id: 3,
      userid: 'lucky',
      date: '2022-11-02',
      comment: '오 신기하군',
    },
    {
      id: 4,
      userid: 'bestpart',
      date: '2022-11-02',
      comment: '첫 댓글입니당ㅎㅎ',
    },
    {
      id: 5,
      userid: 'apple',
      date: '2022-11-02',
      comment: '우와!!!!!',
    },
  ];
};

여기에서 댓글 목록은 배열로 가져오고, 각 댓글은 객체로 저장됩니다. 

아직은 MySQL과 연결하는걸 배우지 않았기때문에 임시로 데이터를 저장해보았습니다! 

다음 시간에는 아마 연결하는걸 하겠죠..?

6)views/comments.ejs

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>MVC패턴</title>
  </head>
  <body>
    <h1>댓글 목록 확인</h1>
    <a href="/">홈으로 이동하기</a>
    <ul>
      <% for (let cmt of comments){%>

      <li>
        <b><%= cmt.userid %></b>
        <a href="/comment/<%=cmt.id%>"><%= cmt.comment %></a>
        <!-- 잘못돈 주소로 접근할 경우 404error -->
      </li>
      <%}%>
    </ul>
  </body>
</html>

7)views/comment.ejs

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>COMMENT</title>
  </head>
  <body>
    <h1><%=comment.userid%>님의 댓글입니다</h1>
    <a href="/comments">댓글목록보기</a>
    <p>작성일: <%=comment.date%></p>
    <p>댓글 내용: <%=comment.comment%></p>
  </body>
</html>

---

이렇게 MVC패턴에 대해서 정리해보았습니다.. 조금 어질하네요..🫥