Tech Log 🛠️

1. 테이블 추가 

create table 테이블명

  (   

    컬럼명 자료형 옵션

  );

ex)

create table newTable

  (   

    id int AUTO_INCREMENT,

    name varchar(10) not null

  );

2. 테이블 컬럼 추가 / 삭제

- 테이블 컬럼 추가

ALTER TABLE 테이블명 ADD 컬럼명 자료형 옵션;

* 특정한 컬럼 다음에 생성하고 싶을 때 

ALTER TABLE 테이블명 ADD 컬럼명 자료형 옵션 AFTER 지정컬럼;

* 테이블의 맨 첫번째에 생성하고 싶을 때

ALTER TABLE 테이블명 ADD 컬럼명 자료형 옵션 FIRST;

- 테이블 컬럼 삭제

ALTER TABLE 테이블명 DROP COLUMN 컬럼명;

3. 테이블 컬럼 변경

- 속성 변경

ALTER TABLE 테이블명 MODIFY 컬럼명 자료형;

- 이름 변경

ALTER TABLE 테이블명 CHANGE 기존 컬럼명 바꿀 컬럼명 자료형;

4. 테이블 데이터 삭제 

- 테이블 데이터 모두 삭제

DELETE FROM 테이블명;

- 조건에 맞는 테이블 데이터 삭제

DELETE FROM 테이블명 WHERE 조건문;

5. 테이블 데이터 초기화

- 테이블 초기화

TRUNCATE TABLE 테이블명

* DELETE 와 다른 점은 테이블을 아예 초기화 시켜서 새로운 테이블로 만듦

쿠팡 ERD

URL : https://aquerytool.com/aquerymain/index/?rurl=d8467984-5fa4-491c-b567-1b1de339fc1c&
Password : xy2cgb

한방 쿼리

- 내 정보 관리

use kiinDB;
select name                     as 고객명,
       passwd                   as 비밀번호,
       email                    as 이메일,
       number                   as 연락처,
       address                  as 주소,
       profile_img              as 프로필사진,
       concat(membership, '회원') as 멤버십
from user u
         inner join (select membership, user_id from membership) m on u.id = m.user_id
         inner join (select address, user_id, min(id) from address) a on u.id = a.user_id
where u.id = 1

- 주문목록

use kiinDB;
select o.created_at                      as 주문일자,
       o.updated_at,
       shipment                          as 배송종류,
       shipment_img_url                  as 배송이미지,
       concat(name, space(1), o2.option) as 상품이름,
       price                             as 가격,
       stock                             as 주문개수,
       prod_img                          as 상품이미지,
       (case
            when
                o.status = 'Y'
                then '배송완료'
            else '배송 중'
           end)                          as 배송상태,
       (case
            when
                o.status = 'Y' and datediff(o.updated_at,current_timestamp) < 1
                then concat('어제',space(1),date_format(o.updated_at,'%m/%d/%W'),space(1),'도착')
           when
                o.status = 'Y' and datediff(o.updated_at,current_timestamp) > 1
                then concat(date_format(o.updated_at,'%m/%d/%W'),'도착')
            when o.status= 'N' and date_format(o.created_at,'%h') < 12
           then '오늘 오후 도착 보장'
           else concat(date_format(DATE_ADD(o.created_at, INTERVAL 1 DAY),'%m/%d/%W'),'도착보장')
           end)                          as 배송상태

from order_ o
         inner join shipments_mapping sm on o.prod_id = sm.prod_id
         inner join shipments s on sm.shipment_id = s.id
         inner join product p on o.prod_id = p.id
         inner join option_mapping om on p.id = om.prod_id
         inner join option_ o2 on om.option_id = o2.id
where o.buyer_id = 1
  and o2.id = 1;

-주문상세

use kiinDB;
select user.name as 이름,
       payment as 결제수단,
       number as 핸드폰번호,
        address as 주소,
        o.created_at                      as 구입일자,
       o.id                              as 주문번호,
       shipment                          as 배송종류,
       shipment_img_url                  as 배송이미지,
       (case
            when s.id = '1' then '0원'
            when s.id = '2' then '0원'
            else '3000원' end)            as 배송비,
       concat(p.name, space(1), o2.option) as 상품이름,
       price                             as 가격,
       stock                             as 주문개수,
       prod_img                          as 상품이미지,
       (case
            when
                o.status = 'Y'
                then '배송완료'
            else '배송 중'
           end)                          as 배송상태,
       (case
            when
                o.status = 'Y' and datediff(o.updated_at, current_timestamp) < 1
                then concat('어제', space(1), date_format(o.updated_at, '%m/%d/%W'), space(1), '도착')
            when
                o.status = 'Y' and datediff(o.updated_at, current_timestamp) > 1
                then concat(date_format(o.updated_at, '%m/%d/%W'), '도착')
            when o.status = 'N' and date_format(o.created_at, '%h') < 12
                then '오늘 오후 도착 보장'
            else concat(date_format(DATE_ADD(o.created_at, INTERVAL 1 DAY), '%m/%d/%W'), '도착보장')
           end)                          as 배송상태

from order_ o
         inner join shipments_mapping sm on o.prod_id = sm.prod_id
         inner join shipments s on sm.shipment_id = s.id
         inner join product p on o.prod_id = p.id
         inner join option_mapping om on p.id = om.prod_id
         inner join (select min(id),'option',id from option_) o2 on om.option_id = o2.id
         inner join user on o.buyer_id = user.id
        left join (select min(id),address,user_id from address) a on user.id = a.user_id
inner join order_payment op on op.order_id = o.id
inner join payments_list pl on pl.id = op.payment_id
where o.id = 5;

-최근 본 상품

use kiinDB;
select u.id,
       p.prod_img,
       p.name,
       p.price     AS 원래가격,
       round((p.price - p.price*(discount/100)),0)   AS 할인가,
       (case when p.status='Y' then '구매가능'
           when p.status='N' then '품절' end)    AS 품절유무,
       shipment    AS 배송,
       avgscore    AS 평균별점,
       reviewCount AS 리뷰개수
from user u
         inner join recently_viewed rv on u.id = rv.user_id
         inner join product p on rv.prod_id = p.id
         inner join shipments_mapping sm on p.id = sm.prod_id
         inner join shipments s on sm.shipment_id = s.id
         inner join (select prod_id, count(r.id) AS reviewCount, avg(score) AS avgscore
                     from review r
                              inner join score s2 on r.id = s2.review_id
                     group by prod_id) r on p.id = r.prod_id
        left join (select discount,prod_id from discount_prod) dp on p.id = dp.prod_id
where u.id = 1
group by p.id
limit 20

-상품 페이지

use kiinDB;
select prod_img,price AS 원래가, round((p.price - p.price*(discount/100)),0)   AS 할인가,name,brand,o.`option` AS 옵션,avgScore AS 평균별점, 리뷰수, shipment
from product p
         inner join (select count(r.id) AS 리뷰수, round(avg(score),0) AS avgScore,prod_id from review r inner join score s on r.id = s.review_id group by prod_id) r on r.prod_id= p.id
         inner join shipments_mapping sm on p.id = sm.prod_id
         inner join shipments s2 on sm.shipment_id = s2.id
        inner join brand b on p.brand_id = b.id
        inner join option_mapping om on p.id = om.prod_id
        inner join (select min(id) AS id,`option`  from option_) o on om.option_id = o.id
        inner join discount_prod dp on p.id = dp.prod_id
where p.id = 1

전체 DB 

DB 설계에 앞서

     - 생활코딩 데이터 베이스 강의와 블로그를 참조하였다.

https://velog.io/@sontulip/how-to-db-design

https://www.youtube.com/watch?v=1d38YZKCM88&list=PLuHgQVnccGMDF6rHsY9qMuJMd295Yk4sa 

테이블 설계

- 처음 테이블을 설계할 때 핵심이 되는 테이블을 먼저 만들어주었다.

  나는 크게 사용자, 상품 , 판매자 , 주문  , 리뷰 테이블을 핵심으로 보았다.

사용자 테이블 

쿠팡의 마이페이지를 보면 쿠폰 , 캐시 , 주문내역 , 주소 등 여러 정보들이 있다.

이 정보들이 유저 테이블을 중심으로 서로 참조될 수 있도록 ERD를 설계 하였다.

일단 유저테이블에는 비밀번호 , 이름과 같은 기본램 정보만 들어가게끔 하였다.

1. coupon : 사용자의 쿠폰 목록을 받는 테이블이다. 쿠폰 목록과 사용자는 N:N 관계이므로 중간에 맵핑 테이블을 만들어주었다.

2. search_history : 사용자의 검색 내역을 저장한다. 사용자들간의 검색 목록이 중복될 수 있으므로 N:N 관계로 설정하였다.

3. address : 사용자의 주소 정보를 저장한다.

4. cart : 사용자의 장바구니를 저장한다. 이 테이블은 상품 테이블 또한 참조하였다. 때문에 N:N 관계이다. (사실상 맵핑테이블임)

5. wishlists : 사용자의 찜 목록을 저장한다. 이 또한 맵핑 테이블로 상품 테이블과 유저 테이블을 FK로 참조한다.

6. payments_mapping : 이 테이블은 결제목록 수단들을 저장하는 데 payments_list라는 테이블의 id와 유저 id를 외래키로                                                 받는다.

상품 테이블

쿠팡의 상품 페이지를 보면 상품의 제목, 옵션 , 로켓배송 유무 , 리뷰 , 스코어 등 여러 정보들을 확인할 수 있다.

따라서 리뷰 테이블과 옵션 테이블을 따로 만들어서 이름 참조 받게끔 하였다.

상품 테이블에는 FK가 들어가는데 이것은 브랜드 테이블을 를 참조한다. 

또한 가격 , 상품 이름과 같은 기본 정보들이 포함되어있다.

1. category : 상품의 카테고리 정보를 외부 테이블로 빼주었다. 이 테이블은 FK 가 두개인데 판매자 테이블과 상품 테이블 두 곳을 참조한다.

2. description : 상품의 상세 설명을 저장한다. 굳이 따로 빼줘야 싶기도 하다..  

3. option : 상품의 옵션 값을 저장한다. 옵션 테이블은 자식 테이블 하나를 더 가지고 있는데 이것은 옵션의 상세 정보(Ex. 가격 , 수량)를 가지고있다.

4. brand : 상품의 브랜드를 담고 있는데 상품과 브랜드는 N:1 관계를 가지고 있다. 따라서 상품 테이블이 FK를 가진다.

5. shipments :  로켓 배송 ,로켓 프레시 등 배송 종류 정보를 담고있다.

주문 테이블

주문 테이블은 판매자 정보 , 회원 정보 , 상품 정보 등 많은 외래키를 가지고 있다. 

이때 판매자 테이블은 seller 라는 테이블로 만들어주었다.

판매자 테이블은 장바구니 , 상품 , 주문 등 여러 테이블의 FK 가 된다.

리뷰 테이블

리뷰 테이블은 테이블 내 여러 정보를 참조하고 별점이나 리뷰 사진은 별도의 테이블로 분리해주었다.

1. score : 리뷰를 쓴 사용자 id 와 별점 정보를 저장한다.

2. review_photo : 리뷰에 들어가는 사진 정보를 저장한다.

관계형 데이터 베이스

관계형 DB는 테이블로 이루어져 있으며 , 테이블은 키와 값의 관계를 나타낸다.

이처럼 관계형 DB는 종속성을 관계로 표현한다.

여기서 말하는 키(key)란 행의 식별자로 이용되는 열을 말한다 ex) ID

관계형 데이터 베이스의 활용

만약 여러 유튜버들의 영상을 데이터 베이스에

표현하고 싶다고 가정해보자

위의 표처럼 다른 영상임에도 같은 유튜버의 창작물인 경우가 있다.

어떻게 하면 효과적으로 DB를 설계할 수 있을까 ?

이런 식으로 유튜버의 코드만을 따로 테이블로 만들어서 DB를 관리하는 실습을 해볼 것이다.

먼저 video라는 DB를 새로 생성해주었다.

두 개의 테이블을 생성해준 후 데이터를 넣어준다.

이제는 join으로 테이블을 합칠 차례다.

기본적인 LEFT JOIN 명령어를 통해서 테이블을 합쳐주었다.

하지만 코드 열이 중복되어 있어서 하나만 나오도록 설정해줄 필요가 있다.

AS 명령어를 통해서 video 테이블의 코드만 출력되도록 하였다.

오히려 더 복잡해지는 거 같은데, 이렇게 관계형 DB를 사용하면 장점이 뭘까?

관계형 DB를 사용하면,

1. 데이터의 분류, 정렬, 탐색 속도가 빠르다

2. 오랫동안 사용된 만큼 신뢰성이 높고, 어떤 상황에서도 데이터의 무결성을 보장해준다.

하지만,

기존에 작성된 스키마를 수정하기가 어렵고 데이터베이스의 부하를 분석하는 것이 어렵다라는

단점이 있다.

데이터베이스란 ,

구조화된 정보 또는 데이터의 조직화된 모음으로써 일반적으로 컴퓨터 시스템 내에 전자적으로 저장

데이터 베이스의 구조

여러 데이터는 표들로 구성되어 있다.

이 데이터들을 연관 된 항목에 맞게 그룹화 시키는 데 이것을 데이터 베이스 혹은 스키마 라고 한다.

또 스키마들이 모인 것이 데이터베이스 서버가 된다.

SQL(strutured query language)

이렇게 만들어진 데이터베이스 서버에 데이터를 얻으려면 SQL 이란것을 사용해야한다.

Sql을 사용함으로써 데이터를 쿼리 , 조작 정의하고 액세스를 제어할 수 있다.

스프레드 시트 vs 데이터 베이스

엑셀의 스프레드 시트를 사용하면 되는데 데이터 베이스를 사용함으로써 얻는 이점은 뭘까 ?

- 데이터 저장 및 조작 방법

- 데이터에 액세스 할 수 있는 사람

- 저장할 수 있는 데이터 양

스프레트 시트는 다양한 사용자에 맞게 설계된 것이 아니기 때문에 사용자가 적거나 데이터의 양이 많지 않은 경우에 적합하다.

하지만 데이터 베이스는 여러 사용자들에게 다양한 권한을 줄 수 있기 때문에 여러 사용자가 동시에 데이터베이스에 접근하여 안전하게 데이터를 얻을 수 있다.

데이터베이스 서버에 접속하기

DB 데이터를 다루고 싶으면 먼저 DB서버에 접속을 해야한다.

Mysql이 설치되있는 경로로 가서

Mysql –uroot –p 명령어를 입력해준다.

그리고 mysql 설치 시 설정했던 비밀번호를 입력해주면 서버로 접속할 수 있다.

Mysql 실습

DB와 테이블

DB 명령어

CREATE DATABASE 명령어를 사용해서 데이터베이스를 만들 수 있다.

DROP DATABASE 명령어를 사용해서 데이터베이스를 삭제할 수 있다.

SHOW DATABASES 명령어를 사용해서 현재 만들어져 있는 데이터 베이스를 조회할 수 있다..

Ex)

topic 데이터베이스를 만들려고 했지만 , 이미 존재하였다.

따라서 DROP 명령어를 이용하여 해당 DB를 삭제하고 다시 만들어주었다.

이제 use topic 명령어를 통해서 해당 DB를 이용해주도록 하자

TABLE 명령어 및 생성 실습

데이터 베이스를 만들었으면 거기에 들어갈 테이블들을 만들어 주어야한다.

테이블을 만들어주는 것은 CREATE TABLE 을 이용하면 된다.

이제 이 테이블들을 이용하여 CRUD 실습을 진행해볼것이다.

CRUD 란?

DB의 가장 기본이 되는 것으로

Create

Read

Update

Delete 를 의미한다.

Create

테이블에 어떤 데이터를 넣고 싶을 때, INSERT 명령을 사용하여 데이터를 넣을 수 있다.

Ex) INSERT INTO topic1(ID,col1,col2,created) VALUES("1","ROW1","ROW2",NOW());

Read

테이블의 정보를 불러오고 싶을 때 , SELECT 명령으로 테이블의 데이터를 읽을 수 있다,

Ex) SELECT * FROM topic1;

   SELECT id,col1 FROM topic1;

*  만일 조건에 맞는 데이터를 검색하고 싶으면 where문을 이용하면 된다.

UPDATE

테이블의 데이터 정보를 수정하고 싶을 때 , UPDATE 명령어를 통해 데이터를 변경할 수 있다.

DELETE

테이블의 데이터를 삭제해야 할 때, DELETE 명령으로 테이블의 데이터를 삭제할 수 있다.

* WHERE 뒤에 조건문을 꼭 달아주어야한다 !

 참고) 생활코딩 Mysql

 https://www.youtube.com/watch?v=h_XDmyz--0w&list=PLuHgQVnccGMCgrP_9HL3dAcvdt8qOZxjW