mysql적용

이동원·2024년 3월 19일
MYSQL적용

  • 중복제거 할때1 : distinct 인데 행단위로 제거한다. so 값의 범주 확인할때쓴다.

    • SELECT distinct Country FROM Customers;

  • 중복제거 할때2: group by 인데 다른 행들을 살려서 집계에 활용할수있다.so 그룹별 집계할때쓴다.

    • SELECT Country FROM Customers group by country;

ex)그룹핑을 사용하는 예시 :

  • 총 카테고리의 범주는 몇개 ?
    • select distinct CategoryID from Products;
  • 카테고리 별 상품 개수 는 ?
    • select count(ProductID) from Products where CategoryID=2; ->일일히 다하기 힘듬
    • select count(ProductID) from Products group by CategoryID;
  • 카테고리별 평균 가격
  • 그룹 대상 컬럼의 범주별로 확인하는 것이 일반적 .
    • select CategoryID, avg(Price) as'범주별 평균가격' from Products group by CategoryID;
  • 두개이상의 컬럼으로 그룹핑
    • select SupplierID,CategoryID, avg(Price) from Products group by SupplierID,CategoryID;

문제

  1. 손흥민의 주문 개수는? ???
    SELECT COUNT(id)
    FROM t_shopping
    WHERE userName = '손흥민';

  2. 손흥민이 산 상품은? ??? 운동화2개 스커트1개
    SELECT pname
    FROM t_shopping
    WHERE userName ='손흥민';

  3. 스커트를 산 사람은? ??? : 송혜교 , 중복 되지않게하기위해 distinct
    SELECT DISTINCT userName
    FROM t_shopping
    WHERE pname ='스커트';

  4. 가장 많이 주문한 사람의 아이디와 이름, 주문개수는? ??? :손흥민 , AS CNT 는 앞부분 이름 정해주는것

SELECT userId, userName , COUNT(userId)AS cnt
FROM t_shopping #테이블명
GROUP BY userId
ORDER BY cnt DESC; #컬럼명 기본값이 asc 그래서 생략가능

  1. 소지섭이 사용한 총 금액은? ???
    SELECT SUM(price)
    FROM t_shopping
    WHERE userName ='소지섭';
  • 테이블의 구조(스키마) 를 잘 짯는가 ? -> 썩 잘 만든건 아니다 .
    why ? 데이터가 중복 되고있다 . 주문이 추가 될때 마다 손흥민 이름 데이터가 중복
  1. 중복되면 저장 용량 많아짐 ->비용 2. 중복 데이터 처리하면 연산량 곱절 > 비용 3. 데이터 삽입 , 갱신 .삭제 등을 할때 이상현상 발생 .

삽입이상

  • 회원가입은 하되 , 주문은 아직 하지않은 회원 ???

  • user7 차태진 가입.
    INSERT INTO t_shopping
    SET userId = 'user7',
    userPw ='1234',
    userName = '차태진',
    address='대전';

    pname='', 못함

갱신이상

  • 손흥민이 개명-> 손흥만
    UPDATE t_shopping
    SET userName ='손흥만'
    WHERE id IN(1,8) ;

삭제 이상

  • 설현이가 주문을 1개함 > 그런데 취소함
    DELETE FROM t_shopping
    WHERE userName ='설현'; ## 설현 정보까지 삭제됌

즉 프로그래밍 세계 , db 세계 ->쪼개라 정규화공부 를 통해

DROP DATABASE mall;

CREATE DATABASE mall;

USE mall;

CREATE TABLE t_shopping(
id INT(5) PRIMARY KEY AUTO_INCREMENT,
userId CHAR(30) NOT NULL,
userPw CHAR(30) NOT NULL,
userName CHAR(30) NOT NULL,
address CHAR(50) NOT NULL,
pname CHAR(50) NOT NULL,
price INT(5) NOT NULL
);

INSERT INTO t_shopping
SET userId = 'user1',
userPw = 'pass1',
userName = '손흥민',
address = '런던',
pname = '운동화',
price = 1000000;

INSERT INTO t_shopping
SET userId = 'user2',
userPw = 'pass2',
userName = '설현',
address = '서울',
pname = '코트',
price = 100000;

INSERT INTO t_shopping
SET userId = 'user3',
userPw = 'pass3',
userName = '원빈',
address = '대전',
pname = '반바지',
price = 30000;

INSERT INTO t_shopping
SET userId = 'user4',
userPw = 'pass4',
userName = '송혜교',
address = '대구',
pname = '스커트',
price = 15000;

INSERT INTO t_shopping
SET userId = 'user5',
userPw = 'pass5',
userName = '소지섭',
address = '부산',
pname = '코트',
price = 100000;

INSERT INTO t_shopping
SET userId = 'user6',
userPw = 'pass6',
userName = '김지원',
address = '울산',
pname = '티셔츠',
price = 9000;

INSERT INTO t_shopping
SET userId = 'user6',
userPw = 'pass6',
userName = '김지원',
address = '울산',
pname = '운동화',
price = 200000;

INSERT INTO t_shopping
SET userId = 'user1',
userPw = 'pass1',
userName = '손흥민',
address = '런던',
pname = '코트',
price = 100000;

INSERT INTO t_shopping
SET userId = 'user4',
userPw = 'pass4',
userName = '송혜교',
address = '울산',
pname = '스커트',
price = 15000;

INSERT INTO t_shopping
SET userId = 'user1',
userPw = 'pass1',
userName = '손흥민',
address = '런던',
pname = '운동화',
price = 1000000;

INSERT INTO t_shopping
SET userId = 'user5',
userPw = 'pass5',
userName = '소지섭',
address = '부산',
pname = '모자',
price = 30000;

SELECT * FROM t_shopping ;

#1. 손흥민의 주문 개수는? ???
SELECT *
FROM t_order;

SELECT id
FROM t_user
WHERE userName = '손흥민';

SELECT COUNT(id)
FROM t_order
WHERE userno =1;

#2. 손흥민이 산 상품은? ???
SELECT ProductNo
FROM t_order
WHERE userNO =1;

SELECT *
FROM t_product
WHERE id IN(1,5);

#3. 스커트를 산 사람은? ???

SELECT *
FROM t_product
WHERE pname ='스커트';

SELECT userNo
FROM t_order
WHERE productNo =4;

SELECT userName
FROM t_user
WHERE id =4;

#4. 가장 많이 주문한 사람의 아이디와 이름, 주문개수는? ???
SELECT userNo,COUNT(userNo) AS cnt
FROM t_order
GROUP BY userNo
ORDER BY cnt DESC;

SELECT userName
FROM t_user
WHERE id =1;

#5. 소지섭이 사용한 총 금액은? ???
SELECT id
FROM t_user
WHERE userName = '소지섭';

SELECT productNo
FROM t_order
WHERE userNo =5;

SELECT SUM(price)
FROM t_product
WHERE id IN(5,8);

  • 데이터 조회를 편하게 하려면 테이블 한개가 좋은데 이러면 데이터 관리가 어렵고 데이터를 효율적으로 관리하기위해 테이블을 쪼개면 데이터 조회가 불편
    so 조인 제공 -> 실제 물리적으로는 분리되어 있는 테이블을 논리적으로 이어 붙이는것

SELECT *
FROM t_order;

SELECT *
FROM t_user;

SELECT *
FROM t_order
INNER JOIN t_user
ON t_order.userNo=t_user.id;

SELECT *
FROM t_order
INNER JOIN t_product
ON t_order.productNo = t_product.id;

  • 테이블 3개도 조인인가능? yes -> 먼저 나오는 테이블이 왼쪽 , 그다음 나오는 테이블이 오른쪽.
    SELECT *
    FROM t_order
    INNER JOIN t_user
    ON t_order.userNo=t_user.id
    INNER JOIN t_product
    ON t_order.productNo=t_product.id;

  • 조인 결과를 하나의 테이블로 볼수있다.

  • 조인 결과 실제 테이블 x . 가상의 테이블 (view) -> update ,delete ,insert x

DROP DATABASE mall;

CREATE DATABASE mall;

USE mall;

CREATE TABLE t_order(
id INT(5) PRIMARY KEY AUTO_INCREMENT, # 주문번호
userNo INT(5) NOT NULL, # 회원번호
productNo INT(5) NOT NULL # 상품번호
);

CREATE TABLE t_user(
id INT(5) PRIMARY KEY AUTO_INCREMENT, # 회원번호
userId CHAR(200) NOT NULL, # 회원아이디
userPw CHAR(200) NOT NULL, # 회원 비밀번호
userName CHAR(50) NOT NULL, # 회원 이름
addr CHAR(200) NOT NULL # 주소
);

CREATE TABLE t_product(
id INT(5) PRIMARY KEY AUTO_INCREMENT, # 상품 번호
pname CHAR(100) NOT NULL, # 상품 이름
price INT(10) NOT NULL # 상품 가격
);

INSERT INTO t_product
SET pname = '운동화',
price = 1000000;

INSERT INTO t_product
SET pname = '코트',
price = 100000;

INSERT INTO t_product
SET pname = '반바지',
price = 30000;

INSERT INTO t_product
SET pname = '스커트',
price = 15000;

INSERT INTO t_product
SET pname = '코트',
price = 100000;

INSERT INTO t_product
SET pname = '티셔츠',
price = 9000;

INSERT INTO t_product
SET pname = '운동화',
price = 200000;

INSERT INTO t_product
SET pname = '모자',
price = 30000;

INSERT INTO t_user
SET userId = 'user1',
userPw = 'pass1',
userName = '손흥민',
addr = '런던';

INSERT INTO t_user
SET userId = 'user2',
userPw = 'pass2',
userName = '설현',
addr = '서울';

INSERT INTO t_user
SET userId = 'user3',
userPw = 'pass3',
userName = '원빈',
addr = '대전';

INSERT INTO t_user
SET userId = 'user4',
userPw = 'pass4',
userName = '송혜교',
addr = '대구';

INSERT INTO t_user
SET userId = 'user5',
userPw = 'pass5',
userName = '소지섭',
addr = '부산';

INSERT INTO t_user
SET userId = 'user6',
userPw = 'pass6',
userName = '김지원',
addr = '울산';

INSERT INTO t_order
SET userNo = 1,
productNo = 1;

INSERT INTO t_order
SET userNo = 2,
productNo = 2;

INSERT INTO t_order
SET userNo = 3,
productNo = 3;

INSERT INTO t_order
SET userNo = 4,
productNo = 4;

INSERT INTO t_order
SET userNo = 5,
productNo = 5;

INSERT INTO t_order
SET userNo = 6,
productNo = 6;

INSERT INTO t_order
SET userNo = 6,
productNo = 7;

INSERT INTO t_order
SET userNo = 1,
productNo = 5;

INSERT INTO t_order
SET userNo = 4,
productNo = 4;

INSERT INTO t_order
SET userNo = 1,
productNo = 1;

INSERT INTO t_order
SET userNo = 5,
productNo = 8;

SELECT *
FROM t_user;

SELECT *
FROM t_product;

SELECT *
FROM t_order;

SO 문제 해결 한후 다시풀어보면
1. 손흥민의 주문 개수는? ???
SELECT a.userName,COUNT(*)AS'주문개수'
FROM t_user a
INNER JOIN t_order b
ON a.id = b.userNo
WHERE a.userName='손흥민';

  1. 손흥민이 산 상품은? ???
    SELECT a.userName,c.pname
    FROM t_order b
    INNER JOIN t_user a
    ON a.id = b.userNo
    INNER JOIN t_product c
    ON b.productNo=c.id
    WHERE a.userName='손흥민';

  2. 스커트를 산 사람은? ???
    SELECT DISTINCT a.userName,c.pname
    FROM t_order b
    INNER JOIN t_user a
    ON a.id = b.userNo
    INNER JOIN t_product c
    ON b.productNo=c.id
    WHERE c.pname='스커트';

  3. 가장 많이 주문한 사람의 아이디와 이름, 주문개수는? ???
    SELECT DISTINCT a.userId, a.userName, COUNT(*)AS'주문개수'
    FROM t_order b
    INNER JOIN t_user a
    ON a.id = b.userNo
    INNER JOIN t_product c
    ON b.productNo=c.id
    GROUP BY a.userId
    LIMIT 1;

  4. 소지섭이 사용한 총 금액은? ???
    SELECT DISTINCT a.userName, SUM(c.price) AS '총금액'
    FROM t_order b
    INNER JOIN t_user a
    ON a.id = b.userNo
    INNER JOIN t_product c
    ON b.productNo=c.id
    WHERE a.userName ='소지섭';

profile
이동원
이전 포스트

mysql사용법

다음 포스트

자바텍스트

0개의 댓글